oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: link.zt
5
# owner group: sql4
6
# tags: optimizer
7
# description: 子查询结果集
8
--disable_info
9
--disable_metadata
10
--disable_warnings
11
create database if not exists test;
12
use test;
13
drop table if exists t1, t2, t3;
14
--enable_warnings
15
create table t1(c1 int primary key, c2 int);
16
create table t2(c1 int primary key, c2 int);
17
create table t3(c1 int primary key, c2 int);
18
insert into t1 values(1, 1), (2, 2), (3, 3), (4, 3), (5, 2);
19
insert into t2 values(1, 1), (2, 2);
20
insert into t3 values(5, 5), (6, 6);
21

22
###where subquery
23
select * from t1 where c1>ANY(select c1 from t2);
24
select * from t1 where c1<ALL(select c1 from t2);
25
--error 1242
26
select * from t1 where c1=(select c1 from t2);
27
select * from t1 where c1=(select c1 from t2 limit 1);
28
select * from t1 where c1=ANY(select c1 from t2 where t1.c2>t2.c2);
29
select * from t1 where exists(select c1 from t2 where t1.c2>t2.c2);
30
select * from t1 where 1<ANY(select c1 from t2);
31
select c2 from t1 where exists(select c2 from t2 where t1.c1>t2.c1);
32
select * from t1 where c1>(select c1 from t2 where t2.c1=1);
33
select * from t1 where exists(select * from t2 where t1.c1=t2.c2);
34
select * from t1 where c1 in (select c1 from t1);
35
select * from t1 where c1 not in (select c1 from t1);
36
select c1 from t1 where c1 not in (select c1 from t2 where c2 not in (select c2 from t2));
37

38
#from-subquery
39
select * from (select * from t1 limit 1) t;
40
select c from (select c1 as c from t1) tt;
41
select c1 from t1 where c1 in (select c1 from t2 where c2 >= some(select max(c1) from (select c1 from t3 where t1.c2=t3.c1 order by c1 limit 1) as tt));
42

43

44
#select subquery
45
--error 1241
46
select (select c1, c2 from t1) from t1;
47
--error 1242
48
select (select c1 from t1) from t1;
49
select (select c1 from t1 where c1=1) from t1;
50
select (select 1)=ANY(select 1);
51
SELECT (SELECT 1)>ALL(SELECT 1);
52
SELECT (SELECT 1,2,3) = ROW(1,2,3);
53
SELECT (SELECT 1,2,3) = ROW(1,2,1);
54
SELECT (SELECT 1,2,3) < ROW(1,2,1);
55
SELECT (SELECT 1,2,3) > ROW(1,2,1);
56
SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
57
SELECT ROW(1,2,3) = (SELECT 1,2,3);
58
SELECT ROW(1,2,3) = (SELECT 1,2,1);
59
SELECT ROW(1,2,3) < (SELECT 1,2,1);
60
SELECT ROW(1,2,3) > (SELECT 1,2,1);
61
SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
62
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
63
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
64
SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
65
SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
66
#row compare
67
#greater than
68
select (select 3, 2, 1)>row(1, 2, 1);
69
select (select 1, 2, 3)>row(1, 2, 1);
70
select (select 1, 2, 3)>row(1, 2, 3);
71
select (select 1, 3, 2)>row(1, 2, 3);
72
select (select 1, 2, 3)>row(1, 3, 2);
73
select (select 1, null, 2)>row(1, 2, 2);
74
select (select 1, 2, null)>row(1, 1, 2);
75
#greater equal
76
select (select 3, 2, 1)>=row(1, 2, 1);
77
select (select 1, 2, 3)>=row(1, 2, 1);
78
select (select 1, 2, 3)>=row(1, 2, 3);
79
select (select 1, 3, 2)>=row(1, 2, 3);
80
select (select 1, 2, 3)>=row(1, 3, 2);
81
select (select 1, null, 2)>=row(1, 2, 2);
82
select (select 1, 2, null)>=row(1, 1, 2);
83
#less than
84
select (select 1, 2, 3)<row(3, 2, 1);
85
select (select 1, 2, 3)<row(1, 3, 2);
86
select (select 1, 2, 1)<row(1, 2, 3);
87
select (select 1, 2, 3)<row(1, 2, 3);
88
select (select 1, 3, 2)<row(1, 2, 3);
89
select (select 1, null, 2)<row(1, 2, 2);
90
select (select 1, 2, null)<row(1, 1, 2);
91
#less equal
92
select (select 1, 2, 3)<=row(3, 2, 1);
93
select (select 1, 2, 3)<=row(1, 3, 2);
94
select (select 1, 2, 1)<=row(1, 2, 3);
95
select (select 1, 2, 3)<=row(1, 2, 3);
96
select (select 1, 3, 2)<=row(1, 2, 3);
97
select (select 1, null, 2)<=row(1, 2, 2);
98
select (select 1, 2, null)<=row(1, 1, 2);
99
#equal
100
select (select 1, 2, 3)=row(1, 2, 3);
101
select (select 1, 2, 3)=row(1, 2, 1);
102
select (select 1, 2, 1)=row(3, 2, 1);
103
select (select 1, null, 1)=row(1, 2, 1);
104
#ns equal
105
select (select 1, 2, 3)<=>row(1, 2, 3);
106
select (select 1, 2, 3)<=>row(1, 2, 1);
107
select (select 1, null, 1)<=>row(1, 2, 1);
108
select (select 1, null, 3)<=>row(1, null, 3);
109
#not equal
110
select (select 1, 2, 3)!=row(1, 2, 3);
111
select (select 1, 2, 3)!=row(1, 2, 1);
112
select (select 1, null, 1)!=row(1, 2, 1);
113
select (select 1, null, 3)!=row(1, null, 3);
114

