[#CORE-5794] Wrong behavour FOR <select_stmt> [AS CURSOR cursorname] with next update and delete

Firebird Core

Wrong behavour FOR <select_stmt> [AS CURSOR cursorname] with next update and delete

Created: 16/Apr/18 01:24 PM Updated: 25/Apr/18 05:28 AM

Component/s:

None

Affects Version/s:

3.0.4

Fix Version/s:

None

QA Status:	Done successfully
Test Details:	See also test for ~~CORE-3362~~ ("Cursors should ignore changes made by the same statement").

Description

« Hide

CREATE EXCEPTION TEST_EXCEPTION 'TEST';
CREATE TABLE TEST_TABLE (
    ID INTEGER,
    VAL INTEGER
);
INSERT INTO TEST_TABLE (ID, VAL) VALUES (1, 10);

SET TERM ^ ;
CREATE OR ALTER TRIGGER TEST_TABLE_BD FOR TEST_TABLE
ACTIVE BEFORE DELETE POSITION 0
as
begin
  IF (OLD.Val >0) THEN EXCEPTION TEST_EXCEPTION 'It is forbidden to delete row with Val>0 (ID = '||Coalesce(OLD.ID, 'null')||', Val='||Coalesce(old.Val,'null')||')';
end^

Execute block
as
declare variable curVal integer;
declare variable curID integer;
begin
  for select ID, VAL
  from TEST_TABLE
  where VAL>0
  into curID, CurVal
  as cursor TmpCursor
  do begin

    update TEST_TABLE
    set Val=0
    where current of TmpCursor;

    --This expression must be executed without exception (because now Val=0), but an error occurs.
    delete from TEST_TABLE
    where current of TmpCursor;
  end
end^

All

Comments

Change History

Subversion Commits

Sort Order:

[ Permalink | « Hide ]

Vlad Khorsun added a comment - 16/Apr/18 01:34 PM

Since Firebird3 implicit PSQL cursors are stable, it means that cursor doesn't see the changes made by "inner" statements.
Fix your code, please

[ Permalink | « Hide ]

Vlad Khorsun added a comment - 16/Apr/18 04:10 PM

Few more notes:

- i failed to see in standard how cursor should work with rows updated by "positioned update statement" identified by the same cursor

- about "positioned delete statement" i see:
-----------
15.5 Effect of a positioned delete
  10) Whether the current row is removed from the sequence of rows of the result set descriptor of CR is implementation-defined
-----------
our implementation of stable cursor

- there is (implementation defined) workaround: if you need that "positioned update" or "positioned delete" statement could see
the changes made by another "inner" statements - enclose it into savepoint.
For example, add WHEN handler to the current BEGIN\END block:

Execute block
as
declare variable curVal integer;
declare variable curID integer;
begin
  for select ID, VAL
  from TEST_TABLE
  where VAL>0
  into curID, CurVal
  as cursor TmpCursor
  do begin

    update TEST_TABLE
    set Val=0
    where current of TmpCursor;

    delete from TEST_TABLE
    where current of TmpCursor;

  -- error handler is added to force savepoints around statements inside begin\end block
   when any do
     exception;
  end
end

Probably we should add this to the engine to avoid dependence of positioned statements on presence of additional savepoints...

[ Permalink | « Hide ]

Sergey Borisov added a comment - 17/Apr/18 05:20 AM

IMHO, this is not the usual behavior. If cursor is stable and "does not see" the changes made in inner statments, then must be the "lock conflict" on last delete statement.
With the current implementation, statement DELETE remove row, which no exists.

[ Permalink | « Hide ]

Vlad Khorsun added a comment - 17/Apr/18 06:47 AM

> IMHO, this is not the usual behavior.
What is "usual" behavior ?
Reference on standard would be the best hint ;)

Firebird 2.5, for example, performs both UPDATE and DELETE and row is deleted finally.

> If cursor is stable and "does not see" the changes made in inner statments, then must be the "lock conflict" on last delete statement.
There is no reason for "lock conflict" as both UPDATE and DELETE run at the same transaction.

> With the current implementation, statement DELETE remove row, which no exists.
DELETE is positioned on current row of cursor, cursor doesn't see effect of inner UPDATE (as i said above), thus cursor position is not invalidated.

I agree that positioned UPDATE\DELETE should not depend on savepoint presence, but this is a bit different question.

[ Permalink | « Hide ]

Sergey Borisov added a comment - 17/Apr/18 07:34 AM

Ok, we need to get used to the new behavior of the cursor.