diff options
Diffstat (limited to 'mysql-test/r/opt_tvc.result')
| -rw-r--r-- | mysql-test/r/opt_tvc.result | 58 |
1 files changed, 50 insertions, 8 deletions
diff --git a/mysql-test/r/opt_tvc.result b/mysql-test/r/opt_tvc.result index 3b03bd4ffaf..0ecae5bf157 100644 --- a/mysql-test/r/opt_tvc.result +++ b/mysql-test/r/opt_tvc.result @@ -27,7 +27,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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_subquery_conversion_threshold= 2; +set @@in_predicate_conversion_threshold= 2; # single IN-predicate in WHERE-part select * from t1 where a in (1,2); a b @@ -485,7 +485,7 @@ a b 2 5 deallocate prepare stmt; # use inside out access from tvc rows -set @@in_subquery_conversion_threshold= default; +set @@in_predicate_conversion_threshold= default; select * from t3 where a in (1,4,10); a b 1 abc @@ -498,7 +498,7 @@ 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_subquery_conversion_threshold= 2; +set @@in_predicate_conversion_threshold= 2; select * from t3 where a in (1,4,10); a b 1 abc @@ -515,7 +515,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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_subquery_conversion_threshold= 4; +set @@in_predicate_conversion_threshold= 4; select * from t1 where (a,b) in ((1,2),(3,4)); a b 1 2 @@ -527,9 +527,9 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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_subquery_conversion_threshold= 2; +set @@in_predicate_conversion_threshold= 2; # trasformation works for the one IN predicate and doesn't work for the other -set @@in_subquery_conversion_threshold= 5; +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)); @@ -546,7 +546,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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_subquery_conversion_threshold= 2; +set @@in_predicate_conversion_threshold= 2; # # mdev-14281: conversion of NOT IN predicate into subquery predicate # @@ -613,4 +613,46 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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_subquery_conversion_threshold= default; +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; |