115
#group by subquery
116
--sorted_result
117
select * from t1 group by (select c1 from t1 limit 1);
118
--error 1241
119
select * from t1 group by (select c1, c2 from t1);
120

121
#test ANY/SOME/ALL keywords
122
delete from t1;
123
insert into t1 values(1, NULL), (2, NULL), (3, NULL), (4, NULL);
124
select NULL=ANY(select c1 from t1);
125
select NULL=ALL(select c1 from t1);
126
delete from t1;
127
insert into t1 values(1, NULL), (2, 2), (3, 3), (4, 4);
128
select 2=ANY(select c2 from t1);
129
select 2=ALL(select c2 from t1);
130
#return NULL
131
select 5=ANY(select c2 from t1);
132
#return NULL
133
select 5!=ANY(select c2 from t1);
134
delete from t1;
135
insert into t1 values(1, 1), (2, NULL), (3, 3), (4, 4);
136
select 1=ANY(select c1 from t1);
137
select 1!=ANY(select c1 from t1);
138
select 1=ALL(select c1 from t1);
139
select 1!=ALL(select c1 from t1);
140
select 5=ANY(select c1 from t1);
141
select 5!=ANY(select c1 from t1);
142
select 5=ALL(select c1 from t1);
143
select 5!=ALL(select c1 from t1);
144
delete from t1;
145
insert into t1 values(1, 1), (2, 2), (3, 3), (4, NULL);
146
select 1!=ANY(select c1 from t1);
147
select 1=ANY(select c1 from t1);
148
select 1!=ALL(select c1 from t1);
149
select 1=ALL(select c1 from t1);
150
select 5!=ANY(select c1 from t1);
151
select 5=ANY(select c1 from t1);
152
select 5!=ALL(select c1 from t1);
153
select 5=ALL(select c1 from t1);
154

155
### test for related subquery with one column expr ###
156
select * from t1 where c1 > (select t2.c1 from t2 where t1.c1  limit 1);
157
select t1.c1 from t1 left join t3 on t1.c1 = t3.c1 where t3.c1 < (select t2.c1 from t2 where t3.c1  limit 1);
158
select * from t1 where c1 > (select t2.c1 from t2 where t1.c1 > 1 limit 1);
159

