From: Martin Hansson
Date: February 18 2011 11:04am
Subject: bzr push into mysql-5.5 branch (martin.hansson:3342 to 3343) Bug#11766675
List-Archive: http://lists.mysql.com/commits/131613
X-Bug: 11766675
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============1520384483=="

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

 3343 Martin Hansson	2011-02-18 [merge]
      Merge of fix for Bug#11766675.

    modified:
      mysql-test/r/group_by.result
      mysql-test/t/group_by.test
      sql/sql_select.cc
 3342 Alexander Barkov	2011-02-18
      Bug#11765108 (Bug#58036) client utf32, utf16, ucs2 should be disallowed, they crash server
      
      Problem: ucs2 was correctly disallowed in "SET NAMES" only,
      while mysql_real_connect() and mysql_change_user() still allowed
      to use ucs2, which made server crash.
      
      Fix: disallow ucs2 in mysql_real_connect() and mysql_change_user().
      
        @ sql/set_var.cc
          Using new function.
      
        @ sql/sql_acl.cc
          - Return error if character set initialization failed
          - Getting rid of pointer aliasing:
            Initialize user_name to NULL, to avoid double free().
      
        @ sql/sql_connect.cc
          - in case of unsupported client character set send error and return true
          - in case of success return false
      
        @ sql/sql_connect.h
          - changing return type for thd_init_client_charset() to bool,
            to return errors to the caller
      
        @ sql/sql_parse.h
          - introducing a new function, to reuse in all places where we need
          to check client character set.
      
        @ tests/mysql_client_test.c
          Adding test

    modified:
      sql/set_var.cc
      sql/sql_acl.cc
      sql/sql_connect.cc
      sql/sql_connect.h
      sql/sql_parse.h
      tests/mysql_client_test.c
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2010-12-17 11:11:34 +0000
+++ b/mysql-test/r/group_by.result	2011-02-18 10:55:24 +0000
@@ -1856,6 +1856,42 @@ 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
 #
 # Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2010-12-17 11:11:34 +0000
+++ b/mysql-test/t/group_by.test	2011-02-18 10:55:24 +0000
@@ -1248,6 +1248,43 @@ 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
 
 --echo #

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-02-08 15:47:33 +0000
+++ b/sql/sql_select.cc	2011-02-18 10:55:24 +0000
@@ -307,61 +307,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;
@@ -433,7 +437,7 @@ fix_inner_refs(THD *thd, List<Item> &all
       return TRUE;
     thd->used_tables|= item->used_tables();
   }
