← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/r/subselect.result

  • Committer: Brian Aker
  • Date: 2008-10-06 06:47:29 UTC
  • Revision ID: brian@tangent.org-20081006064729-2i9mhjkzyvow9xsm
Remove uint.

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/r/subselect.result
1
 
# Initialise
2
1
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
3
2
select (select 2);
4
3
(select 2)
178
177
3       8
179
178
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);
180
179
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
181
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
182
 
2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using filesort
183
 
3       UNION   t4      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
184
 
4       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    #       100.00  
185
 
NULL    UNION RESULT    <union1,3>      ALL     NULL    NULL    NULL    NULL    #       NULL    
 
180
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 
181
2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using filesort
 
182
3       UNION   t4      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
183
4       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    2       100.00  
 
184
NULL    UNION RESULT    <union1,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
186
185
Warnings:
187
186
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`)
188
187
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
196
195
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
197
196
(select * from t2 where a>1) as tt;
198
197
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
199
 
1       PRIMARY <derived3>      system  NULL    NULL    NULL    NULL    #       100.00  
200
 
3       DERIVED t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
201
 
2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where; Using filesort
 
198
1       PRIMARY <derived3>      system  NULL    NULL    NULL    NULL    1       100.00  
 
199
3       DERIVED t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 
200
2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using filesort
202
201
Warnings:
203
202
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`
204
203
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);
216
215
9       7.5000
217
216
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
218
217
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
219
 
1       PRIMARY t4      ALL     NULL    NULL    NULL    NULL    #       100.00  
220
 
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    #       100.00  
221
 
3       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
218
1       PRIMARY t4      ALL     NULL    NULL    NULL    NULL    3       100.00  
 
219
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  
 
220
3       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
222
221
Warnings:
223
222
Note    1276    Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
224
223
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`
243
242
a
244
243
6
245
244
3
246
 
# Rewrite: select * from t3 where not exists (select b from t2 where a <> b);
247
245
select * from t3 where a = all (select b from t2);
248
246
a
249
247
7
265
263
7
266
264
explain extended select * from t3 where a >= any (select b from t2);
267
265
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
268
 
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
269
 
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    #       100.00  
 
266
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
 
267
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    3       100.00  
270
268
Warnings:
271
269
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
272
270
select * from t3 where a >= all (select b from t2);
278
276
select * from t3 where a in (select * from t2);
279
277
ERROR 21000: Operand should contain 1 column(s)
280
278
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
281
 
# empty set
282
279
select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);
283
280
b       ma
284
281
insert into t2 values (2,10);
289
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);
290
287
b       ma
291
288
7       12
292
 
create temporary table t5 (a int) ENGINE=MyISAM;
 
289
create table t5 (a int);
293
290
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
294
291
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)       a
295
292
NULL    1
306
303
2       2
307
304
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;
308
305
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
309
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    #       100.00  
310
 
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
311
 
3       DEPENDENT UNION t5      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
312
 
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    #       NULL    
 
306
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  
 
307
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    1       100.00  Using where
 
308
3       DEPENDENT UNION t5      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 
309
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
313
310
Warnings:
314
311
Note    1276    Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
315
312
Note    1276    Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
316
313
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`
317
314
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
318
315
ERROR 21000: Subquery returns more than 1 row
319
 
create temporary table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)) ENGINE=MyISAM;
320
 
create temporary table t7( uq int primary key, name char(25)) ENGINE=MyISAM;
 
316
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
 
317
create table t7( uq int primary key, name char(25));
321
318
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
322
319
insert into t6 values (1,1),(1,2),(2,2),(1,3);
323
320
select * from t6 where exists (select * from t7 where uq = clinic_uq);
332
329
Warnings:
333
330
Note    1276    Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
334
331
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`))
335
 
# not unique fields
336
332
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
337
333
ERROR 23000: Column 'a' in field list is ambiguous
338
 
# different tipes & group functions
339
334
drop table t1,t2,t3;
340
335
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
341
336
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
342
337
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
343
338
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
344
 
CREATE TABLE t1 (a varchar(20),b date NULL);
 
339
CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00');
345
340
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
346
341
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
347
342
a       b
357
352
`email` varchar(60) NOT NULL default '',
358
353
PRIMARY KEY  (`pseudo`),
359
354
UNIQUE KEY `email` (`email`)
360
 
);
 
355
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
361
356
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
362
357
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
363
358
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
364
359
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');
365
360
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
366
 
1       PRIMARY t8      const   PRIMARY PRIMARY 142     const   #       100.00  Using index
367
 
4       SUBQUERY        t8      const   PRIMARY PRIMARY 142             #       100.00  Using index
368
 
2       SUBQUERY        t8      const   PRIMARY PRIMARY 142     const   #       100.00  
369
 
3       SUBQUERY        t8      const   PRIMARY PRIMARY 142             #       100.00  Using index
 
361
1       PRIMARY t8      const   PRIMARY PRIMARY 142     const   1       100.00  Using index
 
362
4       SUBQUERY        t8      const   PRIMARY PRIMARY 142             1       100.00  Using index
 
363
2       SUBQUERY        t8      const   PRIMARY PRIMARY 142     const   1       100.00  
 
364
3       SUBQUERY        t8      const   PRIMARY PRIMARY 142             1       100.00  Using index
370
365
Warnings:
371
366
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')))
372
367
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
381
376
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
382
377
ERROR 21000: Subquery returns more than 1 row
383
378
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
384
 
#searchconthardwarefr3 forumconthardwarefr7
385
 
CREATE TEMPORARY TABLE `t1` (
 
379
CREATE TABLE `t1` (
386
380
`topic` bigint NOT NULL default '0',
387
 
`date` date NULL,
 
381
`date` date NOT NULL default '0000-00-00',
388
382
`pseudo` varchar(35) NOT NULL default '',
389
383
PRIMARY KEY  (`pseudo`,`date`,`topic`),
390
384
KEY `topic` (`topic`)
424
418
Warnings:
425
419
Note    1003    select 1 AS `1` from `test`.`t1` where 1
426
420
drop table t1;
427
 
#forumconthardwarefr7 searchconthardwarefr7
428
421
CREATE TABLE `t1` (
429
422
`numeropost` bigint NOT NULL auto_increment,
430
423
`maxnumrep` int NOT NULL default '0',
431
424
PRIMARY KEY  (`numeropost`),
432
425
UNIQUE KEY `maxnumrep` (`maxnumrep`)
433
 
);
 
426
) ENGINE=MyISAM ROW_FORMAT=FIXED;
434
427
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
435
428
CREATE TABLE `t2` (
436
429
`mot` varchar(30) NOT NULL default '',
437
430
`topic` bigint NOT NULL default '0',
438
 
`date` date NULL,
 
431
`date` date NOT NULL default '0000-00-00',
439
432
`pseudo` varchar(35) NOT NULL default '',
440
433
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`)
441
 
);
 
434
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
442
435
INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
443
436
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
444
437
a
497
490
joce    40143   2002-10-22      joce    1
498
491
joce    43506   2002-10-22      joce    0
499
492
drop table t1,t2;
500
 
#forumconthardwarefr7
501
493
CREATE TABLE `t1` (
502
494
`numeropost` bigint NOT NULL auto_increment,
503
495
`maxnumrep` int NOT NULL default '0',
504
496
PRIMARY KEY  (`numeropost`),
505
497
UNIQUE KEY `maxnumrep` (`maxnumrep`)
506
 
);
 
498
) ENGINE=MyISAM ROW_FORMAT=FIXED;
507
499
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
508
500
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
509
501
ERROR 21000: Subquery returns more than 1 row
518
510
2
519
511
3
520
512
drop table t1;
521
 
#iftest
522
 
CREATE TEMPORARY TABLE t1 (field char(1) NOT NULL DEFAULT 'b') ENGINE=MyISAM;
 
513
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
523
514
INSERT INTO t1 VALUES ();
524
515
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
525
516
ERROR 21000: Subquery returns more than 1 row
526
517
drop table t1;
527
 
# threadhardwarefr7
528
518
CREATE TABLE `t1` (
529
519
`numeropost` bigint NOT NULL default '0',
530
520
`numreponse` int NOT NULL auto_increment,
532
522
PRIMARY KEY  (`numeropost`,`numreponse`),
533
523
UNIQUE KEY `numreponse` (`numreponse`),
534
524
KEY `pseudo` (`pseudo`,`numeropost`)
535
 
);
 
525
) ENGINE=MyISAM;
536
526
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
537
527
ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list)
538
528
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
544
534
ERROR 21000: Subquery returns more than 1 row
545
535
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
546
536
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
547
 
1       SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    #       NULL    Select tables optimized away
 
537
1       SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
548
538
Warnings:
549
539
Note    1003    select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)
550
540
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
551
541
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
552
 
1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 12      const,const     #       100.00  Using index
553
 
2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    #       NULL    Select tables optimized away
 
542
1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 12      const,const     1       100.00  Using index
 
543
2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
554
544
Warnings:
555
545
Note    1003    select '3' AS `numreponse` from `test`.`t1` where 1
556
546
drop table t1;
557
 
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
 
547
CREATE TABLE t1 (a int);
558
548
INSERT INTO t1 VALUES (1);
559
549
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
560
550
1
561
551
1
562
552
drop table t1;
563
 
#update with subselects
564
553
create table t1 (a int NOT NULL, b int, primary key (a));
565
554
create table t2 (a int NOT NULL, b int, primary key (a));
566
555
insert into t1 values (0, 10),(1, 11),(2, 12);
581
570
1       21
582
571
2       22
583
572
drop table t1, t2;
584
 
#delete with subselects
585
573
create table t1 (a int NOT NULL, b int, primary key (a));
586
574
create table t2 (a int NOT NULL, b int, primary key (a));
587
575
insert into t1 values (0, 10),(1, 11),(2, 12);
604
592
0       10
605
593
1       11
606
594
drop table t1, t2;
607
 
#insert with subselects
 
595
create table t11 (a int NOT NULL, b int, primary key (a));
 
596
create table t12 (a int NOT NULL, b int, primary key (a));
 
597
create table t2 (a int NOT NULL, b int, primary key (a));
 
598
insert into t11 values (0, 10),(1, 11),(2, 12);
 
599
insert into t12 values (33, 10),(22, 11),(2, 12);
 
600
insert into t2 values (1, 21),(2, 12),(3, 23);
 
601
select * from t11;
 
602
a       b
 
603
0       10
 
604
1       11
 
605
2       12
 
606
select * from t12;
 
607
a       b
 
608
2       12
 
609
22      11
 
610
33      10
 
611
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 
612
ERROR HY000: You can't specify target table 't12' for update in FROM clause
 
613
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 
614
ERROR 21000: Subquery returns more than 1 row
 
615
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
 
616
select * from t11;
 
617
a       b
 
618
0       10
 
619
1       11
 
620
select * from t12;
 
621
a       b
 
622
22      11
 
623
33      10
 
624
drop table t11, t12, t2;
608
625
CREATE TABLE t1 (x int);
609
626
create table t2 (a int);
610
627
create table t3 (b int);
619
636
x
620
637
1
621
638
insert into t2 values (1);
622
 
INSERT INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
 
639
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
623
640
select * from t1;
624
641
x
625
642
1
631
648
2
632
649
3
633
650
3
634
 
# After this, only data based on old t1 records should have been added.
635
651
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
636
652
select * from t1;
637
653
x
641
657
3
642
658
11
643
659
11
644
 
INSERT INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
 
660
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
645
661
ERROR 42S22: Unknown column 'x' in 'field list'
646
 
INSERT INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
 
662
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
647
663
select * from t1;
648
664
x
649
665
1
653
669
11
654
670
11
655
671
2
656
 
#
657
 
#TODO: should be uncommented after bug 380 fix pushed
658
 
#INSERT INTO t1 (x) SELECT (SELECT SUM(a)+b FROM t2) from t3;
659
 
#select * from t1;
660
672
drop table t1, t2, t3;
661
 
#replace with subselects
662
673
CREATE TABLE t1 (x int not null, y int, primary key (x));
663
674
create table t2 (a int);
664
 
create temporary table t3 (a int) ENGINE=MyISAM;
 
675
create table t3 (a int);
665
676
insert into t2 values (1);
666
677
insert into t3 values (1),(2);
667
678
select * from t1;
678
689
select * from t1;
679
690
x       y
680
691
1       3
681
 
replace into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
 
692
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
682
693
select * from t1;
683
694
x       y
684
695
1       3
685
696
4       1
686
 
replace into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
 
697
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
687
698
select * from t1;
688
699
x       y
689
700
1       3
690
701
4       2
691
 
replace into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
 
702
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
692
703
select * from t1;
693
704
x       y
694
705
1       3
697
708
drop table t1, t2, t3;
698
709
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
699
710
ERROR HY000: No tables used
700
 
CREATE TABLE t2 (id int default NULL, KEY id (id));
 
711
CREATE TABLE t2 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
701
712
INSERT INTO t2 VALUES (1),(2);
702
713
SELECT * FROM t2 WHERE id IN (SELECT 1);
703
714
id
720
731
Warnings:
721
732
Note    1249    Select 3 was reduced during optimization
722
733
Note    1249    Select 2 was reduced during optimization
723
 
Note    1003    select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
 
734
Note    1003    select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
724
735
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
725
736
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
726
737
1       PRIMARY t2      index   NULL    id      5       NULL    2       100.00  Using where; Using index
742
753
id
743
754
1
744
755
2
745
 
CREATE TEMPORARY TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
 
756
CREATE TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
746
757
INSERT INTO t1 values (1),(1);
747
758
UPDATE t2 SET id=(SELECT * FROM t1);
748
759
ERROR 21000: Subquery returns more than 1 row
749
760
drop table t2, t1;
750
 
#NULL test
751
 
create temporary table t1 (a int) ENGINE=MyISAM;
 
761
create table t1 (a int);
752
762
insert into t1 values (1),(2),(3);
753
763
select 1 IN (SELECT * from t1);
754
764
1 IN (SELECT * from t1)
782
792
10 > ANY (SELECT * from t1)
783
793
1
784
794
drop table t1;
785
 
create temporary table t1 (a varchar(20)) ENGINE=MyISAM;
 
795
create table t1 (a varchar(20));
786
796
insert into t1 values ('A'),('BC'),('DEF');
787
797
select 'A' IN (SELECT * from t1);
788
798
'A' IN (SELECT * from t1)
816
826
'XYZS' > ANY (SELECT * from t1)
817
827
1
818
828
drop table t1;
819
 
create temporary table t1 (a float) ENGINE=MyISAM;
 
