testdata/workloads/functional-query/queries/QueryTest/iceberg-v3-update.test

====
---- QUERY
# Basic UPDATE on unpartitioned V3 table.
CREATE TABLE update_v3_basic (i int, s string)
STORED BY ICEBERG
TBLPROPERTIES('format-version'='3');

INSERT INTO update_v3_basic VALUES (1, 'one'), (2, 'two'), (3, 'three');
UPDATE update_v3_basic SET s = 'updated' WHERE i = 2;
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
SELECT * FROM update_v3_basic ORDER BY i;
---- RESULTS
1,'one'
2,'updated'
3,'three'
---- TYPES
INT,STRING
====
---- QUERY
# _row_id is preserved, _last_updated_sequence_number is updated.
SELECT _row_id, _last_updated_sequence_number, '|', * FROM update_v3_basic ORDER BY i;
---- RESULTS
0,1,'|',1,'one'
1,2,'|',2,'updated'
2,1,'|',3,'three'
---- TYPES
BIGINT,BIGINT,STRING,INT,STRING
====
---- QUERY
# The delete side of UPDATE creates a DV (Puffin) file.
SELECT content, file_format, record_count, referenced_data_file IS NOT NULL, content_offset IS NOT NULL
FROM $DATABASE.update_v3_basic.`files`
ORDER BY content, file_format;
---- RESULTS
0,'PARQUET',1,false,false
0,'PARQUET',3,false,false
1,'PUFFIN',1,true,true
---- TYPES
INT,STRING,BIGINT,BOOLEAN,BOOLEAN
====
---- QUERY
# UPDATE multiple rows at once.
CREATE TABLE update_v3_multi (i int, s string)
STORED BY ICEBERG
TBLPROPERTIES('format-version'='3');

INSERT INTO update_v3_multi VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e');
UPDATE update_v3_multi SET s = upper(s) WHERE i >= 3;
---- RUNTIME_PROFILE
NumModifiedRows: 3
NumDeletedRows: 3
====
---- QUERY
SELECT * FROM update_v3_multi ORDER BY i;
---- RESULTS
1,'a'
2,'b'
3,'C'
4,'D'
5,'E'
---- TYPES
INT,STRING
====
---- QUERY
# _row_id is preserved, _last_updated_sequence_number is updated.
SELECT _row_id, _last_updated_sequence_number, '|', * FROM update_v3_multi ORDER BY i;
---- RESULTS
0,1,'|',1,'a'
1,1,'|',2,'b'
2,2,'|',3,'C'
3,2,'|',4,'D'
4,2,'|',5,'E'
---- TYPES
BIGINT,BIGINT,STRING,INT,STRING
====
---- QUERY
# No WHERE clause, but only update a subset of rows.
UPDATE update_v3_multi SET s = lower(s);
---- RUNTIME_PROFILE
NumModifiedRows: 3
NumDeletedRows: 3
====
---- QUERY
SELECT * FROM update_v3_multi ORDER BY i;
---- RESULTS
1,'a'
2,'b'
3,'c'
4,'d'
5,'e'
---- TYPES
INT,STRING
====
---- QUERY
SELECT content, file_format, record_count, referenced_data_file IS NOT NULL, content_offset IS NOT NULL
FROM $DATABASE.update_v3_multi.`files`
ORDER BY content, file_format, record_count;
---- RESULTS
0,'PARQUET',3,false,false
0,'PARQUET',3,false,false
0,'PARQUET',5,false,false
1,'PUFFIN',3,true,true
1,'PUFFIN',3,true,true
---- TYPES
INT,STRING,BIGINT,BOOLEAN,BOOLEAN
====
---- QUERY
UPDATE update_v3_multi SET s = 'x';
---- RUNTIME_PROFILE
NumModifiedRows: 5
NumDeletedRows: 5
====
---- QUERY
SELECT * FROM update_v3_multi ORDER BY i;
---- RESULTS
1,'x'
2,'x'
3,'x'
4,'x'
5,'x'
---- TYPES
INT,STRING
====
---- QUERY
# UPDATE on partitioned V3 table.
CREATE TABLE update_v3_part (i int, s string)
PARTITIONED BY SPEC (TRUNCATE(1, s))
STORED BY ICEBERG
TBLPROPERTIES('format-version'='3');

