diff options
Diffstat (limited to 'mysql-test/main/compare.result')
| -rw-r--r-- | mysql-test/main/compare.result | 124 |
1 files changed, 124 insertions, 0 deletions
diff --git a/mysql-test/main/compare.result b/mysql-test/main/compare.result new file mode 100644 index 00000000..70664e2c --- /dev/null +++ b/mysql-test/main/compare.result @@ -0,0 +1,124 @@ +drop table if exists t1; +CREATE TABLE t1 (id CHAR(12) not null, PRIMARY KEY (id)); +insert into t1 values ('000000000001'),('000000000002'); +explain select * from t1 where id=000000000001; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 12 NULL 2 Using where; Using index +Warnings: +Note 1105 Cannot use key `PRIMARY` part[0] for lookup: `test`.`t1`.`id` of type `char` = "1" of type `bigint` +select * from t1 where id=000000000001; +id +000000000001 +delete from t1 where id=000000000002; +select * from t1; +id +000000000001 +drop table t1; +SELECT 'a' = 'a '; +'a' = 'a ' +1 +SELECT 'a\0' < 'a'; +'a\0' < 'a' +1 +SELECT 'a\0' < 'a '; +'a\0' < 'a ' +1 +SELECT 'a\t' < 'a'; +'a\t' < 'a' +1 +SELECT 'a\t' < 'a '; +'a\t' < 'a ' +1 +CREATE TABLE t1 (a char(10) not null); +INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a '); +SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1; +hex(a) STRCMP(a,'a') STRCMP(a,'a ') +61 0 0 +6100 -1 -1 +6109 -1 -1 +61 0 0 +DROP TABLE t1; +SELECT CHAR(31) = '', '' = CHAR(31); +CHAR(31) = '' '' = CHAR(31) +0 0 +SELECT CHAR(30) = '', '' = CHAR(30); +CHAR(30) = '' '' = CHAR(30) +0 0 +create table t1 (a tinyint(1),b binary(1)); +insert into t1 values (0x01,0x01); +select * from t1 where a=b; +a b +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '\x01' +select * from t1 where a=b and b=0x01; +a b +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '\x01' +drop table if exists t1; +CREATE TABLE t1 (b int(2) zerofill, c int(2) zerofill); +INSERT INTO t1 (b,c) VALUES (1,2), (1,1), (2,2); +SELECT CONCAT(b,c), CONCAT(b,c) = '0101' FROM t1; +CONCAT(b,c) CONCAT(b,c) = '0101' +0102 0 +0101 1 +0202 0 +EXPLAIN EXTENDED SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`b` = 1 and concat(`test`.`t1`.`b`,`test`.`t1`.`c`) = '0101' +SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101'; +b c +01 01 +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES (1),(2); +SELECT a, +(SELECT COUNT(*) FROM t1 +WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x +FROM t2 ORDER BY a; +a x +1 1 +2 0 +EXPLAIN EXTENDED +SELECT a, +(SELECT COUNT(*) FROM t1 +WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x +FROM t2 ORDER BY a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using filesort +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`>((/* select#2 */ select count(0) from `test`.`t1` where `test`.`t1`.`b` = `test`.`t2`.`a` and concat(`test`.`t1`.`b`,`test`.`t1`.`c`) = concat('0',`test`.`t2`.`a`,'01'))) AS `x` from `test`.`t2` order by `test`.`t2`.`a` +DROP TABLE t1,t2; +CREATE TABLE t1 (a TIMESTAMP); +INSERT INTO t1 VALUES (NOW()),(NOW()),(NOW()); +SELECT * FROM t1 WHERE a > '2008-01-01' AND a = '0000-00-00'; +a +DROP TABLE t1; +End of 5.0 tests +CREATE TABLE t1(a INT ZEROFILL); +SELECT 1 FROM t1 WHERE t1.a IN (1, t1.a) AND t1.a=2; +1 +DROP TABLE t1; +CREATE TABLE t1 (a char(2), index (a)); +insert into t1 values ("aa"),("bb"); +select * from t1 where a="aa"; +a +aa +select * from t1 where a="aaa"; +a +select * from t1 where a="aa "; +a +aa +select * from t1 where a>="aaa"; +a +bb +explain select * from t1 where a="aaa"; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 3 const 1 Using where; Using index +explain select * from t1 where a="aa "; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 3 const 1 Using where; Using index +drop table t1; |