← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/r/subselect_no_opts.result

  • Committer: Brian Aker
  • Date: 2010-01-27 18:58:12 UTC
  • Revision ID: brian@gaz-20100127185812-n62n0vwetnx8jrjy
Remove dead code.

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/r/subselect_no_opts.result
2
2
Variable_name   Value
3
3
optimizer_switch        
4
4
set optimizer_switch='no_materialization,no_semijoin';
 
5
# Initialise
5
6
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
6
7
select (select 2);
7
8
(select 2)
11
12
1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
12
13
Warnings:
13
14
Note    1249    Select 2 was reduced during optimization
14
 
Note    1003    select 2 AS "(select 2)"
 
15
Note    1003    select 2 AS `(select 2)`
15
16
SELECT (SELECT 1) UNION SELECT (SELECT 2);
16
17
(SELECT 1)
17
18
1
24
25
Warnings:
25
26
Note    1249    Select 2 was reduced during optimization
26
27
Note    1249    Select 4 was reduced during optimization
27
 
Note    1003    select 1 AS "(SELECT 1)" union select 2 AS "(SELECT 2)"
 
28
Note    1003    select 1 AS `(SELECT 1)` union select 2 AS `(SELECT 2)`
28
29
SELECT (SELECT (SELECT 0 UNION SELECT 0));
29
30
(SELECT (SELECT 0 UNION SELECT 0))
30
31
0
36
37
NULL    UNION RESULT    <union3,4>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
37
38
Warnings:
38
39
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
Note    1003    select (select 0 AS `0` union select 0 AS `0`) AS `(SELECT (SELECT 0 UNION SELECT 0))`
40
41
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
41
42
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
42
43
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;
54
55
Warnings:
55
56
Note    1276    Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
56
57
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
Note    1003    select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1)
58
59
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
59
60
1
60
61
1
76
77
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
77
78
1
78
79
1
79
 
select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
80
 
ERROR HY000: Incorrect usage of PROCEDURE and subquery
81
 
SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
82
 
ERROR HY000: Incorrect parameters to procedure 'ANALYSE'
83
80
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
84
81
ERROR 42S22: Unknown column 'a' in 'field list'
85
82
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
185
182
3       8
186
183
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);
187
184
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
188
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
189
 
2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
190
 
3       UNION   t4      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
191
 
4       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    2       100.00  
192
 
NULL    UNION RESULT    <union1,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
 
185
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
186
2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using filesort
 
187
3       UNION   t4      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
188
4       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    #       100.00  
 
189
NULL    UNION RESULT    <union1,3>      ALL     NULL    NULL    NULL    NULL    #       NULL    
193
190
Warnings:
194
 
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
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`)
195
192
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
196
193
(select a from t3 where a<t2.a*4 order by 1 desc limit 1)       a
197
194
3       1
203
200
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
204
201
(select * from t2 where a>1) as tt;
205
202
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
206
 
1       PRIMARY <derived3>      system  NULL    NULL    NULL    NULL    1       100.00  
207
 
3       DERIVED t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
208
 
2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using filesort
 
203
1       PRIMARY <derived3>      system  NULL    NULL    NULL    NULL    #       100.00  
 
204
3       DERIVED t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
205
2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where; Using filesort
209
206
Warnings:
210
 
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
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`
211
208
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);
212
209
a
213
210
2
223
220
9       7.5000
224
221
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
225
222
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
226
 
1       PRIMARY t4      ALL     NULL    NULL    NULL    NULL    3       100.00  
227
 
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  
228
 
3       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
223
1       PRIMARY t4      ALL     NULL    NULL    NULL    NULL    #       100.00  
 
224
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    #       100.00  
 
225
3       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
229
226
Warnings:
230
227
Note    1276    Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
231
 
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
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`
232
229
select * from t3 where exists (select * from t2 where t2.b=t3.a);
233
230
a
234
231
7
250
247
a
251
248
6
252
249
3
 
250
# Rewrite: select * from t3 where not exists (select b from t2 where a <> b);
253
251
select * from t3 where a = all (select b from t2);
254
252
a
255
253
7
271
269
7
272
270
explain extended select * from t3 where a >= any (select b from t2);
273
271
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
274
 
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
275
 
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    3       100.00  
 
272
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
273
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    #       100.00  
276
274
Warnings:
277
 
Note    1003    select "test"."t3"."a" AS "a" from "test"."t3" where <nop>(("test"."t3"."a" >= (select min("test"."t2"."b") from "test"."t2")))
 
275
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
278
276
select * from t3 where a >= all (select b from t2);
279
277
a
280
278
7
284
282
select * from t3 where a in (select * from t2);
285
283
ERROR 21000: Operand should contain 1 column(s)
286
284
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
 
285
# empty set
287
286
select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);
288
287
b       ma
289
288
insert into t2 values (2,10);
294
293
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);
295
294
b       ma
296
295
7       12
297
 
create table t5 (a int);
 
296
create temporary table t5 (a int) ENGINE=MyISAM;
298
297
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
299
298
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)       a
300
299
NULL    1
311
310
2       2
312
311
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;
313
312
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
314
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  
315
 
2       DEPENDENT SUBQUERY      t1      system  NULL    NULL    NULL    NULL    1       100.00  
316
 
3       DEPENDENT UNION t5      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
317
 
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
 
313
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    #       100.00  
 
314
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
315
3       DEPENDENT UNION t5      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
316
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    #       NULL    
318
317
Warnings:
319
318
Note    1276    Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
320
319
Note    1276    Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
321
 
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"
 
320
Note    1003    select (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = `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`
322
321
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
323
322
ERROR 21000: Subquery returns more than 1 row
324
 
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
325
 
create table t7( uq int primary key, name char(25));
 
323
create temporary table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)) ENGINE=MyISAM;
 
324
create temporary table t7( uq int primary key, name char(25)) ENGINE=MyISAM;
326
325
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
327
326
insert into t6 values (1,1),(1,2),(2,2),(1,3);
328
327
select * from t6 where exists (select * from t7 where uq = clinic_uq);
336
335
2       DEPENDENT SUBQUERY      t7      eq_ref  PRIMARY PRIMARY 4       test.t6.clinic_uq       1       100.00  Using index
337
336
Warnings:
338
337
Note    1276    Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
339
 
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"))
 
338
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`))
 
339
# not unique fields
340
340
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
341
341
ERROR 23000: Column 'a' in field list is ambiguous
 
342
# different tipes & group functions
342
343
drop table t1,t2,t3;
343
344
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
344
345
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
345
346
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
346
347
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
347
 
CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00');
 
348
CREATE TABLE t1 (a varchar(20),b date NULL);
348
349
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
349
350
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
350
351
a       b
356
357
a       b
357
358
W       a
358
359
CREATE TABLE `t8` (
359
 
`pseudo` varchar(35) character set latin1 NOT NULL default '',
360
 
`email` varchar(60) character set latin1 NOT NULL default '',
 
360
`pseudo` varchar(35) NOT NULL default '',
 
361
`email` varchar(60) NOT NULL default '',
361
362
PRIMARY KEY  (`pseudo`),
362
363
UNIQUE KEY `email` (`email`)
363
 
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
 
364
);
364
365
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
365
366
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
366
367
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
367
368
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');
368
369
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
369
 
1       PRIMARY t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
370
 
4       SUBQUERY        t8      const   PRIMARY PRIMARY 37              1       100.00  Using index
371
 
2       SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  
372
 
3       SUBQUERY        t8      const   PRIMARY PRIMARY 37              1       100.00  Using index
 
370
1       PRIMARY t8      const   PRIMARY PRIMARY 142     const   #       100.00  Using index
 
371
4       SUBQUERY        t8      const   PRIMARY PRIMARY 142             #       100.00  Using index
 
372
2       SUBQUERY        t8      const   PRIMARY PRIMARY 142     const   #       100.00  
 
373
3       SUBQUERY        t8      const   PRIMARY PRIMARY 142             #       100.00  Using index
373
374
Warnings:
374
 
Note    1003    select 'joce' AS "pseudo",(select 'test' AS "email" from "test"."t8" where 1) AS "(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))" from "test"."t8" where 1
 
375
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')))
375
376
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
376
377
t8 WHERE pseudo='joce');
377
378
ERROR 21000: Operand should contain 1 column(s)
384
385
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
385
386
ERROR 21000: Subquery returns more than 1 row
386
387
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
387
 
