From: Mattias Jonsson
Date: September 13 2010 2:08pm
Subject: bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3508)
List-Archive: http://lists.mysql.com/commits/118094
Message-Id: <201009131408.o8CLDW4K002948@acsinet15.oracle.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============0886951552889081355=="

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

#At file:///Users/mattiasj/mysql-bzr/topush-51-bugteam_2/ based on revid:mattias.jonsson@stripped

 3508 Mattias Jonsson	2010-09-13 [merge]
      merge

    modified:
      mysql-test/r/order_by.result
      mysql-test/t/order_by.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/order_by.result'
--- a/mysql-test/r/order_by.result	2009-12-10 15:38:01 +0000
+++ b/mysql-test/r/order_by.result	2010-09-13 11:33:19 +0000
@@ -1617,4 +1617,25 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	range	a	a	5	NULL	2	Using where; Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
 DROP TABLE t1, t2;
+#
+# Bug #50394: Regression in EXPLAIN with index scan, LIMIT, GROUP BY and
+# ORDER BY computed col
+#
+CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, KEY( a, b ) );
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+INSERT INTO t1 SELECT a + 5, b + 5 FROM t1;
+CREATE TABLE t2( a INT PRIMARY KEY, b INT );
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+INSERT INTO t2 SELECT a + 5, b + 5 FROM t2;
+EXPLAIN
+SELECT count(*) AS c, t1.a
+FROM t1 JOIN t2 ON t1.b = t2.a
+WHERE t2.b = 1
+GROUP BY t1.a
+ORDER by c
+LIMIT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	8	NULL	10	Using index; Using temporary; Using filesort
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	Using where
+DROP TABLE t1, t2;
 End of 5.1 tests

