diff options
| -rw-r--r-- | mysql-test/r/opt_tvc.result | 24 | ||||
| -rw-r--r-- | mysql-test/t/opt_tvc.test | 22 | ||||
| -rw-r--r-- | sql/field.h | 2 |
3 files changed, 46 insertions, 2 deletions
diff --git a/mysql-test/r/opt_tvc.result b/mysql-test/r/opt_tvc.result index 4403980c81c..f06669fdf85 100644 --- a/mysql-test/r/opt_tvc.result +++ b/mysql-test/r/opt_tvc.result @@ -614,3 +614,27 @@ 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; diff --git a/mysql-test/t/opt_tvc.test b/mysql-test/t/opt_tvc.test index a13386deb6c..6b5ffae2f70 100644 --- a/mysql-test/t/opt_tvc.test +++ b/mysql-test/t/opt_tvc.test @@ -318,3 +318,25 @@ eval explain extended $query; drop table t1, t2, t3; set @@in_predicate_conversion_threshold= default; + +--echo # +--echo # MDEV-14947: conversion of TVC with only NULL values +--echo # + +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (3), (2), (7); + +let $q= +SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); + +eval $q; +eval EXPLAIN EXTENDED $q; + +SET in_predicate_conversion_threshold= 5; + +eval $q; +eval EXPLAIN EXTENDED $q; + +SET in_predicate_conversion_threshold= default; + +DROP TABLE t1; diff --git a/sql/field.h b/sql/field.h index 549dc3d00d5..2da66bae8bc 100644 --- a/sql/field.h +++ b/sql/field.h @@ -2372,13 +2372,11 @@ public: bool can_optimize_keypart_ref(const Item_bool_func *cond, const Item *item) const { - DBUG_ASSERT(0); return false; } bool can_optimize_group_min_max(const Item_bool_func *cond, const Item *const_item) const { - DBUG_ASSERT(0); return false; } }; |