====
---- QUERY
# count(distinct)
select count(distinct smallint_col, tinyint_col)
from alltypesagg
---- RESULTS
90
---- TYPES
bigint
====
---- QUERY
select count(distinct bool_col)
from alltypesagg
---- RESULTS
2
---- TYPES
bigint
====
---- QUERY
select count(distinct int_col)
from alltypesagg
---- RESULTS
999
---- TYPES
bigint
====
---- QUERY
select count(distinct float_col)
from alltypesagg
---- RESULTS
999
---- TYPES
bigint
====
---- QUERY
select count(distinct double_col)
from alltypesagg
---- RESULTS
999
---- TYPES
bigint
====
---- QUERY
select count(distinct string_col)
from alltypesagg
---- RESULTS
1000
---- TYPES
bigint
====
---- QUERY
select count(distinct date_string_col)
from alltypesagg
---- RESULTS
10
---- TYPES
bigint
====
---- QUERY
select count(distinct timestamp_col)
from alltypesagg
---- RESULTS
10000
---- TYPES
bigint
====
---- QUERY
# count(distinct) w/ grouping and non-distinct count()
select tinyint_col, count(distinct smallint_col), count(smallint_col)
from alltypesagg group by 1
---- RESULTS
2,10,1000
3,10,1000
NULL,9,1800
4,10,1000
8,10,1000
5,10,1000
9,10,1000
7,10,1000
1,10,1000
6,10,1000
---- TYPES
tinyint, bigint, bigint
====
---- QUERY
# count(distinct) w/ grouping and non-distinct count()
select tinyint_col, count(distinct int_col, smallint_col), count(smallint_col)
from alltypesagg group by 1
---- RESULTS
2,100,1000
3,100,1000
NULL,90,1800
4,100,1000
8,100,1000
5,100,1000
9,100,1000
7,100,1000
1,100,1000
6,100,1000
---- TYPES
tinyint, bigint, bigint
====
---- QUERY
# count(distinct) and sum(distinct) w/ grouping and non-distinct count()
select tinyint_col, count(distinct smallint_col), sum(distinct smallint_col),
count(smallint_col)
from alltypesagg group by 1
---- RESULTS
2,10,470,1000
3,10,480,1000
NULL,9,450,1800
4,10,490,1000
8,10,530,1000
5,10,500,1000
9,10,540,1000
7,10,520,1000
1,10,460,1000
6,10,510,1000
---- TYPES
tinyint, bigint, bigint, bigint
====
---- QUERY
# count(distinct) and sum(distinct) w/ grouping; distinct in min() and max()
# ignored
select tinyint_col, count(distinct smallint_col), sum(distinct smallint_col),
count(smallint_col), min(distinct int_col), max(distinct float_col)
from alltypesagg group by 1
---- RESULTS
2,10,470,1000,2,1091.199951171875
3,10,480,1000,3,1092.300048828125
NULL,9,450,1800,10,1089
4,10,490,1000,4,1093.400024414062
8,10,530,1000,8,1097.800048828125
5,10,500,1000,5,1094.5
9,10,540,1000,9,1098.900024414062
7,10,520,1000,7,1096.699951171875
1,10,460,1000,1,1090.099975585938
6,10,510,1000,6,1095.599975585938
---- TYPES
tinyint, bigint, bigint, bigint, int, float
====
---- QUERY
# count distinct order by the same agg expr
select count(distinct id) as count_id from alltypessmall order by count_id limit 100
---- RESULTS
100
---- TYPES
bigint
====
---- QUERY
# count distinct order by a diff agg expr
select count(distinct id) as sum_id from alltypessmall order by max(distinct id)
limit 100
---- RESULTS
100
---- TYPES
bigint
====
---- QUERY
# distinct *
select distinct *
from alltypesagg
where id < 20
---- RESULTS
6,true,6,6,6,60,6.599999904632568,60.59999999999999,'01/01/10','6',2010-01-01 00:06:00.150000000,2010,1,1
14,true,4,14,14,140,15.39999961853027,141.4,'01/01/10','14',2010-01-01 00:14:00.910000000,2010,1,1
18,true,8,18,18,180,19.79999923706055,181.8,'01/01/10','18',2010-01-01 00:18:01.530000000,2010,1,1
19,false,9,19,19,190,20.89999961853027,191.9,'01/01/10','19',2010-01-01 00:19:01.710000000,2010,1,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/10','1',2010-01-01 00:01:00,2010,1,1
2,true,2,2,2,20,2.200000047683716,20.2,'01/01/10','2',2010-01-01 00:02:00.100000000,2010,1,1
0,true,NULL,NULL,NULL,NULL,NULL,NULL,'01/01/10','0',2010-01-01 00:00:00,2010,1,1
7,false,7,7,7,70,7.699999809265137,70.7,'01/01/10','7',2010-01-01 00:07:00.210000000,2010,1,1
9,false,9,9,9,90,9.899999618530273,90.89999999999999,'01/01/10','9',2010-01-01 00:09:00.360000000,2010,1,1
11,false,1,11,11,110,12.10000038146973,111.1,'01/01/10','11',2010-01-01 00:11:00.550000000,2010,1,1
3,false,3,3,3,30,3.299999952316284,30.3,'01/01/10','3',2010-01-01 00:03:00.300000000,2010,1,1
15,false,5,15,15,150,16.5,151.5,'01/01/10','15',2010-01-01 00:15:01.500000000,2010,1,1
10,true,NULL,10,10,100,11,101,'01/01/10','10',2010-01-01 00:10:00.450000000,2010,1,1
17,false,7,17,17,170,18.70000076293945,171.7,'01/01/10','17',2010-01-01 00:17:01.360000000,2010,1,1
5,false,5,5,5,50,5.5,50.5,'01/01/10','5',2010-01-01 00:05:00.100000000,2010,1,1
10,true,NULL,10,10,100,11,101,'01/01/10','10',2010-01-01 00:10:00.450000000,2010,1,NULL
8,true,8,8,8,80,8.800000190734863,80.8,'01/01/10','8',2010-01-01 00:08:00.280000000,2010,1,1
13,false,3,13,13,130,14.30000019073486,131.3,'01/01/10','13',2010-01-01 00:13:00.780000000,2010,1,1
12,true,2,12,12,120,13.19999980926514,121.2,'01/01/10','12',2010-01-01 00:12:00.660000000,2010,1,1
16,true,6,16,16,160,17.60000038146973,161.6,'01/01/10','16',2010-01-01 00:16:01.200000000,2010,1,1
4,true,4,4,4,40,4.400000095367432,40.4,'01/01/10','4',2010-01-01 00:04:00.600000000,2010,1,1
0,true,NULL,NULL,NULL,NULL,NULL,NULL,'01/01/10','0',2010-01-01 00:00:00,2010,1,NULL
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int, int
====
---- QUERY
# distinct w/ explicit select list
select distinct bool_col, tinyint_col
from alltypesagg
---- RESULTS
false,1
false,3
false,5
false,7
false,9
true,2
true,4
true,6
true,8
true,NULL
---- TYPES
boolean, tinyint
====
---- QUERY
# test count distinct with nulls w/o groupby
select count(distinct NULL) from alltypesagg
---- RESULTS
0
---- TYPES
bigint
====
---- QUERY
# test count distinct with nulls w/o groupby
select count(distinct int_col, NULL) from alltypesagg
---- RESULTS
0
---- TYPES
bigint
====
---- QUERY
# test count distinct with nulls and groupby
select tinyint_col, count(distinct NULL) from alltypesagg group by tinyint_col
order by tinyint_col limit 10
---- RESULTS
1,0
2,0
3,0
4,0
5,0
6,0
7,0
8,0
9,0
NULL,0
---- TYPES
tinyint, bigint
====
---- QUERY
select tinyint_col, count(distinct int_col, NULL) from alltypesagg group by tinyint_col
order by tinyint_col limit 10
---- RESULTS
1,0
2,0
3,0
4,0
5,0
6,0
7,0
8,0
9,0
NULL,0
---- TYPES
tinyint, bigint
====
---- QUERY
# make sure we still return the NULL group even though NULLs are not added to COUNT
select tinyint_col, count(distinct tinyint_col, NULL) from alltypesagg group by tinyint_col
order by tinyint_col limit 10
---- RESULTS
1,0
2,0
3,0
4,0
5,0
6,0
7,0
8,0
9,0
NULL,0
---- TYPES
tinyint, bigint
====
---- QUERY
# test join on inline views containing distinct aggregates to make sure
# the aggregation info reports the correct tuple ids (from the 2nd phase
# distinct aggregation) for the inline-view expression substitution
select t1.c, t2.c from
(select count(distinct int_col) as c from functional.alltypestiny) t1 inner join
(select count(distinct bigint_col) as c from functional.alltypestiny) t2 on (t1.c = t2.c)
---- RESULTS
2,2
---- TYPES
bigint, bigint
====
---- QUERY
# Test distinct + non-distinct aggregates without group by and having clause (IMPALA-845)
# alltypessmall has 100 non-null values for bigint_col, expect a single result
select count(distinct tinyint_col) from functional.alltypessmall
having count(bigint_col) > 99
---- RESULTS
10
---- TYPES
bigint
====
---- QUERY
# Test distinct + non-distinct aggregates without group by and having clause (IMPALA-845)
# alltypessmall has 100 non-null values for bigint_col, expect an empty result
select count(distinct tinyint_col) from functional.alltypessmall
having count(bigint_col) > 100
---- RESULTS
---- TYPES
bigint
====
---- QUERY
# Regression test for COUNT(ALL ) with no group-by and other distinct agg. IMPALA-862
select count(*), COUNT(distinct 1) from alltypesagg where false
---- RESULTS
0,0
---- TYPES
bigint, bigint
====
---- QUERY
# Regression test for COUNT(ALL ) with no group-by and other distinct agg. IMPALA-862
select count(tinyint_col), sum(distinct int_col) from alltypesagg
---- RESULTS
9000,499500
---- TYPES
bigint, bigint
====
---- QUERY
# Regression test for COUNT(ALL ) with no group-by and other distinct agg. IMPALA-862
select count(*), COUNT(distinct 1) from alltypesagg
---- RESULTS
11000,1
---- TYPES
bigint, bigint
====
---- QUERY
# Test distinct + non-distinct aggregation with intermediate tuples (IMPALA-1247).
select avg(int_col), count(int_col), avg(distinct bigint_col)
from functional.alltypes
---- RESULTS
4.5,7300,45
---- TYPES
double, bigint, double
====
---- QUERY
# Test distinct + non-distinct aggregation with intermediate tuples (IMPALA-1247).
select bool_col, avg(int_col), count(int_col), avg(distinct bigint_col)
from functional.alltypes
group by bool_col
order by bool_col
---- RESULTS
false,5,3650,50
true,4,3650,40
---- TYPES
boolean, double, bigint, double
====
---- QUERY
# Test rewriting count(distinct) into NDV() via a query option.
set appx_count_distinct=true;
select count(distinct int_col), count(distinct float_col), count(distinct string_col)
from alltypesagg
---- RESULTS
957,1016,963
---- TYPES
bigint, bigint, bigint
====
---- QUERY
# Large (more than 1 regular batches) distinct with multiple group by keys.
SELECT COUNT(*) FROM
(SELECT COUNT(DISTINCT p_partkey)
 FROM tpch_parquet.part GROUP BY p_brand, p_type, p_size) v
---- RESULTS
123039
---- TYPES
bigint
====
---- QUERY
# Distinct agg without a grouping expr and with a compatible child partition
select count(distinct a.int_col) from functional.alltypes a inner join [shuffle]
    functional.alltypes b on a.int_col = b.int_col;
---- RESULTS
10
---- TYPES
bigint
====
---- QUERY
# Distinct agg with a grouping expr. The input is partitioned by grouping exprs.
select count(distinct a.int_col) from functional.alltypes a inner join [shuffle]
    functional.alltypes b on a.year = b.year group by a.year;
---- RESULTS
10
10
---- TYPES
bigint
====
---- QUERY
# Distinct agg with a grouping expr. The input is partitioned by grouping exprs and
# distinct exprs.
select count(distinct a.int_col) from functional.alltypes a inner join [shuffle]
   functional.alltypes b on a.year = b.year and a.int_col = b.int_col group by a.year;
---- RESULTS
10
10
---- TYPES
bigint
====