INSERT INTO update_v3_part VALUES (1, 'aa'), (2, 'ab'), (3, 'ba');
UPDATE update_v3_part SET i = 10 WHERE s = 'ab';
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
SELECT * FROM update_v3_part ORDER BY i;
---- RESULTS
1,'aa'
3,'ba'
10,'ab'
---- TYPES
INT,STRING
====
---- QUERY
# UPDATE after DELETE on the same V3 table exercises DV merging on the same data file.
CREATE TABLE update_v3_after_delete (i int, s string)
STORED BY ICEBERG
TBLPROPERTIES('format-version'='3');

INSERT INTO update_v3_after_delete VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
DELETE FROM update_v3_after_delete WHERE i = 2;
UPDATE update_v3_after_delete SET s = 'updated' WHERE i = 3;
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
SELECT * FROM update_v3_after_delete ORDER BY i;
---- RESULTS
1,'a'
3,'updated'
4,'d'
---- TYPES
INT,STRING
====
---- QUERY
# Verify row lineage after DELETE + UPDATE.
SELECT _row_id, _last_updated_sequence_number, '|', * FROM update_v3_after_delete ORDER BY i;
---- RESULTS
0,1,'|',1,'a'
2,3,'|',3,'updated'
3,1,'|',4,'d'
---- TYPES
BIGINT,BIGINT,STRING,INT,STRING
====
---- QUERY
SELECT content, file_format, record_count, referenced_data_file IS NOT NULL, content_offset IS NOT NULL
FROM $DATABASE.update_v3_after_delete.`files`
ORDER BY content, file_format, record_count;
---- RESULTS
0,'PARQUET',1,false,false
0,'PARQUET',4,false,false
1,'PUFFIN',2,true,true
---- TYPES
INT,STRING,BIGINT,BOOLEAN,BOOLEAN
====
---- QUERY
# UPDATE partition column on a V3 table moves the row to a different partition.
CREATE TABLE update_v3_part_col (i int, s string)
PARTITIONED BY SPEC (TRUNCATE(1, s))
STORED BY ICEBERG
TBLPROPERTIES('format-version'='3');

INSERT INTO update_v3_part_col VALUES (1, 'aa'), (2, 'ab'), (3, 'ba');
UPDATE update_v3_part_col SET s = 'bc' WHERE i = 1;
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
SELECT * FROM update_v3_part_col ORDER BY i;
---- RESULTS
1,'bc'
2,'ab'
3,'ba'
---- TYPES
INT,STRING
====
---- QUERY
# V2 table with position deletes upgraded to V3: UPDATE must be blocked.
CREATE TABLE update_v3_pos_del (i int, s string)
STORED BY ICEBERG
TBLPROPERTIES ('format-version'='2');

