mysql-testsuite-5.6 5.6.16-1~exp1 / usr / lib / mysql-testsuite / r / explain.result

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.