oceanbase

1
drop table if exists t1;
2
drop table if exists t2;
3
drop table if exists t3;
4
create table t1 (c1 int primary key, c2 int, c3 varchar(32)) partition by hash(c1) partitions 2;
5
insert into t1 values(1,1,1),(3,3,3),(5,5,5),(7,7,7),(9,9,9),(11,11,11),(13,13,13),(15,15,15),(17,17,17),(19,19,19);
6
insert into t1 values(31,1,1),(33,3,3),(35,5,5),(37,7,7),(39,9,9),(41,11,11),(43,13,13),(45,15,15),(47,17,17),(49,19,19);
7
insert into t1 values(2,2,2),(4,4,4),(6,6,6),(8,8,8),(10,10,10),(12,12,12),(14,14,14),(16,16,16),(18,18,18),(20,20,20);
8
insert into t1 values(32,2,2),(34,4,4),(36,6,6),(38,8,8),(40,10,10),(42,12,12),(44,14,14),(46,16,16),(48,18,18),(50,20,20);
9
create table t2 (c1 int primary key, c2 int, c3 varchar(32)) partition by hash(c1) partitions 2;
10
create table t3 (c1 int primary key, c2 int, c3 varchar(32)) partition by hash(c1) partitions 2;
11
insert into t2 values(2,2,2),(4,4,4),(6,6,6),(8,8,8),(10,10,10),(12,12,12),(14,14,14),(16,16,16),(18,18,18),(20,20,20),(22,22,22);
12
select /*+topk(20 6)*/ avg(c1), avg(c2), avg(c3) from t1 where c2 <10 group by c2  limit 5;
13
avg(c1)	avg(c2)	avg(c3)
14
17.0000	2.0000	2
15
19.0000	4.0000	4
16
21.0000	6.0000	6
17
23.0000	8.0000	8
18
16.0000	1.0000	1
19
select @@is_result_accurate;
20
@@is_result_accurate
21
1
22
select /*+topk(20 6)*/ avg(c1), avg(c2), avg(c3) from t1  where c2 <10 group by c2 order by avg(c1);
23
avg(c1)	avg(c2)	avg(c3)
24
16.0000	1.0000	1
25
17.0000	2.0000	2
26
18.0000	3.0000	3
27
19.0000	4.0000	4
28
20.0000	5.0000	5
29
21.0000	6.0000	6
30
22.0000	7.0000	7
31
23.0000	8.0000	8
32
24.0000	9.0000	9
33
select @@is_result_accurate;
34
@@is_result_accurate
35
1
36
select /*+topk(20 6)*/ avg(c1), avg(c2), avg(c3) from t1  where c2 <10 order by avg(c1) limit 5;
37
avg(c1)	avg(c2)	avg(c3)
38
20.0000	5.0000	5
39
select @@is_result_accurate;
40
@@is_result_accurate
41
1
42
select /*+topk(20 6)*/ avg(c1), avg(c2), avg(c3) from t1  where c2 <10 group by c2 order by avg(c1) limit 5 for update nowait;
43
avg(c1)	avg(c2)	avg(c3)
44
16.0000	1.0000	1
45
17.0000	2.0000	2
46
18.0000	3.0000	3
47
19.0000	4.0000	4
48
20.0000	5.0000	5
49
select @@is_result_accurate;
50
@@is_result_accurate
51
1
52
select avg(c1), avg(c2), avg(c3) from t1  where c2 <10 group by c2 order by avg(c1) limit 5;
53
avg(c1)	avg(c2)	avg(c3)
54
16.0000	1.0000	1
55
17.0000	2.0000	2
56
18.0000	3.0000	3
57
19.0000	4.0000	4
58
20.0000	5.0000	5
59
select @@is_result_accurate;
60
@@is_result_accurate
61
1
62
select /*+topk(20 6)*/ sql_calc_found_rows avg(c2), avg(c3)  from t1 where c2 <10 group by c2 order by avg(c1) limit 5;
63
avg(c2)	avg(c3)
64
1.0000	1
65
2.0000	2
66
3.0000	3
67
4.0000	4
68
5.0000	5
69
select @@is_result_accurate;
70
@@is_result_accurate
71
1
72
select /*+topk(20 6)*/  avg(c1), avg(c2), avg(c3)  from t1 where c2 <10 group by c2 order by (select c1  from t1 where c2 = 1 and c1 !=1)  limit 5;
73
avg(c1)	avg(c2)	avg(c3)
74
17.0000	2.0000	2
75
19.0000	4.0000	4
76
21.0000	6.0000	6
77
23.0000	8.0000	8
78
16.0000	1.0000	1
79
select @@is_result_accurate;
80
@@is_result_accurate
81
1
82
select /*+topk(20 6)*/ distinct avg(c1), avg(c2), avg(c3)  from t1 where c2 <10 group by c2 order by avg(c1) limit 5;
83
avg(c1)	avg(c2)	avg(c3)
84
16.0000	1.0000	1
85
17.0000	2.0000	2
86
18.0000	3.0000	3
87
19.0000	4.0000	4
88
20.0000	5.0000	5
89
select @@is_result_accurate;
90
@@is_result_accurate
91
1
92
select /*+topk(20 6)*/ sum(c1), avg(c2), group_concat(c3, c1)  from t1 where c2 <10 group by c2 order by avg(c1) limit 5;
93
sum(c1)	avg(c2)	group_concat(c3, c1)
94
32	1.0000	11,131
95
34	2.0000	22,232
96
36	3.0000	33,333
97
38	4.0000	44,434
98
40	5.0000	55,535
99
select @@is_result_accurate;
100
@@is_result_accurate
101
1
102
select /*+topk(20 6)*/  avg(c1), avg(c2), avg(c3) from (select *  from t1 where c2 <10) as a  group by a.c2 order by avg(a.c1) limit 5;
103
avg(c1)	avg(c2)	avg(c3)
104
16.0000	1.0000	1
105
17.0000	2.0000	2
106
18.0000	3.0000	3
107
19.0000	4.0000	4
108
20.0000	5.0000	5
109
select @@is_result_accurate;
110
@@is_result_accurate
111
1
112
select /*+topk(20 6)*/ distinct avg(t1.c1), avg(t1.c2), avg(t1.c3)  from t1 , t2 group by t1.c2 order by avg(t1.c1) limit 5;
113
avg(t1.c1)	avg(t1.c2)	avg(t1.c3)
114
16.0000	1.0000	1
115
17.0000	2.0000	2
116
18.0000	3.0000	3
117
19.0000	4.0000	4
118
20.0000	5.0000	5
119
select @@is_result_accurate;
120
@@is_result_accurate
121
1
122
select /*+topk(1 2) use_hash_aggregation*/ c3, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by c3 limit 1;
123
c3	avg(c1)	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
124
1	16.0000	2	34	2	1	31	1.0000	0.0625
125
select @@is_result_accurate;
126
@@is_result_accurate
127
1
128
select /*+topk(1 2) use_hash_aggregation*/ c3, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by "aaa" limit 1;
129
c3	avg(c1)	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
130
2	17.0000	4	38	2	2	32	2.0000	0.11764705882352941
131
select @@is_result_accurate;
132
@@is_result_accurate
133
1
134
select /*+topk(90 2) use_hash_aggregation*/ c3, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <30 group by c2 order by c3 limit 10;
135
c3	avg(c1)	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
136
1	16.0000	2	34	2	1	31	1.0000	0.0625
137
10	25.0000	20	70	2	10	40	10.0000	0.4
138
11	26.0000	22	74	2	11	41	11.0000	0.4230769230769231
139
12	27.0000	24	78	2	12	42	12.0000	0.4444444444444444
140
13	28.0000	26	82	2	13	43	13.0000	0.4642857142857143
141
14	29.0000	28	86	2	14	44	14.0000	0.4827586206896552
142
15	30.0000	30	90	2	15	45	15.0000	0.5
143
16	31.0000	32	94	2	16	46	16.0000	0.5161290322580645
144
17	32.0000	34	98	2	17	47	17.0000	0.53125
145
18	33.0000	36	102	2	18	48	18.0000	0.5454545454545454
146
select @@is_result_accurate;
147
@@is_result_accurate
148
1
149
select /*+topk(1 1)*/ c3, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by c3 limit 1;
150
c3	avg(c1)	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
151
1	16.0000	2	34	2	1	31	1.0000	0.0625
152
select @@is_result_accurate;
153
@@is_result_accurate
154
1
155
select /*+topk(1 1)*/ c3, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <12 group by c2 order by c3 limit 1 offset 4;
156
c3	avg(c1)	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
157
3	18.0000	6	42	2	3	33	3.0000	0.16666666666666666
158
select @@is_result_accurate;
159
@@is_result_accurate
160
1
161
select /*+topk(1 1)*/ c3, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <12 group by c2 order by c3 limit 1 offset 1000;
162
c3	avg(c1)	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
163
select @@is_result_accurate;
164
@@is_result_accurate
165
1
166
select /*+topk(1 1)*/ c3, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by c2 limit 1;
167
c3	avg(c1)	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
168
1	16.0000	2	34	2	1	31	1.0000	0.0625
169
select @@is_result_accurate;
170
@@is_result_accurate
171
1
172
select /*+topk(1 1)*/ c3, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by c3 limit 0;
173
c3	avg(c1)	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
174
select @@is_result_accurate;
175
@@is_result_accurate
176
1
177
select /*+topk(1 1)*/ c2, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by avg(c3), sum(c1)/sum(c2) limit 5;
178
c2	avg(c1)	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
179
1	16.0000	2	34	2	1	31	1.0000	0.0625
180
2	17.0000	4	38	2	2	32	2.0000	0.11764705882352941
181
3	18.0000	6	42	2	3	33	3.0000	0.16666666666666666
182
4	19.0000	8	46	2	4	34	4.0000	0.21052631578947367
183
5	20.0000	10	50	2	5	35	5.0000	0.25
184
select @@is_result_accurate;
185
@@is_result_accurate
186
1
187
select /*+topk(1 1)*/ c1, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by avg(c3) desc, sum(c1)/sum(c2)  limit 5;
188
c1	avg(c1)	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
189
9	24.0000	18	66	2	9	39	9.0000	0.375
190
8	23.0000	16	62	2	8	38	8.0000	0.34782608695652173
191
7	22.0000	14	58	2	7	37	7.0000	0.3181818181818182
192
6	21.0000	12	54	2	6	36	6.0000	0.2857142857142857
193
5	20.0000	10	50	2	5	35	5.0000	0.25
194
select @@is_result_accurate;
195
@@is_result_accurate
196
1
197
select /*+topk(1 1)*/ c2, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by avg(c3) desc, sum(c1)/sum(c2) + avg(c2)  limit 5;
198
c2	avg(c1)	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
199
9	24.0000	18	66	2	9	39	9.0000	0.375
200
8	23.0000	16	62	2	8	38	8.0000	0.34782608695652173
201
7	22.0000	14	58	2	7	37	7.0000	0.3181818181818182
202
6	21.0000	12	54	2	6	36	6.0000	0.2857142857142857
203
5	20.0000	10	50	2	5	35	5.0000	0.25
204
select @@is_result_accurate;
205
@@is_result_accurate
206
1
207
select /*+topk(1 1)*/ avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by avg(c3) desc, sum(c1)+sum(c2)  limit 5;
208
avg(c1)	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
209
24.0000	18	66	2	9	39	9.0000	0.375
210
23.0000	16	62	2	8	38	8.0000	0.34782608695652173
211
22.0000	14	58	2	7	37	7.0000	0.3181818181818182
212
21.0000	12	54	2	6	36	6.0000	0.2857142857142857
213
20.0000	10	50	2	5	35	5.0000	0.25
214
select @@is_result_accurate;
215
@@is_result_accurate
216
1
217
select /*+topk(0 0)*/ c2, sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <12 group by c2 order by avg(c3) desc, sum(c1)+sum(c2)  limit 5;
218
c2	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
219
11	22	74	2	11	41	11.0000	0.4230769230769231
220
10	20	70	2	10	40	10.0000	0.4
221
9	18	66	2	9	39	9.0000	0.375
222
8	16	62	2	8	38	8.0000	0.34782608695652173
223
7	14	58	2	7	37	7.0000	0.3181818181818182
224
select @@is_result_accurate;
225
@@is_result_accurate
226
1
227
select /*+topk(0 10)*/ c2, sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <12 group by c2 order by avg(c3) desc, sum(c1)+sum(c2)  limit 5;
228
c2	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
229
11	22	74	2	11	41	11.0000	0.4230769230769231
230
10	20	70	2	10	40	10.0000	0.4
231
9	18	66	2	9	39	9.0000	0.375
232
8	16	62	2	8	38	8.0000	0.34782608695652173
233
7	14	58	2	7	37	7.0000	0.3181818181818182
234
select @@is_result_accurate;
235
@@is_result_accurate
236
1
237
select /*+topk(50 0)*/ c2, sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <12 group by c2 order by avg(c3) desc, sum(c1)+sum(c2)  limit 5;
238
c2	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
239
11	22	74	2	11	41	11.0000	0.4230769230769231
240
10	20	70	2	10	40	10.0000	0.4
241
9	18	66	2	9	39	9.0000	0.375
242
8	16	62	2	8	38	8.0000	0.34782608695652173
243
7	14	58	2	7	37	7.0000	0.3181818181818182
244
select @@is_result_accurate;
245
@@is_result_accurate
246
1
247
select /*+topk(10000 1)*/ c2, sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <12 group by c2 order by avg(c3) desc, sum(c1)+sum(c2)  limit 5;
248
c2	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
249
11	22	74	2	11	41	11.0000	0.4230769230769231
250
10	20	70	2	10	40	10.0000	0.4
251
9	18	66	2	9	39	9.0000	0.375
252
8	16	62	2	8	38	8.0000	0.34782608695652173
253
7	14	58	2	7	37	7.0000	0.3181818181818182
254
select @@is_result_accurate;
255
@@is_result_accurate
256
1
257
select /*+topk(1 10000)*/ c2, sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <12 group by c2 order by avg(c3) desc, sum(c1)+sum(c2)  limit 5;
258
c2	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)
259
11	22	74	2	11	41	11.0000	0.4230769230769231
260
10	20	70	2	10	40	10.0000	0.4
261
9	18	66	2	9	39	9.0000	0.375
262
8	16	62	2	8	38	8.0000	0.34782608695652173
263
7	14	58	2	7	37	7.0000	0.3181818181818182
264
select @@is_result_accurate;
265
@@is_result_accurate
266
1
267
select * from (select /*+topk(0 4)*/ avg(c1)  from t1 where c2 <10 group by c2 order by sum(c2) limit 5) as a;
268
avg(c1)
269
16.0000
270
17.0000
271
18.0000
272
19.0000
273
20.0000
274
select c1  from t1 where c1 < any (select /*+topk(0 4)*/ avg(c1)  from t1 where c2 <10 group by c2 order by sum(c2)  limit 5);
275
c1
276
2
277
4
278
6
279
8
280
10
281
12
282
14
283
16
284
18
285
1
286
3
287
5
288
7
289
9
290
11
291
13
292
15
293
17
294
19
295
(select /*+topk(0 4)*/ avg(c1)  from t1 where c2 <10 group by c2 order by sum(c2) limit 5) union (select /*+topk(0 10)*/ sum(c1)  from t1 where c2 <10 group by c2 order by sum(c2) limit 7);
296
avg(c1)
297
16.0000
298
17.0000
299
18.0000
300
19.0000
301
20.0000
302
32.0000
303
34.0000
304
36.0000
305
38.0000
306
40.0000
307
42.0000
308
44.0000
309
select c1  from t1 where (select /*+topk(0 10)*/ avg(c1)  from t1 where c2 <10 group by c2 order by sum(c2) limit 1) > 1 ;
310
c1
311
2
312
4
313
6
314
8
315
10
316
12
317
14
318
16
319
18
320
20
321
32
322
34
323
36
324
38
325
40
326
42
327
44
328
46
329
48
330
50
331
1
332
3
333
5
334
7
335
9
336
11
337
13
338
15
339
17
340
19
341
31
342
33
343
35
344
37
345
39
346
41
347
43
348
45
349
47
350
49
351
select * from (select /*+topk(10 5)*/ c3, c1, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1) from t1 group by c2 order by c3 limit 7) as a left join t2 on a.c1 = t2.c1 left join t3 on t3.c1 = a.c1 order by a.c3;
352
c3	c1	avg(c1)	sum(c2)	sum(c1) + sum(c2)	count(c3)	min(c3)	max(c1)	sum(c2)/count(c1)	sum(c3)/sum(c1)	c1	c2	c3	c1	c2	c3
353
1	1	16.0000	2	34	2	1	31	1.0000	0.0625	NULL	NULL	NULL	NULL	NULL	NULL
354
10	10	25.0000	20	70	2	10	40	10.0000	0.4	10	10	10	NULL	NULL	NULL
355
11	11	26.0000	22	74	2	11	41	11.0000	0.4230769230769231	NULL	NULL	NULL	NULL	NULL	NULL
356
12	12	27.0000	24	78	2	12	42	12.0000	0.4444444444444444	12	12	12	NULL	NULL	NULL
357
13	13	28.0000	26	82	2	13	43	13.0000	0.4642857142857143	NULL	NULL	NULL	NULL	NULL	NULL
358
14	14	29.0000	28	86	2	14	44	14.0000	0.4827586206896552	14	14	14	NULL	NULL	NULL
359
15	15	30.0000	30	90	2	15	45	15.0000	0.5	NULL	NULL	NULL	NULL	NULL	NULL
360
explain basic select /*+topk(20 6)*/ avg(c1), avg(c2), avg(c3)  from t1 where c2 <10 group by c2  limit 5;
361
Query Plan
362
===================================================
363
|ID|OPERATOR                             |NAME    |
364
---------------------------------------------------
365
|0 |LIMIT                                |        |
366
|1 |└─PX COORDINATOR                     |        |
367
|2 |  └─EXCHANGE OUT DISTR               |:EX10001|
368
|3 |    └─LIMIT                          |        |
369
|4 |      └─HASH GROUP BY                |        |
370
|5 |        └─EXCHANGE IN DISTR          |        |
371
|6 |          └─EXCHANGE OUT DISTR (HASH)|:EX10000|
372
|7 |            └─HASH GROUP BY          |        |
373
|8 |              └─PX PARTITION ITERATOR|        |
374
|9 |                └─TABLE FULL SCAN    |t1      |
375
===================================================
376
Outputs & filters:
377
-------------------------------------
378
  0 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
