/usr/lib/mysql-testsuite/r/compare.result is in percona-server-test-5.6 5.6.22-rel71.0-0ubuntu4.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | 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
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 DOUBLE value: '\x01'
select * from t1 where a=b and b=0x01;
a b
Warnings:
Warning 1292 Truncated incorrect DOUBLE 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#1 */ select `test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`b` = 1) and (concat('01',`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`,(/* 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;
|