drop table if exists t0,t1,t2; create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); explain format=json select * from t0; 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 } } ] } } explain format=json select * from t0 where 1>2; EXPLAIN { "query_block": { "select_id": 1, "table": { "message": "Impossible WHERE" } } } explain format=json select * from t0 where a<3; 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 < 3" } } ] } } # Try a basic join create table t1 (a int, b int, filler char(32), key(a)); insert into t1 select a.a + b.a* 10 + c.a * 100, a.a + b.a* 10 + c.a * 100, 'filler' from t0 a, t0 b, t0 c; explain format=json select * from t0,t1 where t1.a=t0.a; 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 is not null" } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["a"], "key": "a", "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t0.a"], "loops": 10, "rows": 1, "cost": "COST_REPLACED", "filtered": 100 } } ] } } # Try range and index_merge create table t2 (a1 int, a2 int, b1 int, b2 int, key(a1,a2), key(b1,b2)); insert into t2 select a,a,a,a from t1; explain format=json select * from t2 where a1<5; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t2", "access_type": "range", "possible_keys": ["a1"], "key": "a1", "key_length": "5", "used_key_parts": ["a1"], "loops": 1, "rows": 5, "cost": "COST_REPLACED", "filtered": 100, "index_condition": "t2.a1 < 5" } } ] } } explain format=json select * from t2 where a1=1 or b1=2; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t2", "access_type": "index_merge", "possible_keys": ["a1", "b1"], "key_length": "5,5", "index_merge": { "sort_union": [ { "range": { "key": "a1", "used_key_parts": ["a1"] } }, { "range": { "key": "b1", "used_key_parts": ["b1"] } } ] }, "loops": 1, "rows": 2, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.a1 = 1 or t2.b1 = 2" } } ] } } explain format=json select * from t2 where a1=1 or (b1=2 and b2=3); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t2", "access_type": "index_merge", "possible_keys": ["a1", "b1"], "key_length": "5,10", "index_merge": { "sort_union": [ { "range": { "key": "a1", "used_key_parts": ["a1"] } }, { "range": { "key": "b1", "used_key_parts": ["b1", "b2"] } } ] }, "loops": 1, "rows": 2, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.a1 = 1 or t2.b1 = 2 and t2.b2 = 3" } } ] } } explain format=json select * from t2 where (a1=1 and a2=1) or (b1=2 and b2=1); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t2", "access_type": "index_merge", "possible_keys": ["a1", "b1"], "key_length": "10,10", "index_merge": { "union": [ { "range": { "key": "a1", "used_key_parts": ["a1", "a2"] } }, { "range": { "key": "b1", "used_key_parts": ["b1", "b2"] } } ] }, "loops": 1, "rows": 2, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.a1 = 1 and t2.a2 = 1 or t2.b1 = 2 and t2.b2 = 1" } } ] } } # Try ref access on two key components explain format=json select * from t0,t2 where t2.b1=t0.a and t2.b2=4; 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 is not null" } }, { "table": { "table_name": "t2", "access_type": "ref", "possible_keys": ["b1"], "key": "b1", "key_length": "10", "used_key_parts": ["b1", "b2"], "ref": ["test.t0.a", "const"], "loops": 10, "rows": 1, "cost": "COST_REPLACED", "filtered": 100 } } ] } } drop table t1,t2; # # Try a UNION # explain format=json select * from t0 A union select * from t0 B; EXPLAIN { "query_block": { "union_result": { "table_name": "", "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 } } ] } } ] } } } explain format=json select * from t0 A union all select * from t0 B; EXPLAIN { "query_block": { "union_result": { "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 } } ] } } ] } } } # # Subqueries # create table t1 (a int, b int); insert into t1 select a,a from t0; explain format=json select a, a > (select max(b) from t1 where t1.b=t0.a) from t0; 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 } } ], "subqueries": [ { "expression_cache": { "state": "uninitialized", "query_block": { "select_id": 2, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.b = t0.a" } } ] } } } ] } } explain format=json select * from t0 where a > (select max(b) from t1 where t1.b=t0.a) or a < 3 ; 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 > (subquery#2) or t0.a < 3" } } ], "subqueries": [ { "expression_cache": { "state": "uninitialized", "query_block": { "select_id": 2, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.b = t0.a" } } ] } } } ] } } drop table t1; # # Join buffering # create table t1 (a int, b int); insert into t1 select tbl1.a+10*tbl2.a, tbl1.a+10*tbl2.a from t0 tbl1, t0 tbl2; explain format=json select * from t1 tbl1, t1 tbl2 where tbl1.a=tbl2.a and tbl1.b < 3 and tbl2.b < 5; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "tbl1", "access_type": "ALL", "loops": 1, "rows": 100, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "tbl1.b < 3" } }, { "block-nl-join": { "table": { "table_name": "tbl2", "access_type": "ALL", "loops": 100, "rows": 100, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "tbl2.b < 5" }, "buffer_type": "flat", "buffer_size": "1Kb", "join_type": "BNL", "attached_condition": "tbl2.a = tbl1.a" } } ] } } drop table t1; # # Single-table UPDATE/DELETE, INSERT # explain format=json delete from t0; EXPLAIN { "query_block": { "select_id": 1, "table": { "message": "Deleting all rows" } } } explain format=json delete from t0 where 1 > 2; EXPLAIN { "query_block": { "select_id": 1, "table": { "message": "Impossible WHERE" } } } explain format=json delete from t0 where a < 3; EXPLAIN { "query_block": { "select_id": 1, "table": { "delete": 1, "table_name": "t0", "access_type": "ALL", "rows": 10, "attached_condition": "t0.a < 3" } } } explain format=json update t0 set a=3 where a in (2,3,4); EXPLAIN { "query_block": { "select_id": 1, "table": { "update": 1, "table_name": "t0", "access_type": "ALL", "rows": 10, "attached_condition": "t0.a in (2,3,4)" } } } explain format=json insert into t0 values (1); EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "t0" } } } create table t1 like t0; explain format=json insert into t1 values ((select max(a) from t0)); EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "t1" }, "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 } } ] } } ] } } drop table t1; # # A derived table # create table t1 (a int, b int); insert into t1 select a,a from t0; explain format=json select * from (select a, count(*) as cnt from t1 group by a) as tbl where cnt>0; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "tbl.cnt > 0", "materialized": { "query_block": { "select_id": 2, "cost": "COST_REPLACED", "having_condition": "cnt > 0", "filesort": { "sort_key": "t1.a", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100 } } ] } } } } } } ] } } explain format=json select * from (select a, count(*) as cnt from t1 group by a) as tbl1, t1 as tbl2 where cnt=tbl2.a; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "tbl2", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "tbl2.a is not null" } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "8", "used_key_parts": ["cnt"], "ref": ["test.tbl2.a"], "loops": 10, "rows": 1, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "tbl1.cnt = tbl2.a", "materialized": { "query_block": { "select_id": 2, "cost": "COST_REPLACED", "filesort": { "sort_key": "t1.a", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100 } } ] } } } } } } ] } } # # Non-merged semi-join (aka JTBM) # explain format=json select * from t1 where a in (select max(a) from t1 group by b); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.a is not null" } }, { "table": { "table_name": "", "access_type": "eq_ref", "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "4", "used_key_parts": ["max(a)"], "ref": ["test.t1.a"], "loops": 10, "rows": 1, "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100 } } ] } } } } } ] } } # # Semi-join Materialization # create table t2 like t1; insert into t2 select * from t1; explain format=json select * from t1,t2 where t1.a in ( select seq+0 from seq_1_to_100); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100 } }, { "table": { "table_name": "", "access_type": "eq_ref", "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", "used_key_parts": ["seq+0"], "ref": ["func"], "rows": 1, "filtered": 100, "attached_condition": "t1.a = seq_1_to_100.seq + 0", "materialized": { "unique": 1, "query_block": { "select_id": 2, "nested_loop": [ { "table": { "table_name": "seq_1_to_100", "access_type": "index", "key": "PRIMARY", "key_length": "8", "used_key_parts": ["seq"], "loops": 1, "rows": 100, "cost": "COST_REPLACED", "filtered": 100, "using_index": true } } ] } } } }, { "block-nl-join": { "table": { "table_name": "t2", "access_type": "ALL", "loops": 10, "rows": 10, "cost": "COST_REPLACED", "filtered": 100 }, "buffer_type": "flat", "buffer_size": "19Kb", "join_type": "BNL" } } ] } } # # First-Match # explain select * from t2 where t2.a in ( select a from t1 where t1.b=t2.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where; FirstMatch(t2); Using join buffer (flat, BNL join) explain format=json select * from t2 where t2.a in ( select a from t1 where t1.b=t2.b); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100 } }, { "block-nl-join": { "table": { "table_name": "t1", "access_type": "ALL", "loops": 10, "rows": 10, "cost": "COST_REPLACED", "filtered": 10, "first_match": "t2" }, "buffer_type": "flat", "buffer_size": "141", "join_type": "BNL", "attached_condition": "t1.b = t2.b and t1.a = t2.a" } } ] } } # # Duplicate Weedout # set @tmp= @@optimizer_switch; set optimizer_switch='firstmatch=off'; explain select * from t2 where t2.a in ( select a from t1 where t1.b=t2.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) explain format=json select * from t2 where t2.a in ( select a from t1 where t1.b=t2.b); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100 } }, { "duplicates_removal": [ { "block-nl-join": { "table": { "table_name": "t1", "access_type": "ALL", "loops": 10, "rows": 10, "cost": "COST_REPLACED", "filtered": 10 }, "buffer_type": "flat", "buffer_size": "206", "join_type": "BNL", "attached_condition": "t1.b = t2.b and t1.a = t2.a" } } ] } ] } } set optimizer_switch=@tmp; drop table t1,t2; # # MRR for range access (no BKA, just MRR) # create table t1 (a int, b int, key(a)); insert into t1 select tbl1.a+10*tbl2.a, 12345 from t0 tbl1, t0 tbl2; set @tmp= @@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on'; explain format=json select * from t1 where a < 3; 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": 2, "cost": "COST_REPLACED", "filtered": 100, "index_condition": "t1.a < 3", "mrr_type": "Rowid-ordered scan" } } ] } } # 'Range checked for each record' set optimizer_switch=@tmp; explain format=json select * from t1 tbl1, t1 tbl2 where tbl2.a < tbl1.b; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "tbl1", "access_type": "ALL", "loops": 1, "rows": 100, "cost": "COST_REPLACED", "filtered": 100 } }, { "range-checked-for-each-record": { "keys": ["a"], "table": { "table_name": "tbl2", "access_type": "ALL", "possible_keys": ["a"], "loops": 100, "rows": 100, "cost": "COST_REPLACED", "filtered": 100 } } } ] } } drop table t1; drop table t0; # # MDEV-7265: "Full scan on NULL key", the join case # CREATE TABLE t1 (a INT, KEY(a)); INSERT INTO t1 VALUES (1),(2),(5),(6),(7); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (3),(4),(9),(10),(11); EXPLAIN FORMAT=JSON SELECT * FROM t1 AS outer_t1 WHERE a <> ALL ( SELECT a FROM t1, t2 WHERE b <> outer_t1.a ); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "outer_t1", "access_type": "index", "key": "a", "key_length": "5", "used_key_parts": ["a"], "loops": 1, "rows": 5, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "!(outer_t1.a,(subquery#2))", "using_index": true } } ], "subqueries": [ { "query_block": { "select_id": 2, "cost": "COST_REPLACED", "having_condition": "trigcond(t1.a is null)", "nested_loop": [ { "full-scan-on-null_key": { "table": { "table_name": "t1", "access_type": "ref_or_null", "possible_keys": ["a"], "key": "a", "key_length": "5", "used_key_parts": ["a"], "ref": ["func"], "loops": 1, "rows": 3, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "trigcond((outer_t1.a) = t1.a or t1.a is null)", "using_index": true } } }, { "block-nl-join": { "table": { "table_name": "t2", "access_type": "ALL", "loops": 3, "rows": 5, "cost": "COST_REPLACED", "filtered": 100 }, "buffer_type": "flat", "buffer_size": "65", "join_type": "BNL", "attached_condition": "t2.b <> outer_t1.a" } } ] } } ] } } DROP TABLE t1,t2; # # Join's constant expression # create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a int, b int); insert into t1 select tbl1.a+10*tbl2.a, 1234 from t0 tbl1, t0 tbl2; explain format=json select * from t0 where 20000 > all (select max(tbl1.a + tbl2.a) from t1 tbl1, t1 tbl2 where tbl1.b=tbl2.b); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "const_condition": "((20000,(subquery#2) >= 20000))", "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100 } } ], "subqueries": [ { "query_block": { "select_id": 2, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "tbl1", "access_type": "ALL", "loops": 1, "rows": 100, "cost": "COST_REPLACED", "filtered": 100 } }, { "block-nl-join": { "table": { "table_name": "tbl2", "access_type": "ALL", "loops": 100, "rows": 100, "cost": "COST_REPLACED", "filtered": 100 }, "buffer_type": "flat", "buffer_size": "1Kb", "join_type": "BNL", "attached_condition": "tbl2.b = tbl1.b" } } ] } } ] } } drop table t1; drop table t0; # # MDEV-7264: Assertion `0' failed in subselect_engine::get_identifier() on EXPLAIN JSON # CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (3),(4); EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE a <> ALL ( SELECT b FROM t2 ); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 2, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "!(t1.a,(subquery#2))" } } ], "subqueries": [ { "query_block": { "select_id": 2, "cost": "COST_REPLACED", "having_condition": "trigcond(t2.b is null)", "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "rows": 2, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "trigcond((t1.a) = t2.b or t2.b is null)" } } ] } } ] } } DROP TABLE t1, t2; # # MDEV-7927: Server crashes in in Time_and_counter_tracker::incr_loops # CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (1),(2); EXPLAIN SELECT * FROM t1 WHERE 3 IN ( SELECT 4 UNION SELECT 5 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL DROP TABLE t1; # # MDEV-7860: EXPLAIN FORMAT=JSON crashes for loose scan query # create table t2(a int); insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, c int, d int, key(a,b,c)); insert into t1 select A.a, B.a, C.a, D.a from t2 A, t2 B, t2 C, t2 D; explain select count(distinct b) from t1 group by a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL a 10 NULL 101 Using index for group-by explain format=json select count(distinct b) from t1 group by a; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "range", "key": "a", "key_length": "10", "used_key_parts": ["a", "b"], "loops": 1, "rows": 101, "cost": "COST_REPLACED", "filtered": 100, "using_index_for_group_by": true } } ] } } analyze format=json select count(distinct b) from t1 group by a; ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "range", "key": "a", "key_length": "10", "used_key_parts": ["a", "b"], "loops": 1, "r_loops": 1, "rows": 101, "r_rows": 100, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "using_index_for_group_by": true } } ] } } drop table t1,t2; # # Try both variants of LooseScan (data/queries borrowed from group_min_max.test) # create table t1 ( a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' ' ); insert into t1 (a1, a2, b, c, d) values ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), ('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), ('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), ('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), ('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), ('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), ('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), ('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), ('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); create index idx_t1_0 on t1 (a1); create index idx_t1_1 on t1 (a1,a2,b,c); create index idx_t1_2 on t1 (a1,a2,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 select count(*) from t1; count(*) 128 explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL idx_t1_2 147 NULL 17 Using where; Using index for group-by explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index explain select count(distinct a1,a2,b) from t1 where a1 >= "" and (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 1 Using where; Using index for group-by explain format=json select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "range", "key": "idx_t1_2", "key_length": "147", "used_key_parts": ["a1", "a2", "b"], "loops": 1, "rows": 17, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.b = 'a' and t1.a2 >= 'b'", "using_index_for_group_by": true } } ] } } explain format=json select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "key": "idx_t1_1", "key_length": "163", "used_key_parts": ["a1", "a2", "b", "c"], "loops": 1, "rows": 128, "cost": "COST_REPLACED", "filtered": 0.198364258, "attached_condition": "t1.b = 'a' and t1.c = 'i121' and t1.a2 >= 'b'", "using_index": true } } ] } } explain format=json select count(distinct a1,a2,b) from t1 where a1 >= "" and (a2 >= 'b') and (b = 'a'); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "range", "possible_keys": ["idx_t1_0", "idx_t1_1", "idx_t1_2"], "key": "idx_t1_1", "key_length": "147", "used_key_parts": ["a1", "a2", "b"], "loops": 1, "rows": 1, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.b = 'a' and t1.a1 >= '' and t1.a2 >= 'b'", "using_index_for_group_by": true } } ] } } drop table t1; # # MDEV-8786 Wrong result for SELECT FORMAT=JSON * FROM t1 WHERE a=_latin1 0xDF # CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); INSERT INTO t1 VALUES ('a'),('b'); EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE a=_latin1 0xDF; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 2, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.a = _latin1'\xDF'" } } ] } } DROP TABLE t1; # # MDEV-8785 Wrong results for EXPLAIN EXTENDED...WHERE NULLIF(latin1_col, _utf8'a' COLLATE utf8_bin) IS NOT NULL # CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); INSERT INTO t1 VALUES ('a'),('A'); EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE NULLIF(a,_utf8'a' COLLATE utf8_bin); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 2, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "(case when convert(t1.a using utf8mb3) = (_utf8mb3'a' collate utf8mb3_bin) then NULL else t1.a end)" } } ] } } DROP TABLE t1; # # MDEV-7970: EXPLAIN FORMAT=JSON does not print HAVING # create table t0(a int); insert into t0 values (0),(1),(2),(3); create table t1(a int); insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; create table t2 ( a int, b int, key (a) ); insert into t2 select A.a*1000 + B.a, A.a*1000 + B.a from t0 A, t1 B; # normal HAVING explain format=json select a, max(b) as TOP from t2 group by a having TOP > a; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "having_condition": "TOP > t2.a", "filesort": { "sort_key": "t2.a", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "rows": 256, "cost": "COST_REPLACED", "filtered": 100 } } ] } } } } # HAVING is always TRUE (not printed) explain format=json select a, max(b) as TOP from t2 group by a having 1<>2; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "filesort": { "sort_key": "t2.a", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "rows": 256, "cost": "COST_REPLACED", "filtered": 100 } } ] } } } } # HAVING is always FALSE (intercepted by message) explain format=json select a, max(b) as TOP from t2 group by a having 1=2; EXPLAIN { "query_block": { "select_id": 1, "table": { "message": "Impossible HAVING" } } } # HAVING is absent explain format=json select a, max(b) as TOP from t2 group by a; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "filesort": { "sort_key": "t2.a", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "rows": 256, "cost": "COST_REPLACED", "filtered": 100 } } ] } } } } drop table t0, t1, t2; # # MDEV-8829: Assertion `0' failed in Explain_table_access::tag_to_json # # Check ET_CONST_ROW_NOT_FOUND create table t1 (i int) engine=myisam; explain select * from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found explain format=json select * from t1; EXPLAIN { "query_block": { "select_id": 1, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "system", "rows": 0, "filtered": 0, "const_row_not_found": true } } ] } } analyze format=json select * from t1; ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "table": { "message": "no matching row in const table" } } } drop table t1; # Check ET_IMPOSSIBLE_ON_CONDITION create table t1 (a int); create table t2 (pk int primary key); insert into t1 values (1),(2); insert into t2 values (1),(2); explain select * from t1 left join t2 on t2.pk > 10 and t2.pk < 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 const PRIMARY NULL NULL NULL 0 Impossible ON condition 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 explain format=json select * from t1 left join t2 on t2.pk > 10 and t2.pk < 0; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "const_condition": "1", "nested_loop": [ { "table": { "table_name": "t2", "access_type": "const", "possible_keys": ["PRIMARY"], "rows": 0, "filtered": 0, "impossible_on_condition": true } }, { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 2, "cost": "COST_REPLACED", "filtered": 100 } } ] } } analyze format=json select * from t1 left join t2 on t2.pk > 10 and t2.pk < 0; ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "const_condition": "1", "nested_loop": [ { "table": { "table_name": "t2", "access_type": "const", "possible_keys": ["PRIMARY"], "r_loops": 0, "rows": 0, "r_rows": null, "filtered": 0, "r_filtered": null, "impossible_on_condition": true } }, { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 2, "r_rows": 2, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100 } } ] } } # Check ET_NOT_EXISTS: explain select * from t1 left join t2 on t2.pk=t1.a where t2.pk is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using index; Not exists explain format=json select * from t1 left join t2 on t2.pk=t1.a where t2.pk is null; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 2, "cost": "COST_REPLACED", "filtered": 100 } }, { "table": { "table_name": "t2", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["pk"], "ref": ["test.t1.a"], "loops": 2, "rows": 1, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "trigcond(t2.pk is null) and trigcond(trigcond(t1.a is not null))", "using_index": true, "not_exists": true } } ] } } analyze format=json select * from t1 left join t2 on t2.pk=t1.a where t2.pk is null; ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 2, "r_rows": 2, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "t2", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["pk"], "ref": ["test.t1.a"], "loops": 2, "r_loops": 2, "rows": 1, "r_rows": 1, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(t2.pk is null) and trigcond(trigcond(t1.a is not null))", "using_index": true, "not_exists": true } } ] } } # Check ET_DISTINCT explain select distinct t1.a from t1 join t2 on t2.pk=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index; Distinct explain format=json select distinct t1.a from t1 join t2 on t2.pk=t1.a; EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 2, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.a is not null" } }, { "table": { "table_name": "t2", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["pk"], "ref": ["test.t1.a"], "loops": 2, "rows": 1, "cost": "COST_REPLACED", "filtered": 100, "using_index": true, "distinct": true } } ] } } } analyze format=json select distinct t1.a from t1 join t2 on t2.pk=t1.a; ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 2, "r_rows": 2, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "attached_condition": "t1.a is not null" } }, { "table": { "table_name": "t2", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["pk"], "ref": ["test.t1.a"], "loops": 2, "r_loops": 2, "rows": 1, "r_rows": 1, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "using_index": true, "distinct": true } } ] } } } drop table t1,t2; # Check ET_USING_INDEX_CONDITION_BKA create table t1(a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2(a int); insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; create table t3(a int, b int); insert into t3 select a,a from t1; create table t4(a int, b int, c int, filler char(100), key (a,b)); insert into t4 select a,a,a, 'filler-data' from t2; set @tmp_optimizer_switch=@@optimizer_switch; set @tmp_join_cache_level=@@join_cache_level; set optimizer_switch='mrr=on'; set join_cache_level=6; explain select * from t3,t4 where t3.a=t4.a and (t4.b+1 <= t3.b+1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 10 Using where 1 SIMPLE t4 ref a a 5 test.t3.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan explain format=json select * from t3,t4 where t3.a=t4.a and (t4.b+1 <= t3.b+1); EXPLAIN { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t3.a is not null" } }, { "block-nl-join": { "table": { "table_name": "t4", "access_type": "ref", "possible_keys": ["a"], "key": "a", "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t3.a"], "loops": 10, "rows": 1, "cost": "COST_REPLACED", "filtered": 100, "index_condition_bka": "t4.b + 1 <= t3.b + 1" }, "buffer_type": "flat", "buffer_size": "400", "join_type": "BKA", "mrr_type": "Rowid-ordered scan" } } ] } } analyze format=json select * from t3,t4 where t3.a=t4.a and (t4.b+1 <= t3.b+1); ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 10, "r_rows": 10, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "attached_condition": "t3.a is not null" } }, { "block-nl-join": { "table": { "table_name": "t4", "access_type": "ref", "possible_keys": ["a"], "key": "a", "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t3.a"], "loops": 10, "r_loops": 1, "rows": 1, "r_rows": 10, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "index_condition_bka": "t4.b + 1 <= t3.b + 1" }, "buffer_type": "flat", "buffer_size": "400", "join_type": "BKA", "mrr_type": "Rowid-ordered scan", "r_filtered": 100, "r_unpack_time_ms": "REPLACED" } } ] } } set optimizer_switch=@tmp_optimizer_switch; set join_cache_level=@tmp_join_cache_level; drop table t1,t2,t3,t4; # # MDEV-9652: EXPLAIN FORMAT=JSON should show outer_ref_cond # create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int); insert into t1 select a,a from t0; explain format=json select a, (select max(a) from t1 where t0.a<5 and t1.b