testdata/workloads/functional-query/queries/QueryTest/iceberg-v2-read-position-deletes.test

====
---- QUERY
SELECT count(*) from iceberg_v2_no_deletes
---- RESULTS
3
---- TYPES
bigint
---- RUNTIME_PROFILE
aggregation(SUM, NumRowGroups): 0
aggregation(SUM, NumFileMetadataRead): 0
====
---- QUERY
SELECT count(*) from iceberg_v2_delete_positional for system_version as of 6816997371555012807
---- RESULTS
3
---- TYPES
bigint
---- RUNTIME_PROFILE
aggregation(SUM, NumRowGroups): 0
aggregation(SUM, NumFileMetadataRead): 0
====
---- QUERY
SELECT count(*) from iceberg_v2_delete_positional;
---- RESULTS
2
---- TYPES
bigint
---- RUNTIME_PROFILE
aggregation(SUM, NumRowGroups): 2
aggregation(SUM, NumFileMetadataRead): 0
====
---- QUERY
SELECT count(*) from iceberg_v2_positional_delete_all_rows for system_version as of 8593920101374128463
---- RESULTS
3
---- TYPES
bigint
---- RUNTIME_PROFILE
aggregation(SUM, NumRowGroups): 0
aggregation(SUM, NumFileMetadataRead): 0
====
---- QUERY
SELECT count(*) from iceberg_v2_positional_delete_all_rows
---- RESULTS
0
---- TYPES
bigint
---- RUNTIME_PROFILE
aggregation(SUM, NumRowGroups): 2
aggregation(SUM, NumFileMetadataRead): 0
====
---- QUERY
SHOW FILES IN iceberg_v2_positional_not_all_data_files_have_delete_files;
---- RESULTS
row_regex:'$NAMENODE/test-warehouse/iceberg_test/hadoop_catalog/ice/iceberg_v2_positional_not_all_data_files_have_delete_files/data/00000-0-data.*.parquet','.*','','$ERASURECODE_POLICY'
row_regex:'$NAMENODE/test-warehouse/iceberg_test/hadoop_catalog/ice/iceberg_v2_positional_not_all_data_files_have_delete_files/data/00000-0-data.*.parquet','.*','','$ERASURECODE_POLICY'
row_regex:'$NAMENODE/test-warehouse/iceberg_test/hadoop_catalog/ice/iceberg_v2_positional_not_all_data_files_have_delete_files/data/00000-0-data.*.parquet','.*','','$ERASURECODE_POLICY'
row_regex:'$NAMENODE/test-warehouse/iceberg_test/hadoop_catalog/ice/iceberg_v2_positional_not_all_data_files_have_delete_files/data/00000-0-data.*.parquet','.*','','$ERASURECODE_POLICY'
row_regex:'$NAMENODE/test-warehouse/iceberg_test/hadoop_catalog/ice/iceberg_v2_positional_not_all_data_files_have_delete_files/data/00000-0-delete.*.parquet','.*','','$ERASURECODE_POLICY'
row_regex:'$NAMENODE/test-warehouse/iceberg_test/hadoop_catalog/ice/iceberg_v2_positional_not_all_data_files_have_delete_files/data/00000-0-delete.*.parquet','.*','','$ERASURECODE_POLICY'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
SELECT count(*) from iceberg_v2_positional_not_all_data_files_have_delete_files for system_version as of 7490459762454857930
---- RESULTS
10
---- TYPES
bigint
---- RUNTIME_PROFILE
aggregation(SUM, NumRowGroups): 0
aggregation(SUM, NumFileMetadataRead): 0
====
---- QUERY
SELECT count(*) from iceberg_v2_positional_not_all_data_files_have_delete_files for system_version as of 752781918366351945
---- RESULTS
9
---- TYPES
bigint
---- RUNTIME_PROFILE
aggregation(SUM, NumRowGroups): 2
aggregation(SUM, NumFileMetadataRead): 0
====
---- QUERY
SELECT count(*) from iceberg_v2_positional_not_all_data_files_have_delete_files
---- RESULTS
6
---- TYPES
bigint
---- RUNTIME_PROFILE
aggregation(SUM, NumRowGroups): 4
aggregation(SUM, NumFileMetadataRead): 0
====
---- QUERY
SHOW FILES IN iceberg_v2_positional_update_all_rows;
---- RESULTS
row_regex:'$NAMENODE/test-warehouse/iceberg_test/hadoop_catalog/ice/iceberg_v2_positional_update_all_rows/data/00000-0-data.*.parquet','.*','','$ERASURECODE_POLICY'
row_regex:'$NAMENODE/test-warehouse/iceberg_test/hadoop_catalog/ice/iceberg_v2_positional_update_all_rows/data/00000-0-data.*.parquet','.*','','$ERASURECODE_POLICY'
row_regex:'$NAMENODE/test-warehouse/iceberg_test/hadoop_catalog/ice/iceberg_v2_positional_update_all_rows/data/00000-0-delete.*.parquet','.*','','$ERASURECODE_POLICY'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
SELECT count(*) from iceberg_v2_positional_update_all_rows for system_version as of 3877007445826010687
---- RESULTS
3
---- TYPES
bigint
---- RUNTIME_PROFILE
aggregation(SUM, NumRowGroups): 2
aggregation(SUM, NumFileMetadataRead): 0
====
---- QUERY
SELECT count(*) from iceberg_v2_positional_update_all_rows
---- RESULTS
3
---- TYPES
bigint
---- RUNTIME_PROFILE
aggregation(SUM, NumRowGroups): 2
aggregation(SUM, NumFileMetadataRead): 0
====
---- QUERY
SELECT count(*) from iceberg_v2_partitioned_position_deletes for system_version as of 2057976186205897384
---- RESULTS
20
---- TYPES
bigint
---- RUNTIME_PROFILE
aggregation(SUM, NumRowGroups): 0
aggregation(SUM, NumFileMetadataRead): 0
====
---- QUERY
# Checks that each delete row is only sent once to the build side of the join because
# it's either a directed Iceberg join (when V2 opt is ON) or because it's a partitioned
# join (when V2 opt is OFF).
SELECT count(*) from iceberg_v2_partitioned_position_deletes
---- RESULTS
10
---- TYPES
bigint
---- RUNTIME_PROFILE
aggregation(SUM, BuildRows): 10
aggregation(SUM, NumRowGroups): 6
aggregation(SUM, NumFileMetadataRead): 0
====
---- QUERY
SELECT count(*) from iceberg_v2_no_deletes where i = 2;
---- RESULTS
1
---- TYPES
bigint
====
---- QUERY
SELECT count(*) from iceberg_v2_delete_positional where id = 2;
---- RESULTS
0
---- TYPES
bigint
====
---- QUERY
SELECT count(*) from iceberg_v2_positional_delete_all_rows where i > 2;
---- RESULTS
0
---- TYPES
bigint
====
---- QUERY
SELECT count(*) from iceberg_v2_positional_not_all_data_files_have_delete_files
where i < 4;
---- RESULTS
3
---- TYPES
bigint
====
---- QUERY
SELECT count(*) from iceberg_v2_positional_update_all_rows
where i = 3;
---- RESULTS
1
---- TYPES
bigint
====
---- QUERY
SELECT count(*) from iceberg_v2_partitioned_position_deletes
where action = 'click';
---- RESULTS
3
---- TYPES
bigint
====
---- QUERY
SELECT * from iceberg_v2_no_deletes
---- RESULTS
1,'x'
2,'y'
3,'z'
---- TYPES
INT, STRING
====
---- QUERY
SELECT * from iceberg_v2_delete_positional;
---- RESULTS
1,'a'
3,'c'
---- TYPES
BIGINT, STRING
====
---- QUERY
SELECT * from iceberg_v2_positional_delete_all_rows;
---- RESULTS
---- TYPES
INT, STRING
====
---- QUERY
SELECT * from iceberg_v2_positional_not_all_data_files_have_delete_files
---- RESULTS
1,'a'
2,'b'
3,'c'
5,'X'
4,'d'
6,'f'
---- TYPES
INT, STRING
====
---- QUERY
SELECT * from iceberg_v2_positional_update_all_rows
---- RESULTS
1,'A'
2,'B'
3,'C'
---- TYPES
INT, STRING
====
---- QUERY
SELECT * from iceberg_v2_partitioned_position_deletes
---- RESULTS
6,'Alex','view',2020-01-01 09:00:00
20,'Alex','view',2020-01-01 09:00:00
4,'Alex','view',2020-01-01 09:00:00
18,'Alan','click',2020-01-01 10:00:00
12,'Alan','click',2020-01-01 10:00:00
10,'Alan','click',2020-01-01 10:00:00
2,'Lisa','download',2020-01-01 11:00:00
14,'Lisa','download',2020-01-01 11:00:00
8,'Lisa','download',2020-01-01 11:00:00
16,'Lisa','download',2020-01-01 11:00:00
---- TYPES
INT, STRING, STRING, TIMESTAMP
====
---- QUERY
SELECT strright(upper(input__file__name),90), file__position + 1000, * from iceberg_v2_no_deletes
---- RESULTS
'_20220819180420_A7E5A731-8762-4B59-B3F2-FE6F065CF59B-JOB_16597105613620_0031-00001.PARQUET',1000,1,'x'
'_20220819180420_A7E5A731-8762-4B59-B3F2-FE6F065CF59B-JOB_16597105613620_0031-00001.PARQUET',1001,2,'y'
'_20220819180420_A7E5A731-8762-4B59-B3F2-FE6F065CF59B-JOB_16597105613620_0031-00001.PARQUET',1002,3,'z'
---- TYPES
STRING, BIGINT, INT, STRING
====
---- QUERY
SELECT strright(upper(input__file__name),90), file__position + 1000, * from iceberg_v2_delete_positional;
---- RESULTS
'EBERG_V2_DELETE_POSITIONAL/DATA/00000-0-FB178C51-B12A-4C5F-A66E-A8E9375DAEBA-00001.PARQUET',1000,1,'a'
'EBERG_V2_DELETE_POSITIONAL/DATA/00000-0-FB178C51-B12A-4C5F-A66E-A8E9375DAEBA-00001.PARQUET',1002,3,'c'
---- TYPES
STRING, BIGINT, BIGINT, STRING
====
---- QUERY
SELECT strright(upper(input__file__name),90), file__position + 1000, * from iceberg_v2_positional_delete_all_rows;
---- RESULTS
---- TYPES
STRING, BIGINT, INT, STRING
====
---- QUERY
SELECT strright(upper(input__file__name),90), file__position + 1000, * from iceberg_v2_positional_not_all_data_files_have_delete_files
---- RESULTS
'_20220819154646_1CAD8C38-C65E-4C7C-B516-C4D9FAF82448-JOB_16597105613620_0026-00001.PARQUET',1000,1,'a'
'_20220819154646_1CAD8C38-C65E-4C7C-B516-C4D9FAF82448-JOB_16597105613620_0026-00001.PARQUET',1001,2,'b'
'_20220819154646_1CAD8C38-C65E-4C7C-B516-C4D9FAF82448-JOB_16597105613620_0026-00001.PARQUET',1002,3,'c'
'_20220819154922_62429D29-6C44-4707-B348-AC189B8D79D3-JOB_16597105613620_0026-00001.PARQUET',1000,5,'X'
'_20220819154718_DB95AEAE-D530-4FBA-8336-E47FA712B987-JOB_16597105613620_0026-00001.PARQUET',1000,4,'d'
'_20220819154718_DB95AEAE-D530-4FBA-8336-E47FA712B987-JOB_16597105613620_0026-00001.PARQUET',1002,6,'f'
---- TYPES
STRING, BIGINT, INT, STRING
====
---- QUERY
SELECT strright(upper(input__file__name),90), file__position + 1000, * from iceberg_v2_positional_update_all_rows
---- RESULTS
'_20220819155811_BF677DB1-CB6C-451B-AFC8-4013AE7EAF1F-JOB_16597105613620_0030-00001.PARQUET',1000,1,'A'
'_20220819155811_BF677DB1-CB6C-451B-AFC8-4013AE7EAF1F-JOB_16597105613620_0030-00001.PARQUET',1001,2,'B'
'_20220819155811_BF677DB1-CB6C-451B-AFC8-4013AE7EAF1F-JOB_16597105613620_0030-00001.PARQUET',1002,3,'C'
---- TYPES
STRING, BIGINT, INT, STRING
====
---- QUERY
SELECT strright(upper(input__file__name),90), file__position + 1000, * from iceberg_v2_partitioned_position_deletes
---- RESULTS
'POSITION_DELETES/DATA/ACTION=VIEW/874B32D9A15DA206-F60E01CB00000004_1711435901_DATA.0.PARQ',1000,6,'Alex','view',2020-01-01 09:00:00
'POSITION_DELETES/DATA/ACTION=VIEW/874B32D9A15DA206-F60E01CB00000004_1711435901_DATA.0.PARQ',1003,20,'Alex','view',2020-01-01 09:00:00
'POSITION_DELETES/DATA/ACTION=VIEW/874B32D9A15DA206-F60E01CB00000004_1711435901_DATA.0.PARQ',1007,4,'Alex','view',2020-01-01 09:00:00
'TION_DELETES/DATA/ACTION=DOWNLOAD/874B32D9A15DA206-F60E01CB00000003_1489587766_DATA.0.PARQ',1000,2,'Lisa','download',2020-01-01 11:00:00
'TION_DELETES/DATA/ACTION=DOWNLOAD/874B32D9A15DA206-F60E01CB00000003_1489587766_DATA.0.PARQ',1002,14,'Lisa','download',2020-01-01 11:00:00
'TION_DELETES/DATA/ACTION=DOWNLOAD/874B32D9A15DA206-F60E01CB00000003_1489587766_DATA.0.PARQ',1003,8,'Lisa','download',2020-01-01 11:00:00
'TION_DELETES/DATA/ACTION=DOWNLOAD/874B32D9A15DA206-F60E01CB00000003_1489587766_DATA.0.PARQ',1005,16,'Lisa','download',2020-01-01 11:00:00
'OSITION_DELETES/DATA/ACTION=CLICK/874B32D9A15DA206-F60E01CB00000003_1034098606_DATA.0.PARQ',1001,18,'Alan','click',2020-01-01 10:00:00
'OSITION_DELETES/DATA/ACTION=CLICK/874B32D9A15DA206-F60E01CB00000003_1034098606_DATA.0.PARQ',1003,12,'Alan','click',2020-01-01 10:00:00
'OSITION_DELETES/DATA/ACTION=CLICK/874B32D9A15DA206-F60E01CB00000003_1034098606_DATA.0.PARQ',1005,10,'Alan','click',2020-01-01 10:00:00
---- TYPES
STRING, BIGINT, INT, STRING, STRING, TIMESTAMP
====
---- QUERY
SELECT * from iceberg_v2_no_deletes where i = 2;
---- RESULTS
2,'y'
---- TYPES
INT, STRING
====
---- QUERY
SELECT * from iceberg_v2_delete_positional where id = 2;
---- RESULTS
---- TYPES
BIGINT, STRING
====
---- QUERY
SELECT * from iceberg_v2_positional_delete_all_rows where i > 2;
---- RESULTS
---- TYPES
INT, STRING
====
---- QUERY
SELECT * from iceberg_v2_positional_not_all_data_files_have_delete_files
where i < 4;
---- RESULTS
1,'a'
2,'b'
3,'c'
---- TYPES
INT, STRING
====
---- QUERY
SELECT * from iceberg_v2_positional_update_all_rows
where i = 3;
---- RESULTS
3,'C'
---- TYPES
INT, STRING
====
---- QUERY
SELECT * from iceberg_v2_partitioned_position_deletes
where action = 'click';
---- RESULTS
18,'Alan','click',2020-01-01 10:00:00
12,'Alan','click',2020-01-01 10:00:00
10,'Alan','click',2020-01-01 10:00:00
---- TYPES
INT, STRING, STRING, TIMESTAMP
====
---- QUERY
select count(*)
from iceberg_v2_positional_not_all_data_files_have_delete_files a,
     iceberg_v2_positional_update_all_rows b