829
create table t1 (a float);
820
830
insert into t1 values (1.5),(2.5),(3.5);
821
831
select 1.5 IN (SELECT * from t1);
822
832
1.5 IN (SELECT * from t1)
859
869
select (select a+1) from t1;
860
870
(select a+1)
861
871
2.5
 
872
NULL
862
873
4.5
863
 
NULL
864
874
drop table t1;
865
 
#
866
 
# Null with keys
867
 
#
868
 
CREATE TEMPORARY TABLE t1 (a int NOT NULL default '0', PRIMARY KEY  (a)) ENGINE=MyISAM;
869
 
CREATE TEMPORARY TABLE t2 (a int default '0', INDEX (a)) ENGINE=MyISAM;
 
875
CREATE TABLE t1 (a int NOT NULL default '0', PRIMARY KEY  (a));
 
876
CREATE TABLE t2 (a int default '0', INDEX (a));
870
877
INSERT INTO t1 VALUES (1),(2),(3),(4);
871
878
INSERT INTO t2 VALUES (1),(2),(3);
872
879
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
881
888
2       DEPENDENT SUBQUERY      t2      index_subquery  a       a       5       func    2       100.00  Using index
882
889
Warnings:
883
890
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`
884
 
CREATE TEMPORARY TABLE t3 (a int default '0') ENGINE=MyISAM;
 
891
CREATE TABLE t3 (a int default '0');
885
892
INSERT INTO t3 VALUES (1),(2),(3);
886
893
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
887
894
a       t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
897
904
Warnings:
898
905
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`
899
906
drop table t1,t2,t3;
900
 
#LIMIT is not supported now
901
 
#create table t1 (a float) ENGINE=MyISAM;
902
 
#-- error 1235
903
 
#select 10.5 IN (SELECT * from t1 LIMIT 1);
904
 
#-- error 1235
905
 
#select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
906
 
#drop table t1;
907
 
#
908
 
#create table t1 (a int, b int, c varchar(10)) ENGINE=MyISAM;
909
 
#create table t2 (a int) ENGINE=MyISAM;
910
 
#insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
911
 
#insert into t2 values (1),(2),(NULL);
912
 
#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;
913
 
#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;
914
 
#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;
915
 
#drop table t1,t2;
916
 
#
917
 
#create table t1 (a int, b real, c varchar(10)) ENGINE=MyISAM;
918
 
#insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
919
 
#select ROW(1, 1, 'a') IN (select a,b,c from t1);
920
 
#select ROW(1, 2, 'a') IN (select a,b,c from t1);
921
 
#select ROW(1, 1, 'a') IN (select b,a,c from t1);
922
 
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
923
 
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
924
 
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
925
 
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
926
 
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
927
 
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
928
 
#-- error 1235
929
 
#select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
930
 
#drop table t1;
931
 
#test of uncacheable subqueries
932
907
CREATE TABLE t1 (a int);
933
908
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
934
909
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
943
918
Warnings:
944
919
Note    1003    select (select benchmark(1,1) AS `BENCHMARK(1,1)` from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1`
945
920
drop table t1;
946
 
CREATE TEMPORARY TABLE `t1` (
947
 
`mot` varchar(30) NOT NULL default '',
948
 
`topic` bigint NOT NULL default '0',
949
 
`date` date NULL DEFAULT '2009-01-20',
950
 
`pseudo` varchar(35) NOT NULL default '',
951
 
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
952
 
KEY `pseudo` (`pseudo`,`date`,`topic`),
953
 
KEY `topic` (`topic`)
954
 
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
955
 
CREATE TEMPORARY TABLE `t2` (
956
 
`mot` varchar(30) NOT NULL default '',
957
 
`topic` bigint NOT NULL default '0',
958
 
`date` date NULL default '1997-08-29',
959
 
`pseudo` varchar(35) NOT NULL default '',
960
 
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
961
 
KEY `pseudo` (`pseudo`,`date`,`topic`),
962
 
KEY `topic` (`topic`)
963
 
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
964
 
CREATE TEMPORARY TABLE `t3` (
 
921
CREATE TABLE `t1` (
 
922
`mot` varchar(30) NOT NULL default '',
 
923
`topic` bigint NOT NULL default '0',
 
924
`date` date NOT NULL default '0000-00-00',
 
925
`pseudo` varchar(35) NOT NULL default '',
 
926
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
 
927
KEY `pseudo` (`pseudo`,`date`,`topic`),
 
928
KEY `topic` (`topic`)
 
929
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
 
930
CREATE TABLE `t2` (
 
931
`mot` varchar(30) NOT NULL default '',
 
932
`topic` bigint NOT NULL default '0',
 
933
`date` date NOT NULL default '0000-00-00',
 
934
`pseudo` varchar(35) NOT NULL default '',
 
935
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
 
936
KEY `pseudo` (`pseudo`,`date`,`topic`),
 
937
KEY `topic` (`topic`)
 
938
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
 
939
CREATE TABLE `t3` (
965
940
`numeropost` bigint NOT NULL auto_increment,
966
941
`maxnumrep` int NOT NULL default '0',
967
942
PRIMARY KEY  (`numeropost`),
976
951
2
977
952
select * from t1;
978
953
mot     topic   date    pseudo
979
 
joce    1       2009-01-20      joce
980
 
test    2       2009-01-20      test
 
954
joce    1       0000-00-00      joce
 
955
test    2       0000-00-00      test
981
956
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
982
957
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
983
958
select * from t1;
984
959
mot     topic   date    pseudo
985
 
joce    1       2009-01-20      joce
 
960
joce    1       0000-00-00      joce
986
961
drop table t1, t2, t3;
987
962
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
988
963
a       (SELECT a)
989
964
1       1
990
 
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
991
 
SHOW CREATE TABLE t1;
992
 
Table   Create Table
993
 
t1      CREATE TEMPORARY TABLE `t1` (
994
 
  `a` int NOT NULL DEFAULT '0',
995
 
  `(SELECT 1)` int NOT NULL DEFAULT '0'
996
 
) ENGINE=MyISAM
997
 
drop table t1;
998
 
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
999
 
SHOW CREATE TABLE t1;
1000
 
Table   Create Table
1001
 
t1      CREATE TEMPORARY TABLE `t1` (
1002
 
  `a` int NOT NULL DEFAULT '0',
1003
 
  `(SELECT a)` int NOT NULL DEFAULT '0'
1004
 
) ENGINE=MyISAM
1005
 
drop table t1;
1006
 
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
1007
 
SHOW CREATE TABLE t1;
1008
 
Table   Create Table
1009
 
t1      CREATE TEMPORARY TABLE `t1` (
1010
 
  `a` int NOT NULL DEFAULT '0',
1011
 
  `(SELECT a+0)` int NOT NULL DEFAULT '0'
1012
 
) ENGINE=MyISAM
1013
 
drop table t1;
1014
 
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
 
965
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
 
966
SHOW CREATE TABLE t1;
 
967
Table   Create Table
 
968
t1      CREATE TABLE `t1` (
 
969
  `a` int NOT NULL,
 
970
  `(SELECT 1)` int NOT NULL
 
971
) ENGINE=InnoDB
 
972
drop table t1;
 
973
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
 
974
SHOW CREATE TABLE t1;
 
975
Table   Create Table
 
976
t1      CREATE TABLE `t1` (
 
977
  `a` int NOT NULL,
 
978
  `(SELECT a)` int NOT NULL
 
979
) ENGINE=InnoDB
 
980
drop table t1;
 
981
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
 
982
SHOW CREATE TABLE t1;
 
983
Table   Create Table
 
984
t1      CREATE TABLE `t1` (
 
985
  `a` int NOT NULL,
 
986
  `(SELECT a+0)` int NOT NULL
 
987
) ENGINE=InnoDB
 
988
drop table t1;
 
989
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
1015
990
select * from t1;
1016
991
a
1017
992
2
1018
993
SHOW CREATE TABLE t1;
1019
994
Table   Create Table
1020
 
t1      CREATE TEMPORARY TABLE `t1` (
 
995
t1      CREATE TABLE `t1` (
1021
996
  `a` bigint NOT NULL
1022
 
) ENGINE=MyISAM
 
997
) ENGINE=InnoDB
1023
998
drop table t1;
1024
999
create table t1 (a int);
1025
1000
insert into t1 values (1), (2), (3);
1026
1001
explain extended select a,(select (select rand() from t1 limit 1)  from t1 limit 1)
1027
1002
from t1;
1028
1003
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1029
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
1030
 
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
1031
 
3       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
 
1004
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  
 
1005
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    3       100.00  
 
1006
3       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    3       100.00  
1032
1007
Warnings:
1033
1008
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`
1034
1009
drop table t1;
1035
 
#
1036
 
# error in IN
1037
 
#
1038
1010
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);
1039
1011
ERROR 42S02: Table 'test.t1' doesn't exist
1040
 
#
1041
 
# complex subquery
1042
 
#
1043
1012
CREATE TABLE t1 (
1044
1013
ID int NOT NULL auto_increment,
1045
1014
name char(35) NOT NULL default '',
1047
1016
District char(20) NOT NULL default '',
1048
1017
Population int NOT NULL default '0',
1049
1018
PRIMARY KEY  (ID)
1050
 
);
 
1019
) ENGINE=MyISAM;
1051
1020
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
1052
1021
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
1053
1022
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
1068
1037
Capital int default NULL,
1069
1038
Code2 char(2) NOT NULL default '',
1070
1039
PRIMARY KEY  (Code)
1071
 
);
 
1040
) ENGINE=MyISAM;
1072
1041
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');
1073
1042
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');
1074
1043
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);
1075
1044
Continent       Name    Population
1076
1045
Oceania Sydney  3276207
1077
1046
drop table t1, t2;
1078
 
#
1079
 
# constants in IN
1080
 
#
1081
 
CREATE TEMPORARY TABLE `t1` (
 
1047
CREATE TABLE `t1` (
1082
1048
`id` bigint NOT NULL auto_increment,
1083
1049
`pseudo` varchar(35) NOT NULL default '',
1084
1050
PRIMARY KEY  (`id`),
1085
1051
UNIQUE KEY `pseudo` (`pseudo`)
1086
 
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
 
1052
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
1087
1053
INSERT INTO t1 (pseudo) VALUES ('test');
1088
1054
SELECT 0 IN (SELECT 1 FROM t1 a);
1089
1055
0 IN (SELECT 1 FROM t1 a)
1105
1071
Warnings:
1106
1072
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)`
1107
1073
drop table t1;
1108
 
CREATE TEMPORARY TABLE `t1` (
 
1074
CREATE TABLE `t1` (
1109
1075
`i` int NOT NULL default '0',
1110
1076
PRIMARY KEY  (`i`)
1111
1077
) ENGINE=MyISAM;
1118
1084
i
1119
1085
3
1120
1086
drop table t1;
1121
 
#
1122
 
# correct NULL in <CONSTANT> IN (SELECT ...)
1123
 
#
1124
 
create temporary table t1 (a int, unique index indexa (a)) ENGINE=MyISAM;
 
1087
CREATE TABLE t1 (
 
1088
id int default NULL
 
1089
) ENGINE=MyISAM;
 
1090
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
 
1091
CREATE TABLE t2 (
 
1092
id int default NULL,
 
1093
name varchar(15) default NULL
 
1094
) ENGINE=MyISAM;
 
1095
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
 
1096
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
 
1097
select * from t2;
 
1098
id      name
 
1099
4       vita
 
1100
1       lenka
 
1101
2       lenka
 
1102
1       lenka
 
1103
drop table t1,t2;
 
1104
create table t1 (a int, unique index indexa (a));
1125
1105
insert into t1 values (-1), (-4), (-2), (NULL);
1126
1106
select -10 IN (select a from t1 FORCE INDEX (indexa));
1127
1107
-10 IN (select a from t1 FORCE INDEX (indexa))
1128
1108
NULL
1129
1109
drop table t1;
1130
 
#
1131
 
# Test optimization for sub selects
1132
 
#
1133
1110
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
1134
1111
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
1135
1112
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
1136
1113
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1137
 
1       PRIMARY t1      ref     salary  salary  5       const   #       100.00  Using where; Using index
1138
 
2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    #       NULL    Select tables optimized away
 
1114
1       PRIMARY t1      ref     salary  salary  5       const   1       100.00  Using where; Using index
 
1115
2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
1139
1116
Warnings:
1140
1117
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`))
1141
1118
drop table t1;
1148
1125
UNIQUE KEY t1_PK (ID,SUB_ID),
1149
1126
KEY t1_FK (REF_ID,REF_SUB),
1150
1127
KEY t1_REFID (REF_ID)
1151
 
);
 
1128
) ENGINE=MyISAM;
1152
1129
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
1153
1130
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
1154
1131
REF_ID
1155
1132
DROP TABLE t1;
1156
 
#
1157
 
# uninterruptable update
1158
 
#
1159
 
create temporary table t1 (a int, b int) ENGINE=MyISAM;
1160
 
create temporary table t2 (a int, b int) ENGINE=MyISAM;
 
1133
create table t1 (a int, b int);
 
1134
create table t2 (a int, b int);
1161
1135
insert into t1 values (1,0), (2,0), (3,0);
1162
1136
insert into t2 values (1,1), (2,1), (3,1), (2,2);
1163
1137
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
1169
1143
2       NULL
1170
1144
3       1
1171
1145
drop table t1, t2;
1172
 
#
1173
 
# reduced subselect in ORDER BY & GROUP BY clauses
1174
 
#
1175
 
CREATE TEMPORARY TABLE `t1` (
 
1146
CREATE TABLE `t1` (
1176
1147
`id` bigint NOT NULL auto_increment,
1177
1148
`pseudo` varchar(35) NOT NULL default '',
1178
1149
`email` varchar(60) NOT NULL default '',
1179
1150
PRIMARY KEY  (`id`),
1180
1151
UNIQUE KEY `email` (`email`),
1181
1152
UNIQUE KEY `pseudo` (`pseudo`)
1182
 
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
 
1153
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
1183
1154
INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');
1184
1155
SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);
1185
1156
a       b
1189
1160
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
1190
1161
a
1191
1162
1
1192
 
#
1193
 
# IN subselect optimization test
1194
 
#
1195
 
create temporary table t1 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
1196
 
create temporary table t2 (a int not null, primary key (a)) ENGINE=MyISAM;
1197
 
create temporary table t3 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
 
1163
create table t1 (a int not null, b int, primary key (a));
 
1164
create table t2 (a int not null, primary key (a));
 
1165
create table t3 (a int not null, b int, primary key (a));
1198
1166
insert into t1 values (1,10), (2,20), (3,30),  (4,40);
1199
1167
insert into t2 values (2), (3), (4), (5);
1200
1168
insert into t3 values (10,3), (20,4), (30,5);
1205
1173
4
1206
1174
explain extended select * from t2 where t2.a in (select a from t1);
1207
1175
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1208
 
1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Using index
1209
 
2       SUBQUERY        t1      index   NULL    PRIMARY 4       NULL    4       100.00  Using index
 
1176
1       PRIMARY t2      index   PRIMARY PRIMARY 4       NULL    4       100.00  Using index
 
1177
1       PRIMARY t1      eq_ref  PRIMARY PRIMARY 4       test.t2.a       1       100.00  Using index
1210
1178
Warnings:
1211
 
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
 
1179
Note    1003    select `test`.`t2`.`a` AS `a` from (`test`.`t1`) join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)
1212
1180
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1213
1181
a
1214
1182
2
1215
1183
4
1216
1184
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1217
1185
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1218
 
