From: Martin Hansson
Date: February 15 2011 1:46pm
Subject: bzr commit into mysql-5.1 branch (martin.hansson:3587) Bug#59839
List-Archive: http://lists.mysql.com/commits/131332
X-Bug: 59839
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============0965071954=="

--===============0965071954==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

#At file:///data0/martin/bzrroot/bug59839/5.1-commit/ based on revid:dmitry.shulga@stripped

 3587 Martin Hansson	2011-02-15
      Bug#59839: Aggregation followed by subquery yields wrong result
      
      The loop that was looping over subqueries' references to outer field used a
      local boolean variable to tell whether the field was grouped or not. But the
      implementor failed to reset the variable after each iteration. Thus a field
      that was not directly aggregated appeared to be.
      
      Fixed by resetting the variable upon each new iteration.

    modified:
      mysql-test/r/group_by.result
      mysql-test/t/group_by.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2010-10-29 08:23:06 +0000
+++ b/mysql-test/r/group_by.result	2011-02-15 13:46:56 +0000
@@ -1855,4 +1855,40 @@ ON 1 WHERE t2.f1 > 1 GROUP BY t2.f1;
 COUNT(*)
 2
 DROP TABLE t1;
+#
+# Bug#59839: Aggregation followed by subquery yields wrong result
+#
+CREATE TABLE t1 (
+a INT,
+b INT,
+c INT,
+KEY (a, b)
+);
+INSERT INTO t1 VALUES
+( 1, 1,  1 ),
+( 1, 2,  2 ),
+( 1, 3,  3 ),
+( 1, 4,  6 ),
+( 1, 5,  5 ),
+( 1, 9, 13 ),
+( 2, 1,  6 ),
+( 2, 2,  7 ),
+( 2, 3,  8 );
+EXPLAIN
+SELECT a, AVG(t1.b),
+(SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c,
+(SELECT t12.c FROM t1 t12 WHERE t12.a = t1.a AND t12.b = AVG(t1.b)) AS t12c
+FROM t1 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	a	10	NULL	9	Using index
+3	DEPENDENT SUBQUERY	t12	ref	a	a	10	func,func	2	Using where
+2	DEPENDENT SUBQUERY	t11	ref	a	a	10	func,func	2	Using where
+SELECT a, AVG(t1.b),
+(SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c,
+(SELECT t12.c FROM t1 t12 WHERE t12.a = t1.a AND t12.b = AVG(t1.b)) AS t12c
+FROM t1 GROUP BY a;
+a	AVG(t1.b)	t11c	t12c
+1	4.0000	6	6
+2	2.0000	7	7
+DROP TABLE t1;
 # End of 5.1 tests

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2010-10-29 08:23:06 +0000
+++ b/mysql-test/t/group_by.test	2011-02-15 13:46:56 +0000
@@ -1247,4 +1247,41 @@ ON 1 WHERE t2.f1 > 1 GROUP BY t2.f1;
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#59839: Aggregation followed by subquery yields wrong result
+--echo #
+
+CREATE TABLE t1 (
+  a INT,
+  b INT,
+  c INT,
+  KEY (a, b)
+);
+
+INSERT INTO t1 VALUES
+  ( 1, 1,  1 ),
+  ( 1, 2,  2 ),
+  ( 1, 3,  3 ),
+  ( 1, 4,  6 ),
+  ( 1, 5,  5 ),
+  ( 1, 9, 13 ),
+
+  ( 2, 1,  6 ),
+  ( 2, 2,  7 ),
+  ( 2, 3,  8 );
+
+EXPLAIN
+SELECT a, AVG(t1.b),
+(SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c,
+(SELECT t12.c FROM t1 t12 WHERE t12.a = t1.a AND t12.b = AVG(t1.b)) AS t12c
+FROM t1 GROUP BY a;
+
+SELECT a, AVG(t1.b),
+(SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c,
+(SELECT t12.c FROM t1 t12 WHERE t12.a = t1.a AND t12.b = AVG(t1.b)) AS t12c
+FROM t1 GROUP BY a;
+
+DROP TABLE t1;
+
+
 --echo # End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-02-08 11:52:33 +0000
+++ b/sql/sql_select.cc	2011-02-15 13:46:56 +0000
@@ -278,61 +278,65 @@ bool handle_select(THD *thd, LEX *lex, s
 }
 
 
-/*
+/**
   Fix fields referenced from inner selects.
 
-  SYNOPSIS
-    fix_inner_refs()
-    thd               Thread handle
-    all_fields        List of all fields used in select
-    select            Current select
-    ref_pointer_array Array of references to Items used in current select
-    group_list        GROUP BY list (is NULL by default)
+  @param thd               Thread handle
+  @param all_fields        List of all fields used in select
+  @param select            Current select
+  @param ref_pointer_array Array of references to Items used in current select
+  @param group_list        GROUP BY list (is NULL by default)
 
-  DESCRIPTION
-    The function serves 3 purposes - adds fields referenced from inner
-    selects to the current select list, resolves which class to use
-    to access referenced item (Item_ref of Item_direct_ref) and fixes
-    references (Item_ref objects) to these fields.
+  @details
+    The function serves 3 purposes
+
+    - adds fields referenced from inner query blocks to the current select list
+
+    - Decides which class to use to reference the items (Item_ref or
+      Item_direct_ref)
 
-    If a field isn't already in the select list and the ref_pointer_array
+    - fixes references (Item_ref objects) to these fields.
+
+    If a field isn't already on the select list and the ref_pointer_array
     is provided then it is added to the all_fields list and the pointer to
     it is saved in the ref_pointer_array.
 
     The class to access the outer field is determined by the following rules:
-    1. If the outer field isn't used under an aggregate function
-      then the Item_ref class should be used.
-    2. If the outer field is used under an aggregate function and this
-      function is aggregated in the select where the outer field was
-      resolved or in some more inner select then the Item_direct_ref
-      class should be used.
-      Also it should be used if we are grouping by a subquery containing
-      the outer field.
+
+    -#. If the outer field isn't used under an aggregate function then the
+        Item_ref class should be used.
+
+    -#. If the outer field is used under an aggregate function and this
+        function is, in turn, aggregated in the query block where the outer
+        field was resolved or some query nested therein, then the
+        Item_direct_ref class should be used. Also it should be used if we are
+        grouping by a subquery containing the outer field.
+
     The resolution is done here and not at the fix_fields() stage as
-    it can be done only after sum functions are fixed and pulled up to
-    selects where they are have to be aggregated.
+    it can be done only after aggregate functions are fixed and pulled up to
+    selects where they are to be aggregated.
+
     When the class is chosen it substitutes the original field in the
     Item_outer_ref object.
 
     After this we proceed with fixing references (Item_outer_ref objects) to
     this field from inner subqueries.
 
-  RETURN
-    TRUE  an error occured
-    FALSE ok
-*/
+  @return Status
+  @retval true An error occured.
+  @retval false OK.
+ */
 
 bool
 fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select,
                  Item **ref_pointer_array, ORDER *group_list)
 {
   Item_outer_ref *ref;
-  bool res= FALSE;
-  bool direct_ref= FALSE;
 
   List_iterator<Item_outer_ref> ref_it(select->inner_refs_list);
   while ((ref= ref_it++))
   {
+    bool direct_ref= false;
     Item *item= ref->outer_ref;
     Item **item_ref= ref->ref;
     Item_ref *new_ref;
@@ -404,7 +408,7 @@ fix_inner_refs(THD *thd, List<Item> &all
       return TRUE;
     thd->used_tables|= item->used_tables();
   }
-  return res;
+  return false;
 }
 
 /**


--===============0965071954==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/martin.hansson@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: martin.hansson@stripped\
#   lpt6otaqaak061jg
# target_branch: file:///data0/martin/bzrroot/bug59839/5.1-commit/
# testament_sha1: 2c7e359696b6d30eba5349806b31f4661170647f
# timestamp: 2011-02-15 14:46:59 +0100
# base_revision_id: dmitry.shulga@stripped\
#   1zmxmvn8cdzxg50s
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWX4bnN4ABX9fgFR4WPf//3/v
/+C////wYAuDvrmvRzhWgh7YXzZ9bj3Pp9PfbE1r77dUvdrhrQlJTDapqYymTNAaaTTE0YgAAGI0
NNDQCUQBMmgTJNEwamp6hoaNB6gbSD1AAPU9QJRJP0Sn6Uep6nog0bUAANAZANAAA9QAxE1U/IEU
8SenqjxMIjRoyekDINAAAACKRNE01PRJ4Kn+knqn6FNlNP1NqMoDJ6jQeg1NBmk2gkiJkJiYJNoT
UwT0aST9NU21NRsU0yG00jQaBRFQrMRWIh2J3Eca2GLka/X1TvyCV8VTKNH1/cyu6W58pIykpTTd
un3QVyFl1yVo6wbE2LEPblKeX3R41aDRT12MUAlOhJBrqjHpmasgrBjlRLmSsrcbCe7eWzOfALQ7
sPLJsqwcME1acVSrWewBg1zemxNLFDki5AMgd47uC4qUOyFu6rVa86cZ8PBCSikbGNGTxdBeAMJb
1cTIExwB6Z2xNgyK6IiUM0bRbym85SgkkkCOL6k0nrbIlhoHAEm12SnCA57jJsh4SEzg9SMDdENH
pZVWKyklmxFpfQcOncOCbmF6kCGPEDkAegMBxA+wCYei7ePP+aO0j9nSBoGt7dbu/zJnZhws8n6y
xKonAPPdd6c6B2Bm21j3W7Ol/fngXskyFZ33FmEEcxXd32CWac+JY4jovknYWxVJl2FUZaHyIUvS
uM7w0k0TTNzZKaRx8stVdT3kvi3LcsXkXUGJRbRuR/F7lMI0J/W2jqJvqF5lcyjWRKm1ydB+YfoK
BygdAODwDoD5QcDgcDgcDTTITxzyMn45mxrDkWL53lSbV5XdmZN2lqnBibNBrYwpOFZYNM6kBee7
zctXj7do4xzcHewmrZt1gyQJ4fAkFxiRyXiRhJJJJJmZmZhgGD8kK/TBzZwAqgBMAYGpZvj8YSA5
JgXQDrwPM2mZ74s0SI457xxxhhhbQzYves+SL05NZB4AHZ1haWb1cT0C15iOs8+15FfOCx5P/Vq/
3Fm3LT135BEGBMDIBgpWpAU3hAFEDfv0GUoOw2CsBZNdrVEAQjCLJi6D+IUByIhkY4UESYOJSIBC
sKnEFBhOJzEb1IuItOom1ImMHKRUYjnB2DB7tC7ZNSz6aIlVML3kMiWBbEUAYCIBlAIAMDITB+Cj
R5fPAPhMScGw7TT6rMGAJiVWtyU6BF6YFcqAZWThnXplpUioxmIkOyLg9t282iI53Bhq3jRLizSK
7PWSMC9YMa7aEYLLa4tvkYiPC1cTiV7sVoQBxDKLAoSBFpcGDCIlCUDZvHyibYkTVJYgpB8hjV2u
YiHjEmUKFsLpKLsspDuChgkhmcDAZUg8TQgwG3CCxGvN9mtfAzTYqVszt1Yda0TW6jTfbJr0NzML
dXTwiE9EfUQ2oUqSJCSeeQ3frY5iflLRKlbLvaZ7I1whZeUWxUoXxoZG80YITQ2oC4jxGU3K7ByF
tAgKRfRfGpVvEJMJSMfwg9bOra1pcwYkTMmI6ISxvX7JiJhB1JhxEjvoXLidsLyqQTl3oUXbnMRh
aJ0lhDzdCa36MjdgMsuLio9s/j0bQhxNGnvktloypDVBh6DU4xFT2zUzHZWmrETRqlDKCSic0tpu
kuFxDrSx1cj95TEqOSZie1gx5E2YRJbjbIp5hYpNMydDstqkZbIuUkGVY5ZhksME4RdDSceITFKb
Sz8xn2ECB4hsQZMB6pzYIMHWstHU9OLyFfA4lKgOoP1O4mB+cAec5ZCGQzEREQR7g7ipK+OTQmD2
+sS0qKXl53AKKVYCd4J4H2B/QDTALjvKsCoC6IIwP18QHuzJEAPexL+zfmQ+bSxWJ+0NAWCsK6gX
672IgSHGGAxLNBsEgrRUTP+E7CAH6l3+wW8+iGMVQUCyFoCooSgcTAiiSA8cZMK2uH4WyazRkpFa
EwFxamMkfkfQXM+3v8lyEfwl5kZswp4eyaNAxTepXH7wBXKl6hzDE7y4BS9p6TuspT8Osgxh2OI/
kTvrM3Y0NWdAvnjgglMZDlsMonZ6DHW1ticTiqLMdJufD8ZKegqxCX5Um2+6aGV7zr2qdwZGV5GA
xDwmTtNHsqfFbygZuWc5kA+oUkqFqWEztzFso7fmzAXIhLxARTIz0NFEJ6nIzSh3zBdgrjxPEw/N
HFFbJHSBU+dMwpIF5szfMbQ08T6Zew0vswVsalcdFo61m3Qg9gQpg1iggNSA3XRvFCDUSpoYYFeP
V2NwsURNEMUKBJEMYylnB0m7ddsLKa0ZzRmQoS7uRYa9DGZCf/oFR7A6yZ2HZQSxVGzqYWrjbvVp
RvMnYf1bo7edDtGKljFhi9kbVJmSECVGFcCGvcLQ/KKoD9PSppWWI0YGExdShGOLGT6wqFr4DXng
cDPNqceB3L5RQUJmb9Mv0wbpXCLMR1e16qqBoPBtxy59MWlIDzZYZ1EEbS4dBkdb4a+LfHH8pHOr
mU8A6YI2FyPJgyShoITLVXVy0RqHWhxoi9fO1qIu13SwpiXWTC9BcGiXRgxog5xBLhLoHKcl+Pe4
M5ZDrE289fEIKPQD4nJ1edIbi2AqW4Ndo+yN0Pe72SYzCTqg6MDgJ9BV8TzlV8O43HQbwvEgOdh5
OZ6D7HhU4eeBskWzHrdh5Tdt8utD7pEQaTV5uYNBG8Clym8DtiIQ2w8QQM3sHelUqoiX0xTQ7UKa
09DQNxT2w3058ktb6JFaadmfMMChB0lBqk7Be6RkLujR4YthOBVLyPh477pIklFQXENozMhFOIez
ub6lRQeHYbfgt6T+j0EPrBcyBecPYdZuEfa+mHE7ff1FhAVdOPuLOps2bO8JDPwTCVTutaFOBgXT
OLmCiBIZXUUnbkyWut2LifU9FSaFZIqXIRPUy8qUryWhq7OHc8aihkyAhQ1GQGJZvYEwjVRX9kt1
0ZC0xq0GwTR5JDgSVKoidapWHTtfJgHUBmCjUIeRRIhu4No4i5J0HiEkVzIIdUicYeRwmRCCYI2v
Bom+yOBMG5Z2EgiEyEnfp2b2GkLhftiC+R4jgLXFTmQHYayTImwEEhundNURn2moo1M0rWg3wQEB
WhfJg1HUJi5szjQxpIi5nTXnzoSWvInJJhKBwNDXOq13tJ95GVvOqMtT6xFpke7L1lQjZrzF6r9u
oZmWoy4oZgYrd2M2RqgkiKJRM1GMBmGs4eIJ5tLcN65ULa4S5khUO51yg1bphY1sWxgm16d2m71J
LbHJAznedrEsNBLVaDFBWItOd220uuWhSlkiwqGzBhsakUu3HKZ3kNYk2/NDdpcrtIcMAlrH6HT0
7GrVaT54bGjxuT+nQNH685phqMDNfdjA8h5EklhSGYZjPHWAbCHJxg2EMC1lvXKDMqv49NdBCtR0
rnSVTZMZU11q7bcAvRCdwBmBmAGYX1cBFlS1VLyE7066I2CPx7JlSqYk2VvxQ7Z94wb7Sg9S6CkT
NzYQYMHlZjz4DArMaEyYahppFuAmUMXjrUSqKIWKipyBs37CgiZWI6tXp9fmB6XcYjA0PS7dl4ZY
JIaGtCJ1asGyXtidtiF9qe3UbisiUk1bQo5Q9y3iqMDFe2NaKlMcXoWuo37B1hQXdlzWbTBPrW8P
DQPjLoSzuA4EgxYopBuqOYmZgmOJQSsK7gs4qjbeeReu4/+LuSKcKEg/Dc5vAA==


--===============0965071954==--