testdata/workloads/functional-query/queries/QueryTest/aggregation.test

====
---- QUERY
# test a larger dataset, includes nulls
# the exact result could vary slightly due to numeric instability
# 0.001 is a conservative upperbound on the possible difference in results
SELECT abs(cast(variance(tinyint_col) as double) - 6.66741) < 0.001,
 abs(cast(variance(double_col) as double) - 8470806.708) < 0.001
from alltypesagg
---- RESULTS
true,true
---- TYPES
boolean, boolean
====
---- QUERY
# No tuples processed (should return null)
SELECT variance(tinyint_col), stddev(smallint_col), variance_pop(int_col),
stddev_pop(bigint_col)
from alltypesagg WHERE id = -9999999
---- RESULTS
NULL,NULL,NULL,NULL
---- TYPES
double, double, double, double
====
---- QUERY
# exactly 1 tuple processed (variance_pop & stddev_pop are 0, stddev and variance
# are NULL)
SELECT variance(tinyint_col), variance_samp(smallint_col), variance_pop(int_col),
stddev(smallint_col), stddev_samp(smallint_col), stddev_pop(bigint_col)
from alltypesagg WHERE id = 1006
---- RESULTS
NULL,NULL,0,NULL,NULL,0
---- TYPES
double, double, double, double, double, double
====
---- QUERY
# Includes one row which is null, and test the aliases for variance() as well
SELECT variance(tinyint_col), variance(smallint_col), variance(int_col),
variance(bigint_col), variance(float_col), variance(double_col),
var_samp(double_col), variance_samp(double_col)
from alltypesagg WHERE id >= 1000 AND id < 1006
---- RESULTS
2.5,2.5,2.5,250,3.025,255.025,255.025,255.025
---- TYPES
double, double, double, double, double, double,double, double
====
---- QUERY
# Test population variance (including the var_pop() alias)
SELECT variance_pop(tinyint_col), variance_pop(smallint_col), variance_pop(int_col),
variance_pop(bigint_col), variance_pop(float_col), variance_pop(double_col),
var_pop(double_col)
from alltypesagg WHERE id >= 1000 AND id < 1006
---- RESULTS
2,2,2,200,2.42,204.02,204.02
---- TYPES
double, double, double, double, double, double, double
====
---- QUERY
SELECT round(stddev(tinyint_col), 5),
       round(stddev(smallint_col), 5),
       round(stddev(int_col), 5),
       round(stddev(bigint_col), 5),
       round(stddev(float_col), 5),
       round(stddev(double_col), 5),
       round(stddev_samp(double_col), 5)
from alltypesagg WHERE id >= 1000 AND id < 1006
---- RESULTS
1.58114,1.58114,1.58114,15.81139,1.73925,15.96950,15.96950
---- TYPES
double, double, double, double, double, double, double
====
---- QUERY
# no grouping exprs, cols contain nulls except for bool cols
SELECT round(stddev_pop(tinyint_col), 5),
       round(stddev_pop(smallint_col), 5),
       round(stddev_pop(int_col), 5),
       round(stddev_pop(bigint_col), 5),
       round(stddev_pop(float_col), 5),
       round(stddev_pop(double_col), 5)