1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Using index
1219
 
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where
 
1186
1       PRIMARY t2      index   PRIMARY PRIMARY 4       NULL    4       100.00  Using index
 
1187
1       PRIMARY t1      ALL     PRIMARY NULL    NULL    NULL    4       75.00   Using where; Using join buffer
1220
1188
Warnings:
1221
 
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
 
1189
Note    1003    select `test`.`t2`.`a` AS `a` from (`test`.`t1`) join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
1222
1190
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1223
1191
a
1224
1192
2
1225
1193
3
1226
1194
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1227
1195
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1228
 
1       PRIMARY t2      index   NULL    PRIMARY 4       NULL    4       100.00  Using where; Using index
1229
 
2       SUBQUERY        t3      index   PRIMARY PRIMARY 4       NULL    3       100.00  Using index
1230
 
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    4       100.00  Using where; Using join buffer
 
1196
1       PRIMARY t2      index   PRIMARY PRIMARY 4       NULL    4       100.00  Using index
 
1197
1       PRIMARY t1      ALL     PRIMARY NULL    NULL    NULL    4       75.00   Using where; Using join buffer
 
1198
1       PRIMARY t3      eq_ref  PRIMARY PRIMARY 4       test.t1.b       1       100.00  Using index
1231
1199
Warnings:
1232
 
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
 
1200
Note    1003    select `test`.`t2`.`a` AS `a` from (`test`.`t1` join `test`.`t3`) join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))
1233
1201
drop table t1, t2, t3;
1234
 
create temporary table t1 (a int, b int, index a (a,b)) ENGINE=MyISAM;
1235
 
create temporary table t2 (a int, index a (a)) ENGINE=MyISAM;
1236
 
create temporary table t3 (a int, b int, index a (a)) ENGINE=MyISAM;
 
1202
create table t1 (a int, b int, index a (a,b));
 
1203
create table t2 (a int, index a (a));
 
1204
create table t3 (a int, b int, index a (a));
1237
1205
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1238
 
# making table large enough
1239
1206
insert into t2 values (2), (3), (4), (5);
1240
1207
insert into t3 values (10,3), (20,4), (30,5);
1241
1208
select * from t2 where t2.a in (select a from t1);
1245
1212
4
1246
1213
explain extended select * from t2 where t2.a in (select a from t1);
1247
1214
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1248
 
1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
1249
 
2       SUBQUERY        t1      index   NULL    a       10      NULL    10004   100.00  Using index
 
1215
1       PRIMARY t2      index   a       a       5       NULL    4       100.00  Using index
 
1216
1       PRIMARY t1      ref     a       a       5       test.t2.a       1       100.00  Using index; FirstMatch(t2)
1250
1217
Warnings:
1251
 
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
 
1218
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`)
1252
1219
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1253
1220
a
1254
1221
2
1255
1222
4
1256
1223
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1257
1224
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1258
 
1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
1259
 
2       SUBQUERY        t1      index   NULL    a       10      NULL    10004   100.00  Using where; Using index
 
1225
1       PRIMARY t2      index   a       a       5       NULL    4       100.00  Using index
 
1226
1       PRIMARY t1      ref     a       a       5       test.t2.a       1       100.00  Using where; Using index; FirstMatch(t2)
1260
1227
Warnings:
1261
 
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
 
1228
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
1262
1229
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1263
1230
a
1264
1231
2
1265
1232
3
1266
1233
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1267
1234
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1268
 
1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
1269
 
2       SUBQUERY        t3      index   a       a       5       NULL    3       100.00  Using index
1270
 
2       SUBQUERY        t1      index   NULL    a       10      NULL    10004   100.00  Using where; Using index; Using join buffer
 
1235
1       PRIMARY t2      index   a       a       5       NULL    4       100.00  Using index
 
1236
1       PRIMARY t1      ref     a       a       5       test.t2.a       1       100.00  Using index; FirstMatch(t2)
 
1237
1       PRIMARY t3      ref     a       a       5       test.t1.b       1       100.00  Using index; FirstMatch(t2)
1271
1238
Warnings:
1272
 
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
 
1239
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))
1273
1240
insert into t1 values (3,31);
1274
1241
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1275
1242
a
1282
1249
4
1283
1250
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1284
1251
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1285
 
1       PRIMARY t2      index   NULL    a       5       NULL    4       100.00  Using where; Using index
1286
 
2       SUBQUERY        t1      index   NULL    a       10      NULL    10005   100.00  Using where; Using index
 
1252
1       PRIMARY t2      index   a       a       5       NULL    4       100.00  Using index
 
1253
1       PRIMARY t1      ref     a       a       5       test.t2.a       1       100.00  Using where; Using index; FirstMatch(t2)
1287
1254
Warnings:
1288
 
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
 
1255
Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
1289
1256
drop table t1, t2, t3;
1290
 
#
1291
 
# alloc_group_fields() working
1292
 
#
1293
 
create temporary table t1 (a int, b int) ENGINE=MyISAM;
1294
 
create temporary table t2 (a int, b int) ENGINE=MyISAM;
1295
 
create temporary table t3 (a int, b int) ENGINE=MyISAM;
 
1257
create table t1 (a int, b int);
 
1258
create table t2 (a int, b int);
 
1259
create table t3 (a int, b int);
1296
1260
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
1297
1261
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
1298
1262
insert into t3 values (3,3), (2,2), (1,1);
1302
1266
2       2
1303
1267
1       2
1304
1268
drop table t1,t2,t3;
1305
 
#
1306
 
# aggregate functions in HAVING test
1307
 
#
1308
 
create temporary table t1 (s1 int) ENGINE=MyISAM;
1309
 
create temporary table t2 (s1 int) ENGINE=MyISAM;
 
1269
create table t1 (s1 int);
 
1270
create table t2 (s1 int);
1310
1271
insert into t1 values (1);
1311
1272
insert into t2 values (1);
1312
1273
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
1313
1274
s1
1314
1275
1
1315
1276
drop table t1,t2;
1316
 
#
1317
 
# update subquery with wrong field (to force name resolving
1318
 
# in UPDATE name space)
1319
 
#
1320
 
create temporary table t1 (s1 int) ENGINE=MyISAM;
1321
 
create temporary table t2 (s1 int) ENGINE=MyISAM;
 
1277
create table t1 (s1 int);
 
1278
create table t2 (s1 int);
1322
1279
insert into t1 values (1);
1323
1280
insert into t2 values (1);
1324
1281
update t1 set  s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
1325
1282
ERROR 42S22: Unknown column 'x.s1' in 'field list'
1326
1283
DROP TABLE t1, t2;
1327
 
#
1328
 
# collation test
1329
 
#
1330
 
#CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
1331
 
#                 s2 CHAR(5) COLLATE latin1_swedish_ci) ENGINE=MyISAM;
1332
 
#INSERT INTO t1 VALUES ('z','?');
1333
 
#-- error 1267
1334
 
#select * from t1 where s1 > (select max(s2) from t1);
1335
 
#-- error 1267
1336
 
#select * from t1 where s1 > any (select max(s2) from t1);
1337
 
#drop table t1;
1338
 
#
1339
 
# aggregate functions reinitialization
1340
 
#
1341
1284
create table t1(toid int,rd int);
1342
1285
create table t2(userid int,pmnew int,pmtotal int);
1343
1286
insert into t2 values(1,0,0),(2,0,0);
1347
1290
1       0       0       9       3
1348
1291
2       0       0       4       2
1349
1292
drop table t1, t2;
1350
 
#
1351
 
# row union
1352
 
#
1353
1293
create table t1 (s1 char(5));
1354
1294
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
1355
1295
ERROR 21000: Operand should contain 1 column(s)
1359
1299
tttt
1360
1300
explain extended (select * from t1);
1361
1301
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1362
 
1       SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
 
1302
1       SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    1       100.00  
1363
1303
Warnings:
1364
1304
Note    1003    (select `test`.`t1`.`s1` AS `s1` from `test`.`t1`)
1365
1305
(select * from t1);
1366
1306
s1
1367
1307
tttt
1368
1308
drop table t1;
1369
 
#
1370
 
# IN optimisation test results
1371
 
#
1372
 
create temporary table t1 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
1373
 
create temporary table t2 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
 
1309
create table t1 (s1 char(5), index s1(s1));
 
1310
create table t2 (s1 char(5), index s1(s1));
1374
1311
insert into t1 values ('a1'),('a2'),('a3');
1375
1312
insert into t2 values ('a1'),('a2');
1376
1313
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1418
1355
Warnings:
1419
1356
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`
1420
1357
drop table t1,t2;
1421
 
#
1422
 
# correct ALL optimisation
1423
 
#
1424
 
create temporary table t2 (a int, b int) ENGINE=MyISAM;
1425
 
create temporary table t3 (a int) ENGINE=MyISAM;
 
1358
create table t2 (a int, b int);
 
1359
create table t3 (a int);
1426
1360
insert into t3 values (6),(7),(3);
1427
1361
select * from t3 where a >= all (select b from t2);
1428
1362
a
1432
1366
explain extended select * from t3 where a >= all (select b from t2);
1433
1367
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1434
1368
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
1435
 
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 
1369
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    1       100.00  
1436
1370
Warnings:
1437
 
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max('0') from `test`.`t2`)))
 
1371
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max(`test`.`t2`.`b`) from `test`.`t2`)))
1438
1372
select * from t3 where a >= some (select b from t2);
1439
1373
a
1440
1374
explain extended select * from t3 where a >= some (select b from t2);
1441
1375
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1442
1376
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
1443
 
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 
1377
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    1       100.00  
1444
1378
Warnings:
1445
 
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min('0') from `test`.`t2`)))
 
1379
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
1446
1380
select * from t3 where a >= all (select b from t2 group by 1);
1447
1381
a
1448
1382
6
1451
1385
explain extended select * from t3 where a >= all (select b from t2 group by 1);
1452
1386
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1453
1387
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
1454
 
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 
1388
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    1       100.00  Using temporary; Using filesort
1455
1389
Warnings:
1456
 
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)))
 
1390
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select `test`.`t2`.`b` AS `b` from `test`.`t2` group by 1)))
1457
1391
select * from t3 where a >= some (select b from t2 group by 1);
1458
1392
a
1459
1393
explain extended select * from t3 where a >= some (select b from t2 group by 1);
1460
1394
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1461
1395
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
1462
 
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 
1396
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    1       100.00  Using temporary; Using filesort
1463
1397
Warnings:
1464
 
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)))
 
1398
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select `test`.`t2`.`b` AS `b` from `test`.`t2` group by 1)))
1465
1399
select * from t3 where NULL >= any (select b from t2);
1466
1400
a
1467
1401
explain extended select * from t3 where NULL >= any (select b from t2);
1468
1402
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1469
1403
1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
1470
 
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 
1404
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    1       100.00  
1471
1405
Warnings:
1472
1406
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1473
1407
select * from t3 where NULL >= any (select b from t2 group by 1);
1475
1409
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
1476
1410
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1477
1411
1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
1478
 
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 
1412
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    1       100.00  Using temporary; Using filesort
1479
1413
Warnings:
1480
1414
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1481
1415
select * from t3 where NULL >= some (select b from t2);
1483
1417
explain extended select * from t3 where NULL >= some (select b from t2);
1484
1418
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1485
1419
1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
1486
 
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 
1420
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    1       100.00  
1487
1421
Warnings:
1488
1422
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1489
1423
select * from t3 where NULL >= some (select b from t2 group by 1);
1491
1425
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
1492
1426
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1493
1427
1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
1494
 
2       SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
 
1428
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    1       100.00  Using temporary; Using filesort
1495
1429
Warnings:
1496
1430
Note    1003    select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1497
 
#
1498
 
# optimized static ALL/ANY with grouping
1499
 
#
1500
1431
insert into t2 values (2,2), (2,1), (3,3), (3,1);
1501
1432
select * from t3 where a > all (select max(b) from t2 group by a);
1502
1433
a
1509
1440
Warnings:
1510
1441
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`)))
1511
1442
drop table t2, t3;
1512
 
#
1513
 
# correct used_tables()
1514
 
#
1515
 
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 ;
 
1443
CREATE TABLE `t1` ( `id` bigint NOT NULL auto_increment, `taskid` bigint NOT NULL default '0', `dbid` int 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 AUTO_INCREMENT=3 ;
1516
1444
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());
1517
 
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;
 
1445
CREATE TABLE `t2` (`db_id` int NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` int NOT NULL default '0',`secondary_uid` int NOT NULL default '0',PRIMARY KEY  (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM AUTO_INCREMENT=2147483647;
1518
1446
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);
1519
 
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 ;
 
1447
CREATE TABLE `t3` (`taskgenid` bigint NOT NULL auto_increment,`dbid` int NOT NULL default '0',`taskid` int NOT NULL default '0',`mon` int NOT NULL default '1',`tues` int NOT NULL default '1',`wed` int NOT NULL default '1',`thur` int NOT NULL default '1',`fri` int NOT NULL default '1',`sat` int NOT NULL default '0',`sun` int NOT NULL default '0',`how_often` int NOT NULL default '1',`userid` int NOT NULL default '0',`active` int NOT NULL default '1',PRIMARY KEY  (`taskgenid`)) ENGINE=MyISAM AUTO_INCREMENT=2 ;
1520
1448
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);
1521
 
CREATE TEMPORARY TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;
 
1449
CREATE TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;
1522
1450
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
1523
1451
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;
1524
1452
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')
1530
1458
-1      Valid
1531
1459
-1      Valid 2
1532
1460
drop table t1,t2,t3,t4;
1533
 
#
1534
 
# cardinality check
1535
 
#
1536
 
CREATE TEMPORARY TABLE t1 (id int default NULL) ENGINE=MyISAM;
 
