From: Jorgen Loland Date: January 24 2011 9:02am Subject: bzr commit into mysql-trunk branch (jorgen.loland:3544) Bug#47853 List-Archive: http://lists.mysql.com/commits/129407 X-Bug: 47853 Message-Id: <20110124090206.E0F994B3@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1923646693885101836==" --===============1923646693885101836== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///export/home/jl208045/mysql/mysql-trunk-47853/ based on revid:anitha.gopi@stripped 3544 Jorgen Loland 2011-01-24 BUG#47853: Implicitly grouped queries do not need to use temporary tables A query is implicitly grouped if it contains an aggregate function but no GROUP BY clause. Implicitly grouped queries return zero or one row, so ordering does not make sense. This patch removes the order by clause during the prepare() stage of the optimizer when implicitly ordered queries are detected. @ mysql-test/r/func_group.result Updated result file @ mysql-test/r/limit.result Updated result file @ mysql-test/r/subquery_nomat_nosj.result Updated result file @ mysql-test/r/subquery_none.result Updated result file @ sql/sql_select.cc Remove order by clause during prepare() if the query is implicitly grouped. modified: mysql-test/r/func_group.result mysql-test/r/limit.result mysql-test/r/subquery_nomat_nosj.result mysql-test/r/subquery_none.result sql/sql_select.cc === modified file 'mysql-test/r/func_group.result' --- a/mysql-test/r/func_group.result 2010-12-21 12:34:13 +0000 +++ b/mysql-test/r/func_group.result 2011-01-24 09:02:03 +0000 @@ -1507,7 +1507,7 @@ SELECT MAX(pk) as max, i FROM t1 ORDER BY max; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 # Only 11 is correct for collumn i in this result SELECT MAX(pk) as max, i === modified file 'mysql-test/r/limit.result' --- a/mysql-test/r/limit.result 2008-10-15 21:34:51 +0000 +++ b/mysql-test/r/limit.result 2011-01-24 09:02:03 +0000 @@ -80,13 +80,13 @@ create table t1 (a int); insert into t1 values (1),(2),(3),(4),(5),(6),(7); explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where; Using temporary +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; c 7 explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where; Using temporary +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; c 28 === modified file 'mysql-test/r/subquery_nomat_nosj.result' --- a/mysql-test/r/subquery_nomat_nosj.result 2011-01-18 11:42:09 +0000 +++ b/mysql-test/r/subquery_nomat_nosj.result 2011-01-24 09:02:03 +0000 @@ -5088,7 +5088,7 @@ SELECT 1 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY count(*); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL a 5 NULL 2 Using index; Using temporary +1 PRIMARY t1 index NULL a 5 NULL 2 Using index 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table # should not crash the next statement === modified file 'mysql-test/r/subquery_none.result' --- a/mysql-test/r/subquery_none.result 2011-01-18 11:42:09 +0000 +++ b/mysql-test/r/subquery_none.result 2011-01-24 09:02:03 +0000 @@ -5087,7 +5087,7 @@ SELECT 1 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY count(*); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL a 5 NULL 2 Using index; Using temporary +1 PRIMARY t1 index NULL a 5 NULL 2 Using index 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table # should not crash the next statement === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-01-20 09:09:26 +0000 +++ b/sql/sql_select.cc 2011-01-24 09:02:03 +0000 @@ -749,7 +749,11 @@ JOIN::prepare(Item ***rref_pointer_array unit= unit_arg; if (tmp_table_param.sum_func_count && !group_list) + { implicit_grouping= TRUE; + // Result will contain zero or one row - ordering is meaningless + order= NULL; + } #ifdef RESTRICTED_GROUP if (implicit_grouping) --===============1923646693885101836== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/jorgen.loland@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: jorgen.loland@stripped\ # 1grig6b522tmw4cf # target_branch: file:///export/home/jl208045/mysql/mysql-trunk-47853/ # testament_sha1: 9e920a904af7586572f76300d88e513bad277b43 # timestamp: 2011-01-24 10:02:06 +0100 # base_revision_id: anitha.gopi@stripped\ # my4wersfuke8kbi5 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWSyET2AABLzfgECwWGf//3K3 3iC////6YAlevL1znfDoABx3eu9oNe7zsL2Jm1ohJRKb0TSGMmp6niEyZhQZADQDQDIcwJiaDCZM mTIwmCaaZGJgCGASU0TJip6QADQ0AAANNDQAAJEgQmkzU1NPSaYT0QNMgyA0MjQNARUIQaNR4mjU zSp5gpp5TJlN6k0BoDIAkkExACZNAmhTyaBGmQxDQADSb4QK+L6ECxuIg/UklxeTEjg+h/FOTWYY by2DUbNr0Zr1El87/bfu2r99+piRRFGSw8UzkhW92lyehepY+5s9rGL3E5XWwhePvLTIVUX92xHU jQhjYMY2mzt7AUs/7i5sbbayankZSnZwFXVkOKwTdX16sMeje6NLZBn4bCvJgdroHgYj6fRexPlx 6AtDM9t1CGjZlbw5hxa3VCSIxnRdr/d4RE9dlXiLpzaWRWBdvnGVT4s+KXHZlLgZjMxQxIa7NDVl QOYkjVQRRdxW9qzECGdsFrH6QfFiiXca3o2trk15bJm+12hvxu6VDlKkxNobaT6OGSnb2J24ggwV nq+LMITT/XLBQpJylL0fDfRouwqiV4altuTeq4RscNd9yZDJNBqlHHQW6qC11FM4TcIN1iqnRMEM IE0CiKgk6Ak9WTlLUThMT3uSpJRcToMpiUGIR5ruqAdBXyHDBcWjnxMq9NF7EMes0BRGDBrhz4DL O1BamZAZyFpBYm81gdYgje8cQjwTwK2QFdB095httrOO4ky+RkdqVw8XXHiWmCoDAwwqrDeshP2O /v8Il5WFvXrEotQtU25O3IvLzIVlyRJbJIYmkX/GX3cj5FVWeOBDCYyrTURSFyEsScpzJ9wwoEAB tniBqjQNZcdTDeaKhyzLBWwyccGAgN0MDE8gtTJ1Qlv/2y2BqQuErYqL1nqP1NDAmSgpMqoVEYc2 IKunQeVvr35j7rXVndm8ssPooP53r0KYbiy/or8FogL0cWIYxqHkEVkR0ZaxIP0MbX0MflKqscd9 BFpcXPIloxkJUUAtMmNFz71kGviuhrTHpI44p7MjKT2qcjxBzx9wkxElQu0J4Bid+qjbMawVym25 XnNRa0ZUVGdTLHwDIkWq4KjtLzk5qvmUD2qievMsb8MMQvt2ibg0ExIpvKC57d+xf14j8Dj5VDli QbgbjfG8GFlM+sScZp5oO4KpNRa8iS7CGawU8R4phvI7j235htteb+KN3ZlhUQLTzK0gSLqig8zS zwsGzdBLg6wgXwXNip24xVSukQpNUXKItHxDLFwMbiukQZjFVSkYPxqJEDiX1mYeeduN03vHPrTJ BkMGZi63CpZwVbriI5lGsaW2MQvoNW6ZQcYnKRSeUBi3iGAOUONtGGTswrMUCra19JtKyItKJCdb TvAoX3v6cwtzlx2tQQNj50aoYDbHu+pHJ/piC7Ysdi/BCwYwbT49HXkqf2f8O5QSiCMaiJeCcrPl FwA04XKBI77Sltivbv2gPygJwzE/RK013JFHqJFD3+/okhMpJXyPNpGlLVUXGlQ+mjTqhgPGxIIN HYOvCtATxbhXTIiU9FyhT3PpPUr1xUrLrzCKS9Qpc8+2Ro/F1EZx9B0m/AkzpGQb3SZzNgaVMoZi C/id51LAlsTW3gvgI5fFc5rucekQSeE3LF3UsWpvNGNjQ0LzxNJphsTM96V6bMYvhi3DbDSxUMip fIpd7AcQwjJDGRvY4Yw0WhetCg4999qWahvKw63JdZRPhJIeIPAK8DIxOzxODBdQH+esXaFy4nTD 4vPPrccm6uNkd9/wZZHjMqkMkEEMjbfQOYWnMf05zDynp3whWTDVbDup2HborT0o9KM6HFgdeIsA u4ScehMoon29W5s8K3OuYH+Bj5ukKaTbdZepcFupa3k7Y4kc+GWvWxBSYtiaDlN5u0WHyhYagoZ1 1LjEFyzXW5z+wVwDXhiyAubtTAOCKRlVzKVcwtZA5G3fMAaOu+Kwapfg9SHinKgtDgPpG5xI5hFI JdmTeSJtp4MfHi/llocYFQukIVDgEEGAhkyuuhrkYsM4ltRGE0xjRYmtUoLkWl4JuBbYGqoLFOhP vMJ+dYDIqCxE0hMwSZhgTlDKUCeJeV6uQLKRchphXmB7h5nfbwGTGU/18eE7ZAZXzrAjYlLgkZm9 6ONJrYpgGhzPibng1vI9gwSOjtrhi8M70b/Js3sd4GDWGN8Pa6dUAO4JwPVys/AKKDuh+0k1qFzQ 35hkoQQ7ICY05arU2cHC/Hvt6CGSIpWGiWoLk6Nwp8D7GPG4ijYIfsLEzZHVrZ+vgUmKr5DELOxf bkJXKaGCUr80SvluBMQQloROaKt1AYgYUSo4SLTCefcrA0Y8WTcwHKQZQXcxeQW8d+Qor00TVtFW 5mf0wEOCpmSFSGIknWYxFrPewP9UO0DlI9kH/ughaz0Odb8yHlpImCRvF0fFq2HfFILCCOwLCuDN 5msVMWikXF0grrTVFo6F58A2GSTaDHFHCIkaTMckSXIHMEgokKrSgqnw3SQqDg9SA9ejPFIJpAxY yoJMaI98Vufb6KjoypfWgsIB5WS9SdArcOSNzXMeSZm5KS5UkLjeHsx2NMK07bjwYHOATJ3hWBFL E1vq1mmCZ3N6JwPGIiIjP7kRKSnMINKySXA7AVtDhtxEhI5hKh2WcgsJoi1YJOmsOqeJkx4kik4e 4S10BoEL3BXYHz10B4ViGqRenh7n49Mzrb5KFEcRctYFgVURWiu1iwoE2cA5q6nRUxoEYDTYhK0M hO3dsz7X1d0q3xtZnMRYegUQaALW5nPc8xfk7Sh7HzYbXY0hX3BnDWWR7mhSRGcIDMPbisE4R6qL lYooPWUW+CWBZsGy0UD/xdyRThQkCyET2AA= --===============1923646693885101836==--