379
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
380
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
381
      limit(5), offset(nil)
382
  1 - output([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
383
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
384
  2 - output([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
385
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
386
      dop=1
387
  3 - output([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
388
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
389
      limit(5), offset(nil)
390
  4 - output([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
391
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
392
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
393
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))])
394
  5 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
395
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
396
  6 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
397
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
398
      (#keys=1, [t1.c2]), dop=1
399
  7 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
400
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
401
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))],
402
       [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
403
  8 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
404
      force partition granule
405
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
406
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
407
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
408
      range_key([t1.c1]), range(MIN ; MAX)always true
409
explain basic select /*+topk(20 6)*/ avg(c1), avg(c2), avg(c3)  from t1 where c2 <10 group by c2 order by avg(c1);
410
Query Plan
411
=================================================
412
|ID|OPERATOR                           |NAME    |
413
-------------------------------------------------
414
|0 |PX COORDINATOR MERGE SORT          |        |
415
|1 |└─EXCHANGE OUT DISTR               |:EX10001|
416
|2 |  └─SORT                           |        |
417
|3 |    └─HASH GROUP BY                |        |
418
|4 |      └─EXCHANGE IN DISTR          |        |
419
|5 |        └─EXCHANGE OUT DISTR (HASH)|:EX10000|
420
|6 |          └─HASH GROUP BY          |        |
421
|7 |            └─PX PARTITION ITERATOR|        |
422
|8 |              └─TABLE FULL SCAN    |t1      |
423
=================================================
424
Outputs & filters:
425
-------------------------------------
426
  0 - output([INTERNAL_FUNCTION(cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15,
427
       4)), cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4)), cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
428
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1)))]), filter(nil), rowset=256
429
      sort_keys([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC])
430
  1 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [INTERNAL_FUNCTION(cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c
431
      1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4)), cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) 
432
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4)), cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3,
433
       DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1)))]), filter(nil), rowset=256
434
      dop=1
435
  2 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
436
       [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
437
      sort_keys([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC])
438
  3 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
439
       [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
440
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
441
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))])
442
  4 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
443
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
444
  5 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
