546
547
DROP PROCEDURE p2;
547
548
DROP PROCEDURE p3;
548
549
DROP PROCEDURE p4;
553
# BUG#35160 "Subquery optimization: table pullout is not reflected in EXPLAIN EXTENDED"
555
create table t0 (a int);
556
insert into t0 values (0),(1),(2),(3),(4);
558
create table t1 (a int, b int, key(a));
559
insert into t1 select a,a from t0;
561
create table t2 (a int, b int, primary key(a));
562
insert into t2 select * from t1;
564
# Table t2 should be pulled out because t2.a=t0.a equality
565
--echo Table t2, unlike table t1, should be displayed as pulled out
566
explain extended select * from t0
567
where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
570
drop table t0, t1, t2;
573
# BUG#35767: Processing of uncorrelated subquery with semi-join cause wrong result and crash
581
fid int(11) NOT NULL,
584
insert into t1 values(1);
585
insert into t2 values(1,7503),(2,1);
588
explain select count(*)
590
where fid IN (select fid from t2 where (id between 7502 and 8420) order by fid );
595
# BUG#36137 "virtual longlong Item_in_subselect::val_int(): Assertion `0' failed."
597
create table t1 (a int, b int, key (a), key (b));
598
insert into t1 values (2,4),(2,4),(2,4);
601
t1.a in (select 1 from t1 where t1.a in (select 1 from t1) group by t1.a);
605
# BUG#36128: not in subquery causes crash in cleanup..
607
create table t1(a int,b int,key(a),key(b));
608
insert into t1 values (1,1),(2,2),(3,3);
610
where t1.a not in (select 1 from t1
611
where t1.a in (select 1 from t1)
616
# BUG#33743 "nested subqueries, unique index, wrong result"
619
(EMPNUM CHAR(3) NOT NULL,
625
(PNUM CHAR(3) NOT NULL,
632
(EMPNUM CHAR(3) NOT NULL,
633
PNUM CHAR(3) NOT NULL,
636
INSERT INTO t1 VALUES ('E1','Alice',12,'Deale');
637
INSERT INTO t1 VALUES ('E2','Betty',10,'Vienna');
638
INSERT INTO t1 VALUES ('E3','Carmen',13,'Vienna');
639
INSERT INTO t1 VALUES ('E4','Don',12,'Deale');
640
INSERT INTO t1 VALUES ('E5','Ed',13,'Akron');
642
INSERT INTO t2 VALUES ('P1','MXSS','Design',10000,'Deale');
643
INSERT INTO t2 VALUES ('P2','CALM','Code',30000,'Vienna');
644
INSERT INTO t2 VALUES ('P3','SDP','Test',30000,'Tampa');
645
INSERT INTO t2 VALUES ('P4','SDP','Design',20000,'Deale');
646
INSERT INTO t2 VALUES ('P5','IRM','Test',10000,'Vienna');
647
INSERT INTO t2 VALUES ('P6','PAYR','Design',50000,'Deale');
649
INSERT INTO t3 VALUES ('E1','P1',40);
650
INSERT INTO t3 VALUES ('E1','P2',20);
651
INSERT INTO t3 VALUES ('E1','P3',80);
652
INSERT INTO t3 VALUES ('E1','P4',20);
653
INSERT INTO t3 VALUES ('E1','P5',12);
654
INSERT INTO t3 VALUES ('E1','P6',12);
655
INSERT INTO t3 VALUES ('E2','P1',40);
656
INSERT INTO t3 VALUES ('E2','P2',80);
657
INSERT INTO t3 VALUES ('E3','P2',20);
658
INSERT INTO t3 VALUES ('E4','P2',20);
659
INSERT INTO t3 VALUES ('E4','P4',40);
660
INSERT INTO t3 VALUES ('E4','P5',80);
664
CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM);
674
WHERE PTYPE = 'Design'));
676
DROP INDEX t1_IDX ON t1;
677
CREATE INDEX t1_IDX ON t1(EMPNUM);
687
WHERE PTYPE = 'Design'));
689
DROP INDEX t1_IDX ON t1;
699
WHERE PTYPE = 'Design'));
701
DROP TABLE t1, t2, t3;
704
# BUG#33245 "Crash on VIEW referencing FROM table in an IN clause"
706
CREATE TABLE t1 (f1 INT NOT NULL);
707
CREATE VIEW v1 (a) AS SELECT f1 IN (SELECT f1 FROM t1) FROM t1;
714
# BUG#35550 "Semi-join subquery in ON clause and no WHERE crashes the server"
716
create table t0 (a int);
717
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
719
create table t1(a int, b int);
720
insert into t1 values (0,0),(1,1),(2,2);
721
create table t2 as select * from t1;
723
create table t3 (pk int, a int, primary key(pk));
724
insert into t3 select a,a from t0;
727
select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3));
729
drop table t0, t1, t2, t3;
732
# BUG#34799: crash or/and memory overrun with dependant subquery and some joins
734
create table t1 (a int);
735
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
737
create table t2 (a char(200), b char(200), c char(200), primary key (a,b,c)) engine=innodb;
738
insert into t2 select concat(a, repeat('X',198)),repeat('B',200),repeat('B',200) from t1;
739
insert into t2 select concat(a, repeat('Y',198)),repeat('B',200),repeat('B',200) from t1;
740
alter table t2 add filler1 int;
742
insert into t1 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C;
744
set @save_join_buffer_size=@@join_buffer_size;
746
set join_buffer_size=1;
749
select * from t2 where filler1 in ( select a from t1);
750
set join_buffer_size=default;
754
# BUG#33509: Server crashes with number of recursive subqueries=61
755
# (the query may or may not fail with an error so we're using it with SP
757
create table t1 (a int not null);
760
drop procedure if exists p1;
765
CREATE PROCEDURE p1()
767
DECLARE EXIT HANDLER FOR SQLEXCEPTION select a from t1;
769
select a from t1 where a in (
770
select a from t1 where a in (
771
select a from t1 where a in (
772
select a from t1 where a in (
773
select a from t1 where a in (
774
select a from t1 where a in (
775
select a from t1 where a in (
776
select a from t1 where a in (
777
select a from t1 where a in (
778
select a from t1 where a in (
779
select a from t1 where a in (
780
select a from t1 where a in (
781
select a from t1 where a in (
782
select a from t1 where a in (
783
select a from t1 where a in (
784
select a from t1 where a in (
785
select a from t1 where a in (
786
select a from t1 where a in (
787
select a from t1 where a in (
788
select a from t1 where a in (
789
select a from t1 where a in (
790
select a from t1 where a in (
791
select a from t1 where a in (
792
select a from t1 where a in (
793
select a from t1 where a in (
794
select a from t1 where a in (
795
select a from t1 where a in (
796
select a from t1 where a in (
797
select a from t1 where a in (
798
select a from t1 where a in (
799
select a from t1 where a in (
800
select a from t1 where a in (
801
select a from t1 where a in (
802
select a from t1 where a in (
803
select a from t1 where a in (
804
select a from t1 where a in (
805
select a from t1 where a in (
806
select a from t1 where a in (
807
select a from t1 where a in (
808
select a from t1 where a in (
809
select a from t1 where a in (
810
select a from t1 where a in (
811
select a from t1 where a in (
812
select a from t1 where a in (
813
select a from t1 where a in (
814
select a from t1 where a in (
815
select a from t1 where a in (
816
select a from t1 where a in (
817
select a from t1 where a in (
818
select a from t1 where a in (
819
select a from t1 where a in (
820
select a from t1 where a in (
821
select a from t1 where a in (
822
select a from t1 where a in (
823
select a from t1 where a in (
824
select a from t1 where a in (
825
select a from t1 where a in (
826
select a from t1 where a in (
827
select a from t1 where a in (
828
select a from t1 where a in (
829
select a from t1 where a in (
830
select a from t1 where a in ( select a from t1)
831
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))';