160
#
161
drop table if exists t1, t2;
162
create table t1 (a int not null, b int not null, index it1a(a), index iab(a, b));
163
create table t2 (c int not null, d int not null);
164
insert into t1 values (1,10);
165
insert into t1 values (1,20);
166
insert into t1 values (2,10);
167
insert into t1 values (2,20);
168
insert into t1 values (2,30);
169
insert into t1 values (3,20);
170
insert into t1 values (4,40);
171
insert into t2 values (2,10);
172
insert into t2 values (2,20);
173
insert into t2 values (2,40);
174
insert into t2 values (3,20);
175
insert into t2 values (4,10);
176
insert into t2 values (5,10);
177
--sorted_result
178
select a from t1 group by a having a in (select c from t2 where d >= 20);
179

180
drop table if exists t1;
181
create table t1(c1 int);
182
select count(1) from t1 where false;
183
select count(1) from t1 having false;
184
--sorted_result
185
select count(1) from t1 group by c1 having false;
186
--sorted_result
187
select count(1) from t1 where false group by c1;
188
insert into t1 values(1);
189
select * from t1 where c1 in (trim((select 1)), 2);
190
select * from t1 where c1 in ((select c1 from t1), 2);
191
select * from t1 where c1 in ((select t1.c1 from t1 t), 2);
192

193
#clean tables
194
--disable_warnings
195
drop table if exists t1, t2;
196
--enable_warnings
197

198
#project/81079/issue/11488676?akProjectId=81079&akProjectId=81079&
199
--disable_warnings
200
drop table if exists DD,AA,J,CC,GG;
201
--enable_warnings
202
CREATE TABLE DD ( col_int int, pk int, col_varchar_20 varchar(20), col_int_key int, primary key (pk));
203
CREATE TABLE AA ( pk int, col_int_key int, col_varchar_20 varchar(20), col_int int, primary key (pk));
204
CREATE TABLE J (  col_varchar_20 varchar(20), pk int primary key);
205
CREATE TABLE CC ( col_int_key int, pk int, col_int int, col_varchar_20 varchar(20), primary key (pk));
206
CREATE TABLE GG (col_varchar_20 varchar(20), pk int primary key);
207
insert into DD (pk, col_varchar_20, col_int, col_int_key) value(1, "YYY", 5,3);
208
insert into AA (pk, col_varchar_20, col_int, col_int_key) value(2, 'me', 4, 3);
209
insert into CC (pk, col_varchar_20, col_int) value(3, 'you', 4);
210
SELECT  GRANDPARENT1 . col_varchar_20 AS G1 FROM DD AS GRANDPARENT1 WHERE GRANDPARENT1 . `col_int_key` IN ( SELECT DISTINCT PARENT1 . `col_int_key` AS P1 FROM AA AS PARENT1 LEFT JOIN J AS PARENT2 USING ( col_varchar_20 ) WHERE PARENT1 . `col_int` IN ( SELECT  CHILD1 . `col_int` AS C1 FROM CC AS CHILD1 LEFT JOIN GG AS CHILD2 USING ( col_varchar_20 )  )  AND ( GRANDPARENT1 . `col_int` >= 1 ));
211
--disable_warnings
212
drop table if exists DD,AA,J,CC,GG;
213
--enable_warnings
214

