| ==== |
| ---- 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, valdec4 decimal(9,9) null, valdec8 decimal(18,2) null, |
| valdec16 decimal(38, 0) null, valdate date null, valvc varchar(10) null) |
| PARTITION BY RANGE (PARTITION VALUES < 10, PARTITION 10 <= VALUES < 30, |
| PARTITION 30 <= VALUES <= 10000) STORED AS KUDU |
| ---- RESULTS |
| 'Table has been created.' |
| ==== |
| ---- QUERY |
| insert into tdata values |
| (1, "martin", 1.0, 232232323, cast('a' as string), true, 1, 2, 3, 0.000000001, 2.22, 3, DATE '1970-01-01', cast('martin' as varchar(10))), |
| (2, "david", cast(1.0 as float), 99398493939, cast('b' as string), false, 4, 5, 6, 0.000000004, 5.55, 6, DATE '1970-01-02', cast('david' as varchar(10))), |
| (3, "todd", cast(1.0 as float), 993393939, "c", true, 7, 8, 9, 0.000000007, 8.88, 9,DATE '1970-01-03', cast('todd' as varchar(10))), |
| (40, "he", cast(0.0 as float), 43, cast('e' as string), false, 50, 60, 70, 0.000000050, 66.60, 70, DATE '1970-01-04', cast('he' as varchar(10))), |
| (120, "she", cast(0.0 as float), 99, cast('f' as string), true, -1, 0, 1, -0.000000001, 0.00, 1, DATE '1970-01-05', cast('she' as varchar(10))) |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 5 |
| NumRowErrors: 0 |
| ==== |
| ---- QUERY |
| # No rows should be modified, because they already have the desired values. |
| update tdata set vali=43 where id = 40 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 0 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'martin',1.0,232232323,'a',true,1,2,3,0.000000001,2.22,3,1970-01-01,'martin' |
| 2,'david',1.0,99398493939,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',1.0,993393939,'c',true,7,8,9,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,99,'f',true,-1,0,1,-0.000000001,0.00,1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # single row, equality on key, bigint |
| # TODO: Verify row count in RESULTS after fixing IMPALA-3713, and supporting RESULTS and |
| # DML_RESULTS in the same test case. |
| update tdata set vali=43 where id = 1 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'martin',1.0,43,'a',true,1,2,3,0.000000001,2.22,3,1970-01-01,'martin' |
| 2,'david',1.0,99398493939,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',1.0,993393939,'c',true,7,8,9,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,99,'f',true,-1,0,1,-0.000000001,0.00,1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # single row, equality on key, string |
| # Try updating a string col where casting a value that is bigger than the varchar in the |
| # cast. The value gets truncated and stored to the string col. |
| update tdata set valv=cast('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' as varchar(20)) where id = 1 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'martin',1.0,43,'aaaaaaaaaaaaaaaaaaaa',true,1,2,3,0.000000001,2.22,3,1970-01-01,'martin' |
| 2,'david',1.0,99398493939,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',1.0,993393939,'c',true,7,8,9,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,99,'f',true,-1,0,1,-0.000000001,0.00,1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # single row, equality on key, boolean |
| update tdata set valb=false where id = 1 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'martin',1.0,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'martin' |
| 2,'david',1.0,99398493939,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',1.0,993393939,'c',true,7,8,9,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,99,'f',true,-1,0,1,-0.000000001,0.00,1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # single row, equality on key, set to NULL |
| update tdata set name=null, valvc=null where id = 40 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'martin',1.0,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'martin' |
| 2,'david',1.0,99398493939,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',1.0,993393939,'c',true,7,8,9,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'NULL',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'NULL' |
| 120,'she',0.0,99,'f',true,-1,0,1,-0.000000001,0.00,1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # single row, equlity on key, set from NULL |
| update tdata set name='he', valvc=cast('he' as varchar(10)) where id = 40 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'martin',1.0,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'martin' |
| 2,'david',1.0,99398493939,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',1.0,993393939,'c',true,7,8,9,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,99,'f',true,-1,0,1,-0.000000001,0.00,1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # single row, equality on key, boundary value |
| update tdata set vali = max_bigint() where id = 120 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'martin',1.0,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'martin' |
| 2,'david',1.0,99398493939,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',1.0,993393939,'c',true,7,8,9,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,9223372036854775807,'f',true,-1,0,1,-0.000000001,0.00,1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # single row, inequality on key, float |
| update tdata set valf = -1 where id > 2 and id < 4 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'martin',1.0,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'martin' |
| 2,'david',1.0,99398493939,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',-1.0,993393939,'c',true,7,8,9,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,9223372036854775807,'f',true,-1,0,1,-0.000000001,0.00,1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # single row, inequality on key, tinyint |
| update tdata set valt = 10 where id > 100 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'martin',1.0,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'martin' |
| 2,'david',1.0,99398493939,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',-1.0,993393939,'c',true,7,8,9,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,9223372036854775807,'f',true,10,0,1,-0.000000001,0.00,1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # single row, equality on non-key, string |
| update tdata set name='unknown', valvc=cast('unknown' as varchar(10)) where name = 'martin' |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'unknown',1.0,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'unknown' |
| 2,'david',1.0,99398493939,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',-1.0,993393939,'c',true,7,8,9,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,9223372036854775807,'f',true,10,0,1,-0.000000001,0.00,1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # single row, inequality on non-key, double |
| update tdata set vald = 0 where valf < 0 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'unknown',1.0,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'unknown' |
| 2,'david',1.0,99398493939,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',-1.0,993393939,'c',true,7,8,0,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,9223372036854775807,'f',true,10,0,1,-0.000000001,0.00,1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # single row, inequality on non-key, decimal |
| update tdata set valdec4 = 0.000000001, valdec16 = -1 where valdec4 < 0 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'unknown',1.0,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'unknown' |
| 2,'david',1.0,99398493939,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',-1.0,993393939,'c',true,7,8,0,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,9223372036854775807,'f',true,10,0,1,0.000000001,0.00,-1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # multiple rows, predicate on key |
| update tdata set vali=43 where id > 1 and id < 10 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 2 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'unknown',1.0,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'unknown' |
| 2,'david',1.0,43,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',-1.0,43,'c',true,7,8,0,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,9223372036854775807,'f',true,10,0,1,0.000000001,0.00,-1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # predicate on non-key |
| # Two rows match, but only one needs to be updated, the other is already false |
| update tdata set valb=false where name LIKE '%he' |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'unknown',1.0,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'unknown' |
| 2,'david',1.0,43,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',-1.0,43,'c',true,7,8,0,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,9223372036854775807,'f',false,10,0,1,0.000000001,0.00,-1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # update the same row multiple times: cross join produces 5 identical updates |
| update a set a.name='they', a.valvc=cast('they' as varchar(10)) from tdata a, tdata b where a.id = 1 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 5 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'they',1.0,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'they' |
| 2,'david',1.0,43,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',-1.0,43,'c',true,7,8,0,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,9223372036854775807,'f',false,10,0,1,0.000000001,0.00,-1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # value of update is a constant expression |
| update tdata set valf = 1 + 2 where id = 1 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'they',3,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'they' |
| 2,'david',1.0,43,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'todd',-1.0,43,'c',true,7,8,0,0.000000007,8.88,9,1970-01-03,'todd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,9223372036854775807,'f',false,10,0,1,0.000000001,0.00,-1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # value of update is a non-constant expression |
| update tdata set name = concat(name, name), valvc = cast(concat(valvc, valvc) as varchar(10)) where id % 2 = 1 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 2 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'theythey',3,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'theythey' |
| 2,'david',1.0,43,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'toddtodd',-1.0,43,'c',true,7,8,0,0.000000007,8.88,9,1970-01-03,'toddtodd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,9223372036854775807,'f',false,10,0,1,0.000000001,0.00,-1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # error - update key column |
| update tdata set id = 0 where name = 'he' |
| ---- CATCH |
| Key column 'id' cannot be updated. |
| ==== |
| ---- QUERY |
| # no rows updated |
| update tdata set name = 'none' where id = 10 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 0 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'theythey',3,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'theythey' |
| 2,'david',1.0,43,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'toddtodd',-1.0,43,'c',true,7,8,0,0.000000007,8.88,9,1970-01-03,'toddtodd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,9223372036854775807,'f',false,10,0,1,0.000000001,0.00,-1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| # Try to update 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. |
| update tdata set vali = 10 where id = 10001 |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 0 |
| NumRowErrors: 0 |
| ---- LABELS |
| ID, NAME, VALF, VALI, VALV, VALB, VALT, VALS, VALD, VALDEC4, VALDEC8, VALDEC16, VALDATE, VALVC |
| ---- DML_RESULTS: tdata |
| 1,'theythey',3,43,'aaaaaaaaaaaaaaaaaaaa',false,1,2,3,0.000000001,2.22,3,1970-01-01,'theythey' |
| 2,'david',1.0,43,'b',false,4,5,6,0.000000004,5.55,6,1970-01-02,'david' |
| 3,'toddtodd',-1.0,43,'c',true,7,8,0,0.000000007,8.88,9,1970-01-03,'toddtodd' |
| 40,'he',0.0,43,'e',false,50,60,70,0.000000050,66.60,70,1970-01-04,'he' |
| 120,'she',0.0,9223372036854775807,'f',false,10,0,1,0.000000001,0.00,-1,1970-01-05,'she' |
| ---- TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,STRING |
| ---- HS2_TYPES |
| INT,STRING,FLOAT,BIGINT,STRING,BOOLEAN,TINYINT,SMALLINT,DOUBLE,DECIMAL,DECIMAL,DECIMAL,DATE,VARCHAR |
| ==== |
| ---- QUERY |
| insert into tdata |
| select id, string_col, float_col, bigint_col, string_col, bool_col, tinyint_col, |
| smallint_col, double_col, NULL, NULL, NULL, NULL, NULL from functional_kudu.alltypes |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 7295 |
| NumRowErrors: 5 |
| ==== |
| ---- QUERY |
| # Test a larger UPDATE |
| update tdata set vali = -1 |
| ---- RESULTS |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 7300 |
| NumRowErrors: 0 |
| ==== |
| ---- QUERY |
| # Create Kudu table with non unique primary key |
| create table update_non_unique_key_test non unique primary key (id) |
| partition by hash (id) partitions 3 stored as kudu |
| as select id, int_col from functional.alltypestiny; |
| select * from update_non_unique_key_test order by id; |
| ---- RESULTS |
| 0,0 |
| 1,1 |
| 2,0 |
| 3,1 |
| 4,0 |
| 5,1 |
| 6,0 |
| 7,1 |
| ---- TYPES |
| INT,INT |
| ==== |
| ---- QUERY |
| # Test a UPDATE with non unique primary key |
| update update_non_unique_key_test set int_col = -1 where id < 3; |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 3 |
| NumRowErrors: 0 |
| ==== |
| ---- QUERY |
| select * from update_non_unique_key_test order by id; |
| ---- RESULTS |
| 0,-1 |
| 1,-1 |
| 2,-1 |
| 3,1 |
| 4,0 |
| 5,1 |
| 6,0 |
| 7,1 |
| ---- TYPES |
| INT,INT |
| ==== |
| ---- QUERY |
| # Test a UPDATE with non key column |
| update update_non_unique_key_test set id = -1 where int_col = 1; |
| ---- CATCH |
| AnalysisException: Key column 'id' cannot be updated. |
| ==== |
| ---- QUERY |
| # Test a UPDATE with non key column |
| update update_non_unique_key_test set int_col = -2 where int_col = 0; |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 2 |
| NumRowErrors: 0 |
| ==== |
| ---- QUERY |
| select * from update_non_unique_key_test order by id; |
| ---- RESULTS |
| 0,-1 |
| 1,-1 |
| 2,-1 |
| 3,1 |
| 4,-2 |
| 5,1 |
| 6,-2 |
| 7,1 |
| ---- TYPES |
| INT,INT |
| ==== |
| ---- QUERY |
| # Test a UPDATE to update auto_incrementing_id column |
| update update_non_unique_key_test set auto_incrementing_id = 100 where id = 1; |
| ---- CATCH |
| AnalysisException: System generated key column 'auto_incrementing_id' cannot be updated. |
| ==== |
| ---- QUERY |
| # Test a UPDATE with auto-incrementing column in where clause |
| update update_non_unique_key_test set int_col = 0 |
| where id = 0 and auto_incrementing_id < 10; |
| ---- RUNTIME_PROFILE |
| NumModifiedRows: 1 |
| NumRowErrors: 0 |
| ==== |
| ---- QUERY |
| select id, int_col from update_non_unique_key_test |
| group by id, int_col, auto_incrementing_id order by id; |
| ---- RESULTS |
| 0,0 |
| 1,-1 |
| 2,-1 |
| 3,1 |
| 4,-2 |
| 5,1 |
| 6,-2 |
| 7,1 |
| ---- TYPES |
| INT,INT |
| ==== |