CREATE TABLE `t1` (
388
 
`topic` mediumint(8) unsigned NOT NULL default '0',
389
 
`date` date NOT NULL default '0000-00-00',
390
 
`pseudo` varchar(35) character set latin1 NOT NULL default '',
 
388
#searchconthardwarefr3 forumconthardwarefr7
 
389
CREATE TEMPORARY TABLE `t1` (
 
390
`topic` bigint NOT NULL default '0',
 
391
`date` date NULL,
 
392
`pseudo` varchar(35) NOT NULL default '',
391
393
PRIMARY KEY  (`pseudo`,`date`,`topic`),
392
394
KEY `topic` (`topic`)
393
395
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
395
397
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
396
398
EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
397
399
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
398
 
1       SIMPLE  t1      index   NULL    PRIMARY 43      NULL    2       100.00  Using where; Using index
 
400
1       SIMPLE  t1      index   NULL    PRIMARY 153     NULL    2       100.00  Using where; Using index
399
401
Warnings:
400
 
Note    1003    select distinct "test"."t1"."date" AS "date" from "test"."t1" where ("test"."t1"."date" = '2002-08-03')
 
402
Note    1003    select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03')
401
403
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
402
404
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
403
405
1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
404
 
2       SUBQUERY        t1      index   NULL    PRIMARY 43      NULL    2       100.00  Using where; Using index
 
406
2       SUBQUERY        t1      index   NULL    PRIMARY 153     NULL    2       100.00  Using where; Using index
405
407
Warnings:
406
 
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')"
 
408
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')`
407
409
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
408
410
date
409
411
2002-08-03
419
421
ERROR 21000: Subquery returns more than 1 row
420
422
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
421
423
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
422
 
1       PRIMARY t1      index   NULL    topic   3       NULL    2       100.00  Using index
 
424
1       PRIMARY t1      index   NULL    topic   8       NULL    2       100.00  Using index
423
425
2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
424
426
3       UNION   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
425
427
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
426
428
Warnings:
427
 
Note    1003    select 1 AS "1" from "test"."t1" where 1
 
429
Note    1003    select 1 AS `1` from `test`.`t1` where 1
428
430
drop table t1;
 
431
#forumconthardwarefr7 searchconthardwarefr7
429
432
CREATE TABLE `t1` (
430
 
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
431
 
`maxnumrep` int(10) unsigned NOT NULL default '0',
 
433
`numeropost` bigint NOT NULL auto_increment,
 
434
`maxnumrep` int NOT NULL default '0',
432
435
PRIMARY KEY  (`numeropost`),
433
436
UNIQUE KEY `maxnumrep` (`maxnumrep`)
434
 
) ENGINE=MyISAM ROW_FORMAT=FIXED;
 
437
);
435
438
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
436
439
CREATE TABLE `t2` (
437
440
`mot` varchar(30) NOT NULL default '',
438
 
`topic` mediumint(8) unsigned NOT NULL default '0',
439
 
`date` date NOT NULL default '0000-00-00',
 
441
`topic` bigint NOT NULL default '0',
 
442
`date` date NULL,
440
443
`pseudo` varchar(35) NOT NULL default '',
441
444
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`)
442
 
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
 
445
);
443
446
INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
444
447
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
445
448
a
498
501
joce    40143   2002-10-22      joce    1
499
502
joce    43506   2002-10-22      joce    0
500
503
drop table t1,t2;
 
504
#forumconthardwarefr7
501
505
CREATE TABLE `t1` (
502
 
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
503
 
`maxnumrep` int(10) unsigned NOT NULL default '0',
 
506
`numeropost` bigint NOT NULL auto_increment,
 
507
`maxnumrep` int NOT NULL default '0',
504
508
PRIMARY KEY  (`numeropost`),
505
509
UNIQUE KEY `maxnumrep` (`maxnumrep`)
506
 
) ENGINE=MyISAM ROW_FORMAT=FIXED;
 
510
);
507
511
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
508
512
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
509
513
ERROR 21000: Subquery returns more than 1 row
518
522
2
519
523
3
520
524
drop table t1;
521
 
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
 
525
#iftest
 
526
CREATE TEMPORARY TABLE t1 (field char(1) NOT NULL DEFAULT 'b') ENGINE=MyISAM;
522
527
INSERT INTO t1 VALUES ();
523
528
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
524
529
ERROR 21000: Subquery returns more than 1 row
525
530
drop table t1;
 
531
# threadhardwarefr7
526
532
CREATE TABLE `t1` (
527
 
`numeropost` mediumint(8) unsigned NOT NULL default '0',
528
 
`numreponse` int(10) unsigned NOT NULL auto_increment,
 
533
`numeropost` bigint NOT NULL default '0',
 
534
`numreponse` int NOT NULL auto_increment,
529
535
`pseudo` varchar(35) NOT NULL default '',
530
536
PRIMARY KEY  (`numeropost`,`numreponse`),
531
537
UNIQUE KEY `numreponse` (`numreponse`),
532
538
KEY `pseudo` (`pseudo`,`numeropost`)
533
 
) ENGINE=MyISAM;
 
539
);
534
540
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
535
541
ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list)
536
542
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
542
548
ERROR 21000: Subquery returns more than 1 row
543
549
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
544
550
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
545
 
1       SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 
551
1       SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    #       NULL    Select tables optimized away
546
552
Warnings:
547
 
Note    1003    select max("test"."t1"."numreponse") AS "MAX(numreponse)" from "test"."t1" where ("test"."t1"."numeropost" = '1')
 
553
Note    1003    select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)
548
554
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
549
555
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
550
 
1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using index
551
 
2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 
556
1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 12      const,const     #       100.00  Using index
 
557
2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    #       NULL    Select tables optimized away
552
558
Warnings:
553
 
Note    1003    select '3' AS "numreponse" from "test"."t1" where (('1' = '1'))
 
559
Note    1003    select '3' AS `numreponse` from `test`.`t1` where 1
554
560
drop table t1;
555
 
CREATE TABLE t1 (a int(1));
 
561
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
556
562
INSERT INTO t1 VALUES (1);
557
563
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
558
564
1
559
565
1
560
566
drop table t1;
 
567
#update with subselects
561
568
create table t1 (a int NOT NULL, b int, primary key (a));
562
569
create table t2 (a int NOT NULL, b int, primary key (a));
563
570
insert into t1 values (0, 10),(1, 11),(2, 12);
578
585
1       21
579
586
2       22
580
587
drop table t1, t2;
 
588
#delete with subselects
581
589
create table t1 (a int NOT NULL, b int, primary key (a));
582
590
create table t2 (a int NOT NULL, b int, primary key (a));
583
591
insert into t1 values (0, 10),(1, 11),(2, 12);
600
608
0       10
601
609
1       11
602
610
drop table t1, t2;
603
 
create table t11 (a int NOT NULL, b int, primary key (a));
604
 
create table t12 (a int NOT NULL, b int, primary key (a));
605
 
create table t2 (a int NOT NULL, b int, primary key (a));
606
 
insert into t11 values (0, 10),(1, 11),(2, 12);
607
 
insert into t12 values (33, 10),(22, 11),(2, 12);
608
 
insert into t2 values (1, 21),(2, 12),(3, 23);
609
 
select * from t11;
610
 
a       b
611
 
0       10
612
 
1       11
613
 
2       12
614
 
select * from t12;
615
 
a       b
616
 
33      10
617
 
22      11
618
 
2       12
619
 
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
620
 
ERROR HY000: You can't specify target table 't12' for update in FROM clause
621
 
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
622
 
ERROR 21000: Subquery returns more than 1 row
623
 
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
624
 
select * from t11;
625
 
a       b
626
 
0       10
627
 
1       11
628
 
select * from t12;
629
 
a       b
630
 
33      10
631
 
22      11
632
 
drop table t11, t12, t2;
 
611
#insert with subselects
633
612
CREATE TABLE t1 (x int);
634
613
create table t2 (a int);
635
614
create table t3 (b int);
644
623
x
645
624
1
646
625
insert into t2 values (1);
647
 
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
 
626
INSERT INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
648
627
select * from t1;
649
628
x
650
629
1
656
635
2
657
636
3
658
637
3
 
638
# After this, only data based on old t1 records should have been added.
659
639
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
660
640
select * from t1;
661
641
x
665
645
3
666
646
11
667
647
11
668
 
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
 
648
INSERT INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
669
649
ERROR 42S22: Unknown column 'x' in 'field list'
670
 
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
 
650
INSERT INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
671
651
select * from t1;
672
652
x
673
653
1
677
657
11
678
658
11
679
659
2
 
660
#
 
661
#TODO: should be uncommented after bug 380 fix pushed
 
662
#INSERT INTO t1 (x) SELECT (SELECT SUM(a)+b FROM t2) from t3;
 
663
#select * from t1;
680
664
drop table t1, t2, t3;
 
665
#replace with subselects
681
666
CREATE TABLE t1 (x int not null, y int, primary key (x));
682
667
create table t2 (a int);
683
 
create table t3 (a int);
 
668
create temporary table t3 (a int) ENGINE=MyISAM;
684
669
insert into t2 values (1);
685
670
insert into t3 values (1),(2);
686
671
select * from t1;
697
682
select * from t1;
698
683
x       y
699
684
1       3
700
 
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
 
685
replace into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
701
686
select * from t1;
702
687
x       y
703
688
1       3
704
689
4       1
705
 
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
706
 
select * from t1;
707
 
x       y
708
 
1       3
709
 
4       2
710
 
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
711
 
select * from t1;
712
 
x       y
713
 
1       3
714
 
4       2
 
690
replace into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
 
691
select * from t1;
 
692
x       y
 
693
1       3
 
694
4       2
 
695
replace into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
 
696
select * from t1;
 
697
x       y
 
698
1       3
715
699
2       1
 
700
4       2
716
701
drop table t1, t2, t3;
717
702
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
718
703
ERROR HY000: No tables used
719
 
CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
 
704
CREATE TABLE t2 (id int default NULL, KEY id (id));
720
705
INSERT INTO t2 VALUES (1),(2);
721
706
SELECT * FROM t2 WHERE id IN (SELECT 1);
722
707
id
726
711
1       PRIMARY t2      ref     id      id      5       const   1       100.00  Using index
727
712
Warnings:
728
713
Note    1249    Select 2 was reduced during optimization
729
 
Note    1003    select "test"."t2"."id" AS "id" from "test"."t2" where ("test"."t2"."id" = 1)
 
714
Note    1003    select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)
730
715
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
731
716
id
732
717
1
739
724
Warnings:
740
725
Note    1249    Select 3 was reduced during optimization
741
726
Note    1249    Select 2 was reduced during optimization
742
 
Note    1003    select "test"."t2"."id" AS "id" from "test"."t2" where ("test"."t2"."id" = (1 + 1))
 
727
Note    1003    select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
743
728
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
744
729
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
745
730
1       PRIMARY t2      index   NULL    id      5       NULL    2       100.00  Using where; Using index
747
732
3       DEPENDENT UNION NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
748
733
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
749
734
Warnings:
750
 
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))))
 
735
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))))
751
736
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
752
737
id
753
738
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
761
746
id
762
747
1
763
748
2
764
 
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
 
749
CREATE TEMPORARY TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
765
750
INSERT INTO t1 values (1),(1);
766
751
UPDATE t2 SET id=(SELECT * FROM t1);
767
752
ERROR 21000: Subquery returns more than 1 row
768
753
drop table t2, t1;
769
 
create table t1 (a int);
 
754
#NULL test
 
755
create temporary table t1 (a int) ENGINE=MyISAM;
770
756
insert into t1 values (1),(2),(3);
771
757
select 1 IN (SELECT * from t1);
772
758
1 IN (SELECT * from t1)
800
786
10 > ANY (SELECT * from t1)
801
787
1
802
788
drop table t1;
803
 
create table t1 (a varchar(20));
 
789
create temporary table t1 (a varchar(20)) ENGINE=MyISAM;
804
790
insert into t1 values ('A'),('BC'),('DEF');
805
791
select 'A' IN (SELECT * from t1);
806
792
'A' IN (SELECT * from t1)
834
820
'XYZS' > ANY (SELECT * from t1)
835
821
1
836
822
drop table t1;
837
 
create table t1 (a float);
 
823
create temporary table t1 (a float) ENGINE=MyISAM;
838
824
insert into t1 values (1.5),(2.5),(3.5);
839
825
select 1.5 IN (SELECT * from t1);
840
826
1.5 IN (SELECT * from t1)
873
859
Warnings:
874
860
Note    1276    Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
875
861
Note    1249    Select 2 was reduced during optimization
876
 
Note    1003    select ("test"."t1"."a" + 1) AS "(select a+1)" from "test"."t1"
 
862
Note    1003    select (`test`.`t1`.`a` + 1) AS `(select a+1)` from `test`.`t1`
877
863
select (select a+1) from t1;
878
864
(select a+1)
879
865
2.5
880
 
NULL
881
866
4.5
 
867
NULL
882
868
drop table t1;
883
 
CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY  (a));
884
 
CREATE TABLE t2 (a int(11) default '0', INDEX (a));
 
869
#
 
870
# Null with keys
 
871
#
 
872
CREATE TEMPORARY TABLE t1 (a int NOT NULL default '0', PRIMARY KEY  (a)) ENGINE=MyISAM;
 
873
CREATE TEMPORARY TABLE t2 (a int default '0', INDEX (a)) ENGINE=MyISAM;
885
874
INSERT INTO t1 VALUES (1),(2),(3),(4);
886
875
INSERT INTO t2 VALUES (1),(2),(3);
887
876
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
895
884
1       PRIMARY t1      index   NULL    PRIMARY 4       NULL    4       100.00  Using index
896
885
2       DEPENDENT SUBQUERY      t2      index_subquery  a       a       5       func    2       100.00  Using index
897
886
Warnings:
898
 
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"
899
 
CREATE TABLE t3 (a int(11) default '0');
 
887
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`
 
888
CREATE TEMPORARY TABLE t3 (a int default '0') ENGINE=MyISAM;
900
889
INSERT INTO t3 VALUES (1),(2),(3);
901
890
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
902
891
a       t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
910
899
2       DEPENDENT SUBQUERY      t2      ref_or_null     a       a       5       func    2       100.00  Using index
911
900
2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using join buffer
912
901
Warnings:
913
 
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"
 
902
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`
914
903
drop table t1,t2,t3;
915
 
create table t1 (a float);
916
 
select 10.5 IN (SELECT * from t1 LIMIT 1);
917
 
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
918
 
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
919
 
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
920
 
drop table t1;
921
 
create table t1 (a int, b int, c varchar(10));
922
 
create table t2 (a int);
923
 
insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
924
 
insert into t2 values (1),(2),(NULL);
925
 
select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a)  from t2;
926
 
a       (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a')   (select c from t1 where a=t2.a)
927
 
1       1       a
928
 
2       0       b
929
 
NULL    0       NULL
930
 
select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
931
 
a       (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b')   (select c from t1 where a=t2.a)
932
 
1       0       a
933
 
2       1       b
934
 
NULL    NULL    NULL
935
 
select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;
936
 
a       (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c')   (select c from t1 where a=t2.a)
937
 
1       0       a
938
 
2       0       b
939
 
NULL    0       NULL
940
 
drop table t1,t2;
941
 
create table t1 (a int, b real, c varchar(10));
942
 
insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
943
 
select ROW(1, 1, 'a') IN (select a,b,c from t1);
944
 
ROW(1, 1, 'a') IN (select a,b,c from t1)
945
 
1
946
 
select ROW(1, 2, 'a') IN (select a,b,c from t1);
947
 
ROW(1, 2, 'a') IN (select a,b,c from t1)
948
 
0
949
 
select ROW(1, 1, 'a') IN (select b,a,c from t1);
950
 
ROW(1, 1, 'a') IN (select b,a,c from t1)
951
 
1
952
 
select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
953
 
ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null)
954
 
1
955
 
select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
956
 
ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null)
957
 
0
958
 
select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
959
 
ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null)
960
 
1
961
 
select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
962
 
ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a')
963
 
1
964
 
select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
965
 
ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a')
966
 
0
967
 
select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
968
 
ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a')
969
 
1
970
 
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
971
 
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
972
 
drop table t1;
973
 
CREATE TABLE t1 (a int(1));
 
904
#LIMIT is not supported now
 
905
#create table t1 (a float) ENGINE=MyISAM;
 
906
#-- error 1235
 
907
#select 10.5 IN (SELECT * from t1 LIMIT 1);
 
908
#-- error 1235
 
909
#select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
 
910
#drop table t1;
 
911
#
 
912
#create table t1 (a int, b int, c varchar(10)) ENGINE=MyISAM;
 
913
#create table t2 (a int) ENGINE=MyISAM;
 
914
#insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
 
915
#insert into t2 values (1),(2),(NULL);
 
916
#select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a)  from t2;
 
917
#select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
 
918
#select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;
 
919
#drop table t1,t2;
 
920
#
 
921
#create table t1 (a int, b real, c varchar(10)) ENGINE=MyISAM;
 
922
#insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
 
923
#select ROW(1, 1, 'a') IN (select a,b,c from t1);
 
924
#select ROW(1, 2, 'a') IN (select a,b,c from t1);
 
925
#select ROW(1, 1, 'a') IN (select b,a,c from t1);
 
926
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
 
927
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
 
928
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
 
929
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
 
930
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
 
931
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
 
932
#-- error 1235
 
933
#select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
 
934
#drop table t1;
 
935
#test of uncacheable subqueries
 
936
CREATE TABLE t1 (a int);
974
937
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
975
938
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
976
 
1       PRIMARY t1      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
977
 
2       UNCACHEABLE SUBQUERY    t1      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
978
 
Warnings:
979
 
Note    1003    select (select rand() AS "RAND()" from "test"."t1") AS "(SELECT RAND() FROM t1)" from "test"."t1"
980
 
EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
981
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
982
 
1       PRIMARY t1      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
983
 
2       UNCACHEABLE SUBQUERY    t1      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
984
 
Warnings:
985
 
Note    1003    select (select encrypt('test') AS "ENCRYPT('test')" from "test"."t1") AS "(SELECT ENCRYPT('test') FROM t1)" from "test"."t1"
 
939
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    1       100.00  
 
940
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    1       100.00  
 
941
Warnings:
 
942
Note    1003    select (select rand() AS `RAND()` from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1`
986
943
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
987
944
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
988
 
1       PRIMARY t1      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
989
 
2       UNCACHEABLE SUBQUERY    t1      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 
945
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    1       100.00  
 
946
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    1       100.00  
990
947
Warnings:
991
 
Note    1003    select (select benchmark(1,1) AS "BENCHMARK(1,1)" from "test"."t1") AS "(SELECT BENCHMARK(1,1) FROM t1)" from "test"."t1"
 
948
Note    1003    select (select benchmark(1,1) AS `BENCHMARK(1,1)` from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1`
992
949
drop table t1;
993
 
CREATE TABLE `t1` (
994
 
`mot` varchar(30) character set latin1 NOT NULL default '',
995
 
`topic` mediumint(8) unsigned NOT NULL default '0',
996
 
`date` date NOT NULL default '0000-00-00',
997
 
`pseudo` varchar(35) character set latin1 NOT NULL default '',
998
 
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
999
 
KEY `pseudo` (`pseudo`,`date`,`topic`),
1000
 
KEY `topic` (`topic`)
1001
 
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
1002
 
CREATE TABLE `t2` (
1003
 
`mot` varchar(30) character set latin1 NOT NULL default '',
1004
 
`topic` mediumint(8) unsigned NOT NULL default '0',
1005
 
`date` date NOT NULL default '0000-00-00',
1006
 
`pseudo` varchar(35) character set latin1 NOT NULL default '',
1007
 
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
1008
 
KEY `pseudo` (`pseudo`,`date`,`topic`),
1009
 
KEY `topic` (`topic`)
1010
 
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
1011
 
CREATE TABLE `t3` (
1012
 
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
1013
 
`maxnumrep` int(10) unsigned NOT NULL default '0',
 
950
CREATE TEMPORARY TABLE `t1` (
 
951
`mot` varchar(30) NOT NULL default '',
 
952
`topic` bigint NOT NULL default '0',
 
953
`date` date NULL DEFAULT '2009-01-20',
 
954
`pseudo` varchar(35) NOT NULL default '',
 
955
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
 
956
KEY `pseudo` (`pseudo`,`date`,`topic`),
 
957
KEY `topic` (`topic`)
 
958
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
 
959
CREATE TEMPORARY TABLE `t2` (
 
960
`mot` varchar(30) NOT NULL default '',
 
961
`topic` bigint NOT NULL default '0',
 
962
`date` date NULL default '1997-08-29',
 
963
`pseudo` varchar(35) NOT NULL default '',
 
964
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
 
965
KEY `pseudo` (`pseudo`,`date`,`topic`),
 
966
KEY `topic` (`topic`)
 
967
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
 
968
CREATE TEMPORARY TABLE `t3` (
 
969
`numeropost` bigint NOT NULL auto_increment,
 
970
`maxnumrep` int NOT NULL default '0',
1014
971
PRIMARY KEY  (`numeropost`),
1015
972
UNIQUE KEY `maxnumrep` (`maxnumrep`)
1016
 
) ENGINE=MyISAM CHARSET=latin1;
 
973
) ENGINE=MyISAM;
1017
974
INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
1018
975
INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
1019
976
INSERT INTO t3 VALUES (1,1);
1023
980
2
1024
981
select * from t1;
1025
982
mot     topic   date    pseudo
1026
 
joce    1       0000-00-00      joce
1027
 
test    2       0000-00-00      test
 
983
joce    1       2009-01-20      joce
 
984
test    2       2009-01-20      test
1028
985
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
1029
986
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
1030
987
select * from t1;
1031
988
mot     topic   date    pseudo
1032
 
joce    1       0000-00-00      joce
 
989
joce    1       2009-01-20      joce
1033
990
drop table t1, t2, t3;
1034
991
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
1035
992
a       (SELECT a)
1036
993
1       1
1037
 
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
1038
 
SHOW CREATE TABLE t1;
1039
 
Table   Create Table
1040
 
t1      CREATE TABLE "t1" (
1041
 
  "a" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP,
1042
 
  "(SELECT 1)" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP
1043
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1044
 
drop table t1;
1045
 
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
1046
 
SHOW CREATE TABLE t1;
1047
 
Table   Create Table
1048
 
t1      CREATE TABLE "t1" (
1049
 
  "a" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP,
1050
 
  "(SELECT a)" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP
1051
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1052
 
drop table t1;
1053
 
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
1054
 
SHOW CREATE TABLE t1;
1055
 
Table   Create Table
1056
 
t1      CREATE TABLE "t1" (
1057
 
  "a" int(1) NOT NULL ON UPDATE CURRENT_TIMESTAMP,
1058
 
  "(SELECT a+0)" int(3) NOT NULL ON UPDATE CURRENT_TIMESTAMP
1059
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1060
 
drop table t1;
1061
 
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
 
994
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
 
995
SHOW CREATE TABLE t1;
 
996
Table   Create Table
 
997
t1      CREATE TEMPORARY TABLE `t1` (
 
998
  `a` int NOT NULL DEFAULT '0',
 
999
  `(SELECT 1)` int NOT NULL DEFAULT '0'
 
1000
) ENGINE=MyISAM
 
1001
drop table t1;
 
1002
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
 
1003
SHOW CREATE TABLE t1;
 
1004
Table   Create Table
 
1005
t1      CREATE TEMPORARY TABLE `t1` (
 
1006
  `a` int NOT NULL DEFAULT '0',
 
1007
  `(SELECT a)` int NOT NULL DEFAULT '0'
 
1008
) ENGINE=MyISAM
 
1009
drop table t1;
 
1010
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
 
1011
SHOW CREATE TABLE t1;
 
1012
Table   Create Table
 
1013
t1      CREATE TEMPORARY TABLE `t1` (
 
1014
  `a` int NOT NULL DEFAULT '0',
 
1015
  `(SELECT a+0)` int NOT NULL DEFAULT '0'
 
1016
) ENGINE=MyISAM
 
1017
drop table t1;
 
1018
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
1062
1019
select * from t1;
1063
1020
a
1064
1021
2
1065
1022
SHOW CREATE TABLE t1;
1066
1023
Table   Create Table
1067
 
t1      CREATE TABLE "t1" (
1068
 
  "a" bigint(20) NOT NULL ON UPDATE CURRENT_TIMESTAMP
1069
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
1024
t1      CREATE TEMPORARY TABLE `t1` (
 
1025
  `a` bigint NOT NULL
 
1026
) ENGINE=MyISAM
1070
1027
drop table t1;
1071
1028
create table t1 (a int);
1072
1029
insert into t1 values (1), (2), (3);
1073
1030
explain extended select a,(select (select rand() from t1 limit 1)  from t1 limit 1)
1074
1031
from t1;
1075
1032
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1076
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  
1077
 
2       UNCACHEABLE SUBQUERY    t1      ALL     NULL    NULL    NULL    NULL    3       100.00  
1078
 
3       UNCACHEABLE SUBQUERY    t1      ALL     NULL    NULL    NULL    NULL    3       100.00  
 
1033
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
 
1034
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
 
1035
3       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
1079
1036
Warnings:
1080
 
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"
 
1037
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`
1081
1038
drop table t1;
 
1039
#
 
1040
# error in IN
 
1041
#
1082
1042
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);
1083
1043
ERROR 42S02: Table 'test.t1' doesn't exist
 
1044
#
 
1045
# complex subquery
 
1046
#
1084
1047
CREATE TABLE t1 (
1085
 
ID int(11) NOT NULL auto_increment,
 
1048
ID int NOT NULL auto_increment,
1086
1049
name char(35) NOT NULL default '',
1087
1050
t2 char(3) NOT NULL default '',
1088
1051
District char(20) NOT NULL default '',
1089
 
Population int(11) NOT NULL default '0',
 
1052
Population int NOT NULL default '0',
1090
1053
PRIMARY KEY  (ID)
1091
 
) ENGINE=MyISAM;
 
1054
);
1092
1055
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
1093
1056
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
1094
1057
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
1098
1061
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
1099
1062
Region char(26) NOT NULL default '',
1100
1063
SurfaceArea float(10,2) NOT NULL default '0.00',
1101
 
IndepYear smallint(6) default NULL,
1102
 
Population int(11) NOT NULL default '0',
 
1064
IndepYear int default NULL,
 
1065
Population int NOT NULL default '0',
1103
1066
LifeExpectancy float(3,1) default NULL,
1104
1067
GNP float(10,2) default NULL,
1105
1068
GNPOld float(10,2) default NULL,
1106
1069
LocalName char(45) NOT NULL default '',
1107
1070
GovernmentForm char(45) NOT NULL default '',
1108
1071
HeadOfState char(60) default NULL,
1109
 
Capital int(11) default NULL,
 
1072
Capital int default NULL,
1110
1073
Code2 char(2) NOT NULL default '',
1111
1074
PRIMARY KEY  (Code)
1112
 
) ENGINE=MyISAM;
 
1075
);
1113
1076
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');
1114
1077
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');
1115
1078
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);
1116
1079
Continent       Name    Population
1117
1080
Oceania Sydney  3276207
1118
1081
drop table t1, t2;
1119
 
