Bug #46743 Azalea processing correlated, aggregate SELECT subqueries incorrectly

• Submitted: 15 Aug 2009 17:14
• Modified: 23 Nov 2010 2:57
• Reporter: Patrick Crews
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.4/6.0
• OS: Any
• Assigned to: Guilhem Bichot
• CPU Architecture: Any
• Tags: aggregate, Correlated Subquery, GROUP BY, regression, SELECT subquery, subquery

[15 Aug 2009 17:14] Patrick Crews

Description:
Azalea seems to be processing certain subqueries in the SELECT clause of queries incorrectly:

For this query (in the attached test case), azalea is returning a value for the SELECT SUM() subquery while mysql-5.1 returns NULL.  From my experiments, it appears that 5.1 is processing things correctly.

This does *not* appear to be affected by optimizer_switch, optimizer_use_mrr, or join_cache_level.

SELECT table2 .`int_key` field12  , (  
SELECT  SUM( `int_nokey`  )  
FROM D  
WHERE `int_key`  = table1 .`int_key`  )  
FROM C table1  RIGHT  JOIN C table2  ON table1 .`varchar_nokey`  
GROUP  BY field12   ;

/* Diff: */ << 5.1 returns NULL, azalea returns 36

/* --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen39076-1250350990-server0.dump	2009-08-15 11:43:10.000000000 -0400
# +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen39076-1250350990-server1.dump	2009-08-15 11:43:10.000000000 -0400
# @@ -1,11 +1,11 @@
# -0	36
# -1	36
# -166	36
# -2	36
# -3	36
# -5	36
# -53	36
# -6	36
# -8	36
# -9	36
# -NULL	36
# +0	NULL
# +1	NULL
# +166	NULL
# +2	NULL
# +3	NULL
# +5	NULL
# +53	NULL
# +6	NULL
# +8	NULL
# +9	NULL
# +NULL	NULL */

How to repeat:
Simplified MTR test case.
The full test is attached as a separate file (this query + the original, more complex version)

Please comment / uncomment the appropriate Server 0 / Server 1 lines depending on the version of the server you are using.

I currently have this commented out for use on azalea:

/* Server0: version = 5.4.4-alpha-debug-log */
/* Server1: version = 5.1.38-debug-log */

/* The value of optimizer_switch is distinct between the two servers: */
SET SESSION optimizer_switch = 'firstmatch=on,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=on,materialization=off,semijoin=on' ;
#/* Server 1 : SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on' */;

/* The value of optimizer_use_mrr is distinct between the two servers: */
 SET SESSION optimizer_use_mrr = 'force' ;
#/* Server 1 : SET SESSION optimizer_use_mrr = '' */;

/* The value of engine_condition_pushdown is common between the two servers: */
/*!50400 SET SESSION engine_condition_pushdown = ON */;

/* The value of join_cache_level is distinct between the two servers: */
SET SESSION join_cache_level = 1; 
#/* Server 1 : SET SESSION join_cache_level = '' */;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ C;
DROP TABLE /*! IF EXISTS */ D;
--enable_warnings

