1#
2# DuplicateElimination strategy test
3#
4
5-- disable_query_log
6-- disable_result_log
7SET GLOBAL innodb_stats_persistent=0;
8-- enable_result_log
9-- enable_query_log
10
11create table t0 (a int);
12insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
13                      (10),(12),(14),(16),(18);
14
15# First test simple cases: I20 order, no join buffering.
16
17create table t1 (
18  a int,
19  b int
20) engine=innodb;
21insert into t1 values (1,1),(1,1),(2,2);
22
23create table t2 (
24  a int,
25  b int,
26  key(b)
27) engine=innodb;
28insert into t2 select a, a/2 from t0;
29
30# Run analyze to ensure more correct index statistics
31-- disable_query_log
32-- disable_result_log
33ANALYZE TABLE t1,t2;
34-- enable_result_log
35-- enable_query_log
36
37select * from t1;
38select * from t2;
39explain select * from t2 where b in (select a from t1);
40select * from t2 where b in (select a from t1);
41
42truncate table t0;
43insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
44
45# Try an InnoDB table with very long rowid
46create table t3 (
47   a int,
48   b int,
49   key(b),
50   pk1 char(200), pk2 char(200), pk3 char(200),
51   primary key(pk1, pk2, pk3)
52) engine=innodb;
53insert into t3 select a,a, a,a,a from t0;
54
55explain select * from t3 where b in (select a from t1);
56select * from t3 where b in (select a from t1);
57
58# Test overflow to MyISAM:
59set @save_max_heap_table_size= @@max_heap_table_size;
60set max_heap_table_size=16384;
61set @save_join_buffer_size = @@join_buffer_size;
62set join_buffer_size= 8192;
63
64drop table t3;
65create table t3 (
66   a int,
67   b int,
68   key(b),
69   pk1 char(200), pk2 char(200),
70   primary key(pk1, pk2)
71) engine=innodb;
72insert into t3 select
73  A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
74from t0 A, t0 B where B.a <5;
75
76--replace_column 10 #
77explain select * from t3 where b in (select a from t0);
78--sorted_result
79select * from t3 where b in (select a.a+b.a from t0 a, t0 b where b.a<5);
80
81set join_buffer_size= @save_join_buffer_size;
82set max_heap_table_size= @save_max_heap_table_size;
83
84# O2I join orders, with shortcutting:
85explain select * from t1 where a in (select b from t2);
86select * from t1;
87select * from t1 where a in (select b from t2);
88
89drop table t0, t1, t2, t3;
90# (no need for anything in range/index_merge/DS-MRR)
91
92#
93# BUG#34799: crash or/and memory overrun with dependant subquery and some joins
94#
95create table t1 (a int);
96insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
97
98create table t2 (a char(200), b char(200), c char(200), primary key (a,b,c)) engine=innodb;
99insert into t2 select concat(a, repeat('X',198)),repeat('B',200),repeat('B',200) from t1;
100insert into t2 select concat(a, repeat('Y',198)),repeat('B',200),repeat('B',200) from t1;
101alter table t2 add filler1 int;
102
103insert into t1 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C;
104
105set @save_join_buffer_size=@@join_buffer_size;
106--disable_warnings
107set join_buffer_size=1;
108--enable_warnings
109
110select * from t2 where filler1 in ( select a from t1);
111set join_buffer_size=default;
112
113drop table t1, t2;
114
115
116--echo
117--echo BUG#42740: crash in optimize_semijoin_nests
118--echo
119create table t1 (c6 timestamp,key (c6)) engine=innodb;
120create table t2 (c2 double) engine=innodb;
121explain select 1 from t2 where c2 = any (select log10(null) from t1 where c6 <null)  ;
122drop table t1, t2;
123
124--echo #
125--echo # BUG#42742: crash in setup_sj_materialization, Copy_field::set
126--echo #
127create table t3 ( c1 year) engine=innodb;
128insert into t3 values (2135),(2142);
129create table t2 (c1 tinytext,c2 text,c6 timestamp) engine=innodb;
130-- echo # The following must not crash, EXPLAIN should show one SJ strategy, not a mix:
131explain select 1 from t2 where
132  c2 in (select 1 from t3, t2) and
133  c1 in (select convert(c6,char(1)) from t2);
134drop table t2, t3;
135--echo #
136--echo # BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
137--echo #
138CREATE TABLE t1 (
139   i INT
140) ENGINE=InnoDB;
141INSERT INTO t1 VALUES (2),(4);
142
143CREATE TABLE t2 (
144   i INT,
145   vc VARCHAR(1)
146) ENGINE=InnoDB;
147INSERT INTO t2 VALUES (8,NULL);
148
149SELECT i
150FROM t1
151WHERE i IN (SELECT innr.i
152             FROM t2 LEFT JOIN t2 innr ON innr.vc)
153   AND i = 2;
154
155DROP TABLE t1, t2;
156
157-- disable_query_log
158-- disable_result_log
159SET GLOBAL innodb_stats_persistent=default;
160-- enable_result_log
161-- enable_query_log
162