CREATE TABLE `t1` (
1120
 
`id` mediumint(8) unsigned NOT NULL auto_increment,
1121
 
`pseudo` varchar(35) character set latin1 NOT NULL default '',
 
1082
#
 
1083
# constants in IN
 
1084
#
 
1085
CREATE TEMPORARY TABLE `t1` (
 
1086
`id` bigint NOT NULL auto_increment,
 
1087
`pseudo` varchar(35) NOT NULL default '',
1122
1088
PRIMARY KEY  (`id`),
1123
1089
UNIQUE KEY `pseudo` (`pseudo`)
1124
 
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
 
1090
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
1125
1091
INSERT INTO t1 (pseudo) VALUES ('test');
1126
1092
SELECT 0 IN (SELECT 1 FROM t1 a);
1127
1093
0 IN (SELECT 1 FROM t1 a)
1131
1097
1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
1132
1098
2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
1133
1099
Warnings:
1134
 
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)"
 
1100
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)`
1135
1101
INSERT INTO t1 (pseudo) VALUES ('test1');
1136
1102
SELECT 0 IN (SELECT 1 FROM t1 a);
1137
1103
0 IN (SELECT 1 FROM t1 a)
1141
1107
1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
1142
1108
2       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
1143
1109
Warnings:
1144
 
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)"
 
1110
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)`
1145
1111
drop table t1;
1146
 
CREATE TABLE `t1` (
1147
 
`i` int(11) NOT NULL default '0',
 
1112
CREATE TEMPORARY TABLE `t1` (
 
1113
`i` int NOT NULL default '0',
1148
1114
PRIMARY KEY  (`i`)
1149
 
) ENGINE=MyISAM CHARSET=latin1;
 
1115
) ENGINE=MyISAM;
1150
1116
INSERT INTO t1 VALUES (1);
1151
1117
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
1152
1118
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
1156
1122
i
1157
1123
3
1158
1124
drop table t1;
1159
 
CREATE TABLE t1 (
1160
 
id int(11) default NULL
1161
 
) ENGINE=MyISAM CHARSET=latin1;
1162
 
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
1163
 
CREATE TABLE t2 (
1164
 
id int(11) default NULL,
1165
 
name varchar(15) default NULL
1166
 
) ENGINE=MyISAM CHARSET=latin1;
1167
 
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
1168
 
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
1169
 
select * from t2;
1170
 
id      name
1171
 
4       vita
1172
 
1       lenka
1173
 
2       lenka
1174
 
1       lenka
1175
 
drop table t1,t2;
1176
 
create table t1 (a int, unique index indexa (a));
 
1125
#
 
1126
# correct NULL in <CONSTANT> IN (SELECT ...)
 
1127
#
 
1128
create temporary table t1 (a int, unique index indexa (a)) ENGINE=MyISAM;
1177
1129
insert into t1 values (-1), (-4), (-2), (NULL);
1178
1130
select -10 IN (select a from t1 FORCE INDEX (indexa));
1179
1131
-10 IN (select a from t1 FORCE INDEX (indexa))
1180
1132
NULL
1181
1133
drop table t1;
 
1134
#
 
1135
# Test optimization for sub selects
 
1136
#
1182
1137
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
1183
1138
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
1184
1139
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
1185
1140
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1186
 
1       PRIMARY t1      ref     salary  salary  5       const   1       100.00  Using index condition
1187
 
2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 
1141
1       PRIMARY t1      ref     salary  salary  5       const   #       100.00  Using where; Using index
 
1142
2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    #       NULL    Select tables optimized away
1188
1143
Warnings:
1189
 
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"))
 
1144
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`))
1190
1145
drop table t1;
1191
1146
CREATE TABLE t1 (
1192
 
ID int(10) unsigned NOT NULL auto_increment,
1193
 
SUB_ID int(3) unsigned NOT NULL default '0',
1194
 
REF_ID int(10) unsigned default NULL,
1195
 
REF_SUB int(3) unsigned default '0',
 
1147
ID int NOT NULL auto_increment,
 
1148
SUB_ID int NOT NULL default '0',
 
1149
REF_ID int default NULL,
 
1150
REF_SUB int default '0',
1196
1151
PRIMARY KEY (ID,SUB_ID),
1197
1152
UNIQUE KEY t1_PK (ID,SUB_ID),
1198
1153
KEY t1_FK (REF_ID,REF_SUB),
1199
1154
KEY t1_REFID (REF_ID)
1200
 
) ENGINE=MyISAM CHARSET=cp1251;
 
1155
);
1201
1156
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
1202
1157
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
1203
1158
REF_ID
1204
1159
DROP TABLE t1;
1205
 
create table t1 (a int, b int);
1206
 
create table t2 (a int, b int);
 
1160
#
 
1161
# uninterruptable update
 
1162
#
 
1163
create temporary table t1 (a int, b int) ENGINE=MyISAM;
 
1164
create temporary table t2 (a int, b int) ENGINE=MyISAM;
1207
1165
insert into t1 values (1,0), (2,0), (3,0);
1208
1166
insert into t2 values (1,1), (2,1), (3,1), (2,2);
1209
1167
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
1215
1173
2       NULL
1216
1174
3       1
1217
1175
drop table t1, t2;
1218
 
CREATE TABLE `t1` (
1219
 
`id` mediumint(8) unsigned NOT NULL auto_increment,
 
1176
#
 
1177
# reduced subselect in ORDER BY & GROUP BY clauses
 
1178
#
 
1179
CREATE TEMPORARY TABLE `t1` (
 
1180
`id` bigint NOT NULL auto_increment,
1220
1181
`pseudo` varchar(35) NOT NULL default '',
1221
1182
`email` varchar(60) NOT NULL default '',
1222
1183
PRIMARY KEY  (`id`),
1223
1184
UNIQUE KEY `email` (`email`),
1224
1185
UNIQUE KEY `pseudo` (`pseudo`)
1225
 
) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
 
1186
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
1226
1187
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
1227
1188
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
1228
1189
a       b
1232
1193
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
1233
1194
a
1234
1195
1
1235
 
create table t1 (a int not null, b int, primary key (a));
1236
 
create table t2 (a int not null, primary key (a));
1237
 
create table t3 (a int not null, b int, primary key (a));
 
1196
#
 
1197
# IN subselect optimization test
 
1198
#
 
1199
create temporary table t1 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
 
1200
create temporary table t2 (a int not null, primary key (a)) ENGINE=MyISAM;
 
1201
create temporary table t3 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
1238
1202
insert into t1 values (1,10), (2,20), (3,30),  (4,40);
1239
1203
insert into t2 values (2), (3), (4), (5);
1240
1204
insert into t3 values (10,3), (20,4), (30,5);
1248
1212
1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Using index
1249
1213
2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index
1250
1214
Warnings:
1251
 
Note    1003    select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a",<exists>(<primary_index_lookup>(<cache>("test"."t2"."a") in t1 on PRIMARY)))
 
1215
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY)))
1252
1216
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1253
1217
a
1254
1218
2
1258
1222
1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Using index
1259
1223
2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using where
1260
1224
Warnings:
1261
 
Note    1003    select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a",<exists>(<primary_index_lookup>(<cache>("test"."t2"."a") in t1 on PRIMARY where (("test"."t1"."b" <> 30) and (<cache>("test"."t2"."a") = "test"."t1"."a")))))
 
1225
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
1262
1226
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1263
1227
a
1264
1228
2
1269
1233
2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       func    1       100.00  
1270
1234
2       DEPENDENT SUBQUERY      t3      eq_ref  PRIMARY PRIMARY 4       test.t1.b       1       100.00  Using index
1271
1235
Warnings:
1272
 
Note    1003    select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a",<exists>(select 1 AS "Not_used" from "test"."t1" join "test"."t3" where (("test"."t3"."a" = "test"."t1"."b") and (<cache>("test"."t2"."a") = "test"."t1"."a"))))
 
1236
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
1273
1237
drop table t1, t2, t3;
1274
 
create table t1 (a int, b int, index a (a,b));
1275
 
create table t2 (a int, index a (a));
1276
 
create table t3 (a int, b int, index a (a));
 
1238
create temporary table t1 (a int, b int, index a (a,b)) ENGINE=MyISAM;
 
1239
create temporary table t2 (a int, index a (a)) ENGINE=MyISAM;
 
1240
create temporary table t3 (a int, b int, index a (a)) ENGINE=MyISAM;
1277
1241
insert into t1 values (1,10), (2,20), (3,30), (4,40);
 
1242
# making table large enough
1278
1243
insert into t2 values (2), (3), (4), (5);
1279
1244
insert into t3 values (10,3), (20,4), (30,5);
1280
1245
select * from t2 where t2.a in (select a from t1);
1287
1252
1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
1288
1253
2       DEPENDENT SUBQUERY      t1      index_subquery  a       a       5       func    1001    100.00  Using index
1289
1254
Warnings:
1290
 
Note    1003    select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a",<exists>(<index_lookup>(<cache>("test"."t2"."a") in t1 on a)))
 
1255
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a)))
1291
1256
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1292
1257
a
1293
1258
2
1297
1262
1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
1298
1263
2       DEPENDENT SUBQUERY      t1      index_subquery  a       a       5       func    1001    100.00  Using index; Using where
1299
1264
Warnings:
1300
 
Note    1003    select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a",<exists>(<index_lookup>(<cache>("test"."t2"."a") in t1 on a where (("test"."t1"."b" <> 30) and (<cache>("test"."t2"."a") = "test"."t1"."a")))))
 
1265
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
1301
1266
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1302
1267
a
1303
1268
2
1308
1273
2       DEPENDENT SUBQUERY      t1      ref     a       a       5       func    1001    100.00  Using index
1309
1274
2       DEPENDENT SUBQUERY      t3      index   a       a       5       NULL    3       100.00  Using where; Using index; Using join buffer
1310
1275
Warnings:
1311
 
Note    1003    select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a",<exists>(select 1 AS "Not_used" from "test"."t1" join "test"."t3" where (("test"."t3"."a" = "test"."t1"."b") and (<cache>("test"."t2"."a") = "test"."t1"."a"))))
 
1276
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
1312
1277
insert into t1 values (3,31);
1313
1278
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1314
1279
a
1324
1289
1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
1325
1290
2       DEPENDENT SUBQUERY      t1      index_subquery  a       a       5       func    1001    100.00  Using index; Using where
1326
1291
Warnings:
1327
 
Note    1003    select "test"."t2"."a" AS "a" from "test"."t2" where <in_optimizer>("test"."t2"."a",<exists>(<index_lookup>(<cache>("test"."t2"."a") in t1 on a where (("test"."t1"."b" <> 30) and (<cache>("test"."t2"."a") = "test"."t1"."a")))))
 
1292
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
1328
1293
drop table t1, t2, t3;
1329
 
create table t1 (a int, b int);
1330
 
create table t2 (a int, b int);
1331
 
create table t3 (a int, b int);
 
1294
#
 
1295
# alloc_group_fields() working
 
1296
#
 
1297
create temporary table t1 (a int, b int) ENGINE=MyISAM;
 
1298
create temporary table t2 (a int, b int) ENGINE=MyISAM;
 
1299
create temporary table t3 (a int, b int) ENGINE=MyISAM;
1332
1300
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
1333
1301
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
1334
1302
insert into t3 values (3,3), (2,2), (1,1);
1338
1306
2       2
1339
1307
1       2
1340
1308
drop table t1,t2,t3;
1341
 
create table t1 (s1 int);
1342
 
create table t2 (s1 int);
 
1309
#
 
1310
# aggregate functions in HAVING test
 
1311
#
 
1312
create temporary table t1 (s1 int) ENGINE=MyISAM;
 
1313
create temporary table t2 (s1 int) ENGINE=MyISAM;
1343
1314
insert into t1 values (1);
1344
1315
insert into t2 values (1);
1345
1316
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
1346
1317
s1
1347
1318
1
1348
1319
drop table t1,t2;
1349
 
create table t1 (s1 int);
1350
 
create table t2 (s1 int);
 
1320
#
 
1321
# update subquery with wrong field (to force name resolving
 
1322
# in UPDATE name space)
 
1323
#
 
1324
create temporary table t1 (s1 int) ENGINE=MyISAM;
 
1325
create temporary table t2 (s1 int) ENGINE=MyISAM;
1351
1326
insert into t1 values (1);
1352
1327
insert into t2 values (1);
1353
1328
update t1 set  s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
1354
1329
ERROR 42S22: Unknown column 'x.s1' in 'field list'
1355
1330
DROP TABLE t1, t2;
1356
 
CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
1357
 
s2 CHAR(5) COLLATE latin1_swedish_ci);
1358
 
INSERT INTO t1 VALUES ('z','?');
1359
 
select * from t1 where s1 > (select max(s2) from t1);
1360
 
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
1361
 
select * from t1 where s1 > any (select max(s2) from t1);
1362
 
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
1363
 
drop table t1;
 
1331
#
 
1332
# collation test
 
1333
#
 
1334
#CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
 
1335
#                 s2 CHAR(5) COLLATE latin1_swedish_ci) ENGINE=MyISAM;
 
1336
#INSERT INTO t1 VALUES ('z','?');
 
1337
#-- error 1267
 
1338
#select * from t1 where s1 > (select max(s2) from t1);
 
1339
#-- error 1267
 
1340
#select * from t1 where s1 > any (select max(s2) from t1);
 
1341
#drop table t1;
 
1342
#
 
1343
# aggregate functions reinitialization
 
1344
#
1364
1345
create table t1(toid int,rd int);
1365
1346
create table t2(userid int,pmnew int,pmtotal int);
1366
1347
insert into t2 values(1,0,0),(2,0,0);
1370
1351
1       0       0       9       3
1371
1352
2       0       0       4       2
1372
1353
drop table t1, t2;
 
1354
#
 
1355
# row union
 
1356
#
1373
1357
create table t1 (s1 char(5));
1374
1358
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
1375
1359
ERROR 21000: Operand should contain 1 column(s)
1379
1363
tttt
1380
1364
explain extended (select * from t1);
1381
1365
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1382
 
1       SIMPLE  t1      system  NULL    NULL    NULL    NULL    1       100.00  
 
1366
1       SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
1383
1367
Warnings:
1384
 
Note    1003    (select 'tttt' AS "s1" from "test"."t1")
 
1368
Note    1003    (select `test`.`t1`.`s1` AS `s1` from `test`.`t1`)
1385
1369
(select * from t1);
1386
1370
s1
1387
1371
tttt
1388
1372
drop table t1;
1389
 
create table t1 (s1 char(5), index s1(s1));
1390
 
create table t2 (s1 char(5), index s1(s1));
 
1373
#
 
1374
# IN optimisation test results
 
1375
#
 
1376
create temporary table t1 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
 
1377
create temporary table t2 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
1391
1378
insert into t1 values ('a1'),('a2'),('a3');
1392
1379
insert into t2 values ('a1'),('a2');
1393
1380
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1412
1399
a3      1
1413
1400
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1414
1401
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1415
 
1       PRIMARY t1      index   NULL    s1      6       NULL    3       100.00  Using index
1416
 
2       DEPENDENT SUBQUERY      t2      index_subquery  s1      s1      6       func    2       100.00  Using index; Full scan on NULL key
 
1402
1       PRIMARY t1      index   NULL    s1      23      NULL    3       100.00  Using index
 
1403
2       DEPENDENT SUBQUERY      t2      index_subquery  s1      s1      23      func    2       100.00  Using index; Full scan on NULL key
1417
1404
Warnings:
1418
 
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"
 
1405
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`
1419
1406
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
1420
1407
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1421
 
1       PRIMARY t1      index   NULL    s1      6       NULL    3       100.00  Using index
1422
 
2       DEPENDENT SUBQUERY      t2      index_subquery  s1      s1      6       func    2       100.00  Using index; Full scan on NULL key
 
1408
1       PRIMARY t1      index   NULL    s1      23      NULL    3       100.00  Using index
 
1409
2       DEPENDENT SUBQUERY      t2      index_subquery  s1      s1      23      func    2       100.00  Using index; Full scan on NULL key
1423
1410
Warnings:
1424
 
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"
 
1411
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`
1425
1412
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
1426
1413
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1427
 
1       PRIMARY t1      index   NULL    s1      6       NULL    3       100.00  Using index
1428
 
2       DEPENDENT SUBQUERY      t2      index_subquery  s1      s1      6       func    2       100.00  Using index; Full scan on NULL key
 
1414
1       PRIMARY t1      index   NULL    s1      23      NULL    3       100.00  Using index
 
1415
2       DEPENDENT SUBQUERY      t2      index_subquery  s1      s1      23      func    2       100.00  Using index; Full scan on NULL key
1429
1416
Warnings:
1430
 
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"
 
1417
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`
1431
1418
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
1432
1419
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1433
 
1       PRIMARY t1      index   NULL    s1      6       NULL    3       100.00  Using index
1434
 
2       DEPENDENT SUBQUERY      t2      index_subquery  s1      s1      6       func    2       100.00  Using index; Using where; Full scan on NULL key
 
1420
1       PRIMARY t1      index   NULL    s1      23      NULL    3       100.00  Using index
 
1421
2       DEPENDENT SUBQUERY      t2      index_subquery  s1      s1      23      func    2       100.00  Using index; Using where; Full scan on NULL key
1435
1422
Warnings:
1436
 
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"
 