CREATE TABLE `C` (
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (7,9,'m'),(9,3,'m'),(7,9,'k'),(4,NULL,'r'),(2,9,'t'),(6,3,'j'),(8,8,'u'),(NULL,8,'h'),(5,53,'o'),(NULL,0,NULL),(6,5,'k'),(188,166,'e'),(2,3,'n'),(1,0,'t'),(1,1,'c'),(0,9,'m'),(9,5,'y'),(NULL,6,'f'),(4,2,'d'),(6,NULL,'r');
CREATE TABLE `D` (
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (8,0,'c'),(6,0,'o'),(6,7,'c'),(3,8,'d'),(9,4,'v'),(2,6,'m'),(1,5,'j'),(8,NULL,'f'),(0,NULL,'n'),(9,8,'z'),(8,8,'h'),(NULL,8,'q'),(0,1,'w'),(5,1,'z'),(1,5,'j'),(1,2,'a'),(6,7,'m'),(6,6,'n'),(1,4,'e'),(8,7,'u'),(1,0,'s'),(0,9,'u'),(4,3,'r'),(9,5,'g'),(8,1,'o'),(5,1,'w'),(9,5,'b'),(5,9,NULL),(NULL,2,'y'),(NULL,5,'y'),(105,248,'u'),(0,0,'p'),(3,8,'s'),(1,1,'e'),(75,255,'d'),(9,9,'d'),(7,9,'c'),(NULL,3,'b'),(NULL,9,'t'),(4,6,NULL),(0,4,'y'),(204,60,'c'),(0,7,'d'),(9,1,'x'),(8,6,'p'),(7,4,'e'),(8,NULL,'g'),(NULL,8,'x'),(6,0,'s'),(5,8,'e'),(2,151,'l'),(3,7,'p'),(7,6,'h'),(NULL,NULL,'m'),(145,23,'n'),(0,2,'v'),(1,4,'b'),(7,NULL,'x'),(3,NULL,'r'),(NULL,77,'t'),(2,NULL,'w'),(2,NULL,'w'),(2,7,'k'),(8,1,'a'),(6,9,'t'),(1,6,'z'),(NULL,2,'e'),(1,3,'q'),(0,0,'e'),(4,NULL,'v'),(1,6,'d'),(1,3,'u'),(27,195,'o'),(4,5,'b'),(6,2,'c'),(2,7,'q'),(248,25,NULL),(NULL,NULL,'h'),(9,0,'d'),(75,98,'w'),(2,6,'m'),(9,5,'i'),(4,0,'w'),(0,3,'f'),(0,1,'k'),(1,1,'v'),(119,147,'c'),(1,3,'y'),(7,3,'h'),(2,NULL,NULL),(7,2,'t'),(2,1,'l'),(6,8,'a'),(4,8,'r'),(5,8,'s'),(7,0,'z'),(1,1,'j'),(7,8,'c'),(2,5,'f'),(1,4,'g');

 
SELECT table2 .`int_key` field12  , (  
SELECT  SUM( `int_nokey`  )  
FROM D  
WHERE `int_key`  = table1 .`int_key`  )  
FROM C table1  RIGHT  JOIN C table2  ON table1 .`varchar_nokey`  
GROUP  BY field12   ;

/* Diff: */

/* --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen39076-1250350990-server0.dump	2009-08-15 11:43:10.000000000 -0400
# +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen39076-1250350990-server1.dump	2009-08-15 11:43:10.000000000 -0400
# @@ -1,11 +1,11 @@
# -0	36
# -1	36
# -166	36
# -2	36
# -3	36
# -5	36
# -53	36
# -6	36
# -8	36
# -9	36
# -NULL	36
# +0	NULL
# +1	NULL
# +166	NULL
# +2	NULL
# +3	NULL
# +5	NULL
# +53	NULL
# +6	NULL
# +8	NULL
# +9	NULL
# +NULL	NULL */

DROP TABLE C;
DROP TABLE D;
#/* End of test case for query 0 */

Suggested fix:
Ensure correct query processing.

[15 Aug 2009 17:15] Patrick Crews

Full MTR test case with original and simplified queries

Attachment: bug46743_test.txt (text/plain), 17.41 KiB.

[14 Sep 2009 20:06] Guilhem Bichot

Regarding the first problematic query:

SELECT table2 .`int_key` field12  , (  
SELECT  SUM( `int_nokey`  )  
FROM D  
WHERE `int_key`  = table1 .`int_key`  )  
FROM C table1  RIGHT  JOIN C table2  ON table1 .`varchar_nokey`  
GROUP  BY field12  ;
The GROUP BY only selects one row per distinct value of field12; if this GROUP BY is removed results become correct (NULL instead of a number; like in 5.1).

The same is true for the second problematic query: removing
GROUP BY field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12, field13, field14
makes the result become correct.

[14 Sep 2009 20:07] Guilhem Bichot

test which runs unchanged on 5.1 and mysql-next

Attachment: bug46743.test (application/octet-stream, text), 16.90 KiB.

[15 Sep 2009 15:11] Guilhem Bichot

minimal testcase for the *first* query *only*

Attachment: bug46473_minimal_first_query_only.test (application/octet-stream, text), 1.87 KiB.

[15 Sep 2009 15:12] Guilhem Bichot

result difference with the above test, between 5.1 and next-bugfixing:
@@ -22,9 +22,10 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	table2	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	table1	system	NULL	NULL	NULL	NULL	1	100.00	
-2	DEPENDENT SUBQUERY	D	ref	int_key,varchar_key	int_key	5	const	1	100.00	Using where
+2	DEPENDENT SUBQUERY	D	ref	int_key,varchar_key	int_key	5	const	1	100.00	
 Warnings:
 Note	1276	Field or reference 'test.table1.int_key' of SELECT #2 was resolved in SELECT #1
+Warning	1292	Truncated incorrect INTEGER value: 'm'
 Note	1003	select '3' AS `field12`,(select sum(`test`.`D`.`int_nokey`) AS `SUM( ``int_nokey``  )` from `test`.`D` where (`test`.`D`.`int_key` = '3')) AS `(  
 SELECT  SUM( ``int_nokey``  )  
 FROM D  
@@ -32,6 +33,7 @@
 SHOW WARNINGS;
 Level	Code	Message
 Note	1276	Field or reference 'test.table1.int_key' of SELECT #2 was resolved in SELECT #1
+Warning	1292	Truncated incorrect INTEGER value: 'm'
 Note	1003	select '3' AS `field12`,(select sum(`test`.`D`.`int_nokey`) AS `SUM( ``int_nokey``  )` from `test`.`D` where (`test`.`D`.`int_key` = '3')) AS `(  
 SELECT  SUM( ``int_nokey``  )  
 FROM D  
@@ -46,6 +48,8 @@
 FROM D  
 WHERE `int_key`  = table1 .`int_key`  )
 3	NULL
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'm'
 EXPLAIN EXTENDED SELECT table2 .`int_key` field12  , (  
 SELECT  SUM( `int_nokey`  )  
 FROM D  
@@ -55,9 +59,10 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	table2	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	table1	system	NULL	NULL	NULL	NULL	1	100.00	
-2	DEPENDENT SUBQUERY	D	ref	int_key,varchar_key	int_key	5	const	1	100.00	Using where
+2	DEPENDENT SUBQUERY	D	ref	int_key,varchar_key	int_key	5	const	1	100.00	
 Warnings:
 Note	1276	Field or reference 'test.table1.int_key' of SELECT #2 was resolved in SELECT #1
+Warning	1292	Truncated incorrect INTEGER value: 'm'
 Note	1003	select '3' AS `field12`,(select sum(`test`.`D`.`int_nokey`) AS `SUM( ``int_nokey``  )` from `test`.`D` where (`test`.`D`.`int_key` = '3')) AS `(  
 SELECT  SUM( ``int_nokey``  )  
 FROM D  
@@ -72,4 +77,6 @@
 SELECT  SUM( `int_nokey`  )  
 FROM D  
 WHERE `int_key`  = table1 .`int_key`  )
-3	NULL
+3	8
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'm'

In short, queries with GROUP BY return 3,8 instead of 3,NULL.
3,NULL is the correct output, see analysis of this at the end of the testcase.

[16 Sep 2009 18:07] Guilhem Bichot

yet more minimal test case:
CREATE TABLE C (c int);
INSERT INTO C VALUES (NULL);

CREATE TABLE D (d int , KEY (d)); # index is needed for bug
INSERT INTO D VALUES (NULL),(NULL); # two rows needed for bug

# we see that subquery returns 0 rows
SELECT 1 AS RESULT FROM D,C WHERE d = c;

# so here it ends up as NULL
SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C ;

# We add GROUP BY and it
# fails with 1242: Subquery returns more than 1 row
SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C GROUP BY c ;

In next-bugfixing we get error 1242, but in 5.1 we get NULL which is correct.
In next-bugfixing, the subquery indeed find 2 rows instead of 0, it's because it loses its WHERE, as can be seen both from the EXPLAIN ("Using where" went away, see some previous post) and from the --debug output in make_join_select():

next-bugfixing:

WHERE:(after substitute_best_equal) (`test`.`D`.`d` = '0')
T@3    : | | | | | | | | | | | >make_join_select
T@3    : | | | | | | | | | | | | >add_not_null_conds
T@3    : | | | | | | | | | | | | <add_not_null_conds
no-WHERE-anymore-here

5.1:
same but after "<add_not_null_conds" there is
WHERE:(D) (`test`.`D`.`d` = '0').

[17 Sep 2009 16:23] Guilhem Bichot

6.0.4 passes the very minimal test, 6.0.5 and all subsequent 6.0 versions fail it. Regression introduced in 6.0.5.

[21 Sep 2009 12:54] Guilhem Bichot

Several facts:

(A) bug goes way if I revert (in table.cc and sql_select.cc) the removal of HA_NULL_PART done by [e-mail]-20070309210824-13110. But I didn't decide that it was a correct solution, because if I create a tree of MySQL 6.0 code at the revision which introduced this change
(bzr branch -r revid:[e-mail]-20070309210824-13110), the test passes; which means that at that moment, removing HA_NULL_PART didn't introduce my bug. So the bug must have happened as an effect of "something later". 

(B) bzrfind says that the test started failing at sp1r-gkodinov/[e-mail]-20071121140553-31481 ; it was caused by the fix for BUG#30788 (merged into 6.0 by this revision).
(B1) if I take the 6.0 tree as it was right before the merge, the 
testcase posted in BUG#30788 already passed (this is also seen in 
Sveta's comment "All versions are affected except 5.2" in BUG#30788), so this suggests that the fix shouldn't have been merged.
(B2) if I take the 6.0 tree as it was right after the merge, and manually 
revert the patch for BUG#30788 (item.h and sql_select.cc), this solves 
BUG#46743 and does not reintroduce BUG#30788 (the testcase posted in 
BUG#30788 still passes), also suggests that the fix shouldn't have been merged.
(B3) BUT if I take the 6.0 tree as it is today (latest mysql-next-bugfixing, 
or its new name mysql-6.0-codebase-bugfixing), and manually revert the 
patch for BUG#30788, this does NOT solve BUG#46743 and it even 
INTRODUCES BUG#30788. I'm attaching the 'revert' patch below.
(B4) So something changed in the 6.0 optimizer code between Nov 2007 and now, 
which must explain that the patch for BUG#30788 has different effects in 
Nov 2007 and now (in Nov 2007 it did some bad to 6.0, now it does some 
good).

Patch to revert HA_NULL_PART:
=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc	2009-09-08 09:51:31 +0000
+++ sql/sql_select.cc	2009-09-16 19:48:40 +0000
@@ -18063,7 +18063,7 @@
 
     for (part=0 ; part < ref_parts ; part++,key_part++)
       if (field->eq(key_part->field) &&
-	  !(key_part->key_part_flag & HA_PART_KEY_SEG))
+	  !(key_part->key_part_flag & (HA_PART_KEY_SEG | HA_NULL_PART)))
 	return table->reginfo.join_tab->ref.items[part];
   }
   return (Item*) 0;

=== modified file 'sql/table.cc'
--- sql/table.cc	2009-08-31 20:25:11 +0000
+++ sql/table.cc	2009-09-16 19:43:53 +0000
@@ -1612,11 +1612,21 @@
                                 share->table_name.str,
                                 share->table_name.str);
             share->crashed= 1;                // Marker for CHECK TABLE
-            continue;
+            goto to_be_deleted;
           }
 #endif
           key_part->key_part_flag|= HA_PART_KEY_SEG;
         }
+
+	to_be_deleted:
+
+        /*
+          If the field can be NULL, don't optimize away the test
+          key_part_column = expression from the WHERE clause
+          as we need to test for NULL = NULL.
+        */
+        if (field->real_maybe_null())
+          key_part->key_part_flag|= HA_NULL_PART;
       }
       keyinfo->usable_key_parts= usable_parts; // Filesort
 
Patch to revert fix for BUG#30788:
=== modified file 'sql/item.h'
--- sql/item.h	2009-08-31 20:25:11 +0000
+++ sql/item.h	2009-09-21 08:05:05 +0000
@@ -3037,6 +3037,7 @@
   table_map used_tables() const { return used_table_map; }
   virtual void keep_array() {}
   virtual void print(String *str, enum_query_type query_type);
+#if 0
   bool eq_def(Field *field) 
   { 
     return cached_field ? cached_field->eq_def (field) : FALSE;
@@ -3045,6 +3046,7 @@
   {
     return this == item;
   }
+#endif
 };
 
 

=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc	2009-09-08 09:51:31 +0000
+++ sql/sql_select.cc	2009-09-21 08:07:07 +0000
@@ -992,13 +992,26 @@
       ((Item_func *)this->conds)->functype() == Item_func::EQ_FUNC &&
       ((Item_func *)conds)->arguments()[0]->type() == Item::REF_ITEM &&
       ((Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM &&
+#if 0
       test_if_ref (this->conds,
                    (Item_field *)((Item_func *)conds)->arguments()[1],
                    ((Item_func *)conds)->arguments()[0]))
+#else
+    1)
+#endif
   {
     *where= 0;
     return;
   }
+#if 0
+#else
+  if (conds->type() == Item::COND_ITEM &&
+      ((class Item_func *)this->conds)->functype() ==
+      Item_func::COND_AND_FUNC)
+  {
+    *where= remove_additional_cond(conds);
+  }
+#endif
 }
 
 
@@ -1941,7 +1954,12 @@
   {
     if (!having)
     {
-      Item *where= conds;
+      Item *where=
+#if 0
+        conds;
+#else
+      0;
+#endif
       if (join_tab[0].type == JT_EQ_REF &&
 	  join_tab[0].ref.items[0]->name == in_left_expr_name)
       {
@@ -17689,12 +17707,16 @@
     Item *ref_item=part_of_refkey(field->table,field);
     if (ref_item && ref_item->eq(right_item,1))
     {
+#if 0
       right_item= right_item->real_item();
+#endif
       if (right_item->type() == Item::FIELD_ITEM)
 	return (field->eq_def(((Item_field *) right_item)->field));
       /* remove equalities injected by IN->EXISTS transformation */
+#if 0
       else if (right_item->type() == Item::CACHE_ITEM)
         return ((Item_cache *)right_item)->eq_def (field);
+#endif
       if (right_item->const_item() && !(right_item->is_null()))
       {
 	/*

[23 Sep 2009 20:15] Guilhem Bichot

# fails with 1242: Subquery returns more than 1 row:
SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C GROUP BY c ;
#passes:
SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C GROUP BY c ;
(though ifnull(c,null) is always =c).

[28 Sep 2009 9:37] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/84809

2841 Guilhem Bichot	2009-09-28
      Fix for BUG#46743 "Azalea processing correlated, aggregate SELECT
      subqueries incorrectly".
      Symptom: "=x" condition matched rows when x is NULL, which should
      never be.
      Summary of the problem: likely a failure of 
      http://forge.mysql.com/wiki/MySQL_Internals_Optimizer#Late_NULLs_Filtering
      which didn't eliminate NULL values in some cases.
      Details below.
      
      Consider tables created and filled as in the minimal testcase of
      subselect4.test in this revision.
      Consider three queries:
      1) SELECT (SELECT 1 FROM D WHERE d = c) FROM C ;
      2) SELECT (SELECT 1 FROM D WHERE d = c) FROM C GROUP BY c ;
      3) SELECT (SELECT 1 FROM D WHERE d = IFNULL(c,NULL)) FROM C ;
      C has one row, C.c is NULL, so all 3 queries are equivalent and should
      return NULL (because the subquery gives an empty result): but the
      second query returns "error 1242: Subquery returns more than 1 row",
      which is unexpected.
      It's all about what happens inside the subquery. Let's compare how
      the subquery is optimized and executed in each of those three queries.
      
      For 1) optimizer understands that d=c should eliminate NULL D.d values:
      it sets "null_rejecting" to "true" in add_key_field(). It also sees
      that the condition d=c will be a byproduct of using the "ref"
      index lookup method so does not need to be explicitely evaluated: this
      condition (COND object) is thus removed, this way: make_join_select()
      calls "tmp= make_cond_for_table()" which calls
      make_cond_for_table_from_pred(); around "EQ_FUNC" in that function, a
      zero COND is returned, so "tmp" is 0, thus we come to the "else
       tab->select_cond= sel->cond= NULL" branch. When the subquery
      is executed, join_read_always_key() does not even read keys of D
      because as C.c is NULL, null_rejecting shortcuts.
      
      For 3) because IFNULL is a function, optimizer does not understand
      that d = IFNULL(c,NULL)) should eliminate null D.d values:
      null_rejecting is set to false. The condition is not removed, also
      because IFNULL is a function (both equality members have too different
      types). join_read_always_key() reads the index, searching for NULL
      keys, finds two of them, but they are eliminated by the kept
      condition.
      
      For 2) optimizer does not understand that d=c should eliminate NULL
      D.d values: null_rejecting is false. But the condition is
      eliminated. So join_read_always_key() finds two NULLs and returns them
      (no condition, and null_rejecting=false): error. How did this happen:
       * setting null_rejecting to true requires that both members of d=c
       are Item_field. But as there is GROUP BY, "c" in d=c is an
       Item_outer_ref (built in Item_field::fix_outer_field() around "If an
       outer field is resolved in a grouping select etc"), not an
       Item_field, so null_rejecting is false. 
       * make_cond_for_table() eliminates the COND condition because in d=c,
       test_if_ref() looks for __real__ items: it sees that inside
       Item_outer_ref "c", there is an Item_field, so it sees two Item_field
       on the sides of the equality and thus sees that "ref" will verify the
       condition naturally (see test_if_ref() calling eq_def()).
      
      Overall it looks like there is some assymetry between test_if_ref()
      (compares real_item()) and add_key_field() (compares plain item), the
      former catching a broader set of conditions (thus in some cases the condition
      is eliminated (COND removal) by the former but not "restored" (in the
      form of null_rejecting) by the latter). This assymetry was
      introduced by placing "right_item= right_item->real_item();" in
      test_in_ref() by 
      sp1r-gkodinov/[e-mail]-20071121140553-31481 (as a fix for
      BUG 30788; revert this line and the present bug goes away).
      Symmetry is put back by using real_item() in add_key_field() too, which the
      present patch does. It looks logical, because what matters in d=c is
      whether "c" comes from a field, it's ok if "c" goes through some
      intermediate steps like GROUP BY (which makes Item_outer_ref).
     @ mysql-test/r/subselect4.result
        result (before the fix, there would be an error in the first "GROUP BY" SELECT, and numbers instead of NULLs in the
        following SELECTs; each time because some rows would be said to match "=NULL").
     @ mysql-test/t/subselect4.test
        test for BUG#46743
     @ sql/sql_select.cc
        * making use of JOIN_TAB::set_select_cond() to track changes of JOIN_TAB::select_cond when debugging
        * stepping into get_best_combination() made easier (see commit comment of mi_scan.c)
        * the bugfix: when computing null_rejecting, use real_item() and not the base item,
        so that we can discover an Item_field inside an Item_outer_ref.
        I use
          if (a)
            null_rejecting= true;
          else
            null_rejecting=false;
        so that the wrong path ("=false") shows up in QA's differential coverage tool which spots lines
        executed only in failing tests (it's on the intranet, contact me for more details).
        * the assertion in add_not_null_conds() must then be updated, because it can now
        meet something else than FIELD_ITEM and must look at the real item. Note that add_not_null_conds()
        is executed in our subquery, but quits the iteration at the "continue;".
        * some DBUG printouts in evaluate_join_record() to track if it eliminates a record
     @ sql/sql_select.h
        I had to detect when JOIN_TAB::select_cond changed from non-NULL to NULL (COND elimination) so added this
        inline method to always have a DBUG printout when this condition is set. If reviewers prefer, I can remove
        it and add DBUG_PRINT after every "join_tab->select_cond=" line instead.
     @ storage/myisam/mi_scan.c
        Stepping into info->s->read_rnd() is made difficult by wrapping it directly in DBUG_RETURN
        (made me step into DBUG_RETURN() dbug internal code and not manage to step into read_rnd()).

[2 Oct 2009 13:32] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/85533

3638 Guilhem Bichot	2009-10-02
      Fix for BUG#46743 "Azalea processing correlated, aggregate SELECT
      subqueries incorrectly".
      Symptom: "=x" condition matched rows when x is NULL, which should
      never be.
      Summary of the problem: likely a failure of 
      http://forge.mysql.com/wiki/MySQL_Internals_Optimizer#Late_NULLs_Filtering
      which didn't eliminate NULL values in some cases.
      Details below.
      
      Consider tables created and filled as in the minimal testcase of
      subselect4.test in this revision.
      Consider three queries:
      1) SELECT (SELECT 1 FROM D WHERE d = c) FROM C ;
      2) SELECT (SELECT 1 FROM D WHERE d = c) FROM C GROUP BY c ;
      3) SELECT (SELECT 1 FROM D WHERE d = IFNULL(c,NULL)) FROM C ;
      C has one row, C.c is NULL, so all 3 queries are equivalent and should
      return NULL (because the subquery gives an empty result): but the
      second query returns "error 1242: Subquery returns more than 1 row",
      which is unexpected.
      It's all about what happens inside the subquery. Let's compare how
      the subquery is optimized and executed in each of those three queries.
      
      For 1) optimizer understands that d=c should eliminate NULL D.d values:
      it sets "null_rejecting" to "true" in add_key_field(). It also sees
      that the condition d=c will be a byproduct of using the "ref"
      index lookup method so does not need to be explicitely evaluated: this
      condition (COND object) is thus removed, this way: make_join_select()
      calls "tmp= make_cond_for_table()" which calls
      make_cond_for_table_from_pred(); around "EQ_FUNC" in that function, a
      zero COND is returned, so "tmp" is 0, thus we come to the "else
       tab->select_cond= sel->cond= NULL" branch. When the subquery
      is executed, join_read_always_key() does not even read keys of D
      because as C.c is NULL, null_rejecting shortcuts.
      
      For 3) because IFNULL is a function, optimizer does not understand
      that d = IFNULL(c,NULL)) should eliminate null D.d values:
      null_rejecting is set to false. The condition is not removed, also
      because IFNULL is a function (both equality members have too different
      types). join_read_always_key() reads the index, searching for NULL
      keys, finds two of them, but they are eliminated by the kept
      condition.
      
      For 2) optimizer does not understand that d=c should eliminate NULL
      D.d values: null_rejecting is false. But the condition is
      eliminated. So join_read_always_key() finds two NULLs and returns them
      (no condition, and null_rejecting=false): error. How did this happen:
       * setting null_rejecting to true requires that both members of d=c
       are Item_field. But as there is GROUP BY, "c" in d=c is an
       Item_outer_ref (built in Item_field::fix_outer_field() around "If an
       outer field is resolved in a grouping select etc"), not an
       Item_field, so null_rejecting is false. 
       * make_cond_for_table() eliminates the COND condition because in d=c,
       test_if_ref() looks for __real__ items: it sees that inside
       Item_outer_ref "c", there is an Item_field, so it sees two Item_field
       on the sides of the equality and thus sees that "ref" will verify the
       condition naturally (see test_if_ref() calling eq_def()).
      
      Overall it looks like there is some assymetry between test_if_ref()
      (compares real_item()) and add_key_field() (compares plain item), the
      former catching a broader set of conditions (thus in some cases the condition
      is eliminated (COND removal) by the former but not "restored" (in the
      form of null_rejecting) by the latter). This assymetry was
      introduced by placing "right_item= right_item->real_item();" in
      test_in_ref() by 
      sp1r-gkodinov/[e-mail]-20071121140553-31481 (as a fix for
      BUG 30788; revert this line and the present bug goes away).
      Symmetry is put back by using real_item() in add_key_field() too, which the
      present patch does. It looks logical, because what matters in d=c is
      whether "c" comes from a field, it's ok if "c" goes through some
      intermediate steps like GROUP BY (which makes Item_outer_ref).
     @ mysql-test/r/subselect4.result
        result (before the fix, there would be an error in the "GROUP BY" SELECT not using IFNULL,
        because some rows would be said to match "=NULL").
     @ mysql-test/t/subselect4.test
        test for BUG#46743
     @ sql/sql_select.cc
        * the bugfix: when computing null_rejecting, use real_item() and not the base item,
        so that we can discover an Item_field inside an Item_outer_ref.
        I use
          if (a)
            null_rejecting= true;
          else
            null_rejecting=false;
        so that the wrong path ("=false") shows up in QA's differential coverage tool which spots lines
        executed only in failing tests (it's on the intranet, contact me for more details).
        * the assertion in add_not_null_conds() must then be updated, because it can now
        meet something else than FIELD_ITEM and must look at the real item. Note that add_not_null_conds()
        is executed in our subquery, but quits the iteration at the "continue;".

[2 Oct 2009 13:58] Jørgen Løland

Good to push

[2 Oct 2009 14:47] Guilhem Bichot

queued to 6.0-codebase-bugfixing. Only the bugfixing piece. The debugging changes will be queued in a separate patch soon.

[2 Oct 2009 14:56] Guilhem Bichot

debugging changes have been queued too.

[9 Oct 2009 8:47] Bugs System

Pushed into 6.0.14-alpha (revid:alik@ibmvm-20091009083208-0o0f0i9w1sq3c1kn) (version source revid:[e-mail]-20091002170530-6ux072g5680avloo) (merge vers: 6.0.14-alpha) (pib:12)

[19 Oct 2009 14:39] Paul DuBois

Noted in 6.0.14 changelog.

In a subquery, a condition of the form subquery_column = x (where x
came from an outer table) matched even when x contained NULL. The
result was that extra rows were incorrectly returned.

[14 May 2010 11:12] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/108309

3162 Guilhem Bichot	2010-05-10
      Fix for BUG#46743 "Azalea processing correlated, aggregate SELECT subqueries incorrectly".
      (Backporting of [e-mail]-20091002133112-69ychheei22nubqj ). See sql_select.cc for details.
     @ mysql-test/r/subselect4.result
        result (before the fix, there would be an error in the "GROUP BY" SELECT not using IFNULL,
        because some rows would be said to match "=NULL").
     @ mysql-test/t/subselect4.test
        test for BUG#46743
     @ sql/sql_select.cc
        Symptom: "=x" condition matched rows when x is NULL, which should
        never be.
        Summary of the problem: likely a failure of 
        http://forge.mysql.com/wiki/MySQL_Internals_Optimizer#Late_NULLs_Filtering
        which didn't eliminate NULL values in some cases.
        Details below.
        
        Consider tables created and filled as in the minimal testcase of
        subselect4.test in this revision.
        Consider three queries:
        1) SELECT (SELECT 1 FROM D WHERE d = c) FROM C ;
        2) SELECT (SELECT 1 FROM D WHERE d = c) FROM C GROUP BY c ;
        3) SELECT (SELECT 1 FROM D WHERE d = IFNULL(c,NULL)) FROM C ;
        C has one row, C.c is NULL, so all 3 queries are equivalent and should
        return NULL (because the subquery gives an empty result): but the
        second query returns "error 1242: Subquery returns more than 1 row",
        which is unexpected.
        It's all about what happens inside the subquery. Let's compare how
        the subquery is optimized and executed in each of those three queries.
        
        For 1) optimizer understands that d=c should eliminate NULL D.d values:
        it sets "null_rejecting" to "true" in add_key_field(). It also sees
        that the condition d=c will be a byproduct of using the "ref"
        index lookup method so does not need to be explicitely evaluated: this
        condition (COND object) is thus removed, this way: make_join_select()
        calls "tmp= make_cond_for_table()" which calls
        make_cond_for_table_from_pred(); around "EQ_FUNC" in that function, a
        zero COND is returned, so "tmp" is 0, thus we come to the "else
         tab->select_cond= sel->cond= NULL" branch. When the subquery
        is executed, join_read_always_key() does not even read keys of D
        because as C.c is NULL, null_rejecting shortcuts.
        
        For 3) because IFNULL is a function, optimizer does not understand
        that d = IFNULL(c,NULL)) should eliminate null D.d values:
        null_rejecting is set to false. The condition is not removed, also
        because IFNULL is a function (both equality members have too different
        types). join_read_always_key() reads the index, searching for NULL
        keys, finds two of them, but they are eliminated by the kept
        condition.
        
        For 2) optimizer does not understand that d=c should eliminate NULL
        D.d values: null_rejecting is false. But the condition is
        eliminated. So join_read_always_key() finds two NULLs and returns them
        (no condition, and null_rejecting=false): error. How did this happen:
         * setting null_rejecting to true requires that both members of d=c
         are Item_field. But as there is GROUP BY, "c" in d=c is an
         Item_outer_ref (built in Item_field::fix_outer_field() around "If an
         outer field is resolved in a grouping select etc"), not an
         Item_field, so null_rejecting is false. 
         * make_cond_for_table() eliminates the COND condition because in d=c,
         test_if_ref() looks for __real__ items: it sees that inside
         Item_outer_ref "c", there is an Item_field, so it sees two Item_field
         on the sides of the equality and thus sees that "ref" will verify the
         condition naturally (see test_if_ref() calling eq_def()).
        
        Overall it looks like there is some assymetry between test_if_ref()
        (compares real_item()) and add_key_field() (compares plain item), the
        former catching a broader set of conditions (thus in some cases the condition
        is eliminated (COND removal) by the former but not "restored" (in the
        form of null_rejecting) by the latter). This assymetry was
        introduced by placing "right_item= right_item->real_item();" in
        test_in_ref() by 
        sp1r-gkodinov/[e-mail]-20071121140553-31481 (as a fix for
        BUG 30788; revert this line and the present bug goes away).
        Symmetry is put back by using real_item() in add_key_field() too, which the
        present patch does. It looks logical, because what matters in d=c is
        whether "c" comes from a field, it's ok if "c" goes through some
        intermediate steps like GROUP BY (which makes Item_outer_ref).
        * the bugfix: when computing null_rejecting, use real_item() and not the base item,
        so that we can discover an Item_field inside an Item_outer_ref.
        I use
          if (a)
            null_rejecting= true;
          else
            null_rejecting=false;
        so that the wrong path ("=false") shows up in QA's differential coverage tool which spots lines
        executed only in failing tests (it's on the intranet, contact me for more details).
        * the assertion in add_not_null_conds() must then be updated, because it can now
        meet something else than FIELD_ITEM and must look at the real item. Note that add_not_null_conds()
        is executed in our subquery, but quits the iteration at the "continue;".

[14 May 2010 14:48] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/108358

3163 Guilhem Bichot	2010-05-10
      Fix for BUG#46743 "Azalea processing correlated, aggregate SELECT subqueries incorrectly".
      (Backporting of [e-mail]-20091002133112-69ychheei22nubqj ). See sql_select.cc for details.
     @ mysql-test/r/subselect4.result
        result (before the fix, there would be an error in the "GROUP BY" SELECT not using IFNULL,
        because some rows would be said to match "=NULL").
     @ mysql-test/t/subselect4.test
        test for BUG#46743
     @ sql/sql_select.cc
        Symptom: "=x" condition matched rows when x is NULL, which should
        never be.
        Summary of the problem: likely a failure of 
        http://forge.mysql.com/wiki/MySQL_Internals_Optimizer#Late_NULLs_Filtering
        which didn't eliminate NULL values in some cases.
        Details below.
        
        Consider tables created and filled as in the minimal testcase of
        subselect4.test in this revision.
        Consider three queries:
        1) SELECT (SELECT 1 FROM D WHERE d = c) FROM C ;
        2) SELECT (SELECT 1 FROM D WHERE d = c) FROM C GROUP BY c ;
        3) SELECT (SELECT 1 FROM D WHERE d = IFNULL(c,NULL)) FROM C ;
        C has one row, C.c is NULL, so all 3 queries are equivalent and should
        return NULL (because the subquery gives an empty result): but the
        second query returns "error 1242: Subquery returns more than 1 row",
        which is unexpected.
        It's all about what happens inside the subquery. Let's compare how
        the subquery is optimized and executed in each of those three queries.
        
        For 1) optimizer understands that d=c should eliminate NULL D.d values:
        it sets "null_rejecting" to "true" in add_key_field(). It also sees
        that the condition d=c will be a byproduct of using the "ref"
        index lookup method so does not need to be explicitely evaluated: this
        condition (COND object) is thus removed, this way: make_join_select()
        calls "tmp= make_cond_for_table()" which calls
        make_cond_for_table_from_pred(); around "EQ_FUNC" in that function, a
        zero COND is returned, so "tmp" is 0, thus we come to the "else
         tab->select_cond= sel->cond= NULL" branch. When the subquery
        is executed, join_read_always_key() does not even read keys of D
        because as C.c is NULL, null_rejecting shortcuts.
        
        For 3) because IFNULL is a function, optimizer does not understand
        that d = IFNULL(c,NULL)) should eliminate null D.d values:
        null_rejecting is set to false. The condition is not removed, also
        because IFNULL is a function (both equality members have too different
        types). join_read_always_key() reads the index, searching for NULL
        keys, finds two of them, but they are eliminated by the kept
        condition.
        
        For 2) optimizer does not understand that d=c should eliminate NULL
        D.d values: null_rejecting is false. But the condition is
        eliminated. So join_read_always_key() finds two NULLs and returns them
        (no condition, and null_rejecting=false): error. How did this happen:
         * setting null_rejecting to true requires that both members of d=c
         are Item_field. But as there is GROUP BY, "c" in d=c is an
         Item_outer_ref (built in Item_field::fix_outer_field() around "If an
         outer field is resolved in a grouping select etc"), not an
         Item_field, so null_rejecting is false. 
         * make_cond_for_table() eliminates the COND condition because in d=c,
         test_if_ref() looks for __real__ items: it sees that inside
         Item_outer_ref "c", there is an Item_field, so it sees two Item_field
         on the sides of the equality and thus sees that "ref" will verify the
         condition naturally (see test_if_ref() calling eq_def()).
        
        Overall it looks like there is some assymetry between test_if_ref()
        (compares real_item()) and add_key_field() (compares plain item), the
        former catching a broader set of conditions (thus in some cases the condition
        is eliminated (COND removal) by the former but not "restored" (in the
        form of null_rejecting) by the latter). This assymetry was
        introduced by placing "right_item= right_item->real_item();" in
        test_in_ref() by 
        sp1r-gkodinov/[e-mail]-20071121140553-31481 (as a fix for
        BUG 30788; revert this line and the present bug goes away).
        Symmetry is put back by using real_item() in add_key_field() too, which the
        present patch does. It looks logical, because what matters in d=c is
        whether "c" comes from a field, it's ok if "c" goes through some
        intermediate steps like GROUP BY (which makes Item_outer_ref).
        * the bugfix: when computing null_rejecting, use real_item() and not the base item,
        so that we can discover an Item_field inside an Item_outer_ref.
        I use
          if (a)
            null_rejecting= true;
          else
            null_rejecting=false;
        so that the wrong path ("=false") shows up in QA's differential coverage tool which spots lines
        executed only in failing tests (it's on the intranet, contact me for more details).
        * the assertion in add_not_null_conds() must then be updated, because it can now
        meet something else than FIELD_ITEM and must look at the real item. Note that add_not_null_conds()
        is executed in our subquery, but quits the iteration at the "continue;".

[16 Aug 2010 6:34] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100816062819-bluwgdq8q4xysmlg) (version source revid:[e-mail]-20100816062612-enatdwnv809iw3s9) (pib:20)

[13 Nov 2010 16:23] Bugs System

Pushed into mysql-trunk 5.6.99-m5 (revid:[e-mail]-20101113155825-czmva9kg4n31anmu) (version source revid:[e-mail]-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

[23 Nov 2010 2:57] Paul DuBois

Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.