1
show variables like 'optimizer_switch';
4
set optimizer_switch='no_semijoin';
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
9
explain extended select (select 2);
10
id select_type table type possible_keys key key_len ref rows filtered Extra
11
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
13
Note 1249 Select 2 was reduced during optimization
14
Note 1003 select 2 AS `(select 2)`
15
SELECT (SELECT 1) UNION SELECT (SELECT 2);
19
explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2);
20
id select_type table type possible_keys key key_len ref rows filtered Extra
21
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
22
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
23
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
25
Note 1249 Select 2 was reduced during optimization
26
Note 1249 Select 4 was reduced during optimization
27
Note 1003 select 1 AS `(SELECT 1)` union select 2 AS `(SELECT 2)`
28
SELECT (SELECT (SELECT 0 UNION SELECT 0));
29
(SELECT (SELECT 0 UNION SELECT 0))
31
explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0));
32
id select_type table type possible_keys key key_len ref rows filtered Extra
33
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
34
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
35
4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
36
NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL NULL
38
Note 1249 Select 2 was reduced during optimization
39
Note 1003 select (select 0 AS `0` union select 0 AS `0`) AS `(SELECT (SELECT 0 UNION SELECT 0))`
40
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
41
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
42
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) as c HAVING a=1) as b;
43
ERROR 42S22: Reference 'b' not supported (forward reference in item list)
44
SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
47
SELECT (SELECT a) as a;
48
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
49
EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
50
id select_type table type possible_keys key key_len ref rows filtered Extra
51
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00
52
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
53
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
55
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
56
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
57
Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1)
58
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
62
ERROR 42S22: Unknown column 'a' in 'field list'
63
SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
66
SELECT 1 FROM (SELECT (SELECT a) b) c;
67
ERROR 42S22: Unknown column 'a' in 'field list'
68
SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id);
71
SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1);
72
ERROR 21000: Operand should contain 1 column(s)
73
SELECT 1 IN (SELECT 1);
76
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
79
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
80
ERROR 42S22: Unknown column 'a' in 'field list'
81
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
82
ERROR 42S22: Unknown column 'a' in 'field list'
83
SELECT (SELECT 1,2,3) = ROW(1,2,3);
84
(SELECT 1,2,3) = ROW(1,2,3)
86
SELECT (SELECT 1,2,3) = ROW(1,2,1);
87
(SELECT 1,2,3) = ROW(1,2,1)
89
SELECT (SELECT 1,2,3) < ROW(1,2,1);
90
(SELECT 1,2,3) < ROW(1,2,1)
92
SELECT (SELECT 1,2,3) > ROW(1,2,1);
93
(SELECT 1,2,3) > ROW(1,2,1)
95
SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
96
(SELECT 1,2,3) = ROW(1,2,NULL)
98
SELECT ROW(1,2,3) = (SELECT 1,2,3);
99
ROW(1,2,3) = (SELECT 1,2,3)
101
SELECT ROW(1,2,3) = (SELECT 1,2,1);
102
ROW(1,2,3) = (SELECT 1,2,1)
104
SELECT ROW(1,2,3) < (SELECT 1,2,1);
105
ROW(1,2,3) < (SELECT 1,2,1)
107
SELECT ROW(1,2,3) > (SELECT 1,2,1);
108
ROW(1,2,3) > (SELECT 1,2,1)
110
SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
111
ROW(1,2,3) = (SELECT 1,2,NULL)
113
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
114
(SELECT 1.5,2,'a') = ROW(1.5,2,'a')
116
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
117
(SELECT 1.5,2,'a') = ROW(1.5,2,'b')
119
SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b');
120
(SELECT 1.5,2,'a') = ROW('1.5b',2,'b')
123
Warning 1292 Truncated incorrect DOUBLE value: '1.5b'
124
SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
125
(SELECT 'b',2,'a') = ROW(1.5,2,'a')
127
SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
128
(SELECT 1.5,2,'a') = ROW(1.5,'2','a')
130
SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
131
(SELECT 1.5,'c','a') = ROW(1.5,2,'a')
133
SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
134
ERROR 21000: Operand should contain 1 column(s)
135
SELECT 1 as a,(SELECT a+a) b,(SELECT b);
138
create table t1 (a int) ENGINE=MyISAM;
139
create table t2 (a int, b int) ENGINE=MyISAM;
140
create table t3 (a int) ENGINE=MyISAM;
141
create table t4 (a int not null, b int not null) ENGINE=MyISAM;
142
insert into t1 values (2);
143
insert into t2 values (1,7),(2,7);
144
insert into t4 values (4,8),(3,8),(5,9);
145
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
146
ERROR 42S22: Reference 'a1' not supported (forward reference in item list)
147
select (select a from t1 where t1.a=t2.a), a from t2;
148
(select a from t1 where t1.a=t2.a) a
151
select (select a from t1 where t1.a=t2.b), a from t2;
152
(select a from t1 where t1.a=t2.b) a
155
select (select a from t1), a, (select 1 union select 2 limit 1) from t2;
156
(select a from t1) a (select 1 union select 2 limit 1)
159
select (select a from t3), a from t2;
163
select * from t2 where t2.a=(select a from t1);
166
insert into t3 values (6),(7),(3);
167
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
171
(select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 order by a limit 2) limit 3;
176
(select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
182
explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
183
id select_type table type possible_keys key key_len ref rows filtered Extra
184
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
185
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using filesort
186
3 UNION t4 ALL NULL NULL NULL NULL 3 100.00 Using where
187
4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
188
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
190
Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` AS `a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) AS `max(t2.a)*4` from `test`.`t2`)) order by `a`)
191
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
192
(select a from t3 where a<t2.a*4 order by 1 desc limit 1) a
195
select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
196
(select * from t2 where a>1) as tt;
197
(select t3.a from t3 where a<8 order by 1 desc limit 1) a
199
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
200
(select * from t2 where a>1) as tt;
201
id select_type table type possible_keys key key_len ref rows filtered Extra
202
1 PRIMARY <derived3> system NULL NULL NULL NULL 1 100.00
203
3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
204
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
206
Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
207
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
210
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1);
213
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1);
215
select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
216
b (select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)
220
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
221
id select_type table type possible_keys key key_len ref rows filtered Extra
222
1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00
223
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
224
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
226
Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
227
Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
228
select * from t3 where exists (select * from t2 where t2.b=t3.a);
231
select * from t3 where not exists (select * from t2 where t2.b=t3.a);
235
select * from t3 where a in (select b from t2);
238
select * from t3 where a not in (select b from t2);
242
select * from t3 where a = some (select b from t2);
245
select * from t3 where a <> any (select b from t2);
249
select * from t3 where a = all (select b from t2);
252
select * from t3 where a <> all (select b from t2);
256
insert into t2 values (100, 5);
257
select * from t3 where a < any (select b from t2);
261
select * from t3 where a < all (select b from t2);
264
select * from t3 where a >= any (select b from t2);
268
explain extended select * from t3 where a >= any (select b from t2);
269
id select_type table type possible_keys key key_len ref rows filtered Extra
270
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
271
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00
273
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
274
select * from t3 where a >= all (select b from t2);
277
delete from t2 where a=100;
278
select * from t3 where a in (select a,b from t2);
279
ERROR 21000: Operand should contain 1 column(s)
280
select * from t3 where a in (select * from t2);
281
ERROR 21000: Operand should contain 1 column(s)
282
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
283
select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);
285
insert into t2 values (2,10);
286
select b,max(a) as ma from t4 group by b having ma < (select max(t2.a) from t2 where t2.b=t4.b);
289
delete from t2 where a=2 and b=10;
290
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);
293
create table t5 (a int) ENGINE=MyISAM;
294
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
295
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
298
insert into t5 values (5);
299
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
300
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
303
insert into t5 values (2);
304
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
305
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
308
explain extended select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
309
id select_type table type possible_keys key key_len ref rows filtered Extra
310
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
311
2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
312
3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 100.00 Using where
313
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
315
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
316
Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
317
Note 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
318
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
319
ERROR 21000: Subquery returns more than 1 row
320
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)) ENGINE=MyISAM;
321
create table t7( uq int primary key, name char(25)) ENGINE=MyISAM;
322
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
323
insert into t6 values (1,1),(1,2),(2,2),(1,3);
324
select * from t6 where exists (select * from t7 where uq = clinic_uq);
329
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
330
id select_type table type possible_keys key key_len ref rows filtered Extra
331
1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where
332
2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
334
Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
335
Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
336
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
337
ERROR 23000: Column 'a' in field list is ambiguous
339
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0') ENGINE=MyISAM;
340
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
341
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0') ENGINE=MyISAM;
342
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
343
CREATE TABLE t1 (a varchar(20),b date NULL) ENGINE=MyISAM;
344
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
345
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
348
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
351
SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);
355
`pseudo` varchar(35) NOT NULL default '',
356
`email` varchar(60) NOT NULL default '',
357
PRIMARY KEY (`pseudo`),
358
UNIQUE KEY `email` (`email`)
359
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
360
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
361
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
362
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
363
EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
364
id select_type table type possible_keys key key_len ref rows filtered Extra
365
1 PRIMARY t8 const PRIMARY PRIMARY 142 const 1 100.00 Using index
366
4 SUBQUERY t8 const PRIMARY PRIMARY 142 1 100.00 Using index
367
2 SUBQUERY t8 const PRIMARY PRIMARY 142 const 1 100.00
368
3 SUBQUERY t8 const PRIMARY PRIMARY 142 1 100.00 Using index
370
Note 1003 select 'joce' AS `pseudo`,(select 'test' AS `email` from `test`.`t8` where ('joce' = (select 'joce' AS `pseudo` from `test`.`t8` where ('joce' = 'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where ('joce' = (select 'joce' AS `pseudo` from `test`.`t8` where ('joce' = 'joce')))
371
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
372
t8 WHERE pseudo='joce');
373
ERROR 21000: Operand should contain 1 column(s)
374
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
376
ERROR 21000: Operand should contain 1 column(s)
377
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
380
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
381
ERROR 21000: Subquery returns more than 1 row
382
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
384
`topic` bigint NOT NULL default '0',
386
`pseudo` varchar(35) NOT NULL default '',
387
PRIMARY KEY (`pseudo`,`date`,`topic`),
388
KEY `topic` (`topic`)
389
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
390
INSERT INTO t1 (topic,date,pseudo) VALUES
391
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
392
EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
393
id select_type table type possible_keys key key_len ref rows filtered Extra
394
1 SIMPLE t1 index NULL PRIMARY 153 NULL 2 100.00 Using where; Using index
396
Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03')
397
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
398
id select_type table type possible_keys key key_len ref rows filtered Extra
399
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
400
2 SUBQUERY t1 index NULL PRIMARY 153 NULL 2 100.00 Using where; Using index
402
Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
403
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
406
SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
407
(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')
409
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
414
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
415
ERROR 21000: Subquery returns more than 1 row
416
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
417
id select_type table type possible_keys key key_len ref rows filtered Extra
418
1 PRIMARY t1 index NULL topic 8 NULL 2 100.00 Using index
419
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
420
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
421
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
423
Note 1003 select 1 AS `1` from `test`.`t1` where 1
426
`numeropost` bigint NOT NULL auto_increment,
427
`maxnumrep` int NOT NULL default '0',
428
PRIMARY KEY (`numeropost`),
429
UNIQUE KEY `maxnumrep` (`maxnumrep`)
430
) ENGINE=MyISAM ROW_FORMAT=FIXED;
431
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
433
`mot` varchar(30) NOT NULL default '',
434
`topic` bigint NOT NULL default '0',
436
`pseudo` varchar(35) NOT NULL default '',
437
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`)
438
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
439
INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
440
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
443
SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND date >= '2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20;
447
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
448
ERROR 42S22: Unknown column 'a' in 'having clause'
449
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
450
ERROR 42S22: Unknown column 'a' in 'having clause'
451
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
452
mot topic date pseudo
453
joce 40143 2002-10-22 joce
454
joce 43506 2002-10-22 joce
455
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
456
mot topic date pseudo
457
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
458
mot topic date pseudo
459
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
460
mot topic date pseudo
461
joce 40143 2002-10-22 joce
462
joce 43506 2002-10-22 joce
463
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
464
mot topic date pseudo
465
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
466
mot topic date pseudo
467
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
468
mot topic date pseudo
469
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
470
mot topic date pseudo
471
joce 40143 2002-10-22 joce
472
joce 43506 2002-10-22 joce
473
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
474
mot topic date pseudo topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100)
475
joce 40143 2002-10-22 joce 1
476
joce 43506 2002-10-22 joce 1
477
SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);
478
mot topic date pseudo
479
SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
480
mot topic date pseudo
481
joce 40143 2002-10-22 joce
482
joce 43506 2002-10-22 joce
483
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
484
mot topic date pseudo
485
joce 40143 2002-10-22 joce
486
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
487
mot topic date pseudo
488
joce 40143 2002-10-22 joce
489
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
490
mot topic date pseudo
491
joce 40143 2002-10-22 joce
492
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;
493
mot topic date pseudo topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000)
494
joce 40143 2002-10-22 joce 1
495
joce 43506 2002-10-22 joce 0
498
`numeropost` bigint NOT NULL auto_increment,
499
`maxnumrep` int NOT NULL default '0',
500
PRIMARY KEY (`numeropost`),
501
UNIQUE KEY `maxnumrep` (`maxnumrep`)
502
) ENGINE=MyISAM ROW_FORMAT=FIXED;
503
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
504
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
505
ERROR 21000: Subquery returns more than 1 row
506
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
507
ERROR 21000: Subquery returns more than 1 row
509
create table t1 (a int) ENGINE=MyISAM;
510
insert into t1 values (1),(2),(3);
511
(select * from t1) union (select * from t1) order by (select a from t1 limit 1);
517
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b') ENGINE=MyISAM;
518
INSERT INTO t1 VALUES ();
519
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
520
ERROR 21000: Subquery returns more than 1 row
523
`numeropost` bigint NOT NULL default '0',
524
`numreponse` int NOT NULL auto_increment,
525
`pseudo` varchar(35) NOT NULL default '',
526
PRIMARY KEY (`numeropost`,`numreponse`),
527
UNIQUE KEY `numreponse` (`numreponse`),
528
KEY `pseudo` (`pseudo`,`numeropost`)
530
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
531
ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list)
532
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
533
ERROR 42S22: Unknown column 'a' in 'having clause'
534
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
535
numreponse (SELECT numeropost FROM t1 HAVING numreponse=1)
536
INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
537
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
538
ERROR 21000: Subquery returns more than 1 row
539
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
540
id select_type table type possible_keys key key_len ref rows filtered Extra
541
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
543
Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)
544
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
545
id select_type table type possible_keys key key_len ref rows filtered Extra
546
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 12 const,const 1 100.00 Using index
547
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
549
Note 1003 select '3' AS `numreponse` from `test`.`t1` where 1
551
CREATE TABLE t1 (a int) ENGINE=MyISAM;
552
INSERT INTO t1 VALUES (1);
553
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
557
create table t1 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
558
create table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
559
insert into t1 values (0, 10),(1, 11),(2, 12);
560
insert into t2 values (1, 21),(2, 22),(3, 23);
566
update t1 set b= (select b from t1);
567
ERROR HY000: You can't specify target table 't1' for update in FROM clause
568
update t1 set b= (select b from t2);
569
ERROR 21000: Subquery returns more than 1 row
570
update t1 set b= (select b from t2 where t1.a = t2.a);
577
create table t1 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
578
create table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
579
insert into t1 values (0, 10),(1, 11),(2, 12);
580
insert into t2 values (1, 21),(2, 12),(3, 23);
586
select * from t1 where b = (select b from t2 where t1.a = t2.a);
589
delete from t1 where b = (select b from t1);
590
ERROR HY000: You can't specify target table 't1' for update in FROM clause
591
delete from t1 where b = (select b from t2);
592
ERROR 21000: Subquery returns more than 1 row
593
delete from t1 where b = (select b from t2 where t1.a = t2.a);
599
create table t11 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
600
create table t12 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
601
create table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
602
insert into t11 values (0, 10),(1, 11),(2, 12);
603
insert into t12 values (33, 10),(22, 11),(2, 12);
604
insert into t2 values (1, 21),(2, 12),(3, 23);
615
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
616
ERROR HY000: You can't specify target table 't12' for update in FROM clause
617
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
618
ERROR 21000: Subquery returns more than 1 row
619
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
628
drop table t11, t12, t2;
629
CREATE TABLE t1 (x int) ENGINE=MyISAM;
630
create table t2 (a int) ENGINE=MyISAM;
631
create table t3 (b int) ENGINE=MyISAM;
632
insert into t2 values (1);
633
insert into t3 values (1),(2);
634
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
635
ERROR HY000: You can't specify target table 't1' for update in FROM clause
636
INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));
637
ERROR 21000: Subquery returns more than 1 row
638
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
642
insert into t2 values (1);
643
INSERT INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
648
INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;
655
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
664
INSERT INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
665
ERROR 42S22: Unknown column 'x' in 'field list'
666
INSERT INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
676
drop table t1, t2, t3;
677
CREATE TABLE t1 (x int not null, y int, primary key (x)) ENGINE=MyISAM;
678
create table t2 (a int) ENGINE=MyISAM;
679
create table t3 (a int) ENGINE=MyISAM;
680
insert into t2 values (1);
681
insert into t3 values (1),(2);
684
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
685
ERROR HY000: You can't specify target table 't1' for update in FROM clause
686
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
687
ERROR 21000: Subquery returns more than 1 row
688
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
692
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
696
replace into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
701
replace into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
706
replace into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
712
drop table t1, t2, t3;
713
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
714
ERROR HY000: No tables used
715
CREATE TABLE t2 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
716
INSERT INTO t2 VALUES (1),(2);
717
SELECT * FROM t2 WHERE id IN (SELECT 1);
720
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
721
id select_type table type possible_keys key key_len ref rows filtered Extra
722
1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
724
Note 1249 Select 2 was reduced during optimization
725
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)
726
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
729
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
732
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
733
id select_type table type possible_keys key key_len ref rows filtered Extra
734
1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
736
Note 1249 Select 3 was reduced during optimization
737
Note 1249 Select 2 was reduced during optimization
738
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
739
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
740
id select_type table type possible_keys key key_len ref rows filtered Extra
741
1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
742
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
743
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
744
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
746
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))
747
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
749
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
752
INSERT INTO t2 VALUES ((SELECT * FROM t2));
753
ERROR HY000: You can't specify target table 't2' for update in FROM clause
754
INSERT INTO t2 VALUES ((SELECT id FROM t2));
755
ERROR HY000: You can't specify target table 't2' for update in FROM clause
760
CREATE TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
761
INSERT INTO t1 values (1),(1);
762
UPDATE t2 SET id=(SELECT * FROM t1);
763
ERROR 21000: Subquery returns more than 1 row
765
create table t1 (a int) ENGINE=MyISAM;
766
insert into t1 values (1),(2),(3);
767
select 1 IN (SELECT * from t1);
768
1 IN (SELECT * from t1)
770
select 10 IN (SELECT * from t1);
771
10 IN (SELECT * from t1)
773
select NULL IN (SELECT * from t1);
774
NULL IN (SELECT * from t1)
776
update t1 set a=NULL where a=2;
777
select 1 IN (SELECT * from t1);
778
1 IN (SELECT * from t1)
780
select 3 IN (SELECT * from t1);
781
3 IN (SELECT * from t1)
783
select 10 IN (SELECT * from t1);
784
10 IN (SELECT * from t1)
786
select 1 > ALL (SELECT * from t1);
787
1 > ALL (SELECT * from t1)
789
select 10 > ALL (SELECT * from t1);
790
10 > ALL (SELECT * from t1)
792
select 1 > ANY (SELECT * from t1);
793
1 > ANY (SELECT * from t1)
795
select 10 > ANY (SELECT * from t1);
796
10 > ANY (SELECT * from t1)
799
create table t1 (a varchar(20)) ENGINE=MyISAM;
800
insert into t1 values ('A'),('BC'),('DEF');
801
select 'A' IN (SELECT * from t1);
802
'A' IN (SELECT * from t1)
804
select 'XYZS' IN (SELECT * from t1);
805
'XYZS' IN (SELECT * from t1)
807
select NULL IN (SELECT * from t1);
808
NULL IN (SELECT * from t1)
810
update t1 set a=NULL where a='BC';
811
select 'A' IN (SELECT * from t1);
812
'A' IN (SELECT * from t1)
814
select 'DEF' IN (SELECT * from t1);
815
'DEF' IN (SELECT * from t1)
817
select 'XYZS' IN (SELECT * from t1);
818
'XYZS' IN (SELECT * from t1)
820
select 'A' > ALL (SELECT * from t1);
821
'A' > ALL (SELECT * from t1)
823
select 'XYZS' > ALL (SELECT * from t1);
824
'XYZS' > ALL (SELECT * from t1)
826
select 'A' > ANY (SELECT * from t1);
827
'A' > ANY (SELECT * from t1)
829
select 'XYZS' > ANY (SELECT * from t1);
830
'XYZS' > ANY (SELECT * from t1)
833
create table t1 (a float) ENGINE=MyISAM;
834
insert into t1 values (1.5),(2.5),(3.5);
835
select 1.5 IN (SELECT * from t1);
836
1.5 IN (SELECT * from t1)
838
select 10.5 IN (SELECT * from t1);
839
10.5 IN (SELECT * from t1)
841
select NULL IN (SELECT * from t1);
842
NULL IN (SELECT * from t1)
844
update t1 set a=NULL where a=2.5;
845
select 1.5 IN (SELECT * from t1);
846
1.5 IN (SELECT * from t1)
848
select 3.5 IN (SELECT * from t1);
849
3.5 IN (SELECT * from t1)
851
select 10.5 IN (SELECT * from t1);
852
10.5 IN (SELECT * from t1)
854
select 1.5 > ALL (SELECT * from t1);
855
1.5 > ALL (SELECT * from t1)
857
select 10.5 > ALL (SELECT * from t1);
858
10.5 > ALL (SELECT * from t1)
860
select 1.5 > ANY (SELECT * from t1);
861
1.5 > ANY (SELECT * from t1)
863
select 10.5 > ANY (SELECT * from t1);
864
10.5 > ANY (SELECT * from t1)
866
explain extended select (select a+1) from t1;
867
id select_type table type possible_keys key key_len ref rows filtered Extra
868
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
870
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
871
Note 1249 Select 2 was reduced during optimization
872
Note 1003 select (`test`.`t1`.`a` + 1) AS `(select a+1)` from `test`.`t1`
873
select (select a+1) from t1;
879
CREATE TABLE t1 (a int NOT NULL default '0', PRIMARY KEY (a)) ENGINE=MyISAM;
880
CREATE TABLE t2 (a int default '0', INDEX (a)) ENGINE=MyISAM;
881
INSERT INTO t1 VALUES (1),(2),(3),(4);
882
INSERT INTO t2 VALUES (1),(2),(3);
883
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
884
a t1.a in (select t2.a from t2)
889
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
890
id select_type table type possible_keys key key_len ref rows filtered Extra
891
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
892
2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
894
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
895
CREATE TABLE t3 (a int default '0') ENGINE=MyISAM;
896
INSERT INTO t3 VALUES (1),(2),(3);
897
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
898
a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
903
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
904
id select_type table type possible_keys key key_len ref rows filtered Extra
905
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
906
2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
907
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
909
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
911
CREATE TABLE t1 (a int) ENGINE=MyISAM;
912
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
913
id select_type table type possible_keys key key_len ref rows filtered Extra
914
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
915
2 SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
917
Note 1003 select (select rand() AS `RAND()` from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1`
918
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
919
id select_type table type possible_keys key key_len ref rows filtered Extra
920
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
921
2 SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
923
Note 1003 select (select benchmark(1,1) AS `BENCHMARK(1,1)` from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1`
926
`mot` varchar(30) NOT NULL default '',
927
`topic` bigint NOT NULL default '0',
928
`date` date NULL DEFAULT '2009-01-20',
929
`pseudo` varchar(35) NOT NULL default '',
930
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
931
KEY `pseudo` (`pseudo`,`date`,`topic`),
932
KEY `topic` (`topic`)
933
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
935
`mot` varchar(30) NOT NULL default '',
936
`topic` bigint NOT NULL default '0',
937
`date` date NULL default '1997-08-29',
938
`pseudo` varchar(35) NOT NULL default '',
939
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
940
KEY `pseudo` (`pseudo`,`date`,`topic`),
941
KEY `topic` (`topic`)
942
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
944
`numeropost` bigint NOT NULL auto_increment,
945
`maxnumrep` int NOT NULL default '0',
946
PRIMARY KEY (`numeropost`),
947
UNIQUE KEY `maxnumrep` (`maxnumrep`)
949
INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
950
INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
951
INSERT INTO t3 VALUES (1,1);
952
SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
957
mot topic date pseudo
958
joce 1 2009-01-20 joce
959
test 2 2009-01-20 test
960
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
961
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
963
mot topic date pseudo
964
joce 1 2009-01-20 joce
965
drop table t1, t2, t3;
966
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
969
CREATE TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
970
SHOW CREATE TABLE t1;
972
t1 CREATE TABLE `t1` (
973
`a` int NOT NULL DEFAULT '0',
974
`(SELECT 1)` int NOT NULL DEFAULT '0'
977
CREATE TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
978
SHOW CREATE TABLE t1;
980
t1 CREATE TABLE `t1` (
981
`a` int NOT NULL DEFAULT '0',
982
`(SELECT a)` int NOT NULL DEFAULT '0'
985
CREATE TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
986
SHOW CREATE TABLE t1;
988
t1 CREATE TABLE `t1` (
989
`a` int NOT NULL DEFAULT '0',
990
`(SELECT a+0)` int NOT NULL DEFAULT '0'
993
CREATE TABLE t1 ENGINE=MyISAM SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
997
SHOW CREATE TABLE t1;
999
t1 CREATE TABLE `t1` (
1003
create table t1 (a int) ENGINE=MyISAM;
1004
insert into t1 values (1), (2), (3);
1005
explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1)
1007
id select_type table type possible_keys key key_len ref rows filtered Extra
1008
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1009
2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
1010
3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
1012
Note 1003 select `test`.`t1`.`a` AS `a`,(select (select rand() AS `rand()` from `test`.`t1` limit 1) AS `(select rand() from t1 limit 1)` from `test`.`t1` limit 1) AS `(select (select rand() from t1 limit 1) from t1 limit 1)` from `test`.`t1`
1014
select t1.Continent, t2.Name, t2.Population from t1 LEFT JOIN t2 ON t1.Code = t2.Country where t2.Population IN (select max(t2.Population) AS Population from t2, t1 where t2.Country = t1.Code group by Continent);
1015
ERROR 42S02: Table 'test.t1' doesn't exist
1017
ID int NOT NULL auto_increment,
1018
name char(35) NOT NULL default '',
1019
t2 char(3) NOT NULL default '',
1020
District char(20) NOT NULL default '',
1021
Population int NOT NULL default '0',
1024
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
1025
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
1026
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
1028
Code char(3) NOT NULL default '',
1029
Name char(52) NOT NULL default '',
1030
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
1031
Region char(26) NOT NULL default '',
1032
SurfaceArea float(10,2) NOT NULL default '0.00',
1033
IndepYear int default NULL,
1034
Population int NOT NULL default '0',
1035
LifeExpectancy float(3,1) default NULL,
1036
GNP float(10,2) default NULL,
1037
GNPOld float(10,2) default NULL,
1038
LocalName char(45) NOT NULL default '',
1039
GovernmentForm char(45) NOT NULL default '',
1040
HeadOfState char(60) default NULL,
1041
Capital int default NULL,
1042
Code2 char(2) NOT NULL default '',
1045
INSERT INTO t2 VALUES ('AUS','Australia','Oceania','Australia and New Zealand',7741220.00,1901,18886000,79.8,351182.00,392911.00,'Australia','Constitutional Monarchy, Federation','Elisabeth II',135,'AU');
1046
INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ');
1047
select t2.Continent, t1.Name, t1.Population from t2 LEFT JOIN t1 ON t2.Code = t1.t2 where t1.Population IN (select max(t1.Population) AS Population from t1, t2 where t1.t2 = t2.Code group by Continent);
1048
Continent Name Population
1049
Oceania Sydney 3276207
1052
`id` bigint NOT NULL auto_increment,
1053
`pseudo` varchar(35) NOT NULL default '',
1055
UNIQUE KEY `pseudo` (`pseudo`)
1056
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
1057
INSERT INTO t1 (pseudo) VALUES ('test');
1058
SELECT 0 IN (SELECT 1 FROM t1 a);
1059
0 IN (SELECT 1 FROM t1 a)
1061
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
1062
id select_type table type possible_keys key key_len ref rows filtered Extra
1063
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1064
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1066
Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
1067
INSERT INTO t1 (pseudo) VALUES ('test1');
1068
SELECT 0 IN (SELECT 1 FROM t1 a);
1069
0 IN (SELECT 1 FROM t1 a)
1071
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
1072
id select_type table type possible_keys key key_len ref rows filtered Extra
1073
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1074
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1076
Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
1079
`i` int NOT NULL default '0',
1082
INSERT INTO t1 VALUES (1);
1083
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
1084
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
1085
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
1086
ERROR 42S22: Unknown column 't.i' in 'field list'
1094
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
1096
id int default NULL,
1097
name varchar(15) default NULL
1099
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
1100
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
1108
create table t1 (a int, unique index indexa (a)) ENGINE=MyISAM;
1109
insert into t1 values (-1), (-4), (-2), (NULL);
1110
select -10 IN (select a from t1 FORCE INDEX (indexa));
1111
-10 IN (select a from t1 FORCE INDEX (indexa))
1114
create table t1 (id int not null auto_increment primary key, salary int, key(salary)) ENGINE=MyISAM;
1115
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
1116
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
1117
id select_type table type possible_keys key key_len ref rows filtered Extra
1118
1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where
1119
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
1121
Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) AS `MAX(salary)` from `test`.`t1`))
1124
ID int NOT NULL auto_increment,
1125
SUB_ID int NOT NULL default '0',
1126
REF_ID int default NULL,
1127
REF_SUB int default '0',
1128
PRIMARY KEY (ID,SUB_ID),
1129
UNIQUE KEY t1_PK (ID,SUB_ID),
1130
KEY t1_FK (REF_ID,REF_SUB),
1131
KEY t1_REFID (REF_ID)
1133
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
1134
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
1137
create table t1 (a int, b int) ENGINE=MyISAM;
1138
create table t2 (a int, b int) ENGINE=MyISAM;
1139
insert into t1 values (1,0), (2,0), (3,0);
1140
insert into t2 values (1,1), (2,1), (3,1), (2,2);
1141
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
1143
Error 1242 Subquery returns more than 1 row
1151
`id` bigint NOT NULL auto_increment,
1152
`pseudo` varchar(35) NOT NULL default '',
1153
`email` varchar(60) NOT NULL default '',
1155
UNIQUE KEY `email` (`email`),
1156
UNIQUE KEY `pseudo` (`pseudo`)
1157
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
1158
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
1159
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
1163
drop table if exists t1;
1164
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
1167
create table t1 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
1168
create table t2 (a int not null, primary key (a)) ENGINE=MyISAM;
1169
create table t3 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
1170
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1171
insert into t2 values (2), (3), (4), (5);
1172
insert into t3 values (10,3), (20,4), (30,5);
1173
select * from t2 where t2.a in (select a from t1);
1178
explain extended select * from t2 where t2.a in (select a from t1);
1179
id select_type table type possible_keys key key_len ref rows filtered Extra
1180
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
1181
2 SUBQUERY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
1183
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1184
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1188
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1189
id select_type table type possible_keys key key_len ref rows filtered Extra
1190
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
1191
2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where
1193
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1194
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1198
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1199
id select_type table type possible_keys key key_len ref rows filtered Extra
1200
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
1201
2 SUBQUERY t3 index PRIMARY PRIMARY 4 NULL 3 100.00 Using index
1202
2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer
1204
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1205
drop table t1, t2, t3;
1206
create table t1 (a int, b int, index a (a,b)) ENGINE=MyISAM;
1207
create table t2 (a int, index a (a)) ENGINE=MyISAM;
1208
create table t3 (a int, b int, index a (a)) ENGINE=MyISAM;
1209
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1210
insert into t2 values (2), (3), (4), (5);
1211
insert into t3 values (10,3), (20,4), (30,5);
1212
select * from t2 where t2.a in (select a from t1);
1217
explain extended select * from t2 where t2.a in (select a from t1);
1218
id select_type table type possible_keys key key_len ref rows filtered Extra
1219
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1220
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using index
1222
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1223
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1227
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1228
id select_type table type possible_keys key key_len ref rows filtered Extra
1229
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1230
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index
1232
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1233
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1237
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1238
id select_type table type possible_keys key key_len ref rows filtered Extra
1239
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1240
2 SUBQUERY t3 index a a 5 NULL 3 100.00 Using index
1241
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index; Using join buffer
1243
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1244
insert into t1 values (3,31);
1245
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1250
select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
1254
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1255
id select_type table type possible_keys key key_len ref rows filtered Extra
1256
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1257
2 SUBQUERY t1 index NULL a 10 NULL 10005 100.00 Using where; Using index
1259
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1260
drop table t1, t2, t3;
1261
create table t1 (a int, b int) ENGINE=MyISAM;
1262
create table t2 (a int, b int) ENGINE=MyISAM;
1263
create table t3 (a int, b int) ENGINE=MyISAM;
1264
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
1265
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
1266
insert into t3 values (3,3), (2,2), (1,1);
1267
select a,(select count(distinct t1.b) as sum from t1,t2 where t1.a=t2.a and t2.b > 0 and t1.a <= t3.b group by t1.a order by sum limit 1) from t3;
1268
a (select count(distinct t1.b) as sum from t1,t2 where t1.a=t2.a and t2.b > 0 and t1.a <= t3.b group by t1.a order by sum limit 1)
1272
drop table t1,t2,t3;
1273
create table t1 (s1 int) ENGINE=MyISAM;
1274
create table t2 (s1 int) ENGINE=MyISAM;
1275
insert into t1 values (1);
1276
insert into t2 values (1);
1277
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
1281
create table t1 (s1 int) ENGINE=MyISAM;
1282
create table t2 (s1 int) ENGINE=MyISAM;
1283
insert into t1 values (1);
1284
insert into t2 values (1);
1285
update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
1286
ERROR 42S22: Unknown column 'x.s1' in 'field list'
1288
create table t1(toid int,rd int) ENGINE=MyISAM;
1289
create table t2(userid int,pmnew int,pmtotal int) ENGINE=MyISAM;
1290
insert into t2 values(1,0,0),(2,0,0);
1291
insert into t1 values(1,0),(1,0),(1,0),(1,12),(1,15),(1,123),(1,12312),(1,12312),(1,123),(2,0),(2,0),(2,1),(2,2);
1292
select userid,pmtotal,pmnew, (select count(rd) from t1 where toid=t2.userid) calc_total, (select count(rd) from t1 where rd=0 and toid=t2.userid) calc_new from t2 where userid in (select distinct toid from t1);
1293
userid pmtotal pmnew calc_total calc_new
1297
create table t1 (s1 char(5)) ENGINE=MyISAM;
1298
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
1299
ERROR 21000: Operand should contain 1 column(s)
1300
insert into t1 values ('tttt');
1301
select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);
1304
explain extended (select * from t1);
1305
id select_type table type possible_keys key key_len ref rows filtered Extra
1306
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
1308
Note 1003 (select 'tttt' AS `s1` from `test`.`t1`)
1313
create table t1 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
1314
create table t2 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
1315
insert into t1 values ('a1'),('a2'),('a3');
1316
insert into t2 values ('a1'),('a2');
1317
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1318
s1 s1 NOT IN (SELECT s1 FROM t2)
1322
select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
1323
s1 s1 = ANY (SELECT s1 FROM t2)
1327
select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
1328
s1 s1 <> ALL (SELECT s1 FROM t2)
1332
select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
1333
s1 s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')
1337
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1338
id select_type table type possible_keys key key_len ref rows filtered Extra
1339
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1340
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key
1342
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
1343
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
1344
id select_type table type possible_keys key key_len ref rows filtered Extra
1345
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1346
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key
1348
Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
1349
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
1350
id select_type table type possible_keys key key_len ref rows filtered Extra
1351
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1352
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key
1354
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
1355
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
1356
id select_type table type possible_keys key key_len ref rows filtered Extra
1357
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1358
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Using where; Full scan on NULL key
1360
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
1362
create table t2 (a int, b int) ENGINE=MyISAM;
1363
create table t3 (a int) ENGINE=MyISAM;
1364
insert into t3 values (6),(7),(3);
1365
select * from t3 where a >= all (select b from t2);
1370
explain extended select * from t3 where a >= all (select b from t2);
1371
id select_type table type possible_keys key key_len ref rows filtered Extra
1372
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1373
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1375
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max('0') from `test`.`t2`)))
1376
select * from t3 where a >= some (select b from t2);
1378
explain extended select * from t3 where a >= some (select b from t2);
1379
id select_type table type possible_keys key key_len ref rows filtered Extra
1380
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1381
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1383
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min('0') from `test`.`t2`)))
1384
select * from t3 where a >= all (select b from t2 group by 1);
1389
explain extended select * from t3 where a >= all (select b from t2 group by 1);
1390
id select_type table type possible_keys key key_len ref rows filtered Extra
1391
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1392
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1394
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select '0' AS `b` from `test`.`t2` group by 1)))
1395
select * from t3 where a >= some (select b from t2 group by 1);
1397
explain extended select * from t3 where a >= some (select b from t2 group by 1);
1398
id select_type table type possible_keys key key_len ref rows filtered Extra
1399
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1400
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1402
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select '0' AS `b` from `test`.`t2` group by 1)))
1403
select * from t3 where NULL >= any (select b from t2);
1405
explain extended select * from t3 where NULL >= any (select b from t2);
1406
id select_type table type possible_keys key key_len ref rows filtered Extra
1407
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1408
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1410
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1411
select * from t3 where NULL >= any (select b from t2 group by 1);
1413
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
1414
id select_type table type possible_keys key key_len ref rows filtered Extra
1415
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1416
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1418
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1419
select * from t3 where NULL >= some (select b from t2);
1421
explain extended select * from t3 where NULL >= some (select b from t2);
1422
id select_type table type possible_keys key key_len ref rows filtered Extra
1423
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1424
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1426
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1427
select * from t3 where NULL >= some (select b from t2 group by 1);
1429
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
1430
id select_type table type possible_keys key key_len ref rows filtered Extra
1431
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1432
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1434
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1435
insert into t2 values (2,2), (2,1), (3,3), (3,1);
1436
select * from t3 where a > all (select max(b) from t2 group by a);
1440
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
1441
id select_type table type possible_keys key key_len ref rows filtered Extra
1442
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1443
2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
1445
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) AS `max(b)` from `test`.`t2` group by `test`.`t2`.`a`)))
1447
CREATE TABLE `t1` ( `id` bigint NOT NULL auto_increment, `taskid` bigint NOT NULL default '0', `dbid` int NOT NULL default '0', `create_date` datetime, `last_update` datetime, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ;
1448
INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
1449
CREATE TABLE `t2` (`db_id` int NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` int NOT NULL default '0',`secondary_uid` int NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM AUTO_INCREMENT=2147483647;
1450
INSERT INTO `t2` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (18, 'Not Set 1', 0, 0),(19, 'Valid', 1, 2),(20, 'Valid 2', 1, 2),(21, 'Should Not Return', 1, 2),(26, 'Not Set 2', 0, 0),(-1, 'ALL DB\'S', 0, 0);
1451
CREATE TABLE `t3` (`taskgenid` bigint NOT NULL auto_increment,`dbid` int NOT NULL default '0',`taskid` int NOT NULL default '0',`mon` int NOT NULL default '1',`tues` int NOT NULL default '1',`wed` int NOT NULL default '1',`thur` int NOT NULL default '1',`fri` int NOT NULL default '1',`sat` int NOT NULL default '0',`sun` int NOT NULL default '0',`how_often` int NOT NULL default '1',`userid` int NOT NULL default '0',`active` int NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM AUTO_INCREMENT=2 ;
1452
INSERT INTO `t3` (`taskgenid`, `dbid`, `taskid`, `mon`, `tues`,`wed`, `thur`, `fri`, `sat`, `sun`, `how_often`, `userid`, `active`) VALUES (1,-1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1);
1453
CREATE TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;
1454
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
1455
select dbid, name, (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01') from t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND t4.task_id = taskid;
1456
dbid name (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01')
1459
-1 Should Not Return 0
1460
SELECT dbid, name FROM t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND ((date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01')) AND t4.task_id = taskid;
1464
drop table t1,t2,t3,t4;
1465
CREATE TABLE t1 (id int default NULL) ENGINE=MyISAM;
1466
INSERT INTO t1 VALUES (1),(5);
1467
CREATE TABLE t2 (id int default NULL) ENGINE=MyISAM;
1468
INSERT INTO t2 VALUES (2),(6);
1469
select * from t1 where (1,2,6) in (select * from t2);
1470
ERROR 21000: Operand should contain 3 column(s)
1472
create table t1 (s1 char) ENGINE=MyISAM;
1473
insert into t1 values ('e');
1474
select * from t1 where 'f' > any (select s1 from t1);
1477
select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1480
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1481
id select_type table type possible_keys key key_len ref rows filtered Extra
1482
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1483
2 SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
1484
3 UNION t1 system NULL NULL NULL NULL 1 100.00
1485
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
1487
Note 1003 select 'e' AS `s1` from `test`.`t1` where 1
1489
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;
1490
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
1491
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;
1492
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
1493
select c.number as phone,(select p.code from t2 p where c.number like concat(p.code, '%') order by length(p.code) desc limit 1) as code from t1 c;
1500
create table t1 (s1 int) ENGINE=MyISAM;
1501
create table t2 (s1 int) ENGINE=MyISAM;
1502
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
1503
ERROR 42S22: Unknown column 't1.s2' in 'where clause'
1504
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
1505
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
1506
select count(*) from t2 group by t1.s2;
1507
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
1509
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB)) ENGINE=MyISAM;
1510
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA)) ENGINE=MyISAM;
1511
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
1512
INSERT INTO t2 VALUES (100, 200, 'C');
1513
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
1516
CREATE TABLE t1 (a int) ENGINE=MyISAM;
1517
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
1518
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
1527
`id` int NOT NULL auto_increment,
1528
`id_cns` int NOT NULL default '0',
1529
`tipo` enum('','UNO','DUE') NOT NULL default '',
1530
`anno_dep` int NOT NULL default '0',
1531
`particolare` bigint NOT NULL default '0',
1532
`generale` bigint NOT NULL default '0',
1533
`bis` int NOT NULL default '0',
1535
UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
1536
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`))
1538
INSERT INTO `t1` VALUES (1,16,'UNO',1987,2048,9681,0),(2,50,'UNO',1987,1536,13987,0),(3,16,'UNO',1987,2432,14594,0),(4,16,'UNO',1987,1792,13422,0),(5,16,'UNO',1987,1025,10240,0),(6,16,'UNO',1987,1026,7089,0);
1540
`id` int NOT NULL auto_increment,
1541
`max_anno_dep` int NOT NULL default '0',
1542
PRIMARY KEY (`id`)) ENGINE=MyISAM;
1543
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
1544
SELECT cns.id, cns.max_anno_dep, cns.max_anno_dep = (SELECT s.anno_dep FROM t1 AS s WHERE s.id_cns = cns.id ORDER BY s.anno_dep DESC LIMIT 1) AS PIPPO FROM t2 AS cns;
1545
id max_anno_dep PIPPO
1550
create table t1 (a int) ENGINE=MyISAM;
1551
insert into t1 values (1), (2), (3);
1552
SET SQL_SELECT_LIMIT=1;
1553
select sum(a) from (select * from t1) as a;
1556
select 2 in (select * from t1);
1557
2 in (select * from t1)
1559
SET SQL_SELECT_LIMIT=default;
1561
CREATE TABLE t1 (a int, b int, INDEX (a)) ENGINE=MyISAM;
1562
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
1563
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
1569
create table t1(val varchar(10)) ENGINE=MyISAM;
1570
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
1571
select count(*) from t1 as w1 where w1.val in (select w2.val from t1 as w2 where w2.val like 'm%') and w1.val in (select w3.val from t1 as w3 where w3.val like 'e%');
1575
create table t1 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
1576
insert into t1 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12');
1577
select * from t1 where id not in (select id from t1 where id < 8);
1584
select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
1591
explain extended select * from t1 where id not in (select id from t1 where id < 8);
1592
id select_type table type possible_keys key key_len ref rows filtered Extra
1593
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where
1594
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where
1596
Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
1597
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
1598
id select_type table type possible_keys key key_len ref rows filtered Extra
1599
1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
1600
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index
1602
Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
1603
Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))
1604
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
1605
create table t2 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
1606
insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');
1607
select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
1608
id text id text id text
1609
1 text1 1 text1 1 text1
1610
2 text2 2 text2 2 text2
1611
3 text3 3 text3 3 text3
1612
4 text4 4 text4 4 text4
1613
5 text5 5 text5 5 text5
1614
6 text6 6 text6 6 text6
1615
7 text7 7 text7 7 text7
1616
8 text8 8 text8 8 text8
1617
9 text9 9 text9 9 text9
1618
10 text10 10 text10 10 text10
1619
11 text11 11 text1 11 text11
1620
12 text12 12 text2 12 text12
1621
1000 text1000 NULL NULL 1000 text1000
1622
1001 text1001 NULL NULL 1000 text1000
1623
explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
1624
id select_type table type possible_keys key key_len ref rows filtered Extra
1625
1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00
1626
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id 2 100.00
1627
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where
1629
Note 1003 select `test`.`a`.`id` AS `id`,`test`.`a`.`text` AS `text`,`test`.`b`.`id` AS `id`,`test`.`b`.`text` AS `text`,`test`.`c`.`id` AS `id`,`test`.`c`.`text` AS `text` from `test`.`t1` `a` left join `test`.`t2` `b` on(((`test`.`b`.`id` = `test`.`a`.`id`) or isnull(`test`.`b`.`id`))) join `test`.`t1` `c` where (if(isnull(`test`.`b`.`id`),1000,`test`.`b`.`id`) = `test`.`c`.`id`)
1631
create table t1 (a int) ENGINE=MyISAM;
1632
insert into t1 values (1);
1633
explain select benchmark(1000, (select a from t1 where a=rand()));
1634
id select_type table type possible_keys key key_len ref rows Extra
1635
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
1636
2 SUBQUERY t1 system NULL NULL NULL NULL 1
1638
create table t1(id int) ENGINE=MyISAM;
1639
create table t2(id int) ENGINE=MyISAM;
1640
create table t3(flag int) ENGINE=MyISAM;
1641
select (select * from t3 where id not null) from t1, t2;
1642
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'null) from t1, t2' at line 1
1643
drop table t1,t2,t3;
1644
CREATE TABLE t1 (id INT) ENGINE=MyISAM;
1645
CREATE TABLE t2 (id INT) ENGINE=MyISAM;
1646
INSERT INTO t1 VALUES (1), (2);
1647
INSERT INTO t2 VALUES (1);
1648
SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
1652
SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
1656
SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY t1.id;
1660
SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY id;
1665
CREATE TABLE t1 ( a int, b int ) ENGINE=MyISAM;
1666
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1667
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1670
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1673
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
1676
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
1680
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
1684
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1688
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1691
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1694
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
1697
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
1701
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
1705
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1709
ALTER TABLE t1 ADD INDEX (a);
1710
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1713
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1716
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
1719
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
1723
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
1727
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
1731
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 WHERE b = 2 );
1734
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 WHERE b = 2 );
1737
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
1740
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
1744
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
1748
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
1752
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
1755
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
1758
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
1761
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
1765
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
1769
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
1773
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2);
1776
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2);
1779
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2);
1782
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2);
1786
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2);
1790
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2);
1794
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1797
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1800
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1803
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1807
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1811
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1815
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1818
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1821
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1824
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1828
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1832
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1836
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1839
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1842
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1845
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1849
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1853
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1857
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1860
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1863
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1866
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1870
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1874
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1878
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
1879
ERROR 21000: Operand should contain 1 column(s)
1880
SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1881
ERROR 21000: Operand should contain 1 column(s)
1882
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1883
ERROR 21000: Operand should contain 1 column(s)
1884
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a FROM t1 WHERE b = 2);
1885
ERROR 21000: Operand should contain 1 column(s)
1886
SELECT a FROM t1 WHERE a > ALL (SELECT a,2 FROM t1 WHERE b = 2);
1887
ERROR 21000: Operand should contain 1 column(s)
1888
SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a,2 FROM t1 WHERE b = 2);
1889
ERROR 21000: Operand should contain 1 column(s)
1890
SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2);
1891
ERROR 21000: Operand should contain 1 column(s)
1892
SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
1893
ERROR 21000: Operand should contain 1 column(s)
1894
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
1895
ERROR 21000: Operand should contain 2 column(s)
1896
SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1897
ERROR 21000: Operand should contain 1 column(s)
1898
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1900
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
1901
ERROR 21000: Operand should contain 2 column(s)
1902
SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
1903
ERROR 21000: Operand should contain 1 column(s)
1904
SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
1909
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
1912
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2);
1916
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
1919
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2);
1923
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2);
1926
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2);
1930
SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2);
1933
SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2);
1937
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1940
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1943
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1946
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1950
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1954
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1958
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1961
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1964
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1967
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1971
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1975
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 group by a);
1979
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
1982
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
1985
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
1988
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
1992
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
1996
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
2000
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 group by a HAVING a = 2);
2003
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 group by a HAVING a = 2);
2006
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 group by a HAVING a = 2);
2009
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 group by a HAVING a = 2);
2013
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 group by a HAVING a = 2);
2017
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2);
2021
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
2022
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-')
2026
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;
2027
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-')
2031
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;
2032
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-')
2037
CREATE TABLE t1 ( a double, b double ) ENGINE=MyISAM;
2038
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
2039
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
2042
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
2045
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
2048
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
2052
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
2056
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
2060
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2e0);
2063
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2e0);
2066
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2e0);
2069
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2e0);
2073
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2e0);
2077
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0);
2082
CREATE TABLE t1 ( a char(1), b char(1)) ENGINE=MyISAM;
2083
INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
2084
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
2087
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
2090
SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
2093
SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
2097
SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
2101
SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
2105
SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = '2');
2108
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = '2');
2111
SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = '2');
2114
SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = '2');
2118
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = '2');
2122
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = '2');
2127
create table t1 (a int, b int) ENGINE=MyISAM;
2128
insert into t1 values (1,2),(3,4);
2129
select * from t1 up where exists (select * from t1 where t1.a=up.a);
2133
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
2134
id select_type table type possible_keys key key_len ref rows filtered Extra
2135
1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 Using where
2136
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2138
Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
2139
Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))
2141
CREATE TABLE t1 (t1_a int) ENGINE=MyISAM;
2142
INSERT INTO t1 VALUES (1);
2143
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b)) ENGINE=MyISAM;
2144
INSERT INTO t2 VALUES (1, 1), (1, 2);
2145
SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1
2146
HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
2150
CREATE TABLE t1 (id int default NULL,name varchar(10) default NULL) ENGINE=MyISAM;
2151
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
2152
CREATE TABLE t2 (id int default NULL, pet varchar(10) default NULL) ENGINE=MyISAM;
2153
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
2154
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
2160
CREATE TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;
2161
insert into t1 values (1);
2162
CREATE TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
2163
insert into t2 values (1,2);
2164
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
2168
CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) COLLATE utf8_bin,`KUERZEL` VARCHAR(10) COLLATE utf8_bin,`IZAANALYSEART_ID` VARCHAR(11) COLLATE utf8_bin,`IZAPMKZ_ID` VARCHAR(11) COLLATE utf8_bin) ENGINE=MyISAM;
2169
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
2170
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
2171
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
2172
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000003','603','D0000000001','I0000000001');
2173
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000004','101','D0000000001','I0000000001');
2174
SELECT `IZAVORGANG_ID` FROM t1 WHERE `KUERZEL` IN(SELECT MIN(`KUERZEL`)`Feld1` FROM t1 WHERE `KUERZEL` LIKE'601%'And`IZAANALYSEART_ID`='D0000000001');
2178
CREATE TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)) ENGINE=MyISAM;
2179
CREATE TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)) ENGINE=MyISAM;
2180
insert into t1 values (1,1),(1,2),(2,1),(2,2);
2181
insert into t2 values (1,2),(2,2);
2182
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2186
alter table t2 drop primary key;
2187
alter table t2 add key KEY1 (aid, bid);
2188
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2192
alter table t2 drop key KEY1;
2193
alter table t2 add primary key (bid, aid);
2194
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2199
CREATE TABLE t1 (howmanyvalues bigint, avalue int) ENGINE=MyISAM;
2200
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
2201
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
2202
howmanyvalues count(*)
2207
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
2208
howmanyvalues mycount
2213
CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues);
2214
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues;
2215
howmanyvalues mycount
2220
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
2221
howmanyvalues mycount
2226
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues;
2227
howmanyvalues mycount
2233
create table t1 (x int) ENGINE=MyISAM;
2234
select (select b.x from t1 as b where b.x=a.x) from t1 as a where a.x=2 group by a.x;
2235
(select b.x from t1 as b where b.x=a.x)
2237
CREATE TABLE `t1` ( `master` int NOT NULL default '0', `map` int NOT NULL default '0', `slave` int NOT NULL default '0', `access` int NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`)) ENGINE=MyISAM;
2238
INSERT INTO `t1` VALUES (1,0,0,700),(1,1,1,400),(1,5,5,400),(1,12,12,400),(1,12,32,400),(4,12,32,400);
2239
CREATE TABLE `t2` ( `id` int NOT NULL default '0', `pid` int NOT NULL default '0', `map` int NOT NULL default '0', `level` int NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ENGINE=MyISAM ;
2240
INSERT INTO `t2` VALUES (6,5,12,7,'a'),(12,0,0,7,'a'),(12,1,0,7,'a'),(12,5,5,7,'a'),(12,5,12,7,'a');
2241
SELECT b.sc FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;
2242
ERROR 42S22: Unknown column 'b.sc' in 'field list'
2243
SELECT b.ac FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;
2248
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
2249
create table t1 (a int, b int) ENGINE=MyISAM;
2250
create table t2 (a int, b int) ENGINE=MyISAM;
2251
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
2252
insert into t2 values (1,3),(2,1);
2253
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
2254
a b (select max(b) from t2 where t1.b=t2.a)
2261
create table t1 (s1 int,s2 int) ENGINE=MyISAM;
2262
insert into t1 values (20,15);
2263
select * from t1 where (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
2266
create table t1 (s1 int) ENGINE=MyISAM;
2267
insert into t1 values (1),(null);
2268
select * from t1 where s1 < all (select s1 from t1);
2270
select s1, s1 < all (select s1 from t1) from t1;
2271
s1 s1 < all (select s1 from t1)
2276
Code char(3) NOT NULL default '',
2277
Name char(52) NOT NULL default '',
2278
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
2279
Region char(26) NOT NULL default '',
2280
SurfaceArea float(10,2) NOT NULL default '0.00',
2281
IndepYear int default NULL,
2282
Population int NOT NULL default '0',
2283
LifeExpectancy float(3,1) default NULL,
2284
GNP float(10,2) default NULL,
2285
GNPOld float(10,2) default NULL,
2286
LocalName char(45) NOT NULL default '',
2287
GovernmentForm char(45) NOT NULL default '',
2288
HeadOfState char(60) default NULL,
2289
Capital int default NULL,
2290
Code2 char(2) NOT NULL default ''
2292
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
2293
INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,68000,75.1,334.00,NULL,'Amerika Samoa','US Territory','George W. Bush',54,'AS');
2294
INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');
2295
INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM');
2296
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
2297
SELECT DISTINCT Continent AS c FROM t1 outr WHERE
2298
Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
2303
create table t1 (a1 int) ENGINE=MyISAM;
2304
create table t2 (b1 int) ENGINE=MyISAM;
2305
select * from t1 where a2 > any(select b1 from t2);
2306
ERROR 42S22: Unknown column 'a2' in 'IN/ALL/ANY subquery'
2307
select * from t1 where a1 > any(select b1 from t2);
2310
create table t1 (a integer, b integer) ENGINE=MyISAM;
2311
select (select * from t1) = (select 1,2);
2312
(select * from t1) = (select 1,2)
2314
select (select 1,2) = (select * from t1);
2315
(select 1,2) = (select * from t1)
2317
select row(1,2) = ANY (select * from t1);
2318
row(1,2) = ANY (select * from t1)
2320
select row(1,2) != ALL (select * from t1);
2321
row(1,2) != ALL (select * from t1)
2324
create table t1 (a integer, b integer) ENGINE=MyISAM;
2325
select row(1,(2,2)) in (select * from t1 );
2326
ERROR 21000: Operand should contain 2 column(s)
2327
select row(1,(2,2)) = (select * from t1 );
2328
ERROR 21000: Operand should contain 2 column(s)
2329
select (select * from t1) = row(1,(2,2));
2330
ERROR 21000: Operand should contain 1 column(s)
2332
create table t1 (a integer);
2333
insert into t1 values (1);
2334
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;
2335
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2336
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
2337
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2338
select 1 as xx, 1 = ALL ( select 1 from t1 where 1 = xx );
2339
xx 1 = ALL ( select 1 from t1 where 1 = xx )
2341
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
2342
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2345
categoryId int NOT NULL,
2346
courseId int NOT NULL,
2347
startDate datetime NOT NULL,
2348
endDate datetime NOT NULL,
2349
createDate datetime NOT NULL,
2350
modifyDate timestamp NOT NULL,
2351
attributes text NOT NULL)
2353
INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2354
(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2355
(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2356
(2,52,'2004-03-15','2004-10-01','2004-03-15','2004-09-17',''),
2357
(2,53,'2004-03-16','2004-10-01','2004-03-16','2004-09-17',''),
2358
(2,88,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2359
(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2360
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2361
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');
2363
userId int NOT NULL,
2364
courseId int NOT NULL,
2365
date datetime NOT NULL)
2367
INSERT INTO t2 VALUES (5141,71,'2003-11-18'),
2368
(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),
2369
(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),
2370
(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),
2371
(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),
2372
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');
2374
groupId int NOT NULL,
2375
parentId int NOT NULL,
2376
startDate datetime NOT NULL,
2377
endDate datetime NOT NULL,
2378
createDate datetime NOT NULL,
2379
modifyDate timestamp NOT NULL,
2382
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
2385
groupTypeId int NOT NULL,
2386
groupKey varchar(50) NOT NULL,
2390
createDate datetime NOT NULL,
2391
modifyDate timestamp NOT NULL)
2393
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
2394
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
2396
userId int NOT NULL,
2397
groupId int NOT NULL,
2398
createDate datetime NOT NULL,
2399
modifyDate timestamp NOT NULL) ENGINE=MyISAM;
2400
INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
2402
count(distinct t2.userid) pass,
2404
count(t2.courseid) crse,
2407
date_format(date, '%b%y') as colhead
2409
join t1 on t2.courseid=t1.courseid
2422
select t4.id as parentid,
2423
t4.name as parentgroup,
2425
t4.name as groupname,
2428
) as gin on t5.groupid=gin.childid
2429
) as groupstuff on t2.userid = groupstuff.userid
2431
groupstuff.groupname, colhead , t2.courseid;
2432
pass userid parentid parentgroup childid groupname grouptypeid crse categoryid courseid colhead
2433
1 5141 12 group2 12 group2 5 1 5 12 Aug04
2434
1 5141 12 group2 12 group2 5 1 1 41 Aug04
2435
1 5141 12 group2 12 group2 5 1 2 52 Aug04
2436
1 5141 12 group2 12 group2 5 1 2 53 Aug04
2437
1 5141 12 group2 12 group2 5 1 3 51 Oct04
2438
1 5141 12 group2 12 group2 5 1 1 86 Oct04
2439
1 5141 12 group2 12 group2 5 1 1 87 Oct04
2440
1 5141 12 group2 12 group2 5 1 2 88 Oct04
2441
1 5141 12 group2 12 group2 5 1 2 89 Oct04
2442
drop table t1, t2, t3, t4, t5;
2443
create table t1 (a int) ENGINE=MyISAM;
2444
insert into t1 values (1), (2), (3);
2445
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
2451
create table t1 (a int, b int) ENGINE=MyISAM;
2452
insert into t1 values (1,2);
2453
select 1 = (select * from t1);
2454
ERROR 21000: Operand should contain 1 column(s)
2455
select (select * from t1) = 1;
2456
ERROR 21000: Operand should contain 2 column(s)
2457
select (1,2) = (select a from t1);
2458
ERROR 21000: Operand should contain 2 column(s)
2459
select (select a from t1) = (1,2);
2460
ERROR 21000: Operand should contain 1 column(s)
2461
select (1,2,3) = (select * from t1);
2462
ERROR 21000: Operand should contain 3 column(s)
2463
select (select * from t1) = (1,2,3);
2464
ERROR 21000: Operand should contain 2 column(s)
2466
create table t1 (fld enum('0','1')) ENGINE=MyISAM;
2467
insert into t1 values ('1');
2468
select * from (select max(fld) from t1) as foo;
2472
CREATE TABLE t1 (one int, two int, flag char(1)) ENGINE=MyISAM;
2473
CREATE TABLE t2 (one int, two int, flag char(1)) ENGINE=MyISAM;
2474
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2475
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2477
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
2482
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
2486
insert into t2 values (null,null,'N');
2487
insert into t2 values (null,3,'0');
2488
insert into t2 values (null,5,'0');
2489
insert into t2 values (10,null,'0');
2490
insert into t1 values (10,3,'0');
2491
insert into t1 values (10,5,'0');
2492
insert into t1 values (10,10,'0');
2493
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
2503
SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2507
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
2517
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
2527
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
2537
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
2538
id select_type table type possible_keys key key_len ref rows filtered Extra
2539
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2540
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
2542
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
2543
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2544
id select_type table type possible_keys key key_len ref rows filtered Extra
2545
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary
2546
1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer
2548
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N'))
2549
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
2550
id select_type table type possible_keys key key_len ref rows filtered Extra
2551
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2552
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
2554
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
2556
CREATE TABLE t1 (a char(5), b char(5)) ENGINE=MyISAM;
2557
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
2558
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
2562
CREATE TABLE t1 (a int) ENGINE=MyISAM;
2563
CREATE TABLE t2 (a int, b int) ENGINE=MyISAM;
2564
CREATE TABLE t3 (b int NOT NULL) ENGINE=MyISAM;
2565
INSERT INTO t1 VALUES (1), (2), (3), (4);
2566
INSERT INTO t2 VALUES (1,10), (3,30);
2567
SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b
2568
WHERE t3.b IS NOT NULL OR t2.a > 10;
2571
WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b
2572
WHERE t3.b IS NOT NULL OR t2.a > 10);
2578
DROP TABLE t1,t2,t3;
2579
CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
2580
CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;
2581
INSERT INTO t1 VALUES (1);
2582
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
2585
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);
2588
INSERT INTO t2 VALUES (1);
2589
INSERT INTO t2 VALUES (2);
2590
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);
2594
create table t1 (s1 char) ENGINE=MyISAM;
2595
insert into t1 values (1),(2);
2596
select * from t1 where (s1 < any (select s1 from t1));
2599
select * from t1 where not (s1 < any (select s1 from t1));
2602
select * from t1 where (s1 < ALL (select s1+1 from t1));
2605
select * from t1 where not(s1 < ALL (select s1+1 from t1));
2608
select * from t1 where (s1+1 = ANY (select s1 from t1));
2611
select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
2614
select * from t1 where (s1 = ALL (select s1/s1 from t1));
2617
select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
2622
retailerID varchar(8) NOT NULL,
2623
statusID int NOT NULL,
2624
changed datetime NOT NULL,
2625
UNIQUE KEY retailerID (retailerID, statusID, changed))
2627
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
2628
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
2629
INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
2630
INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");
2631
INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");
2632
INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");
2634
where (r1.retailerID,(r1.changed)) in
2635
(SELECT r2.retailerId,(max(changed)) from t1 r2
2636
group by r2.retailerId);
2637
retailerID statusID changed
2638
0026 2 2006-01-06 12:25:53
2639
0037 2 2006-01-06 12:25:53
2640
0048 1 2006-01-06 12:37:50
2641
0059 1 2006-01-06 12:37:50
2643
create table t1(a int, primary key (a)) ENGINE=MyISAM;
2644
insert into t1 values (10);
2645
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b)) ENGINE=MyISAM;
2646
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
2647
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2648
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2649
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2650
id select_type table type possible_keys key key_len ref rows Extra
2651
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
2652
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2653
2 DEPENDENT SUBQUERY t2 range b b 136 NULL 2 Using where
2654
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2655
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2656
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2659
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2660
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2661
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2662
id select_type table type possible_keys key key_len ref rows Extra
2663
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
2664
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2665
2 DEPENDENT SUBQUERY t2 range b b 136 NULL 2 Using where; Using MRR
2666
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2667
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2668
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2673
field1 int NOT NULL,
2674
field2 int NOT NULL,
2675
field3 int NOT NULL,
2676
PRIMARY KEY (field1,field2,field3))
2679
fieldA int NOT NULL,
2680
fieldB int NOT NULL,
2681
PRIMARY KEY (fieldA,fieldB))
2683
INSERT INTO t1 VALUES
2684
(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
2685
INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
2686
SELECT field1, field2, COUNT(*)
2687
FROM t1 GROUP BY field1, field2;
2688
field1 field2 COUNT(*)
2692
SELECT field1, field2
2694
GROUP BY field1, field2
2695
HAVING COUNT(*) >= ALL (SELECT fieldB
2696
FROM t2 WHERE fieldA = field1);
2699
SELECT field1, field2
2701
GROUP BY field1, field2
2702
HAVING COUNT(*) < ANY (SELECT fieldB
2703
FROM t2 WHERE fieldA = field1);
2708
CREATE TABLE t1(a int, INDEX (a)) ENGINE=MyISAM;
2709
INSERT INTO t1 VALUES (1), (3), (5), (7);
2710
INSERT INTO t1 VALUES (NULL);
2711
CREATE TABLE t2(a int) ENGINE=MyISAM;
2712
INSERT INTO t2 VALUES (1),(2),(3);
2713
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
2714
id select_type table type possible_keys key key_len ref rows Extra
2715
1 PRIMARY t2 ALL NULL NULL NULL NULL 3
2716
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key
2717
SELECT a, a IN (SELECT a FROM t1) FROM t2;
2718
a a IN (SELECT a FROM t1)
2723
CREATE TABLE t1 (a DATETIME) ENGINE=MyISAM;
2724
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
2725
CREATE TABLE t2 ENGINE=MyISAM AS SELECT
2726
(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
2727
FROM t1 WHERE a > '2000-01-01';
2728
SHOW CREATE TABLE t2;
2730
t2 CREATE TABLE `t2` (
2731
`sub_a` datetime DEFAULT NULL
2733
CREATE TABLE t3 ENGINE=MyISAM AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
2734
SHOW CREATE TABLE t3;
2736
t3 CREATE TABLE `t3` (
2737
`a` datetime DEFAULT NULL
2739
DROP TABLE t1,t2,t3;
2740
CREATE TABLE t1 (a int) ENGINE=MyISAM;
2741
INSERT INTO t1 VALUES (2), (4), (1), (3);
2742
CREATE TABLE t2 (b int, c int) ENGINE=MyISAM;
2743
INSERT INTO t2 VALUES
2744
(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
2745
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
2751
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);
2752
ERROR 21000: Subquery returns more than 1 row
2753
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;
2759
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
2760
ERROR 21000: Subquery returns more than 1 row
2761
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
2766
SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
2767
ERROR 21000: Subquery returns more than 1 row
2768
SELECT a FROM t1 GROUP BY a
2769
HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
2770
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
2776
SELECT a FROM t1 GROUP BY a
2777
HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
2778
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
2779
ERROR 21000: Subquery returns more than 1 row
2780
SELECT a FROM t1 GROUP BY a
2781
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
2782
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
2785
SELECT a FROM t1 GROUP BY a
2786
HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
2787
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;
2788
ERROR 21000: Subquery returns more than 1 row
2790
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
2791
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
2798
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
2799
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
2800
ERROR 21000: Subquery returns more than 1 row
2802
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
2803
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
2810
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
2811
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
2812
ERROR 21000: Subquery returns more than 1 row
2814
create table t1 (df decimal(5,1)) ENGINE=MyISAM;
2815
insert into t1 values(1.1);
2816
insert into t1 values(2.2);
2817
select * from t1 where df <= all (select avg(df) from t1 group by df);
2820
select * from t1 where df >= all (select avg(df) from t1 group by df);
2824
create table t1 (df decimal(5,1)) ENGINE=MyISAM;
2825
insert into t1 values(1.1);
2826
select 1.1 * exists(select * from t1);
2827
1.1 * exists(select * from t1)
2831
grp int default NULL,
2832
a decimal(10,2) default NULL) ENGINE=MyISAM;
2833
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
2843
select min(a) from t1 group by grp;
2850
CREATE table t1 ( c1 integer ) ENGINE=MyISAM;
2851
INSERT INTO t1 VALUES ( 1 );
2852
INSERT INTO t1 VALUES ( 2 );
2853
INSERT INTO t1 VALUES ( 3 );
2854
CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;
2855
INSERT INTO t2 VALUES ( 1 );
2856
INSERT INTO t2 VALUES ( 4 );
2857
INSERT INTO t2 VALUES ( 5 );
2858
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);
2861
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2
2862
WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );
2866
CREATE TABLE t1 ( c1 integer ) ENGINE=MyISAM;
2867
INSERT INTO t1 VALUES ( 1 );
2868
INSERT INTO t1 VALUES ( 2 );
2869
INSERT INTO t1 VALUES ( 3 );
2870
INSERT INTO t1 VALUES ( 6 );
2871
CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;
2872
INSERT INTO t2 VALUES ( 1 );
2873
INSERT INTO t2 VALUES ( 4 );
2874
INSERT INTO t2 VALUES ( 5 );
2875
INSERT INTO t2 VALUES ( 6 );
2876
CREATE TABLE t3 ( c3 integer ) ENGINE=MyISAM;
2877
INSERT INTO t3 VALUES ( 7 );
2878
INSERT INTO t3 VALUES ( 8 );
2879
SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
2880
WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
2884
DROP TABLE t1,t2,t3;
2885
CREATE TABLE t1 (EMPNUM CHAR(3)) ENGINE=MyISAM;
2886
CREATE TABLE t2 (EMPNUM CHAR(3) ) ENGINE=MyISAM;
2887
INSERT INTO t1 VALUES ('E1'),('E2');
2888
INSERT INTO t2 VALUES ('E1');
2890
WHERE t1.EMPNUM NOT IN
2893
WHERE t1.EMPNUM = t2.EMPNUM);
2898
CREATE TABLE t1(select_id BIGINT, values_id BIGINT) ENGINE=MyISAM;
2899
INSERT INTO t1 VALUES (1, 1);
2900
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,
2901
PRIMARY KEY(select_id,values_id)) ENGINE=MyISAM;
2902
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
2903
SELECT values_id FROM t1
2904
WHERE values_id IN (SELECT values_id FROM t2
2905
WHERE select_id IN (1, 0));
2908
SELECT values_id FROM t1
2909
WHERE values_id IN (SELECT values_id FROM t2
2910
WHERE select_id BETWEEN 0 AND 1);
2913
SELECT values_id FROM t1
2914
WHERE values_id IN (SELECT values_id FROM t2
2915
WHERE select_id = 0 OR select_id = 1);
2919
create table t1 (fld enum('0','1')) ENGINE=MyISAM;
2920
insert into t1 values ('1');
2921
select * from (select max(fld) from t1) as foo;
2925
CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
2926
CREATE TABLE t2 (c int, d int) ENGINE=MyISAM;
2927
CREATE TABLE t3 (e int) ENGINE=MyISAM;
2928
INSERT INTO t1 VALUES
2929
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
2930
INSERT INTO t2 VALUES
2931
(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
2932
INSERT INTO t3 VALUES (10), (30), (10), (20) ;
2933
SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a;
2953
SELECT a FROM t1 GROUP BY a
2954
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
2958
SELECT a FROM t1 GROUP BY a
2959
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);
2962
SELECT a FROM t1 GROUP BY a
2963
HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);
2967
SELECT a FROM t1 GROUP BY a
2968
HAVING a IN (SELECT c FROM t2
2969
WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));
2973
SELECT a FROM t1 GROUP BY a
2974
HAVING a IN (SELECT c FROM t2
2975
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
2979
SELECT a FROM t1 GROUP BY a
2980
HAVING a IN (SELECT c FROM t2
2981
WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));
2984
SELECT a FROM t1 GROUP BY a
2985
HAVING a IN (SELECT c FROM t2
2986
WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));
2989
SELECT a FROM t1 GROUP BY a
2990
HAVING a IN (SELECT c FROM t2
2991
WHERE MIN(b) < d AND
2992
EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
2995
SELECT a, SUM(a) FROM t1 GROUP BY a;
3002
WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;
3006
SELECT a FROM t1 GROUP BY a
3007
HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
3014
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;
3020
EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);
3027
SELECT t1.a FROM t1 GROUP BY t1.a
3028
HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c
3029
HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
3030
HAVING SUM(t1.a+t2.c) < t3.e/4));
3034
SELECT t1.a FROM t1 GROUP BY t1.a
3035
HAVING t1.a > ALL(SELECT t2.c FROM t2
3036
WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
3037
HAVING SUM(t1.a+t2.c) < t3.e/4));
3040
SELECT t1.a FROM t1 GROUP BY t1.a
3041
HAVING t1.a > ALL(SELECT t2.c FROM t2
3042
WHERE EXISTS(SELECT t3.e FROM t3
3043
WHERE SUM(t1.a+t2.c) < t3.e/4));
3044
ERROR HY000: Invalid use of group function
3045
SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;
3046
ERROR HY000: Invalid use of group function
3047
SELECT t1.a FROM t1 GROUP BY t1.a
3048
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3049
HAVING AVG(t2.c+SUM(t1.b)) > 20);
3054
SELECT t1.a FROM t1 GROUP BY t1.a
3055
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3056
HAVING AVG(SUM(t1.b)) > 20);
3060
SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a
3061
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
3062
HAVING t2.c+sum > 20);
3067
DROP TABLE t1,t2,t3;
3068
CREATE TABLE t1 (a varchar(5), b varchar(10)) ENGINE=MyISAM;
3069
INSERT INTO t1 VALUES
3070
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
3071
('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
3072
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3078
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3079
id select_type table type possible_keys key key_len ref rows Extra
3080
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
3081
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
3082
ALTER TABLE t1 ADD INDEX(a);
3083
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3089
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3090
id select_type table type possible_keys key key_len ref rows Extra
3091
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
3092
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
3094
create table t1( f1 int,f2 int) ENGINE=MyISAM;
3095
insert into t1 values (1,1),(2,2);
3096
select tt.t from (select 'crash1' as t, f2 from t1) as tt left join t1 on tt.t = 'crash2' and tt.f2 = t1.f2 where tt.t = 'crash1';
3101
create table t1 (c int, key(c)) ENGINE=MyISAM;
3102
insert into t1 values (1142477582), (1142455969);
3103
create table t2 (a int, b int) ENGINE=MyISAM;
3104
insert into t2 values (2, 1), (1, 0);
3105
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
3107
create table t1 (i int, j bigint) ENGINE=MyISAM;
3108
insert into t1 values (1, 2), (2, 2), (3, 2);
3109
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
3113
CREATE TABLE t1 (i BIGINT) ENGINE=MyISAM;
3114
INSERT INTO t1 VALUES (10000000000000000);
3115
INSERT INTO t1 VALUES (1);
3116
CREATE TABLE t2 (i BIGINT) ENGINE=MyISAM;
3117
INSERT INTO t2 VALUES (10000000000000000);
3118
INSERT INTO t2 VALUES (1);
3120
SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;
3125
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
3128
/* subquery test with cast*/
3129
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
3135
id bigint NOT NULL auto_increment,
3136
name varchar(255) NOT NULL,
3139
INSERT INTO t1 VALUES
3140
(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
3142
id bigint NOT NULL auto_increment,
3143
mid bigint NOT NULL,
3147
INSERT INTO t2 VALUES
3148
(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
3149
(4, 2, '2006-04-20'), (5, 1, '2006-05-01');
3151
(SELECT date FROM t2 WHERE mid = t1.id
3152
ORDER BY date DESC LIMIT 0, 1) AS date_last,
3153
(SELECT date FROM t2 WHERE mid = t1.id
3154
ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last
3156
id name date_last date_next_to_last
3157
1 Balazs 2006-05-01 NULL
3158
2 Joe 2006-04-20 NULL
3159
3 Frank 2006-04-13 NULL
3161
(SELECT COUNT(*) FROM t2 WHERE mid = t1.id
3162
ORDER BY date DESC LIMIT 1, 1) AS date_count
3169
(SELECT date FROM t2 WHERE mid = t1.id
3170
ORDER BY date DESC LIMIT 0, 1) AS date_last,
3171
(SELECT date FROM t2 WHERE mid = t1.id
3172
ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last
3174
id name date_last date_next_to_last
3175
1 Balazs 2006-05-01 2006-03-30
3176
2 Joe 2006-04-20 2006-04-06
3177
3 Frank 2006-04-13 NULL
3180
i1 int NOT NULL default '0',
3181
i2 int NOT NULL default '0',
3183
PRIMARY KEY (i1,i2,t))
3185
INSERT INTO t1 VALUES
3186
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
3187
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
3188
(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),
3189
(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),
3190
(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),
3191
(24,2,'2005-05-27 12:40:06');
3193
i1 int NOT NULL default '0',
3194
i2 int NOT NULL default '0',
3195
t datetime default NULL,
3198
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
3201
WHERE t1.t = (SELECT t1.t FROM t1
3202
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
3203
ORDER BY t1.t DESC LIMIT 1);
3204
id select_type table type possible_keys key key_len ref rows Extra
3205
1 PRIMARY t2 system NULL NULL NULL NULL 1
3206
1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
3207
2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
3209
WHERE t1.t = (SELECT t1.t FROM t1
3210
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
3211
ORDER BY t1.t DESC LIMIT 1);
3213
24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40
3215
CREATE TABLE t1 (i INT) ENGINE=MyISAM;
3216
(SELECT i FROM t1) UNION (SELECT i FROM t1);
3218
SELECT * FROM t1 WHERE NOT EXISTS
3220
(SELECT i FROM t1) UNION
3225
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
3226
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 1
3227
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
3229
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'union (select t12.i from t1 t12))
3231
explain select * from t1 where not exists
3232
((select t11.i from t1 t11) union (select t12.i from t1 t12));
3233
id select_type table type possible_keys key key_len ref rows Extra
3234
1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
3235
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3236
3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3237
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
3239
CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
3240
CREATE TABLE t2 (a INT) ENGINE=MyISAM;
3241
INSERT INTO t2 values (1);
3242
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
3243
SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
3244
(SELECT COUNT(DISTINCT t1.b) from t2)
3248
SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
3249
FROM t1 GROUP BY t1.a;
3250
(SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
3254
SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
3255
COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b))
3262
SELECT COUNT(DISTINCT t1.b)
3265
FROM t1 GROUP BY t1.a LIMIT 1)
3271
SELECT COUNT(DISTINCT t1.b)
3274
FROM t1 GROUP BY t1.a LIMIT 1)
3279
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)) ENGINE=MyISAM;
3280
CREATE TABLE t2 (x int auto_increment, y int, z int,
3281
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)) ENGINE=MyISAM;
3282
SET SESSION sort_buffer_size = 32 * 1024;
3284
Error 1292 Truncated incorrect sort_buffer_size value: '32768'
3286
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
3290
SET SESSION sort_buffer_size = 8 * 1024 * 1024;
3292
FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
3297
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int) ENGINE=MyISAM;
3298
CREATE TABLE t2 (c int) ENGINE=MyISAM;
3299
INSERT INTO t1 VALUES ('aa', 1);
3300
INSERT INTO t2 VALUES (1);
3302
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
3304
SELECT c from t2 WHERE c=t1.c);
3307
INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
3309
WHERE EXISTS (SELECT c FROM t2 WHERE c=1
3311
SELECT c from t2 WHERE c=t1.c);
3317
INSERT INTO t2 VALUES (2);
3318
CREATE TABLE t3 (c int) ENGINE=MyISAM;
3319
INSERT INTO t3 VALUES (1);
3321
WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
3323
SELECT c from t2 WHERE c=t1.c);
3329
DROP TABLE t1,t2,t3;
3330
DROP TABLE IF EXISTS t1;
3331
DROP TABLE IF EXISTS t2;
3332
DROP TABLE IF EXISTS t1xt2;
3335
t varchar(4) DEFAULT NULL)
3339
t varchar(4) DEFAULT NULL)
3341
CREATE TABLE t1xt2 (
3345
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
3346
INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');
3347
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
3348
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3349
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3351
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3352
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3354
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3355
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3357
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3358
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3364
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3365
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));
3371
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3372
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));
3378
insert INTO t1xt2 VALUES (1, 12);
3379
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3380
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3383
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3384
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3387
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3388
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3391
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3392
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3397
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3398
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3403
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3404
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3409
insert INTO t1xt2 VALUES (2, 12);
3410
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3411
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3415
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3416
(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3420
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3421
(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3425
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3426
(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3430
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3431
(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
3435
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3436
(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
3443
CREATE TABLE t1 (a int) ENGINE=MyISAM;
3444
INSERT INTO t1 VALUES (3), (1), (2);
3445
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
3450
SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
3456
CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
3457
CREATE TABLE t2 (m int, n int) ENGINE=MyISAM;
3458
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3459
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3461
(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
3463
COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
3468
(SELECT MIN(m) FROM t2 WHERE m = count(*))
3470
COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*))
3476
HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
3481
CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
3482
CREATE TABLE t2 (m int, n int) ENGINE=MyISAM;
3483
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3484
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3485
SELECT COUNT(*) c, a,
3486
(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
3488
c a (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
3492
SELECT COUNT(*) c, a,
3493
(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
3495
c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
3500
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)) ENGINE=MyISAM;
3501
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
3502
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
3503
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
3505
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test
3512
(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test
3519
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test
3520
FROM t1 WHERE t1.d=0 GROUP BY a;
3526
(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3527
LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
3547
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3549
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
3550
FROM t1 as tt GROUP BY tt.a;
3556
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
3558
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
3559
FROM t1 as tt GROUP BY tt.a;
3565
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
3566
INSERT INTO t1 values (1),(1),(1),(1);
3567
CREATE TABLE t2 (x INT) ENGINE=MyISAM;
3568
INSERT INTO t1 values (1000),(1001),(1002);
3569
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
3570
ERROR HY000: Invalid use of group function
3571
SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
3572
ERROR HY000: Invalid use of group function
3574
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
3576
ERROR HY000: Invalid use of group function
3578
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
3580
ERROR HY000: Invalid use of group function
3582
CREATE TABLE t1 (a int, b int, KEY (a)) ENGINE=MyISAM;
3583
INSERT INTO t1 VALUES (1,1),(2,1);
3584
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
3585
id select_type table type possible_keys key key_len ref rows Extra
3586
1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
3587
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
3589
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)) ENGINE=MyISAM;
3590
INSERT INTO t1 VALUES
3591
(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
3592
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;
3593
INSERT INTO t2 VALUES (7), (5), (1), (3);
3594
SELECT id, st FROM t1
3595
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
3600
SELECT id, st FROM t1
3601
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
3607
SELECT id, st FROM t1
3608
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
3612
SELECT id, st FROM t1
3613
WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
3619
CREATE TABLE t1 (a int) ENGINE=MyISAM;
3620
INSERT INTO t1 VALUES (1), (2);
3622
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
3623
id select_type table type possible_keys key key_len ref rows filtered Extra
3624
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
3625
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
3627
Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`
3630
a varchar(255) default NULL,
3631
b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3635
a varchar(255) default NULL)
3637
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
3638
INSERT INTO t1 SELECT * FROM t1;
3639
INSERT INTO t1 SELECT * FROM t1;
3640
INSERT INTO t1 SELECT * FROM t1;
3641
INSERT INTO t1 SELECT * FROM t1;
3642
INSERT INTO t1 SELECT * FROM t1;
3643
INSERT INTO t1 SELECT * FROM t1;
3644
INSERT INTO t1 SELECT * FROM t1;
3645
INSERT INTO t1 SELECT * FROM t1;
3646
INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');
3647
INSERT INTO `t2` VALUES ('abcdefghijk');
3648
INSERT INTO `t2` VALUES ('asdf');
3649
SET session sort_buffer_size=8192;
3651
Error 1292 Truncated incorrect sort_buffer_size value: '8192'
3652
SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;
3657
CREATE TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;
3658
CREATE TABLE t2 (x INTEGER) ENGINE=MyISAM;
3659
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
3660
INSERT INTO t2 VALUES (1), (2);
3661
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
3662
ERROR 21000: Subquery returns more than 1 row
3663
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
3664
ERROR 21000: Subquery returns more than 1 row
3665
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
3666
(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)
3669
CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
3670
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
3671
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
3672
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
3677
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
3678
CREATE TABLE t2 (a INT) ENGINE=MyISAM;
3679
INSERT INTO t1 VALUES (1),(2);
3680
INSERT INTO t2 VALUES (1),(2);
3681
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
3682
(SELECT SUM(t1.a) FROM t2 WHERE a=0)
3684
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;
3685
ERROR 21000: Subquery returns more than 1 row
3686
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
3687
(SELECT SUM(t1.a) FROM t2 WHERE a=1)
3690
CREATE TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;
3691
CREATE TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;
3692
INSERT INTO t1 VALUES (100, 200);
3693
INSERT INTO t1 VALUES (101, 201);
3694
INSERT INTO t2 VALUES (101, 201);
3695
INSERT INTO t2 VALUES (103, 203);
3696
SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
3697
((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL
3701
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10)) ENGINE=MyISAM;
3702
INSERT INTO t1 VALUES ('a', 'aa');
3703
INSERT INTO t1 VALUES ('a', 'aaa');
3704
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3706
CREATE INDEX I1 ON t1 (a);
3707
CREATE INDEX I2 ON t1 (b);
3708
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3709
id select_type table type possible_keys key key_len ref rows Extra
3710
1 PRIMARY t1 index I1 I1 7 NULL 2 Using index; LooseScan
3711
1 PRIMARY t1 ref I2 I2 43 test.t1.a 2 Using where
3712
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3714
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)) ENGINE=MyISAM;
3715
INSERT INTO t2 SELECT * FROM t1;
3716
CREATE INDEX I1 ON t2 (a);
3717
CREATE INDEX I2 ON t2 (b);
3718
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
3719
id select_type table type possible_keys key key_len ref rows Extra
3720
1 PRIMARY t2 index I1 I1 7 NULL 2 Using index; LooseScan
3721
1 PRIMARY t2 ref I2 I2 43 test.t2.a 2 Using where
3722
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
3725
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
3726
id select_type table type possible_keys key key_len ref rows Extra
3727
1 PRIMARY t1 index I1 I1 7 NULL 2 Using where; Using index; LooseScan
3728
1 PRIMARY t1 ref I2 I2 43 test.t1.a 2 Using where
3729
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
3732
CREATE TABLE t1(a INT, b INT) ENGINE=MyISAM;
3733
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
3735
SELECT a AS out_a, MIN(b) FROM t1
3736
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
3738
ERROR 42S22: Unknown column 'out_a' in 'where clause'
3739
SELECT a AS out_a, MIN(b) FROM t1
3740
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
3742
ERROR 42S22: Unknown column 'out_a' in 'where clause'
3744
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
3745
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
3747
id select_type table type possible_keys key key_len ref rows Extra
3748
1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
3749
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
3750
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
3751
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
3757
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
3758
CREATE TABLE t2 (a INT) ENGINE=MyISAM;
3759
INSERT INTO t1 VALUES (1),(2);
3760
INSERT INTO t2 VALUES (1),(2);
3761
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
3766
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
3767
id select_type table type possible_keys key key_len ref rows filtered Extra
3768
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
3769
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
3771
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
3772
Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))
3774
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
3775
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
3776
id select_type table type possible_keys key key_len ref rows filtered Extra
3777
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
3778
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
3779
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where
3780
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
3782
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
3783
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
3784
Note 1003 select 2 AS `2` from `test`.`t1` where exists((select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))
3787
f7 varchar(32) collate utf8_bin NOT NULL default '',
3788
f10 varchar(32) collate utf8_bin default NULL,
3791
INSERT INTO t4 VALUES(1,1), (2,null);
3793
f4 varchar(32) collate utf8_bin NOT NULL default '',
3794
f2 varchar(50) collate utf8_bin default NULL,
3795
f3 varchar(10) collate utf8_bin default NULL,
3797
UNIQUE KEY uk1 (f2))
3799
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
3801
f8 varchar(32) collate utf8_bin NOT NULL default '',
3802
f1 varchar(10) collate utf8_bin default NULL,
3803
f9 varchar(32) collate utf8_bin default NULL,
3806
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
3808
f6 varchar(32) collate utf8_bin NOT NULL default '',
3809
f5 varchar(50) collate utf8_bin default NULL,
3812
INSERT INTO t3 VALUES (1,null), (2,null);
3814
IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,
3815
IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3,
3819
FROM t2 VPC, t4 a2, t2 a3
3821
VPC.f4 = a2.f10 AND a3.f2 = a4
3828
t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4
3833
DROP TABLE t1, t2, t3, t4;
3835
create table t_out (subcase char(3),
3836
a1 char(2), b1 char(2), c1 char(2)) ENGINE=MyISAM;
3837
create table t_in (a2 char(2), b2 char(2), c2 char(2)) ENGINE=MyISAM;
3838
insert into t_out values ('A.1','2a', NULL, '2a');
3839
insert into t_out values ('A.3', '2a', NULL, '2a');
3840
insert into t_out values ('A.4', '2a', NULL, 'xx');
3841
insert into t_out values ('B.1', '2a', '2a', '2a');
3842
insert into t_out values ('B.2', '2a', '2a', '2a');
3843
insert into t_out values ('B.3', '3a', 'xx', '3a');
3844
insert into t_out values ('B.4', 'xx', '3a', '3a');
3845
insert into t_in values ('1a', '1a', '1a');
3846
insert into t_in values ('2a', '2a', '2a');
3847
insert into t_in values (NULL, '2a', '2a');
3848
insert into t_in values ('3a', NULL, '3a');
3850
Test general IN semantics (not top-level)
3854
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
3855
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
3856
from t_out where subcase = 'A.1';
3857
subcase pred_in pred_not_in
3859
case A.2 - impossible
3862
(a1, b1, c1) IN (select * from t_in) pred_in,
3863
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
3864
from t_out where subcase = 'A.3';
3865
subcase pred_in pred_not_in
3869
(a1, b1, c1) IN (select * from t_in) pred_in,
3870
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
3871
from t_out where subcase = 'A.4';
3872
subcase pred_in pred_not_in
3876
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
3877
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
3878
from t_out where subcase = 'B.1';
3879
subcase pred_in pred_not_in
3883
(a1, b1, c1) IN (select * from t_in) pred_in,
3884
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
3885
from t_out where subcase = 'B.2';
3886
subcase pred_in pred_not_in
3890
(a1, b1, c1) IN (select * from t_in) pred_in,
3891
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
3892
from t_out where subcase = 'B.3';
3893
subcase pred_in pred_not_in
3897
(a1, b1, c1) IN (select * from t_in) pred_in,
3898
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
3899
from t_out where subcase = 'B.4';
3900
subcase pred_in pred_not_in
3903
Test IN as top-level predicate, and
3904
as non-top level for cases A.3, B.3 (the only cases with NULL result).
3907
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
3908
where subcase = 'A.1' and
3909
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
3912
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
3913
where subcase = 'A.1' and
3914
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
3917
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
3918
where subcase = 'A.1' and
3919
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
3923
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
3924
where subcase = 'A.3' and
3925
(a1, b1, c1) IN (select * from t_in);
3928
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
3929
where subcase = 'A.3' and
3930
(a1, b1, c1) NOT IN (select * from t_in);
3933
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
3934
where subcase = 'A.3' and
3935
NOT((a1, b1, c1) IN (select * from t_in));
3938
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
3939
where subcase = 'A.3' and
3940
((a1, b1, c1) IN (select * from t_in)) is NULL and
3941
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
3945
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
3946
where subcase = 'A.4' and
3947
(a1, b1, c1) IN (select * from t_in);
3950
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
3951
where subcase = 'A.4' and
3952
(a1, b1, c1) NOT IN (select * from t_in);
3955
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
3956
where subcase = 'A.4' and
3957
NOT((a1, b1, c1) IN (select * from t_in));
3961
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
3962
where subcase = 'B.1' and
3963
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
3966
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
3967
where subcase = 'B.1' and
3968
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
3971
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
3972
where subcase = 'B.1' and
3973
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
3977
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
3978
where subcase = 'B.2' and
3979
(a1, b1, c1) IN (select * from t_in);
3982
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
3983
where subcase = 'B.2' and
3984
(a1, b1, c1) NOT IN (select * from t_in);
3987
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
3988
where subcase = 'B.2' and
3989
NOT((a1, b1, c1) IN (select * from t_in));
3993
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
3994
where subcase = 'B.3' and
3995
(a1, b1, c1) IN (select * from t_in);
3998
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
3999
where subcase = 'B.3' and
4000
(a1, b1, c1) NOT IN (select * from t_in);
4003
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4004
where subcase = 'B.3' and
4005
NOT((a1, b1, c1) IN (select * from t_in));
4008
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4009
where subcase = 'B.3' and
4010
((a1, b1, c1) IN (select * from t_in)) is NULL and
4011
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
4015
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
4016
where subcase = 'B.4' and
4017
(a1, b1, c1) IN (select * from t_in);
4020
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
4021
where subcase = 'B.4' and
4022
(a1, b1, c1) NOT IN (select * from t_in);
4025
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
4026
where subcase = 'B.4' and
4027
NOT((a1, b1, c1) IN (select * from t_in));
4032
CREATE TABLE t1 (s1 char(1)) ENGINE=MyISAM;
4033
INSERT INTO t1 VALUES ('a');
4034
SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);
4038
CREATE TABLE t1( a INT ) ENGINE=MyISAM;
4039
INSERT INTO t1 VALUES (1),(2);
4040
CREATE TABLE t2( a INT, b INT ) ENGINE=MyISAM;
4042
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
4043
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2
4045
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
4046
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
4048
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
4049
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
4053
SELECT a INTO @var FROM t1 WHERE a = 2
4055
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2
4060
SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
4062
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2
4067
SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
4069
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2
4071
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
4075
SELECT a FROM t1 WHERE a = 2
4077
SELECT a FROM t1 WHERE a = 2
4084
SELECT a FROM t1 WHERE a = 2
4086
SELECT a FROM t1 WHERE a = 2
4091
SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
4092
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')' at line 1
4093
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
4096
SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;
4099
SELECT * FROM ((SELECT 1 a INTO @a)) t1a;
4100
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)) t1a' at line 1
4101
SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;
4102
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1
4103
SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;
4104
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1
4105
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;
4106
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)) t1a' at line 1
4107
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;
4108
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1
4109
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;
4110
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1
4111
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;
4112
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a))) t1a' at line 1
4113
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;
4114
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' ))) t1a' at line 1
4115
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;
4116
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' ))) t1a' at line 1
4117
SELECT * FROM (SELECT 1 a ORDER BY a) t1a;
4120
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a;
4123
SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a;
4126
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
4129
SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;
4133
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
4134
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')) ON 1' at line 1
4135
SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;
4136
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'ON 1' at line 1
4137
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
4138
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ') ON 1' at line 1
4139
SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1;
4140
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 't1a ON 1' at line 1
4141
SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;
4142
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 't1a ON 1' at line 1
4143
SELECT * FROM t1 JOIN (t1 t1a) ON 1;
4149
SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;
4155
SELECT * FROM (t1 t1a);
4159
SELECT * FROM ((t1 t1a));
4163
SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1;
4167
SELECT * FROM t1 JOIN ((SELECT 1 t1a)) alias ON 1;
4171
SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1;
4175
SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1;
4179
SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;
4180
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 't1a2' at line 1
4181
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );
4184
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 );
4187
SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 );
4190
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);
4191
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)' at line 1
4192
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4193
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4194
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4195
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4196
SELECT * FROM t1 WHERE a = ( SELECT 1 );
4199
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 );
4202
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);
4203
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)' at line 1
4204
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );
4205
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4206
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );
4207
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4208
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);
4209
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)' at line 1
4210
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4211
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4212
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4213
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4214
SELECT ( SELECT 1 INTO @v );
4215
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1
4216
SELECT ( SELECT 1 INTO OUTFILE 'file' );
4217
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4218
SELECT ( SELECT 1 INTO DUMPFILE 'file' );
4219
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4220
SELECT ( SELECT 1 UNION SELECT 1 INTO @v );
4221
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1
4222
SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4223
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4224
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4225
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4226
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
4227
( SELECT a FROM t1 WHERE a = 1 ) a
4230
SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1;
4231
( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a
4234
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
4236
SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );
4237
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4238
( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
4239
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1' at line 1
4240
SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4241
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4242
SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
4243
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1' at line 1
4244
SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4245
( SELECT 1 UNION SELECT 1 UNION SELECT 1 )
4247
SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
4248
((SELECT 1 UNION SELECT 1 UNION SELECT 1))
4250
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4251
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4252
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4253
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ') UNION SELECT 1 )' at line 1
4254
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
4257
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4258
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4259
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4260
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4261
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4262
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4263
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4264
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4265
SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4266
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4267
SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4268
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1
4269
SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4270
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1
4271
SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4272
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4273
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4276
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4279
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4282
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4285
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );
4286
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1
4287
SELECT EXISTS(SELECT 1+1);
4290
SELECT EXISTS(SELECT 1+1 INTO @test);
4291
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @test)' at line 1
4292
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );
4293
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1
4294
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );
4295
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1
4296
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
4297
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1
4299
set optimizer_switch=0;
4300
show variables like 'optimizer_switch';
added
removed
tests/r/subselect_no_semijoin.result
