From: Date: November 9 2006 9:31am Subject: bk commit into 5.0 tree (sergefp:1.2303) BUG#24085 List-Archive: http://lists.mysql.com/commits/15067 X-Bug: 24085 Message-Id: <20061109083137.33D16BBC002@pylon64.mylan> 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 ALL NULL NULL NULL NULL NULL Warnings: -Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id`,(select 1 AS `1` having trigcond(((`test`.`t2`.`id`) = (1))) union select 3 AS `3` having trigcond(((`test`.`t2`.`id`) = (3))))) +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id`,(select 1 AS `1` having ((`test`.`t2`.`id`) = (1)) union select 3 AS `3` having ((`test`.`t2`.`id`) = (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`,(`test`.`t1`.`a`,(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and trigcond((((`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)))) having trigcond((`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`,(`test`.`t1`.`a`,(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and (((`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having (`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` 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 *)"", 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 *)"", (char *)"")); - 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`,(`test`.`t2`.`a`,(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(((`test`.`t2`.`a`) = (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`,(`test`.`t2`.`a`,(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(((`test`.`t2`.`a`) = (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 (`test`.`t2`.`a`,(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having ((`test`.`t2`.`a`) = (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`,(`test`.`t3`.`a`,(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((((`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond((`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`,(`test`.`t3`.`a`,(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(((`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`,(`test`.`t2`.`a`,(select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond(((`test`.`t2`.`a`) = (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;