445
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
446
      (#keys=1, [t1.c2]), dop=1
447
  6 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
448
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
449
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))],
450
       [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
451
  7 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
452
      force partition granule
453
  8 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
454
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
455
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
456
      range_key([t1.c1]), range(MIN ; MAX)always true
457
explain basic select /*+topk(20 6)*/ avg(c1), avg(c2), avg(c3)  from t1 where c2 <10 order by avg(c1) limit 5;
458
Query Plan
459
=============================================
460
|ID|OPERATOR                       |NAME    |
461
---------------------------------------------
462
|0 |LIMIT                          |        |
463
|1 |└─SCALAR GROUP BY              |        |
464
|2 |  └─PX COORDINATOR             |        |
465
|3 |    └─EXCHANGE OUT DISTR       |:EX10000|
466
|4 |      └─MERGE GROUP BY         |        |
467
|5 |        └─PX PARTITION ITERATOR|        |
468
|6 |          └─TABLE FULL SCAN    |t1      |
469
=============================================
470
Outputs & filters:
471
-------------------------------------
472
  0 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
473
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
474
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
475
      limit(5), offset(nil)
476
  1 - output([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
477
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
478
      group(nil), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
479
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))])
480
  2 - output([T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
481
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
482
  3 - output([T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
483
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
484
      dop=1
485
  4 - output([T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
486
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
487
      group(nil), agg_func([T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))],
488
       [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
489
  5 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
490
      force partition granule
491
  6 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
492
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
493
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
494
      range_key([t1.c1]), range(MIN ; MAX)always true
495
explain basic select /*+topk(20 6)*/ avg(c1), avg(c2), avg(c3)  from t1 where c2 <10 group by c2 order by avg(c1) limit 5 for update nowait;
496
Query Plan
497
=======================================================
498
|ID|OPERATOR                                 |NAME    |
499
-------------------------------------------------------
500
|0 |MATERIAL                                 |        |
501
|1 |└─LIMIT                                  |        |
502
|2 |  └─PX COORDINATOR MERGE SORT            |        |
503
|3 |    └─EXCHANGE OUT DISTR                 |:EX10001|
504
|4 |      └─TOP-N SORT                       |        |
505
|5 |        └─HASH GROUP BY                  |        |
506
|6 |          └─EXCHANGE IN DISTR            |        |
507
|7 |            └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
508
|8 |              └─HASH GROUP BY            |        |
509
|9 |                └─DISTRIBUTED FOR UPDATE |        |
510
|10|                  └─PX PARTITION ITERATOR|        |
511
|11|                    └─TABLE FULL SCAN    |t1      |
512
=======================================================
513
Outputs & filters:
514
-------------------------------------
515
  0 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
516
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
517
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
518
  1 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
519
       [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
520
      limit(5), offset(nil)
521
  2 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
522
       [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
523
      sort_keys([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC])
524
  3 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
525
       [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
526
      dop=1
527
  4 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
528
       [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
529
      sort_keys([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC]),
530
       topn(5)
531
  5 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
532
       [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
533
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
534
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))])
535
  6 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
536
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
537
  7 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
538
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
539
      (#keys=1, [t1.c2]), dop=1
540
  8 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
541
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
542
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))],
543
       [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
544
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
545
      lock tables(t1)
546
 10 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
547
      force partition granule
548
 11 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
549
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
550
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
551
      range_key([t1.c1]), range(MIN ; MAX)always true
552
explain basic select avg(c1), avg(c2), avg(c3)  from t1 where c2 <10 group by c2 order by avg(c1) limit 5;
553
Query Plan
554
===================================================
555
|ID|OPERATOR                             |NAME    |
556
---------------------------------------------------
557
|0 |LIMIT                                |        |
558
|1 |└─PX COORDINATOR MERGE SORT          |        |
559
|2 |  └─EXCHANGE OUT DISTR               |:EX10001|
560
|3 |    └─TOP-N SORT                     |        |
561
|4 |      └─HASH GROUP BY                |        |
562
|5 |        └─EXCHANGE IN DISTR          |        |
563
|6 |          └─EXCHANGE OUT DISTR (HASH)|:EX10000|
564
|7 |            └─HASH GROUP BY          |        |
565
|8 |              └─PX PARTITION ITERATOR|        |
566
|9 |                └─TABLE FULL SCAN    |t1      |
567
===================================================
568
Outputs & filters:
569
-------------------------------------
570
  0 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
571
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
572
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
573
      limit(5), offset(nil)
574
  1 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
575
       [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
576
      sort_keys([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC])
577
  2 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
578
       [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
579
      dop=1
580
  3 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
581
       [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
582
      sort_keys([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC]),
583
       topn(5)
584
  4 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
585
       [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
586
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
587
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))])
588
  5 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
589
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
590
  6 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
591
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
592
      (#keys=1, [t1.c2]), dop=1
593
  7 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
594
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
595
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))],
596
       [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
597
  8 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
598
      force partition granule
599
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
600
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
601
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
602
      range_key([t1.c1]), range(MIN ; MAX)always true
603
explain basic select /*+topk(20 6)*/ sql_calc_found_rows avg(c2), avg(c3)  from t1 where c2 <10 group by c2 order by avg(c1) limit 5;
604
Query Plan
605
===================================================
606
|ID|OPERATOR                             |NAME    |
607
---------------------------------------------------
608
|0 |LIMIT                                |        |
609
|1 |└─PX COORDINATOR MERGE SORT          |        |
610
|2 |  └─EXCHANGE OUT DISTR               |:EX10001|
611
|3 |    └─SORT                           |        |
612
|4 |      └─HASH GROUP BY                |        |
613
|5 |        └─EXCHANGE IN DISTR          |        |
614
|6 |          └─EXCHANGE OUT DISTR (HASH)|:EX10000|
615
|7 |            └─HASH GROUP BY          |        |
616
|8 |              └─PX PARTITION ITERATOR|        |
617
|9 |                └─TABLE FULL SCAN    |t1      |
618
===================================================
619
Outputs & filters:
620
-------------------------------------
621
  0 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
622
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
623
      limit(5), offset(nil)
624
  1 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3,
625
       DOUBLE(-1, -1))))]), filter(nil), rowset=256
626
      sort_keys([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC])
627
  2 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
628
       [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
629
      dop=1
630
  3 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
631
       [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
632
      sort_keys([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC])
633
  4 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
634
       [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
635
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))],
636
       [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))])
637
  5 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)],
638
       [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
639
  6 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)],
640
       [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
641
      (#keys=1, [t1.c2]), dop=1
642
  7 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)],
643
       [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
644
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))],
645
       [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)])
646
  8 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
647
      force partition granule
648
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
649
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
650
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
651
      range_key([t1.c1]), range(MIN ; MAX)always true
652
explain basic select /*+topk(20 6)*/  avg(c1), avg(c2), avg(c3)  from t1 where c2 <10 group by c2 order by (select c1  from t1 where c2 = 1 and c1 != 1) limit 5;
653
Query Plan
654
===================================================
655
|ID|OPERATOR                             |NAME    |
656
---------------------------------------------------
657
|0 |LIMIT                                |        |
658
|1 |└─SUBPLAN FILTER                     |        |
659
|2 |  ├─PX COORDINATOR                   |        |
660
|3 |  │ └─EXCHANGE OUT DISTR             |:EX10001|
661
|4 |  │   └─HASH GROUP BY                |        |
662
|5 |  │     └─EXCHANGE IN DISTR          |        |
663
|6 |  │       └─EXCHANGE OUT DISTR (HASH)|:EX10000|
664
|7 |  │         └─HASH GROUP BY          |        |
665
|8 |  │           └─PX PARTITION ITERATOR|        |
666
|9 |  │             └─TABLE FULL SCAN    |t1      |
667
|10|  └─PX COORDINATOR                   |        |
668
|11|    └─EXCHANGE OUT DISTR             |:EX20000|
669
|12|      └─PX PARTITION ITERATOR        |        |
670
|13|        └─TABLE RANGE SCAN           |t1      |
671
===================================================
672
Outputs & filters:
673
-------------------------------------
674
  0 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
675
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
676
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
677
      limit(5), offset(nil)
678
  1 - output([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
679
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
680
      exec_params_(nil), onetime_exprs_([subquery(1)(:0)]), init_plan_idxs_(nil), use_batch=false
681
  2 - output([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
682
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
683
  3 - output([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
684
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
685
      dop=1
686
  4 - output([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
687
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
688
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
689
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))])
690
  5 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
691
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
692
  6 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
693
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
694
      (#keys=1, [t1.c2]), dop=1
695
  7 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
696
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
697
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))],
698
       [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
699
  8 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
700
      force partition granule
701
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
702
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
703
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
704
      range_key([t1.c1]), range(MIN ; MAX)always true
705
 10 - output([t1.c1]), filter(nil), rowset=256
706
 11 - output([t1.c1]), filter(nil), rowset=256
707
      dop=1
708
 12 - output([t1.c1]), filter(nil), rowset=256
709
      force partition granule
710
 13 - output([t1.c1]), filter([t1.c2 = 1]), rowset=256
711
      access([t1.c1], [t1.c2]), partitions(p[0-1])
712
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
713
      range_key([t1.c1]), range(NULL ; 1), (1 ; MAX), 
714
      range_cond([t1.c1 != 1])
715
explain basic select /*+topk(20 6)*/ distinct avg(c1), avg(c2), avg(c3)  from t1 where c2 <10 group by c2 order by avg(c1) limit 5;
716
Query Plan
717
=============================================================
718
|ID|OPERATOR                                       |NAME    |
719
-------------------------------------------------------------
720
|0 |LIMIT                                          |        |
721
|1 |└─PX COORDINATOR MERGE SORT                    |        |
722
|2 |  └─EXCHANGE OUT DISTR                         |:EX10002|
723
|3 |    └─LIMIT                                    |        |
724
|4 |      └─MERGE DISTINCT                         |        |
725
|5 |        └─EXCHANGE IN MERGE SORT DISTR         |        |
726
|6 |          └─EXCHANGE OUT DISTR (HASH)          |:EX10001|
727
|7 |            └─MERGE DISTINCT                   |        |
728
|8 |              └─SORT                           |        |
729
|9 |                └─HASH GROUP BY                |        |
730
|10|                  └─EXCHANGE IN DISTR          |        |
731
|11|                    └─EXCHANGE OUT DISTR (HASH)|:EX10000|
732
|12|                      └─HASH GROUP BY          |        |
733
|13|                        └─PX PARTITION ITERATOR|        |
734
|14|                          └─TABLE FULL SCAN    |t1      |
735
=============================================================
736
Outputs & filters:
737
-------------------------------------
738
  0 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
739
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
740
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
741
      limit(5), offset(nil)
742
  1 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
743
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
744
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
745
      sort_keys([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC])
746
  2 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
747
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
748
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
749
      dop=1
750
  3 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
751
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
752
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
753
      limit(5), offset(nil)
754
  4 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
755
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
756
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
757
      distinct([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
758
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
759
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))])
760
  5 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
761
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
762
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
763
      sort_keys([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC],
764
       [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
765
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1)), ASC])
766
  6 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
767
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
768
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
769
      (#keys=3, [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
770
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
771
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), dop=1
772
  7 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
773
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
774
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
775
      distinct([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
776
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
777
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))])
778
  8 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
779
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
780
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
781
      sort_keys([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC],
782
       [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
783
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1)), ASC])
784
  9 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
785
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) 
786
      / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
787
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))],
788
       [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))))])
789
 10 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
790
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
791
 11 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
792
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
793
      (#keys=1, [t1.c2]), dop=1
794
 12 - output([t1.c2], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3,
795
       DOUBLE(-1, -1)))]), filter(nil), rowset=256
