From: Martin Hansson
Date: February 9 2011 9:58am
Subject: bzr commit into mysql-5.1 branch (martin.hansson:3587) Bug#59839
List-Archive: http://lists.mysql.com/commits/130814
X-Bug: 59839
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============1743922274=="

--===============1743922274==
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-09
      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-09 09:58:43 +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-09 09:58:43 +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-09 09:58:43 +0000
@@ -281,46 +281,54 @@ 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
 
-    If a field isn't already in the select list and the ref_pointer_array
+    - resolves which class to use to access referenced item (Item_ref or
+      Item_direct_ref)
+
+    - 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.
+  
+  @todo This comment must be restructured. It is jumping back and forth
+        between abstraction levels and it uses inconsistent terminology. 
+ */
 
 bool
 fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select,
@@ -328,11 +336,11 @@ fix_inner_refs(THD *thd, List<Item> &all
 {
   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;


--===============1743922274==
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\
#   8dwoxx29ehftobtg
# target_branch: file:///data0/martin/bzrroot/bug59839/5.1-commit/
# testament_sha1: d5eeed4c8fc2a25e84c2195dd38b36e0c932c6aa
# timestamp: 2011-02-09 10:58:47 +0100
# base_revision_id: dmitry.shulga@stripped\
#   1zmxmvn8cdzxg50s
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWbzQOyAABXpfgFR4WPf//3/v
/+C////wYAvPr2udDu6modEKl3Nxs+4evXuwcUpsAtuigJJJphIaVPekzU0w0p4mqfknkmiemk0N
DJ+qPUDRpoCURlGTEJoNU2VNqeJNA0eU0MhptT1GQDRoPUHGjJkYRiAYTQYBNBoGTJoyZDCAwwkF
MjQap6npqeRPSHqGgPSAyDQAAAAyqY1NANAAAAAAA9QAAAACSQEATEaGkaAE9CSepsp6nqGho9T9
T0kB5RpYSAiwL7V7jJkj1tm7ow6/jf+dWqBKR8CJCX+/lJ/8R5/A4tlQbF7Owtn9X3t2Q6ZkGJap
+HmRysPB8D/Xqeuv7cK76xYsui9/lp+7oUc8qJsNRavMX47Y8WHdkc96kA0D6qdOuWDbnAzoW7L7
VhXBiAYeN+rEnK+cAiqLQOk8eEXSjG+emFdrp0Yz5sxI3MhEWEhkjNjcwOi5BgAECdZyCpAr+dol
Ze9ry89ZJznKbc++Ltx1HSUEklUD4f4QlPXHQlluDkCTi2xTlcPgxsmOExNHL3N9wbXSairWUh2u
KzIEZOwXtfJgvL8/eA4gUiGAuA8QIh61o09vNGwfmAwhW6/W5zvIgunPWzk/uLF8qieAPFSnXsqH
iC1PrfJj448Gn7/Vce22TYQk2GVDrxlOaRvP6u+dwnfkdlZ0tCl8cnFbn5RVR1k8UNt6aYYjhtlE
TbDRsXmptUz75PdvWjQ8iWk2zctzl7UIJVcb8EfqdlMqJP0YUbbfVKhYzpB4wvKm/BOJ6h9BgN4H
EIB8w4h8AgEAgEAgDzK3AH935hKeW/QM9NKrG/EsFXbogQ03xLZWZnVuZtYSpZ9A168XRmwdS4+k
l477xw4tr5Vrvn6NQnsJcPqZhtwkb94SM0kkkkkkkkEAEB8CDl3szGfUALIAiAMDSZmeBrTAsQOW
05GMsPB7M8eOHHgOHDDDC7QsYrdUfYitOIroDsALtJgEMMWblcTzC12h7el0Snvesez83zan4Dq9
uW7rtxCAMCYGQDBNX0BK8MCZB79CooevMqiYNVU0gCDKDDmcnsP+BEO0gHl8cDDSZIpBwlQPB7u0
HjxBAMabDRSOMnnUUPQrYCJlqjQ4csR7s30Gw5QXUXz2JZM8ZFg4UAYB4BseDwGBkJg/FQjn6Xhs
mJOBres1+NiAkJUbnEZREZIoJZHBTVax01nzazQukaYCqhiHy441NJhHGekuRYUFdm2C6+4kaiaH
I1LNhY1HmXUrgcCnO45x4OEMoMCfIEcTaAZkCK2sDy1uA1cC+BOJ3VWwBplYzvwEZygK1kfagqLc
KpWs4bSp1FA/FQcQ2I+A0DIYiExbDwQZyDGsGjQqnhMC1G03OdS0MsIHDhFDp6Ka7ICDhu0S/Mpm
OLcUBzvIb0icTkS75PolCOS1ibMtyNjqcx4JqYljLHEqc8g3TOpGOA5GylhunhiIYKZlTcfAkujH
ceM5iHUVEuQa+k6NrXXsFxpEyGx3reUNWD5LpYid0V3VFSYycvcQckxPnPDU2YUzjCWcCGcShfGT
eWzOdJnT5GUXuYhihxyOYzeQu4nebDIx2XUtiG1VjpKIKJZAnGMcibtVJxIX9C1HARx0orMC0etQ
wiGg7RySmhAtEcEnroUHCSaFDhQ3l4hY3pYjlAvE5WFqipkLAkyFRhyAqDSqpfuCeGBA4gL0FrAQ
0tFMxaupJ2z3rulwIX1JIeg7w+4+RQB7WA9hqcIKywZmZhv2D5D0Q73KkoBeXMRMYsWP7AUUrAE7
QT1ncHtAiZBM9JSAZALkhOD8/sA67WSHgfzZS/u30H+jSxWJ+sNwLBWFVIL66MQAkOGGAxLNw2CQ
VIpJn/Sdg8D6l32BaHqiJewMBkGsDnNS1DisEwnhIcWxf/bZz5mUiulGJQKkJgKzAmMSP0PIW8+v
dyXYIdt3YNeqinq7ppEDNNVK5/mAKyonIxPR2lEMVSBb6k+085gHrT/mNnqRyx3cNogVZ1FIREgJ
lI+9IbIKAzIildYLtUKkPYeBQkJCyWCFC05qkBg/hllFH1C1OsG3CZleYehr9ILtp6wMet73jApn
5bDpOq4+ZRuOcw/t4HUTJpZfhsInreFcz3t/hH8oyEQFxFad53nLBHBFjINAKXeFEwoHmtsN56n5
YywstVGMIrnLNbecxyucRM6SLuCeVPaxPQFiAduuoftKUDAFUW0Md1BhzbOpUsoCxWIiDDKCSIZn
v6E+CPodOmk9xtLrESR57h7QS7e4whk0h/6OHniHWQNpspIZ1dC3iwsL8QivOqyGcscw7R1Pt4Uu
Z71DE9hsGRm02iTaZBQGcCMlF/HwstIvyicBs9DEn1pmlj3VNsMwsJxOJu3K+kzqSKAyKq+zH2WX
mTYyqJO3uxZZQKHCW3rdWSnbQAbkdesMi7OEGQMw7IJ0P5bt/sav/zjvSWKRhmws2aPAsFzsosDp
JyHlQyLoShxih6OdHXYmh0c8IJNnU7i8fgVXBC+tDXwSpvm567MXClIoS4UZjirWvTwcDOL39Qs3
dx0cxzkthJT0TnjxxgdvXCsGKmCkmKKk4sDmrF9zcmX6LzTkWm9y2scm3lg7xHiIfY+JG1lgYHie
Z0FyQwbky1blpNisSLNjCi4nQL2rsORv1cetB6XDMZC3lww/SGMbzgRwJFIHkMguZZgYZt5aIoop
mpqopLKXpqNiRFS9RlDSS2UZpY9yTZUiaaF7p8Q1EUHORKpOYL3JDsRdro+wwbVN5TL26Hy8eFdI
E2GQ3QGSLVKwTGwPdL1vK2TBANpk5tFT9naQ7zeUH0GbtK9q8Wt6NBr8Li+UCqlZ6i65sbNj6AgK
ix4OguVKiRsTCWb2rTEUA4Tq4pD6brtdSZzFZG51M01FRRAmoVSccppYSFpM1detz6qiDJkA5HxO
4GWbbuXAjJaZxsIvypsMlynIMU51QWgyehXLzimDErIxCSk+giGpBsKXCKRIokTfaHGOuYJNx1BJ
FVFBBD0PE4YdI4TIIemCFrnsQNLIGBMFWgfMcDMi4cvPZzeSbeykwt9zhHFzDrnj+wtKljST7UA5
k102FSwD0hrN81GEzrLCNjNK1nt8kA8VyLXJi4n2YKlBShw1Sf9wiW/LJA5KdyOlyZEQ4G4vnTdJ
77h/eQld7KYSrdYZH35dpsuyFxv21DMyqMedDMDGpzmMmRW9AMwoIlAyUIPGYazdoD8TYqxWpXoK
ZMitOQQA5Les+UAmpJqWvTZ2nyy18yoz1YIMabTucrbmgCdYPA0AmVh4JaNGDhkIiV8TtpDEapQw
x0n0Z0upBTiHmoe7TFjplfbBcUOAdlFasfdnT8kZj9rKSgrlX+q1i2yXdrM2USJjaV1AvNebMzNe
zHAlwTZVAqw2qyIu0TxxsgokbY8kUhBaf7AnX7LizqjrakmmYXsmNlFdS1b7wF70JzgBmBmAGYXl
XQqqF7ROdO+MPn2SKFQxFtVbZChl4DBpYSJ8VsHCbcI8H8mQGLB8b90uAwK3KKYGGi00jUI8tWIm
Udb4rdojlHQjiyvSCMa8tRxjhRDoqfR5ewD0p5CRyM17l5brQvY83oNimgaEc9FQ70Z9UxFt1SPd
YaFZApKFfpJR2QPvURUmRavdCpFKroHi95bvEPUsNpoVHERq5QVivUkvxqEO3ndSP2J7EL3g8NsR
6emTMOyHGszMkx0ESVxXqC3oUd+J+owXcf/F3JFOFCQvNA7IAA==


--===============1743922274==--