testdata/workloads/functional-query/queries/QueryTest/multiple-distinct-aggs.test

====
---- QUERY
# Distinct and non-distinct without grouping.
select count(distinct tinyint_col), count(smallint_col) from alltypes
---- TYPES
BIGINT,BIGINT
---- RESULTS
10,7300
====
---- QUERY
# Distinct and non-distinct without grouping. Distinct needs intermediate agg tuple.
select avg(distinct tinyint_col), count(smallint_col) from alltypes
---- TYPES
DOUBLE,BIGINT
---- RESULTS
4.5,7300
====
---- QUERY
# Distinct and non-distinct without grouping. Non-distinct needs intermediate agg tuple.
select count(distinct tinyint_col), avg(smallint_col) from alltypes
---- TYPES
BIGINT,DOUBLE
---- RESULTS
10,4.5
====
---- QUERY
# Distinct and non-distinct without grouping. Both need intermediate agg tuples.
select avg(distinct tinyint_col), avg(smallint_col) from alltypes
---- TYPES
DOUBLE,DOUBLE
---- RESULTS
4.5,4.5
====
---- QUERY
# Distinct and non-distinct with grouping.
select bigint_col, count(distinct tinyint_col), count(smallint_col)
from alltypes group by bigint_col
---- TYPES
BIGINT,BIGINT,BIGINT
---- RESULTS
0,1,730
10,1,730
20,1,730
30,1,730
40,1,730
50,1,730
60,1,730
70,1,730
80,1,730
90,1,730
====
---- QUERY
# Distinct and non-distinct with grouping. Distinct needs intermediate agg tuple.
select bigint_col, avg(distinct tinyint_col), count(smallint_col)
from alltypes group by bigint_col
---- TYPES
BIGINT,DOUBLE,BIGINT
---- RESULTS
0,0,730
10,1,730
20,2,730
30,3,730
40,4,730
50,5,730
60,6,730
70,7,730
80,8,730
90,9,730
====
---- QUERY
# Distinct and non-distinct with grouping. Non-distinct needs intermediate agg tuple.
select bigint_col, count(distinct tinyint_col), avg(smallint_col)
from alltypes group by bigint_col
---- TYPES
BIGINT,BIGINT,DOUBLE
---- RESULTS
0,1,0
10,1,1
20,1,2
30,1,3
40,1,4
50,1,5
60,1,6
70,1,7
80,1,8
90,1,9
====
---- QUERY
# Distinct and non-distinct with grouping. Both need intermediate agg tuples.
select bigint_col, avg(distinct tinyint_col), avg(smallint_col)
from alltypes group by bigint_col
---- TYPES
BIGINT,DOUBLE,DOUBLE
---- RESULTS
0,0,0
10,1,1
20,2,2
30,3,3
40,4,4
50,5,5
60,6,6
70,7,7
80,8,8
90,9,9
====
---- QUERY
# Multiple distinct without grouping.
select count(distinct tinyint_col), sum(distinct int_col), count(distinct smallint_col)
from alltypes
---- TYPES
BIGINT,BIGINT,BIGINT
---- RESULTS
10,45,10
====
---- QUERY
# Multiple distinct without grouping. First needs intermediate tuple.
select avg(distinct tinyint_col), sum(distinct int_col), count(distinct smallint_col)
from alltypes
---- TYPES
DOUBLE,BIGINT,BIGINT
---- RESULTS
4.5,45,10
====
---- QUERY
# Multiple distinct without grouping. Last needs intermediate tuple.
select count(distinct tinyint_col), sum(distinct int_col), avg(distinct smallint_col)
from alltypes
---- TYPES
BIGINT,BIGINT,DOUBLE
---- RESULTS
10,45,4.5
====
---- QUERY
# Multiple distinct without grouping. All need intermediate tuples
select avg(distinct tinyint_col), avg(distinct int_col), avg(distinct smallint_col)
from alltypes
---- TYPES
DOUBLE,DOUBLE,DOUBLE
---- RESULTS
4.5,4.5,4.5
====
---- QUERY
# Multiple distinct with grouping.
select bigint_col, count(distinct tinyint_col), sum(distinct int_col),
       count(distinct smallint_col)