796
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))],
797
       [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
798
 13 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
799
      force partition granule
800
 14 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
801
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
802
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
803
      range_key([t1.c1]), range(MIN ; MAX)always true
804
explain basic select /*+topk(20 6)*/ sum(c1), avg(c2), group_concat(c3, c1)  from t1 where c2 <10 group by c2 order by avg(c1) limit 5;
805
Query Plan
806
=====================================================
807
|ID|OPERATOR                               |NAME    |
808
-----------------------------------------------------
809
|0 |LIMIT                                  |        |
810
|1 |└─PX COORDINATOR MERGE SORT            |        |
811
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
812
|3 |    └─TOP-N SORT                       |        |
813
|4 |      └─MERGE GROUP BY                 |        |
814
|5 |        └─PARTITION SORT               |        |
815
|6 |          └─EXCHANGE IN DISTR          |        |
816
|7 |            └─EXCHANGE OUT DISTR (HASH)|:EX10000|
817
|8 |              └─PX PARTITION ITERATOR  |        |
818
|9 |                └─TABLE FULL SCAN      |t1      |
819
=====================================================
820
Outputs & filters:
821
-------------------------------------
822
  0 - output([T_FUN_SUM(t1.c1)], [cast(cast(T_FUN_SUM(t1.c2), DECIMAL(33, 0)) / cast(T_FUN_COUNT(t1.c2), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_GROUP_CONCAT(t1.c3,
823
       cast(t1.c1, VARCHAR(65536)))]), filter(nil), rowset=256
824
      limit(5), offset(nil)
825
  1 - output([T_FUN_SUM(t1.c1)], [T_FUN_GROUP_CONCAT(t1.c3, cast(t1.c1, VARCHAR(65536)))], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)]), filter(nil), rowset=256
826
      sort_keys([cast(cast(T_FUN_SUM(t1.c1), DECIMAL(33, 0)) / cast(T_FUN_COUNT(t1.c1), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC])
827
  2 - output([T_FUN_SUM(t1.c1)], [T_FUN_GROUP_CONCAT(t1.c3, cast(t1.c1, VARCHAR(65536)))], [cast(cast(T_FUN_SUM(t1.c1), DECIMAL(33, 0)) / cast(T_FUN_COUNT(t1.c1),
828
       DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)]), filter(nil), rowset=256
829
      dop=1
830
  3 - output([T_FUN_SUM(t1.c1)], [T_FUN_GROUP_CONCAT(t1.c3, cast(t1.c1, VARCHAR(65536)))], [cast(cast(T_FUN_SUM(t1.c1), DECIMAL(33, 0)) / cast(T_FUN_COUNT(t1.c1),
831
       DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)]), filter(nil), rowset=256
832
      sort_keys([cast(cast(T_FUN_SUM(t1.c1), DECIMAL(33, 0)) / cast(T_FUN_COUNT(t1.c1), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC]), topn(5)
833
  4 - output([T_FUN_SUM(t1.c1)], [T_FUN_GROUP_CONCAT(t1.c3, cast(t1.c1, VARCHAR(65536)))], [cast(cast(T_FUN_SUM(t1.c1), DECIMAL(33, 0)) / cast(T_FUN_COUNT(t1.c1),
834
       DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)]), filter(nil), rowset=256
835
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c1)], [T_FUN_GROUP_CONCAT(t1.c3, cast(t1.c1, VARCHAR(65536)))], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [T_FUN_COUNT(t1.c1)])
836
  5 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
837
      sort_keys([HASH(t1.c2), ASC], [t1.c2, ASC])
838
  6 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
839
  7 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
840
      (#keys=1, [t1.c2]), dop=1
841
  8 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
842
      force partition granule
843
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
844
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
845
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
846
      range_key([t1.c1]), range(MIN ; MAX)always true
847
explain basic select /*+topk(1 2) use_hash_aggregation*/ c3, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by c3 limit 1;
848
Query Plan
849
=====================================================
850
|ID|OPERATOR                               |NAME    |
851
-----------------------------------------------------
852
|0 |LIMIT                                  |        |
853
|1 |└─PX COORDINATOR MERGE SORT            |        |
854
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
855
|3 |    └─TOP-N SORT                       |        |
856
|4 |      └─HASH GROUP BY                  |        |
857
|5 |        └─EXCHANGE IN DISTR            |        |
858
|6 |          └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
859
|7 |            └─SORT                     |        |
860
|8 |              └─HASH GROUP BY          |        |
861
|9 |                └─PX PARTITION ITERATOR|        |
862
|10|                  └─TABLE FULL SCAN    |t1      |
863
=====================================================
864
Outputs & filters:
865
-------------------------------------
866
  0 - output([t1.c3], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))],
867
       [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
868
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
869
       DOUBLE(-1, -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), rowset=256
870
      limit(1), offset(nil)
871
  1 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
872
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
873
      sort_keys([t1.c3, ASC])
874
  2 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
875
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
876
      dop=1
877
  3 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
878
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
879
      sort_keys([t1.c3, ASC]), topn(1)
880
  4 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
881
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
882
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
883
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))])
884
  5 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
885
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
886
  6 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
887
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
888
      (#keys=1, [t1.c2]), dop=1
889
  7 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
890
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
891
      sort_keys([t1.c3, ASC]), minimum_row_count:2 top_precision:1 , limit(1), offset(nil)
892
  8 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
893
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
894
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
895
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))])
896
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
897
      force partition granule
898
 10 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
899
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
900
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
901
      range_key([t1.c1]), range(MIN ; MAX)always true
902
explain basic select /*+topk(90 2) use_hash_aggregation*/ c3, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <30 group by c2 order by c3 limit 10;
903
Query Plan
904
=====================================================
905
|ID|OPERATOR                               |NAME    |
906
-----------------------------------------------------
907
|0 |LIMIT                                  |        |
908
|1 |└─PX COORDINATOR MERGE SORT            |        |
909
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
910
|3 |    └─TOP-N SORT                       |        |
911
|4 |      └─HASH GROUP BY                  |        |
912
|5 |        └─EXCHANGE IN DISTR            |        |
913
|6 |          └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
914
|7 |            └─SORT                     |        |
915
|8 |              └─HASH GROUP BY          |        |
916
|9 |                └─PX PARTITION ITERATOR|        |
917
|10|                  └─TABLE FULL SCAN    |t1      |
918
=====================================================
919
Outputs & filters:
920
-------------------------------------
921
  0 - output([t1.c3], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))],
922
       [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
923
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
924
       DOUBLE(-1, -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), rowset=256
925
      limit(10), offset(nil)
926
  1 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
927
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
928
      sort_keys([t1.c3, ASC])
929
  2 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
930
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
931
      dop=1
932
  3 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
933
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
934
      sort_keys([t1.c3, ASC]), topn(10)
935
  4 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
936
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
937
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
938
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))])
939
  5 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
940
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
941
  6 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
942
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
943
      (#keys=1, [t1.c2]), dop=1
944
  7 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
945
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
946
      sort_keys([t1.c3, ASC]), minimum_row_count:2 top_precision:90 , limit(10), offset(nil)
947
  8 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
948
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
949
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
950
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))])
951
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
952
      force partition granule
953
 10 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 30]), rowset=256
954
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
955
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
956
      range_key([t1.c1]), range(MIN ; MAX)always true
957
explain basic select /*+topk(1 1)*/ c3, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by c3 limit 1;
958
Query Plan
959
=====================================================
960
|ID|OPERATOR                               |NAME    |
961
-----------------------------------------------------
962
|0 |LIMIT                                  |        |
963
|1 |└─PX COORDINATOR MERGE SORT            |        |
964
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
965
|3 |    └─TOP-N SORT                       |        |
966
|4 |      └─HASH GROUP BY                  |        |
967
|5 |        └─EXCHANGE IN DISTR            |        |
968
|6 |          └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
969
|7 |            └─SORT                     |        |
970
|8 |              └─HASH GROUP BY          |        |
971
|9 |                └─PX PARTITION ITERATOR|        |
972
|10|                  └─TABLE FULL SCAN    |t1      |
973
=====================================================
974
Outputs & filters:
975
-------------------------------------
976
  0 - output([t1.c3], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))],
977
       [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
978
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
979
       DOUBLE(-1, -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), rowset=256
980
      limit(1), offset(nil)
981
  1 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
982
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
983
      sort_keys([t1.c3, ASC])
984
  2 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
985
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
986
      dop=1
987
  3 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
988
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
989
      sort_keys([t1.c3, ASC]), topn(1)
990
  4 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
991
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
992
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
993
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))])
994
  5 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
995
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
996
  6 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
997
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
998
      (#keys=1, [t1.c2]), dop=1
999
  7 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1000
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
1001
      sort_keys([t1.c3, ASC]), minimum_row_count:1 top_precision:1 , limit(1), offset(nil)
1002
  8 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1003
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
1004
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1005
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))])
1006
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
1007
      force partition granule
1008
 10 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
1009
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
1010
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1011
      range_key([t1.c1]), range(MIN ; MAX)always true
1012
explain basic select /*+topk(1 1)*/ c3, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <12 group by c2 order by c3 limit 1 offset 4;
1013
Query Plan
1014
=====================================================
1015
|ID|OPERATOR                               |NAME    |
1016
-----------------------------------------------------
1017
|0 |LIMIT                                  |        |
1018
|1 |└─PX COORDINATOR MERGE SORT            |        |
1019
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
1020
|3 |    └─TOP-N SORT                       |        |
1021
|4 |      └─HASH GROUP BY                  |        |
1022
|5 |        └─EXCHANGE IN DISTR            |        |
1023
|6 |          └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
1024
|7 |            └─SORT                     |        |
1025
|8 |              └─HASH GROUP BY          |        |
1026
|9 |                └─PX PARTITION ITERATOR|        |
1027
|10|                  └─TABLE FULL SCAN    |t1      |
1028
=====================================================
1029
Outputs & filters:
1030
-------------------------------------
1031
  0 - output([t1.c3], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))],
1032
       [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1033
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1034
       DOUBLE(-1, -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), rowset=256
1035
      limit(1), offset(4)
1036
  1 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1037
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
1038
      sort_keys([t1.c3, ASC])
1039
  2 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1040
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
1041
      dop=1
1042
  3 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1043
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
1044
      sort_keys([t1.c3, ASC]), topn(1 + 4)
1045
  4 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1046
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
1047
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1048
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))])
1049
  5 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1050
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
1051
  6 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1052
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
1053
      (#keys=1, [t1.c2]), dop=1
1054
  7 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1055
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
1056
      sort_keys([t1.c3, ASC]), minimum_row_count:1 top_precision:1 , limit(1), offset(4)
1057
  8 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1058
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
1059
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1060
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))])
1061
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
1062
      force partition granule
1063
 10 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 12]), rowset=256
1064
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
1065
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1066
      range_key([t1.c1]), range(MIN ; MAX)always true
1067
explain basic select /*+topk(1 1)*/ c3, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by c3 limit 0;
1068
Query Plan
1069
=====================================================
1070
|ID|OPERATOR                               |NAME    |
1071
-----------------------------------------------------
1072
|0 |LIMIT                                  |        |
1073
|1 |└─PX COORDINATOR MERGE SORT            |        |
1074
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
1075
|3 |    └─TOP-N SORT                       |        |
1076
|4 |      └─HASH GROUP BY                  |        |
1077
|5 |        └─EXCHANGE IN DISTR            |        |
1078
|6 |          └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
1079
|7 |            └─SORT                     |        |
1080
|8 |              └─HASH GROUP BY          |        |
1081
|9 |                └─PX PARTITION ITERATOR|        |
1082
|10|                  └─TABLE FULL SCAN    |t1      |
1083
=====================================================
1084
Outputs & filters:
1085
-------------------------------------
1086
  0 - output([t1.c3], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))],
1087
       [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1088
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1089
       DOUBLE(-1, -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), startup_filter([0]), rowset=256
1090
      limit(0), offset(nil)
1091
  1 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1092
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
1093
      sort_keys([t1.c3, ASC])
1094
  2 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1095
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
1096
      dop=1
1097
  3 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1098
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
1099
      sort_keys([t1.c3, ASC]), topn(0)
1100
  4 - output([t1.c3], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1101
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
1102
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1103
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))])
1104
  5 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1105
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
1106
  6 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1107
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
1108
      (#keys=1, [t1.c2]), dop=1
1109
  7 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1110
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
1111
      sort_keys([t1.c3, ASC]), minimum_row_count:1 top_precision:1 , limit(0), offset(nil)
1112
  8 - output([t1.c3], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1113
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
1114
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1115
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))])
1116
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
1117
      force partition granule
1118
 10 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
1119
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
1120
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1121
      range_key([t1.c1]), range(MIN ; MAX)always true
1122
explain basic  select /*+topk(1 1)*/ c2, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <12 group by c2 order by avg(c3), sum(c1)/sum(c2) limit 5;
1123
Query Plan
1124
=====================================================
1125
|ID|OPERATOR                               |NAME    |
1126
-----------------------------------------------------
1127
|0 |LIMIT                                  |        |
1128
|1 |└─PX COORDINATOR MERGE SORT            |        |
1129
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
1130
|3 |    └─TOP-N SORT                       |        |
1131
|4 |      └─HASH GROUP BY                  |        |
1132
|5 |        └─EXCHANGE IN DISTR            |        |
1133
|6 |          └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
1134
|7 |            └─SORT                     |        |
1135
|8 |              └─HASH GROUP BY          |        |
1136
|9 |                └─PX PARTITION ITERATOR|        |
1137
|10|                  └─TABLE FULL SCAN    |t1      |
1138
=====================================================
1139
Outputs & filters:
1140
-------------------------------------
1141
  0 - output([t1.c2], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))],
