CALL mtr.add_suppression("==[0-9]*== Warning: set address range perms: large range");
drop table if exists t1;
create table t1 (id int not null, str char(10), unique(str)) charset utf8mb4;
explain select * from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
Warnings:
Note	1003	/* select#1 */ select NULL AS `id`,NULL AS `str` from `test`.`t1`
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	str	str	41	const	1	100.00	Using index condition
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`str` AS `str` from `test`.`t1` where (`test`.`t1`.`str` is null)
explain format=json select * from t1 where str is null;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
    "table": {
      "table_name": "t1",
      "access_type": "ref",
      "possible_keys": [
        "str"
      ],
      "key": "str",
      "used_key_parts": [
        "str"
      ],
      "key_length": "41",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "index_condition": "(`test`.`t1`.`str` is null)",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.10",
        "prefix_cost": "0.35",
        "data_read_per_join": "48"
      },
      "used_columns": [
        "id",
        "str"
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`str` AS `str` from `test`.`t1` where (`test`.`t1`.`str` is null)
explain select * from t1 where str="foo";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	str	str	41	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select '3' AS `id`,'foo' AS `str` from `test`.`t1` where true
explain select * from t1 ignore key (str) where str="foo";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	25.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`str` AS `str` from `test`.`t1` IGNORE INDEX (`str`) where (`test`.`t1`.`str` = 'foo')
explain select * from t1 use key (str,str) where str="foo";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	str	str	41	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select '3' AS `id`,'foo' AS `str` from `test`.`t1` USE INDEX (`str`) USE INDEX (`str`) where true
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	/* select#1 */ select 1 AS `1`
create table t1 (a int not null);
explain select count(*) from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
Warnings:
Note	1003	/* select#1 */ select count(0) AS `count(*)` from `test`.`t1`
insert into t1 values(1);
explain select count(*) from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
Warnings:
Note	1003	/* select#1 */ select count(0) AS `count(*)` from `test`.`t1`
insert into t1 values(1);
explain select count(*) from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
Warnings:
Note	1003	/* select#1 */ select count(0) AS `count(*)` from `test`.`t1`
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE		NULL	ref	0,01	0	5	const	1	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.``.`0` AS `0` from `test`.`` where (`test`.``.`0` = 1)
drop table ;
set names latin1;
select 3 into @v1;
explain select 3 into @v1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	/* select#1 */ select 3 AS `3`
create table t1(f1 int, f2 int);
insert into t1 values (1,1);
create view v1 as select * from t1 where f1=1;
explain select * from v1 where f2=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select '1' AS `f1`,'1' AS `f2` from dual where true
explain select * from t1 where 0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	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 false
explain select * from t1 where 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select '1' AS `f1`,'1' AS `f2` from dual
explain select * from t1 having 0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	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 false
explain select * from t1 having 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select '1' AS `f1`,'1' AS `f2` from dual having true
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:
Warning	1831	Duplicate index 'b_2' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_3' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_4' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_5' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_6' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_7' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_8' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_9' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_10' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_11' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_12' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_13' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_14' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_15' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_16' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_17' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_18' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_19' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_20' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_21' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_22' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_23' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_24' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_25' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_26' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_27' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_28' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_29' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_30' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_31' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_32' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_33' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_34' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_35' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_36' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_37' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_38' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	1831	Duplicate index 'b_39' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
Warning	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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
X	X	X	NULL	X	X	X	X	X	X	NULL	no matching row in const table
X	X	X	NULL	X	X	X	X	X	X	100.00	NULL
X	X	X	NULL	X	X	X	X	X	X	33.33	Range checked for each record (index map: 0xFFFFFFFFFF)
Warnings:
X	X	X
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 SELECT 1
FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	DERIVED	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
2	DERIVED	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (hash join)
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 SELECT 1
FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	DERIVED	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
2	DERIVED	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (hash join)
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 SELECT 1  
 FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1';
execute s1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	DERIVED	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
2	DERIVED	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (hash join)
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 FORMAT=JSON SELECT 1  
 FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1';
execute s1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.95"
    },
    "table": {
      "table_name": "s1",
      "access_type": "ALL",
      "rows_examined_per_scan": 4,
      "rows_produced_per_join": 4,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "2.55",
        "eval_cost": "0.40",
        "prefix_cost": "2.95",
        "data_read_per_join": "64"
      },
      "used_columns": [
        "COUNT(DISTINCT t1.a)"
      ],
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 2,
          "cost_info": {
            "query_cost": "5.60"
          },
          "grouping_operation": {
            "using_temporary_table": true,
            "using_filesort": true,
            "cost_info": {
              "sort_cost": "4.00"
            },
            "nested_loop": [
              {
                "table": {
                  "table_name": "t1",
                  "access_type": "ALL",
                  "rows_examined_per_scan": 2,
                  "rows_produced_per_join": 2,
                  "filtered": "100.00",
                  "cost_info": {
                    "read_cost": "0.50",
                    "eval_cost": "0.20",
                    "prefix_cost": "0.70",
                    "data_read_per_join": "16"
                  },
                  "used_columns": [
                    "a"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "rows_examined_per_scan": 2,
                  "rows_produced_per_join": 4,
                  "filtered": "100.00",
                  "using_join_buffer": "hash join",
                  "cost_info": {
                    "read_cost": "0.50",
                    "eval_cost": "0.40",
                    "prefix_cost": "1.60",
                    "data_read_per_join": "32"
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
}
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 SELECT 1  
 FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1';
execute s1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	DERIVED	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
2	DERIVED	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (hash join)
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	DERIVED	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
2	DERIVED	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (hash join)
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 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 42S22: Unknown column 'd1.a' in 'where clause'
DROP TABLE t1;
#
# Bug#48295:
# explain 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 SELECT 1 FROM t1
WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Not optimized, outer query is empty
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` from `test`.`t1` where <not>((NULL <= <max>(/* select#2 */ select `test`.`t`.`f1` from `test`.`t1` join `test`.`t1` `t`)))
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	outr	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
2	DEPENDENT SUBQUERY	innr	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where
Warnings:
Note	1276	Field or reference 'test.outr.dt' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select `test`.`outr`.`dt` AS `dt` from `test`.`t1` `outr` where <in_optimizer>(`test`.`outr`.`dt`,<exists>(/* select#2 */ select `test`.`innr`.`dt` from `test`.`t2` `innr` where ((`test`.`outr`.`dt` = TIMESTAMP'0000-00-00 00:00:00') and (<cache>(`test`.`outr`.`dt`) = `test`.`innr`.`dt`))))
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	outr	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
2	DEPENDENT SUBQUERY	innr	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where
Warnings:
Note	1276	Field or reference 'test.outr.t' of SELECT #2 was resolved in SELECT #1
Note	1292	Incorrect time value: '2005-11-13 7:41:31' for column 't' at row 1
Note	1003	/* select#1 */ select `test`.`outr`.`dt` AS `dt` from `test`.`t1` `outr` where <in_optimizer>(`test`.`outr`.`dt`,<exists>(/* select#2 */ select `test`.`innr`.`dt` from `test`.`t2` `innr` where ((`test`.`outr`.`t` < TIME'07:41:31') and (<cache>(`test`.`outr`.`dt`) = `test`.`innr`.`dt`))))
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
Warnings:
Note	1292	Incorrect time value: '2005-11-13 7:41:31' for column 't' at row 1
drop tables t1, t2;
set optimizer_switch= @opt_sw_save;
#
# Bug#47669: Query showed by EXPLAIN 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 SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
2	DEPENDENT SUBQUERY	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	50.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 warning.
#
create table t1(f1 int);
create table t2(f2 int);
insert into t1 values(1);
insert into t2 values(1),(2);
explain select * from t1 where f1=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select '1' AS `f1` from dual where true
explain select * from t1 join t2 on f1=f2 where f1=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	50.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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Not optimized, outer query is empty
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` from `test`.`t1` where multiple equal((/* select#2 */ select 1 from `test`.`t1` `t` join `test`.`t2` where ((`test`.`t2`.`b` <= 1) and (0 <> `test`.`t`.`a`))), NULL)
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	c2,c2_2	c2	10	const,const	3	25.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where ((`test`.`t1`.`c5` = 1) and (`test`.`t1`.`c4` = 1) and (`test`.`t1`.`c2` = 1))
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
2	SUBQUERY	a	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
2	SUBQUERY	t1	NULL	fulltext	f1	f1	0	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` from dual where true
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
2	SUBQUERY	a	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
2	SUBQUERY	t1	NULL	fulltext	f1	f1	0	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` from dual where true
EXECUTE stmt;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
2	SUBQUERY	a	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
2	SUBQUERY	t1	NULL	fulltext	f1	f1	0	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` from dual where true
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
2	SUBQUERY	a	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
2	SUBQUERY	t1	NULL	fulltext	f1	f1	0	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` from dual where true
EXECUTE stmt;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
2	SUBQUERY	a	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
2	SUBQUERY	t1	NULL	fulltext	f1	f1	0	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` from dual where true
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
End of 5.1 tests.
#
# Bug#46860: 
# Crash/segfault using EXPLAIN on query using UNION in subquery.
#
drop table if exists t1;
create table `t1` (`a` int);
explain 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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
5	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
6	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
7	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
8	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
9	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
10	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
11	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
12	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
13	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
14	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
15	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
16	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
17	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
18	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
19	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
20	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
21	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
22	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
23	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
24	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
25	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` union all /* select#2 */ select 1 AS `1` union all /* select#3 */ select 1 AS `1` union all /* select#4 */ select 1 AS `1` union all /* select#5 */ select 1 AS `1` union all /* select#6 */ select 1 AS `1` union all /* select#7 */ select 1 AS `1` union all /* select#8 */ select 1 AS `1` union all /* select#9 */ select 1 AS `1` union all /* select#10 */ select 1 AS `1` union all /* select#11 */ select 1 AS `1` union all /* select#12 */ select 1 AS `1` union all /* select#13 */ select 1 AS `1` union all /* select#14 */ select 1 AS `1` union all /* select#15 */ select 1 AS `1` union all /* select#16 */ select 1 AS `1` union all /* select#17 */ select 1 AS `1` union all /* select#18 */ select 1 AS `1` union all /* select#19 */ select 1 AS `1` union all /* select#20 */ select 1 AS `1` union all /* select#21 */ select 1 AS `1` union all /* select#22 */ select 1 AS `1` union all /* select#23 */ select 1 AS `1` union all /* select#24 */ select 1 AS `1` union all /* select#25 */ select 1 AS `1`
# 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) charset latin1;
INSERT INTO t1 VALUES ('1'),('2'),('3'),('4'),('5');

# Normally, lookup access on primary key is done
EXPLAIN SELECT * FROM t1 WHERE url='1';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	PRIMARY	PRIMARY	1	const	1	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select '1' AS `url` from `test`.`t1` where true

# 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 SELECT * FROM t1 WHERE url=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	PRIMARY	PRIMARY	1	NULL	5	20.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 SELECT * FROM t1 WHERE url='1' collate latin1_german2_ci;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	PRIMARY	PRIMARY	1	NULL	5	20.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 SELECT * FROM t1 WHERE url>'3';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	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 SELECT * FROM t1 WHERE url>3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	PRIMARY	PRIMARY	1	NULL	5	33.33	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 SELECT * FROM t1 WHERE url>'3' collate latin1_german2_ci;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	PRIMARY	PRIMARY	1	NULL	5	33.33	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 CRASH WITH RIGHT OUTER JOIN, SUBQUERIES
#
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (0), (0);
PREPARE s FROM 
'EXPLAIN
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
3	UNCACHEABLE SUBQUERY	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
2	SUBQUERY	a1	NULL	const	NULL	NULL	NULL	NULL	1	100.00	Impossible ON condition
2	SUBQUERY	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select substr(1,(/* select#2 */ select 1 from `test`.`t1` left join `test`.`t1` `a1` on(false) where true)) AS `d` from `test`.`t1` where <nop>(<in_optimizer>(0,<exists>(/* select#3 */ select (@`a`) from `test`.`t1` where true having <is_not_null_test>(<cache>((@`a`))))))
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
2	UNION	t2	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
3	UNION RESULT	<union1,2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary
Warnings:
Warning	1287	SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
Note	1003	/* select#1 */ select sql_calc_found_rows `test`.`t1`.`a` AS `a` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` limit 2,2
EXPLAIN REPLACE INTO t1 VALUES (10);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	REPLACE	t1	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
Warnings:
Note	1003	replace into `test`.`t1` values (10)
EXPLAIN REPLACE INTO t1 SELECT * FROM t2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	REPLACE	t1	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
Warnings:
Note	1003	replace into `test`.`t1` /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2`
DROP TABLE t1, t2;
# End WL#4897
#
# Bug#21139522 PREPARED STATEMENT EXPLAIN DELETE .. WITH STRICT
#              MODE VIOLATION FLATLINES
#
CREATE TABLE t1(a INT);
PREPARE s FROM "EXPLAIN DELETE FROM t1 WHERE a || 'a' LIMIT 1";
ERROR 22007: Truncated incorrect DOUBLE value: 'a'
DROP TABLE t1;
# End of test Bug#21139522
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select '1' AS `c1` from `test`.`t1` where true
START TRANSACTION;
EXPLAIN SELECT * FROM t1 WHERE c1 = 1 FOR UPDATE;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select '1' AS `c1` from `test`.`t1` where true
DROP TABLE t1;
# End of test for Bug#18899860
# 
# Bug #23209903: ASSERTION: SELECT_LEX->LEAF_TABLE_COUNT == 0 ||
# THD->LEX->IS_QUERY_TABLES_LOCKED
#
CREATE TABLE t1 (a INT, b INT) ENGINE=INNODB PARTITION BY KEY (b) PARTITIONS 2;
CREATE TABLE t2 (c INT) ENGINE=INNODB;

# Test single-table update with subquery and empty outer query block
UPDATE t1 SET a=(SELECT c from t2) WHERE 0;
EXPLAIN UPDATE t1 SET a=(SELECT c from t2) WHERE 0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching rows after partition pruning
Warnings:
Note	1003	update `test`.`t1` set `test`.`t1`.`a` = (/* select#2 */ select `test`.`t2`.`c` from `test`.`t2`) where false

# Test delete from single-table with subquery and empty outer query block
DELETE FROM t1 WHERE 0 AND a IN (SELECT c from t2);
EXPLAIN DELETE FROM t1 WHERE 0 AND a IN (SELECT c from t2);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching rows after partition pruning
Warnings:
Note	1003	delete from `test`.`t1` where false
DROP TABLE t1, t2;
# End of test for Bug#23209903
#
# Bug#29115386: CONTRIBUTION: IMPOSSIBLE WHERE FOR A!=A, A<A, A>A
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(NULL);
# Expect "Impossible WHERE" when "a<>a, a<a, a>a" is
# present in WHERE clause. In select clause the same
# conditions will evaulate to false only when "a"
# has non-null values. For NULL value, it evaluates
# to NULL (UNKNOWN RESULT).
EXPLAIN SELECT * FROM t1 WHERE a<>a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where false
SELECT COUNT(*) FROM t1 WHERE a<>a;
COUNT(*)
0
SELECT a<>a FROM t1;
a<>a
0
NULL
EXPLAIN SELECT * FROM t1 WHERE a>a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where false
SELECT COUNT(*) FROM t1 WHERE a>a;
COUNT(*)
0
SELECT a>a FROM t1;
a>a
0
NULL
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where false
SELECT COUNT(*) FROM t1 WHERE a<a;
COUNT(*)
0
SELECT a<a FROM t1;
a<a
0
NULL
# a<=>a evaluates to true when the result is "UNKNOWN"
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<=>a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where true
SELECT COUNT(*) FROM t1 WHERE a<=>a;
COUNT(*)
2
SELECT a<=>a FROM t1;
a<=>a
1
1
# No optimization done for "a<=a, a>=a, a=a" as
# the result does not evaulate to "FALSE"
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<=a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`a` <= `test`.`t1`.`a`)
SELECT COUNT(*) FROM t1 WHERE a<=a;
COUNT(*)
1
SELECT a<=a FROM t1;
a<=a
1
NULL
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a>=a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`a` >= `test`.`t1`.`a`)
SELECT COUNT(*) FROM t1 WHERE a>=a;
COUNT(*)
1
SELECT a>=a FROM t1;
a>=a
1
NULL
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a=a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t1`.`a`)
SELECT COUNT(*) FROM t1 WHERE a=a;
COUNT(*)
1
SELECT a=a FROM t1;
a=a
1
NULL
# Do not expect "Impossible WHERE" as
# we skip conditions with truth value tests
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<>a IS NOT FALSE;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`a` <> `test`.`t1`.`a`) is not false)
SELECT COUNT(*) FROM t1 WHERE a<>a IS NOT FALSE;
COUNT(*)
1
# Expect "Impossible WHERE" as the value of
# "a" is non-NULL since a=1 is true
EXPLAIN SELECT COUNT(*) FROM t1 WHERE (a=1 and a<>a IS NOT FALSE);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where false
SELECT COUNT(*) FROM t1 WHERE (a=1 AND a<>a IS NOT FALSE);
COUNT(*)
0
# No "Impossible WHERE" as it is an OR condition
EXPLAIN SELECT * FROM t1 WHERE (a=1 OR a<>a);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 1)
SELECT * FROM t1 WHERE (a= 1 OR a<>a);
a
1
# "Impossible WHERE" as it is an AND condition
EXPLAIN SELECT * FROM t1 WHERE (a=1 AND a<>a);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where false
SELECT * FROM t1 WHERE (a=1 AND a<>a);
a
UPDATE t1 SET a = 2 WHERE a IS NULL;
ALTER TABLE t1 MODIFY a INT NOT NULL;
# Expect "Impossible WHERE" for never true values like
# a<>a, a<a, a>a and always true for a<=>a, a<=a, a>=a
# and a=a
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a!=a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where false
SELECT COUNT(*) FROM t1 WHERE a<>a;
COUNT(*)
0
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a>a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where false
SELECT COUNT(*) FROM t1 WHERE a>a;
COUNT(*)
0
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where false
SELECT COUNT(*) FROM t1 WHERE a<a;
COUNT(*)
0
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<=>a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where true
SELECT COUNT(*) FROM t1 WHERE a<=>a;
COUNT(*)
2
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<=a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where true
SELECT COUNT(*) FROM t1 WHERE a<=a;
COUNT(*)
2
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a>=a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where true
SELECT COUNT(*) FROM t1 WHERE a>=a;
COUNT(*)
2
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a=a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where true
SELECT COUNT(*) FROM t1 WHERE a=a;
COUNT(*)
2
# Do not expect "Impossible WHERE" as
# we skip conditions with truth value tests
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<>a IS NOT FALSE;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`a` <> `test`.`t1`.`a`) is not false)
SELECT COUNT(*) FROM t1 WHERE a<>a IS NOT FALSE;
COUNT(*)
0
# Expect "Impossible WHERE" as the value of
# "a" is non-NULL since a=1 is true
EXPLAIN SELECT COUNT(*) FROM t1 WHERE (a =1 and a<>a IS NOT FALSE);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where false
SELECT COUNT(*) FROM t1 WHERE (a=1 AND a<>a IS NOT FALSE);
COUNT(*)
0
# No "Impossible WHERE" as it is an OR condition
EXPLAIN SELECT * FROM t1 WHERE (a=1 OR a<>a);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 1)
SELECT * FROM t1 WHERE (a=1 OR a<>a);
a
1
# "Impossible WHERE" as it is an AND condition
EXPLAIN SELECT * FROM t1 WHERE (a=1 AND a<>a);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where false
SELECT * FROM t1 WHERE (a=1 AND a<>a);
a
DROP TABLE t1;
#
# WL#11350
#
CREATE TABLE r(a INT);
INSERT INTO r VALUES (1), (2), (-1), (-2);
CREATE TABLE s(a INT);
INSERT INTO s VALUES (1), (10), (20), (-10), (-20);
CREATE TABLE t(a INT);
INSERT INTO t VALUES (10), (100), (200), (-100), (-200);
ANALYZE TABLE r,s,t;
Table	Op	Msg_type	Msg_text
test.r	analyze	status	OK
test.s	analyze	status	OK
test.t	analyze	status	OK
# Test nesting structures
EXPLAIN ( ( SELECT * FROM r UNION
(SELECT * FROM s ORDER BY a LIMIT 1)) UNION
( SELECT * FROM s UNION SELECT 2 LIMIT 3)
ORDER BY 1 LIMIT 3)
ORDER BY 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	r	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
3	PRIMARY	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
5	UNION RESULT	<union3,4>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary
6	UNION RESULT	<union1,2,5>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary; Using filesort
7	RESULT	<ordered6>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary; Using filesort
Warnings:
Note	1003	(/* select#1 */ select `test`.`r`.`a` AS `a` from `test`.`r` union (/* select#2 */ select `test`.`s`.`a` AS `a` from `test`.`s` order by `test`.`s`.`a` limit 1) union (/* select#3 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#4 */ select 2 AS `2` limit 3) order by `a` limit 3) order by `a`
# For comparison
EXPLAIN format=tree ( ( SELECT * FROM r UNION
(SELECT * FROM s ORDER BY a LIMIT 1)) UNION
( SELECT * FROM s UNION SELECT 2 LIMIT 3)
ORDER BY 1 LIMIT 3)
ORDER BY 1;
EXPLAIN
-> Sort: a  (rows=3)
    -> Table scan on <result temporary>  (rows=3)
        -> Temporary table  (rows=3)
            -> Limit: 3 row(s)  (rows=3)
                -> Sort: a, limit input to 3 row(s) per chunk  (rows=3)
                    -> Table scan on <union temporary>  (rows=8)
                        -> Union materialize with deduplication  (rows=8)
                            -> Table scan on r  (rows=4)
                            -> Limit: 1 row(s)  (rows=1)
                                -> Sort: s.a, limit input to 1 row(s) per chunk  (rows=5)
                                    -> Table scan on s  (rows=5)
                            -> Limit: 3 row(s)  (rows=3)
                                -> Table scan on <union temporary>  (rows=6)
                                    -> Union materialize with deduplication  (rows=6)
                                        -> Limit table size: 3 unique row(s)
                                            -> Table scan on s  (rows=5)
                                        -> Limit table size: 3 unique row(s)
                                            -> Rows fetched before execution  (rows=1)

# Test no result if no materializaton
EXPLAIN SELECT 1 UNION ALL SELECT 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` union all /* select#2 */ select 2 AS `2`
# Used to not show sorting before WL#11350
EXPLAIN (SELECT a FROM r LIMIT 2) ORDER BY 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	r	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	RESULT	<ordered1>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary; Using filesort
Warnings:
Note	1003	(/* select#1 */ select `test`.`r`.`a` AS `a` from `test`.`r` limit 2) order by `a`
EXPLAIN ((SELECT a FROM t ORDER BY 1 LIMIT 3) ORDER BY -a LIMIT 2) ORDER BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
2	RESULT	<ordered/limited1>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary; Using filesort
3	RESULT	<ordered2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary; Using filesort
Warnings:
Note	1003	((/* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t` order by `test`.`t`.`a` limit 3) order by -(`a`) limit 2) order by `a`
# Test big one
# This query has max nesting and should work
EXPLAIN (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((SELECT * FROM r
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s)
UNION SELECT * FROM s);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	r	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
3	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
4	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
5	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
6	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
7	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
8	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
9	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
10	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
11	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
12	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
13	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
14	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
15	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
16	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
17	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
18	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
19	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
20	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
21	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
22	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
23	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
24	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
25	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
26	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
27	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
28	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
29	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
30	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
31	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
32	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
33	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
34	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
35	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
36	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
37	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
38	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
39	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
40	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
41	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
42	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
43	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
44	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
45	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
46	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
47	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
48	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
49	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
50	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
51	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
52	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
53	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
54	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
55	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
56	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
57	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
58	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
59	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
60	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
61	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
62	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
63	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
64	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
65	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
66	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
67	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
68	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
69	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
70	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
71	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
72	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
73	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
74	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
75	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
76	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
77	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
78	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
79	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
80	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
81	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
82	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
83	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
84	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
85	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
86	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
87	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
88	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
89	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
90	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
91	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
92	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
93	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
94	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
95	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
96	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
97	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
98	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
99	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
100	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
101	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
102	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
103	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
104	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
105	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
106	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
107	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
108	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
109	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
110	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
111	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
112	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
113	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
114	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
115	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
116	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
117	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
118	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
119	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
120	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
121	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
122	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
123	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
124	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
125	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
126	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
127	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
128	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
129	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
130	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
131	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
132	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
133	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
134	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
135	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
136	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
137	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
138	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
139	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
140	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
141	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
142	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
143	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
144	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
145	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
146	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
147	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
148	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
149	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
150	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
151	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
152	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
153	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
154	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
155	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
156	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
157	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
158	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
159	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
160	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
161	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
162	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
163	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
164	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
165	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
166	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
167	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
168	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
169	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
170	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
171	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
172	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
173	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
174	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
175	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
176	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
177	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
178	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
179	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
180	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
181	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
182	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
183	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
184	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
185	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
186	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
187	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
188	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
189	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
190	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
191	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
192	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
193	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
194	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
195	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
196	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
197	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
198	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
199	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
200	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
201	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
202	UNION	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
203	UNION RESULT	<union1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...,202>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`r`.`a` AS `a` from `test`.`r` union /* select#2 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#3 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#4 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#5 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#6 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#7 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#8 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#9 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#10 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#11 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#12 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#13 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#14 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#15 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#16 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#17 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#18 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#19 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#20 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#21 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#22 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#23 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#24 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#25 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#26 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#27 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#28 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#29 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#30 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#31 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#32 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#33 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#34 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#35 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#36 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#37 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#38 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#39 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#40 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#41 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#42 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#43 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#44 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#45 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#46 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#47 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#48 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#49 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#50 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#51 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#52 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#53 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#54 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#55 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#56 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#57 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#58 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#59 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#60 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#61 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#62 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#63 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#64 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#65 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#66 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#67 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#68 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#69 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#70 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#71 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#72 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#73 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#74 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#75 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#76 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#77 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#78 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#79 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#80 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#81 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#82 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#83 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#84 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#85 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#86 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#87 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#88 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#89 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#90 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#91 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#92 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#93 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#94 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#95 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#96 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#97 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#98 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#99 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#100 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#101 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#102 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#103 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#104 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#105 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#106 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#107 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#108 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#109 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#110 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#111 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#112 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#113 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#114 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#115 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#116 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#117 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#118 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#119 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#120 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#121 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#122 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#123 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#124 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#125 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#126 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#127 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#128 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#129 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#130 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#131 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#132 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#133 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#134 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#135 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#136 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#137 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#138 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#139 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#140 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#141 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#142 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#143 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#144 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#145 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#146 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#147 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#148 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#149 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#150 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#151 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#152 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#153 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#154 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#155 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#156 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#157 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#158 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#159 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#160 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#161 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#162 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#163 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#164 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#165 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#166 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#167 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#168 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#169 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#170 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#171 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#172 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#173 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#174 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#175 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#176 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#177 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#178 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#179 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#180 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#181 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#182 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#183 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#184 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#185 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#186 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#187 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#188 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#189 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#190 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#191 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#192 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#193 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#194 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#195 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#196 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#197 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#198 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#199 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#200 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#201 */ select `test`.`s`.`a` AS `a` from `test`.`s` union /* select#202 */ select `test`.`s`.`a` AS `a` from `test`.`s`
#
# INTERSECT operator
#
EXPLAIN SELECT * FROM r INTERSECT SELECT * FROM s;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	r	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	INTERSECT	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
3	INTERSECT RESULT	<intersect1,2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`r`.`a` AS `a` from `test`.`r` intersect /* select#2 */ select `test`.`s`.`a` AS `a` from `test`.`s`
# For comparison
EXPLAIN format=tree SELECT * FROM r INTERSECT SELECT * FROM s;
EXPLAIN
-> Table scan on <intersect temporary>  (rows=4)
    -> Intersect materialize with deduplication  (rows=4)
        -> Table scan on r  (rows=4)
        -> Table scan on s  (rows=5)

EXPLAIN SELECT * FROM r INTERSECT ALL SELECT * FROM s;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	r	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	INTERSECT	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
3	INTERSECT RESULT	<intersect1,2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`r`.`a` AS `a` from `test`.`r` intersect all /* select#2 */ select `test`.`s`.`a` AS `a` from `test`.`s`
# For comparison
EXPLAIN format=tree SELECT * FROM r INTERSECT ALL SELECT * FROM s;
EXPLAIN
-> Table scan on <intersect temporary>  (rows=4)
    -> Intersect all materialize  (rows=4)
        -> Table scan on r  (rows=4)
        -> Table scan on s  (rows=5)

#
# EXCEPT operator
#
EXPLAIN SELECT * FROM r EXCEPT SELECT * FROM s;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	r	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	EXCEPT	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
3	EXCEPT RESULT	<except1,2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`r`.`a` AS `a` from `test`.`r` except /* select#2 */ select `test`.`s`.`a` AS `a` from `test`.`s`
# For comparison
EXPLAIN format=tree SELECT * FROM r EXCEPT SELECT * FROM s;
EXPLAIN
-> Table scan on <except temporary>  (rows=4)
    -> Except materialize with deduplication  (rows=4)
        -> Table scan on r  (rows=4)
        -> Table scan on s  (rows=5)

EXPLAIN SELECT * FROM r EXCEPT ALL SELECT * FROM s;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	r	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	EXCEPT	s	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
3	EXCEPT RESULT	<except1,2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`r`.`a` AS `a` from `test`.`r` except all /* select#2 */ select `test`.`s`.`a` AS `a` from `test`.`s`
# For comparison
EXPLAIN format=tree SELECT * FROM r EXCEPT ALL SELECT * FROM s;
EXPLAIN
-> Table scan on <except temporary>  (rows=4)
    -> Except all materialize  (rows=4)
        -> Table scan on r  (rows=4)
        -> Table scan on s  (rows=5)

DROP TABLE r, s, t;