firebird-qa/tests/bugs/gh_7713_test.py

#coding:utf-8

"""
ID:          issue-7713
ISSUE:       https://github.com/FirebirdSQL/firebird/issues/7713
TITLE:       Stability of implicit cursor could be broken, if cursor's select expression is based on view with sub-query in select list, or on table with sub-query in computed field.
DESCRIPTION:
NOTES:
    [25.08.2023] pzotov
    Confirmed problem on 5.0.0.1169
    Checked on 5.0.0.1170, 4.0.4.2979 (intermediate snapshots).
"""

import pytest
from firebird.qa import *

db = db_factory()

test_script = """
    set list on;
    create table test_1 (
        id int generated by default as identity constraint test_1_pk primary key,
        name varchar(20)
    );

    create table test_2 (
        id int generated by default as identity constraint test_2_pk primary key,
        t1_id int not null,
        x int
    );
        
    alter table test_2 add constraint test_2_fk foreign key (t1_id) references test_1(id) on delete cascade on update cascade;

    create or alter view v_test_1(
        id,
        x)
    as
      select
        a.id,
        case
          when a.id > 2 then (select sum(b.x) from test_2 b where b.t1_id < a.id)
          else a.id * 10
        end x
      from test_1 a
      order by a.id
    ;

    set term ^;

    create procedure table_aaa_proc
    returns (
        id int,
        x int)
    as
    begin
      for
        select
          a.id,
          case
            when a.id > 2 then (select sum(b.x) from test_2 b where b.t1_id < a.id)
            else a.id * 10
          end x
        from
          test_1 a
        into
          :id, :x
      do
      begin
        suspend;
      end
    end
    ^
    set term ;^

    commit;

    insert into test_1 (id, name) values ( 1, '1');
    insert into test_1 (id, name) values ( 2, '2');
    insert into test_1 (id, name) values ( 3, '3');
    commit;

    --##################################################################
    
    -- All following checks must output <NULL> for 'X' column if T1_ID =3:

    set term ^;
    execute block as
        declare variable t1_id int;
        declare variable x int;
    begin
      delete from test_2 b where 0 = 0;
      for
        select
          a.id, a.x
        from
          v_test_1 a
        order by
          a.id
        into
          :t1_id, :x
      do
      begin
        insert into test_2 (t1_id, x) values (:t1_id, :x);
      end

    end
    ^
    select 'chk-1' as msg, b.t1_id, b.x from test_2 b order by b.t1_id
    ^
    rollback
    ^
    --##################################################################

    delete from test_2
    ^
    insert into test_2 (t1_id, x)
    select a.id, a.x
    from v_test_1 a
    order by a.id
    ^
    select 'chk-2' as msg, b.t1_id, b.x from test_2 b order by b.t1_id
    ^
    rollback
    ^
    --##################################################################

    alter table test_1
      add dbl_expr computed by (
        case
          when test_1.id > 2 then (select sum(b.x) from test_2 b where b.t1_id < test_1.id)
          else test_1.id * 10
        end )
    ^
    commit
    ^
    delete from test_2
    ^
    insert into test_2 (t1_id, x)
    select a.id, a.dbl_expr
      from test_1 a
    order by a.id
    ^
    select 'chk-3' as msg, b.t1_id, b.x from test_2 b order by b.t1_id
    ^
    rollback
    ^
"""

act = isql_act('db', test_script)

expected_stdout = """
    MSG                             chk-1
    T1_ID                           1
    X                               10
    MSG                             chk-1
    T1_ID                           2
    X                               20
    MSG                             chk-1
    T1_ID                           3
    X                               <null>

    MSG                             chk-2
    T1_ID                           1
    X                               10
    MSG                             chk-2
    T1_ID                           2
    X                               20
    MSG                             chk-2
    T1_ID                           3
    X                               <null>

    MSG                             chk-3
    T1_ID                           1
    X                               10
    MSG                             chk-3
    T1_ID                           2
    X                               20
    MSG                             chk-3
    T1_ID                           3
    X                               <null>
"""

@pytest.mark.version('>=3.0.12')
def test_1(act: Action):
    act.expected_stdout = expected_stdout
    act.execute(combine_output = True)
    assert act.clean_stdout == act.clean_expected_stdout