drop table if exists t1;
create table t1 (id int not null, str char(10), unique(str));
explain select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
insert into t1 values (1, null),(2, null),(3, "foo"),(4, "bar");
select * from t1 where str is null;
id str
1 NULL
2 NULL
select * from t1 where str="foo";
id str
3 foo
explain select * from t1 where str is null;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref str str 11 const 1 Using index condition
explain format=json select * from t1 where str is null;
EXPLAIN
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "t1",
"access_type": "ref",
"possible_keys": [
"str"
],
"key": "str",
"used_key_parts": [
"str"
],
"key_length": "11",
"ref": [
"const"
],
"rows": 1,
"filtered": 100,
"index_condition": "isnull(`test`.`t1`.`str`)"
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`str` AS `str` from `test`.`t1` where isnull(`test`.`t1`.`str`)
explain select * from t1 where str="foo";
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const str str 11 const 1 NULL
explain select * from t1 ignore key (str) where str="foo";
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
explain select * from t1 use key (str,str) where str="foo";
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const str str 11 const 1 NULL
explain select * from t1 use key (str,str,foo) where str="foo";
ERROR 42000: Key 'foo' doesn't exist in table 't1'
explain select * from t1 ignore key (str,str,foo) where str="foo";
ERROR 42000: Key 'foo' doesn't exist in table 't1'
drop table t1;
explain select 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
create table t1 (a int not null);
explain select count(*) from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
insert into t1 values(1);
explain select count(*) from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
insert into t1 values(1);
explain select count(*) from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
drop table t1;
set names koi8r;
create table �� (���0 int, ���1 int, key ���0 (���0), key ���01 (���0,���1));
insert into �� (���0) values (1);
insert into �� (���0) values (2);
explain select ���0 from �� where ���0=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE �� ref ���0,���01 ���0 5 const 1 Using index
drop table ��;
set names latin1;
select 3 into @v1;
explain select 3 into @v1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
create table t1(f1 int, f2 int);
insert into t1 values (1,1);
create view v1 as select * from t1 where f1=1;
explain extended select * from v1 where f2=1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select '1' AS `f1`,'1' AS `f2` from dual where 1
explain extended select * from t1 where 0;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where 0
explain extended select * from t1 where 1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select '1' AS `f1`,'1' AS `f2` from dual where 1
explain extended select * from t1 having 0;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` having 0
explain extended select * from t1 having 1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select '1' AS `f1`,'1' AS `f2` from dual having 1
drop view v1;
drop table t1;
CREATE TABLE t1(c INT);
INSERT INTO t1 VALUES (),();
CREATE TABLE t2 (b INT,
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b),
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b));
Warnings:
Note 1831 Duplicate index 'b_2' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_3' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_4' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_5' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_6' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_7' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_8' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_9' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_10' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_11' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_12' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_13' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_14' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_15' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_16' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_17' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_18' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_19' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_20' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_21' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_22' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_23' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_24' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_25' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_26' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_27' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_28' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_29' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_30' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_31' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_32' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_33' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_34' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_35' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_36' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_37' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_38' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_39' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Note 1831 Duplicate index 'b_40' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
INSERT INTO t2 VALUES (),(),();
EXPLAIN SELECT 1 FROM
(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
id select_type table type possible_keys key key_len ref rows Extra
X X X X X X X X X const row not found
X X X X X X X X X NULL
X X X X X X X X X Range checked for each record (index map: 0xFFFFFFFFFF)
DROP TABLE t2;
DROP TABLE t1;
CREATE TABLE t1(a INT);
CREATE TABLE t2(a INT);
INSERT INTO t1 VALUES (1),(2);
INSERT INTO t2 VALUES (1),(2);
EXPLAIN EXTENDED SELECT 1
FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 NULL
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop)
Warnings:
Note 1003 /* select#1 */ select 1 AS `1` from (/* select#2 */ select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
EXPLAIN EXTENDED SELECT 1
FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 NULL
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop)
Warnings:
Note 1003 /* select#1 */ select 1 AS `1` from (/* select#2 */ select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
prepare s1 from
'EXPLAIN EXTENDED SELECT 1
FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1';
execute s1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 NULL
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop)
Warnings:
Note 1003 /* select#1 */ select 1 AS `1` from (/* select#2 */ select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
prepare s1 from
'EXPLAIN EXTENDED SELECT 1
FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1';
execute s1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 NULL
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop)
Warnings:
Note 1003 /* select#1 */ select 1 AS `1` from (/* select#2 */ select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
execute s1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 NULL
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop)
Warnings:
Note 1003 /* select#1 */ select 1 AS `1` from (/* select#2 */ select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT PRIMARY KEY);
EXPLAIN EXTENDED SELECT COUNT(a) FROM t1 USE KEY(a);
ERROR 42000: Key 'a' doesn't exist in table 't1'
DROP TABLE t1;
CREATE TABLE t1(a LONGTEXT);
INSERT INTO t1 VALUES (repeat('a',@@global.max_allowed_packet));
INSERT INTO t1 VALUES (repeat('b',@@global.max_allowed_packet));
EXPLAIN SELECT DISTINCT 1 FROM t1,
(SELECT a AS away FROM t1 GROUP BY a WITH ROLLUP) as d1
WHERE t1.a = d1.a;
ERROR 42S22: Unknown column 'd1.a' in 'where clause'
EXPLAIN SELECT DISTINCT 1 FROM t1,
(SELECT DISTINCTROW a AS away FROM t1 GROUP BY a WITH ROLLUP) as d1
WHERE t1.a = d1.a;
ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT
DROP TABLE t1;
#
# Bug#48295:
# explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode
#
CREATE TABLE t1 (f1 INT);
SELECT @@session.sql_mode INTO @old_sql_mode;
SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
EXPLAIN EXTENDED SELECT 1 FROM t1
WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
2 SUBQUERY t system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where 0
SET SESSION sql_mode=@old_sql_mode;
DROP TABLE t1;
End of 5.0 tests.
#
# Bug#37870: Usage of uninitialized value caused failed assertion.
#
set @opt_sw_save= @@optimizer_switch;
create table t1 (dt datetime not null, t time not null);
create table t2 (dt datetime not null);
insert into t1 values ('2001-01-01 1:1:1', '1:1:1'),
('2001-01-01 1:1:1', '1:1:1');
insert into t2 values ('2001-01-01 1:1:1'), ('2001-01-01 1:1:1');
flush tables;
EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY INNR ALL NULL NULL NULL NULL 2 Using where
flush tables;
SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
dt
flush tables;
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' );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY INNR ALL NULL NULL NULL NULL 2 Using where
flush tables;
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' );
dt
2001-01-01 01:01:01
2001-01-01 01:01:01
drop tables t1, t2;
set optimizer_switch= @opt_sw_save;
#
# Bug#47669: Query showed by EXPLAIN EXTENDED gives different result from original query
#
CREATE TABLE t1 (c int);
INSERT INTO t1 VALUES (NULL);
CREATE TABLE t2 (d int);
INSERT INTO t2 VALUES (NULL), (0);
EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 NULL
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where (`test`.`t2`.`d` = NULL)) AS `(SELECT 1 FROM t2 WHERE d = c)` from dual
DROP TABLE t1, t2;
#
# Bug#30302: Tables that were optimized away are printed in the
# EXPLAIN EXTENDED warning.
#
create table t1(f1 int);
create table t2(f2 int);
insert into t1 values(1);
insert into t2 values(1),(2);
explain extended select * from t1 where f1=1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select '1' AS `f1` from dual where 1
explain extended select * from t1 join t2 on f1=f2 where f1=1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1003 /* select#1 */ select '1' AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t2` where (`test`.`t2`.`f2` = 1)
drop table t1,t2;
#
# Bug #48419: another explain crash..
#
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (b BLOB, KEY b(b(100)));
INSERT INTO t2 VALUES ('1'), ('2'), ('3');
FLUSH TABLES;
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t1 t JOIN t2 WHERE b <= 1 AND t.a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1, t2;
#
# Bug #48573: difference of index selection between rpm binary and
# .tar.gz, windows vs linux..
#
CREATE TABLE t1(c1 INT, c2 INT, c4 INT, c5 INT, KEY(c2, c5), KEY(c2, c4, c5));
INSERT INTO t1 VALUES(4, 1, 1, 1);
INSERT INTO t1 VALUES(3, 1, 1, 1);
INSERT INTO t1 VALUES(2, 1, 1, 1);
INSERT INTO t1 VALUES(1, 1, 1, 1);
EXPLAIN SELECT c1 FROM t1 WHERE c2 = 1 AND c4 = 1 AND c5 = 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref c2,c2_2 c2 10 const,const 3 Using where
DROP TABLE t1;
#
# Bug#56814 Explain + subselect + fulltext crashes server
#
CREATE TABLE t1(f1 VARCHAR(6) NOT NULL,
FULLTEXT KEY(f1),UNIQUE(f1));
INSERT INTO t1 VALUES ('test');
EXPLAIN SELECT 1 FROM t1
WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST (""))
WHERE t1.f1 GROUP BY t1.f1));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 NULL
2 SUBQUERY a system NULL NULL NULL NULL 1 NULL
2 SUBQUERY t1 fulltext f1 f1 0 NULL 1 Using where
PREPARE stmt FROM
'EXPLAIN SELECT 1 FROM t1
WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a
ON (MATCH(t1.f1) AGAINST (""))
WHERE t1.f1 GROUP BY t1.f1))';
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 NULL
2 SUBQUERY a system NULL NULL NULL NULL 1 NULL
2 SUBQUERY t1 fulltext f1 f1 0 NULL 1 Using where
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 NULL
2 SUBQUERY a system NULL NULL NULL NULL 1 NULL
2 SUBQUERY t1 fulltext f1 f1 0 NULL 1 Using where
DEALLOCATE PREPARE stmt;
PREPARE stmt FROM
'EXPLAIN SELECT 1 FROM t1
WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a
ON (MATCH(t1.f1) AGAINST (""))
WHERE t1.f1 GROUP BY t1.f1))';
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 NULL
2 SUBQUERY a system NULL NULL NULL NULL 1 NULL
2 SUBQUERY t1 fulltext f1 f1 0 NULL 1 Using where
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 NULL
2 SUBQUERY a system NULL NULL NULL NULL 1 NULL
2 SUBQUERY t1 fulltext f1 f1 0 NULL 1 Using where
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
End of 5.1 tests.
#
# Bug#46860:
# Crash/segfault using EXPLAIN EXTENDED on query using UNION in subquery.
#
drop table if exists t1;
create table `t1` (`a` int);
explain extended select 1 from `t1`, `t1` as `t2`
where `t1`.`a` > all ( (select `a` from `t1` ) union (select `a`) );
ERROR 23000: Column 'a' in field list is ambiguous
drop table t1;
#
# BUG#30597: Change EXPLAIN output to include extrema of
# UNION components
#
EXPLAIN
SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
2 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
5 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
6 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
7 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
8 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
9 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
10 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
11 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
12 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
13 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
14 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
15 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
16 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
17 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
18 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
19 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
20 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
21 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
22 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
23 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
24 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
25 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...,25> ALL NULL NULL NULL NULL NULL Using temporary
# End BUG#30597
#
# BUG#53562: EXPLAIN statement should hint when
# index is not used due to type conversion
#
CREATE TABLE t1 (url char(1) PRIMARY KEY);
INSERT INTO t1 VALUES ('1'),('2'),('3'),('4'),('5');
# Normally, lookup access on primary key is done
EXPLAIN EXTENDED SELECT * FROM t1 WHERE url='1';
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 const PRIMARY PRIMARY 1 const 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select '1' AS `url` from `test`.`t1` where 1
# Test that index can't be used for lookup due to type conversion
# (comparing char and int)
SELECT * FROM t1 WHERE url=1;
url
1
EXPLAIN EXTENDED SELECT * FROM t1 WHERE url=1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index PRIMARY PRIMARY 1 NULL 5 100.00 Using where; Using index
Warnings:
Warning 1739 Cannot use ref access on index 'PRIMARY' due to type or collation conversion on field 'url'
Warning 1739 Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'url'
Note 1003 /* select#1 */ select `test`.`t1`.`url` AS `url` from `test`.`t1` where (`test`.`t1`.`url` = 1)
# Test that index can't be used for lookup due to collation mismatch
SELECT * FROM t1 WHERE url='1' collate latin1_german2_ci;
url
1
EXPLAIN EXTENDED SELECT * FROM t1 WHERE url='1' collate latin1_german2_ci;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index PRIMARY PRIMARY 1 NULL 5 100.00 Using where; Using index
Warnings:
Warning 1739 Cannot use ref access on index 'PRIMARY' due to type or collation conversion on field 'url'
Warning 1739 Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'url'
Note 1003 /* select#1 */ select `test`.`t1`.`url` AS `url` from `test`.`t1` where (`test`.`t1`.`url` = <cache>(('1' collate latin1_german2_ci)))
# Normally, range access on primary key is done
EXPLAIN EXTENDED SELECT * FROM t1 WHERE url>'3';
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 range PRIMARY PRIMARY 1 NULL 3 100.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`url` AS `url` from `test`.`t1` where (`test`.`t1`.`url` > '3')
# Test that range access on index can't be done due to type conversion
# (comparing char and int)
SELECT * FROM t1 WHERE url>3;
url
4
5
EXPLAIN EXTENDED SELECT * FROM t1 WHERE url>3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index PRIMARY PRIMARY 1 NULL 5 100.00 Using where; Using index
Warnings:
Warning 1739 Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'url'
Note 1003 /* select#1 */ select `test`.`t1`.`url` AS `url` from `test`.`t1` where (`test`.`t1`.`url` > 3)
# Test that range access on index can't be done due to collation mismatch
SELECT * FROM t1 WHERE url>'3' collate latin1_german2_ci;
url
4
5
EXPLAIN EXTENDED SELECT * FROM t1 WHERE url>'3' collate latin1_german2_ci;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index PRIMARY PRIMARY 1 NULL 5 100.00 Using where; Using index
Warnings:
Warning 1739 Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'url'
Note 1003 /* select#1 */ select `test`.`t1`.`url` AS `url` from `test`.`t1` where (`test`.`t1`.`url` > <cache>(('3' collate latin1_german2_ci)))
DROP TABLE t1;
# End BUG#53562
#
# Bug#11829785 EXPLAIN EXTENDED CRASH WITH RIGHT OUTER JOIN, SUBQUERIES
#
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (0), (0);
PREPARE s FROM
'EXPLAIN EXTENDED
SELECT SUBSTRING(1, (SELECT 1 FROM t1 a1 RIGHT OUTER JOIN t1 ON 0)) AS d
FROM t1 WHERE 0 > ANY (SELECT @a FROM t1)';
# After WL#4443 we don't evaluate subqueries during prepare, so no error.
EXECUTE s;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 NULL
2 SUBQUERY a1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
Warnings:
Note 1003 /* select#1 */ select substr(1,(/* select#2 */ select 1 from `test`.`t1` left join `test`.`t1` `a1` on(0) where 1)) AS `d` from `test`.`t1` where <nop>(<in_optimizer>(0,<exists>(/* select#3 */ select (@`a`) from `test`.`t1` where 1 having 0)))
DEALLOCATE PREPARE s;
DROP TABLE t1;
#
# WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
#
# Coverage tests after code refactoring
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
CREATE TABLE t2 (a INT);
INSERT INTO t2 VALUES (3),(4),(5);
# EXPLAIN must return 3 rows:
EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 NULL
2 UNION t2 ALL NULL NULL NULL NULL 3 NULL
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using temporary
DROP TABLE t1, t2;
# End WL#4897
End of 6.0 tests.
#
# Bug #18899860: EXPLAIN .. SELECT .. FOR UPDATE TAKES LOCKS
#
CREATE TABLE t1(c1 INT PRIMARY KEY) ENGINE=INNODB;
INSERT INTO t1 VALUES (1),(2),(3);
START TRANSACTION;
EXPLAIN SELECT * FROM t1 WHERE c1 = 1 FOR UPDATE;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
START TRANSACTION;
EXPLAIN SELECT * FROM t1 WHERE c1 = 1 FOR UPDATE;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
DROP TABLE t1;
# End of test for Bug#18899860