[debian-mysql] Bug#471737: mysql-dfsg-5.0: ORDER BY not working with GROUP BY (upstream bug #32202)
Jamie Strandboge
jamie at strandboge.com
Wed Mar 19 20:41:42 UTC 2008
Package: mysql-dfsg-5.0
Version: 5.0.51a-3
Severity: normal
Tags: patch
User: ubuntu-devel at lists.ubuntu.com
Usertags: origin-ubuntu hardy ubuntu-patch
In Ubuntu, we've applied the attached patch to achieve the following:
* debian/patches/92_fix_order_by32202.dpatch: fix for ORDER BY not working
with GROUP BY
* References:
http://bugs.mysql.com/bug.php?id=32202
We thought you might be interested in doing the same.
-- System Information:
Debian Release: lenny/sid
APT prefers hardy-updates
APT policy: (500, 'hardy-updates'), (500, 'hardy-security'), (500, 'hardy')
Architecture: amd64 (x86_64)
Kernel: Linux 2.6.24-12-generic (SMP w/2 CPU cores)
Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
-------------- next part --------------
diff -u mysql-dfsg-5.0-5.0.51a/debian/patches/00list mysql-dfsg-5.0-5.0.51a/debian/patches/00list
--- mysql-dfsg-5.0-5.0.51a/debian/patches/00list
+++ mysql-dfsg-5.0-5.0.51a/debian/patches/00list
@@ -19,0 +20 @@
+92_fix_order_by32202.dpatch
diff -u mysql-dfsg-5.0-5.0.51a/debian/changelog mysql-dfsg-5.0-5.0.51a/debian/changelog
only in patch2:
unchanged:
--- mysql-dfsg-5.0-5.0.51a.orig/debian/patches/92_fix_order_by32202.dpatch
+++ mysql-dfsg-5.0-5.0.51a/debian/patches/92_fix_order_by32202.dpatch
@@ -0,0 +1,147 @@
+#! /bin/sh /usr/share/dpatch/dpatch-run
+## 92_fix_order_by32202.dpatch by Jamie Strandboge <jamie at ubuntu.com>
+##
+## All lines beginning with `## DP:' are a description of the patch.
+## DP: No description.
+
+ at DPATCH@
+diff -urNad mysql-dfsg-5.0-5.0.51a~/mysql-test/r/group_by.result mysql-dfsg-5.0-5.0.51a/mysql-test/r/group_by.result
+--- mysql-dfsg-5.0-5.0.51a~/mysql-test/r/group_by.result 2008-01-11 10:23:35.000000000 -0500
++++ mysql-dfsg-5.0-5.0.51a/mysql-test/r/group_by.result 2008-03-19 16:18:19.000000000 -0400
+@@ -1113,3 +1113,68 @@
+ 3 1
+ 3 2
+ DROP TABLE t1;
++CREATE TABLE t1(
++id INT AUTO_INCREMENT PRIMARY KEY,
++c1 INT NOT NULL,
++c2 INT NOT NULL,
++UNIQUE KEY (c2,c1));
++INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
++SELECT * FROM t1 ORDER BY c1;
++id c1 c2
++5 1 3
++4 2 3
++3 3 5
++2 4 1
++1 5 1
++SELECT * FROM t1 GROUP BY id ORDER BY c1;
++id c1 c2
++5 1 3
++4 2 3
++3 3 5
++2 4 1
++1 5 1
++SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
++id c1 c2
++5 1 3
++4 2 3
++3 3 5
++2 4 1
++1 5 1
++SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
++id c1 c2
++2 4 1
++1 5 1
++5 1 3
++4 2 3
++3 3 5
++SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
++id c1 c2
++3 3 5
++5 1 3
++4 2 3
++2 4 1
++1 5 1
++SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
++id c1 c2
++3 3 5
++4 2 3
++5 1 3
++1 5 1
++2 4 1
++SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1;
++id c1 c2
++1 5 1
++4 2 3
++3 3 5
++SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1;
++id c1 c2
++3 3 5
++4 2 3
++1 5 1
++SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;
++id c1 c2
++3 3 5
++4 2 3
++1 5 1
++DROP TABLE t1;
++End of 5.0 tests
+diff -urNad mysql-dfsg-5.0-5.0.51a~/mysql-test/t/group_by.test mysql-dfsg-5.0-5.0.51a/mysql-test/t/group_by.test
+--- mysql-dfsg-5.0-5.0.51a~/mysql-test/t/group_by.test 2008-01-11 10:23:11.000000000 -0500
++++ mysql-dfsg-5.0-5.0.51a/mysql-test/t/group_by.test 2008-03-19 16:18:19.000000000 -0400
+@@ -815,3 +815,38 @@
+ SELECT c,b FROM t1 GROUP BY c,b;
+
+ DROP TABLE t1;
++
++#
++# Bug #32202: ORDER BY not working with GROUP BY
++#
++
++CREATE TABLE t1(
++ id INT AUTO_INCREMENT PRIMARY KEY,
++ c1 INT NOT NULL,
++ c2 INT NOT NULL,
++ UNIQUE KEY (c2,c1));
++
++INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
++
++# Show that the test cases from the bug report pass
++SELECT * FROM t1 ORDER BY c1;
++SELECT * FROM t1 GROUP BY id ORDER BY c1;
++
++# Show that DESC is handled correctly
++SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
++
++# Show that results are correctly ordered when ORDER BY fields
++# are a subset of GROUP BY ones
++SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
++SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
++SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
++
++# Show that results are correctly ordered when GROUP BY fields
++# are a subset of ORDER BY ones
++SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1;
++SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1;
++SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;
++
++DROP TABLE t1;
++
++--echo End of 5.0 tests
+diff -urNad mysql-dfsg-5.0-5.0.51a~/sql/sql_select.cc mysql-dfsg-5.0-5.0.51a/sql/sql_select.cc
+--- mysql-dfsg-5.0-5.0.51a~/sql/sql_select.cc 2008-03-19 16:18:01.000000000 -0400
++++ mysql-dfsg-5.0-5.0.51a/sql/sql_select.cc 2008-03-19 16:18:19.000000000 -0400
+@@ -1065,10 +1065,19 @@
+ We have found that grouping can be removed since groups correspond to
+ only one row anyway, but we still have to guarantee correct result
+ order. The line below effectively rewrites the query from GROUP BY
+- <fields> to ORDER BY <fields>. One exception is if skip_sort_order is
+- set (see above), then we can simply skip GROUP BY.
++ <fields> to ORDER BY <fields>. There are two exceptions:
++ - if skip_sort_order is set (see above), then we can simply skip
++ GROUP BY;
++ - we can only rewrite ORDER BY if the ORDER BY fields are 'compatible'
++ with the GROUP BY ones, i.e. either one is a prefix of another.
++ We only check if the ORDER BY is a prefix of GROUP BY. In this case
++ test_if_subpart() copies the ASC/DESC attributes from the original
++ ORDER BY fields.
++ If GROUP BY is a prefix of ORDER BY, then it is safe to leave
++ 'order' as is.
+ */
+- order= skip_sort_order ? 0 : group_list;
++ if (!order || test_if_subpart(group_list, order))
++ order= skip_sort_order ? 0 : group_list;
+ group_list= 0;
+ group= 0;
+ }
More information about the pkg-mysql-maint
mailing list