List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:July 6 2010 1:01pm
Subject:Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3207)
Bug#54608
View as plain text  
Hi Guilhem,

Thanks for the great explanation of the problem and the fix in the 
commit message. The fix looks correct.

OK to push.

Olav


On 07/04/10 17:07, Guilhem Bichot wrote:
> #At file:///home/mysql_src/bzrrepos_new/opt-back-54608/ based on
> revid:epotemkin@stripped
>
>   3207 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-06-30 13:09:43 +0000
> +++ b/sql/sql_select.cc	2010-07-04 15:07:55 +0000
> @@ -18295,7 +18295,9 @@ static bool test_if_ref(COND *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))
>
>
>
>
>

Thread
bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3207) Bug#54608Guilhem Bichot4 Jul
Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3207)Bug#54608Olav Sandstaa6 Jul
Re: bzr commit into mysql-next-mr-opt-backporting branch(guilhem:3207) Bug#54608Roy Lyseng8 Jul
  • Re: bzr commit into mysql-next-mr-opt-backporting branch(guilhem:3207) Bug#54608Guilhem Bichot8 Jul