1461
CREATE TABLE t1 (id int default NULL) ENGINE=MyISAM;
1537
1462
INSERT INTO t1 VALUES (1),(5);
1538
 
CREATE TEMPORARY TABLE t2 (id int default NULL) ENGINE=MyISAM;
 
1463
CREATE TABLE t2 (id int default NULL) ENGINE=MyISAM;
1539
1464
INSERT INTO t2 VALUES (2),(6);
1540
1465
select * from t1 where (1,2,6) in (select * from t2);
1541
1466
ERROR 21000: Operand should contain 3 column(s)
1542
1467
DROP TABLE t1,t2;
1543
 
#
1544
 
# optimized ALL/ANY with union
1545
 
#
1546
1468
create table t1 (s1 char);
1547
1469
insert into t1 values ('e');
1548
1470
select * from t1 where 'f' > any (select s1 from t1);
1553
1475
e
1554
1476
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1555
1477
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1556
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
1557
 
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
1558
 
3       UNION   t1      ALL     NULL    NULL    NULL    NULL    #       100.00  
1559
 
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    #       NULL    
 
1478
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    1       100.00  
 
1479
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    1       100.00  
 
1480
3       UNION   t1      ALL     NULL    NULL    NULL    NULL    1       100.00  
 
1481
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
1560
1482
Warnings:
1561
1483
Note    1003    select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where 1
1562
1484
drop table t1;
1563
 
#
1564
 
# filesort in subquery (restoring join_tab)
1565
 
#
1566
 
CREATE TEMPORARY TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;
 
1485
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;
1567
1486
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
1568
 
CREATE TEMPORARY TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;
 
1487
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;
1569
1488
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
1570
1489
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;
1571
1490
phone   code
1574
1493
89356874041     NULL
1575
1494
95895001874     NULL
1576
1495
drop table t1, t2;
1577
 
#
1578
 
# unresolved field error
1579
 
#
1580
 
create temporary table t1 (s1 int) ENGINE=MyISAM;
1581
 
create temporary table t2 (s1 int) ENGINE=MyISAM;
 
1496
create table t1 (s1 int);
 
1497
create table t2 (s1 int);
1582
1498
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
1583
1499
ERROR 42S22: Unknown column 't1.s2' in 'where clause'
1584
1500
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
1586
1502
select count(*) from t2 group by t1.s2;
1587
1503
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
1588
1504
drop table t1, t2;
1589
 
#
1590
 
# fix_fields() in add_ref_to_table_cond()
1591
 
#
1592
 
CREATE TEMPORARY TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB)) ENGINE=MyISAM;
1593
 
CREATE TEMPORARY TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA)) ENGINE=MyISAM;
 
1505
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
 
1506
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
1594
1507
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
1595
1508
INSERT INTO t2 VALUES (100, 200, 'C');
1596
1509
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
1597
1510
COLC
1598
1511
DROP TABLE t1, t2;
1599
 
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
 
1512
CREATE TABLE t1 (a int);
1600
1513
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
1601
1514
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
1602
1515
(SELECT a)
1606
1519
4
1607
1520
5
1608
1521
DROP TABLE t1;
1609
 
#
1610
 
# Bug 2479
1611
 
#
1612
 
CREATE TEMPORARY TABLE `t1` (
 
1522
CREATE TABLE `t1` (
1613
1523
`id` int NOT NULL auto_increment,
1614
1524
`id_cns` int NOT NULL default '0',
1615
1525
`tipo` enum('','UNO','DUE') NOT NULL default '',
1619
1529
`bis` int NOT NULL default '0',
1620
1530
PRIMARY KEY  (`id`),
1621
1531
UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
1622
 
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`))
1623
 
ENGINE=MyISAM;
 
1532
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
 
1533
);
1624
1534
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);
1625
 
CREATE TEMPORARY TABLE `t2` (
 
1535
CREATE TABLE `t2` (
1626
1536
`id` int NOT NULL auto_increment,
1627
1537
`max_anno_dep` int NOT NULL default '0',
1628
 
PRIMARY KEY  (`id`)) ENGINE=MyISAM;
 
1538
PRIMARY KEY  (`id`)
 
1539
);
1629
1540
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
1630
1541
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;
1631
1542
id      max_anno_dep    PIPPO
1633
1544
50      1990    0
1634
1545
51      1990    NULL
1635
1546
DROP TABLE t1, t2;
1636
 
#
1637
 
# GLOBAL LIMIT
1638
 
#
1639
 
create temporary table t1 (a int) ENGINE=MyISAM;
 
1547
create table t1 (a int);
1640
1548
insert into t1 values (1), (2), (3);
1641
1549
SET SQL_SELECT_LIMIT=1;
1642
1550
select sum(a) from (select * from t1) as a;
1647
1555
1
1648
1556
SET SQL_SELECT_LIMIT=default;
1649
1557
drop table t1;
1650
 
#
1651
 
# Bug #3118: subselect + order by
1652
 
#
1653
1558
CREATE TABLE t1 (a int, b int, INDEX (a));
1654
1559
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
1655
1560
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
1658
1563
1       2
1659
1564
1       3
1660
1565
DROP TABLE t1;
1661
 
# Item_cond fix field
1662
 
#
1663
1566
create table t1(val varchar(10));
1664
1567
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
1665
1568
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%');
1666
1569
count(*)
1667
1570
0
1668
1571
drop table t1;
1669
 
#
1670
 
# ref_or_null replacing with ref
1671
 
#
1672
1572
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
1673
1573
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');
1674
1574
select * from t1 where id not in (select id from t1 where id < 8);
1687
1587
12      text12
1688
1588
explain extended select * from t1 where id not in (select id from t1 where id < 8);
1689
1589
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1690
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
1691
 
2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    #       100.00  Using index; Using where
 
1590
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
 
1591
2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index; Using where
1692
1592
Warnings:
1693
1593
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`)))))))
1694
1594
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);
1695
1595
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1696
 
1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
1697
 
2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      #       100.00  Using where; Using index
 
1596
1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
 
1597
2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
1698
1598
Warnings:
1699
1599
Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
1700
1600
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))))
1701
1601
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
1702
 
create temporary table t2 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
 
1602
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
1703
1603
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');
1704
1604
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);
1705
1605
id      text    id      text    id      text
1719
1619
1001    text1001        NULL    NULL    1000    text1000
1720
1620
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);
1721
1621
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1722
 
1       SIMPLE  a       ALL     NULL    NULL    NULL    NULL    #       100.00  
1723
 
1       SIMPLE  b       eq_ref  PRIMARY PRIMARY 4       test.a.id       #       100.00  
1724
 
1       SIMPLE  c       eq_ref  PRIMARY PRIMARY 4       func    #       100.00  Using where
 
1622
1       SIMPLE  a       ALL     NULL    NULL    NULL    NULL    14      100.00  
 
1623
1       SIMPLE  b       eq_ref  PRIMARY PRIMARY 4       test.a.id       2       100.00  
 
1624
1       SIMPLE  c       eq_ref  PRIMARY PRIMARY 4       func    1       100.00  Using where
1725
1625
Warnings:
1726
1626
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`)
1727
1627
drop table t1,t2;
1728
 
#
1729
 
# Static tables & rund() in subqueries
1730
 
#
1731
 
create temporary table t1 (a int) ENGINE=MyISAM;
 
1628
create table t1 (a int);
1732
1629
insert into t1 values (1);
1733
1630
explain select benchmark(1000, (select a from t1 where a=rand()));
1734
1631
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1735
1632
1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
1736
 
2       SUBQUERY        t1      system  NULL    NULL    NULL    NULL    1       
 
1633
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    1       Using where
1737
1634
drop table t1;
1738
 
#
1739
 
# bug 3188
1740
 
#
1741
 
create temporary table t1(id int) ENGINE=MyISAM;
1742
 
create temporary table t2(id int) ENGINE=MyISAM;
1743
 
create temporary table t3(flag int) ENGINE=MyISAM;
 
1635
create table t1(id int);
 
1636
create table t2(id int);
 
1637
create table t3(flag int);
1744
1638
select (select * from t3 where id not null) from t1, t2;
1745
 
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
 
1639
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
1746
1640
drop table t1,t2,t3;
1747
 
#
1748
 
# aggregate functions (Bug #3505)
1749
 
#
1750
1641
CREATE TABLE t1 (id INT);
1751
1642
CREATE TABLE t2 (id INT);
1752
1643
INSERT INTO t1 VALUES (1), (2);
1768
1659
1       1
1769
1660
2       0
1770
1661
DROP TABLE t1,t2;
1771
 
#
1772
 
# ALL/ANY test
1773
 
#
1774
1662
CREATE TABLE t1 ( a int, b int );
1775
1663
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1776
1664
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1815
1703
a
1816
1704
1
1817
1705
3
1818
 
# with index
1819
1706
ALTER TABLE t1 ADD INDEX (a);
1820
1707
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1821
1708
a
1859
1746
a
1860
1747
1
1861
1748
3
1862
 
# having clause test
1863
1749
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
1864
1750
a
1865
1751
3
1902
1788
a
1903
1789
1
1904
1790
3
1905
 
# union test
1906
1791
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1907
1792
a
1908
1793
3
1945
1830
a
1946
1831
1
1947
1832
3
1948
 
# union + having test
1949
1833
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1950
1834
a
1951
1835
3
1988
1872
a
1989
1873
1
1990
1874
3
1991
 
# row tests
1992
 
# < > >= <= and = ALL/ <> ANY do not support row operation
1993
1875
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
1994
1876
ERROR 21000: Operand should contain 1 column(s)
1995
1877
SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
2006
1888
ERROR 21000: Operand should contain 1 column(s)
2007
1889
SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
2008
1890
ERROR 21000: Operand should contain 1 column(s)
2009
 
# following should be converted to IN
2010
1891
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
2011
1892
ERROR 21000: Operand should contain 2 column(s)
2012
1893
SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
2050
1931
a
2051
1932
1
2052
1933
3
2053
 
# without optimisation
2054
1934
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
2055
1935
a
2056
1936
3
2093
1973
a
2094
1974
1
2095
1975
3
2096
 
# without optimisation + having
2097
1976
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
2098
1977
a
2099
1978
3
2136
2015
a
2137
2016
1
2138
2017
3
2139
 
# EXISTS in string contence
2140
2018
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
2141
2019
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-')
2142
2020
0-
2243
2121
1
2244
2122
3
2245
2123
DROP TABLE t1;
2246
 
#
2247
 
# SELECT(EXISTS * ...)optimisation
2248
 
#
2249
2124
create table t1 (a int, b int);
2250
2125
insert into t1 values (1,2),(3,4);
2251
2126
select * from t1 up where exists (select * from t1 where t1.a=up.a);
2254
2129
3       4
2255
2130
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
2256
2131
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
2257
 
1       PRIMARY up      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
2258
 
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
 
2132
1       PRIMARY up      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 
2133
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
2259
2134
Warnings:
2260
2135
Note    1276    Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
2261
2136
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`))
2262
2137
drop table t1;
2263
 
#
2264
 
# Bug #4102: subselect in HAVING
2265
 
#
2266
 
CREATE TEMPORARY TABLE t1 (t1_a int) ENGINE=MyISAM;
 
2138
CREATE TABLE t1 (t1_a int);
2267
2139
INSERT INTO t1 VALUES (1);
2268
2140
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
2269
2141
INSERT INTO t2 VALUES (1, 1), (1, 2);
2272
2144
t1_a    t2_a    t2_b
2273
2145
1       1       2
2274
2146
DROP TABLE t1, t2;
2275
 
#
2276
 
# Test problem with NULL and derived tables (Bug #4097)
2277
 
#
2278
 
CREATE TEMPORARY TABLE t1 (id int default NULL,name varchar(10) default NULL) ENGINE=MyISAM;
 
2147
CREATE TABLE t1 (id int default NULL,name varchar(10) default NULL);
2279
2148
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
2280
 
CREATE TEMPORARY TABLE t2 (id int default NULL, pet varchar(10) default NULL) ENGINE=MyISAM;
 
2149
CREATE TABLE t2 (id int default NULL, pet varchar(10) default NULL);
2281
2150
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
2282
2151
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
2283
2152
id      name    id      pet
2285
2154
2       Rebecca 2       Spot
2286
2155
3       NULL    3       Felix
2287
2156
drop table t1,t2;
2288
 
#
2289
 
# Aggregate function comparation with ALL/ANY/SOME subselect
2290
 
#
2291
 
CREATE TEMPORARY TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;
 
2157
CREATE TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;
2292
2158
insert into t1 values (1);
2293
 
CREATE TEMPORARY TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
 
2159
CREATE TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
2294
2160
insert into t2 values (1,2);
2295
2161
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
2296
2162
a       C
2297
2163
1       1
2298
2164
drop table t1,t2;
2299
 
#
2300
 
# BUG#5003 - like in subselect
2301
 
#
2302
2165
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);
2303
2166
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
2304
2167
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
2309
2172
IZAVORGANG_ID
2310
2173
D0000000001
2311
2174
drop table t1;
2312
 
#
2313
 
# Optimized IN with compound index
2314
 
#
2315
 
CREATE TEMPORARY TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`)) ENGINE=MyISAM;
2316
 
CREATE TEMPORARY TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`)) ENGINE=MyISAM;
 
2175
CREATE TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`));
 
2176
CREATE TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`));
2317
2177
insert into t1 values (1,1),(1,2),(2,1),(2,2);
2318
2178
insert into t2 values (1,2),(2,2);
2319
2179
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2333
2193
1       1
2334
2194
2       1
2335
2195
drop table t1,t2;
2336
 
#
2337
 
# resolving fields of grouped outer SELECT
2338
 
#
2339
2196
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
2340
2197
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
2341
2198
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
2374
2231
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;
2375
2232
(select b.x from t1 as b where b.x=a.x)
2376
2233
drop table t1;
2377
 
#
2378
 
# Test of correct maybe_null flag returning by subquwery for temporary table
2379
 
# creation
2380
 
#
2381
 
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;
 
