/
select.result
485 lines (485 loc) · 16.9 KB
/
select.result
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
DROP TABLE IF EXISTS t;
CREATE TABLE t (
c1 int,
c2 int,
c3 int,
PRIMARY KEY (c1)
);
INSERT INTO t VALUES (1,2,3);
set session tidb_hashagg_partial_concurrency = 1;
set session tidb_hashagg_final_concurrency = 1;
SELECT * from t;
c1 c2 c3
1 2 3
SELECT c1, c2, c3 from t;
c1 c2 c3
1 2 3
SELECT c1, c1 from t;
c1 c1
1 1
SELECT c1 as a, c2 as a from t;
a a
1 2
SELECT 1;
1
1
SELECT 1, 1;
1 1
1 1
SET @@autocommit = 1;
SELECT @@autocommit;
@@autocommit
1
SELECT @@autocommit, @@autocommit;
@@autocommit @@autocommit
1 1
SET @a = 10;
SET @b = 11;
SELECT @a, @@autocommit;
@a @@autocommit
10 1
SELECT @a, @b;
@a @b
10 11
SELECT 1, @a;
1 @a
1 10
SELECT 1, @a as a;
1 a
1 10
SELECT 1, @a, @@autocommit as a, c1 from t;
1 @a a c1
1 10 1 1
SET @b = "123";
SELECT @b + "123";
@b + "123"
246
SELECT 1 + 1;
1 + 1
2
SELECT 1 a, 1 as a, 1 + 1 a;
a a a
1 1 2
SELECT c1 a, c1 as a from t;
a a
1 1
SELECT * from t LIMIT 0,1;
c1 c2 c3
1 2 3
SELECT * from t LIMIT 1;
c1 c2 c3
1 2 3
SELECT * from t LIMIT 1,1;
c1 c2 c3
SELECT * from t LIMIT 1 OFFSET 0;
c1 c2 c3
1 2 3
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
c1 int,
c2 int,
PRIMARY KEY (c1)
);
INSERT INTO t2 VALUES (1,2);
SELECT * from t a;
c1 c2 c3
1 2 3
SELECT * from t a, t2 b;
c1 c2 c3 c1 c2
1 2 3 1 2
SELECT * from t as a, t2 as b;
c1 c2 c3 c1 c2
1 2 3 1 2
SELECT * from t a left join t2 b on a.c1 = b.c1;
c1 c2 c3 c1 c2
1 2 3 1 2
SELECT * from (SELECT 1, 1) as a;
Error 1060: Duplicate column name '1'
SELECT * from (SELECT * FROM t, t2) as a;
Error 1060: Duplicate column name 'c1'
DROP TABLE IF EXISTS t;
CREATE TABLE t (c1 INT, c2 INT);
INSERT INTO t VALUES (1, 2), (1, 1), (1, 3);
SELECT c1=c2 FROM t;
c1=c2
0
1
0
SELECT 1=1;
1=1
1
SELECT t.c1 + t.c2 from t limit 1;
t.c1 + t.c2
3
SELECT t.c1 from t limit 1;
c1
1
SELECT t.c1 + c2 from t limit 1;
t.c1 + c2
3
SELECT c1 + 10 from t limit 1;
c1 + 10
11
SELECT t.c1 + 10 from t limit 1;
t.c1 + 10
11
SELECT all c1, c2 from t limit 1;
c1 c2
1 2
SELECT distinct c1, c2 from t order by c1, c2 limit 1;
c1 c2
1 1
SELECT c2 from t where not (c2 > 2);
c2
2
1
select c2 from t where not null is null;
c2
select !(1 + 2);
!(1 + 2)
0
select + - 1, --1, +-+-+1, + "123";
+ - 1 --1 +-+-+1 123
-1 1 1 123
select --------------------1, ++++++++++++++++++++1;
--------------------1 1
1 1
select +(+(1)), (-+1), ((+1)), +1.23, +1e23, +1E23, +null, +true, +false, + ( ( 1 ) );
1 (-+1) 1 1.23 1e23 1E23 NULL TRUE FALSE 1
1 -1 1 1.23 1e23 1e23 NULL 1 0 1
select +
(
+
(
1
)
)
;
1
1
select + ( + 1 );
1
1
select --+(1 + 1), +-+-(1 * 1);
--+(1 + 1) +-+-(1 * 1)
2 1
select * from t where null;
c1 c2
select * from t where 1;
c1 c2
1 2
1 1
1 3
select * from t where 0;
c1 c2
select * from t where 0 * 10;
c1 c2
select * from t where null is not null;
c1 c2
select * from t where !1;
c1 c2
select * from t where 1 && 0 || 3 && null;
c1 c2
select * from t as a, t2 as b;
c1 c2 c1 c2
1 2 1 2
1 1 1 2
1 3 1 2
select * from t as a cross join t2 as b;
c1 c2 c1 c2
1 2 1 2
1 1 1 2
1 3 1 2
select * from t as a join t2 as b;
c1 c2 c1 c2
1 2 1 2
1 1 1 2
1 3 1 2
select * from t as a join t2 as b on a.c2 = b.c2;
c1 c2 c1 c2
1 2 1 2
select * from (t);
c1 c2
1 2
1 1
1 3
select * from (t as a, t2 as b);
c1 c2 c1 c2
1 2 1 2
1 1 1 2
1 3 1 2
select * from (t as a cross join t2 as b);
c1 c2 c1 c2
1 2 1 2
1 1 1 2
1 3 1 2
select 1 as a from t;
a
1
1
1
select count(*), 1 from t;
count(*) 1
3 1
select *, 1 from t;
c1 c2 1
1 2 1
1 1 1
1 3 1
select 1, count(1), sum(1);
1 count(1) sum(1)
1 1 1
drop table if exists t1;
create table t1(a int primary key, b int, c int, index idx(b, c));
insert into t1 values(1, 2, 3);
insert into t1 values(2, 3, 4);
insert into t1 values(3 ,4, 5);
insert into t1 values(4, 5, 6);
insert into t1 values(5, 6, 7);
insert into t1 values(6, 7, 8);
insert into t1 values(7, 8, 9);
insert into t1 values(9, 10, 11);
explain select a, c from t1 use index(idx) order by a limit 5;
id count task operator info
TopN_7 5.00 root Column#1:asc, offset:0, count:5
└─IndexReader_15 5.00 root index:TopN_14
└─TopN_14 5.00 cop[tikv] Column#1:asc, offset:0, count:5
└─IndexScan_13 10000.00 cop[tikv] table:t1, index:b, c, range:[NULL,+inf], keep order:false, stats:pseudo
select c, a from t1 use index(idx) order by a limit 5;
c a
3 1
4 2
5 3
6 4
7 5
drop table if exists t;
create table t (a int, b int, c int, key idx(a, b, c));
explain select count(a) from t;
id count task operator info
StreamAgg_20 1.00 root funcs:count(Column#8)
└─IndexReader_21 1.00 root index:StreamAgg_8
└─StreamAgg_8 1.00 cop[tikv] funcs:count(Column#1)
└─IndexScan_19 10000.00 cop[tikv] table:t, index:a, b, c, range:[NULL,+inf], keep order:false, stats:pseudo
select count(a) from t;
count(a)
0
insert t values(0,0,0);
explain select distinct b from t group by a;
id count task operator info
HashAgg_7 8000.00 root group by:Column#2, funcs:firstrow(Column#2)
└─StreamAgg_22 8000.00 root group by:Column#9, funcs:firstrow(Column#8)
└─IndexReader_23 8000.00 root index:StreamAgg_11
└─StreamAgg_11 8000.00 cop[tikv] group by:Column#1, funcs:firstrow(Column#2)
└─IndexScan_21 10000.00 cop[tikv] table:t, index:a, b, c, range:[NULL,+inf], keep order:true, stats:pseudo
select distinct b from t group by a;
b
0
explain select count(b) from t group by a;
id count task operator info
StreamAgg_19 8000.00 root group by:Column#10, funcs:count(Column#9)
└─IndexReader_20 8000.00 root index:StreamAgg_8
└─StreamAgg_8 8000.00 cop[tikv] group by:Column#1, funcs:count(Column#2)
└─IndexScan_18 10000.00 cop[tikv] table:t, index:a, b, c, range:[NULL,+inf], keep order:true, stats:pseudo
select count(b) from t group by a;
count(b)
1
insert t values(1,1,1),(3,3,6),(3,2,5),(2,1,4),(1,1,3),(1,1,2);
explain select count(a) from t where b>0 group by a, b;
id count task operator info
StreamAgg_25 2666.67 root group by:Column#11, Column#12, funcs:count(Column#10)
└─IndexReader_26 2666.67 root index:StreamAgg_9
└─StreamAgg_9 2666.67 cop[tikv] group by:Column#1, Column#2, funcs:count(Column#1)
└─Selection_24 3333.33 cop[tikv] gt(Column#2, 0)
└─IndexScan_23 10000.00 cop[tikv] table:t, index:a, b, c, range:[NULL,+inf], keep order:true, stats:pseudo
select count(a) from t where b>0 group by a, b;
count(a)
3
1
1
1
explain select count(a) from t where b>0 group by a, b order by a;
id count task operator info
Projection_7 2666.67 root Column#5
└─StreamAgg_36 2666.67 root group by:Column#19, Column#20, funcs:count(Column#17), firstrow(Column#19)
└─IndexReader_37 2666.67 root index:StreamAgg_34
└─StreamAgg_34 2666.67 cop[tikv] group by:Column#1, Column#2, funcs:count(Column#1)
└─Selection_28 3333.33 cop[tikv] gt(Column#2, 0)
└─IndexScan_27 10000.00 cop[tikv] table:t, index:a, b, c, range:[NULL,+inf], keep order:true, stats:pseudo
select count(a) from t where b>0 group by a, b order by a;
count(a)
3
1
1
1
explain select count(a) from t where b>0 group by a, b order by a limit 1;
id count task operator info
Projection_9 1.00 root Column#5
└─Limit_15 1.00 root offset:0, count:1
└─StreamAgg_44 1.00 root group by:Column#19, Column#20, funcs:count(Column#17), firstrow(Column#19)
└─IndexReader_45 1.00 root index:StreamAgg_40
└─StreamAgg_40 1.00 cop[tikv] group by:Column#1, Column#2, funcs:count(Column#1)
└─Selection_43 1.25 cop[tikv] gt(Column#2, 0)
└─IndexScan_42 3.75 cop[tikv] table:t, index:a, b, c, range:[NULL,+inf], keep order:true, stats:pseudo
select count(a) from t where b>0 group by a, b order by a limit 1;
count(a)
3
drop table if exists t;
create table t (id int primary key, a int, b int);
explain select * from (t t1 left join t t2 on t1.a = t2.a) left join (t t3 left join t t4 on t3.a = t4.a) on t2.b = 1;
id count task operator info
HashLeftJoin_10 155937656.25 root CARTESIAN left outer join, inner:HashLeftJoin_17, left cond:[eq(Column#6, 1)]
├─HashLeftJoin_11 12487.50 root left outer join, inner:TableReader_16, equal:[eq(Column#2, Column#5)]
│ ├─TableReader_13 10000.00 root data:TableScan_12
│ │ └─TableScan_12 10000.00 cop[tikv] table:t1, range:[-inf,+inf], keep order:false, stats:pseudo
│ └─TableReader_16 9990.00 root data:Selection_15
│ └─Selection_15 9990.00 cop[tikv] not(isnull(Column#5))
│ └─TableScan_14 10000.00 cop[tikv] table:t2, range:[-inf,+inf], keep order:false, stats:pseudo
└─HashLeftJoin_17 12487.50 root left outer join, inner:TableReader_22, equal:[eq(Column#8, Column#11)]
├─TableReader_19 10000.00 root data:TableScan_18
│ └─TableScan_18 10000.00 cop[tikv] table:t3, range:[-inf,+inf], keep order:false, stats:pseudo
└─TableReader_22 9990.00 root data:Selection_21
└─Selection_21 9990.00 cop[tikv] not(isnull(Column#11))
└─TableScan_20 10000.00 cop[tikv] table:t4, range:[-inf,+inf], keep order:false, stats:pseudo
drop table if exists t;
create table t(a bigint primary key, b bigint);
desc select * from t where a = 1;
id count task operator info
Point_Get_1 1.00 root table:t, handle:1
desc select * from t where a = '1';
id count task operator info
Point_Get_1 1.00 root table:t, handle:1
desc select sysdate(), sleep(1), sysdate();
id count task operator info
Projection_3 1.00 root sysdate(), sleep(1), sysdate()
└─TableDual_4 1.00 root rows:1
drop table if exists th;
set @@session.tidb_enable_table_partition = '1';
create table th (a int, b int) partition by hash(a) partitions 3;
insert into th values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
insert into th values (-1,-1),(-2,-2),(-3,-3),(-4,-4),(-5,-5),(-6,-6),(-7,-7),(-8,-8);
desc select * from th where a=-2;
id count task operator info
TableReader_8 10.00 root data:Selection_7
└─Selection_7 10.00 cop[tikv] eq(Column#1, -2)
└─TableScan_6 10000.00 cop[tikv] table:th, partition:p2, range:[-inf,+inf], keep order:false, stats:pseudo
desc select * from th;
id count task operator info
Union_8 30000.00 root
├─TableReader_10 10000.00 root data:TableScan_9
│ └─TableScan_9 10000.00 cop[tikv] table:th, partition:p0, range:[-inf,+inf], keep order:false, stats:pseudo
├─TableReader_12 10000.00 root data:TableScan_11
│ └─TableScan_11 10000.00 cop[tikv] table:th, partition:p1, range:[-inf,+inf], keep order:false, stats:pseudo
└─TableReader_14 10000.00 root data:TableScan_13
└─TableScan_13 10000.00 cop[tikv] table:th, partition:p2, range:[-inf,+inf], keep order:false, stats:pseudo
desc select * from th partition (p2,p1);
id count task operator info
Union_7 20000.00 root
├─TableReader_9 10000.00 root data:TableScan_8
│ └─TableScan_8 10000.00 cop[tikv] table:th, partition:p1, range:[-inf,+inf], keep order:false, stats:pseudo
└─TableReader_11 10000.00 root data:TableScan_10
└─TableScan_10 10000.00 cop[tikv] table:th, partition:p2, range:[-inf,+inf], keep order:false, stats:pseudo
drop table if exists t;
create table t(a int, b int);
explain select a != any (select a from t t2) from t t1;
id count task operator info
Projection_8 10000.00 root and(or(or(gt(Column#9, 1), ne(Column#1, Column#8)), if(ne(Column#10, 0), NULL, 0)), and(ne(Column#11, 0), if(isnull(Column#1), NULL, 1)))
└─HashLeftJoin_9 10000.00 root CARTESIAN inner join, inner:StreamAgg_16
├─TableReader_12 10000.00 root data:TableScan_11
│ └─TableScan_11 10000.00 cop[tikv] table:t1, range:[-inf,+inf], keep order:false, stats:pseudo
└─StreamAgg_16 1.00 root funcs:firstrow(Column#15), count(distinct Column#16), sum(Column#17), count(1)
└─Projection_26 10000.00 root Column#4, Column#4, cast(isnull(Column#4))
└─TableReader_23 10000.00 root data:TableScan_22
└─TableScan_22 10000.00 cop[tikv] table:t2, range:[-inf,+inf], keep order:false, stats:pseudo
explain select a = all (select a from t t2) from t t1;
id count task operator info
Projection_8 10000.00 root or(and(and(le(Column#9, 1), eq(Column#1, Column#8)), if(ne(Column#10, 0), NULL, 1)), or(eq(Column#11, 0), if(isnull(Column#1), NULL, 0)))
└─HashLeftJoin_9 10000.00 root CARTESIAN inner join, inner:StreamAgg_16
├─TableReader_12 10000.00 root data:TableScan_11
│ └─TableScan_11 10000.00 cop[tikv] table:t1, range:[-inf,+inf], keep order:false, stats:pseudo
└─StreamAgg_16 1.00 root funcs:firstrow(Column#15), count(distinct Column#16), sum(Column#17), count(1)
└─Projection_26 10000.00 root Column#4, Column#4, cast(isnull(Column#4))
└─TableReader_23 10000.00 root data:TableScan_22
└─TableScan_22 10000.00 cop[tikv] table:t2, range:[-inf,+inf], keep order:false, stats:pseudo
drop table if exists t;
create table t(a int, b int);
drop table if exists s;
create table s(a varchar(20), b varchar(20));
explain select a in (select a from s where s.b = t.b) from t;
id count task operator info
Projection_9 10000.00 root Column#8
└─HashLeftJoin_10 10000.00 root left outer semi join, inner:Projection_14, equal:[eq(Column#10, Column#11)], other cond:eq(cast(Column#1), cast(Column#4))
├─Projection_11 10000.00 root Column#1, Column#2, cast(Column#2)
│ └─TableReader_13 10000.00 root data:TableScan_12
│ └─TableScan_12 10000.00 cop[tikv] table:t, range:[-inf,+inf], keep order:false, stats:pseudo
└─Projection_14 10000.00 root Column#4, Column#5, cast(Column#5)
└─TableReader_16 10000.00 root data:TableScan_15
└─TableScan_15 10000.00 cop[tikv] table:s, range:[-inf,+inf], keep order:false, stats:pseudo
explain select a in (select a+b from t t2 where t2.b = t1.b) from t t1;
id count task operator info
Projection_7 10000.00 root Column#8
└─HashLeftJoin_8 10000.00 root left outer semi join, inner:TableReader_12, equal:[eq(Column#2, Column#5)], other cond:eq(Column#1, plus(Column#4, Column#5))
├─TableReader_10 10000.00 root data:TableScan_9
│ └─TableScan_9 10000.00 cop[tikv] table:t1, range:[-inf,+inf], keep order:false, stats:pseudo
└─TableReader_12 10000.00 root data:TableScan_11
└─TableScan_11 10000.00 cop[tikv] table:t2, range:[-inf,+inf], keep order:false, stats:pseudo
drop table t;
create table t(a int not null, b int);
explain select a in (select a from t t2 where t2.b = t1.b) from t t1;
id count task operator info
Projection_7 10000.00 root Column#8
└─HashLeftJoin_8 10000.00 root left outer semi join, inner:TableReader_12, equal:[eq(Column#2, Column#5) eq(Column#1, Column#4)]
├─TableReader_10 10000.00 root data:TableScan_9
│ └─TableScan_9 10000.00 cop[tikv] table:t1, range:[-inf,+inf], keep order:false, stats:pseudo
└─TableReader_12 10000.00 root data:TableScan_11
└─TableScan_11 10000.00 cop[tikv] table:t2, range:[-inf,+inf], keep order:false, stats:pseudo
explain select 1 from (select sleep(1)) t;
id count task operator info
Projection_4 1.00 root 1
└─Projection_5 1.00 root sleep(1)
└─TableDual_6 1.00 root rows:1
drop table if exists t;
create table t(a int, b int);
explain select a from t order by rand();
id count task operator info
Projection_8 10000.00 root Column#4
└─Sort_4 10000.00 root Column#5:asc
└─Projection_9 10000.00 root Column#4, rand()
└─TableReader_7 10000.00 root data:TableScan_6
└─TableScan_6 10000.00 cop[tikv] table:t, range:[-inf,+inf], keep order:false, stats:pseudo
explain select a, b from t order by abs(2);
id count task operator info
TableReader_8 10000.00 root data:TableScan_7
└─TableScan_7 10000.00 cop[tikv] table:t, range:[-inf,+inf], keep order:false, stats:pseudo
explain select a from t order by abs(rand())+1;
id count task operator info
Projection_8 10000.00 root Column#4
└─Sort_4 10000.00 root Column#5:asc
└─Projection_9 10000.00 root Column#4, plus(abs(rand()), 1)
└─TableReader_7 10000.00 root data:TableScan_6
└─TableScan_6 10000.00 cop[tikv] table:t, range:[-inf,+inf], keep order:false, stats:pseudo
drop table if exists t1;
create table t1(a int, b int);
drop table if exists t2;
create table t2(a int, b int);
explain select * from t1 where t1.a in (select t2.a as a from t2 where t2.b > t1.b order by t1.b);
id count task operator info
HashLeftJoin_10 7984.01 root semi join, inner:TableReader_16, equal:[eq(Column#1, Column#4)], other cond:gt(Column#5, Column#2)
├─TableReader_13 9980.01 root data:Selection_12
│ └─Selection_12 9980.01 cop[tikv] not(isnull(Column#1)), not(isnull(Column#2))
│ └─TableScan_11 10000.00 cop[tikv] table:t1, range:[-inf,+inf], keep order:false, stats:pseudo
└─TableReader_16 9980.01 root data:Selection_15
└─Selection_15 9980.01 cop[tikv] not(isnull(Column#4)), not(isnull(Column#5))
└─TableScan_14 10000.00 cop[tikv] table:t2, range:[-inf,+inf], keep order:false, stats:pseudo
drop table t;
CREATE TABLE t (id int(10) unsigned NOT NULL AUTO_INCREMENT,
i int(10) unsigned DEFAULT NULL,
x int(10) unsigned DEFAULT 0,
PRIMARY KEY (`id`)
);
explain select row_number() over( partition by i ) - x as rnk from t;
id count task operator info
Projection_7 10000.00 root minus(Column#7, Column#3)
└─Window_8 10000.00 root row_number() over(partition by Column#2)
└─Sort_11 10000.00 root Column#2:asc
└─TableReader_10 10000.00 root data:TableScan_9
└─TableScan_9 10000.00 cop[tikv] table:t, range:[0,+inf], keep order:false, stats:pseudo