diff options
author | Igor Babaev <igor@askmonty.org> | 2019-06-17 14:23:10 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2019-06-17 14:23:10 -0700 |
commit | 167da05f554dbe27d16373f6f0b02408ee76dc94 (patch) | |
tree | 98ab99b8cad94a971c5b7cb7a6e0252cef15a192 | |
parent | 039b8782d4794f34c5f0219d8a8d21f6e21d74f1 (diff) | |
download | mariadb-git-167da05f554dbe27d16373f6f0b02408ee76dc94.tar.gz |
MDEV-19790 Wrong result for query with outer join and IS NOT TRUE predicate
in where clause
The classes Item_func_isnottrue and Item_func_isnotfalse inherited the
implementation of the eval_not_null_tables method from the Item_func
class. As a result the not_null_tables_cache was set incorrectly for
the objects of these classes. It led to improper conversion of outer
joins to inner joins when the where clause of the processed query
contained IS NOT TRUE or IS NOT FALSE predicates. The coverted query
in many cases produced a wrong result set.
-rw-r--r-- | mysql-test/r/join_outer.result | 36 | ||||
-rw-r--r-- | mysql-test/r/join_outer_jcl6.result | 36 | ||||
-rw-r--r-- | mysql-test/t/join_outer.test | 25 | ||||
-rw-r--r-- | sql/item_cmpfunc.h | 4 |
4 files changed, 98 insertions, 3 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index d55f11cc2f2..c9767011a36 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -2353,7 +2353,7 @@ t1.b1+'0' t2.b2 + '0' 0 0 1 1 DROP TABLE t1, t2; -set @join_cache_level= @save_join_cache_level; +set @@join_cache_level= @save_join_cache_level; # # MDEV-14779: using left join causes incorrect results with materialization and derived tables # @@ -2418,5 +2418,39 @@ WHERE tb1.pk = 40 ORDER BY tb1.i1; v2 DROP TABLE t1,t2; +# +# MDEV-19790 : IS NOT TRUE / IS NOT FALSE predicates over +# inner tables of outer joins +# +create table t1 (a int); +create table t2 (b int); +insert into t1 values (3), (7), (1); +insert into t2 values (7), (4), (3); +select * from t1 left join t2 on a=b; +a b +3 3 +7 7 +1 NULL +select * from t1 left join t2 on a=b where (b > 3) is not true; +a b +3 3 +1 NULL +explain extended select * from t1 left join t2 on a=b where (b > 3) is not true; +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 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`b` = `test`.`t1`.`a`)) where ((`test`.`t2`.`b` > 3) is not true) +select * from t1 left join t2 on a=b where (b > 3) is not false; +a b +7 7 +1 NULL +explain extended select * from t1 left join t2 on a=b where (b > 3) is not false; +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 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`b` = `test`.`t1`.`a`)) where ((`test`.`t2`.`b` > 3) is not false) +drop table t1,t2; # end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 8a9b395edff..5c5581f3d3d 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -2364,7 +2364,7 @@ t1.b1+'0' t2.b2 + '0' 0 0 1 1 DROP TABLE t1, t2; -set @join_cache_level= @save_join_cache_level; +set @@join_cache_level= @save_join_cache_level; # # MDEV-14779: using left join causes incorrect results with materialization and derived tables # @@ -2429,6 +2429,40 @@ WHERE tb1.pk = 40 ORDER BY tb1.i1; v2 DROP TABLE t1,t2; +# +# MDEV-19790 : IS NOT TRUE / IS NOT FALSE predicates over +# inner tables of outer joins +# +create table t1 (a int); +create table t2 (b int); +insert into t1 values (3), (7), (1); +insert into t2 values (7), (4), (3); +select * from t1 left join t2 on a=b; +a b +7 7 +3 3 +1 NULL +select * from t1 left join t2 on a=b where (b > 3) is not true; +a b +3 3 +1 NULL +explain extended select * from t1 left join t2 on a=b where (b > 3) is not true; +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 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`b` = `test`.`t1`.`a`)) where ((`test`.`t2`.`b` > 3) is not true) +select * from t1 left join t2 on a=b where (b > 3) is not false; +a b +7 7 +1 NULL +explain extended select * from t1 left join t2 on a=b where (b > 3) is not false; +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 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`b` = `test`.`t1`.`a`)) where ((`test`.`t2`.`b` > 3) is not false) +drop table t1,t2; # end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 305421c10d5..bdd61499d78 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1895,7 +1895,7 @@ set @save_join_cache_level= @@join_cache_level; SET @@join_cache_level = 3; SELECT t1.b1+'0' , t2.b2 + '0' FROM t1 LEFT JOIN t2 ON b1 = b2; DROP TABLE t1, t2; -set @join_cache_level= @save_join_cache_level; +set @@join_cache_level= @save_join_cache_level; --echo # --echo # MDEV-14779: using left join causes incorrect results with materialization and derived tables @@ -1959,6 +1959,29 @@ ORDER BY tb1.i1; DROP TABLE t1,t2; +--echo # +--echo # MDEV-19790 : IS NOT TRUE / IS NOT FALSE predicates over +--echo # inner tables of outer joins +--echo # + +create table t1 (a int); +create table t2 (b int); +insert into t1 values (3), (7), (1); +insert into t2 values (7), (4), (3); +select * from t1 left join t2 on a=b; + +let $q= +select * from t1 left join t2 on a=b where (b > 3) is not true; +eval $q; +eval explain extended $q; + +let $q= +select * from t1 left join t2 on a=b where (b > 3) is not false; +eval $q; +eval explain extended $q; + +drop table t1,t2; + --echo # end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index b10dc2b8056..2eafa0b78da 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -183,6 +183,8 @@ public: Item_func_isnottrue(Item *a) : Item_func_truth(a, true, false) {} ~Item_func_isnottrue() {} virtual const char* func_name() const { return "isnottrue"; } + bool eval_not_null_tables(uchar *opt_arg) + { not_null_tables_cache= 0; return false; } }; @@ -209,6 +211,8 @@ public: Item_func_isnotfalse(Item *a) : Item_func_truth(a, false, false) {} ~Item_func_isnotfalse() {} virtual const char* func_name() const { return "isnotfalse"; } + bool eval_not_null_tables(uchar *opt_arg) + { not_null_tables_cache= 0; return false; } }; |