Issue Details (XML | Word | Printable)

Key: CORE-5794
Type: Bug Bug
Status: Closed Closed
Resolution: Won't Fix
Priority: Major Major
Assignee: Unassigned
Reporter: Sergey Borisov
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
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: Ascending order - Click to sort in descending order
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

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...

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.

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.

Sergey Borisov added a comment - 17/Apr/18 07:34 AM
Ok, we need to get used to the new behavior of the cursor.