Question about "For select"

[Boris Belchev]

Hi,

I have simple for select statement:

execute block
as
declare variable AAA_ID integer;
declare variable DBL double precision;
begin
  for
    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 as DBL
    from
      TABLE_AAA A
    order by
      A.ID
    into
      :AAA_ID, :DBL
  do
  begin
    insert into TABLE_BBB (AAA_ID, DBL)
    values (:AAA_ID, :DBL);
  end
end

If change the select with execute statement ' select ...'   for select working like cursors, 

For example:

In table AAA I have 10 records (ID = 1, ...., ID = 10), in BBB I have 0

When execute this block, in BBB was inserted 10 records, for every row in AAA I have value:

for ID=1 -> DBL=10

ID = 2 -> 20, 

ID > 2 ???? DBL is 0!!!

In the Firebird 2.5 for ID=2 DBL is 10 + 20 = 30, ID = 3 -> 60 ...

If change select with execute statement the for select FB40 working like FB 2.5.

I donn'r know the option for change this.

Is this behavior correct?

Regards,

Boris Belchev

[Dimitry Sibiryakov]

'Boris Belchev' via firebird-support wrote 16.08.2023 16:52:
> I donn'r know the option for change this.
> Is this behavior correct?

Yes. Cursor stability rules require running cursor (FOR SELECT) not to see
any changes that were done inside of DO block (or anywhere else at all for
Firebird 4).
I.e. a statement cannot see it's own changes.
It was a well-known bug in pre-3 versions that INSERT-SELECT could fall into
an endless loop.

--
WBR, SD.

[Mark Rotteveel]

See also the Firebird 3.0 Release Notes:

-
https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb30-psql-cursor-stablility
-
https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb30-compat-cursorstability

Mark
--
Mark Rotteveel

[Boris Belchev]

Hi,

I know about the endless loop, but:

1. only for select .... from  ... into - when use table I can not see changes made in the body of cycle (in do block).