INSERT INTO update_v3_pos_del VALUES (1, 'a'), (2, 'b'), (3, 'c');
DELETE FROM update_v3_pos_del WHERE i = 2;
ALTER TABLE update_v3_pos_del SET TBLPROPERTIES ('format-version'='3');
UPDATE update_v3_pos_del SET s = 'updated' WHERE i = 1;
---- CATCH
UPDATE is not allowed on Iceberg format version 3 table
====
---- QUERY
# After OPTIMIZE removes position deletes, UPDATE should work on the upgraded table.
OPTIMIZE TABLE update_v3_pos_del;
UPDATE update_v3_pos_del SET s = 'updated' WHERE i = 1;
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
SELECT * FROM update_v3_pos_del ORDER BY i;
---- RESULTS
1,'updated'
3,'c'
---- TYPES
INT,STRING
====
---- QUERY
create table single_col (i int)
stored by iceberg
tblproperties ('format-version'='3');
update single_col set i = 4;
---- DML_RESULTS: single_col
---- TYPES
INT
---- RUNTIME_PROFILE
NumModifiedRows: 0
NumDeletedRows: 0
====
---- QUERY
insert into single_col values (1), (2), (3);
update single_col set i = cast(i + 1 as int);
---- DML_RESULTS: single_col
2
3
4
---- TYPES
INT
---- RUNTIME_PROFILE
NumModifiedRows: 3
NumDeletedRows: 3
====
---- QUERY
update single_col set i = 1 where i = 2
---- DML_RESULTS: single_col
1
3
4
---- TYPES
INT
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
create table ice_alltypes (bool_col boolean, int_col int, bigint_col bigint, float_col float,
  double_col double, dec_10_0_col decimal(10,0), dec_8_2_col decimal(8,2), date_col date,
  timestamp_col timestamp, string_col string, binary_col binary)
stored by iceberg
tblproperties ('format-version'='3');
insert into ice_alltypes values (false, 0, 0, 0, 0, 0, 0, '2000-01-01', '2000-01-01 00:00:00',
 'zero', cast('zerob' as binary));
---- DML_RESULTS: ice_alltypes
false,0,0,0,0,0,0.00,2000-01-01,2000-01-01 00:00:00,'zero','zerob'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 0
====
---- QUERY
update ice_alltypes set bool_col = true;
---- DML_RESULTS: ice_alltypes
true,0,0,0,0,0,0.00,2000-01-01,2000-01-01 00:00:00,'zero','zerob'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
update ice_alltypes set int_col = 1;
---- DML_RESULTS: ice_alltypes
true,1,0,0,0,0,0.00,2000-01-01,2000-01-01 00:00:00,'zero','zerob'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
update ice_alltypes set bigint_col = 1;
---- DML_RESULTS: ice_alltypes
true,1,1,0,0,0,0.00,2000-01-01,2000-01-01 00:00:00,'zero','zerob'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
update ice_alltypes set float_col = 1;
---- DML_RESULTS: ice_alltypes
true,1,1,1,0,0,0.00,2000-01-01,2000-01-01 00:00:00,'zero','zerob'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
update ice_alltypes set double_col = 1;
---- DML_RESULTS: ice_alltypes
true,1,1,1,1,0,0.00,2000-01-01,2000-01-01 00:00:00,'zero','zerob'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
update ice_alltypes set dec_10_0_col = 1;
---- DML_RESULTS: ice_alltypes
true,1,1,1,1,1,0.00,2000-01-01,2000-01-01 00:00:00,'zero','zerob'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
update ice_alltypes set dec_8_2_col = 1;
---- DML_RESULTS: ice_alltypes
true,1,1,1,1,1,1.00,2000-01-01,2000-01-01 00:00:00,'zero','zerob'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
update ice_alltypes set date_col = '2001-01-01';
---- DML_RESULTS: ice_alltypes
true,1,1,1,1,1,1.00,2001-01-01,2000-01-01 00:00:00,'zero','zerob'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
update ice_alltypes set timestamp_col = '2001-01-01 01:01:01';
---- DML_RESULTS: ice_alltypes
true,1,1,1,1,1,1.00,2001-01-01,2001-01-01 01:01:01,'zero','zerob'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
update ice_alltypes set string_col = 'one';
---- DML_RESULTS: ice_alltypes
true,1,1,1,1,1,1.00,2001-01-01,2001-01-01 01:01:01,'one','zerob'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
update ice_alltypes set binary_col = cast('oneb' as binary);
---- DML_RESULTS: ice_alltypes
true,1,1,1,1,1,1.00,2001-01-01,2001-01-01 01:01:01,'one','oneb'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
update ice_alltypes set bigint_col = 33, int_col = 30, string_col = 'three';
---- DML_RESULTS: ice_alltypes
true,30,33,1,1,1,1.00,2001-01-01,2001-01-01 01:01:01,'three','oneb'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
# The row already has the desired values, no need to update. (IMPALA-12588)
update ice_alltypes set bigint_col = 33, int_col = 30, string_col = 'three';
---- DML_RESULTS: ice_alltypes
true,30,33,1,1,1,1.00,2001-01-01,2001-01-01 01:01:01,'three','oneb'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 0
NumDeletedRows: 0
====
---- QUERY
# Only one field changes.
update ice_alltypes set bigint_col = 33, int_col = 3, string_col = 'three';
---- DML_RESULTS: ice_alltypes
true,3,33,1,1,1,1.00,2001-01-01,2001-01-01 01:01:01,'three','oneb'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
update ice_alltypes set dec_10_0_col = 23, dec_8_2_col = cast(123.123 as decimal(8, 2));
---- DML_RESULTS: ice_alltypes
true,3,33,1,1,23,123.12,2001-01-01,2001-01-01 01:01:01,'three','oneb'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
# UPDATE table based on a reference table.
insert into ice_alltypes values (false, 0, 0, 0, 0, 0, 0, '2000-01-01', '2000-01-01 00:00:00',
 'zero', cast('zerob' as binary));