from alltypesagg WHERE id >= 1000 AND id < 1006
---- RESULTS
1.41421,1.41421,1.41421,14.14214,1.55563,14.28356
---- TYPES
double, double, double, double, double, double
====
---- QUERY
# no grouping exprs, cols contain nulls except for bool cols
select count(bool_col), min(bool_col), max(bool_col)
from alltypesagg where day is not null
---- RESULTS
10000,false,true
---- TYPES
bigint, boolean, boolean
====
---- QUERY
# no grouping exprs, cols contain nulls
select count(*), count(tinyint_col), min(tinyint_col), max(tinyint_col), sum(tinyint_col),
avg(tinyint_col)
from alltypesagg where day is not null
---- RESULTS
10000,9000,1,9,45000,5
---- TYPES
bigint, bigint, tinyint, tinyint, bigint, double
====
---- QUERY
select count(*), count(smallint_col), min(smallint_col), max(smallint_col), sum(smallint_col),
avg(smallint_col)
from alltypesagg where day is not null
---- RESULTS
10000,9900,1,99,495000,50
---- TYPES
bigint, bigint, smallint, smallint, bigint, double
====
---- QUERY
select count(*), count(int_col), min(int_col), max(int_col), sum(int_col), avg(int_col)
from alltypesagg where day is not null
---- RESULTS
10000,9990,1,999,4995000,500
---- TYPES
bigint, bigint, int, int, bigint, double
====
---- QUERY
select count(*), count(bigint_col), min(bigint_col), max(bigint_col), sum(bigint_col),
avg(bigint_col)
from alltypesagg where day is not null
---- RESULTS
10000,9990,10,9990,49950000,5000
---- TYPES
bigint, bigint, bigint, bigint, bigint, double
====
---- QUERY
select count(*), count(float_col), min(float_col), max(float_col), sum(float_col),
avg(float_col)
from alltypesagg where day is not null
---- RESULTS
10000,9990,1.100000023841858,1098.900024414062,5494499.999767542,549.9999999767309
---- TYPES
bigint, bigint, float, float, double, double
====
---- QUERY
select count(*), count(double_col), min(double_col), max(double_col), round(sum(double_col), 0),
round(avg(double_col), 0)
from alltypesagg where day is not null
---- RESULTS
10000,9990,10.1,10089.9,50449500,5050
---- TYPES
bigint, bigint, double, double, double, double
====
---- QUERY
select count(*), min(string_col), max(string_col), min(date_string_col),
max(date_string_col)
from alltypesagg where day is not null
---- RESULTS
10000,'0','999','01/01/10','01/10/10'
---- TYPES
bigint, string, string, string, string
====
---- QUERY
# Test for IMPALA-3018. Verify update() functions of min() and max() handle
# zero-length string correctly.
select max(str), min(str) from (values ('aaa' as str), (''), ('123')) as tmp
---- RESULTS
'aaa',''
---- TYPES
string,string
====
---- QUERY
# Test for IMPALA-3018. Verify update() function of last_value() handles
# zero-length string correctly.
select last_value(b) over (partition by a order by d) from functional.nulltable;
---- RESULTS
''
---- TYPES
string
====
---- QUERY
# Test for IMPALA-3018. Verify update() function of first_value() handles
# zero-length string correctly.
select first_value(b) over (partition by a order by d) from functional.nulltable;
---- RESULTS
''
---- TYPES
string
====
---- QUERY
# grouping by different data types, with NULLs
select tinyint_col, count(*) from alltypesagg where day is not null group by 1 order by 1
---- RESULTS
1,1000
2,1000
3,1000
4,1000
5,1000
6,1000
7,1000
8,1000
9,1000
NULL,1000
---- TYPES
tinyint, bigint
====
---- QUERY
# grouping by different data types, with NULLs, grouping expr missing from select list
select bool_col,min(bool_col),max(bool_col) from alltypesagg where day is not null group by 1
---- RESULTS
false,false,false
true,true,true
---- TYPES
boolean,boolean,boolean
====
---- QUERY
select count(*) from alltypesagg where day is not null group by tinyint_col
---- RESULTS
1000
1000
1000
1000
1000
1000
1000
1000
1000
1000
---- TYPES
bigint
====
---- QUERY
select smallint_col % 10, count(*) from alltypesagg where day is not null group by 1
---- RESULTS
3,1000
NULL,100
8,1000
7,1000
0,900
6,1000
9,1000
5,1000
4,1000
1,1000
2,1000
---- TYPES
smallint, bigint
====
---- QUERY
select count(*) from alltypesagg where day is not null group by smallint_col % 10
---- RESULTS
1000
100
1000
1000
900
1000
1000
1000
1000
1000
1000
---- TYPES
bigint
====
---- QUERY
select int_col % 10, count(*) from alltypesagg where day is not null group by 1
---- RESULTS
4,1000
9,1000
NULL,10
6,1000
5,1000
2,1000
0,990
1,1000
3,1000
8,1000
7,1000
---- TYPES
int, bigint
====
---- QUERY
select count(*) from alltypesagg where day is not null group by int_col % 10
---- RESULTS
1000
1000
10
1000
1000
1000
990
1000
1000
1000
1000
---- TYPES
bigint
====
---- QUERY
# Check that ALL inside aggregates is correct
select count(ALL *) from alltypesagg where day is not null group by int_col % 10
---- RESULTS
1000
1000
10
1000
1000
1000
990
1000
1000
1000
1000
---- TYPES
bigint
====
---- QUERY
select bigint_col % 100, count(*) from alltypesagg where day is not null group by 1
---- RESULTS
60,1000
70,1000
20,1000
NULL,10
40,1000
80,1000
30,1000
0,990
50,1000
90,1000
10,1000
---- TYPES
bigint, bigint
====
---- QUERY
select count(*) from alltypesagg where day is not null group by bigint_col % 100
---- RESULTS
1000
1000
1000
10
1000
1000
1000
990
1000
1000
1000
---- TYPES
bigint
====
---- QUERY
select float_col, float_col * 2, count(*) from alltypes group by 1, 2
---- RESULTS
0,0,730
3.299999952316284,6.599999904632568,730
8.800000190734863,17.60000038146973,730
6.599999904632568,13.19999980926514,730
7.699999809265137,15.39999961853027,730
2.200000047683716,4.400000095367432,730
5.5,11,730
1.100000023841858,2.200000047683716,730
9.899999618530273,19.79999923706055,730
4.400000095367432,8.800000190734863,730
---- TYPES
float, double, bigint
====
---- QUERY
select count(*) from alltypes group by float_col
---- RESULTS
730
730
730
730
730
730
730
730
730
730
---- TYPES
bigint
====
---- QUERY
select float_col, count(*) from alltypesagg where float_col is null and day is not null group by 1
---- RESULTS
NULL,10
---- TYPES
float, bigint
====
---- QUERY
select double_col, double_col * 2, count(*) from alltypes group by 1, 2
---- RESULTS
0,0,730
90.90000000000001,181.8,730
40.4,80.8,730
20.2,40.4,730
80.8,161.6,730
10.1,20.2,730
70.7,141.4,730
50.5,101,730
30.3,60.6,730
60.6,121.2,730
---- TYPES
double, double, bigint
====
---- QUERY
select count(*) from alltypes group by double_col
---- RESULTS
730
730
730
730
730
730
730
730
730
730
---- TYPES
bigint
====
---- QUERY
select double_col, count(*) from alltypesagg where double_col is null and day is not null group by 1
---- RESULTS
NULL,10
---- TYPES
double, bigint
====
---- QUERY
select date_string_col, count(*) from alltypesagg where day is not null group by 1
---- RESULTS
'01/08/10',1000
'01/09/10',1000
'01/02/10',1000
'01/06/10',1000
'01/01/10',1000
'01/03/10',1000
'01/04/10',1000
'01/10/10',1000
'01/07/10',1000
'01/05/10',1000
---- TYPES
string, bigint
====
---- QUERY
select count(*) from alltypesagg where day is not null group by date_string_col
---- RESULTS
1000
1000
1000
1000
1000
1000
1000
1000
1000
1000
---- TYPES
bigint
====
---- QUERY
# grouping by multiple exprs, with nulls
select tinyint_col % 3, smallint_col % 3, count(*) from alltypesagg
where day = 1 group by 1, 2
---- RESULTS
0,0,120
0,1,90
0,2,90
1,0,90
1,1,120
1,2,90
2,0,90
2,1,90
2,2,120
NULL,0,30
NULL,1,30
NULL,2,30
NULL,NULL,10
---- TYPES
tinyint, smallint, bigint
====
---- QUERY
select count(*) from alltypesagg
where day = 1 group by tinyint_col % 3, smallint_col % 3
---- RESULTS
10
120
120
120
30
30
30
90
90
90
90
90
90
---- TYPES
bigint
====
---- QUERY
# same result as previous query
select tinyint_col % 3, smallint_col % 3, count(*) from alltypesagg where day = 1 group by 2, 1
---- RESULTS
0,0,120
0,1,90
0,2,90
1,0,90
1,1,120
1,2,90
2,0,90
2,1,90
2,2,120
NULL,0,30
NULL,1,30
NULL,2,30
NULL,NULL,10
---- TYPES
tinyint, smallint, bigint
====
---- QUERY
select tinyint_col % 2, smallint_col % 2, int_col % 2, bigint_col % 2, date_string_col, count(*)
from alltypesagg
where (date_string_col = '01/01/10' or date_string_col = '01/02/10') and day is not null
group by 1, 2, 3, 4, 5
---- RESULTS
1,1,1,0,'01/02/10',500
0,0,0,0,'01/02/10',400
NULL,NULL,0,0,'01/02/10',9
NULL,NULL,NULL,NULL,'01/02/10',1
0,0,0,0,'01/01/10',400
NULL,NULL,0,0,'01/01/10',9
NULL,NULL,NULL,NULL,'01/01/10',1
NULL,0,0,0,'01/02/10',90
1,1,1,0,'01/01/10',500
NULL,0,0,0,'01/01/10',90
---- TYPES
tinyint, smallint, int, bigint, string, bigint
====
---- QUERY
select count(*)
from alltypesagg
where (date_string_col = '01/01/10' or date_string_col = '01/02/10') and day is not null
group by tinyint_col % 2, smallint_col % 2, int_col % 2, bigint_col % 2, date_string_col
---- RESULTS
500
400
9
1
400
9
1
90
500
90
---- TYPES
bigint
====
---- QUERY
# no grouping cols, no matching rows
select count(*), min(tinyint_col), max(tinyint_col), sum(tinyint_col), avg(tinyint_col)
from alltypesagg
where tinyint_col = -1 and day is not null
---- RESULTS
0,NULL,NULL,NULL,NULL
---- TYPES
bigint, tinyint, tinyint, bigint, double
====
---- QUERY
select count(*), min(smallint_col), max(smallint_col), sum(smallint_col), avg(smallint_col)
from alltypesagg
where smallint_col = -1 and day is not null
---- RESULTS
0,NULL,NULL,NULL,NULL
---- TYPES
bigint, smallint, smallint, bigint, double
====
---- QUERY
select count(*), min(int_col), max(int_col), sum(int_col), avg(int_col)
from alltypesagg
where int_col = -1 and day is not null
---- RESULTS
0,NULL,NULL,NULL,NULL
---- TYPES
bigint, int, int, bigint, double
====
---- QUERY
select count(*), min(bigint_col), max(bigint_col), sum(bigint_col), avg(bigint_col)
from alltypesagg
where bigint_col = -1 and day is not null
---- RESULTS
0,NULL,NULL,NULL,NULL
---- TYPES
bigint, bigint, bigint, bigint, double
====
---- QUERY
select count(*), min(float_col), max(float_col), sum(float_col), avg(float_col)
from alltypesagg
where float_col < -1.0 and day is not null
---- RESULTS
0,NULL,NULL,NULL,NULL
---- TYPES
bigint, float, float, double, double
====
---- QUERY
select count(*), min(double_col), max(double_col), sum(double_col), avg(double_col)
from alltypesagg
where double_col < -1.0 and day is not null
---- RESULTS
0,NULL,NULL,NULL,NULL
---- TYPES
bigint, double, double, double, double
====
---- QUERY
# HAVING clauses over all aggregation functions, plus compound HAVING clauses
select int_col % 7, count(*), max(int_col) from alltypesagg where day is not null group by 1
---- RESULTS
4,1430,998
NULL,10,NULL
6,1420,993
5,1430,999
2,1430,996
0,1420,994
1,1430,995
3,1430,997
---- TYPES
int, bigint, int
====
---- QUERY
select int_col % 7, count(*) from alltypesagg where day is not null group by 1 having max(int_col) > 991
---- RESULTS
4,1430
6,1420
5,1430
2,1430
0,1420
1,1430
3,1430
---- TYPES
int, bigint
====
---- QUERY
select int_col % 7, count(*) from alltypesagg where day is not null group by 1
having max(int_col) > 991 and count(*) > 1420
---- RESULTS
4,1430
5,1430
2,1430
1,1430
3,1430
---- TYPES
int, bigint
====
---- QUERY
select int_col % 7, count(*) from alltypesagg where day is not null group by 1
having min(int_col) < 7
---- RESULTS
4,1430
6,1420
5,1430
2,1430
1,1430
3,1430
---- TYPES
int, bigint
====
---- QUERY
select int_col % 7, count(*) from alltypesagg where day is not null group by 1
having min(int_col) < 7 and count(*) > 1420
---- RESULTS
4,1430
5,1430
2,1430
1,1430
3,1430
---- TYPES
int, bigint
====
---- QUERY
select int_col % 7, count(*), sum(int_col) from alltypesagg where day is not null group by 1
---- RESULTS
4,1430,716430
NULL,10,NULL
6,1420,709290
5,1430,717860
2,1430,713570
0,1420,710710
1,1430,712140
3,1430,715000
---- TYPES
int, bigint, bigint
====
---- QUERY
select int_col % 7, count(*), sum(int_col) from alltypesagg where day is not null group by 1
having sum(int_col) >= 715000
---- RESULTS
4,1430,716430
5,1430,717860
3,1430,715000
---- TYPES
int, bigint, bigint
====
---- QUERY
select int_col % 7, count(*), sum(int_col) from alltypesagg where day is not null group by 1
having sum(int_col) >= 715000 or count(*) > 1420
---- RESULTS
4,1430,716430
5,1430,717860
2,1430,713570
1,1430,712140
3,1430,715000
---- TYPES
int, bigint, bigint
====
---- QUERY
select int_col % 7, count(*), sum(int_col) from alltypesagg where day is not null group by 1
having sum(int_col) is null
---- RESULTS
NULL,10,NULL
---- TYPES
int, bigint, bigint
====
---- QUERY
select int_col % 7, count(*), avg(int_col) from alltypesagg where day is not null group by 1
---- RESULTS
4,1430,501
NULL,10,NULL
6,1420,499.5
5,1430,502
2,1430,499
0,1420,500.5
1,1430,498
3,1430,500
---- TYPES
int, bigint, double
====
---- QUERY
select int_col % 7, count(*), avg(int_col) from alltypesagg where day is not null group by 1
having avg(int_col) > 500
---- RESULTS
4,1430,501
5,1430,502
0,1420,500.5
---- TYPES
int, bigint, double
====
---- QUERY
select int_col % 7, count(*), avg(int_col) from alltypesagg where day is not null group by 1
having avg(int_col) > 500 or count(*) = 10
---- RESULTS
4,1430,501
NULL,10,NULL
5,1430,502
0,1420,500.5
---- TYPES
int, bigint, double
====
---- QUERY
select timestamp_col, count(*) from alltypesagg where day is not null
group by timestamp_col having timestamp_col < cast('2010-01-01 01:05:20' as timestamp)
---- RESULTS
2010-01-01 00:49:11.760000000,1
2010-01-01 01:01:18.300000000,1
2010-01-01 00:17:01.360000000,1
2010-01-01 00:58:16.530000000,1
2010-01-01 00:09:00.360000000,1
2010-01-01 00:00:00,1
2010-01-01 01:00:17.700000000,1
2010-01-01 00:57:15.960000000,1
2010-01-01 00:24:02.760000000,1
2010-01-01 00:23:02.530000000,1
2010-01-01 00:45:09.900000000,1
2010-01-01 00:39:07.410000000,1
2010-01-01 00:33:05.280000000,1
2010-01-01 00:03:00.300000000,1
2010-01-01 00:20:01.900000000,1
2010-01-01 00:36:06.300000000,1
2010-01-01 00:44:09.460000000,1
2010-01-01 00:14:00.910000000,1
2010-01-01 00:31:04.650000000,1
2010-01-01 00:48:11.280000000,1
2010-01-01 01:03:19.530000000,1
2010-01-01 00:29:04.600000000,1
2010-01-01 01:02:18.910000000,1
2010-01-01 00:16:01.200000000,1
2010-01-01 00:47:10.810000000,1
2010-01-01 00:51:12.750000000,1
2010-01-01 00:55:14.850000000,1
2010-01-01 00:42:08.610000000,1
2010-01-01 00:56:15.400000000,1
2010-01-01 00:05:00.100000000,1
2010-01-01 00:43:09.300000000,1
2010-01-01 00:28:03.780000000,1
2010-01-01 00:04:00.600000000,1
2010-01-01 00:54:14.310000000,1
2010-01-01 00:26:03.250000000,1
2010-01-01 00:32:04.960000000,1
2010-01-01 00:46:10.350000000,1
2010-01-01 00:37:06.660000000,1
2010-01-01 00:50:12.250000000,1
2010-01-01 00:27:03.510000000,1
2010-01-01 00:19:01.710000000,1
2010-01-01 00:40:07.800000000,1
2010-01-01 00:07:00.210000000,1
2010-01-01 00:22:02.310000000,1
2010-01-01 00:21:02.100000000,1
2010-01-01 00:18:01.530000000,1
2010-01-01 00:11:00.550000000,1
2010-01-01 00:35:05.950000000,1
2010-01-01 00:30:04.350000000,1
2010-01-01 00:08:00.280000000,1
2010-01-01 00:34:05.610000000,1
2010-01-01 00:15:01.500000000,1
2010-01-01 00:41:08.200000000,1
2010-01-01 00:02:00.100000000,1
2010-01-01 00:01:00,1
2010-01-01 00:10:00.450000000,1
2010-01-01 00:52:13.260000000,1
2010-01-01 01:04:20.160000000,1
2010-01-01 00:12:00.660000000,1
2010-01-01 00:38:07.300000000,1
2010-01-01 00:53:13.780000000,1
2010-01-01 00:25:03,1
2010-01-01 00:59:17.110000000,1
2010-01-01 00:06:00.150000000,1
2010-01-01 00:13:00.780000000,1
---- TYPES
timestamp, bigint
====
---- QUERY
# Test NULLs in aggregate functions
select count(NULL), min(NULL), max(NULL), sum(NULL), avg(NULL) from alltypesagg
where day is not null
---- RESULTS
0,NULL,NULL,NULL,NULL
---- TYPES
bigint, boolean, boolean, bigint, double
====
---- QUERY
# Test ignored distinct in MIN and MAX with NULLs
select min(distinct NULL), max(distinct NULL) from alltypes
---- RESULTS
NULL,NULL
---- TYPES
boolean, boolean
====
---- QUERY
# Test group_concat with default delimiter. Use a subquery with an ORDER BY to
# ensure group_concat results are in a deterministic order.
select day, group_concat(string_col)
from (select * from alltypesagg where id % 100 = day order by id limit 99999) a
group by day
---- RESULTS: VERIFY_IS_EQUAL_SORTED
3,'3, 103, 203, 303, 403, 503, 603, 703, 803, 903'
5,'5, 105, 205, 305, 405, 505, 605, 705, 805, 905'
8,'8, 108, 208, 308, 408, 508, 608, 708, 808, 908'
4,'4, 104, 204, 304, 404, 504, 604, 704, 804, 904'
9,'9, 109, 209, 309, 409, 509, 609, 709, 809, 909'
2,'2, 102, 202, 302, 402, 502, 602, 702, 802, 902'
6,'6, 106, 206, 306, 406, 506, 606, 706, 806, 906'
10,'10, 110, 210, 310, 410, 510, 610, 710, 810, 910'
7,'7, 107, 207, 307, 407, 507, 607, 707, 807, 907'
1,'1, 101, 201, 301, 401, 501, 601, 701, 801, 901'
---- TYPES
int, string
====
---- QUERY
# Test group_concat with NULL (default) delimiter
select day, group_concat(string_col, NULL)
from (select * from alltypesagg where id % 100 = day order by id limit 99999) a
group by day
---- RESULTS: VERIFY_IS_EQUAL_SORTED
3,'3, 103, 203, 303, 403, 503, 603, 703, 803, 903'
5,'5, 105, 205, 305, 405, 505, 605, 705, 805, 905'
8,'8, 108, 208, 308, 408, 508, 608, 708, 808, 908'
4,'4, 104, 204, 304, 404, 504, 604, 704, 804, 904'
9,'9, 109, 209, 309, 409, 509, 609, 709, 809, 909'
2,'2, 102, 202, 302, 402, 502, 602, 702, 802, 902'
6,'6, 106, 206, 306, 406, 506, 606, 706, 806, 906'
10,'10, 110, 210, 310, 410, 510, 610, 710, 810, 910'
7,'7, 107, 207, 307, 407, 507, 607, 707, 807, 907'
1,'1, 101, 201, 301, 401, 501, 601, 701, 801, 901'
---- TYPES
int, string
====
---- QUERY
# Test group_concat with both args as NULL
select day, group_concat(NULL, NULL)
from (select * from alltypesagg where id % 100 = day order by id limit 99999) a
group by day
---- RESULTS: VERIFY_IS_EQUAL_SORTED
3,'NULL'
5,'NULL'
8,'NULL'
4,'NULL'
9,'NULL'
2,'NULL'
6,'NULL'
10,'NULL'
7,'NULL'
1,'NULL'
---- TYPES
int, string
====
---- QUERY
# Test group_concat with arrow delimiter
select day, group_concat(string_col, "->")
from (select * from alltypesagg where id % 100 = day order by id limit 99999) a
group by day
---- RESULTS: VERIFY_IS_EQUAL_SORTED
3,'3->103->203->303->403->503->603->703->803->903'
5,'5->105->205->305->405->505->605->705->805->905'
8,'8->108->208->308->408->508->608->708->808->908'
4,'4->104->204->304->404->504->604->704->804->904'
9,'9->109->209->309->409->509->609->709->809->909'
2,'2->102->202->302->402->502->602->702->802->902'
6,'6->106->206->306->406->506->606->706->806->906'
10,'10->110->210->310->410->510->610->710->810->910'
7,'7->107->207->307->407->507->607->707->807->907'
1,'1->101->201->301->401->501->601->701->801->901'
---- TYPES
int, string
====
---- QUERY
# Test group_concat with column delimiter
# Will cause all columns save first to be duplicated
select day, group_concat(trim(string_col), trim(string_col))
from (select * from alltypesagg where id % 200 = day order by id limit 99999) a
group by day
---- RESULTS: VERIFY_IS_EQUAL_SORTED
3,'3203203403403603603803803'
5,'5205205405405605605805805'
8,'8208208408408608608808808'
4,'4204204404404604604804804'
9,'9209209409409609609809809'
2,'2202202402402602602802802'
6,'6206206406406606606806806'
10,'10210210410410610610810810'
7,'7207207407407607607807807'
1,'1201201401401601601801801'
---- TYPES
int, string
====
---- QUERY
# Test group_concat with multiple agg columns
select day, group_concat(string_col, '->'), group_concat(date_string_col)
from (select * from alltypesagg where id % 250 = day order by id limit 99999) a
group by day
---- RESULTS: VERIFY_IS_EQUAL_SORTED
3,'3->253->503->753','01/03/10, 01/03/10, 01/03/10, 01/03/10'
5,'5->255->505->755','01/05/10, 01/05/10, 01/05/10, 01/05/10'
8,'8->258->508->758','01/08/10, 01/08/10, 01/08/10, 01/08/10'
4,'4->254->504->754','01/04/10, 01/04/10, 01/04/10, 01/04/10'
9,'9->259->509->759','01/09/10, 01/09/10, 01/09/10, 01/09/10'
2,'2->252->502->752','01/02/10, 01/02/10, 01/02/10, 01/02/10'
6,'6->256->506->756','01/06/10, 01/06/10, 01/06/10, 01/06/10'
10,'10->260->510->760','01/10/10, 01/10/10, 01/10/10, 01/10/10'
7,'7->257->507->757','01/07/10, 01/07/10, 01/07/10, 01/07/10'
1,'1->251->501->751','01/01/10, 01/01/10, 01/01/10, 01/01/10'
---- TYPES
int, string, string
====
---- QUERY
# Test group_concat distinct with multiple agg columns
select day, group_concat(string_col, '->'), group_concat(date_string_col),
group_concat(distinct date_string_col)
from (select * from alltypesagg where id % 250 = day order by id limit 99999) a
group by day order by day
---- RESULTS: VERIFY_IS_EQUAL_SORTED
1,'1->251->501->751','01/01/10, 01/01/10, 01/01/10, 01/01/10','01/01/10'
2,'2->252->502->752','01/02/10, 01/02/10, 01/02/10, 01/02/10','01/02/10'
3,'3->253->503->753','01/03/10, 01/03/10, 01/03/10, 01/03/10','01/03/10'
4,'4->254->504->754','01/04/10, 01/04/10, 01/04/10, 01/04/10','01/04/10'
5,'5->255->505->755','01/05/10, 01/05/10, 01/05/10, 01/05/10','01/05/10'
6,'6->256->506->756','01/06/10, 01/06/10, 01/06/10, 01/06/10','01/06/10'
7,'7->257->507->757','01/07/10, 01/07/10, 01/07/10, 01/07/10','01/07/10'
8,'8->258->508->758','01/08/10, 01/08/10, 01/08/10, 01/08/10','01/08/10'
9,'9->259->509->759','01/09/10, 01/09/10, 01/09/10, 01/09/10','01/09/10'
10,'10->260->510->760','01/10/10, 01/10/10, 01/10/10, 01/10/10','01/10/10'
---- TYPES
int, string, string, string
====
---- QUERY
# Test group_concat with null result
select group_concat(string_col) from alltypesagg where string_col = NULL;
---- RESULTS
'NULL'
---- TYPES
string
====
---- QUERY
# Test group_concat distinct with null result
select group_concat(distinct string_col) from alltypesagg where string_col = NULL;
---- RESULTS
'NULL'
---- TYPES
string
====
---- QUERY
# Test group_concat with merge node
select group_concat(string_col) from alltypesagg where int_col = 1
---- RESULTS
'1, 1, 1, 1, 1, 1, 1, 1, 1, 1'
---- TYPES
STRING
====
---- QUERY
# Test merge phase uses correct separator (IMPALA-1110).  The query needs to load data
# from multiple nodes in order to exercise this path, however the merge order is
# non-deterministic.  So, aggregate a string literal to make the result deterministic.
select group_concat('abc', 'xy') from functional.alltypesagg where id % 1000 = day
---- RESULTS
'abcxyabcxyabcxyabcxyabcxyabcxyabcxyabcxyabcxyabc'
---- TYPES
string
====
---- QUERY
# Same as last query test, now adding the "distinct" clause
select group_concat(distinct 'abc', 'xy') from functional.alltypesagg
where id % 1000 = day
---- RESULTS
'abc'
---- TYPES
string
====
---- QUERY
# Test group_concat when separator varies by row.
select group_concat(cast(id as string), cast ((10 - id) as string))
from functional.alltypesagg
where id < 10 and day is not null
group by day
---- RESULTS
'0918273645546372819'
---- TYPES
string
====
---- QUERY
# Test correct removal of redundant group-by expressions (IMPALA-817)
select int_col * int_col, int_col + int_col
from functional.alltypesagg
group by int_col * int_col, int_col + int_col, int_col * int_col
having (int_col + int_col) < 5 order by 1 limit 10
---- RESULTS
1,2
4,4
---- TYPES
bigint,bigint
====
---- QUERY
# Test that binding predicates on an aggregation properly trigger materialization of
# slots in the agg tuple and the slots needed for evaluating the corresponding agg funcs
# (IMPALA-822).
select 1 from
  (select count(bigint_col) c from functional.alltypesagg
   having min(int_col) is not null) as t