from alltypes group by bigint_col
---- TYPES
BIGINT,BIGINT,BIGINT,BIGINT
---- RESULTS
0,1,0,1
10,1,1,1
20,1,2,1
30,1,3,1
40,1,4,1
50,1,5,1
60,1,6,1
70,1,7,1
80,1,8,1
90,1,9,1
====
---- QUERY
# Multiple distinct with grouping. First needs intermediate tuple.
select bigint_col, avg(distinct tinyint_col), sum(distinct int_col),
       count(distinct smallint_col)
from alltypes group by bigint_col
---- TYPES
BIGINT,DOUBLE,BIGINT,BIGINT
---- RESULTS
0,0,0,1
10,1,1,1
20,2,2,1
30,3,3,1
40,4,4,1
50,5,5,1
60,6,6,1
70,7,7,1
80,8,8,1
90,9,9,1
====
---- QUERY
# Multiple distinct with grouping. Last needs intermediate tuple.
select bigint_col, count(distinct tinyint_col), sum(distinct int_col),
       avg(distinct smallint_col)
from alltypes group by bigint_col
---- TYPES
BIGINT,BIGINT,BIGINT,DOUBLE
---- RESULTS
0,1,0,0
10,1,1,1
20,1,2,2
30,1,3,3
40,1,4,4
50,1,5,5
60,1,6,6
70,1,7,7
80,1,8,8
90,1,9,9
====
---- QUERY
# Multiple distinct with grouping. All need intermediate tuples
select bigint_col, avg(distinct tinyint_col), avg(distinct int_col),
       avg(distinct smallint_col)
from alltypes group by bigint_col
---- TYPES
BIGINT,DOUBLE,DOUBLE,DOUBLE
---- RESULTS
0,0,0,0
10,1,1,1
20,2,2,2
30,3,3,3
40,4,4,4
50,5,5,5
60,6,6,6
70,7,7,7
80,8,8,8
90,9,9,9
====
---- QUERY
# Multiple distinct and non-distinct without grouping.
select count(distinct tinyint_col), count(distinct smallint_col), count(int_col)
from alltypes
---- TYPES
BIGINT,BIGINT,BIGINT
---- RESULTS
10,10,7300
====
---- QUERY
# Multiple distinct and non-distinct without grouping. First distinct needs
# intermediate agg tuple.
select avg(distinct tinyint_col), count(distinct smallint_col), count(int_col)
from alltypes
---- TYPES
DOUBLE,BIGINT,BIGINT
---- RESULTS
4.5,10,7300
====
---- QUERY
# Multiple distinct and non-distinct without grouping. Non-distinct needs
# intermediate agg tuple.
select count(distinct tinyint_col), count(distinct smallint_col), avg(int_col)
from alltypes
---- TYPES
BIGINT,BIGINT,DOUBLE
---- RESULTS
10,10,4.5
====
---- QUERY
# Multiple distinct and non-distinct without grouping. All need intermediate agg tuples.
select avg(distinct tinyint_col), avg(distinct smallint_col), avg(int_col)
from alltypes
---- TYPES
DOUBLE,DOUBLE,DOUBLE
---- RESULTS
4.5,4.5,4.5
====
---- QUERY
# Multiple distinct and non-distinct with grouping.
select bigint_col, count(distinct tinyint_col), count(distinct smallint_col),
       count(int_col)
from alltypes group by bigint_col
---- TYPES
BIGINT,BIGINT,BIGINT,BIGINT
---- RESULTS
0,1,1,730
10,1,1,730
20,1,1,730
30,1,1,730
40,1,1,730
50,1,1,730
60,1,1,730
70,1,1,730
80,1,1,730
90,1,1,730
====
---- QUERY
# Multiple distinct and non-distinct without grouping. First distinct needs
# intermediate agg tuple.
select bigint_col, avg(distinct tinyint_col), count(distinct smallint_col),
       count(int_col)
from alltypes group by bigint_col
---- TYPES
BIGINT,DOUBLE,BIGINT,BIGINT
---- RESULTS
0,0,1,730
10,1,1,730
20,2,1,730
30,3,1,730
40,4,1,730
50,5,1,730
60,6,1,730
70,7,1,730
80,8,1,730
90,9,1,730
====
---- QUERY
# Multiple distinct and non-distinct without grouping. Non-distinct needs
# intermediate agg tuple.
select bigint_col, count(distinct tinyint_col), count(distinct smallint_col),
       avg(int_col)