1423
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`
1437
1424
drop table t1,t2;
1438
 
create table t2 (a int, b int);
1439
 
create table t3 (a int);
 
1425
#
 
1426
# correct ALL optimisation
 
1427
#
 
1428
create temporary table t2 (a int, b int) ENGINE=MyISAM;
 
1429
create temporary table t3 (a int) ENGINE=MyISAM;
1440
1430
insert into t3 values (6),(7),(3);
1441
1431
select * from t3 where a >= all (select b from t2);
1442
1432
a
1448
1438
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
1449
1439
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
1450
1440
Warnings:
1451
 
Note    1003    select "test"."t3"."a" AS "a" from "test"."t3" where <not>(("test"."t3"."a" < (select max('0') from "test"."t2")))
 
1441
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max('0') from `test`.`t2`)))
1452
1442
select * from t3 where a >= some (select b from t2);
1453
1443
a
1454
1444
explain extended select * from t3 where a >= some (select b from t2);
1456
1446
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
1457
1447
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
1458
1448
Warnings:
1459
 
Note    1003    select "test"."t3"."a" AS "a" from "test"."t3" where <nop>(("test"."t3"."a" >= (select min('0') from "test"."t2")))
 
1449
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min('0') from `test`.`t2`)))
1460
1450
select * from t3 where a >= all (select b from t2 group by 1);
1461
1451
a
1462
1452
6
1467
1457
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
1468
1458
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
1469
1459
Warnings:
1470
 
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)))
 
1460
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)))
1471
1461
select * from t3 where a >= some (select b from t2 group by 1);
1472
1462
a
1473
1463
explain extended select * from t3 where a >= some (select b from t2 group by 1);
1475
1465
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
1476
1466
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
1477
1467
Warnings:
1478
 
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)))
 
1468
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)))
1479
1469
select * from t3 where NULL >= any (select b from t2);
1480
1470
a
1481
1471
explain extended select * from t3 where NULL >= any (select b from t2);
1483
1473
1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
1484
1474
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
1485
1475
Warnings:
1486
 
Note    1003    select "test"."t3"."a" AS "a" from "test"."t3" where 0
 
1476
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1487
1477
select * from t3 where NULL >= any (select b from t2 group by 1);
1488
1478
a
1489
1479
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
1491
1481
1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
1492
1482
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
1493
1483
Warnings:
1494
 
Note    1003    select "test"."t3"."a" AS "a" from "test"."t3" where 0
 
1484
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1495
1485
select * from t3 where NULL >= some (select b from t2);
1496
1486
a
1497
1487
explain extended select * from t3 where NULL >= some (select b from t2);
1499
1489
1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
1500
1490
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
1501
1491
Warnings:
1502
 
Note    1003    select "test"."t3"."a" AS "a" from "test"."t3" where 0
 
1492
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1503
1493
select * from t3 where NULL >= some (select b from t2 group by 1);
1504
1494
a
1505
1495
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
1507
1497
1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
1508
1498
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
1509
1499
Warnings:
1510
 
Note    1003    select "test"."t3"."a" AS "a" from "test"."t3" where 0
 
1500
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 
1501
#
 
1502
# optimized static ALL/ANY with grouping
 
1503
#
1511
1504
insert into t2 values (2,2), (2,1), (3,3), (3,1);
1512
1505
select * from t3 where a > all (select max(b) from t2 group by a);
1513
1506
a
1518
1511
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
1519
1512
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    4       100.00  Using temporary; Using filesort
1520
1513
Warnings:
1521
 
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")))
 
1514
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`)))
1522
1515
drop table t2, t3;
1523
 
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY  (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
 
1516
#
 
1517
# correct used_tables()
 
1518
#
 
1519
CREATE TEMPORARY 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 ;
1524
1520
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());
1525
 
CREATE TABLE `t2` (`db_id` int(11) NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint(6) NOT NULL default '0',`secondary_uid` smallint(6) NOT NULL default '0',PRIMARY KEY  (`db_id`),UNIQUE KEY `name_2` (`name`),FULLTEXT KEY `name` (`name`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;
 
1521
CREATE TEMPORARY 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;
1526
1522
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);
1527
 
CREATE TABLE `t3` (`taskgenid` mediumint(9) NOT NULL auto_increment,`dbid` int(11) NOT NULL default '0',`taskid` int(11) NOT NULL default '0',`mon` tinyint(4) NOT NULL default '1',`tues` tinyint(4) NOT NULL default '1',`wed` tinyint(4) NOT NULL default '1',`thur` tinyint(4) NOT NULL default '1',`fri` tinyint(4) NOT NULL default '1',`sat` tinyint(4) NOT NULL default '0',`sun` tinyint(4) NOT NULL default '0',`how_often` smallint(6) NOT NULL default '1',`userid` smallint(6) NOT NULL default '0',`active` tinyint(4) NOT NULL default '1',PRIMARY KEY  (`taskgenid`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;
 
1523
CREATE TEMPORARY 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 ;
1528
1524
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);
1529
 
CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
 
1525
CREATE TEMPORARY TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;
1530
1526
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
1531
1527
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;
1532
1528
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')
1538
1534
-1      Valid
1539
1535
-1      Valid 2
1540
1536
drop table t1,t2,t3,t4;
1541
 
CREATE TABLE t1 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
 
1537
#
 
1538
# cardinality check
 
1539
#
 
1540
CREATE TEMPORARY TABLE t1 (id int default NULL) ENGINE=MyISAM;
1542
1541
INSERT INTO t1 VALUES (1),(5);
1543
 
CREATE TABLE t2 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
 
1542
CREATE TEMPORARY TABLE t2 (id int default NULL) ENGINE=MyISAM;
1544
1543
INSERT INTO t2 VALUES (2),(6);
1545
1544
select * from t1 where (1,2,6) in (select * from t2);
1546
1545
ERROR 21000: Operand should contain 3 column(s)
1547
1546
DROP TABLE t1,t2;
 
1547
#
 
1548
# optimized ALL/ANY with union
 
1549
#
1548
1550
create table t1 (s1 char);
1549
1551
insert into t1 values ('e');
1550
1552
select * from t1 where 'f' > any (select s1 from t1);
1555
1557
e
1556
1558
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1557
1559
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1558
 
1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       100.00  
1559
 
2       SUBQUERY        t1      system  NULL    NULL    NULL    NULL    1       100.00  
1560
 
3       UNION   t1      system  NULL    NULL    NULL    NULL    1       100.00  
1561
 
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
 
1560
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
 
1561
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
 
1562
3       UNION   t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
 
1563
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    #       NULL    
1562
1564
Warnings:
1563
 
Note    1003    select 'e' AS "s1" from "test"."t1" where 1
 
1565
Note    1003    select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where 1
1564
1566
drop table t1;
1565
 
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
 
1567
#
 
1568
# filesort in subquery (restoring join_tab)
 
1569
#
 
1570
CREATE TEMPORARY TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;
1566
1571
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
1567
 
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM CHARSET=latin1;
 
1572
CREATE TEMPORARY TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;
1568
1573
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
1569
1574
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;
1570
1575
phone   code
1573
1578
89356874041     NULL
1574
1579
95895001874     NULL
1575
1580
drop table t1, t2;
1576
 
create table t1 (s1 int);
1577
 
create table t2 (s1 int);
 
1581
#
 
1582
# unresolved field error
 
1583
#
 
1584
create temporary table t1 (s1 int) ENGINE=MyISAM;
 
1585
create temporary table t2 (s1 int) ENGINE=MyISAM;
1578
1586
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
1579
1587
ERROR 42S22: Unknown column 't1.s2' in 'where clause'
1580
1588
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
1582
1590
select count(*) from t2 group by t1.s2;
1583
1591
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
1584
1592
drop table t1, t2;
1585
 
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
1586
 
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
 
1593
#
 
1594
# fix_fields() in add_ref_to_table_cond()
 
1595
#
 
1596
CREATE TEMPORARY TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB)) ENGINE=MyISAM;
 
1597
CREATE TEMPORARY TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA)) ENGINE=MyISAM;
1587
1598
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
1588
1599
INSERT INTO t2 VALUES (100, 200, 'C');
1589
1600
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
1590
1601
COLC
1591
1602
DROP TABLE t1, t2;
1592
 
CREATE TABLE t1 (a int(1));
 
1603
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
1593
1604
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
1594
1605
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
1595
1606
(SELECT a)
1599
1610
4
1600
1611
5
1601
1612
DROP TABLE t1;
1602
 
CREATE TABLE `t1` (
1603
 
`id` int(11) NOT NULL auto_increment,
1604
 
`id_cns` tinyint(3) unsigned NOT NULL default '0',
 
1613
#
 
1614
# Bug 2479
 
1615
#
 
1616
CREATE TEMPORARY TABLE `t1` (
 
1617
`id` int NOT NULL auto_increment,
 
1618
`id_cns` int NOT NULL default '0',
1605
1619
`tipo` enum('','UNO','DUE') NOT NULL default '',
1606
 
`anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000',
1607
 
`particolare` mediumint(8) unsigned NOT NULL default '0',
1608
 
`generale` mediumint(8) unsigned NOT NULL default '0',
1609
 
`bis` tinyint(3) unsigned NOT NULL default '0',
 
1620
`anno_dep` int NOT NULL default '0',
 
1621
`particolare` bigint NOT NULL default '0',
 
1622
`generale` bigint NOT NULL default '0',
 
1623
`bis` int NOT NULL default '0',
1610
1624
PRIMARY KEY  (`id`),
1611
1625
UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
1612
 
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
1613
 
);
 
1626
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`))
 
1627
ENGINE=MyISAM;
1614
1628
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);
1615
 
CREATE TABLE `t2` (
1616
 
`id` tinyint(3) unsigned NOT NULL auto_increment,
1617
 
`max_anno_dep` smallint(6) unsigned NOT NULL default '0',
1618
 
PRIMARY KEY  (`id`)
1619
 
);
 
1629
CREATE TEMPORARY TABLE `t2` (
 
1630
`id` int NOT NULL auto_increment,
 
1631
`max_anno_dep` int NOT NULL default '0',
 
1632
PRIMARY KEY  (`id`)) ENGINE=MyISAM;
1620
1633
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
1621
1634
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;
1622
1635
id      max_anno_dep    PIPPO
1624
1637
50      1990    0
1625
1638
51      1990    NULL
1626
1639
DROP TABLE t1, t2;
1627
 
create table t1 (a int);
 
1640
#
 
1641
# GLOBAL LIMIT
 
1642
#
 
1643
create temporary table t1 (a int) ENGINE=MyISAM;
1628
1644
insert into t1 values (1), (2), (3);
1629
1645
SET SQL_SELECT_LIMIT=1;
1630
1646
select sum(a) from (select * from t1) as a;
1635
1651
1
1636
1652
SET SQL_SELECT_LIMIT=default;
1637
1653
drop table t1;
 
1654
#
 
1655
# Bug #3118: subselect + order by
 
1656
#
1638
1657
CREATE TABLE t1 (a int, b int, INDEX (a));
1639
1658
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
1640
1659
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
1643
1662
1       2
1644
1663
1       3
1645
1664
DROP TABLE t1;
 
1665
# Item_cond fix field
 
1666
#
1646
1667
create table t1(val varchar(10));
1647
1668
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
1648
1669
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%');
1649
1670
count(*)
1650
1671
0
1651
1672
drop table t1;
 
1673
#
 
1674
# ref_or_null replacing with ref
 
1675
#
1652
1676
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
1653
1677
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');
1654
1678
select * from t1 where id not in (select id from t1 where id < 8);
1667
1691
12      text12
1668
1692
explain extended select * from t1 where id not in (select id from t1 where id < 8);
1669
1693
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1670
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
1671
 
2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index; Using where
 
1694
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
1695
2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    #       100.00  Using index; Using where
1672
1696
Warnings:
1673
 
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")))))))
 
1697
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`)))))))
1674
1698
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);
1675
1699
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1676
 
1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
1677
 
2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
 
1700
1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
1701
2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      #       100.00  Using where; Using index
1678
1702
Warnings:
1679
1703
Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
1680
 
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))))
 
1704
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))))
1681
1705
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
1682
 
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
 
1706
create temporary table t2 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
1683
1707
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');
1684
1708
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);
1685
1709
id      text    id      text    id      text
1699
1723
1001    text1001        NULL    NULL    1000    text1000
1700
1724
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);
1701
1725
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1702
 
1       SIMPLE  a       ALL     NULL    NULL    NULL    NULL    14      100.00  
1703
 
1       SIMPLE  b       eq_ref  PRIMARY PRIMARY 4       test.a.id       2       100.00  
1704
 
1       SIMPLE  c       eq_ref  PRIMARY PRIMARY 4       func    1       100.00  Using index condition
 
1726
1       SIMPLE  a       ALL     NULL    NULL    NULL    NULL    #       100.00  
 
1727
1       SIMPLE  b       eq_ref  PRIMARY PRIMARY 4       test.a.id       #       100.00  
 
1728
1       SIMPLE  c       eq_ref  PRIMARY PRIMARY 4       func    #       100.00  Using where
1705
1729
Warnings:
1706
 
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")
 
1730
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`)
1707
1731
drop table t1,t2;
1708
 
create table t1 (a int);
 
1732
#
 
1733
# Static tables & rund() in subqueries
 
1734
#
 
1735
create temporary table t1 (a int) ENGINE=MyISAM;
1709
1736
insert into t1 values (1);
1710
 
explain select benchmark(1000, (select a from t1 where a=sha(rand())));
 
1737
explain select benchmark(1000, (select a from t1 where a=rand()));
1711
1738
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1712
1739
1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
1713
 
2       UNCACHEABLE SUBQUERY    t1      system  NULL    NULL    NULL    NULL    1       
 
1740
2       SUBQUERY        t1      system  NULL    NULL    NULL    NULL    1       
1714
1741
drop table t1;
1715
 
create table t1(id int);
1716
 
create table t2(id int);
1717
 
create table t3(flag int);
 
1742
#
 
1743
# bug 3188
 
1744
#
 
1745
create temporary table t1(id int) ENGINE=MyISAM;
 
1746
create temporary table t2(id int) ENGINE=MyISAM;
 
1747
create temporary table t3(flag int) ENGINE=MyISAM;
1718
1748
select (select * from t3 where id not null) from t1, t2;
1719
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null) from t1, t2' at line 1
 
1749
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
1720
1750
drop table t1,t2,t3;
 
1751
#
 
1752
# aggregate functions (Bug #3505)
 
1753
#
1721
1754
CREATE TABLE t1 (id INT);
1722
1755
CREATE TABLE t2 (id INT);
1723
1756
INSERT INTO t1 VALUES (1), (2);
1739
1772
1       1
1740
1773
2       0
1741
1774
DROP TABLE t1,t2;
 
1775
#
 
1776
# ALL/ANY test
 
1777
#
1742
1778
CREATE TABLE t1 ( a int, b int );
1743
1779
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1744
1780
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1783
1819
a
1784
1820
1
1785
1821
3
 
1822
# with index
1786
1823
ALTER TABLE t1 ADD INDEX (a);
1787
1824
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1788
1825
a
1826
1863
a
1827
1864
1
1828
1865
3
 
1866
# having clause test
1829
1867
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
1830
1868
a
1831
1869
3
1868
1906
a
1869
1907
1
1870
1908
3
 
1909
# union test
1871
1910
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1872
1911
a
1873
1912
3
1910
1949
a
1911
1950
1
1912
1951
3
 
1952
# union + having test
1913
1953
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1914
1954
a
1915
1955
3
1952
1992
a
1953
1993
1
1954
1994
3
 
1995
# row tests
 
1996
# < > >= <= and = ALL/ <> ANY do not support row operation
1955
1997
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
1956
1998
ERROR 21000: Operand should contain 1 column(s)
1957
1999
SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1968
2010
ERROR 21000: Operand should contain 1 column(s)
1969
2011
SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
1970
2012
ERROR 21000: Operand should contain 1 column(s)
 
2013
# following should be converted to IN
1971
2014
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
1972
2015
ERROR 21000: Operand should contain 2 column(s)
1973
2016
SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
2011
2054
a
2012
2055
1
2013
2056
3
 
2057
# without optimisation
2014
2058
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2015
2059
a
2016
2060
3
2053
2097
a
2054
2098
1
2055
2099
3
 
2100
# without optimisation + having
2056
2101
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
2057
2102
a
2058
2103
3
2095
2140
a
2096
2141
1
2097
2142
3
 
2143
# EXISTS in string contence
2098
2144
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
2099
2145
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-')
2100
2146
0-
2201
2247
1
2202
2248
3
2203
2249
DROP TABLE t1;
 
2250
#
 
2251
# SELECT(EXISTS * ...)optimisation
 
2252
#
2204
2253
create table t1 (a int, b int);
2205
2254
insert into t1 values (1,2),(3,4);
2206
2255
select * from t1 up where exists (select * from t1 where t1.a=up.a);
2209
2258
3       4
2210
2259
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
2211
2260
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
2212
 
1       PRIMARY up      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
2213
 
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 
2261
1       PRIMARY up      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
2262
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
2214
2263
Warnings:
2215
2264
Note    1276    Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
2216
 
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"))
 
2265
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`))
2217
2266
drop table t1;
2218
 
CREATE TABLE t1 (t1_a int);
 
2267
#
 
2268
# Bug #4102: subselect in HAVING
 
2269
#
 
2270
CREATE TEMPORARY TABLE t1 (t1_a int) ENGINE=MyISAM;
2219
2271
INSERT INTO t1 VALUES (1);
2220
2272
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
2221
2273
INSERT INTO t2 VALUES (1, 1), (1, 2);
2224
2276
t1_a    t2_a    t2_b
2225
2277
1       1       2
2226
2278
DROP TABLE t1, t2;
2227
 
CREATE TABLE t1 (id int(11) default NULL,name varchar(10) default NULL);
 
2279
#
 
2280
# Test problem with NULL and derived tables (Bug #4097)
 
2281
#
 
2282
CREATE TEMPORARY TABLE t1 (id int default NULL,name varchar(10) default NULL) ENGINE=MyISAM;
2228
2283
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
2229
 
CREATE TABLE t2 (id int(11) default NULL, pet varchar(10) default NULL);
 
2284
CREATE TEMPORARY TABLE t2 (id int default NULL, pet varchar(10) default NULL) ENGINE=MyISAM;
2230
2285
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
2231
2286
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
2232
2287
id      name    id      pet
2234
2289
2       Rebecca 2       Spot
2235
2290
3       NULL    3       Felix
2236
2291
drop table t1,t2;
2237
 
CREATE TABLE `t1` ( `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
2292
#
 
2293
# Aggregate function comparation with ALL/ANY/SOME subselect
 
2294
#
 
2295
CREATE TEMPORARY TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;
2238
2296
insert into t1 values (1);
2239
 
CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
2297
CREATE TEMPORARY TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
2240
2298
insert into t2 values (1,2);
2241
2299
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
2242
2300
a       C
2243
2301
1       1
2244
2302
drop table t1,t2;
2245
 
create table t1 (a int not null auto_increment primary key, b varchar(40), fulltext(b));
2246
 
insert into t1 (b) values ('ball'),('ball games'), ('games'), ('foo'), ('foobar'), ('Serg'), ('Sergei'),('Georg'), ('Patrik'),('Hakan');
2247
 
create table t2 (a int);
2248
 
insert into t2 values (1),(3),(2),(7);
2249
 
select a,b from t1 where match(b) against ('Ball') > 0;
2250
 
a       b
2251
 
1       ball
2252
 
2       ball games
2253
 
