Below is the list of changes that have just been committed into a local
5.0 repository of psergey. When psergey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2006-11-09 11:31:31+03:00, sergefp@stripped +4 -0
BUG#24085: Wrong result for NULL IN (SELECT not_null_val FROM ...)
When transforming "oe IN (SELECT ie ...)" wrap the pushed-down predicates
iff "oe can be null", not "ie can be null".
The fix doesn't cover row-based subqueries, those will be fixed in #24085.
mysql-test/r/subselect.result@stripped, 2006-11-09 11:31:28+03:00, sergefp@stripped +2 -2
BUG#24085: Wrong result for NULL IN (SELECT not_null_val FROM ...)
Update the test results (checked)
mysql-test/r/subselect3.result@stripped, 2006-11-09 11:31:28+03:00, sergefp@stripped +84 -6
BUG#24085: Wrong result for NULL IN (SELECT not_null_val FROM ...)
- Testcase
mysql-test/t/subselect3.test@stripped, 2006-11-09 11:31:28+03:00, sergefp@stripped +69 -2
BUG#24085: Wrong result for NULL IN (SELECT not_null_val FROM ...)
- Testcase
sql/item_subselect.cc@stripped, 2006-11-09 11:31:28+03:00, sergefp@stripped +25 -14
BUG#24085: Wrong result for NULL IN (SELECT not_null_val FROM ...)
When transforming "oe IN (SELECT ie ...)" we should make special
provisions (wrap the pushed predicates) if we can encounter
NULL IN (SELECT ...), i.e. when oe->maybe_null. The code was checking
for ie->maybe_null instead, fixed it for single value based subqueries.
Row-based subqueries (e.g. (a,b) IN (SELECT c,d ...)) are not fixed
because they won't produce correct results for several other reasons
(filed as #24085)
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: sergefp
# Host: pylon64.mylan
# Root: /home/psergey/mysql-5.0-bug8804-r10
--- 1.167/mysql-test/r/subselect.result 2006-11-09 11:31:37 +03:00
+++ 1.168/mysql-test/r/subselect.result 2006-11-09 11:31:37 +03:00
@@ -744,7 +744,7 @@
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1))) union select 3 AS `3` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3)))))
+Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
id
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
@@ -907,7 +907,7 @@
2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and trigcond(((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)))) having trigcond(<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`
+Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` 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`
drop table t1,t2,t3;
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
--- 1.140/sql/item_subselect.cc 2006-11-09 11:31:37 +03:00
+++ 1.141/sql/item_subselect.cc 2006-11-09 11:31:37 +03:00
@@ -924,7 +924,7 @@
if (!substitution)
{
- //first call for this unit
+ /* We're invoked for the 1st (or the only) SELECT in the subquery UNION */
SELECT_LEX_UNIT *unit= select_lex->master_unit();
substitution= optimizer;
@@ -974,7 +974,7 @@
ref_pointer_array,
(char *)"<ref>",
this->full_name()));
- if (!abort_on_null && ((Item*)select_lex->item_list.head())->maybe_null)
+ if (!abort_on_null && left_expr->maybe_null)
{
/*
We can encounter "NULL IN (SELECT ...)". Wrap the added condition
@@ -1015,9 +1015,13 @@
item= func->create(expr, item);
if (!abort_on_null && orig_item->maybe_null)
{
- having=
- new Item_func_trig_cond(new Item_is_not_null_test(this, having),
- &enable_pushed_conds);
+ having= new Item_is_not_null_test(this, having);
+ if (left_expr->maybe_null)
+ {
+ if (!(having= new Item_func_trig_cond(having,
+ &enable_pushed_conds)))
+ DBUG_RETURN(RES_ERROR);
+ }
/*
Item_is_not_null_test can't be changed during fix_fields()
we can assign select_lex->having here, and pass 0 as last
@@ -1034,16 +1038,19 @@
select_lex->having_fix_field= 0;
if (tmp)
DBUG_RETURN(RES_ERROR);
- /*
- NOTE: It is important that we add this "IS NULL" here, even when
- orig_item can't be NULL. This is needed so that this predicate is
- only used by ref[_or_null] analyzer (and, e.g. is not used by const
- propagation).
- */
item= new Item_cond_or(item,
new Item_func_isnull(orig_item));
- item= new Item_func_trig_cond(item, &enable_pushed_conds);
}
+ /*
+ If we may encounter NULL IN (SELECT ...) and care between NULL and
+ FALSE, wrap it in a trigger.
+ */
+ if (!abort_on_null && left_expr->maybe_null)
+ {
+ if (!(item= new Item_func_trig_cond(item, &enable_pushed_conds)))
+ DBUG_RETURN(RES_ERROR);
+ }
+
item->name= (char *)in_additional_cond;
/*
AND can't be changed during fix_fields()
@@ -1075,9 +1082,13 @@
select_lex->ref_pointer_array,
(char *)"<no matter>",
(char *)"<result>"));
- new_having= new Item_func_trig_cond(new_having, &enable_pushed_conds);
+ if (!abort_on_null && left_expr->maybe_null)
+ {
+ if (!(new_having= new Item_func_trig_cond(new_having,
+ &enable_pushed_conds)))
+ DBUG_RETURN(RES_ERROR);
+ }
select_lex->having= join->having= new_having;
-
select_lex->having_fix_field= 1;
/*
we do not check join->having->fixed, because comparison function
--- 1.1/mysql-test/r/subselect3.result 2006-11-09 11:31:37 +03:00
+++ 1.2/mysql-test/r/subselect3.result 2006-11-09 11:31:37 +03:00
@@ -15,9 +15,8 @@
(4, NULL),
(2, NULL);
select a, oref, a in (select max(ie)
-from t1 where oref=t2.oref group by grp) from t2;
-a oref a in (select max(ie)
-from t1 where oref=t2.oref group by grp)
+from t1 where oref=t2.oref group by grp) Z from t2;
+a oref Z
1 1 1
2 2 0
3 3 NULL
@@ -25,14 +24,13 @@
NULL 2 NULL
explain extended
select a, oref, a in (select max(ie)
-from t1 where oref=t2.oref group by grp) from t2;
+from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie)
-from t1 where oref=t2.oref group by grp)` from `test`.`t2`
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2`
explain extended
select a, oref from t2
where a in (select max(ie) from t1 where oref=t2.oref group by grp);
@@ -42,6 +40,16 @@
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
+select a, oref, a in (
+select max(ie) from t1 where oref=t2.oref group by grp union
+select max(ie) from t1 where oref=t2.oref group by grp
+) Z from t2;
+a oref Z
+1 1 1
+2 2 0
+3 3 NULL
+NULL 4 0
+NULL 2 NULL
create table t3 (a int);
insert into t3 values (NULL), (NULL);
flush status;
@@ -151,3 +159,73 @@
Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
drop table t1, t2, t3;
+create table t1 (a int NOT NULL, b int NOT NULL, key(a));
+insert into t1 values
+(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+create table t2 like t1;
+insert into t2 select * from t1;
+update t2 set b=1;
+create table t3 (a int, oref int);
+insert into t3 values (1, 1), (NULL,1), (NULL,0);
+select a, oref,
+t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
+from t3;
+a oref Z
+1 1 1
+NULL 1 NULL
+NULL 0 0
+This must show a trig_cond:
+explain extended
+select a, oref,
+t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
+from t3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3
+2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 Using where
+2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 Using where
+Warnings:
+Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3`
+drop table t1,t2,t3;
+create table t1 (oref int, grp int);
+insert into t1 (oref, grp) values
+(1, 1),
+(1, 1);
+create table t2 (oref int, a int);
+insert into t2 values
+(1, NULL),
+(2, NULL);
+select a, oref,
+a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
+a oref Z
+NULL 1 NULL
+NULL 2 0
+This must show a trig_cond:
+explain extended
+select a, oref,
+a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+Warnings:
+Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
+drop table t1, t2;
+create table t1 (a int, b int, primary key (a));
+insert into t1 values (1,1), (3,1),(100,1);
+create table t2 (a int, b int);
+insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);
+select a,b, a in (select a from t1 where t1.b = t2.b union select a from
+t1 where t1.b = t2.b) Z from t2 ;
+a b Z
+1 1 1
+2 1 0
+NULL 1 NULL
+NULL 0 0
+select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
+a b Z
+1 1 1
+2 1 0
+NULL 1 NULL
+NULL 0 0
+drop table t1, t2;
--- 1.1/mysql-test/t/subselect3.test 2006-11-09 11:31:37 +03:00
+++ 1.2/mysql-test/t/subselect3.test 2006-11-09 11:31:37 +03:00
@@ -34,18 +34,22 @@
# true, false, null, false, null
select a, oref, a in (select max(ie)
- from t1 where oref=t2.oref group by grp) from t2;
+ from t1 where oref=t2.oref group by grp) Z from t2;
# This must have a trigcond
explain extended
select a, oref, a in (select max(ie)
- from t1 where oref=t2.oref group by grp) from t2;
+ from t1 where oref=t2.oref group by grp) Z from t2;
# This must not have a trigcond:
explain extended
select a, oref from t2
where a in (select max(ie) from t1 where oref=t2.oref group by grp);
+select a, oref, a in (
+ select max(ie) from t1 where oref=t2.oref group by grp union
+ select max(ie) from t1 where oref=t2.oref group by grp
+ ) Z from t2;
# Non-correlated subquery, 2 NULL evaluations
create table t3 (a int);
@@ -134,4 +138,67 @@
from t3;
drop table t1, t2, t3;
+
+
+#
+# BUG#24085
+#
+
+# case 1: NULL IN (SELECT not_null_val FROM ...) w/o HAVING/GROUP-BY/etc
+create table t1 (a int NOT NULL, b int NOT NULL, key(a));
+insert into t1 values
+ (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+
+create table t2 like t1;
+insert into t2 select * from t1;
+update t2 set b=1;
+
+create table t3 (a int, oref int);
+insert into t3 values (1, 1), (NULL,1), (NULL,0);
+select a, oref,
+ t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
+from t3;
+
+--echo This must show a trig_cond:
+explain extended
+select a, oref,
+ t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
+from t3;
+drop table t1,t2,t3;
+
+
+# case 2: NULL IN (SELECT not_null_val FROM) where SELECT has GROUP BY
+create table t1 (oref int, grp int);
+insert into t1 (oref, grp) values
+ (1, 1),
+ (1, 1);
+# Ok, for
+# select count(*) from t1 group by grp having grp=$PARAM$
+# we'll have:
+# 1 -> (2)
+# 2 -> () - nothing
+create table t2 (oref int, a int);
+insert into t2 values
+ (1, NULL),
+ (2, NULL);
+
+select a, oref,
+ a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
+
+--echo This must show a trig_cond:
+explain extended
+select a, oref,
+ a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
+
+drop table t1, t2;
+
+create table t1 (a int, b int, primary key (a));
+insert into t1 values (1,1), (3,1),(100,1);
+create table t2 (a int, b int);
+insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);
+
+select a,b, a in (select a from t1 where t1.b = t2.b union select a from
+t1 where t1.b = t2.b) Z from t2 ;
+select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
+drop table t1, t2;
| Thread |
|---|
| • bk commit into 5.0 tree (sergefp:1.2303) BUG#24085 | Sergey Petrunia | 9 Nov |
