Drizzle

Merge lp:~kalebral-deactivatedaccount/drizzle/fix-622005 into lp:~drizzle-trunk/drizzle/development

  1. fix-622005
  2. Merge into development
Proposed by Lee Bieber
Status: Merged
Approved by: Brian Aker
Approved revision: 1719
Merged at revision: 1728
Proposed branch: lp:~kalebral-deactivatedaccount/drizzle/fix-622005
Merge into: lp:~drizzle-trunk/drizzle/development
Diff against target: 1223 lines (+198/-129)
33 files modified
plugin/filesystem_engine/tests/t/multiple_update.data (+1/-1)
tests/r/distinct.result (+2/-2)
tests/r/func_group.result (+2/-2)
tests/r/func_in.result (+2/-2)
tests/r/func_time.result (+2/-2)
tests/r/greedy_optimizer.result (+20/-20)
tests/r/group_min_max.result (+12/-12)
tests/r/join.result (+8/-8)
tests/r/join_nested.result (+32/-32)
tests/r/join_outer.result (+3/-3)
tests/r/key_diff.result (+17/-17)
tests/r/range.result (+1/-1)
tests/r/select.result (+20/-20)
tests/r/subselect.result (+1/-1)
tests/r/type_blob.result (+4/-4)
tests/r/type_datetime.result (+2/-2)
tests/t/derived.test (+3/-0)
tests/t/distinct.test (+2/-0)
tests/t/func_group.test (+2/-0)
tests/t/func_in.test (+2/-0)
tests/t/func_time.test (+2/-0)
tests/t/greedy_optimizer.test (+25/-0)
tests/t/group_by.test (+2/-0)
tests/t/group_min_max.test (+1/-0)
tests/t/join.test (+7/-0)
tests/t/join_nested.test (+13/-0)
tests/t/join_outer.test (+3/-0)
tests/t/key_diff.test (+1/-0)
tests/t/range.test (+1/-0)
tests/t/select.test (+2/-0)
tests/t/subselect.test (+1/-0)
tests/t/type_blob.test (+1/-0)
tests/t/type_datetime.test (+1/-0)
To merge this branch: bzr merge lp:~kalebral-deactivatedaccount/drizzle/fix-622005
Reviewer Review Type Date Requested Status
Drizzle Merge Team Pending
Review via email: mp+33316@code.launchpad.net

Description of the change