where a.i = b.i;
---- RESULTS
3
---- TYPES
BIGINT
====
---- QUERY
select *
from iceberg_v2_positional_not_all_data_files_have_delete_files a,
     iceberg_v2_positional_update_all_rows b
where a.i = b.i;
---- RESULTS
1,'a',1,'A'
2,'b',2,'B'
3,'c',3,'C'
---- TYPES
INT, STRING, INT, STRING
====
---- QUERY
select *
from iceberg_v2_positional_not_all_data_files_have_delete_files
where i = (select max(i) from iceberg_v2_positional_update_all_rows);
---- RESULTS
3,'c'
---- TYPES
INT, STRING
====
---- QUERY
SELECT action, count(*) from iceberg_v2_partitioned_position_deletes
group by action;
---- RESULTS
'click',3
'download',4
'view',3
---- TYPES
STRING, BIGINT
====
---- QUERY
select * from iceberg_v2_positional_not_all_data_files_have_delete_files
union all
select * from iceberg_v2_positional_not_all_data_files_have_delete_files for system_version as of 5762682948883272650
---- RESULTS
4,'d'
5,'e'
6,'f'
1,'a'
2,'b'
3,'c'
5,'X'
4,'d'
6,'f'
1,'a'
2,'b'
3,'c'
---- TYPES
INT, STRING
====
---- QUERY
select * from iceberg_v2_positional_not_all_data_files_have_delete_files for system_version as of 1497619269847778439
minus
select * from iceberg_v2_positional_not_all_data_files_have_delete_files for system_version as of 4363979609026842966;
---- RESULTS
5,'X'
4,'d'
6,'f'
---- TYPES
INT, STRING
====
---- QUERY
with v as (select i + 1000 as ii, upper(s) as ss from iceberg_v2_positional_not_all_data_files_have_delete_files)
select * from v where ii > 1003;
---- RESULTS
1005,'X'
1004,'D'
1006,'F'
---- TYPES
BIGINT, STRING
====
---- QUERY
SET TIMEZONE='Europe/Budapest';
select *
from functional_parquet.iceberg_v2_partitioned_position_deletes a,
     functional_parquet.iceberg_partitioned b
