List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:July 8 2010 12:48pm
Subject:bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3210) Bug#54608
View as plain text  
#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#54608Guilhem Bichot8 Jul