1# 2# Bug when using comparions of strings and integers. 3# 4 5--disable_warnings 6drop table if exists t1; 7--enable_warnings 8 9CREATE TABLE t1 (id CHAR(12) not null, PRIMARY KEY (id)); 10insert into t1 values ('000000000001'),('000000000002'); 11explain select * from t1 where id=000000000001; 12select * from t1 where id=000000000001; 13delete from t1 where id=000000000002; 14select * from t1; 15drop table t1; 16 17# 18# Check the following: 19# "a" == "a " 20# "a\0" < "a" 21# "a\0" < "a " 22 23SELECT 'a' = 'a '; 24SELECT 'a\0' < 'a'; 25SELECT 'a\0' < 'a '; 26SELECT 'a\t' < 'a'; 27SELECT 'a\t' < 'a '; 28 29CREATE TABLE t1 (a char(10) not null); 30INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a '); 31SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1; 32DROP TABLE t1; 33 34# Bug #8134: Comparison against CHAR(31) at end of string 35SELECT CHAR(31) = '', '' = CHAR(31); 36# Extra test 37SELECT CHAR(30) = '', '' = CHAR(30); 38 39# End of 4.1 tests 40 41# 42#Bug #21159: Optimizer: wrong result after AND with different data types 43# 44create table t1 (a tinyint(1),b binary(1)); 45insert into t1 values (0x01,0x01); 46select * from t1 where a=b; 47select * from t1 where a=b and b=0x01; 48drop table if exists t1; 49 50# 51# Bug #31887: DML Select statement not returning same results when executed 52# in version 5 53# 54 55CREATE TABLE t1 (b int(2) zerofill, c int(2) zerofill); 56INSERT INTO t1 (b,c) VALUES (1,2), (1,1), (2,2); 57 58SELECT CONCAT(b,c), CONCAT(b,c) = '0101' FROM t1; 59 60EXPLAIN EXTENDED SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101'; 61SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101'; 62 63CREATE TABLE t2 (a int); 64INSERT INTO t2 VALUES (1),(2); 65 66SELECT a, 67 (SELECT COUNT(*) FROM t1 68 WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x 69FROM t2 ORDER BY a; 70 71EXPLAIN EXTENDED 72SELECT a, 73 (SELECT COUNT(*) FROM t1 74 WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x 75FROM t2 ORDER BY a; 76 77DROP TABLE t1,t2; 78 79# 80# Bug #39353: Multiple conditions on timestamp column crashes server 81# 82 83CREATE TABLE t1 (a TIMESTAMP); 84INSERT INTO t1 VALUES (NOW()),(NOW()),(NOW()); 85SELECT * FROM t1 WHERE a > '2008-01-01' AND a = '0000-00-00'; 86DROP TABLE t1; 87 88--echo End of 5.0 tests 89 90# 91# Bug #11764818 57692: Crash in item_func_in::val_int() with ZEROFILL 92# 93 94CREATE TABLE t1(a INT ZEROFILL); 95SELECT 1 FROM t1 WHERE t1.a IN (1, t1.a) AND t1.a=2; 96DROP TABLE t1; 97 98# 99# Check what happens when comparing to long string 100# 101 102CREATE TABLE t1 (a char(2), index (a)); 103insert into t1 values ("aa"),("bb"); 104select * from t1 where a="aa"; 105select * from t1 where a="aaa"; 106select * from t1 where a="aa "; 107select * from t1 where a>="aaa"; 108explain select * from t1 where a="aaa"; 109explain select * from t1 where a="aa "; 110drop table t1; 111