diff options
Diffstat (limited to 'mysql-test/main/opt_tvc.result')
| -rw-r--r-- | mysql-test/main/opt_tvc.result | 658 |
1 files changed, 658 insertions, 0 deletions
diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result new file mode 100644 index 00000000000..0ecae5bf157 --- /dev/null +++ b/mysql-test/main/opt_tvc.result @@ -0,0 +1,658 @@ +create table t1 (a int, b int); +insert into t1 +values (1,2), (4,6), (9,7), +(1,1), (2,5), (7,8); +create table t2 (a int, b int, c int); +insert into t2 +values (1,2,3), (5,1,2), (4,3,7), +(8,9,0), (10,7,1), (5,5,1); +create table t3 (a int, b varchar(16), index idx(a)); +insert into t3 values +(1, "abc"), (3, "egh"), (8, "axxx"), (10, "abc"), +(2, "ccw"), (8, "wqqe"), (7, "au"), (9, "waa"), +(3, "rass"), (9, "ert"), (9, "lok"), (8, "aww"), +(1, "todd"), (3, "rew"), (8, "aww"), (3, "sw"), +(11, "llk"), (7, "rbw"), (1, "sm"), (2, "jyp"), +(4, "yq"), (5, "pled"), (12, "ligin"), (12, "toww"), +(6, "mxm"), (15, "wanone"), (9, "sunqq"), (2, "abe"); +# optimization is not used +select * from t1 where a in (1,2); +a b +1 2 +1 1 +2 5 +explain extended select * from t1 where a in (1,2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` in (1,2) +# set minimum number of values in VALUEs list when optimization works to 2 +set @@in_predicate_conversion_threshold= 2; +# single IN-predicate in WHERE-part +select * from t1 where a in (1,2); +a b +1 2 +1 1 +2 5 +select * from t1 +where a in +( +select * +from (values (1),(2)) as tvc_0 +); +a b +1 2 +1 1 +2 5 +explain extended select * from t1 where a in (1,2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +explain extended select * from t1 +where a in +( +select * +from (values (1),(2)) as tvc_0 +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +# AND-condition with IN-predicates in WHERE-part +select * from t1 +where a in (1,2) and +b in (1,5); +a b +1 1 +2 5 +select * from t1 +where a in +( +select * +from (values (1),(2)) as tvc_0 +) +and b in +( +select * +from (values (1),(5)) as tvc_1 +); +a b +1 1 +2 5 +explain extended select * from t1 +where a in (1,2) and +b in (1,5); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`1` +explain extended select * from t1 +where a in +( +select * +from (values (1),(2)) as tvc_0 +) +and b in +( +select * +from (values (1),(5)) as tvc_1 +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`1` +# subquery with IN-predicate +select * from t1 +where a in +( +select a +from t2 where b in (3,4) +); +a b +4 6 +select * from t1 +where a in +( +select a from t2 +where b in +( +select * +from (values (3),(4)) as tvc_0 +) +); +a b +4 6 +explain extended select * from t1 +where a in +( +select a +from t2 where b in (3,4) +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`3` +explain extended select * from t1 +where a in +( +select a from t2 +where b in +( +select * +from (values (3),(4)) as tvc_0 +) +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`3` +# derived table with IN-predicate +select * from +( +select * +from t1 +where a in (1,2) +) as dr_table; +a b +1 2 +1 1 +2 5 +select * from +( +select * +from t1 +where a in +( +select * +from (values (1),(2)) +as tvc_0 +) +) as dr_table; +a b +1 2 +1 1 +2 5 +explain extended select * from +( +select * +from t1 +where a in (1,2) +) as dr_table; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +explain extended select * from +( +select * +from t1 +where a in +( +select * +from (values (1),(2)) +as tvc_0 +) +) as dr_table; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +# non-recursive CTE with IN-predicate +with tvc_0 as +( +select * +from t1 +where a in (1,2) +) +select * from tvc_0; +a b +1 2 +1 1 +2 5 +select * from +( +select * +from t1 +where a in +( +select * +from (values (1),(2)) +as tvc_0 +) +) as dr_table; +a b +1 2 +1 1 +2 5 +explain extended with tvc_0 as +( +select * +from t1 +where a in (1,2) +) +select * from tvc_0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +explain extended select * from +( +select * +from t1 +where a in +( +select * +from (values (1),(2)) +as tvc_0 +) +) as dr_table; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +# VIEW with IN-predicate +create view v1 as +select * +from t1 +where a in (1,2); +create view v2 as +select * +from t1 +where a in +( +select * +from (values (1),(2)) +as tvc_0 +) +; +select * from v1; +a b +1 2 +1 1 +2 5 +select * from v2; +a b +1 2 +1 1 +2 5 +explain extended select * from v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +explain extended select * from v2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +drop view v1,v2; +# subselect defined by derived table with IN-predicate +select * from t1 +where a in +( +select 1 +from +( +select * +from t1 +where a in (1,2) +) +as dr_table +); +a b +1 2 +1 1 +select * from t1 +where a in +( +select 1 +from +( +select * +from t1 +where a in +( +select * +from (values (1),(2)) +as tvc_0 +) +) +as dr_table +); +a b +1 2 +1 1 +explain extended select * from t1 +where a in +( +select 1 +from +( +select * +from t1 +where a in (1,2) +) +as dr_table +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`1` +explain extended select * from t1 +where a in +( +select 1 +from +( +select * +from t1 +where a in +( +select * +from (values (1),(2)) +as tvc_0 +) +) +as dr_table +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`1` +# derived table with IN-predicate and group by +select * from +( +select max(a),b +from t1 +where b in (3,5) +group by b +) as dr_table; +max(a) b +2 5 +select * from +( +select max(a),b +from t1 +where b in +( +select * +from (values (3),(5)) +as tvc_0 +) +group by b +) as dr_table; +max(a) b +2 5 +explain extended select * from +( +select max(a),b +from t1 +where b in (3,5) +group by b +) as dr_table; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort +2 DERIVED <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `dr_table`.`max(a)` AS `max(a)`,`dr_table`.`b` AS `b` from (/* select#2 */ select max(`test`.`t1`.`a`) AS `max(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(5)) `tvc_0`) where 1 group by `test`.`t1`.`b`) `dr_table` +explain extended select * from +( +select max(a),b +from t1 +where b in +( +select * +from (values (3),(5)) +as tvc_0 +) +group by b +) as dr_table; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort +2 DERIVED <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `dr_table`.`max(a)` AS `max(a)`,`dr_table`.`b` AS `b` from (/* select#2 */ select max(`test`.`t1`.`a`) AS `max(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(5)) `tvc_0`) where 1 group by `test`.`t1`.`b`) `dr_table` +# prepare statement +prepare stmt from "select * from t1 where a in (1,2)"; +execute stmt; +a b +1 2 +1 1 +2 5 +execute stmt; +a b +1 2 +1 1 +2 5 +deallocate prepare stmt; +# use inside out access from tvc rows +set @@in_predicate_conversion_threshold= default; +select * from t3 where a in (1,4,10); +a b +1 abc +1 todd +1 sm +4 yq +10 abc +explain extended select * from t3 where a in (1,4,10); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 range idx idx 5 NULL 5 100.00 Using index condition +Warnings: +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4,10) +set @@in_predicate_conversion_threshold= 2; +select * from t3 where a in (1,4,10); +a b +1 abc +1 todd +1 sm +4 yq +10 abc +explain extended select * from t3 where a in (1,4,10); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t3 ref idx idx 5 tvc_0.1 3 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4),(10)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1` +# use vectors in IN predeicate +set @@in_predicate_conversion_threshold= 4; +select * from t1 where (a,b) in ((1,2),(3,4)); +a b +1 2 +explain extended select * from t1 where (a,b) in ((1,2),(3,4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` and `test`.`t1`.`b` = `tvc_0`.`2` +set @@in_predicate_conversion_threshold= 2; +# trasformation works for the one IN predicate and doesn't work for the other +set @@in_predicate_conversion_threshold= 5; +select * from t2 +where (a,b) in ((1,2),(8,9)) and +(a,c) in ((1,3),(8,0),(5,1)); +a b c +1 2 3 +8 9 0 +explain extended select * from t2 +where (a,b) in ((1,2),(8,9)) and +(a,c) in ((1,3),(8,0),(5,1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`1` and `test`.`t2`.`c` = `tvc_0`.`3` and (`tvc_0`.`1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) +set @@in_predicate_conversion_threshold= 2; +# +# mdev-14281: conversion of NOT IN predicate into subquery predicate +# +select * from t1 +where (a,b) not in ((1,2),(8,9), (5,1)); +a b +4 6 +9 7 +1 1 +2 5 +7 8 +select * from t1 +where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0); +a b +4 6 +9 7 +1 1 +2 5 +7 8 +explain extended select * from t1 +where (a,b) not in ((1,2),(8,9), (5,1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) +explain extended select * from t1 +where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) +select * from t1 +where b < 7 and (a,b) not in ((1,2),(8,9), (5,1)); +a b +4 6 +1 1 +2 5 +explain extended select * from t1 +where b < 7 and (a,b) not in ((1,2),(8,9), (5,1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) +select * from t2 +where (a,c) not in ((1,2),(8,9), (5,1)); +a b c +1 2 3 +5 1 2 +4 3 7 +8 9 0 +10 7 1 +explain extended select * from t2 +where (a,c) not in ((1,2),(8,9), (5,1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),(`test`.`t2`.`a`,`test`.`t2`.`c`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`1` and `test`.`t2`.`c` = `<subquery2>`.`2`)))) +drop table t1, t2, t3; +set @@in_predicate_conversion_threshold= default; +# +# MDEV-14947: conversion of TVC with only NULL values +# +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (3), (2), (7); +SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); +i +EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` where `test`.`t1`.`i` in (NULL,NULL,NULL,NULL,NULL) +SET in_predicate_conversion_threshold= 5; +SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); +i +EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join ((values (NULL),(NULL),(NULL),(NULL),(NULL)) `tvc_0`) where `test`.`t1`.`i` = `tvc_0`.`NULL` +SET in_predicate_conversion_threshold= default; +DROP TABLE t1; +# +# MDEV-14835: conversion of TVC with BIGINT or YEAR values +# +SET @@in_predicate_conversion_threshold= 2; +CREATE TABLE t1 (a BIGINT); +CREATE TABLE t2 (y YEAR); +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (2009), (2010), (2011); +SELECT * FROM t1 WHERE a IN ('1','5','3'); +a +1 +3 +SELECT * FROM t2 WHERE y IN ('2009','2011'); +y +2009 +2011 +DROP TABLE t1,t2; +SET @@in_predicate_conversion_threshold= default; |