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. -- -- ** insert positionedDelete.sql -- -- tests for positioned delete -- -- note that comments that begin '-- .' are test cases from the test plan -- assumed available in queries at time of initial writing: -- subqueries. Additional tests will be needed once we have: -- order by, group by, having, aggregates, distinct, views ... -- setup some tables for use in the tests create table t1 ( i int, v varchar(10), d double precision, t time ); 0 rows inserted/updated/deleted ij> create table t1_copy ( i int, v varchar(10), d double precision, t time ); 0 rows inserted/updated/deleted ij> create table t2 ( s smallint, c char(10), r real, ts timestamp ); 0 rows inserted/updated/deleted ij> -- populate the first table and copy insert into t1 values (1, '1111111111', 11e11, time('11:11:11')); 1 row inserted/updated/deleted ij> insert into t1_copy select * from t1; 1 row inserted/updated/deleted ij> -- we need to turn autocommit off so that cursors aren't closed before -- the positioned statements against them. autocommit off; ij> -- empty table tests -- .no table name given -- this should fail with a syntax error delete; ERROR 42X01: Syntax error: Encountered "" at line 5, column 6. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- this should succeed get cursor c0 as 'select * from t1 for update'; ij> next c0; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> delete from t1 where current of c0; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ ij> close c0; ij> -- restore t1 delete from t1; 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> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- .same table name -- .cursor before 1st row get cursor c1 as 'select * from t2 for update'; ij> -- 'cursor not on a row' expected delete from t2 where current of c1; ERROR 24000: Invalid cursor state - no current row. ij> -- .different table name delete from t1 where current of c1; ERROR 42X28: Delete table 'T1' is not target of cursor 'C1'. ij> -- restore t1 delete from t1; 1 row inserted/updated/deleted ij> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- .non-existant table delete from not_exists where current of c1; ERROR 42X28: Delete table 'NOT_EXISTS' is not target of cursor 'C1'. ij> close c1; ij> -- .delete from base table, not exposed table name -- (this one should work, since base table) get cursor c2 as 'select * from t2 asdf for update'; ij> delete from t2 where current of c2; ERROR 24000: Invalid cursor state - no current row. ij> -- restore t1 delete from t1; 1 row inserted/updated/deleted ij> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- .match correlation name -- (this one should fail, since correlation name) delete from asdf where current of c2; ERROR 42X28: Delete table 'ASDF' is not target of cursor 'C2'. ij> close c2; ij> -- .non-updatable cursor -- NOTE - forupdate responsible for extensive tests get cursor c3 as 'select * from t2 for read only'; ij> delete from t2 where current of c3; ERROR 42X23: Cursor C3 is not updatable. ij> close c3; ij> -- .target cursor does not exist delete from t2 where current of c44; ERROR 42X30: Cursor 'C44' not found. Verify that autocommit is OFF. ij> -- .target cursor after last row get cursor c4 as 'select * from t1 for update'; ij> next c4; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> next c4; No current row ij> next c4; No current row ij> delete from t1 where current of c4; ERROR 24000: Invalid cursor state - no current row. ij> close c4; ij> -- .target cursor exists, closed get cursor c5 as 'select * from t1'; ij> close c5; ij> delete from t1 where current of c5; ERROR 42X30: Cursor 'C5' not found. Verify that autocommit is OFF. ij> -- .target cursor on row get cursor c6 as 'select * from t1 for update'; ij> next c6; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> delete from t1 where current of c6; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ ij> close c6; ij> -- restore t1 delete from t1; 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> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- .target cursor on row deleted by another cursor get cursor c7 as 'select * from t1 for update'; ij> next c7; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> get cursor c8 as 'select * from t1 for update'; ij> next c8; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> delete from t1 where current of c7; 1 row inserted/updated/deleted ij> delete from t1 where current of c8; ERROR 24000: Invalid cursor state - no current row. ij> select * from t1; I |V |D |T ------------------------------------------------------ ij> close c7; ij> close c8; ij> -- restore t1 delete from t1; 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> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- .target cursor on already deleted row get cursor c9 as 'select * from t1 for update'; ij> next c9; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> delete from t1 where current of c9; 1 row inserted/updated/deleted ij> delete from t1 where current of c9; ERROR 24000: Invalid cursor state - no current row. ij> select * from t1; I |V |D |T ------------------------------------------------------ ij> close c9; ij> -- restore t1 delete from t1; 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> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- delete to row which was subject to searched update -- (row still within cursor qualification) get cursor c10 as 'select * from t1 for update'; ij> next c10; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = i + 1; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 2 |1111111111|1.1E12 |11:11:11 ij> delete from t1 where current of c10; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ ij> close c10; ij> -- restore t1 delete from t1; 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> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- delete to row which was subject to searched update -- (row becomes outside of cursor qualification) get cursor c10a as 'select * from t1 where i = 1 for update'; ij> next c10a; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = i + 1; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 2 |1111111111|1.1E12 |11:11:11 ij> delete from t1 where current of c10a; ERROR 24000: Invalid cursor state - no current row. ij> select * from t1; I |V |D |T ------------------------------------------------------ 2 |1111111111|1.1E12 |11:11:11 ij> close c10a; ij> -- restore t1 delete from t1; 1 row inserted/updated/deleted ij> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- delete to row which was subject to positioned update -- (row becomes outside of cursor qualification) get cursor c11 as 'select * from t1 where i = 1 for update'; ij> next c11; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = i + 1 where current of c11; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 2 |1111111111|1.1E12 |11:11:11 ij> delete from t1 where current of c11; ERROR 24000: Invalid cursor state - no current row. ij> select * from t1; I |V |D |T ------------------------------------------------------ 2 |1111111111|1.1E12 |11:11:11 ij> close c11; ij> -- restore t1 delete from t1; 1 row inserted/updated/deleted ij> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- delete to row which was subject to 2 searched updates -- (1st puts row outside of cursor qualification, 2nd restores it) get cursor c12 as 'select * from t1 where i = 1 for update'; ij> next c12; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = i + 1; 1 row inserted/updated/deleted ij> update t1 set i = 1; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> delete from t1 where current of c12; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ ij> close c12; ij> -- restore t1 delete from t1; 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> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- positioned delete on table with index (#724) create table t5 (c1 int, c2 int); 0 rows inserted/updated/deleted ij> insert into t5 values (1, 1), (2, 2), (3, 3), (4, 4); 4 rows inserted/updated/deleted ij> commit; ij> create index i5 on t5(c1); 0 rows inserted/updated/deleted ij> get cursor c1 as 'select * from t5 where c1 > 1 for update of c2'; ij> next c1; C1 |C2 ----------------------- 2 |2 ij> delete from t5 where current of c1; 1 row inserted/updated/deleted ij> next c1; C1 |C2 ----------------------- 3 |3 ij> next c1; C1 |C2 ----------------------- 4 |4 ij> delete from t5 where current of c1; 1 row inserted/updated/deleted ij> select * from t5; C1 |C2 ----------------------- 1 |1 3 |3 ij> close c1; ij> rollback; ij> create index i5 on t5(c2); 0 rows inserted/updated/deleted ij> get cursor c1 as 'select * from t5 where c1 > 1 for update of c2'; ij> next c1; C1 |C2 ----------------------- 2 |2 ij> delete from t5 where current of c1; 1 row inserted/updated/deleted ij> next c1; C1 |C2 ----------------------- 3 |3 ij> next c1; C1 |C2 ----------------------- 4 |4 ij> delete from t5 where current of c1; 1 row inserted/updated/deleted ij> select * from t5; C1 |C2 ----------------------- 1 |1 3 |3 ij> close c1; ij> rollback; ij> -- reset autocommit autocommit on; ij> -- drop the tables drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> drop table t5; 0 rows inserted/updated/deleted ij> drop table t1_copy; 0 rows inserted/updated/deleted ij> -- ** insert positionedUpdate.sql -- -- tests for positioned update -- -- note that comments that begin '-- .' are test cases from the test plan -- assumed available in queries at time of initial writing: -- subqueries. Additional tests will be needed once we have: -- order by, group by, having, aggregates, distinct, views ... -- setup some tables for use in the tests create table t1 ( i int, v varchar(10), d double precision, t time ); 0 rows inserted/updated/deleted ij> create table t1_copy ( i int, v varchar(10), d double precision, t time ); 0 rows inserted/updated/deleted ij> create table t2 ( s smallint, c char(10), r real, ts timestamp ); 0 rows inserted/updated/deleted ij> -- populate the first table and copy insert into t1 values (1, '1111111111', 11e11, time('11:11:11')); 1 row inserted/updated/deleted ij> insert into t1_copy select * from t1; 1 row inserted/updated/deleted ij> -- we need to turn autocommit off so that cursors aren't closed before -- the positioned statements against them. autocommit off; ij> -- empty table tests -- .no table name given -- this should fail with a syntax error update set c1 = c1; ERROR 42X01: Syntax error: Encountered "set" at line 5, column 8. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- this should succeed get cursor c0 as 'select * from t1 for update'; ij> next c0; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = 999 where current of c0; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 999 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = 1 where current of c0; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> close c0; ij> -- .same table name -- .cursor before 1st row get cursor c1 as 'select * from t2 for update'; ij> -- 'cursor not on a row' expected update t2 set s = s where current of c1; ERROR 24000: Invalid cursor state - no current row. ij> -- .different table name update t1 set i = i where current of c1; ERROR 42X29: Update table 'T1' is not the target of cursor 'C1'. ij> -- .non-existant table update not_exists set i = i where current of c1; ERROR 42X29: Update table 'NOT_EXISTS' is not the target of cursor 'C1'. ij> close c1; ij> -- .update base table, not exposed table name -- (this one should work, since base table) get cursor c2 as 'select * from t2 asdf for update'; ij> update t2 set s = s where current of c2; ERROR 24000: Invalid cursor state - no current row. ij> -- .match correlation name -- (this one should fail, since correlation name) update asdf set s = s where current of c2; ERROR 42X29: Update table 'ASDF' is not the target of cursor 'C2'. ij> close c2; ij> -- .non-updatable cursor -- NOTE - forupdate responsible for extensive tests get cursor c3 as 'select * from t2 for read only'; ij> update t2 set s = s where current of c3; ERROR 42X23: Cursor C3 is not updatable. ij> close c3; ij> -- .target cursor does not exist update t2 set s = s where current of c44; ERROR 42X30: Cursor 'C44' not found. Verify that autocommit is OFF. ij> -- .target cursor after last row get cursor c4 as 'select * from t1 for update'; ij> next c4; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> next c4; No current row ij> next c4; No current row ij> update t1 set i = i where current of c4; ERROR 24000: Invalid cursor state - no current row. ij> close c4; ij> -- .target cursor exists, closed get cursor c5 as 'select * from t1'; ij> close c5; ij> update t1 set i = i where current of c5; ERROR 42X30: Cursor 'C5' not found. Verify that autocommit is OFF. ij> -- .target cursor on row get cursor c6 as 'select * from t1 for update'; ij> next c6; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = i + 1 where current of c6; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 2 |1111111111|1.1E12 |11:11:11 ij> -- .consecutive updates to same row in cursor, keeping it in the cursor qual update t1 set i = i + 1 where current of c6; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 3 |1111111111|1.1E12 |11:11:11 ij> close c6; ij> -- restore t1 delete from t1; 1 row inserted/updated/deleted ij> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- .target cursor on row deleted by another cursor get cursor c7 as 'select * from t1 for update'; ij> next c7; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> get cursor c8 as 'select * from t1 for update'; ij> next c8; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> delete from t1 where current of c7; 1 row inserted/updated/deleted ij> update t1 set i = i + 1 where current of c8; ERROR 24000: Invalid cursor state - no current row. ij> select * from t1; I |V |D |T ------------------------------------------------------ ij> close c7; ij> close c8; ij> -- restore t1 delete from t1; 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> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- .target cursor on already deleted row get cursor c9 as 'select * from t1 for update'; ij> next c9; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> delete from t1 where current of c9; 1 row inserted/updated/deleted ij> update t1 set i = i + 1 where current of c9; ERROR 24000: Invalid cursor state - no current row. ij> select * from t1; I |V |D |T ------------------------------------------------------ ij> close c9; ij> -- restore t1 delete from t1; 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> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- update to row which was subject to searched update -- (row still within cursor qualification) get cursor c10 as 'select * from t1 for update'; ij> next c10; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = i + 1; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 2 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = i + 2 where current of c10; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 4 |1111111111|1.1E12 |11:11:11 ij> close c10; ij> -- restore t1 delete from t1; 1 row inserted/updated/deleted ij> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- update to row which was subject to searched update -- (row becomes outside of cursor qualification) get cursor c10a as 'select * from t1 where i = 1 for update'; ij> next c10a; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = i + 1; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 2 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = i + 2 where current of c10a; ERROR 24000: Invalid cursor state - no current row. ij> select * from t1; I |V |D |T ------------------------------------------------------ 2 |1111111111|1.1E12 |11:11:11 ij> close c10a; ij> -- restore t1 delete from t1; 1 row inserted/updated/deleted ij> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- update to row which was subject to positioned update -- (row becomes outside of cursor qualification) get cursor c11 as 'select * from t1 where i = 1 for update'; ij> next c11; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = i + 1 where current of c11; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 2 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = i + 2 where current of c11; ERROR 24000: Invalid cursor state - no current row. ij> select * from t1; I |V |D |T ------------------------------------------------------ 2 |1111111111|1.1E12 |11:11:11 ij> close c11; ij> -- restore t1 delete from t1; 1 row inserted/updated/deleted ij> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- update to row which was subject to 2 searched updates -- (1st puts row outside of cursor qualification, 2nd restores it) get cursor c12 as 'select * from t1 where i = 1 for update'; ij> next c12; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = i + 1; 1 row inserted/updated/deleted ij> update t1 set i = 1; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> update t1 set i = i + 2 where current of c12; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 3 |1111111111|1.1E12 |11:11:11 ij> -- negative test - try to update a non-existant column update t1 set notacolumn = i + 1 where current of c12; ERROR 42X14: 'NOTACOLUMN' is not a column in table or VTI 'APP.T1'. ij> close c12; ij> -- restore t1 delete from t1; 1 row inserted/updated/deleted ij> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- update column not in SELECT list, but in FOR UPDATE OF list get cursor c13 as 'select i from t1 for update of v'; ij> next c13; I ----------- 1 ij> update t1 set v = '999' where current of c13; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |999 |1.1E12 |11:11:11 ij> -- update column not in FOR UPDATE OF list (negative test) update t1 set i = 999 where current of c13; ERROR 42X31: Column 'I' is not in the FOR UPDATE list of cursor 'C13'. ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |999 |1.1E12 |11:11:11 ij> close c13; ij> -- restore t1 delete from t1; 1 row inserted/updated/deleted ij> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- update a non-referenced column get cursor c14 as 'select i from t1 for update'; ij> next c14; I ----------- 1 ij> update t1 set v = '999' where current of c14; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |999 |1.1E12 |11:11:11 ij> close c14; ij> -- restore t1 delete from t1; 1 row inserted/updated/deleted ij> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- .update columns in list in order different from the list's get cursor c15 as 'select i, v from t1 for update of i, v'; ij> next c15; I |V ---------------------- 1 |1111111111 ij> update t1 set v = '999', i = 888 where current of c15; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 888 |999 |1.1E12 |11:11:11 ij> -- . show that target table name must be used as qualifier, other names not allowed update t1 set t1.v = '998' where current of c15; 1 row inserted/updated/deleted ij> update t1 set t2.v = '997' where current of c15; ERROR 42X55: Table name 'T2' should be the same as 'T1'. ij> select * from t1; I |V |D |T ------------------------------------------------------ 888 |998 |1.1E12 |11:11:11 ij> close c15; ij> -- restore t1 delete from t1; 1 row inserted/updated/deleted ij> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- .update only 1 column in the list get cursor c16 as 'select i, v from t1 for update of i, v'; ij> next c16; I |V ---------------------- 1 |1111111111 ij> update t1 set v = '999' where current of c16; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |999 |1.1E12 |11:11:11 ij> close c16; ij> -- restore t1 delete from t1; 1 row inserted/updated/deleted ij> insert into t1 select * from t1_copy; 1 row inserted/updated/deleted ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- .try to update through a closed cursor get cursor c17 as 'select i, v from t1 for update of i, v'; ij> next c17; I |V ---------------------- 1 |1111111111 ij> close c17; ij> update t1 set v = '999' where current of c17; ERROR 42X30: Cursor 'C17' not found. Verify that autocommit is OFF. ij> select * from t1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> -- a positioned update requires a named target table. -- if we prepare the positioned update, close the underlying cursor -- and reopen it on a different table, then the positioned update -- should fail create table t3(c1 int, c2 int); 0 rows inserted/updated/deleted ij> insert into t3 values (1,1), (2, 1), (3,3); 3 rows inserted/updated/deleted ij> create table t4(c1 int, c2 int); 0 rows inserted/updated/deleted ij> insert into t4 select * from t3; 3 rows inserted/updated/deleted ij> get cursor c1 as 'select c1 from t3 for update of c1'; ij> next c1; C1 ----------- 1 ij> prepare u1 as 'update t3 set c1 = c1 + 1 where current of c1'; ij> execute u1; 1 row inserted/updated/deleted ij> next c1; C1 ----------- 2 ij> select * from t3; C1 |C2 ----------------------- 2 |1 2 |1 3 |3 ij> close c1; ij> get cursor c1 as 'select c1 from t4 for update of c1'; ij> next c1; C1 ----------- 1 ij> execute u1; ERROR 42X29: Update table 'T3' is not the target of cursor 'C1'. ij> select * from t4; C1 |C2 ----------------------- 1 |1 2 |1 3 |3 ij> select * from t3; C1 |C2 ----------------------- 2 |1 2 |1 3 |3 ij> close c1; ij> -- now, reopen c1 on a table without column c1 and see -- what happens on an attempted positioned update get cursor c1 as 'select * from t1 for update'; ij> next c1; I |V |D |T ------------------------------------------------------ 1 |1111111111|1.1E12 |11:11:11 ij> execute u1; ERROR 42X29: Update table 'T3' is not the target of cursor 'C1'. ij> close c1; ij> -- now, reopen c1 on t3, but as a read only cursor select * from t3; C1 |C2 ----------------------- 2 |1 2 |1 3 |3 ij> get cursor c1 as 'select c1 from t3 '; ij> next c1; C1 ----------- 2 ij> execute u1; ERROR 42X23: Cursor C1 is not updatable. ij> select * from t3; C1 |C2 ----------------------- 2 |1 2 |1 3 |3 ij> close c1; ij> -- positioned update on table with index (#724) create table t5 (c1 int, c2 int); 0 rows inserted/updated/deleted ij> insert into t5 values (1, 1), (2, 2), (3, 3), (4, 4); 4 rows inserted/updated/deleted ij> commit; ij> create index i5 on t5(c1); 0 rows inserted/updated/deleted ij> get cursor c1 as 'select * from t5 where c1 > 1 for update of c2'; ij> next c1; C1 |C2 ----------------------- 2 |2 ij> update t5 set c2 = 9 where current of c1; 1 row inserted/updated/deleted ij> next c1; C1 |C2 ----------------------- 3 |3 ij> next c1; C1 |C2 ----------------------- 4 |4 ij> update t5 set c2 = 9 where current of c1; 1 row inserted/updated/deleted ij> select * from t5; C1 |C2 ----------------------- 1 |1 2 |9 3 |3 4 |9 ij> close c1; ij> rollback; ij> create index i5 on t5(c2); 0 rows inserted/updated/deleted ij> get cursor c1 as 'select * from t5 where c1 > 1 for update of c2'; ij> next c1; C1 |C2 ----------------------- 2 |2 ij> update t5 set c2 = 9 where current of c1; 1 row inserted/updated/deleted ij> next c1; C1 |C2 ----------------------- 3 |3 ij> next c1; C1 |C2 ----------------------- 4 |4 ij> update t5 set c2 = 9 where current of c1; 1 row inserted/updated/deleted ij> select * from t5; C1 |C2 ----------------------- 1 |1 2 |9 3 |3 4 |9 ij> close c1; ij> rollback; ij> -- reset autocommit autocommit on; ij> -- drop the tables drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> drop table t3; 0 rows inserted/updated/deleted ij> drop table t4; 0 rows inserted/updated/deleted ij> drop table t5; 0 rows inserted/updated/deleted ij> drop table t1_copy; 0 rows inserted/updated/deleted ij> -- tests for beetle 4417, schema and correlation name not working with -- current of create schema ejb; 0 rows inserted/updated/deleted ij> create table ejb.test1 (primarykey varchar(41) not null primary key, name varchar(200), parentkey varchar(41)); 0 rows inserted/updated/deleted ij> insert into ejb.test1 values('0','jack','jill'); 1 row inserted/updated/deleted ij> autocommit off; ij> -- test update with schema name get cursor c1 as 'select primarykey, parentkey, name from ejb.test1 where primarykey = ''0'' for update'; ij> next c1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |jack ij> prepare p1 as 'update ejb.test1 set name = ''john'' where current of c1'; ij> execute p1; 1 row inserted/updated/deleted ij> select primarykey, parentkey, name from ejb.test1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |john ij> close c1; ij> -- test update with schema name and correlation name get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from ejb.test1 t1 where t1.primarykey = ''0'' for update'; ij> next c1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |john ij> prepare p1 as 'update ejb.test1 set name = ''joe'' where current of c1'; ij> execute p1; 1 row inserted/updated/deleted ij> select primarykey, parentkey, name from ejb.test1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |joe ij> close c1; ij> -- test update with set schema set schema ejb; 0 rows inserted/updated/deleted ij> get cursor c1 as 'select primarykey, parentkey, name from test1 where primarykey = ''0'' for update'; ij> next c1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |joe ij> prepare p1 as 'update test1 set name = ''john'' where current of c1'; ij> execute p1; 1 row inserted/updated/deleted ij> select primarykey, parentkey, name from ejb.test1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |john ij> close c1; ij> -- test update with set schema and correlation name get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from test1 t1 where t1.primarykey = ''0'' for update'; ij> next c1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |john ij> prepare p1 as 'update test1 set name = ''joe'' where current of c1'; ij> execute p1; 1 row inserted/updated/deleted ij> select primarykey, parentkey, name from ejb.test1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |joe ij> close c1; ij> -- test update with set schema and correlation name and schema name get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from ejb.test1 t1 where t1.primarykey = ''0'' for update'; ij> next c1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |joe ij> prepare p1 as 'update ejb.test1 set name = ''joe'' where current of c1'; ij> execute p1; 1 row inserted/updated/deleted ij> select primarykey, parentkey, name from ejb.test1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |joe ij> close c1; ij> -- -- reset schema name set schema app; 0 rows inserted/updated/deleted ij> -- test delete with schema name get cursor c1 as 'select primarykey, parentkey, name from ejb.test1 where primarykey = ''0'' for update'; ij> next c1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |joe ij> prepare p2 as 'delete from ejb.test1 where current of c1'; ij> execute p2; 1 row inserted/updated/deleted ij> select primarykey, parentkey, name from ejb.test1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ij> close c1; ij> -- test delete with schema name and correlation name insert into ejb.test1 values('0','jack','jill'); 1 row inserted/updated/deleted ij> get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from ejb.test1 t1 where t1.primarykey = ''0'' for update'; ij> next c1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |jack ij> prepare p2 as 'delete from ejb.test1 where current of c1'; ij> execute p2; 1 row inserted/updated/deleted ij> select primarykey, parentkey, name from ejb.test1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ij> close c1; ij> -- test delete with set schema set schema ejb; 0 rows inserted/updated/deleted ij> insert into test1 values('0','jack','jill'); 1 row inserted/updated/deleted ij> get cursor c1 as 'select primarykey, parentkey, name from test1 where primarykey = ''0'' for update'; ij> next c1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |jack ij> prepare p2 as 'delete from test1 where current of c1'; ij> execute p2; 1 row inserted/updated/deleted ij> select primarykey, parentkey, name from ejb.test1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ij> close c1; ij> -- test delete with set schema and correlation name insert into test1 values('0','jack','jill'); 1 row inserted/updated/deleted ij> get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from test1 t1 where t1.primarykey = ''0'' for update'; ij> next c1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |jack ij> prepare p2 as 'delete from test1 where current of c1'; ij> execute p2; 1 row inserted/updated/deleted ij> select primarykey, parentkey, name from ejb.test1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ij> close c1; ij> -- test delete with set schema and correlation name and schema name insert into test1 values('0','jack','jill'); 1 row inserted/updated/deleted ij> get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from ejb.test1 t1 where t1.primarykey = ''0'' for update'; ij> next c1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |jack ij> prepare p2 as 'delete from ejb.test1 where current of c1'; ij> execute p2; 1 row inserted/updated/deleted ij> select primarykey, parentkey, name from ejb.test1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ij> close c1; ij> commit; ij> -- clean up autocommit on; ij> set schema app; 0 rows inserted/updated/deleted ij> drop table ejb.test1; 0 rows inserted/updated/deleted ij> --drop schema ejb restrict; - can't drop this because it will fail SPS tests since --statements are created and would need to be dropped -- test correlation on select in current of cursor in current schema -- this was also broken create table test1 (primarykey varchar(41) not null primary key, name varchar(200), parentkey varchar(41)); 0 rows inserted/updated/deleted ij> -- make sure a cursor will work fine in this situation insert into test1 values('0','jack','jill'); 1 row inserted/updated/deleted ij> autocommit off; ij> get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from test1 t1 where t1.primarykey = ''0'' for update'; ij> next c1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |jill |jack ij> prepare p2 as 'delete from test1 where current of c1'; ij> execute p2; 1 row inserted/updated/deleted ij> select primarykey, parentkey, name from test1; PRIMARYKEY |PARENTKEY |NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ij> close c1; ij> commit; ij> -- clean up autocommit on; ij> drop table test1; 0 rows inserted/updated/deleted ij>