For the feature request (https://blueprints.launchpad.net/drizzle/+spec/limit-maximum-sort-size) that is requesting the ability to cap various buffers, we first tried setting the join buffer to 1 to see how that would affect the test results and expose test results that need to sorted (by adding --sorted_result).

To post a comment you must log in.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'plugin/filesystem_engine/tests/t/multiple_update.data'
2--- plugin/filesystem_engine/tests/t/multiple_update.data 2010-06-18 04:58:30 +0000
3+++ plugin/filesystem_engine/tests/t/multiple_update.data 2010-08-22 01:04:46 +0000
4@@ -1,4 +1,4 @@
5-777
6 555
7 333
8 111
9+777
10
11=== modified file 'tests/r/distinct.result'
12--- tests/r/distinct.result 2010-08-09 16:31:29 +0000
13+++ tests/r/distinct.result 2010-08-22 01:04:46 +0000
14@@ -397,8 +397,8 @@
15 SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID;
16 email shipcode
17 test1@testdomain.com Z001
18+test2@testdomain.com R002
19 test2@testdomain.com Z001
20-test2@testdomain.com R002
21 test3@testdomain.com Z001
22 SELECT DISTINCTROW email FROM t1 ORDER BY dateentered DESC;
23 email
24@@ -529,8 +529,8 @@
25 EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2
26 WHERE t1_1.a = t1_2.a;
27 id select_type table type possible_keys key key_len ref rows Extra
28+1 SIMPLE t1_1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
29 1 SIMPLE t1_2 index PRIMARY PRIMARY 4 NULL 3 Using index; Using temporary
30-1 SIMPLE t1_1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
31 EXPLAIN SELECT a FROM t1 GROUP BY a;
32 id select_type table type possible_keys key key_len ref rows Extra
33 1 SIMPLE t1 index NULL PRIMARY 4 NULL 3 Using index
34
35=== modified file 'tests/r/func_group.result'
36--- tests/r/func_group.result 2010-08-20 18:39:24 +0000
37+++ tests/r/func_group.result 2010-08-22 01:04:46 +0000
38@@ -192,10 +192,10 @@
39 insert into t2 values('BBB', 20, 1.0);
40 select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2;
41 a1 a2 a1 a2
42+10 NULL AAA 10
43+10 NULL BBB 20
44 10 aaa AAA 10
45 10 aaa BBB 20
46-10 NULL AAA 10
47-10 NULL BBB 20
48 10 bbb AAA 10
49 10 bbb BBB 20
50 20 zzz AAA 10
51
52=== modified file 'tests/r/func_in.result'
53--- tests/r/func_in.result 2009-12-15 16:55:05 +0000
54+++ tests/r/func_in.result 2010-08-22 01:04:46 +0000
55@@ -349,9 +349,9 @@
56 JOIN t2 ON t3.a=t2.a
57 JOIN t4 WHERE t4.a IN (t1.b, t2.b);
58 id select_type table type possible_keys key key_len ref rows Extra
59-1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
60 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
61 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer
62+1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
63 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1)
64 SELECT STRAIGHT_JOIN * FROM t3
65 JOIN t1 ON t3.a=t1.a
66@@ -367,9 +367,9 @@
67 FROM t3, t1, t2
68 WHERE t3.a=t1.a AND t3.a=t2.a;
69 id select_type table type possible_keys key key_len ref rows Extra
70-1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
71 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
72 1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer
73+1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
74 2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index
75 SELECT STRAIGHT_JOIN
76 (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
77
78=== modified file 'tests/r/func_time.result'
79--- tests/r/func_time.result 2010-07-12 06:26:42 +0000
80+++ tests/r/func_time.result 2010-08-22 01:04:46 +0000
81@@ -805,18 +805,18 @@
82 '2005.09.01' - INTERVAL 6 MONTH AND t2.day;
83 id day id day
84 1 2005-06-01 1 2005-08-01
85-3 2005-07-01 1 2005-08-01
86 1 2005-06-01 2 2005-06-15
87 1 2005-06-01 3 2005-07-15
88+3 2005-07-01 1 2005-08-01
89 3 2005-07-01 3 2005-07-15
90 SELECT * FROM t1, t2
91 WHERE CAST(t1.day AS DATE) BETWEEN
92 '2005.09.01' - INTERVAL 6 MONTH AND t2.day;
93 id day id day
94 1 2005-06-01 1 2005-08-01
95-3 2005-07-01 1 2005-08-01
96 1 2005-06-01 2 2005-06-15
97 1 2005-06-01 3 2005-07-15
98+3 2005-07-01 1 2005-08-01
99 3 2005-07-01 3 2005-07-15
100 DROP TABLE t1,t2;
101 create table t1 (field DATE);
102
103=== modified file 'tests/r/greedy_optimizer.result'
104--- tests/r/greedy_optimizer.result 2010-02-15 18:29:31 +0000
105+++ tests/r/greedy_optimizer.result 2010-08-22 01:04:46 +0000
106@@ -224,10 +224,10 @@
107 Last_query_cost #
108 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
109 id select_type table type possible_keys key key_len ref rows Extra
110+1 # t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
111 1 # t2 ALL NULL NULL NULL NULL 6
112+1 # t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
113 1 # t4 ALL NULL NULL NULL NULL 12 Using join buffer
114-1 # t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
115-1 # t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
116 1 # t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
117 1 # t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
118 1 # t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
119@@ -236,10 +236,10 @@
120 Last_query_cost #
121 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
122 id select_type table type possible_keys key key_len ref rows Extra
123+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
124 1 SIMPLE t2 ALL NULL NULL NULL NULL 6
125+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
126 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
127-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
128-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
129 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
130 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
131 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
132@@ -248,10 +248,10 @@
133 Last_query_cost #
134 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
135 id select_type table type possible_keys key key_len ref rows Extra
136+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
137 1 SIMPLE t2 ALL NULL NULL NULL NULL 6
138+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
139 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
140-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
141-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
142 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
143 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
144 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
145@@ -301,12 +301,12 @@
146 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
147 id select_type table type possible_keys key key_len ref rows Extra
148 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
149+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
150 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
151+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
152 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
153+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
154 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
155-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
156-1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
157-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
158 show status like 'Last_query_cost';
159 Variable_name Value
160 Last_query_cost #
161@@ -325,12 +325,12 @@
162 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
163 id select_type table type possible_keys key key_len ref rows Extra
164 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
165+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
166 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
167+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
168 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
169+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
170 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
171-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
172-1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
173-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
174 show status like 'Last_query_cost';
175 Variable_name Value
176 Last_query_cost #
177@@ -376,10 +376,10 @@
178 Last_query_cost #
179 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
180 id select_type table type possible_keys key key_len ref rows Extra
181+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
182 1 SIMPLE t2 ALL NULL NULL NULL NULL 6
183+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
184 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
185-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
186-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
187 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
188 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
189 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
190@@ -388,10 +388,10 @@
191 Last_query_cost #
192 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
193 id select_type table type possible_keys key key_len ref rows Extra
194+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
195 1 SIMPLE t2 ALL NULL NULL NULL NULL 6
196+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
197 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
198-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
199-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
200 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
201 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
202 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
203@@ -400,10 +400,10 @@
204 Last_query_cost #
205 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
206 id select_type table type possible_keys key key_len ref rows Extra
207+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
208 1 SIMPLE t2 ALL NULL NULL NULL NULL 6
209+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
210 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
211-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
212-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
213 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
214 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
215 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
216@@ -412,10 +412,10 @@
217 Last_query_cost #
218 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
219 id select_type table type possible_keys key key_len ref rows Extra
220+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
221 1 SIMPLE t2 ALL NULL NULL NULL NULL 6
222+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
223 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
224-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
225-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
226 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
227 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
228 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
229
230=== modified file 'tests/r/group_min_max.result'
231--- tests/r/group_min_max.result 2009-11-26 02:37:40 +0000
232+++ tests/r/group_min_max.result 2010-08-22 01:04:46 +0000
233@@ -1611,24 +1611,24 @@
234 select distinct t1.a1,t2.a1 from t1,t2;
235 a1 a1
236 a a
237-b a
238-c a
239-d a
240 a b
241-b b
242-c b
243-d b
244 a c
245+a d
246+a e
247+b a
248+b b
249 b c
250-c c
251-d c
252-a d
253 b d
254+b e
255+c a
256+c b
257+c c
258 c d
259-d d
260-a e
261-b e
262 c e
263+d a
264+d b
265+d c
266+d d
267 d e
268 explain select distinct a1,a2,b from t1;
269 id select_type table type possible_keys key key_len ref rows Extra
270
271=== modified file 'tests/r/join.result'
272--- tests/r/join.result 2010-02-15 18:29:31 +0000
273+++ tests/r/join.result 2010-08-22 01:04:46 +0000
274@@ -599,30 +599,30 @@
275 3 1 2 2
276 select * from (t1 cross join t2) join (t3 cross join t4) on (a < y and t2.b < t3.c);
277 c b a b b c y c
278+10 1 2 1 1 10 11 3
279 10 1 2 1 1 3 11 3
280-10 1 2 1 1 10 11 3
281+3 1 2 1 1 10 11 3
282 3 1 2 1 1 3 11 3
283-3 1 2 1 1 10 11 3
284+3 2 2 1 1 10 11 3
285 3 2 2 1 1 3 11 3
286-3 2 2 1 1 10 11 3
287 select * from (t1, t2) join (t3, t4) on (a < y and t2.b < t3.c);
288 c b a b b c y c
289+10 1 2 1 1 10 11 3
290 10 1 2 1 1 3 11 3
291-10 1 2 1 1 10 11 3
292+3 1 2 1 1 10 11 3
293 3 1 2 1 1 3 11 3
294-3 1 2 1 1 10 11 3
295+3 2 2 1 1 10 11 3
296 3 2 2 1 1 3 11 3
297-3 2 2 1 1 10 11 3
298 select * from (t1 natural join t2) join (t3 natural join t4) on a = y;
299 b c a c b y
300 1 10 2 3 1 2
301 1 3 2 3 1 2
302 select * from ((t3 join (t1 join t2 on c > a) on t3.b < t2.a) join t4 on y > t1.c) join t5 on z = t1.b + 3;
303 b c c b a b y c y z
304+1 10 10 1 2 1 11 3 11 4
305+1 10 3 1 2 1 11 3 11 4
306 1 3 10 1 2 1 11 3 11 4
307-1 10 10 1 2 1 11 3 11 4
308 1 3 3 1 2 1 11 3 11 4
309-1 10 3 1 2 1 11 3 11 4
310 select * from t1 natural join t2 where t1.b > 0;
311 b c a
312 1 10 2
313
314=== modified file 'tests/r/join_nested.result'
315--- tests/r/join_nested.result 2010-08-09 16:31:29 +0000
316+++ tests/r/join_nested.result 2010-08-22 01:04:46 +0000
317@@ -39,8 +39,8 @@
318 FROM t3,t4;
319 a b a b
320 1 2 3 2
321+1 2 4 2
322 2 2 3 2
323-1 2 4 2
324 2 2 4 2
325 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
326 FROM t2
327@@ -108,17 +108,17 @@
328 SELECT t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
329 FROM t3,t4,t5;
330 a b a b a b
331+1 2 3 2 2 2
332 1 2 3 2 3 1
333-2 2 3 2 3 1
334+1 2 3 2 3 3
335+1 2 4 2 2 2
336 1 2 4 2 3 1
337-2 2 4 2 3 1
338-1 2 3 2 2 2
339+1 2 4 2 3 3
340 2 2 3 2 2 2
341-1 2 4 2 2 2
342-2 2 4 2 2 2
343-1 2 3 2 3 3
344+2 2 3 2 3 1
345 2 2 3 2 3 3
346-1 2 4 2 3 3
347+2 2 4 2 2 2
348+2 2 4 2 3 1
349 2 2 4 2 3 3
350 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
351 FROM t2
352@@ -212,10 +212,10 @@
353 a b a b
354 3 2 1 1
355 3 2 2 2
356+6 1 1 1
357+6 1 2 2
358 6 2 1 1
359 6 2 2 2
360-6 1 1 1
361-6 1 2 2
362 SELECT t8.a,t8.b
363 FROM t8;
364 a b
365@@ -228,11 +228,11 @@
366 t8
367 ON t7.b=t8.b AND t6.b < 10;
368 id select_type table type possible_keys key key_len ref rows filtered Extra
369-1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00
370 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using join buffer
371+1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00
372 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00
373-Warnings:
374 Note 1003 select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10))) where 1
375+Warnings:
376 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
377 FROM (t6, t7)
378 LEFT JOIN
379@@ -361,17 +361,17 @@
380 WHERE t2.a > 3 AND
381 (t6.a < 6 OR t6.c IS NULL);
382 a b a b a b a b a b a b a b
383-4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
384-4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
385-5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
386 4 2 1 2 3 2 2 2 3 2 2 2 0 2
387 4 2 1 2 3 2 2 2 3 2 2 2 1 2
388+4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
389+4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
390 4 2 1 2 4 2 2 2 3 2 2 2 0 2
391 4 2 1 2 4 2 2 2 3 2 2 2 1 2
392+4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
393+4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
394 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
395 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
396-4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
397-4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
398+5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
399 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
400 SELECT t1.a,t1.b
401 FROM t1;
402@@ -644,12 +644,12 @@
403 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where
404 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where
405 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where
406+1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where
407 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where
408-1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where
409 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where
410 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
411+Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
412 Warnings:
413-Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
414 SELECT t9.a,t9.b
415 FROM t9;
416 a b
417@@ -743,12 +743,12 @@
418 WHERE t1.a <= 2;
419 a b a b a b
420 1 3 3 3 NULL NULL
421-2 2 3 3 NULL NULL
422 1 3 4 2 1 2
423 1 3 4 2 2 2
424+1 3 5 3 NULL NULL
425+2 2 3 3 NULL NULL
426 2 2 4 2 1 2
427 2 2 4 2 2 2
428-1 3 5 3 NULL NULL
429 2 2 5 3 NULL NULL
430 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
431 FROM t1, t3
432@@ -758,12 +758,12 @@
433 WHERE t1.a <= 2;
434 a b a b a b
435 1 3 3 3 NULL NULL
436-2 2 3 3 NULL NULL
437 1 3 4 2 1 2
438 1 3 4 2 2 2
439+1 3 5 3 NULL NULL
440+2 2 3 3 NULL NULL
441 2 2 4 2 1 2
442 2 2 4 2 2 2
443-1 3 5 3 NULL NULL
444 2 2 5 3 NULL NULL
445 SELECT t3.a,t3.b,t4.a,t4.b
446 FROM t3,t4;
447@@ -790,12 +790,12 @@
448 WHERE t1.a <= 2;
449 a b a b a b a b
450 1 3 3 3 NULL NULL NULL NULL
451-2 2 3 3 NULL NULL NULL NULL
452 1 3 4 2 1 2 3 2
453 1 3 4 2 1 2 4 2
454+1 3 5 3 NULL NULL NULL NULL
455+2 2 3 3 NULL NULL NULL NULL
456 2 2 4 2 1 2 3 2
457 2 2 4 2 1 2 4 2
458-1 3 5 3 NULL NULL NULL NULL
459 2 2 5 3 NULL NULL NULL NULL
460 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
461 FROM t1, (t3, t4)
462@@ -805,12 +805,12 @@
463 WHERE t1.a <= 2;
464 a b a b a b a b
465 1 3 3 3 NULL NULL NULL NULL
466-2 2 3 3 NULL NULL NULL NULL
467 1 3 4 2 1 2 3 2
468 1 3 4 2 1 2 4 2
469+1 3 5 3 NULL NULL NULL NULL
470+2 2 3 3 NULL NULL NULL NULL
471 2 2 4 2 1 2 3 2
472 2 2 4 2 1 2 4 2
473-1 3 5 3 NULL NULL NULL NULL
474 2 2 5 3 NULL NULL NULL NULL
475 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
476 FROM t1, (t3, t4)
477@@ -849,12 +849,12 @@
478 (t1,t2)
479 ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
480 id select_type table type possible_keys key key_len ref rows filtered Extra
481+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
482+1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 1 100.00
483 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00
484 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer
485-1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 1 100.00
486-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
487-Warnings:
488 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where 1
489+Warnings:
490 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
491 FROM (t3,t4)
492 LEFT JOIN
493@@ -911,12 +911,12 @@
494 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where
495 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where
496 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where
497+1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where
498 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where
499-1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where
500 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where
501 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
502+Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
503 Warnings:
504-Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
505 CREATE INDEX idx_b ON t4(b);
506 CREATE INDEX idx_b ON t5(b);
507 EXPLAIN EXTENDED
508
509=== modified file 'tests/r/join_outer.result'
510--- tests/r/join_outer.result 2010-02-15 18:29:31 +0000
511+++ tests/r/join_outer.result 2010-08-22 01:04:46 +0000
512@@ -462,19 +462,19 @@
513 7 green
514 select * from t2 natural join t1;
515 color count name
516+black 5 grape
517 green 10 lime
518 green 7 lime
519-black 5 grape
520 select t2.count, t1.name from t2 natural join t1;
521 count name
522 10 lime
523+5 grape
524 7 lime
525-5 grape
526 select t2.count, t1.name from t2 inner join t1 using (color);
527 count name
528 10 lime
529+5 grape
530 7 lime
531-5 grape
532 drop table t1;
533 drop table t2;
534 CREATE TABLE t1 (
535
536=== modified file 'tests/r/key_diff.result'
537--- tests/r/key_diff.result 2008-12-06 22:41:03 +0000
538+++ tests/r/key_diff.result 2010-08-22 01:04:46 +0000
539@@ -9,30 +9,30 @@
540 select * from t1,t1 as t2;
541 a b a b
542 A B A B
543-b A A B
544+A B C c
545+A B D E
546+A B a a
547+A B b A
548 C c A B
549+C c C c
550+C c D E
551+C c a a
552+C c b A
553 D E A B
554+D E C c
555+D E D E
556+D E a a
557+D E b A
558 a a A B
559-A B b A
560-b A b A
561-C c b A
562-D E b A
563+a a C c
564+a a D E
565+a a a a
566 a a b A
567-A B C c
568+b A A B
569 b A C c
570-C c C c
571-D E C c
572-a a C c
573-A B D E
574 b A D E
575-C c D E
576-D E D E
577-a a D E
578-A B a a
579 b A a a
580-C c a a
581-D E a a
582-a a a a
583+b A b A
584 explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B;
585 id select_type table type possible_keys key key_len ref rows Extra
586 1 SIMPLE t1 ALL a NULL NULL NULL 5
587
588=== modified file 'tests/r/range.result'
589--- tests/r/range.result 2010-08-09 16:31:29 +0000
590+++ tests/r/range.result 2010-08-22 01:04:46 +0000
591@@ -694,8 +694,8 @@
592 v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
593 s.oxleft > v.oxleft AND s.oxleft < v.oxright;
594 id select_type table type possible_keys key key_len ref rows Extra
595+1 SIMPLE # ALL OXLEFT NULL NULL # # Range checked for each record (index map: 0x4)
596 1 SIMPLE # ALL OXLEFT,OXRIGHT,OXROOTID NULL NULL # # Using where
597-1 SIMPLE # ALL OXLEFT NULL NULL # # Range checked for each record (index map: 0x4)
598 SELECT s.oxid FROM t1 v, t1 s
599 WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
600 v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
601
602=== modified file 'tests/r/select.result'
603--- tests/r/select.result 2010-08-19 20:36:05 +0000
604+++ tests/r/select.result 2010-08-22 01:04:46 +0000
605@@ -1272,29 +1272,29 @@
606 select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505;
607 fld1 fld1
608 250501 250501
609+250501 250502
610+250501 250503
611+250501 250504
612+250501 250505
613 250502 250501
614+250502 250502
615+250502 250503
616+250502 250504
617+250502 250505
618 250503 250501
619+250503 250502
620+250503 250503
621+250503 250504
622+250503 250505
623 250504 250501
624+250504 250502
625+250504 250503
626+250504 250504
627+250504 250505
628 250505 250501
629-250501 250502
630-250502 250502
631-250503 250502
632-250504 250502
633 250505 250502
634-250501 250503
635-250502 250503
636-250503 250503
637-250504 250503
638 250505 250503
639-250501 250504
640-250502 250504
641-250503 250504
642-250504 250504
643 250505 250504
644-250501 250505
645-250502 250505
646-250503 250505
647-250504 250505
648 250505 250505
649 insert into t2 (fld1, companynr) values (999999,99);
650 select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
651@@ -2014,13 +2014,13 @@
652 select * from (t1 as t2 left join t1 as t3 using (a)), t1;
653 a a
654 1 1
655+1 2
656+1 3
657 2 1
658+2 2
659+2 3
660 3 1
661-1 2
662-2 2
663 3 2
664-1 3
665-2 3
666 3 3
667 select * from t1, (t1 as t2 left join t1 as t3 using (a));
668 a a
669
670=== modified file 'tests/r/subselect.result'
671--- tests/r/subselect.result 2010-08-19 20:36:05 +0000
672+++ tests/r/subselect.result 2010-08-22 01:04:46 +0000
673@@ -4526,8 +4526,8 @@
674 SELECT * FROM t1 JOIN (t1 t1a) ON 1;
675 a a
676 1 1
677+1 2
678 2 1
679-1 2
680 2 2
681 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;
682 a a
683
684=== modified file 'tests/r/type_blob.result'
685--- tests/r/type_blob.result 2010-07-27 00:57:00 +0000
686+++ tests/r/type_blob.result 2010-08-22 01:04:46 +0000
687@@ -46,13 +46,13 @@
688 select * from t1,t1 as t2;
689 nr b str nr b str
690 1 a A 1 a A
691+1 a A 2 bbb BBB
692+1 a A 3 ccc CCC
693 2 bbb BBB 1 a A
694+2 bbb BBB 2 bbb BBB
695+2 bbb BBB 3 ccc CCC
696 3 ccc CCC 1 a A
697-1 a A 2 bbb BBB
698-2 bbb BBB 2 bbb BBB
699 3 ccc CCC 2 bbb BBB
700-1 a A 3 ccc CCC
701-2 bbb BBB 3 ccc CCC
702 3 ccc CCC 3 ccc CCC
703 drop table t1;
704 create table t1 (a text);
705
706=== modified file 'tests/r/type_datetime.result'
707--- tests/r/type_datetime.result 2010-07-28 23:57:35 +0000
708+++ tests/r/type_datetime.result 2010-08-22 01:04:46 +0000
709@@ -268,10 +268,10 @@
710 select f1,f2,f3 from t1,t2,t3 where (f1,'1') in ((f2,'1'),(f3,'1'));
711 f1 f2 f3
712 2001-01-01 2001-01-01 00:00:00 2001-01-01 00:00:00
713+2001-01-01 2001-01-01 00:00:00 2001-02-03 12:34:56
714+2001-01-01 2001-01-01 00:00:00 2002-04-06 11:22:33
715 2001-01-01 2001-02-03 12:34:56 2001-01-01 00:00:00
716 2001-01-01 2002-04-06 11:22:33 2001-01-01 00:00:00
717-2001-01-01 2001-01-01 00:00:00 2001-02-03 12:34:56
718-2001-01-01 2001-01-01 00:00:00 2002-04-06 11:22:33
719 select f1 from t1 where ('1',f1) in (('1','01-01-01'),('1','2001-1-1 0:0:0'),('1','02-02-02'));
720 f1
721 2001-01-01
722
723=== modified file 'tests/t/derived.test'
724--- tests/t/derived.test 2009-08-04 02:33:47 +0000
725+++ tests/t/derived.test 2010-08-22 01:04:46 +0000
726@@ -27,6 +27,7 @@
727 SELECT 1 FROM (SELECT 1) a WHERE a=2;
728 --error 1054
729 SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) as a;
730+--sort_result
731 select * from t1 as x1, (select * from t1) as x2;
732 explain select * from t1 as x1, (select * from t1) as x2;
733 drop table if exists t2,t3;
734@@ -116,12 +117,14 @@
735 create table t1 (E1 INTEGER NOT NULL, E2 INTEGER NOT NULL, E3 INTEGER NOT NULL, PRIMARY KEY(E1)
736 );
737 insert into t1 VALUES(1,1,1), (2,2,1);
738+--sort_result
739 select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2;
740 explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2;
741 drop table t1;
742
743 create table t1 (a int);
744 insert into t1 values (1),(2);
745+--sort_result
746 select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
747 explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
748 drop table t1;
749
750=== modified file 'tests/t/distinct.test'
751--- tests/t/distinct.test 2010-02-15 18:29:31 +0000
752+++ tests/t/distinct.test 2010-08-22 01:04:46 +0000
753@@ -235,6 +235,7 @@
754 (1, 'Z001'),
755 (2, 'R002');
756
757+--sorted_result
758 SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID;
759 SELECT DISTINCTROW email FROM t1 ORDER BY dateentered DESC;
760 SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID ORDER BY dateentered DESC;
761@@ -345,6 +346,7 @@
762 EXPLAIN SELECT DISTINCT a FROM t1;
763 EXPLAIN SELECT DISTINCT a,b FROM t1;
764 EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2;
765+--sorted_result
766 EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2
767 WHERE t1_1.a = t1_2.a;
768 EXPLAIN SELECT a FROM t1 GROUP BY a;
769
770=== modified file 'tests/t/func_group.test'
771--- tests/t/func_group.test 2010-08-18 21:57:15 +0000
772+++ tests/t/func_group.test 2010-08-22 01:04:46 +0000
773@@ -128,6 +128,7 @@
774
775 insert into t2 values('AAA', 10, 0.5);
776 insert into t2 values('BBB', 20, 1.0);
777+--sorted_result
778 select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2;
779
780 select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9;
781@@ -629,6 +630,7 @@
782 CREATE TABLE t2 (a INT PRIMARY KEY, b INT);
783 INSERT INTO t2 VALUES (1,1), (3,3);
784
785+--sorted_result
786 SELECT
787 (SELECT SUM(c.a) FROM t1 ttt, t2 ccc
788 WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid
789
790=== modified file 'tests/t/func_in.test'
791--- tests/t/func_in.test 2009-07-03 13:13:20 +0000
792+++ tests/t/func_in.test 2010-08-22 01:04:46 +0000
793@@ -262,6 +262,7 @@
794 INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002),
795 (1003,1003),(1004,1004);
796
797+--sorted_result
798 EXPLAIN SELECT STRAIGHT_JOIN * FROM t3
799 JOIN t1 ON t3.a=t1.a
800 JOIN t2 ON t3.a=t2.a
801@@ -272,6 +273,7 @@
802 JOIN t2 ON t3.a=t2.a
803 JOIN t4 WHERE t4.a IN (t1.b, t2.b);
804
805+--sorted_result
806 EXPLAIN SELECT STRAIGHT_JOIN
807 (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
808 FROM t3, t1, t2
809
810=== modified file 'tests/t/func_time.test'
811--- tests/t/func_time.test 2009-06-16 00:53:22 +0000
812+++ tests/t/func_time.test 2010-08-22 01:04:46 +0000
813@@ -498,9 +498,11 @@
814 INSERT INTO t2 VALUES
815 (1, '2005-08-01'), (2, '2005-06-15'), (3, '2005-07-15');
816
817+--sorted_result
818 SELECT * FROM t1, t2
819 WHERE t1.day BETWEEN
820 '2005.09.01' - INTERVAL 6 MONTH AND t2.day;
821+--sorted_result
822 SELECT * FROM t1, t2
823 WHERE CAST(t1.day AS DATE) BETWEEN
824 '2005.09.01' - INTERVAL 6 MONTH AND t2.day;
825
826=== modified file 'tests/t/greedy_optimizer.test'
827--- tests/t/greedy_optimizer.test 2010-02-15 18:29:31 +0000
828+++ tests/t/greedy_optimizer.test 2010-08-22 01:04:46 +0000
829@@ -173,18 +173,23 @@
830 # 6-table join, chain
831 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
832 --replace_column 2 #
833+--sorted_result
834 show status like 'Last_query_cost';
835 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
836 --replace_column 2 #
837+--sorted_result
838 show status like 'Last_query_cost';
839 # 6-table join, star
840+--sorted_result
841 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
842 --replace_column 2 #
843 show status like 'Last_query_cost';
844+--sorted_result
845 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
846 --replace_column 2 #
847 show status like 'Last_query_cost';
848 # 6-table join, clique
849+--sorted_result
850 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
851 --replace_column 2 #
852 show status like 'Last_query_cost';
853@@ -202,6 +207,7 @@
854 select @@optimizer_search_depth;
855
856 # 6-table join, chain
857+--sorted_result
858 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
859 --replace_column 2 #
860 show status like 'Last_query_cost';
861@@ -210,15 +216,19 @@
862 show status like 'Last_query_cost';
863 # 6-table join, star
864 --replace_column 2 #
865+--sorted_result
866 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
867 --replace_column 2 #
868 show status like 'Last_query_cost';
869+--sorted_result
870 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
871 --replace_column 2 #
872 show status like 'Last_query_cost';
873+--sorted_result
874 # 6-table join, clique
875 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
876 --replace_column 2 #
877+--sorted_result
878 show status like 'Last_query_cost';
879 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
880 --replace_column 2 #
881@@ -228,22 +238,30 @@
882 select @@optimizer_search_depth;
883
884 # 6-table join, chain
885+--sorted_result
886 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
887 --replace_column 2 #
888+--sorted_result
889 show status like 'Last_query_cost';
890 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
891 --replace_column 2 #
892+--sorted_result
893 show status like 'Last_query_cost';
894 # 6-table join, star
895+--sorted_result
896 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
897 --replace_column 2 #
898+--sorted_result
899 show status like 'Last_query_cost';
900 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
901 --replace_column 2 #
902+--sorted_result
903 show status like 'Last_query_cost';
904 # 6-table join, clique
905+--sorted_result
906 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
907 --replace_column 2 #
908+--sorted_result
909 show status like 'Last_query_cost';
910 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
911 --replace_column 2 #
912@@ -260,16 +278,20 @@
913 --replace_column 2 #
914 show status like 'Last_query_cost';
915 # 6-table join, star
916+--sorted_result
917 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
918 --replace_column 2 #
919 show status like 'Last_query_cost';
920+--sorted_result
921 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
922 --replace_column 2 #
923 show status like 'Last_query_cost';
924 # 6-table join, clique
925+--sorted_result
926 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
927 --replace_column 2 #
928 show status like 'Last_query_cost';
929+--sorted_result
930 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
931 --replace_column 2 #
932 show status like 'Last_query_cost';
933@@ -289,11 +311,14 @@
934 --replace_column 2 #
935 show status like 'Last_query_cost';
936 # 6-table join, star
937+--sorted_result
938 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
939 --replace_column 2 #
940+--sorted_result
941 show status like 'Last_query_cost';
942 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
943 --replace_column 2 #
944+--sorted_result
945 show status like 'Last_query_cost';
946 # 6-table join, clique
947 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
948
949=== modified file 'tests/t/group_by.test'
950--- tests/t/group_by.test 2010-08-19 01:24:09 +0000
951+++ tests/t/group_by.test 2010-08-22 01:04:46 +0000
952@@ -400,7 +400,9 @@
953 insert into t2 values (1,3),(3,1),(2,2),(1,1);
954 select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
955 select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
956+--sorted_result
957 explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
958+--sorted_result
959 explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
960 drop table t1,t2;
961
962
963=== modified file 'tests/t/group_min_max.test'
964--- tests/t/group_min_max.test 2009-11-26 02:37:40 +0000
965+++ tests/t/group_min_max.test 2010-08-22 01:04:46 +0000
966@@ -523,6 +523,7 @@
967 # BUG #8532 - SELECT DISTINCT a, a causes server to crash
968 select distinct a1,a1 from t1;
969 select distinct a2,a1,a2,a1 from t1;
970+--sorted_result
971 select distinct t1.a1,t2.a1 from t1,t2;
972
973
974
975=== modified file 'tests/t/join.test'
976--- tests/t/join.test 2010-02-15 18:29:31 +0000
977+++ tests/t/join.test 2010-08-22 01:04:46 +0000
978@@ -248,6 +248,7 @@
979 PRIMARY KEY (id)
980 ) ENGINE=MyISAM;
981 INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5');
982+--sorted_result
983 select t1.*, t2.* from t1, t2 where t2.id=t1.t2_id limit 2;
984 drop table t1,t2;
985
986@@ -400,9 +401,11 @@
987 from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
988
989 # Views with bigger natural join
990+--sorted_result
991 select * from t1 natural join (t2 natural join t3);
992
993 # Nested natural/using joins.
994+--sorted_result
995 select * from (t1 natural join t2) natural join (t3 natural join t4);
996 select * from (t1 natural join t2) natural left join (t3 natural join t4);
997 select * from (t3 natural join t4) natural right join (t1 natural join t2);
998@@ -443,11 +446,14 @@
999 select * from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
1000 select * from (t2 join t4 on b + 1 = y) join t1 on t1.c = t4.c;
1001 select * from t1 natural join (t2 join t4 on b + 1 = y);
1002+--sorted_result
1003 select * from (t1 cross join t2) join (t3 cross join t4) on (a < y and t2.b < t3.c);
1004
1005 # MySQL extension - 'join ... on' over nested comma operator
1006+--sorted_result
1007 select * from (t1, t2) join (t3, t4) on (a < y and t2.b < t3.c);
1008 select * from (t1 natural join t2) join (t3 natural join t4) on a = y;
1009+--sorted_result
1010 select * from ((t3 join (t1 join t2 on c > a) on t3.b < t2.a) join t4 on y > t1.c) join t5 on z = t1.b + 3;
1011
1012 # MySQL extension - refererence qualified coalesced columns
1013@@ -456,6 +462,7 @@
1014 select * from (t4 natural join t5) natural join t1 where t4.y > 7;
1015 select * from t1 natural left join (t4 natural join t5) where t4.y > 7;
1016 select * from (t4 natural join t5) natural right join t1 where t4.y > 7;
1017+--sorted_result
1018 select * from (t1 natural join t2) join (t3 natural join t4) on t1.b = t3.b;
1019
1020 # MySQL extension - select qualified columns of NJ columns
1021
1022=== modified file 'tests/t/join_nested.test'
1023--- tests/t/join_nested.test 2008-12-06 22:41:03 +0000
1024+++ tests/t/join_nested.test 2010-08-22 01:04:46 +0000
1025@@ -35,6 +35,7 @@
1026 SELECT t4.a,t4.b
1027 FROM t4;
1028
1029+--sorted_result
1030 SELECT t3.a,t3.b,t4.a,t4.b
1031 FROM t3,t4;
1032
1033@@ -75,6 +76,7 @@
1034 SELECT t5.a,t5.b
1035 FROM t5;
1036
1037+--sorted_result
1038 SELECT t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
1039 FROM t3,t4,t5;
1040
1041@@ -122,12 +124,14 @@
1042 SELECT t7.a,t7.b
1043 FROM t7;
1044
1045+--sorted_result
1046 SELECT t6.a,t6.b,t7.a,t7.b
1047 FROM t6,t7;
1048
1049 SELECT t8.a,t8.b
1050 FROM t8;
1051
1052+--sorted_result
1053 EXPLAIN EXTENDED
1054 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
1055 FROM (t6, t7)
1056@@ -155,6 +159,7 @@
1057 )
1058 ON t6.b >= 2 AND t5.b=t7.b;
1059
1060+--select_sorted
1061 SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
1062 FROM t5
1063 LEFT JOIN
1064@@ -189,6 +194,7 @@
1065 )
1066 ON t6.b >= 2 AND t5.b=t7.b;
1067
1068+--sorted_result
1069 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
1070 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
1071 FROM t2
1072@@ -313,6 +319,7 @@
1073 t0.b=t1.b AND
1074 (t2.a >= 4 OR t2.c IS NULL);
1075
1076+--sorted_result
1077 EXPLAIN EXTENDED
1078 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
1079 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
1080@@ -402,6 +409,7 @@
1081 t3
1082 ON t2.b=t3.b;
1083
1084+--sorted_result
1085 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
1086 FROM t1, t2
1087 LEFT JOIN
1088@@ -409,6 +417,7 @@
1089 ON t2.b=t3.b
1090 WHERE t1.a <= 2;
1091
1092+--sorted_result
1093 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
1094 FROM t1, t3
1095 RIGHT JOIN
1096@@ -425,6 +434,7 @@
1097 (t3, t4)
1098 ON t3.a=1 AND t2.b=t4.b;
1099
1100+--sorted_result
1101 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
1102 FROM t1, t2
1103 LEFT JOIN
1104@@ -432,6 +442,7 @@
1105 ON t3.a=1 AND t2.b=t4.b
1106 WHERE t1.a <= 2;
1107
1108+--sorted_result
1109 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
1110 FROM t1, (t3, t4)
1111 RIGHT JOIN
1112@@ -456,6 +467,7 @@
1113
1114 CREATE INDEX idx_b ON t2(b);
1115
1116+--sorted_result
1117 EXPLAIN EXTENDED
1118 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
1119 FROM (t3,t4)
1120@@ -469,6 +481,7 @@
1121 (t1,t2)
1122 ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
1123
1124+--sorted_result
1125 EXPLAIN EXTENDED
1126 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
1127 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
1128
1129=== modified file 'tests/t/join_outer.test'
1130--- tests/t/join_outer.test 2010-02-15 18:29:31 +0000
1131+++ tests/t/join_outer.test 2010-08-22 01:04:46 +0000
1132@@ -344,8 +344,11 @@
1133 insert into t2 values (7, 'green');
1134 select * from t1;
1135 select * from t2;
1136+--sorted_result
1137 select * from t2 natural join t1;
1138+--sorted_result
1139 select t2.count, t1.name from t2 natural join t1;
1140+--sorted_result
1141 select t2.count, t1.name from t2 inner join t1 using (color);
1142 drop table t1;
1143 drop table t2;
1144
1145=== modified file 'tests/t/key_diff.test'
1146--- tests/t/key_diff.test 2008-12-06 22:41:03 +0000
1147+++ tests/t/key_diff.test 2010-08-22 01:04:46 +0000
1148@@ -14,6 +14,7 @@
1149
1150 INSERT INTO t1 VALUES ('A','B'),('b','A'),('C','c'),('D','E'),('a','a');
1151
1152+--sorted_result
1153 select * from t1,t1 as t2;
1154 explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B;
1155 #select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B;
1156
1157=== modified file 'tests/t/range.test'
1158--- tests/t/range.test 2010-05-20 06:32:31 +0000
1159+++ tests/t/range.test 2010-08-22 01:04:46 +0000
1160@@ -556,6 +556,7 @@
1161 'd8c4177d09f8b11f5.52725521');
1162
1163 --replace_column 3 # 8 # 9 #
1164+--sorted_result
1165 EXPLAIN
1166 SELECT s.oxid FROM t1 v, t1 s
1167 WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
1168
1169=== modified file 'tests/t/select.test'
1170--- tests/t/select.test 2010-04-03 07:02:51 +0000
1171+++ tests/t/select.test 2010-08-22 01:04:46 +0000
1172@@ -1535,6 +1535,7 @@
1173 #
1174
1175 select * from t1,t1 t12;
1176+--sorted_result
1177 select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505;
1178
1179 #
1180@@ -1828,6 +1829,7 @@
1181 insert into t1 values ();
1182 insert into t1 values ();
1183 # ,
1184+--sorted_result
1185 select * from (t1 as t2 left join t1 as t3 using (a)), t1;
1186 select * from t1, (t1 as t2 left join t1 as t3 using (a));
1187 # stright_join
1188
1189=== modified file 'tests/t/subselect.test'
1190--- tests/t/subselect.test 2010-03-03 20:57:45 +0000
1191+++ tests/t/subselect.test 2010-08-22 01:04:46 +0000
1192@@ -3119,6 +3119,7 @@
1193 --error ER_PARSE_ERROR
1194 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;
1195
1196+--sorted_result
1197 SELECT * FROM t1 JOIN (t1 t1a) ON 1;
1198 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;
1199
1200
1201=== modified file 'tests/t/type_blob.test'
1202--- tests/t/type_blob.test 2009-06-16 00:46:49 +0000
1203+++ tests/t/type_blob.test 2010-08-22 01:04:46 +0000
1204@@ -54,6 +54,7 @@
1205 insert into t1 values (null,"bbb","BBB");
1206 insert into t1 values (null,"ccc","CCC");
1207 select last_insert_id();
1208+--sorted_result
1209 select * from t1,t1 as t2;
1210
1211 drop table t1;
1212
1213=== modified file 'tests/t/type_datetime.test'
1214--- tests/t/type_datetime.test 2010-02-03 21:54:38 +0000
1215+++ tests/t/type_datetime.test 2010-08-22 01:04:46 +0000
1216@@ -231,6 +231,7 @@
1217 create table t3(f3 varchar(20));
1218 insert into t3 select * from t2;
1219 select * from t2,t3 where f2 in (f3,'03-04-05');
1220+--sorted_result
1221 select f1,f2,f3 from t1,t2,t3 where (f1,'1') in ((f2,'1'),(f3,'1'));
1222 select f1 from t1 where ('1',f1) in (('1','01-01-01'),('1','2001-1-1 0:0:0'),('1','02-02-02'));
1223 drop table t1,t2,t3;