create table ref_table (i int, bi bigint, s string, d date);
insert into ref_table values (0, 111, 'IMPALA', '2023-11-07'), (3, 222, 'ICEBERG', '2023-11-08');
update ice_alltypes set bigint_col=bi, string_col=s, date_col=d from ice_alltypes, ref_table where int_col = i;
---- DML_RESULTS: ice_alltypes
false,0,111,0,0,0,0.00,2023-11-07,2000-01-01 00:00:00,'IMPALA','zerob'
true,3,222,1,1,23,123.12,2023-11-08,2001-01-01 01:01:01,'ICEBERG','oneb'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 2
NumDeletedRows: 2
====
---- QUERY
# If the JOIN in UPDATE has multiple matches Impala should raise an error.
# Changing bigint_col to actually have rows to update.
insert into ref_table values (0, 1111, 'IIMMPPAALLAA', '2023-12-01');
update ice_alltypes set bigint_col=bi+1, string_col=s, date_col=d from ice_alltypes, ref_table where int_col = i;
---- CATCH
Duplicated row in DELETE sink.
====
---- QUERY
# Check that NULL values and expressions evaluating to NULL in assignments are handled correctly.
update ice_alltypes set dec_10_0_col = NULL, dec_8_2_col = cast(123.123 + NULL as decimal(8, 2));
---- DML_RESULTS: ice_alltypes
false,0,111,0,0,NULL,NULL,2023-11-07,2000-01-01 00:00:00,'IMPALA','zerob'
true,3,222,1,1,NULL,NULL,2023-11-08,2001-01-01 01:01:01,'ICEBERG','oneb'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 2
NumDeletedRows: 2
====
---- QUERY
# Check that NULL assignments and WHERE condition filter rows correctly.
update ice_alltypes set dec_10_0_col = cast(123.123 as decimal(10, 0)), dec_8_2_col = NULL where int_col < 2;
---- DML_RESULTS: ice_alltypes
false,0,111,0,0,123,NULL,2023-11-07,2000-01-01 00:00:00,'IMPALA','zerob'
true,3,222,1,1,NULL,NULL,2023-11-08,2001-01-01 01:01:01,'ICEBERG','oneb'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
# Swap two columns
update ice_alltypes set dec_10_0_col = cast(dec_8_2_col as decimal(10, 0)), dec_8_2_col = cast(dec_10_0_col as decimal(8, 2));
---- DML_RESULTS: ice_alltypes
false,0,111,0,0,NULL,123.00,2023-11-07,2000-01-01 00:00:00,'IMPALA','zerob'
true,3,222,1,1,NULL,NULL,2023-11-08,2001-01-01 01:01:01,'ICEBERG','oneb'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
# Check col_a = col_b + col_c
update ice_alltypes set dec_10_0_col = cast(bigint_col + dec_8_2_col as decimal(10, 0));
---- DML_RESULTS: ice_alltypes
false,0,111,0,0,234,123.00,2023-11-07,2000-01-01 00:00:00,'IMPALA','zerob'
true,3,222,1,1,NULL,NULL,2023-11-08,2001-01-01 01:01:01,'ICEBERG','oneb'
---- TYPES
BOOLEAN,INT,BIGINT,FLOAT,DOUBLE,DECIMAL,DECIMAL,DATE,TIMESTAMP,STRING,BINARY
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
create table ice_id_partitioned (i int, p int, s string)
partitioned by spec(p)
stored by iceberg
tblproperties('format-version'='3');
insert into ice_id_partitioned values
(1, 0, 'impala'), (2, 0, 'iceberg'), (3, 0, 'hive'), (4, 1, 'spark'),
(5, 2, 'kudu');
update ice_id_partitioned set s='Impala' where i = 1;
update ice_id_partitioned set s='Kudu' where i = 5;
---- DML_RESULTS: ice_id_partitioned
1,0,'Impala'
2,0,'iceberg'
3,0,'hive'
4,1,'spark'
5,2,'Kudu'
---- TYPES
INT,INT,STRING
====
---- QUERY
# Update partitioned Iceberg table based on VIEW
create table ref_table_1 (ri int, rs string);
insert into ref_table_1 values (1, 'Apache Impala');
create table ref_table_2 (ri int, rs string);
insert into ref_table_2 values (4, 'Apache Spark');
create view ref_view as select * from ref_table_1 union select * from ref_table_2;
update ice_id_partitioned SET s = rs
FROM ice_id_partitioned JOIN ref_view ON (i = ri);
---- DML_RESULTS: ice_id_partitioned
1,0,'Apache Impala'
2,0,'iceberg'
3,0,'hive'
4,1,'Apache Spark'
5,2,'Kudu'
---- TYPES
INT,INT,STRING
====
---- QUERY
# Update partitioned Iceberg table based on VIEW
UPDATE ice_id_partitioned SET s = upper(rs)
FROM ice_id_partitioned JOIN ref_view ON (i = ri)
WHERE p < 1 AND rs = (select min(rs) from ref_view);
---- DML_RESULTS: ice_id_partitioned
1,0,'APACHE IMPALA'
2,0,'iceberg'
3,0,'hive'
4,1,'Apache Spark'
5,2,'Kudu'
---- TYPES
INT,INT,STRING
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
# Test if changing SKIP_UNNEEDED_UPDATES_COL_LIMIT query option takes effect: the 4th row
# is updated even though the table content remained the same.
SET SKIP_UNNEEDED_UPDATES_COL_LIMIT = 1;
UPDATE ice_id_partitioned SET i=ri, p=1
FROM ice_id_partitioned JOIN ref_table_2 ON (i = ri);
---- DML_RESULTS: ice_id_partitioned
1,0,'APACHE IMPALA'
2,0,'iceberg'
3,0,'hive'
4,1,'Apache Spark'
5,2,'Kudu'
---- TYPES
INT,INT,STRING
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
# Setting SKIP_UNNEEDED_UPDATES_COL_LIMIT back to default. No rows should be updated.
SET SKIP_UNNEEDED_UPDATES_COL_LIMIT = 10;
UPDATE ice_id_partitioned SET i=ri, p=1
FROM ice_id_partitioned JOIN ref_table_2 ON (i = ri);
---- DML_RESULTS: ice_id_partitioned
1,0,'APACHE IMPALA'
2,0,'iceberg'
3,0,'hive'
4,1,'Apache Spark'
5,2,'Kudu'
---- TYPES
INT,INT,STRING
---- RUNTIME_PROFILE
aggregation(SUM, RowsInserted): 0
====
---- QUERY
# Update partitioned Iceberg table with inline view in FROM clause
UPDATE ice_id_partitioned SET i=cast(a.max_val*(p+1) as int)
FROM ice_id_partitioned JOIN ref_view ON (i = ri), (SELECT max(i) max_val FROM ice_id_partitioned) a
WHERE p = 1;
---- DML_RESULTS: ice_id_partitioned
1,0,'APACHE IMPALA'
2,0,'iceberg'
3,0,'hive'
10,1,'Apache Spark'
5,2,'Kudu'
---- TYPES
INT,INT,STRING
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumDeletedRows: 1
====
---- QUERY
# Negative test for UPDATE part 3:
# updating partition column AND right side is non-constant value AND we have a FROM clause with multiple table refs.
# For such operations, if there are multiple matches in the JOIN, the duplicated records can
# get shuffled independently to different sink operators, therefore they cannot check the
# presence of duplicates. For statements like this we need to raise an error during analysis.
UPDATE ice_id_partitioned set p = cast(ref_table.bi as int)
FROM ice_id_partitioned, ref_table
WHERE ice_id_partitioned.i = ref_table.i;
---- CATCH
AnalysisException: Cannot UPDATE partitioning column 'p' via UPDATE FROM statement with multiple table refs, and when right-hand side 'CAST(ref_table.bi AS INT)' is non-constant.
====
---- QUERY
create table ice_bucket_transform(i int, str string, bi bigint, ts timestamp)
partitioned by spec(bucket(91, str), bucket(199, bi), bucket(403, ts))
stored by iceberg
tblproperties('format-version'='3');
insert into ice_bucket_transform values (1, 'a fairly long string value', 1000, '1999-09-19 12:00:01'),
  (2, 'bbb', 2030, '2001-01-01 00:00:00'), (3, 'cccccccccccccccccccccccccccccccccccccccc', -123, '2023-11-24 17:44:30');