1142
       [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1143
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1144
       DOUBLE(-1, -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), rowset=256
1145
      limit(5), offset(nil)
1146
  1 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1147
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
1148
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1149
       DOUBLE(-1, -1)), ASC], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)), ASC])
1150
  2 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1151
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1152
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)),
1153
       DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0))]), filter(nil), rowset=256
1154
      dop=1
1155
  3 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1156
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1157
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)),
1158
       DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0))]), filter(nil), rowset=256
1159
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1160
       DOUBLE(-1, -1)), ASC], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)), ASC]), topn(5)
1161
  4 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1162
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1163
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)),
1164
       DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0))]), filter(nil), rowset=256
1165
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1166
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3,
1167
       DOUBLE(-1, -1))))])
1168
  5 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1169
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1170
  6 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1171
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1172
      (#keys=1, [t1.c2]), dop=1
1173
  7 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1174
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1175
      sort_keys([cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1)), ASC], [cast(T_FUN_SUM(t1.c1),
1176
       DECIMAL(33, 0)) / cast(T_FUN_SUM(t1.c2), DECIMAL(33, 0)), ASC]), minimum_row_count:1 top_precision:1 , limit(5), offset(nil)
1177
  8 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) 
1178
      / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [cast(T_FUN_SUM(t1.c1), DECIMAL(33,
1179
       0)) / cast(T_FUN_SUM(t1.c2), DECIMAL(33, 0))], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1180
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1181
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
1182
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
1183
      force partition granule
1184
 10 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 12]), rowset=256
1185
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
1186
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1187
      range_key([t1.c1]), range(MIN ; MAX)always true
1188
explain basic  select /*+topk(1 1)*/ c1, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by avg(c3) desc, sum(c1)/sum(c2)  limit 5;
1189
Query Plan
1190
=====================================================
1191
|ID|OPERATOR                               |NAME    |
1192
-----------------------------------------------------
1193
|0 |LIMIT                                  |        |
1194
|1 |└─PX COORDINATOR MERGE SORT            |        |
1195
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
1196
|3 |    └─TOP-N SORT                       |        |
1197
|4 |      └─HASH GROUP BY                  |        |
1198
|5 |        └─EXCHANGE IN DISTR            |        |
1199
|6 |          └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
1200
|7 |            └─SORT                     |        |
1201
|8 |              └─HASH GROUP BY          |        |
1202
|9 |                └─PX PARTITION ITERATOR|        |
1203
|10|                  └─TABLE FULL SCAN    |t1      |
1204
=====================================================
1205
Outputs & filters:
1206
-------------------------------------
1207
  0 - output([t1.c1], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))],
1208
       [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1209
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1210
       DOUBLE(-1, -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), rowset=256
1211
      limit(5), offset(nil)
1212
  1 - output([t1.c1], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1213
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
1214
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1215
       DOUBLE(-1, -1)), DESC], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)), ASC])
1216
  2 - output([t1.c1], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1217
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1218
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)),
1219
       DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0))]), filter(nil), rowset=256
1220
      dop=1
1221
  3 - output([t1.c1], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1222
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1223
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)),
1224
       DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0))]), filter(nil), rowset=256
1225
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1226
       DOUBLE(-1, -1)), DESC], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)), ASC]), topn(5)
1227
  4 - output([t1.c1], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1228
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1229
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)),
1230
       DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0))]), filter(nil), rowset=256
1231
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1232
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3,
1233
       DOUBLE(-1, -1))))])
1234
  5 - output([t1.c1], [t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)],
1235
       [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1236
  6 - output([t1.c1], [t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)],
1237
       [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1238
      (#keys=1, [t1.c2]), dop=1
1239
  7 - output([t1.c1], [t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)],
1240
       [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1241
      sort_keys([cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1)), DESC], [cast(T_FUN_SUM(t1.c1),
1242
       DECIMAL(33, 0)) / cast(T_FUN_SUM(t1.c2), DECIMAL(33, 0)), ASC]), minimum_row_count:1 top_precision:1 , limit(5), offset(nil)
1243
  8 - output([t1.c1], [t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1,
1244
       -1))) / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [cast(T_FUN_SUM(t1.c1),
1245
       DECIMAL(33, 0)) / cast(T_FUN_SUM(t1.c2), DECIMAL(33, 0))], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1246
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1247
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
1248
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
1249
      force partition granule
1250
 10 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
1251
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
1252
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1253
      range_key([t1.c1]), range(MIN ; MAX)always true
1254
explain basic select /*+topk(1 1)*/ c2, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by avg(c3) desc, sum(c1)/sum(c2) + avg(c2)  limit 5;
1255
Query Plan
1256
=====================================================
1257
|ID|OPERATOR                               |NAME    |
1258
-----------------------------------------------------
1259
|0 |LIMIT                                  |        |
1260
|1 |└─PX COORDINATOR MERGE SORT            |        |
1261
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
1262
|3 |    └─TOP-N SORT                       |        |
1263
|4 |      └─HASH GROUP BY                  |        |
1264
|5 |        └─EXCHANGE IN DISTR            |        |
1265
|6 |          └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
1266
|7 |            └─SORT                     |        |
1267
|8 |              └─HASH GROUP BY          |        |
1268
|9 |                └─PX PARTITION ITERATOR|        |
1269
|10|                  └─TABLE FULL SCAN    |t1      |
1270
=====================================================
1271
Outputs & filters:
1272
-------------------------------------
1273
  0 - output([t1.c2], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))],
1274
       [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1275
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1276
       DOUBLE(-1, -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), rowset=256
1277
      limit(5), offset(nil)
1278
  1 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1279
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
1280
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1281
       DOUBLE(-1, -1)), DESC], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) + cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
1282
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC])
1283
  2 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1284
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1285
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)),
1286
       DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) + cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)),
1287
       DECIMAL(20, 0)), DECIMAL(15, 4))]), filter(nil), rowset=256
1288
      dop=1
1289
  3 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1290
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1291
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)),
1292
       DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) + cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)),
1293
       DECIMAL(20, 0)), DECIMAL(15, 4))]), filter(nil), rowset=256
1294
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1295
       DOUBLE(-1, -1)), DESC], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) + cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)),
1296
       DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)), DECIMAL(20, 0)), DECIMAL(15, 4)), ASC]), topn(5)
1297
  4 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1298
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1299
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c1)),
1300
       DECIMAL(33, 0)) / cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) + cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2)),
1301
       DECIMAL(20, 0)), DECIMAL(15, 4))]), filter(nil), rowset=256
1302
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1303
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3,
1304
       DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c2))])
1305
  5 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)],
1306
       [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1307
  6 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)],
1308
       [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1309
      (#keys=1, [t1.c2]), dop=1
1310
  7 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)],
1311
       [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1312
      sort_keys([cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1)), DESC], [cast(T_FUN_SUM(t1.c1),
1313
       DECIMAL(33, 0)) / cast(T_FUN_SUM(t1.c2), DECIMAL(33, 0)) + cast(cast(T_FUN_SUM(t1.c2), DECIMAL(33, 0)) / cast(T_FUN_COUNT(t1.c2), DECIMAL(20, 0)), DECIMAL(15,
1314
       4)), ASC]), minimum_row_count:1 top_precision:1 , limit(5), offset(nil)
1315
  8 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) 
1316
      / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c2)], [cast(T_FUN_SUM(t1.c1),
1317
       DECIMAL(33, 0)) / cast(T_FUN_SUM(t1.c2), DECIMAL(33, 0)) + cast(cast(T_FUN_SUM(t1.c2), DECIMAL(33, 0)) / cast(T_FUN_COUNT(t1.c2), DECIMAL(20, 0)), DECIMAL(15,
1318
       4))], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1319
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1320
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(t1.c2)])
1321
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
1322
      force partition granule
1323
 10 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
1324
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
1325
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1326
      range_key([t1.c1]), range(MIN ; MAX)always true
1327
explain basic select /*+topk(1 1)*/ avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by avg(c3) desc, sum(c1)+sum(c2)  limit 5;
1328
Query Plan
1329
=====================================================
1330
|ID|OPERATOR                               |NAME    |
1331
-----------------------------------------------------
1332
|0 |LIMIT                                  |        |
1333
|1 |└─PX COORDINATOR MERGE SORT            |        |
1334
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
1335
|3 |    └─TOP-N SORT                       |        |
1336
|4 |      └─HASH GROUP BY                  |        |
1337
|5 |        └─EXCHANGE IN DISTR            |        |
1338
|6 |          └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
1339
|7 |            └─SORT                     |        |
1340
|8 |              └─HASH GROUP BY          |        |
1341
|9 |                └─PX PARTITION ITERATOR|        |
1342
|10|                  └─TABLE FULL SCAN    |t1      |
1343
=====================================================
1344
Outputs & filters:
1345
-------------------------------------
1346
  0 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))],
1347
       [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))],
1348
       [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1,
1349
       -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), rowset=256
1350
      limit(5), offset(nil)
1351
  1 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1352
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
1353
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1354
       DOUBLE(-1, -1)), DESC], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC])
1355
  2 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1356
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))],
1357
       [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
1358
      dop=1
1359
  3 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1360
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))],
1361
       [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
1362
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1363
       DOUBLE(-1, -1)), DESC], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC]), topn(5)
1364
  4 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1365
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))],
1366
       [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))]), filter(nil), rowset=256
1367
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1368
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3,
1369
       DOUBLE(-1, -1))))])
1370
  5 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1371
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1372
  6 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1373
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1374
      (#keys=1, [t1.c2]), dop=1
1375
  7 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1376
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1377
      sort_keys([cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1)), DESC], [T_FUN_SUM(t1.c1) 
1378
      + T_FUN_SUM(t1.c2), ASC]), minimum_row_count:1 top_precision:1 , limit(5), offset(nil)
1379
  8 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) 
1380
      / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1) + T_FUN_SUM(t1.c2)],
1381
       [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1382
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1383
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
1384
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
1385
      force partition granule
1386
 10 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
1387
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
1388
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1389
      range_key([t1.c1]), range(MIN ; MAX)always true
1390
explain basic select /*+topk(0 0)*/ c2, sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by avg(c3) desc, sum(c1)+sum(c2)  limit 5;
1391
Query Plan
1392
===================================================
1393
|ID|OPERATOR                             |NAME    |
1394
---------------------------------------------------
1395
|0 |LIMIT                                |        |
1396
|1 |└─PX COORDINATOR MERGE SORT          |        |
1397
|2 |  └─EXCHANGE OUT DISTR               |:EX10001|
1398
|3 |    └─TOP-N SORT                     |        |
1399
|4 |      └─HASH GROUP BY                |        |
1400
|5 |        └─EXCHANGE IN DISTR          |        |
1401
|6 |          └─EXCHANGE OUT DISTR (HASH)|:EX10000|
1402
|7 |            └─HASH GROUP BY          |        |
1403
|8 |              └─PX PARTITION ITERATOR|        |
1404
|9 |                └─TABLE FULL SCAN    |t1      |
1405
===================================================
1406
Outputs & filters:
1407
-------------------------------------
1408
  0 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1409
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)),
1410
       DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), rowset=256
