| 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 |
| WARNING 01004: Data truncation |
| 5th |expan|5th |
| WARNING 01004: Data truncation |
| 5th |expan|5th |
| WARNING 01004: Data truncation |
| 5th |expan|5th |
| WARNING 01004: Data truncation |
| 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 |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| 5th |expan|expan |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| 5th |expan|expan |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| 5th |expan|expan |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| 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 |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| 5th |shrin|shrin |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| 5th |shrin|shrin |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| 5th |shrin|shrin |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| 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 |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| 5th |expan|expan |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| 5th |expan|expan |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| 5th |expan|expan |
| WARNING 01004: Data truncation |
| WARNING 01004: Data truncation |
| 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 CAST(tablename AS VARCHAR(128)) = '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 CAST(tablename AS VARCHAR(128)) = '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> |
