#At file:///home/mysql_src/bzrrepos_new/opt-back-54608/ based on revid:guilhem@stripped
3210 Guilhem Bichot 2010-07-04
Fix for BUG#54608 "Query using IN + OR + IS TRUE and IS NULL returns NULL when should be empty"
selection condition was wrongly assumed to be satisfied by ref_or_null
@ mysql-test/include/null_key.inc
test for bug; without the code fix, the two SELECTs would return NULL instead of
empty set.
@ sql/sql_select.cc
In the test's scenario, the WHERE condition is
a IN (42) OR (a IS TRUE AND a IS NULL) .
If this condition is true then so is
a IN (42) OR a IS NULL ,
thus ref_or_null access is correctly chosen to access t1.
But then it is wrongly assumed that this ref_or_null access makes
checking the entire condition unneeded. Here's how:
- make_join_select() wants to attach conditions to tables (i.e. push as
much as possible of the WHERE clause as deep as possible in the operator
tree)
- for that it calls
make_cond_for_table_from_pred("a IN (42) OR (a IS TRUE AND a IS NULL)")
("make a condition to be attached to the table, from this
predicate"). That function sees that the condition is "cond1 OR cond2";
it calls make_cond_for_table_from_pred() on cond1 and on cond2, to later
recombine the resulting conditions with an OR. Here cond1 is
"a IN (42)", cond2 is "a IS TRUE AND a IS NULL".
- make_cond_for_table_from_pred(cond1) calls test_if_ref()
- test_if_ref() sees that in the ref_or_null access, "a" is the field
used for index lookup, 42 is the lookup value, and so any found record
automatically satisfies "a IN (42)"
- thus make_cond_for_table_from_pred(cond1) believes that cond1 needn't
be checked (is guaranteed to be true by the use of ref_or_null)
- thus the top-level make_cond_for_table_from_pred() believes that
"cond1 OR cond2" == "TRUE or cond2" thus needn't be checked
- what test_if_ref() forgot is that 42 is not the _only_ lookup value:
at execution, ref_or_null will look up "42" _and_ also look up
NULL. ref_or_null does not implement "a IN (42)" but
"a IN (42) OR a IS NULL". So a record returned by index lookup could
possibly have a being NULL and not satisfy "a IN (42)". Thus checking
"a IN (42)" after ref_or_null is still needed!
The fix: test_if_ref() should not say that x=y can be replaced by
ref_or_null access.
Note: this does cause plan changes, where many "Using where" now appear
in EXPLAIN for "ref_or_null" access. This actually makes plans closer
to what they are in next-mr. next-mr-opt-backporting had lost the "Using
where" for many tests (and the bug was introduced) in that commit:
sp1r-sergefp@stripped
The intention of that commit looks correct: there was some code which
made part_of_refkey() pretend that the ref/ref_or_null access referred
to no item if the index was on a nullable column; that commit removed
this code. Apparently it accidentally exposed a bug in the logic of
test_if_ref().
Undoing this Sergey's commit, though it would restore all next-mr plans,
would have stronger effects than the present patch: for example it would
add "Using where" for a ref access using an index on a nullable column:
innodb.innodb_mysql w8 [ fail ]
# query sourced from mix1.inc:
EXPLAIN
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index
-1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index
+1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index
and that would be a superfluous "using where". Whereas the current patch
affects only ref_or_null.
Note2: the current patch restores corrects results, but there would
be room for future improvements: when the condition is as simple as
key_col=constant OR key_col IS NULL,
and access method is "ref_or_null", the Optimizer, when attaching this
condition to the table, asks itself two questions (one call to
make_cond_for_table_from_pred() for each):
- does the access method guarantee key_col=constant?
- does the access method guarantee key_col IS NULL?
The correct answer is "no" for both, thus the plan has "using
where". But the access method does guarantee the OR-combined condition,
and so in reality "using where" is superfluous and could be dropped. The
Optimizer does not see it because it treats the above two questions
independently.
modified:
mysql-test/include/null_key.inc
mysql-test/r/null.result
mysql-test/r/null_key_all.result
mysql-test/r/null_key_icp.result
mysql-test/r/null_key_none.result
mysql-test/r/order_by_all.result
mysql-test/r/order_by_icp_mrr.result
mysql-test/r/order_by_none.result
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_jcl6.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_jcl6.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_jcl6.result
sql/sql_select.cc
=== modified file 'mysql-test/include/null_key.inc'
--- a/mysql-test/include/null_key.inc 2010-06-18 08:45:53 +0000
+++ b/mysql-test/include/null_key.inc 2010-07-04 15:07:55 +0000
@@ -263,3 +263,15 @@ SELECT * FROM t2 inner join t1 WHERE ( t
drop table t1, t2;
-- echo End of 5.0 tests
+--echo #
+--echo # Bug#54608 Query using IN + OR + IS TRUE and IS NULL returns
+--echo # NULL when should be empty
+--echo #
+
+CREATE TABLE t1 (a INT, KEY (a));
+INSERT INTO t1 VALUES (1), (2), (NULL);
+explain SELECT a FROM t1 WHERE a IN (42) OR (a IS TRUE AND a IS NULL);
+SELECT a FROM t1 WHERE a IN (42) OR (a IS TRUE AND a IS NULL);
+explain SELECT a FROM t1 WHERE a IN (42) OR (a=NULL);
+SELECT a FROM t1 WHERE a IN (42) OR (a=NULL);
+drop table t1;
=== modified file 'mysql-test/r/null.result'
--- a/mysql-test/r/null.result 2010-06-19 13:22:14 +0000
+++ b/mysql-test/r/null.result 2010-07-04 15:07:55 +0000
@@ -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
=== modified file 'mysql-test/r/null_key_all.result'
--- a/mysql-test/r/null_key_all.result 2010-06-18 08:45:53 +0000
+++ b/mysql-test/r/null_key_all.result 2010-07-04 15:07:55 +0000
@@ -22,10 +22,10 @@ id select_type table type possible_keys
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
@@ -152,7 +152,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
@@ -167,7 +167,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
@@ -193,7 +193,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
@@ -203,7 +203,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
@@ -227,7 +227,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
@@ -448,4 +448,21 @@ a b a b
3 12 0 12
drop table t1, t2;
End of 5.0 tests
+#
+# Bug#54608 Query using IN + OR + IS TRUE and IS NULL returns
+# NULL when should be empty
+#
+CREATE TABLE t1 (a INT, KEY (a));
+INSERT INTO t1 VALUES (1), (2), (NULL);
+explain SELECT a FROM t1 WHERE a IN (42) OR (a IS TRUE AND 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 2 Using where; Using index
+SELECT a FROM t1 WHERE a IN (42) OR (a IS TRUE AND a IS NULL);
+a
+explain SELECT a FROM t1 WHERE a IN (42) OR (a=NULL);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref_or_null a a 5 const 2 Using where; Using index
+SELECT a FROM t1 WHERE a IN (42) OR (a=NULL);
+a
+drop table t1;
set optimizer_switch=default;
=== modified file 'mysql-test/r/null_key_icp.result'
--- a/mysql-test/r/null_key_icp.result 2010-06-18 08:45:53 +0000
+++ b/mysql-test/r/null_key_icp.result 2010-07-04 15:07:55 +0000
@@ -22,10 +22,10 @@ id select_type table type possible_keys
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
@@ -152,7 +152,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
@@ -167,7 +167,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
@@ -193,7 +193,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
@@ -203,7 +203,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
@@ -227,7 +227,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
@@ -448,4 +448,21 @@ a b a b
3 12 0 12
drop table t1, t2;
End of 5.0 tests
+#
+# Bug#54608 Query using IN + OR + IS TRUE and IS NULL returns
+# NULL when should be empty
+#
+CREATE TABLE t1 (a INT, KEY (a));
+INSERT INTO t1 VALUES (1), (2), (NULL);
+explain SELECT a FROM t1 WHERE a IN (42) OR (a IS TRUE AND 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 2 Using where; Using index
+SELECT a FROM t1 WHERE a IN (42) OR (a IS TRUE AND a IS NULL);
+a
+explain SELECT a FROM t1 WHERE a IN (42) OR (a=NULL);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref_or_null a a 5 const 2 Using where; Using index
+SELECT a FROM t1 WHERE a IN (42) OR (a=NULL);
+a
+drop table t1;
set optimizer_switch=default;
=== modified file 'mysql-test/r/null_key_none.result'
--- a/mysql-test/r/null_key_none.result 2010-06-18 08:45:53 +0000
+++ b/mysql-test/r/null_key_none.result 2010-07-04 15:07:55 +0000
@@ -21,10 +21,10 @@ id select_type table type possible_keys
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,4 +447,21 @@ a b a b
3 12 0 12
drop table t1, t2;
End of 5.0 tests
+#
+# Bug#54608 Query using IN + OR + IS TRUE and IS NULL returns
+# NULL when should be empty
+#
+CREATE TABLE t1 (a INT, KEY (a));
+INSERT INTO t1 VALUES (1), (2), (NULL);
+explain SELECT a FROM t1 WHERE a IN (42) OR (a IS TRUE AND 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 2 Using where; Using index
+SELECT a FROM t1 WHERE a IN (42) OR (a IS TRUE AND a IS NULL);
+a
+explain SELECT a FROM t1 WHERE a IN (42) OR (a=NULL);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref_or_null a a 5 const 2 Using where; Using index
+SELECT a FROM t1 WHERE a IN (42) OR (a=NULL);
+a
+drop table t1;
set optimizer_switch=default;
=== modified file 'mysql-test/r/order_by_all.result'
--- a/mysql-test/r/order_by_all.result 2010-06-25 09:34:37 +0000
+++ b/mysql-test/r/order_by_all.result 2010-07-04 15:07:55 +0000
@@ -639,7 +639,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
@@ -648,7 +648,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
=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result 2010-06-25 09:34:37 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result 2010-07-04 15:07:55 +0000
@@ -639,7 +639,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
@@ -648,7 +648,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
=== modified file 'mysql-test/r/order_by_none.result'
--- a/mysql-test/r/order_by_none.result 2010-06-25 09:34:37 +0000
+++ b/mysql-test/r/order_by_none.result 2010-07-04 15:07:55 +0000
@@ -638,7 +638,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 where; Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -647,7 +647,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 where; Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result 2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_all.result 2010-07-04 15:07:55 +0000
@@ -904,7 +904,7 @@ a t1.a in (select t2.a from t2,t3 where
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 (BNL, regular buffers)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
=== modified file 'mysql-test/r/subquery_all_jcl6.result'
--- a/mysql-test/r/subquery_all_jcl6.result 2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_all_jcl6.result 2010-07-04 15:07:55 +0000
@@ -908,7 +908,7 @@ a t1.a in (select t2.a from t2,t3 where
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 (BNL, incremental buffers)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result 2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result 2010-07-04 15:07:55 +0000
@@ -904,7 +904,7 @@ a t1.a in (select t2.a from t2,t3 where
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 (BNL, regular buffers)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
=== modified file 'mysql-test/r/subquery_nomat_nosj_jcl6.result'
--- a/mysql-test/r/subquery_nomat_nosj_jcl6.result 2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_jcl6.result 2010-07-04 15:07:55 +0000
@@ -908,7 +908,7 @@ a t1.a in (select t2.a from t2,t3 where
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 (BNL, incremental buffers)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result 2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_none.result 2010-07-04 15:07:55 +0000
@@ -903,7 +903,7 @@ a t1.a in (select t2.a from t2,t3 where
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 (BNL, regular buffers)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
=== modified file 'mysql-test/r/subquery_none_jcl6.result'
--- a/mysql-test/r/subquery_none_jcl6.result 2010-06-23 09:00:02 +0000
+++ b/mysql-test/r/subquery_none_jcl6.result 2010-07-04 15:07:55 +0000
@@ -907,7 +907,7 @@ a t1.a in (select t2.a from t2,t3 where
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 (BNL, incremental buffers)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-07-02 11:19:19 +0000
+++ b/sql/sql_select.cc 2010-07-04 15:07:55 +0000
@@ -18295,7 +18295,9 @@ static bool test_if_ref(Item *root_cond,
// No need to change const test
if (!field->table->const_table && join_tab &&
(!join_tab->first_inner ||
- *join_tab->first_inner->on_expr_ref == root_cond))
+ *join_tab->first_inner->on_expr_ref == root_cond) &&
+ /* "ref_or_null" implements "x=y or x is null", not "x=y" */
+ (join_tab->type != JT_REF_OR_NULL))
{
Item *ref_item=part_of_refkey(field->table,field);
if (ref_item && ref_item->eq(right_item,1))
Attachment: [text/bzr-bundle] bzr/guilhem@mysql.com-20100704150755-nkfwvq48d1bld7yj.bundle
| Thread |
|---|
| • bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3210) Bug#54608 | Guilhem Bichot | 8 Jul |
