ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- -- this test is for basic update functionality -- -- create the table create table t1 (int_col int, smallint_col smallint, char_30_col char(30), varchar_50_col varchar(50)); 0 rows inserted/updated/deleted ij> create table t2 (int_col int, smallint_col smallint, char_30_col char(30), varchar_50_col varchar(50)); 0 rows inserted/updated/deleted ij> -- populate t1 insert into t1 values (1, 2, 'char_30_col', 'varchar_50_col'); 1 row inserted/updated/deleted ij> insert into t1 values (null, null, null, null); 1 row inserted/updated/deleted ij> insert into t2 select * from t1; 2 rows inserted/updated/deleted ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- 1 |2 |char_30_col |varchar_50_col NULL |NULL |NULL |NULL ij> -- update with constants update t1 set int_col = 3, smallint_col = 4, char_30_col = 'CHAR_30_COL', varchar_50_col = 'VARCHAR_50_COL'; 2 rows inserted/updated/deleted ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- 3 |4 |CHAR_30_COL |VARCHAR_50_COL 3 |4 |CHAR_30_COL |VARCHAR_50_COL ij> update t1 set varchar_50_col = null, char_30_col = null, smallint_col = null, int_col = null; 2 rows inserted/updated/deleted ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL NULL |NULL |NULL |NULL ij> update t1 set smallint_col = 6, int_col = 5, varchar_50_col = 'varchar_50_col', char_30_col = 'char_30_col'; 2 rows inserted/updated/deleted ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- 5 |6 |char_30_col |varchar_50_col 5 |6 |char_30_col |varchar_50_col ij> -- update columns with column values update t1 set smallint_col = int_col, int_col = smallint_col, varchar_50_col = char_30_col, char_30_col = varchar_50_col; 2 rows inserted/updated/deleted ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- 6 |5 |varchar_50_col |char_30_col 6 |5 |varchar_50_col |char_30_col ij> update t1 set int_col = int_col, smallint_col = smallint_col, char_30_col = char_30_col, varchar_50_col = varchar_50_col; 2 rows inserted/updated/deleted ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- 6 |5 |varchar_50_col |char_30_col 6 |5 |varchar_50_col |char_30_col ij> -- Negative test - column in SET clause twice update t1 set int_col = 1, int_col = 2; ERROR 42X16: Column name 'INT_COL' appears multiple times in the SET clause of an UPDATE statement. ij> -- Negative test - non-existent column in SET clause update t1 set notacolumn = int_col + 1; ERROR 42X14: 'NOTACOLUMN' is not a column in table or VTI 'APP.T1'. ij> -- target table in source - deferred update -- -- first, populate table delete from t1; 2 rows inserted/updated/deleted ij> insert into t1 values (1, 1, 'one', 'one'); 1 row inserted/updated/deleted ij> insert into t1 values (2, 2, 'two', 'two'); 1 row inserted/updated/deleted ij> delete from t2; 2 rows inserted/updated/deleted ij> insert into t2 select * from t1; 2 rows inserted/updated/deleted ij> autocommit off; ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- 1 |1 |one |one 2 |2 |two |two ij> update t1 set int_col = (select t1.int_col from t1, t2 where t1.int_col = t2.int_col and t1.int_col = 1); 2 rows inserted/updated/deleted ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- 1 |1 |one |one 1 |2 |two |two ij> rollback; ij> update t1 set int_col = (select (select int_col from t1 where int_col = 2) from t2 where int_col = 1); 2 rows inserted/updated/deleted ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- 2 |1 |one |one 2 |2 |two |two ij> rollback; ij> update t1 set int_col = (select 1 from t2 where int_col = 2 and 1 in (select int_col from t1) ); 2 rows inserted/updated/deleted ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- 1 |1 |one |one 1 |2 |two |two ij> rollback; ij> update t1 set int_col = (select int_col from (select int_col from t1) a where int_col = 2); 2 rows inserted/updated/deleted ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- 2 |1 |one |one 2 |2 |two |two ij> rollback; ij> update t1 set int_col = (select int_col from t2 where int_col = 37 union select int_col from t1 where int_col = 2); 2 rows inserted/updated/deleted ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- 2 |1 |one |one 2 |2 |two |two ij> rollback; ij> update t1 set int_col = (select int_col from t2 where int_col = 37 union select int_col from (select int_col from t1 where int_col = 2) a ); 2 rows inserted/updated/deleted ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- 2 |1 |one |one 2 |2 |two |two ij> rollback; ij> -- single-row deferred update update t1 set int_col = (select int_col from t1 where int_col = 1) where int_col = 2; 1 row inserted/updated/deleted ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- 1 |1 |one |one 1 |2 |two |two ij> rollback; ij> -- zero-row deferred update - degenerate case update t1 set int_col = (select int_col from t1 where int_col = 1) where int_col = 37; 0 rows inserted/updated/deleted WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from t1; INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ---------------------------------------------------------------------------------------------------- 1 |1 |one |one 2 |2 |two |two ij> rollback; ij> autocommit on; ij> -- drop the table drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> -- Show whether update is statement atomic or not create table s (s smallint, i int); 0 rows inserted/updated/deleted ij> insert into s values (1, 1); 1 row inserted/updated/deleted ij> insert into s values (1, 65337); 1 row inserted/updated/deleted ij> insert into s values (1, 1); 1 row inserted/updated/deleted ij> select * from s; S |I ------------------ 1 |1 1 |65337 1 |1 ij> -- this should fail and no rows should change update s set s=s+i; ERROR 22003: The resulting value is outside the range for the data type SMALLINT. ij> -- this select should have the same results as the previous one. select * from s; S |I ------------------ 1 |1 1 |65337 1 |1 ij> -- Show that the table name can be used on the set column update s set s.s=3; 3 rows inserted/updated/deleted ij> -- and that it must match the target table update s set t.s=4; ERROR 42X55: Table name 'T' should be the same as 'S'. ij> select * from s; S |I ------------------ 3 |1 3 |65337 3 |1 ij> -- do some partial updates create table t1 (c1 char(250), c2 varchar(100), c3 varchar(100)); 0 rows inserted/updated/deleted ij> insert into t1 values ('a', 'b', 'c'); 1 row inserted/updated/deleted ij> insert into t1 values ('a', 'b', 'c'); 1 row inserted/updated/deleted ij> insert into t1 values ('a', 'b', 'c'); 1 row inserted/updated/deleted ij> insert into t1 values ('a', 'b', 'c'); 1 row inserted/updated/deleted ij> update t1 set c1 = '1st'; 4 rows inserted/updated/deleted ij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1; 1 |2 |3 ----------------- 1st |b |c 1st |b |c 1st |b |c 1st |b |c ij> update t1 set c2 = '2nd'; 4 rows inserted/updated/deleted ij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1; 1 |2 |3 ----------------- 1st |2nd |c 1st |2nd |c 1st |2nd |c 1st |2nd |c ij> update t1 set c3 = '3rd'; 4 rows inserted/updated/deleted ij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1; 1 |2 |3 ----------------- 1st |2nd |3rd 1st |2nd |3rd 1st |2nd |3rd 1st |2nd |3rd ij> update t1 set c3 = '4th', c2 = '4th'; 4 rows inserted/updated/deleted ij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1; 1 |2 |3 ----------------- 1st |4th |4th 1st |4th |4th 1st |4th |4th 1st |4th |4th ij> update t1 set c1 = '5th', c3 = '5th'; 4 rows inserted/updated/deleted ij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1; 1 |2 |3 ----------------- 5th |4th |5th 5th |4th |5th 5th |4th |5th 5th |4th |5th ij> update t1 set c2 = 'expandingxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; 4 rows inserted/updated/deleted ij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1; 1 |2 |3 ----------------- 5th |expan|5th 5th |expan|5th 5th |expan|5th 5th |expan|5th ij> update t1 set c3 = 'expandingxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; 4 rows inserted/updated/deleted ij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1; 1 |2 |3 ----------------- 5th |expan|expan 5th |expan|expan 5th |expan|expan 5th |expan|expan ij> update t1 set c2 = 'shrink'; 4 rows inserted/updated/deleted ij> update t1 set c3 = 'shrink'; 4 rows inserted/updated/deleted ij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1; 1 |2 |3 ----------------- 5th |shrin|shrin 5th |shrin|shrin 5th |shrin|shrin 5th |shrin|shrin ij> update t1 set c2 = 'expandingxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', c3 = 'expandingxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; 4 rows inserted/updated/deleted ij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1; 1 |2 |3 ----------------- 5th |expan|expan 5th |expan|expan 5th |expan|expan 5th |expan|expan ij> drop table t1; 0 rows inserted/updated/deleted ij> create table t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int); 0 rows inserted/updated/deleted ij> insert into t1 values (1,2,3,4,5,6,7,8,9); 1 row inserted/updated/deleted ij> update t1 set c3 = 33, c5 = 55, c6 = 666, c8 = 88; 1 row inserted/updated/deleted ij> select * from t1; C1 |C2 |C3 |C4 |C5 |C6 |C7 |C8 |C9 ----------------------------------------------------------------------------------------------------------- 1 |2 |33 |4 |55 |666 |7 |88 |9 ij> update t1 set c9 = 99; 1 row inserted/updated/deleted ij> select * from t1; C1 |C2 |C3 |C4 |C5 |C6 |C7 |C8 |C9 ----------------------------------------------------------------------------------------------------------- 1 |2 |33 |4 |55 |666 |7 |88 |99 ij> drop table t1; 0 rows inserted/updated/deleted ij> -- -- here we test extra state lying around in the -- deleteResultSet on a prepared statement that -- is executed multiple times. if we don't -- get a nasty error then we are ok -- create table x (x int, y int); 0 rows inserted/updated/deleted ij> create index ix on x(x); 0 rows inserted/updated/deleted ij> create index iy on x(y); 0 rows inserted/updated/deleted ij> insert into x values (1,1),(2,2),(3,3); 3 rows inserted/updated/deleted ij> autocommit off; ij> prepare p as 'update x set x = x where x = ? and y = ?'; ij> execute p using 'values (1,1)'; 1 row inserted/updated/deleted ij> execute p using 'values (2,2)'; 1 row inserted/updated/deleted ij> commit; ij> -- test extra state in update get cursor c1 as 'select * from x for update of x'; ij> prepare p1 as 'update x set x = x where current of c1'; ij> execute p1; ERROR 24000: Invalid cursor state - no current row. ij> next c1; X |Y ----------------------- 1 |1 ij> execute p1; 1 row inserted/updated/deleted ij> next c1; X |Y ----------------------- 2 |2 ij> next c1; X |Y ----------------------- 3 |3 ij> execute p1; 1 row inserted/updated/deleted ij> close c1; ij> execute p1; ERROR 42X30: Cursor 'C1' not found. Verify that autocommit is OFF. ij> -- clean up autocommit on; ij> drop table x; 0 rows inserted/updated/deleted ij> -- bug 4318, possible deadlock if table first has IX, then X table lock; make -- sure you don't have IX table lock and X table lock at the same time create table tab1 (c1 int not null primary key, c2 int); 0 rows inserted/updated/deleted ij> insert into tab1 values (1, 8); 1 row inserted/updated/deleted ij> autocommit off; ij> -- default read committed isolation level update tab1 set c2 = c2 + 3 where c1 = 1; 1 row inserted/updated/deleted ij> select type, mode from syscs_diag.lock_table where tablename = 'TAB1' order by type; TYPE |MODE ---------- ROW |X TABLE|IX ij> rollback; ij> -- serializable isolation level set current isolation to SERIALIZABLE; 0 rows inserted/updated/deleted ij> update tab1 set c2 = c2 + 3 where c1 = 1; 1 row inserted/updated/deleted ij> select type, mode from syscs_diag.lock_table where tablename = 'TAB1' order by type; TYPE |MODE ---------- ROW |X TABLE|IX ij> rollback; ij> autocommit on; ij> drop table tab1; 0 rows inserted/updated/deleted ij> -------------------------------------------- -- -- Test upgrade piece of the fix for bug171. -- -------------------------------------------- create table bug171_employee( empl_id int, bonus int ); 0 rows inserted/updated/deleted ij> create table bug171_bonuses( empl_id int, bonus int ); 0 rows inserted/updated/deleted ij> insert into bug171_employee( empl_id, bonus ) values ( 1, 0 ), ( 2, 0 ), ( 3, 0 ); 3 rows inserted/updated/deleted ij> insert into bug171_bonuses( empl_id, bonus ) values ( 1, 100 ), ( 1, 100 ), ( 1, 100 ), ( 2, 200 ), ( 2, 200 ), ( 2, 200 ), ( 3, 300 ), ( 3, 300 ), ( 3, 300 ); 9 rows inserted/updated/deleted ij> select * from bug171_employee; EMPL_ID |BONUS ----------------------- 1 |0 2 |0 3 |0 ij> select * from bug171_bonuses; EMPL_ID |BONUS ----------------------- 1 |100 1 |100 1 |100 2 |200 2 |200 2 |200 3 |300 3 |300 3 |300 ij> -- -- The problem query. could not use correlation names in update. -- update bug171_employee e set e.bonus = ( select sum( b.bonus ) from bug171_bonuses b where b.empl_id = e.empl_id ); 3 rows inserted/updated/deleted ij> select * from bug171_employee; EMPL_ID |BONUS ----------------------- 1 |300 2 |600 3 |900 ij> -- positioned update with correlation names autocommit off; ij> get cursor bug171_c1 as 'select * from bug171_employee where empl_id = 1 for update'; ij> next bug171_c1; EMPL_ID |BONUS ----------------------- 1 |300 ij> update bug171_employee e set e.bonus = ( select 2 * sum( b.bonus ) from bug171_bonuses b where b.empl_id = e.empl_id ) where current of bug171_c1; 1 row inserted/updated/deleted ij> close bug171_c1; ij> select * from bug171_employee; EMPL_ID |BONUS ----------------------- 1 |600 2 |600 3 |900 ij> autocommit on; ij> -- -- Cleanup -- drop table bug171_employee; 0 rows inserted/updated/deleted ij> drop table bug171_bonuses; 0 rows inserted/updated/deleted ij> -- -- DERBY-1329: Correlated subquery in UPDATE ... SET ... WHERE CURRENT OF -- CREATE TABLE BASICTABLE1(ID INTEGER, C3 CHAR(10)); 0 rows inserted/updated/deleted ij> CREATE TABLE BASICTABLE2(IID INTEGER, CC3 CHAR(10)); 0 rows inserted/updated/deleted ij> insert into BASICTABLE1 (C3, ID) values ('abc', 1); 1 row inserted/updated/deleted ij> insert into BASICTABLE2 (CC3, IID) values ('def', 1); 1 row inserted/updated/deleted ij> -- Check data. select * from BASICTABLE1; ID |C3 ---------------------- 1 |abc ij> select * from BASICTABLE2; IID |CC3 ---------------------- 1 |def ij> autocommit off; ij> get cursor c1 as 'select c3, id from basictable1 for update'; ij> next c1; C3 |ID ---------------------- abc |1 ij> -- Before fix for DERBY-1329 the following statement would fail with -- an ASSERT failure or an IndexOutOfBoundsException; after the fix -- the statement should succeed and the update as well. update BASICTABLE1 set C3 = (SELECT CC3 FROM BASICTABLE2 WHERE BASICTABLE1.ID=BASICTABLE2.IID) where current of c1; 1 row inserted/updated/deleted ij> -- Check data; BASICTABLE1 should have been updated. select * from BASICTABLE1; ID |C3 ---------------------- 1 |def ij> select * from BASICTABLE2; IID |CC3 ---------------------- 1 |def ij> -- Cleanup. rollback; ij> drop table BASICTABLE1; 0 rows inserted/updated/deleted ij> drop table BASICTABLE2; 0 rows inserted/updated/deleted ij> -- tests for DERBY-1043 CREATE TABLE DERBY10431 (ID SMALLINT GENERATED ALWAYS AS IDENTITY, A_COL VARCHAR(15) NOT NULL PRIMARY KEY); 0 rows inserted/updated/deleted ij> CREATE TABLE DERBY10432 (TYPE VARCHAR(15) NOT NULL, A_COL VARCHAR(15) NOT NULL, AMOUNT SMALLINT NOT NULL DEFAULT 0); 0 rows inserted/updated/deleted ij> INSERT INTO DERBY10431(A_COL) VALUES ('apples'); 1 row inserted/updated/deleted ij> INSERT INTO DERBY10432 VALUES ('tree fruit','apples',1); 1 row inserted/updated/deleted ij> SELECT * FROM DERBY10431; ID |A_COL ---------------------- 1 |apples ij> SELECT * FROM DERBY10432; TYPE |A_COL |AMOUNT -------------------------------------- tree fruit |apples |1 ij> -- after fix for DERBY-1043 this update should cause an exception UPDATE DERBY10432 SET DERBY10432.A_COL = DERBY10431.A_COL WHERE A_COL = 'apples'; ERROR 42X04: Column 'DERBY10431.A_COL' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'DERBY10431.A_COL' is not a column in the target table. ij> DROP TABLE DERBY10431; 0 rows inserted/updated/deleted ij> DROP TABLE DERBY10432; 0 rows inserted/updated/deleted ij>