testdata/workloads/functional-query/queries/QueryTest/spilling-regression-exhaustive.test

====
---- QUERY
# Test sort with small char column materialized by exprs.
# Set low mem_limit to force spilling. mem_limit is tuned for a 3-node HDFS minicluster.
# IMPALA-3332: comparator makes local allocations that cause runaway memory consumption.
set buffer_pool_limit=28m;
set mem_limit=200m;
set disable_outermost_topn=1;
select cast(l_comment as char(50))
from lineitem
order by 1
limit 20;
---- RESULTS
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias about the en                            '
' Tiresias about the slyly ironic dinos ca         '
' Tiresias about the slyly unus                    '
' Tiresias above                                   '
' Tiresias above the fox                           '
' Tiresias above the furiously final th            '
' Tiresias above the slyly expr                    '
' Tiresias above the stealthily p                  '
---- TYPES
CHAR
---- RUNTIME_PROFILE
# Verify that the sort actually spilled
row_regex: .*SpilledRuns: .* \([1-9][0-9]*\)
row_regex: .*TotalMergesPerformed: .* \([1-9][0-9]*\)
====
---- QUERY
# Test sort with small input char column materialized before sort.
# mem_limit is tuned for a 3-node HDFS minicluster.
set mem_limit=200m;
set buffer_pool_limit=28m;
set disable_outermost_topn=1;
select char_col
from (select cast(l_comment as char(50)) char_col
      from lineitem) subquery
order by 1
limit 20;
---- RESULTS
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias                                         '
' Tiresias about the en                            '
' Tiresias about the slyly ironic dinos ca         '
' Tiresias about the slyly unus                    '
' Tiresias above                                   '
' Tiresias above the fox                           '
' Tiresias above the furiously final th            '
' Tiresias above the slyly expr                    '
' Tiresias above the stealthily p                  '
---- TYPES
CHAR
---- RUNTIME_PROFILE
# Verify that the sort actually spilled
row_regex: .*SpilledRuns: .* \([1-9][0-9]*\)
row_regex: .*TotalMergesPerformed: .* \([1-9][0-9]*\)
====
---- QUERY
# Test sort with large input char column materialized before sort.
# Set low mem_limit to force spilling. mem_limit is tuned for a 3-node HDFS minicluster.
set mem_limit=200m;
set buffer_pool_limit=28m;
set disable_outermost_topn=1;
select char_col
from (select cast(l_comment as char(200)) char_col
      from lineitem) subquery
order by 1
limit 20;
---- RESULTS
' Tiresias                                                                                                                                                                                               '
' Tiresias                                                                                                                                                                                               '
' Tiresias                                                                                                                                                                                               '
' Tiresias                                                                                                                                                                                               '
' Tiresias                                                                                                                                                                                               '
' Tiresias                                                                                                                                                                                               '
' Tiresias                                                                                                                                                                                               '
' Tiresias                                                                                                                                                                                               '
' Tiresias                                                                                                                                                                                               '
' Tiresias                                                                                                                                                                                               '
' Tiresias                                                                                                                                                                                               '
' Tiresias                                                                                                                                                                                               '
' Tiresias about the en                                                                                                                                                                                  '
' Tiresias about the slyly ironic dinos ca                                                                                                                                                               '
' Tiresias about the slyly unus                                                                                                                                                                          '
' Tiresias above                                                                                                                                                                                         '
' Tiresias above the fox                                                                                                                                                                                 '
' Tiresias above the furiously final th                                                                                                                                                                  '
' Tiresias above the slyly expr                                                                                                                                                                          '
' Tiresias above the stealthily p                                                                                                                                                                        '
---- TYPES
CHAR
---- RUNTIME_PROFILE
# Verify that the sort actually spilled
row_regex: .*SpilledRuns: .* \([1-9][0-9]*\)
row_regex: .*TotalMergesPerformed: .* \([1-9][0-9]*\)
====
---- QUERY
# Test sort with varchar column materialized by exprs.
# Set low mem_limit to force spilling. mem_limit is tuned for a 3-node HDFS minicluster.
set buffer_pool_limit=28m;
# IMPALA-3332: comparator makes local allocations that cause runaway memory consumption.
set mem_limit=200m;
set disable_outermost_topn=1;
select cast(l_comment as varchar(50))
from lineitem
order by 1
limit 20;
---- RESULTS
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias about the en'
' Tiresias about the slyly ironic dinos ca'
' Tiresias about the slyly unus'
' Tiresias above'
' Tiresias above the fox'
' Tiresias above the furiously final th'
' Tiresias above the slyly expr'
' Tiresias above the stealthily p'
---- TYPES
STRING
---- RUNTIME_PROFILE
# Verify that the sort actually spilled
row_regex: .*SpilledRuns: .* \([1-9][0-9]*\)
row_regex: .*TotalMergesPerformed: .* \([1-9][0-9]*\)
====
---- QUERY
# Test sort with input varchar column materialized before sort.
# Set low mem_limit to force spilling. mem_limit is tuned for a 3-node HDFS minicluster.
set mem_limit=200m;
set buffer_pool_limit=28m;
set disable_outermost_topn=1;
select char_col
from (select cast(l_comment as varchar(50)) char_col
      from lineitem) subquery