where c is not null
---- RESULTS
1
---- TYPES
tinyint
====
---- QUERY
# Regression test for subexpr elimination in codegen. IMPALA-765
select count(tinyint_col), sum(tinyint_col * tinyint_col) from alltypesagg
---- RESULTS
9000,285000
---- TYPES
bigint,bigint
====
---- QUERY
# Regression test for subexpr elimination in codegen. IMPALA-765
select count(int_col), sum(int_col), avg(int_col) from alltypesagg where int_col is NULL
---- RESULTS
0,NULL,NULL
---- TYPES
bigint,bigint,double
====
---- QUERY
# Regression test for subexpr elimination in codegen. IMPALA-850
select id % 2, int_col > 1, id from alltypesagg where id < 2 group by 1,2,3
---- RESULTS
0,NULL,0
1,false,1
---- TYPES
int,boolean,int
====
---- QUERY
# Regression test for min/max of all negative values. IMPALA-869.
select min(cast(-1.0 as float)), max(cast(-1.0 as float)) from tinytable
---- RESULTS
-1,-1
---- TYPES
float,float
====
---- QUERY
# Regression test codegen with nulls and compound predicates. IMPALA-892.
select COUNT(int_col is not null AND bool_col) - COUNT(bool_col) FROM alltypesagg
---- RESULTS
0
---- TYPES
BIGINT
====
---- QUERY
select histogram(bool_col) from functional.alltypestiny;
---- RESULTS
'0, 0, 0, 0, 1, 1, 1, 1'
---- TYPES
STRING
====
---- QUERY
select histogram(tinyint_col) from functional.alltypestiny;
---- RESULTS
'0, 0, 0, 0, 1, 1, 1, 1'
---- TYPES
STRING
====
---- QUERY
select histogram(smallint_col) from functional.alltypestiny;
---- RESULTS
'0, 0, 0, 0, 1, 1, 1, 1'
---- TYPES
STRING
====
---- QUERY
select histogram(int_col) from functional.alltypestiny;
---- RESULTS
'0, 0, 0, 0, 1, 1, 1, 1'
---- TYPES
STRING
====
---- QUERY
select histogram(bigint_col) from functional.alltypestiny;
---- RESULTS
'0, 0, 0, 0, 10, 10, 10, 10'
---- TYPES
STRING
====
---- QUERY
select histogram(float_col) from functional.alltypestiny;
---- RESULTS
'0, 0, 0, 0, 1.1, 1.1, 1.1, 1.1'
---- TYPES
STRING
====
---- QUERY
select histogram(double_col) from functional.alltypestiny;
---- RESULTS
'0, 0, 0, 0, 10.1, 10.1, 10.1, 10.1'
---- TYPES
STRING
====
---- QUERY
select histogram(string_col) from functional.alltypestiny;
---- RESULTS
'0, 0, 0, 0, 1, 1, 1, 1'
---- TYPES
STRING
====
---- QUERY
select histogram(timestamp_col) from functional.alltypestiny;
---- RESULTS
'2009-01-01 00:00:00, 2009-01-01 00:01:00, 2009-02-01 00:00:00, 2009-02-01 00:01:00, 2009-03-01 00:00:00, 2009-03-01 00:01:00, 2009-04-01 00:00:00, 2009-04-01 00:01:00'
---- TYPES
STRING
====
---- QUERY
# IMPALA-4787: appx_median() on a medium sized dataset. This should excercise merge() with
# differently sized inputs in the Reservoir Sampling algorithm.
select
appx_median(bool_col),
appx_median(tinyint_col),
appx_median(smallint_col),
appx_median(int_col),
appx_median(float_col),
appx_median(double_col),
appx_median(string_col),
appx_median(timestamp_col)
from alltypes
---- RESULTS
true,5,5,5,5.5,50.5,'5',2010-01-01 00:00:00
---- TYPES
BOOLEAN, TINYINT, SMALLINT, INT, FLOAT, DOUBLE, STRING, TIMESTAMP
====
---- QUERY
# IMPALA-4787: appx_median on a large dataset. This requires several buffer resizes in the
# Reservoir Sampling algorithm.
select appx_median(l_returnflag)
from tpch.lineitem
where l_returnflag = "N"
---- RESULTS
'N'
---- TYPES
STRING
====
---- QUERY
# IMPALA-1419: Agg fn containing arithmetic expr on NULL fails
select count(null * 1) from functional.alltypes
---- RESULTS
0
---- TYPES
BIGINT
====
---- QUERY
# IMPALA-1898: ordinal in group/order by combined with explicit select-list alias
# that match columns in underlying table
select extract(timestamp_col, 'year') as timestamp_col,
       extract(timestamp_col, 'month') as month,
       sum(tinyint_col)
