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

====
---- QUERY
####################################################
# Test case 1: Min-max filters of all possible types.
####################################################
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from alltypes a join [BROADCAST] alltypestiny b
where a.bool_col = (b.bool_col && !b.bool_col)
---- RESULTS
29200
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from alltypes a join [BROADCAST] alltypestiny b
where a.tinyint_col = b.tinyint_col
---- RESULTS
5840
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from alltypes a join [BROADCAST] alltypestiny b
where a.smallint_col = b.smallint_col
---- RESULTS
5840
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from alltypes a join [BROADCAST] alltypestiny b
where a.int_col = b.int_col
---- RESULTS
5840
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from alltypes a join [BROADCAST] alltypestiny b
where a.bigint_col = b.bigint_col
---- RESULTS
5840
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from alltypes a join [BROADCAST] alltypestiny b
where a.float_col = b.float_col
---- RESULTS
5840
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from alltypes a join [BROADCAST] alltypestiny b
where a.double_col = b.double_col
---- RESULTS
5840
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from alltypes a join [BROADCAST] alltypestiny b
where a.string_col = b.string_col
---- RESULTS
5840
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from alltypes a join [BROADCAST] alltypestiny b
where a.timestamp_col = b.timestamp_col
---- RESULTS
8
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d5_0 = b.d5_0
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 111
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d5_1 = b.d5_1
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 110
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d5_3 = b.d5_3
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 105
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d5_5 = b.d5_5 and b.d5_5 != 0
---- RESULTS
37
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 310
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d5_5 = b.d5_5 and b.d5_5 = 0
---- RESULTS
180
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 180
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d9_0 = b.d9_0
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 111
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d9_1 = b.d9_1
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 110
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d9_5 = b.d9_5
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 136
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d9_9 = b.d9_9 and b.d9_9 != 0
---- RESULTS
37
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 342
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d9_9 = b.d9_9 and b.d9_9 = 0
---- RESULTS
306
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 306
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d14_0 = b.d14_0
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 111
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d14_1 = b.d14_1
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 110
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d14_7 = b.d14_7
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 197
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d14_14 = b.d14_14 and b.d14_14 != 0
---- RESULTS
37
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 442
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d14_14 = b.d14_14 and b.d14_14 = 0
---- RESULTS
441
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 441
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d28_0 = b.d28_0
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 111
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d28_1 = b.d28_1
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 110
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d28_14 = b.d28_14
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 386
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d28_28 = b.d28_28 and b.d28_28 != 0
---- RESULTS
37
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 619
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d28_28 = b.d28_28 and b.d28_28 = 0
---- RESULTS
686
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 686
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d38_0 = b.d38_0
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 111
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d38_1 = b.d38_1
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 110
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d38_19 = b.d38_19
---- RESULTS
38
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 491
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d38_38 = b.d38_38 and b.d38_38 != 0
---- RESULTS
37
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 643
====
---- QUERY
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*) from decimal_rtf_tbl a
join [BROADCAST] decimal_rtf_tiny_tbl b
where a.d38_38 = b.d38_38 and b.d38_38 = 0
---- RESULTS
732
---- RUNTIME_PROFILE
aggregation(SUM, ProbeRows): 732
====

---- QUERY
####################################################
# Test case 2: Min-max filters on a primary key/partition column
####################################################
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN a.id, a.tinyint_col, b.id, b.tinyint_col
from alltypes a join [BROADCAST] alltypestiny b
where a.id = b.tinyint_col * 2;
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,0,4,0
0,0,2,0
0,0,0,0
0,0,6,0
2,2,3,1
2,2,7,1
2,2,5,1
2,2,1,1
----TYPES
INT,TINYINT,INT,TINYINT
====


---- QUERY
####################################################
# Test case 3: Target expr has an implicit integer cast
####################################################
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*)
from alltypes a join [BROADCAST] alltypes b
where a.tinyint_col = b.int_col and b.int_col in (0, 1)
---- RESULTS
1065800
====
---- QUERY
# The min/max values in the filter are both above the range of the target col so all rows
# are filtered.
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*)
from alltypes a join [BROADCAST] alltypes b
where a.tinyint_col = b.int_col + 10000
---- RESULTS
0
====
---- QUERY
# The min/max values in the filter are below/above the range for the target col,
# respectively, so no rows are filtered.
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select STRAIGHT_JOIN count(*)
from alltypes a join [BROADCAST]
  (values (min_int() x), (max_int()), (0)) v
where a.tinyint_col = v.x
---- RESULTS
730
====


---- QUERY
####################################################
# Test case 4: Two StringMinMaxFilters generated in the same fragment (IMPALA-7272).
###################################################
SET RUNTIME_FILTER_WAIT_TIME_MS=$RUNTIME_FILTER_WAIT_TIME_MS;
select straight_join a.l_orderkey
from tpch_kudu.lineitem a
  inner join /* +shuffle */ tpch_kudu.lineitem b on a.l_comment = b.l_comment
  inner join /* +shuffle */ tpch_kudu.lineitem c on b.l_comment = c.l_comment
where a.l_orderkey = 1
---- TYPES
BIGINT
---- RESULTS
1
1
1
1
1
1
1
1
1
====