|
1
by brian
clean slate |
1 |
drop table if exists t1;
|
2 |
CREATE TABLE t1 (id CHAR(12) not null, PRIMARY KEY (id));
|
|
3 |
insert into t1 values ('000000000001'),('000000000002');
|
|
4 |
explain select * from t1 where id=000000000001;
|
|
5 |
id select_type table type possible_keys key key_len ref rows Extra
|
|
6 |
1 SIMPLE t1 index PRIMARY PRIMARY 12 NULL 2 Using where; Using index
|
|
7 |
select * from t1 where id=000000000001;
|
|
8 |
id
|
|
9 |
000000000001
|
|
10 |
delete from t1 where id=000000000002;
|
|
11 |
select * from t1;
|
|
12 |
id
|
|
13 |
000000000001
|
|
14 |
drop table t1;
|
|
15 |
SELECT 'a' = 'a ';
|
|
16 |
'a' = 'a '
|
|
17 |
1
|
|
18 |
SELECT 'a\0' < 'a';
|
|
19 |
'a\0' < 'a'
|
|
20 |
1
|
|
21 |
SELECT 'a\0' < 'a ';
|
|
22 |
'a\0' < 'a '
|
|
23 |
1
|
|
24 |
SELECT 'a\t' < 'a';
|
|
25 |
'a\t' < 'a'
|
|
26 |
1
|
|
27 |
SELECT 'a\t' < 'a ';
|
|
28 |
'a\t' < 'a '
|
|
29 |
1
|
|
30 |
CREATE TABLE t1 (a char(10) not null);
|
|
31 |
INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a ');
|
|
32 |
SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1;
|
|
33 |
hex(a) STRCMP(a,'a') STRCMP(a,'a ')
|
|
34 |
61 0 0
|
|
35 |
6100 -1 -1
|
|
36 |
6109 -1 -1
|
|
37 |
61 0 0
|
|
38 |
DROP TABLE t1;
|
|
39 |
SELECT CHAR(31) = '', '' = CHAR(31);
|
|
40 |
CHAR(31) = '' '' = CHAR(31)
|
|
41 |
0 0
|
|
42 |
SELECT CHAR(30) = '', '' = CHAR(30);
|
|
43 |
CHAR(30) = '' '' = CHAR(30)
|
|
44 |
0 0
|
|
45 |
create table t1 (a tinyint(1),b binary(1));
|
|
46 |
insert into t1 values (0x01,0x01);
|
|
47 |
select * from t1 where a=b;
|
|
48 |
a b
|
|
49 |
Warnings:
|
|
50 |
Warning 1292 Truncated incorrect DOUBLE value: ''
|
|
51 |
select * from t1 where a=b and b=0x01;
|
|
52 |
a b
|
|
53 |
Warnings:
|
|
54 |
Warning 1292 Truncated incorrect DOUBLE value: ''
|
|
55 |
drop table if exists t1;
|
|
56 |
CREATE TABLE t1 (b int(2) zerofill, c int(2) zerofill);
|
|
57 |
INSERT INTO t1 (b,c) VALUES (1,2), (1,1), (2,2);
|
|
58 |
SELECT CONCAT(b,c), CONCAT(b,c) = '0101' FROM t1;
|
|
59 |
CONCAT(b,c) CONCAT(b,c) = '0101'
|
|
60 |
0102 0
|
|
61 |
0101 1
|
|
62 |
0202 0
|
|
63 |
EXPLAIN EXTENDED SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101';
|
|
64 |
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
65 |
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
66 |
Warnings:
|
|
67 |
Note 1003 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'))
|
|
68 |
SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101';
|
|
69 |
b c
|
|
70 |
01 01
|
|
71 |
CREATE TABLE t2 (a int);
|
|
72 |
INSERT INTO t2 VALUES (1),(2);
|
|
73 |
SELECT a,
|
|
74 |
(SELECT COUNT(*) FROM t1
|
|
75 |
WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x
|
|
76 |
FROM t2 ORDER BY a;
|
|
77 |
a x
|
|
78 |
1 1
|
|
79 |
2 0
|
|
80 |
EXPLAIN EXTENDED
|
|
81 |
SELECT a,
|
|
82 |
(SELECT COUNT(*) FROM t1
|
|
83 |
WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x
|
|
84 |
FROM t2 ORDER BY a;
|
|
85 |
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
86 |
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using filesort
|
|
87 |
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
88 |
Warnings:
|
|
89 |
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
|
|
90 |
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
|
|
91 |
Note 1003 select "test"."t2"."a" AS "a",(select count(0) AS "COUNT(*)" 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"
|
|
92 |
DROP TABLE t1,t2;
|
|
93 |
End of 5.0 tests
|