select a from t2 where a in (select a from t1 where match(b) against ('Ball') > 0);
2254
 
a
2255
 
1
2256
 
2
2257
 
drop table t1,t2;
2258
 
CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`KUERZEL` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,`IZAANALYSEART_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`IZAPMKZ_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin);
 
2303
#
 
2304
# BUG#5003 - like in subselect
 
2305
#
 
2306
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);
2259
2307
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
2260
2308
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
2261
2309
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
2265
2313
IZAVORGANG_ID
2266
2314
D0000000001
2267
2315
drop table t1;
2268
 
CREATE TABLE `t1` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`));
2269
 
CREATE TABLE `t2` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`));
 
2316
#
 
2317
# Optimized IN with compound index
 
2318
#
 
2319
CREATE TEMPORARY TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`)) ENGINE=MyISAM;
 
2320
CREATE TEMPORARY TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`)) ENGINE=MyISAM;
2270
2321
insert into t1 values (1,1),(1,2),(2,1),(2,2);
2271
2322
insert into t2 values (1,2),(2,2);
2272
2323
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2286
2337
1       1
2287
2338
2       1
2288
2339
drop table t1,t2;
 
2340
#
 
2341
# resolving fields of grouped outer SELECT
 
2342
#
2289
2343
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
2290
2344
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
2291
2345
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
2324
2378
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;
2325
2379
(select b.x from t1 as b where b.x=a.x)
2326
2380
drop table t1;
2327
 