from alltypes group by bigint_col
---- TYPES
BIGINT,BIGINT,BIGINT,DOUBLE
---- RESULTS
0,1,1,0
10,1,1,1
20,1,1,2
30,1,1,3
40,1,1,4
50,1,1,5
60,1,1,6
70,1,1,7
80,1,1,8
90,1,1,9
====
---- QUERY
# Multiple distinct and non-distinct without grouping. All need intermediate agg tuples.
select bigint_col, avg(distinct tinyint_col), avg(distinct smallint_col),
       avg(int_col)
from alltypes group by bigint_col
---- TYPES
BIGINT,DOUBLE,DOUBLE,DOUBLE
---- RESULTS
0,0,0,0
10,1,1,1
20,2,2,2
30,3,3,3
40,4,4,4
50,5,5,5
60,6,6,6
70,7,7,7
80,8,8,8
90,9,9,9
====
---- QUERY
# Multiple distinct with constant and null
select count(distinct 0), count(distinct null) from alltypes
---- TYPES
BIGINT,BIGINT
---- RESULTS
1,0
====
---- QUERY
# Multiple distinct with agg that returns a string (group_concat)
select id, count(distinct id), group_concat(distinct string_col)
from alltypestiny group by id
---- TYPES
INT,BIGINT,STRING
---- RESULTS
4,1,'0'
2,1,'0'
6,1,'0'
0,1,'0'
7,1,'1'
1,1,'1'
5,1,'1'
3,1,'1'
====
---- QUERY
# Multiple distinct over more complex espressions
select count(distinct id % 2),
  count(distinct concat(string_col, 'a')) > 0,
  sum(distinct tinyint_col * 0),
  abs(count(distinct id) * 100)
from alltypestiny;
---- TYPES
BIGINT,BOOLEAN,BIGINT,BIGINT
---- RESULTS
2,True,0,800
====
---- QUERY
# Multiple distinct inside a subplan
select id, v.cnt, v.sm
from functional_parquet.complextypestbl a cross join
  (select count(distinct item) cnt, sum(distinct item) sm from a.int_array) v;
---- TYPES
BIGINT,BIGINT,BIGINT
---- RESULTS
1,3,6
2,3,6
3,0,NULL
4,0,NULL
5,0,NULL
6,0,NULL
7,0,NULL
8,1,-1
====
---- QUERY
# Multiple distinct with a subquery
select sum(distinct v.cnt), count(distinct v.sm)
from (select id, count(distinct int_col) cnt, sum(distinct tinyint_col) sm
  from alltypestiny group by id) v;
---- TYPES
BIGINT,BIGINT
---- RESULTS
1,2
====
---- QUERY
# Multiple distinct with NULLs (from the left outer join)
select count(distinct a.id), count(distinct b.id), avg(distinct b.tinyint_col)
from alltypessmall a left outer join alltypestiny b on a.id = b.id
where a.id < 12 and a.id > 5;
---- TYPES
BIGINT,BIGINT,DOUBLE
---- RESULTS
6,2,0.5
====
---- QUERY
# Multiple distinct with a larger number of classes
select
  count(distinct id),
  count(distinct tinyint_col),
  count(distinct smallint_col),
  count(distinct int_col),
  count(distinct bigint_col),
  count(distinct double_col),
  count(distinct float_col),
  count(distinct string_col),
  count(distinct timestamp_col)
from alltypestiny;
---- TYPES
BIGINT,BIGINT,BIGINT,BIGINT,BIGINT,BIGINT,BIGINT,BIGINT,BIGINT
---- RESULTS
8,2,2,2,2,2,2,2,8
====
---- QUERY
# IMPALA-7677: query that hits the path in StreamingAggregationNode where the final child
# batch is not fully processed in a single GetNext() call.
select
  count(distinct o_totalprice),
  count(distinct o_orderkey),
  count(distinct o_orderdate)
from tpch.orders
where 1 not in
  (select count(distinct o_custkey) from tpch.orders)
---- TYPES
BIGINT,BIGINT,BIGINT
---- RESULTS
1464556,1500000,2406
====
---- QUERY
# IMPALA-8969: Grouping aggregator can cause segmentation fault when doing multiple
# aggregations.
select sum(len_orderkey), sum(len_comment)
from (
  select
    length(group_concat(distinct cast(l_orderkey as string))) len_orderkey,
    length(group_concat(distinct(l_comment))) len_comment
    from tpch.lineitem
    group by l_comment
  ) v
---- TYPES
BIGINT,BIGINT
---- RESULTS
43737923,135857609
====