1411
      limit(5), offset(nil)
1412
  1 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1413
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1414
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1415
       DOUBLE(-1, -1)), DESC], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC])
1416
  2 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1417
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1418
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1419
      dop=1
1420
  3 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1421
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1422
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1423
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1424
       DOUBLE(-1, -1)), DESC], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC]), topn(5)
1425
  4 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1426
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1427
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1428
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1429
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3,
1430
       DOUBLE(-1, -1))))])
1431
  5 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(cast(t1.c3,
1432
       DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
1433
  6 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(cast(t1.c3,
1434
       DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
1435
      (#keys=1, [t1.c2]), dop=1
1436
  7 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)], [T_FUN_SUM(cast(t1.c3,
1437
       DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
1438
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1439
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
1440
  8 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
1441
      force partition granule
1442
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
1443
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
1444
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1445
      range_key([t1.c1]), range(MIN ; MAX)always true
1446
explain basic select /*+topk(0 10)*/ c2, sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by avg(c3) desc, sum(c1)+sum(c2)  limit 5;
1447
Query Plan
1448
=====================================================
1449
|ID|OPERATOR                               |NAME    |
1450
-----------------------------------------------------
1451
|0 |LIMIT                                  |        |
1452
|1 |└─PX COORDINATOR MERGE SORT            |        |
1453
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
1454
|3 |    └─TOP-N SORT                       |        |
1455
|4 |      └─HASH GROUP BY                  |        |
1456
|5 |        └─EXCHANGE IN DISTR            |        |
1457
|6 |          └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
1458
|7 |            └─SORT                     |        |
1459
|8 |              └─HASH GROUP BY          |        |
1460
|9 |                └─PX PARTITION ITERATOR|        |
1461
|10|                  └─TABLE FULL SCAN    |t1      |
1462
=====================================================
1463
Outputs & filters:
1464
-------------------------------------
1465
  0 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1466
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)),
1467
       DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), rowset=256
1468
      limit(5), offset(nil)
1469
  1 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1470
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1471
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1472
       DOUBLE(-1, -1)), DESC], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC])
1473
  2 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1474
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1475
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1476
      dop=1
1477
  3 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1478
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1479
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1480
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1481
       DOUBLE(-1, -1)), DESC], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC]), topn(5)
1482
  4 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1483
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1484
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1485
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1486
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3,
1487
       DOUBLE(-1, -1))))])
1488
  5 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1489
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1490
  6 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1491
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1492
      (#keys=1, [t1.c2]), dop=1
1493
  7 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1494
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1495
      sort_keys([cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1)), DESC], [T_FUN_SUM(t1.c1) 
1496
      + T_FUN_SUM(t1.c2), ASC]), minimum_row_count:10 top_precision:0 , limit(5), offset(nil)
1497
  8 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) 
1498
      / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1) + T_FUN_SUM(t1.c2)],
1499
       [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1500
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1501
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
1502
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
1503
      force partition granule
1504
 10 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
1505
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
1506
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1507
      range_key([t1.c1]), range(MIN ; MAX)always true
1508
explain basic select /*+topk(50 0)*/ c2, sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by avg(c3) desc, sum(c1)+sum(c2)  limit 5;
1509
Query Plan
1510
=====================================================
1511
|ID|OPERATOR                               |NAME    |
1512
-----------------------------------------------------
1513
|0 |LIMIT                                  |        |
1514
|1 |└─PX COORDINATOR MERGE SORT            |        |
1515
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
1516
|3 |    └─TOP-N SORT                       |        |
1517
|4 |      └─HASH GROUP BY                  |        |
1518
|5 |        └─EXCHANGE IN DISTR            |        |
1519
|6 |          └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
1520
|7 |            └─SORT                     |        |
1521
|8 |              └─HASH GROUP BY          |        |
1522
|9 |                └─PX PARTITION ITERATOR|        |
1523
|10|                  └─TABLE FULL SCAN    |t1      |
1524
=====================================================
1525
Outputs & filters:
1526
-------------------------------------
1527
  0 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1528
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)),
1529
       DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), rowset=256
1530
      limit(5), offset(nil)
1531
  1 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1532
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1533
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1534
       DOUBLE(-1, -1)), DESC], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC])
1535
  2 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1536
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1537
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1538
      dop=1
1539
  3 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1540
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1541
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1542
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1543
       DOUBLE(-1, -1)), DESC], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC]), topn(5)
1544
  4 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1545
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1546
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1547
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1548
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3,
1549
       DOUBLE(-1, -1))))])
1550
  5 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1551
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1552
  6 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1553
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1554
      (#keys=1, [t1.c2]), dop=1
1555
  7 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1556
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1557
      sort_keys([cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1)), DESC], [T_FUN_SUM(t1.c1) 
1558
      + T_FUN_SUM(t1.c2), ASC]), minimum_row_count:0 top_precision:50 , limit(5), offset(nil)
1559
  8 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) 
1560
      / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1) + T_FUN_SUM(t1.c2)],
1561
       [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1562
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1563
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
1564
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
1565
      force partition granule
1566
 10 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
1567
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
1568
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1569
      range_key([t1.c1]), range(MIN ; MAX)always true
1570
explain basic select /*+topk(10000 1)*/ c2, sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by avg(c3) desc, sum(c1)+sum(c2)  limit 5;
1571
Query Plan
1572
=====================================================
1573
|ID|OPERATOR                               |NAME    |
1574
-----------------------------------------------------
1575
|0 |LIMIT                                  |        |
1576
|1 |└─PX COORDINATOR MERGE SORT            |        |
1577
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
1578
|3 |    └─TOP-N SORT                       |        |
1579
|4 |      └─HASH GROUP BY                  |        |
1580
|5 |        └─EXCHANGE IN DISTR            |        |
1581
|6 |          └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
1582
|7 |            └─SORT                     |        |
1583
|8 |              └─HASH GROUP BY          |        |
1584
|9 |                └─PX PARTITION ITERATOR|        |
1585
|10|                  └─TABLE FULL SCAN    |t1      |
1586
=====================================================
1587
Outputs & filters:
1588
-------------------------------------
1589
  0 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1590
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)),
1591
       DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), rowset=256
1592
      limit(5), offset(nil)
1593
  1 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1594
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1595
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1596
       DOUBLE(-1, -1)), DESC], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC])
1597
  2 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1598
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1599
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1600
      dop=1
1601
  3 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1602
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1603
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1604
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1605
       DOUBLE(-1, -1)), DESC], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC]), topn(5)
1606
  4 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1607
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1608
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1609
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1610
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3,
1611
       DOUBLE(-1, -1))))])
1612
  5 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1613
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1614
  6 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1615
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1616
      (#keys=1, [t1.c2]), dop=1
1617
  7 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1618
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1619
      sort_keys([cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1)), DESC], [T_FUN_SUM(t1.c1) 
1620
      + T_FUN_SUM(t1.c2), ASC]), minimum_row_count:1 top_precision:10000 , limit(5), offset(nil)
1621
  8 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) 
1622
      / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1) + T_FUN_SUM(t1.c2)],
1623
       [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1624
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1625
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
1626
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
1627
      force partition granule
1628
 10 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
1629
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
1630
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1631
      range_key([t1.c1]), range(MIN ; MAX)always true
1632
explain basic select /*+topk(1 10000)*/ c2, sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1)  from t1 where c2 <10 group by c2 order by avg(c3) desc, sum(c1)+sum(c2)  limit 5;
1633
Query Plan
1634
=====================================================
1635
|ID|OPERATOR                               |NAME    |
1636
-----------------------------------------------------
1637
|0 |LIMIT                                  |        |
1638
|1 |└─PX COORDINATOR MERGE SORT            |        |
1639
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
1640
|3 |    └─TOP-N SORT                       |        |
1641
|4 |      └─HASH GROUP BY                  |        |
1642
|5 |        └─EXCHANGE IN DISTR            |        |
1643
|6 |          └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
1644
|7 |            └─SORT                     |        |
1645
|8 |              └─HASH GROUP BY          |        |
1646
|9 |                └─PX PARTITION ITERATOR|        |
1647
|10|                  └─TABLE FULL SCAN    |t1      |
1648
=====================================================
1649
Outputs & filters:
1650
-------------------------------------
1651
  0 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1652
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)),
1653
       DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), rowset=256
1654
      limit(5), offset(nil)
1655
  1 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1656
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1657
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1658
       DOUBLE(-1, -1)), DESC], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC])
1659
  2 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1660
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1661
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1662
      dop=1
1663
  3 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1664
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1665
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1666
      sort_keys([cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)),
1667
       DOUBLE(-1, -1)), DESC], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC]), topn(5)
1668
  4 - output([t1.c2], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))],
1669
       [T_FUN_MIN(T_FUN_MIN(t1.c3))], [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [cast(T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1670
       DOUBLE(-1, -1)))) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1671
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1672
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))], [T_FUN_COUNT_SUM(T_FUN_COUNT(cast(t1.c3,
1673
       DOUBLE(-1, -1))))])
1674
  5 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1675
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1676
  6 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1677
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1678
      (#keys=1, [t1.c2]), dop=1
1679
  7 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_COUNT(t1.c3)],
1680
       [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1681
      sort_keys([cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1)), DESC], [T_FUN_SUM(t1.c1) 
1682
      + T_FUN_SUM(t1.c2), ASC]), minimum_row_count:10000 top_precision:1 , limit(5), offset(nil)
1683
  8 - output([t1.c2], [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))], [cast(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))) 
1684
      / cast(T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1))), DOUBLE(-1, -1)), DOUBLE(-1, -1))], [T_FUN_SUM(t1.c1)], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1) + T_FUN_SUM(t1.c2)],
1685
       [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1686
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
1687
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))], [T_FUN_COUNT(cast(t1.c3, DOUBLE(-1, -1)))])
1688
  9 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=256
1689
      force partition granule
1690
 10 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 < 10]), rowset=256
1691
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])
1692
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1693
      range_key([t1.c1]), range(MIN ; MAX)always true
1694
set @@ob_enable_transformation=default;
1695
explain basic select * from (select /*+topk(0 4)*/ avg(c1)  from t1 where c2 <10 group by c2 order by sum(c2) limit 5) as a;
1696
Query Plan
1697
=====================================================
1698
|ID|OPERATOR                               |NAME    |
1699
-----------------------------------------------------
1700
|0 |LIMIT                                  |        |
1701
|1 |└─PX COORDINATOR MERGE SORT            |        |
1702
|2 |  └─EXCHANGE OUT DISTR                 |:EX10001|
1703
|3 |    └─TOP-N SORT                       |        |
1704
|4 |      └─HASH GROUP BY                  |        |
1705
|5 |        └─EXCHANGE IN DISTR            |        |
1706
|6 |          └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
1707
|7 |            └─SORT                     |        |
1708
|8 |              └─HASH GROUP BY          |        |
1709
|9 |                └─PX PARTITION ITERATOR|        |
1710
|10|                  └─TABLE FULL SCAN    |t1      |
1711
=====================================================
1712
Outputs & filters:
1713
-------------------------------------
1714
  0 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))]), filter(nil), rowset=256
1715
      limit(5), offset(nil)
