diff options
Diffstat (limited to 'mysql-test/main/opt_tvc.result')
| -rw-r--r-- | mysql-test/main/opt_tvc.result | 761 |
1 files changed, 761 insertions, 0 deletions
diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result new file mode 100644 index 00000000..eaf75ed7 --- /dev/null +++ b/mysql-test/main/opt_tvc.result @@ -0,0 +1,761 @@ +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 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 <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 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 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 <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 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 t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +4 MATERIALIZED <derived5> 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 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 t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +4 MATERIALIZED <derived5> 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 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`.`_col_1` +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 t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <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 `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 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 t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <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 `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 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 t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <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 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 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 t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <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 `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 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 t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <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 `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 +explain extended select * from v2; +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 <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 `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 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`.`_col_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); +a b +1 abc +1 todd +1 sm +4 yq +explain extended select * from t3 where a in (1,4); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 range idx idx 5 NULL 4 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) +set @@in_predicate_conversion_threshold= 2; +select * from t3 where a in (1,4); +a b +1 abc +1 todd +1 sm +4 yq +explain extended select * from t3 where a in (1,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 t3 ref idx idx 5 tvc_0._col_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)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`_col_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 t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 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`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where 1 +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 t2 ALL NULL NULL NULL NULL 6 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 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`,`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 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key +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`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`_col_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 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key +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`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`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 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key +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`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`_col_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 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key +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`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t2`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t2`.`c`) = `tvc_0`.`_col_2`))))) +drop table t1, t2, t3; +set @@in_predicate_conversion_threshold= default; +# +# MDEV-14947: conversion to 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 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= default; +DROP TABLE t1; +# +# MDEV-14835: conversion to 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; +# +# MDEV-17222: conversion to TVC with no names for constants +# conversion to TVC with the same constants in the first row +# +SET @@in_predicate_conversion_threshold= 2; +CREATE TABLE t1 (f BINARY(16)) ENGINE=MYISAM; +INSERT INTO t1 VALUES +(x'BAE56AF2B1C2397D99D58E2A06761DDB'), (x'9B9B698BCCB939EE8F1EA56C1A2E5DAA'), +(x'A0A1C4FE39A239BABD3E0D8985E6BEA5'); +SELECT COUNT(*) FROM t1 WHERE f IN +(x'9B9B698BCCB939EE8F1EA56C1A2E5DAA', x'E2362DBAB5EA30B5804917A0A7D881E2', +x'B78B2EEAD13635088D93EA3309E24802', x'BAE56AF2B1C2397D99D58E2A06761DDB'); +COUNT(*) +2 +CREATE TABLE t2 (f1 BINARY(16), f2 BINARY(16)) ENGINE=MYISAM; +INSERT INTO t2 VALUES +(x'55FB3B14D6B83D39859E42533906350D', x'00F3458C47FA39DDBEAD918A13F8342E'), +(x'86052C062AAF368D84247ED0F6346A70', x'BF5C35045C6037C79E11026ABB9A3A4E'); +SELECT COUNT(*) FROM t2 WHERE (f1,f2) IN +((x'9B9B698BCCB939EE8F1EA56C1A2E5DAA', x'E2362DBAB5EA30B5804917A0A7D881E2'), +(x'B78B2EEAD13635088D93EA3309E24802', x'BAE56AF2B1C2397D99D58E2A06761DDB'), +(x'55FB3B14D6B83D39859E42533906350D', x'00F3458C47FA39DDBEAD918A13F8342E'), +(x'1606014E7C4A312F83EDC9D91BBFCACA', x'33F6068E56FD3A1D8326517F0D81CB5A')); +COUNT(*) +1 +CREATE TABLE t3 (f1 int, f2 int) ENGINE=MYISAM; +INSERT INTO t3 VALUES (2,5), (2,3), (1,2), (7,8), (1,1); +SELECT * FROM t3 WHERE (f1,f2) IN ((2, 2), (1, 2), (3, 5), (1, 1)); +f1 f2 +1 2 +1 1 +DROP TABLE t1,t2,t3; +SET @@in_predicate_conversion_threshold= default; +# +# MDEV-20900: IN predicate to IN subquery conversion causes performance regression +# +create table t1(a int, b int); +insert into t1 select seq-1, seq-1 from seq_1_to_10; +set in_predicate_conversion_threshold=2; +explain select * from t1 where t1.a IN ("1","2","3","4"); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +select * from t1 where t1.a IN ("1","2","3","4"); +a b +1 1 +2 2 +3 3 +4 4 +set in_predicate_conversion_threshold=0; +explain select * from t1 where t1.a IN ("1","2","3","4"); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +select * from t1 where t1.a IN ("1","2","3","4"); +a b +1 1 +2 2 +3 3 +4 4 +set in_predicate_conversion_threshold=2; +explain select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); +a b +1 1 +2 2 +3 3 +4 4 +set in_predicate_conversion_threshold=0; +explain select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); +a b +1 1 +2 2 +3 3 +4 4 +drop table t1; +SET @@in_predicate_conversion_threshold= default; +# +# MDEV-27937: Prepared statement with ? in the list if IN predicate +# +set in_predicate_conversion_threshold=2; +create table t1 (id int, a int, b int); +insert into t1 values (1,3,30), (2,7,70), (3,1,10); +prepare stmt from " +select * from t1 where a in (7, ?, 5, 1); +"; +execute stmt using 3; +id a b +1 3 30 +2 7 70 +3 1 10 +deallocate prepare stmt; +prepare stmt from " +select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10)); +"; +execute stmt using 30; +id a b +1 3 30 +2 7 70 +3 1 10 +deallocate prepare stmt; +drop table t1; +set in_predicate_conversion_threshold=default; +# End of 10.3 tests |