2234
CREATE TABLE `t1` ( `master` int NOT NULL default '0', `map` int NOT NULL default '0', `slave` int NOT NULL default '0', `access` int NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`));
2382
2235
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);
2383
 
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 ;
 
2236
CREATE TABLE `t2` ( `id` int NOT NULL default '0', `pid` int NOT NULL default '0', `map` int NOT NULL default '0', `level` int NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY  (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ;
2384
2237
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');
2385
2238
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;
2386
2239
ERROR 42S22: Unknown column 'b.sc' in 'field list'
2389
2242
700
2390
2243
NULL
2391
2244
drop tables t1,t2;
2392
 
#
2393
 
# Subselect in non-select command just after connection
2394
 
#
2395
2245
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
2396
 
#
2397
 
# primary query with temporary table and subquery with groupping
2398
 
#
2399
 
create temporary table t1 (a int, b int) ENGINE=MyISAM;
2400
 
create temporary table t2 (a int, b int) ENGINE=MyISAM;
 
2246
create table t1 (a int, b int);
 
2247
create table t2 (a int, b int);
2401
2248
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
2402
2249
insert into t2 values (1,3),(2,1);
2403
2250
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
2408
2255
2       4       NULL
2409
2256
2       5       NULL
2410
2257
drop table t1, t2;
2411
 
#
2412
 
# Equal operation under row and empty subquery
2413
 
#
2414
2258
create table t1 (s1 int,s2 int);
2415
2259
insert into t1 values (20,15);
2416
2260
select * from t1 where  (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
2417
2261
s1      s2
2418
2262
drop table t1;
2419
 
#
2420
 
# ALL/ANY with NULL
2421
 
#
2422
2263
create table t1 (s1 int);
2423
2264
insert into t1 values (1),(null);
2424
2265
select * from t1 where s1 < all (select s1 from t1);
2428
2269
1       0
2429
2270
NULL    NULL
2430
2271
drop table t1;
2431
 
#
2432
 
# reference on changable fields from subquery
2433
 
#
2434
2272
CREATE TABLE t1 (
2435
2273
Code char(3) NOT NULL default '',
2436
2274
Name char(52) NOT NULL default '',
2447
2285
HeadOfState char(60) default NULL,
2448
2286
Capital int default NULL,
2449
2287
Code2 char(2) NOT NULL default ''
2450
 
);
 
2288
) ENGINE=MyISAM;
2451
2289
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
2452
2290
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');
2453
2291
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');
2454
2292
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');
2455
2293
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
2456
 
Warnings:
2457
 
Note    1031    Table storage engine for 't1' doesn't have this option
2458
2294
SELECT DISTINCT Continent AS c FROM t1 outr WHERE 
2459
2295
Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND 
2460
2296
Population < 200);
2461
2297
c
2462
2298
Oceania
2463
2299
drop table t1;
2464
 
#
2465
 
# Test for BUG#7885: Server crash when 'any' subselect compared to
2466
 
# non-existant field.
2467
 
#
2468
 
create temporary table t1 (a1 int) ENGINE=MyISAM;
2469
 
create temporary table t2 (b1 int) ENGINE=MyISAM;
 
2300
create table t1 (a1 int);
 
2301
create table t2 (b1 int);
2470
2302
select * from t1 where a2 > any(select b1 from t2);
2471
2303
ERROR 42S22: Unknown column 'a2' in 'IN/ALL/ANY subquery'
2472
2304
select * from t1 where a1 > any(select b1 from t2);
2473
2305
a1
2474
2306
drop table t1,t2;
2475
 
#
2476
 
# Comparison subquery with * and row
2477
 
#
2478
 
create temporary table t1 (a integer, b integer) ENGINE=MyISAM;
 
2307
create table t1 (a integer, b integer);
2479
2308
select (select * from t1) = (select 1,2);
2480
2309
(select * from t1) = (select 1,2)
2481
2310
NULL
2482
2311
select (select 1,2) = (select * from t1);
2483
2312
(select 1,2) = (select * from t1)
2484
2313
NULL
2485
 
# queries whih can be converted to IN
2486
2314
select  row(1,2) = ANY (select * from t1);
2487
2315
row(1,2) = ANY (select * from t1)
2488
2316
0
2490
2318
row(1,2) != ALL (select * from t1)
2491
2319
1
2492
2320
drop table t1;
2493
 
#
2494
 
# Comparison subquery and row with nested rows
2495
 
#
2496
 
create temporary table t1 (a integer, b integer) ENGINE=MyISAM;
 
2321
create table t1 (a integer, b integer);
2497
2322
select row(1,(2,2)) in (select * from t1 );
2498
2323
ERROR 21000: Operand should contain 2 column(s)
2499
2324
select row(1,(2,2)) = (select * from t1 );
2501
2326
select (select * from t1) = row(1,(2,2));
2502
2327
ERROR 21000: Operand should contain 1 column(s)
2503
2328
drop table t1;
2504
 
#
2505
 
# Forward reference detection
2506
 
#
2507
2329
create  table t1 (a integer);
2508
2330
insert into t1 values (1);
2509
2331
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;
2516
2338
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
2517
2339
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2518
2340
drop table t1;
2519
 
#
2520
 
# Test for BUG#8218
2521
 
#
2522
 
CREATE TEMPORARY TABLE t1 (
 
2341
CREATE TABLE t1 (
2523
2342
categoryId int NOT NULL,
2524
2343
courseId int NOT NULL,
2525
2344
startDate datetime NOT NULL,
2526
2345
endDate datetime NOT NULL,
2527
2346
createDate datetime NOT NULL,
2528
2347
modifyDate timestamp NOT NULL,
2529
 
attributes text NOT NULL)
2530
 
engine=myisam;
 
2348
attributes text NOT NULL
 
2349
);
2531
2350
INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2532
2351
(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2533
2352
(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2537
2356
(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2538
2357
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2539
2358
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');
2540
 
CREATE TEMPORARY TABLE t2 (
 
2359
CREATE TABLE t2 (
2541
2360
userId int NOT NULL,
2542
2361
courseId int NOT NULL,
2543
 
date datetime NOT NULL)
2544
 
engine=myisam;
 
2362
date datetime NOT NULL
 
2363
);
2545
2364
INSERT INTO t2 VALUES (5141,71,'2003-11-18'),
2546
2365
(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),
2547
2366
(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),
2548
2367
(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),
2549
2368
(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),
2550
2369
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');
2551
 
CREATE TEMPORARY TABLE t3 (
 
2370
CREATE TABLE t3 (
2552
2371
groupId int NOT NULL,
2553
2372
parentId int NOT NULL,
2554
2373
startDate datetime NOT NULL,
2555
2374
endDate datetime NOT NULL,
2556
2375
createDate datetime NOT NULL,
2557
2376
modifyDate timestamp NOT NULL,
2558
 
ordering int)
2559
 
engine=myisam;
 
2377
ordering int
 
2378
);
2560
2379
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
2561
 
CREATE TEMPORARY TABLE t4 (
 
2380
CREATE TABLE t4 (
2562
2381
id int NOT NULL,
2563
2382
groupTypeId int NOT NULL,
2564
2383
groupKey varchar(50) NOT NULL,
2566
2385
ordering int,
2567
2386
description text,
2568
2387
createDate datetime NOT NULL,
2569
 
modifyDate timestamp NOT NULL)
2570
 
engine=myisam;
 
2388
modifyDate timestamp NOT NULL
 
2389
);
2571
2390
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
2572
2391
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
2573
 
CREATE TEMPORARY TABLE t5 (
 
2392
CREATE TABLE t5 (
2574
2393
userId int NOT NULL,
2575
2394
groupId int NOT NULL,
2576
2395
createDate datetime NOT NULL,
2577
 
modifyDate timestamp NOT NULL) ENGINE=MyISAM;
 
2396
modifyDate timestamp NOT NULL
 
2397
);
2578
2398
INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
2579
2399
select
2580
2400
count(distinct t2.userid) pass,
2618
2438
1       5141    12      group2  12      group2  5       1       2       88      Oct04
2619
2439
1       5141    12      group2  12      group2  5       1       2       89      Oct04
2620
2440
drop table t1, t2, t3, t4, t5;
2621
 
#
2622
 
# Transformation in left expression of subquery (BUG#8888)
2623
 
#
2624
 
create temporary table t1 (a int) ENGINE=MyISAM;
 
2441
create table t1 (a int);
2625
2442
insert into t1 values (1), (2), (3);
2626
2443
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
2627
2444
1
2629
2446
1
2630
2447
1
2631
2448
drop table t1;
2632
 
#
2633
 
# single row subqueries and row operations (code covarage improvement)
2634
 
#
2635
 
create temporary table t1 (a int, b int) ENGINE=MyISAM;
 
2449
create table t1 (a int, b int);
2636
2450
insert into t1 values (1,2);
2637
2451
select 1 = (select * from t1);
2638
2452
ERROR 21000: Operand should contain 1 column(s)
2647
2461
select (select * from t1) = (1,2,3);
2648
2462
ERROR 21000: Operand should contain 2 column(s)
2649
2463
drop table t1;
2650
 
#
2651
 
# Item_int_with_ref check (BUG#10020)
2652
 
#
2653
 
#CREATE TABLE `t1` (
2654
 
#  `itemid` bigint NOT NULL auto_increment,
2655
 
#  `sessionid` bigint default NULL,
2656
 
#  `time` int NOT NULL default '0',
2657
 
#  `data` text collate latin1_general_ci NOT NULL,
2658
 
#  PRIMARY KEY  (`itemid`)
2659
 
#);
2660
 
#INSERT INTO `t1` VALUES (1, 1, 1, '');
2661
 
#CREATE TABLE `t2` (
2662
 
#  `sessionid` bigint NOT NULL auto_increment,
2663
 
#  `pid` int NOT NULL default '0',
2664
 
#  `date` int NOT NULL default '0',
2665
 
#  `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
2666
 
#  PRIMARY KEY  (`sessionid`)
2667
 
#);
2668
 
#INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
2669
 
#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;
2670
 
#drop tables t1,t2;
2671
 
# BUG#11821 : Select from subselect using aggregate function on an enum
2672
 
# segfaults:
2673
 
create temporary table t1 (fld enum('0','1')) ENGINE=MyISAM;
 
2464
create table t1 (fld enum('0','1'));
2674
2465
insert into t1 values ('1');
2675
2466
select * from (select max(fld) from t1) as foo;
2676
2467
max(fld)
2677
2468
1
2678
2469
drop table t1;
2679
 
#
2680
 
# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
2681
 
#
2682
2470
CREATE TABLE t1 (one int, two int, flag char(1));
2683
2471
CREATE TABLE t2 (one int, two int, flag char(1));
2684
2472
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2752
2540
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`
2753
2541
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2754
2542
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
2755
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    8       100.00  Using where
2756
 
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    9       100.00  Using where
 
2543
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    8       100.00  Start temporary
 
2544
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    9       100.00  Using where; End temporary; Using join buffer
2757
2545
Warnings:
2758
 
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)))
 
2546
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'))
2759
2547
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;
2760
2548
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
2761
2549
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    8       100.00  
2763
2551
Warnings:
2764
2552
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`
2765
2553
DROP TABLE t1,t2;
2766
 
#
2767
 
# Bug #12392: where cond with IN predicate for rows and NULL values in table 
2768
 
#
2769
 
CREATE TEMPORARY TABLE t1 (a char(5), b char(5)) ENGINE=MyISAM;
 
2554
CREATE TABLE t1 (a char(5), b char(5));
2770
2555
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
2771
2556
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
2772
2557
a       b
2773
2558
aaa     aaa
2774
2559
DROP TABLE t1;
2775
 
#
2776
 
# Bug #11479: subquery over left join with an empty inner table 
2777
 
#
2778
 
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
2779
 
CREATE TEMPORARY TABLE t2 (a int, b int) ENGINE=MyISAM;
2780
 
CREATE TEMPORARY TABLE t3 (b int NOT NULL) ENGINE=MyISAM;
 
2560
CREATE TABLE t1 (a int);
 
2561
CREATE TABLE t2 (a int, b int);
 
2562
CREATE TABLE t3 (b int NOT NULL);
2781
2563
INSERT INTO t1 VALUES (1), (2), (3), (4);
2782
2564
INSERT INTO t2 VALUES (1,10), (3,30);
2783
2565
SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b
2792
2574
3
2793
2575
4
2794
2576
DROP TABLE t1,t2,t3;
2795
 
#
2796
 
# Bug#18503: Queries with a quantified subquery returning empty set may
2797
 
# return a wrong result. 
2798
 
#
2799
 
CREATE TEMPORARY TABLE t1 (f1 INT) ENGINE=MyISAM;
2800
 
CREATE TEMPORARY TABLE t2 (f2 INT) ENGINE=MyISAM;
 
2577
CREATE TABLE t1 (f1 INT);
 
2578
CREATE TABLE t2 (f2 INT);
2801
2579
INSERT INTO t1 VALUES (1);
2802
2580
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
2803
2581
f1
2811
2589
f1
2812
2590
1
2813
2591
DROP TABLE t1, t2;
2814
 
# BUG#20975 Wrong query results for subqueries within NOT
2815
2592
create table t1 (s1 char);
2816
2593
insert into t1 values (1),(2);
2817
2594
select * from t1 where (s1 < any (select s1 from t1));
2839
2616
s1
2840
2617
2
2841
2618
drop table t1;
2842
 
#
2843
 
# Bug #16255: Subquery in where
2844
 
#
2845
2619
create table t1 (
2846
2620
retailerID varchar(8) NOT NULL,
2847
2621
statusID   int NOT NULL,
2848
2622
changed    datetime NOT NULL,
2849
 
UNIQUE KEY retailerID (retailerID, statusID, changed));
 
2623
UNIQUE KEY retailerID (retailerID, statusID, changed)
 
2624
);
2850
2625
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
2851
2626
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
2852
2627
INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
2863
2638
0048    1       2006-01-06 12:37:50
2864
2639
0059    1       2006-01-06 12:37:50
2865
2640
drop table t1;
2866
 
#
2867
 
# Bug #21180: Subselect with index for both WHERE and ORDER BY 
2868
 
#             produces empty result
2869
 
#
2870
2641
create table t1(a int, primary key (a));
2871
2642
insert into t1 values (10);
2872
2643
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
2875
2646
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
2876
2647
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2877
2648
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
2878
 
1       PRIMARY t1      const   PRIMARY PRIMARY 4       const   #       Using index
2879
 
1       PRIMARY r       const   PRIMARY PRIMARY 4       const   #       
2880
 
2       DEPENDENT SUBQUERY      t2      ref     b       b       5               #       Using where; Using index
 
2649
1       PRIMARY t1      const   PRIMARY PRIMARY 4       const   1       Using index
 
2650
1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
 
2651
2       DEPENDENT SUBQUERY      t2      ref     b       b       5               1       Using where; Using index
2881
2652
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
2882
2653
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
2883
2654
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2896
2667
a       a       b
2897
2668
10      1       359
2898
2669
drop table t1,t2;
2899
 
#
2900
 
# Bug #21853: assert failure for a grouping query with
2901
 
#             an ALL/ANY quantified subquery in HAVING 
2902
 
#
2903
 