2. When use view (create view with select which I want to use in for cycle (create view XXX (ID, DBL)  as select  .. from ) and in for statement use this view for select .... from XXX into ... - its working as previous version (every changes in do statement are visible)

3. When convert to "for execute statement 'select ... from ....' into - its work as previous version (every changes are visible)

4. When use cursor for this select - its work as previous version (every changes are visible)

You can see the samples of the different cases in my video:

https://youtu.be/Fm_4lsg1Sy4

[Dimitry Sibiryakov]

'Boris Belchev' via firebird-support wrote 17.08.2023 10:10:
> 2. When use view (create view with select which I want to use in for cycle
> (create view XXX (ID, DBL)  as select  .. from ) and in for statement use this
> view for select .... from XXX into ... - its working as previous version (every
> changes in do statement are visible)
> 3. When convert to "for execute statement 'select ... from ....' into - its work
> as previous version (every changes are visible)
> 4. When use cursor for this select - its work as previous version (every changes
> are visible)

These sounds as bugs. Could you provide reproducible examples, please?

--
WBR, SD.

[Boris Belchev]

You can see the video: https://youtu.be/Fm_4lsg1Sy4

This is my scripts

CREATE TABLE TABLE_AAA (
    ID INTEGER NOT NULL,
    NAME VARCHAR(20));
   
ALTER TABLE TABLE_AAA ADD CONSTRAINT PK_TABLE_AAA PRIMARY KEY (ID);

CREATE TABLE TABLE_BBB (
    ID INTEGER NOT NULL,
    AAA_ID integer NOT NULL,
    DBL DOUBLE PRECISION);
   
ALTER TABLE TABLE_BBB ADD CONSTRAINT PK_TABLE_BBB PRIMARY KEY (ID);

ALTER TABLE TABLE_BBB ADD CONSTRAINT FK_TABLE_BBB_AAA FOREIGN KEY (AAA_ID) REFERENCES TABLE_AAA(ID) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TRIGGER TABLE_BBB_BI0 FOR TABLE_BBB
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  new.ID = (select max(ID) from TABLE_BBB) + 1;
  if (new.ID is null) then
    new.ID = 1;
end;


CREATE OR ALTER VIEW TABLE_AAA_VIEW(
    ID,
    DBL)
AS

  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

;

create procedure TABLE_AAA_PROC
returns (
    ID integer,
    DBL double precision)
as

begin
  for
    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
    into
      :ID, :DBL
  do
  begin
    suspend;
  end
end



commit work;

insert into TABLE_AAA (ID, NAME) values ( 1, '1');
insert into TABLE_AAA (ID, NAME) values ( 2, '2');
insert into TABLE_AAA (ID, NAME) values ( 3, '3');
insert into TABLE_AAA (ID, NAME) values ( 4, '4');
insert into TABLE_AAA (ID, NAME) values ( 5, '5');
insert into TABLE_AAA (ID, NAME) values ( 6, '6');
insert into TABLE_AAA (ID, NAME) values ( 7, '7');
insert into TABLE_AAA (ID, NAME) values ( 8, '8');
insert into TABLE_AAA (ID, NAME) values ( 9, '9');
insert into TABLE_AAA (ID, NAME) values (10, '10');




commit work;




SQL 1: not see changes in do block

execute block
as
declare variable AAA_ID integer;
declare variable DBL double precision;
begin

  delete from TABLE_BBB b where 0 = 0;

  for
    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
    into
      :AAA_ID, :DBL
  do
  begin
    insert into TABLE_BBB (AAA_ID, DBL) values (:AAA_ID, :DBL);
  end

end

SQL2: Using VIEW

execute block
as
declare variable AAA_ID integer;
declare variable DBL double precision;
begin

  delete from TABLE_BBB b where 0 = 0;
  for
    select
      A.ID, A.DBL
    from
      TABLE_AAA_VIEW A

    order by
      A.ID
    into
      :AAA_ID, :DBL
  do
  begin
    insert into TABLE_BBB (AAA_ID, DBL) values (:AAA_ID, :DBL);
  end

end

SQL 3: execute statement

execute block
as
declare variable AAA_ID integer;
declare variable DBL double precision;
begin

  delete from TABLE_BBB b where 0 = 0;
  for
    execute statement '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'
    into
      :AAA_ID, :DBL
  do
  begin
    insert into TABLE_BBB (AAA_ID, DBL) values (:AAA_ID, :DBL);
  end

end

SQL 4: using cursor

execute block
as
declare variable AAA_ID integer;
declare variable DBL double precision;

declare variable RC smallint;

declare A_CURSOR cursor for
  (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);
begin
  delete from TABLE_BBB b where 0 = 0;

  open A_CURSOR;
  fetch A_CURSOR
  into :AAA_ID, :DBL;
  if (row_count = 0) then
    RC = 0;
  else
    RC = 1;
  while (RC = 1) do

  begin
    insert into TABLE_BBB (AAA_ID, DBL) values (:AAA_ID, :DBL);

    fetch A_CURSOR
    into :AAA_ID, :DBL;
    if (row_count = 0) then
      RC = 0;
    else
      RC = 1;
  end

end

[Dimitry Sibiryakov]

'Boris Belchev' via firebird-support wrote 17.08.2023 11:31:
> You can see the video: https://youtu.be/Fm_4lsg1Sy4

Case with SP is doubtful because of SUSPEND, but the rest IMHO is a
definitely a bug. Create an issue in tracker, please.

--
WBR, SD.

[Dmitry Yemanov]

Select from EXECUTE STATEMENT does not enforce cursor stability and it's
already registered in the tracker.

Cursor stability was intentionally ignored for PSQL cursors, so this is
no a bug.

Only the view example is questionable.


Dmitry

[Boris Belchev]

https://github.com/FirebirdSQL/firebird/issues/7713