From: Martin Hansson Date: February 18 2011 11:04am Subject: bzr push into mysql-trunk branch (martin.hansson:3674 to 3675) Bug#11766675 List-Archive: http://lists.mysql.com/commits/131612 X-Bug: 11766675 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============2082095850==" --===============2082095850== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline 3675 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 3674 Alexander Barkov 2011-02-18 [merge] Merging from mysql-5.5. 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 2011-02-17 13:25:01 +0000 +++ b/mysql-test/r/group_by.result 2011-02-18 10:59:18 +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:28:59 +0000 +++ b/mysql-test/t/group_by.test 2011-02-18 10:59:18 +0000 @@ -1249,6 +1249,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-16 09:31:18 +0000 +++ b/sql/sql_select.cc 2011-02-18 10:59:18 +0000 @@ -333,61 +333,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) + + - fixes references (Item_ref objects) to these fields. - If a field isn't already in the select list and the ref_pointer_array + 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 &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 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; @@ -459,7 +463,7 @@ fix_inner_refs(THD *thd, List &all return TRUE; thd->used_tables|= item->used_tables(); } - return res; + return false; } #define MAGIC_IN_WHERE_TOP_LEVEL 10 --===============2082095850== 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\ # woxgf319ak45v9p4 # target_branch: file:///data0/martin/bzrroot/bug59839/mysql-trunk/ # testament_sha1: b5662bdd8ba9777942d9f2923b753db87cbe9e4f # timestamp: 2011-02-18 12:04:49 +0100 # source_branch: file:///data0/martin/bzrroot/bug59839/mysql-5.5/ # base_revision_id: alexander.barkov@stripped\ # apiqbwkd47xosxh1 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWU2VSQwAC3BfgFR4WPf//3/v /+C////wYBI6Q9G7Q8e9969Prn2yW7J0yt3Ou2Vbhgvmzut2N17vp331tOumJCpVXpujVVQkimlT 3qmm0aNJ6k/RT2ij2pqbRlGgehPU00YgAPUAkkJlNiAIJiU9T1M0JoPUAAZDIbUBmkCUIyCaEGqe k9KeTKbKaGhoAAAADTQAxIUxNNT1DTamJptTRkBiAAAAAAAikQENTCnhqPST9SngT1U/Gpip+qHq fqhptR6j0QGh6gikICYg1MCniEbQp5SaaMmgNABoABwKocRUqqIfOn0nJ6dK+l5qR0+fH+X7YK1P uKEV/376z/end2FbUROBcec/d2Ml5s8ma0QYtT8JFi/UahWrOC8aIEovxQfGnczOO/3OUrL769d0 WKjxXd2QaV+2tEWGkxrFN4K08ymc+qvixAB2E8nrz7raeo4JyU6OXKUlnTN9sLmvXoDDbm+vVWns 8kE56Mls2QVTCiT0WDXdaABhOhVIoeO9pyWbUSGnleuerQ5VNsZHX6yCIjoh0KdKo02QN4AoidSb iiBQ7yVdm0bXNdXd5uzPsZqeKF6GlCAiIICIggiCICIE7v8K25L9N5VNbCihxDmCXmPvVlF5sCtB q08pSlvZqqyovCchqyngsGQkyL6WqLkuVzbILvRQqqqy4W1iJNdqi5SZc1XA5S0X92ZASnvnKWau Uylksxo2yL4FYBUBaQMBWB6QIh6Fnr09iNBYS08wMgxTr8XOd+pI97PpwsLfwMDZUXMPDOfx6UDs DN+mhtLlOvo74M2q0Ytnc40ojOejz+84Vy0P5rfNrxLRxodL+MGYsbGLxExs5L4jtqsIhrN4tdIP tY1ln7dNzQp8KC6T9M2lIiNCg5Ua+U0FnksgxhKhb976QouFQVxlN1HukVN811H7h6hgOQHUEA9A dQfQEAgEAgEAeaZCD2Z5GVmOZuNseDSfzfDZJ09z3uZk2sr0UDCJmgarGCe4KWUUxSICydnPxYU7 GMdY8BwmcHEBtyhMGaIzvQUoSqxtpVtxzTaJAEckHEgjBvJBYRQRYtB5gkkkkkkkkggAgPAgz2rD DPvAtmBWAQEWRHt7TqA7WAdQSfgeZgbjZQzQIDjmwccYYYVQYWJ3tlzaidSCygGEH0BX4hMdDBkO kAnLk6hteAOTJm7HoNx2qfgUFaKl1vJI8iitvY7ubD6zLHjn2Pq/boEQYEwQjkMBIL0MVV1Oi0zJ Ip5hDIQ8OVwgPfGWZizMZ8FxMyZssTgJPszV8rTzBkEgwRZCVQWwFLpIUoym2wT+wuBuGgNoBvKX XvKRVQrGG4CFRtJMwY01g+BNUec2QGWRYzMiJHs/I5n0uFwArZhDI4agFikSGJyk6cyhShbrNOsm Xt7QZDgXFUBMhCS1HuAXXBcqlDkAQHEDxjFBgDBUULLCHQc1j0e0AtOZtdhyQGuptN+99mwYWDKL FrFzM2MrIukNYQBUAUuFVIUgQEIwHzapWYP9a1N9KoyAwESyZnyHpuaGOcAErlR0EiM3RQ61pjbR XXLbcibSYAoqLBMCYRexin68ruJR3LEfTmolyJVL2CLTCJB6cktQt7Vms8bM4q0gDqOz0NhFxcRz xyLSKLgZkPcYynLnO4SsAYsQzIfl4S5HmMXicC8DialJbSE18EOJuNRfGB0xEEdSXaYgHSAbNi6z rKbt8+nfexAihwlcgZ5gaoBWowG+Cp1kzA5ZE6GARnaSRElSDLFF5mB3HdbzQCdk2RUdUoXOVBqP xo8uBMNBcB8S3YPsUeOCeAYSHjzvGRMhYajziXyUzAxLxxIpAxOVarflSkrYDa2OYflinBECzMOD JxEzNAed7sXIbUQq8lRd42wmMKOhcXBU3Z+U3ZlZpBgtgTmjmfXpCLFC1JplMryajIrMzEvoVFzV KsrMh4sx++wM2t6lemCDLTTJtFkvKqLq1SYBQ6mmlbQqAb26L3UwZU7+7n1pBx4DXCWLJmbm7BDs M4hF253gHhedFHSjwPvGonUFoCtWQ4oDzrFZrYrjLc6UC06xVXtWSqNS9lUL8DUse3GBeuHORcrQ BsjSBKC4qdSOnQNQAhTUqKacUhO9FO2qPqAcCQ2DUhONQTBdGA8qRIq3ZOW+SL/u7Gcba8Ot1zQN g8VcKNcQxGNIktJQzK48y8dhmfnfB3GsPUcCXjrOfM2GB6jaXDc+IOZ5xL8R7JmmV9+7PEoDl2ZY lSAWCqSJSQjnsOpaypKhHbsQXyBM6riSUkjleApXvpvqx1JCeunNzdqOkqvnbIVtJGoS6kpAoJHb iMaDx1C5bLR2UbDximuasAMxI33Bjg9q7TMcbpKzU0RNRosIysXN3lTwRUq1ksSPIJyEjE6qSKzG cnUAdh46bqjCUy2mJqMnhZEAml1qyPceBjlK52e4fiMQMHRdQ6BeEWwWSpnvxG0L3wLL3w1zkSd5 gk3LDjAjpt1UE8TjCCFmpTdPcLkpIbBwhGLRRM9Jp5FQhcG57kz1e+1a1KGddCjeBZJuCtIGlXE8 M7jIsKlw4iR6OWK6bS2bnKbimg8RkWkGqyEN9BowTh4OmT0HRTBU8MWIQNnri0t6KGdkxTJa6wyk YzZNJP4Xp4ty7GWJMRWriZdvNuwjEuy8swD0Euc6s2Q4zZNBmyZMJa7XKEq2CShANNVTQrDJkSpB rAK6mGbVd5ES5CZM8AdymiAKuxitCFgdLywxDChJ2Pmh5NmtKoiGIGtFJgeYfvPzJge0AfI6ZCFw aSIhgCAIgj8g/MoSrGSUkwfr6RFpAsuLj1gMMVcC8wL0H5B/gDzAJnmKuCoDaITg7vYA5X6kR4Hn Yj/Deof+zRzWZ8wcAWSqTtBevihiAERwwwGZXgNkkE0WkjvJVHgesv9gLiepFhgwMBoGoHSYrEcU BMJ4RHG+X+2czvOgktUaKJNCYC8uTGiO47hDuPl2vgh2CPzq4lDXEEH4fytQmKaHdBm0fgANBlMw YxxdZ0bZOggTJKRkDhOQIGwcgPOuk2cvSXCJb2bRewtqT1nWUBOXm/0TeKu1ShkxJJK5P85kVeek Av36ZRLVMAcKYBpebxGXmN4s7GttTpQtaCzTSzdHQfiO+X8GSKYH4tePPgk3N9JqOt+g/Y/m/BFP qFLoZ5yH8hhh1m3SX7Wh5/Pe+wHd7TflShAPqeVNpgTofc8j10tpMrJPmZir4yUmExYKnLrFPZwm EXjHVzEKkWmgJok5zv9Eez15NB8hDYPuPucuxgnijjCL8AL5b25sCwLSgZuBjuNZeXgHCR47ZGo5 o5MCeYkZjpOfHAeVrZ1FwBcZnVvjwQUIYEt25De467QToLOh1cCjLWE8JsaWaq6VV4YNOY2qFykW MGyov2vy862TCWQkmC0iISTU28kJ0DeZ9Ojvt5+RidbjLy5kziXmRZ7MzC87npo64o6OmxWCkzaV OW1E65mF/capbSRaX3adZhlAnd9plRdYaD394PQ+ENBMcQqVEVzPLyZ8TCwLfoukLDG7D12Qw8Ur tCCCrVRBBwNMkxg6amywtaSJAIcpzHWbi3r7cT7qjuTxmDgdy36FOjBJZYa5CXLuLRhGMQAeEAr0 JmfQhuQ1hriCB4ERLaJpAMPM8SCWO03CM6At21LRCkXzzQJNQpL1sbVdXjdekH1r7SSQCczltM+p n19HlWb+98NV03GVMQIfgSd9CyaXEhkhvH5JLm0u8fg7Pb4zcNVf/Wg+ivBAzHjBHgaviQCdXOhJ WYVG4hNlNdC9dKzTBPI7W5jt6Wc1Nxp4XFcwpislekmWLwU5Rc8SIQMxSQjlLQc3lhXdpcYYZyaS xIMZozZq/JyZC8IiWHNcWpb5x3mBIh4PK5nGBQmRT2HK4x6wfUn7/ivsGGYqBaLyUJWZCRsB6nMP 9I4w/ZDzQkjmHpkl8BUswrgsEO+Srcc5C6yqKaBa8ovMc18RqRoWYWBXgamGACDUEHeJZQPs0B9w +hxaQDqfR3ocGQwwN5y+57RzvjrGfxAa+RFKQPrEQh3Q8QgiPUZStbW4oPvm3cSOJV2RL3uDsyD0 anwyjgGI5+6eLm5ngia5POpkYr5Hl6wdo3KKQuoiKxVk9MGTkJHAA8ULPLNspPJxmHafb4OLlbVt YIVjK2BtsyGkQg9A+/5T9YYaQeA7X6RyIW4nq+I1+p1jM2God73D0P6TpIXykWAHUSpnd6tg+G5l 4nbN6jgTAMn3DsOPB+sdfaOURySjQA3A1iFTm72gM7EKzRe1jXkwLynGhCCpVZCErKkCd2rVkPWU EivyZYXMM8hcVmRryw+diOcaXeZexxcJUNpTDCq50/yegQ+9YJBDJYJBDL4PMveV2IWVOkegMmsP wNGxrBlc2lSHUcgCs+I1WXMNtxOJy20kE4qJSIq7yqdEhFKmT859Q3HWiC8kOQWJobRmkguAIJED IivmQ8bSoJsBVeyspLzAKzxmGx0teY3gDkrOskJEK/W7B4FCHFv9/FhqhdgBg7ADV0RBaGmYXmjY AVksXCzsqsoCHTbCXQITRSHd6NjVSzkaCnLDFWU0yoj2VWYBR0G4JLnoQjfHq6zL2N7ciXJTMgNz uGjQ1cBLca29R7xJqFmtOpkhCtYPM7znnbodjKVTsG3v9iu3X720ttcWbkcRy7XAfVuwcXdcwCdb S58biIhuH02dEEBEBBXKUG5hHGaqFEsaG5tqqoRBGPTSE+7e4DyLoQtqhMzJRoHwQ3ygvfFmDWmc ggK2V8SGa8HF7nuz727c6taJsuwPmk5BMAcsCYGKAQMtLqKI6X3U99eEpUGzgwvNHCOao4pnOeDf D41TcnwbmB2t7pjTIgDiO9Db9PNputJnaAIiIsQqcNr9ztEKkPZzN0KRBFDgOvHRoAH4ofFhhhhh 0aHAmyGQ6GxpyADlSahw1AFUeQAgCfJXX80w5zU73UjYPqocJy2FqkmTA7GxyZENF+cB9UZSAIgI gAiB4cXM2oZW19whJlZnpofpjxrG1tYKo13eySjZ8CA524pPBDcNghDkl3wIaYDi3N/e7xgAbtVL DBFTFimWgAhCbslTlQuorqcWu+RMd/w+g4DUUjsbXN6OdOrRn86XvA+9PAcpaAcxte/naB1wMkN5 jFaJFKnhxbZfxie+xDK5PpcDcPQA/W5hmVeAkK5TI4tuqtCrVN6kOgQqHYOk8mn5TLEOABGD10DM fsHZ8z0axKmzq5dR8R0kHubGYzaxMW5rpRPnswAJ9BozE72cBR0zCY0JCEmBlzGQ8Rw+pt0D3MHk PkAXF/ICvWJe0mhqbfe4DvHWP/xdyRThQkE2VSQw --===============2082095850==--