CREATE TEMPORARY TABLE t1 (                  
 
2670
CREATE TABLE t1 (                  
2904
2671
field1 int NOT NULL,                 
2905
2672
field2 int NOT NULL,                 
2906
2673
field3 int NOT NULL,                 
2907
 
PRIMARY KEY  (field1,field2,field3))
2908
 
ENGINE=MyISAM;
2909
 
CREATE TEMPORARY TABLE t2 (             
 
2674
PRIMARY KEY  (field1,field2,field3)  
 
2675
);
 
2676
CREATE TABLE t2 (             
2910
2677
fieldA int NOT NULL,            
2911
2678
fieldB int NOT NULL,            
2912
 
PRIMARY KEY  (fieldA,fieldB))
2913
 
ENGINE=MyISAM;
 
2679
PRIMARY KEY  (fieldA,fieldB)     
 
2680
);
2914
2681
INSERT INTO t1 VALUES
2915
2682
(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
2916
2683
INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
2936
2703
1       1
2937
2704
1       3
2938
2705
DROP TABLE t1, t2;
2939
 
#
2940
 
# Bug #23478: not top-level IN subquery returning a non-empty result set
2941
 
#             with possible NULL values by index access from the outer query
2942
 
#
2943
 
CREATE TEMPORARY TABLE t1(a int, INDEX (a)) ENGINE=MyISAM;
 
2706
CREATE TABLE t1(a int, INDEX (a));
2944
2707
INSERT INTO t1 VALUES (1), (3), (5), (7);
2945
2708
INSERT INTO t1 VALUES (NULL);
2946
 
CREATE TEMPORARY TABLE t2(a int) ENGINE=MyISAM;
 
2709
CREATE TABLE t2(a int);
2947
2710
INSERT INTO t2 VALUES (1),(2),(3);
2948
2711
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
2949
2712
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
2955
2718
2       NULL
2956
2719
3       1
2957
2720
DROP TABLE t1,t2;
2958
 
#
2959
 
# Bug #11302: getObject() returns a String for a sub-query of type datetime
2960
 
#
2961
2721
CREATE TABLE t1 (a DATETIME);
2962
2722
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
2963
 
CREATE TEMPORARY TABLE t2 ENGINE=MyISAM AS SELECT 
 
2723
CREATE TABLE t2 AS SELECT 
2964
2724
(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a 
2965
2725
FROM t1 WHERE a > '2000-01-01';
2966
2726
SHOW CREATE TABLE t2;
2967
2727
Table   Create Table
2968
 
t2      CREATE TEMPORARY TABLE `t2` (
2969
 
  `sub_a` datetime DEFAULT NULL
2970
 
) ENGINE=MyISAM
2971
 
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');
 
2728
t2      CREATE TABLE `t2` (
 
2729
  `sub_a` datetime
 
2730
) ENGINE=InnoDB
 
2731
CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
2972
2732
SHOW CREATE TABLE t3;
2973
2733
Table   Create Table
2974
 
t3      CREATE TEMPORARY TABLE `t3` (
2975
 
  `a` datetime DEFAULT NULL
2976
 
) ENGINE=MyISAM
 
2734
t3      CREATE TABLE `t3` (
 
2735
  `a` datetime
 
2736
) ENGINE=InnoDB
2977
2737
DROP TABLE t1,t2,t3;
2978
 
#
2979
 
# Bug 24653: sorting by expressions containing subselects
2980
 
#            that return more than one row
2981
 
#
2982
 
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
 
2738
CREATE TABLE t1 (a int);
2983
2739
INSERT INTO t1 VALUES (2), (4), (1), (3);
2984
2740
CREATE TABLE t2 (b int, c int);
2985
2741
INSERT INTO t2 VALUES
3053
2809
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
3054
2810
ERROR 21000: Subquery returns more than 1 row
3055
2811
DROP TABLE t1,t2;
3056
 
# End of 4.1 tests
3057
 
#
3058
 
#decimal-related tests
3059
 
#
3060
2812
create table t1 (df decimal(5,1));
3061
2813
insert into t1 values(1.1);
3062
2814
insert into t1 values(2.2);
3067
2819
df
3068
2820
2.2
3069
2821
drop table t1;
3070
 
create temporary table t1 (df decimal(5,1)) ENGINE=MyISAM;
 
2822
create table t1 (df decimal(5,1));
3071
2823
insert into t1 values(1.1);
3072
2824
select 1.1 * exists(select * from t1);
3073
2825
1.1 * exists(select * from t1)
3074
2826
1.1
3075
2827
drop table t1;
3076
 
CREATE TEMPORARY TABLE t1 (
 
2828
CREATE TABLE t1 (
3077
2829
grp int default NULL,
3078
 
a decimal(10,2) default NULL) ENGINE=MyISAM;
 
2830
a decimal(10,2) default NULL);
3079
2831
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
3080
2832
select * from t1;
3081
2833
grp     a
3093
2845
2.00
3094
2846
4.00
3095
2847
drop table t1;
3096
 
#
3097
 
# Test for bug #9338: lame substitution of c1 instead of c2 
3098
 
#
3099
 
CREATE temporary table t1 ( c1 integer ) ENGINE=MyISAM;
 
2848
CREATE table t1 ( c1 integer );
3100
2849
INSERT INTO t1 VALUES ( 1 );
3101
2850
INSERT INTO t1 VALUES ( 2 );
3102
2851
INSERT INTO t1 VALUES ( 3 );
3112
2861
c1      c2
3113
2862
1       1
3114
2863
DROP TABLE t1,t2;
3115
 
#
3116
 
# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ 
3117
 
#
3118
 
CREATE TEMPORARY TABLE t1 ( c1 integer ) ENGINE=MyISAM;
 
2864
CREATE TABLE t1 ( c1 integer );
3119
2865
INSERT INTO t1 VALUES ( 1 );
3120
2866
INSERT INTO t1 VALUES ( 2 );
3121
2867
INSERT INTO t1 VALUES ( 3 );
3122
2868
INSERT INTO t1 VALUES ( 6 );
3123
 
CREATE TEMPORARY TABLE t2 ( c2 integer ) ENGINE=MyISAM;
 
2869
CREATE TABLE t2 ( c2 integer );
3124
2870
INSERT INTO t2 VALUES ( 1 );
3125
2871
INSERT INTO t2 VALUES ( 4 );
3126
2872
INSERT INTO t2 VALUES ( 5 );
3127
2873
INSERT INTO t2 VALUES ( 6 );
3128
 
CREATE TEMPORARY TABLE t3 ( c3 integer ) ENGINE=MyISAM;
 
2874
CREATE TABLE t3 ( c3 integer );
3129
2875
INSERT INTO t3 VALUES ( 7 );
3130
2876
INSERT INTO t3 VALUES ( 8 );
3131
2877
SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 
3134
2880
2       NULL
3135
2881
3       NULL
3136
2882
DROP TABLE t1,t2,t3;
3137
 
#
3138
 
# Correct building of equal fields list (do not include outer
3139
 
# fields) (BUG#6384)
3140
 
#
3141
 
CREATE TEMPORARY TABLE t1 (EMPNUM   CHAR(3)) ENGINE=MyISAM;
3142
 
CREATE TEMPORARY TABLE t2 (EMPNUM   CHAR(3) ) ENGINE=MyISAM;
 
2883
CREATE TABLE t1 (EMPNUM   CHAR(3));
 
2884
CREATE TABLE t2 (EMPNUM   CHAR(3) );
3143
2885
INSERT INTO t1 VALUES ('E1'),('E2');
3144
2886
INSERT INTO t2 VALUES ('E1');
3145
2887
DELETE FROM t1
3151
2893
EMPNUM
3152
2894
E1
3153
2895
DROP TABLE t1,t2;
3154
 
#
3155
 
# Test for bug #11487: range access in a subquery
3156
 
#
3157
 
CREATE TEMPORARY TABLE t1(select_id BIGINT, values_id BIGINT) ENGINE=MyISAM;
 
2896
CREATE TABLE t1(select_id BIGINT, values_id BIGINT);
3158
2897
INSERT INTO t1 VALUES (1, 1);
3159
 
CREATE TEMPORARY TABLE t2 (select_id BIGINT, values_id BIGINT, 
3160
 
PRIMARY KEY(select_id,values_id)) ENGINE=MyISAM;
 
2898
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT, 
 
2899
PRIMARY KEY(select_id,values_id));
3161
2900
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
3162
2901
SELECT values_id FROM t1 
3163
2902
WHERE values_id IN (SELECT values_id FROM t2
3175
2914
values_id
3176
2915
1
3177
2916
DROP TABLE t1, t2;
3178
 
# BUG#11821 : Select from subselect using aggregate function on an enum
3179
 
# segfaults:
3180
 
create temporary table t1 (fld enum('0','1')) ENGINE=MyISAM;
 
2917
create table t1 (fld enum('0','1'));
3181
2918
insert into t1 values ('1');
3182
2919
select * from (select max(fld) from t1) as foo;
3183
2920
max(fld)
3184
2921
1
3185
2922
drop table t1;
3186
 
#
3187
 
# Test for bug #11762: subquery with an aggregate function in HAVING
3188
 
#
3189
 
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
3190
 
CREATE TEMPORARY TABLE t2 (c int, d int) ENGINE=MyISAM;
3191
 
CREATE TEMPORARY TABLE t3 (e int) ENGINE=MyISAM;
 
2923
CREATE TABLE t1 (a int, b int);
 
2924
CREATE TABLE t2 (c int, d int);
 
2925
CREATE TABLE t3 (e int);
3192
2926
INSERT INTO t1 VALUES 
3193
2927
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
3194
2928
INSERT INTO t2 VALUES
3329
3063
3       20
3330
3064
4       40
3331
3065
DROP TABLE t1,t2,t3;
3332
 
#
3333
 
# Test for bug #16603: GROUP BY in a row subquery with a quantifier 
3334
 
#                      when an index is defined on the grouping field
3335
3066
CREATE TABLE t1 (a varchar(5), b varchar(10));
3336
3067
INSERT INTO t1 VALUES
3337
3068
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
3344
3075
EXPLAIN
3345
3076
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3346
3077
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
3347
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       Using where
3348
 
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    #       Using temporary; Using filesort
 
3078
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    9       Using where
 
3079
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    9       Using temporary; Using filesort
3349
3080
ALTER TABLE t1 ADD INDEX(a);
3350
3081
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3351
3082
a       b
3355
3086
EXPLAIN
3356
3087
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3357
3088
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
3358
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       Using where
3359
 
2       SUBQUERY        t1      index   NULL    a       23      NULL    #       
 
3089
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    9       Using where
 
3090
2       SUBQUERY        t1      index   NULL    a       23      NULL    9       
3360
3091
DROP TABLE t1;
3361
 
#
3362
 
# Bug#17366: Unchecked Item_int results in server crash
3363
 
#
3364
3092
create table t1( f1 int,f2 int);
3365
3093
insert into t1 values (1,1),(2,2);
3366
3094
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';
3368
3096
crash1
3369
3097
crash1
3370
3098
drop table t1;
3371
 
#
3372
 
# Bug #18306: server crash on delete using subquery.
3373
 
#
3374
 
create temporary table t1 (c int, key(c)) ENGINE=MyISAM;
 
3099
create table t1 (c int, key(c));
3375
3100
insert into t1 values (1142477582), (1142455969);
3376
 
create temporary table t2 (a int, b int) ENGINE=MyISAM;
 
3101
create table t2 (a int, b int);
3377
3102
insert into t2 values (2, 1), (1, 0);
3378
3103
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
3379
3104
drop table t1, t2;
3380
 
#
3381
 
# Bug#19077: A nested materialized derived table is used before being populated.
3382
 
#
3383
3105
create table t1 (i int, j bigint);
3384
3106
insert into t1 values (1, 2), (2, 2), (3, 2);
3385
3107
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
3386
3108
min(i)
3387
3109
1
3388
3110
drop table t1;
3389
 
# 
3390
 
# Bug#19700: subselect returning BIGINT always returned it as SIGNED
3391
 
#
3392
 
CREATE TEMPORARY TABLE t1 (i BIGINT) ENGINE=MyISAM;
 
3111
CREATE TABLE t1 (i BIGINT);
3393
3112
INSERT INTO t1 VALUES (10000000000000000);
3394
3113
INSERT INTO t1 VALUES (1);
3395
 
CREATE TEMPORARY TABLE t2 (i BIGINT) ENGINE=MyISAM;
 
3114
CREATE TABLE t2 (i BIGINT);
3396
3115
INSERT INTO t2 VALUES (10000000000000000);
3397
3116
INSERT INTO t2 VALUES (1);
3398
3117
/* simple test */
3410
3129
10000000000000000
3411
3130
DROP TABLE t1;
3412
3131
DROP TABLE t2;
3413
 
# 
3414
 
# Bug#20519: subselect with LIMIT M, N
3415
 
#
3416
 
CREATE TEMPORARY TABLE t1 (
 
3132
CREATE TABLE t1 (
3417
3133
id bigint NOT NULL auto_increment,
3418
3134
name varchar(255) NOT NULL,
3419
 
PRIMARY KEY  (id))
3420
 
ENGINE=MyISAM;
 
3135
PRIMARY KEY  (id)
 
3136
);
3421
3137
INSERT INTO t1 VALUES
3422
3138
(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
3423
3139
CREATE TABLE t2 (
3424
3140
id bigint NOT NULL auto_increment,
3425
3141
mid bigint NOT NULL,
3426
3142
date date NOT NULL,
3427
 
PRIMARY KEY  (id));
 
3143
PRIMARY KEY  (id)
 
3144
);
3428
3145
INSERT INTO t2 VALUES 
3429
3146
(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
3430
3147
(4, 2, '2006-04-20'), (5, 1, '2006-05-01');
3457
3174
2       Joe     2006-04-20      2006-04-06
3458
3175
3       Frank   2006-04-13      NULL
3459
3176
DROP TABLE t1,t2;
3460
 
#
3461
 
# Bug#20869: subselect with range access by DESC
3462
 
#
3463
3177
CREATE TABLE t1 (
3464
3178
i1 int NOT NULL default '0',
3465
3179
i2 int NOT NULL default '0',
3466
 
t datetime,
3467
 
PRIMARY KEY  (i1,i2,t));
 
3180
t datetime NOT NULL default '0000-00-00 00:00:00',
 
3181
PRIMARY KEY  (i1,i2,t)
 
3182
);
3468
3183
INSERT INTO t1 VALUES 
3469
3184
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
3470
3185
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
3476
3191
i1 int NOT NULL default '0',
3477
3192
i2 int NOT NULL default '0',
3478
3193
t datetime default NULL,
3479
 
PRIMARY KEY  (i1));
 
3194
PRIMARY KEY  (i1)
 
3195
);
3480
3196
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
3481
3197
EXPLAIN
3482
3198
SELECT * FROM t1,t2
3494
3210
i1      i2      t       i1      i2      t
3495
3211
24      1       2005-05-27 12:40:30     24      1       2006-06-20 12:29:40
3496
3212
DROP TABLE t1, t2;
3497
 