1716
  1 - output([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1717
      sort_keys([T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC])
1718
  2 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1719
      dop=1
1720
  3 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1721
      sort_keys([T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC]), topn(5)
1722
  4 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1723
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))])
1724
  5 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1725
  6 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1726
      (#keys=1, [t1.c2]), dop=1
1727
  7 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1728
      sort_keys([T_FUN_SUM(t1.c2), ASC]), minimum_row_count:4 top_precision:0 , limit(5), offset(nil)
1729
  8 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1730
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)])
1731
  9 - output([t1.c1], [t1.c2]), filter(nil), rowset=256
1732
      force partition granule
1733
 10 - output([t1.c1], [t1.c2]), filter([t1.c2 < 10]), rowset=256
1734
      access([t1.c1], [t1.c2]), partitions(p[0-1])
1735
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1736
      range_key([t1.c1]), range(MIN ; MAX)always true
1737
explain basic select c1  from t1 where c1 < any (select /*+topk(0 4)*/ avg(c1)  from t1 where c2 <10 group by c2 order by sum(c2)  limit 5);
1738
Query Plan
1739
===========================================================
1740
|ID|OPERATOR                                     |NAME    |
1741
-----------------------------------------------------------
1742
|0 |NESTED-LOOP SEMI JOIN                        |        |
1743
|1 |├─PX COORDINATOR                             |        |
1744
|2 |│ └─EXCHANGE OUT DISTR                       |:EX10000|
1745
|3 |│   └─PX PARTITION ITERATOR                  |        |
1746
|4 |│     └─TABLE FULL SCAN                      |t1      |
1747
|5 |└─MATERIAL                                   |        |
1748
|6 |  └─SUBPLAN SCAN                             |VIEW1   |
1749
|7 |    └─LIMIT                                  |        |
1750
|8 |      └─PX COORDINATOR MERGE SORT            |        |
1751
|9 |        └─EXCHANGE OUT DISTR                 |:EX20001|
1752
|10|          └─TOP-N SORT                       |        |
1753
|11|            └─HASH GROUP BY                  |        |
1754
|12|              └─EXCHANGE IN DISTR            |        |
1755
|13|                └─EXCHANGE OUT DISTR (HASH)  |:EX20000|
1756
|14|                  └─SORT                     |        |
1757
|15|                    └─HASH GROUP BY          |        |
1758
|16|                      └─PX PARTITION ITERATOR|        |
1759
|17|                        └─TABLE FULL SCAN    |t1      |
1760
===========================================================
1761
Outputs & filters:
1762
-------------------------------------
1763
  0 - output([t1.c1]), filter(nil), rowset=256
1764
      conds([cast(t1.c1, DECIMAL(11, 0)) < VIEW1.avg(c1)]), nl_params_(nil), use_batch=false
1765
  1 - output([t1.c1]), filter(nil), rowset=256
1766
  2 - output([t1.c1]), filter(nil), rowset=256
1767
      dop=1
1768
  3 - output([t1.c1]), filter(nil), rowset=256
1769
      force partition granule
1770
  4 - output([t1.c1]), filter(nil), rowset=256
1771
      access([t1.c1]), partitions(p[0-1])
1772
      is_index_back=false, is_global_index=false, 
1773
      range_key([t1.c1]), range(MIN ; MAX)always true
1774
  5 - output([VIEW1.avg(c1)]), filter(nil), rowset=256
1775
  6 - output([VIEW1.avg(c1)]), filter(nil), rowset=256
1776
      access([VIEW1.avg(c1)])
1777
  7 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))]), filter(nil), rowset=256
1778
      limit(5), offset(nil)
1779
  8 - output([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1780
      sort_keys([T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC])
1781
  9 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1782
      dop=1
1783
 10 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1784
      sort_keys([T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC]), topn(5)
1785
 11 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1786
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))])
1787
 12 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1788
 13 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1789
      (#keys=1, [t1.c2]), dop=1
1790
 14 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1791
      sort_keys([T_FUN_SUM(t1.c2), ASC]), minimum_row_count:4 top_precision:0 , limit(5), offset(nil)
1792
 15 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1793
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)])
1794
 16 - output([t1.c1], [t1.c2]), filter(nil), rowset=256
1795
      force partition granule
1796
 17 - output([t1.c1], [t1.c2]), filter([t1.c2 < 10]), rowset=256
1797
      access([t1.c1], [t1.c2]), partitions(p[0-1])
1798
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1799
      range_key([t1.c1]), range(MIN ; MAX)always true
1800
explain basic (select /*+topk(0 4)*/ avg(c1)  from t1 where c2 <10 group by c2 order by sum(c2) limit 5) union (select /*+topk(0 10)*/ sum(c1)  from t1 where c2 <10 group by c2 order by sum(c2) limit 7);
1801
Query Plan
1802
=======================================================
1803
|ID|OPERATOR                             |NAME        |
1804
-------------------------------------------------------
1805
|0 |TEMP TABLE TRANSFORMATION            |            |
1806
|1 |├─PX COORDINATOR                     |            |
1807
|2 |│ └─EXCHANGE OUT DISTR               |:EX10001    |
1808
|3 |│   └─TEMP TABLE INSERT              |TEMP1       |
1809
|4 |│     └─HASH GROUP BY                |            |
1810
|5 |│       └─EXCHANGE IN DISTR          |            |
1811
|6 |│         └─EXCHANGE OUT DISTR (HASH)|:EX10000    |
1812
|7 |│           └─HASH GROUP BY          |            |
1813
|8 |│             └─PX PARTITION ITERATOR|            |
1814
|9 |│               └─TABLE FULL SCAN    |t1          |
1815
|10|└─HASH UNION DISTINCT                |            |
1816
|11|  ├─LIMIT                            |            |
1817
|12|  │ └─PX COORDINATOR MERGE SORT      |            |
1818
|13|  │   └─EXCHANGE OUT DISTR           |:EX20000    |
1819
|14|  │     └─TOP-N SORT                 |            |
1820
|15|  │       └─TEMP TABLE ACCESS        |VIEW1(TEMP1)|
1821
|16|  └─LIMIT                            |            |
1822
|17|    └─PX COORDINATOR MERGE SORT      |            |
1823
|18|      └─EXCHANGE OUT DISTR           |:EX30000    |
1824
|19|        └─TOP-N SORT                 |            |
1825
|20|          └─TEMP TABLE ACCESS        |VIEW2(TEMP1)|
1826
=======================================================
1827
Outputs & filters:
1828
-------------------------------------
1829
  0 - output([UNION([1])]), filter(nil), rowset=256
1830
  1 - output(nil), filter(nil), rowset=256
1831
  2 - output(nil), filter(nil), rowset=256
1832
      dop=1
1833
  3 - output(nil), filter(nil), rowset=256
1834
  4 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1835
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))])
1836
  5 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1837
  6 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1838
      (#keys=1, [t1.c2]), dop=1
1839
  7 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1840
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)])
1841
  8 - output([t1.c1], [t1.c2]), filter(nil), rowset=256
1842
      force partition granule
1843
  9 - output([t1.c1], [t1.c2]), filter([t1.c2 < 10]), rowset=256
1844
      access([t1.c1], [t1.c2]), partitions(p[0-1])
1845
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1846
      range_key([t1.c1]), range(MIN ; MAX)always true
1847
 10 - output([UNION([1])]), filter(nil), rowset=256
1848
 11 - output([cast(cast(VIEW1.T_FUN_SUM(t1.c1), DECIMAL(33, 0)) / cast(VIEW1.T_FUN_COUNT(t1.c1), DECIMAL(20, 0)), DECIMAL(15, 4))]), filter(nil), rowset=256
1849
      limit(5), offset(nil)
1850
 12 - output([VIEW1.T_FUN_SUM(t1.c1)], [VIEW1.T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1851
      sort_keys([VIEW1.T_FUN_SUM(t1.c2), ASC])
1852
 13 - output([VIEW1.T_FUN_SUM(t1.c2)], [VIEW1.T_FUN_SUM(t1.c1)], [VIEW1.T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1853
      dop=1
1854
 14 - output([VIEW1.T_FUN_SUM(t1.c2)], [VIEW1.T_FUN_SUM(t1.c1)], [VIEW1.T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1855
      sort_keys([VIEW1.T_FUN_SUM(t1.c2), ASC]), topn(5)
1856
 15 - output([VIEW1.T_FUN_SUM(t1.c2)], [VIEW1.T_FUN_SUM(t1.c1)], [VIEW1.T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1857
      access([VIEW1.T_FUN_SUM(t1.c2)], [VIEW1.T_FUN_SUM(t1.c1)], [VIEW1.T_FUN_COUNT(t1.c1)])
1858
 16 - output([cast(VIEW2.T_FUN_SUM(t1.c1), DECIMAL(37, 4))]), filter(nil), rowset=256
1859
      limit(7), offset(nil)
1860
 17 - output([VIEW2.T_FUN_SUM(t1.c1)]), filter(nil), rowset=256
1861
      sort_keys([VIEW2.T_FUN_SUM(t1.c2), ASC])
1862
 18 - output([VIEW2.T_FUN_SUM(t1.c2)], [VIEW2.T_FUN_SUM(t1.c1)]), filter(nil), rowset=256
1863
      dop=1
1864
 19 - output([VIEW2.T_FUN_SUM(t1.c2)], [VIEW2.T_FUN_SUM(t1.c1)]), filter(nil), rowset=256
1865
      sort_keys([VIEW2.T_FUN_SUM(t1.c2), ASC]), topn(7)
1866
 20 - output([VIEW2.T_FUN_SUM(t1.c1)], [VIEW2.T_FUN_SUM(t1.c2)]), filter(nil), rowset=256
1867
      access([VIEW2.T_FUN_SUM(t1.c1)], [VIEW2.T_FUN_SUM(t1.c2)])
1868
explain basic select c1  from t1 where (select /*+topk(0 10)*/ avg(c1)  from t1 where c2 <10 group by c2 order by sum(c2) limit 1) > 1 ;
1869
Query Plan
1870
=======================================================
1871
|ID|OPERATOR                                 |NAME    |
1872
-------------------------------------------------------
1873
|0 |SUBPLAN FILTER                           |        |
1874
|1 |├─PX COORDINATOR                         |        |
1875
|2 |│ └─EXCHANGE OUT DISTR                   |:EX10000|
1876
|3 |│   └─PX PARTITION ITERATOR              |        |
1877
|4 |│     └─TABLE FULL SCAN                  |t1      |
1878
|5 |└─LIMIT                                  |        |
1879
|6 |  └─PX COORDINATOR MERGE SORT            |        |
1880
|7 |    └─EXCHANGE OUT DISTR                 |:EX20001|
1881
|8 |      └─TOP-N SORT                       |        |
1882
|9 |        └─HASH GROUP BY                  |        |
1883
|10|          └─EXCHANGE IN DISTR            |        |
1884
|11|            └─EXCHANGE OUT DISTR (HASH)  |:EX20000|
1885
|12|              └─SORT                     |        |
1886
|13|                └─HASH GROUP BY          |        |
1887
|14|                  └─PX PARTITION ITERATOR|        |
1888
|15|                    └─TABLE FULL SCAN    |t1      |
1889
=======================================================
1890
Outputs & filters:
1891
-------------------------------------
1892
  0 - output([t1.c1]), filter(nil), rowset=256
1893
      exec_params_(nil), onetime_exprs_([subquery(1)(:0)]), init_plan_idxs_(nil), use_batch=false
1894
  1 - output([t1.c1]), filter(nil), startup_filter([:0 > cast(1, DECIMAL(1, 0))]), rowset=256
1895
  2 - output([t1.c1]), filter(nil), rowset=256
1896
      dop=1
1897
  3 - output([t1.c1]), filter(nil), rowset=256
1898
      force partition granule
1899
  4 - output([t1.c1]), filter(nil), rowset=256
1900
      access([t1.c1]), partitions(p[0-1])
1901
      is_index_back=false, is_global_index=false, 
1902
      range_key([t1.c1]), range(MIN ; MAX)always true
1903
  5 - output([cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15, 4))]), filter(nil), rowset=256
1904
      limit(1), offset(nil)
1905
  6 - output([T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1906
      sort_keys([T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC])
1907
  7 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1908
      dop=1
1909
  8 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1910
      sort_keys([T_FUN_SUM(T_FUN_SUM(t1.c2)), ASC]), topn(1)
1911
  9 - output([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))]), filter(nil), rowset=256
1912
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))])
1913
 10 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1914
 11 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1915
      (#keys=1, [t1.c2]), dop=1
1916
 12 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1917
      sort_keys([T_FUN_SUM(t1.c2), ASC]), minimum_row_count:10 top_precision:0 , limit(1), offset(nil)
1918
 13 - output([t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)]), filter(nil), rowset=256
1919
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c1)])
1920
 14 - output([t1.c1], [t1.c2]), filter(nil), rowset=256
