For select statement can not see any changes made in DO block

[BobyBel77]

There are many changes started from In Firebird 3.0.
One of them is "PSQL Cursor Stabilization". This is basic functionality and now for stability statement cannot see it's own changes.

I have 5 different cases with insert record in do block with different visibility in for cycle:

1. for select ... from ... into - do not see any changes
2. for execute statement 'select ... from ... ' into - see changes
3. If create view (select from ...) and after that use created view - see changes
4. If use cursor - open, fetch, ... see any changes
5. If create stored procedure with suspend evry row from select - do not see changes

You can find the SQLS:
01 - create tables, view, SP, trigger and data.
02 - 5 samples of the different cases

Regards,
Boris Belchev

01. CREATE_DB_TABLES.txt
02. TESTS.txt

[hvlad]

Case 5 doesn't corresponds to the description, actually it is copy of case 2.

As @dyemanov already said in support only the case 3 (with a view) is suspicious, all else as expected.

[aafemt]

I don't see in documentation what was the purpose for cursors to be an exception from rules?

[hvlad]

I don't see in documentation what was the purpose for cursors to be an exception from rules?

It is impossible to detect if given SQL operator is executed within the scope of the explicit cursor, or no.
Thus it is impossible to conclude if cursor should see changes by that operator or no.

[hvlad]

Issue with view is related with a way of how engine handles view with subqueries in select list.
Looking for correct fix.

[hvlad]

A bit more test cases, based on metadata above.

1. Stable (no view, select with sub-query)

delete from TABLE_BBB;
insert into TABLE_BBB (AAA_ID, DBL)
select A.ID,
       case when A.ID > 2 then (select sum(B.DBL) from TABLE_BBB B where B.AAA_ID < A.ID)
            else A.ID * 10
       end DBL
  from TABLE_AAA A
order by A.ID;

2. Not stable (view with sub-query)

delete from TABLE_BBB;
insert into TABLE_BBB (AAA_ID, DBL)
select A.ID, A.DBL
  from TABLE_AAA_VIEW A
order by A.ID;

3. Stable (view with join and group by, no subquery)

CREATE OR ALTER VIEW TABLE_AAA_VIEW_J(ID,  DBL)
AS
select A.ID,
       case when A.ID > 2 then sum(B.DBL) else A.ID * 10 end DBL
  from TABLE_AAA A left join TABLE_BBB B on B.AAA_ID < A.ID
group by A.ID;

delete from TABLE_BBB;
insert into TABLE_BBB (AAA_ID, DBL)
select A.ID, A.DBL
  from TABLE_AAA_VIEW_J A
order by A.ID;

4. Not stable (use computed field with sub-query, no view)

alter table TABLE_AAA
  add dbl_expr computed by (
    case
      when TABLE_AAA.ID > 2 then (select sum(B.DBL) from TABLE_BBB B where B.AAA_ID < TABLE_AAA.ID)
      else TABLE_AAA.ID * 10
    end );
commit

delete from TABLE_BBB;
insert into TABLE_BBB (AAA_ID, DBL)
select A.ID, A.dbl_expr
  from TABLE_AAA A
order by A.ID;