From 76165dc76064ea5942c9f62c16fc4f59264226fc Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sat, 5 Mar 2011 12:56:22 +0300 Subject: BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 - put the code that sets HA_NULL_PART bit back - Fix test_if_ref/part_of_refkey() so that = NULL-ability of lookup columns does not prevent the equality from being removed (we now have early/late NULLs filtering which will filter out NULL values) = equality is not removed if it is ref_or_null access, and the value of the lookup column can alternate between the lookup value and NULL. --- mysql-test/r/null.result | 2 +- mysql-test/r/null_key.result | 26 +++++++++++++++++++------- mysql-test/r/order_by.result | 4 ++-- mysql-test/r/subselect.result | 2 +- mysql-test/r/subselect_no_mat.result | 2 +- mysql-test/r/subselect_no_opts.result | 2 +- mysql-test/r/subselect_no_semijoin.result | 2 +- 7 files changed, 26 insertions(+), 14 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index 897366787f1..7cadeb81c2c 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -170,7 +170,7 @@ insert into t1 select i*2 from t1; insert into t1 values(null); explain select * from t1 where i=2 or i is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null i i 5 const 9 Using index +1 SIMPLE t1 ref_or_null i i 5 const 9 Using where; Using index select count(*) from t1 where i=2 or i is null; count(*) 10 diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 67725c45314..db2f662eeac 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -21,10 +21,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index explain select * from t1 where (a is null or a = 7) and b=7; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index explain select * from t1 where (a is null or a = 7) and b=7 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index; Using filesort +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index @@ -151,7 +151,7 @@ alter table t1 modify b int null; insert into t1 values (7,null), (8,null), (8,7); explain select * from t1 where a = 7 and (b=7 or b is null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using index +1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index select * from t1 where a = 7 and (b=7 or b is null); a b 7 7 @@ -166,7 +166,7 @@ a b NULL 7 explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a a 5 const 5 Using index +1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); a b 7 NULL @@ -192,7 +192,7 @@ a a b explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); a a b 7 7 7 @@ -202,7 +202,7 @@ a a b explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 -1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; a a b 7 7 7 @@ -226,7 +226,7 @@ delete from t1 where a=8; explain select * from t2,t1 where t1.a=t2.a or t1.a is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 -1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using index +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 @@ -447,3 +447,15 @@ a b a b 3 12 0 12 drop table t1, t2; End of 5.0 tests +# +# BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 +# +CREATE TABLE t1 ( +f3 int(11), +f10 varchar(1), +KEY (f3) +); +INSERT INTO t1 VALUES ('9','k'),(NULL,'r'); +SELECT * FROM t1 WHERE (f3 = 83) OR (f10 = 'z' AND f3 IS NULL); +f3 f10 +DROP TABLE t1; diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 50c601b14a8..24a1727b0b9 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -643,7 +643,7 @@ create table t1(a int, b int, index(b)); insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); explain select * from t1 where b=1 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 3 Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort select * from t1 where b=1 or b is null order by a; a b 1 1 @@ -652,7 +652,7 @@ a b 4 NULL explain select * from t1 where b=2 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 4 Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort select * from t1 where b=2 or b is null order by a; a b 3 NULL diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 73d14acd532..a04b26a074f 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -906,7 +906,7 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a) explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 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`.`t1`.`a`>((`test`.`t1`.`a`,(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and (((`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having (`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 486bb03373e..24a9593aea1 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -910,7 +910,7 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a) explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 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`.`t1`.`a`>((`test`.`t1`.`a`,(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and (((`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having (`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 07412a0c72c..0e48c753dfb 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -907,7 +907,7 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a) explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 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`.`t1`.`a`>((`test`.`t1`.`a`,(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and (((`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having (`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 737fd5f2eb5..8808ed15bea 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -907,7 +907,7 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a) explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 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`.`t1`.`a`>((`test`.`t1`.`a`,(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and (((`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having (`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` -- cgit v1.2.1