-  return res;
+  return false;
 }
 
 /**


--===============1520384483==
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\
#   c0e6v0suk1ol0c9k
# target_branch: file:///data0/martin/bzrroot/bug59839/mysql-5.5/
# testament_sha1: 69e0a3f7dcb2c13a205ba5c4099336f5920befba
# timestamp: 2011-02-18 12:05:04 +0100
# source_branch: file:///data0/martin/bzrroot/bug59839/5.1-commit/
# base_revision_id: alexander.barkov@stripped\
#   k5b3zi1wsa84i0nu
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWa2SRYwACHpfgFR4WPf//3/v
/+C////wYA+abfebXGd6x5CgAUAeqAZRBT0oANCgqh61VAQ01JlNpTZNNT1PUzSeTSeUaDyhoADQ
AADQDRATCaUfkpoGgHqAAAAAAAACRSMJqnpoGhNNKeSeMppGJoaDTI0zU0ek00Hkg00jUBGU9TJ6
nlGj1AAGgDIaAAAAHGjJkYRiAYTQYBNBoGTJoyZDCAwVKEBACYmgQ00AQ00mmJonqNGmZQHkmmgo
jSNUaR6V+ZrjPke7yzlyfG/ur4JFKHtJkqd/ZS34T7+0YTG0SJr1D7uNchNTmJ0LIdu3mmKWwB3U
khGNYYB8RjNo+QKvcTiux8JLFpv5pgWK7p0DSC5gS+/pNd2iIF4GzxYABkhUF1Yea6VyyIUPCyot
pA4nfrAQEWI6ZGhVFpIPMyjRViwyRwnFI2i2TmdFLCyRGNtbq34QlGhjq0+HuiSXyOujtpK3VhvC
iTvm5jDF6lsuysyz2zOzM9lVrotNfCVBKAhQkkEkkglEGr7IgjkqmoEQnjJFwCsBJHtQVIrJWAF0
irAVIxyYHVJhcLRLJSY87rcY1C6KKNujpQjZC+Ex1yW8Re14aPnoYF2/XWzHTayl+bOvB6h2DGFB
5h5wKB9q6ebxe1HDqI6uUDOGYhhmhCHuJnRo+bLEu9RlKogr4Fz37FIAZ4E0ykLU3lVfNzBWipDI
ureKNaSFj599xYFUJD4qfWh7EyWDVkJxlGYWeAxJE85tLX2YixgFr0graKS4xa2f7S8aoawKtqaT
NrWA1QCCgcmvBL0Vqo246Wf8babtM6ahrb9FdNq4tTr0Tzv7H5qDuHnLj/Y859pcXFxcXFnVvJ+3
De337uDm5YdKQuvOfFCslLnKqldc58LI0KqySKjF7gZZRZDGQKkvz17m2LY4hWLgFibrwKsghUQQ
87VBvQUoEdapECavASDtCkDIrD0YkkkkkkkkggAgeCBJq7qie8DOwxChTKlPx/G2A9koTeVnufN2
ubndSly5VV9yqqiiidDjRuroav4xuljzhxHtSHplk2G1GuvbNxk6DYqtOE8TN8nksyFessWO+Hvd
/4GF+3DfZDTpCIMEwaFkTCAXCvARiq0zkAS4JwBc+3cK22ihCC37m285gD16vTu9nVrIpQXUZUmG
VUspWZUW+5mMy4Yh1rtCi4oxSiDBVYU16R6FkHdZVMWpb6Pw0dMJiibNehrDSwqp21rNnBV525pZ
NDdKmppX0lqRWPiGk09yKtCz2slUbFCNptOVbe8RI5TVDHckYs0admuMaDSi6PW3lkSS8oLw7rFh
QpEofzl2Hc/ryHfigqmlEq6u08Oa5r6qBkg0LnU0XPFwy0NLlpjKkGczb5QUG1Xj26O3Pu16O9qj
U50mStEdRn8m9tu43yw1u+dTDu0amCl8ZtijqbK2r3XxJgm9R6et7CmccWsV3qRxTk3vG7o7sCYf
JqDwRhhPQ9DPh1dWmiq4qi+pLaB3USGDtnGmE4OxcssDpmtItjg08caaV11ysjGdikvZOV7HhlVp
FFMnNbJnW5f01rSPD+6sZsrnFeybWxiqwwctWOiqm+1aLau9U3JlWq9VuNOovulqZ0op1GWVFjFr
y+DbxbbtbLSdHhaarscbTY2cZc2L29qZ3Ni7DBsdFT5KRaXzPZNevOnHVesi521tgjdXljmyw8vT
3dzdjgd+OqaLK2px10WwvME6BgBJZCBQ+cIF98TnCp3m1UHiqKuBvrt53Ns49MdbMOFmF2Whfw61
cEdlGLZwXzymE260blldjzOGQ6a1Vy6YY389Jq/H6KVdi5nNWNM1uJVuXU9jGcn8XrnB7jer6djt
7XJzewzajU+kangeXh4V6+TrodOOdK7gyKS1rMODIvvXebNmNOF1ZoYbVHpx7savOve9c5rMMG7W
1ncvhu2571lexpaW71nnncsjgRz1Nu+1M+XBVztStcd0bFcM4rVocPg5X5KZzJm9ZzjCyi43B5HZ
r23tFbN12pql8TU2oq+J1tu3s1V381tqi5srhXS7D3GR17Kdbq03a9FdlbIucbSyiJtrfrtrZICQ
ixxLK2LyEThWqoKGYt9SsE4g7RdoLEXDACQ4PbW/s2zuyZ0ymVdG9YrL3Cl7Qt10raYMHK/gvLmO
aLzgloDQuc1VzEiYUSMqC4xIPfITdYCGGViZKxtsrdLK1UNDeylcVZFRfesijBVW8YyMcFFphy+t
SvNZZ8RyRyoMPFTFKFD1ztpKRKuL5p9bL4mFJMSSw+Z/J+qw/Gg/B6aoa5vUpRQoUop+h+q6MPPU
vWJ/b+iM1y/W1v1FFGqpPyJ/s/0P/BZtND8mqpqFOqJU/T9hVs4sFh+VGH/FPzW/rTDhOD7DoTfN
TRmT9e2UXDBVRQcGropvkNEZsX92OpYfq2fsTtfnF7bQoOo4jwbpuVaSUKwJEDZP/I8p3G0msUaV
IqhMDAyJmlHae8F4T8eB4xbxB+kvKWk2xnu9lwrElmWxl+b1gDta10O5U/zfhWrSitEqbFn2vvXN
r8H4LmP2/ifyJr0GULz5RJGr2KIbdYCKllzOwA9ujNhImUSIC9YBnyGX1HnWaakqI8AqFpTNEsWX
+Ft5/SQBLjJbtiIKvZYINC7F8j9TeCXwCKwVmQZ2iY1oOP6Ovkunwze4dXus8brlCetbqaH5uj16
5V6mL1OB33NUo8Tj4fE8fU6k9ys7Die3eacXvfvr+ke9VPUTYfA+Dr9bRHohpoR1jRXizYmRmuLT
rd+9xR+mxypTB1sXodz8+fj3u9Glud/PDrTQuw48Yp3V4FLTdm7+a5XaW0WU1ywjWI5713cFAC5A
5jM8SuJ0cJKCaJtEEyg20QUTtStjm7+9199zPx8FnNfv3MO7KHTUii2S5gk9fP2N04O/Ry2uvTFt
P+LMGnJrdvwHfPCeTW9R2sh9exPeISIZ2fAF0C+ohVNC46WKK1hLUSyCMd1SCYijCZZM5epVJHR2
PQ5s/b4PP5PQ0t/splzo51ro1L2S+4q03o6w08H1JyTdN1KUnXSleMmyRp9TzlP6AkDRQJZTbkZh
FKdDAEtISfaTJGcSTLJC36lPPMFLVtTh4OHh63W5+Zsxq010j5KTlZWMmJWHI8ivauy6Nvvq0eP/
yx7ZJySbk50U8GyD09iVR4zByUjqvxuPK9LR5R9fra5T1eha0lOmn01auE07pvbEUbrQ0Y4VsF1z
hMUnZj1Ks3Gf099VKKVa7emJ2u/0VXPpnncd7xeq6mTJcyng0d5bQ9+myh4pii0YqNo8zNP96fRS
f4T5JWGg8KnbSfU7zUW5Ln7PvXa6TrdXA+k+psR7TxdxtiercopI3SrGyfjLT6j5PgvDwfJ0h+5U
oocnP7Hpj4dRX6Aw1kl4/tSlIdVJ0KKU9pWuUyma43sutTrXeMGbhPGVn1zB7zsPA2eVvBp6PWk6
qu6RolMdbp4jka5gh4MC+asbKG6olsSOm2PCrzzsKyqvGejrOY4i5K4YxDmGudxnEo+R+X32+dJR
ePac39Ka0z8z+D4mP2PIs3ne9h6J83pUk++uKLpXt6mHoerc8TNaRpfCeqaeT7Dy8qdKU6S6XI4j
Al73zmuFsU06JeX7JRJ2WppSi8RUldN6S2e7dsnhWirDyV1Zyi2hguUqtfVMRlCJuK9RzHCFpcRa
YCMEfE8wNcylFSkr7nbHxY5Jlg3nomxhPkumLAVzZL08xsDD0l+OajLNalq8rqlqXq1Uv86+1xQk
vq+h8zNYh2J2GEaGJaKmUiqsqph2qOmS8tKF+atqewL1Zua1+8ySTXItgrJSiPsm4965Pqc+/6pS
YUk4h2OoN/mpRnN9l0GbEQ8ivl0gIg0zLNoo0KwEmdXhJqNs9rWv00Uw0763U/ERaRdzlI2WSjey
9TlNEk0RAisVircilTlkYiCCFLKjnICYiIGw5DZmpVuVruMvYuw2e/Rdg1Ktbid/F5H2TrbXKJ6G
526VKUmk/fx7qUKUKOFa0clIarSEujFc5S66ylFNfb4FuPXNKbJN0M8KRplULk+Cd9aOj4LDGNsx
ldFKy0dGudTq4/GZ827fJOGep7azZLIiTWnAKCAQJbHU0xWPzS99xpULLlhRnLE0VuByFXWuPEuq
1zzslDizbKbKqDonNOPy7pdyyWk9rcxRV+K7ViCiLrNJqYDtNgZ8nFxCS8gvIMaiijY7aqlTbMW/
QkmmLRPw4SNUe1F0FmTDcmQg+LuhW+JZereOs6FxMaGGooVqLNx5QT7YlahShSgUon0tLJNDKcCV
Vx2X3P6c0goqDIvRxfkiD07xhuLyJ4hagoCZWHWxDdQ+hrc/c7SiSa+F8pKKXqYyNmmRRLOFbrMr
I2tUvzqscuz4uZgvOLOfObI8df13PkPyPgZskdjlPf3ro20Kw5S+SUtc59JjX+tLcsYa84+ppcTv
R901FmEjQznhMt2CYc7P3J4EwOJufJd/BqOsKaZmVT95u7ZaX9009x4GpTc9y0tLqpP59Ar4PuyW
5y1Jd6LFouVJVQr2lTmfunYcJR6T1hiy7I1b5OcvbWDvdp8w0h/4u5IpwoSFbJIsYA==


--===============1520384483==--