1
drop table if exists t1;
2
create table t1 (id int not null, str char(10), unique(str));
3
explain select * from t1;
4
id select_type table type possible_keys key key_len ref rows Extra
5
1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
6
insert into t1 values (1, null),(2, null),(3, "foo"),(4, "bar");
7
select * from t1 where str is null;
11
select * from t1 where str="foo";
14
explain select * from t1 where str is null;
15
id select_type table type possible_keys key key_len ref rows Extra
16
1 SIMPLE t1 ref str str 11 const 1 Using where
17
explain select * from t1 where str="foo";
18
id select_type table type possible_keys key key_len ref rows Extra
19
1 SIMPLE t1 const str str 11 const 1
20
explain select * from t1 ignore key (str) where str="foo";
21
id select_type table type possible_keys key key_len ref rows Extra
22
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
23
explain select * from t1 use key (str,str) where str="foo";
24
id select_type table type possible_keys key key_len ref rows Extra
25
1 SIMPLE t1 const str str 11 const 1
26
explain select * from t1 use key (str,str,foo) where str="foo";
27
ERROR 42000: Key 'foo' doesn't exist in table 't1'
28
explain select * from t1 ignore key (str,str,foo) where str="foo";
29
ERROR 42000: Key 'foo' doesn't exist in table 't1'
32
id select_type table type possible_keys key key_len ref rows Extra
33
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
34
create table t1 (a int not null);
35
explain select count(*) from t1;
36
id select_type table type possible_keys key key_len ref rows Extra
37
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
38
insert into t1 values(1);
39
explain select count(*) from t1;
40
id select_type table type possible_keys key key_len ref rows Extra
41
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
42
insert into t1 values(1);
43
explain select count(*) from t1;
44
id select_type table type possible_keys key key_len ref rows Extra
45
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
48
create table ��� (���0 int, ���1 int, key ���0 (���0), key ���01 (���0,���1));
49
insert into ��� (���0) values (1);
50
insert into ��� (���0) values (2);
51
explain select ���0 from ��� where ���0=1;
52
id select_type table type possible_keys key key_len ref rows Extra
53
1 SIMPLE ��� ref ���0,���01 ���0 5 const 1 Using where; Using index
57
explain select 3 into @v1;
58
id select_type table type possible_keys key key_len ref rows Extra
59
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
60
create table t1(f1 int, f2 int);
61
insert into t1 values (1,1);
62
create view v1 as select * from t1 where f1=1;
63
explain extended select * from v1 where f2=1;
64
id select_type table type possible_keys key key_len ref rows filtered Extra
65
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
67
Note 1003 select '1' AS `f1`,'1' AS `f2` from `test`.`t1` where 1
68
explain extended select * from t1 where 0;
69
id select_type table type possible_keys key key_len ref rows filtered Extra
70
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
72
Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where 0
73
explain extended select * from t1 where 1;
74
id select_type table type possible_keys key key_len ref rows filtered Extra
75
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
77
Note 1003 select '1' AS `f1`,'1' AS `f2` from `test`.`t1` where 1
78
explain extended select * from t1 having 0;
79
id select_type table type possible_keys key key_len ref rows filtered Extra
80
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
82
Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` having 0
83
explain extended select * from t1 having 1;
84
id select_type table type possible_keys key key_len ref rows filtered Extra
85
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
87
Note 1003 select '1' AS `f1`,'1' AS `f2` from `test`.`t1` having 1
90
CREATE TABLE t1(c INT);
91
INSERT INTO t1 VALUES (),();
92
CREATE TABLE t2 (b INT,
93
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
94
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
95
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
96
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
97
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
98
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
99
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
100
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b));
101
INSERT INTO t2 VALUES (),(),();
102
EXPLAIN SELECT 1 FROM
103
(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
104
id select_type table type possible_keys key key_len ref rows Extra
105
X X X X X X X X X const row not found
107
X X X X X X X X X Range checked for each record (index map: 0xFFFFFFFFFF)
110
CREATE TABLE t1(a INT);
111
CREATE TABLE t2(a INT);
112
INSERT INTO t1 VALUES (1),(2);
113
INSERT INTO t2 VALUES (1),(2);
114
EXPLAIN EXTENDED SELECT 1
115
FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1;
116
id select_type table type possible_keys key key_len ref rows filtered Extra
117
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
118
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
119
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer
121
Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
122
EXPLAIN EXTENDED SELECT 1
123
FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1;
124
id select_type table type possible_keys key key_len ref rows filtered Extra
125
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
126
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
127
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer
129
Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
131
'EXPLAIN EXTENDED SELECT 1
132
FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1';
134
id select_type table type possible_keys key key_len ref rows filtered Extra
135
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
136
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
137
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer
139
Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
141
'EXPLAIN EXTENDED SELECT 1
142
FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1';
144
id select_type table type possible_keys key key_len ref rows filtered Extra
145
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
146
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
147
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer
149
Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
151
id select_type table type possible_keys key key_len ref rows filtered Extra
152
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
153
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
154
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer
156
Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
158
CREATE TABLE t1 (a INT PRIMARY KEY);
159
EXPLAIN EXTENDED SELECT COUNT(a) FROM t1 USE KEY(a);
160
ERROR 42000: Key 'a' doesn't exist in table 't1'
162
CREATE TABLE t1(a LONGTEXT);
163
INSERT INTO t1 VALUES (repeat('a',@@global.max_allowed_packet));
164
INSERT INTO t1 VALUES (repeat('b',@@global.max_allowed_packet));
165
EXPLAIN SELECT DISTINCT 1 FROM t1,
166
(SELECT DISTINCTROW a AS away FROM t1 GROUP BY a WITH ROLLUP) as d1
168
ERROR 42S22: Unknown column 'd1.a' in 'where clause'
171
# Bug#37870: Usage of uninitialized value caused failed assertion.
173
create table t1 (dt datetime not null, t time not null);
174
create table t2 (dt datetime not null);
175
insert into t1 values ('2001-01-01 1:1:1', '1:1:1'),
176
('2001-01-01 1:1:1', '1:1:1');
177
insert into t2 values ('2001-01-01 1:1:1'), ('2001-01-01 1:1:1');
179
EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
180
id select_type table type possible_keys key key_len ref rows Extra
181
1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where
182
2 DEPENDENT SUBQUERY INNR ALL NULL NULL NULL NULL 2 Using where
184
SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
187
EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
188
id select_type table type possible_keys key key_len ref rows Extra
189
1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where
190
2 DEPENDENT SUBQUERY INNR ALL NULL NULL NULL NULL 2 Using where
192
SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
199
# explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode
201
CREATE TABLE t1 (f1 INT);
202
SELECT @@session.sql_mode INTO @old_sql_mode;
203
SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
204
EXPLAIN EXTENDED SELECT 1 FROM t1
205
WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
206
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
209
Error 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
210
Note 1003 select 1 AS `1` from `test`.`t1` where <not>(<exists>(...))
211
SET SESSION sql_mode=@old_sql_mode;