drop table if exists t0, t1, t2, t3, t4; drop view if exists v1; SET @old_debug= @@session.debug; 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); alter table t1 add b int, add c int, add filler char(32); insert into t1 select A.a, 10*B.a, 100*C.a, 'foo filler' from t0 A, t0 B, t0 C; alter table t1 add key(a), add key(b); analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date show analyze 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 show analyze format=json for 1001; ERROR HY000: Unknown thread id: 1001 show analyze format=JSON for (select a from t0 limit 1); ERROR HY000: You may only use constant expressions in this statement connect con1, localhost, root,,; connection con1; SET @old_debug= @@session.debug; connection default; show analyze format = JSON for $thr2; ERROR HY000: Target is not executing an operation with a query plan show analyze format = json for $thr1; ERROR HY000: Target is not executing an operation with a query plan show analyze FORMAT=HTML for $thr1; ERROR HY000: Unknown EXPLAIN/ANALYZE format name: 'HTML' analyze 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_end'; select count(*) from t1 where c < 500; connection default; show analyze format=JSON for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "r_loops": 1, "rows": 1000, "r_rows": 1000, "filtered": 50, "r_filtered": 50, "attached_condition": "t1.c < 500" } } ] } } Warnings: Note 1003 select count(*) from t1 where c < 500 connection con1; count(*) 500 select max(c) from t1 where c < 10; connection default; show analyze format=json for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "r_loops": 1, "rows": 1000, "r_rows": 1000, "filtered": 10, "r_filtered": 10, "attached_condition": "t1.c < 10" } } ] } } Warnings: Note 1003 select max(c) from t1 where c < 10 connection con1; max(c) 0 # We can catch ANALYZE too. analyze select max(c) from t1 where a < 10; connection default; show analyze format=json for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "possible_keys": ["a"], "r_loops": 1, "rows": 1000, "r_rows": 1000, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 99.90000153, "r_filtered": 100, "attached_condition": "t1.a < 10" } } ] } } Warnings: Note 1003 analyze select max(c) from t1 where a < 10 connection con1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE t1 ALL a NULL NULL NULL 1000 1000.00 99.90 100.00 Using where SET debug_dbug=@old_debug; # UNION, select, first branch set @show_explain_probe_select_id=1; SET debug_dbug='+d,show_explain_probe_join_exec_end'; select max(a) from t0 a where a<=5 union select max(a+1) from t0 b where a>=9; connection default; show analyze FORMAT= json for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 0, "r_rows": null, "query_specifications": [ { "query_block": { "select_id": 1, "r_loops": 1, "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "filtered": 100, "r_filtered": 60, "attached_condition": "a.a <= 5" } } ] } }, { "query_block": { "select_id": 2, "operation": "UNION", "nested_loop": [ { "table": { "table_name": "b", "access_type": "ALL", "r_loops": 0, "rows": 10, "r_rows": null, "filtered": 100, "r_filtered": null, "attached_condition": "b.a >= 9" } } ] } } ] } } } Warnings: Note 1003 select max(a) from t0 a where a<=5 union select max(a+1) from t0 b where a>=9 connection con1; max(a) 5 10 # UNION, select, second branch set @show_explain_probe_select_id=2; SET debug_dbug='+d,show_explain_probe_join_exec_end'; select max(a) from t0 a where a<=5 union select max(a+1) from t0 b where a>=9; connection default; show analyze format=JSON for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 0, "r_rows": null, "query_specifications": [ { "query_block": { "select_id": 1, "r_loops": 1, "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "filtered": 100, "r_filtered": 60, "attached_condition": "a.a <= 5" } } ] } }, { "query_block": { "select_id": 2, "operation": "UNION", "r_loops": 1, "nested_loop": [ { "table": { "table_name": "b", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "filtered": 100, "r_filtered": 10, "attached_condition": "b.a >= 9" } } ] } } ] } } } Warnings: Note 1003 select max(a) from t0 a where a<=5 union select max(a+1) from t0 b where a>=9 connection con1; max(a) 5 10 # UNION, analyze, first branch set @show_explain_probe_select_id=1; SET debug_dbug='+d,show_explain_probe_join_exec_end'; analyze select a from t0 a where a<=5 union select a+1 from t0 b where a>=9; connection default; show analyze format=json for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 0, "r_rows": null, "query_specifications": [ { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 60, "attached_condition": "a.a <= 5" } } ] } }, { "query_block": { "select_id": 2, "operation": "UNION", "nested_loop": [ { "table": { "table_name": "b", "access_type": "ALL", "r_loops": 0, "rows": 10, "r_rows": null, "filtered": 100, "r_filtered": null, "attached_condition": "b.a >= 9" } } ] } } ] } } } Warnings: Note 1003 analyze select a from t0 a where a<=5 union select a+1 from t0 b where a>=9 connection con1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY a ALL NULL NULL NULL NULL 10 10.00 100.00 60.00 Using where 2 UNION b ALL NULL NULL NULL NULL 10 10.00 100.00 10.00 Using where NULL UNION RESULT ALL NULL NULL NULL NULL NULL 7.00 NULL NULL # UNION, analyze, second branch set @show_explain_probe_select_id=2; analyze select a from t0 a where a<=5 union select a+1 from t0 b where a>=9; connection default; show analyze FORMAT=JSON for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 0, "r_rows": null, "query_specifications": [ { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 60, "attached_condition": "a.a <= 5" } } ] } }, { "query_block": { "select_id": 2, "operation": "UNION", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "b", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 10, "attached_condition": "b.a >= 9" } } ] } } ] } } } Warnings: Note 1003 analyze select a from t0 a where a<=5 union select a+1 from t0 b where a>=9 connection con1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY a ALL NULL NULL NULL NULL 10 10.00 100.00 60.00 Using where 2 UNION b ALL NULL NULL NULL NULL 10 10.00 100.00 10.00 Using where NULL UNION RESULT ALL NULL NULL NULL NULL NULL 7.00 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_end'; select a, (select max(a) from t0 b where a>6) from t0 a where a<2; connection default; show analyze format=json for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "filtered": 100, "r_filtered": 20, "attached_condition": "a.a < 2" } } ], "subqueries": [ { "query_block": { "select_id": 2, "r_loops": 1, "nested_loop": [ { "table": { "table_name": "b", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "filtered": 100, "r_filtered": 30, "attached_condition": "b.a > 6" } } ] } } ] } } Warnings: Note 1003 select a, (select max(a) from t0 b where a>6) from t0 a where a<2 connection con1; a (select max(a) from t0 b where a>6) 0 9 1 9 SET debug_dbug=@old_debug; # Uncorrelated subquery, analyze set @show_explain_probe_select_id=1; SET debug_dbug='+d,show_explain_probe_join_exec_end'; analyze select a, (select max(a) from t0 b where a>6) from t0 a where a<2; connection default; show analyze format=json for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 20, "attached_condition": "a.a < 2" } } ], "subqueries": [ { "query_block": { "select_id": 2, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "b", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 30, "attached_condition": "b.a > 6" } } ] } } ] } } Warnings: Note 1003 analyze select a, (select max(a) from t0 b where a>6) from t0 a where a<2 connection con1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY a ALL NULL NULL NULL NULL 10 10.00 100.00 20.00 Using where 2 SUBQUERY b ALL NULL NULL NULL NULL 10 10.00 100.00 30.00 Using where SET debug_dbug=@old_debug; # correlated subquery, select, before execution start 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<2; connection default; show analyze format=json for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "r_loops": 0, "rows": 10, "r_rows": null, "filtered": 100, "r_filtered": null, "attached_condition": "a.a < 2" } } ], "subqueries": [ { "expression_cache": { "state": "uninitialized", "r_loops": 0, "query_block": { "select_id": 2, "nested_loop": [ { "table": { "table_name": "b", "access_type": "ALL", "r_loops": 0, "rows": 10, "r_rows": null, "filtered": 100, "r_filtered": null, "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<2 connection con1; a (select max(a) from t0 b where b.a+a.a<10) 0 9 1 8 SET debug_dbug=@old_debug; # correlated subquery, select, after execution 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<2; connection default; show analyze format=json for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "filtered": 100, "r_filtered": 20, "attached_condition": "a.a < 2" } } ], "subqueries": [ { "expression_cache": { "r_loops": 2, "r_hit_ratio": 0, "query_block": { "select_id": 2, "r_loops": 2, "nested_loop": [ { "table": { "table_name": "b", "access_type": "ALL", "r_loops": 2, "rows": 10, "r_rows": 10, "filtered": 100, "r_filtered": 95, "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<2 connection con1; a (select max(a) from t0 b where b.a+a.a<10) 0 9 1 8 SET debug_dbug=@old_debug; # correlated subquery, analyze set @show_explain_probe_select_id=1; SET debug_dbug='+d,show_explain_probe_join_exec_end'; analyze select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<2; connection default; show analyze format=json for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 20, "attached_condition": "a.a < 2" } } ], "subqueries": [ { "expression_cache": { "r_loops": 2, "r_hit_ratio": 0, "query_block": { "select_id": 2, "r_loops": 2, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "b", "access_type": "ALL", "r_loops": 2, "rows": 10, "r_rows": 10, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 95, "attached_condition": "b.a + a.a < 10" } } ] } } } ] } } Warnings: Note 1003 analyze select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<2 connection con1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY a ALL NULL NULL NULL NULL 10 10.00 100.00 20.00 Using where 2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 10.00 100.00 95.00 Using where SET debug_dbug=@old_debug; # Try to do SHOW ANALYZE for a query that runs a SET command: # set @show_explain_probe_select_id=2; SET debug_dbug='+d,show_explain_probe_join_exec_start'; set @foo= (select max(a) from t0 where sin(a) >0); connection default; show analyze 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; # # Attempt SHOW ANALYZE 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 analyze FORMAT=JSON for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "table": { "update": 1, "table_name": "t2", "access_type": "ALL", "rows": 2, "r_rows": 1, "r_filtered": 0, "attached_condition": "(subquery#2) > 3" }, "subqueries": [ { "query_block": { "select_id": 2, "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "r_loops": 0, "rows": 10, "r_rows": null, "filtered": 100, "r_filtered": null, "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 analyze FORMAT=JSON for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "table": { "update": 1, "table_name": "t2", "access_type": "ALL", "rows": 2, "r_rows": 2, "r_filtered": 0, "attached_condition": "(subquery#2) > 3" }, "subqueries": [ { "query_block": { "select_id": 2, "r_loops": 1, "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "filtered": 100, "r_filtered": 30, "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 ANALYZE 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 analyze FORMAT=JSON for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "table": { "delete": 1, "table_name": "t2", "access_type": "ALL", "rows": 2, "r_rows": 1, "r_filtered": 0, "attached_condition": "(subquery#2) > 3" }, "subqueries": [ { "query_block": { "select_id": 2, "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "r_loops": 0, "rows": 10, "r_rows": null, "filtered": 100, "r_filtered": null, "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 analyze FORMAT=JSON for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "table": { "delete": 1, "table_name": "t2", "access_type": "ALL", "rows": 2, "r_rows": 2, "r_filtered": 0, "attached_condition": "(subquery#2) > 3" }, "subqueries": [ { "query_block": { "select_id": 2, "r_loops": 1, "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "filtered": 100, "r_filtered": 30, "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 ANALYZE 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 analyze format=json for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "r_loops": 1, "rows": 3, "r_rows": 1, "filtered": 100, "r_filtered": 100 } } ], "subqueries": [ { "expression_cache": { "r_loops": 1, "r_hit_ratio": 0, "query_block": { "select_id": 2, "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "r_loops": 0, "rows": 10, "r_rows": null, "filtered": 100, "r_filtered": null, "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 analyze format=json for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "r_loops": 1, "rows": 3, "r_rows": 2, "filtered": 100, "r_filtered": 100 } } ], "subqueries": [ { "expression_cache": { "r_loops": 2, "r_hit_ratio": 0, "query_block": { "select_id": 2, "r_loops": 1, "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "filtered": 100, "r_filtered": 30, "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 analyze format=json for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "r_loops": 1, "rows": 3, "r_rows": 3, "filtered": 100, "r_filtered": 100 } } ], "subqueries": [ { "expression_cache": { "r_loops": 3, "r_hit_ratio": 0, "query_block": { "select_id": 2, "r_loops": 2, "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "r_loops": 2, "rows": 10, "r_rows": 10, "filtered": 100, "r_filtered": 25, "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 SET debug_dbug=@old_debug; drop table t2; # # SHOW ANALYZE for SELECT ... ORDER BY with "Using filesort" # SET debug_dbug='+d,show_explain_probe_join_exec_end'; set @show_explain_probe_select_id=1; select * from t0 order by a; connection default; show analyze format=JSON for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "nested_loop": [ { "read_sorted_file": { "r_rows": 10, "filesort": { "sort_key": "t0.a", "r_loops": 1, "r_used_priority_queue": false, "r_output_rows": 10, "r_buffer_size": "REPLACED", "r_sort_mode": "sort_key,addon_fields", "table": { "table_name": "t0", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "filtered": 100, "r_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 ANALYZE for SELECT ... with "Using temporary" # connection con1; SET debug_dbug='+d,show_explain_probe_join_exec_end'; set @show_explain_probe_select_id=1; select distinct a from t0; connection default; show analyze format=json for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "filtered": 100, "r_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 ANALYZE for SELECT ... with "Using temporary; Using filesort" # SET debug_dbug='+d,show_explain_probe_join_exec_end'; set @show_explain_probe_select_id=1; select distinct a from t0; connection default; show analyze format=json for $thr2; SHOW ANALYZE { "r_query_time_in_progress_ms": "REPLACED", "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "filtered": 100, "r_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; drop table t0,t1; # # 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 ANALYZE 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';