update ice_bucket_transform set i = cast(i * 2 as int);
---- DML_RESULTS: ice_bucket_transform
2,'a fairly long string value',1000,1999-09-19 12:00:01
4,'bbb',2030,2001-01-01 00:00:00
6,'cccccccccccccccccccccccccccccccccccccccc',-123,2023-11-24 17:44:30
---- TYPES
INT,STRING,BIGINT,TIMESTAMP
====
---- QUERY
create table ice_time_transforms_timestamp(id decimal(8, 4), ts1 timestamp, ts2 timestamp, ts3 timestamp, ts4 timestamp)
partitioned by spec(year(ts1), month(ts2), day(ts3), hour(ts4))
stored by iceberg
tblproperties('format-version'='3');
insert into ice_time_transforms_timestamp values
  (0.75, '2001-01-01 01:01:01', '2001-01-01 01:01:01', '2001-01-01 01:01:01', '2001-01-01 01:01:01'),
  (1.2345, '2023-11-24 18:02:00', '2023-11-24 18:02:00', '2023-11-24 18:02:00', '2023-11-24 18:02:00'),
  (999.9999, '2199-12-31 23:59:59', '2199-12-31 23:59:59', '2199-12-31 23:59:59', '2199-12-31 23:59:59');
update ice_time_transforms_timestamp set id = cast(id * 2 as decimal(8, 4));
---- DML_RESULTS: ice_time_transforms_timestamp
1.5000,2001-01-01 01:01:01,2001-01-01 01:01:01,2001-01-01 01:01:01,2001-01-01 01:01:01
2.4690,2023-11-24 18:02:00,2023-11-24 18:02:00,2023-11-24 18:02:00,2023-11-24 18:02:00
1999.9998,2199-12-31 23:59:59,2199-12-31 23:59:59,2199-12-31 23:59:59,2199-12-31 23:59:59
---- TYPES
DECIMAL,TIMESTAMP,TIMESTAMP,TIMESTAMP,TIMESTAMP
====
---- QUERY
create table ice_time_transforms_date(id decimal(8, 4), ts1 DATE, ts2 DATE, ts3 DATE)
partitioned by spec(year(ts1), month(ts2), day(ts3))
stored by iceberg
tblproperties('format-version'='3');
insert into ice_time_transforms_date values
  (0.75, '2001-01-01 01:01:01', '2001-01-01 01:01:01', '2001-01-01 01:01:01'),
  (1.2345, '2023-11-24 18:02:00', '2023-11-24 18:02:00', '2023-11-24 18:02:00'),
  (999.9999, '2199-12-31 23:59:59', '2199-12-31 23:59:59', '2199-12-31 23:59:59');
