From 29a6d23622caabe59c1627e32518f2bfcf8143f9 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Sun, 20 Sep 2020 00:07:37 +0300 Subject: MDEV-23767: IN-to-subquery conversion is not visible in optimizer trace Add the printout --- mysql-test/main/opt_trace.result | 98 ++++++++++++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 26 +++++++++++ 2 files changed, 124 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index b0b6120e271..8f4f6ec97ce 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -8684,5 +8684,103 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const')) } ] drop table t0, t1, t2, t3; +# +# MDEV-23767: IN-to-subquery conversion is not visible in optimizer trace +# +create table t0 (a int); +INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +set @tmp=@@in_predicate_conversion_threshold; +set in_predicate_conversion_threshold=3; +explain select * from t0 where a in (1,2,3,4,5,6); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY ref key0 key0 4 test.t0.a 2 FirstMatch(t0) +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) +from information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) +[ + + { + "item": "t0.a in (1,2,3,4,5,6)", + "conversion": + [ + + { + "join_preparation": + { + "select_id": 2, + "steps": + [ + + { + "derived": + { + "table": "tvc_0", + "select_id": 3, + "algorithm": "materialized" + } + }, + + { + "transformation": + { + "select_id": 2, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, + + { + "transformation": + { + "select_id": 2, + "from": "IN (SELECT)", + "to": "semijoin", + "chosen": true + } + }, + + { + "expanded_query": "/* select#2 */ select tvc_0._col_1 from (values (1),(2),(3),(4),(5),(6)) tvc_0" + } + ] + } + } + ] + } +] +explain select * from t0 where a in (1,2,3,4,5,a+1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) +from information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) +[ + + { + "item": "t0.a in (1,2,3,4,5,t0.a + 1)", + "done": false, + "reason": "non-constant element in the IN-list" + } +] +explain select * from t0 where a in ('1','2','3','4','5','6'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) +from information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) +[ + + { + "item": "t0.a in ('1','2','3','4','5','6')", + "done": false, + "reason": "type mismatch" + } +] +set in_predicate_conversion_threshold=@tmp; +drop table t0; # End of 10.5 tests set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index d6030543313..314e5825c08 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -654,5 +654,31 @@ from information_schema.optimizer_trace; drop table t0, t1, t2, t3; +--echo # +--echo # MDEV-23767: IN-to-subquery conversion is not visible in optimizer trace +--echo # +create table t0 (a int); +INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +set @tmp=@@in_predicate_conversion_threshold; +set in_predicate_conversion_threshold=3; + +explain select * from t0 where a in (1,2,3,4,5,6); + +select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) +from information_schema.optimizer_trace; + +explain select * from t0 where a in (1,2,3,4,5,a+1); + +select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) +from information_schema.optimizer_trace; + +explain select * from t0 where a in ('1','2','3','4','5','6'); +select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) +from information_schema.optimizer_trace; + +set in_predicate_conversion_threshold=@tmp; +drop table t0; + --echo # End of 10.5 tests set optimizer_trace='enabled=off'; -- cgit v1.2.1