← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/subselect.test

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

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/subselect.test
53
53
 
54
54
SELECT 1 as a,(SELECT a+a) b,(SELECT b);
55
55
 
56
 
create table t1 (a int) ENGINE=MyISAM;
57
 
create table t2 (a int, b int) ENGINE=MyISAM;
58
 
create table t3 (a int) ENGINE=MyISAM;
59
 
create table t4 (a int not null, b int not null) ENGINE=MyISAM;
 
56
create table t1 (a int);
 
57
create table t2 (a int, b int);
 
58
create table t3 (a int);
 
59
create table t4 (a int not null, b int not null);
60
60
insert into t1 values (2);
61
61
insert into t2 values (1,7),(2,7);
62
62
insert into t4 values (4,8),(3,8),(5,9);
111
111
select b,max(a) as ma from t4 group by b having ma < (select max(t2.a) from t2 where t2.b=t4.b);
112
112
delete from t2 where a=2 and b=10;
113
113
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);
114
 
create table t5 (a int) ENGINE=MyISAM;
 
114
create table t5 (a int);
115
115
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
116
116
insert into t5 values (5);
117
117
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
120
120
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;
121
121
-- error 1242
122
122
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
123
 
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)) ENGINE=MyISAM;
124
 
create table t7( uq int primary key, name char(25)) ENGINE=MyISAM;
 
123
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
 
124
create table t7( uq int primary key, name char(25));
125
125
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
126
126
insert into t6 values (1,1),(1,2),(2,2),(1,3);
127
127
select * from t6 where exists (select * from t7 where uq = clinic_uq);
134
134
# different tipes & group functions
135
135
drop table t1,t2,t3;
136
136
 
137
 
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0') ENGINE=MyISAM;
 
137
CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
138
138
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
139
 
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0') ENGINE=MyISAM;
 
139
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
140
140
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
141
 
CREATE TABLE t1 (a varchar(20),b date NULL) ENGINE=MyISAM;
 