update ice_time_transforms_date set id = cast(id * 2 as decimal(8, 4));
---- DML_RESULTS: ice_time_transforms_date
1.5000,2001-01-01,2001-01-01,2001-01-01
2.4690,2023-11-24,2023-11-24,2023-11-24
1999.9998,2199-12-31,2199-12-31,2199-12-31
---- TYPES
DECIMAL,DATE,DATE,DATE
====
---- QUERY
create table ice_part_transforms (i int, ts timestamp, s string, p bigint)
partitioned by spec(day(ts), truncate(1, s), truncate(1000, p))
stored by iceberg
tblproperties('format-version'='3');
insert into ice_part_transforms values (1, '2023-11-13 18:07:05', 'blue', 1234),
 (2, '2023-11-13 18:07:23', 'gray', 2500), (3, '2023-11-14 19:07:05', 'green', 1700),
 (4, '2023-11-01 00:11:11', 'black', 722);
====
---- QUERY
update ice_part_transforms set i = cast(i*2 as int) where i % 2 = 0;
---- DML_RESULTS: ice_part_transforms
1,2023-11-13 18:07:05,'blue',1234
4,2023-11-13 18:07:23,'gray',2500
3,2023-11-14 19:07:05,'green',1700
8,2023-11-01 00:11:11,'black',722
---- TYPES
INT,TIMESTAMP,STRING,BIGINT
====
---- QUERY
# Regression test for IMPALA-13770: using a native UDF in the target value of an UPDATE
# Note: even with the incorrect behaviour before IMPALA-13770 the test may pass and the
# values can also be updated successfully. This is because the crash happens after the
# query is closed, when releasing resources. This test is a valid regression test because
# even if it passes, there will be a crash: some later queries are likely to fail and
# there will be a minidump in the build artifacts, so the build will be marked FAILED.
# Also testing that rows which already have the desired value should not be skipped
# because there is a UDF in the SET list.
create function if not exists identity(int) returns int location 'UDF_LOCATION' symbol='Identity';
create table update_with_udf(int_col INT)
stored by iceberg
tblproperties ('format-version'='3');
insert into update_with_udf values (1), (2);
====
---- QUERY
update update_with_udf set int_col = identity(int_col);
---- DML_RESULTS: update_with_udf
1
2
---- TYPES
INT
---- RUNTIME_PROFILE
NumModifiedRows: 2
NumDeletedRows: 2
====
---- QUERY
CREATE TABLE update_all (s STRING)
STORED BY ICEBERG
TBLPROPERTIES ('format-version'='3');

INSERT INTO update_all VALUES ('a'), ('b');
DELETE FROM update_all WHERE s='a';
INSERT INTO update_all VALUES ('c'), ('d'), ('e');
---- DML_RESULTS: update_all
'b'
'c'
'd'
'e'
---- TYPES
STRING
====
---- QUERY
UPDATE update_all set s = 'updated';
---- DML_RESULTS: update_all
'updated'
'updated'
'updated'
'updated'
---- TYPES
STRING
---- RUNTIME_PROFILE
# Unpartitioned V3 table must shuffle on INPUT__FILE__NAME to guarantee one
# Deletion Vector per data file.
row_regex: .*EXCHANGE \[HASH\(.*INPUT__FILE__NAME.*\)\].*
====