where a.action = b.action and b.id=3;
---- RESULTS
12,'Alan','click',2020-01-01 10:00:00,3,'Alan','click',2020-01-01 10:00:00
10,'Alan','click',2020-01-01 10:00:00,3,'Alan','click',2020-01-01 10:00:00
18,'Alan','click',2020-01-01 10:00:00,3,'Alan','click',2020-01-01 10:00:00
---- TYPES
INT, STRING, STRING, TIMESTAMP, INT, STRING, STRING, TIMESTAMP
====
---- QUERY
SET TIMEZONE='Europe/Budapest';
select a.input__file__name, a.*
from iceberg_partitioned a,
     iceberg_partitioned b
where a.id = b.id and a.action = b.action and b.user = 'Lisa'
order by a.id;
---- RESULTS
regex:'$NAMENODE/test-warehouse/iceberg_test/iceberg_partitioned/data/event_time_hour=2020-01-01-10/action=download/.*parquet',2,'Lisa','download',2020-01-01 11:00:00
regex:'$NAMENODE/test-warehouse/iceberg_test/iceberg_partitioned/data/event_time_hour=2020-01-01-10/action=download/.*parquet',5,'Lisa','download',2020-01-01 11:00:00
regex:'$NAMENODE/test-warehouse/iceberg_test/iceberg_partitioned/data/event_time_hour=2020-01-01-10/action=download/.*parquet',7,'Lisa','download',2020-01-01 11:00:00
regex:'$NAMENODE/test-warehouse/iceberg_test/iceberg_partitioned/data/event_time_hour=2020-01-01-10/action=download/.*parquet',8,'Lisa','download',2020-01-01 11:00:00
regex:'$NAMENODE/test-warehouse/iceberg_test/iceberg_partitioned/data/event_time_hour=2020-01-01-10/action=download/.*parquet',14,'Lisa','download',2020-01-01 11:00:00
regex:'$NAMENODE/test-warehouse/iceberg_test/iceberg_partitioned/data/event_time_hour=2020-01-01-10/action=download/.*parquet',16,'Lisa','download',2020-01-01 11:00:00
---- TYPES
STRING, INT, STRING, STRING, TIMESTAMP
====
---- QUERY
SELECT count(*) from iceberg_lineitem_multiblock;
---- RESULTS
17886
---- TYPES
bigint
====
---- QUERY
SET BATCH_SIZE=2;
SELECT count(*) from iceberg_lineitem_multiblock;
---- RESULTS
17886
---- TYPES
bigint
====
---- QUERY
select count(*) from iceberg_lineitem_multiblock where l_linenumber%5=0;
---- RESULTS
0
---- TYPES
bigint
====
---- QUERY
SET BATCH_SIZE=2;
select count(*) from iceberg_lineitem_multiblock where l_linenumber%5=0;
---- RESULTS
0
---- TYPES
bigint
====
---- QUERY
SET MT_DOP=0;
select count(*) from iceberg_lineitem_sixblocks;
---- RESULTS
19836
---- TYPES
bigint
====
---- QUERY
SET MT_DOP=2;
select count(*) from iceberg_lineitem_sixblocks;
---- RESULTS
19836
---- TYPES
bigint
====
---- QUERY
SET MT_DOP=0;
select count(*) from iceberg_lineitem_sixblocks where l_returnflag='N';
---- RESULTS
0
---- TYPES
bigint
====
---- QUERY
SET MT_DOP=2;
select count(*) from iceberg_lineitem_sixblocks where l_returnflag='N';
---- RESULTS
0
---- TYPES
bigint
====
---- QUERY
SELECT * from iceberg_v2_partitioned_position_deletes;
---- RESULTS
6,'Alex','view',2020-01-01 09:00:00
20,'Alex','view',2020-01-01 09:00:00
4,'Alex','view',2020-01-01 09:00:00
18,'Alan','click',2020-01-01 10:00:00
12,'Alan','click',2020-01-01 10:00:00
10,'Alan','click',2020-01-01 10:00:00
2,'Lisa','download',2020-01-01 11:00:00
14,'Lisa','download',2020-01-01 11:00:00
8,'Lisa','download',2020-01-01 11:00:00
16,'Lisa','download',2020-01-01 11:00:00
---- TYPES
INT, STRING, STRING, TIMESTAMP
====
---- QUERY
# Changing back in case we add new tests later
SET BATCH_SIZE=0;
====
---- QUERY
# IMPALA-12984: Wrong count(*) results when having dangling deletes after a Spark
# compaction.
select count(*) from functional_parquet.iceberg_spark_compaction_with_dangling_delete;
---- RESULTS
4
---- TYPES
bigint
---- RUNTIME_PROFILE
aggregation(SUM, NumRowGroups): 0
aggregation(SUM, NumFileMetadataRead): 0
====
---- QUERY
# IMPALA-12984: Let's see that we can disable Iceberg statistic-based
# count(*) optimization.
set iceberg_disable_count_star_optimization=true;
select count(*) from functional_parquet.iceberg_spark_compaction_with_dangling_delete;
---- RESULTS
4
---- TYPES
bigint
---- RUNTIME_PROFILE
aggregation(SUM, NumRowGroups): 0
aggregation(SUM, NumFileMetadataRead): 1
====
---- QUERY
# Regression test for IMPALA-14154.
# To reproduce the bug, we need a table where there is no data file without a delete file.
# The metadata table query ensures that.
select content from functional_parquet.iceberg_v2_delete_positional.`files`;
---- RESULTS
0
1
---- TYPES
INT
====
---- QUERY
# Regression test for IMPALA-14154.
select `data`
from functional_parquet.iceberg_v2_delete_positional
where `data` not in (select min(`data`) from functional_parquet.iceberg_v2_delete_positional);
---- RESULTS
'c'
---- TYPES
STRING
====
---- QUERY
# Regression test for IMPALA-IMPALA-13888.
select a.id
from alltypestiny a LEFT ANTI JOIN iceberg_v2_positional_update_all_rows b
ON a.id = b.i;
---- RESULTS
4
5
0
6
7
---- TYPES
INT
====
---- QUERY
# Regression test for IMPALA-IMPALA-13888 with multiple joins.
select a.id from alltypestiny a
LEFT ANTI JOIN iceberg_v2_positional_update_all_rows b
ON a.id = b.i
LEFT ANTI JOIN iceberg_v2_positional_update_all_rows c
ON a.id = c.i
LEFT ANTI JOIN iceberg_v2_positional_update_all_rows d
ON a.id = d.i;
---- RESULTS
4
5
0
6
7
---- TYPES
INT
====