215
#extract query range from push-down filter
216
#
217
--disable_warnings
218
drop table if exists t1, t2, t3;
219
--enable_warnings
220
create table t1(c1 int primary key, c2 int, c3 int);
221
create table t2(c1 int primary key, c2 int, c3 int);
222
create table t3(c1 int primary key, c2 int, c3 int);
223
insert into t1 values(1, 2, 3);
224
insert into t1 values(2, 2, 3);
225
insert into t2 values(1, 2, 3);
226
insert into t2 values(2, 2, 3);
227
insert into t3 values(1, 2, 3);
228
insert into t3 values(2, 2, 3);
229
explain select * from t1 where t1.c2 = 5 or exists (select 1 from t2 where t1.c1 = t2.c1);
230
explain select * from t1 where t1.c2 = 5 or exists (select 1 from t2 where t1.c1 > t2.c1);
231
explain select * from t1 where t1.c2 = 5 or exists (select 1 from t2 where t1.c1 < t2.c1);
232
explain select * from t1, t2 where t1.c1 > exists(select c1 from t2 where t2.c1 = t1.c1);
233
explain select * from t1 where (select c1 from t2 limit 1)+1 in (select 2 from t3 where t1.c1=t3.c1);
234
explain select * from t1 having count(*) > (select c1 from t2 where t1.c1=t2.c1);
235
explain select * from t1, t2 where t2.c1 = t1.c1 and t2.c1 = (select c1 from t3 where t3.c1 = t1.c1);
236
explain select * from (select c1+1 as a1 from t1 where t1.c2 = 2) a, t2 where a.a1 = t2.c2 or t2.c1 = ANY(select c3 from t3 where t3.c1 > a.a1);
237
select * from t1 where t1.c2 = 5 or exists (select 1 from t2 where t1.c1 = t2.c1);
238
select * from t1 where t1.c2 = 5 or exists (select 1 from t2 where t1.c1 > t2.c1);
239
select * from t1 where t1.c2 = 5 or exists (select 1 from t2 where t1.c1 < t2.c1);
240
select * from t1, t2 where t1.c1 > exists(select c1 from t2 where t2.c1 = t1.c1);
241
select * from t1 where (select c1 from t2 limit 1)+1 in (select 2 from t3 where t1.c1=t3.c1);
242
select * from t1 having count(*) > (select c1 from t2 where t1.c1=t2.c1);
243
select * from t1, t2 where t2.c1 = t1.c1 and t2.c1 = (select c1 from t3 where t3.c1 = t1.c1);
244
select * from (select c1+1 as a1 from t1 where t1.c2 = 2) a, t2 where a.a1 = t2.c2 or t2.c1 = ANY(select c3 from t3 where t3.c1 > a.a1);
245
#extract query range for multi plan operators
246
explain select * from t1 where t1.c1 > (select sum(c1) from t2 where t2.c1 = t1.c1);
247
explain select * from t1 where t1.c1 > (select sum(c1) from t2 where t2.c1 = t1.c1 and t2.c2 > (select max(c2) from t3 where t3.c1 = t2.c1));
248
explain select * from t1 where t1.c2 in (select avg(c1) from t2 where t2.c1 = t1.c1 union select count(1) from t3 where t3.c1 = t1.c1);
249
explain select * from t1 where t1.c1 != (select c2 from t2 where t2.c1 = (select max(c2) from t3 where t3.c1 = t1.c1) order by t2.c2 limit 1);
250
select * from t1 where t1.c1 > (select sum(c1) from t2 where t2.c1 = t1.c1);
251
select * from t1 where t1.c1 > (select sum(c1) from t2 where t2.c1 = t1.c1 and t2.c2 > (select max(c2) from t3 where t3.c1 = t2.c1));
252
select * from t1 where t1.c2 in (select avg(c1) from t2 where t2.c1 = t1.c1 union select count(1) from t3 where t3.c1 = t1.c1);
253
select * from t1 where t1.c1 != (select c2 from t2 where t2.c1 = (select max(c2) from t3 where t3.c1 = t1.c1) order by t2.c2 limit 1);
254
--disable_warnings
255
drop table if exists t1, t2, t3;
256