=== modified file 'mysql-test/t/order_by.test'
--- a/mysql-test/t/order_by.test	2009-12-10 15:38:01 +0000
+++ b/mysql-test/t/order_by.test	2010-09-13 11:33:19 +0000
@@ -1467,4 +1467,29 @@ SELECT * FROM t1 FORCE INDEX FOR JOIN (a
 
 DROP TABLE t1, t2;
 
+--echo #
+--echo # Bug #50394: Regression in EXPLAIN with index scan, LIMIT, GROUP BY and
+--echo # ORDER BY computed col
+--echo #
+CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, KEY( a, b ) );
+
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+INSERT INTO t1 SELECT a + 5, b + 5 FROM t1;
+
+CREATE TABLE t2( a INT PRIMARY KEY, b INT );
+
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+INSERT INTO t2 SELECT a + 5, b + 5 FROM t2;
+
+EXPLAIN
+SELECT count(*) AS c, t1.a
+FROM t1 JOIN t2 ON t1.b = t2.a
+WHERE t2.b = 1
+GROUP BY t1.a
+ORDER by c
+LIMIT 2;
+
+DROP TABLE t1, t2;
+
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-09-10 09:50:38 +0000
+++ b/sql/sql_select.cc	2010-09-13 14:07:50 +0000
@@ -1487,6 +1487,15 @@ JOIN::optimize()
     if (order)
     {
       /*
+        Do we need a temporary table due to the ORDER BY not being equal to
+        the GROUP BY? The call to test_if_skip_sort_order above tests for the
+        GROUP BY clause only and hence is not valid in this case. So the
+        estimated number of rows to be read from the first table is not valid.
+        We clear it here so that it doesn't show up in EXPLAIN.
+       */
+      if (need_tmp && (select_options & SELECT_DESCRIBE) != 0)
+        join_tab[const_tables].limit= 0;
+      /*
         Force using of tmp table if sorting by a SP or UDF function due to
         their expensive and probably non-deterministic nature.
       */


--===============0886951552889081355==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/mattias.jonsson@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: mattias.jonsson@stripped\
#   7ntduuhi5ahrpr8v
# target_branch: file:///Users/mattiasj/mysql-bzr/topush-51-bugteam_2/
# testament_sha1: 5559ae556878db08b13a1c7b7e773dc03990bfab
# timestamp: 2010-09-13 16:08:21 +0200
# source_branch: file:///Users/mattiasj/mysql-bzr/topush-51-bugteam/
# base_revision_id: mattias.jonsson@stripped\
#   cu65zdjai1zwutis
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWZ956TcABgXfgFwwef//////
/+q////wYAyHfVXR3c7Q0AFAZoyWlAoAAABoGoamUaZKflRk9CGhgmQyZPSaDEyMmRoYmmmEYQSU
CqfmkyFP1T20o9U008obap6IeUGmT1GgbUyAAGgOaNGhphANMCaaAMhoYgDRiNDBGQASJKZGmgaS
eKPIg9MiDTTTI2iYg0D0QAabUDmjRoaYQDTAmmgDIaGIA0YjQwRkAEkQRoBMQTJkATRqnlNo0app
sob1TNR6jRoB6ZRLwQL9a5f0TtbvzKEolyzXsw5l4u8O85QIpgUCBUmxIRPeND5Z8Y+hJGsMhqjO
yGK7AZU92BdHg7HHHJ2p5CJajN8lCTEJrigjPYKxJpvCWpznJoGipm+2wenuyQXC9NtN2iVK6rfV
OZl4ZTh7BJSEa7w5qv/2p6H5B8gB/P45SnC3AYg8oZFtqiFDbbGw4sT/Iu/NIyzkq6jEOqERmF1n
Jx2KTCwgYkAHPuBwYKFI1NrsHFi4VBfNK6W20221nNvqrhsheWxkdakMYagUzOdD4k3VKgPIezAr
hI/erCo7ML/qMxj8IehWTlAeUKquC950Nr/Mlwy5BghvcO3ulLzgMOKgGwTVkfOkjFqsnCDED/60
hYqnayBsXwhKMs1SySnSds6oT+AM9UikKiH7p0+uVAiicoC6VB30qOh/M/2BzqMTAkMSCR9YxOTN
fQgw+tppxvyA0hDPSDzetneiw2x0uJrrYaWKf2KQYdYxRMjor1uaePAVA0GE1TLtDbTKODgx9sCa
eARoujG2G8zAM1mUmUqJJIuaOw48xIJwsCsIHCMYM16jzuA4wCIRCIRCIaVpK1vXEAyEDrGAkr9s
HhtBeJK87vVFrEr+u6v719AmENBipaFh4G49QtPeTIBo56NH6t2/S+9FIa42MvCqLBgUCUIGxgG4
kdVtTtZvTlCqNad39y912FSW9jk3WwDJ3HGJDaCaMcwcKgmQGSF0HPCY4JSCQMDKAdZ98iIc6wtD
3yrrC8YbquTivsh4mAkrObxBjB6wL1BGVpXYM6rJGK26eB9OOGt4i2I4DCxugHtCJlEalHwDgSnY
FtA0OfKFmoxCT3Dy4iTBElxOrJ8uOHM2ytELFYnESkDsTywmKwBnZTmL5mwxIsEZFji3ZL77ui9K
88TQgJZkfcG3AOJWF1xUaBSRw7Ige43RVmdxiQFZm5qHMHDMURgS3mydCXDlsLpBcca+Uym2BtOB
3FJBWWERtglxcQ8WTWlRy2fnzMsfzg+xXNCEHri2Wt4bw1rWNAxpFJFGUy1hJycaiAlANSG6RClR
SihIhiWMcHObwhamuYmSqIqw2sTmE4SjrvNN50EuJ32ku63PGRdvDeEyVxhNJRYLI7SPdp2FWsja
XnVIvGOjQbYmMKOMbEcBcLci2N7mf6BcdX6HcnxwfMDeDBjctKhWf4rauU8qzdzz3mhxP1Kj3hPt
q28xwbSBDXZrAcK31CPVvOAv2yMTvMMMyB+8qzC47PU6lc0GMlxIkJ2QyXTpRIwkULLcp5E0yvbS
y2yJRDNjC5yiZByMkpSGC/Iq0HwllgbQhpRUMYBUEuQx1FiqxytatJ4HoG+dTEqpmYHgUqZjM7Re
ZYtA8pwz1c1GY5OahkxLgdRrpbpNVFZSSOomRROorNDAyLCyqg2hthAqNRLaK+vZXVDRmdJSCu+C
vNIISqGrzMsTt0m2arIIsIurQeJIvbQJT0O2RfAvBxLzErVVBxx2T7GoQgNcZjp5BsZEUYQIikOA
xUNMVaCqsYZ5MfaNODwE3v8NwWI6NmAZUqBs3VrNyGAzNxA/OcOwq4LONJaBcKXWHqC6cgWy+mMa
G3+vqKZATMzuYOV6WxBxvPjUYQYKBvsCpyUUvqg/A0DoB1BUj+BvyJA2rbhseodpFIrJfXSMK4E4
BgEwJzUwf2QXxYEpq41UKgLIsQLETQpDFAMBVEBhA1FQpDGWWDNISEUiKD+pZlhWaGKCaBj80Zh/
UXUfoIkBqFE4OM4ZDZAXHrA1iMZgOGAMYCipCsCIEYUbgFYVcRKy2wmMdpKkOyAQD4+fax8gsxuB
sg2IwQvjY9fMKsBzdEUPeeC18BYQXGziItAaBhB+BA/DL+BkWmpWbF+MzJIw2ppVgrKBByCBy8Xu
XNzc4ymNocphKiCpGo3DxyAHR143MEtUwBOr/+WSLY2AxJlzZmNcKQSNeiHt7+kTETgwOafjp/Hm
x29+8wP5Vn4hAUdaeD/5YqLaFZwWIxOgHFjeM1myFC9At03IvO8LoYIc/KMgNDQaC02rDqYiBs2c
5lmi4lXvvIFxaYYpu0VdMrIHn2HpxJ7ZDR5LcEBKxi5b2gCYLwA5byCl+u6BaU9QtQX3WqUFFER3
IM7GoMQa3U26rt2vjuahItSoB4OcSkDiVCL5nCNtp3+mbJVAZ9OrmdXaKuv9is0DxKsz+L+87xZ3
Hp4F7DspNJvpGUHBiXZCO539YzVZGXwl40iQJM6A4+7Tws2naek6sX4O52t29hPUvKw5A+dB3YG4
0UIJyssZNDG4zbwPQR9sylia91F+d9Neh2ddOzgPo5fTQsqP9CyOegfJXnK/DpQt5uS6v6kA3gYe
Giy4ERXCYB1P7KuHTYVwhYOKtHd2WR8CJ5mFbMN2EDzhQyU0nMT7mhFeLJFwEBHNVGpaWn3t2ARr
8hxhjzPhyOZztqNfGVQWDh4sUFbMoLgjq/QP2hwSotpUvGJYy+AydzuOh0qHVSQ6Xvp0EqBoSXSa
QznfqelSwV3mdwcdzFxiDwBe0AkFq2EVAD5sK2rPa6eMoteKMVIHgVTgGV4t6CtDrcvIWZu3TBWz
Jx1EthOzebj7BDG2Ej+mc8BSvNRLMKIVAJNWkzJgDx6kHtwkJtM9XWF254f06HIoRWDCGrvyOGDD
BIJCGc9Ng2AMSDsX0tCOvzzTHtPXvSNCG7pxXUJbC44nX4n7GB0Gz5TPkQ9gUXlt1RkB9lQiJusJ
HgwqhZokEbGtBkRBM4DPbUB14yyarZUR58IHcXewmbGxILIiU7urzXxsSAuzOf8z7gZOj2di2gxA
IkhnlPI+JQJKiOSbEuJDL1OWcSsVVRIMStV/qsyVVGmMh4QCDMN0inMEBEAcsZAD4hVMmyIoGGAi
esyALcWECS2kCwwJd4WOzJDJJrS4WgmE9zgW8sQiKv1u19QZT/Fz+XM6jNdMF53HgeSvwrUAA3jC
xNisGQOkJjxIxh+VyRGtS887i7oCgg8lLxxGY4W1iuMgroJvJc98azPLXfmdupufMlzYyBWc1b9d
CHiRBgO0rF+40NCK8jYGFSW73QgaCuuI2rdNbzGFu81QqjywKrhjxYaT9GC0mJxW+RNCOQyrZBJ2
T0VZTOUZiVq9quzOwmKpUbASuRccRyHYp2GFNBdZWFpkgH6H2iTkSI4N2/HslspODnJ9kIscYquA
8Uhz5NIIz67es2SNHTlyLWNRe7bPqwm/gyZioOtTun+QOqxE0dyoPAwd3CFmwhKXWWNsN1tsZeMa
ggIJzIysQl4jpOxbi4aIGD2oB5765msA5ZbCol3VnbLAcc8tYlgo8dgl5MDsC+Ykwfbs2FArpk/x
AYhdbYA8VQ6ygrFQ6aKE6LmQWSujmMgYmDoPsYF/YVG2gLHMYOAd8EBq+KrGr1tFXRBolOmnsEoy
ucDIuBWZ3RC0tTL39+P1D++I5/eudQsTobFsGQHpcbDfHSLIib2a5l6r1n0Nq2CvDLmRjKSd0Bdr
LqRByRYG3ifHOzAOtEnLTqB7gYtWVyV/tfObiPo0uHWXgzA7idfwlJZhX1kfqRKJRvrJhFgqiW0l
FYcS0B/xdyRThQkJ956TcA==


--===============0886951552889081355==--