CREATE TABLE `t1` ( `master` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `slave` int(10) unsigned NOT NULL default '0', `access` int(10) unsigned NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`));
 
2381
#
 
2382
# Test of correct maybe_null flag returning by subquwery for temporary table
 
2383
# creation
 
2384
#
 
2385
CREATE TEMPORARY 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;
2328
2386
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);
2329
 
CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL default '0', `pid` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `level` tinyint(4) unsigned NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY  (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ;
 
2387
CREATE TEMPORARY 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 ;
2330
2388
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');
2331
2389
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;
2332
2390
ERROR 42S22: Unknown column 'b.sc' in 'field list'
2335
2393
700
2336
2394
NULL
2337
2395
drop tables t1,t2;
 
2396
#
 
2397
# Subselect in non-select command just after connection
 
2398
#
2338
2399
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
2339
 
create table t1 (a int, b int);
2340
 
create table t2 (a int, b int);
 
2400
#
 
2401
# primary query with temporary table and subquery with groupping
 
2402
#
 
2403
create temporary table t1 (a int, b int) ENGINE=MyISAM;
 
2404
create temporary table t2 (a int, b int) ENGINE=MyISAM;
2341
2405
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
2342
2406
insert into t2 values (1,3),(2,1);
2343
2407
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
2348
2412
2       4       NULL
2349
2413
2       5       NULL
2350
2414
drop table t1, t2;
2351
 
create table t1 (id int);
2352
 
create table t2 (id int, body text, fulltext (body));
2353
 
insert into t1 values(1),(2),(3);
2354
 
insert into t2 values (1,'test'), (2,'mysql'), (3,'test'), (4,'test');
2355
 
select count(distinct id) from t1 where id in (select id from t2 where match(body) against ('mysql' in boolean mode));
2356
 
count(distinct id)
2357
 
1
2358
 
drop table t2,t1;
 
2415
#
 
2416
# Equal operation under row and empty subquery
 
2417
#
2359
2418
create table t1 (s1 int,s2 int);
2360
2419
insert into t1 values (20,15);
2361
2420
select * from t1 where  (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
2362
2421
s1      s2
2363
2422
drop table t1;
 
2423
#
 
2424
# ALL/ANY with NULL
 
2425
#
2364
2426
create table t1 (s1 int);
2365
2427
insert into t1 values (1),(null);
2366
2428
select * from t1 where s1 < all (select s1 from t1);
2370
2432
1       0
2371
2433
NULL    NULL
2372
2434
drop table t1;
 
2435
#
 
2436
# reference on changable fields from subquery
 
2437
#
2373
2438
CREATE TABLE t1 (
2374
2439
Code char(3) NOT NULL default '',
2375
2440
Name char(52) NOT NULL default '',
2376
2441
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
2377
2442
Region char(26) NOT NULL default '',
2378
2443
SurfaceArea float(10,2) NOT NULL default '0.00',
2379
 
IndepYear smallint(6) default NULL,
2380
 
Population int(11) NOT NULL default '0',
 
2444
IndepYear int default NULL,
 
2445
Population int NOT NULL default '0',
2381
2446
LifeExpectancy float(3,1) default NULL,
2382
2447
GNP float(10,2) default NULL,
2383
2448
GNPOld float(10,2) default NULL,
2384
2449
LocalName char(45) NOT NULL default '',
2385
2450
GovernmentForm char(45) NOT NULL default '',
2386
2451
HeadOfState char(60) default NULL,
2387
 
Capital int(11) default NULL,
 
2452
Capital int default NULL,
2388
2453
Code2 char(2) NOT NULL default ''
2389
 
) ENGINE=MyISAM;
 
2454
);
2390
2455
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
2391
2456
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');
2392
2457
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');
2393
2458
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');
2394
2459
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
 
2460
Warnings:
 
2461
Note    1031    Table storage engine for 't1' doesn't have this option
2395
2462
SELECT DISTINCT Continent AS c FROM t1 outr WHERE 
2396
2463
Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND 
2397
2464
Population < 200);
2398
2465
c
2399
2466
Oceania
2400
2467
drop table t1;
2401
 
create table t1 (a1 int);
2402
 
create table t2 (b1 int);
 
2468
#
 
2469
# Test for BUG#7885: Server crash when 'any' subselect compared to
 
2470
# non-existant field.
 
2471
#
 
2472
create temporary table t1 (a1 int) ENGINE=MyISAM;
 
2473
create temporary table t2 (b1 int) ENGINE=MyISAM;
2403
2474
select * from t1 where a2 > any(select b1 from t2);
2404
2475
ERROR 42S22: Unknown column 'a2' in 'IN/ALL/ANY subquery'
2405
2476
select * from t1 where a1 > any(select b1 from t2);
2406
2477
a1
2407
2478
drop table t1,t2;
2408
 
create table t1 (a integer, b integer);
 
2479
#
 
2480
# Comparison subquery with * and row
 
2481
#
 
2482
create temporary table t1 (a integer, b integer) ENGINE=MyISAM;
2409
2483
select (select * from t1) = (select 1,2);
2410
2484
(select * from t1) = (select 1,2)
2411
2485
NULL
2412
2486
select (select 1,2) = (select * from t1);
2413
2487
(select 1,2) = (select * from t1)
2414
2488
NULL
 
2489
# queries whih can be converted to IN
2415
2490
select  row(1,2) = ANY (select * from t1);
2416
2491
row(1,2) = ANY (select * from t1)
2417
2492
0
2419
2494
row(1,2) != ALL (select * from t1)
2420
2495
1
2421
2496
drop table t1;
2422
 
create table t1 (a integer, b integer);
 
2497
#
 
2498
# Comparison subquery and row with nested rows
 
2499
#
 
2500
create temporary table t1 (a integer, b integer) ENGINE=MyISAM;
2423
2501
select row(1,(2,2)) in (select * from t1 );
2424
2502
ERROR 21000: Operand should contain 2 column(s)
2425
2503
select row(1,(2,2)) = (select * from t1 );
2427
2505
select (select * from t1) = row(1,(2,2));
2428
2506
ERROR 21000: Operand should contain 1 column(s)
2429
2507
drop table t1;
 
2508
#
 
2509
# Forward reference detection
 
2510
#
2430
2511
create  table t1 (a integer);
2431
2512
insert into t1 values (1);
2432
2513
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;
2438
2519
1       1
2439
2520
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
2440
2521
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2441
 
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx from DUAL;
2442
 
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2443
2522
drop table t1;
2444
 
CREATE TABLE t1 (
2445
 
categoryId int(11) NOT NULL,
2446
 
courseId int(11) NOT NULL,
 
2523
#
 
2524
# Test for BUG#8218
 
2525
#
 
2526
CREATE TEMPORARY TABLE t1 (
 
2527
categoryId int NOT NULL,
 
2528
courseId int NOT NULL,
2447
2529
startDate datetime NOT NULL,
2448
2530
endDate datetime NOT NULL,
2449
2531
createDate datetime NOT NULL,
2450
2532
modifyDate timestamp NOT NULL,
2451
 
attributes text NOT NULL
2452
 
);
 
2533
attributes text NOT NULL)
 
2534
engine=myisam;
2453
2535
INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2454
2536
(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2455
2537
(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2459
2541
(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2460
2542
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2461
2543
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');
2462
 
CREATE TABLE t2 (
2463
 
userId int(11) NOT NULL,
2464
 
courseId int(11) NOT NULL,
2465
 
date datetime NOT NULL
2466
 
);
 
2544
CREATE TEMPORARY TABLE t2 (
 
2545
userId int NOT NULL,
 
2546
courseId int NOT NULL,
 
2547
date datetime NOT NULL)
 
2548
engine=myisam;
2467
2549
INSERT INTO t2 VALUES (5141,71,'2003-11-18'),
2468
2550
(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),
2469
2551
(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),
2470
2552
(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),
2471
2553
(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),
2472
2554
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');
2473
 
CREATE TABLE t3 (
2474
 
groupId int(11) NOT NULL,
2475
 
parentId int(11) NOT NULL,
 
2555
CREATE TEMPORARY TABLE t3 (
 
2556
groupId int NOT NULL,
 
2557
parentId int NOT NULL,
2476
2558
startDate datetime NOT NULL,
2477
2559
endDate datetime NOT NULL,
2478
2560
createDate datetime NOT NULL,
2479
2561
modifyDate timestamp NOT NULL,
2480
 
ordering int(11)
2481
 
);
 
2562
ordering int)
 
2563
engine=myisam;
2482
2564
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
2483
 
CREATE TABLE t4 (
2484
 
id int(11) NOT NULL,
2485
 
groupTypeId int(11) NOT NULL,
 
2565
CREATE TEMPORARY TABLE t4 (
 
2566
id int NOT NULL,
 
2567
groupTypeId int NOT NULL,
2486
2568
groupKey varchar(50) NOT NULL,
2487
2569
name text,
2488
 
ordering int(11),
 
2570
ordering int,
2489
2571
description text,
2490
2572
createDate datetime NOT NULL,
2491
 
modifyDate timestamp NOT NULL
2492
 
);
 
2573
modifyDate timestamp NOT NULL)
 
2574
engine=myisam;
2493
2575
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
2494
2576
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
2495
 
CREATE TABLE t5 (
2496
 
userId int(11) NOT NULL,
2497
 
groupId int(11) NOT NULL,
 
2577
CREATE TEMPORARY TABLE t5 (
 
2578
userId int NOT NULL,
 
2579
groupId int NOT NULL,
2498
2580
createDate datetime NOT NULL,
2499
 
modifyDate timestamp NOT NULL
2500
 
);
 
2581
modifyDate timestamp NOT NULL) ENGINE=MyISAM;
2501
2582
INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
2502
2583
select
2503
2584
count(distinct t2.userid) pass,
2541
2622
1       5141    12      group2  12      group2  5       1       2       88      Oct04
2542
2623
1       5141    12      group2  12      group2  5       1       2       89      Oct04
2543
2624
drop table t1, t2, t3, t4, t5;
2544
 
create table t1 (a int);
 
2625
#
 
2626
# Transformation in left expression of subquery (BUG#8888)
 
2627
#
 
2628
create temporary table t1 (a int) ENGINE=MyISAM;
2545
2629
insert into t1 values (1), (2), (3);
2546
2630
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
2547
2631
1
2549
2633
1
2550
2634
1
2551
2635
drop table t1;
2552
 
create table t1 (a int, b int);
 
2636
#
 
2637
# single row subqueries and row operations (code covarage improvement)
 
2638
#
 
2639
create temporary table t1 (a int, b int) ENGINE=MyISAM;
2553
2640
insert into t1 values (1,2);
2554
2641
select 1 = (select * from t1);
2555
2642
ERROR 21000: Operand should contain 1 column(s)
2564
2651
select (select * from t1) = (1,2,3);
2565
2652
ERROR 21000: Operand should contain 2 column(s)
2566
2653
drop table t1;
2567
 
CREATE TABLE `t1` (
2568
 
`itemid` bigint(20) unsigned NOT NULL auto_increment,
2569
 
`sessionid` bigint(20) unsigned default NULL,
2570
 
`time` int(10) unsigned NOT NULL default '0',
2571
 
`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
2572
 
NULL default '',
2573
 
`data` text collate latin1_general_ci NOT NULL,
2574
 
PRIMARY KEY  (`itemid`)
2575
 
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
2576
 
INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
2577
 
CREATE TABLE `t2` (
2578
 
`sessionid` bigint(20) unsigned NOT NULL auto_increment,
2579
 
`pid` int(10) unsigned NOT NULL default '0',
2580
 
`date` int(10) unsigned NOT NULL default '0',
2581
 
`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
2582
 
PRIMARY KEY  (`sessionid`)
2583
 
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
2584
 
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
2585
 
SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;
2586
 
ip      count( e.itemid )
2587
 
10.10.10.1      1
2588
 
drop tables t1,t2;
2589
 
create table t1 (fld enum('0','1'));
 
2654
#
 
2655
# Item_int_with_ref check (BUG#10020)
 
2656
#
 
2657
#CREATE TABLE `t1` (
 
2658
#  `itemid` bigint NOT NULL auto_increment,
 
2659
#  `sessionid` bigint default NULL,
 
2660
#  `time` int NOT NULL default '0',
 
2661
#  `data` text collate latin1_general_ci NOT NULL,
 
2662
#  PRIMARY KEY  (`itemid`)
 
2663
#);
 
2664
#INSERT INTO `t1` VALUES (1, 1, 1, '');
 
2665
#CREATE TABLE `t2` (
 
2666
#  `sessionid` bigint NOT NULL auto_increment,
 
2667
#  `pid` int NOT NULL default '0',
 
2668
#  `date` int NOT NULL default '0',
 
2669
#  `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
 
2670
#  PRIMARY KEY  (`sessionid`)
 
2671
#);
 
2672
#INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
 
2673
#SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;
 
2674
#drop tables t1,t2;
 
2675
# BUG#11821 : Select from subselect using aggregate function on an enum
 
2676
# segfaults:
 
2677
create temporary table t1 (fld enum('0','1')) ENGINE=MyISAM;
2590
2678
insert into t1 values ('1');
2591
2679
select * from (select max(fld) from t1) as foo;
2592
2680
max(fld)
2593
2681
1
2594
2682
drop table t1;
 
2683
#
 
2684
# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
 
2685
#
2595
2686
CREATE TABLE t1 (one int, two int, flag char(1));
2596
2687
CREATE TABLE t2 (one int, two int, flag char(1));
2597
2688
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2662
2753
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    8       100.00  
2663
2754
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    9       100.00  Using where
2664
2755
Warnings:
2665
 
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"
 
2756
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`
2666
2757
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2667
2758
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
2668
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    8       100.00  Start temporary
2669
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    9       100.00  Using where; End temporary; Using join buffer
 
2759
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    8       100.00  Using where
 
2760
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    9       100.00  Using where
2670
2761
Warnings:
2671
 
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'))
 
2762
Note    1003    select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( <materialize> (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N') ), <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on distinct_key)))
2672
2763
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;
2673
2764
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
2674
2765
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    8       100.00  
2675
2766
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    9       100.00  Using where; Using temporary; Using filesort
2676
2767
Warnings:
2677
 
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"
 
2768
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`
2678
2769
DROP TABLE t1,t2;
2679
 
CREATE TABLE t1 (a char(5), b char(5));
 
2770
#
 
2771
# Bug #12392: where cond with IN predicate for rows and NULL values in table 
 
2772
#
 
2773
CREATE TEMPORARY TABLE t1 (a char(5), b char(5)) ENGINE=MyISAM;
2680
2774
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
2681
2775
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
2682
2776
a       b
2683
2777
aaa     aaa
2684
2778
DROP TABLE t1;
2685
 
CREATE TABLE t1 (a int);
2686
 
CREATE TABLE t2 (a int, b int);
2687
 
CREATE TABLE t3 (b int NOT NULL);
 
2779
#
 
2780
# Bug #11479: subquery over left join with an empty inner table 
 
2781
#
 
2782
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
 
2783
CREATE TEMPORARY TABLE t2 (a int, b int) ENGINE=MyISAM;
 
2784
CREATE TEMPORARY TABLE t3 (b int NOT NULL) ENGINE=MyISAM;
2688
2785
INSERT INTO t1 VALUES (1), (2), (3), (4);
2689
2786
INSERT INTO t2 VALUES (1,10), (3,30);
2690
2787
SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b
2699
2796
3
2700
2797
4
2701
2798
DROP TABLE t1,t2,t3;
2702
 
CREATE TABLE t1 (f1 INT);
2703
 
CREATE TABLE t2 (f2 INT);
 
2799
#
 
2800
# Bug#18503: Queries with a quantified subquery returning empty set may
 
2801
# return a wrong result. 
 
2802
#
 
2803
CREATE TEMPORARY TABLE t1 (f1 INT) ENGINE=MyISAM;
 
2804
CREATE TEMPORARY TABLE t2 (f2 INT) ENGINE=MyISAM;
2704
2805
INSERT INTO t1 VALUES (1);
2705
2806
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
2706
2807
f1
2714
2815
f1
2715
2816
1
2716
2817
DROP TABLE t1, t2;
2717
 
select 1 from dual where 1 < any (select 2);
2718
 
1
2719
 
1
2720
 
select 1 from dual where 1 < all (select 2);
2721
 
1
2722
 
1
2723
 
select 1 from dual where 2 > any (select 1);
2724
 
1
2725
 
1
2726
 
select 1 from dual where 2 > all (select 1);
2727
 
1
2728
 
1
2729
 
select 1 from dual where 1 < any (select 2 from dual);
2730
 
1
2731
 
1
2732
 
select 1 from dual where 1 < all (select 2 from dual where 1!=1);
2733
 
1
2734
 
1
 
2818
# BUG#20975 Wrong query results for subqueries within NOT
2735
2819
create table t1 (s1 char);
2736
2820
insert into t1 values (1),(2);
2737
2821
select * from t1 where (s1 < any (select s1 from t1));
2759
2843
s1
2760
2844
2
2761
2845
drop table t1;
 
2846
#
 
2847
# Bug #16255: Subquery in where
 
2848
#
2762
2849
create table t1 (
2763
2850
retailerID varchar(8) NOT NULL,
2764
 
statusID   int(10) unsigned NOT NULL,
 
2851
statusID   int NOT NULL,
2765
2852
changed    datetime NOT NULL,
2766
 
UNIQUE KEY retailerID (retailerID, statusID, changed)
2767
 
);
 
2853
UNIQUE KEY retailerID (retailerID, statusID, changed));
2768
2854
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
2769
2855
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
2770
2856
INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
2781
2867
0048    1       2006-01-06 12:37:50
2782
2868
0059    1       2006-01-06 12:37:50
2783
2869
drop table t1;
 
2870
#
 
2871
# Bug #21180: Subselect with index for both WHERE and ORDER BY 
 
2872
#             produces empty result
 
2873
#
2784
2874
create table t1(a int, primary key (a));
2785
2875
insert into t1 values (10);
2786
2876
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
2787
2877
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
2788
 
explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
 
2878
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
2789
2879
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
2790
2880
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2791
2881
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
2792
 
1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
2793
 
1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
2794
 
2       DEPENDENT SUBQUERY      t2      range   b       b       40      NULL    2       Using index condition
2795
 
SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
 
2882
1       PRIMARY t1      const   PRIMARY PRIMARY 4       const   #       Using index
 
2883
1       PRIMARY r       const   PRIMARY PRIMARY 4       const   #       
 
2884
2       DEPENDENT SUBQUERY      t2      ref     b       b       5               #       Using where; Using index
 
2885
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
2796
2886
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
2797
2887
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2798
2888
a       a       b
2799
2889
10      3       35989
2800
 
explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
 
2890
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
2801
2891
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
2802
2892
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2803
2893
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
2804
 
1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
 
2894
1       PRIMARY t1      const   PRIMARY PRIMARY 4       const   1       Using index
2805
2895
1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
2806
 
2       DEPENDENT SUBQUERY      t2      range   b       b       40      NULL    2       Using index condition; Using MRR
2807
 
SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
 
2896
2       DEPENDENT SUBQUERY      t2      ref     b       b       5               1       Using where; Using index
 
2897
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
2808
2898
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
2809
2899
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2810
2900
a       a       b
2811
2901
10      1       359
2812
2902
drop table t1,t2;
2813
 
CREATE TABLE t1 (                  
 
2903
#
 
2904
# Bug #21853: assert failure for a grouping query with
 
2905
#             an ALL/ANY quantified subquery in HAVING 
 
2906
#
 
2907
CREATE TEMPORARY TABLE t1 (                  
2814
2908
field1 int NOT NULL,                 
2815
2909
field2 int NOT NULL,                 
2816
2910
field3 int NOT NULL,                 
2817
 
PRIMARY KEY  (field1,field2,field3)  
2818
 
);
2819
 
CREATE TABLE t2 (             
 
2911
PRIMARY KEY  (field1,field2,field3))
 
2912
ENGINE=MyISAM;
 
2913
CREATE TEMPORARY TABLE t2 (             
2820
2914
fieldA int NOT NULL,            
2821
2915
fieldB int NOT NULL,            
2822
 
PRIMARY KEY  (fieldA,fieldB)     
2823
 
);
 
2916
PRIMARY KEY  (fieldA,fieldB))
 
2917
ENGINE=MyISAM;
2824
2918
INSERT INTO t1 VALUES
2825
2919
(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
2826
2920
INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
2846
2940
1       1
2847
2941
1       3
2848
2942
DROP TABLE t1, t2;
2849
 
CREATE TABLE t1(a int, INDEX (a));
 
2943
#
 
2944
# Bug #23478: not top-level IN subquery returning a non-empty result set
 
2945
#             with possible NULL values by index access from the outer query
 
2946
#
 
2947
CREATE TEMPORARY TABLE t1(a int, INDEX (a)) ENGINE=MyISAM;
2850
2948
INSERT INTO t1 VALUES (1), (3), (5), (7);
2851
2949
INSERT INTO t1 VALUES (NULL);
2852
 
CREATE TABLE t2(a int);
 
2950
CREATE TEMPORARY TABLE t2(a int) ENGINE=MyISAM;
2853
2951
INSERT INTO t2 VALUES (1),(2),(3);
2854
2952
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
2855
2953
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
2861
2959
2       NULL
2862
2960
3       1
2863
2961
DROP TABLE t1,t2;
 
2962
#
 
2963
# Bug #11302: getObject() returns a String for a sub-query of type datetime
 
2964
#
2864
2965
CREATE TABLE t1 (a DATETIME);
2865
2966
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
2866
 
CREATE TABLE t2 AS SELECT 
 
2967
CREATE TEMPORARY TABLE t2 ENGINE=MyISAM AS SELECT 
2867
2968
(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a 
2868
2969
FROM t1 WHERE a > '2000-01-01';
2869
2970
SHOW CREATE TABLE t2;
2870
2971
Table   Create Table
2871
 
t2      CREATE TABLE "t2" (
2872
 
  "sub_a" datetime ON UPDATE CURRENT_TIMESTAMP
2873
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2874
 
CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
 
2972
t2      CREATE TEMPORARY TABLE `t2` (
 
2973
  `sub_a` datetime DEFAULT NULL
 
2974
) ENGINE=MyISAM
 
2975
CREATE TEMPORARY TABLE t3 ENGINE=MyISAM AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
2875
2976
SHOW CREATE TABLE t3;
2876
2977
Table   Create Table
2877
 
t3      CREATE TABLE "t3" (
2878
 
  "a" datetime ON UPDATE CURRENT_TIMESTAMP
2879
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
2978
t3      CREATE TEMPORARY TABLE `t3` (
 
2979
  `a` datetime DEFAULT NULL
 
2980
) ENGINE=MyISAM
2880
2981
DROP TABLE t1,t2,t3;
2881
 
CREATE TABLE t1 (a int);
2882
 
INSERT INTO t1 VALUES (1), (2);
2883
 
SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;
2884
 
a
2885
 
SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
2886
 
a
2887
 
1
2888
 
2
2889
 
EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
2890
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
2891
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
2892
 
2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
2893
 
DROP TABLE t1;
2894
 
CREATE TABLE t1 (a int);
 
2982
#
 
2983
# Bug 24653: sorting by expressions containing subselects
 
2984
#            that return more than one row
 
2985
#
 
2986
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
2895
2987
INSERT INTO t1 VALUES (2), (4), (1), (3);
2896
2988
CREATE TABLE t2 (b int, c int);
2897
2989
INSERT INTO t2 VALUES
2965
3057
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
2966
3058
ERROR 21000: Subquery returns more than 1 row
2967
3059
DROP TABLE t1,t2;
 
3060
# End of 4.1 tests
 
3061
#
 
3062
#decimal-related tests
 
3063
#
2968
3064
create table t1 (df decimal(5,1));
2969
3065
insert into t1 values(1.1);
2970
3066
insert into t1 values(2.2);
2975
3071
df
2976
3072
2.2
2977
3073
drop table t1;
2978
 
create table t1 (df decimal(5,1));
 
3074
create temporary table t1 (df decimal(5,1)) ENGINE=MyISAM;
2979
3075
insert into t1 values(1.1);
2980
3076
select 1.1 * exists(select * from t1);
2981
3077
1.1 * exists(select * from t1)
2982
3078
1.1
2983
3079
drop table t1;
2984
 
CREATE TABLE t1 (
2985
 
grp int(11) default NULL,
2986
 
a decimal(10,2) default NULL);
 
3080
CREATE TEMPORARY TABLE t1 (
 
3081
grp int default NULL,
 
3082
a decimal(10,2) default NULL) ENGINE=MyISAM;
2987
3083
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
2988
3084
select * from t1;
2989
3085
grp     a
3001
3097
2.00
3002
3098
4.00
3003
3099
drop table t1;
3004
 
CREATE table t1 ( c1 integer );
 
3100
#
 
3101
# Test for bug #9338: lame substitution of c1 instead of c2 
 
3102
#
 
3103
CREATE temporary table t1 ( c1 integer ) ENGINE=MyISAM;
3005
3104
INSERT INTO t1 VALUES ( 1 );
3006
3105
INSERT INTO t1 VALUES ( 2 );
3007
3106
INSERT INTO t1 VALUES ( 3 );
3017
3116
c1      c2
3018
3117
1       1
3019
3118
DROP TABLE t1,t2;
3020
 
CREATE TABLE t1 ( c1 integer );
 
3119
#
 
3120
# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ 
 
3121
#
 
3122
CREATE TEMPORARY TABLE t1 ( c1 integer ) ENGINE=MyISAM;
3021
3123
INSERT INTO t1 VALUES ( 1 );
3022
3124
INSERT INTO t1 VALUES ( 2 );
3023
3125
INSERT INTO t1 VALUES ( 3 );
3024
3126
INSERT INTO t1 VALUES ( 6 );
3025
 
CREATE TABLE t2 ( c2 integer );
 
3127
CREATE TEMPORARY TABLE t2 ( c2 integer ) ENGINE=MyISAM;
3026
3128
INSERT INTO t2 VALUES ( 1 );
3027
3129
INSERT INTO t2 VALUES ( 4 );
3028
3130
INSERT INTO t2 VALUES ( 5 );
3029
3131
INSERT INTO t2 VALUES ( 6 );
3030
 
CREATE TABLE t3 ( c3 integer );
 
3132
CREATE TEMPORARY TABLE t3 ( c3 integer ) ENGINE=MyISAM;
3031
3133
INSERT INTO t3 VALUES ( 7 );
3032
3134
INSERT INTO t3 VALUES ( 8 );
3033
3135
SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 
3036
3138
2       NULL
3037
3139
3       NULL
3038
3140
DROP TABLE t1,t2,t3;
3039
 
CREATE TABLE `t1` (
3040
 
`itemid` bigint(20) unsigned NOT NULL auto_increment,
3041
 
`sessionid` bigint(20) unsigned default NULL,
3042
 
`time` int(10) unsigned NOT NULL default '0',
3043
 
`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
3044
 
NULL default '',
3045
 
`data` text collate latin1_general_ci NOT NULL,
3046
 
PRIMARY KEY  (`itemid`)
3047
 
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
3048
 
INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
3049
 
CREATE TABLE `t2` (
3050
 
`sessionid` bigint(20) unsigned NOT NULL auto_increment,
3051
 
`pid` int(10) unsigned NOT NULL default '0',
3052
 
`date` int(10) unsigned NOT NULL default '0',
3053
 
`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
3054
 
PRIMARY KEY  (`sessionid`)
3055
 
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
3056
 
INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
3057
 
SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;
3058
 
ip      count( e.itemid )
3059
 
10.10.10.1      1
3060
 
drop tables t1,t2;
3061
 
CREATE TABLE t1 (EMPNUM   CHAR(3));
3062
 
CREATE TABLE t2 (EMPNUM   CHAR(3) );
 
3141
#
 
3142
# Correct building of equal fields list (do not include outer
 
3143
# fields) (BUG#6384)
 
3144
#
 
3145
CREATE TEMPORARY TABLE t1 (EMPNUM   CHAR(3)) ENGINE=MyISAM;
 
3146
CREATE TEMPORARY TABLE t2 (EMPNUM   CHAR(3) ) ENGINE=MyISAM;
3063
3147
INSERT INTO t1 VALUES ('E1'),('E2');
3064
3148
INSERT INTO t2 VALUES ('E1');
3065
3149
DELETE FROM t1
3071
3155
EMPNUM
3072
3156
E1
3073
3157
DROP TABLE t1,t2;
3074
 
CREATE TABLE t1(select_id BIGINT, values_id BIGINT);
 
3158
#
 
3159
# Test for bug #11487: range access in a subquery
 
3160
#
 
3161
CREATE TEMPORARY TABLE t1(select_id BIGINT, values_id BIGINT) ENGINE=MyISAM;
3075
3162
INSERT INTO t1 VALUES (1, 1);
3076
 
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT, 
3077
 
PRIMARY KEY(select_id,values_id));
 
3163
CREATE TEMPORARY TABLE t2 (select_id BIGINT, values_id BIGINT, 
 
3164
PRIMARY KEY(select_id,values_id)) ENGINE=MyISAM;
3078
3165
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
3079
3166
SELECT values_id FROM t1 
3080
3167
WHERE values_id IN (SELECT values_id FROM t2
3092
3179
values_id
3093
3180
1
3094
3181
DROP TABLE t1, t2;
3095
 
create table t1 (fld enum('0','1'));
 
3182
# BUG#11821 : Select from subselect using aggregate function on an enum
 
3183
# segfaults:
 
3184
create temporary table t1 (fld enum('0','1')) ENGINE=MyISAM;
3096
3185
insert into t1 values ('1');
3097
3186
select * from (select max(fld) from t1) as foo;
3098
3187
max(fld)
3099
3188
1
3100
3189
drop table t1;
3101
 
CREATE TABLE t1 (a int, b int);
3102
 
CREATE TABLE t2 (c int, d int);
3103
 
CREATE TABLE t3 (e int);
 
3190
#
 
3191
# Test for bug #11762: subquery with an aggregate function in HAVING
 
3192
#
 
3193
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
 
3194
CREATE TEMPORARY TABLE t2 (c int, d int) ENGINE=MyISAM;
 
3195
CREATE TEMPORARY TABLE t3 (e int) ENGINE=MyISAM;
3104
3196
INSERT INTO t1 VALUES 
3105
3197
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
3106
3198
INSERT INTO t2 VALUES
3241
3333
3       20
3242
3334
4       40
3243
3335
DROP TABLE t1,t2,t3;
 
3336
#
 
3337
# Test for bug #16603: GROUP BY in a row subquery with a quantifier 
 
3338
#                      when an index is defined on the grouping field
3244
3339
CREATE TABLE t1 (a varchar(5), b varchar(10));
3245
3340
INSERT INTO t1 VALUES
3246
3341
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
3253
3348
EXPLAIN
3254
3349
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3255
3350
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
3256
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    9       Using where
3257
 
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    9       Using temporary; Using filesort
 
3351
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       Using where
 
3352
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    #       Using temporary; Using filesort
3258
3353
ALTER TABLE t1 ADD INDEX(a);
3259
3354
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3260
3355
a       b
3264
3359
EXPLAIN
3265
3360
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3266
3361
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
3267
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    9       Using where
3268
 
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    9       Using temporary; Using filesort
 
3362
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       Using where
 
3363
2       SUBQUERY        t1      index   NULL    a       23      NULL    #       
3269
3364
DROP TABLE t1;
 
3365
#
 
3366
# Bug#17366: Unchecked Item_int results in server crash
 
3367
#
3270
3368
create table t1( f1 int,f2 int);
3271
3369
insert into t1 values (1,1),(2,2);
3272
3370
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';
3274
3372
crash1
3275
3373
crash1
3276
3374
drop table t1;
3277
 
create table t1 (c int, key(c));
 
3375
#
 
3376
# Bug #18306: server crash on delete using subquery.
 
3377
#
 
3378
create temporary table t1 (c int, key(c)) ENGINE=MyISAM;
3278
3379
insert into t1 values (1142477582), (1142455969);
3279
 
create table t2 (a int, b int);
 
3380
create temporary table t2 (a int, b int) ENGINE=MyISAM;
3280
3381
insert into t2 values (2, 1), (1, 0);
3281
3382
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
3282
3383
drop table t1, t2;
 
3384
#
 
3385
# Bug#19077: A nested materialized derived table is used before being populated.
 
3386
#
3283
3387
create table t1 (i int, j bigint);
3284
3388
insert into t1 values (1, 2), (2, 2), (3, 2);
3285
3389
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
3286
3390
min(i)
3287
3391
1
3288
3392
drop table t1;
3289
 
CREATE TABLE t1 (i BIGINT UNSIGNED);
3290
 
INSERT INTO t1 VALUES (10000000000000000000);
 
3393
# 
 
3394
# Bug#19700: subselect returning BIGINT always returned it as SIGNED
 
3395
#
 
3396
CREATE TEMPORARY TABLE t1 (i BIGINT) ENGINE=MyISAM;
 
3397
INSERT INTO t1 VALUES (10000000000000000);
3291
3398
INSERT INTO t1 VALUES (1);
3292
 
CREATE TABLE t2 (i BIGINT UNSIGNED);
3293
 
INSERT INTO t2 VALUES (10000000000000000000);
 
3399
CREATE TEMPORARY TABLE t2 (i BIGINT) ENGINE=MyISAM;
 
3400
INSERT INTO t2 VALUES (10000000000000000);
3294
3401
INSERT INTO t2 VALUES (1);
3295
3402
/* simple test */
3296
3403
SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;
3297
3404
i
3298
 
10000000000000000000
 
3405
10000000000000000
3299
3406
1
3300
3407
/* subquery test */
3301
3408
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
3302
3409
i
3303
 
10000000000000000000
 
3410
10000000000000000
3304
3411
/* subquery test with cast*/
3305
 
SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);
 
3412
SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);
3306
3413
i
3307
 
10000000000000000000
 
3414
10000000000000000
3308
3415
DROP TABLE t1;
3309
3416
DROP TABLE t2;
3310
 
CREATE TABLE t1 (
3311
 
id bigint(20) unsigned NOT NULL auto_increment,
 
3417
# 
 
3418
# Bug#20519: subselect with LIMIT M, N
 
3419
#
 
3420
CREATE TEMPORARY TABLE t1 (
 
3421
id bigint NOT NULL auto_increment,
3312
3422
name varchar(255) NOT NULL,
3313
 
PRIMARY KEY  (id)
3314
 
);
 
3423
PRIMARY KEY  (id))
 
3424
ENGINE=MyISAM;
3315
3425
INSERT INTO t1 VALUES
3316
3426
(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
3317
3427
CREATE TABLE t2 (
3318
 
id bigint(20) unsigned NOT NULL auto_increment,
3319
 
mid bigint(20) unsigned NOT NULL,
 
3428
id bigint NOT NULL auto_increment,
 
3429
mid bigint NOT NULL,
3320
3430
date date NOT NULL,
3321
 
PRIMARY KEY  (id)
3322
 
);
 
3431
PRIMARY KEY  (id));
3323
3432
INSERT INTO t2 VALUES 
3324
3433
(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
3325
3434
(4, 2, '2006-04-20'), (5, 1, '2006-05-01');
3352
3461
2       Joe     2006-04-20      2006-04-06
3353
3462
3       Frank   2006-04-13      NULL
3354
3463
DROP TABLE t1,t2;
 
3464
#
 
3465
# Bug#20869: subselect with range access by DESC
 
3466
#
3355
3467
CREATE TABLE t1 (
3356
 
i1 int(11) NOT NULL default '0',
3357
 
i2 int(11) NOT NULL default '0',
3358
 
t datetime NOT NULL default '0000-00-00 00:00:00',
3359
 
PRIMARY KEY  (i1,i2,t)
3360
 
);
 
3468
i1 int NOT NULL default '0',
 
3469
i2 int NOT NULL default '0',
 
3470
t datetime,
 
3471
PRIMARY KEY  (i1,i2,t));
3361
3472
INSERT INTO t1 VALUES 
3362
3473
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
3363
3474
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
3366
3477
(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),
3367
3478
(24,2,'2005-05-27 12:40:06');
3368
3479
CREATE TABLE t2 (
3369
 
i1 int(11) NOT NULL default '0',
3370
 
i2 int(11) NOT NULL default '0',
 
3480
i1 int NOT NULL default '0',
 
3481
i2 int NOT NULL default '0',
3371
3482
t datetime default NULL,
3372
 
PRIMARY KEY  (i1)
3373
 
);
 
3483
PRIMARY KEY  (i1));
3374
3484
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
3375
3485
EXPLAIN
3376
3486
SELECT * FROM t1,t2
3378
3488
WHERE t1.t < t2.t  AND t1.i2=1 AND t2.i1=t1.i1
3379
3489
ORDER BY t1.t DESC LIMIT 1);
3380
3490
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
3381
 
1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       
3382
 
1       PRIMARY t1      index   NULL    PRIMARY 16      NULL    11      Using where; Using index
3383
 
2       DEPENDENT SUBQUERY      t1      range   PRIMARY PRIMARY 16      NULL    5       Using where; Using index
 
3491
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    1       
 
3492
1       PRIMARY t1      index   NULL    PRIMARY 16      NULL    11      Using where; Using index; Using join buffer
 
3493
2       DEPENDENT SUBQUERY      t1      ref     PRIMARY PRIMARY 8       test.t2.i1,const        5       Using where; Using index; Using filesort
3384
3494
SELECT * FROM t1,t2
3385
3495
WHERE t1.t = (SELECT t1.t FROM t1 
3386
3496
WHERE t1.t < t2.t  AND t1.i2=1 AND t2.i1=t1.i1
3388
3498
i1      i2      t       i1      i2      t
3389
3499
24      1       2005-05-27 12:40:30     24      1       2006-06-20 12:29:40
3390
3500
DROP TABLE t1, t2;
 
3501
#
 
3502
# Bug#14654 : Cannot select from the same table twice within a UNION
 
3503
# statement 
 
3504
#
3391
3505
CREATE TABLE t1 (i INT);
3392
3506
(SELECT i FROM t1) UNION (SELECT i FROM t1);
3393
3507
i
3394
 
SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS 
 
3508
SELECT * FROM t1 WHERE NOT EXISTS 
3395
3509
(
3396
3510
(SELECT i FROM t1) UNION 
3397
3511
(SELECT i FROM t1)
3398
3512
);
3399
3513
i
 
3514
#TODO:not supported
3400
3515
SELECT * FROM t1 
3401
3516
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
3402
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 1
 
3517
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
 
3518
#TODO:not supported
3403
3519
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
3404
3520
from t1;
3405
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))
 
3521
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))
3406
3522
from t1' at line 1
3407
3523
explain select * from t1 where not exists 
3408
3524
((select t11.i from t1 t11) union (select t12.i from t1 t12));
3409
3525
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
3410
 
