1--echo #
2--echo # Tests with $signedness type $int_type $nullness against decimal
3--echo #
4eval CREATE TABLE $table_name (colA $int_type $signedness $nullness);
5eval INSERT INTO $table_name VALUES ($minint), ($minint_plus_1),
6                                    (0),(1),(3),
7                                    ($maxint_minus_1), ($maxint),
8                                    ($nullval);
9eval ANALYZE TABLE $table_name;
10
11eval SELECT * FROM $table_name;
12
13eval SELECT * FROM $table_name WHERE colA < $dec_0_5;
14eval SELECT * FROM $table_name WHERE colA <= $dec_0_5;
15eval SELECT * FROM $table_name WHERE colA > $dec_0_5;
16eval SELECT * FROM $table_name WHERE colA >= $dec_0_5;
17eval SELECT * FROM $table_name WHERE colA = $dec_0_5;
18eval SELECT * FROM $table_name WHERE colA <> $dec_0_5;
19
20eval SELECT * FROM $table_name WHERE colA < $dec_minus_0_5;
21eval SELECT * FROM $table_name WHERE colA <= $dec_minus_0_5;
22eval SELECT * FROM $table_name WHERE colA > $dec_minus_0_5;
23eval SELECT * FROM $table_name WHERE colA >= $dec_minus_0_5;
24eval SELECT * FROM $table_name WHERE colA = $dec_minus_0_5;
25eval SELECT * FROM $table_name WHERE colA <> $dec_minus_0_5;
26
27eval SELECT * FROM $table_name WHERE colA < $dec_max_plus_delta;
28eval SELECT * FROM $table_name WHERE colA <= $dec_max_plus_delta;
29eval SELECT * FROM $table_name WHERE colA > $dec_max_plus_delta;
30eval SELECT * FROM $table_name WHERE colA >= $dec_max_plus_delta;
31eval SELECT * FROM $table_name WHERE colA = $dec_max_plus_delta;
32eval SELECT * FROM $table_name WHERE colA <> $dec_max_plus_delta;
33
34eval SELECT * FROM $table_name WHERE colA < $dec_max_minus_delta;
35eval SELECT * FROM $table_name WHERE colA <= $dec_max_minus_delta;
36eval SELECT * FROM $table_name WHERE colA > $dec_max_minus_delta;
37eval SELECT * FROM $table_name WHERE colA >= $dec_max_minus_delta;
38eval SELECT * FROM $table_name WHERE colA = $dec_max_minus_delta;
39eval SELECT * FROM $table_name WHERE colA <> $dec_max_minus_delta;
40
41eval SELECT * FROM $table_name WHERE colA < $dec_min_plus_delta;
42eval SELECT * FROM $table_name WHERE colA <= $dec_min_plus_delta;
43eval SELECT * FROM $table_name WHERE colA > $dec_min_plus_delta;
44eval SELECT * FROM $table_name WHERE colA >= $dec_min_plus_delta;
45eval SELECT * FROM $table_name WHERE colA = $dec_min_plus_delta;
46eval SELECT * FROM $table_name WHERE colA <> $dec_min_plus_delta;
47
48eval SELECT * FROM $table_name WHERE colA < $dec_min_minus_delta;
49eval SELECT * FROM $table_name WHERE colA <= $dec_min_minus_delta;
50eval SELECT * FROM $table_name WHERE colA > $dec_min_minus_delta;
51eval SELECT * FROM $table_name WHERE colA >= $dec_min_minus_delta;
52eval SELECT * FROM $table_name WHERE colA = $dec_min_minus_delta;
53eval SELECT * FROM $table_name WHERE colA <> $dec_min_minus_delta;
54
55eval SELECT * FROM $table_name WHERE colA < $dec_too_big_positive_for_any_int;
56eval SELECT * FROM $table_name WHERE colA <= $dec_too_big_positive_for_any_int;
57eval SELECT * FROM $table_name WHERE colA > $dec_too_big_positive_for_any_int;
58eval SELECT * FROM $table_name WHERE colA >= $dec_too_big_positive_for_any_int;
59eval SELECT * FROM $table_name WHERE colA = $dec_too_big_positive_for_any_int;
60eval SELECT * FROM $table_name WHERE colA <> $dec_too_big_positive_for_any_int;
61
62eval SELECT * FROM $table_name WHERE colA < $dec_too_big_negative_for_any_int;
63eval SELECT * FROM $table_name WHERE colA <= $dec_too_big_negative_for_any_int;
64eval SELECT * FROM $table_name WHERE colA > $dec_too_big_negative_for_any_int;
65eval SELECT * FROM $table_name WHERE colA >= $dec_too_big_negative_for_any_int;
66eval SELECT * FROM $table_name WHERE colA = $dec_too_big_negative_for_any_int;
67eval SELECT * FROM $table_name WHERE colA <> $dec_too_big_negative_for_any_int;
68
69eval PREPARE p_less    FROM 'SELECT * FROM $table_name WHERE colA < ?';
70eval PREPARE p_less_eq FROM 'SELECT * FROM $table_name WHERE colA <= ?';
71eval PREPARE p_greater FROM 'SELECT * FROM $table_name WHERE colA > ?';
72eval PREPARE p_gret_eq FROM 'SELECT * FROM $table_name WHERE colA >= ?';
73eval PREPARE p_equal   FROM 'SELECT * FROM $table_name WHERE colA = ?';
74eval PREPARE p_n_equal FROM 'SELECT * FROM $table_name WHERE colA <> ?';
75
76eval SET @dec_0_5=$dec_0_5;
77eval SET @dec_minus_0_5=$dec_minus_0_5;
78eval SET @dec_max_plus_delta=$dec_max_plus_delta;
79eval SET @dec_max_minus_delta=$dec_max_minus_delta;
80eval SET @dec_min_plus_delta=$dec_min_plus_delta;
81eval SET @dec_min_minus_delta=$dec_min_minus_delta;
82eval SET @dec_too_big_positive_for_any_int=$dec_too_big_positive_for_any_int;
83eval SET @dec_too_big_negative_for_any_int=$dec_too_big_negative_for_any_int;
84
85eval EXECUTE p_less    USING @dec_0_5;
86eval EXECUTE p_less_eq USING @dec_0_5;
87eval EXECUTE p_greater USING @dec_0_5;
88eval EXECUTE p_gret_eq USING @dec_0_5;
89eval EXECUTE p_equal   USING @dec_0_5;
90eval EXECUTE p_n_equal USING @dec_0_5;
91
92eval EXECUTE p_less    USING @dec_minus_0_5;
93eval EXECUTE p_less_eq USING @dec_minus_0_5;
94eval EXECUTE p_greater USING @dec_minus_0_5;
95eval EXECUTE p_gret_eq USING @dec_minus_0_5;
96eval EXECUTE p_equal   USING @dec_minus_0_5;
97eval EXECUTE p_n_equal USING @dec_minus_0_5;
98
99eval EXECUTE p_less    USING @dec_max_plus_delta;
100eval EXECUTE p_less_eq USING @dec_max_plus_delta;
101eval EXECUTE p_greater USING @dec_max_plus_delta;
102eval EXECUTE p_gret_eq USING @dec_max_plus_delta;
103eval EXECUTE p_equal   USING @dec_max_plus_delta;
104eval EXECUTE p_n_equal USING @dec_max_plus_delta;
105
106eval EXECUTE p_less    USING @dec_max_minus_delta;
107eval EXECUTE p_less_eq USING @dec_max_minus_delta;
108eval EXECUTE p_greater USING @dec_max_minus_delta;
109eval EXECUTE p_gret_eq USING @dec_max_minus_delta;
110eval EXECUTE p_equal   USING @dec_max_minus_delta;
111eval EXECUTE p_n_equal USING @dec_max_minus_delta;
112
113eval EXECUTE p_less    USING @dec_min_plus_delta;
114eval EXECUTE p_less_eq USING @dec_min_plus_delta;
115eval EXECUTE p_greater USING @dec_min_plus_delta;
116eval EXECUTE p_gret_eq USING @dec_min_plus_delta;
117eval EXECUTE p_equal   USING @dec_min_plus_delta;
118eval EXECUTE p_n_equal USING @dec_min_plus_delta;
119
120eval EXECUTE p_less    USING @dec_min_minus_delta;
121eval EXECUTE p_less_eq USING @dec_min_minus_delta;
122eval EXECUTE p_greater USING @dec_min_minus_delta;
123eval EXECUTE p_gret_eq USING @dec_min_minus_delta;
124eval EXECUTE p_equal   USING @dec_min_minus_delta;
125eval EXECUTE p_n_equal USING @dec_min_minus_delta;
126
127eval EXECUTE p_less    USING @dec_too_big_positive_for_any_int;
128eval EXECUTE p_less_eq USING @dec_too_big_positive_for_any_int;
129eval EXECUTE p_greater USING @dec_too_big_positive_for_any_int;
130eval EXECUTE p_gret_eq USING @dec_too_big_positive_for_any_int;
131eval EXECUTE p_equal   USING @dec_too_big_positive_for_any_int;
132eval EXECUTE p_n_equal USING @dec_too_big_positive_for_any_int;
133
134eval EXECUTE p_less    USING @dec_too_big_negative_for_any_int;
135eval EXECUTE p_less_eq USING @dec_too_big_negative_for_any_int;
136eval EXECUTE p_greater USING @dec_too_big_negative_for_any_int;
137eval EXECUTE p_gret_eq USING @dec_too_big_negative_for_any_int;
138eval EXECUTE p_equal   USING @dec_too_big_negative_for_any_int;
139eval EXECUTE p_n_equal USING @dec_too_big_negative_for_any_int;
140
141DROP PREPARE p_less;
142DROP PREPARE p_less_eq;
143DROP PREPARE p_greater;
144DROP PREPARE p_gret_eq;
145DROP PREPARE p_equal;
146DROP PREPARE p_n_equal;
147
148eval DROP TABLE $table_name;
149
150--echo Cruft inside string
151CREATE TABLE t(i INT);
152INSERT INTO t VALUES (-1), (0), (1);
153ANALYZE TABLE t;
154let $query=SELECT * FROM t WHERE i > 'A';
155eval $query;
156eval EXPLAIN $query;
157DROP TABLE t;
158
159--echo Detect decimal inside a string when compared to int
160--echo Used to return one row; should be none.
161CREATE TABLE t(i BIGINT);
162ANALYZE TABLE t;
163INSERT INTO t VALUES (1), (2);
164let $query=SELECT * FROM t WHERE i = '1.1';
165eval $query;
166eval EXPLAIN $query;
167
168DROP TABLE t;
169
170--echo Detect out of range real inside a string when compared to int
171--echo Used to return one row; should be none.
172CREATE TABLE t(i TINYINT);
173ANALYZE TABLE t;
174INSERT INTO t VALUES (1), (2);
175let $query=SELECT * FROM t WHERE i > '1E10';
176eval $query;
177eval EXPLAIN $query;
178
179DROP TABLE t;
180
181--echo Detect out of range real inside a string when compared to int
182--echo Used to return one row; should be none.
183CREATE TABLE t(i TINYINT);
184ANALYZE TABLE t;
185INSERT INTO t VALUES (0);
186let $query=SELECT * FROM t WHERE i = '1E-308';
187eval $query;
188eval EXPLAIN $query;
189
190--echo Above query should yield the same as this:
191let $query=SELECT * FROM t WHERE i = 1E-308;
192eval $query;
193eval EXPLAIN $query;
194
195DROP TABLE t;
196