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

--===============0340446198==
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#11766675 - 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:48:57 +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:48:57 +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:48:57 +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;
 }
 
 /**


--===============0340446198==
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\
#   vhxb2773230pw6ui
# target_branch: file:///data0/martin/bzrroot/bug59839/5.1-commit/
# testament_sha1: 693a63bf5245e01a5f1c0394ccdfe2a789591c8a
# timestamp: 2011-02-15 14:49:00 +0100
# base_revision_id: dmitry.shulga@stripped\
#   1zmxmvn8cdzxg50s
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWSOmLzgABZffgHR4WPf//3/v
/+C////wYAuDvqOJuMADoAyEk2addORR1QdtQASKTQ1BpU/1Tankyp+k8KeI0yp4KaeTU9T2lB4p
+UQ0eUaDJNEyZMpgmlPTU/UQ9QNDEAyDQAAHqAOMjTJiaDJkwmmQMhoDQGmTQwAmgMMRNU9DU0o8
mp6n6pvRok0NDaj1ADI0AAAAOMjTJiaDJkwmmQMhoDQGmTQwAmgMJIgICYQBAmTRpDIaJpkwj1GJ
hAwS6WAJM+1oO9eBgwS4nT0U0Yvpb/2zmoJSPEpKJf95Sj/NPn4mR3ZF5cug9LkpvseibNSRZTXT
XzE+m7UckSc+nzN971xZbIr1zHZ9cA54eWFEmOa+s3PhlTzMqjclit0AHERtTy1cFeThgmvDLWrF
fQBg1remiaWWHJFqAZA9w58lpUYeaGHKy7B6V0p09KElFI3McWTxdBkAMJc1iTIExwh6tLZWybyt
iIlDNHdGONxuOgoJJJAji6ZVlu7HMrs1DkCshHNjlMjKJpCpIKiynJikzbQNZLFmajVqsYUGuwuI
kbPjuJBbJzFihTPwA5ASQMB2AfKBQPkXh2eX90eBHTqA4hsfDY7v9xQ6d2yjhX5xQQsB1gbYx4r5
AdUC9aUHCuTYbttQmyDKrK5xGD3ljhu+/ecBCVB+IRmyNS0cEbmu81EDktqUi+o7o0cBhBQcL2rB
Bg+3+3aUbLtmb1uxR7g9sgwySj4mXfKLGylxZ9ULo3LheGBna90HoXm+1dh+AfiMBzA7AiH2h2B8
oRCIRCIRCBrmL9dMzOWWhvN07lN9Lyqm1mh3ZmTddbJwYmzQbGMKThUoNc60Bcvu6uex2cts1nVn
6IKXv6cwosS0fBsesvK7gIqdttttttttjAYbkKvB7IF7EBYmApAGDk33d01AGlMFfCC5DoMBeOuL
cSJAgdZAgMYxcQ2sZPaX/SjJQKrUPSAebuDEw5rYU4hi/cfD3vIs7hHlFbfF/ya35i/lt5+N+oRB
gTAyAYKq5AVyGBMg7eXRRjJeKuLQbXvvexASZSZOzqDEP5EgxIhoc4kCYOJSIBCwJuIJGVM52nJW
Da1c7S2CmSIMmTVyDwJFk9SAjyotxsndqlexpm8xkTzLoigDARANYBABgZCYPcoy9f35h9FBJwbP
vOPK/NgCglZnlA6p1sqI0TAslQGWL7s+WHKqGqSmImOyNgey/obeIiT4Bb0GyiYDX8pLu2eBNZma
HRmpYcYC9Z6LF2HYWcMVuIA4hlFgUJgjmXhgwiJJKBp1DCHgbYkDndwEUeoMVsGfddAF+sy03GBl
nbVhubVqRxYMVUeRiXDiJR8WCYhuZJwlaIapCRxf+0M6njE74kNE8lHGlhjXFR0EUjTirOpr1zfJ
DqvL4REbAjeVJBzYmcf3M48pnUWfvNXmV+DZxNe6O9ScRrm+0biYm0ywKmJZUN1h3qJ0NzCNY9GI
UwLjavA93oMDkYbe86FPeCncd1eD4GLBmRJDRy2rZaDzTMSPRFcYejoPec1NceSLadRiYl91h1nH
6hEltx1eluQ+LasInIvJ1nZ8uUICInea3WRXP1lOFoh8Klm9Dnvm8h5pjHkcDUjdstZ0YMblaTUj
UR0nvslRsrDXuWRWHGV5HYXEVbebzFpFBiZTUzsLOg1FVZFpsLO/xhuNytzyDGDu0bQzafFOEnQ0
3HiFBTo07PjGfcQIHvBuQbWA7qUTDHFckn19q+KvvE71JIiB94bT0JgPyYH4maAguHANtsfzD0Io
oxwU5MC3+YiwRJ6io9AGMtwBfmC8j9Q/sCBsC0+wvcLwG1QnD8v1AfHaTIAe5if5t+JD/LT0Wh8Y
cQWavLbAX6c2IgTHGGA0L+I2aQWosKH7FLyAH6GP6guZ+KJGxgYDUNoHWZLIcuBMKATHOFP/S6j9
jkUW1GqmWoTAYmCZdRuPkLUdvHzLSI76OgjS2M8fCwpgK1wst17wBxaqYYiC2lkBkDnNu252ke8+
Jt2z1B4q0eH1sFG73KZz3oDZC5itb4kpicXO00+kvkpKaOtUiUvlBb+nzukfSR4aIdV/OCDYv4Pr
8yR94YJ7xuYmZbD2bj2+cVx2NAHBommmGCyEjeI3X+Faaz9zRkLvKXBZzObeE1G+4FmR1v+ESgLK
K2ajUbPkj3IstI4gLDO1B4EAotwOYz4R8rszs1aLSxTOKaZhhsGWoKyQC1wa5QQFyA44R4FUKxJq
MaSLce7xmnTIoyHTFRmZDqW5PAOhz5ct5faiKLNpCKXh4lxj2meVSMvrMWTWGUkZjgnKMCnvZGK1
xVYVUT4T2wZDHhjj1Tu0YqekWGMmRxYukqUKqQzgjqNSXr5HKh08+uutj3EvUT7baHsWq2HaxvfQ
LhZ9Y2hqdRv3tXbTqOPefNiIEykvpn48ywNckhLH22rrgJ2dXjDiwksWIAbksyo6OBgOg0PM+3w6
VZv3cgksUjAWbDcSxC38SwhwtCETbU3gcQpHARtkUz1qpKYx2MErRGh0YIbZAVtJvARGJNLhPuHK
6r7fFwZy+HWLa/fzOoILgDajLI8AqKCPFi4OVRfc3ky/he+nRacXV9mvAWyaoj6nxI1NcZsOU1lQ
jQsmdajkVaRXoYmSgLuWU2GrHswoOmA2Xi/8M4XR7AJ7SRsA3NmQdjLmDDN5DvVVVhEyrFNCAjmd
6FV0mOPtOQdZb3w6WbvFLJ7+SRcmrhu8gyJIOskXzdgzdI2i8Iy9OraUgWT+A+j1dLapVYZDZBwp
YZiY8A+zsm6WmE4aS/5OpWO3EUdRqJisxazKdj4a/ce/8MhaJhWVz9p29rbWbbkExQzgDxXqsUiV
6YS3Qa5MSQDie6SRCzHGxcnYxKdrysTQtJlinay9dEthLgW93PveNhJkyAxR/s9gMunn9C9RSqrP
M7OSbZQ9daBOysQdQ3HWeMROxVWB07XSYB1AZgo2CHkUSIf3z4j0qCS611hNFtCCHVROMPM6UIhB
MEcGdSG/jllEwDQl5hgEiMgY7ehp2lGETdbgkLWPAvLFWXnQDsNfNkUYCCQ3VyopRp3lxK5mng0G
+dAQFgGDpjeU8M1YgsQ41qhLjpogdKeCO10yJBzOBjSzB3wKeJGeHpsjO57zQ9qRp4G3HUXXs32j
MytNOpDMDGTuxqyLoJIiicTVRjAZhr+HIIablcLBLYgsmyLk6CIvJcnY3+UAoplK00HKZ8WGPdMM
rbiBfXMdZhoFANLFAYgCMKSzbo0VbJalKW9FCoa8MOBqoqtxOUzvIbBJsJoZ6rVbrEBwH5C4Ld7O
pR32HniFFJdqx/BbRSXjqbmUTiaYUsAO07UklzkheLzPNYAaEOTmBoQwLUx75QZ2s/Z62yIWqPG2
lSyjJjWttqx4YgL2oTuAMwMwAzC966qtqvgE70xlH7fNMqqsSbS76EO2voGDleSOtbiombyYQZMH
zX7/LmMCv0kmTDSaiRhmJlDR47FEsiiF6lY5A3ctxIia3o7Lvk+P4QPrW4R6iw4m5fCvV1Yhkw6D
cpoGhHhyVH+9oWUQY2I9lxzLSJUosJKO2Ht84hjqFaaGq9kcBF6LlUcXyEXUc+B0KmOMhEVar1NL
3NwPlsIaKDEeuAQCI6dMmYfcOaG0zTHYSJ3luIX9ilwyPWZLsP/i7kinChIEdMXnAA==


--===============0340446198==--