141
CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00');
142
142
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
143
143
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
144
144
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
170
170
#searchconthardwarefr3 forumconthardwarefr7
171
171
CREATE TABLE `t1` (
172
172
  `topic` bigint NOT NULL default '0',
173
 
  `date` date NULL,
 
173
  `date` date NOT NULL default '0000-00-00',
174
174
  `pseudo` varchar(35) NOT NULL default '',
175
175
  PRIMARY KEY  (`pseudo`,`date`,`topic`),
176
176
  KEY `topic` (`topic`)
200
200
CREATE TABLE `t2` (
201
201
      `mot` varchar(30) NOT NULL default '',
202
202
      `topic` bigint NOT NULL default '0',
203
 
      `date` date NULL,
 
203
      `date` date NOT NULL default '0000-00-00',
204
204
      `pseudo` varchar(35) NOT NULL default '',
205
205
       PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`)
206
206
    ) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
245
245
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
246
246
drop table t1;
247
247
 
248
 
create table t1 (a int) ENGINE=MyISAM;
 
248
create table t1 (a int);
249
249
insert into t1 values (1),(2),(3);
250
250
(select * from t1) union (select * from t1) order by (select a from t1 limit 1);
251
251
drop table t1;
252
252
 
253
253
#iftest
254
 
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b') ENGINE=MyISAM;
 
254
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
255
255
INSERT INTO t1 VALUES ();
256
256
-- error 1242
257
257
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
278
278
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
279
279
drop table t1;
280
280
 
281
 
CREATE TABLE t1 (a int) ENGINE=MyISAM;
 
281
CREATE TABLE t1 (a int);
282
282
INSERT INTO t1 VALUES (1);
283
283
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
284
284
drop table t1;
285
285
 
286
286
#update with subselects
287
 
create table t1 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
288
 
create table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
 
287
create table t1 (a int NOT NULL, b int, primary key (a));
 
288
create table t2 (a int NOT NULL, b int, primary key (a));
289
289
insert into t1 values (0, 10),(1, 11),(2, 12);
290
290
insert into t2 values (1, 21),(2, 22),(3, 23);
291
291
select * from t1;
298
298
drop table t1, t2;
299
299
 
300
300
#delete with subselects
301
 
create table t1 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
302
 
create table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
 
301
create table t1 (a int NOT NULL, b int, primary key (a));
 
302
create table t2 (a int NOT NULL, b int, primary key (a));
303
303
insert into t1 values (0, 10),(1, 11),(2, 12);
304
304
insert into t2 values (1, 21),(2, 12),(3, 23);
305
305
select * from t1;
314
314
 
315
315
#multi-delete with subselects
316
316
 
317
 
create table t11 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
318
 
create table t12 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
319
 
create table t2 (a int NOT NULL, b int, primary key (a)) ENGINE=MyISAM;
 
317
create table t11 (a int NOT NULL, b int, primary key (a));
 
318
create table t12 (a int NOT NULL, b int, primary key (a));
 
319
create table t2 (a int NOT NULL, b int, primary key (a));
320
320
insert into t11 values (0, 10),(1, 11),(2, 12);
321
321
insert into t12 values (33, 10),(22, 11),(2, 12);
322
322
insert into t2 values (1, 21),(2, 12),(3, 23);
323
323
select * from t11;
324
 
--sorted_result
325
324
select * from t12;
326
325
-- error 1093
327
326
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
329
328
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
330
329
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
331
330
select * from t11;
332
 
--sorted_result
333
331
select * from t12;
334
332
drop table t11, t12, t2;
335
333
 
336
334
#insert with subselects
337
 
CREATE TABLE t1 (x int) ENGINE=MyISAM;
338
 
create table t2 (a int) ENGINE=MyISAM;
339
 
create table t3 (b int) ENGINE=MyISAM;
 
335
CREATE TABLE t1 (x int);
 
336
create table t2 (a int);
 
337
create table t3 (b int);
340
338
insert into t2 values (1);
341
339
insert into t3 values (1),(2);
342
340
-- error 1093
366
364
drop table t1, t2, t3;
367
365
 
368
366
#replace with subselects
369
 
CREATE TABLE t1 (x int not null, y int, primary key (x)) ENGINE=MyISAM;
370
 
create table t2 (a int) ENGINE=MyISAM;
371
 
create table t3 (a int) ENGINE=MyISAM;
 
367
CREATE TABLE t1 (x int not null, y int, primary key (x));
 
368
create table t2 (a int);
 
369
create table t3 (a int);
372
370
insert into t2 values (1);
373
371
insert into t3 values (1),(2);
374
372
select * from t1;
382
380
select * from t1;
383
381
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
384
382
-- sleep 1
385
 
--sorted_result
386
383
select * from t1;
387
384
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
388
385
-- sleep 1
389
386
select * from t1;
390
387
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
391
 
--sorted_result
392
388
select * from t1;
393
389
drop table t1, t2, t3;
394
390
 
417
413
drop table t2, t1;
418
414
 
419
415
#NULL test
420
 
create table t1 (a int) ENGINE=MyISAM;
 
416
create table t1 (a int);
421
417
insert into t1 values (1),(2),(3);
422
418
select 1 IN (SELECT * from t1);
423
419
select 10 IN (SELECT * from t1);
431
427
select 1 > ANY (SELECT * from t1);
432
428
select 10 > ANY (SELECT * from t1);
433
429
drop table t1;
434
 
create table t1 (a varchar(20)) ENGINE=MyISAM;
 
430
create table t1 (a varchar(20));
435
431
insert into t1 values ('A'),('BC'),('DEF');
436
432
select 'A' IN (SELECT * from t1);
437
433
select 'XYZS' IN (SELECT * from t1);
445
441
select 'A' > ANY (SELECT * from t1);
446
442
select 'XYZS' > ANY (SELECT * from t1);
447
443
drop table t1;
448
 
create table t1 (a float) ENGINE=MyISAM;
 
444
create table t1 (a float);
449
445
insert into t1 values (1.5),(2.5),(3.5);
450
446
select 1.5 IN (SELECT * from t1);
451
447
select 10.5 IN (SELECT * from t1);
459
455
select 1.5 > ANY (SELECT * from t1);
460
456
select 10.5 > ANY (SELECT * from t1);
461
457
explain extended select (select a+1) from t1;
462
 
--sorted_result
463
458
select (select a+1) from t1;
464
459
drop table t1;
465
460
 
467
462
# Null with keys
468
463
#
469
464
 
470
 
CREATE TABLE t1 (a int NOT NULL default '0', PRIMARY KEY  (a)) ENGINE=MyISAM;
471
 
CREATE TABLE t2 (a int default '0', INDEX (a)) ENGINE=MyISAM;
 
465
CREATE TABLE t1 (a int NOT NULL default '0', PRIMARY KEY  (a));
 
466
CREATE TABLE t2 (a int default '0', INDEX (a));
472
467
INSERT INTO t1 VALUES (1),(2),(3),(4);
473
468
INSERT INTO t2 VALUES (1),(2),(3);
474
469
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
475
470
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
476
 
CREATE TABLE t3 (a int default '0') ENGINE=MyISAM;
 
471
CREATE TABLE t3 (a int default '0');
477
472
INSERT INTO t3 VALUES (1),(2),(3);
478
473
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
479
474
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
480
475
drop table t1,t2,t3;
481
476
 
482
477
#LIMIT is not supported now
483
 
#create table t1 (a float) ENGINE=MyISAM;
 
478
#create table t1 (a float);
484
479
#-- error 1235
485
480
#select 10.5 IN (SELECT * from t1 LIMIT 1);
486
481
#-- error 1235
487
482
#select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
488
483
#drop table t1;
489
484
#
490
 
#create table t1 (a int, b int, c varchar(10)) ENGINE=MyISAM;
491
 
#create table t2 (a int) ENGINE=MyISAM;
 
485
#create table t1 (a int, b int, c varchar(10));
 
486
#create table t2 (a int);
492
487
#insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
493
488
#insert into t2 values (1),(2),(NULL);
494
489
#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;
496
491
#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;
497
492
#drop table t1,t2;
498
493
#
499
 
#create table t1 (a int, b real, c varchar(10)) ENGINE=MyISAM;
 
494
#create table t1 (a int, b real, c varchar(10));
500
495
#insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
501
496
#select ROW(1, 1, 'a') IN (select a,b,c from t1);
502
497
#select ROW(1, 2, 'a') IN (select a,b,c from t1);
512
507
#drop table t1;
513
508
 
514
509
#test of uncacheable subqueries
515
 
CREATE TABLE t1 (a int) ENGINE=MyISAM;
 
510
CREATE TABLE t1 (a int);
516
511
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
517
512
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
518
513
drop table t1;
521
516
CREATE TABLE `t1` (
522
517
  `mot` varchar(30) NOT NULL default '',
523
518
  `topic` bigint NOT NULL default '0',
524
 
  `date` date NULL DEFAULT '2009-01-20',
 
519
  `date` date NOT NULL default '0000-00-00',
525
520
  `pseudo` varchar(35) NOT NULL default '',
526
521
  PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
527
522
  KEY `pseudo` (`pseudo`,`date`,`topic`),
531
526
CREATE TABLE `t2` (
532
527
  `mot` varchar(30) NOT NULL default '',
533
528
  `topic` bigint NOT NULL default '0',
534
 
  `date` date NULL default '1997-08-29',
 
529
  `date` date NOT NULL default '0000-00-00',
535
530
  `pseudo` varchar(35) NOT NULL default '',
536
531
  PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
537
532
  KEY `pseudo` (`pseudo`,`date`,`topic`),
560
555
drop table t1, t2, t3;
561
556
 
562
557
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
563
 
CREATE TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
564
 
SHOW CREATE TABLE t1;
565
 
drop table t1;
566
 
CREATE TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
567
 
SHOW CREATE TABLE t1;
568
 
drop table t1;
569
 
CREATE TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
570
 
SHOW CREATE TABLE t1;
571
 
drop table t1;
572
 
CREATE TABLE t1 ENGINE=MyISAM SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
 
558
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
 
559
SHOW CREATE TABLE t1;
 
560
drop table t1;
 
561
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
 
562
SHOW CREATE TABLE t1;
 
563
drop table t1;
 
564
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
 
565
SHOW CREATE TABLE t1;
 
566
drop table t1;
 
567
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
573
568
select * from t1;
574
569
SHOW CREATE TABLE t1;
575
570
drop table t1;
576
571
 
577
 
create table t1 (a int) ENGINE=MyISAM;
 
572
create table t1 (a int);
578
573
insert into t1 values (1), (2), (3);
579
574
explain extended select a,(select (select rand() from t1 limit 1)  from t1 limit 1)
580
575
from t1;
679
674
#
680
675
# correct NULL in <CONSTANT> IN (SELECT ...)
681
676
#
682
 
create table t1 (a int, unique index indexa (a)) ENGINE=MyISAM; 
 
677
create table t1 (a int, unique index indexa (a)); 
683
678
insert into t1 values (-1), (-4), (-2), (NULL); 
684
679
select -10 IN (select a from t1 FORCE INDEX (indexa)); 
685
680
drop table t1;
687
682
#
688
683
# Test optimization for sub selects
689
684
#
690
 
create table t1 (id int not null auto_increment primary key, salary int, key(salary)) ENGINE=MyISAM;
 
685
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
691
686
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
692
687
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
693
688
drop table t1;
709
704
#
710
705
# uninterruptable update
711
706
#
712
 
create table t1 (a int, b int) ENGINE=MyISAM;
713
 
create table t2 (a int, b int) ENGINE=MyISAM;
 
707
create table t1 (a int, b int);
 
708
create table t2 (a int, b int);
714
709
 
715
710
insert into t1 values (1,0), (2,0), (3,0);
716
711
insert into t2 values (1,1), (2,1), (3,1), (2,2);
741
736
#
742
737
# IN subselect optimization test
743
738
#
744
 
create table t1 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
745
 
create table t2 (a int not null, primary key (a)) ENGINE=MyISAM;
746
 
create table t3 (a int not null, b int, primary key (a)) ENGINE=MyISAM;
 
739
create table t1 (a int not null, b int, primary key (a));
 
740
create table t2 (a int not null, primary key (a));
 
741
create table t3 (a int not null, b int, primary key (a));
747
742
insert into t1 values (1,10), (2,20), (3,30),  (4,40);
748
743
insert into t2 values (2), (3), (4), (5);
749
744
insert into t3 values (10,3), (20,4), (30,5);
754
749
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
755
750
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
756
751
drop table t1, t2, t3;
757
 
create table t1 (a int, b int, index a (a,b)) ENGINE=MyISAM;
758
 
create table t2 (a int, index a (a)) ENGINE=MyISAM;
759
 
create table t3 (a int, b int, index a (a)) ENGINE=MyISAM;
 
752
create table t1 (a int, b int, index a (a,b));
 
753
create table t2 (a int, index a (a));
 
754
create table t3 (a int, b int, index a (a));
760
755
insert into t1 values (1,10), (2,20), (3,30), (4,40);
761
756
disable_query_log;
762
757
# making table large enough
788
783
#
789
784
# alloc_group_fields() working
790
785
#
791
 
create table t1 (a int, b int) ENGINE=MyISAM;
792
 
create table t2 (a int, b int) ENGINE=MyISAM;
793
 
create table t3 (a int, b int) ENGINE=MyISAM;
 
786
create table t1 (a int, b int);
 
787
create table t2 (a int, b int);
 
788
create table t3 (a int, b int);
794
789
insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);
795
790
insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1);
796
791
insert into t3 values (3,3), (2,2), (1,1); 
800
795
#
801
796
# aggregate functions in HAVING test
802
797
#
803
 
create table t1 (s1 int) ENGINE=MyISAM;
804
 
create table t2 (s1 int) ENGINE=MyISAM;
 
798
create table t1 (s1 int);
 
799
create table t2 (s1 int);
805
800
insert into t1 values (1);
806
801
insert into t2 values (1);
807
802
select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);
811
806
# update subquery with wrong field (to force name resolving
812
807
# in UPDATE name space)
813
808
#
814
 
create table t1 (s1 int) ENGINE=MyISAM;
815
 
create table t2 (s1 int) ENGINE=MyISAM;
 
809
create table t1 (s1 int);
 
810
create table t2 (s1 int);
816
811
insert into t1 values (1);
817
812
insert into t2 values (1);
818
813
-- error 1054
823
818
# collation test
824
819
#
825
820
#CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,
826
 
#                 s2 CHAR(5) COLLATE latin1_swedish_ci) ENGINE=MyISAM;
 
821
#                 s2 CHAR(5) COLLATE latin1_swedish_ci);
827
822
#INSERT INTO t1 VALUES ('z','?');
828
823
#-- error 1267
829
824
#select * from t1 where s1 > (select max(s2) from t1);
834
829
#
835
830
# aggregate functions reinitialization
836
831
#
837
 
create table t1(toid int,rd int) ENGINE=MyISAM;
838
 
create table t2(userid int,pmnew int,pmtotal int) ENGINE=MyISAM;
 
832
create table t1(toid int,rd int);
 
833
create table t2(userid int,pmnew int,pmtotal int);
839
834
insert into t2 values(1,0,0),(2,0,0);
840
835
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);
841
836
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);
844
839
#
845
840
# row union
846
841
#
847
 
create table t1 (s1 char(5)) ENGINE=MyISAM;
 
842
create table t1 (s1 char(5));
848
843
-- error 1241
849
844
select (select 'a','b' from t1 union select 'a','b' from t1) from t1;
850
845
insert into t1 values ('tttt');
856
851
#
857
852
# IN optimisation test results
858
853
#
859
 
create table t1 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
860
 
create table t2 (s1 char(5), index s1(s1)) ENGINE=MyISAM;
 
854
create table t1 (s1 char(5), index s1(s1));
 
855
create table t2 (s1 char(5), index s1(s1));
861
856
insert into t1 values ('a1'),('a2'),('a3');
862
857
insert into t2 values ('a1'),('a2');
863
858
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
873
868
#
874
869
# correct ALL optimisation
875
870
#
876
 
create table t2 (a int, b int) ENGINE=MyISAM;
877
 
create table t3 (a int) ENGINE=MyISAM;
 
871
create table t2 (a int, b int);
 
872
create table t3 (a int);
878
873
insert into t3 values (6),(7),(3);
879
874
select * from t3 where a >= all (select b from t2);
880
875
explain extended select * from t3 where a >= all (select b from t2);
904
899
# correct used_tables()
905
900
#
906
901
 
907
 
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 ;
 
902
CREATE TABLE `t1` ( `id` bigint NOT NULL auto_increment, `taskid` bigint NOT NULL default '0', `dbid` int NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY  (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ;
908
903
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());
909
904
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;
910
905
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);
930
925
#
931
926
# optimized ALL/ANY with union
932
927
#
933
 
create table t1 (s1 char) ENGINE=MyISAM;
 
928
create table t1 (s1 char);
934
929
insert into t1 values ('e');
935
930
select * from t1 where 'f' > any (select s1 from t1);
936
931
select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
950
945
#
951
946
# unresolved field error
952
947
#
953
 
create table t1 (s1 int) ENGINE=MyISAM; 
954
 
create table t2 (s1 int) ENGINE=MyISAM;
 
948
create table t1 (s1 int); 
 
949
create table t2 (s1 int);
955
950
-- error 1054
956
951
select * from t1 where (select count(*) from t2 where t1.s2) = 1;
957
952
-- error 1054
963
958
#
964
959
# fix_fields() in add_ref_to_table_cond()
965
960
#
966
 
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB)) ENGINE=MyISAM;
967
 
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA)) ENGINE=MyISAM;
 
961
CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
 
962
CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
968
963
INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');
969
964
INSERT INTO t2 VALUES (100, 200, 'C');
970
965
SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);
971
966
DROP TABLE t1, t2;
972
967
 
973
 
CREATE TABLE t1 (a int) ENGINE=MyISAM;
 
968
CREATE TABLE t1 (a int);
974
969
INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5);
975
970
SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;
976
971
DROP TABLE t1;
989
984
  `bis` int NOT NULL default '0',
990
985
  PRIMARY KEY  (`id`),
991
986
  UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
992
 
  UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`))
