|
1
by brian
clean slate |
1 |
drop table if exists t1,t2,t3; |
2 |
CREATE TABLE t1 ( |
|
|
500
by Brian Aker
Re-enabled more tests. |
3 |
id int DEFAULT '0' NOT NULL, |
4 |
idservice int, |
|
5 |
clee varchar(20) NOT NULL, |
|
6 |
flag varchar(1), |
|
|
1
by brian
clean slate |
7 |
KEY id (id), |
8 |
PRIMARY KEY (clee) |
|
9 |
);
|
|
10 |
INSERT INTO t1 VALUES (2,4,'6067169d','Y'); |
|
11 |
INSERT INTO t1 VALUES (2,5,'606716d1','Y'); |
|
12 |
INSERT INTO t1 VALUES (2,1,'606717c1','Y'); |
|
13 |
INSERT INTO t1 VALUES (3,1,'6067178d','Y'); |
|
14 |
INSERT INTO t1 VALUES (2,6,'60671515','Y'); |
|
15 |
INSERT INTO t1 VALUES (2,7,'60671569','Y'); |
|
16 |
INSERT INTO t1 VALUES (2,3,'dd','Y'); |
|
17 |
CREATE TABLE t2 ( |
|
|
500
by Brian Aker
Re-enabled more tests. |
18 |
id int NOT NULL auto_increment, |
|
1
by brian
clean slate |
19 |
description varchar(40) NOT NULL, |
20 |
idform varchar(40), |
|
|
500
by Brian Aker
Re-enabled more tests. |
21 |
ordre int DEFAULT '0' NOT NULL, |
|
1
by brian
clean slate |
22 |
image varchar(60), |
23 |
PRIMARY KEY (id), |
|
24 |
KEY id (id,ordre) |
|
25 |
);
|
|
26 |
INSERT INTO t2 VALUES (1,'Emettre un appel d''offres','en_construction.html',10,'emettre.gif'); |
|
27 |
INSERT INTO t2 VALUES (2,'Emettre des soumissions','en_construction.html',20,'emettre.gif'); |
|
28 |
INSERT INTO t2 VALUES (7,'Liste des t2','t2_liste_form.phtml',51060,'link.gif'); |
|
29 |
INSERT INTO t2 VALUES (8,'Consulter les soumissions','consulter_soumissions.phtml',200,'link.gif'); |
|
30 |
INSERT INTO t2 VALUES (9,'Ajouter un type de materiel','typeMateriel_ajoute_form.phtml',51000,'link.gif'); |
|
31 |
INSERT INTO t2 VALUES (10,'Lister/modifier un type de materiel','typeMateriel_liste_form.phtml',51010,'link.gif'); |
|
32 |
INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif'); |
|
33 |
INSERT INTO t2 VALUES (4,'Modifier des clients','en_construction.html',40010,'link.gif'); |
|
34 |
INSERT INTO t2 VALUES (5,'Effacer des clients','en_construction.html',40020,'link.gif'); |
|
35 |
INSERT INTO t2 VALUES (6,'Ajouter un service','t2_ajoute_form.phtml',51050,'link.gif'); |
|
36 |
select t1.id,t1.idservice,t2.ordre,t2.description from t1, t2 where t1.id = 2 and t1.idservice = t2.id order by t2.ordre; |
|
37 |
id idservice ordre description |
|
38 |
2 1 10 Emettre un appel d'offres |
|
39 |
2 3 40000 Créer une fiche de client |
|
40 |
2 4 40010 Modifier des clients |
|
41 |
2 5 40020 Effacer des clients |
|
42 |
2 6 51050 Ajouter un service |
|
43 |
2 7 51060 Liste des t2 |
|
44 |
drop table t1,t2; |
|
45 |
create table t1 (first char(10),last char(10)); |
|
46 |
insert into t1 values ("Michael","Widenius"); |
|
47 |
insert into t1 values ("Allan","Larsson"); |
|
48 |
insert into t1 values ("David","Axmark"); |
|
49 |
select concat(first," ",last) as name from t1 order by name; |
|
50 |
name
|
|
51 |
Allan Larsson |
|
52 |
David Axmark |
|
53 |
Michael Widenius |
|
54 |
select concat(last," ",first) as name from t1 order by name; |
|
55 |
name
|
|
56 |
Axmark David |
|
57 |
Larsson Allan |
|
58 |
Widenius Michael |
|
59 |
drop table t1; |
|
60 |
create table t1 (i int); |
|
61 |
insert into t1 values(1),(2),(1),(2),(1),(2),(3); |
|
62 |
select distinct i from t1; |
|
63 |
i
|
|
64 |
1
|
|
65 |
2
|
|
66 |
3
|
|
67 |
select distinct i from t1 order by rand(5); |
|
68 |
i
|
|
69 |
1
|
|
70 |
3
|
|
71 |
2
|
|
72 |
select distinct i from t1 order by i desc; |
|
73 |
i
|
|
74 |
3
|
|
75 |
2
|
|
76 |
1
|
|
77 |
select distinct i from t1 order by 1-i; |
|
78 |
i
|
|
79 |
3
|
|
80 |
2
|
|
81 |
1
|
|
82 |
select distinct i from t1 order by mod(i,2),i; |
|
83 |
i
|
|
84 |
2
|
|
85 |
1
|
|
86 |
3
|
|
87 |
drop table t1; |
|
88 |
create table t1 ( pk int primary key, name varchar(255) not null, number varchar(255) not null); |
|
89 |
insert into t1 values (1, 'Gamma', '123'), (2, 'Gamma Ext', '123a'), (3, 'Alpha', '001'), (4, 'Beta', '200c'); |
|
90 |
select distinct t1.name as 'Building Name',t1.number as 'Building Number' from t1 order by t1.name asc; |
|
91 |
Building Name Building Number |
|
92 |
Alpha 001 |
|
93 |
Beta 200c |
|
94 |
Gamma 123 |
|
95 |
Gamma Ext 123a |
|
96 |
drop table t1; |
|
97 |
create table t1 (id int not null,col1 int not null,col2 int not null,index(col1)); |
|
98 |
insert into t1 values(1,2,2),(2,2,1),(3,1,2),(4,1,1),(5,1,4),(6,2,3),(7,3,1),(8,2,4); |
|
99 |
select * from t1 order by col1,col2; |
|
100 |
id col1 col2 |
|
101 |
4 1 1 |
|
102 |
3 1 2 |
|
103 |
5 1 4 |
|
104 |
2 2 1 |
|
105 |
1 2 2 |
|
106 |
6 2 3 |
|
107 |
8 2 4 |
|
108 |
7 3 1 |
|
109 |
select col1 from t1 order by id; |
|
110 |
col1
|
|
111 |
2
|
|
112 |
2
|
|
113 |
1
|
|
114 |
1
|
|
115 |
1
|
|
116 |
2
|
|
117 |
3
|
|
118 |
2
|
|
119 |
select col1 as id from t1 order by id; |
|
120 |
id
|
|
121 |
1
|
|
122 |
1
|
|
123 |
1
|
|
124 |
2
|
|
125 |
2
|
|
126 |
2
|
|
127 |
2
|
|
128 |
3
|
|
129 |
select concat(col1) as id from t1 order by id; |
|
130 |
id
|
|
131 |
1
|
|
132 |
1
|
|
133 |
1
|
|
134 |
2
|
|
135 |
2
|
|
136 |
2
|
|
137 |
2
|
|
138 |
3
|
|
139 |
drop table t1; |
|
140 |
CREATE TABLE t1 (id int auto_increment primary key,aika varchar(40),aikakentta timestamp); |
|
141 |
insert into t1 (aika) values ('Keskiviikko'); |
|
142 |
insert into t1 (aika) values ('Tiistai'); |
|
143 |
insert into t1 (aika) values ('Maanantai'); |
|
144 |
insert into t1 (aika) values ('Sunnuntai'); |
|
145 |
SELECT FIELD(SUBSTRING(t1.aika,1,2),'Ma','Ti','Ke','To','Pe','La','Su') AS test FROM t1 ORDER by test; |
|
146 |
test
|
|
147 |
1
|
|
148 |
2
|
|
149 |
3
|
|
150 |
7
|
|
151 |
drop table t1; |
|
152 |
CREATE TABLE t1 |
|
153 |
(
|
|
|
500
by Brian Aker
Re-enabled more tests. |
154 |
a int NOT NULL, |
155 |
b int NOT NULL, |
|
156 |
c int NOT NULL, |
|
|
1
by brian
clean slate |
157 |
UNIQUE(a), |
158 |
INDEX(b), |
|
159 |
INDEX(c) |
|
160 |
);
|
|
161 |
CREATE TABLE t2 |
|
162 |
(
|
|
|
500
by Brian Aker
Re-enabled more tests. |
163 |
c int NOT NULL, |
164 |
i int NOT NULL, |
|
|
1
by brian
clean slate |
165 |
INDEX(c) |
166 |
);
|
|
167 |
CREATE TABLE t3 |
|
168 |
(
|
|
|
500
by Brian Aker
Re-enabled more tests. |
169 |
c int NOT NULL, |
|
1
by brian
clean slate |
170 |
v varchar(64), |
171 |
INDEX(c) |
|
172 |
);
|
|
173 |
INSERT INTO t1 VALUES (1,1,1); |
|
174 |
INSERT INTO t1 VALUES (2,1,2); |
|
175 |
INSERT INTO t1 VALUES (3,2,1); |
|
176 |
INSERT INTO t1 VALUES (4,2,2); |
|
177 |
INSERT INTO t2 VALUES (1,50); |
|
178 |
INSERT INTO t2 VALUES (2,25); |
|
179 |
INSERT INTO t3 VALUES (1,'123 Park Place'); |
|
180 |
INSERT INTO t3 VALUES (2,'453 Boardwalk'); |
|
181 |
SELECT a,b,if(b = 1,i,if(b = 2,v,'')) |
|
182 |
FROM t1 |
|
183 |
LEFT JOIN t2 USING(c) |
|
184 |
LEFT JOIN t3 ON t3.c = t1.c; |
|
185 |
a b if(b = 1,i,if(b = 2,v,'')) |
|
186 |
1 1 50 |
|
187 |
2 1 25 |
|
188 |
3 2 123 Park Place |
|
189 |
4 2 453 Boardwalk |
|
190 |
SELECT a,b,if(b = 1,i,if(b = 2,v,'')) |
|
191 |
FROM t1 |
|
192 |
LEFT JOIN t2 ON t1.c = t2.c |
|
193 |
LEFT JOIN t3 ON t3.c = t1.c; |
|
194 |
a b if(b = 1,i,if(b = 2,v,'')) |
|
195 |
1 1 50 |
|
196 |
2 1 25 |
|
197 |
3 2 123 Park Place |
|
198 |
4 2 453 Boardwalk |
|
199 |
SELECT a,b,if(b = 1,i,if(b = 2,v,'')) |
|
200 |
FROM t1 |
|
201 |
LEFT JOIN t2 USING(c) |
|
202 |
LEFT JOIN t3 ON t3.c = t1.c |
|
203 |
ORDER BY a; |
|
204 |
a b if(b = 1,i,if(b = 2,v,'')) |
|
205 |
1 1 50 |
|
206 |
2 1 25 |
|
207 |
3 2 123 Park Place |
|
208 |
4 2 453 Boardwalk |
|
209 |
SELECT a,b,if(b = 1,i,if(b = 2,v,'')) |
|
210 |
FROM t1 |
|
211 |
LEFT JOIN t2 ON t1.c = t2.c |
|
212 |
LEFT JOIN t3 ON t3.c = t1.c |
|
213 |
ORDER BY a; |
|
214 |
a b if(b = 1,i,if(b = 2,v,'')) |
|
215 |
1 1 50 |
|
216 |
2 1 25 |
|
217 |
3 2 123 Park Place |
|
218 |
4 2 453 Boardwalk |
|
219 |
drop table t1,t2,t3; |
|
220 |
create table t1 (ID int not null primary key, TransactionID int not null); |
|
221 |
insert into t1 (ID, TransactionID) values (1, 87), (2, 89), (3, 92), (4, 94), (5, 486), (6, 490), (7, 753), (9, 828), (10, 832), (11, 834), (12, 840); |
|
222 |
create table t2 (ID int not null primary key, GroupID int not null); |
|
223 |
insert into t2 (ID, GroupID) values (87, 87), (89, 89), (92, 92), (94, 94), (486, 486), (490, 490),(753, 753), (828, 828), (832, 832), (834, 834), (840, 840); |
|
224 |
create table t3 (ID int not null primary key, DateOfAction date not null); |
|
225 |
insert into t3 (ID, DateOfAction) values (87, '1999-07-19'), (89, '1999-07-19'), (92, '1999-07-19'), (94, '1999-07-19'), (486, '1999-07-18'), (490, '2000-03-27'), (753, '2000-03-28'), (828, '1999-07-27'), (832, '1999-07-27'),(834, '1999-07-27'), (840, '1999-07-27'); |
|
226 |
select t3.DateOfAction, t1.TransactionID from t1 join t2 join t3 where t2.ID = t1.TransactionID and t3.ID = t2.GroupID order by t3.DateOfAction, t1.TransactionID; |
|
227 |
DateOfAction TransactionID |
|
228 |
1999-07-18 486 |
|
229 |
1999-07-19 87 |
|
230 |
1999-07-19 89 |
|
231 |
1999-07-19 92 |
|
232 |
1999-07-19 94 |
|
233 |
1999-07-27 828 |
|
234 |
1999-07-27 832 |
|
235 |
1999-07-27 834 |
|
236 |
1999-07-27 840 |
|
237 |
2000-03-27 490 |
|
238 |
2000-03-28 753 |
|
239 |
select t3.DateOfAction, t1.TransactionID from t1 join t2 join t3 where t2.ID = t1.TransactionID and t3.ID = t2.GroupID order by t1.TransactionID,t3.DateOfAction; |
|
240 |
DateOfAction TransactionID |
|
241 |
1999-07-19 87 |
|
242 |
1999-07-19 89 |
|
243 |
1999-07-19 92 |
|
244 |
1999-07-19 94 |
|
245 |
1999-07-18 486 |
|
246 |
2000-03-27 490 |
|
247 |
2000-03-28 753 |
|
248 |
1999-07-27 828 |
|
249 |
1999-07-27 832 |
|
250 |
1999-07-27 834 |
|
251 |
1999-07-27 840 |
|
252 |
drop table t1,t2,t3; |
|
|
1063.9.19
by Stewart Smith
order_by.test for MyISAM temp only: use temp myisam tables |
253 |
CREATE TEMPORARY TABLE t1 ( |
|
500
by Brian Aker
Re-enabled more tests. |
254 |
member_id int NOT NULL auto_increment, |
|
1
by brian
clean slate |
255 |
inschrijf_datum varchar(20) NOT NULL default '', |
256 |
lastchange_datum varchar(20) NOT NULL default '', |
|
257 |
nickname varchar(20) NOT NULL default '', |
|
258 |
password varchar(8) NOT NULL default '', |
|
259 |
voornaam varchar(30) NOT NULL default '', |
|
260 |
tussenvoegsels varchar(10) NOT NULL default '', |
|
261 |
achternaam varchar(50) NOT NULL default '', |
|
262 |
straat varchar(100) NOT NULL default '', |
|
263 |
postcode varchar(10) NOT NULL default '', |
|
264 |
wijk varchar(40) NOT NULL default '', |
|
265 |
plaats varchar(50) NOT NULL default '', |
|
266 |
telefoon varchar(10) NOT NULL default '', |
|
|
873.1.1
by Jay Pipes
Fixes the Field_date class to not allow any invalid input at |
267 |
geboortedatum date, |
|
1
by brian
clean slate |
268 |
geslacht varchar(5) NOT NULL default '', |
269 |
email varchar(80) NOT NULL default '', |
|
270 |
uin varchar(15) NOT NULL default '', |
|
271 |
homepage varchar(100) NOT NULL default '', |
|
272 |
internet varchar(15) NOT NULL default '', |
|
273 |
scherk varchar(30) NOT NULL default '', |
|
274 |
favo_boek varchar(50) NOT NULL default '', |
|
275 |
favo_tijdschrift varchar(50) NOT NULL default '', |
|
276 |
favo_tv varchar(50) NOT NULL default '', |
|
277 |
favo_eten varchar(50) NOT NULL default '', |
|
278 |
favo_muziek varchar(30) NOT NULL default '', |
|
279 |
info text NOT NULL default '', |
|
280 |
ipnr varchar(30) NOT NULL default '', |
|
281 |
PRIMARY KEY (member_id) |
|
282 |
) ENGINE=MyISAM PACK_KEYS=1; |
|
283 |
insert into t1 (member_id) values (1),(2),(3); |
|
284 |
select member_id, nickname, voornaam FROM t1 |
|
285 |
ORDER by lastchange_datum DESC LIMIT 2; |
|
286 |
member_id nickname voornaam |
|
287 |
1
|
|
288 |
2
|
|
289 |
drop table t1; |
|
290 |
create table t1 (a int not null, b int, c varchar(10), key (a, b, c)); |
|
291 |
insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b'); |
|
292 |
explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; |
|
293 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
500
by Brian Aker
Re-enabled more tests. |
294 |
1 SIMPLE t1 index a a 52 NULL 11 Using where; Using index |
|
1
by brian
clean slate |
295 |
select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; |
296 |
a b c |
|
297 |
1 NULL b |
|
298 |
explain select * from t1 where a >= 1 and a < 3 order by a desc; |
|
299 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
500
by Brian Aker
Re-enabled more tests. |
300 |
1 SIMPLE t1 range a a 4 NULL 5 Using where; Using index |
|
1
by brian
clean slate |
301 |
select * from t1 where a >= 1 and a < 3 order by a desc; |
302 |
a b c |
|
303 |
2 3 c |
|
304 |
2 2 b |
|
305 |
2 2 a |
|
306 |
2 1 b |
|
307 |
2 1 a |
|
308 |
1 3 b |
|
309 |
1 1 b |
|
310 |
1 1 b |
|
311 |
1 1 NULL |
|
312 |
1 NULL b |
|
313 |
1 NULL NULL |
|
314 |
explain select * from t1 where a = 1 order by a desc, b desc; |
|
315 |
id select_type table type possible_keys key key_len ref rows Extra |
|
316 |
1 SIMPLE t1 ref a a 4 const 5 Using where; Using index |
|
317 |
select * from t1 where a = 1 order by a desc, b desc; |
|
318 |
a b c |
|
319 |
1 3 b |
|
320 |
1 1 b |
|
321 |
1 1 b |
|
322 |
1 1 NULL |
|
323 |
1 NULL b |
|
324 |
1 NULL NULL |
|
325 |
explain select * from t1 where a = 1 and b is null order by a desc, b desc; |
|
326 |
id select_type table type possible_keys key key_len ref rows Extra |
|
327 |
1 SIMPLE t1 ref a a 9 const,const 2 Using where; Using index; Using filesort |
|
328 |
select * from t1 where a = 1 and b is null order by a desc, b desc; |
|
329 |
a b c |
|
330 |
1 NULL NULL |
|
331 |
1 NULL b |
|
332 |
explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc; |
|
333 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
500
by Brian Aker
Re-enabled more tests. |
334 |
1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index |
|
1
by brian
clean slate |
335 |
explain select * from t1 where a = 2 and b >0 order by a desc,b desc; |
336 |
id select_type table type possible_keys key key_len ref rows Extra |
|
337 |
1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index |
|
338 |
explain select * from t1 where a = 2 and b is null order by a desc,b desc; |
|
339 |
id select_type table type possible_keys key key_len ref rows Extra |
|
340 |
1 SIMPLE t1 ref a a 9 const,const 1 Using where; Using index; Using filesort |
|
341 |
explain select * from t1 where a = 2 and (b is null or b > 0) order by a |
|
342 |
desc,b desc; |
|
343 |
id select_type table type possible_keys key key_len ref rows Extra |
|
344 |
1 SIMPLE t1 range a a 9 NULL 6 Using where; Using index |
|
345 |
explain select * from t1 where a = 2 and b > 0 order by a desc,b desc; |
|
346 |
id select_type table type possible_keys key key_len ref rows Extra |
|
347 |
1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index |
|
348 |
explain select * from t1 where a = 2 and b < 2 order by a desc,b desc; |
|
349 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
500
by Brian Aker
Re-enabled more tests. |
350 |
1 SIMPLE t1 range a a 9 NULL 1 Using where; Using index |
|
1
by brian
clean slate |
351 |
explain select * from t1 where a = 1 order by b desc; |
352 |
id select_type table type possible_keys key key_len ref rows Extra |
|
353 |
1 SIMPLE t1 ref a a 4 const 5 Using where; Using index |
|
354 |
select * from t1 where a = 1 order by b desc; |
|
355 |
a b c |
|
356 |
1 3 b |
|
357 |
1 1 b |
|
358 |
1 1 b |
|
359 |
1 1 NULL |
|
360 |
1 NULL b |
|
361 |
1 NULL NULL |
|
|
500
by Brian Aker
Re-enabled more tests. |
362 |
delete from t1 WHERE b IS NULL OR c IS NULL; |
363 |
alter table t1 modify b bigint not null, modify c varchar(100) not null; |
|
|
1
by brian
clean slate |
364 |
explain select * from t1 order by a, b, c; |
365 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
500
by Brian Aker
Re-enabled more tests. |
366 |
1 SIMPLE t1 index NULL a 414 NULL 8 Using index |
|
1
by brian
clean slate |
367 |
select * from t1 order by a, b, c; |
368 |
a b c |
|
369 |
1 1 b |
|
370 |
1 1 b |
|
371 |
1 3 b |
|
372 |
2 1 a |
|
373 |
2 1 b |
|
374 |
2 2 a |
|
375 |
2 2 b |
|
376 |
2 3 c |
|
377 |
explain select * from t1 order by a desc, b desc, c desc; |
|
378 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
500
by Brian Aker
Re-enabled more tests. |
379 |
1 SIMPLE t1 index NULL a 414 NULL 8 Using index |
|
1
by brian
clean slate |
380 |
select * from t1 order by a desc, b desc, c desc; |
381 |
a b c |
|
382 |
2 3 c |
|
383 |
2 2 b |
|
384 |
2 2 a |
|
385 |
2 1 b |
|
386 |
2 1 a |
|
387 |
1 3 b |
|
388 |
1 1 b |
|
389 |
1 1 b |
|
390 |
explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; |
|
391 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
500
by Brian Aker
Re-enabled more tests. |
392 |
1 SIMPLE t1 range a a 414 NULL 3 Using where; Using index |
|
1
by brian
clean slate |
393 |
select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; |
394 |
a b c |
|
395 |
1 1 b |
|
396 |
1 1 b |
|
397 |
explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc; |
|
398 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
500
by Brian Aker
Re-enabled more tests. |
399 |
1 SIMPLE t1 range a a 4 NULL 3 Using where; Using index |
|
1
by brian
clean slate |
400 |
select * from t1 where a < 2 and b <= 1 order by a desc, b desc; |
401 |
a b c |
|
402 |
1 1 b |
|
403 |
1 1 b |
|
404 |
select count(*) from t1 where a < 5 and b > 0; |
|
405 |
count(*) |
|
|
500
by Brian Aker
Re-enabled more tests. |
406 |
8
|
|
1
by brian
clean slate |
407 |
select * from t1 where a < 5 and b > 0 order by a desc,b desc; |
408 |
a b c |
|
409 |
2 3 c |
|
410 |
2 2 b |
|
411 |
2 2 a |
|
412 |
2 1 b |
|
413 |
2 1 a |
|
414 |
1 3 b |
|
415 |
1 1 b |
|
416 |
1 1 b |
|
417 |
explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc; |
|
418 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
500
by Brian Aker
Re-enabled more tests. |
419 |
1 SIMPLE t1 range a a 12 NULL 4 Using where; Using index |
|
1
by brian
clean slate |
420 |
select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc; |
421 |
a b c |
|
422 |
2 1 b |
|
423 |
2 1 a |
|
424 |
1 1 b |
|
425 |
1 1 b |
|
426 |
explain select * from t1 where a between 0 and 1 order by a desc, b desc; |
|
427 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
500
by Brian Aker
Re-enabled more tests. |
428 |
1 SIMPLE t1 range a a 4 NULL 3 Using where; Using index |
|
1
by brian
clean slate |
429 |
select * from t1 where a between 0 and 1 order by a desc, b desc; |
430 |
a b c |
|
431 |
1 3 b |
|
432 |
1 1 b |
|
433 |
1 1 b |
|
434 |
drop table t1; |
|
|
1063.9.19
by Stewart Smith
order_by.test for MyISAM temp only: use temp myisam tables |
435 |
CREATE TEMPORARY TABLE t1 ( |
|
500
by Brian Aker
Re-enabled more tests. |
436 |
gid int NOT NULL auto_increment, |
437 |
cid int NOT NULL default '0', |
|
|
1
by brian
clean slate |
438 |
PRIMARY KEY (gid), |
439 |
KEY component_id (cid) |
|
440 |
) ENGINE=MyISAM; |
|
441 |
INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108); |
|
|
500
by Brian Aker
Re-enabled more tests. |
442 |
ALTER TABLE t1 add skr int not null; |
|
1063.9.19
by Stewart Smith
order_by.test for MyISAM temp only: use temp myisam tables |
443 |
CREATE TEMPORARY TABLE t2 ( |
|
500
by Brian Aker
Re-enabled more tests. |
444 |
gid int NOT NULL default '0', |
445 |
uid int NOT NULL default '1', |
|
446 |
sid int NOT NULL default '1', |
|
|
1
by brian
clean slate |
447 |
PRIMARY KEY (gid), |
448 |
KEY uid (uid), |
|
449 |
KEY status_id (sid) |
|
450 |
) ENGINE=MyISAM; |
|
451 |
INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5); |
|
|
1063.9.19
by Stewart Smith
order_by.test for MyISAM temp only: use temp myisam tables |
452 |
CREATE TEMPORARY TABLE t3 ( |
|
500
by Brian Aker
Re-enabled more tests. |
453 |
uid int NOT NULL auto_increment, |
|
1
by brian
clean slate |
454 |
PRIMARY KEY (uid) |
455 |
) ENGINE=MyISAM; |
|
456 |
INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250); |
|
|
500
by Brian Aker
Re-enabled more tests. |
457 |
ALTER TABLE t3 add skr int not null; |
|
1
by brian
clean slate |
458 |
select t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid; |
459 |
gid sid uid |
|
460 |
104620 5 15 |
|
461 |
103867 5 27 |
|
462 |
103962 5 27 |
|
463 |
104619 5 75 |
|
464 |
104505 5 117 |
|
465 |
103853 5 250 |
|
466 |
select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid; |
|
467 |
gid sid uid |
|
468 |
104620 5 15 |
|
469 |
103867 5 27 |
|
470 |
103962 5 27 |
|
471 |
104619 5 75 |
|
472 |
104505 5 117 |
|
473 |
103853 5 250 |
|
|
501
by Brian Aker
Removed EXPL from tests. Why? The explain output is based on MyISAM... |
474 |
select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid; |
475 |
gid sid uid |
|
476 |
103853 5 250 |
|
477 |
103867 5 27 |
|
478 |
103962 5 27 |
|
479 |
104505 5 117 |
|
480 |
104619 5 75 |
|
481 |
104620 5 15 |
|
482 |
SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr; |
|
483 |
gid uid |
|
484 |
SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid; |
|
485 |
gid sid uid |
|
486 |
104620 5 15 |
|
487 |
103867 5 27 |
|
488 |
103962 5 27 |
|
489 |
104619 5 75 |
|
490 |
104505 5 117 |
|
491 |
103853 5 250 |
|
492 |
SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid; |
|
493 |
gid uid |
|
494 |
SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr; |
|
495 |
gid uid |
|
|
1
by brian
clean slate |
496 |
drop table t1,t2,t3; |
497 |
CREATE TABLE t1 ( |
|
498 |
`titre` char(80) NOT NULL default '', |
|
|
500
by Brian Aker
Re-enabled more tests. |
499 |
`numeropost` int NOT NULL auto_increment, |
|
873.1.2
by Jay Pipes
Fixed Field_datetime to never accept any bad datetimes as a string. This broke |
500 |
`date` datetime, |
|
1
by brian
clean slate |
501 |
`auteur` char(35) NOT NULL default '', |
|
500
by Brian Aker
Re-enabled more tests. |
502 |
`icone` int NOT NULL default '0', |
|
1
by brian
clean slate |
503 |
`lastauteur` char(35) NOT NULL default '', |
|
500
by Brian Aker
Re-enabled more tests. |
504 |
`nbrep` int NOT NULL default '0', |
|
1
by brian
clean slate |
505 |
`dest` char(35) NOT NULL default '', |
|
500
by Brian Aker
Re-enabled more tests. |
506 |
`lu` int NOT NULL default '0', |
507 |
`vue` int NOT NULL default '0', |
|
508 |
`ludest` int NOT NULL default '0', |
|
509 |
`ouvert` int NOT NULL default '1', |
|
|
1
by brian
clean slate |
510 |
PRIMARY KEY (`numeropost`), |
511 |
KEY `date` (`date`), |
|
512 |
KEY `dest` (`dest`,`ludest`), |
|
513 |
KEY `auteur` (`auteur`,`lu`), |
|
514 |
KEY `auteur_2` (`auteur`,`date`), |
|
515 |
KEY `dest_2` (`dest`,`date`) |
|
516 |
) CHECKSUM=1; |
|
517 |
CREATE TABLE t2 ( |
|
|
500
by Brian Aker
Re-enabled more tests. |
518 |
`numeropost` int NOT NULL default '0', |
|
1
by brian
clean slate |
519 |
`pseudo` char(35) NOT NULL default '', |
520 |
PRIMARY KEY (`numeropost`,`pseudo`), |
|
521 |
KEY `pseudo` (`pseudo`) |
|
522 |
);
|
|
523 |
INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug'); |
|
524 |
INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug'); |
|
525 |
SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30; |
|
526 |
titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest |
|
|
873.1.2
by Jay Pipes
Fixed Field_datetime to never accept any bad datetimes as a string. This broke |
527 |
test 1 joce 0 0 0 NULL 0 1 bug |
|
1
by brian
clean slate |
528 |
SELECT titre,numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30; |
529 |
titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest |
|
|
873.1.2
by Jay Pipes
Fixed Field_datetime to never accept any bad datetimes as a string. This broke |
530 |
test 1 joce 0 0 0 NULL 0 1 bug |
|
1
by brian
clean slate |
531 |
SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30; |
532 |
titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest |
|
|
873.1.2
by Jay Pipes
Fixed Field_datetime to never accept any bad datetimes as a string. This broke |
533 |
test 1 joce 0 0 0 NULL 0 1 bug |
|
1
by brian
clean slate |
534 |
SELECT titre,numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30; |
535 |
titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest |
|
|
873.1.2
by Jay Pipes
Fixed Field_datetime to never accept any bad datetimes as a string. This broke |
536 |
test 1 joce 0 0 0 NULL 0 1 bug |
|
1
by brian
clean slate |
537 |
drop table t1,t2; |
538 |
CREATE TABLE t1 (a int, b int); |
|
539 |
INSERT INTO t1 VALUES (1, 2); |
|
540 |
INSERT INTO t1 VALUES (3, 4); |
|
541 |
INSERT INTO t1 VALUES (5, NULL); |
|
542 |
SELECT * FROM t1 ORDER BY b; |
|
543 |
a b |
|
544 |
5 NULL |
|
545 |
1 2 |
|
546 |
3 4 |
|
547 |
SELECT * FROM t1 ORDER BY b DESC; |
|
548 |
a b |
|
549 |
3 4 |
|
550 |
1 2 |
|
551 |
5 NULL |
|
552 |
SELECT * FROM t1 ORDER BY (a + b); |
|
553 |
a b |
|
554 |
5 NULL |
|
555 |
1 2 |
|
556 |
3 4 |
|
557 |
SELECT * FROM t1 ORDER BY (a + b) DESC; |
|
558 |
a b |
|
559 |
3 4 |
|
560 |
1 2 |
|
561 |
5 NULL |
|
562 |
DROP TABLE t1; |
|
563 |
create table t1(id int not null auto_increment primary key, t char(12)); |
|
|
500
by Brian Aker
Re-enabled more tests. |
564 |
select id,t from t1 force index (primary) order by id; |
565 |
id t |
|
566 |
1 1000 |
|
567 |
2 999 |
|
568 |
3 998 |
|
569 |
4 997 |
|
570 |
5 996 |
|
571 |
6 995 |
|
572 |
7 994 |
|
573 |
8 993 |
|
574 |
9 992 |
|
575 |
10 991 |
|
576 |
11 990 |
|
577 |
12 989 |
|
578 |
13 988 |
|
579 |
14 987 |
|
580 |
15 986 |
|
581 |
16 985 |
|
582 |
17 984 |
|
583 |
18 983 |
|
584 |
19 982 |
|
585 |
20 981 |
|
586 |
21 980 |
|
587 |
22 979 |
|
588 |
23 978 |
|
589 |
24 977 |
|
590 |
25 976 |
|
591 |
26 975 |
|
592 |
27 974 |
|
593 |
28 973 |
|
594 |
29 972 |
|
595 |
30 971 |
|
596 |
31 970 |
|
597 |
32 969 |
|
598 |
33 968 |
|
599 |
34 967 |
|
600 |
35 966 |
|
601 |
36 965 |
|
602 |
37 964 |
|
603 |
38 963 |
|
604 |
39 962 |
|
605 |
40 961 |
|
606 |
41 960 |
|
607 |
42 959 |
|
608 |
43 958 |
|
609 |
44 957 |
|
610 |
45 956 |
|
611 |
46 955 |
|
612 |
47 954 |
|
613 |
48 953 |
|
614 |
49 952 |
|
615 |
50 951 |
|
616 |
51 950 |
|
617 |
52 949 |
|
618 |
53 948 |
|
619 |
54 947 |
|
620 |
55 946 |
|
621 |
56 945 |
|
622 |
57 944 |
|
623 |
58 943 |
|
624 |
59 942 |
|
625 |
60 941 |
|
626 |
61 940 |
|
627 |
62 939 |
|
628 |
63 938 |
|
629 |
64 937 |
|
630 |
65 936 |
|
631 |
66 935 |
|
632 |
67 934 |
|
633 |
68 933 |
|
634 |
69 932 |
|
635 |
70 931 |
|
636 |
71 930 |
|
637 |
72 929 |
|
638 |
73 928 |
|
639 |
74 927 |
|
640 |
75 926 |
|
641 |
76 925 |
|
642 |
77 924 |
|
643 |
78 923 |
|
644 |
79 922 |
|
645 |
80 921 |
|
646 |
81 920 |
|
647 |
82 919 |
|
648 |
83 918 |
|
649 |
84 917 |
|
650 |
85 916 |
|
651 |
86 915 |
|
652 |
87 914 |
|
653 |
88 913 |
|
654 |
89 912 |
|
655 |
90 911 |
|
656 |
91 910 |
|
657 |
92 909 |
|
658 |
93 908 |
|
659 |
94 907 |
|
660 |
95 906 |
|
661 |
96 905 |
|
662 |
97 904 |
|
663 |
98 903 |
|
664 |
99 902 |
|
665 |
100 901 |
|
666 |
101 900 |
|
667 |
102 899 |
|
668 |
103 898 |
|
669 |
104 897 |
|
670 |
105 896 |
|
671 |
106 895 |
|
672 |
107 894 |
|
673 |
108 893 |
|
674 |
109 892 |
|
675 |
110 891 |
|
676 |
111 890 |
|
677 |
112 889 |
|
678 |
113 888 |
|
679 |
114 887 |
|
680 |
115 886 |
|
681 |
116 885 |
|
682 |
117 884 |
|
683 |
118 883 |
|
684 |
119 882 |
|
685 |
120 881 |
|
686 |
121 880 |
|
687 |
122 879 |
|
688 |
123 878 |
|
689 |
124 877 |
|
690 |
125 876 |
|
691 |
126 875 |
|
692 |
127 874 |
|
693 |
128 873 |
|
694 |
129 872 |
|
695 |
130 871 |
|
696 |
131 870 |
|
697 |
132 869 |
|
698 |
133 868 |
|
699 |
134 867 |
|
700 |
135 866 |
|
701 |
136 865 |
|
702 |
137 864 |
|
703 |
138 863 |
|
704 |
139 862 |
|
705 |
140 861 |
|
706 |
141 860 |
|
707 |
142 859 |
|
708 |
143 858 |
|
709 |
144 857 |
|
710 |
145 856 |
|
711 |
146 855 |
|
712 |
147 854 |
|
713 |
148 853 |
|
714 |
149 852 |
|
715 |
150 851 |
|
716 |
151 850 |
|
717 |
152 849 |
|
718 |
153 848 |
|
719 |
154 847 |
|
720 |
155 846 |
|
721 |
156 845 |
|
722 |
157 844 |
|
723 |
158 843 |
|
724 |
159 842 |
|
725 |
160 841 |
|
726 |
161 840 |
|
727 |
162 839 |
|
728 |
163 838 |
|
729 |
164 837 |
|
730 |
165 836 |
|
731 |
166 835 |
|
732 |
167 834 |
|
733 |
168 833 |
|
734 |
169 832 |
|
735 |
170 831 |
|
736 |
171 830 |
|
737 |
172 829 |
|
738 |
173 828 |
|
739 |
174 827 |
|
740 |
175 826 |
|
741 |
176 825 |
|
742 |
177 824 |
|
743 |
178 823 |
|
744 |
179 822 |
|
745 |
180 821 |
|
746 |
181 820 |
|
747 |
182 819 |
|
748 |
183 818 |
|
749 |
184 817 |
|
750 |
185 816 |
|
751 |
186 815 |
|
752 |
187 814 |
|
753 |
188 813 |
|
754 |
189 812 |
|
755 |
190 811 |
|
756 |
191 810 |
|
757 |
192 809 |
|
758 |
193 808 |
|
759 |
194 807 |
|
760 |
195 806 |
|
761 |
196 805 |
|
762 |
197 804 |
|
763 |
198 803 |
|
764 |
199 802 |
|
765 |
200 801 |
|
766 |
201 800 |
|
767 |
202 799 |
|
768 |
203 798 |
|
769 |
204 797 |
|
770 |
205 796 |
|
771 |
206 795 |
|
772 |
207 794 |
|
773 |
208 793 |
|
774 |
209 792 |
|
775 |
210 791 |
|
776 |
211 790 |
|
777 |
212 789 |
|
778 |
213 788 |
|
779 |
214 787 |
|
780 |
215 786 |
|
781 |
216 785 |
|
782 |
217 784 |
|
783 |
218 783 |
|
784 |
219 782 |
|
785 |
220 781 |
|
786 |
221 780 |
|
787 |
222 779 |
|
788 |
223 778 |
|
789 |
224 777 |
|
790 |
225 776 |
|
791 |
226 775 |
|
792 |
227 774 |
|
793 |
228 773 |
|
794 |
229 772 |
|
795 |
230 771 |
|
796 |
231 770 |
|
797 |
232 769 |
|
798 |
233 768 |
|
799 |
234 767 |
|
800 |
235 766 |
|
801 |
236 765 |
|
802 |
237 764 |
|
803 |
238 763 |
|
804 |
239 762 |
|
805 |
240 761 |
|
806 |
241 760 |
|
807 |
242 759 |
|
808 |
243 758 |
|
809 |
244 757 |
|
810 |
245 756 |
|
811 |
246 755 |
|
812 |
247 754 |
|
813 |
248 753 |
|
814 |
249 752 |
|
815 |
250 751 |
|
816 |
251 750 |
|
817 |
252 749 |
|
818 |
253 748 |
|
819 |
254 747 |
|
820 |
255 746 |
|
821 |
256 745 |
|
822 |
257 744 |
|
823 |
258 743 |
|
824 |
259 742 |
|
825 |
260 741 |
|
826 |
261 740 |
|
827 |
262 739 |
|
828 |
263 738 |
|
829 |
264 737 |
|
830 |
265 736 |
|
831 |
266 735 |
|
832 |
267 734 |
|
833 |
268 733 |
|
834 |
269 732 |
|
835 |
270 731 |
|
836 |
271 730 |
|
837 |
272 729 |
|
838 |
273 728 |
|
839 |
274 727 |
|
840 |
275 726 |
|
841 |
276 725 |
|
842 |
277 724 |
|
843 |
278 723 |
|
844 |
279 722 |
|
845 |
280 721 |
|
846 |
281 720 |
|
847 |
282 719 |
|
848 |
283 718 |
|
849 |
284 717 |
|
850 |
285 716 |
|
851 |
286 715 |
|
852 |
287 714 |
|
853 |
288 713 |
|
854 |
289 712 |
|
855 |
290 711 |
|
856 |
291 710 |
|
857 |
292 709 |
|
858 |
293 708 |
|
859 |
294 707 |
|
860 |
295 706 |
|
861 |
296 705 |
|
862 |
297 704 |
|
863 |
298 703 |
|
864 |
299 702 |
|
865 |
300 701 |
|
866 |
301 700 |
|
867 |
302 699 |
|
868 |
303 698 |
|
869 |
304 697 |
|
870 |
305 696 |
|
871 |
306 695 |
|
872 |
307 694 |
|
873 |
308 693 |
|
874 |
309 692 |
|
875 |
310 691 |
|
876 |
311 690 |
|
877 |
312 689 |
|
878 |
313 688 |
|
879 |
314 687 |
|
880 |
315 686 |
|
881 |
316 685 |
|
882 |
317 684 |
|
883 |
318 683 |
|
884 |
319 682 |
|
885 |
320 681 |
|
886 |
321 680 |
|
887 |
322 679 |
|
888 |
323 678 |
|
889 |
324 677 |
|
890 |
325 676 |
|
891 |
326 675 |
|
892 |
327 674 |
|
893 |
328 673 |
|
894 |
329 672 |
|
895 |
330 671 |
|
896 |
331 670 |
|
897 |
332 669 |
|
898 |
333 668 |
|
899 |
334 667 |
|
900 |
335 666 |
|
901 |
336 665 |
|
902 |
337 664 |
|
903 |
338 663 |
|
904 |
339 662 |
|
905 |
340 661 |
|
906 |
341 660 |
|
907 |
342 659 |
|
908 |
343 658 |
|
909 |
344 657 |
|
910 |
345 656 |
|
911 |
346 655 |
|
912 |
347 654 |
|
913 |
348 653 |
|
914 |
349 652 |
|
915 |
350 651 |
|
916 |
351 650 |
|
917 |
352 649 |
|
918 |
353 648 |
|
919 |
354 647 |
|
920 |
355 646 |
|
921 |
356 645 |
|
922 |
357 644 |
|
923 |
358 643 |
|
924 |
359 642 |
|
925 |
360 641 |
|
926 |
361 640 |
|
927 |
362 639 |
|
928 |
363 638 |
|
929 |
364 637 |
|
930 |
365 636 |
|
931 |
366 635 |
|
932 |
367 634 |
|
933 |
368 633 |
|
934 |
369 632 |
|
935 |
370 631 |
|
936 |
371 630 |
|
937 |
372 629 |
|
938 |
373 628 |
|
939 |
374 627 |
|
940 |
375 626 |
|
941 |
376 625 |
|
942 |
377 624 |
|
943 |
378 623 |
|
944 |
379 622 |
|
945 |
380 621 |
|
946 |
381 620 |
|
947 |
382 619 |
|
948 |
383 618 |
|
949 |
384 617 |
|
950 |
385 616 |
|
951 |
386 615 |
|
952 |
387 614 |
|
953 |
388 613 |
|
954 |
389 612 |
|
955 |
390 611 |
|
956 |
391 610 |
|
957 |
392 609 |
|
958 |
393 608 |
|
959 |
394 607 |
|
960 |
395 606 |
|
961 |
396 605 |
|
962 |
397 604 |
|
963 |
398 603 |
|
964 |
399 602 |
|
965 |
400 601 |
|
966 |
401 600 |
|
967 |
402 599 |
|
968 |
403 598 |
|
969 |
404 597 |
|
970 |
405 596 |
|
971 |
406 595 |
|
972 |
407 594 |
|
973 |
408 593 |
|
974 |
409 592 |
|
975 |
410 591 |
|
976 |
411 590 |
|
977 |
412 589 |
|
978 |
413 588 |
|
979 |
414 587 |
|
980 |
415 586 |
|
981 |
416 585 |
|
982 |
417 584 |
|
983 |
418 583 |
|
984 |
419 582 |
|
985 |
420 581 |
|
986 |
421 580 |
|
987 |
422 579 |
|
988 |
423 578 |
|
989 |
424 577 |
|
990 |
425 576 |
|
991 |
426 575 |
|
992 |
427 574 |
|
993 |
428 573 |
|
994 |
429 572 |
|
995 |
430 571 |
|
996 |
431 570 |
|
997 |
432 569 |
|
998 |
433 568 |
|
999 |
434 567 |
|
1000 |
435 566 |
|
1001 |
436 565 |
|
1002 |
437 564 |
|
1003 |
438 563 |
|
1004 |
439 562 |
|
1005 |
440 561 |
|
1006 |
441 560 |
|
1007 |
442 559 |
|
1008 |
443 558 |
|
1009 |
444 557 |
|
1010 |
445 556 |
|
1011 |
446 555 |
|
1012 |
447 554 |
|
1013 |
448 553 |
|
1014 |
449 552 |
|
1015 |
450 551 |
|
1016 |
451 550 |
|
1017 |
452 549 |
|
1018 |
453 548 |
|
1019 |
454 547 |
|
1020 |
455 546 |
|
1021 |
456 545 |
|
1022 |
457 544 |
|
1023 |
458 543 |
|
1024 |
459 542 |
|
1025 |
460 541 |
|
1026 |
461 540 |
|
1027 |
462 539 |
|
1028 |
463 538 |
|
1029 |
464 537 |
|
1030 |
465 536 |
|
1031 |
466 535 |
|
1032 |
467 534 |
|
1033 |
468 533 |
|
1034 |
469 532 |
|
1035 |
470 531 |
|
1036 |
471 530 |
|
1037 |
472 529 |
|
1038 |
473 528 |
|
1039 |
474 527 |
|
1040 |
475 526 |
|
1041 |
476 525 |
|
1042 |
477 524 |
|
1043 |
478 523 |
|
1044 |
479 522 |
|
1045 |
480 521 |
|
1046 |
481 520 |
|
1047 |
482 519 |
|
1048 |
483 518 |
|
1049 |
484 517 |
|
1050 |
485 516 |
|
1051 |
486 515 |
|
1052 |
487 514 |
|
1053 |
488 513 |
|
1054 |
489 512 |
|
1055 |
490 511 |
|
1056 |
491 510 |
|
1057 |
492 509 |
|
1058 |
493 508 |
|
1059 |
494 507 |
|
1060 |
495 506 |
|
1061 |
496 505 |
|
1062 |
497 504 |
|
1063 |
498 503 |
|
1064 |
499 502 |
|
1065 |
500 501 |
|
1066 |
501 500 |
|
1067 |
502 499 |
|
1068 |
503 498 |
|
1069 |
504 497 |
|
1070 |
505 496 |
|
1071 |
506 495 |
|
1072 |
507 494 |
|
1073 |
508 493 |
|
1074 |
509 492 |
|
1075 |
510 491 |
|
1076 |
511 490 |
|
1077 |
512 489 |
|
1078 |
513 488 |
|
1079 |
514 487 |
|
1080 |
515 486 |
|
1081 |
516 485 |
|
1082 |
517 484 |
|
1083 |
518 483 |
|
1084 |
519 482 |
|
1085 |
520 481 |
|
1086 |
521 480 |
|
1087 |
522 479 |
|
1088 |
523 478 |
|
1089 |
524 477 |
|
1090 |
525 476 |
|
1091 |
526 475 |
|
1092 |
527 474 |
|
1093 |
528 473 |
|
1094 |
529 472 |
|
1095 |
530 471 |
|
1096 |
531 470 |
|
1097 |
532 469 |
|
1098 |
533 468 |
|
1099 |
534 467 |
|
1100 |
535 466 |
|
1101 |
536 465 |
|
1102 |
537 464 |
|
1103 |
538 463 |
|
1104 |
539 462 |
|
1105 |
540 461 |
|
1106 |
541 460 |
|
1107 |
542 459 |
|
1108 |
543 458 |
|
1109 |
544 457 |
|
1110 |
545 456 |
|
1111 |
546 455 |
|
1112 |
547 454 |
|
1113 |
548 453 |
|
1114 |
549 452 |
|
1115 |
550 451 |
|
1116 |
551 450 |
|
1117 |
552 449 |
|
1118 |
553 448 |
|
1119 |
554 447 |
|
1120 |
555 446 |
|
1121 |
556 445 |
|
1122 |
557 444 |
|
1123 |
558 443 |
|
1124 |
559 442 |
|
1125 |
560 441 |
|
1126 |
561 440 |
|
1127 |
562 439 |
|
1128 |
563 438 |
|
1129 |
564 437 |
|
1130 |
565 436 |
|
1131 |
566 435 |
|
1132 |
567 434 |
|
1133 |
568 433 |
|
1134 |
569 432 |
|
1135 |
570 431 |
|
1136 |
571 430 |
|
1137 |
572 429 |
|
1138 |
573 428 |
|
1139 |
574 427 |
|
1140 |
575 426 |
|
1141 |
576 425 |
|
1142 |
577 424 |
|
1143 |
578 423 |
|
1144 |
579 422 |
|
1145 |
580 421 |
|
1146 |
581 420 |
|
1147 |
582 419 |
|
1148 |
583 418 |
|
1149 |
584 417 |
|
1150 |
585 416 |
|
1151 |
586 415 |
|
1152 |
587 414 |
|
1153 |
588 413 |
|
1154 |
589 412 |
|
1155 |
590 411 |
|
1156 |
591 410 |
|
1157 |
592 409 |
|
1158 |
593 408 |
|
1159 |
594 407 |
|
1160 |
595 406 |
|
1161 |
596 405 |
|
1162 |
597 404 |
|
1163 |
598 403 |
|
1164 |
599 402 |
|
1165 |
600 401 |
|
1166 |
601 400 |
|
1167 |
602 399 |
|
1168 |
603 398 |
|
1169 |
604 397 |
|
1170 |
605 396 |
|
1171 |
606 395 |
|
1172 |
607 394 |
|
1173 |
608 393 |
|
1174 |
609 392 |
|
1175 |
610 391 |
|
1176 |
611 390 |
|
1177 |
612 389 |
|
1178 |
613 388 |
|
1179 |
614 387 |
|
1180 |
615 386 |
|
1181 |
616 385 |
|
1182 |
617 384 |
|
1183 |
618 383 |
|
1184 |
619 382 |
|
1185 |
620 381 |
|
1186 |
621 380 |
|
1187 |
622 379 |
|
1188 |
623 378 |
|
1189 |
624 377 |
|
1190 |
625 376 |
|
1191 |
626 375 |
|
1192 |
627 374 |
|
1193 |
628 373 |
|
1194 |
629 372 |
|
1195 |
630 371 |
|
1196 |
631 370 |
|
1197 |
632 369 |
|
1198 |
633 368 |
|
1199 |
634 367 |
|
1200 |
635 366 |
|
1201 |
636 365 |
|
1202 |
637 364 |
|
1203 |
638 363 |
|
1204 |
639 362 |
|
1205 |
640 361 |
|
1206 |
641 360 |
|
1207 |
642 359 |
|
1208 |
643 358 |
|
1209 |
644 357 |
|
1210 |
645 356 |
|
1211 |
646 355 |
|
1212 |
647 354 |
|
1213 |
648 353 |
|
1214 |
649 352 |
|
1215 |
650 351 |
|
1216 |
651 350 |
|
1217 |
652 349 |
|
1218 |
653 348 |
|
1219 |
654 347 |
|
1220 |
655 346 |
|
1221 |
656 345 |
|
1222 |
657 344 |
|
1223 |
658 343 |
|
1224 |
659 342 |
|
1225 |
660 341 |
|
1226 |
661 340 |
|
1227 |
662 339 |
|
1228 |
663 338 |
|
1229 |
664 337 |
|
1230 |
665 336 |
|
1231 |
666 335 |
|
1232 |
667 334 |
|
1233 |
668 333 |
|
1234 |
669 332 |
|
1235 |
670 331 |
|
1236 |
671 330 |
|
1237 |
672 329 |
|
1238 |
673 328 |
|
1239 |
674 327 |
|
1240 |
675 326 |
|
1241 |
676 325 |
|
1242 |
677 324 |
|
1243 |
678 323 |
|
1244 |
679 322 |
|
1245 |
680 321 |
|
1246 |
681 320 |
|
1247 |
682 319 |
|
1248 |
683 318 |
|
1249 |
684 317 |
|
1250 |
685 316 |
|
1251 |
686 315 |
|
1252 |
687 314 |
|
1253 |
688 313 |
|
1254 |
689 312 |
|
1255 |
690 311 |
|
1256 |
691 310 |
|
1257 |
692 309 |
|
1258 |
693 308 |
|
1259 |
694 307 |
|
1260 |
695 306 |
|
1261 |
696 305 |
|
1262 |
697 304 |
|
1263 |
698 303 |
|
1264 |
699 302 |
|
1265 |
700 301 |
|
1266 |
701 300 |
|
1267 |
702 299 |
|
1268 |
703 298 |
|
1269 |
704 297 |
|
1270 |
705 296 |
|
1271 |
706 295 |
|
1272 |
707 294 |
|
1273 |
708 293 |
|
1274 |
709 292 |
|
1275 |
710 291 |
|
1276 |
711 290 |
|
1277 |
712 289 |
|
1278 |
713 288 |
|
1279 |
714 287 |
|
1280 |
715 286 |
|
1281 |
716 285 |
|
1282 |
717 284 |
|
1283 |
718 283 |
|
1284 |
719 282 |
|
1285 |
720 281 |
|
1286 |
721 280 |
|
1287 |
722 279 |
|
1288 |
723 278 |
|
1289 |
724 277 |
|
1290 |
725 276 |
|
1291 |
726 275 |
|
1292 |
727 274 |
|
1293 |
728 273 |
|
1294 |
729 272 |
|
1295 |
730 271 |
|
1296 |
731 270 |
|
1297 |
732 269 |
|
1298 |
733 268 |
|
1299 |
734 267 |
|
1300 |
735 266 |
|
1301 |
736 265 |
|
1302 |
737 264 |
|
1303 |
738 263 |
|
1304 |
739 262 |
|
1305 |
740 261 |
|
1306 |
741 260 |
|
1307 |
742 259 |
|
1308 |
743 258 |
|
1309 |
744 257 |
|
1310 |
745 256 |
|
1311 |
746 255 |
|
1312 |
747 254 |
|
1313 |
748 253 |
|
1314 |
749 252 |
|
1315 |
750 251 |
|
1316 |
751 250 |
|
1317 |
752 249 |
|
1318 |
753 248 |
|
1319 |
754 247 |
|
1320 |
755 246 |
|
1321 |
756 245 |
|
1322 |
757 244 |
|
1323 |
758 243 |
|
1324 |
759 242 |
|
1325 |
760 241 |
|
1326 |
761 240 |
|
1327 |
762 239 |
|
1328 |
763 238 |
|
1329 |
764 237 |
|
1330 |
765 236 |
|
1331 |
766 235 |
|
1332 |
767 234 |
|
1333 |
768 233 |
|
1334 |
769 232 |
|
1335 |
770 231 |
|
1336 |
771 230 |
|
1337 |
772 229 |
|
1338 |
773 228 |
|
1339 |
774 227 |
|
1340 |
775 226 |
|
1341 |
776 225 |
|
1342 |
777 224 |
|
1343 |
778 223 |
|
1344 |
779 222 |
|
1345 |
780 221 |
|
1346 |
781 220 |
|
1347 |
782 219 |
|
1348 |
783 218 |
|
1349 |
784 217 |
|
1350 |
785 216 |
|
1351 |
786 215 |
|
1352 |
787 214 |
|
1353 |
788 213 |
|
1354 |
789 212 |
|
1355 |
790 211 |
|
1356 |
791 210 |
|
1357 |
792 209 |
|
1358 |
793 208 |
|
1359 |
794 207 |
|
1360 |
795 206 |
|
1361 |
796 205 |
|
1362 |
797 204 |
|
1363 |
798 203 |
|
1364 |
799 202 |
|
1365 |
800 201 |
|
1366 |
801 200 |
|
1367 |
802 199 |
|
1368 |
803 198 |
|
1369 |
804 197 |
|
1370 |
805 196 |
|
1371 |
806 195 |
|
1372 |
807 194 |
|
1373 |
808 193 |
|
1374 |
809 192 |
|
1375 |
810 191 |
|
1376 |
811 190 |
|
1377 |
812 189 |
|
1378 |
813 188 |
|
1379 |
814 187 |
|
1380 |
815 186 |
|
1381 |
816 185 |
|
1382 |
817 184 |
|
1383 |
818 183 |
|
1384 |
819 182 |
|
1385 |
820 181 |
|
1386 |
821 180 |
|
1387 |
822 179 |
|
1388 |
823 178 |
|
1389 |
824 177 |
|
1390 |
825 176 |
|
1391 |
826 175 |
|
1392 |
827 174 |
|
1393 |
828 173 |
|
1394 |
829 172 |
|
1395 |
830 171 |
|
1396 |
831 170 |
|
1397 |
832 169 |
|
1398 |
833 168 |
|
1399 |
834 167 |
|
1400 |
835 166 |
|
1401 |
836 165 |
|
1402 |
837 164 |
|
1403 |
838 163 |
|
1404 |
839 162 |
|
1405 |
840 161 |
|
1406 |
841 160 |
|
1407 |
842 159 |
|
1408 |
843 158 |
|
1409 |
844 157 |
|
1410 |
845 156 |
|
1411 |
846 155 |
|
1412 |
847 154 |
|
1413 |
848 153 |
|
1414 |
849 152 |
|
1415 |
850 151 |
|
1416 |
851 150 |
|
1417 |
852 149 |
|
1418 |
853 148 |
|
1419 |
854 147 |
|
1420 |
855 146 |
|
1421 |
856 145 |
|
1422 |
857 144 |
|
1423 |
858 143 |
|
1424 |
859 142 |
|
1425 |
860 141 |
|
1426 |
861 140 |
|
1427 |
862 139 |
|
1428 |
863 138 |
|
1429 |
864 137 |
|
1430 |
865 136 |
|
1431 |
866 135 |
|
1432 |
867 134 |
|
1433 |
868 133 |
|
1434 |
869 132 |
|
1435 |
870 131 |
|
1436 |
871 130 |
|
1437 |
872 129 |
|
1438 |
873 128 |
|
1439 |
874 127 |
|
1440 |
875 126 |
|
1441 |
876 125 |
|
1442 |
877 124 |
|
1443 |
878 123 |
|
1444 |
879 122 |
|
1445 |
880 121 |
|
1446 |
881 120 |
|
1447 |
882 119 |
|
1448 |
883 118 |
|
1449 |
884 117 |
|
1450 |
885 116 |
|
1451 |
886 115 |
|
1452 |
887 114 |
|
1453 |
888 113 |
|
1454 |
889 112 |
|
1455 |
890 111 |
|
1456 |
891 110 |
|
1457 |
892 109 |
|
1458 |
893 108 |
|
1459 |
894 107 |
|
1460 |
895 106 |
|
1461 |
896 105 |
|
1462 |
897 104 |
|
1463 |
898 103 |
|
1464 |
899 102 |
|
1465 |
900 101 |
|
1466 |
901 100 |
|
1467 |
902 99 |
|
1468 |
903 98 |
|
1469 |
904 97 |
|
1470 |
905 96 |
|
1471 |
906 95 |
|
1472 |
907 94 |
|
1473 |
908 93 |
|
1474 |
909 92 |
|
1475 |
910 91 |
|
1476 |
911 90 |
|
1477 |
912 89 |
|
1478 |
913 88 |
|
1479 |
914 87 |
|
1480 |
915 86 |
|
1481 |
916 85 |
|
1482 |
917 84 |
|
1483 |
918 83 |
|
1484 |
919 82 |
|
1485 |
920 81 |
|
1486 |
921 80 |
|
1487 |
922 79 |
|
1488 |
923 78 |
|
1489 |
924 77 |
|
1490 |
925 76 |
|
1491 |
926 75 |
|
1492 |
927 74 |
|
1493 |
928 73 |
|
1494 |
929 72 |
|
1495 |
930 71 |
|
1496 |
931 70 |
|
1497 |
932 69 |
|
1498 |
933 68 |
|
1499 |
934 67 |
|
1500 |
935 66 |
|
1501 |
936 65 |
|
1502 |
937 64 |
|
1503 |
938 63 |
|
1504 |
939 62 |
|
1505 |
940 61 |
|
1506 |
941 60 |
|
1507 |
942 59 |
|
1508 |
943 58 |
|
1509 |
944 57 |
|
1510 |
945 56 |
|
1511 |
946 55 |
|
1512 |
947 54 |
|
1513 |
948 53 |
|
1514 |
949 52 |
|
1515 |
950 51 |
|
1516 |
951 50 |
|
1517 |
952 49 |
|
1518 |
953 48 |
|
1519 |
954 47 |
|
1520 |
955 46 |
|
1521 |
956 45 |
|
1522 |
957 44 |
|
1523 |
958 43 |
|
1524 |
959 42 |
|
1525 |
960 41 |
|
1526 |
961 40 |
|
1527 |
962 39 |
|
1528 |
963 38 |
|
1529 |
964 37 |
|
1530 |
965 36 |
|
1531 |
966 35 |
|
1532 |
967 34 |
|
1533 |
968 33 |
|
1534 |
969 32 |
|
1535 |
970 31 |
|
1536 |
971 30 |
|
1537 |
972 29 |
|
1538 |
973 28 |
|
1539 |
974 27 |
|
1540 |
975 26 |
|
1541 |
976 25 |
|
1542 |
977 24 |
|
1543 |
978 23 |
|
1544 |
979 22 |
|
1545 |
980 21 |
|
1546 |
981 20 |
|
1547 |
982 19 |
|
1548 |
983 18 |
|
1549 |
984 17 |
|
1550 |
985 16 |
|
1551 |
986 15 |
|
1552 |
987 14 |
|
1553 |
988 13 |
|
1554 |
989 12 |
|
1555 |
990 11 |
|
1556 |
991 10 |
|
1557 |
992 9 |
|
1558 |
993 8 |
|
1559 |
994 7 |
|
1560 |
995 6 |
|
1561 |
996 5 |
|
1562 |
997 4 |
|
1563 |
998 3 |
|
1564 |
999 2 |
|
1565 |
1000 1 |
|
|
1
by brian
clean slate |
1566 |
drop table t1; |
1567 |
CREATE TABLE t1 ( |
|
1568 |
FieldKey varchar(36) NOT NULL default '', |
|
|
500
by Brian Aker
Re-enabled more tests. |
1569 |
LongVal bigint default NULL, |
1570 |
StringVal text, |
|
|
1
by brian
clean slate |
1571 |
KEY FieldKey (FieldKey), |
1572 |
KEY LongField (FieldKey,LongVal), |
|
1573 |
KEY StringField (FieldKey,StringVal(32)) |
|
1574 |
);
|
|
1575 |
INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3'); |
|
1576 |
SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; |
|
1577 |
FieldKey LongVal StringVal |
|
1578 |
1 0 2 |
|
1579 |
1 1 3 |
|
1580 |
1 2 1 |
|
1581 |
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; |
|
1582 |
FieldKey LongVal StringVal |
|
1583 |
3 1 2 |
|
1584 |
3 2 1 |
|
1585 |
3 3 3 |
|
1586 |
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; |
|
1587 |
FieldKey LongVal StringVal |
|
1588 |
3 1 2 |
|
1589 |
3 2 1 |
|
1590 |
3 3 3 |
|
1591 |
DROP TABLE t1; |
|
1592 |
CREATE TABLE t1 (a INT, b INT); |
|
1593 |
SET @id=0; |
|
1594 |
UPDATE t1 SET a=0 ORDER BY (a=@id), b; |
|
1595 |
DROP TABLE t1; |
|
|
1063.9.19
by Stewart Smith
order_by.test for MyISAM temp only: use temp myisam tables |
1596 |
CREATE TEMPORARY TABLE t1 ( id int NOT NULL default '0', menu int NOT NULL default '0', KEY id (id), KEY menu (menu)) ENGINE=MyISAM; |
|
1
by brian
clean slate |
1597 |
INSERT INTO t1 VALUES (11384, 2),(11392, 2); |
1598 |
SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ; |
|
1599 |
id
|
|
1600 |
11392
|
|
1601 |
drop table t1; |
|
1602 |
create table t1(a int, b int, index(b)); |
|
1603 |
insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); |
|
1604 |
explain select * from t1 where b=1 or b is null order by a; |
|
1605 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
500
by Brian Aker
Re-enabled more tests. |
1606 |
1 SIMPLE t1 ALL b NULL NULL NULL 6 Using where; Using filesort |
|
1
by brian
clean slate |
1607 |
select * from t1 where b=1 or b is null order by a; |
1608 |
a b |
|
1609 |
1 1 |
|
1610 |
2 1 |
|
1611 |
3 NULL |
|
1612 |
4 NULL |
|
1613 |
explain select * from t1 where b=2 or b is null order by a; |
|
1614 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
500
by Brian Aker
Re-enabled more tests. |
1615 |
1 SIMPLE t1 ALL b NULL NULL NULL 6 Using where; Using filesort |
|
1
by brian
clean slate |
1616 |
select * from t1 where b=2 or b is null order by a; |
1617 |
a b |
|
1618 |
3 NULL |
|
1619 |
4 NULL |
|
1620 |
5 2 |
|
1621 |
6 2 |
|
1622 |
drop table t1; |
|
1623 |
create table t1 (a int not null auto_increment, b int not null, c int not null, d int not null, |
|
1624 |
key(a,b,d), key(c,b,a)); |
|
1625 |
create table t2 like t1; |
|
1626 |
insert into t1 values (NULL, 1, 2, 0), (NULL, 2, 1, 1), (NULL, 3, 4, 2), (NULL, 4, 3, 3); |
|
1627 |
insert into t2 select null, b, c, d from t1; |
|
1628 |
insert into t1 select null, b, c, d from t2; |
|
1629 |
insert into t2 select null, b, c, d from t1; |
|
1630 |
insert into t1 select null, b, c, d from t2; |
|
1631 |
insert into t2 select null, b, c, d from t1; |
|
1632 |
insert into t1 select null, b, c, d from t2; |
|
1633 |
insert into t2 select null, b, c, d from t1; |
|
1634 |
insert into t1 select null, b, c, d from t2; |
|
1635 |
insert into t2 select null, b, c, d from t1; |
|
1636 |
insert into t1 select null, b, c, d from t2; |
|
1637 |
optimize table t1; |
|
1638 |
Table Op Msg_type Msg_text |
|
1639 |
test.t1 optimize status OK |
|
1640 |
set @row=10; |
|
1641 |
insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10; |
|
1642 |
select * from t1 where a=1 and b in (1) order by c, b, a; |
|
1643 |
a b c d |
|
1644 |
1 1 2 0 |
|
1645 |
1 1 12 -1 |
|
1646 |
1 1 52 -5 |
|
1647 |
1 1 92 -9 |
|
1648 |
select * from t1 where a=1 and b in (1); |
|
1649 |
a b c d |
|
1650 |
1 1 92 -9 |
|
1651 |
1 1 52 -5 |
|
1652 |
1 1 12 -1 |
|
1653 |
1 1 2 0 |
|
1654 |
drop table t1, t2; |
|
1655 |
create table t1 (col1 int, col int); |
|
1656 |
create table t2 (col2 int, col int); |
|
1657 |
insert into t1 values (1,1),(2,2),(3,3); |
|
1658 |
insert into t2 values (1,3),(2,2),(3,1); |
|
1659 |
select t1.* , t2.col as t2_col from t1 left join t2 on (t1.col1=t2.col2) |
|
1660 |
order by col; |
|
1661 |
col1 col t2_col |
|
1662 |
1 1 3 |
|
1663 |
2 2 2 |
|
1664 |
3 3 1 |
|
1665 |
select col1 as col, col from t1 order by col; |
|
1666 |
ERROR 23000: Column 'col' in order clause is ambiguous |
|
1667 |
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2 |
|
1668 |
order by col; |
|
1669 |
ERROR 23000: Column 'col' in order clause is ambiguous |
|
1670 |
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2 |
|
1671 |
order by col; |
|
1672 |
ERROR 23000: Column 'col' in order clause is ambiguous |
|
1673 |
select col1 from t1, t2 where t1.col1=t2.col2 order by col; |
|
1674 |
ERROR 23000: Column 'col' in order clause is ambiguous |
|
1675 |
select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2 |
|
1676 |
order by col; |
|
1677 |
ERROR 23000: Column 'col' in order clause is ambiguous |
|
1678 |
select t1.col as t1_col, t2.col from t1, t2 where t1.col1=t2.col2 |
|
1679 |
order by col; |
|
1680 |
t1_col col |
|
1681 |
3 1 |
|
1682 |
2 2 |
|
1683 |
1 3 |
|
1684 |
select col2 as c, col as c from t2 order by col; |
|
1685 |
c c |
|
1686 |
3 1 |
|
1687 |
2 2 |
|
1688 |
1 3 |
|
1689 |
select col2 as col, col as col2 from t2 order by col; |
|
1690 |
col col2 |
|
1691 |
1 3 |
|
1692 |
2 2 |
|
1693 |
3 1 |
|
1694 |
select t2.col2, t2.col, t2.col from t2 order by col; |
|
1695 |
col2 col col |
|
1696 |
3 1 1 |
|
1697 |
2 2 2 |
|
1698 |
1 3 3 |
|
1699 |
select t2.col2 as col from t2 order by t2.col; |
|
1700 |
col
|
|
1701 |
3
|
|
1702 |
2
|
|
1703 |
1
|
|
1704 |
select t2.col2 as col, t2.col from t2 order by t2.col; |
|
1705 |
col col |
|
1706 |
3 1 |
|
1707 |
2 2 |
|
1708 |
1 3 |
|
1709 |
select t2.col2, t2.col, t2.col from t2 order by t2.col; |
|
1710 |
col2 col col |
|
1711 |
3 1 1 |
|
1712 |
2 2 2 |
|
1713 |
1 3 3 |
|
1714 |
drop table t1, t2; |
|
1715 |
create table t1 (a char(25)); |
|
1716 |
insert into t1 set a = repeat('x', 20); |
|
1717 |
insert into t1 set a = concat(repeat('x', 19), 'z'); |
|
1718 |
insert into t1 set a = concat(repeat('x', 19), 'ab'); |
|
1719 |
insert into t1 set a = concat(repeat('x', 19), 'aa'); |
|
1720 |
set max_sort_length=20; |
|
1721 |
select a from t1 order by a; |
|
1722 |
a
|
|
|
500
by Brian Aker
Re-enabled more tests. |
1723 |
xxxxxxxxxxxxxxxxxxxx
|
1724 |
xxxxxxxxxxxxxxxxxxxz
|
|
|
1
by brian
clean slate |
1725 |
xxxxxxxxxxxxxxxxxxxab
|
1726 |
xxxxxxxxxxxxxxxxxxxaa
|
|
1727 |
drop table t1; |
|
|
1063.9.19
by Stewart Smith
order_by.test for MyISAM temp only: use temp myisam tables |
1728 |
create temporary table t1 ( |
|
1
by brian
clean slate |
1729 |
`sid` decimal(8,0) default null, |
1730 |
`wnid` varchar(11) not null default '', |
|
1731 |
key `wnid14` (`wnid`(4)), |
|
1732 |
key `wnid` (`wnid`) |
|
|
500
by Brian Aker
Re-enabled more tests. |
1733 |
) engine=myisam; |
|
1
by brian
clean slate |
1734 |
insert into t1 (`sid`, `wnid`) values |
1735 |
('10100','01019000000'),('37986','01019000000'),('37987','01019010000'), |
|
1736 |
('39560','01019090000'),('37989','01019000000'),('37990','01019011000'), |
|
1737 |
('37991','01019011000'),('37992','01019019000'),('37993','01019030000'), |
|
1738 |
('37994','01019090000'),('475','02070000000'),('25253','02071100000'), |
|
1739 |
('25255','02071100000'),('25256','02071110000'),('25258','02071130000'), |
|
1740 |
('25259','02071190000'),('25260','02071200000'),('25261','02071210000'), |
|
1741 |
('25262','02071290000'),('25263','02071300000'),('25264','02071310000'), |
|
1742 |
('25265','02071310000'),('25266','02071320000'),('25267','02071320000'), |
|
1743 |
('25269','02071330000'),('25270','02071340000'),('25271','02071350000'), |
|
1744 |
('25272','02071360000'),('25273','02071370000'),('25281','02071391000'), |
|
1745 |
('25282','02071391000'),('25283','02071399000'),('25284','02071400000'), |
|
1746 |
('25285','02071410000'),('25286','02071410000'),('25287','02071420000'), |
|
1747 |
('25288','02071420000'),('25291','02071430000'),('25290','02071440000'), |
|
1748 |
('25292','02071450000'),('25293','02071460000'),('25294','02071470000'), |
|
1749 |
('25295','02071491000'),('25296','02071491000'),('25297','02071499000'); |
|
1750 |
explain select * from t1 where wnid like '0101%' order by wnid; |
|
1751 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
500
by Brian Aker
Re-enabled more tests. |
1752 |
1 SIMPLE t1 range wnid14,wnid wnid 46 NULL 10 Using where |
|
1
by brian
clean slate |
1753 |
select * from t1 where wnid like '0101%' order by wnid; |
1754 |
sid wnid |
|
1755 |
10100 01019000000 |
|
1756 |
37986 01019000000 |
|
1757 |
37989 01019000000 |
|
1758 |
37987 01019010000 |
|
1759 |
37990 01019011000 |
|
1760 |
37991 01019011000 |
|
1761 |
37992 01019019000 |
|
1762 |
37993 01019030000 |
|
1763 |
39560 01019090000 |
|
1764 |
37994 01019090000 |
|
1765 |
drop table t1; |
|
1766 |
CREATE TABLE t1 (a int); |
|
1767 |
INSERT INTO t1 VALUES (2), (1), (1), (2), (1); |
|
1768 |
SELECT a FROM t1 ORDER BY a; |
|
1769 |
a
|
|
1770 |
1
|
|
1771 |
1
|
|
1772 |
1
|
|
1773 |
2
|
|
1774 |
2
|
|
1775 |
(SELECT a FROM t1) ORDER BY a; |
|
1776 |
a
|
|
1777 |
1
|
|
1778 |
1
|
|
1779 |
1
|
|
1780 |
2
|
|
1781 |
2
|
|
1782 |
DROP TABLE t1; |
|
1783 |
CREATE TABLE t1 (a int, b int); |
|
1784 |
INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10); |
|
1785 |
(SELECT b,a FROM t1 ORDER BY a,b) ORDER BY b,a; |
|
1786 |
b a |
|
1787 |
10 1 |
|
1788 |
10 2 |
|
1789 |
20 1 |
|
1790 |
20 2 |
|
1791 |
30 1 |
|
1792 |
30 2 |
|
1793 |
(SELECT b FROM t1 ORDER BY b DESC) ORDER BY b ASC; |
|
1794 |
b
|
|
1795 |
10
|
|
1796 |
10
|
|
1797 |
20
|
|
1798 |
20
|
|
1799 |
30
|
|
1800 |
30
|
|
1801 |
(SELECT b,a FROM t1 ORDER BY b,a) ORDER BY a,b; |
|
1802 |
b a |
|
1803 |
10 1 |
|
1804 |
20 1 |
|
1805 |
30 1 |
|
1806 |
10 2 |
|
1807 |
20 2 |
|
1808 |
30 2 |
|
1809 |
(SELECT b,a FROM t1 ORDER by b,a LIMIT 3) ORDER by a,b; |
|
1810 |
b a |
|
1811 |
10 1 |
|
1812 |
20 1 |
|
1813 |
10 2 |
|
1814 |
DROP TABLE t1; |
|
1815 |
CREATE TABLE t1 (a INT); |
|
1816 |
INSERT INTO t1 VALUES (1),(2); |
|
1817 |
SELECT a + 1 AS num FROM t1 ORDER BY 30 - num; |
|
1818 |
num
|
|
1819 |
3
|
|
1820 |
2
|
|
1821 |
SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str); |
|
1822 |
str
|
|
1823 |
test1
|
|
1824 |
test2
|
|
1825 |
SELECT a + 1 AS num FROM t1 GROUP BY 30 - num; |
|
1826 |
num
|
|
1827 |
3
|
|
1828 |
2
|
|
1829 |
SELECT a + 1 AS num FROM t1 HAVING 30 - num; |
|
1830 |
num
|
|
1831 |
2
|
|
1832 |
3
|
|
1833 |
SELECT a + 1 AS num, num + 1 FROM t1; |
|
1834 |
ERROR 42S22: Unknown column 'num' in 'field list' |
|
1835 |
SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1; |
|
1836 |
num (select num + 2 FROM t1 LIMIT 1) |
|
1837 |
2 4 |
|
1838 |
3 5 |
|
1839 |
SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a; |
|
1840 |
ERROR 42S22: Unknown column 'num' in 'on clause' |
|
1841 |
DROP TABLE t1; |
|
1842 |
CREATE TABLE bug25126 ( |
|
|
500
by Brian Aker
Re-enabled more tests. |
1843 |
val int NOT NULL AUTO_INCREMENT PRIMARY KEY |
|
1
by brian
clean slate |
1844 |
);
|
1845 |
UPDATE bug25126 SET MissingCol = MissingCol; |
|
1846 |
ERROR 42S22: Unknown column 'MissingCol' in 'field list' |
|
1847 |
UPDATE bug25126 SET val = val ORDER BY MissingCol; |
|
1848 |
ERROR 42S22: Unknown column 'MissingCol' in 'order clause' |
|
1849 |
UPDATE bug25126 SET val = val ORDER BY val; |
|
1850 |
UPDATE bug25126 SET val = 1 ORDER BY val; |
|
1851 |
UPDATE bug25126 SET val = 1 ORDER BY MissingCol; |
|
1852 |
ERROR 42S22: Unknown column 'MissingCol' in 'order clause' |
|
1853 |
UPDATE bug25126 SET val = 1 ORDER BY val, MissingCol; |
|
1854 |
ERROR 42S22: Unknown column 'MissingCol' in 'order clause' |
|
1855 |
UPDATE bug25126 SET val = MissingCol ORDER BY MissingCol; |
|
1856 |
ERROR 42S22: Unknown column 'MissingCol' in 'order clause' |
|
1857 |
UPDATE bug25126 SET MissingCol = 1 ORDER BY val, MissingCol; |
|
1858 |
ERROR 42S22: Unknown column 'MissingCol' in 'order clause' |
|
1859 |
UPDATE bug25126 SET MissingCol = 1 ORDER BY MissingCol; |
|
1860 |
ERROR 42S22: Unknown column 'MissingCol' in 'order clause' |
|
1861 |
UPDATE bug25126 SET MissingCol = val ORDER BY MissingCol; |
|
1862 |
ERROR 42S22: Unknown column 'MissingCol' in 'order clause' |
|
1863 |
UPDATE bug25126 SET MissingCol = MissingCol ORDER BY MissingCol; |
|
1864 |
ERROR 42S22: Unknown column 'MissingCol' in 'order clause' |
|
1865 |
DROP TABLE bug25126; |
|
1866 |
CREATE TABLE t1 (a int); |
|
1867 |
SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1; |
|
1868 |
val val1 |
|
1869 |
SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val; |
|
1870 |
ERROR 23000: Column 'val' in order clause is ambiguous |
|
1871 |
SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1; |
|
1872 |
ERROR 23000: Column 'val' in order clause is ambiguous |
|
1873 |
DROP TABLE t1; |
|
1874 |
CREATE TABLE t1 (a int); |
|
1875 |
INSERT INTO t1 VALUES (3), (2), (4), (1); |
|
1876 |
SELECT a, IF(a IN (2,3), a, a+10) FROM t1 |
|
1877 |
ORDER BY IF(a IN (2,3), a, a+10); |
|
1878 |
a IF(a IN (2,3), a, a+10) |
|
1879 |
2 2 |
|
1880 |
3 3 |
|
1881 |
1 11 |
|
1882 |
4 14 |
|
1883 |
SELECT a, IF(a NOT IN (2,3), a, a+10) FROM t1 |
|
1884 |
ORDER BY IF(a NOT IN (2,3), a, a+10); |
|
1885 |
a IF(a NOT IN (2,3), a, a+10) |
|
1886 |
1 1 |
|
1887 |
4 4 |
|
1888 |
2 12 |
|
1889 |
3 13 |
|
1890 |
SELECT a, IF(a IN (2,3), a, a+10) FROM t1 |
|
1891 |
ORDER BY IF(a NOT IN (2,3), a, a+10); |
|
1892 |
a IF(a IN (2,3), a, a+10) |
|
1893 |
1 11 |
|
1894 |
4 14 |
|
1895 |
2 2 |
|
1896 |
3 3 |
|
1897 |
SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1 |
|
1898 |
ORDER BY IF(a BETWEEN 2 AND 3, a, a+10); |
|
1899 |
a IF(a BETWEEN 2 AND 3, a, a+10) |
|
1900 |
2 2 |
|
1901 |
3 3 |
|
1902 |
1 11 |
|
1903 |
4 14 |
|
1904 |
SELECT a, IF(a NOT BETWEEN 2 AND 3, a, a+10) FROM t1 |
|
1905 |
ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10); |
|
1906 |
a IF(a NOT BETWEEN 2 AND 3, a, a+10) |
|
1907 |
1 1 |
|
1908 |
4 4 |
|
1909 |
2 12 |
|
1910 |
3 13 |
|
1911 |
SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1 |
|
1912 |
ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10); |
|
1913 |
a IF(a BETWEEN 2 AND 3, a, a+10) |
|
1914 |
1 11 |
|
1915 |
4 14 |
|
1916 |
2 2 |
|
1917 |
3 3 |
|
1918 |
SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2 |
|
1919 |
FROM t1 GROUP BY x1, x2; |
|
1920 |
x1 x2 |
|
1921 |
3
|
|
1922 |
4
|
|
1923 |
1
|
|
1924 |
2
|
|
1925 |
SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2 |
|
1926 |
FROM t1 GROUP BY x1, IF(a NOT IN (1,2), a, ''); |
|
1927 |
x1 x2 |
|
1928 |
3
|
|
1929 |
4
|
|
1930 |
1
|
|
1931 |
2
|
|
1932 |
SELECT a, a IN (1,2) FROM t1 ORDER BY a IN (1,2); |
|
1933 |
a a IN (1,2) |
|
1934 |
3 0 |
|
1935 |
4 0 |
|
1936 |
2 1 |
|
1937 |
1 1 |
|
1938 |
SELECT a FROM t1 ORDER BY a IN (1,2); |
|
1939 |
a
|
|
1940 |
3
|
|
1941 |
4
|
|
1942 |
2
|
|
1943 |
1
|
|
1944 |
SELECT a+10 FROM t1 ORDER BY a IN (1,2); |
|
1945 |
a+10 |
|
1946 |
13
|
|
1947 |
14
|
|
1948 |
12
|
|
1949 |
11
|
|
1950 |
SELECT a, IF(a IN (1,2), a, a+10) FROM t1 |
|
1951 |
ORDER BY IF(a IN (3,4), a, a+10); |
|
1952 |
a IF(a IN (1,2), a, a+10) |
|
1953 |
3 13 |
|
1954 |
4 14 |
|
1955 |
1 1 |
|
1956 |
2 2 |
|
1957 |
DROP TABLE t1; |
|
1958 |
create table t1 (a int not null, b int not null, c int not null); |
|
1959 |
insert t1 values (1,1,1),(1,1,2),(1,2,1); |
|
1960 |
select a, b from t1 group by a, b order by sum(c); |
|
1961 |
a b |
|
1962 |
1 2 |
|
1963 |
1 1 |
|
1964 |
drop table t1; |
|
1965 |
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); |
|
1966 |
INSERT INTO t1 VALUES (1,1), (2,2), (3,3); |
|
1967 |
explain SELECT t1.b as a, t2.b as c FROM |
|
1968 |
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) |
|
1969 |
ORDER BY c; |
|
1970 |
id select_type table type possible_keys key key_len ref rows Extra |
|
1971 |
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort |
|
1972 |
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 |
|
1973 |
SELECT t2.b as c FROM |
|
1974 |
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) |
|
1975 |
ORDER BY c; |
|
1976 |
c
|
|
1977 |
NULL
|
|
1978 |
NULL
|
|
1979 |
2
|
|
1980 |
explain SELECT t1.b as a, t2.b as c FROM |
|
1981 |
t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) |
|
1982 |
ORDER BY c; |
|
1983 |
id select_type table type possible_keys key key_len ref rows Extra |
|
1984 |
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 |
|
1985 |
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 |
|
1986 |
CREATE TABLE t2 LIKE t1; |
|
1987 |
INSERT INTO t2 SELECT * from t1; |
|
1988 |
CREATE TABLE t3 LIKE t1; |
|
1989 |
INSERT INTO t3 SELECT * from t1; |
|
1990 |
CREATE TABLE t4 LIKE t1; |
|
1991 |
INSERT INTO t4 SELECT * from t1; |
|
1992 |
INSERT INTO t1 values (0,0),(4,4); |
|
1993 |
SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a) |
|
1994 |
ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b; |
|
1995 |
b
|
|
1996 |
NULL
|
|
1997 |
NULL
|
|
1998 |
1
|
|
1999 |
2
|
|
2000 |
3
|
|
2001 |
DROP TABLE t1,t2,t3,t4; |
|
2002 |
create table t1 (a int, b int, c int); |
|
2003 |
insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9); |
|
2004 |
select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc; |
|
2005 |
a ratio |
|
2006 |
1 0.5000 |
|
2007 |
19 1.3333 |
|
2008 |
9 2.6667 |
|
2009 |
drop table t1; |
|
2010 |
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b)); |
|
2011 |
INSERT INTO t1 VALUES (1,1),(2,2); |
|
2012 |
CREATE TABLE t2 (a INT, b INT, KEY a (a,b)); |
|
2013 |
INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2); |
|
|
501
by Brian Aker
Removed EXPL from tests. Why? The explain output is based on MyISAM... |
2014 |
SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b; |
2015 |
1
|
|
2016 |
1
|
|
2017 |
1
|
|
|
1
by brian
clean slate |
2018 |
DROP TABLE t1,t2; |
2019 |
CREATE TABLE t1 ( |
|
2020 |
a INT, |
|
2021 |
b INT, |
|
2022 |
PRIMARY KEY (a), |
|
2023 |
KEY ab(a, b) |
|
2024 |
);
|
|
2025 |
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4); |
|
2026 |
INSERT INTO t1 SELECT a + 4, b + 4 FROM t1; |
|
2027 |
INSERT INTO t1 SELECT a + 8, b + 8 FROM t1; |
|
2028 |
INSERT INTO t1 SELECT a +16, b +16 FROM t1; |
|
2029 |
INSERT INTO t1 SELECT a +32, b +32 FROM t1; |
|
2030 |
INSERT INTO t1 SELECT a +64, b +64 FROM t1; |
|
2031 |
SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; |
|
2032 |
a
|
|
2033 |
1
|
|
2034 |
2
|
|
2035 |
3
|
|
2036 |
4
|
|
2037 |
5
|
|
2038 |
6
|
|
2039 |
7
|
|
2040 |
8
|
|
2041 |
9
|
|
2042 |
10
|
|
2043 |
11
|
|
2044 |
12
|
|
2045 |
13
|
|
2046 |
14
|
|
2047 |
15
|
|
2048 |
16
|
|
2049 |
17
|
|
2050 |
18
|
|
2051 |
19
|
|
2052 |
20
|
|
2053 |
21
|
|
2054 |
22
|
|
2055 |
23
|
|
2056 |
24
|
|
2057 |
25
|
|
2058 |
26
|
|
2059 |
27
|
|
2060 |
28
|
|
2061 |
29
|
|
2062 |
30
|
|
2063 |
31
|
|
2064 |
32
|
|
2065 |
33
|
|
2066 |
34
|
|
2067 |
35
|
|
2068 |
36
|
|
2069 |
37
|
|
2070 |
38
|
|
2071 |
39
|
|
2072 |
40
|
|
2073 |
41
|
|
2074 |
42
|
|
2075 |
43
|
|
2076 |
44
|
|
2077 |
45
|
|
2078 |
46
|
|
2079 |
47
|
|
2080 |
48
|
|
2081 |
49
|
|
2082 |
50
|
|
2083 |
51
|
|
2084 |
52
|
|
2085 |
53
|
|
2086 |
54
|
|
2087 |
55
|
|
2088 |
56
|
|
2089 |
57
|
|
2090 |
58
|
|
2091 |
59
|
|
2092 |
60
|
|
2093 |
61
|
|
2094 |
62
|
|
2095 |
63
|
|
2096 |
64
|
|
2097 |
65
|
|
2098 |
66
|
|
2099 |
67
|
|
2100 |
68
|
|
2101 |
69
|
|
2102 |
70
|
|
2103 |
71
|
|
2104 |
72
|
|
2105 |
73
|
|
2106 |
74
|
|
2107 |
75
|
|
2108 |
76
|
|
2109 |
77
|
|
2110 |
78
|
|
2111 |
79
|
|
2112 |
80
|
|
2113 |
81
|
|
2114 |
82
|
|
2115 |
83
|
|
2116 |
84
|
|
2117 |
85
|
|
2118 |
86
|
|
2119 |
87
|
|
2120 |
88
|
|
2121 |
89
|
|
2122 |
90
|
|
2123 |
91
|
|
2124 |
92
|
|
2125 |
93
|
|
2126 |
94
|
|
2127 |
95
|
|
2128 |
96
|
|
2129 |
97
|
|
2130 |
98
|
|
2131 |
99
|
|
2132 |
100
|
|
2133 |
101
|
|
2134 |
102
|
|
2135 |
103
|
|
2136 |
104
|
|
2137 |
105
|
|
2138 |
106
|
|
2139 |
107
|
|
2140 |
108
|
|
2141 |
109
|
|
2142 |
110
|
|
2143 |
111
|
|
2144 |
112
|
|
2145 |
113
|
|
2146 |
114
|
|
2147 |
115
|
|
2148 |
116
|
|
2149 |
117
|
|
2150 |
118
|
|
2151 |
119
|
|
2152 |
120
|
|
2153 |
121
|
|
2154 |
122
|
|
2155 |
123
|
|
2156 |
124
|
|
2157 |
125
|
|
2158 |
126
|
|
2159 |
127
|
|
2160 |
128
|
|
2161 |
SELECT @tmp_tables_after = @tmp_tables_before ; |
|
2162 |
@tmp_tables_after = @tmp_tables_before |
|
|
500
by Brian Aker
Re-enabled more tests. |
2163 |
NULL
|
|
1
by brian
clean slate |
2164 |
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a; |
2165 |
a
|
|
2166 |
1
|
|
2167 |
2
|
|
2168 |
3
|
|
2169 |
4
|
|
2170 |
5
|
|
2171 |
6
|
|
2172 |
7
|
|
2173 |
8
|
|
2174 |
9
|
|
2175 |
10
|
|
2176 |
11
|
|
2177 |
12
|
|
2178 |
13
|
|
2179 |
14
|
|
2180 |
15
|
|
2181 |
16
|
|
2182 |
17
|
|
2183 |
18
|
|
2184 |
19
|
|
2185 |
20
|
|
2186 |
21
|
|
2187 |
22
|
|
2188 |
23
|
|
2189 |
24
|
|
2190 |
25
|
|
2191 |
26
|
|
2192 |
27
|
|
2193 |
28
|
|
2194 |
29
|
|
2195 |
30
|
|
2196 |
31
|
|
2197 |
32
|
|
2198 |
33
|
|
2199 |
34
|
|
2200 |
35
|
|
2201 |
36
|
|
2202 |
37
|
|
2203 |
38
|
|
2204 |
39
|
|
2205 |
40
|
|
2206 |
41
|
|
2207 |
42
|
|
2208 |
43
|
|
2209 |
44
|
|
2210 |
45
|
|
2211 |
46
|
|
2212 |
47
|
|
2213 |
48
|
|
2214 |
49
|
|
2215 |
50
|
|
2216 |
51
|
|
2217 |
52
|
|
2218 |
53
|
|
2219 |
54
|
|
2220 |
55
|
|
2221 |
56
|
|
2222 |
57
|
|
2223 |
58
|
|
2224 |
59
|
|
2225 |
60
|
|
2226 |
61
|
|
2227 |
62
|
|
2228 |
63
|
|
2229 |
64
|
|
2230 |
65
|
|
2231 |
66
|
|
2232 |
67
|
|
2233 |
68
|
|
2234 |
69
|
|
2235 |
70
|
|
2236 |
71
|
|
2237 |
72
|
|
2238 |
73
|
|
2239 |
74
|
|
2240 |
75
|
|
2241 |
76
|
|
2242 |
77
|
|
2243 |
78
|
|
2244 |
79
|
|
2245 |
80
|
|
2246 |
81
|
|
2247 |
82
|
|
2248 |
83
|
|
2249 |
84
|
|
2250 |
85
|
|
2251 |
86
|
|
2252 |
87
|
|
2253 |
88
|
|
2254 |
89
|
|
2255 |
90
|
|
2256 |
91
|
|
2257 |
92
|
|
2258 |
93
|
|
2259 |
94
|
|
2260 |
95
|
|
2261 |
96
|
|
2262 |
97
|
|
2263 |
98
|
|
2264 |
99
|
|
2265 |
100
|
|
2266 |
101
|
|
2267 |
102
|
|
2268 |
103
|
|
2269 |
104
|
|
2270 |
105
|
|
2271 |
106
|
|
2272 |
107
|
|
2273 |
108
|
|
2274 |
109
|
|
2275 |
110
|
|
2276 |
111
|
|
2277 |
112
|
|
2278 |
113
|
|
2279 |
114
|
|
2280 |
115
|
|
2281 |
116
|
|
2282 |
117
|
|
2283 |
118
|
|
2284 |
119
|
|
2285 |
120
|
|
2286 |
121
|
|
2287 |
122
|
|
2288 |
123
|
|
2289 |
124
|
|
2290 |
125
|
|
2291 |
126
|
|
2292 |
127
|
|
2293 |
128
|
|
2294 |
SELECT @tmp_tables_after = @tmp_tables_before; |
|
2295 |
@tmp_tables_after = @tmp_tables_before |
|
|
500
by Brian Aker
Re-enabled more tests. |
2296 |
NULL
|
|
1
by brian
clean slate |
2297 |
DROP TABLE t1; |
2298 |
#
|
|
2299 |
# Bug#31590: Wrong error message on sort buffer being too small.
|
|
2300 |
#
|
|
|
500
by Brian Aker
Re-enabled more tests. |
2301 |
create table t1(a int, b text); |
|
1
by brian
clean slate |
2302 |
insert into t1 values (1,2),(3,2); |
2303 |
set session sort_buffer_size= 30000; |
|
2304 |
Warnings: |
|
|
910.4.10
by Stewart Smith
fix system variables for correct endian architectures. |
2305 |
Error 1292 Truncated incorrect sort_buffer_size value: '30000' |
|
1
by brian
clean slate |
2306 |
set session max_sort_length= 2180; |
2307 |
select * from t1 order by b; |
|
|
500
by Brian Aker
Re-enabled more tests. |
2308 |
a b |
2309 |
1 2 |
|
2310 |
3 2 |
|
|
1
by brian
clean slate |
2311 |
drop table t1; |
|
500
by Brian Aker
Re-enabled more tests. |
2312 |
CREATE TABLE t2 (a varchar(32), b int, c float, d double, |
|
1
by brian
clean slate |
2313 |
UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c)); |
2314 |
CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b)); |
|
2315 |
CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b)); |
|
2316 |
INSERT INTO t3 SELECT * FROM t1; |
|
2317 |
SELECT d FROM t1, t2 |
|
2318 |
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' |
|
2319 |
ORDER BY t2.c LIMIT 1; |
|
2320 |
d
|
|
2321 |
52.5
|
|
2322 |
SELECT d FROM t3 AS t1, t2 AS t2 |
|
2323 |
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' |
|
2324 |
ORDER BY t2.c LIMIT 1; |
|
2325 |
d
|
|
2326 |
52.5
|
|
2327 |
DROP TABLE t1,t2,t3; |