| 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 "<EOF>" 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> |
