4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/r/explain.result

770 lines
39 KiB
Plaintext
Raw Permalink Blame History

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 <20><><EFBFBD> (<28><><EFBFBD>0 int, <20><><EFBFBD>1 int, key <20><><EFBFBD>0 (<28><><EFBFBD>0), key <20><><EFBFBD>01 (<28><><EFBFBD>0,<2C><><EFBFBD>1));
insert into <20><><EFBFBD> (<28><><EFBFBD>0) values (1);
insert into <20><><EFBFBD> (<28><><EFBFBD>0) values (2);
explain select <20><><EFBFBD>0 from <20><><EFBFBD> where <20><><EFBFBD>0=1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE <09><><EFBFBD> NULL ref <09><><EFBFBD>0,<2C><><EFBFBD>01 <09><><EFBFBD>0 5 const 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`<60><><EFBFBD>`.`<60><><EFBFBD>0` AS `<60><><EFBFBD>0` from `test`.`<60><><EFBFBD>` where (`test`.`<60><><EFBFBD>`.`<60><><EFBFBD>0` = 1)
drop table <20><><EFBFBD>;
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
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 (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 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 (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 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 (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 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": "Block Nested Loop",
"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 (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 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 (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 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 1 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 1 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 t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
2 SUBQUERY a1 NULL 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(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
NULL 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
Reference in New Issue View Git Blame Copy Permalink