1921
      force partition granule
1922
 15 - output([t1.c1], [t1.c2]), filter([t1.c2 < 10]), rowset=256
1923
      access([t1.c1], [t1.c2]), partitions(p[0-1])
1924
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
1925
      range_key([t1.c1]), range(MIN ; MAX)always true
1926
explain basic select * from (select /*+topk(10 5)*/ c3, c1, avg(c1), sum(c2), sum(c1) + sum(c2), count(c3), min(c3), max(c1), sum(c2)/count(c1), sum(c3)/sum(c1) from t1 group by c2 order by c3 limit 7) as a left join t2 on a.c1 = t2.c1 left join t3 on t3.c1 = a.c1;
1927
Query Plan
1928
=======================================================================
1929
|ID|OPERATOR                                                 |NAME    |
1930
-----------------------------------------------------------------------
1931
|0 |PX COORDINATOR                                           |        |
1932
|1 |└─EXCHANGE OUT DISTR                                     |:EX10004|
1933
|2 |  └─HASH RIGHT OUTER JOIN                                |        |
1934
|3 |    ├─PX PARTITION ITERATOR                              |        |
1935
|4 |    │ └─TABLE FULL SCAN                                  |t3      |
1936
|5 |    └─EXCHANGE IN DISTR                                  |        |
1937
|6 |      └─EXCHANGE OUT DISTR (PKEY)                        |:EX10003|
1938
|7 |        └─HASH RIGHT OUTER JOIN                          |        |
1939
|8 |          ├─PX PARTITION ITERATOR                        |        |
1940
|9 |          │ └─TABLE FULL SCAN                            |t2      |
1941
|10|          └─EXCHANGE IN DISTR                            |        |
1942
|11|            └─EXCHANGE OUT DISTR (PKEY)                  |:EX10002|
1943
|12|              └─SUBPLAN SCAN                             |a       |
1944
|13|                └─LIMIT                                  |        |
1945
|14|                  └─EXCHANGE IN MERGE SORT DISTR         |        |
1946
|15|                    └─EXCHANGE OUT DISTR                 |:EX10001|
1947
|16|                      └─TOP-N SORT                       |        |
1948
|17|                        └─HASH GROUP BY                  |        |
1949
|18|                          └─EXCHANGE IN DISTR            |        |
1950
|19|                            └─EXCHANGE OUT DISTR (HASH)  |:EX10000|
1951
|20|                              └─SORT                     |        |
1952
|21|                                └─HASH GROUP BY          |        |
1953
|22|                                  └─PX PARTITION ITERATOR|        |
1954
|23|                                    └─TABLE FULL SCAN    |t1      |
1955
=======================================================================
1956
Outputs & filters:
1957
-------------------------------------
1958
  0 - output([INTERNAL_FUNCTION(a.c3, a.c1, a.avg(c1), a.sum(c2), a.sum(c1) + sum(c2), a.count(c3), a.min(c3), a.max(c1), a.sum(c2)/count(c1), a.sum(c3)/sum(c1),
1959
       t2.c1, t2.c2, t2.c3, t3.c1, t3.c2, t3.c3)]), filter(nil), rowset=256
1960
  1 - output([INTERNAL_FUNCTION(a.c3, a.c1, a.avg(c1), a.sum(c2), a.sum(c1) + sum(c2), a.count(c3), a.min(c3), a.max(c1), a.sum(c2)/count(c1), a.sum(c3)/sum(c1),
1961
       t2.c1, t2.c2, t2.c3, t3.c1, t3.c2, t3.c3)]), filter(nil), rowset=256
1962
      dop=1
1963
  2 - output([t3.c1], [a.c1], [t3.c2], [t3.c3], [t2.c1], [t2.c2], [t2.c3], [a.c3], [a.avg(c1)], [a.sum(c2)], [a.sum(c1) + sum(c2)], [a.count(c3)], [a.min(c3)],
1964
       [a.max(c1)], [a.sum(c2)/count(c1)], [a.sum(c3)/sum(c1)]), filter(nil), rowset=256
1965
      equal_conds([t3.c1 = a.c1]), other_conds(nil)
1966
  3 - output([t3.c1], [t3.c2], [t3.c3]), filter(nil), rowset=256
1967
      affinitize, force partition granule
1968
  4 - output([t3.c1], [t3.c2], [t3.c3]), filter(nil), rowset=256
1969
      access([t3.c1], [t3.c2], [t3.c3]), partitions(p[0-1])
1970
      is_index_back=false, is_global_index=false, 
1971
      range_key([t3.c1]), range(MIN ; MAX)always true
1972
  5 - output([a.c1], [t2.c1], [t2.c2], [t2.c3], [a.c3], [a.avg(c1)], [a.sum(c2)], [a.sum(c1) + sum(c2)], [a.count(c3)], [a.min(c3)], [a.max(c1)], [a.sum(c2)/count(c1)],
1973
       [a.sum(c3)/sum(c1)]), filter(nil), rowset=256
1974
  6 - output([a.c1], [t2.c1], [t2.c2], [t2.c3], [a.c3], [a.avg(c1)], [a.sum(c2)], [a.sum(c1) + sum(c2)], [a.count(c3)], [a.min(c3)], [a.max(c1)], [a.sum(c2)/count(c1)],
1975
       [a.sum(c3)/sum(c1)]), filter(nil), rowset=256
1976
      (#keys=1, [a.c1]), dop=1
1977
  7 - output([a.c1], [t2.c1], [t2.c2], [t2.c3], [a.c3], [a.avg(c1)], [a.sum(c2)], [a.sum(c1) + sum(c2)], [a.count(c3)], [a.min(c3)], [a.max(c1)], [a.sum(c2)/count(c1)],
1978
       [a.sum(c3)/sum(c1)]), filter(nil), rowset=256
1979
      equal_conds([a.c1 = t2.c1]), other_conds(nil)
1980
  8 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=256
1981
      affinitize, force partition granule
1982
  9 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=256
1983
      access([t2.c1], [t2.c2], [t2.c3]), partitions(p[0-1])
1984
      is_index_back=false, is_global_index=false, 
1985
      range_key([t2.c1]), range(MIN ; MAX)always true
1986
 10 - output([a.c1], [a.c3], [a.avg(c1)], [a.sum(c2)], [a.sum(c1) + sum(c2)], [a.count(c3)], [a.min(c3)], [a.max(c1)], [a.sum(c2)/count(c1)], [a.sum(c3)/sum(c1)]), filter(nil), rowset=256
1987
 11 - output([a.c1], [a.c3], [a.avg(c1)], [a.sum(c2)], [a.sum(c1) + sum(c2)], [a.count(c3)], [a.min(c3)], [a.max(c1)], [a.sum(c2)/count(c1)], [a.sum(c3)/sum(c1)]), filter(nil), rowset=256
1988
      (#keys=1, [a.c1]), is_single, dop=1
1989
 12 - output([a.c1], [a.c3], [a.avg(c1)], [a.sum(c2)], [a.sum(c1) + sum(c2)], [a.count(c3)], [a.min(c3)], [a.max(c1)], [a.sum(c2)/count(c1)], [a.sum(c3)/sum(c1)]), filter(nil), rowset=256
1990
      access([a.c1], [a.c3], [a.avg(c1)], [a.sum(c2)], [a.sum(c1) + sum(c2)], [a.count(c3)], [a.min(c3)], [a.max(c1)], [a.sum(c2)/count(c1)], [a.sum(c3)/sum(c1)])
1991
 13 - output([t1.c3], [t1.c1], [cast(cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0)), DECIMAL(15,
1992
       4))], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1)) + T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1993
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [cast(T_FUN_SUM(T_FUN_SUM(t1.c2)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3,
1994
       DOUBLE(-1, -1)))) / cast(T_FUN_SUM(T_FUN_SUM(t1.c1)), DOUBLE(33, 0))]), filter(nil), rowset=256
1995
      limit(7), offset(nil)
1996
 14 - output([t1.c3], [t1.c1], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
1997
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
1998
      sort_keys([t1.c3, ASC])
1999
 15 - output([t1.c3], [t1.c1], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
2000
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
2001
      dop=1
2002
 16 - output([t1.c3], [t1.c1], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
2003
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
2004
      sort_keys([t1.c3, ASC]), topn(7)
2005
 17 - output([t1.c3], [t1.c1], [T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
2006
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))]), filter(nil), rowset=256
2007
      group([t1.c2]), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c2))], [T_FUN_SUM(T_FUN_SUM(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c3))], [T_FUN_MIN(T_FUN_MIN(t1.c3))],
2008
       [T_FUN_MAX(T_FUN_MAX(t1.c1))], [T_FUN_COUNT_SUM(T_FUN_COUNT(t1.c1))], [T_FUN_SUM(T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1))))])
2009
 18 - output([t1.c3], [t1.c1], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
2010
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
2011
 19 - output([t1.c3], [t1.c1], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
2012
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
2013
      (#keys=1, [t1.c2]), dop=1
2014
 20 - output([t1.c3], [t1.c1], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
2015
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
2016
      sort_keys([t1.c3, ASC]), minimum_row_count:5 top_precision:10 , limit(7), offset(nil)
2017
 21 - output([t1.c3], [t1.c1], [t1.c2], [T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
2018
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))]), filter(nil), rowset=256
2019
      group([t1.c2]), agg_func([T_FUN_SUM(t1.c2)], [T_FUN_SUM(t1.c1)], [T_FUN_COUNT(t1.c3)], [T_FUN_MIN(t1.c3)], [T_FUN_MAX(t1.c1)], [T_FUN_COUNT(t1.c1)],
2020
       [T_FUN_SUM(cast(t1.c3, DOUBLE(-1, -1)))])
2021
 22 - output([t1.c1], [t1.c3], [t1.c2]), filter(nil), rowset=256
2022
      force partition granule
2023
 23 - output([t1.c1], [t1.c3], [t1.c2]), filter(nil), rowset=256
2024
      access([t1.c1], [t1.c3], [t1.c2]), partitions(p[0-1])
2025
      is_index_back=false, is_global_index=false, 
2026
      range_key([t1.c1]), range(MIN ; MAX)always true
2027
drop table t1;
2028
drop table t2;
2029
drop table t3;
2030