257
#issue/9168337
258
#subquery in [not]exists could be eliminated or refined
259
--disable_warnings
260
drop table if exists t1,t2,t3;
261
--enable_warnings
262
create table t1(a int, b int);
263
create table t2(a int, b int);
264
create table t3(a int, b int);
265
#basic exists subquery with aggr, group by, having
266
explain select * from t1 where exists (select 1, round(1.1) from dual);
267
explain select * from t1 where exists (select max(a) from t2);
268
explain select * from t1 where exists (select group_concat(a, b) from t2 group by a);
269
explain select * from t1 where exists (select max(a), sum(a), count(a) from t2);
270
explain select * from t1 where exists (select max(a), sum(a), count(a) from t2 group by a);
271
explain select * from t1 where exists (select max(a), sum(a), count(a) from t2 group by a having a > 1);
272
explain select * from t1 where exists (select max(a), sum(a), count(a) from t2 group by a having sum(a) > 1);
273
explain select * from t1 where exists (select max(a), sum(a), count(a) from t2 group by a having sum(a) > 1 and count(a) > 0);
274
#basic not exists subquery with aggr, group by, having
275
explain select * from t1 where not exists (select 1, round(1.1) from dual);
276
explain select * from t1 where not exists (select max(a) from t2);
277
explain select * from t1 where not exists (select group_concat(a, b) from t2 group by a);
278
explain select * from t1 where not exists (select max(a), sum(a), count(a) from t2);
279
explain select * from t1 where not exists (select max(a), sum(a), count(a) from t2 group by a);
280
explain select * from t1 where not exists (select max(a), sum(a), count(a) from t2 group by a having a > 1);
281
explain select * from t1 where not exists (select max(a), sum(a), count(a) from t2 group by a having sum(a) > 1);
282
explain select * from t1 where not exists (select max(a), sum(a), count(a) from t2 group by a having sum(a) > 1 and count(a) > 0);
283
#mixed situations
284
explain select * from t1, (select * from t2 where exists(select sum(a) from t3)) t4 where t1.a = t4.a;
285
explain select * from t1 where t1.a in (select a from t2 where exists(select count(a) from t3));
286
explain select * from t1 where exists(select max(a) from t2 group by a having max(a) > 1);
287
explain select * from t1, (select * from t2 having a > 1) t4 where t1.a = t4.a;
288
explain select * from t1, (select * from t2 where exists(select sum(a) from t3 group by a having a > 1)) t4 where t1.a = t4.a;
289
explain select * from t1 where exists ( select max(a) from t2 group by a having exists (select * from t3 where t3.a > max(t2.a)));
290
explain select * from t1 where exists ( select sum(a) from t2 group by a having exists (select * from t3 where t3.a > sum(t2.a)));
291
#eliminate exists subquery in params
292
explain select * from t1 where b > exists(select b from t2 where b > exists(select b from t3));
293
explain select * from t1 where b > exists(select a from t2 where b > exists(select sum(a) from t3));
294
explain select * from t1 where b > exists(select sum(a) from t2 where t2.b > exists(select sum(a) from t3));
295

296
## bug: 
297
--disable_warnings
298
drop table if exists T;
299
--enable_warnings
300
create table T (pk int);
301
explain select pk from t table1 where (table1.pk >= (select 1 from T limit 1)) AND ((1,9) in (select 1,9));
302

303
## bug: 
304
explain select (select 1 except select pk),
305
        (select 1 except select pk),
306
        (select 1 except select pk),
307
        (select 1 except select pk),
308
        (select 1 except select pk),
309
        (select 1 except select pk),
310
        (select 1 except select pk),
311
        (select 1 except select pk),
312
        (select 1 except select pk),
313
        (select 1 except select pk),
314
        (select 1 except select pk),
315
        (select 1 except select pk),
316
        (select 1 except select pk),
317
        (select 1 except select pk),
318
        (select 1 except select pk),
319
        (select 1 except select pk),
320
        (select 1 except select pk),
321
        (select 1 except select pk),
322
        (select 1 except select pk),
323
        (select 1 except select pk),
324
        (select 1 except select pk),
325
        (select 1 except select pk),
326
        (select 1 except select pk),
327
        (select 1 except select pk),
328
        (select 1 except select pk),
