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

====
---- QUERY
create table tdata
  (id int primary key, valf float null, vali bigint null, valv string null,
   valb boolean null, valt tinyint null, vals smallint null, vald double null,
   valdec4 decimal(9,9) null, valdec8 decimal(18,2) null,
   valdec16 decimal(38, 0) null)
  PARTITION BY RANGE (PARTITION VALUES < 100, PARTITION 100 <= VALUES < 1000,
  PARTITION 1000 <= VALUES <= 10000) STORED AS KUDU
---- RESULTS
'Table has been created.'
====
---- QUERY
insert into table tdata values
(1, 1.0, 1, 'one', true, 1, 1, 1, 0.000000001, 1.11, 1),
(2, -2, 20, 'two', false, 0, 1, NULL, 0.000000002, 2.22, 2),
(3, 0, NULL, 'three', false, 10, 20, 30, 0.000000040, 50.00, 60),
(4, 5, 6, 'four', true, 7, 8, 9, 0.000000010, 11.11, 12),
(5, 0, 10, 'five', NULL, 15, 20, 25, 0.000000030, 35.35, 40),
(6, 9, 12, 'six', true, -1, -2, cast('inf' as double), -0.000000001, -1.11, -1),
(7, NULL, 7, 'seven', false, 77, 777, NULL, NULL, NULL, NULL),
(8, 0, 80, NULL, true, 10, 11, 12, 0.000000013, 0.14, 15),
(9, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(127, 1, 2, '127', false, 3, 4, 5, 0.000000000, 0.00, 0)
---- RESULTS
: 10
====
---- QUERY
# single row, predicate on key
delete from tdata where id = 1
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
2,-2,20,'two',false,0,1,NULL,0.000000002,2.22,2
3,0,NULL,'three',false,10,20,30,0.000000040,50.00,60
4,5,6,'four',true,7,8,9,0.000000010,11.11,12
5,0,10,'five',NULL,15,20,25,0.000000030,35.35,40
6,9,12,'six',true,-1,-2,Infinity,-0.000000001,-1.11,-1
7,NULL,7,'seven',false,77,777,NULL,NULL,NULL,NULL
8,0,80,'NULL',true,10,11,12,0.000000013,0.14,15
9,NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL,NULL,NULL
127,1,2,'127',false,3,4,5,0.000000000,0.00,0
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
# predicate on key, NULL
delete from tdata where id is NULL
---- RUNTIME_PROFILE
NumModifiedRows: 0
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
2,-2,20,'two',false,0,1,NULL,0.000000002,2.22,2
3,0,NULL,'three',false,10,20,30,0.000000040,50.00,60
4,5,6,'four',true,7,8,9,0.000000010,11.11,12
5,0,10,'five',NULL,15,20,25,0.000000030,35.35,40
6,9,12,'six',true,-1,-2,Infinity,-0.000000001,-1.11,-1
7,NULL,7,'seven',false,77,777,NULL,NULL,NULL,NULL
8,0,80,'NULL',true,10,11,12,0.000000013,0.14,15
9,NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL,NULL,NULL
127,1,2,'127',false,3,4,5,0.000000000,0.00,0
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
# predicate on key, nothing is deleted
delete from tdata where id = 10
---- RUNTIME_PROFILE
NumModifiedRows: 0
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
2,-2,20,'two',false,0,1,NULL,0.000000002,2.22,2
3,0,NULL,'three',false,10,20,30,0.000000040,50.00,60
4,5,6,'four',true,7,8,9,0.000000010,11.11,12
5,0,10,'five',NULL,15,20,25,0.000000030,35.35,40
6,9,12,'six',true,-1,-2,Infinity,-0.000000001,-1.11,-1
7,NULL,7,'seven',false,77,777,NULL,NULL,NULL,NULL
8,0,80,'NULL',true,10,11,12,0.000000013,0.14,15
9,NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL,NULL,NULL
127,1,2,'127',false,3,4,5,0.000000000,0.00,0
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
# predicate on key, boundary value
delete from tdata where id = max_tinyint()
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
2,-2,20,'two',false,0,1,NULL,0.000000002,2.22,2
3,0,NULL,'three',false,10,20,30,0.000000040,50.00,60
4,5,6,'four',true,7,8,9,0.000000010,11.11,12
5,0,10,'five',NULL,15,20,25,0.000000030,35.35,40
6,9,12,'six',true,-1,-2,Infinity,-0.000000001,-1.11,-1
7,NULL,7,'seven',false,77,777,NULL,NULL,NULL,NULL
8,0,80,'NULL',true,10,11,12,0.000000013,0.14,15
9,NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL,NULL,NULL
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
# compound predicate on key
delete from tdata where id > 6 and id < 8
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
2,-2,20,'two',false,0,1,NULL,0.000000002,2.22,2
3,0,NULL,'three',false,10,20,30,0.000000040,50.00,60
4,5,6,'four',true,7,8,9,0.000000010,11.11,12
5,0,10,'five',NULL,15,20,25,0.000000030,35.35,40
6,9,12,'six',true,-1,-2,Infinity,-0.000000001,-1.11,-1
8,0,80,'NULL',true,10,11,12,0.000000013,0.14,15
9,NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL,NULL,NULL
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
# predicate on key, multiple rows
delete from tdata where id % 4 = 0
---- RUNTIME_PROFILE
NumModifiedRows: 2
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
2,-2,20,'two',false,0,1,NULL,0.000000002,2.22,2
3,0,NULL,'three',false,10,20,30,0.000000040,50.00,60
5,0,10,'five',NULL,15,20,25,0.000000030,35.35,40
6,9,12,'six',true,-1,-2,Infinity,-0.000000001,-1.11,-1
9,NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL,NULL,NULL
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
# insert new values, including some that were previously deleted
insert into table tdata values
(10, 20, 30, 'ten', true, 40, 50, 60, 0.000000070, 80.80, 90),
(1, 1.0, 1, 'one', true, 1, 1, 1, 0.000000001, 1.11, 1),
(11, -11, 11, 'eleven', false, 1, 11, 111, 0.000000011, 11.00, 11),
(8, 0, 80, NULL, true, 10, 11, 12, 0.000000013, 0.14, 15)
====
---- QUERY
# single row, predicate on non-key
delete from tdata where valv = 'five'
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
2,-2,20,'two',false,0,1,NULL,0.000000002,2.22,2
3,0,NULL,'three',false,10,20,30,0.000000040,50.00,60
6,9,12,'six',true,-1,-2,Infinity,-0.000000001,-1.11,-1
9,NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL,NULL,NULL
10,20,30,'ten',true,40,50,60,0.000000070,80.80,90
1,1.0,1,'one',true,1,1,1,0.000000001,1.11,1
11,-11,11,'eleven',false,1,11,111,0.000000011,11.00,11
8,0,80,'NULL',true,10,11,12,0.000000013,0.14,15
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
# predicate on non-key, NULL
delete from tdata where valb is NULL
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
2,-2,20,'two',false,0,1,NULL,0.000000002,2.22,2
3,0,NULL,'three',false,10,20,30,0.000000040,50.00,60
6,9,12,'six',true,-1,-2,Infinity,-0.000000001,-1.11,-1
10,20,30,'ten',true,40,50,60,0.000000070,80.80,90
1,1.0,1,'one',true,1,1,1,0.000000001,1.11,1
11,-11,11,'eleven',false,1,11,111,0.000000011,11.00,11
8,0,80,'NULL',true,10,11,12,0.000000013,0.14,15
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
# predicate on non-key, nothing is deleted
delete from tdata where vals = -100
---- RUNTIME_PROFILE
NumModifiedRows: 0
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
2,-2,20,'two',false,0,1,NULL,0.000000002,2.22,2
3,0,NULL,'three',false,10,20,30,0.000000040,50.00,60
6,9,12,'six',true,-1,-2,Infinity,-0.000000001,-1.11,-1
10,20,30,'ten',true,40,50,60,0.000000070,80.80,90
1,1.0,1,'one',true,1,1,1,0.000000001,1.11,1
11,-11,11,'eleven',false,1,11,111,0.000000011,11.00,11
8,0,80,'NULL',true,10,11,12,0.000000013,0.14,15
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
# predicate on non-key, compound predicate
delete from tdata where valf = 0 and vali = 80
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
2,-2,20,'two',false,0,1,NULL,0.000000002,2.22,2
3,0,NULL,'three',false,10,20,30,0.000000040,50.00,60
6,9,12,'six',true,-1,-2,Infinity,-0.000000001,-1.11,-1
10,20,30,'ten',true,40,50,60,0.000000070,80.80,90
1,1.0,1,'one',true,1,1,1,0.000000001,1.11,1
11,-11,11,'eleven',false,1,11,111,0.000000011,11.00,11
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
# predicate on non-key, multiple rows
delete from tdata where vals % 10 = 0
---- RUNTIME_PROFILE
NumModifiedRows: 2
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
2,-2,20,'two',false,0,1,NULL,0.000000002,2.22,2
6,9,12,'six',true,-1,-2,Infinity,-0.000000001,-1.11,-1
1,1.0,1,'one',true,1,1,1,0.000000001,1.11,1
11,-11,11,'eleven',false,1,11,111,0.000000011,11.00,11
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
# 'from' syntax - the join results in four deletes, 3 of which fail
delete a from tdata a, tdata b where a.id = 11
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumRowErrors: 3
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
2,-2,20,'two',false,0,1,NULL,0.000000002,2.22,2
6,9,12,'six',true,-1,-2,Infinity,-0.000000001,-1.11,-1
1,1.0,1,'one',true,1,1,1,0.000000001,1.11,1
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
# Try to delete a row with a primary key value that is not covered by the existing range
# partitions. This doesn't actually end up selecting any rows to modify.
delete from tdata where id = 10001
---- RUNTIME_PROFILE
NumModifiedRows: 0
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
2,-2,20,'two',false,0,1,NULL,0.000000002,2.22,2
6,9,12,'six',true,-1,-2,Infinity,-0.000000001,-1.11,-1
1,1.0,1,'one',true,1,1,1,0.000000001,1.11,1
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
# Add a couple more rows back
insert into table tdata values
(3, 0, NULL, 'three', false, 10, 20, 30, 0.000000040, 50.00, 60),
(4, 5, 6, 'four', true, 7, 8, 9, 0.000000010, 11.11, 12),
(5, 0, 10, 'five', NULL, 15, 20, 25, 0.000000030, 35.35, 40)
---- RESULTS
: 3
====
---- QUERY
# predicate on decimal, multiple rows
delete from tdata where valdec8 < 11.11
---- RUNTIME_PROFILE
NumModifiedRows: 3
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
3,0,NULL,'three',false,10,20,30,0.000000040,50.00,60
4,5,6,'four',true,7,8,9,0.000000010,11.11,12
5,0,10,'five',NULL,15,20,25,0.000000030,35.35,40
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
# predicate on decimal, single row
delete from tdata where valdec4 = 0.000000030
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
3,0,NULL,'three',false,10,20,30,0.000000040,50.00,60
4,5,6,'four',true,7,8,9,0.000000010,11.11,12
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
insert into tdata
select cast(id + 100 as int), float_col, bigint_col, string_col, bool_col, tinyint_col,
smallint_col, double_col, NULL, NULL, NULL
from functional_kudu.alltypes
---- RESULTS
: 7300
---- RUNTIME_PROFILE
NumModifiedRows: 7300
NumRowErrors: 0
====
---- QUERY
# Test a larger DELETE
delete from tdata where id > -1
---- RUNTIME_PROFILE
NumModifiedRows: 7302
NumRowErrors: 0
---- LABELS
ID, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16
---- DML_RESULTS: tdata
---- TYPES
INT,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL
====
---- QUERY
create table multiple_key_cols
  (string_col string, bigint_col bigint, tinyint_col tinyint,
   smallint_col smallint, bool_col boolean null, int_col int null,
   double_col double null, float_col float null,
   primary key (string_col, bigint_col, tinyint_col, smallint_col))
  PARTITION BY HASH (string_col) PARTITIONS 16 STORED AS KUDU
====
---- QUERY
insert into multiple_key_cols values
('a', 1, 2, 3, true, 4, 5, NULL),
('b', 1, 2, 3, false, 7, NULL, 9),
('c', 4, 5, 6, true, 0, -1, 0),
('d', 10, 20, 30, false, NULL, 40, 50)
---- RESULTS
: 4
====
---- QUERY
# multiple key cols, predicate on one of them
delete from multiple_key_cols where bigint_col = 1
---- RUNTIME_PROFILE
NumModifiedRows: 2
NumRowErrors: 0
---- LABELS
STRING_COL, BIGINT_COL, TINYINT_COL, SMALLINT_COL, BOOL_COL, INT_COL, DOUBLE_COL, FLOAT_COL
---- DML_RESULTS: multiple_key_cols
'c',4,5,6,true,0,-1,0
'd',10,20,30,false,NULL,40,50
---- TYPES
STRING,BIGINT,TINYINT,SMALLINT,BOOLEAN,INT,DOUBLE,FLOAT
====
---- QUERY
# multiple key cols, predicate on non-key col
delete from multiple_key_cols where bool_col = false
---- RUNTIME_PROFILE
NumModifiedRows: 1
NumRowErrors: 0
---- LABELS
STRING_COL, BIGINT_COL, TINYINT_COL, SMALLINT_COL, BOOL_COL, INT_COL, DOUBLE_COL, FLOAT_COL
---- DML_RESULTS: multiple_key_cols
'c',4,5,6,true,0,-1,0
---- TYPES
STRING,BIGINT,TINYINT,SMALLINT,BOOLEAN,INT,DOUBLE,FLOAT
====
---- QUERY
# IMPALA-3454: A delete that requires a rewrite may not get the Kudu column order correct
# if the Kudu columns are of different types.
create table impala_3454 (key_1 tinyint, key_2 bigint, PRIMARY KEY (key_1, key_2))
  PARTITION BY HASH PARTITIONS 3 STORED AS KUDU
---- RESULTS
'Table has been created.'
====
---- QUERY
insert into impala_3454 values
(1, 1),
(2, 2),
(3, 3)
---- RESULTS
: 3
====
---- QUERY
delete from impala_3454 where key_1 < (select max(key_2) from impala_3454)
---- RESULTS
---- RUNTIME_PROFILE
NumModifiedRows: 2
NumRowErrors: 0
====
---- QUERY
select * from impala_3454
---- RESULTS
3,3
---- TYPES
TINYINT,BIGINT
====