1       PRIMARY t1      system  NULL    NULL    NULL    NULL    0       const row not found
3411
 
2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
3412
 
3       UNION   NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table
3413
 
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    
3414
 
DROP TABLE t1;
3415
 
CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
3416
 
insert into t1 (a) values (FLOOR(rand() * 100));
3417
 
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3418
 
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3419
 
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3420
 
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3421
 
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3422
 
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3423
 
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3424
 
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3425
 
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3426
 
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3427
 
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3428
 
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3429
 
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3430
 
SELECT a, 
3431
 
(SELECT REPEAT(' ',250) FROM t1 i1 
3432
 
WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a 
3433
 
FROM t1 ORDER BY a LIMIT 5;
3434
 
a       a
3435
 
0       NULL
3436
 
0       NULL
3437
 
0       NULL
3438
 
0       NULL
3439
 
0       NULL
3440
 
DROP TABLE t1;
 
3526
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       
 
3527
2       SUBQUERY        t11     ALL     NULL    NULL    NULL    NULL    #       
 
3528
3       UNION   t12     ALL     NULL    NULL    NULL    NULL    #       
 
3529
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    #       
 
3530
DROP TABLE t1;
 
3531
#
 
3532
# Bug #21540: Subqueries with no from and aggregate functions return 
 
3533
#              wrong results
3441
3534
CREATE TABLE t1 (a INT, b INT);
3442
3535
CREATE TABLE t2 (a INT);
3443
3536
INSERT INTO t2 values (1);
3458
3551
2       2
3459
3552
1       1
3460
3553
1       1
3461
 
SELECT COUNT(DISTINCT t1.b), 
3462
 
(SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
3463
 
FROM t1 GROUP BY t1.a;
3464
 
COUNT(DISTINCT t1.b)    (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
3465
 
2       2
3466
 
1       1
3467
 
1       1
3468
 
SELECT (
3469
 
SELECT (
3470
 
SELECT COUNT(DISTINCT t1.b)
3471
 
)
3472
 
) 
3473
 
FROM t1 GROUP BY t1.a;
3474
 
(
3475
 
SELECT (
3476
 
SELECT COUNT(DISTINCT t1.b)
3477
 
)
3478
 
)
3479
 
2
3480
 
1
3481
 
1
3482
3554
SELECT (
3483
3555
SELECT (
3484
3556
SELECT (
3499
3571
2
3500
3572
2
3501
3573
DROP TABLE t1,t2;
3502
 
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
3503
 
CREATE TABLE t2 (x int auto_increment, y int, z int,
3504
 
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
 
3574
#
 
3575
# Bug #21727: Correlated subquery that requires filesort:
 
3576
#             slow with big sort_buffer_size 
 
3577
#
 
3578
CREATE TEMPORARY TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)) ENGINE=MyISAM;
 
3579
CREATE TEMPORARY TABLE t2 (x int auto_increment, y int, z int,
 
3580
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)) ENGINE=MyISAM;
3505
3581
SET SESSION sort_buffer_size = 32 * 1024;
3506
3582
Warnings:
3507
 
Warning 1292    Truncated incorrect sort_buffer_size value: '32768'
3508
 
SELECT SQL_NO_CACHE COUNT(*) 
 
3583
Error   1292    Truncated incorrect sort_buffer_size value: '32768'
 
3584
SELECT COUNT(*) 
3509
3585
FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
3510
3586
FROM t1) t;
3511
3587
COUNT(*)
3512
3588
3000
3513
3589
SET SESSION sort_buffer_size = 8 * 1024 * 1024;
3514
 
SELECT SQL_NO_CACHE COUNT(*) 
 
3590
SELECT COUNT(*) 
3515
3591
FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
3516
3592
FROM t1) t;
3517
3593
COUNT(*)
3518
3594
3000
3519
 
DROP TABLE t1,t2;
 
3595
DROP TABLE t2,t1;
 
3596
#
 
3597
# Bug #25219: EXIST subquery with UNION over a mix of
 
3598
#             correlated and uncorrelated selects
 
3599
#
3520
3600
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
3521
3601
CREATE TABLE t2 (c int);
3522
3602
INSERT INTO t1 VALUES ('aa', 1);
3538
3618
cc      3
3539
3619
dd      1
3540
3620
INSERT INTO t2 VALUES (2);
3541
 
CREATE TABLE t3 (c int);
 
3621
CREATE TEMPORARY TABLE t3 (c int) ENGINE=MyISAM;
3542
3622
INSERT INTO t3 VALUES (1);
3543
3623
SELECT * FROM t1
3544
3624
WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
3550
3630
cc      3
3551
3631
dd      1
3552
3632
DROP TABLE t1,t2,t3;
 
3633
#
 
3634
# Bug#21904 (parser problem when using IN with a double "(())")
 
3635
#
3553
3636
DROP TABLE IF EXISTS t1;
3554
3637
DROP TABLE IF EXISTS t2;
3555
3638
DROP TABLE IF EXISTS t1xt2;
3556
 
CREATE TABLE t1 (
3557
 
id_1 int(5) NOT NULL,
3558
 
t varchar(4) DEFAULT NULL
3559
 
);
3560
 
CREATE TABLE t2 (
3561
 
id_2 int(5) NOT NULL,
3562
 
t varchar(4) DEFAULT NULL
3563
 
);
3564
 
CREATE TABLE t1xt2 (
3565
 
id_1 int(5) NOT NULL,
3566
 
id_2 int(5) NOT NULL
3567
 
);
 
3639
CREATE TEMPORARY TABLE t1 (
 
3640
id_1 int NOT NULL,
 
3641
t varchar(4) DEFAULT NULL)
 
3642
ENGINE=MyISAM;
 
3643
CREATE TEMPORARY TABLE t2 (
 
3644
id_2 int NOT NULL,
 
3645
t varchar(4) DEFAULT NULL)
 
3646
ENGINE=MyISAM;
 
3647
CREATE TEMPORARY TABLE t1xt2 (
 
3648
id_1 int NOT NULL,
 
3649
id_2 int NOT NULL)
 
3650
ENGINE=MyISAM;
3568
3651
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
3569
3652
INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');
3570
3653
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
 
3654
# subselect returns 0 rows
3571
3655
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3572
3656
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3573
3657
id_1
3599
3683
3
3600
3684
4
3601
3685
insert INTO t1xt2 VALUES (1, 12);
 
3686
# subselect returns 1 row
3602
3687
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3603
3688
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3604
3689
id_1
3630
3715
3
3631
3716
4
3632
3717
insert INTO t1xt2 VALUES (2, 12);
 
3718
# subselect returns more than 1 row
3633
3719
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3634
3720
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3635
3721
id_1
3663
3749
DROP TABLE t1;
3664
3750
DROP TABLE t2;
3665
3751
DROP TABLE t1xt2;
3666
 
CREATE TABLE t1 (a int);
 
3752
#
 
3753
# Bug #26728: derived table with concatanation of literals in select list
 
3754
#  
 
3755
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
3667
3756
INSERT INTO t1 VALUES (3), (1), (2);
3668
3757
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
3669
3758
col1    col2
3676
3765
this is a test. 1
3677
3766
this is a test. 2
3678
3767
DROP table t1;
3679
 
CREATE TABLE t1 (a int, b int);
3680
 
CREATE TABLE t2 (m int, n int);
 
3768
#
 
3769
# Bug #27257: COUNT(*) aggregated in outer query
 
3770
#  
 
3771
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
 
3772
CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;
3681
3773
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3682
3774
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3683
3775
SELECT COUNT(*), a,
3701
3793
2       2
3702
3794
3       3
3703
3795
DROP TABLE t1,t2;
3704
 
CREATE TABLE t1 (a int, b int);
3705
 
CREATE TABLE t2 (m int, n int);
 
3796
#
 
3797
# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument 
 
3798
#  
 
3799
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
 
3800
CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;
3706
3801
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3707
3802
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3708
3803
SELECT COUNT(*) c, a,
3720
3815
3       3       4
3721
3816
1       4       2,2
3722
3817
DROP table t1,t2;
 
3818
#
 
3819
# Bug#27321: Wrong subquery result in a grouping select
 
3820
#
3723
3821
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
3724
3822
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
3725
3823
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
3785
3883
2       o
3786
3884
3       p
3787
3885
DROP TABLE t1;
 
3886
#
 
3887
# Bug #27363: nested aggregates in outer, subquery / sum(select
 
3888
# count(outer))
 
3889
#
3788
3890
CREATE TABLE t1 (a INT);
3789
3891
INSERT INTO t1 values (1),(1),(1),(1);
3790
 
CREATE TABLE t2 (x INT);
 
3892
CREATE TEMPORARY TABLE t2 (x INT) ENGINE=MyISAM;
3791
3893
INSERT INTO t1 values (1000),(1001),(1002);
3792
3894
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
3793
3895
ERROR HY000: Invalid use of group function
3794
3896
SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
3795
3897
ERROR HY000: Invalid use of group function
3796
 
SELECT COUNT(1) FROM DUAL;
3797
 
COUNT(1)
3798
 
1
3799
 
SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1;
3800
 
ERROR HY000: Invalid use of group function
3801
3898
SELECT 
3802
3899
SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
3803
3900
FROM t1;
3807
3904
FROM t1;
3808
3905
ERROR HY000: Invalid use of group function
3809
3906
DROP TABLE t1,t2;
 
3907
#
 
3908
# Bug #27807: Server crash when executing subquery with EXPLAIN
 
3909
#  
3810
3910
CREATE TABLE t1 (a int, b int, KEY (a));
3811
3911
INSERT INTO t1 VALUES (1,1),(2,1);
3812
3912
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
3814
3914
1       PRIMARY t1      ref     a       a       5       const   1       Using where; Using index
3815
3915
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       Using temporary; Using filesort
3816
3916
DROP TABLE t1;
 
3917
#
 
3918
# Bug #28377: grouping query with a correlated subquery in WHERE condition
 
3919
#  
3817
3920
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
3818
3921
INSERT INTO t1 VALUES
3819
3922
(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
3820
 
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
 
3923
CREATE TEMPORARY TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;
3821
3924
INSERT INTO t2 VALUES (7), (5), (1), (3);
3822
3925
SELECT id, st FROM t1 
3823
3926
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
3844
3947
2       GA
3845
3948
4       FL
3846
3949
DROP TABLE t1,t2;
3847
 
CREATE TABLE t1 (a int);
 
3950
#
 
3951
# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table
 
3952
#             over a grouping subselect
 
3953
# 
 
3954
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
3848
3955
INSERT INTO t1 VALUES (1), (2);
3849
3956
EXPLAIN EXTENDED
3850
3957
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
3852
3959
1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    2       100.00  
3853
3960
2       DERIVED t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using temporary; Using filesort
3854
3961
Warnings:
3855
 
Note    1003    select "res"."count(*)" AS "count(*)" from (select count(0) AS "count(*)" from "test"."t1" group by "test"."t1"."a") "res"
 
3962
Note    1003    select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`
3856
3963
DROP TABLE t1;
 
3964
#
 
3965
# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1 
 
3966
#
3857
3967
CREATE TABLE t1 (
3858
3968
a varchar(255) default NULL,
3859
3969
b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3860
 
INDEX idx(a,b)
3861
 
);
 
3970
INDEX idx(a,b));
 
3971
Warnings:
 
3972
Warning 1071    Specified key was too long; max key length is 767 bytes
3862
3973
CREATE TABLE t2 (
3863
 
a varchar(255) default NULL
3864
 
);
 
3974
a varchar(255) default NULL);
3865
3975
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
3866
3976
INSERT INTO t1 SELECT * FROM t1;
3867
3977
INSERT INTO t1 SELECT * FROM t1;
3876
3986
INSERT INTO `t2` VALUES ('asdf');
3877
3987
SET session sort_buffer_size=8192;
3878
3988
Warnings:
3879
 
Warning 1292    Truncated incorrect sort_buffer_size value: '8192'
 
3989
Error   1292    Truncated incorrect sort_buffer_size value: '8192'
3880
3990
SELECT (SELECT 1 FROM  t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;
3881
3991
d1
3882
3992
1
3883
3993
1
3884
3994
DROP TABLE t1,t2;
3885
 
CREATE TABLE t1 (a INTEGER, b INTEGER);
3886
 
CREATE TABLE t2 (x INTEGER);
 
3995
#
 
3996
# Bug #27333: subquery grouped for aggregate of outer query / no aggregate
 
3997
# of subquery
 
3998
#
 
3999
CREATE TEMPORARY TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;
 
4000
CREATE TEMPORARY TABLE t2 (x INTEGER) ENGINE=MyISAM;
3887
4001
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
3888
4002
INSERT INTO t2 VALUES (1), (2);
 
4003
# wasn't failing, but should
3889
4004
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
3890
4005
ERROR 21000: Subquery returns more than 1 row
 
4006
# fails as it should
3891
4007
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
3892
4008
ERROR 21000: Subquery returns more than 1 row
3893
4009
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
3894
4010
(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)
3895
4011
3.3333
3896
4012
DROP TABLE t1,t2;
 
4013
# second test case from 27333
3897
4014
CREATE TABLE t1 (a INT, b INT);
3898
4015
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
 
4016
# returns no rows, when it should
3899
4017
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
3900
4018
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
3901
4019
GROUP BY a1.a;
3902
4020
a       COUNT(*)
3903
4021
1       3
3904
4022
DROP TABLE t1;
3905
 
CREATE TABLE t1 (a INT);
3906
 
CREATE TABLE t2 (a INT);
 
4023
#test cases from 29297
 
4024
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;
 
4025
CREATE TEMPORARY TABLE t2 (a INT) ENGINE=MyISAM;
3907
4026
INSERT INTO t1 VALUES (1),(2);
3908
4027
INSERT INTO t2 VALUES (1),(2);
3909
4028
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
3915
4034
(SELECT SUM(t1.a) FROM t2 WHERE a=1)
3916
4035
3
3917
4036
DROP TABLE t1,t2;
3918
 
CREATE TABLE t1 (a1 INT, a2 INT);
3919
 
CREATE TABLE t2 (b1 INT, b2 INT);
 
4037
#
 
4038
# Bug #31884: Assertion + crash in subquery in the SELECT clause.
 
4039
#
 
4040
CREATE TEMPORARY TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;
 
4041
CREATE TEMPORARY TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;
3920
4042
INSERT INTO t1 VALUES (100, 200);
3921
4043
INSERT INTO t1 VALUES (101, 201);
3922
4044
INSERT INTO t2 VALUES (101, 201);
3926
4048
0
3927
4049
0
3928
4050
DROP TABLE t1, t2;
3929
 
CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
3930
 
INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
3931
 
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
3932
 
s1      s2
3933
 
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
3934
 
s1      s2
3935
 
CREATE INDEX I1 ON t1 (s1);
3936
 
CREATE INDEX I2 ON t1 (s2);
3937
 
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
3938
 
s1      s2
3939
 
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
3940
 
s1      s2
3941
 
TRUNCATE t1;
3942
 
INSERT INTO t1 VALUES (0x41,0x41);
3943
 
SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
3944
 
s1      s2
3945
 
DROP TABLE t1;
3946
 
CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));
3947
 
CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));
3948
 
CREATE TABLE t3 (a3 BINARY(2) default '0');
3949
 
INSERT INTO t1 VALUES (1),(2),(3),(4);
3950
 
INSERT INTO t2 VALUES (1),(2),(3);
3951
 
INSERT INTO t3 VALUES (1),(2),(3);
3952
 
SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;
3953
 
LEFT(t2.a2, 1)
3954
 
1
3955
 
2
3956
 
3
3957
 
SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;
3958
 
a1      t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2)
3959
 
1       0
3960
 
2       0
3961
 
3       0
3962
 
4       0
3963
 
DROP TABLE t1,t2,t3;
3964
 
CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));
3965
 
CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);
3966
 
CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);
3967
 
INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
3968
 
INSERT INTO t2 VALUES (2), (3), (4), (5);
3969
 
INSERT INTO t3 VALUES (10), (20), (30);
3970
 
SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;
3971
 
LEFT(t1.a1,1)
3972
 
1
3973
 
2
3974
 
3
3975
 
SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
3976
 
a2
3977
 
DROP TABLE t1, t2, t3;
 
4051
#
 
4052
# Bug #30788: Inconsistent retrieval of char/varchar
 
4053
#
3978
4054
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
3979
4055
INSERT INTO t1 VALUES ('a', 'aa');
3980
4056
INSERT INTO t1 VALUES ('a', 'aaa');
3981
4057
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3982
4058
a       b
 
4059
a       aa
 
4060
a       aaa
3983
4061
CREATE INDEX I1 ON t1 (a);
3984
4062
CREATE INDEX I2 ON t1 (b);
3985
4063
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3986
4064
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
3987
 
1       PRIMARY t1      index   I1      I1      2       NULL    2       Using index; LooseScan
3988
 
1       PRIMARY t1      ref     I2      I2      13      test.t1.a       2       Using index condition
 
4065
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       Using where
 
4066
2       SUBQUERY        t1      index   NULL    I1      7       NULL    #       Using index
3989
4067
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3990
4068
a       b
 
4069
a       aa
 
4070
a       aaa
3991
4071
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
3992
4072
INSERT INTO t2 SELECT * FROM t1;
3993
4073
CREATE INDEX I1 ON t2 (a);
3994
4074
CREATE INDEX I2 ON t2 (b);
3995
4075
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
3996
4076
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
3997
 
1       PRIMARY t2      index   I1      I1      4       NULL    2       Using index; LooseScan
3998
 
1       PRIMARY t2      ref     I2      I2      13      test.t2.a       2       Using index condition
 
4077
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    #       Using where
 
4078
2       SUBQUERY        t2      index   NULL    I1      7       NULL    #       Using index
3999
4079
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
4000
4080
a       b
 
4081
a       aa
 
4082
a       aaa
4001
4083
EXPLAIN
4002
4084
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
4003
4085
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
4004
 
1       PRIMARY t1      index   I1      I1      2       NULL    2       Using where; Using index; LooseScan
4005
 
1       PRIMARY t1      ref     I2      I2      13      test.t1.a       2       Using index condition
 
4086
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       Using where
 
4087
2       SUBQUERY        t1      index   NULL    I1      7       NULL    #       Using where; Using index
4006
4088
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
4007
4089
a       b
 
4090
a       aa
 
4091
a       aaa
4008
4092
DROP TABLE t1,t2;
 
4093
#
 
4094
# Bug #32400: Complex SELECT query returns correct result only on some
 
4095
# occasions
 
4096
#
4009
4097
CREATE TABLE t1(a INT, b INT);
4010
4098
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
4011
4099
EXPLAIN 
4022
4110
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
4023
4111
GROUP BY a;
4024
4112
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
4025
 
1       PRIMARY t1_outer        ALL     NULL    NULL    NULL    NULL    4       Using where; Using temporary; Using filesort
4026
 
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    4       Using where
 
4113
1       PRIMARY t1_outer        ALL     NULL    NULL    NULL    NULL    #       Using where; Using temporary; Using filesort
 
4114
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    #       Using where
4027
4115
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
4028
4116
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
4029
4117
GROUP BY a;
4031
4119
1       2
4032
4120
2       4
4033
4121
DROP TABLE t1;
4034
 
CREATE TABLE t1 (a INT);
 
4122
#
 
4123
# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
 
4124
#
 
4125
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;
4035
4126
CREATE TABLE t2 (a INT);
4036
4127
INSERT INTO t1 VALUES (1),(2);
4037
4128
INSERT INTO t2 VALUES (1),(2);
4046
4137
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
4047
4138
Warnings:
4048
4139
Note    1276    Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
4049
 
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"))
 
4140
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`))
4050
4141
EXPLAIN EXTENDED
4051
4142
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION 
4052
4143
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
4053
4144
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
4054
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
4055
 
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
4056
 
3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
4057
 
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
 
4145
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
4146
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
4147
3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
4148
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    #       NULL    
4058
4149
Warnings:
4059
4150
Note    1276    Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
4060
4151
Note    1276    Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
4061
 
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")))
 
4152
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`)))
4062
4153
DROP TABLE t1,t2;
4063
 
CREATE TABLE t4 (
 
4154
#
 
4155
# BUG#33794 "MySQL crashes executing specific query on specific dump"
 
4156
#
 
4157
CREATE TEMPORARY TABLE t4 (
4064
4158
f7 varchar(32) collate utf8_bin NOT NULL default '',
4065
4159
f10 varchar(32) collate utf8_bin default NULL,
4066
 
PRIMARY KEY  (f7)
4067
 
);
 
4160
PRIMARY KEY  (f7))
 
4161
ENGINE=MyISAM;
4068
4162
INSERT INTO t4 VALUES(1,1), (2,null);
4069
4163
CREATE TABLE t2 (
4070
4164
f4 varchar(32) collate utf8_bin NOT NULL default '',
4071
4165
f2 varchar(50) collate utf8_bin default NULL,
4072
4166
f3 varchar(10) collate utf8_bin default NULL,
4073
4167
PRIMARY KEY  (f4),
4074
 
UNIQUE KEY uk1 (f2)
4075
 
);
 
4168
UNIQUE KEY uk1 (f2));
4076
4169
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
4077
 
CREATE TABLE t1 (
 
4170
CREATE TEMPORARY TABLE t1  (
4078
4171
f8 varchar(32) collate utf8_bin NOT NULL default '',
4079
4172
f1 varchar(10) collate utf8_bin default NULL,
4080
4173
f9 varchar(32) collate utf8_bin default NULL,
4081
 
PRIMARY KEY  (f8)
4082
 
);
 
4174
PRIMARY KEY  (f8))
 
4175
ENGINE=MyISAM;
4083
4176
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
4084
 
CREATE TABLE t3 (
 
4177
CREATE TEMPORARY TABLE t3 (
4085
4178
f6 varchar(32) collate utf8_bin NOT NULL default '',
4086
4179
f5 varchar(50) collate utf8_bin default NULL,
4087
 
PRIMARY KEY (f6)
4088
 
);
 
4180
PRIMARY KEY (f6))
 
4181
ENGINE=MyISAM;
4089
4182
INSERT INTO t3 VALUES (1,null), (2,null);
4090
4183
SELECT
4091
4184
IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,
4109
4202
2       NULL    NULL
4110
4203
DROP TABLE t1, t2, t3, t4;
4111
4204
End of 5.0 tests.
4112
 
create table t_out (subcase char(3),
4113
 
a1 char(2), b1 char(2), c1 char(2));
 
4205
#
 
4206
#  Test [NOT] IN truth table (both as top-level and general predicate).
 
4207
#
 
4208
create temporary table t_out (subcase char(3),
 
4209
a1 char(2), b1 char(2), c1 char(2)) ENGINE=MyISAM;
4114
4210
create table t_in  (a2 char(2), b2 char(2), c2 char(2));
4115
4211
insert into t_out values ('A.1','2a', NULL, '2a');
 
4212
#------------------------- A.2 - impossible
4116
4213
insert into t_out values ('A.3', '2a', NULL, '2a');
4117
4214
insert into t_out values ('A.4', '2a', NULL, 'xx');
4118
4215
insert into t_out values ('B.1', '2a', '2a', '2a');
4212
4309
NOT((a1, b1, c1) IN (select * from t_in));
4213
4310
not_pred_in
4214
4311
F
 
4312
# test non-top level result indirectly
4215
4313
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4216
4314
where subcase = 'A.3' and
4217
4315
((a1, b1, c1) IN (select * from t_in)) is NULL and
4282
4380
NOT((a1, b1, c1) IN (select * from t_in));
4283
4381
not_pred_in
4284
4382
F
 
4383
# test non-top level result indirectly
4285
4384
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4286
4385
where subcase = 'B.3' and
4287
4386
((a1, b1, c1) IN (select * from t_in)) is NULL and
4306
4405
T
4307
4406
drop table t_out;
4308
4407
drop table t_in;
 
4408
#
 
4409
# Bug#20835 (literal string with =any values)
 
4410
#
4309
4411
CREATE TABLE t1 (s1 char(1));
4310
4412
INSERT INTO t1 VALUES ('a');
4311
 
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
 
4413
SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);
4312
4414
s1
4313
4415
a
4314
4416
DROP TABLE t1;
 
4417
#
 
4418
# Bug#33204: INTO is allowed in subselect, causing inconsistent results
 
4419
#
4315
4420
CREATE TABLE t1( a INT );
4316
4421
INSERT INTO t1 VALUES (1),(2);
4317
4422
CREATE TABLE t2( a INT, b INT );
4318
4423
SELECT * 
4319
4424
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
4320
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2
 
4425
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
4321
4426
SELECT * 
4322
4427
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
4323
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
 
4428
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
4324
4429
SELECT * 
4325
4430
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
4326
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
 
4431
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
4327
4432
SELECT * FROM ( 
4328
4433
SELECT 1 a 
4329
4434
UNION 
4330
4435
SELECT a INTO @var FROM t1 WHERE a = 2 
4331
4436
) t1a;
4332
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2 
 
4437
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 
4333
4438
) t1a' at line 2
4334
4439
SELECT * FROM ( 
4335
4440
SELECT 1 a 
4336
4441
UNION 
4337
4442
SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2 
4338
4443
) t1a;
4339
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2 
 
4444
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 
4340
4445
) t1a' at line 2
4341
4446
SELECT * FROM ( 
4342
4447
SELECT 1 a 
4343
4448
UNION 
4344
4449
SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2 
4345
4450
) t1a;
4346
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2 
 
4451
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 
4347
4452
) t1a' at line 2
4348
4453
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
4349
4454
a
4365
4470
a
4366
4471
1
4367
4472
2
 
4473
# This was not allowed previously. Possibly, it should be allowed on the future.
 
4474
# For now, the intent is to keep the fix as non-intrusive as possible.
4368
4475
SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
4369
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
 
4476
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
4370
4477
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
4371
4478
a
4372
4479
1
4374
4481
1
4375
4482
1
4376
4483
SELECT * FROM ((SELECT 1 a INTO @a)) t1a;
4377
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)) t1a' at line 1
 
4484
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
4378
4485
SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;
4379
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1
 
4486
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
4380
4487
SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;
4381
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1
 
4488
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
4382
4489
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;
4383
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)) t1a' at line 1
 
4490
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
4384
4491
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;
4385
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1
 
4492
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
4386
4493
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;
4387
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1
 
4494
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
4388
4495
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;
4389
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a))) t1a' at line 1
 
4496
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
4390
4497
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;
4391
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' ))) t1a' at line 1
 
4498
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
4392
4499
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;
4393
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' ))) t1a' at line 1
 
4500
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
4394
4501
SELECT * FROM (SELECT 1 a ORDER BY a) t1a;
4395
4502
a
4396
4503
1
4403
4510
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
4404
4511
a
4405
4512
1
 
4513
# Test of rule
 
4514
# table_factor:  '(' get_select_lex query_expression_body ')' opt_table_alias
 
4515
# UNION should not be allowed inside the parentheses, nor should
 
4516
# aliases after.
 
4517
# 
4406
4518
SELECT * FROM t1 JOIN  (SELECT 1 UNION SELECT 1) alias ON 1;
4407
4519
a       1
4408
4520
1       1
4409
4521
2       1
4410
4522
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
4411
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) ON 1' at line 1
 
4523
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
4412
4524
SELECT * FROM t1 JOIN  (t1 t1a UNION SELECT 1)  ON 1;
4413
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON 1' at line 1
 
4525
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
4414
4526
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
4415
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ON 1' at line 1
 
4527
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
4416
4528
SELECT * FROM t1 JOIN  (t1 t1a)  t1a ON 1;
4417
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a ON 1' at line 1
 
4529
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
4418
4530
SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;
4419
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a ON 1' at line 1
 
4531
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
4420
4532
SELECT * FROM t1 JOIN  (t1 t1a)  ON 1;
4421
4533
a       a
4422
4534
1       1
4453
4565
a       a
4454
4566
1       1
4455
4567
2       1
 
4568
# For the join, TABLE_LIST::select_lex == NULL
 
4569
# Check that we handle this.
4456
4570
SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;
4457
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a2' at line 1
 
4571
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
4458
4572
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );
4459
4573
a
4460
4574
1
4465
4579
a
4466
4580
1
4467
4581
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);
4468
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1
 
4582
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
4469
4583
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4470
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
 
4584
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
4471
4585
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4472
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
 
4586
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
4473
4587
SELECT * FROM t1 WHERE a = ( SELECT 1 );
4474
4588
a
4475
4589
1
4477
4591
a
4478
4592
1
4479
4593
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);
4480
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1
 
4594
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
4481
4595
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );
4482
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
 
4596
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
4483
4597
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );
4484
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
 
4598
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
4485
4599
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);
4486
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1
 
4600
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
4487
4601
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4488
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
 
4602
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
4489
4603
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4490
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
 
4604
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
4491
4605
SELECT ( SELECT 1 INTO @v );
4492
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
 
4606
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
4493
4607
SELECT ( SELECT 1 INTO OUTFILE 'file' );
4494
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
 
4608
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
4495
4609
SELECT ( SELECT 1 INTO DUMPFILE 'file' );
4496
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
 
4610
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
4497
4611
SELECT ( SELECT 1 UNION SELECT 1 INTO @v );
4498
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
 
4612
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
4499
4613
SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4500
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
 
4614
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
4501
4615
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4502
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
 
4616
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
 
4617
# Make sure context is popped when we leave the nested select
4503
4618
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
4504
4619
( SELECT a FROM t1 WHERE a = 1 )        a
4505
4620
1       1
4508
4623
( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a
4509
4624
1       1
4510
4625
1       2
 
4626
# Make sure we have feature F561 (see .yy file)
4511
4627
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
4512
4628
a       b
 
4629
# Make sure the parser does not allow nested UNIONs anywhere
4513
4630
SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );
4514
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
 
4631
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
4515
4632
( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
4516
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1' at line 1
 
4633
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
4517
4634
SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4518
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
 
4635
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
4519
4636
SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
4520
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1' at line 1
 
4637
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
4521
4638
SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4522
4639
( SELECT 1 UNION SELECT 1 UNION SELECT 1 )
4523
4640
1
4525
4642
((SELECT 1 UNION SELECT 1 UNION SELECT 1))
4526
4643
1
4527
4644
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4528
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
 
4645
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
4529
4646
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4530
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') UNION SELECT 1 )' at line 1
 
4647
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
4531
4648
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
4532
4649
1
4533
4650
1
4534
4651
SELECT * FROM t1 WHERE a =     ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4535
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
 
4652
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
4536
4653
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4537
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
 
4654
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
4538
4655
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4539
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
 
4656
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
4540
4657
SELECT * FROM t1 WHERE a IN    ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4541
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
 
4658
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
4542
4659
SELECT * FROM t1 WHERE a =     ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
4543
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
 
4660
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
4544
4661
SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
4545
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )  UNION SELECT 1 )' at line 1
 
4662
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
4546
4663
SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
4547
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )  UNION SELECT 1 )' at line 1
 
4664
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
4548
4665
SELECT * FROM t1 WHERE a IN    ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
4549
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
 
4666
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
4550
4667
SELECT * FROM t1 WHERE a =     ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4551
4668
a
4552
4669
1
4560
4677
a
4561
4678
1
4562
4679
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );
4563
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
 
4680
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
4564
4681
SELECT EXISTS(SELECT 1+1);
4565
4682
EXISTS(SELECT 1+1)
4566
4683
1
4567
4684
SELECT EXISTS(SELECT 1+1 INTO @test);
4568
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @test)' at line 1
 
4685
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
4569
4686
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );
4570
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
 
4687
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
4571
4688
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );
4572
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
 
4689
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
4573
4690
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
4574
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
 
4691
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
4575
4692
DROP TABLE t1, t2;
4576
 
set optimizer_switch='';
 
4693
set optimizer_switch=0;
4577
4694
show variables like 'optimizer_switch';
4578
4695
Variable_name   Value
4579
4696
optimizer_switch