diff options
| -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; } }; |