from functional.alltypes
group by 1, 2
order by 1, 2;
---- RESULTS
2009,1,1395
2009,2,1260
2009,3,1395
2009,4,1350
2009,5,1395
2009,6,1350
2009,7,1395
2009,8,1395
2009,9,1350
2009,10,1395
2009,11,1350
2009,12,1395
2010,1,1395
2010,2,1260
2010,3,1395
2010,4,1350
2010,5,1395
2010,6,1350
2010,7,1395
2010,8,1395
2010,9,1350
2010,10,1395
2010,11,1350
2010,12,1395
---- TYPES
BIGINT,BIGINT,BIGINT
====
---- QUERY
# IMPALA-2089: Tests correct elimination of redundant predicates.
# The equivalences between inline-view slots are enforced inside the inline-view plan.
# Equivalences between simple grouping slots (with SlotRef grouping exprs) are enforced
# at the scan, and equivalences between grouping slots with complex grouping exprs are
# enforced at the aggregation.
select t2.timestamp_col, t1.int_col_1
from
(select coalesce(t1.smallint_col, t1.month, t1.month) as int_col,
        (count(t1.int_col)) <= (coalesce(t1.smallint_col, t1.month, t1.month)) as boolean_col,
        (t1.bigint_col) + (t1.smallint_col) as int_col_1
 from functional.alltypes t1
 group by coalesce(t1.smallint_col, t1.month, t1.month), (t1.bigint_col) + (t1.smallint_col)
 having (t1.bigint_col) + (t1.smallint_col) != (count(t1.bigint_col + t1.smallint_col))
) t1
inner join functional.alltypes t2
  on (t2.month = t1.int_col and t2.month = t1.int_col_1 and t2.tinyint_col = t1.int_col)
