| ==== |
| ---- QUERY |
| create table tdata |
| (id int primary key, name string null, valf float null, vali bigint null, |
| valv string null, valb boolean null, valt tinyint null, vals smallint null, |
| vald double null, valdec decimal(9, 0) null) |
| PARTITION BY RANGE (PARTITION VALUES < 10, PARTITION 10 <= VALUES < 30, |
| PARTITION 30 <= VALUES) STORED AS KUDU |
| ---- RESULTS |
| 'Table has been created.' |
| ==== |
| ---- QUERY |
| insert into table tdata values |
| (40,'he',0,43,'e',false,35,36,1.2,37), |
| (1,'unknown',1,43,'aaaaaaaaaaaaaaaaaaaa',false,-1,-2,0,-3), |
| (2,'david',1,43,'b',false,0,0,0,0), |
| (3,'todd',1,43,'c',true,3,3,3,3) |
| ---- RESULTS |
| : 4 |
| ==== |
| ---- QUERY |
| # VALUES, single row, all cols, results in insert |
| upsert into table tdata values (4, 'a', 0, 1, 'b', false, 1, 2, 1.5, 4) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'he',0,43,'e',false,35,36,1.2,37 |
| 1,'unknown',1,43,'aaaaaaaaaaaaaaaaaaaa',false,-1,-2,0,-3 |
| 2,'david',1,43,'b',false,0,0,0,0 |
| 3,'todd',1,43,'c',true,3,3,3,3 |
| 4,'a',0,1,'b',false,1,2,1.5,4 |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # VALUES, single row, all cols, results in update |
| upsert into table tdata values (4, 'b', -1, 1, 'a', true, 2, 3, 2.5, 5) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'he',0,43,'e',false,35,36,1.2,37 |
| 1,'unknown',1,43,'aaaaaaaaaaaaaaaaaaaa',false,-1,-2,0,-3 |
| 2,'david',1,43,'b',false,0,0,0,0 |
| 3,'todd',1,43,'c',true,3,3,3,3 |
| 4,'b',-1,1,'a',true,2,3,2.5,5 |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # VALUES, single row, all cols, insert NULL all types |
| upsert into table tdata values (10, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'he',0,43,'e',false,35,36,1.2,37 |
| 1,'unknown',1,43,'aaaaaaaaaaaaaaaaaaaa',false,-1,-2,0,-3 |
| 2,'david',1,43,'b',false,0,0,0,0 |
| 3,'todd',1,43,'c',true,3,3,3,3 |
| 4,'b',-1,1,'a',true,2,3,2.5,5 |
| 10,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # VALUES, single row, all cols, update NULL all types |
| upsert into table tdata values (4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'he',0,43,'e',false,35,36,1.2,37 |
| 1,'unknown',1,43,'aaaaaaaaaaaaaaaaaaaa',false,-1,-2,0,-3 |
| 2,'david',1,43,'b',false,0,0,0,0 |
| 3,'todd',1,43,'c',true,3,3,3,3 |
| 4,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| 10,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # VALUES, single row, all cols, update from NULL all types |
| upsert into table tdata values (4, 'four', 5, 6, 'f', true, 7, 8, 7.5, 9) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'he',0,43,'e',false,35,36,1.2,37 |
| 1,'unknown',1,43,'aaaaaaaaaaaaaaaaaaaa',false,-1,-2,0,-3 |
| 2,'david',1,43,'b',false,0,0,0,0 |
| 3,'todd',1,43,'c',true,3,3,3,3 |
| 4,'four',5,6,'f',true,7,8,7.5,9 |
| 10,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # VALUES, single row, all cols, insert boundary values |
| upsert into table tdata values |
| (max_int(), '', cast('nan' as float), min_bigint(), '', true, max_tinyint(), |
| min_smallint(), cast('inf' as double), cast(999999999 as decimal(9, 0))) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'he',0,43,'e',false,35,36,1.2,37 |
| 1,'unknown',1,43,'aaaaaaaaaaaaaaaaaaaa',false,-1,-2,0,-3 |
| 2,'david',1,43,'b',false,0,0,0,0 |
| 3,'todd',1,43,'c',true,3,3,3,3 |
| 4,'four',5,6,'f',true,7,8,7.5,9 |
| 10,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| 2147483647,'',NaN,-9223372036854775808,'',true,127,-32768,Infinity,999999999 |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # VALUES, single row, all cols, update boundary values |
| upsert into table tdata values |
| (max_int(), '', cast('-inf' as float), max_bigint(), '', true, min_tinyint(), |
| max_smallint(), cast('nan' as double), cast(-999999999 as decimal(9, 0))) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'he',0,43,'e',false,35,36,1.2,37 |
| 1,'unknown',1,43,'aaaaaaaaaaaaaaaaaaaa',false,-1,-2,0,-3 |
| 2,'david',1,43,'b',false,0,0,0,0 |
| 3,'todd',1,43,'c',true,3,3,3,3 |
| 4,'four',5,6,'f',true,7,8,7.5,9 |
| 10,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| 2147483647,'',-Infinity,9223372036854775807,'',true,-128,32767,NaN,-999999999 |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # VALUES, single row, subset of cols, results in insert |
| upsert into table tdata (id, name, vali, valb, vald) values (5, 'five', -5, NULL, 0.5) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'he',0,43,'e',false,35,36,1.2,37 |
| 1,'unknown',1,43,'aaaaaaaaaaaaaaaaaaaa',false,-1,-2,0,-3 |
| 2,'david',1,43,'b',false,0,0,0,0 |
| 3,'todd',1,43,'c',true,3,3,3,3 |
| 4,'four',5,6,'f',true,7,8,7.5,9 |
| 10,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| 2147483647,'',-Infinity,9223372036854775807,'',true,-128,32767,NaN,-999999999 |
| 5,'five',NULL,-5,'NULL',NULL,NULL,NULL,0.5,NULL |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # VALUES, single row, subset of cols, results in update |
| upsert into table tdata (id, name, valf, valv, valb) values (5, NULL, 0, 'six', false) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'he',0,43,'e',false,35,36,1.2,37 |
| 1,'unknown',1,43,'aaaaaaaaaaaaaaaaaaaa',false,-1,-2,0,-3 |
| 2,'david',1,43,'b',false,0,0,0,0 |
| 3,'todd',1,43,'c',true,3,3,3,3 |
| 4,'four',5,6,'f',true,7,8,7.5,9 |
| 10,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| 2147483647,'',-Infinity,9223372036854775807,'',true,-128,32767,NaN,-999999999 |
| 5,'NULL',0,-5,'six',false,NULL,NULL,0.5,NULL |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # VALUES, multiple rows, all cols, no errors |
| upsert into table tdata values |
| (1, 'one', NULL, 44, 'a', true, -1, -2, 0, 0), |
| (6, '', -6, 40, 'b', NULL, 0, 0, 10, 11), |
| (7, 'seven', 0, min_bigint(), NULL, true, 7, 1, 2, 3), |
| (2, 'you', cast('inf' as float), 0, 't', false, NULL, min_smallint(), 0, 0) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 4 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'he',0,43,'e',false,35,36,1.2,37 |
| 1,'one',NULL,44,'a',true,-1,-2,0,0 |
| 2,'you',Infinity,0,'t',false,NULL,-32768,0,0 |
| 3,'todd',1,43,'c',true,3,3,3,3 |
| 4,'four',5,6,'f',true,7,8,7.5,9 |
| 10,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| 2147483647,'',-Infinity,9223372036854775807,'',true,-128,32767,NaN,-999999999 |
| 5,'NULL',0,-5,'six',false,NULL,NULL,0.5,NULL |
| 6,'',-6,40,'b',NULL,0,0,10,11 |
| 7,'seven',0,-9223372036854775808,'NULL',true,7,1,2,3 |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # VALUES, multiple rows, subset of cols, no errors |
| upsert into table tdata (id, valb, name, vali) values |
| (1, true, NULL, 1), |
| (8, false, 'hello', 2), |
| (5, NULL, 'five', 10), |
| (9, true, 'nine', 9) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 4 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'he',0,43,'e',false,35,36,1.2,37 |
| 1,'NULL',NULL,1,'a',true,-1,-2,0,0 |
| 2,'you',Infinity,0,'t',false,NULL,-32768,0,0 |
| 3,'todd',1,43,'c',true,3,3,3,3 |
| 4,'four',5,6,'f',true,7,8,7.5,9 |
| 10,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| 2147483647,'',-Infinity,9223372036854775807,'',true,-128,32767,NaN,-999999999 |
| 5,'five',0,10,'six',NULL,NULL,NULL,0.5,NULL |
| 6,'',-6,40,'b',NULL,0,0,10,11 |
| 7,'seven',0,-9223372036854775808,'NULL',true,7,1,2,3 |
| 8,'hello',NULL,2,'NULL',false,NULL,NULL,NULL,NULL |
| 9,'nine',NULL,9,'NULL',true,NULL,NULL,NULL,NULL |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # SELECT, all cols, single row, no errors |
| upsert into table tdata |
| select id, 'a', valf, vali, valv, NULL, valt, vals, 3, valdec from tdata where id = 1 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'he',0,43,'e',false,35,36,1.2,37 |
| 1,'a',NULL,1,'a',NULL,-1,-2,3,0 |
| 2,'you',Infinity,0,'t',false,NULL,-32768,0,0 |
| 3,'todd',1,43,'c',true,3,3,3,3 |
| 4,'four',5,6,'f',true,7,8,7.5,9 |
| 10,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| 2147483647,'',-Infinity,9223372036854775807,'',true,-128,32767,NaN,-999999999 |
| 5,'five',0,10,'six',NULL,NULL,NULL,0.5,NULL |
| 6,'',-6,40,'b',NULL,0,0,10,11 |
| 7,'seven',0,-9223372036854775808,'NULL',true,7,1,2,3 |
| 8,'hello',NULL,2,'NULL',false,NULL,NULL,NULL,NULL |
| 9,'nine',NULL,9,'NULL',true,NULL,NULL,NULL,NULL |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # SELECT, all cols, multiple rows, no errors |
| upsert into table tdata |
| select id, valv, valf, vali, name, valb, valt, vals, vald, valdec from tdata where id % 2 = 0 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 6 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'e',0,43,'he',false,35,36,1.2,37 |
| 1,'a',NULL,1,'a',NULL,-1,-2,3,0 |
| 2,'t',Infinity,0,'you',false,NULL,-32768,0,0 |
| 3,'todd',1,43,'c',true,3,3,3,3 |
| 4,'f',5,6,'four',true,7,8,7.5,9 |
| 10,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| 2147483647,'',-Infinity,9223372036854775807,'',true,-128,32767,NaN,-999999999 |
| 5,'five',0,10,'six',NULL,NULL,NULL,0.5,NULL |
| 6,'b',-6,40,'',NULL,0,0,10,11 |
| 7,'seven',0,-9223372036854775808,'NULL',true,7,1,2,3 |
| 8,'NULL',NULL,2,'hello',false,NULL,NULL,NULL,NULL |
| 9,'nine',NULL,9,'NULL',true,NULL,NULL,NULL,NULL |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # SELECT, subset of cols, single row, no errors |
| upsert into table tdata (id, valv, vali) |
| select int_col, string_col, bigint_col from functional.alltypes where id = 0 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'e',0,43,'he',false,35,36,1.2,37 |
| 1,'a',NULL,1,'a',NULL,-1,-2,3,0 |
| 2,'t',Infinity,0,'you',false,NULL,-32768,0,0 |
| 3,'todd',1,43,'c',true,3,3,3,3 |
| 4,'f',5,6,'four',true,7,8,7.5,9 |
| 10,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| 2147483647,'',-Infinity,9223372036854775807,'',true,-128,32767,NaN,-999999999 |
| 5,'five',0,10,'six',NULL,NULL,NULL,0.5,NULL |
| 6,'b',-6,40,'',NULL,0,0,10,11 |
| 7,'seven',0,-9223372036854775808,'NULL',true,7,1,2,3 |
| 8,'NULL',NULL,2,'hello',false,NULL,NULL,NULL,NULL |
| 9,'nine',NULL,9,'NULL',true,NULL,NULL,NULL,NULL |
| 0,'NULL',NULL,0,'0',NULL,NULL,NULL,NULL,NULL |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # SELECT, subset of cols, multiple rows, no errors |
| upsert into table tdata (id, valb, name, valt) |
| select int_col, bool_col, string_col, tinyint_col from functional.alltypes where id < 4 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 4 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'e',0,43,'he',false,35,36,1.2,37 |
| 1,'1',NULL,1,'a',false,1,-2,3,0 |
| 2,'2',Infinity,0,'you',true,2,-32768,0,0 |
| 3,'3',1,43,'c',false,3,3,3,3 |
| 4,'f',5,6,'four',true,7,8,7.5,9 |
| 10,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| 2147483647,'',-Infinity,9223372036854775807,'',true,-128,32767,NaN,-999999999 |
| 5,'five',0,10,'six',NULL,NULL,NULL,0.5,NULL |
| 6,'b',-6,40,'',NULL,0,0,10,11 |
| 7,'seven',0,-9223372036854775808,'NULL',true,7,1,2,3 |
| 8,'NULL',NULL,2,'hello',false,NULL,NULL,NULL,NULL |
| 9,'nine',NULL,9,'NULL',true,NULL,NULL,NULL,NULL |
| 0,'0',NULL,0,'0',true,0,NULL,NULL,NULL |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # SELECT, decimal column, multiple rows, no errors |
| upsert into table tdata (id, valdec) |
| select id, cast((valdec * 2) as decimal(9,0)) from tdata where valdec > 0 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 5 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC |
| ---- DML_RESULTS: tdata |
| 40,'e',0,43,'he',false,35,36,1.2,74 |
| 1,'1',NULL,1,'a',false,1,-2,3,0 |
| 2,'2',Infinity,0,'you',true,2,-32768,0,0 |
| 3,'3',1,43,'c',false,3,3,3,6 |
| 4,'f',5,6,'four',true,7,8,7.5,18 |
| 10,'NULL',NULL,NULL,'NULL',NULL,NULL,NULL,NULL,NULL |
| 2147483647,'',-Infinity,9223372036854775807,'',true,-128,32767,NaN,-999999999 |
| 5,'five',0,10,'six',NULL,NULL,NULL,0.5,NULL |
| 6,'b',-6,40,'',NULL,0,0,10,22 |
| 7,'seven',0,-9223372036854775808,'NULL',true,7,1,2,6 |
| 8,'NULL',NULL,2,'hello',false,NULL,NULL,NULL,NULL |
| 9,'nine',NULL,9,'NULL',true,NULL,NULL,NULL,NULL |
| 0,'0',NULL,0,'0',true,0,NULL,NULL,NULL |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL |
| ==== |
| ---- QUERY |
| # VALUES, single row, all cols, null for non-nullable column |
| upsert into table tdata values (null, '', 0, 0, cast('' as VARCHAR(20)), false, 0, 0, 0, null) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 0 |
| NumRowErrors: 1 |
| ==== |
| ---- QUERY |
| # VALUES, single row, subset of cols, null for non-nullable column |
| upsert into table tdata (id, name) values (null, '') |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 0 |
| NumRowErrors: 1 |
| ==== |
| ---- QUERY |
| # VALUES, multiple rows, all cols, null for non-nullable column |
| upsert into table tdata values |
| (3,'todd',1,43,'c',true,3,3,3,null), |
| (4,'four',5,6,'f',true,7,8,7.5,null), |
| (6,'',-6,40,'b',NULL,0,0,10,null), |
| (NULL,'seven',0,0,'NULL',true,7,1,2,null) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 3 |
| NumRowErrors: 1 |
| ==== |
| ---- QUERY |
| # VALUES, multiple rows, subset of cols, null for non-nullable column |
| upsert into table tdata (id, valv, valt, vals) values |
| (0, 'bbb', 1, 2), |
| (NULL, 'aaa', 2, 1), |
| (10, 'ccc', 11, 12) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 2 |
| NumRowErrors: 1 |
| ==== |
| ---- QUERY |
| # high cardinality |
| upsert into table tdata (id, valb, name, valt) |
| select int_col, bool_col, string_col, tinyint_col from functional.alltypes limit 100 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 100 |
| NumRowErrors: 0 |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # IMPALA-6280: check that TupleIsNullPredicate is materialized correctly |
| upsert into table tdata (id, vali) |
| select t1.id, v.id from functional.alltypestiny t1 |
| left outer join (select ifnull(id, 10) id from functional.alltypessmall) v |
| on t1.id = v.id limit 1 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ==== |
| ---- 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) |
| ---- RESULTS |
| : 2 |
| ==== |
| ---- QUERY |
| # VALUES, multiple key columns, all cols |
| upsert into table multiple_key_cols values |
| ('a', 1, 2, 3, true, NULL, 5, 6), |
| ('a', -1, -2, -3, true, 0, NULL, NULL), |
| ('c', 0, 0, 0, NULL, 10, 20, 30) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 3 |
| NumRowErrors: 0 |
| ---- LABELS |
| STRING_COL, BIGINT_COL, TINYINT_COL, SMALLINT_COL, BOOL_COL, INT_COL, DOUBLE_COL, FLOAT_COL |
| ---- DML_RESULTS: multiple_key_cols |
| 'a',1,2,3,true,NULL,5,6 |
| 'a',-1,-2,-3,true,0,NULL,NULL |
| 'b',1,2,3,false,7,NULL,9 |
| 'c',0,0,0,NULL,10,20,30 |
| ---- TYPES |
| STRING,BIGINT,TINYINT,SMALLINT,BOOLEAN,INT,DOUBLE,FLOAT |
| ==== |
| ---- QUERY |
| # VALUES, multiple key columns, subset of cols |
| upsert into table multiple_key_cols |
| (string_col, bool_col, bigint_col, smallint_col, tinyint_col) values |
| ('a', false, 1, 3, 2), |
| ('d', NULL, 0, 1, 2), |
| ('b', true, 1, 3, 3) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 3 |
| NumRowErrors: 0 |
| ---- LABELS |
| STRING_COL, BIGINT_COL, TINYINT_COL, SMALLINT_COL, BOOL_COL, INT_COL, DOUBLE_COL, FLOAT_COL |
| ---- DML_RESULTS: multiple_key_cols |
| 'a',1,2,3,false,NULL,5,6 |
| 'a',-1,-2,-3,true,0,NULL,NULL |
| 'b',1,2,3,false,7,NULL,9 |
| 'c',0,0,0,NULL,10,20,30 |
| 'd',0,2,1,NULL,NULL,NULL,NULL |
| 'b',1,3,3,true,NULL,NULL,NULL |
| ---- TYPES |
| STRING,BIGINT,TINYINT,SMALLINT,BOOLEAN,INT,DOUBLE,FLOAT |
| ==== |
| ---- QUERY |
| # SELECT, multiple key columns, all cols |
| upsert into table multiple_key_cols |
| select string_col, bigint_col, tinyint_col, smallint_col, false, -1, -2, -3 |
| from multiple_key_cols where string_col = 'a' |
| ---- 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 |
| 'a',1,2,3,false,-1,-2,-3 |
| 'a',-1,-2,-3,false,-1,-2,-3 |
| 'b',1,2,3,false,7,NULL,9 |
| 'c',0,0,0,NULL,10,20,30 |
| 'd',0,2,1,NULL,NULL,NULL,NULL |
| 'b',1,3,3,true,NULL,NULL,NULL |
| ---- TYPES |
| STRING,BIGINT,TINYINT,SMALLINT,BOOLEAN,INT,DOUBLE,FLOAT |
| ==== |
| ---- QUERY |
| # SELECT, multiple key columns, subset of cols |
| upsert into table multiple_key_cols |
| (string_col, float_col, bigint_col, tinyint_col, double_col, smallint_col) |
| select 'b', float_col, 1, tinyint_col, double_col, 3 |
| from functional.alltypes where id = 2 or id = 3 |
| ---- 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 |
| 'a',1,2,3,false,-1,-2,-3 |
| 'a',-1,-2,-3,false,-1,-2,-3 |
| 'b',1,2,3,false,7,20.2,2.200000047683716 |
| 'c',0,0,0,NULL,10,20,30 |
| 'd',0,2,1,NULL,NULL,NULL,NULL |
| 'b',1,3,3,true,NULL,30.3,3.299999952316284 |
| ---- TYPES |
| STRING,BIGINT,TINYINT,SMALLINT,BOOLEAN,INT,DOUBLE,FLOAT |
| ==== |
| ---- QUERY |
| # missing key column |
| upsert into table multiple_key_cols |
| (string_col, tinyint_col, smallint_col) values ('a', 1, 1) |
| ---- CATCH |
| All primary key columns must be specified for UPSERTing into Kudu tables. Missing columns are: bigint_col |
| ==== |