993
 
 ENGINE=MyISAM;
 
987
  UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
 
988
);
994
989
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);
995
990
CREATE TABLE `t2` (
996
991
  `id` int NOT NULL auto_increment,
997
992
  `max_anno_dep` int NOT NULL default '0',
998
 
  PRIMARY KEY  (`id`)) ENGINE=MyISAM;
 
993
  PRIMARY KEY  (`id`)
 
994
);
999
995
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
1000
996
 
1001
997
SELECT cns.id, cns.max_anno_dep, cns.max_anno_dep = (SELECT s.anno_dep FROM t1 AS s WHERE s.id_cns = cns.id ORDER BY s.anno_dep DESC LIMIT 1) AS PIPPO FROM t2 AS cns;
1005
1001
#
1006
1002
# GLOBAL LIMIT
1007
1003
#
1008
 
create table t1 (a int) ENGINE=MyISAM;
 
1004
create table t1 (a int);
1009
1005
insert into t1 values (1), (2), (3);
1010
1006
SET SQL_SELECT_LIMIT=1;
1011
1007
select sum(a) from (select * from t1) as a;
1017
1013
# Bug #3118: subselect + order by
1018
1014
#
1019
1015
 
1020
 
CREATE TABLE t1 (a int, b int, INDEX (a)) ENGINE=MyISAM;
 
1016
CREATE TABLE t1 (a int, b int, INDEX (a));
1021
1017
INSERT INTO t1 VALUES (1, 1), (1, 2), (1, 3);
1022
1018
SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;
1023
1019
DROP TABLE t1;
1024
1020
 
1025
1021
# Item_cond fix field
1026
1022
#
1027
 
create table t1(val varchar(10)) ENGINE=MyISAM;
 
1023
create table t1(val varchar(10));
1028
1024
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
1029
1025
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%');
1030
1026
drop table t1;
1032
1028
#
1033
1029
# ref_or_null replacing with ref
1034
1030
#
1035
 
create table t1 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
 
1031
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
1036
1032
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');
1037
1033
select * from t1 where id not in (select id from t1 where id < 8);
1038
1034
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);
1039
1035
explain extended select * from t1 where id not in (select id from t1 where id < 8);
1040
1036
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);
1041
1037
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
1042
 
create table t2 (id int not null, text varchar(20) not null default '', primary key (id)) ENGINE=MyISAM;
 
1038
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
1043
1039
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');
1044
1040
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);
1045
1041
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);
1048
1044
#
1049
1045
# Static tables & rund() in subqueries
1050
1046
#
1051
 
create table t1 (a int) ENGINE=MyISAM;
 
1047
create table t1 (a int);
1052
1048
insert into t1 values (1);
1053
1049
explain select benchmark(1000, (select a from t1 where a=rand()));
1054
1050
drop table t1;
1056
1052
#
1057
1053
# bug 3188
1058
1054
#
1059
 
create table t1(id int) ENGINE=MyISAM;
1060
 
create table t2(id int) ENGINE=MyISAM;
1061
 
create table t3(flag int) ENGINE=MyISAM;
 
1055
create table t1(id int);
 
1056
create table t2(id int);
 
1057
create table t3(flag int);
1062
1058
-- error 1064
1063
1059
select (select * from t3 where id not null) from t1, t2;
1064
1060
drop table t1,t2,t3;
1066
1062
#
1067
1063
# aggregate functions (Bug #3505)
1068
1064
#
1069
 
CREATE TABLE t1 (id INT) ENGINE=MyISAM;
1070
 
CREATE TABLE t2 (id INT) ENGINE=MyISAM;
 
1065
CREATE TABLE t1 (id INT);
 
1066
CREATE TABLE t2 (id INT);
1071
1067
INSERT INTO t1 VALUES (1), (2);
1072
1068
INSERT INTO t2 VALUES (1);
1073
1069
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);
1079
1075
#
1080
1076
# ALL/ANY test
1081
1077
#
1082
 
CREATE TABLE t1 ( a int, b int ) ENGINE=MyISAM;
 