where t2.int_col IN (t1.int_col_1, t1.int_col)
---- RESULTS
---- TYPES
TIMESTAMP,BIGINT
====
---- QUERY
# IMPALA-5036: Tests the correctness of the Parquet count(*) optimization.
select count(1)
from functional_parquet.alltypes
---- RESULTS
7300
---- TYPES
bigint
=====
---- QUERY
# IMPALA-5036: Parquet count(*) optimization with predicates on the partition columns.
select count(1)
from functional_parquet.alltypes where year < 2010 and month > 8
---- RESULTS
1220
---- TYPES
bigint
=====
---- QUERY
# IMPALA-5036: Parquet count(*) optimization with group by partition columns.
select year, month, count(1)
from functional_parquet.alltypes where month > 10 group by year, month
---- RESULTS
2009,11,300
2009,12,310
2010,11,300
2010,12,310
---- TYPES
int, int, bigint
=====
---- QUERY
# IMPALA-5036: Parquet count(*) optimization with both group by and predicates on
# partition columns.
select count(1)
from functional_parquet.alltypes where year < 2010 and month > 8
group by month
---- RESULTS
310
300
310
300
---- TYPES
bigint
=====
---- QUERY
# IMPALA-5036: Parquet count(*) optimization with the result of the going into a join.
select x.bigint_col from functional.alltypes x
  inner join (
    select count(1) as a from functional_parquet.alltypes group by year
  ) t on x.id = t.a;
