« back to all changes in this revision
Viewing changes to debian/patches/92_fix_order_by32202.dpatch
- browse files at revision 639
- compare with another revision
- download diff
- download tarball
- view history from revision 639
- Committer: Mathias Gug
- Date: 2008-04-03 18:23:23 UTC
- Revision ID: mathias.gug@canonical.com-20080403182323-bi1y8v4ip41rhggx
fix for ORDER BY not working with GROUP BY (LP: #202706)
- files added:
- debian/patches/92_fix_order_by32202.dpatch
- files modified:
- debian/patches/00list
added
removed
debian/patches/92_fix_order_by32202.dpatch
1
#! /bin/sh /usr/share/dpatch/dpatch-run
2
## 92_fix_order_by32202.dpatch by Jamie Strandboge <jamie@ubuntu.com>
3
##
4
## All lines beginning with `## DP:' are a description of the patch.
5
## DP: No description.
6
7
@DPATCH@
8
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
9
--- mysql-dfsg-5.0-5.0.51a~/mysql-test/r/group_by.result 2008-01-11 10:23:35.000000000 -0500
10
+++ mysql-dfsg-5.0-5.0.51a/mysql-test/r/group_by.result 2008-03-17 07:41:43.000000000 -0400
11
@@ -1113,3 +1113,68 @@
12
3 1
13
3 2
14
DROP TABLE t1;
15
+CREATE TABLE t1(
16
+id INT AUTO_INCREMENT PRIMARY KEY,
17
+c1 INT NOT NULL,
18
+c2 INT NOT NULL,
19
+UNIQUE KEY (c2,c1));
20
+INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
21
+SELECT * FROM t1 ORDER BY c1;
22
+id c1 c2
23
+5 1 3
24
+4 2 3
25
+3 3 5
26
+2 4 1
27
+1 5 1
28
+SELECT * FROM t1 GROUP BY id ORDER BY c1;
29
+id c1 c2
30
+5 1 3
31
+4 2 3
32
+3 3 5
33
+2 4 1
34
+1 5 1
35
+SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
36
+id c1 c2
37
+5 1 3
38
+4 2 3
39
+3 3 5
40
+2 4 1
41
+1 5 1
42
+SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
43
+id c1 c2
44
+2 4 1
45
+1 5 1
46
+5 1 3
47
+4 2 3
48
+3 3 5
49
+SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
50
+id c1 c2
51
+3 3 5
52
+5 1 3
53
+4 2 3
54
+2 4 1
55
+1 5 1
56
+SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
57
+id c1 c2
58
+3 3 5
59
+4 2 3
60
+5 1 3
61
+1 5 1
62
+2 4 1
63
+SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1;
64
+id c1 c2
65
+1 5 1
66
+4 2 3
67
+3 3 5
68
+SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1;
69
+id c1 c2
70
+3 3 5
71
+4 2 3
72
+1 5 1
73
+SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;
74
+id c1 c2
75
+3 3 5
76
+4 2 3
77
+1 5 1
78
+DROP TABLE t1;
79
+End of 5.0 tests
80
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
81
--- mysql-dfsg-5.0-5.0.51a~/mysql-test/t/group_by.test 2008-01-11 10:23:11.000000000 -0500
82
+++ mysql-dfsg-5.0-5.0.51a/mysql-test/t/group_by.test 2008-03-17 07:41:43.000000000 -0400
83
@@ -815,3 +815,38 @@
84
SELECT c,b FROM t1 GROUP BY c,b;
85
86
DROP TABLE t1;
87
+
88
+#
89
+# Bug #32202: ORDER BY not working with GROUP BY
90
+#
91
+
92
+CREATE TABLE t1(
93
+ id INT AUTO_INCREMENT PRIMARY KEY,
94
+ c1 INT NOT NULL,
95
+ c2 INT NOT NULL,
96
+ UNIQUE KEY (c2,c1));
97
+
98
+INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
99
+
100
+# Show that the test cases from the bug report pass
101
+SELECT * FROM t1 ORDER BY c1;
102
+SELECT * FROM t1 GROUP BY id ORDER BY c1;
103
+
104
+# Show that DESC is handled correctly
105
+SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
106
+
107
+# Show that results are correctly ordered when ORDER BY fields
108
+# are a subset of GROUP BY ones
109
+SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
110
+SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
111
+SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
112
+
113
+# Show that results are correctly ordered when GROUP BY fields
114
+# are a subset of ORDER BY ones
115
+SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1;
116
+SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1;
117
+SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;
118
+
119
+DROP TABLE t1;
120
+
121
+--echo End of 5.0 tests
122
diff -urNad mysql-dfsg-5.0-5.0.51a~/sql/sql_select.cc mysql-dfsg-5.0-5.0.51a/sql/sql_select.cc
123
--- mysql-dfsg-5.0-5.0.51a~/sql/sql_select.cc 2008-03-17 07:41:25.000000000 -0400
124
+++ mysql-dfsg-5.0-5.0.51a/sql/sql_select.cc 2008-03-17 07:41:43.000000000 -0400
125
@@ -1065,10 +1065,19 @@
126
We have found that grouping can be removed since groups correspond to
127
only one row anyway, but we still have to guarantee correct result
128
order. The line below effectively rewrites the query from GROUP BY
129
- <fields> to ORDER BY <fields>. One exception is if skip_sort_order is
130
- set (see above), then we can simply skip GROUP BY.
131
+ <fields> to ORDER BY <fields>. There are two exceptions:
132
+ - if skip_sort_order is set (see above), then we can simply skip
133
+ GROUP BY;
134
+ - we can only rewrite ORDER BY if the ORDER BY fields are 'compatible'
135
+ with the GROUP BY ones, i.e. either one is a prefix of another.
136
+ We only check if the ORDER BY is a prefix of GROUP BY. In this case
137
+ test_if_subpart() copies the ASC/DESC attributes from the original
138
+ ORDER BY fields.
139
+ If GROUP BY is a prefix of ORDER BY, then it is safe to leave
140
+ 'order' as is.
141
*/
142
- order= skip_sort_order ? 0 : group_list;
143
+ if (!order || test_if_subpart(group_list, order))
144
+ order= skip_sort_order ? 0 : group_list;
145
group_list= 0;
146
group= 0;
147
}
Loggerhead is a web-based interface for Breezy
Version: 2.0.1