diff options
author | Sergei Petrunia <psergey@askmonty.org> | 2020-09-20 00:07:37 +0300 |
---|---|---|
committer | Sergei Petrunia <psergey@askmonty.org> | 2020-09-20 00:07:37 +0300 |
commit | 29a6d23622caabe59c1627e32518f2bfcf8143f9 (patch) | |
tree | 06c3ad73cf06517547fa94e7792c305bedb64c04 | |
parent | ccbe6bb6fc3cbe31e74404723f4ab78f7c530950 (diff) | |
download | mariadb-git-bb-10.5-mdev23767.tar.gz |
MDEV-23767: IN-to-subquery conversion is not visible in optimizer tracebb-10.5-mdev23767
Add the printout
-rw-r--r-- | mysql-test/main/opt_trace.result | 98 | ||||
-rw-r--r-- | mysql-test/main/opt_trace.test | 26 | ||||
-rw-r--r-- | sql/sql_tvc.cc | 26 |
3 files changed, 148 insertions, 2 deletions
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 <derived3> 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'; diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 37f0f80e8a0..df774a5d8dd 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -22,6 +22,7 @@ #include "sql_explain.h" #include "sql_parse.h" #include "sql_cte.h" +#include "my_json_writer.h" /** @@ -903,6 +904,10 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd, if (!transform_into_subq) return this; + Json_writer_object trace_wrapper(thd); + Json_writer_object trace_conv(thd, "in_to_subquery_conversion"); + trace_conv.add("item", this); + transform_into_subq= false; List<List_item> values; @@ -922,13 +927,29 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd, uint32 length= max_length_of_left_expr(); if (!length || length > tmp_table_max_key_length() || args[0]->cols() > tmp_table_max_key_parts()) + { + trace_conv.add("done", false); + trace_conv.add("reason", "key is too long"); return this; - + } + for (uint i=1; i < arg_count; i++) { - if (!args[i]->const_item() || cmp_row_types(args[0], args[i])) + if (!args[i]->const_item()) + { + trace_conv.add("done", false); + trace_conv.add("reason", "non-constant element in the IN-list"); return this; + } + + if (cmp_row_types(args[0], args[i])) + { + trace_conv.add("done", false); + trace_conv.add("reason", "type mismatch"); + return this; + } } + Json_writer_array trace_nested_obj(thd, "conversion"); Query_arena backup; Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); @@ -1020,6 +1041,7 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd, goto err; parent_select->curr_tvc_name++; + return sq; err: |