---- RESULTS
0
0
---- TYPES
bigint
=====
---- QUERY
# IMPALA-5036: Parquet count(*) optimization with the agg function in the having clause.
select 1 from functional_parquet.alltypes having count(*) > 1
---- RESULTS
1
---- TYPES
tinyint
====
---- QUERY
# IMPALA-5855: pre-aggregation does not reserve enough memory with 2MB buffers.
# The pre-aggregation in this query is estimated to consume enough memory for the planner
# to use 2MB buffers.
set debug_action="-1:PREPARE:SET_DENY_RESERVATION_PROBABILITY@1.0";
select count(*) from (
    select distinct l_orderkey, l_comment from tpch_parquet.lineitem) v
---- RESULTS
6001198
---- TYPES
bigint
====
---- QUERY
# IMPALA-6295: min/max where nan/inf/-inf are the only values
with x as (select cast('nan' as float) a, cast('inf' as float) b, cast('-inf' as float) c)
select min(a), min(b), min(c), max(a), max(b), max(c) from x
---- RESULTS
NaN,Infinity,-Infinity,NaN,Infinity,-Infinity
---- TYPES
FLOAT,FLOAT,FLOAT,FLOAT,FLOAT,FLOAT
====
---- QUERY
# IMPALA-6295: min/max/sum/avg should return nan if any of the values is nan
with x as (values (0), (1), (cast('nan' as double)), (cast('inf' as double)),
  (cast('-inf' as double)))