order by 1
limit 20;
---- RESULTS
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias '
' Tiresias about the en'
' Tiresias about the slyly ironic dinos ca'
' Tiresias about the slyly unus'
' Tiresias above'
' Tiresias above the fox'
' Tiresias above the furiously final th'
' Tiresias above the slyly expr'
' Tiresias above the stealthily p'
---- TYPES
STRING
---- RUNTIME_PROFILE
# Verify that the sort actually spilled
row_regex: .*SpilledRuns: .* \([1-9][0-9]*\)
row_regex: .*TotalMergesPerformed: .* \([1-9][0-9]*\)
====
---- QUERY
# Regression test for IMPALA-2612. The following query will cause CastToChar
# to be invoked when building the hash tables in partitioned aggregation
# nodes. CastToChar will do "local" memory allocation. Without the fix of
# IMPALA-2612, the peak memory consumption will be higher.
# mem_limit is tuned to reproduce the issue on a 3-node HDFS minicluster.
set mem_limit=800m;
set num_scanner_threads=1;
select count(distinct concat(cast(l_comment as char(120)), cast(l_comment as char(120)),
                             cast(l_comment as char(120)), cast(l_comment as char(120)),
                             cast(l_comment as char(120)), cast(l_comment as char(120))))
from lineitem
---- RESULTS
4502054
---- TYPES
BIGINT
---- RUNTIME_PROFILE
# Verify that the agg spilled.
row_regex: .*SpilledPartitions: .* \([1-9][0-9]*\)
====
---- QUERY
# Same as above, except disable streaming preaggs to ensure that AggregationNode is also
# releasing local memory allocations as appropriate.
set mem_limit=800m;
set num_scanner_threads=1;
set disable_streaming_preaggregations=true;
select count(distinct concat(cast(l_comment as char(120)), cast(l_comment as char(120)),
                             cast(l_comment as char(120)), cast(l_comment as char(120)),
                             cast(l_comment as char(120)), cast(l_comment as char(120))))
from lineitem
---- RESULTS
4502054
---- TYPES
BIGINT
---- RUNTIME_PROFILE
# Verify that the agg spilled.
row_regex: .*SpilledPartitions: .* \([1-9][0-9]*\)
====
---- QUERY
# Same as above, except use a non-grouping aggregate function to ensure that
# NonGroupingAggregator is also releasing local memory allocations as appropriate.
set mem_limit=50m;
set num_scanner_threads=1;
select min(regexp_replace(l_comment, ".", "x"))
from lineitem
---- RESULTS
'xxxxxxxxxx'
---- TYPES
STRING
====
---- QUERY
# IMPALA-3304: test that avg() can spill with a query mem limit.
# This test only covers that use FIXED_UDA_INTERMEDIATE, not functions that allocate
# strings for intermediate values. mem_limit is tuned to reproduce the issue on a 3-node
# HDFS minicluster.
set mem_limit=100m;
select l_orderkey, avg(l_tax), avg(l_quantity), avg(l_discount), avg(l_extendedprice)
from tpch_parquet.lineitem
group by 1
order by avg(l_extendedprice) desc, avg(l_discount)
limit 5
---- TYPES
BIGINT,DECIMAL,DECIMAL,DECIMAL,DECIMAL
---- RESULTS
3811460,0.050000,50.000000,0.050000,104899.500000
1744195,0.040000,50.000000,0.090000,104649.500000
5151266,0.070000,50.000000,0.000000,104449.500000
4571042,0.030000,50.000000,0.090000,104399.500000
1198304,0.010000,50.000000,0.020000,104299.500000
---- RUNTIME_PROFILE
row_regex: .*SpilledPartitions: .* \([1-9][0-9]*\)
====