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