#
3498
 
# Bug#14654 : Cannot select from the same table twice within a UNION
3499
 
# statement 
3500
 
#
3501
3213
CREATE TABLE t1 (i INT);
3502
3214
(SELECT i FROM t1) UNION (SELECT i FROM t1);
3503
3215
i
3507
3219
(SELECT i FROM t1)
3508
3220
);
3509
3221
i
3510
 
#TODO:not supported
3511
3222
SELECT * FROM t1 
3512
3223
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
3513
 
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
3514
 
#TODO:not supported
 
3224
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
3515
3225
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
3516
3226
from t1;
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 t12.i from t1 t12))
 
3227
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))
3518
3228
from t1' at line 1
3519
3229
explain select * from t1 where not exists 
3520
3230
((select t11.i from t1 t11) union (select t12.i from t1 t12));
3521
3231
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
3522
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       
3523
 
2       SUBQUERY        t11     ALL     NULL    NULL    NULL    NULL    #       
3524
 
3       UNION   t12     ALL     NULL    NULL    NULL    NULL    #       
3525
 
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    #       
 
3232
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    1       
 
3233
2       SUBQUERY        t11     ALL     NULL    NULL    NULL    NULL    1       
 
3234
3       UNION   t12     ALL     NULL    NULL    NULL    NULL    1       
 
3235
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    
3526
3236
DROP TABLE t1;
3527
 
#
3528
 
# Bug #21540: Subqueries with no from and aggregate functions return 
3529
 
#              wrong results
3530
3237
CREATE TABLE t1 (a INT, b INT);
3531
3238
CREATE TABLE t2 (a INT);
3532
3239
INSERT INTO t2 values (1);
3567
3274
2
3568
3275
2
3569
3276
DROP TABLE t1,t2;
3570
 
#
3571
 
# Bug #21727: Correlated subquery that requires filesort:
3572
 
#             slow with big sort_buffer_size 
3573
 
#
3574
 
CREATE TEMPORARY TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)) ENGINE=MyISAM;
3575
 
CREATE TEMPORARY TABLE t2 (x int auto_increment, y int, z int,
3576
 
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)) ENGINE=MyISAM;
 
3277
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
 
3278
CREATE TABLE t2 (x int auto_increment, y int, z int,
 
3279
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
3577
3280
SET SESSION sort_buffer_size = 32 * 1024;
3578
3281
Warnings:
3579
 
Error   1292    Truncated incorrect sort_buffer_size value: '32768'
 
3282
Warning 1292    Truncated incorrect sort_buffer_size value: '32768'
3580
3283
SELECT COUNT(*) 
3581
3284
FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
3582
3285
FROM t1) t;
3589
3292
COUNT(*)
3590
3293
3000
3591
3294
DROP TABLE t2,t1;
3592
 
#
3593
 
# Bug #25219: EXIST subquery with UNION over a mix of
3594
 
#             correlated and uncorrelated selects
3595
 
#
3596
3295
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
3597
3296
CREATE TABLE t2 (c int);
3598
3297
INSERT INTO t1 VALUES ('aa', 1);
3614
3313
cc      3
3615
3314
dd      1
3616
3315
INSERT INTO t2 VALUES (2);
3617
 
CREATE TEMPORARY TABLE t3 (c int) ENGINE=MyISAM;
 
3316
CREATE TABLE t3 (c int);
3618
3317
INSERT INTO t3 VALUES (1);
3619
3318
SELECT * FROM t1
3620
3319
WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
3626
3325
cc      3
3627
3326
dd      1
3628
3327
DROP TABLE t1,t2,t3;
3629
 
#
3630
 
# Bug#21904 (parser problem when using IN with a double "(())")
3631
 
#
3632
3328
DROP TABLE IF EXISTS t1;
3633
3329
DROP TABLE IF EXISTS t2;
3634
3330
DROP TABLE IF EXISTS t1xt2;
3635
 
CREATE TEMPORARY TABLE t1 (
 
3331
CREATE TABLE t1 (
3636
3332
id_1 int NOT NULL,
3637
 
t varchar(4) DEFAULT NULL)
3638
 
ENGINE=MyISAM;
3639
 
CREATE TEMPORARY TABLE t2 (
 
3333
t varchar(4) DEFAULT NULL
 
3334
);
 
3335
CREATE TABLE t2 (
3640
3336
id_2 int NOT NULL,
3641
 
t varchar(4) DEFAULT NULL)
3642
 
ENGINE=MyISAM;
3643
 
CREATE TEMPORARY TABLE t1xt2 (
 
3337
t varchar(4) DEFAULT NULL
 
3338
);
 
3339
CREATE TABLE t1xt2 (
3644
3340
id_1 int NOT NULL,
3645
 
id_2 int NOT NULL)
3646
 
ENGINE=MyISAM;
 
3341
id_2 int NOT NULL
 
3342
);
3647
3343
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
3648
3344
INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');
3649
3345
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
3650
 
# subselect returns 0 rows
3651
3346
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3652
3347
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3653
3348
id_1
3679
3374
3
3680
3375
4
3681
3376
insert INTO t1xt2 VALUES (1, 12);
3682
 
# subselect returns 1 row
3683
3377
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3684
3378
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3685
3379
id_1
3711
3405
3
3712
3406
4
3713
3407
insert INTO t1xt2 VALUES (2, 12);
3714
 
# subselect returns more than 1 row
3715
3408
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3716
3409
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3717
3410
id_1
3745
3438
DROP TABLE t1;
3746
3439
DROP TABLE t2;
3747
3440
DROP TABLE t1xt2;
3748
 
#
3749
 
# Bug #26728: derived table with concatanation of literals in select list
3750
 
#  
3751
 
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
 
3441
CREATE TABLE t1 (a int);
3752
3442
INSERT INTO t1 VALUES (3), (1), (2);
3753
3443
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
3754
3444
col1    col2
3761
3451
this is a test. 1
3762
3452
this is a test. 2
3763
3453
DROP table t1;
3764
 
#
3765
 
# Bug #27257: COUNT(*) aggregated in outer query
3766
 
#  
3767
 
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
3768
 
CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;
 
3454
CREATE TABLE t1 (a int, b int);
 
3455
CREATE TABLE t2 (m int, n int);
3769
3456
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3770
3457
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3771
3458
SELECT COUNT(*), a,
3789
3476
2       2
3790
3477
3       3
3791
3478
DROP TABLE t1,t2;
3792
 
#
3793
 
# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument 
3794
 
#  
3795
 
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
3796
 
CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;
 
3479
CREATE TABLE t1 (a int, b int);
 
3480
CREATE TABLE t2 (m int, n int);
3797
3481
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3798
3482
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3799
3483
SELECT COUNT(*) c, a,
3811
3495
3       3       4
3812
3496
1       4       2,2
3813
3497
DROP table t1,t2;
3814
 
#
3815
 
# Bug#27321: Wrong subquery result in a grouping select
3816
 
#
3817
3498
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
3818
3499
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
3819
3500
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
3879
3560
2       o
3880
3561
3       p
3881
3562
DROP TABLE t1;
3882
 
#
3883
 
# Bug #27363: nested aggregates in outer, subquery / sum(select
3884
 
# count(outer))
3885
 
#
3886
3563
CREATE TABLE t1 (a INT);
3887
3564
INSERT INTO t1 values (1),(1),(1),(1);
3888
 
CREATE TEMPORARY TABLE t2 (x INT) ENGINE=MyISAM;
 
3565
CREATE TABLE t2 (x INT);
3889
3566
INSERT INTO t1 values (1000),(1001),(1002);
3890
3567
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
3891
3568
ERROR HY000: Invalid use of group function
3900
3577
FROM t1;
3901
3578
ERROR HY000: Invalid use of group function
3902
3579
DROP TABLE t1,t2;
3903
 
#
3904
 
# Bug #27807: Server crash when executing subquery with EXPLAIN
3905
 
#  
3906
3580
CREATE TABLE t1 (a int, b int, KEY (a));
3907
3581
INSERT INTO t1 VALUES (1,1),(2,1);
3908
3582
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
3910
3584
1       PRIMARY t1      ref     a       a       5       const   1       Using where; Using index
3911
3585
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    2       Using temporary; Using filesort
3912
3586
DROP TABLE t1;
3913
 
#
3914
 
# Bug #28377: grouping query with a correlated subquery in WHERE condition
3915
 