1078
CREATE TABLE t1 ( a int, b int );
1083
1079
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1084
1080
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
1085
1081
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
1214
1210
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;
1215
1211
SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;
1216
1212
DROP TABLE t1;
1217
 
CREATE TABLE t1 ( a double, b double ) ENGINE=MyISAM;
 
1213
CREATE TABLE t1 ( a double, b double );
1218
1214
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
1219
1215
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
1220
1216
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
1229
1225
SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2e0);
1230
1226
SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0);
1231
1227
DROP TABLE t1;
1232
 
CREATE TABLE t1 ( a char(1), b char(1)) ENGINE=MyISAM;
 
1228
CREATE TABLE t1 ( a char(1), b char(1));
1233
1229
INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
1234
1230
SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
1235
1231
SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
1249
1245
#
1250
1246
# SELECT(EXISTS * ...)optimisation
1251
1247
#
1252
 
create table t1 (a int, b int) ENGINE=MyISAM;
 
1248
create table t1 (a int, b int);
1253
1249
insert into t1 values (1,2),(3,4);
1254
1250
select * from t1 up where exists (select * from t1 where t1.a=up.a);
1255
1251
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
1259
1255
# Bug #4102: subselect in HAVING
1260
1256
#
1261
1257
 
1262
 
CREATE TABLE t1 (t1_a int) ENGINE=MyISAM;
 
1258
CREATE TABLE t1 (t1_a int);
1263
1259
INSERT INTO t1 VALUES (1);
1264
 
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b)) ENGINE=MyISAM;
 
1260
CREATE TABLE t2 (t2_a int, t2_b int, PRIMARY KEY (t2_a, t2_b));
1265
1261
INSERT INTO t2 VALUES (1, 1), (1, 2);
1266
1262
SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1
1267
1263
  HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a);
1271
1267
# Test problem with NULL and derived tables (Bug #4097)
1272
1268
#
1273
1269
 
1274
 
CREATE TABLE t1 (id int default NULL,name varchar(10) default NULL) ENGINE=MyISAM;
 
1270
CREATE TABLE t1 (id int default NULL,name varchar(10) default NULL);
1275
1271
INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);
1276
 
CREATE TABLE t2 (id int default NULL, pet varchar(10) default NULL) ENGINE=MyISAM;
 
1272
CREATE TABLE t2 (id int default NULL, pet varchar(10) default NULL);
1277
1273
INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');
1278
1274
SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;
1279
1275
drop table t1,t2;
1291
1287
#
1292
1288
# BUG#5003 - like in subselect
1293
1289
#
1294
 
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;
 
1290
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);
1295
1291
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
1296
1292
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
1297
1293
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
1303
1299
#
1304
1300
# Optimized IN with compound index
1305
1301
#
1306
 
CREATE TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`)) ENGINE=MyISAM;
1307
 
CREATE TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`)) ENGINE=MyISAM;
 
1302
CREATE TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`));
 
1303
CREATE TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY  (`aid`,`bid`));
1308
1304
insert into t1 values (1,1),(1,2),(2,1),(2,2);
1309
1305
insert into t2 values (1,2),(2,2);
1310
1306
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
1319
1315
#
1320
1316
# resolving fields of grouped outer SELECT
1321
1317
#
1322
 
CREATE TABLE t1 (howmanyvalues bigint, avalue int) ENGINE=MyISAM;
 
1318
CREATE TABLE t1 (howmanyvalues bigint, avalue int);
1323
1319
INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4);
1324
1320
SELECT howmanyvalues, count(*) from t1 group by howmanyvalues;
1325
1321
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues;
1329
1325
SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues;
1330
1326
drop table t1;
1331
1327
 
1332
 
create table t1 (x int) ENGINE=MyISAM;
 
1328
create table t1 (x int);
1333
1329
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;
1334
1330
drop table t1;
1335
1331
 
1337
1333
# Test of correct maybe_null flag returning by subquwery for temporary table
1338
1334
# creation
1339
1335
#
1340
 
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;
 
1336
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`));
1341
1337
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);
1342
 
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 ;
 