329
        (select 1 except select pk),
330
        (select 1 except select pk),
331
        (select 1 except select pk)
332
  from t;
333
select (select 1 except select pk),
334
       (select 1 except select pk),
335
       (select 1 except select pk),
336
       (select 1 except select pk),
337
       (select 1 except select pk),
338
       (select 1 except select pk),
339
       (select 1 except select pk),
340
       (select 1 except select pk),
341
       (select 1 except select pk),
342
       (select 1 except select pk),
343
       (select 1 except select pk),
344
       (select 1 except select pk),
345
       (select 1 except select pk),
346
       (select 1 except select pk),
347
       (select 1 except select pk),
348
       (select 1 except select pk),
349
       (select 1 except select pk),
350
       (select 1 except select pk),
351
       (select 1 except select pk),
352
       (select 1 except select pk),
353
       (select 1 except select pk),
354
       (select 1 except select pk),
355
       (select 1 except select pk),
356
       (select 1 except select pk),
357
       (select 1 except select pk),
358
       (select 1 except select pk),
359
       (select 1 except select pk),
360
       (select 1 except select pk)
361
  from t;
362

363
--disable_warnings
364
drop table if exists t1,t2,t3;
365
--enable_warnings
366

367
--echo #
368
CREATE TABLE `t1` (
369
  `c1` varbinary(20) DEFAULT NULL
370
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0;
371
CREATE TABLE `t2` (
372
  `a` int(11) NOT NULL,
373
  `b` varchar(20) DEFAULT NULL,
374
  `c` decimal(20,10) DEFAULT NULL,
375
  PRIMARY KEY (`a`)
376
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
377
 partition by hash(a) partitions 2;
378

379
insert into t1 values(1), (2);
380
insert into t2 values(1,2,1);
381

382
select /*+no_rewrite*/(select sum(b) from t2) as temp from t1 group by temp having temp > 4 and temp > (select sum(b) from t2);
383

384
drop table t1;
385
drop table t2;
386
--echo #
387
CREATE TABLE `t1` (
388
  `pk1` int(11) NOT NULL,
389
  `pk2` varchar(10) NOT NULL,
390
  `c1` int(11) DEFAULT NULL,
391
  `c2` int(11) DEFAULT NULL,
392
  `c3` varchar(20) DEFAULT NULL,
393
  `c4` timestamp(6) NULL DEFAULT '2012-01-01 04:00:00.000000',
394
  `c5` int(11) DEFAULT NULL,
395
  PRIMARY KEY (`pk1`, `pk2`)
396
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0;
397
insert into t1 values(1, '2', 3, 3, null, null, null);
398
select /*+no_rewrite*/ c1, (select max(c2) FROM t1 ) a FROM t1  order by (select c1 FROM t1  where c2 in (select c1 FROM t1  where c2 = a)) + 1;
399
drop table t1 ;
400

401
# bug: 
402
--disable_warnings
403
drop table if exists table0_hash_pk_parts_1_bigint;
404
drop table if exists s;
405
drop table if exists ff;
406
--enable_warnings
407

408
CREATE TABLE table0_hash_pk_parts_1_bigint (
409
col_timestamp_6_index timestamp(6) NULL DEFAULT NULL,
410
col_varchar_20 varchar(20),
411
col_char_20 char(20),
412
col_decimal_20_0_key decimal(20,0),
413
pk bigint,
414
col_char_20_index char(20),
415
col_bigint_key bigint,
416
col_bigint bigint,
417
col_timestamp_6 timestamp(6) NULL DEFAULT NULL,
418
col_varchar_20_key varchar(20),
419
col_bigint_index bigint,
420
col_decimal_20_0_index decimal(20,0),
421
col_decimal_20_0 decimal(20,0),
422
col_char_20_key char(20),
423
col_varchar_20_index varchar(20),
424
col_timestamp_6_key timestamp(6) NULL DEFAULT NULL,
425
/*Indices*/
426
index idx14(pk, col_timestamp_6_index ),
427
key idx1(pk, col_decimal_20_0_key ),
428
primary key (pk) ,
429
index idx8(pk, col_char_20_index ),
430
key idx4(pk, col_bigint_key ),
431
key idx10(pk, col_varchar_20_key ),
432
index idx5(pk, col_bigint_index ),
433
index idx2(pk, col_decimal_20_0_index ),
434
key idx7(pk, col_char_20_key ),
435
index idx11(pk, col_varchar_20_index ),
436
key idx13(pk, col_timestamp_6_key ))  PARTITION BY hash (pk) partitions 1;
437

438
CREATE TABLE S (
439
col_varchar_20_key varchar(20),
440
col_varchar_10 varchar(10),
441
col_varchar_20 varchar(20),
442
col_date date,
443
col_datetime datetime,
444
col_int int,
445
col_date_key date,
446
col_int_key int,
447
pk int,
448
col_datetime_key datetime,
449
col_varchar_10_key varchar(10),
450
/*Indices*/
451
key idx5(pk, col_varchar_20_key ),
452
key idx7(pk, col_date_key ),
453
key idx1(pk, col_int_key ),
454
primary key (pk) ,
455
key idx9(pk, col_datetime_key ),
456
key idx3(pk, col_varchar_10_key ));
457

458
CREATE TABLE FF (
459
col_varchar_20 varchar(20),
460
col_varchar_10_key varchar(10),
461
col_int int,
462
col_datetime datetime,
463
col_date_key date,
464
col_varchar_20_key varchar(20),
465
col_varchar_10 varchar(10),
466
col_datetime_key datetime,
467
pk int,
468
col_int_key int,
469
col_date date,
470
/*Indices*/
471
key idx3(pk, col_varchar_10_key ),
472
key idx7(pk, col_date_key ),
473
key idx5(pk, col_varchar_20_key ),
474
key idx9(pk, col_datetime_key ),
475
primary key (pk) ,
476
key idx1(pk, col_int_key ));
477

478
explain
479
SELECT 1
480
FROM table0_hash_pk_parts_1_bigint
481
WHERE
482
(SELECT
483
 (SELECT MIN(
484
             (SELECT MAX(-100)
485
              FROM ff
486
              WHERE (col_timestamp_6 >= '2015-01-01')))
487
  FROM s
488
  WHERE pk IN
489
  (SELECT col_bigint
490
   FROM table0_hash_pk_parts_1_bigint))) < 1;
491

492
SELECT 1
493
FROM table0_hash_pk_parts_1_bigint
494
WHERE
495
(SELECT
496
 (SELECT MIN(
497
             (SELECT MAX(-100)
498
              FROM ff
499
              WHERE (col_timestamp_6 >= '2015-01-01')))
500
  FROM s
501
  WHERE pk IN
502
  (SELECT col_bigint
503
   FROM table0_hash_pk_parts_1_bigint))) < 1;
504

505
explain
506
SELECT 1
507
FROM table0_hash_pk_parts_1_bigint
508
WHERE
509
(SELECT
510
 (SELECT MIN(
511
             (SELECT 1
512
              FROM ff
513
              WHERE (col_timestamp_6 >= '2015-01-01'))) + 1 AS tmp
514
  FROM s
515
  WHERE pk IN
516
  (SELECT col_bigint
517
   FROM table0_hash_pk_parts_1_bigint)));
518

519
SELECT 1
520
FROM table0_hash_pk_parts_1_bigint
521
WHERE
522
(SELECT
523
 (SELECT MIN(
524
             (SELECT 1
525
              FROM ff
526
              WHERE (col_timestamp_6 >= '2015-01-01'))) + 1 AS tmp
527
  FROM s
528
  WHERE pk IN
529
  (SELECT col_bigint
530
   FROM table0_hash_pk_parts_1_bigint)));
531

532
drop table s;
533
drop table table0_hash_pk_parts_1_bigint;
534
drop table ff;
535