#  
3916
3587
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
3917
3588
INSERT INTO t1 VALUES
3918
3589
(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
3919
 
CREATE TEMPORARY TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;
 
3590
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
3920
3591
INSERT INTO t2 VALUES (7), (5), (1), (3);
3921
3592
SELECT id, st FROM t1 
3922
3593
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
3943
3614
2       GA
3944
3615
4       FL
3945
3616
DROP TABLE t1,t2;
3946
 
#
3947
 
# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table
3948
 
#             over a grouping subselect
3949
 
# 
3950
 
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
 
3617
CREATE TABLE t1 (a int);
3951
3618
INSERT INTO t1 VALUES (1), (2);
3952
3619
EXPLAIN EXTENDED
3953
3620
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
3957
3624
Warnings:
3958
3625
Note    1003    select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`
3959
3626
DROP TABLE t1;
3960
 
#
3961
 
# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1 
3962
 
#
3963
3627
CREATE TABLE t1 (
3964
3628
a varchar(255) default NULL,
3965
3629
b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3966
 
INDEX idx(a,b));
 
3630
INDEX idx(a,b)
 
3631
);
3967
3632
Warnings:
3968
3633
Warning 1071    Specified key was too long; max key length is 767 bytes
3969
3634
CREATE TABLE t2 (
3970
 
a varchar(255) default NULL);
 
3635
a varchar(255) default NULL
 
3636
);
3971
3637
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
3972
3638
INSERT INTO t1 SELECT * FROM t1;
3973
3639
INSERT INTO t1 SELECT * FROM t1;
3982
3648
INSERT INTO `t2` VALUES ('asdf');
3983
3649
SET session sort_buffer_size=8192;
3984
3650
Warnings:
3985
 
Error   1292    Truncated incorrect sort_buffer_size value: '8192'
 
3651
Warning 1292    Truncated incorrect sort_buffer_size value: '8192'
3986
3652
SELECT (SELECT 1 FROM  t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;
3987
3653
d1
3988
3654
1
3989
3655
1
3990
3656
DROP TABLE t1,t2;
3991
 
#
3992
 
# Bug #27333: subquery grouped for aggregate of outer query / no aggregate
3993
 
# of subquery
3994
 
#
3995
 
CREATE TEMPORARY TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;
3996
 
CREATE TEMPORARY TABLE t2 (x INTEGER) ENGINE=MyISAM;
 
3657
CREATE TABLE t1 (a INTEGER, b INTEGER);
 
3658
CREATE TABLE t2 (x INTEGER);
3997
3659
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
3998
3660
INSERT INTO t2 VALUES (1), (2);
3999
 
# wasn't failing, but should
4000
3661
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
4001
3662
ERROR 21000: Subquery returns more than 1 row
4002
 
# fails as it should
4003
3663
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
4004
3664
ERROR 21000: Subquery returns more than 1 row
4005
3665
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
4006
3666
(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)
4007
3667
3.3333
4008
3668
DROP TABLE t1,t2;
4009
 
# second test case from 27333
4010
3669
CREATE TABLE t1 (a INT, b INT);
4011
3670
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
4012
 
# returns no rows, when it should
4013
3671
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
4014
3672
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
4015
3673
GROUP BY a1.a;
4016
3674
a       COUNT(*)
4017
3675
1       3
4018
3676
DROP TABLE t1;
4019
 
#test cases from 29297
4020
 
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;
4021
 
CREATE TEMPORARY TABLE t2 (a INT) ENGINE=MyISAM;
 
3677
CREATE TABLE t1 (a INT);
 
3678
CREATE TABLE t2 (a INT);
4022
3679
INSERT INTO t1 VALUES (1),(2);
4023
3680
INSERT INTO t2 VALUES (1),(2);
4024
3681
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
4030
3687
(SELECT SUM(t1.a) FROM t2 WHERE a=1)
4031
3688
3
4032
3689
DROP TABLE t1,t2;
4033
 
#
4034
 
# Bug #31884: Assertion + crash in subquery in the SELECT clause.
4035
 
#
4036
 
CREATE TEMPORARY TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;
4037
 
CREATE TEMPORARY TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;
 
3690
CREATE TABLE t1 (a1 INT, a2 INT);
 
3691
CREATE TABLE t2 (b1 INT, b2 INT);
4038
3692
INSERT INTO t1 VALUES (100, 200);
4039
3693
INSERT INTO t1 VALUES (101, 201);
4040
3694
INSERT INTO t2 VALUES (101, 201);
4044
3698
0
4045
3699
0
4046
3700
DROP TABLE t1, t2;
4047
 
#
4048
 
# Bug #30788: Inconsistent retrieval of char/varchar
4049
 
#
4050
3701
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
4051
3702
INSERT INTO t1 VALUES ('a', 'aa');
4052
3703
INSERT INTO t1 VALUES ('a', 'aaa');
4053
3704
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
4054
3705
a       b
4055
 
a       aa
4056
 
a       aaa
4057
3706
CREATE INDEX I1 ON t1 (a);
4058
3707
CREATE INDEX I2 ON t1 (b);
4059
3708
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
4060
3709
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
4061
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       Using where
4062
 
2       SUBQUERY        t1      index   NULL    I1      7       NULL    #       Using index
 
3710
1       PRIMARY t1      index   I1      I1      7       NULL    2       Using index; LooseScan
 
3711
1       PRIMARY t1      ref     I2      I2      43      test.t1.a       1       Using index condition
4063
3712
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
4064
3713
a       b
4065
 
a       aa
4066
 
a       aaa
4067
3714
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
4068
3715
INSERT INTO t2 SELECT * FROM t1;
4069
3716
CREATE INDEX I1 ON t2 (a);
4070
3717
CREATE INDEX I2 ON t2 (b);
4071
3718
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
4072
3719
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
4073
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    #       Using where
4074
 
2       SUBQUERY        t2      index   NULL    I1      7       NULL    #       Using index
 
3720
1       PRIMARY t2      index   I1      I1      7       NULL    2       Using index; LooseScan
 
3721
1       PRIMARY t2      ref     I2      I2      43      test.t2.a       1       Using index condition
4075
3722
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
4076
3723
a       b
4077
 
a       aa
4078
 
a       aaa
4079
3724
EXPLAIN
4080
3725
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
4081
3726
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
4082
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       Using where
4083
 
2       SUBQUERY        t1      index   NULL    I1      7       NULL    #       Using where; Using index
 
3727
1       PRIMARY t1      index   I1      I1      7       NULL    2       Using where; Using index; LooseScan
 
3728
1       PRIMARY t1      ref     I2      I2      43      test.t1.a       1       Using index condition
4084
3729
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
4085
3730
a       b
4086
 
a       aa
4087
 
a       aaa
4088
3731
DROP TABLE t1,t2;
4089
 
#
4090
 
# Bug #32400: Complex SELECT query returns correct result only on some
4091
 
# occasions
4092
 
#
4093
3732
CREATE TABLE t1(a INT, b INT);
4094
3733
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
4095
3734
EXPLAIN 
4106
3745
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
4107
3746
GROUP BY a;
4108
3747
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
4109
 
1       PRIMARY t1_outer        ALL     NULL    NULL    NULL    NULL    #       Using where; Using temporary; Using filesort
4110
 
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    #       Using where
 
3748
1       PRIMARY t1_outer        ALL     NULL    NULL    NULL    NULL    4       Using where; Using temporary; Using filesort
 
3749
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    4       Using where
4111
3750
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
4112
3751
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
4113
3752
GROUP BY a;
4115
3754
1       2
4116
3755
2       4
4117
3756
DROP TABLE t1;
4118
 
#
4119
 
# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
4120
 
#
4121
 
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;
 
3757
CREATE TABLE t1 (a INT);
4122
3758
CREATE TABLE t2 (a INT);
4123
3759
INSERT INTO t1 VALUES (1),(2);
4124
3760
INSERT INTO t2 VALUES (1),(2);
4138
3774
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION 
4139
3775
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
4140
3776
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
4141
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
4142
 
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
4143
 
3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    #       100.00  Using where
4144
 
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    #       NULL    
 
3777
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 
3778
2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 
3779
3       DEPENDENT UNION t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
 
3780
NULL    UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    NULL    NULL    
4145
3781
Warnings:
4146
3782
Note    1276    Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
4147
3783
Note    1276    Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
4148
3784
Note    1003    select 2 AS `2` from `test`.`t1` where exists((select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))
4149
3785
DROP TABLE t1,t2;
4150
 
#
4151
 
# BUG#33794 "MySQL crashes executing specific query on specific dump"
4152
 
#
4153
 
CREATE TEMPORARY TABLE t4 (
 
3786
CREATE TABLE t4 (
4154
3787
f7 varchar(32) collate utf8_bin NOT NULL default '',
4155
3788
f10 varchar(32) collate utf8_bin default NULL,
4156
 
PRIMARY KEY  (f7))
4157
 
ENGINE=MyISAM;
 
3789
PRIMARY KEY  (f7)
 
3790
);
4158
3791
INSERT INTO t4 VALUES(1,1), (2,null);
4159
3792
CREATE TABLE t2 (
4160
3793
f4 varchar(32) collate utf8_bin NOT NULL default '',
4161
3794
f2 varchar(50) collate utf8_bin default NULL,
4162
3795
f3 varchar(10) collate utf8_bin default NULL,
4163
3796
PRIMARY KEY  (f4),
4164
 
UNIQUE KEY uk1 (f2));
 
3797
UNIQUE KEY uk1 (f2)
 
3798
);
4165
3799
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
4166
 
CREATE TEMPORARY TABLE t1  (
 
3800
CREATE TABLE t1 (
4167
3801
f8 varchar(32) collate utf8_bin NOT NULL default '',
4168
3802
f1 varchar(10) collate utf8_bin default NULL,
4169
3803
f9 varchar(32) collate utf8_bin default NULL,
4170
 
PRIMARY KEY  (f8))
4171
 
ENGINE=MyISAM;
 
3804
PRIMARY KEY  (f8)
 
3805
);
4172
3806
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
4173
 
CREATE TEMPORARY TABLE t3 (
 
3807
CREATE TABLE t3 (
4174
3808
f6 varchar(32) collate utf8_bin NOT NULL default '',
4175
3809
f5 varchar(50) collate utf8_bin default NULL,
4176
 
PRIMARY KEY (f6))
4177
 
ENGINE=MyISAM;
 
3810
PRIMARY KEY (f6)
 
3811
);
4178
3812
INSERT INTO t3 VALUES (1,null), (2,null);
4179
3813
SELECT
4180
3814
IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,
4198
3832
2       NULL    NULL
4199
3833
DROP TABLE t1, t2, t3, t4;
4200
3834
End of 5.0 tests.
4201
 
#
4202
 
#  Test [NOT] IN truth table (both as top-level and general predicate).
4203
 
#
4204
 
create temporary table t_out (subcase char(3),
4205
 
a1 char(2), b1 char(2), c1 char(2)) ENGINE=MyISAM;
 
3835
create table t_out (subcase char(3),
 
3836
a1 char(2), b1 char(2), c1 char(2));
4206
3837
create table t_in  (a2 char(2), b2 char(2), c2 char(2));
4207
3838
insert into t_out values ('A.1','2a', NULL, '2a');
4208
 
#------------------------- A.2 - impossible
4209
3839
insert into t_out values ('A.3', '2a', NULL, '2a');
4210
3840
insert into t_out values ('A.4', '2a', NULL, 'xx');
4211
3841
insert into t_out values ('B.1', '2a', '2a', '2a');
4305
3935
NOT((a1, b1, c1) IN (select * from t_in));
4306
3936
not_pred_in
4307
3937
F
4308
 
# test non-top level result indirectly
4309
3938
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4310
3939
where subcase = 'A.3' and
4311
3940
((a1, b1, c1) IN (select * from t_in)) is NULL and
4376
4005
NOT((a1, b1, c1) IN (select * from t_in));
4377
4006
not_pred_in
4378
4007
F
4379
 
# test non-top level result indirectly
4380
4008
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4381
4009
where subcase = 'B.3' and
4382
4010
((a1, b1, c1) IN (select * from t_in)) is NULL and
4401
4029
T
4402
4030
drop table t_out;
4403
4031
drop table t_in;
4404
 
#
4405
 
# Bug#20835 (literal string with =any values)
4406
 
#
4407
4032
CREATE TABLE t1 (s1 char(1));
4408
4033
INSERT INTO t1 VALUES ('a');
4409
 
SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);
 
4034
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
4410
4035
s1
4411
4036
a
4412
4037
DROP TABLE t1;
4413
 
#
4414
 
# Bug#33204: INTO is allowed in subselect, causing inconsistent results
4415
 
#
4416
4038
CREATE TABLE t1( a INT );
4417
4039
INSERT INTO t1 VALUES (1),(2);
4418
4040
CREATE TABLE t2( a INT, b INT );
4419
4041
SELECT * 
4420
4042
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
4421
 
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
 
4043
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
4422
4044
SELECT * 
4423
4045
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
4424
 
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
 
4046
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
4425
4047
SELECT * 
4426
4048
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
4427
 
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
 
4049
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
4428
4050
SELECT * FROM ( 
4429
4051
SELECT 1 a 
4430
4052
UNION 
4431
4053
SELECT a INTO @var FROM t1 WHERE a = 2 
4432
4054
) t1a;
4433
 
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 
 
4055
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 
4434
4056
) t1a' at line 2
4435
4057
SELECT * FROM ( 
4436
4058
SELECT 1 a 
4437
4059
UNION 
4438
4060
SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2 
4439
4061
) t1a;
4440
 
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 
 
4062
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 
4441
4063
) t1a' at line 2
4442
4064
SELECT * FROM ( 
4443
4065
SELECT 1 a 
4444
4066
UNION 
4445
4067
SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2 
4446
4068
) t1a;
4447
 
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 
 
4069
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 
4448
4070
) t1a' at line 2
4449
4071
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
4450
4072
a
4466
4088
a
4467
4089
1
4468
4090
2
4469
 
# This was not allowed previously. Possibly, it should be allowed on the future.
4470
 
# For now, the intent is to keep the fix as non-intrusive as possible.
4471
4091
SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
4472
 
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
 
4092
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
4473
4093
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
4474
4094
a
4475
4095
1
4477
4097
1
4478
4098
1
4479
4099
SELECT * FROM ((SELECT 1 a INTO @a)) t1a;
4480
 
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
 
4100
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
4481
4101
SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;
4482
 
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
 
4102
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
4483
4103
SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;
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 DUMPFILE 'file' )) t1a' at line 1
 
4104
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
4485
4105
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;
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 @a)) t1a' at line 1
 
4106
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
4487
4107
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;
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
 
4108
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
4489
4109
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;
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 OUTFILE 'file' )) t1a' at line 1
 
4110
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
4491
4111
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;
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 @a))) t1a' at line 1
 
4112
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
4493
4113
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;
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 DUMPFILE 'file' ))) t1a' at line 1
 
4114
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
4495
4115
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;
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 OUTFILE 'file' ))) t1a' at line 1
 
4116
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
4497
4117
SELECT * FROM (SELECT 1 a ORDER BY a) t1a;
4498
4118
a
4499
4119
1
4506
4126
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
4507
4127
a
4508
4128
1
4509
 
# Test of rule
4510
 
# table_factor:  '(' get_select_lex query_expression_body ')' opt_table_alias
4511
 
# UNION should not be allowed inside the parentheses, nor should
4512
 
# aliases after.
4513
 
# 
4514
4129
SELECT * FROM t1 JOIN  (SELECT 1 UNION SELECT 1) alias ON 1;
4515
4130
a       1
4516
4131
1       1
4517
4132
2       1
4518
4133
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
4519
 
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
 
4134
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
4520
4135
SELECT * FROM t1 JOIN  (t1 t1a UNION SELECT 1)  ON 1;
4521
 
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
 
4136
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
4522
4137
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 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
 
4138
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
4524
4139
SELECT * FROM t1 JOIN  (t1 t1a)  t1a ON 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 't1a ON 1' at line 1
 
4140
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
4526
4141
SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 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 't1a ON 1' at line 1
 
4142
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
4528
4143
SELECT * FROM t1 JOIN  (t1 t1a)  ON 1;
4529
4144
a       a
4530
4145
1       1
4561
4176
a       a
4562
4177
1       1
4563
4178
2       1
4564
 
# For the join, TABLE_LIST::select_lex == NULL
4565
 
# Check that we handle this.
4566
4179
SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;
4567
 
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
 
4180
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
4568
4181
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );
4569
4182
a
4570
4183
1
4575
4188
a
4576
4189
1
4577
4190
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a);
4578
 
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
 
4191
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
4579
4192
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4580
 
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
 
4193
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
4581
4194
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
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 DUMPFILE 'file' )' at line 1
 
4195
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
4583
4196
SELECT * FROM t1 WHERE a = ( SELECT 1 );
4584
4197
a
4585
4198
1
4587
4200
a
4588
4201
1
4589
4202
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);
4590
 
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
 
4203
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
4591
4204
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );
4592
 
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
 
4205
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
4593
4206
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );
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 DUMPFILE 'file' )' at line 1
 
4207
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
4595
4208
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);
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 @a)' at line 1
 
4209
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
4597
4210
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
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 OUTFILE 'file' )' at line 1
 
4211
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
4599
4212
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
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 DUMPFILE 'file' )' at line 1
 
4213
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
4601
4214
SELECT ( SELECT 1 INTO @v );
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 @v )' at line 1
 
4215
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
4603
4216
SELECT ( SELECT 1 INTO OUTFILE 'file' );
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 OUTFILE 'file' )' at line 1
 
4217
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
4605
4218
SELECT ( SELECT 1 INTO DUMPFILE 'file' );
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 DUMPFILE 'file' )' at line 1
 
4219
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
4607
4220
SELECT ( SELECT 1 UNION SELECT 1 INTO @v );
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 @v )' at line 1
 
4221
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
4609
4222
SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
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 OUTFILE 'file' )' at line 1
 
4223
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
4611
4224
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
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 DUMPFILE 'file' )' at line 1
4613
 
# Make sure context is popped when we leave the nested select
 
4225
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
4614
4226
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
4615
4227
( SELECT a FROM t1 WHERE a = 1 )        a
4616
4228
1       1
4619
4231
( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a
4620
4232
1       1
4621
4233
1       2
4622
 
# Make sure we have feature F561 (see .yy file)
4623
4234
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
4624
4235
a       b
4625
 
# Make sure the parser does not allow nested UNIONs anywhere
4626
4236
SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );
4627
 
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
 
4237
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
4628
4238
( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
4629
 
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
 
4239
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
4630
4240
SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 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
 
4241
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
4632
4242
SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 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' at line 1
 
4243
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
4634
4244
SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4635
4245
( SELECT 1 UNION SELECT 1 UNION SELECT 1 )
4636
4246
1
4638
4248
((SELECT 1 UNION SELECT 1 UNION SELECT 1))
4639
4249
1
4640
4250
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4641
 
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
 
4251
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
4642
4252
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4643
 
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
 
4253
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
4644
4254
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
4645
4255
1
4646
4256
1
4647
4257
SELECT * FROM t1 WHERE a =     ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4648
 
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
 
4258
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
4649
4259
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4650
 
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
 
4260
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
4651
4261
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 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
 
4262
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
4653
4263
SELECT * FROM t1 WHERE a IN    ( SELECT 1 UNION ( SELECT 1 UNION SELECT 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
 
4264
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
4655
4265
SELECT * FROM t1 WHERE a =     ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 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
 
4266
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
4657
4267
SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 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 )  UNION SELECT 1 )' at line 1
 
4268
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
4659
4269
SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 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 )  UNION SELECT 1 )' at line 1
 
4270
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
4661
4271
SELECT * FROM t1 WHERE a IN    ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 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 )' at line 1
 
4272
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
4663
4273
SELECT * FROM t1 WHERE a =     ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
4664
4274
a
4665
4275
1
4673
4283
a
4674
4284
1
4675
4285
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v );
4676
 
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
 
4286
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
4677
4287
SELECT EXISTS(SELECT 1+1);
4678
4288
EXISTS(SELECT 1+1)
4679
4289
1
4680
4290
SELECT EXISTS(SELECT 1+1 INTO @test);
4681
 
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
 
4291
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
4682
4292
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v );
4683
 
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
 
4293
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
4684
4294
SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );
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 @v )' at line 1
 
4295
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
4686
4296
SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
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
 
4297
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
4688
4298
DROP TABLE t1, t2;