mirror of
https://github.com/MariaDB/server.git
synced 2025-10-30 18:36:12 +01:00
1429 lines
36 KiB
Text
1429 lines
36 KiB
Text
drop table if exists t0, t1, t2, t3, t4;
|
||
drop view if exists v1;
|
||
SET @old_debug= @@session.debug_dbug;
|
||
set debug_sync='RESET';
|
||
create table t0 (a int);
|
||
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
||
create table t1 (a int);
|
||
insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C;
|
||
alter table t1 add b int, add c int, add filler char(32);
|
||
update t1 set b=a, c=a, filler='fooo';
|
||
alter table t1 add key(a), add key(b);
|
||
show explain format=JSON for;
|
||
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
|
||
explain format=JSON for connection;
|
||
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
|
||
show explain FORMAT=json for 123456789;
|
||
ERROR HY000: Unknown thread id: 123456789
|
||
explain FORMAT=json for connection 123456789;
|
||
ERROR HY000: Unknown thread id: 123456789
|
||
show explain FORMAT=json for (select max(a) from t0);
|
||
ERROR HY000: You may only use constant expressions in this statement
|
||
explain FORMAT=json for connection (select max(a) from t0);
|
||
ERROR HY000: You may only use constant expressions in this statement
|
||
connect con1, localhost, root,,;
|
||
connection con1;
|
||
SET @old_debug= @@session.debug_dbug;
|
||
connection default;
|
||
show explain format=JSON for $thr2;
|
||
ERROR HY000: Target is not executing an operation with a query plan
|
||
explain format=json for connection $thr2;
|
||
ERROR HY000: Target is not executing an operation with a query plan
|
||
show explain format=json for $thr1;
|
||
ERROR HY000: Target is not executing an operation with a query plan
|
||
explain FORMAT=JSON for connection $thr1;
|
||
ERROR HY000: Target is not executing an operation with a query plan
|
||
show explain FORMAT=HTML for $thr1;
|
||
ERROR HY000: Unknown EXPLAIN/ANALYZE format name: 'HTML'
|
||
explain FORMAT=XML for connection $thr1;
|
||
ERROR HY000: Unknown EXPLAIN/ANALYZE format name: 'XML'
|
||
connection con1;
|
||
set @show_explain_probe_select_id=1;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
select count(*) from t1 where a < 100000;
|
||
connection default;
|
||
show explain FORMAT=JSON for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t1",
|
||
"access_type": "range",
|
||
"possible_keys": ["a"],
|
||
"key": "a",
|
||
"key_length": "5",
|
||
"used_key_parts": ["a"],
|
||
"loops": 1,
|
||
"rows": 999,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t1.a < 100000",
|
||
"using_index": true
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select count(*) from t1 where a < 100000
|
||
connection con1;
|
||
count(*)
|
||
1000
|
||
select max(c) from t1 where a < 10;
|
||
connection default;
|
||
explain FORMAT=JSON for connection $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t1",
|
||
"access_type": "range",
|
||
"possible_keys": ["a"],
|
||
"key": "a",
|
||
"key_length": "5",
|
||
"used_key_parts": ["a"],
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"index_condition": "t1.a < 10"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select max(c) from t1 where a < 10
|
||
connection con1;
|
||
max(c)
|
||
9
|
||
select max(c) from t1 where a < 10;
|
||
connection default;
|
||
explain format=JSON for connection $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t1",
|
||
"access_type": "range",
|
||
"possible_keys": ["a"],
|
||
"key": "a",
|
||
"key_length": "5",
|
||
"used_key_parts": ["a"],
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"index_condition": "t1.a < 10"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select max(c) from t1 where a < 10
|
||
connection con1;
|
||
max(c)
|
||
9
|
||
# We can catch EXPLAIN, too.
|
||
set @show_expl_tmp= @@optimizer_switch;
|
||
set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on';
|
||
explain select max(c) from t1 where a < 10;
|
||
connection default;
|
||
show explain format=json for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t1",
|
||
"access_type": "range",
|
||
"possible_keys": ["a"],
|
||
"key": "a",
|
||
"key_length": "5",
|
||
"used_key_parts": ["a"],
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"index_condition": "t1.a < 10",
|
||
"mrr_type": "Rowid-ordered scan"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 explain select max(c) from t1 where a < 10
|
||
connection con1;
|
||
id select_type table type possible_keys key key_len ref rows Extra
|
||
1 SIMPLE t1 range a a 5 NULL 10 Using index condition; Rowid-ordered scan
|
||
# Same for EXPLAIN FOR CONNECTION
|
||
explain select max(c) from t1 where a < 10;
|
||
connection default;
|
||
explain format=JSON for connection $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t1",
|
||
"access_type": "range",
|
||
"possible_keys": ["a"],
|
||
"key": "a",
|
||
"key_length": "5",
|
||
"used_key_parts": ["a"],
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"index_condition": "t1.a < 10",
|
||
"mrr_type": "Rowid-ordered scan"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 explain select max(c) from t1 where a < 10
|
||
connection con1;
|
||
id select_type table type possible_keys key key_len ref rows Extra
|
||
1 SIMPLE t1 range a a 5 NULL 10 Using index condition; Rowid-ordered scan
|
||
set optimizer_switch= @show_expl_tmp;
|
||
SET debug_dbug=@old_debug;
|
||
# UNION, first branch
|
||
set @show_explain_probe_select_id=1;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
explain select a from t0 A union select a+1 from t0 B;
|
||
connection default;
|
||
show explain format = JSON for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"union_result": {
|
||
"table_name": "<union1,2>",
|
||
"access_type": "ALL",
|
||
"query_specifications": [
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "A",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100
|
||
}
|
||
}
|
||
]
|
||
}
|
||
},
|
||
{
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"operation": "UNION",
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "B",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 explain select a from t0 A union select a+1 from t0 B
|
||
connection con1;
|
||
id select_type table type possible_keys key key_len ref rows Extra
|
||
1 PRIMARY A ALL NULL NULL NULL NULL 10
|
||
2 UNION B ALL NULL NULL NULL NULL 10
|
||
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
|
||
SET debug_dbug=@old_debug;
|
||
# UNION, second branch
|
||
set @show_explain_probe_select_id=2;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
explain select a from t0 A union select a+1 from t0 B;
|
||
connection default;
|
||
show explain format=JSON for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"union_result": {
|
||
"table_name": "<union1,2>",
|
||
"access_type": "ALL",
|
||
"query_specifications": [
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "A",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100
|
||
}
|
||
}
|
||
]
|
||
}
|
||
},
|
||
{
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"operation": "UNION",
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "B",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 explain select a from t0 A union select a+1 from t0 B
|
||
connection con1;
|
||
id select_type table type possible_keys key key_len ref rows Extra
|
||
1 PRIMARY A ALL NULL NULL NULL NULL 10
|
||
2 UNION B ALL NULL NULL NULL NULL 10
|
||
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
|
||
SET debug_dbug=@old_debug;
|
||
# Uncorrelated subquery, select
|
||
set @show_explain_probe_select_id=1;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
select a, (select max(a) from t0 B) from t0 A where a<1;
|
||
connection default;
|
||
show explain for $thr2;
|
||
id select_type table type possible_keys key key_len ref rows Extra
|
||
1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
|
||
2 SUBQUERY B ALL NULL NULL NULL NULL 10
|
||
Warnings:
|
||
Note 1003 select a, (select max(a) from t0 B) from t0 A where a<1
|
||
connection con1;
|
||
a (select max(a) from t0 B)
|
||
0 9
|
||
SET debug_dbug=@old_debug;
|
||
# Uncorrelated subquery, explain
|
||
set @show_explain_probe_select_id=1;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
explain select a, (select max(a) from t0 B) from t0 A where a<1;
|
||
connection default;
|
||
show explain for $thr2;
|
||
id select_type table type possible_keys key key_len ref rows Extra
|
||
1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
|
||
2 SUBQUERY B ALL NULL NULL NULL NULL 10
|
||
Warnings:
|
||
Note 1003 explain select a, (select max(a) from t0 B) from t0 A where a<1
|
||
connection con1;
|
||
id select_type table type possible_keys key key_len ref rows Extra
|
||
1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
|
||
2 SUBQUERY B ALL NULL NULL NULL NULL 10
|
||
SET debug_dbug=@old_debug;
|
||
# correlated subquery, select
|
||
set @show_explain_probe_select_id=1;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
|
||
connection default;
|
||
show explain for $thr2;
|
||
id select_type table type possible_keys key key_len ref rows Extra
|
||
1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where
|
||
2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where
|
||
Warnings:
|
||
Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
|
||
connection con1;
|
||
a (select max(a) from t0 b where b.a+a.a<10)
|
||
0 9
|
||
SET debug_dbug=@old_debug;
|
||
# correlated subquery, explain
|
||
set @show_explain_probe_select_id=1;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
explain select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
|
||
connection default;
|
||
show explain for $thr2;
|
||
id select_type table type possible_keys key key_len ref rows Extra
|
||
1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where
|
||
2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where
|
||
Warnings:
|
||
Note 1003 explain select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
|
||
connection con1;
|
||
id select_type table type possible_keys key key_len ref rows Extra
|
||
1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where
|
||
2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where
|
||
SET debug_dbug=@old_debug;
|
||
# correlated subquery, select, while inside the subquery
|
||
set @show_explain_probe_select_id=2;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
|
||
connection default;
|
||
show explain format=JSON for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "a",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "a.a < 1"
|
||
}
|
||
}
|
||
],
|
||
"subqueries": [
|
||
{
|
||
"expression_cache": {
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "b",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "b.a + a.a < 10"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
|
||
connection con1;
|
||
a (select max(a) from t0 b where b.a+a.a<10)
|
||
0 9
|
||
SET debug_dbug=@old_debug;
|
||
# correlated subquery, explain, while inside the subquery
|
||
set @show_explain_probe_select_id=2;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
|
||
connection default;
|
||
show explain format=JSON for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "a",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "a.a < 1"
|
||
}
|
||
}
|
||
],
|
||
"subqueries": [
|
||
{
|
||
"expression_cache": {
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "b",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "b.a + a.a < 10"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
|
||
connection con1;
|
||
a (select max(a) from t0 b where b.a+a.a<10)
|
||
0 9
|
||
SET debug_dbug=@old_debug;
|
||
# correlated subquery, explain, while inside the subquery
|
||
set @show_explain_probe_select_id=1;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_end';
|
||
select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
|
||
connection default;
|
||
show explain format=JSON for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "a",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "a.a < 1"
|
||
}
|
||
}
|
||
],
|
||
"subqueries": [
|
||
{
|
||
"expression_cache": {
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "b",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "b.a + a.a < 10"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
|
||
connection con1;
|
||
a (select max(a) from t0 b where b.a+a.a<10)
|
||
0 9
|
||
SET debug_dbug=@old_debug;
|
||
# Try to do SHOW EXPLAIN for a query that runs a SET command:
|
||
# I've found experimentally that select_id==2 here...
|
||
#
|
||
create table t2 (a int);
|
||
insert into t2 values (1),(2);
|
||
set @show_explain_probe_select_id=3;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
set @foo= (select max(a) from t2
|
||
where a + (select max(a) from t0 where t0.a>t2.a) < 10000);
|
||
connection default;
|
||
show explain format=JSON for $thr2;
|
||
ERROR HY000: Target is not executing an operation with a query plan
|
||
kill query $thr2;
|
||
connection con1;
|
||
ERROR 70100: Query execution was interrupted
|
||
SET debug_dbug=@old_debug;
|
||
drop table t2;
|
||
#
|
||
# Attempt SHOW EXPLAIN for an UPDATE
|
||
#
|
||
create table t2 as select a as a, a as dummy from t0 limit 2;
|
||
set @show_explain_probe_select_id=2;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
|
||
connection default;
|
||
show explain format=JSON for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"table": {
|
||
"update": 1,
|
||
"table_name": "t2",
|
||
"access_type": "ALL",
|
||
"rows": 2,
|
||
"attached_condition": "(subquery#2) > 3"
|
||
},
|
||
"subqueries": [
|
||
{
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t2.a + t0.a < 3"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3
|
||
show explain format=JSON for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"table": {
|
||
"update": 1,
|
||
"table_name": "t2",
|
||
"access_type": "ALL",
|
||
"rows": 2,
|
||
"attached_condition": "(subquery#2) > 3"
|
||
},
|
||
"subqueries": [
|
||
{
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t2.a + t0.a < 3"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3
|
||
connection con1;
|
||
#
|
||
# EXPLAIN FOR CONNECTION for an UPDATE
|
||
#
|
||
set @show_explain_probe_select_id=2;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
|
||
connection default;
|
||
explain format=JSON for connection $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"table": {
|
||
"update": 1,
|
||
"table_name": "t2",
|
||
"access_type": "ALL",
|
||
"rows": 2,
|
||
"attached_condition": "(subquery#2) > 3"
|
||
},
|
||
"subqueries": [
|
||
{
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t2.a + t0.a < 3"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3
|
||
explain format=JSON for connection $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"table": {
|
||
"update": 1,
|
||
"table_name": "t2",
|
||
"access_type": "ALL",
|
||
"rows": 2,
|
||
"attached_condition": "(subquery#2) > 3"
|
||
},
|
||
"subqueries": [
|
||
{
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t2.a + t0.a < 3"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3
|
||
connection con1;
|
||
drop table t2;
|
||
SET debug_dbug=@old_debug;
|
||
#
|
||
# Attempt SHOW EXPLAIN for a DELETE
|
||
#
|
||
create table t2 as select a as a, a as dummy from t0 limit 2;
|
||
set @show_explain_probe_select_id=2;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
|
||
connection default;
|
||
show explain format=JSON for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"table": {
|
||
"delete": 1,
|
||
"table_name": "t2",
|
||
"access_type": "ALL",
|
||
"rows": 2,
|
||
"attached_condition": "(subquery#2) > 3"
|
||
},
|
||
"subqueries": [
|
||
{
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t2.a + t0.a < 3"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
|
||
show explain format=JSON for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"table": {
|
||
"delete": 1,
|
||
"table_name": "t2",
|
||
"access_type": "ALL",
|
||
"rows": 2,
|
||
"attached_condition": "(subquery#2) > 3"
|
||
},
|
||
"subqueries": [
|
||
{
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t2.a + t0.a < 3"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
|
||
connection con1;
|
||
drop table t2;
|
||
SET debug_dbug=@old_debug;
|
||
#
|
||
# Attempt EXPLAIN FOR CONNECTION for a DELETE
|
||
#
|
||
create table t2 as select a as a, a as dummy from t0 limit 2;
|
||
set @show_explain_probe_select_id=2;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
|
||
connection default;
|
||
explain format=JSON for connection $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"table": {
|
||
"delete": 1,
|
||
"table_name": "t2",
|
||
"access_type": "ALL",
|
||
"rows": 2,
|
||
"attached_condition": "(subquery#2) > 3"
|
||
},
|
||
"subqueries": [
|
||
{
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t2.a + t0.a < 3"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
|
||
explain format=JSON for connection $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"table": {
|
||
"delete": 1,
|
||
"table_name": "t2",
|
||
"access_type": "ALL",
|
||
"rows": 2,
|
||
"attached_condition": "(subquery#2) > 3"
|
||
},
|
||
"subqueries": [
|
||
{
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t2.a + t0.a < 3"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
|
||
connection con1;
|
||
drop table t2;
|
||
SET debug_dbug=@old_debug;
|
||
#
|
||
# Multiple SHOW EXPLAIN calls for one select
|
||
#
|
||
create table t2 as select a as a, a as dummy from t0 limit 3;
|
||
set @show_explain_probe_select_id=2;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2;
|
||
connection default;
|
||
show explain format=json for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t2",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 3,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100
|
||
}
|
||
}
|
||
],
|
||
"subqueries": [
|
||
{
|
||
"expression_cache": {
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t2.a + t0.a < 3"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
|
||
show explain format=json for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t2",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 3,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100
|
||
}
|
||
}
|
||
],
|
||
"subqueries": [
|
||
{
|
||
"expression_cache": {
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t2.a + t0.a < 3"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
|
||
show explain format=json for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t2",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 3,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100
|
||
}
|
||
}
|
||
],
|
||
"subqueries": [
|
||
{
|
||
"expression_cache": {
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t2.a + t0.a < 3"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
|
||
connection con1;
|
||
a SUBQ
|
||
0 0
|
||
1 0
|
||
2 0
|
||
drop table t2;
|
||
SET debug_dbug=@old_debug;
|
||
#
|
||
# Multiple EXPLAIN FOR CONNECTION calls for one select
|
||
#
|
||
create table t2 as select a as a, a as dummy from t0 limit 3;
|
||
set @show_explain_probe_select_id=2;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2;
|
||
connection default;
|
||
explain format=JSON for connection $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t2",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 3,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100
|
||
}
|
||
}
|
||
],
|
||
"subqueries": [
|
||
{
|
||
"expression_cache": {
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t2.a + t0.a < 3"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
|
||
explain format=JSON for connection $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t2",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 3,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100
|
||
}
|
||
}
|
||
],
|
||
"subqueries": [
|
||
{
|
||
"expression_cache": {
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t2.a + t0.a < 3"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
|
||
explain format=JSON for connection $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t2",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 3,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100
|
||
}
|
||
}
|
||
],
|
||
"subqueries": [
|
||
{
|
||
"expression_cache": {
|
||
"query_block": {
|
||
"select_id": 2,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t2.a + t0.a < 3"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
|
||
connection con1;
|
||
a SUBQ
|
||
0 0
|
||
1 0
|
||
2 0
|
||
drop table t2;
|
||
SET debug_dbug=@old_debug;
|
||
#
|
||
# SHOW EXPLAIN for SELECT ... ORDER BY with "Using filesort"
|
||
#
|
||
explain select * from t0 order by a;
|
||
id select_type table type possible_keys key key_len ref rows Extra
|
||
1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using filesort
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
set @show_explain_probe_select_id=1;
|
||
select * from t0 order by a;
|
||
connection default;
|
||
show explain format=json for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"read_sorted_file": {
|
||
"filesort": {
|
||
"sort_key": "t0.a",
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100
|
||
}
|
||
}
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select * from t0 order by a
|
||
connection con1;
|
||
a
|
||
0
|
||
1
|
||
2
|
||
3
|
||
4
|
||
5
|
||
6
|
||
7
|
||
8
|
||
9
|
||
SET debug_dbug=@old_debug;
|
||
#
|
||
# SHOW EXPLAIN for SELECT ... with "Using temporary"
|
||
#
|
||
connection default;
|
||
explain select distinct a from t0;
|
||
id select_type table type possible_keys key key_len ref rows Extra
|
||
1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using temporary
|
||
connection con1;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
set @show_explain_probe_select_id=1;
|
||
select distinct a from t0;
|
||
connection default;
|
||
show explain format=json for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"temporary_table": {
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select distinct a from t0
|
||
connection con1;
|
||
a
|
||
0
|
||
1
|
||
2
|
||
3
|
||
4
|
||
5
|
||
6
|
||
7
|
||
8
|
||
9
|
||
SET debug_dbug=@old_debug;
|
||
#
|
||
# SHOW EXPLAIN for SELECT ... with "Using temporary; Using filesort"
|
||
#
|
||
connection default;
|
||
explain select distinct a from t0;
|
||
id select_type table type possible_keys key key_len ref rows Extra
|
||
1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using temporary
|
||
connection con1;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
set @show_explain_probe_select_id=1;
|
||
select distinct a from t0;
|
||
connection default;
|
||
show explain format=json for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"temporary_table": {
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select distinct a from t0
|
||
connection con1;
|
||
a
|
||
0
|
||
1
|
||
2
|
||
3
|
||
4
|
||
5
|
||
6
|
||
7
|
||
8
|
||
9
|
||
SET debug_dbug=@old_debug;
|
||
#
|
||
# Check if queries in non-default charsets work.
|
||
#
|
||
set names cp1251;
|
||
select charset('<27><>');
|
||
charset('<27><>')
|
||
cp1251
|
||
select hex('<27><>');
|
||
hex('<27><>')
|
||
E3FB
|
||
set @show_explain_probe_select_id=1;
|
||
SET debug_dbug='+d,show_explain_probe_join_exec_start';
|
||
select * from t0 where length('<27><>') = a;
|
||
connection default;
|
||
set names utf8;
|
||
show explain format=JSON for $thr2;
|
||
SHOW EXPLAIN
|
||
{
|
||
"query_block": {
|
||
"select_id": 1,
|
||
"cost": "COST_REPLACED",
|
||
"nested_loop": [
|
||
{
|
||
"table": {
|
||
"table_name": "t0",
|
||
"access_type": "ALL",
|
||
"loops": 1,
|
||
"rows": 10,
|
||
"cost": "COST_REPLACED",
|
||
"filtered": 100,
|
||
"attached_condition": "t0.a = <cache>(octet_length('гы'))"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
}
|
||
Warnings:
|
||
Note 1003 select * from t0 where length('гы') = a
|
||
set names default;
|
||
connection con1;
|
||
a
|
||
2
|
||
SET debug_dbug=@old_debug;
|
||
set names default;
|
||
drop table if exists t0,t1,t2;
|
||
Warnings:
|
||
Note 1051 Unknown table 'test.t2'
|
||
#
|
||
# MDEV-28124: Server crashes in Explain_aggr_filesort::print_json_members
|
||
# upon SHOW ANALYZE/EXPLAIN FORMAT=JSON
|
||
#
|
||
connection con1;
|
||
set @show_explain_probe_query= 'SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency';
|
||
set debug_dbug='+d,explain_notify_tables_are_closed';
|
||
SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency;
|
||
connection default;
|
||
SHOW EXPLAIN FORMAT=JSON FOR $thr2;
|
||
ERROR HY000: Target is not executing an operation with a query plan
|
||
connection con1;
|
||
count(*) - count(*)
|
||
0
|
||
# End
|
||
connection default;
|
||
disconnect con1;
|
||
set debug_sync='RESET';
|