1338
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`)) ;
1343
1339
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');
1344
1340
-- error 1054
1345
1341
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;
1356
1352
#
1357
1353
# primary query with temporary table and subquery with groupping
1358
1354
#
1359
 
create table t1 (a int, b int) ENGINE=MyISAM;
1360
 
create table t2 (a int, b int) ENGINE=MyISAM;
 
1355
create table t1 (a int, b int);
 
1356
create table t2 (a int, b int);
1361
1357
insert into t1 values (1,1),(1,2),(1,3),(2,4),(2,5);
1362
1358
insert into t2 values (1,3),(2,1);
1363
1359
select distinct a,b, (select max(b) from t2 where t1.b=t2.a) from t1 order by t1.b;
1366
1362
#
1367
1363
# Equal operation under row and empty subquery
1368
1364
#
1369
 
create table t1 (s1 int,s2 int) ENGINE=MyISAM;
 
1365
create table t1 (s1 int,s2 int);
1370
1366
insert into t1 values (20,15);
1371
1367
select * from t1 where  (('a',null) <=> (select 'a',s2 from t1 where s1 = 0));
1372
1368
drop table t1;
1374
1370
#
1375
1371
# ALL/ANY with NULL
1376
1372
#
1377
 
create table t1 (s1 int) ENGINE=MyISAM;
 
1373
create table t1 (s1 int);
1378
1374
insert into t1 values (1),(null);
1379
1375
select * from t1 where s1 < all (select s1 from t1);
1380
1376
select s1, s1 < all (select s1 from t1) from t1;
1414
1410
# Test for BUG#7885: Server crash when 'any' subselect compared to
1415
1411
# non-existant field.
1416
1412
#
1417
 
create table t1 (a1 int) ENGINE=MyISAM;
1418
 
create table t2 (b1 int) ENGINE=MyISAM;
 
1413
create table t1 (a1 int);
 
1414
create table t2 (b1 int);
1419
1415
--error 1054
1420
1416
select * from t1 where a2 > any(select b1 from t2);
1421
1417
select * from t1 where a1 > any(select b1 from t2);
1425
1421
#
1426
1422
# Comparison subquery with * and row
1427
1423
#
1428
 
create table t1 (a integer, b integer) ENGINE=MyISAM;
 
1424
create table t1 (a integer, b integer);
1429
1425
select (select * from t1) = (select 1,2);
1430
1426
select (select 1,2) = (select * from t1);
1431
1427
# queries whih can be converted to IN
1436
1432
#
1437
1433
# Comparison subquery and row with nested rows
1438
1434
#
1439
 
create table t1 (a integer, b integer) ENGINE=MyISAM;
 
1435
create table t1 (a integer, b integer);
1440
1436
-- error 1241
1441
1437
select row(1,(2,2)) in (select * from t1 );
1442
1438
-- error 1241
1469
1465
  endDate datetime NOT NULL,
1470
1466
  createDate datetime NOT NULL,
1471
1467
  modifyDate timestamp NOT NULL,
1472
 
  attributes text NOT NULL)
1473
 
  engine=myisam;
1474
 
 
 
1468
  attributes text NOT NULL
 
1469
);
1475
1470
INSERT INTO t1 VALUES (1,41,'2004-02-09','2010-01-01','2004-02-09','2004-02-09',''),
1476
1471
(1,86,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1477
1472
(1,87,'2004-08-16','2004-08-16','2004-08-16','2004-08-16',''),
1485
1480
CREATE TABLE t2 (
1486
1481
  userId int NOT NULL,
1487
1482
  courseId int NOT NULL,
1488
 
  date datetime NOT NULL)
1489
 
  engine=myisam;
1490
 
 
 
1483
  date datetime NOT NULL
 
1484
);
1491
1485
INSERT INTO t2 VALUES (5141,71,'2003-11-18'),
1492
1486
(5141,72,'2003-11-25'),(5141,41,'2004-08-06'),
1493
1487
(5141,52,'2004-08-06'),(5141,53,'2004-08-06'),
1503
1497
  endDate datetime NOT NULL,
1504
1498
  createDate datetime NOT NULL,
1505
1499
  modifyDate timestamp NOT NULL,
1506
 
  ordering int)
1507
 
  engine=myisam;
1508
 
 
 
1500
  ordering int
 
1501
);
1509
1502
INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);
1510
1503
 
1511
1504
CREATE TABLE t4 (
1516
1509
  ordering int,
1517
1510
  description text,
1518
1511
  createDate datetime NOT NULL,
1519
 
  modifyDate timestamp NOT NULL)
1520
 
  engine=myisam;
1521
 
 
 
1512
  modifyDate timestamp NOT NULL
 
1513
);
1522
1514
INSERT INTO t4 VALUES (9,5,'stationer','stationer',0,'Stationer','2004-01-29','2004-01-29'),
1523
1515
(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');
1524
1516
 
1526
1518
  userId int NOT NULL,
1527
1519
  groupId int NOT NULL,
1528
1520
  createDate datetime NOT NULL,
1529
 
  modifyDate timestamp NOT NULL) ENGINE=MyISAM;
1530
 
 
 
1521
  modifyDate timestamp NOT NULL
 
1522
);
1531
1523
INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
1532
1524
 
1533
1525
select
1567
1559
#
1568
1560
# Transformation in left expression of subquery (BUG#8888)
1569
1561
#
1570
 
create table t1 (a int) ENGINE=MyISAM;
 
1562
create table t1 (a int);
1571
1563
insert into t1 values (1), (2), (3);
1572
1564
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
1573
1565
drop table t1;
1575
1567
#
1576
1568
# single row subqueries and row operations (code covarage improvement)
1577
1569
#
1578
 
create table t1 (a int, b int) ENGINE=MyISAM;
 
1570
create table t1 (a int, b int);
1579
1571
insert into t1 values (1,2);
1580
1572
-- error 1241
1581
1573
select 1 = (select * from t1);
1615
1607
 
1616
1608
# BUG#11821 : Select from subselect using aggregate function on an enum
1617
1609
# segfaults:
1618
 
create table t1 (fld enum('0','1')) ENGINE=MyISAM;
 
1610
create table t1 (fld enum('0','1'));
1619
1611
insert into t1 values ('1');
1620
1612
select * from (select max(fld) from t1) as foo;
1621
1613
drop table t1;
1624
1616
# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
1625
1617
#
1626
1618
 
1627
 
CREATE TABLE t1 (one int, two int, flag char(1)) ENGINE=MyISAM;
1628
 
CREATE TABLE t2 (one int, two int, flag char(1)) ENGINE=MyISAM;
 
1619
CREATE TABLE t1 (one int, two int, flag char(1));
 
1620
CREATE TABLE t2 (one int, two int, flag char(1));
1629
1621
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
1630
1622
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
1631
1623
 
1655
1647
# Bug #12392: where cond with IN predicate for rows and NULL values in table 
1656
1648
#
1657
1649
 
1658
 
CREATE TABLE t1 (a char(5), b char(5)) ENGINE=MyISAM;
 
1650
CREATE TABLE t1 (a char(5), b char(5));
1659
1651
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
1660
1652
 
1661
1653
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
1666
1658
# Bug #11479: subquery over left join with an empty inner table 
1667
1659
#
1668
1660
 
1669
 
CREATE TABLE t1 (a int) ENGINE=MyISAM;
1670
 
CREATE TABLE t2 (a int, b int) ENGINE=MyISAM;
1671
 
CREATE TABLE t3 (b int NOT NULL) ENGINE=MyISAM;
 
1661
CREATE TABLE t1 (a int);
 
1662
CREATE TABLE t2 (a int, b int);
 
1663
CREATE TABLE t3 (b int NOT NULL);
1672
1664
INSERT INTO t1 VALUES (1), (2), (3), (4);
1673
1665
INSERT INTO t2 VALUES (1,10), (3,30);
1674
1666
 
1684
1676
# Bug#18503: Queries with a quantified subquery returning empty set may
1685
1677
# return a wrong result. 
1686
1678
#
1687
 
CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
1688
 
CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;
 
1679
CREATE TABLE t1 (f1 INT);
 
1680
CREATE TABLE t2 (f2 INT);
1689
1681
INSERT INTO t1 VALUES (1);
1690
1682
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
1691
1683
SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);
1695
1687
DROP TABLE t1, t2;
1696
1688
 
1697
1689
# BUG#20975 Wrong query results for subqueries within NOT
1698
 
create table t1 (s1 char) ENGINE=MyISAM;
 
1690
create table t1 (s1 char);
1699
1691
insert into t1 values (1),(2);
1700
1692
 
1701
1693
select * from t1 where (s1 < any (select s1 from t1));
1718
1710
  retailerID varchar(8) NOT NULL,
1719
1711
  statusID   int NOT NULL,
1720
1712
  changed    datetime NOT NULL,
1721
 
  UNIQUE KEY retailerID (retailerID, statusID, changed))
1722
 
  ENGINE=MyISAM;
 
1713
  UNIQUE KEY retailerID (retailerID, statusID, changed)
 
1714
);
1723
1715
 
1724
1716
INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56");
1725
1717
INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53");
1738
1730
# Bug #21180: Subselect with index for both WHERE and ORDER BY 
1739
1731
#             produces empty result
1740
1732
#
1741
 
create table t1(a int, primary key (a)) ENGINE=MyISAM;
 
1733
create table t1(a int, primary key (a));
1742
1734
insert into t1 values (10);
1743
1735
 
1744
 
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b)) ENGINE=MyISAM;
 
1736
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
1745
1737
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
1746
1738
 
1747
1739
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
1769
1761
  field1 int NOT NULL,                 
1770
1762
  field2 int NOT NULL,                 
1771
1763
  field3 int NOT NULL,                 
1772
 
  PRIMARY KEY  (field1,field2,field3))
1773
 
  ENGINE=MyISAM;
1774
 
 
 
1764
  PRIMARY KEY  (field1,field2,field3)  
 
1765
);
1775
1766
CREATE TABLE t2 (             
1776
1767
  fieldA int NOT NULL,            
1777
1768
  fieldB int NOT NULL,            
1778
 
  PRIMARY KEY  (fieldA,fieldB))
1779
 
  ENGINE=MyISAM;
 
1769
  PRIMARY KEY  (fieldA,fieldB)     
 
1770
); 
1780
1771
 
1781
1772
INSERT INTO t1 VALUES
1782
1773
  (1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
1803
1794
#             with possible NULL values by index access from the outer query
1804
1795
#
1805
1796
 
1806
 
CREATE TABLE t1(a int, INDEX (a)) ENGINE=MyISAM;
 
1797
CREATE TABLE t1(a int, INDEX (a));
1807
1798
INSERT INTO t1 VALUES (1), (3), (5), (7);
1808
1799
INSERT INTO t1 VALUES (NULL);
1809
1800
 
1810
 
CREATE TABLE t2(a int) ENGINE=MyISAM;
 
1801
CREATE TABLE t2(a int);
1811
1802
INSERT INTO t2 VALUES (1),(2),(3);
1812
1803
 
1813
1804
EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
1818
1809
#
1819
1810
# Bug #11302: getObject() returns a String for a sub-query of type datetime
1820
1811
#
1821
 
CREATE TABLE t1 (a DATETIME) ENGINE=MyISAM;
 
1812
CREATE TABLE t1 (a DATETIME);
1822
1813
INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
1823
1814
 
1824
 
CREATE TABLE t2 ENGINE=MyISAM AS SELECT 
 
1815
CREATE TABLE t2 AS SELECT 
1825
1816
  (SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a 
1826
1817
   FROM t1 WHERE a > '2000-01-01';
1827
1818
SHOW CREATE TABLE t2;
1828
1819
 
1829
 
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'); 
 
1820
CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01'); 
1830
1821
SHOW CREATE TABLE t3;
1831
1822
 
1832
1823
DROP TABLE t1,t2,t3;
1836
1827
#            that return more than one row
1837
1828
#
1838
1829
 
1839
 
CREATE TABLE t1 (a int) ENGINE=MyISAM;
 
1830
CREATE TABLE t1 (a int);
1840
1831
INSERT INTO t1 VALUES (2), (4), (1), (3);
1841
1832
 
1842
 
CREATE TABLE t2 (b int, c int) ENGINE=MyISAM;
 
1833
CREATE TABLE t2 (b int, c int);
1843
1834
INSERT INTO t2 VALUES
1844
1835
  (2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
1845
1836
 
1894
1885
#
1895
1886
#decimal-related tests
1896
1887
#
1897
 
create table t1 (df decimal(5,1)) ENGINE=MyISAM;
 
1888
create table t1 (df decimal(5,1));
1898
1889
insert into t1 values(1.1);
1899
1890
insert into t1 values(2.2);
1900
1891
 
1902
1893
select * from t1 where df >= all (select avg(df) from t1 group by df);
1903
1894
drop table t1;
1904
1895
 
1905
 
create table t1 (df decimal(5,1)) ENGINE=MyISAM;
 
1896
create table t1 (df decimal(5,1));
1906
1897
insert into t1 values(1.1);
1907
1898
select 1.1 * exists(select * from t1);
1908
1899
drop table t1;
1909
1900
 
1910
1901
CREATE TABLE t1 (
1911
1902
  grp int default NULL,
1912
 
  a decimal(10,2) default NULL) ENGINE=MyISAM;
 
1903
  a decimal(10,2) default NULL);
1913
1904
 
1914
1905
insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);
1915
1906
select * from t1;
1920
1911
# Test for bug #9338: lame substitution of c1 instead of c2 
1921
1912
#
1922
1913
 
1923
 
CREATE table t1 ( c1 integer ) ENGINE=MyISAM;
 
1914
CREATE table t1 ( c1 integer );
1924
1915
INSERT INTO t1 VALUES ( 1 );
1925
1916
INSERT INTO t1 VALUES ( 2 );
1926
1917
INSERT INTO t1 VALUES ( 3 );
1927
1918
 
1928
 
CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;
 
1919
CREATE TABLE t2 ( c2 integer );
1929
1920
INSERT INTO t2 VALUES ( 1 );
1930
1921
INSERT INTO t2 VALUES ( 4 );
1931
1922
INSERT INTO t2 VALUES ( 5 );
1940
1931
#
1941
1932
# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ 
1942
1933
#
1943
 
CREATE TABLE t1 ( c1 integer ) ENGINE=MyISAM;
 
1934
CREATE TABLE t1 ( c1 integer );
1944
1935
INSERT INTO t1 VALUES ( 1 );
1945
1936
INSERT INTO t1 VALUES ( 2 );
1946
1937
INSERT INTO t1 VALUES ( 3 );
1947
1938
INSERT INTO t1 VALUES ( 6 ); 
1948
1939
 
1949
 
CREATE TABLE t2 ( c2 integer ) ENGINE=MyISAM;
 
1940
CREATE TABLE t2 ( c2 integer );
1950
1941
INSERT INTO t2 VALUES ( 1 );
1951
1942
INSERT INTO t2 VALUES ( 4 );
1952
1943
INSERT INTO t2 VALUES ( 5 );
1953
1944
INSERT INTO t2 VALUES ( 6 );
1954
1945
 
1955
 
CREATE TABLE t3 ( c3 integer ) ENGINE=MyISAM;
 
1946
CREATE TABLE t3 ( c3 integer );
1956
1947
INSERT INTO t3 VALUES ( 7 );
1957
1948
INSERT INTO t3 VALUES ( 8 );
1958
1949
 
1965
1956
# Correct building of equal fields list (do not include outer
1966
1957
# fields) (BUG#6384)
1967
1958
#
1968
 
CREATE TABLE t1 (EMPNUM   CHAR(3)) ENGINE=MyISAM;
1969
 
CREATE TABLE t2 (EMPNUM   CHAR(3) ) ENGINE=MyISAM;
 
1959
CREATE TABLE t1 (EMPNUM   CHAR(3));
 
1960
CREATE TABLE t2 (EMPNUM   CHAR(3) );
1970
1961
INSERT INTO t1 VALUES ('E1'),('E2');
1971
1962
INSERT INTO t2 VALUES ('E1');
1972
1963
DELETE FROM t1
1981
1972
# Test for bug #11487: range access in a subquery
1982
1973
#
1983
1974
 
1984
 
CREATE TABLE t1(select_id BIGINT, values_id BIGINT) ENGINE=MyISAM;
 
1975
CREATE TABLE t1(select_id BIGINT, values_id BIGINT);
1985
1976
INSERT INTO t1 VALUES (1, 1);
1986
1977
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT, 
1987
 
                 PRIMARY KEY(select_id,values_id)) ENGINE=MyISAM;
 
1978
                 PRIMARY KEY(select_id,values_id));
1988
1979
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
1989
1980
 
1990
1981
SELECT values_id FROM t1 
2001
1992
 
2002
1993
# BUG#11821 : Select from subselect using aggregate function on an enum
2003
1994
# segfaults:
2004
 
create table t1 (fld enum('0','1')) ENGINE=MyISAM;
 
1995
create table t1 (fld enum('0','1'));
2005
1996
insert into t1 values ('1');
2006
1997
select * from (select max(fld) from t1) as foo;
2007
1998
drop table t1;
2010
2001
# Test for bug #11762: subquery with an aggregate function in HAVING
2011
2002
#
2012
2003
 
2013
 
CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
2014
 
CREATE TABLE t2 (c int, d int) ENGINE=MyISAM;
2015
 
CREATE TABLE t3 (e int) ENGINE=MyISAM;
 
2004
CREATE TABLE t1 (a int, b int);
 
2005
CREATE TABLE t2 (c int, d int);
 
2006
CREATE TABLE t3 (e int);
2016
2007
 
2017
2008
INSERT INTO t1 VALUES 
2018
2009
  (1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
2094
2085
# Test for bug #16603: GROUP BY in a row subquery with a quantifier 
2095
2086
#                      when an index is defined on the grouping field
2096
2087
 
2097
 
CREATE TABLE t1 (a varchar(5), b varchar(10)) ENGINE=MyISAM;
 
2088
CREATE TABLE t1 (a varchar(5), b varchar(10));
2098
2089
INSERT INTO t1 VALUES
2099
2090
  ('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
2100
2091
  ('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
2114
2105
#
2115
2106
# Bug#17366: Unchecked Item_int results in server crash
2116
2107
#
2117
 
create table t1( f1 int,f2 int) ENGINE=MyISAM;
 
2108
create table t1( f1 int,f2 int);
2118
2109
insert into t1 values (1,1),(2,2);
2119
2110
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';
2120
2111
drop table t1;
2123
2114
# Bug #18306: server crash on delete using subquery.
2124
2115
#
2125
2116
 
2126
 
create table t1 (c int, key(c)) ENGINE=MyISAM;                              
 
2117
create table t1 (c int, key(c));                              
2127
2118
insert into t1 values (1142477582), (1142455969);
2128
 
create table t2 (a int, b int) ENGINE=MyISAM;
 
2119
create table t2 (a int, b int);
2129
2120
insert into t2 values (2, 1), (1, 0);
2130
2121
delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1;
2131
2122
drop table t1, t2;
2133
2124
#
2134
2125
# Bug#19077: A nested materialized derived table is used before being populated.
2135
2126
#
2136
 
create table t1 (i int, j bigint) ENGINE=MyISAM;
 
2127
create table t1 (i int, j bigint);
2137
2128
insert into t1 values (1, 2), (2, 2), (3, 2);
2138
2129
select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3;
2139
2130
drop table t1;
2141
2132
# 
2142
2133
# Bug#19700: subselect returning BIGINT always returned it as SIGNED
2143
2134
#
2144
 
CREATE TABLE t1 (i BIGINT) ENGINE=MyISAM;
 
2135
CREATE TABLE t1 (i BIGINT);
2145
2136
INSERT INTO t1 VALUES (10000000000000000); # > MAX SIGNED BIGINT 9323372036854775807
2146
2137
INSERT INTO t1 VALUES (1);
2147
2138
 
2148
 
CREATE TABLE t2 (i BIGINT) ENGINE=MyISAM;
 
2139
CREATE TABLE t2 (i BIGINT);
2149
2140
INSERT INTO t2 VALUES (10000000000000000); # same as first table
2150
2141
INSERT INTO t2 VALUES (1);
2151
2142
 
2168
2159
CREATE TABLE t1 (
2169
2160
  id bigint NOT NULL auto_increment,
2170
2161
  name varchar(255) NOT NULL,
2171
 
  PRIMARY KEY  (id))
2172
 
  ENGINE=MyISAM;
2173
 
 
 
2162
  PRIMARY KEY  (id)
 
2163
);
2174
2164
INSERT INTO t1 VALUES
2175
2165
  (1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
2176
2166
 
2178
2168
  id bigint NOT NULL auto_increment,
2179
2169
  mid bigint NOT NULL,
2180
2170
  date date NOT NULL,
2181
 
  PRIMARY KEY  (id))
2182
 
  ENGINE=MyISAM;
2183
 
 
 
2171
  PRIMARY KEY  (id)
 
2172
);
2184
2173
INSERT INTO t2 VALUES 
2185
2174
  (1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
2186
2175
  (4, 2, '2006-04-20'), (5, 1, '2006-05-01');
2210
2199
CREATE TABLE t1 (
2211
2200
  i1 int NOT NULL default '0',
2212
2201
  i2 int NOT NULL default '0',
2213
 
  t datetime,
2214
 
  PRIMARY KEY  (i1,i2,t))
2215
 
  ENGINE=MyISAM;
2216
 
 
 
2202
  t datetime NOT NULL default '0000-00-00 00:00:00',
 
2203
  PRIMARY KEY  (i1,i2,t)
 
2204
);
2217
2205
INSERT INTO t1 VALUES 
2218
2206
(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
2219
2207
(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
2226
2214
  i1 int NOT NULL default '0',
2227
2215
  i2 int NOT NULL default '0',
2228
2216
  t datetime default NULL,
2229
 
  PRIMARY KEY  (i1))
2230
 
  ENGINE=MyISAM;
2231
 
 
 
2217
  PRIMARY KEY  (i1)
 
2218
);
2232
2219
INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
2233
2220
 
2234
2221
EXPLAIN
2247
2234
# Bug#14654 : Cannot select from the same table twice within a UNION
2248
2235
# statement 
2249
2236
#
2250
 
CREATE TABLE t1 (i INT) ENGINE=MyISAM;
 
2237
CREATE TABLE t1 (i INT);
2251
2238
 
2252
2239
(SELECT i FROM t1) UNION (SELECT i FROM t1);
2253
2240
SELECT * FROM t1 WHERE NOT EXISTS 
2274
2261
#
2275
2262
# Bug #21540: Subqueries with no from and aggregate functions return 
2276
2263
#              wrong results
2277
 
CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
2278
 
CREATE TABLE t2 (a INT) ENGINE=MyISAM;
 
2264
CREATE TABLE t1 (a INT, b INT);
 
2265
CREATE TABLE t2 (a INT);
2279
2266
INSERT INTO t2 values (1);
2280
2267
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
2281
2268
SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
2298
2285
#             slow with big sort_buffer_size 
2299
2286
#
2300
2287
 
2301
 
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)) ENGINE=MyISAM;
 
2288
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
2302
2289
CREATE TABLE t2 (x int auto_increment, y int, z int,
2303
 
                 PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)) ENGINE=MyISAM;
 
2290
                 PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
2304
2291
disable_query_log;
2305
2292
set autocommit=0;
2306
2293
begin;
2338
2325
#             correlated and uncorrelated selects
2339
2326
#
2340
2327
 
2341
 
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int) ENGINE=MyISAM;
2342
 
CREATE TABLE t2 (c int) ENGINE=MyISAM;
 
2328
CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
 
2329
CREATE TABLE t2 (c int);
2343
2330
 
2344
2331
INSERT INTO t1 VALUES ('aa', 1);
2345
2332
INSERT INTO t2 VALUES (1);
2357
2344
                SELECT c from t2 WHERE c=t1.c);
2358
2345
 
2359
2346
INSERT INTO t2 VALUES (2);
2360
 
CREATE TABLE t3 (c int) ENGINE=MyISAM;
 
2347
CREATE TABLE t3 (c int);
2361
2348
INSERT INTO t3 VALUES (1);
2362
2349
 
2363
2350
SELECT * FROM t1
2379
2366
 
2380
2367
CREATE TABLE t1 (
2381
2368
  id_1 int NOT NULL,
2382
 
  t varchar(4) DEFAULT NULL)
2383
 
  ENGINE=MyISAM;
 
2369
  t varchar(4) DEFAULT NULL
 
2370
);
2384
2371
 
2385
2372
CREATE TABLE t2 (
2386
2373
  id_2 int NOT NULL,
2387
 
  t varchar(4) DEFAULT NULL)
2388
 
  ENGINE=MyISAM;
 
2374
  t varchar(4) DEFAULT NULL
 
2375
);
2389
2376
 
2390
2377
CREATE TABLE t1xt2 (
2391
2378
  id_1 int NOT NULL,
2392
 
  id_2 int NOT NULL)
2393
 
  ENGINE=MyISAM;
 
2379
  id_2 int NOT NULL
 
2380
);
2394
2381
 
2395
2382
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
2396
2383
 
2470
2457
# Bug #26728: derived table with concatanation of literals in select list
2471
2458
#  
2472
2459
 
2473
 
CREATE TABLE t1 (a int) ENGINE=MyISAM;
 
2460
CREATE TABLE t1 (a int);
2474
2461
INSERT INTO t1 VALUES (3), (1), (2);           
2475
2462
 
2476
2463
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
2482
2469
# Bug #27257: COUNT(*) aggregated in outer query
2483
2470
#  
2484
2471
 
2485
 
CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
2486
 
CREATE TABLE t2 (m int, n int) ENGINE=MyISAM;
 
2472
CREATE TABLE t1 (a int, b int);
 
2473
CREATE TABLE t2 (m int, n int);
2487
2474
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
2488
2475
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
2489
2476
 
2505
2492
# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument 
2506
2493
#  
2507
2494
 
2508
 
CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
2509
 
CREATE TABLE t2 (m int, n int) ENGINE=MyISAM;
 
2495
CREATE TABLE t1 (a int, b int);
 
2496
CREATE TABLE t2 (m int, n int);
2510
2497
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
2511
2498
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
2512
2499
 
2523
2510
#
2524
2511
# Bug#27321: Wrong subquery result in a grouping select
2525
2512
#
2526
 
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)) ENGINE=MyISAM;
 
2513
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
2527
2514
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
2528
2515
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
2529
2516
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
2561
2548
# Bug #27363: nested aggregates in outer, subquery / sum(select
2562
2549
# count(outer))
2563
2550
#
2564
 
CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 values (1),(1),(1),(1);
2565
 
CREATE TABLE t2 (x INT) ENGINE=MyISAM; INSERT INTO t1 values (1000),(1001),(1002);
 
2551
CREATE TABLE t1 (a INT); INSERT INTO t1 values (1),(1),(1),(1);
 
2552
CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002);
2566
2553
 
2567
2554
--error ER_INVALID_GROUP_FUNC_USE
2568
2555
SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
2584
2571
#
2585
2572
# Bug #27807: Server crash when executing subquery with EXPLAIN
2586
2573
#  
2587
 
CREATE TABLE t1 (a int, b int, KEY (a)) ENGINE=MyISAM; 
 
2574
CREATE TABLE t1 (a int, b int, KEY (a)); 
2588
2575
INSERT INTO t1 VALUES (1,1),(2,1);
2589
2576
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
2590
2577
DROP TABLE t1;
2593
2580
# Bug #28377: grouping query with a correlated subquery in WHERE condition
2594
2581
#  
2595
2582
 
2596
 
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)) ENGINE=MyISAM;
 
2583
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
2597
2584
INSERT INTO t1 VALUES
2598
2585
  (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
2599
 
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id)) ENGINE=MyISAM;
 
2586
CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
2600
2587
INSERT INTO t2 VALUES (7), (5), (1), (3);
2601
2588
 
2602
2589
SELECT id, st FROM t1 
2618
2605
#             over a grouping subselect
2619
2606
# 
2620
2607
 
2621
 
CREATE TABLE t1 (a int) ENGINE=MyISAM;
 
2608
CREATE TABLE t1 (a int);
2622
2609
 
2623
2610
INSERT INTO t1 VALUES (1), (2);
2624
2611
 
2634
2621
CREATE TABLE t1 (
2635
2622
  a varchar(255) default NULL,
2636
2623
  b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2637
 
  INDEX idx(a,b))
2638
 
  ENGINE=MyISAM;
2639
 
 
 
2624
  INDEX idx(a,b)
 
2625
);
2640
2626
CREATE TABLE t2 (
2641
 
  a varchar(255) default NULL)
2642
 
  ENGINE=MyISAM;
 
2627
  a varchar(255) default NULL
 
2628
);
2643
2629
 
2644
2630
INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');
2645
2631
INSERT INTO t1 SELECT * FROM t1;
2665
2651
# Bug #27333: subquery grouped for aggregate of outer query / no aggregate
2666
2652
# of subquery
2667
2653
#
2668
 
CREATE TABLE t1 (a INTEGER, b INTEGER) ENGINE=MyISAM;
2669
 
CREATE TABLE t2 (x INTEGER) ENGINE=MyISAM;
 
2654
CREATE TABLE t1 (a INTEGER, b INTEGER);
 
2655
CREATE TABLE t2 (x INTEGER);
2670
2656
INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
2671
2657
INSERT INTO t2 VALUES (1), (2);
2672
2658
 
2682
2668
DROP TABLE t1,t2;
2683
2669
 
2684
2670
# second test case from 27333
2685
 
CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
 
2671
CREATE TABLE t1 (a INT, b INT);
2686
2672
INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
2687
2673
 
2688
2674
# returns no rows, when it should
2692
2678
DROP TABLE t1;
2693
2679
 
2694
2680
#test cases from 29297
2695
 
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
2696
 
CREATE TABLE t2 (a INT) ENGINE=MyISAM;
 
2681
CREATE TABLE t1 (a INT);
 
2682
CREATE TABLE t2 (a INT);
2697
2683
INSERT INTO t1 VALUES (1),(2);
2698
2684
INSERT INTO t2 VALUES (1),(2);
2699
2685
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
2706
2692
# Bug #31884: Assertion + crash in subquery in the SELECT clause.
2707
2693
#
2708
2694
 
2709
 
CREATE TABLE t1 (a1 INT, a2 INT) ENGINE=MyISAM;
2710
 
CREATE TABLE t2 (b1 INT, b2 INT) ENGINE=MyISAM;
 
2695
CREATE TABLE t1 (a1 INT, a2 INT);
 
2696
CREATE TABLE t2 (b1 INT, b2 INT);
2711
2697
 
2712
2698
INSERT INTO t1 VALUES (100, 200);
2713
2699
INSERT INTO t1 VALUES (101, 201);
2721
2707
# Bug #30788: Inconsistent retrieval of char/varchar
2722
2708
#
2723
2709
 
2724
 
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10)) ENGINE=MyISAM;
 
2710
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
2725
2711
INSERT INTO t1 VALUES ('a', 'aa');
2726
2712
INSERT INTO t1 VALUES ('a', 'aaa');
2727
2713
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2730
2716
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2731
2717
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
2732
2718
 
2733
 
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)) ENGINE=MyISAM;
 
2719
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
2734
2720
INSERT INTO t2 SELECT * FROM t1;
2735
2721
CREATE INDEX I1 ON t2 (a);
2736
2722
CREATE INDEX I2 ON t2 (b);
2747
2733
# occasions
2748
2734
#
2749
2735
 
2750
 
CREATE TABLE t1(a INT, b INT) ENGINE=MyISAM;
 
2736
CREATE TABLE t1(a INT, b INT);
2751
2737
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
2752
2738
 
2753
2739
--error ER_BAD_FIELD_ERROR
2777
2763
# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
2778
2764
#
2779
2765
 
2780
 
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
2781
 
CREATE TABLE t2 (a INT) ENGINE=MyISAM;
 
2766
CREATE TABLE t1 (a INT);
 
2767
CREATE TABLE t2 (a INT);
2782
2768
 
2783
2769
INSERT INTO t1 VALUES (1),(2);
2784
2770
INSERT INTO t2 VALUES (1),(2);
2800
2786
CREATE TABLE t4 (
2801
2787
  f7 varchar(32) collate utf8_bin NOT NULL default '',
2802
2788
  f10 varchar(32) collate utf8_bin default NULL,
2803
 
  PRIMARY KEY  (f7))
2804
 
  ENGINE=MyISAM;
2805
 
 
 
2789
  PRIMARY KEY  (f7)
 
2790
);
2806
2791
INSERT INTO t4 VALUES(1,1), (2,null);
2807
2792
 
2808
2793
CREATE TABLE t2 (
2810
2795
  f2 varchar(50) collate utf8_bin default NULL,
2811
2796
  f3 varchar(10) collate utf8_bin default NULL,
2812
2797
  PRIMARY KEY  (f4),
2813
 
  UNIQUE KEY uk1 (f2))
2814
 
  ENGINE=MyISAM;
2815
 
 
 
2798
  UNIQUE KEY uk1 (f2)
 
2799
);
2816
2800
INSERT INTO t2 VALUES(1,1,null), (2,2,null);
2817
2801
 
2818
 
CREATE TABLE t1  (
 
2802
CREATE TABLE t1 (
2819
2803
  f8 varchar(32) collate utf8_bin NOT NULL default '',
2820
2804
  f1 varchar(10) collate utf8_bin default NULL,
2821
2805
  f9 varchar(32) collate utf8_bin default NULL,
2822
 
  PRIMARY KEY  (f8))
2823
 
  ENGINE=MyISAM;
2824
 
 
 
2806
  PRIMARY KEY  (f8)
 
2807
);
2825
2808
INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2);
2826
2809
 
2827
2810
CREATE TABLE t3 (
2828
2811
  f6 varchar(32) collate utf8_bin NOT NULL default '',
2829
2812
  f5 varchar(50) collate utf8_bin default NULL,
2830
 
  PRIMARY KEY (f6))
2831
 
  ENGINE=MyISAM;
2832
 
 
 
2813
  PRIMARY KEY (f6)
 
2814
);
2833
2815
INSERT INTO t3 VALUES (1,null), (2,null);
2834
2816
 
2835
2817
SELECT
2859
2841
#
2860
2842
 
2861
2843
create table t_out (subcase char(3),
2862
 
                    a1 char(2), b1 char(2), c1 char(2)) ENGINE=MyISAM;
2863
 
create table t_in  (a2 char(2), b2 char(2), c2 char(2)) ENGINE=MyISAM;
 
2844
                    a1 char(2), b1 char(2), c1 char(2));
 
2845
create table t_in  (a2 char(2), b2 char(2), c2 char(2));
2864
2846
 
2865
2847
insert into t_out values ('A.1','2a', NULL, '2a');
2866
2848
#------------------------- A.2 - impossible
3034
3016
#
3035
3017
# Bug#20835 (literal string with =any values)
3036
3018
#
3037
 
CREATE TABLE t1 (s1 char(1)) ENGINE=MyISAM;
 
3019
CREATE TABLE t1 (s1 char(1));
3038
3020
INSERT INTO t1 VALUES ('a');
3039
 
SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);
 
3021
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
3040
3022
DROP TABLE t1;
3041
3023
 
3042
3024
#
3043
3025
# Bug#33204: INTO is allowed in subselect, causing inconsistent results
3044
3026
#
3045
 
CREATE TABLE t1( a INT ) ENGINE=MyISAM;
 
3027
CREATE TABLE t1( a INT );
3046
3028
INSERT INTO t1 VALUES (1),(2);
3047
3029
 
3048
 
CREATE TABLE t2( a INT, b INT ) ENGINE=MyISAM;
 
3030
CREATE TABLE t2( a INT, b INT );
3049
3031
 
3050
3032
--error ER_PARSE_ERROR
3051
3033
SELECT *