select min(`0`), max(`0`), sum(`0`), avg(`0`) from x
---- RESULTS
NaN,NaN,NaN,NaN
---- TYPES
DOUBLE,DOUBLE,DOUBLE,DOUBLE
====
---- QUERY
# Test behavior of inf
with x as (values (0), (cast('inf' as double)), (5.2))
select min(`0`), max(`0`), sum(`0`), avg(`0`) from x
---- RESULTS
0,Infinity,Infinity,Infinity
---- TYPES
DOUBLE,DOUBLE,DOUBLE,DOUBLE
====
---- QUERY
# Test behavior of -inf
with x as (values (cast('-inf' as double)), (0), (-10))
select min(`cast('-inf' as double)`), max(`cast('-inf' as double)`),
  sum(`cast('-inf' as double)`), avg(`cast('-inf' as double)`)
from x
---- RESULTS
-Infinity,0,-Infinity,-Infinity
---- TYPES
DOUBLE,DOUBLE,DOUBLE,DOUBLE
====
---- QUERY
# IMPALA-7397: conjunct that makes allocations in Prepare (extract) assigned to agg node
select timestamp_col, count(int_col) from alltypesagg group by timestamp_col, int_col
having extract(hour from timestamp_col) = int_col
---- TYPES
TIMESTAMP,BIGINT
---- RESULTS
====
---- QUERY
# GROUP BY of NaN values aggregates NaN's as one grouping
select count(*), sqrt(0.5-x) as Z
from (VALUES((1.6 x, 2 y), (3.2, 4), (5.4,6))) T
group by Z
---- RESULTS
3, NaN
---- TYPES
bigint, double
====
---- QUERY
# GROUP BY of NaN values aggregates NaN's as one grouping
select count(*), cast(sqrt(0.4-x) as FLOAT) as P, cast(sqrt(1.5-y) as FLOAT) as Q
from (VALUES((1.6 x, 1.6 y, 0 z), (0.5, 0.5, 0), (5.4, 6, 0),
              (0.5, 0.5, 0), (0.5, 0.5, 0), (-0.6, 0.5, 0))) T
group by P, Q order by P, Q
---- RESULTS
2, NaN, NaN
3, NaN, 1
1, 1, 1
---- TYPES
bigint, float, float
====
---- QUERY
# IMPALA-6660: GROUP BY of -0/+0 values aggregates zeros as one grouping
select x, count(*)
from (values(cast("-0" as float) x), (cast("0" as float))) v
group by x
---- RESULTS
0,2
---- TYPES
float, bigint
====
---- QUERY
# IMPALA-6660: -0/+0 values are not distinct
select distinct *
from (values(cast("-0" as float)), (cast("0" as float))) v;
---- RESULTS
0
---- TYPES
float
====
---- QUERY
# IMPALA-8140: Test that group by with limit does not crash on Asan
select count(*) from tpch_parquet.orders o group by o.o_clerk limit 10
# We don't validate the results since the order is not deterministic.
---- TYPES
bigint
====