# These tests check that the page selection and value value-skipping logic works well
# for nested types. 'nested_decimals' contains an array column of decimals.
# 'double_nested_decimals' contains an array of arrays of decimals column They are
# created in a way to have tiny, misaligned pages.
# The result set checks that the reading and skipping of values went well. And via
# on the runtime profile check we can verify that we used the page index properly.
====
---- QUERY
# Test value-skipping logic by selecting a single top-level row from each page.
# Skipping other rows.
select d_38, pos, item from nested_decimals n, n.arr where d_38 = 1
---- RESULTS
1,0,1
1,1,1
1,2,1
1,0,1
1,0,NULL
1,1,NULL
1,2,NULL
---- TYPES
DECIMAL, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 18
====
---- QUERY
# Test value-skipping logic by selecting a single top-level row from each page.
# Skipping other rows.
select d_38, pos, item from nested_decimals n, n.arr where d_38 = 2
---- RESULTS
2,0,2
2,1,2
2,2,2
2,0,2
2,1,2
2,0,2
2,1,NULL
2,2,NULL
---- TYPES
DECIMAL, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 18
====
---- QUERY
# Test value-skipping logic by selecting a single top-level row from each page.
# Skipping other rows.
select d_38, pos, item from nested_decimals n, n.arr where d_38 = 3
---- RESULTS
3,0,3
3,1,3
3,2,3
3,0,3
3,1,3
3,2,3
3,0,3
3,1,NULL
3,2,3
---- TYPES
DECIMAL, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 18
====
---- QUERY
# Test value-skipping logic by selecting a single top-level row from each page.
# Skipping other rows.
select d_38, pos, item from nested_decimals n, n.arr where d_38 = 4
---- RESULTS
4,0,4
4,1,4
4,2,4
4,0,4
4,1,4
4,2,4
4,3,4
4,0,NULL
4,1,4
4,2,4
4,3,NULL
---- TYPES
DECIMAL, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 18
====
---- QUERY
# Test value-skipping logic by selecting a single top-level row from each page.
# Skipping other rows.
select d_38, pos, item from nested_decimals n, n.arr where d_38 = 5
---- RESULTS
5,0,5
5,1,5
5,2,5
5,0,5
5,1,5
5,2,5
5,3,5
5,4,5
5,0,NULL
5,1,5
5,2,NULL
5,3,NULL
5,4,5
---- TYPES
DECIMAL, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 18
====
---- QUERY
# Test value-skipping logic by selecting a single top-level row from each page.
# Skipping other rows.
select d_38, pos, item from nested_decimals n, n.arr where d_38 = 6
---- RESULTS
6,0,6
6,1,NULL
6,2,6
6,0,NULL
6,1,NULL
6,2,NULL
6,3,6
---- TYPES
DECIMAL, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 21
====
---- QUERY
# Test value-skipping logic by selecting a single top-level row from each page.
# Skipping other rows.
select d_38, pos, item from nested_decimals n, n.arr where d_38 = 7
---- RESULTS
7,0,7
7,1,7
7,2,7
7,3,NULL
7,0,7
7,1,7
7,2,7
---- TYPES
DECIMAL, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 21
====
---- QUERY
# Test value-skipping logic by selecting a single top-level row from each page.
# Skipping other rows.
select d_38, pos, item from nested_decimals n, n.arr where d_38 = 8
---- RESULTS
8,0,NULL
8,1,NULL
8,2,8
---- TYPES
DECIMAL, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 22
====
---- QUERY
# Selecting the first rows from the pages.
select d_38, pos, item from nested_decimals n, n.arr where d_38 < 3
---- RESULTS
1,0,1
1,1,1
1,2,1
2,0,2
2,1,2
2,2,2
1,0,1
2,0,2
2,1,2
1,0,NULL
1,1,NULL
1,2,NULL
2,0,2
2,1,NULL
2,2,NULL
---- TYPES
DECIMAL, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 15
====
---- QUERY
# Selecting the last rows from the pages.
select d_38, pos, item from nested_decimals n, n.arr where d_38 > 2 and d_38 < 6
---- RESULTS
3,0,3
3,1,3
3,2,3
4,0,4
4,1,4
4,2,4
5,0,5
5,1,5
5,2,5
3,0,3
3,1,3
3,2,3
4,0,4
4,1,4
4,2,4
4,3,4
5,0,5
5,1,5
5,2,5
5,3,5
5,4,5
3,0,3
3,1,NULL
3,2,3
4,0,NULL
4,1,4
4,2,4
4,3,NULL
5,0,NULL
5,1,5
5,2,NULL
5,3,NULL
5,4,5
---- TYPES
DECIMAL, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 12
====
---- QUERY
# This query selects the first and last rows from a page, so it tests the case
# when we read, skip, and read values.
select d_38, pos, item from nested_decimals n, n.arr where d_38 > 5 and d_38 < 8
---- RESULTS
6,0,6
6,1,NULL
6,2,6
7,0,7
7,1,7
7,2,7
7,3,NULL
7,0,7
7,1,7
7,2,7
6,0,NULL
6,1,NULL
6,2,NULL
6,3,6
---- TYPES
DECIMAL, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 19
====
---- QUERY
# Selecting middle rows from a page.
select d_38, pos, item from nested_decimals n, n.arr where d_38 > 6
---- RESULTS
7,0,7
7,1,7
7,2,7
7,3,NULL
8,0,NULL
8,1,NULL
8,2,8
7,0,7
7,1,7
7,2,7
---- TYPES
DECIMAL, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 20
====
---- QUERY
# Filtering based on nested value.
select d_38, pos, item from nested_decimals n, n.arr where item = 1
---- RESULTS
1,0,1
1,1,1
1,2,1
1,0,1
---- TYPES
DECIMAL, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 12
====
---- QUERY
# Only selecting the nested item and its position. The Parquet scanner reads
# the values in batches in this case.
select pos, item from nested_decimals.arr where item < 3
---- RESULTS
0,1
1,1
2,1
0,2
1,2
2,2
0,1
0,2
1,2
0,2
---- TYPES
BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 1
====
---- QUERY
# Only selecting the nested item and its position. The Parquet scanner reads
# the values in batches in this case.
select pos, item from nested_decimals.arr where item < 8 and item > 5
---- RESULTS
0,6
2,6
0,7
1,7
2,7
0,7
1,7
2,7
3,6
---- TYPES
BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 3
====
---- QUERY
# Only selecting the nested item and its position. The Parquet scanner reads
# the values in batches in this case.
select pos, item from nested_decimals.arr where item = 8
---- RESULTS
2,8
---- TYPES
BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 3
====
---- QUERY
# Selecting one top-level row from a table that has a double-nested column.
# Skipping all other rows.
select d_38, a1.pos, a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where d_38 = 1
---- RESULTS
1,0,0,1
1,0,1,1
1,0,0,1
1,1,0,1
1,2,0,1
1,0,0,1
1,0,0,1
1,1,0,1
1,2,0,1
1,0,0,1
1,1,0,1
1,1,1,1
1,0,0,NULL
1,1,0,NULL
1,2,0,1
---- TYPES
DECIMAL, BIGINT, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 30
====
---- QUERY
# Selecting one top-level row from a table that has a double-nested column.
# Skipping all other rows.
select d_38, a1.pos, a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where d_38 = 2
---- RESULTS
2,0,0,2
2,0,1,2
2,0,0,2
2,1,0,2
2,0,0,2
2,1,0,2
2,0,0,2
2,1,0,2
2,0,0,2
2,0,0,NULL
2,1,0,NULL
2,2,0,NULL
---- TYPES
DECIMAL, BIGINT, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 30
====
---- QUERY
# Selecting one top-level row from a table that has a double-nested column.
# Skipping all other rows.
select d_38, a1.pos, a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where d_38 = 3
---- RESULTS
3,0,0,3
3,0,1,3
3,0,2,3
3,0,0,3
3,1,0,3
3,2,0,3
3,0,0,3
3,1,0,3
3,2,0,3
3,0,0,3
3,0,0,3
3,0,1,3
3,1,0,3
3,0,0,NULL
3,1,0,3
---- TYPES
DECIMAL, BIGINT, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 30
====
---- QUERY
# Selecting one top-level row from a table that has a double-nested column.
# Skipping all other rows.
select d_38, a1.pos, a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where d_38 = 4
---- RESULTS
4,0,0,4
4,0,1,4
4,0,2,4
4,0,0,4
4,1,0,4
4,0,0,4
4,1,0,4
4,0,0,4
4,1,0,4
4,0,0,4
4,1,0,4
4,1,1,4
4,2,0,4
4,2,0,NULL
4,2,1,NULL
4,2,2,NULL
4,2,3,NULL
4,2,4,NULL
---- TYPES
DECIMAL, BIGINT, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 30
====
---- QUERY
# Selecting one top-level row from a table that has a double-nested column.
# Skipping all other rows.
select d_38, a1.pos, a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where d_38 = 5
---- RESULTS
5,0,0,5
5,0,1,5
5,0,2,5
5,0,0,5
5,1,0,5
5,0,0,5
5,0,0,5
5,1,0,5
5,2,0,5
5,0,0,5
5,1,0,5
5,2,0,5
5,0,0,NULL
5,0,1,5
5,0,2,NULL
5,0,3,NULL
5,0,4,NULL
---- TYPES
DECIMAL, BIGINT, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 30
====
---- QUERY
# Selecting one top-level row from a table that has a double-nested column.
# Skipping all other rows.
select d_38, a1.pos, a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where d_38 = 6
---- RESULTS
6,0,0,6
6,0,1,NULL
6,1,0,6
6,0,0,NULL
6,0,1,NULL
6,0,2,6
6,1,0,NULL
6,1,1,6
---- TYPES
DECIMAL, BIGINT, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 39
====
---- QUERY
# Selecting one top-level row from a table that has a double-nested column.
# Skipping all other rows.
select d_38, a1.pos, a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where d_38 = 7
---- RESULTS
7,0,0,7
7,0,1,7
7,0,0,7
7,0,1,NULL
7,1,0,7
---- TYPES
DECIMAL, BIGINT, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 39
====
---- QUERY
# Selecting one top-level row from a table that has a double-nested column.
# Skipping all other rows.
select d_38, a1.pos, a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where d_38 = 8
---- RESULTS
8,0,0,NULL
8,0,1,NULL
8,0,2,8
---- TYPES
DECIMAL, BIGINT, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 40
====
---- QUERY
# Selecting frist couple of rows from the pages.
select d_38, a1.pos, a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where d_38 < 3
---- RESULTS
1,0,0,1
1,0,1,1
2,0,0,2
2,0,1,2
1,0,0,1
1,1,0,1
1,2,0,1
2,0,0,2
2,1,0,2
1,0,0,1
2,0,0,2
2,1,0,2
1,0,0,1
1,1,0,1
1,2,0,1
2,0,0,2
2,1,0,2
1,0,0,1
1,1,0,1
1,1,1,1
2,0,0,2
1,0,0,NULL
1,1,0,NULL
1,2,0,1
2,0,0,NULL
2,1,0,NULL
2,2,0,NULL
---- TYPES
DECIMAL, BIGINT, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 24
====
---- QUERY
# Selecting last couple of rows from the pages.
select d_38, a1.pos, a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where d_38 > 2 and d_38 < 6
---- RESULTS
3,0,0,3
3,0,1,3
3,0,2,3
4,0,0,4
4,0,1,4
4,0,2,4
5,0,0,5
5,0,1,5
5,0,2,5
3,0,0,3
3,1,0,3
3,2,0,3
4,0,0,4
4,1,0,4
5,0,0,5
5,1,0,5
3,0,0,3
3,1,0,3
3,2,0,3
4,0,0,4
4,1,0,4
5,0,0,5
3,0,0,3
4,0,0,4
4,1,0,4
5,0,0,5
5,1,0,5
5,2,0,5
3,0,0,3
3,0,1,3
3,1,0,3
4,0,0,4
4,1,0,4
4,1,1,4
4,2,0,4
5,0,0,5
5,1,0,5
5,2,0,5
3,0,0,NULL
3,1,0,3
4,2,0,NULL
4,2,1,NULL
4,2,2,NULL
4,2,3,NULL
4,2,4,NULL
5,0,0,NULL
5,0,1,5
5,0,2,NULL
5,0,3,NULL
5,0,4,NULL
---- TYPES
DECIMAL, BIGINT, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 18
====
---- QUERY
# This query selects the first and last rows from a page, so it tests the case
# when we read, then skip, then we read again from a page.
select d_38, a1.pos, a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where d_38 > 5 and d_38 < 8
---- RESULTS
6,0,0,6
6,0,1,NULL
6,1,0,6
7,0,0,7
7,0,1,7
7,0,0,7
7,0,1,NULL
7,1,0,7
6,0,0,NULL
6,0,1,NULL
6,0,2,6
6,1,0,NULL
6,1,1,6
---- TYPES
DECIMAL, BIGINT, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 37
====
---- QUERY
# Selecting middle rows from a page. So the scanner needs to skip, then read,
# then skip again.
select d_38, a1.pos, a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where d_38 > 6
---- RESULTS
7,0,0,7
7,0,1,7
8,0,0,NULL
8,0,1,NULL
8,0,2,8
7,0,0,7
7,0,1,NULL
7,1,0,7
---- TYPES
DECIMAL, BIGINT, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 38
====
---- QUERY
# Selecting rows based on the innermost item. Tests whether the page index works for
# nested columns.
select d_38, a1.pos, a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where a2.item = 1
---- RESULTS
1,0,0,1
1,0,1,1
1,0,0,1
1,1,0,1
1,2,0,1
1,0,0,1
1,0,0,1
1,1,0,1
1,2,0,1
1,0,0,1
1,1,0,1
1,1,1,1
1,2,0,1
---- TYPES
DECIMAL, BIGINT, BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 6
====
---- QUERY
# Only selecting the innermost item and its position column. The Parquet scanner reads
# the values in batches in this case.
select a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where a2.item = 2
---- RESULTS
0,2
1,2
0,2
0,2
0,2
0,2
0,2
0,2
0,2
---- TYPES
BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 1
====
---- QUERY
# Only selecting the innermost item and its position column. The Parquet scanner reads
# the values in batches in this case.
select a2.pos, a2.item from double_nested_decimals d, d.arr a1, a1.item a2
where a2.item > 5 and a2.item < 7
---- RESULTS
0,6
0,6
2,6
1,6
---- TYPES
BIGINT, DECIMAL
---- RUNTIME_PROFILE
aggregation(SUM, NumStatsFilteredPages): 6
====