« back to all changes in this revision
Viewing changes to tests/r/subselect_no_opts.result
- browse files at revision 1094
- compare with another revision
- compare with revision 1730.6.1
- download diff
- download tarball
- view history from revision 1094
- Committer: Brian Aker
- Date: 2009-07-12 00:49:18 UTC
- mfrom: (1063.9.51 brian-tmp-fix)
- Revision ID: brian@gaz-20090712004918-chprmyj387ex6l8a
Merge Stewart
- files removed:
- tests/r/drizzlecheck.result
- files renamed:
- tests/r/rollback.result => tests/r/myisam_rollback.result
- tests/t/rollback.test => tests/t/myisam_rollback.test
- files modified:
- drizzled/base.h
added
removed
tests/r/subselect_no_opts.result
2
2
Variable_name Value
3
3
optimizer_switch
4
4
set optimizer_switch='no_materialization,no_semijoin';
5
# Initialise
5
6
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
6
7
select (select 2);
7
8
(select 2)
135
136
SELECT 1 as a,(SELECT a+a) b,(SELECT b);
136
137
a b (SELECT b)
137
138
1 2 2
138
create table t1 (a int) ENGINE=MyISAM;
139
create table t2 (a int, b int) ENGINE=MyISAM;
140
create table t3 (a int) ENGINE=MyISAM;
141
create table t4 (a int not null, b int not null) ENGINE=MyISAM;
139
create table t1 (a int);
140
create table t2 (a int, b int);
141
create table t3 (a int);
142
create table t4 (a int not null, b int not null);
142
143
insert into t1 values (2);
143
144
insert into t2 values (1,7),(2,7);
144
145
insert into t4 values (4,8),(3,8),(5,9);
181
182
3 8
182
183
explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
183
184
id select_type table type possible_keys key key_len ref rows filtered Extra
184
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
185
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using filesort
186
3 UNION t4 ALL NULL NULL NULL NULL 3 100.00 Using where
187
4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
188
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
185
1 PRIMARY t2 ALL NULL NULL NULL NULL # 100.00 Using where
186
2 SUBQUERY t3 ALL NULL NULL NULL NULL # 100.00 Using filesort
187
3 UNION t4 ALL NULL NULL NULL NULL # 100.00 Using where
188
4 SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00
189
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL # NULL
189
190
Warnings:
190
191
Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` AS `a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) AS `max(t2.a)*4` from `test`.`t2`)) order by `a`)
191
192
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
199
200
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
200
201
(select * from t2 where a>1) as tt;
201
202
id select_type table type possible_keys key key_len ref rows filtered Extra
202
1 PRIMARY <derived3> system NULL NULL NULL NULL 1 100.00
203
3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
204
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
203
1 PRIMARY <derived3> system NULL NULL NULL NULL # 100.00
204
3 DERIVED t2 ALL NULL NULL NULL NULL # 100.00 Using where
205
2 SUBQUERY t3 ALL NULL NULL NULL NULL # 100.00 Using where; Using filesort
205
206
Warnings:
206
207
Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
207
208
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
219
220
9 7.5000
220
221
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
221
222
id select_type table type possible_keys key key_len ref rows filtered Extra
222
1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00
223
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
224
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
223
1 PRIMARY t4 ALL NULL NULL NULL NULL # 100.00
224
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00
225
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL # 100.00 Using where
225
226
Warnings:
226
227
Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
227
228
Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
246
247
a
247
248
6
248
249
3
250
# Rewrite: select * from t3 where not exists (select b from t2 where a <> b);
249
251
select * from t3 where a = all (select b from t2);
250
252
a
251
253
7
267
269
7
268
270
explain extended select * from t3 where a >= any (select b from t2);
269
271
id select_type table type possible_keys key key_len ref rows filtered Extra
270
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
271
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00
272
1 PRIMARY t3 ALL NULL NULL NULL NULL # 100.00 Using where
273
2 SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00
272
274
Warnings:
273
275
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
274
276
select * from t3 where a >= all (select b from t2);
280
282
select * from t3 where a in (select * from t2);
281
283
ERROR 21000: Operand should contain 1 column(s)
282
284
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
285
# empty set
283
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);
284
287
b ma
285
288
insert into t2 values (2,10);
290
293
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);
291
294
b ma
292
295
7 12
293
create table t5 (a int) ENGINE=MyISAM;
296
create temporary table t5 (a int) ENGINE=MyISAM;
294
297
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
295
298
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a
296
299
NULL 1
307
310
2 2
308
311
explain extended select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
309
312
id select_type table type possible_keys key key_len ref rows filtered Extra
310
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
311
2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
312
3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 100.00 Using where
313
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
313
1 PRIMARY t2 ALL NULL NULL NULL NULL # 100.00
314
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00 Using where
315
3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL # 100.00 Using where
316
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL # NULL
314
317
Warnings:
315
318
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
316
319
Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
317
Note 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
320
Note 1003 select (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
318
321
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
319
322
ERROR 21000: Subquery returns more than 1 row
320
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)) ENGINE=MyISAM;
321
create table t7( uq int primary key, name char(25)) ENGINE=MyISAM;
323
create temporary table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)) ENGINE=MyISAM;
324
create temporary table t7( uq int primary key, name char(25)) ENGINE=MyISAM;
322
325
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
323
326
insert into t6 values (1,1),(1,2),(2,2),(1,3);
324
327
select * from t6 where exists (select * from t7 where uq = clinic_uq);
333
336
Warnings:
334
337
Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
335
338
Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
339
# not unique fields
336
340
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
337
341
ERROR 23000: Column 'a' in field list is ambiguous
342
# different tipes & group functions
338
343
drop table t1,t2,t3;
339
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0') ENGINE=MyISAM;
344
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
340
345
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
341
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0') ENGINE=MyISAM;
346
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
342
347
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
343
CREATE TABLE t1 (a varchar(20),b date NULL) ENGINE=MyISAM;
348
CREATE TABLE t1 (a varchar(20),b date NULL);
344
349
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
345
350
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
346
351
a b
356
361
`email` varchar(60) NOT NULL default '',
357
362
PRIMARY KEY (`pseudo`),
358
363
UNIQUE KEY `email` (`email`)
359
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
364
) ROW_FORMAT=DYNAMIC;
365
Warnings:
366
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
367
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
360
368
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
361
369
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
362
370
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
363
371
EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
364
372
id select_type table type possible_keys key key_len ref rows filtered Extra
365
1 PRIMARY t8 const PRIMARY PRIMARY 142 const 1 100.00 Using index
366
4 SUBQUERY t8 const PRIMARY PRIMARY 142 1 100.00 Using index
367
2 SUBQUERY t8 const PRIMARY PRIMARY 142 const 1 100.00
368
3 SUBQUERY t8 const PRIMARY PRIMARY 142 1 100.00 Using index
373
1 PRIMARY t8 const PRIMARY PRIMARY 142 const # 100.00 Using index
374
4 SUBQUERY t8 const PRIMARY PRIMARY 142 # 100.00 Using index
375
2 SUBQUERY t8 const PRIMARY PRIMARY 142 const # 100.00
376
3 SUBQUERY t8 const PRIMARY PRIMARY 142 # 100.00 Using index
369
377
Warnings:
370
378
Note 1003 select 'joce' AS `pseudo`,(select 'test' AS `email` from `test`.`t8` where ('joce' = (select 'joce' AS `pseudo` from `test`.`t8` where ('joce' = 'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where ('joce' = (select 'joce' AS `pseudo` from `test`.`t8` where ('joce' = 'joce')))
371
379
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
380
388
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
381
389
ERROR 21000: Subquery returns more than 1 row
382
390
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
383
CREATE TABLE `t1` (
391
#searchconthardwarefr3 forumconthardwarefr7
392
CREATE TEMPORARY TABLE `t1` (
384
393
`topic` bigint NOT NULL default '0',
385
394
`date` date NULL,
386
395
`pseudo` varchar(35) NOT NULL default '',
422
431
Warnings:
423
432
Note 1003 select 1 AS `1` from `test`.`t1` where 1
424
433
drop table t1;
434
#forumconthardwarefr7 searchconthardwarefr7
425
435
CREATE TABLE `t1` (
426
436
`numeropost` bigint NOT NULL auto_increment,
427
437
`maxnumrep` int NOT NULL default '0',
428
438
PRIMARY KEY (`numeropost`),
429
439
UNIQUE KEY `maxnumrep` (`maxnumrep`)
430
) ENGINE=MyISAM ROW_FORMAT=FIXED;
440
) ROW_FORMAT=FIXED;
441
Warnings:
442
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
431
443
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
432
444
CREATE TABLE `t2` (
433
445
`mot` varchar(30) NOT NULL default '',
435
447
`date` date NULL,
436
448
`pseudo` varchar(35) NOT NULL default '',
437
449
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`)
438
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
450
) ROW_FORMAT=DYNAMIC;
451
Warnings:
452
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.
453
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
439
454
INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
440
455
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
441
456
a
494
509
joce 40143 2002-10-22 joce 1
495
510
joce 43506 2002-10-22 joce 0
496
511
drop table t1,t2;
512
#forumconthardwarefr7
497
513
CREATE TABLE `t1` (
498
514
`numeropost` bigint NOT NULL auto_increment,
499
515
`maxnumrep` int NOT NULL default '0',
500
516
PRIMARY KEY (`numeropost`),
501
517
UNIQUE KEY `maxnumrep` (`maxnumrep`)
502
) ENGINE=MyISAM ROW_FORMAT=FIXED;
518
) ROW_FORMAT=FIXED;
519
Warnings:
520
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
503
521
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
504
522
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
505
523
ERROR 21000: Subquery returns more than 1 row
506
524
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
507
525
ERROR 21000: Subquery returns more than 1 row
508
526
drop table t1;
509
create table t1 (a int) ENGINE=MyISAM;
527
create table t1 (a int);
510
528
insert into t1 values (1),(2),(3);
511
529
(select * from t1) union (select * from t1) order by (select a from t1 limit 1);
512
530
a
514
532
2
515
533
3
516
534
drop table t1;
517
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b') ENGINE=MyISAM;
535
#iftest
536
CREATE TEMPORARY TABLE t1 (field char(1) NOT NULL DEFAULT 'b') ENGINE=MyISAM;
518
537
INSERT INTO t1 VALUES ();
519
538
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
520
539
ERROR 21000: Subquery returns more than 1 row
521
540
drop table t1;
541
# threadhardwarefr7
522
542
CREATE TABLE `t1` (
523
543
`numeropost` bigint NOT NULL default '0',
524
544
`numreponse` int NOT NULL auto_increment,
526
546
PRIMARY KEY (`numeropost`,`numreponse`),
527
547
UNIQUE KEY `numreponse` (`numreponse`),
528
548
KEY `pseudo` (`pseudo`,`numeropost`)
529
) ENGINE=MyISAM;
549
);
530
550
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
531
551
ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list)
532
552
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
538
558
ERROR 21000: Subquery returns more than 1 row
539
559
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
540
560
id select_type table type possible_keys key key_len ref rows filtered Extra
541
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
561
1 SIMPLE NULL NULL NULL NULL NULL NULL # NULL Select tables optimized away
542
562
Warnings:
543
563
Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)
544
564
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
545
565
id select_type table type possible_keys key key_len ref rows filtered Extra
546
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 12 const,const 1 100.00 Using index
547
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
566
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 12 const,const # 100.00 Using index
567
2 SUBQUERY NULL NULL NULL NULL NULL NULL # NULL Select tables optimized away
548
568
Warnings:
549
569
Note 1003 select '3' AS `numreponse` from `test`.`t1` where 1
550
570
drop table t1;
551
CREATE TABLE t1 (a int) ENGINE=MyISAM;
571
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
552
572
INSERT INTO t1 VALUES (1);
553
573
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
554
574
1
555
575
1
556
576
drop table t1;
557
create table t1 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
558
create table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
577
#update with subselects
578
create table t1 (a int NOT NULL, b int, primary key (a));
579
create table t2 (a int NOT NULL, b int, primary key (a));
559
580
insert into t1 values (0, 10),(1, 11),(2, 12);
560
581
insert into t2 values (1, 21),(2, 22),(3, 23);
561
582
select * from t1;
574
595
1 21
575
596
2 22
576
597
drop table t1, t2;
577
create table t1 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
578
create table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
598
#delete with subselects
599
create table t1 (a int NOT NULL, b int, primary key (a));
600
create table t2 (a int NOT NULL, b int, primary key (a));
579
601
insert into t1 values (0, 10),(1, 11),(2, 12);
580
602
insert into t2 values (1, 21),(2, 12),(3, 23);
581
603
select * from t1;
596
618
0 10
597
619
1 11
598
620
drop table t1, t2;
599
create table t11 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
600
create table t12 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
601
create table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
621
#multi-delete with subselects
622
create table t11 (a int NOT NULL, b int, primary key (a));
623
create table t12 (a int NOT NULL, b int, primary key (a));
624
create temporary table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
602
625
insert into t11 values (0, 10),(1, 11),(2, 12);
603
626
insert into t12 values (33, 10),(22, 11),(2, 12);
604
627
insert into t2 values (1, 21),(2, 12),(3, 23);
626
649
22 11
627
650
33 10
628
651
drop table t11, t12, t2;
629
CREATE TABLE t1 (x int) ENGINE=MyISAM;
630
create table t2 (a int) ENGINE=MyISAM;
631
create table t3 (b int) ENGINE=MyISAM;
652
#insert with subselects
653
CREATE TABLE t1 (x int);
654
create table t2 (a int);
655
create table t3 (b int);
632
656
insert into t2 values (1);
633
657
insert into t3 values (1),(2);
634
658
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
652
676
2
653
677
3
654
678
3
679
# After this, only data based on old t1 records should have been added.
655
680
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
656
681
select * from t1;
657
682
x
673
698
11
674
699
11
675
700
2
701
#
702
#TODO: should be uncommented after bug 380 fix pushed
703
#INSERT INTO t1 (x) SELECT (SELECT SUM(a)+b FROM t2) from t3;
704
#select * from t1;
676
705
drop table t1, t2, t3;
677
CREATE TABLE t1 (x int not null, y int, primary key (x)) ENGINE=MyISAM;
678
create table t2 (a int) ENGINE=MyISAM;
679
create table t3 (a int) ENGINE=MyISAM;
706
#replace with subselects
707
CREATE TABLE t1 (x int not null, y int, primary key (x));
708
create table t2 (a int);
709
create temporary table t3 (a int) ENGINE=MyISAM;
680
710
insert into t2 values (1);
681
711
insert into t3 values (1),(2);
682
712
select * from t1;
712
742
drop table t1, t2, t3;
713
743
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
714
744
ERROR HY000: No tables used
715
CREATE TABLE t2 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
745
CREATE TABLE t2 (id int default NULL, KEY id (id));
716
746
INSERT INTO t2 VALUES (1),(2);
717
747
SELECT * FROM t2 WHERE id IN (SELECT 1);
718
748
id
757
787
id
758
788
1
759
789
2
760
CREATE TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
790
CREATE TEMPORARY TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;
761
791
INSERT INTO t1 values (1),(1);
762
792
UPDATE t2 SET id=(SELECT * FROM t1);
763
793
ERROR 21000: Subquery returns more than 1 row
764
794
drop table t2, t1;
765
create table t1 (a int) ENGINE=MyISAM;
795
#NULL test
796
create temporary table t1 (a int) ENGINE=MyISAM;
766
797
insert into t1 values (1),(2),(3);
767
798
select 1 IN (SELECT * from t1);
768
799
1 IN (SELECT * from t1)
796
827
10 > ANY (SELECT * from t1)
797
828
1
798
829
drop table t1;
799
create table t1 (a varchar(20)) ENGINE=MyISAM;
830
create temporary table t1 (a varchar(20)) ENGINE=MyISAM;
800
831
insert into t1 values ('A'),('BC'),('DEF');
801
832
select 'A' IN (SELECT * from t1);
802
833
'A' IN (SELECT * from t1)
830
861
'XYZS' > ANY (SELECT * from t1)
831
862
1
832
863
drop table t1;
833
create table t1 (a float) ENGINE=MyISAM;
864
create temporary table t1 (a float) ENGINE=MyISAM;
834
865
insert into t1 values (1.5),(2.5),(3.5);
835
866
select 1.5 IN (SELECT * from t1);
836
867
1.5 IN (SELECT * from t1)
876
907
4.5
877
908
NULL
878
909
drop table t1;
879
CREATE TABLE t1 (a int NOT NULL default '0', PRIMARY KEY (a)) ENGINE=MyISAM;
880
CREATE TABLE t2 (a int default '0', INDEX (a)) ENGINE=MyISAM;
910
#
911
# Null with keys
912
#
913
CREATE TEMPORARY TABLE t1 (a int NOT NULL default '0', PRIMARY KEY (a)) ENGINE=MyISAM;
914
CREATE TEMPORARY TABLE t2 (a int default '0', INDEX (a)) ENGINE=MyISAM;
881
915
INSERT INTO t1 VALUES (1),(2),(3),(4);
882
916
INSERT INTO t2 VALUES (1),(2),(3);
883
917
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
892
926
2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
893
927
Warnings:
894
928
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
895
CREATE TABLE t3 (a int default '0') ENGINE=MyISAM;
929
CREATE TEMPORARY TABLE t3 (a int default '0') ENGINE=MyISAM;
896
930
INSERT INTO t3 VALUES (1),(2),(3);
897
931
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
898
932
a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
908
942
Warnings:
909
943
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`
910
944
drop table t1,t2,t3;
911
CREATE TABLE t1 (a int) ENGINE=MyISAM;
945
#LIMIT is not supported now
946
#create table t1 (a float) ENGINE=MyISAM;
947
#-- error 1235
948
#select 10.5 IN (SELECT * from t1 LIMIT 1);
949
#-- error 1235
950
#select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
951
#drop table t1;
952
#
953
#create table t1 (a int, b int, c varchar(10)) ENGINE=MyISAM;
954
#create table t2 (a int) ENGINE=MyISAM;
955
#insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
956
#insert into t2 values (1),(2),(NULL);
957
#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;
958
#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;
959
#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;
960
#drop table t1,t2;
961
#
962
#create table t1 (a int, b real, c varchar(10)) ENGINE=MyISAM;
963
#insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
964
#select ROW(1, 1, 'a') IN (select a,b,c from t1);
965
#select ROW(1, 2, 'a') IN (select a,b,c from t1);
966
#select ROW(1, 1, 'a') IN (select b,a,c from t1);
967
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
968
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
969
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
970
#select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
971
#select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
972
#select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
973
#-- error 1235
974
#select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
975
#drop table t1;
976
#test of uncacheable subqueries
977
CREATE TABLE t1 (a int);
912
978
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
913
979
id select_type table type possible_keys key key_len ref rows filtered Extra
914
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
915
2 SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
980
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00
981
2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00
916
982
Warnings:
917
983
Note 1003 select (select rand() AS `RAND()` from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1`
918
984
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
919
985
id select_type table type possible_keys key key_len ref rows filtered Extra
920
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
921
2 SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
986
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00
987
2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00
922
988
Warnings:
923
989
Note 1003 select (select benchmark(1,1) AS `BENCHMARK(1,1)` from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1`
924
990
drop table t1;
925
CREATE TABLE `t1` (
991
CREATE TEMPORARY TABLE `t1` (
926
992
`mot` varchar(30) NOT NULL default '',
927
993
`topic` bigint NOT NULL default '0',
928
994
`date` date NULL DEFAULT '2009-01-20',
931
997
KEY `pseudo` (`pseudo`,`date`,`topic`),
932
998
KEY `topic` (`topic`)
933
999
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
934
CREATE TABLE `t2` (
1000
CREATE TEMPORARY TABLE `t2` (
935
1001
`mot` varchar(30) NOT NULL default '',
936
1002
`topic` bigint NOT NULL default '0',
937
1003
`date` date NULL default '1997-08-29',
940
1006
KEY `pseudo` (`pseudo`,`date`,`topic`),
941
1007
KEY `topic` (`topic`)
942
1008
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
943
CREATE TABLE `t3` (
1009
CREATE TEMPORARY TABLE `t3` (
944
1010
`numeropost` bigint NOT NULL auto_increment,
945
1011
`maxnumrep` int NOT NULL default '0',
946
1012
PRIMARY KEY (`numeropost`),
966
1032
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
967
1033
a (SELECT a)
968
1034
1 1
969
CREATE TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
1035
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
970
1036
SHOW CREATE TABLE t1;
971
1037
Table Create Table
972
t1 CREATE TABLE `t1` (
1038
t1 CREATE TEMPORARY TABLE `t1` (
973
1039
`a` int NOT NULL DEFAULT '0',
974
1040
`(SELECT 1)` int NOT NULL DEFAULT '0'
975
1041
) ENGINE=MyISAM
976
1042
drop table t1;
977
CREATE TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
1043
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
978
1044
SHOW CREATE TABLE t1;
979
1045
Table Create Table
980
t1 CREATE TABLE `t1` (
1046
t1 CREATE TEMPORARY TABLE `t1` (
981
1047
`a` int NOT NULL DEFAULT '0',
982
1048
`(SELECT a)` int NOT NULL DEFAULT '0'
983
1049
) ENGINE=MyISAM
984
1050
drop table t1;
985
CREATE TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
1051
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
986
1052
SHOW CREATE TABLE t1;
987
1053
Table Create Table
988
t1 CREATE TABLE `t1` (
1054
t1 CREATE TEMPORARY TABLE `t1` (
989
1055
`a` int NOT NULL DEFAULT '0',
990
1056
`(SELECT a+0)` int NOT NULL DEFAULT '0'
991
1057
) ENGINE=MyISAM
992
1058
drop table t1;
993
CREATE TABLE t1 ENGINE=MyISAM SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
1059
CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
994
1060
select * from t1;
995
1061
a
996
1062
2
997
1063
SHOW CREATE TABLE t1;
998
1064
Table Create Table
999
t1 CREATE TABLE `t1` (
1065
t1 CREATE TEMPORARY TABLE `t1` (
1000
1066
`a` bigint NOT NULL
1001
1067
) ENGINE=MyISAM
1002
1068
drop table t1;
1003
create table t1 (a int) ENGINE=MyISAM;
1069
create table t1 (a int);
1004
1070
insert into t1 values (1), (2), (3);
1005
1071
explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1)
1006
1072
from t1;
1007
1073
id select_type table type possible_keys key key_len ref rows filtered Extra
1008
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1009
2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
1010
3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
1074
1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00
1075
2 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00
1076
3 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00
1011
1077
Warnings:
1012
1078
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`
1013
1079
drop table t1;
1080
#
1081
# error in IN
1082
#
1014
1083
select t1.Continent, t2.Name, t2.Population from t1 LEFT JOIN t2 ON t1.Code = t2.Country where t2.Population IN (select max(t2.Population) AS Population from t2, t1 where t2.Country = t1.Code group by Continent);
1015
1084
ERROR 42S02: Table 'test.t1' doesn't exist
1085
#
1086
# complex subquery
1087
#
1016
1088
CREATE TABLE t1 (
1017
1089
ID int NOT NULL auto_increment,
1018
1090
name char(35) NOT NULL default '',
1020
1092
District char(20) NOT NULL default '',
1021
1093
Population int NOT NULL default '0',
1022
1094
PRIMARY KEY (ID)
1023
) ENGINE=MyISAM;
1095
);
1024
1096
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
1025
1097
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
1026
1098
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
1041
1113
Capital int default NULL,
1042
1114
Code2 char(2) NOT NULL default '',
1043
1115
PRIMARY KEY (Code)
1044
) ENGINE=MyISAM;
1116
);
1045
1117
INSERT INTO t2 VALUES ('AUS','Australia','Oceania','Australia and New Zealand',7741220.00,1901,18886000,79.8,351182.00,392911.00,'Australia','Constitutional Monarchy, Federation','Elisabeth II',135,'AU');
1046
1118
INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ');
1047
1119
select t2.Continent, t1.Name, t1.Population from t2 LEFT JOIN t1 ON t2.Code = t1.t2 where t1.Population IN (select max(t1.Population) AS Population from t1, t2 where t1.t2 = t2.Code group by Continent);
1048
1120
Continent Name Population
1049
1121
Oceania Sydney 3276207
1050
1122
drop table t1, t2;
1051
CREATE TABLE `t1` (
1123
#
1124
# constants in IN
1125
#
1126
CREATE TEMPORARY TABLE `t1` (
1052
1127
`id` bigint NOT NULL auto_increment,
1053
1128
`pseudo` varchar(35) NOT NULL default '',
1054
1129
PRIMARY KEY (`id`),
1075
1150
Warnings:
1076
1151
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)`
1077
1152
drop table t1;
1078
CREATE TABLE `t1` (
1153
CREATE TEMPORARY TABLE `t1` (
1079
1154
`i` int NOT NULL default '0',
1080
1155
PRIMARY KEY (`i`)
1081
1156
) ENGINE=MyISAM;
1088
1163
i
1089
1164
3
1090
1165
drop table t1;
1166
#
1167
# Multi update test
1168
#
1091
1169
CREATE TABLE t1 (
1092
1170
id int default NULL
1093
) ENGINE=MyISAM;
1171
);
1094
1172
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
1095
CREATE TABLE t2 (
1173
CREATE TEMPORARY TABLE t2 (
1096
1174
id int default NULL,
1097
1175
name varchar(15) default NULL
1098
1176
) ENGINE=MyISAM;
1105
1183
2 lenka
1106
1184
1 lenka
1107
1185
drop table t1,t2;
1108
create table t1 (a int, unique index indexa (a)) ENGINE=MyISAM;
1186
#
1187
# correct NULL in <CONSTANT> IN (SELECT ...)
1188
#
1189
create temporary table t1 (a int, unique index indexa (a)) ENGINE=MyISAM;
1109
1190
insert into t1 values (-1), (-4), (-2), (NULL);
1110
1191
select -10 IN (select a from t1 FORCE INDEX (indexa));
1111
1192
-10 IN (select a from t1 FORCE INDEX (indexa))
1112
1193
NULL
1113
1194
drop table t1;
1114
create table t1 (id int not null auto_increment primary key, salary int, key(salary)) ENGINE=MyISAM;
1195
#
1196
# Test optimization for sub selects
1197
#
1198
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
1115
1199
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
1116
1200
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
1117
1201
id select_type table type possible_keys key key_len ref rows filtered Extra
1118
1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where
1119
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
1202
1 PRIMARY t1 ref salary salary 5 const # 100.00 Using where; Using index
1203
2 SUBQUERY NULL NULL NULL NULL NULL NULL # NULL Select tables optimized away
1120
1204
Warnings:
1121
1205
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`))
1122
1206
drop table t1;
1129
1213
UNIQUE KEY t1_PK (ID,SUB_ID),
1130
1214
KEY t1_FK (REF_ID,REF_SUB),
1131
1215
KEY t1_REFID (REF_ID)
1132
) ENGINE=MyISAM;
1216
);
1133
1217
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
1134
1218
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
1135
1219
REF_ID
1136
1220
DROP TABLE t1;
1137
create table t1 (a int, b int) ENGINE=MyISAM;
1138
create table t2 (a int, b int) ENGINE=MyISAM;
1221
#
1222
# uninterruptable update
1223
#
1224
create temporary table t1 (a int, b int) ENGINE=MyISAM;
1225
create temporary table t2 (a int, b int) ENGINE=MyISAM;
1139
1226
insert into t1 values (1,0), (2,0), (3,0);
1140
1227
insert into t2 values (1,1), (2,1), (3,1), (2,2);
1141
1228
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
1147
1234
2 NULL
1148
1235
3 1
1149
1236
drop table t1, t2;
1150
CREATE TABLE `t1` (
1237
#
1238
# reduced subselect in ORDER BY & GROUP BY clauses
1239
#
1240
CREATE TEMPORARY TABLE `t1` (
1151
1241
`id` bigint NOT NULL auto_increment,
1152
1242
`pseudo` varchar(35) NOT NULL default '',
1153
1243
`email` varchar(60) NOT NULL default '',
1164
1254
(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
1165
1255
a
1166
1256
1
1167
create table t1 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
1168
create table t2 (a int not null, primary key (a)) ENGINE=MyISAM;
1169
create table t3 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
1257
#
1258
# IN subselect optimization test
1259
#
1260
create temporary table t1 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
1261
create temporary table t2 (a int not null, primary key (a)) ENGINE=MyISAM;
1262
create temporary table t3 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
1170
1263
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1171
1264
insert into t2 values (2), (3), (4), (5);
1172
1265
insert into t3 values (10,3), (20,4), (30,5);
1203
1296
Warnings:
1204
1297
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
1205
1298
drop table t1, t2, t3;
1206
create table t1 (a int, b int, index a (a,b)) ENGINE=MyISAM;
1207
create table t2 (a int, index a (a)) ENGINE=MyISAM;
1208
create table t3 (a int, b int, index a (a)) ENGINE=MyISAM;
1299
create temporary table t1 (a int, b int, index a (a,b)) ENGINE=MyISAM;
1300
create temporary table t2 (a int, index a (a)) ENGINE=MyISAM;
1301
create temporary table t3 (a int, b int, index a (a)) ENGINE=MyISAM;
1209
1302
insert into t1 values (1,10), (2,20), (3,30), (4,40);
1303
# making table large enough
1210
1304
insert into t2 values (2), (3), (4), (5);
1211
1305
insert into t3 values (10,3), (20,4), (30,5);
1212
1306
select * from t2 where t2.a in (select a from t1);
1258
1352
Warnings:
1259
1353
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
1260
1354
drop table t1, t2, t3;
1261
create table t1 (a int, b int) ENGINE=MyISAM;
1262
create table t2 (a int, b int) ENGINE=MyISAM;
1263
create table t3 (a int, b int) ENGINE=MyISAM;
1355
#
1356
# alloc_group_fields() working
1357
#
1358
create temporary table t1 (a int, b int) ENGINE=MyISAM;
1359
create temporary table t2 (a int, b int) ENGINE=MyISAM;
1360
create temporary table t3 (a int, b int) ENGINE=MyISAM;
1264
1361
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
1265
1362
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
1266
1363
insert into t3 values (3,3), (2,2), (1,1);
1270
1367
2 2
1271
1368
1 2
1272
1369
drop table t1,t2,t3;
1273
create table t1 (s1 int) ENGINE=MyISAM;
1274
create table t2 (s1 int) ENGINE=MyISAM;
1370
#
1371
# aggregate functions in HAVING test
1372
#
1373
create temporary table t1 (s1 int) ENGINE=MyISAM;
1374
create temporary table t2 (s1 int) ENGINE=MyISAM;
1275
1375
insert into t1 values (1);
1276
1376
insert into t2 values (1);
1277
1377
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
1278
1378
s1
1279
1379
1
1280
1380
drop table t1,t2;
1281
create table t1 (s1 int) ENGINE=MyISAM;
1282
create table t2 (s1 int) ENGINE=MyISAM;
1381
#
1382
# update subquery with wrong field (to force name resolving
1383
# in UPDATE name space)
1384
#
1385
create temporary table t1 (s1 int) ENGINE=MyISAM;
1386
create temporary table t2 (s1 int) ENGINE=MyISAM;
1283
1387
insert into t1 values (1);
1284
1388
insert into t2 values (1);
1285
1389
update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);
1286
1390
ERROR 42S22: Unknown column 'x.s1' in 'field list'
1287
1391
DROP TABLE t1, t2;
1288
create table t1(toid int,rd int) ENGINE=MyISAM;
1289
create table t2(userid int,pmnew int,pmtotal int) ENGINE=MyISAM;
1392
#
1393
# collation test
1394
#
1395
#CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
1396
# s2 CHAR(5) COLLATE latin1_swedish_ci) ENGINE=MyISAM;
1397
#INSERT INTO t1 VALUES ('z','?');
1398
#-- error 1267
1399
#select * from t1 where s1 > (select max(s2) from t1);
1400
#-- error 1267
1401
#select * from t1 where s1 > any (select max(s2) from t1);
1402
#drop table t1;
1403
#
1404
# aggregate functions reinitialization
1405
#
1406
create table t1(toid int,rd int);
1407
create table t2(userid int,pmnew int,pmtotal int);
1290
1408
insert into t2 values(1,0,0),(2,0,0);
1291
1409
insert into t1 values(1,0),(1,0),(1,0),(1,12),(1,15),(1,123),(1,12312),(1,12312),(1,123),(2,0),(2,0),(2,1),(2,2);
1292
1410
select userid,pmtotal,pmnew, (select count(rd) from t1 where toid=t2.userid) calc_total, (select count(rd) from t1 where rd=0 and toid=t2.userid) calc_new from t2 where userid in (select distinct toid from t1);
1294
1412
1 0 0 9 3
1295
1413
2 0 0 4 2
1296
1414
drop table t1, t2;
1297
create table t1 (s1 char(5)) ENGINE=MyISAM;
1415
#
1416
# row union
1417
#
1418
create table t1 (s1 char(5));
1298
1419
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
1299
1420
ERROR 21000: Operand should contain 1 column(s)
1300
1421
insert into t1 values ('tttt');
1303
1424
tttt
1304
1425
explain extended (select * from t1);
1305
1426
id select_type table type possible_keys key key_len ref rows filtered Extra
1306
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
1427
1 SIMPLE t1 ALL NULL NULL NULL NULL # 100.00
1307
1428
Warnings:
1308
Note 1003 (select 'tttt' AS `s1` from `test`.`t1`)
1429
Note 1003 (select `test`.`t1`.`s1` AS `s1` from `test`.`t1`)
1309
1430
(select * from t1);
1310
1431
s1
1311
1432
tttt
1312
1433
drop table t1;
1313
create table t1 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
1314
create table t2 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
1434
#
1435
# IN optimisation test results
1436
#
1437
create temporary table t1 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
1438
create temporary table t2 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
1315
1439
insert into t1 values ('a1'),('a2'),('a3');
1316
1440
insert into t2 values ('a1'),('a2');
1317
1441
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1359
1483
Warnings:
1360
1484
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`
1361
1485
drop table t1,t2;
1362
create table t2 (a int, b int) ENGINE=MyISAM;
1363
create table t3 (a int) ENGINE=MyISAM;
1486
#
1487
# correct ALL optimisation
1488
#
1489
create temporary table t2 (a int, b int) ENGINE=MyISAM;
1490
create temporary table t3 (a int) ENGINE=MyISAM;
1364
1491
insert into t3 values (6),(7),(3);
1365
1492
select * from t3 where a >= all (select b from t2);
1366
1493
a
1432
1559
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1433
1560
Warnings:
1434
1561
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1562
#
1563
# optimized static ALL/ANY with grouping
1564
#
1435
1565
insert into t2 values (2,2), (2,1), (3,3), (3,1);
1436
1566
select * from t3 where a > all (select max(b) from t2 group by a);
1437
1567
a
1444
1574
Warnings:
1445
1575
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`)))
1446
1576
drop table t2, t3;
1447
CREATE TABLE `t1` ( `id` bigint NOT NULL auto_increment, `taskid` bigint NOT NULL default '0', `dbid` int NOT NULL default '0', `create_date` datetime, `last_update` datetime, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ;
1577
#
1578
# correct used_tables()
1579
#
1580
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 ;
1448
1581
INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
1449
CREATE TABLE `t2` (`db_id` int NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` int NOT NULL default '0',`secondary_uid` int NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM AUTO_INCREMENT=2147483647;
1582
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;
1450
1583
INSERT INTO `t2` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (18, 'Not Set 1', 0, 0),(19, 'Valid', 1, 2),(20, 'Valid 2', 1, 2),(21, 'Should Not Return', 1, 2),(26, 'Not Set 2', 0, 0),(-1, 'ALL DB\'S', 0, 0);
1451
CREATE TABLE `t3` (`taskgenid` bigint NOT NULL auto_increment,`dbid` int NOT NULL default '0',`taskid` int NOT NULL default '0',`mon` int NOT NULL default '1',`tues` int NOT NULL default '1',`wed` int NOT NULL default '1',`thur` int NOT NULL default '1',`fri` int NOT NULL default '1',`sat` int NOT NULL default '0',`sun` int NOT NULL default '0',`how_often` int NOT NULL default '1',`userid` int NOT NULL default '0',`active` int NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM AUTO_INCREMENT=2 ;
1584
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 ;
1452
1585
INSERT INTO `t3` (`taskgenid`, `dbid`, `taskid`, `mon`, `tues`,`wed`, `thur`, `fri`, `sat`, `sun`, `how_often`, `userid`, `active`) VALUES (1,-1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1);
1453
CREATE TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;
1586
CREATE TEMPORARY TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;
1454
1587
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
1455
1588
select dbid, name, (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01') from t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND t4.task_id = taskid;
1456
1589
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')
1462
1595
-1 Valid
1463
1596
-1 Valid 2
1464
1597
drop table t1,t2,t3,t4;
1465
CREATE TABLE t1 (id int default NULL) ENGINE=MyISAM;
1598
#
1599
# cardinality check
1600
#
1601
CREATE TEMPORARY TABLE t1 (id int default NULL) ENGINE=MyISAM;
1466
1602
INSERT INTO t1 VALUES (1),(5);
1467
CREATE TABLE t2 (id int default NULL) ENGINE=MyISAM;
1603
CREATE TEMPORARY TABLE t2 (id int default NULL) ENGINE=MyISAM;
1468
1604
INSERT INTO t2 VALUES (2),(6);
1469
1605
select * from t1 where (1,2,6) in (select * from t2);
1470
1606
ERROR 21000: Operand should contain 3 column(s)
1471
1607
DROP TABLE t1,t2;
1472
create table t1 (s1 char) ENGINE=MyISAM;
1608
#
1609
# optimized ALL/ANY with union
1610
#
1611
create table t1 (s1 char);
1473
1612
insert into t1 values ('e');
1474
1613
select * from t1 where 'f' > any (select s1 from t1);
1475
1614
s1
1479
1618
e
1480
1619
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
1481
1620
id select_type table type possible_keys key key_len ref rows filtered Extra
1482
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1483
2 SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
1484
3 UNION t1 system NULL NULL NULL NULL 1 100.00
1485
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
1621
1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00
1622
2 SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00
1623
3 UNION t1 ALL NULL NULL NULL NULL # 100.00
1624
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL # NULL
1486
1625
Warnings:
1487
Note 1003 select 'e' AS `s1` from `test`.`t1` where 1
1626
Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where 1
1488
1627
drop table t1;
1489
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;
1628
#
1629
# filesort in subquery (restoring join_tab)
1630
#
1631
CREATE TEMPORARY TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;
1490
1632
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
1491
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;
1633
CREATE TEMPORARY TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;
1492
1634
INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');
1493
1635
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;
1494
1636
phone code
1497
1639
89356874041 NULL
1498
1640
95895001874 NULL
1499
1641
drop table t1, t2;
1500
create table t1 (s1 int) ENGINE=MyISAM;
1501
create table t2 (s1 int) ENGINE=MyISAM;
1642
#
1643
# unresolved field error
1644
#
1645
create temporary table t1 (s1 int) ENGINE=MyISAM;
1646
create temporary table t2 (s1 int) ENGINE=MyISAM;
1502
1647
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
1503
1648
ERROR 42S22: Unknown column 't1.s2' in 'where clause'
1504
1649
select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
1506
1651
select count(*) from t2 group by t1.s2;
1507
1652
ERROR 42S22: Unknown column 't1.s2' in 'group statement'
1508
1653
drop table t1, t2;
1509
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB)) ENGINE=MyISAM;
1510
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA)) ENGINE=MyISAM;
1654
#
1655
# fix_fields() in add_ref_to_table_cond()
1656
#
1657
CREATE TEMPORARY TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB)) ENGINE=MyISAM;
1658
CREATE TEMPORARY TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA)) ENGINE=MyISAM;
1511
1659
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
1512
1660
INSERT INTO t2 VALUES (100, 200, 'C');
1513
1661
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
1514
1662
COLC
1515
1663
DROP TABLE t1, t2;
1516
CREATE TABLE t1 (a int) ENGINE=MyISAM;
1664
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
1517
1665
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
1518
1666
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
1519
1667
(SELECT a)
1523
1671
4
1524
1672
5
1525
1673
DROP TABLE t1;
1526
CREATE TABLE `t1` (
1674
#
1675
# Bug 2479
1676
#
1677
CREATE TEMPORARY TABLE `t1` (
1527
1678
`id` int NOT NULL auto_increment,
1528
1679
`id_cns` int NOT NULL default '0',
1529
1680
`tipo` enum('','UNO','DUE') NOT NULL default '',
1536
1687
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`))
1537
1688
ENGINE=MyISAM;
1538
1689
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);
1539
CREATE TABLE `t2` (
1690
CREATE TEMPORARY TABLE `t2` (
1540
1691
`id` int NOT NULL auto_increment,
1541
1692
`max_anno_dep` int NOT NULL default '0',
1542
1693
PRIMARY KEY (`id`)) ENGINE=MyISAM;
1547
1698
50 1990 0
1548
1699
51 1990 NULL
1549
1700
DROP TABLE t1, t2;
1550
create table t1 (a int) ENGINE=MyISAM;
1701
#
1702
# GLOBAL LIMIT
1703
#
1704
create temporary table t1 (a int) ENGINE=MyISAM;
1551
1705
insert into t1 values (1), (2), (3);
1552
1706
SET SQL_SELECT_LIMIT=1;
1553
1707
select sum(a) from (select * from t1) as a;
1558
1712
1
1559
1713
SET SQL_SELECT_LIMIT=default;
1560
1714
drop table t1;
1561
CREATE TABLE t1 (a int, b int, INDEX (a)) ENGINE=MyISAM;
1715
#
1716
# Bug #3118: subselect + order by
1717
#
1718
CREATE TABLE t1 (a int, b int, INDEX (a));
1562
1719
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
1563
1720
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
1564
1721
a b
1566
1723
1 2
1567
1724
1 3
1568
1725
DROP TABLE t1;
1569
create table t1(val varchar(10)) ENGINE=MyISAM;
1726
# Item_cond fix field
1727
#
1728
create table t1(val varchar(10));
1570
1729
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
1571
1730
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%');
1572
1731
count(*)
1573
1732
0
1574
1733
drop table t1;
1575
create table t1 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
1734
#
1735
# ref_or_null replacing with ref
1736
#
1737
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
1576
1738
insert into t1 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12');
1577
1739
select * from t1 where id not in (select id from t1 where id < 8);
1578
1740
id text
1590
1752
12 text12
1591
1753
explain extended select * from t1 where id not in (select id from t1 where id < 8);
1592
1754
id select_type table type possible_keys key key_len ref rows filtered Extra
1593
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where
1594
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where
1755
1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00 Using where
1756
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func # 100.00 Using index; Using where
1595
1757
Warnings:
1596
1758
Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
1597
1759
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
1598
1760
id select_type table type possible_keys key key_len ref rows filtered Extra
1599
1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
1600
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index
1761
1 PRIMARY tt ALL NULL NULL NULL NULL # 100.00 Using where
1762
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id # 100.00 Using where; Using index
1601
1763
Warnings:
1602
1764
Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
1603
1765
Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))
1604
1766
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
1605
create table t2 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
1767
create temporary table t2 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
1606
1768
insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');
1607
1769
select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
1608
1770
id text id text id text
1622
1784
1001 text1001 NULL NULL 1000 text1000
1623
1785
explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
1624
1786
id select_type table type possible_keys key key_len ref rows filtered Extra
1625
1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00
1626
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id 2 100.00
1627
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where
1787
1 SIMPLE a ALL NULL NULL NULL NULL # 100.00
1788
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id # 100.00
1789
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func # 100.00 Using where
1628
1790
Warnings:
1629
1791
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`)
1630
1792
drop table t1,t2;
1631
create table t1 (a int) ENGINE=MyISAM;
1793
#
1794
# Static tables & rund() in subqueries
1795
#
1796
create temporary table t1 (a int) ENGINE=MyISAM;
1632
1797
insert into t1 values (1);
1633
1798
explain select benchmark(1000, (select a from t1 where a=rand()));
1634
1799
id select_type table type possible_keys key key_len ref rows Extra
1635
1800
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
1636
1801
2 SUBQUERY t1 system NULL NULL NULL NULL 1
1637
1802
drop table t1;
1638
create table t1(id int) ENGINE=MyISAM;
1639
create table t2(id int) ENGINE=MyISAM;
1640
create table t3(flag int) ENGINE=MyISAM;
1803
#
1804
# bug 3188
1805
#
1806
create temporary table t1(id int) ENGINE=MyISAM;
1807
create temporary table t2(id int) ENGINE=MyISAM;
1808
create temporary table t3(flag int) ENGINE=MyISAM;
1641
1809
select (select * from t3 where id not null) from t1, t2;
1642
1810
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'null) from t1, t2' at line 1
1643
1811
drop table t1,t2,t3;
1644
CREATE TABLE t1 (id INT) ENGINE=MyISAM;
1645
CREATE TABLE t2 (id INT) ENGINE=MyISAM;
1812
#
1813
# aggregate functions (Bug #3505)
1814
#
1815
CREATE TABLE t1 (id INT);
1816
CREATE TABLE t2 (id INT);
1646
1817
INSERT INTO t1 VALUES (1), (2);
1647
1818
INSERT INTO t2 VALUES (1);
1648
1819
SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
1662
1833
1 1
1663
1834
2 0
1664
1835
DROP TABLE t1,t2;
1665
CREATE TABLE t1 ( a int, b int ) ENGINE=MyISAM;
1836
#
1837
# ALL/ANY test
1838
#
1839
CREATE TABLE t1 ( a int, b int );
1666
1840
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1667
1841
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1668
1842
a
1706
1880
a
1707
1881
1
1708
1882
3
1883
# with index
1709
1884
ALTER TABLE t1 ADD INDEX (a);
1710
1885
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1711
1886
a
1749
1924
a
1750
1925
1
1751
1926
3
1927
# having clause test
1752
1928
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
1753
1929
a
1754
1930
3
1791
1967
a
1792
1968
1
1793
1969
3
1970
# union test
1794
1971
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
1795
1972
a
1796
1973
3
1833
2010
a
1834
2011
1
1835
2012
3
2013
# union + having test
1836
2014
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
1837
2015
a
1838
2016
3
1875
2053
a
1876
2054
1
1877
2055
3
2056
# row tests
2057
# < > >= <= and = ALL/ <> ANY do not support row operation
1878
2058
SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
1879
2059
ERROR 21000: Operand should contain 1 column(s)
1880
2060
SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
1891
2071
ERROR 21000: Operand should contain 1 column(s)
1892
2072
SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
1893
2073
ERROR 21000: Operand should contain 1 column(s)
2074
# following should be converted to IN
1894
2075
SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
1895
2076
ERROR 21000: Operand should contain 2 column(s)
1896
2077
SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
1934
2115
a
1935
2116
1
1936
2117
3
2118
# without optimisation
1937
2119
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
1938
2120
a
1939
2121
3
1976
2158
a
1977
2159
1
1978
2160
3
2161
# without optimisation + having
1979
2162
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
1980
2163
a
1981
2164
3
2018
2201
a
2019
2202
1
2020
2203
3
2204
# EXISTS in string contence
2021
2205
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
2022
2206
concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-')
2023
2207
0-
2034
2218
1-
2035
2219
0-
2036
2220
DROP TABLE t1;
2037
CREATE TABLE t1 ( a double, b double ) ENGINE=MyISAM;
2221
CREATE TABLE t1 ( a double, b double );
2038
2222
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
2039
2223
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
2040
2224
a
2079
2263
1
2080
2264
3
2081
2265
DROP TABLE t1;
2082
CREATE TABLE t1 ( a char(1), b char(1)) ENGINE=MyISAM;
2266
CREATE TABLE t1 ( a char(1), b char(1));
2083
2267
INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
2084
2268
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
2085
2269
a
2124
2308
1
2125
2309
3
2126
2310
DROP TABLE t1;
2127
create table t1 (a int, b int) ENGINE=MyISAM;
2311
#
2312
# SELECT(EXISTS * ...)optimisation
2313
#
2314
create table t1 (a int, b int);
2128
2315
insert into t1 values (1,2),(3,4);
2129
2316
select * from t1 up where exists (select * from t1 where t1.a=up.a);
2130
2317
a b
2132
2319
3 4
2133
2320
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
2134
2321
id select_type table type possible_keys key key_len ref rows filtered Extra
2135
1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 Using where
2136
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2322
1 PRIMARY up ALL NULL NULL NULL NULL # 100.00 Using where
2323
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 100.00 Using where
2137
2324
Warnings:
2138
2325
Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
2139
2326
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`))
2140
2327
drop table t1;
2141
CREATE TABLE t1 (t1_a int) ENGINE=MyISAM;
2328
#
2329
# Bug #4102: subselect in HAVING
2330
#
2331
CREATE TEMPORARY TABLE t1 (t1_a int) ENGINE=MyISAM;
2142
2332
INSERT INTO t1 VALUES (1);
2143
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b)) ENGINE=MyISAM;
2333
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
2144
2334
INSERT INTO t2 VALUES (1, 1), (1, 2);
2145
2335
SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1
2146
2336
HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
2147
2337
t1_a t2_a t2_b
2148
2338
1 1 2
2149
2339
DROP TABLE t1, t2;
2150
CREATE TABLE t1 (id int default NULL,name varchar(10) default NULL) ENGINE=MyISAM;
2340
#
2341
# Test problem with NULL and derived tables (Bug #4097)
2342
#
2343
CREATE TEMPORARY TABLE t1 (id int default NULL,name varchar(10) default NULL) ENGINE=MyISAM;
2151
2344
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
2152
CREATE TABLE t2 (id int default NULL, pet varchar(10) default NULL) ENGINE=MyISAM;
2345
CREATE TEMPORARY TABLE t2 (id int default NULL, pet varchar(10) default NULL) ENGINE=MyISAM;
2153
2346
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
2154
2347
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
2155
2348
id name id pet
2157
2350
2 Rebecca 2 Spot
2158
2351
3 NULL 3 Felix
2159
2352
drop table t1,t2;
2160
CREATE TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;
2353
#
2354
# Aggregate function comparation with ALL/ANY/SOME subselect
2355
#
2356
CREATE TEMPORARY TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;
2161
2357
insert into t1 values (1);
2162
CREATE TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
2358
CREATE TEMPORARY TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
2163
2359
insert into t2 values (1,2);
2164
2360
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
2165
2361
a C
2166
2362
1 1
2167
2363
drop table t1,t2;
2168
CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) COLLATE utf8_bin,`KUERZEL` VARCHAR(10) COLLATE utf8_bin,`IZAANALYSEART_ID` VARCHAR(11) COLLATE utf8_bin,`IZAPMKZ_ID` VARCHAR(11) COLLATE utf8_bin) ENGINE=MyISAM;
2364
#
2365
# BUG#5003 - like in subselect
2366
#
2367
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);
2169
2368
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
2170
2369
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
2171
2370
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
2175
2374
IZAVORGANG_ID
2176
2375
D0000000001
2177
2376
drop table t1;
2178
CREATE TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)) ENGINE=MyISAM;
2179
CREATE TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)) ENGINE=MyISAM;
2377
#
2378
# Optimized IN with compound index
2379
#
2380
CREATE TEMPORARY TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)) ENGINE=MyISAM;
2381
CREATE TEMPORARY TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)) ENGINE=MyISAM;
2180
2382
insert into t1 values (1,1),(1,2),(2,1),(2,2);
2181
2383
insert into t2 values (1,2),(2,2);
2182
2384
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
2196
2398
1 1
2197
2399
2 1
2198
2400
drop table t1,t2;
2199
CREATE TABLE t1 (howmanyvalues bigint, avalue int) ENGINE=MyISAM;
2401
#
2402
# resolving fields of grouped outer SELECT
2403
#
2404
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
2200
2405
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
2201
2406
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
2202
2407
howmanyvalues count(*)
2230
2435
3 1
2231
2436
4 1
2232
2437
drop table t1;
2233
create table t1 (x int) ENGINE=MyISAM;
2438
create table t1 (x int);
2234
2439
select (select b.x from t1 as b where b.x=a.x) from t1 as a where a.x=2 group by a.x;
2235
2440
(select b.x from t1 as b where b.x=a.x)
2236
2441
drop table t1;
2237
CREATE TABLE `t1` ( `master` int NOT NULL default '0', `map` int NOT NULL default '0', `slave` int NOT NULL default '0', `access` int NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`)) ENGINE=MyISAM;
2442
#
2443
# Test of correct maybe_null flag returning by subquwery for temporary table
2444
# creation
2445
#
2446
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;
2238
2447
INSERT INTO `t1` VALUES (1,0,0,700),(1,1,1,400),(1,5,5,400),(1,12,12,400),(1,12,32,400),(4,12,32,400);
2239
CREATE TABLE `t2` ( `id` int NOT NULL default '0', `pid` int NOT NULL default '0', `map` int NOT NULL default '0', `level` int NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ENGINE=MyISAM ;
2448
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 ;
2240
2449
INSERT INTO `t2` VALUES (6,5,12,7,'a'),(12,0,0,7,'a'),(12,1,0,7,'a'),(12,5,5,7,'a'),(12,5,12,7,'a');
2241
2450
SELECT b.sc FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;
2242
2451
ERROR 42S22: Unknown column 'b.sc' in 'field list'
2245
2454
700
2246
2455
NULL
2247
2456
drop tables t1,t2;
2457
#
2458
# Subselect in non-select command just after connection
2459
#
2248
2460
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
2249
create table t1 (a int, b int) ENGINE=MyISAM;
2250
create table t2 (a int, b int) ENGINE=MyISAM;
2461
#
2462
# primary query with temporary table and subquery with groupping
2463
#
2464
create temporary table t1 (a int, b int) ENGINE=MyISAM;
2465
create temporary table t2 (a int, b int) ENGINE=MyISAM;
2251
2466
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
2252
2467
insert into t2 values (1,3),(2,1);
2253
2468
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
2258
2473
2 4 NULL
2259
2474
2 5 NULL
2260
2475
drop table t1, t2;
2261
create table t1 (s1 int,s2 int) ENGINE=MyISAM;
2476
#
2477
# Equal operation under row and empty subquery
2478
#
2479
create table t1 (s1 int,s2 int);
2262
2480
insert into t1 values (20,15);
2263
2481
select * from t1 where (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
2264
2482
s1 s2
2265
2483
drop table t1;
2266
create table t1 (s1 int) ENGINE=MyISAM;
2484
#
2485
# ALL/ANY with NULL
2486
#
2487
create table t1 (s1 int);
2267
2488
insert into t1 values (1),(null);
2268
2489
select * from t1 where s1 < all (select s1 from t1);
2269
2490
s1
2272
2493
1 0
2273
2494
NULL NULL
2274
2495
drop table t1;
2496
#
2497
# reference on changable fields from subquery
2498
#
2275
2499
CREATE TABLE t1 (
2276
2500
Code char(3) NOT NULL default '',
2277
2501
Name char(52) NOT NULL default '',
2288
2512
HeadOfState char(60) default NULL,
2289
2513
Capital int default NULL,
2290
2514
Code2 char(2) NOT NULL default ''
2291
) ENGINE=MyISAM;
2515
);
2292
2516
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
2293
2517
INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,68000,75.1,334.00,NULL,'Amerika Samoa','US Territory','George W. Bush',54,'AS');
2294
2518
INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');
2295
2519
INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM');
2296
2520
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
2521
Warnings:
2522
Note 1031 Table storage engine for 't1' doesn't have this option
2297
2523
SELECT DISTINCT Continent AS c FROM t1 outr WHERE
2298
2524
Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
2299
2525
Population < 200);
2300
2526
c
2301
2527
Oceania
2302
2528
drop table t1;
2303
create table t1 (a1 int) ENGINE=MyISAM;
2304
create table t2 (b1 int) ENGINE=MyISAM;
2529
#
2530
# Test for BUG#7885: Server crash when 'any' subselect compared to
2531
# non-existant field.
2532
#
2533
create temporary table t1 (a1 int) ENGINE=MyISAM;
2534
create temporary table t2 (b1 int) ENGINE=MyISAM;
2305
2535
select * from t1 where a2 > any(select b1 from t2);
2306
2536
ERROR 42S22: Unknown column 'a2' in 'IN/ALL/ANY subquery'
2307
2537
select * from t1 where a1 > any(select b1 from t2);
2308
2538
a1
2309
2539
drop table t1,t2;
2310
create table t1 (a integer, b integer) ENGINE=MyISAM;
2540
#
2541
# Comparison subquery with * and row
2542
#
2543
create temporary table t1 (a integer, b integer) ENGINE=MyISAM;
2311
2544
select (select * from t1) = (select 1,2);
2312
2545
(select * from t1) = (select 1,2)
2313
2546
NULL
2314
2547
select (select 1,2) = (select * from t1);
2315
2548
(select 1,2) = (select * from t1)
2316
2549
NULL
2550
# queries whih can be converted to IN
2317
2551
select row(1,2) = ANY (select * from t1);
2318
2552
row(1,2) = ANY (select * from t1)
2319
2553
0
2321
2555
row(1,2) != ALL (select * from t1)
2322
2556
1
2323
2557
drop table t1;
2324
create table t1 (a integer, b integer) ENGINE=MyISAM;
2558
#
2559
# Comparison subquery and row with nested rows
2560
#
2561
create temporary table t1 (a integer, b integer) ENGINE=MyISAM;
2325
2562
select row(1,(2,2)) in (select * from t1 );
2326
2563
ERROR 21000: Operand should contain 2 column(s)
2327
2564
select row(1,(2,2)) = (select * from t1 );
2329
2566
select (select * from t1) = row(1,(2,2));
2330
2567
ERROR 21000: Operand should contain 1 column(s)
2331
2568
drop table t1;
2569
#
2570
# Forward reference detection
2571
#
2332
2572
create table t1 (a integer);
2333
2573
insert into t1 values (1);
2334
2574
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ;
2341
2581
select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
2342
2582
ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
2343
2583
drop table t1;
2344
CREATE TABLE t1 (
2584
#
2585
# Test for BUG#8218
2586
#
2587
CREATE TEMPORARY TABLE t1 (
2345
2588
categoryId int NOT NULL,
2346
2589
courseId int NOT NULL,
2347
2590
startDate datetime NOT NULL,
2359
2602
(2,89,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
2360
2603
(3,51,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
2361
2604
(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');
2362
CREATE TABLE t2 (
2605
CREATE TEMPORARY TABLE t2 (
2363
2606
userId int NOT NULL,
2364
2607
courseId int NOT NULL,
2365
2608
date datetime NOT NULL)
2370
2613
(5141,12,'2004-08-06'),(5141,86,'2004-10-21'),
2371
2614
(5141,87,'2004-10-21'),(5141,88,'2004-10-21'),
2372
2615
(5141,89,'2004-10-22'),(5141,51,'2004-10-26');
2373
CREATE TABLE t3 (
2616
CREATE TEMPORARY TABLE t3 (
2374
2617
groupId int NOT NULL,
2375
2618
parentId int NOT NULL,
2376
2619
startDate datetime NOT NULL,
2380
2623
ordering int)
2381
2624
engine=myisam;
2382
2625
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
2383
CREATE TABLE t4 (
2626
CREATE TEMPORARY TABLE t4 (
2384
2627
id int NOT NULL,
2385
2628
groupTypeId int NOT NULL,
2386
2629
groupKey varchar(50) NOT NULL,
2392
2635
engine=myisam;
2393
2636
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
2394
2637
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
2395
CREATE TABLE t5 (
2638
CREATE TEMPORARY TABLE t5 (
2396
2639
userId int NOT NULL,
2397
2640
groupId int NOT NULL,
2398
2641
createDate datetime NOT NULL,
2440
2683
1 5141 12 group2 12 group2 5 1 2 88 Oct04
2441
2684
1 5141 12 group2 12 group2 5 1 2 89 Oct04
2442
2685
drop table t1, t2, t3, t4, t5;
2443
create table t1 (a int) ENGINE=MyISAM;
2686
#
2687
# Transformation in left expression of subquery (BUG#8888)
2688
#
2689
create temporary table t1 (a int) ENGINE=MyISAM;
2444
2690
insert into t1 values (1), (2), (3);
2445
2691
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
2446
2692
1
2448
2694
1
2449
2695
1
2450
2696
drop table t1;
2451
create table t1 (a int, b int) ENGINE=MyISAM;
2697
#
2698
# single row subqueries and row operations (code covarage improvement)
2699
#
2700
create temporary table t1 (a int, b int) ENGINE=MyISAM;
2452
2701
insert into t1 values (1,2);
2453
2702
select 1 = (select * from t1);
2454
2703
ERROR 21000: Operand should contain 1 column(s)
2463
2712
select (select * from t1) = (1,2,3);
2464
2713
ERROR 21000: Operand should contain 2 column(s)
2465
2714
drop table t1;
2466
create table t1 (fld enum('0','1')) ENGINE=MyISAM;
2715
#
2716
# Item_int_with_ref check (BUG#10020)
2717
#
2718
#CREATE TABLE `t1` (
2719
# `itemid` bigint NOT NULL auto_increment,
2720
# `sessionid` bigint default NULL,
2721
# `time` int NOT NULL default '0',
2722
# `data` text collate latin1_general_ci NOT NULL,
2723
# PRIMARY KEY (`itemid`)
2724
#);
2725
#INSERT INTO `t1` VALUES (1, 1, 1, '');
2726
#CREATE TABLE `t2` (
2727
# `sessionid` bigint NOT NULL auto_increment,
2728
# `pid` int NOT NULL default '0',
2729
# `date` int NOT NULL default '0',
2730
# `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
2731
# PRIMARY KEY (`sessionid`)
2732
#);
2733
#INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
2734
#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;
2735
#drop tables t1,t2;
2736
# BUG#11821 : Select from subselect using aggregate function on an enum
2737
# segfaults:
2738
create temporary table t1 (fld enum('0','1')) ENGINE=MyISAM;
2467
2739
insert into t1 values ('1');
2468
2740
select * from (select max(fld) from t1) as foo;
2469
2741
max(fld)
2470
2742
1
2471
2743
drop table t1;
2472
CREATE TABLE t1 (one int, two int, flag char(1)) ENGINE=MyISAM;
2473
CREATE TABLE t2 (one int, two int, flag char(1)) ENGINE=MyISAM;
2744
#
2745
# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
2746
#
2747
CREATE TABLE t1 (one int, two int, flag char(1));
2748
CREATE TABLE t2 (one int, two int, flag char(1));
2474
2749
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2475
2750
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
2476
2751
SELECT * FROM t1
2553
2828
Warnings:
2554
2829
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`
2555
2830
DROP TABLE t1,t2;
2556
CREATE TABLE t1 (a char(5), b char(5)) ENGINE=MyISAM;
2831
#
2832
# Bug #12392: where cond with IN predicate for rows and NULL values in table
2833
#
2834
CREATE TEMPORARY TABLE t1 (a char(5), b char(5)) ENGINE=MyISAM;
2557
2835
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
2558
2836
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
2559
2837
a b
2560
2838
aaa aaa
2561
2839
DROP TABLE t1;
2562
CREATE TABLE t1 (a int) ENGINE=MyISAM;
2563
CREATE TABLE t2 (a int, b int) ENGINE=MyISAM;
2564
CREATE TABLE t3 (b int NOT NULL) ENGINE=MyISAM;
2840
#
2841
# Bug #11479: subquery over left join with an empty inner table
2842
#
2843
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
2844
CREATE TEMPORARY TABLE t2 (a int, b int) ENGINE=MyISAM;
2845
CREATE TEMPORARY TABLE t3 (b int NOT NULL) ENGINE=MyISAM;
2565
2846
INSERT INTO t1 VALUES (1), (2), (3), (4);
2566
2847
INSERT INTO t2 VALUES (1,10), (3,30);
2567
2848
SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b
2576
2857
3
2577
2858
4
2578
2859
DROP TABLE t1,t2,t3;
2579
CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
2580
CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;
2860
#
2861
# Bug#18503: Queries with a quantified subquery returning empty set may
2862
# return a wrong result.
2863
#
2864
CREATE TEMPORARY TABLE t1 (f1 INT) ENGINE=MyISAM;
2865
CREATE TEMPORARY TABLE t2 (f2 INT) ENGINE=MyISAM;
2581
2866
INSERT INTO t1 VALUES (1);
2582
2867
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
2583
2868
f1
2591
2876
f1
2592
2877
1
2593
2878
DROP TABLE t1, t2;
2594
create table t1 (s1 char) ENGINE=MyISAM;
2879
# BUG#20975 Wrong query results for subqueries within NOT
2880
create table t1 (s1 char);
2595
2881
insert into t1 values (1),(2);
2596
2882
select * from t1 where (s1 < any (select s1 from t1));
2597
2883
s1
2618
2904
s1
2619
2905
2
2620
2906
drop table t1;
2907
#
2908
# Bug #16255: Subquery in where
2909
#
2621
2910
create table t1 (
2622
2911
retailerID varchar(8) NOT NULL,
2623
2912
statusID int NOT NULL,
2624
2913
changed datetime NOT NULL,
2625
UNIQUE KEY retailerID (retailerID, statusID, changed))
2626
ENGINE=MyISAM;
2914
UNIQUE KEY retailerID (retailerID, statusID, changed));
2627
2915
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
2628
2916
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
2629
2917
INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
2640
2928
0048 1 2006-01-06 12:37:50
2641
2929
0059 1 2006-01-06 12:37:50
2642
2930
drop table t1;
2643
create table t1(a int, primary key (a)) ENGINE=MyISAM;
2931
#
2932
# Bug #21180: Subselect with index for both WHERE and ORDER BY
2933
# produces empty result
2934
#
2935
create table t1(a int, primary key (a));
2644
2936
insert into t1 values (10);
2645
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b)) ENGINE=MyISAM;
2937
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
2646
2938
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
2647
2939
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2648
2940
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2649
2941
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2650
2942
id select_type table type possible_keys key key_len ref rows Extra
2651
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
2652
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2653
2 DEPENDENT SUBQUERY t2 range b b 136 NULL 2 Using where
2943
1 PRIMARY t1 const PRIMARY PRIMARY 4 const # Using index
2944
1 PRIMARY r const PRIMARY PRIMARY 4 const #
2945
2 DEPENDENT SUBQUERY t2 ref b b 5 # Using where; Using index
2654
2946
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2655
2947
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2656
2948
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2660
2952
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2661
2953
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2662
2954
id select_type table type possible_keys key key_len ref rows Extra
2663
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
2955
1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index
2664
2956
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2665
2 DEPENDENT SUBQUERY t2 range b b 136 NULL 2 Using where; Using MRR
2957
2 DEPENDENT SUBQUERY t2 ref b b 5 1 Using where; Using index
2666
2958
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2667
2959
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2668
2960
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2669
2961
a a b
2670
2962
10 1 359
2671
2963
drop table t1,t2;
2672
CREATE TABLE t1 (
2964
#
2965
# Bug #21853: assert failure for a grouping query with
2966
# an ALL/ANY quantified subquery in HAVING
2967
#
2968
CREATE TEMPORARY TABLE t1 (
2673
2969
field1 int NOT NULL,
2674
2970
field2 int NOT NULL,
2675
2971
field3 int NOT NULL,
2676
2972
PRIMARY KEY (field1,field2,field3))
2677
2973
ENGINE=MyISAM;
2678
CREATE TABLE t2 (
2974
CREATE TEMPORARY TABLE t2 (
2679
2975
fieldA int NOT NULL,
2680
2976
fieldB int NOT NULL,
2681
2977
PRIMARY KEY (fieldA,fieldB))
2705
3001
1 1
2706
3002
1 3
2707
3003
DROP TABLE t1, t2;
2708
CREATE TABLE t1(a int, INDEX (a)) ENGINE=MyISAM;
3004
#
3005
# Bug #23478: not top-level IN subquery returning a non-empty result set
3006
# with possible NULL values by index access from the outer query
3007
#
3008
CREATE TEMPORARY TABLE t1(a int, INDEX (a)) ENGINE=MyISAM;
2709
3009
INSERT INTO t1 VALUES (1), (3), (5), (7);
2710
3010
INSERT INTO t1 VALUES (NULL);
2711
CREATE TABLE t2(a int) ENGINE=MyISAM;
3011
CREATE TEMPORARY TABLE t2(a int) ENGINE=MyISAM;
2712
3012
INSERT INTO t2 VALUES (1),(2),(3);
2713
3013
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
2714
3014
id select_type table type possible_keys key key_len ref rows Extra
2720
3020
2 NULL
2721
3021
3 1
2722
3022
DROP TABLE t1,t2;
2723
CREATE TABLE t1 (a DATETIME) ENGINE=MyISAM;
3023
#
3024
# Bug #11302: getObject() returns a String for a sub-query of type datetime
3025
#
3026
CREATE TABLE t1 (a DATETIME);
2724
3027
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
2725
CREATE TABLE t2 ENGINE=MyISAM AS SELECT
3028
CREATE TEMPORARY TABLE t2 ENGINE=MyISAM AS SELECT
2726
3029
(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
2727
3030
FROM t1 WHERE a > '2000-01-01';
2728
3031
SHOW CREATE TABLE t2;
2729
3032
Table Create Table
2730
t2 CREATE TABLE `t2` (
3033
t2 CREATE TEMPORARY TABLE `t2` (
2731
3034
`sub_a` datetime DEFAULT NULL
2732
3035
) ENGINE=MyISAM
2733
CREATE TABLE t3 ENGINE=MyISAM AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
3036
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');
2734
3037
SHOW CREATE TABLE t3;
2735
3038
Table Create Table
2736
t3 CREATE TABLE `t3` (
3039
t3 CREATE TEMPORARY TABLE `t3` (
2737
3040
`a` datetime DEFAULT NULL
2738
3041
) ENGINE=MyISAM
2739
3042
DROP TABLE t1,t2,t3;
2740
CREATE TABLE t1 (a int) ENGINE=MyISAM;
3043
#
3044
# Bug 24653: sorting by expressions containing subselects
3045
# that return more than one row
3046
#
3047
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
2741
3048
INSERT INTO t1 VALUES (2), (4), (1), (3);
2742
CREATE TABLE t2 (b int, c int) ENGINE=MyISAM;
3049
CREATE TABLE t2 (b int, c int);
2743
3050
INSERT INTO t2 VALUES
2744
3051
(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
2745
3052
SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
2811
3118
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
2812
3119
ERROR 21000: Subquery returns more than 1 row
2813
3120
DROP TABLE t1,t2;
2814
create table t1 (df decimal(5,1)) ENGINE=MyISAM;
3121
# End of 4.1 tests
3122
#
3123
#decimal-related tests
3124
#
3125
create table t1 (df decimal(5,1));
2815
3126
insert into t1 values(1.1);
2816
3127
insert into t1 values(2.2);
2817
3128
select * from t1 where df <= all (select avg(df) from t1 group by df);
2821
3132
df
2822
3133
2.2
2823
3134
drop table t1;
2824
create table t1 (df decimal(5,1)) ENGINE=MyISAM;
3135
create temporary table t1 (df decimal(5,1)) ENGINE=MyISAM;
2825
3136
insert into t1 values(1.1);
2826
3137
select 1.1 * exists(select * from t1);
2827
3138
1.1 * exists(select * from t1)
2828
3139
1.1
2829
3140
drop table t1;
2830
CREATE TABLE t1 (
3141
CREATE TEMPORARY TABLE t1 (
2831
3142
grp int default NULL,
2832
3143
a decimal(10,2) default NULL) ENGINE=MyISAM;
2833
3144
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
2847
3158
2.00
2848
3159
4.00
2849
3160
drop table t1;
2850
CREATE table t1 ( c1 integer ) ENGINE=MyISAM;
3161
#
3162
# Test for bug #9338: lame substitution of c1 instead of c2
3163
#
3164
CREATE temporary table t1 ( c1 integer ) ENGINE=MyISAM;
2851
3165
INSERT INTO t1 VALUES ( 1 );
2852
3166
INSERT INTO t1 VALUES ( 2 );
2853
3167
INSERT INTO t1 VALUES ( 3 );
2854
CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;
3168
CREATE TABLE t2 ( c2 integer );
2855
3169
INSERT INTO t2 VALUES ( 1 );
2856
3170
INSERT INTO t2 VALUES ( 4 );
2857
3171
INSERT INTO t2 VALUES ( 5 );
2863
3177
c1 c2
2864
3178
1 1
2865
3179
DROP TABLE t1,t2;
2866
CREATE TABLE t1 ( c1 integer ) ENGINE=MyISAM;
3180
#
3181
# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ
3182
#
3183
CREATE TEMPORARY TABLE t1 ( c1 integer ) ENGINE=MyISAM;
2867
3184
INSERT INTO t1 VALUES ( 1 );
2868
3185
INSERT INTO t1 VALUES ( 2 );
2869
3186
INSERT INTO t1 VALUES ( 3 );
2870
3187
INSERT INTO t1 VALUES ( 6 );
2871
CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;
3188
CREATE TEMPORARY TABLE t2 ( c2 integer ) ENGINE=MyISAM;
2872
3189
INSERT INTO t2 VALUES ( 1 );
2873
3190
INSERT INTO t2 VALUES ( 4 );
2874
3191
INSERT INTO t2 VALUES ( 5 );
2875
3192
INSERT INTO t2 VALUES ( 6 );
2876
CREATE TABLE t3 ( c3 integer ) ENGINE=MyISAM;
3193
CREATE TEMPORARY TABLE t3 ( c3 integer ) ENGINE=MyISAM;
2877
3194
INSERT INTO t3 VALUES ( 7 );
2878
3195
INSERT INTO t3 VALUES ( 8 );
2879
3196
SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
2882
3199
2 NULL
2883
3200
3 NULL
2884
3201
DROP TABLE t1,t2,t3;
2885
CREATE TABLE t1 (EMPNUM CHAR(3)) ENGINE=MyISAM;
2886
CREATE TABLE t2 (EMPNUM CHAR(3) ) ENGINE=MyISAM;
3202
#
3203
# Correct building of equal fields list (do not include outer
3204
# fields) (BUG#6384)
3205
#
3206
CREATE TEMPORARY TABLE t1 (EMPNUM CHAR(3)) ENGINE=MyISAM;
3207
CREATE TEMPORARY TABLE t2 (EMPNUM CHAR(3) ) ENGINE=MyISAM;
2887
3208
INSERT INTO t1 VALUES ('E1'),('E2');
2888
3209
INSERT INTO t2 VALUES ('E1');
2889
3210
DELETE FROM t1
2895
3216
EMPNUM
2896
3217
E1
2897
3218
DROP TABLE t1,t2;
2898
CREATE TABLE t1(select_id BIGINT, values_id BIGINT) ENGINE=MyISAM;
3219
#
3220
# Test for bug #11487: range access in a subquery
3221
#
3222
CREATE TEMPORARY TABLE t1(select_id BIGINT, values_id BIGINT) ENGINE=MyISAM;
2899
3223
INSERT INTO t1 VALUES (1, 1);
2900
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,
3224
CREATE TEMPORARY TABLE t2 (select_id BIGINT, values_id BIGINT,
2901
3225
PRIMARY KEY(select_id,values_id)) ENGINE=MyISAM;
2902
3226
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
2903
3227
SELECT values_id FROM t1
2916
3240
values_id
2917
3241
1
2918
3242
DROP TABLE t1, t2;
2919
create table t1 (fld enum('0','1')) ENGINE=MyISAM;
3243
# BUG#11821 : Select from subselect using aggregate function on an enum
3244
# segfaults:
3245
create temporary table t1 (fld enum('0','1')) ENGINE=MyISAM;
2920
3246
insert into t1 values ('1');
2921
3247
select * from (select max(fld) from t1) as foo;
2922
3248
max(fld)
2923
3249
1
2924
3250
drop table t1;
2925
CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
2926
CREATE TABLE t2 (c int, d int) ENGINE=MyISAM;
2927
CREATE TABLE t3 (e int) ENGINE=MyISAM;
3251
#
3252
# Test for bug #11762: subquery with an aggregate function in HAVING
3253
#
3254
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
3255
CREATE TEMPORARY TABLE t2 (c int, d int) ENGINE=MyISAM;
3256
CREATE TEMPORARY TABLE t3 (e int) ENGINE=MyISAM;
2928
3257
INSERT INTO t1 VALUES
2929
3258
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
2930
3259
INSERT INTO t2 VALUES
3065
3394
3 20
3066
3395
4 40
3067
3396
DROP TABLE t1,t2,t3;
3068
CREATE TABLE t1 (a varchar(5), b varchar(10)) ENGINE=MyISAM;
3397
#
3398
# Test for bug #16603: GROUP BY in a row subquery with a quantifier
3399
# when an index is defined on the grouping field
3400
CREATE TABLE t1 (a varchar(5), b varchar(10));
3069
3401
INSERT INTO t1 VALUES
3070
3402
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
3071
3403
('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
3077
3409
EXPLAIN
3078
3410
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3079
3411
id select_type table type possible_keys key key_len ref rows Extra
3080
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
3081
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
3412
1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where
3413
2 SUBQUERY t1 ALL NULL NULL NULL NULL # Using temporary; Using filesort
3082
3414
ALTER TABLE t1 ADD INDEX(a);
3083
3415
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3084
3416
a b
3088
3420
EXPLAIN
3089
3421
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3090
3422
id select_type table type possible_keys key key_len ref rows Extra
3091
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
3092
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
3423
1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where
3424
2 SUBQUERY t1 index NULL a 23 NULL #
3093
3425
DROP TABLE t1;
3094
create table t1( f1 int,f2 int) ENGINE=MyISAM;
3426
#
3427
# Bug#17366: Unchecked Item_int results in server crash
3428
#
3429
create table t1( f1 int,f2 int);
3095
3430
insert into t1 values (1,1),(2,2);
3096
3431
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';
3097
3432
t
3098
3433
crash1
3099
3434
crash1
3100
3435
drop table t1;
3101
create table t1 (c int, key(c)) ENGINE=MyISAM;
3436
#
3437
# Bug #18306: server crash on delete using subquery.
3438
#
3439
create temporary table t1 (c int, key(c)) ENGINE=MyISAM;
3102
3440
insert into t1 values (1142477582), (1142455969);
3103
create table t2 (a int, b int) ENGINE=MyISAM;
3441
create temporary table t2 (a int, b int) ENGINE=MyISAM;
3104
3442
insert into t2 values (2, 1), (1, 0);
3105
3443
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
3106
3444
drop table t1, t2;
3107
create table t1 (i int, j bigint) ENGINE=MyISAM;
3445
#
3446
# Bug#19077: A nested materialized derived table is used before being populated.
3447
#
3448
create table t1 (i int, j bigint);
3108
3449
insert into t1 values (1, 2), (2, 2), (3, 2);
3109
3450
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
3110
3451
min(i)
3111
3452
1
3112
3453
drop table t1;
3113
CREATE TABLE t1 (i BIGINT) ENGINE=MyISAM;
3454
#
3455
# Bug#19700: subselect returning BIGINT always returned it as SIGNED
3456
#
3457
CREATE TEMPORARY TABLE t1 (i BIGINT) ENGINE=MyISAM;
3114
3458
INSERT INTO t1 VALUES (10000000000000000);
3115
3459
INSERT INTO t1 VALUES (1);
3116
CREATE TABLE t2 (i BIGINT) ENGINE=MyISAM;
3460
CREATE TEMPORARY TABLE t2 (i BIGINT) ENGINE=MyISAM;
3117
3461
INSERT INTO t2 VALUES (10000000000000000);
3118
3462
INSERT INTO t2 VALUES (1);
3119
3463
/* simple test */
3131
3475
10000000000000000
3132
3476
DROP TABLE t1;
3133
3477
DROP TABLE t2;
3134
CREATE TABLE t1 (
3478
#
3479
# Bug#20519: subselect with LIMIT M, N
3480
#
3481
CREATE TEMPORARY TABLE t1 (
3135
3482
id bigint NOT NULL auto_increment,
3136
3483
name varchar(255) NOT NULL,
3137
3484
PRIMARY KEY (id))
3142
3489
id bigint NOT NULL auto_increment,
3143
3490
mid bigint NOT NULL,
3144
3491
date date NOT NULL,
3145
PRIMARY KEY (id))
3146
ENGINE=MyISAM;
3492
PRIMARY KEY (id));
3147
3493
INSERT INTO t2 VALUES
3148
3494
(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
3149
3495
(4, 2, '2006-04-20'), (5, 1, '2006-05-01');
3176
3522
2 Joe 2006-04-20 2006-04-06
3177
3523
3 Frank 2006-04-13 NULL
3178
3524
DROP TABLE t1,t2;
3525
#
3526
# Bug#20869: subselect with range access by DESC
3527
#
3179
3528
CREATE TABLE t1 (
3180
3529
i1 int NOT NULL default '0',
3181
3530
i2 int NOT NULL default '0',
3182
3531
t datetime,
3183
PRIMARY KEY (i1,i2,t))
3184
ENGINE=MyISAM;
3532
PRIMARY KEY (i1,i2,t));
3185
3533
INSERT INTO t1 VALUES
3186
3534
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
3187
3535
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
3193
3541
i1 int NOT NULL default '0',
3194
3542
i2 int NOT NULL default '0',
3195
3543
t datetime default NULL,
3196
PRIMARY KEY (i1))
3197
ENGINE=MyISAM;
3544
PRIMARY KEY (i1));
3198
3545
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
3199
3546
EXPLAIN
3200
3547
SELECT * FROM t1,t2
3202
3549
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
3203
3550
ORDER BY t1.t DESC LIMIT 1);
3204
3551
id select_type table type possible_keys key key_len ref rows Extra
3205
1 PRIMARY t2 system NULL NULL NULL NULL 1
3206
1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
3207
2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
3552
1 PRIMARY t2 ALL NULL NULL NULL NULL 1
3553
1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index; Using join buffer
3554
2 DEPENDENT SUBQUERY t1 ref PRIMARY PRIMARY 8 test.t2.i1,const 5 Using where; Using index; Using filesort
3208
3555
SELECT * FROM t1,t2
3209
3556
WHERE t1.t = (SELECT t1.t FROM t1
3210
3557
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
3212
3559
i1 i2 t i1 i2 t
3213
3560
24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40
3214
3561
DROP TABLE t1, t2;
3215
CREATE TABLE t1 (i INT) ENGINE=MyISAM;
3562
#
3563
# Bug#14654 : Cannot select from the same table twice within a UNION
3564
# statement
3565
#
3566
CREATE TABLE t1 (i INT);
3216
3567
(SELECT i FROM t1) UNION (SELECT i FROM t1);
3217
3568
i
3218
3569
SELECT * FROM t1 WHERE NOT EXISTS
3221
3572
(SELECT i FROM t1)
3222
3573
);
3223
3574
i
3575
#TODO:not supported
3224
3576
SELECT * FROM t1
3225
3577
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
3226
3578
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
3579
#TODO:not supported
3227
3580
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
3228
3581
from t1;
3229
3582
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'union (select t12.i from t1 t12))
3231
3584
explain select * from t1 where not exists
3232
3585
((select t11.i from t1 t11) union (select t12.i from t1 t12));
3233
3586
id select_type table type possible_keys key key_len ref rows Extra
3234
1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
3235
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3236
3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3237
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
3587
1 PRIMARY t1 ALL NULL NULL NULL NULL #
3588
2 SUBQUERY t11 ALL NULL NULL NULL NULL #
3589
3 UNION t12 ALL NULL NULL NULL NULL #
3590
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL #
3238
3591
DROP TABLE t1;
3239
CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
3240
CREATE TABLE t2 (a INT) ENGINE=MyISAM;
3592
#
3593
# Bug #21540: Subqueries with no from and aggregate functions return
3594
# wrong results
3595
CREATE TABLE t1 (a INT, b INT);
3596
CREATE TABLE t2 (a INT);
3241
3597
INSERT INTO t2 values (1);
3242
3598
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
3243
3599
SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
3276
3632
2
3277
3633
2
3278
3634
DROP TABLE t1,t2;
3279
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)) ENGINE=MyISAM;
3280
CREATE TABLE t2 (x int auto_increment, y int, z int,
3635
#
3636
# Bug #21727: Correlated subquery that requires filesort:
3637
# slow with big sort_buffer_size
3638
#
3639
CREATE TEMPORARY TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)) ENGINE=MyISAM;
3640
CREATE TEMPORARY TABLE t2 (x int auto_increment, y int, z int,
3281
3641
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)) ENGINE=MyISAM;
3282
3642
SET SESSION sort_buffer_size = 32 * 1024;
3283
3643
Warnings:
3294
3654
COUNT(*)
3295
3655
3000
3296
3656
DROP TABLE t2,t1;
3297
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int) ENGINE=MyISAM;
3298
CREATE TABLE t2 (c int) ENGINE=MyISAM;
3657
#
3658
# Bug #25219: EXIST subquery with UNION over a mix of
3659
# correlated and uncorrelated selects
3660
#
3661
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
3662
CREATE TABLE t2 (c int);
3299
3663
INSERT INTO t1 VALUES ('aa', 1);
3300
3664
INSERT INTO t2 VALUES (1);
3301
3665
SELECT * FROM t1
3315
3679
cc 3
3316
3680
dd 1
3317
3681
INSERT INTO t2 VALUES (2);
3318
CREATE TABLE t3 (c int) ENGINE=MyISAM;
3682
CREATE TEMPORARY TABLE t3 (c int) ENGINE=MyISAM;
3319
3683
INSERT INTO t3 VALUES (1);
3320
3684
SELECT * FROM t1
3321
3685
WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
3327
3691
cc 3
3328
3692
dd 1
3329
3693
DROP TABLE t1,t2,t3;
3694
#
3695
# Bug#21904 (parser problem when using IN with a double "(())")
3696
#
3330
3697
DROP TABLE IF EXISTS t1;
3331
3698
DROP TABLE IF EXISTS t2;
3332
3699
DROP TABLE IF EXISTS t1xt2;
3333
CREATE TABLE t1 (
3700
CREATE TEMPORARY TABLE t1 (
3334
3701
id_1 int NOT NULL,
3335
3702
t varchar(4) DEFAULT NULL)
3336
3703
ENGINE=MyISAM;
3337
CREATE TABLE t2 (
3704
CREATE TEMPORARY TABLE t2 (
3338
3705
id_2 int NOT NULL,
3339
3706
t varchar(4) DEFAULT NULL)
3340
3707
ENGINE=MyISAM;
3341
CREATE TABLE t1xt2 (
3708
CREATE TEMPORARY TABLE t1xt2 (
3342
3709
id_1 int NOT NULL,
3343
3710
id_2 int NOT NULL)
3344
3711
ENGINE=MyISAM;
3345
3712
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
3346
3713
INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');
3347
3714
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
3715
# subselect returns 0 rows
3348
3716
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3349
3717
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3350
3718
id_1
3376
3744
3
3377
3745
4
3378
3746
insert INTO t1xt2 VALUES (1, 12);
3747
# subselect returns 1 row
3379
3748
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3380
3749
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3381
3750
id_1
3407
3776
3
3408
3777
4
3409
3778
insert INTO t1xt2 VALUES (2, 12);
3779
# subselect returns more than 1 row
3410
3780
SELECT DISTINCT t1.id_1 FROM t1 WHERE
3411
3781
(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
3412
3782
id_1
3440
3810
DROP TABLE t1;
3441
3811
DROP TABLE t2;
3442
3812
DROP TABLE t1xt2;
3443
CREATE TABLE t1 (a int) ENGINE=MyISAM;
3813
#
3814
# Bug #26728: derived table with concatanation of literals in select list
3815
#
3816
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
3444
3817
INSERT INTO t1 VALUES (3), (1), (2);
3445
3818
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
3446
3819
col1 col2
3453
3826
this is a test. 1
3454
3827
this is a test. 2
3455
3828
DROP table t1;
3456
CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
3457
CREATE TABLE t2 (m int, n int) ENGINE=MyISAM;
3829
#
3830
# Bug #27257: COUNT(*) aggregated in outer query
3831
#
3832
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
3833
CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;
3458
3834
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3459
3835
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3460
3836
SELECT COUNT(*), a,
3478
3854
2 2
3479
3855
3 3
3480
3856
DROP TABLE t1,t2;
3481
CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
3482
CREATE TABLE t2 (m int, n int) ENGINE=MyISAM;
3857
#
3858
# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument
3859
#
3860
CREATE TEMPORARY TABLE t1 (a int, b int) ENGINE=MyISAM;
3861
CREATE TEMPORARY TABLE t2 (m int, n int) ENGINE=MyISAM;
3483
3862
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
3484
3863
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
3485
3864
SELECT COUNT(*) c, a,
3497
3876
3 3 4
3498
3877
1 4 2,2
3499
3878
DROP table t1,t2;
3500
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)) ENGINE=MyISAM;
3879
#
3880
# Bug#27321: Wrong subquery result in a grouping select
3881
#
3882
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
3501
3883
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
3502
3884
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
3503
3885
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
3562
3944
2 o
3563
3945
3 p
3564
3946
DROP TABLE t1;
3565
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
3947
#
3948
# Bug #27363: nested aggregates in outer, subquery / sum(select
3949
# count(outer))
3950
#
3951
CREATE TABLE t1 (a INT);
3566
3952
INSERT INTO t1 values (1),(1),(1),(1);
3567
CREATE TABLE t2 (x INT) ENGINE=MyISAM;
3953
CREATE TEMPORARY TABLE t2 (x INT) ENGINE=MyISAM;
3568
3954
INSERT INTO t1 values (1000),(1001),(1002);
3569
3955
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
3570
3956
ERROR HY000: Invalid use of group function
3579
3965
FROM t1;
3580
3966
ERROR HY000: Invalid use of group function
3581
3967
DROP TABLE t1,t2;
3582
CREATE TABLE t1 (a int, b int, KEY (a)) ENGINE=MyISAM;
3968
#
3969
# Bug #27807: Server crash when executing subquery with EXPLAIN
3970
#
3971
CREATE TABLE t1 (a int, b int, KEY (a));
3583
3972
INSERT INTO t1 VALUES (1,1),(2,1);
3584
3973
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
3585
3974
id select_type table type possible_keys key key_len ref rows Extra
3586
3975
1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
3587
3976
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
3588
3977
DROP TABLE t1;
3589
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)) ENGINE=MyISAM;
3978
#
3979
# Bug #28377: grouping query with a correlated subquery in WHERE condition
3980
#
3981
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
3590
3982
INSERT INTO t1 VALUES
3591
3983
(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
3592
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;
3984
CREATE TEMPORARY TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;
3593
3985
INSERT INTO t2 VALUES (7), (5), (1), (3);
3594
3986
SELECT id, st FROM t1
3595
3987
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
3616
4008
2 GA
3617
4009
4 FL
3618
4010
DROP TABLE t1,t2;
3619
CREATE TABLE t1 (a int) ENGINE=MyISAM;
4011
#
4012
# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table
4013
# over a grouping subselect
4014
#
4015
CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;
3620
4016
INSERT INTO t1 VALUES (1), (2);
3621
4017
EXPLAIN EXTENDED
3622
4018
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
3626
4022
Warnings:
3627
4023
Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`
3628
4024
DROP TABLE t1;
4025
#
4026
# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1
4027
#
3629
4028
CREATE TABLE t1 (
3630
4029
a varchar(255) default NULL,
3631
4030
b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3632
INDEX idx(a,b))
3633
ENGINE=MyISAM;
4031
INDEX idx(a,b));
4032
Warnings:
4033
Warning 1071 Specified key was too long; max key length is 767 bytes
3634
4034
CREATE TABLE t2 (
3635
a varchar(255) default NULL)
3636
ENGINE=MyISAM;
4035
a varchar(255) default NULL);
3637
4036
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
3638
4037
INSERT INTO t1 SELECT * FROM t1;
3639
4038
INSERT INTO t1 SELECT * FROM t1;
3654
4053
1
3655
4054
1
3656
4055
DROP TABLE t1,t2;
3657
CREATE TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;
3658
CREATE TABLE t2 (x INTEGER) ENGINE=MyISAM;
4056
#
4057
# Bug #27333: subquery grouped for aggregate of outer query / no aggregate
4058
# of subquery
4059
#
4060
CREATE TEMPORARY TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;
4061
CREATE TEMPORARY TABLE t2 (x INTEGER) ENGINE=MyISAM;
3659
4062
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
3660
4063
INSERT INTO t2 VALUES (1), (2);
4064
# wasn't failing, but should
3661
4065
SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
3662
4066
ERROR 21000: Subquery returns more than 1 row
4067
# fails as it should
3663
4068
SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
3664
4069
ERROR 21000: Subquery returns more than 1 row
3665
4070
SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
3666
4071
(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)
3667
4072
3.3333
3668
4073
DROP TABLE t1,t2;
3669
CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
4074
# second test case from 27333
4075
CREATE TABLE t1 (a INT, b INT);
3670
4076
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
4077
# returns no rows, when it should
3671
4078
SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
3672
4079
AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
3673
4080
GROUP BY a1.a;
3674
4081
a COUNT(*)
3675
4082
1 3
3676
4083
DROP TABLE t1;
3677
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
3678
CREATE TABLE t2 (a INT) ENGINE=MyISAM;
4084
#test cases from 29297
4085
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;
4086
CREATE TEMPORARY TABLE t2 (a INT) ENGINE=MyISAM;
3679
4087
INSERT INTO t1 VALUES (1),(2);
3680
4088
INSERT INTO t2 VALUES (1),(2);
3681
4089
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
3687
4095
(SELECT SUM(t1.a) FROM t2 WHERE a=1)
3688
4096
3
3689
4097
DROP TABLE t1,t2;
3690
CREATE TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;
3691
CREATE TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;
4098
#
4099
# Bug #31884: Assertion + crash in subquery in the SELECT clause.
4100
#
4101
CREATE TEMPORARY TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;
4102
CREATE TEMPORARY TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;
3692
4103
INSERT INTO t1 VALUES (100, 200);
3693
4104
INSERT INTO t1 VALUES (101, 201);
3694
4105
INSERT INTO t2 VALUES (101, 201);
3698
4109
0
3699
4110
0
3700
4111
DROP TABLE t1, t2;
3701
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10)) ENGINE=MyISAM;
4112
#
4113
# Bug #30788: Inconsistent retrieval of char/varchar
4114
#
4115
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
3702
4116
INSERT INTO t1 VALUES ('a', 'aa');
3703
4117
INSERT INTO t1 VALUES ('a', 'aaa');
3704
4118
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3707
4121
CREATE INDEX I2 ON t1 (b);
3708
4122
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3709
4123
id select_type table type possible_keys key key_len ref rows Extra
3710
1 PRIMARY t1 index I1 I1 7 NULL 2 Using index; LooseScan
3711
1 PRIMARY t1 ref I2 I2 43 test.t1.a 2 Using where
4124
1 PRIMARY t1 index I1 I1 7 NULL # Using index; LooseScan
4125
1 PRIMARY t1 ref I2 I2 43 test.t1.a # Using where
3712
4126
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3713
4127
a b
3714
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)) ENGINE=MyISAM;
4128
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
3715
4129
INSERT INTO t2 SELECT * FROM t1;
3716
4130
CREATE INDEX I1 ON t2 (a);
3717
4131
CREATE INDEX I2 ON t2 (b);
3718
4132
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
3719
4133
id select_type table type possible_keys key key_len ref rows Extra
3720
1 PRIMARY t2 index I1 I1 7 NULL 2 Using index; LooseScan
3721
1 PRIMARY t2 ref I2 I2 43 test.t2.a 2 Using where
4134
1 PRIMARY t2 index I1 I1 7 NULL # Using index; LooseScan
4135
1 PRIMARY t2 ref I2 I2 43 test.t2.a # Using where
3722
4136
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
3723
4137
a b
3724
4138
EXPLAIN
3725
4139
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
3726
4140
id select_type table type possible_keys key key_len ref rows Extra
3727
1 PRIMARY t1 index I1 I1 7 NULL 2 Using where; Using index; LooseScan
3728
1 PRIMARY t1 ref I2 I2 43 test.t1.a 2 Using where
4141
1 PRIMARY t1 index I1 I1 7 NULL # Using where; Using index; LooseScan
4142
1 PRIMARY t1 ref I2 I2 43 test.t1.a # Using where
3729
4143
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
3730
4144
a b
3731
4145
DROP TABLE t1,t2;
3732
CREATE TABLE t1(a INT, b INT) ENGINE=MyISAM;
4146
#
4147
# Bug #32400: Complex SELECT query returns correct result only on some
4148
# occasions
4149
#
4150
CREATE TABLE t1(a INT, b INT);
3733
4151
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
3734
4152
EXPLAIN
3735
4153
SELECT a AS out_a, MIN(b) FROM t1
3745
4163
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
3746
4164
GROUP BY a;
3747
4165
id select_type table type possible_keys key key_len ref rows Extra
3748
1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
3749
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
4166
1 PRIMARY t1_outer ALL NULL NULL NULL NULL # Using where; Using temporary; Using filesort
4167
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where
3750
4168
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
3751
4169
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
3752
4170
GROUP BY a;
3754
4172
1 2
3755
4173
2 4
3756
4174
DROP TABLE t1;
3757
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
3758
CREATE TABLE t2 (a INT) ENGINE=MyISAM;
4175
#
4176
# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
4177
#
4178
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=MyISAM;
4179
CREATE TABLE t2 (a INT);
3759
4180
INSERT INTO t1 VALUES (1),(2);
3760
4181
INSERT INTO t2 VALUES (1),(2);
3761
4182
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
3774
4195
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
3775
4196
(SELECT 1 FROM t2 WHERE t1.a = t2.a));
3776
4197
id select_type table type possible_keys key key_len ref rows filtered Extra
3777
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
3778
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
3779
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where
3780
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
4198
1 PRIMARY t1 ALL NULL NULL NULL NULL # 100.00 Using where
4199
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL # 100.00 Using where
4200
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL # 100.00 Using where
4201
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL # NULL
3781
4202
Warnings:
3782
4203
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
3783
4204
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
3784
4205
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`)))
3785
4206
DROP TABLE t1,t2;
3786
CREATE TABLE t4 (
4207
#
4208
# BUG#33794 "MySQL crashes executing specific query on specific dump"
4209
#
4210
CREATE TEMPORARY TABLE t4 (
3787
4211
f7 varchar(32) collate utf8_bin NOT NULL default '',
3788
4212
f10 varchar(32) collate utf8_bin default NULL,
3789
4213
PRIMARY KEY (f7))
3794
4218
f2 varchar(50) collate utf8_bin default NULL,
3795
4219
f3 varchar(10) collate utf8_bin default NULL,
3796
4220
PRIMARY KEY (f4),
3797
UNIQUE KEY uk1 (f2))
3798
ENGINE=MyISAM;
4221
UNIQUE KEY uk1 (f2));
3799
4222
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
3800
CREATE TABLE t1 (
4223
CREATE TEMPORARY TABLE t1 (
3801
4224
f8 varchar(32) collate utf8_bin NOT NULL default '',
3802
4225
f1 varchar(10) collate utf8_bin default NULL,
3803
4226
f9 varchar(32) collate utf8_bin default NULL,
3804
4227
PRIMARY KEY (f8))
3805
4228
ENGINE=MyISAM;
3806
4229
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
3807
CREATE TABLE t3 (
4230
CREATE TEMPORARY TABLE t3 (
3808
4231
f6 varchar(32) collate utf8_bin NOT NULL default '',
3809
4232
f5 varchar(50) collate utf8_bin default NULL,
3810
4233
PRIMARY KEY (f6))
3832
4255
2 NULL NULL
3833
4256
DROP TABLE t1, t2, t3, t4;
3834
4257
End of 5.0 tests.
3835
create table t_out (subcase char(3),
4258
#
4259
# Test [NOT] IN truth table (both as top-level and general predicate).
4260
#
4261
create temporary table t_out (subcase char(3),
3836
4262
a1 char(2), b1 char(2), c1 char(2)) ENGINE=MyISAM;
3837
create table t_in (a2 char(2), b2 char(2), c2 char(2)) ENGINE=MyISAM;
4263
create table t_in (a2 char(2), b2 char(2), c2 char(2));
3838
4264
insert into t_out values ('A.1','2a', NULL, '2a');
4265
#------------------------- A.2 - impossible
3839
4266
insert into t_out values ('A.3', '2a', NULL, '2a');
3840
4267
insert into t_out values ('A.4', '2a', NULL, 'xx');
3841
4268
insert into t_out values ('B.1', '2a', '2a', '2a');
3935
4362
NOT((a1, b1, c1) IN (select * from t_in));
3936
4363
not_pred_in
3937
4364
F
4365
# test non-top level result indirectly
3938
4366
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
3939
4367
where subcase = 'A.3' and
3940
4368
((a1, b1, c1) IN (select * from t_in)) is NULL and
4005
4433
NOT((a1, b1, c1) IN (select * from t_in));
4006
4434
not_pred_in
4007
4435
F
4436
# test non-top level result indirectly
4008
4437
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
4009
4438
where subcase = 'B.3' and
4010
4439
((a1, b1, c1) IN (select * from t_in)) is NULL and
4029
4458
T
4030
4459
drop table t_out;
4031
4460
drop table t_in;
4032
CREATE TABLE t1 (s1 char(1)) ENGINE=MyISAM;
4461
#
4462
# Bug#20835 (literal string with =any values)
4463
#
4464
CREATE TABLE t1 (s1 char(1));
4033
4465
INSERT INTO t1 VALUES ('a');
4034
4466
SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);
4035
4467
s1
4036
4468
a
4037
4469
DROP TABLE t1;
4038
CREATE TABLE t1( a INT ) ENGINE=MyISAM;
4470
#
4471
# Bug#33204: INTO is allowed in subselect, causing inconsistent results
4472
#
4473
CREATE TABLE t1( a INT );
4039
4474
INSERT INTO t1 VALUES (1),(2);
4040
CREATE TABLE t2( a INT, b INT ) ENGINE=MyISAM;
4475
CREATE TABLE t2( a INT, b INT );
4041
4476
SELECT *
4042
4477
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
4043
4478
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
4088
4523
a
4089
4524
1
4090
4525
2
4526
# This was not allowed previously. Possibly, it should be allowed on the future.
4527
# For now, the intent is to keep the fix as non-intrusive as possible.
4091
4528
SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
4092
4529
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')' at line 1
4093
4530
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
4126
4563
SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
4127
4564
a
4128
4565
1
4566
# Test of rule
4567
# table_factor: '(' get_select_lex query_expression_body ')' opt_table_alias
4568
# UNION should not be allowed inside the parentheses, nor should
4569
# aliases after.
4570
#
4129
4571
SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;
4130
4572
a 1
4131
4573
1 1
4176
4618
a a
4177
4619
1 1
4178
4620
2 1
4621
# For the join, TABLE_LIST::select_lex == NULL
4622
# Check that we handle this.
4179
4623
SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2;
4180
4624
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 't1a2' at line 1
4181
4625
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 );
4223
4667
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4224
4668
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4225
4669
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
4670
# Make sure context is popped when we leave the nested select
4226
4671
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
4227
4672
( SELECT a FROM t1 WHERE a = 1 ) a
4228
4673
1 1
4231
4676
( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a
4232
4677
1 1
4233
4678
1 2
4679
# Make sure we have feature F561 (see .yy file)
4234
4680
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
4235
4681
a b
4682
# Make sure the parser does not allow nested UNIONs anywhere
4236
4683
SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );
4237
4684
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4238
4685
( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
Loggerhead is a web-based interface for Breezy
Version: 2.0.1