MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Sergey Petrunia Date:January 12 2007 7:11pm
Subject:bk commit into 5.0 tree (sergefp:1.2375) BUG#24085
View as plain text  
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, 2007-01-12 22:11:40+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 #24127.

  mysql-test/r/subselect.result@stripped, 2007-01-12 22:11:37+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, 2007-01-12 22:11:37+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, 2007-01-12 22:11:37+03:00, sergefp@stripped +69 -2
    BUG#24085: Wrong result for NULL IN (SELECT not_null_val FROM ...)
    - Testcase

  sql/item_subselect.cc@stripped, 2007-01-12 22:11:37+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:	pylon.mylan
# Root:	/home/psergey/mysql-5.0-bug8804-r12

--- 1.167/mysql-test/r/subselect.result	2007-01-12 22:11:45 +03:00
+++ 1.168/mysql-test/r/subselect.result	2007-01-12 22:11:45 +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.146/sql/item_subselect.cc	2007-01-12 22:11:45 +03:00
+++ 1.147/sql/item_subselect.cc	2007-01-12 22:11:45 +03:00
@@ -922,7 +922,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;
 
@@ -972,7 +972,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
@@ -1013,9 +1013,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
@@ -1032,16 +1036,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()
@@ -1073,9 +1080,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	2007-01-12 22:11:45 +03:00
+++ 1.2/mysql-test/r/subselect3.result	2007-01-12 22:11:45 +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	2007-01-12 22:11:45 +03:00
+++ 1.2/mysql-test/t/subselect3.test	2007-01-12 22:11:45 +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.2375) BUG#24085Sergey Petrunia12 Jan