From: Martin Hansson
Date: February 18 2011 10:50am
Subject: bzr commit into mysql-5.1 branch (martin.hansson:3598) Bug#11766675
List-Archive: http://lists.mysql.com/commits/131609
X-Bug: 11766675
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============1108319346=="

--===============1108319346==
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:vasil.dimov@stripped

 3598 Martin Hansson	2011-02-18
      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-18 10:50:06 +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-18 10:50:06 +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-18 10:50:06 +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;
 }
 
 /**


--===============1108319346==
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\
#   1noae76xyo0agmwc
# target_branch: file:///data0/martin/bzrroot/bug59839/5.1-commit/
# testament_sha1: fbaea329372c9b766e0d9dfad9c6655d952e9fb2
# timestamp: 2011-02-18 11:50:11 +0100
# base_revision_id: vasil.dimov@stripped\
#   jyf3wl73u6q78v15
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWT77gEwABY7fgFR4WPf//3/v
/+C////wYAuF31mg3OA6OjQHMbWrbVOjqnazrRTYdcBIqaBJPyo/SYTU8jTJk009TQ0mho0ekM1M
gaPUASSCZMhJ4mlPEnpNHqNRjSZqAepo00yabUBmkDjRkyMIxAMJoMAmg0DJk0ZMhhAYaZChqaaU
fqm1P9VNPRimygBoAA0AAeoADjRkyMIxAMJoMAmg0DJk0ZMhhAYSRAgBGSbQUzRME1J5qhshNMho
0aaAGTbJwJmke5ai7dmvvBzTS3/Gj+bWKUnnO0mJZ/73Tx/ebX2mF2py4s+Q2eWeHmeSGKYjZNVN
VoJMtqkzRkh42Tvt/u/XtC0w4U7do0XezcibDP2LnrtnLysXw4z5brADukKxnpz25tks1NONDkOi
8BBYL03lRjLswwICIbtNnzYDtqY7YYcbLsHnVndz8kJKKR1McGTxdBsAGEuSxKIC0bQ92lZVhlOM
zMSlO8Ms8bb7VYgkkkMOnuZqU24Yk9WYbgVr4YsbrCEYGT5kQmKtkWJWGuQZxUmZpNlNYPkNW+pA
hP0VIhWLjBiRLHiBuAigYDrA+cCgfrXt6/P7ke0jt5gYhi9+Lu/3lDt2cuES30mBO4W0ONKebKoe
EMn4YHjfshP4cbC903WXW30M5STonw/rqiE64H9zpe6lj8IjQ4P3wZiLclj0ZWL4UwiGc3izyg+b
GcuT/cdGhXyq+T8dbSiShUcqthKij4YwY1SqW+l9YVXcuC8xo6r3SLjZRcx7w7BgN4HMEA9ocwfW
EAgEAgEAeZ4i7csTGPJkbDSza1r8LxdaslXdJFdU9C2Wm1KWyJpMODfeNdtCBvt2OjlsevjoKzTk
54Kfg6MYo2JZelsdZcVq8RqRttttttttjAYd6FVesxFzABYgBMAMHO319cJgeCYFkDryPU3mh8os
0SI458hxxhhhbw0Yyewu/ajJQOsRoHkAdsAwLuKxKahe8F0PXueZX5wW3wf8Ws+gu114d+eYRBgT
AyAYKlagNFXsGBMLr14qMZrgri0G06X1oCTKTJ2dSYh8iQeBENgjQ2yIlAcSmQCF1oOJau4THzgI
srqEXFHNHVjcTrK6E3YGXuEUD2kiuVNx7t5jK/clexnk8xkT2l8iQogwEgDdAIAMDITB61Czze7E
OuQk4KiHcnA39N+LAExKlrnZsnVXUijIDaslWDLB+Xs1nxqRUaSGZGQW/C+6Ojh+S66rPYNyVDAY
a/lERJd/iUWwsQ6Mk3Fxa+pQR1dh2FWt9TGBEHEScFCoESOcV1cWQrA7MnFc50B5FXFmyRhcaAEG
6wWw9cqDaSw5f8oWm8wJZ21MNlU7EOLBkqPEyLCURbSThC5pkJGr1Qk6sNqyIEq89InqwQV1qstc
6mgjY+co93TWAQyqKsyRWGSYkdY99XE5Ff5c1XcZ7M4G24RlFuI2JcVv76qbSl5y0DSo83lkoW62
I655kjNeA2evA8C8ew6EyfoE+w69zXF7BkZiJDJo5LDWwNYUJrwlNjWPfEe4qW/VF13DmQN5Xedp
zXsyBbsNHpkNo2N5cSqUazyzhWpHQ0CuC5CPmNbcK24WJyzVDnuN5UbS924Hgbzf1FN1LWdENjkB
Gx8HGJzUjZOvznKjbKzA0yWsd5bkWjq9bzFokTZUUibdDI5FpJVrYWnt77DqVWWCugzxYqyae1OE
XQ03HiFBTo0FPT1GfcQIHmGiDNgOlKJhjwXBJ8/0r4Ve4m1MkQA0h7zYQA9rA6jHEIKSyNtsf1DY
RolwxKQgC8NYiwRklJSbAGMoiBagXcfAPyA81BQ/EucFwDZoTg/ftAdhrLB4H5sWfw3YP9zWZLI6
g2gsVcUtBfHexACwcMMBkXbRsUgoi0kfIlcPA+Jh2gt570RNTAwGYawOBkshy0EwoBMc30/3Lmf+
OBRaIzUyxCYDApTLSO88BcR1ZtC4RGqXmI5mxmv42FACpbjKKuwAaLK9g5Dn0t9hah2TjnxL/qMv
rPoLQofnE2fwUDTJAVfNj2CPXqgRsE4XqEYVP06y+xRUkd1SgUvlBacfpdI+sjvzQ6q/GCDFfmfZ
0SP+BFXqFw34CZlqMew17oLxW8QMuL3vGBe0me/Yc2Bt9xwMfnLhcJiLZ4GSkLEx0v6I7Ihb4qDM
ZjjxlaN1HkaRgAsfzroFQVkSC/rt3Hp1nE/DP0NctifPE6rzGC6GvAg6oPte1U9AVQG++G4mhWpN
JjSJTLv9FicmRRkOmKhmZDqWqeAew5cuXYNhfaiaOO4hFLv8S5cMamcx8/+eUPIG5eAmcTnsJQ2Y
sKvC+4hvM3MO6ax6dJO0YqeUWGMGRvY4SVKFSkM4I7DmZnnwOXZUeGebUrI9IG4RVie9ZrAvY3Ps
C0WXIa3YdhzK1NM/CNreeGAliTuz7uYdTaYJEX8apSQXTnHQMeDb7ItVUB6ss5Do1MR0Gh2vwjyw
bs/JzvSWaRiLRhuRahdN6dEjaMjGM49skQR2o7rU3TnCCTZ2dbluRZgsb0NjAVlJvARGJNLhPuHK
s19/g4M5dDfSwxVrdMIQW9eCK2Msm8F8UEqAvzC+bztf5cidFZwdb2PTgXEPEj/0+kjey3mh7j0L
xHeuz3GCRhwMTJYhda5TQZ4xG7o3EGmIbLRb6cYWR1gSUJHoB+DMyDmy1Bhm8R3qVSrImyofohUW
/yNQ5FnSHGrT2pYvqkWJqXaeIbC9TQcSZJXUgwZOkZi78I+KjYyeUs8h1/NvpNKbDIbANkrTETHn
D1fbDnacgZS7rdKsezjJdJnIFRgrN9Yjqd6zhOD04iggKk8uo5+dtGbSCxCwUOhZwB9i8dFEjemE
u49qJiKAcT3SSIV448mYxJ9r3VpoWEytTsZe+iWJLgWd3PveNZJkyAwR/k+AMufj5r0KVKqew7DC
Z74qkwKlwltQzBhsEWUNtA5dc2EA5kJAzrEvDFmEn+Y9R0lwXAJIrmQQ6oJxh5m+hEIJgjc728Qi
JhdHUTBXJPmOBmRgOXTnu6UyiyWIjZsEY8mYxc6GBarJdqAdhsJsijAQSG7vCilGnaWkrWaeLQb6
kBAV4XumMCndtVaCtDmxPaoz4y2oHSnejrdMiQaG8wpXe73lO8jO/yrjO17jI6svAehaatYubk7l
RmZVM+CGYGMXOY1sir0kQROJooxgMw12vEIZ9StFeligrmyLU6CIvNcXY3+cAoppq2xW+9fHPT0V
XVlsQba7zxdYEAWF0AIgEX6nrW7ijUYjem9jK4AhsV03YWOxTvA4hC1yY1zva9wwG0Wxa/o5VDSY
8OiSirs12LMUV0ZGtlA2GN9aguldLMzNgzG41KWNAVUPSxFVDhOrdtTDnNb8nTpEfRQ2UlMtkyYz
nSiw2YAL6UJzgBmBmAGYXVWapNfeJzpYRh+vesJqbEWyr1oc7WUYeegkM6uE4m+JiCphyUXeK+MF
RtyJpji0ki/ETJ+ToPJvRquUbXDzTdoRIGfUr0d670Q84H2rzKjU0XlyuDBh0GakgaEd29Uf+jQ9
aIL60e+04HyVpJFZUuaxopbofOuYrTM+ELA3CGrVBxfEw1UFLPU5Fhh5QUEvu7hD8j40IaKDEeyA
QCI4nYfUc0P1LebEx2neIoVal2QYR48D1Np/8XckU4UJA++4BMA=


--===============1108319346==--