Show firebird.log [FOUND messages for interval when this test was running]
Jump to: output_first_mismatch    outcomes_full_history    elapsed_time_chart
Show cross-report outcomes.

Annotation type Annotation details
2 @message
assert   
    1000
    select txt_short from test a01 order by id
    Must NOT use refetch because length of non-key column is less than threshold
    Select Expression
    ....-> Sort (record length: 1036, key length: 8)
    ........-> Table "TEST" as "A01" Full Scan
    1010
    select txt_broad from test a02 order by id
    MUST use refetch because length of non-key column is greater than threshold
    Select Expression
    ....-> Refetch
    ........-> Sort (record length: 28, key length: 8)
    ............-> Table "TEST" as "A02" Full Scan
    1020
    select txt_short from test a03 order by id rows 1
    MUST use refetch regardless on length of column because ROWS <N> presents
    Select Expression
    ....-> First N Records
    ........-> Refetch
    ............-> Sort (record length: 28, key length: 8)
    ................-> Table "TEST" as "A03" Full Scan
    2000
    select id, computed_ts_dup from test order by id
    Must NOT use refetch because computed column is based on txt_short with length < threshold
    Select Expression
    ....-> Sort (record length: 1036, key length: 8)
    ........-> Table "TEST" Full Scan
    2010
    select id, computed_tb_dup from test order by id
    MUST use refetch because computed column is based on txt_broad which has length >= threshold
    Select Expression
    ....-> Refetch
    ........-> Sort (record length: 28, key length: 8)
    ............-> Table "TEST" Full Scan
    3000
    select id from test a04 where '' in (select txt_short from test x04 where txt_short = '' order by id)
    *** not [yet] commented ***
    Sub-query (invariant)
    ....-> Filter
    ........-> Sort (record length: 1036, key length: 8)
    ............-> Filter
    ................-> Table "TEST" as "X04" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A04" Full Scan
    3010
    select id from test a05 where '' in (select txt_broad from test x05 where txt_broad = '' order by id)
    *** not [yet] commented ***
    Sub-query (invariant)
    ....-> Filter
    ........-> Refetch
    ............-> Sort (record length: 28, key length: 8)
    ................-> Filter
    ....................-> Table "TEST" as "X05" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A05" Full Scan
    3020
    select id from test a06 where '' not in (select txt_short from test x06 where txt_short>'' order by id)
    *** not [yet] commented ***
  - Sub-query (invariant)
  + Select Expression
    ....-> Sort (record length: 1036, key length: 8)
    ........-> Filter
    ............-> Table "TEST" as "X06" Full Scan
  - Sub-query (invariant)
  + Select Expression
    ....-> Sort (record length: 1036, key length: 8)
    ........-> Filter
    ............-> Table "TEST" as "X06" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A06" Full Scan
    3030
    select id from test a07 where '' not in (select txt_broad from test x07 where txt_broad>'' order by id)
    *** not [yet] commented ***
  - Sub-query (invariant)
  + Select Expression
    ....-> Refetch
    ........-> Sort (record length: 28, key length: 8)
    ............-> Filter
    ................-> Table "TEST" as "X07" Full Scan
  - Sub-query (invariant)
  + Select Expression
    ....-> Refetch
    ........-> Sort (record length: 28, key length: 8)
    ............-> Filter
    ................-> Table "TEST" as "X07" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A07" Full Scan
    3040
    select id from test a08 where '' > all (select id from test x08 where txt_short>'' order by id)
    *** not [yet] commented ***
    Sub-query (invariant)
    ....-> Filter
    ........-> Sort (record length: 1036, key length: 8)
    ............-> Filter
    ................-> Table "TEST" as "X08" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A08" Full Scan
    3050
    select id from test a09 where '' > all (select id from test x09 where txt_broad>'' order by id)
    *** not [yet] commented ***
    Sub-query (invariant)
    ....-> Filter
    ........-> Refetch
    ............-> Sort (record length: 28, key length: 8)
    ................-> Filter
    ....................-> Table "TEST" as "X09" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A09" Full Scan
    3060
    select id from test a10 where '' <> any (select id from test x10 where txt_short>'' order by id)
    *** not [yet] commented ***
    Sub-query (invariant)
    ....-> Filter
    ........-> Sort (record length: 1036, key length: 8)
    ............-> Filter
    ................-> Table "TEST" as "X10" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A10" Full Scan
    3070
    select id from test a11 where '' <> any (select id from test x11 where txt_broad>'' order by id)
    *** not [yet] commented ***
    Sub-query (invariant)
    ....-> Filter
    ........-> Refetch
    ............-> Sort (record length: 28, key length: 8)
    ................-> Filter
    ....................-> Table "TEST" as "X11" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A11" Full Scan
    4000
    select id,txt_short from test a12 where exists(select 1 from test x12 where txt_short>'' order by id)
    MUST use refetch: column x12.txt_short not present in order by
    Sub-query (invariant)
    ....-> Refetch
    ........-> Sort (record length: 28, key length: 8)
    ............-> Filter
    ................-> Table "TEST" as "X12" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A12" Full Scan
    4010
    select id,txt_short from test a13 where exists(select 1 from test x13 where computed_id_dup > 0  order by id)
    Must NOT use refetch: ORDER BY list contains the single element: ID, and it is base for x13.computed_id_dup column
    Sub-query (invariant)
    ....-> Sort (record length: 28, key length: 8)
    ........-> Filter
    ............-> Table "TEST" as "X13" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A13" Full Scan
    4020
    select id,txt_short from test a14 where exists(select 1 from test x14 where computed_id_dup > 0  order by computed_id_dup)
    MUST use refetch! See letter from dimitr 28.12.2020 14:49
    Sort procedure will get:
    a KEY = result of evaluating 'computed_id_dup';
    a VAL = value of the field 'ID' which is base for computing 'computed_id_dup'
    Thus sorter will have a field which not equals to a key, which leads to refetch.
    Sub-query (invariant)
    ....-> Refetch
    ........-> Sort (record length: 36, key length: 12)
    ............-> Filter
    ................-> Table "TEST" as "X14" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A14" Full Scan
    4030
    select id,txt_short from test a15 where exists(select 1 from test x15 where f02>0 and f01>0 order by f01, f02)
    Must NOT use refetch: all persistent columns from WHERE expression (f01, f02) belong to ORDER BY list
    Sub-query (invariant)
    ....-> Sort (record length: 36, key length: 16)
    ........-> Filter
    ............-> Table "TEST" as "X15" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A15" Full Scan
    4040
    select id,txt_short from test a16 where exists(select 1 from test x16 where id>0 and f01>0 order by f01, f02)
    Must use refetch: one of columns from WHERE expr (id) does not belong to ORDER BY list
    Sub-query (invariant)
    ....-> Refetch
    ........-> Sort (record length: 36, key length: 16)
    ............-> Filter
    ................-> Table "TEST" as "X16" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A16" Full Scan
    4050
    select id,txt_short from test a17 where exists(select 1 from test x17 where computed_id_dup > 0 order by f01)
    Must use refetch: computed column in WHERE expr does not belong to ORDER BY list
    Sub-query (invariant)
    ....-> Refetch
    ........-> Sort (record length: 28, key length: 8)
    ............-> Filter
    ................-> Table "TEST" as "X17" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A17" Full Scan
    4060
    select id,txt_short from test a18 where exists(select 1 from test x18 where computed_guid > '' order by f01)
    Must NOT use refetch: computed column x18.computed_guid does is evaluated via GUID and does not refer to any columns
    Sub-query (invariant)
    ....-> Sort (record length: 28, key length: 8)
    ........-> Filter
    ............-> Table "TEST" as "X18" Full Scan
    Select Expression
    ....-> Filter (preliminary)
    ........-> Table "TEST" as "A18" Full Scan
    4070
    with recursive
    r as (
    select a19.id, a19.txt_short
    from test a19
    where not exists(select * from test x where x.txt_short < a19.txt_short order by id)
    UNION ALL
    select i.id, i.txt_short
    from test i
    join r on i.id > r.id
    and not exists( select * from test x where x.txt_short between r.txt_short and i.txt_short order by id )
    )
    select * from r
    MUST use refetch both in anchor and recursive parts
    Sub-query
    ....-> Refetch
    ........-> Sort (record length: 28, key length: 8)
    ............-> Filter
    ................-> Table "TEST" as "R X" Full Scan
    Sub-query
    ....-> Refetch
    ........-> Sort (record length: 28, key length: 8)
    ............-> Filter
    ................-> Table "TEST" as "R X" Full Scan
    Select Expression
    ....-> Recursion
    ........-> Filter
    ............-> Table "TEST" as "R A19" Full Scan
    ........-> Filter
    ............-> Table "TEST" as "R I" Full Scan
    5000
    select txt_broad from v_unioned v01 order by id
    Must NOT use refetch because view DDL includes UNION
    Select Expression
    ....-> Sort (record length: 4044, key length: 8)
    ........-> First N Records
    ............-> Union
    ................-> Table "TEST" as "V01 TEST" Full Scan
    ................-> Table "RDB$DATABASE" as "V01 RDB$DATABASE" Full Scan
    6000
    select left(txt_broad, 50) as txt from test a21 order by id
    MUST use refetch because expression is based on column which has length >= threshold
    (even if final length of expression result is much less than threshold)
    Select Expression
    ....-> Refetch
    ........-> Sort (record length: 28, key length: 8)
    ............-> Table "TEST" as "A21" Full Scan
    6010
    select left( txt_short || txt_short, 2000) as txt from test a22 order by id
    Must NOT use refetch because expression is based on column which has length < threshold
    (even if final length of expression result is much bigger than threshold)
    Select Expression
    ....-> Sort (record length: 1036, key length: 8)
    ........-> Table "TEST" as "A22" Full Scan
    7000
    select * from test_ns_01 a23 order by id
    MUST use refetch
    Select Expression
    ....-> Refetch
    ........-> Sort (record length: 44, key length: 24)
    ............-> Table "TEST_NS_01" as "A23" Full Scan
    7010
    select * from test_ns_02 a24 order by id
    Must NOT refetch
    Select Expression
    ....-> Sort (record length: 1052, key length: 24)
    ........-> Table "TEST_NS_02" as "A24" Full Scan
    7020
    select * from test_ns_03 order by id
    MUST use refetch
    Select Expression
    ....-> Refetch
    ........-> Sort (record length: 36, key length: 12)
    ............-> Table "TEST_NS_03" Full Scan
    7030
    select * from test_ns_04 order by id
    Must NOT use refetch
    Select Expression
    ....-> Sort (record length: 1036, key length: 12)
    ........-> Table "TEST_NS_04" Full Scan
    7040
    select * from test_ns_05 order by id
    MUST use refetch
    Select Expression
    ....-> Refetch
    ........-> Sort (record length: 36, key length: 12)
    ............-> Table "TEST_NS_05" Full Scan
    7050
    select * from test_ns_06 order by id
    Must NOT use refetch
    Select Expression
    ....-> Sort (record length: 1036, key length: 12)
    ........-> Table "TEST_NS_06" Full Scan

LOG DETAILS:

2025-02-14 09:37:03.802
2025-02-14 09:37:03.803 act = <firebird.qa.plugin.Action object at [hex]>
2025-02-14 09:37:03.803 capsys = <_pytest.capture.CaptureFixture object at [hex]>
2025-02-14 09:37:03.803
2025-02-14 09:37:03.803     @pytest.mark.version('>=4.0')
2025-02-14 09:37:03.803     def test_1(act: Action, capsys):
2025-02-14 09:37:03.803         with act.db.connect() as con:
2025-02-14 09:37:03.803
2025-02-14 09:37:03.803             # 13.01.2025: test will FAIL if config parameter OptimizeForFirstRows differs from default value (i.e. is set to true).
2025-02-14 09:37:03.803             # To prevent this, we have to explicitly change appropriate session-level value:
2025-02-14 09:37:03.803             if act.is_version('<5'):
2025-02-14 09:37:03.803                 pass
2025-02-14 09:37:03.803             else:
2025-02-14 09:37:03.803                 con.execute_immediate('set optimize for all rows')
2025-02-14 09:37:03.803
2025-02-14 09:37:03.803             cur = con.cursor()
2025-02-14 09:37:03.803             for q_idx, q_tuple in query_map.items():
2025-02-14 09:37:03.803                 test_sql, qry_comment = q_tuple[:2]
2025-02-14 09:37:03.803                 ps = cur.prepare(test_sql)
2025-02-14 09:37:03.803                 print(q_idx)
2025-02-14 09:37:03.804                 print(test_sql)
2025-02-14 09:37:03.804                 print(qry_comment)
2025-02-14 09:37:03.804                 print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
2025-02-14 09:37:03.804                 ps.free()
2025-02-14 09:37:03.804
2025-02-14 09:37:03.804         act.expected_stdout = fb4x_expected_out if act.is_version('<5') else fb5x_expected_out if act.is_version('<6') else fb6x_expected_out
2025-02-14 09:37:03.804         act.stdout = capsys.readouterr().out
2025-02-14 09:37:03.804 >       assert act.clean_stdout == act.clean_expected_stdout
2025-02-14 09:37:03.804 E       assert
2025-02-14 09:37:03.804 E           1000
2025-02-14 09:37:03.804 E           select txt_short from test a01 order by id
2025-02-14 09:37:03.804 E           Must NOT use refetch because length of non-key column is less than threshold
2025-02-14 09:37:03.804 E           Select Expression
2025-02-14 09:37:03.804 E           ....-> Sort (record length: 1036, key length: 8)
2025-02-14 09:37:03.804 E           ........-> Table "TEST" as "A01" Full Scan
2025-02-14 09:37:03.804 E           1010
2025-02-14 09:37:03.804 E           select txt_broad from test a02 order by id
2025-02-14 09:37:03.804 E           MUST use refetch because length of non-key column is greater than threshold
2025-02-14 09:37:03.805 E           Select Expression
2025-02-14 09:37:03.805 E           ....-> Refetch
2025-02-14 09:37:03.805 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.805 E           ............-> Table "TEST" as "A02" Full Scan
2025-02-14 09:37:03.805 E           1020
2025-02-14 09:37:03.805 E           select txt_short from test a03 order by id rows 1
2025-02-14 09:37:03.805 E           MUST use refetch regardless on length of column because ROWS <N> presents
2025-02-14 09:37:03.805 E           Select Expression
2025-02-14 09:37:03.805 E           ....-> First N Records
2025-02-14 09:37:03.805 E           ........-> Refetch
2025-02-14 09:37:03.805 E           ............-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.805 E           ................-> Table "TEST" as "A03" Full Scan
2025-02-14 09:37:03.805 E           2000
2025-02-14 09:37:03.805 E           select id, computed_ts_dup from test order by id
2025-02-14 09:37:03.805 E           Must NOT use refetch because computed column is based on txt_short with length < threshold
2025-02-14 09:37:03.805 E           Select Expression
2025-02-14 09:37:03.805 E           ....-> Sort (record length: 1036, key length: 8)
2025-02-14 09:37:03.805 E           ........-> Table "TEST" Full Scan
2025-02-14 09:37:03.805 E           2010
2025-02-14 09:37:03.806 E           select id, computed_tb_dup from test order by id
2025-02-14 09:37:03.806 E           MUST use refetch because computed column is based on txt_broad which has length >= threshold
2025-02-14 09:37:03.806 E           Select Expression
2025-02-14 09:37:03.806 E           ....-> Refetch
2025-02-14 09:37:03.806 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.806 E           ............-> Table "TEST" Full Scan
2025-02-14 09:37:03.806 E           3000
2025-02-14 09:37:03.806 E           select id from test a04 where '' in (select txt_short from test x04 where txt_short = '' order by id)
2025-02-14 09:37:03.806 E           *** not [yet] commented ***
2025-02-14 09:37:03.806 E           Sub-query (invariant)
2025-02-14 09:37:03.806 E           ....-> Filter
2025-02-14 09:37:03.806 E           ........-> Sort (record length: 1036, key length: 8)
2025-02-14 09:37:03.806 E           ............-> Filter
2025-02-14 09:37:03.806 E           ................-> Table "TEST" as "X04" Full Scan
2025-02-14 09:37:03.806 E           Select Expression
2025-02-14 09:37:03.806 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.806 E           ........-> Table "TEST" as "A04" Full Scan
2025-02-14 09:37:03.806 E           3010
2025-02-14 09:37:03.806 E           select id from test a05 where '' in (select txt_broad from test x05 where txt_broad = '' order by id)
2025-02-14 09:37:03.807 E           *** not [yet] commented ***
2025-02-14 09:37:03.807 E           Sub-query (invariant)
2025-02-14 09:37:03.807 E           ....-> Filter
2025-02-14 09:37:03.807 E           ........-> Refetch
2025-02-14 09:37:03.807 E           ............-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.807 E           ................-> Filter
2025-02-14 09:37:03.807 E           ....................-> Table "TEST" as "X05" Full Scan
2025-02-14 09:37:03.807 E           Select Expression
2025-02-14 09:37:03.807 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.807 E           ........-> Table "TEST" as "A05" Full Scan
2025-02-14 09:37:03.807 E           3020
2025-02-14 09:37:03.807 E           select id from test a06 where '' not in (select txt_short from test x06 where txt_short>'' order by id)
2025-02-14 09:37:03.807 E           *** not [yet] commented ***
2025-02-14 09:37:03.807 E         - Sub-query (invariant)
2025-02-14 09:37:03.807 E         + Select Expression
2025-02-14 09:37:03.807 E           ....-> Sort (record length: 1036, key length: 8)
2025-02-14 09:37:03.807 E           ........-> Filter
2025-02-14 09:37:03.807 E           ............-> Table "TEST" as "X06" Full Scan
2025-02-14 09:37:03.807 E         - Sub-query (invariant)
2025-02-14 09:37:03.808 E         + Select Expression
2025-02-14 09:37:03.808 E           ....-> Sort (record length: 1036, key length: 8)
2025-02-14 09:37:03.808 E           ........-> Filter
2025-02-14 09:37:03.808 E           ............-> Table "TEST" as "X06" Full Scan
2025-02-14 09:37:03.808 E           Select Expression
2025-02-14 09:37:03.808 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.808 E           ........-> Table "TEST" as "A06" Full Scan
2025-02-14 09:37:03.808 E           3030
2025-02-14 09:37:03.808 E           select id from test a07 where '' not in (select txt_broad from test x07 where txt_broad>'' order by id)
2025-02-14 09:37:03.808 E           *** not [yet] commented ***
2025-02-14 09:37:03.808 E         - Sub-query (invariant)
2025-02-14 09:37:03.808 E         + Select Expression
2025-02-14 09:37:03.808 E           ....-> Refetch
2025-02-14 09:37:03.808 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.808 E           ............-> Filter
2025-02-14 09:37:03.808 E           ................-> Table "TEST" as "X07" Full Scan
2025-02-14 09:37:03.808 E         - Sub-query (invariant)
2025-02-14 09:37:03.808 E         + Select Expression
2025-02-14 09:37:03.809 E           ....-> Refetch
2025-02-14 09:37:03.809 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.809 E           ............-> Filter
2025-02-14 09:37:03.809 E           ................-> Table "TEST" as "X07" Full Scan
2025-02-14 09:37:03.809 E           Select Expression
2025-02-14 09:37:03.809 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.809 E           ........-> Table "TEST" as "A07" Full Scan
2025-02-14 09:37:03.809 E           3040
2025-02-14 09:37:03.809 E           select id from test a08 where '' > all (select id from test x08 where txt_short>'' order by id)
2025-02-14 09:37:03.809 E           *** not [yet] commented ***
2025-02-14 09:37:03.809 E           Sub-query (invariant)
2025-02-14 09:37:03.809 E           ....-> Filter
2025-02-14 09:37:03.809 E           ........-> Sort (record length: 1036, key length: 8)
2025-02-14 09:37:03.809 E           ............-> Filter
2025-02-14 09:37:03.809 E           ................-> Table "TEST" as "X08" Full Scan
2025-02-14 09:37:03.809 E           Select Expression
2025-02-14 09:37:03.809 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.809 E           ........-> Table "TEST" as "A08" Full Scan
2025-02-14 09:37:03.810 E           3050
2025-02-14 09:37:03.810 E           select id from test a09 where '' > all (select id from test x09 where txt_broad>'' order by id)
2025-02-14 09:37:03.810 E           *** not [yet] commented ***
2025-02-14 09:37:03.810 E           Sub-query (invariant)
2025-02-14 09:37:03.810 E           ....-> Filter
2025-02-14 09:37:03.810 E           ........-> Refetch
2025-02-14 09:37:03.810 E           ............-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.810 E           ................-> Filter
2025-02-14 09:37:03.810 E           ....................-> Table "TEST" as "X09" Full Scan
2025-02-14 09:37:03.810 E           Select Expression
2025-02-14 09:37:03.810 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.810 E           ........-> Table "TEST" as "A09" Full Scan
2025-02-14 09:37:03.810 E           3060
2025-02-14 09:37:03.810 E           select id from test a10 where '' <> any (select id from test x10 where txt_short>'' order by id)
2025-02-14 09:37:03.810 E           *** not [yet] commented ***
2025-02-14 09:37:03.810 E           Sub-query (invariant)
2025-02-14 09:37:03.810 E           ....-> Filter
2025-02-14 09:37:03.810 E           ........-> Sort (record length: 1036, key length: 8)
2025-02-14 09:37:03.810 E           ............-> Filter
2025-02-14 09:37:03.810 E           ................-> Table "TEST" as "X10" Full Scan
2025-02-14 09:37:03.811 E           Select Expression
2025-02-14 09:37:03.811 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.811 E           ........-> Table "TEST" as "A10" Full Scan
2025-02-14 09:37:03.811 E           3070
2025-02-14 09:37:03.811 E           select id from test a11 where '' <> any (select id from test x11 where txt_broad>'' order by id)
2025-02-14 09:37:03.811 E           *** not [yet] commented ***
2025-02-14 09:37:03.811 E           Sub-query (invariant)
2025-02-14 09:37:03.811 E           ....-> Filter
2025-02-14 09:37:03.811 E           ........-> Refetch
2025-02-14 09:37:03.811 E           ............-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.811 E           ................-> Filter
2025-02-14 09:37:03.811 E           ....................-> Table "TEST" as "X11" Full Scan
2025-02-14 09:37:03.811 E           Select Expression
2025-02-14 09:37:03.811 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.811 E           ........-> Table "TEST" as "A11" Full Scan
2025-02-14 09:37:03.811 E           4000
2025-02-14 09:37:03.811 E           select id,txt_short from test a12 where exists(select 1 from test x12 where txt_short>'' order by id)
2025-02-14 09:37:03.812 E           MUST use refetch: column x12.txt_short not present in order by
2025-02-14 09:37:03.812 E           Sub-query (invariant)
2025-02-14 09:37:03.812 E           ....-> Refetch
2025-02-14 09:37:03.812 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.812 E           ............-> Filter
2025-02-14 09:37:03.812 E           ................-> Table "TEST" as "X12" Full Scan
2025-02-14 09:37:03.812 E           Select Expression
2025-02-14 09:37:03.812 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.812 E           ........-> Table "TEST" as "A12" Full Scan
2025-02-14 09:37:03.812 E           4010
2025-02-14 09:37:03.812 E           select id,txt_short from test a13 where exists(select 1 from test x13 where computed_id_dup > 0  order by id)
2025-02-14 09:37:03.812 E           Must NOT use refetch: ORDER BY list contains the single element: ID, and it is base for x13.computed_id_dup column
2025-02-14 09:37:03.812 E           Sub-query (invariant)
2025-02-14 09:37:03.812 E           ....-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.812 E           ........-> Filter
2025-02-14 09:37:03.812 E           ............-> Table "TEST" as "X13" Full Scan
2025-02-14 09:37:03.812 E           Select Expression
2025-02-14 09:37:03.812 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.812 E           ........-> Table "TEST" as "A13" Full Scan
2025-02-14 09:37:03.813 E           4020
2025-02-14 09:37:03.813 E           select id,txt_short from test a14 where exists(select 1 from test x14 where computed_id_dup > 0  order by computed_id_dup)
2025-02-14 09:37:03.813 E           MUST use refetch! See letter from dimitr 28.12.2020 14:49
2025-02-14 09:37:03.813 E           Sort procedure will get:
2025-02-14 09:37:03.813 E           a KEY = result of evaluating 'computed_id_dup';
2025-02-14 09:37:03.813 E           a VAL = value of the field 'ID' which is base for computing 'computed_id_dup'
2025-02-14 09:37:03.813 E           Thus sorter will have a field which not equals to a key, which leads to refetch.
2025-02-14 09:37:03.813 E           Sub-query (invariant)
2025-02-14 09:37:03.813 E           ....-> Refetch
2025-02-14 09:37:03.813 E           ........-> Sort (record length: 36, key length: 12)
2025-02-14 09:37:03.813 E           ............-> Filter
2025-02-14 09:37:03.813 E           ................-> Table "TEST" as "X14" Full Scan
2025-02-14 09:37:03.813 E           Select Expression
2025-02-14 09:37:03.813 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.813 E           ........-> Table "TEST" as "A14" Full Scan
2025-02-14 09:37:03.813 E           4030
2025-02-14 09:37:03.813 E           select id,txt_short from test a15 where exists(select 1 from test x15 where f02>0 and f01>0 order by f01, f02)
2025-02-14 09:37:03.813 E           Must NOT use refetch: all persistent columns from WHERE expression (f01, f02) belong to ORDER BY list
2025-02-14 09:37:03.813 E           Sub-query (invariant)
2025-02-14 09:37:03.814 E           ....-> Sort (record length: 36, key length: 16)
2025-02-14 09:37:03.814 E           ........-> Filter
2025-02-14 09:37:03.814 E           ............-> Table "TEST" as "X15" Full Scan
2025-02-14 09:37:03.814 E           Select Expression
2025-02-14 09:37:03.814 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.814 E           ........-> Table "TEST" as "A15" Full Scan
2025-02-14 09:37:03.814 E           4040
2025-02-14 09:37:03.814 E           select id,txt_short from test a16 where exists(select 1 from test x16 where id>0 and f01>0 order by f01, f02)
2025-02-14 09:37:03.814 E           Must use refetch: one of columns from WHERE expr (id) does not belong to ORDER BY list
2025-02-14 09:37:03.814 E           Sub-query (invariant)
2025-02-14 09:37:03.814 E           ....-> Refetch
2025-02-14 09:37:03.814 E           ........-> Sort (record length: 36, key length: 16)
2025-02-14 09:37:03.814 E           ............-> Filter
2025-02-14 09:37:03.814 E           ................-> Table "TEST" as "X16" Full Scan
2025-02-14 09:37:03.814 E           Select Expression
2025-02-14 09:37:03.814 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.814 E           ........-> Table "TEST" as "A16" Full Scan
2025-02-14 09:37:03.814 E           4050
2025-02-14 09:37:03.814 E           select id,txt_short from test a17 where exists(select 1 from test x17 where computed_id_dup > 0 order by f01)
2025-02-14 09:37:03.815 E           Must use refetch: computed column in WHERE expr does not belong to ORDER BY list
2025-02-14 09:37:03.815 E           Sub-query (invariant)
2025-02-14 09:37:03.815 E           ....-> Refetch
2025-02-14 09:37:03.815 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.815 E           ............-> Filter
2025-02-14 09:37:03.815 E           ................-> Table "TEST" as "X17" Full Scan
2025-02-14 09:37:03.815 E           Select Expression
2025-02-14 09:37:03.815 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.815 E           ........-> Table "TEST" as "A17" Full Scan
2025-02-14 09:37:03.815 E           4060
2025-02-14 09:37:03.815 E           select id,txt_short from test a18 where exists(select 1 from test x18 where computed_guid > '' order by f01)
2025-02-14 09:37:03.815 E           Must NOT use refetch: computed column x18.computed_guid does is evaluated via GUID and does not refer to any columns
2025-02-14 09:37:03.815 E           Sub-query (invariant)
2025-02-14 09:37:03.815 E           ....-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.815 E           ........-> Filter
2025-02-14 09:37:03.815 E           ............-> Table "TEST" as "X18" Full Scan
2025-02-14 09:37:03.815 E           Select Expression
2025-02-14 09:37:03.815 E           ....-> Filter (preliminary)
2025-02-14 09:37:03.815 E           ........-> Table "TEST" as "A18" Full Scan
2025-02-14 09:37:03.816 E           4070
2025-02-14 09:37:03.816 E           with recursive
2025-02-14 09:37:03.816 E           r as (
2025-02-14 09:37:03.816 E           select a19.id, a19.txt_short
2025-02-14 09:37:03.816 E           from test a19
2025-02-14 09:37:03.816 E           where not exists(select * from test x where x.txt_short < a19.txt_short order by id)
2025-02-14 09:37:03.816 E           UNION ALL
2025-02-14 09:37:03.816 E           select i.id, i.txt_short
2025-02-14 09:37:03.816 E           from test i
2025-02-14 09:37:03.816 E           join r on i.id > r.id
2025-02-14 09:37:03.816 E           and not exists( select * from test x where x.txt_short between r.txt_short and i.txt_short order by id )
2025-02-14 09:37:03.816 E           )
2025-02-14 09:37:03.816 E           select * from r
2025-02-14 09:37:03.816 E           MUST use refetch both in anchor and recursive parts
2025-02-14 09:37:03.816 E           Sub-query
2025-02-14 09:37:03.816 E           ....-> Refetch
2025-02-14 09:37:03.816 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.816 E           ............-> Filter
2025-02-14 09:37:03.816 E           ................-> Table "TEST" as "R X" Full Scan
2025-02-14 09:37:03.817 E           Sub-query
2025-02-14 09:37:03.817 E           ....-> Refetch
2025-02-14 09:37:03.817 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.817 E           ............-> Filter
2025-02-14 09:37:03.817 E           ................-> Table "TEST" as "R X" Full Scan
2025-02-14 09:37:03.817 E           Select Expression
2025-02-14 09:37:03.817 E           ....-> Recursion
2025-02-14 09:37:03.817 E           ........-> Filter
2025-02-14 09:37:03.817 E           ............-> Table "TEST" as "R A19" Full Scan
2025-02-14 09:37:03.817 E           ........-> Filter
2025-02-14 09:37:03.817 E           ............-> Table "TEST" as "R I" Full Scan
2025-02-14 09:37:03.817 E           5000
2025-02-14 09:37:03.817 E           select txt_broad from v_unioned v01 order by id
2025-02-14 09:37:03.817 E           Must NOT use refetch because view DDL includes UNION
2025-02-14 09:37:03.817 E           Select Expression
2025-02-14 09:37:03.817 E           ....-> Sort (record length: 4044, key length: 8)
2025-02-14 09:37:03.817 E           ........-> First N Records
2025-02-14 09:37:03.817 E           ............-> Union
2025-02-14 09:37:03.817 E           ................-> Table "TEST" as "V01 TEST" Full Scan
2025-02-14 09:37:03.817 E           ................-> Table "RDB$DATABASE" as "V01 RDB$DATABASE" Full Scan
2025-02-14 09:37:03.818 E           6000
2025-02-14 09:37:03.818 E           select left(txt_broad, 50) as txt from test a21 order by id
2025-02-14 09:37:03.818 E           MUST use refetch because expression is based on column which has length >= threshold
2025-02-14 09:37:03.818 E           (even if final length of expression result is much less than threshold)
2025-02-14 09:37:03.818 E           Select Expression
2025-02-14 09:37:03.818 E           ....-> Refetch
2025-02-14 09:37:03.818 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 09:37:03.818 E           ............-> Table "TEST" as "A21" Full Scan
2025-02-14 09:37:03.818 E           6010
2025-02-14 09:37:03.818 E           select left( txt_short || txt_short, 2000) as txt from test a22 order by id
2025-02-14 09:37:03.818 E           Must NOT use refetch because expression is based on column which has length < threshold
2025-02-14 09:37:03.818 E           (even if final length of expression result is much bigger than threshold)
2025-02-14 09:37:03.818 E           Select Expression
2025-02-14 09:37:03.818 E           ....-> Sort (record length: 1036, key length: 8)
2025-02-14 09:37:03.818 E           ........-> Table "TEST" as "A22" Full Scan
2025-02-14 09:37:03.818 E           7000
2025-02-14 09:37:03.818 E           select * from test_ns_01 a23 order by id
2025-02-14 09:37:03.818 E           MUST use refetch
2025-02-14 09:37:03.818 E           Select Expression
2025-02-14 09:37:03.819 E           ....-> Refetch
2025-02-14 09:37:03.819 E           ........-> Sort (record length: 44, key length: 24)
2025-02-14 09:37:03.819 E           ............-> Table "TEST_NS_01" as "A23" Full Scan
2025-02-14 09:37:03.819 E           7010
2025-02-14 09:37:03.819 E           select * from test_ns_02 a24 order by id
2025-02-14 09:37:03.819 E           Must NOT refetch
2025-02-14 09:37:03.819 E           Select Expression
2025-02-14 09:37:03.819 E           ....-> Sort (record length: 1052, key length: 24)
2025-02-14 09:37:03.819 E           ........-> Table "TEST_NS_02" as "A24" Full Scan
2025-02-14 09:37:03.819 E           7020
2025-02-14 09:37:03.819 E           select * from test_ns_03 order by id
2025-02-14 09:37:03.819 E           MUST use refetch
2025-02-14 09:37:03.819 E           Select Expression
2025-02-14 09:37:03.819 E           ....-> Refetch
2025-02-14 09:37:03.819 E           ........-> Sort (record length: 36, key length: 12)
2025-02-14 09:37:03.819 E           ............-> Table "TEST_NS_03" Full Scan
2025-02-14 09:37:03.819 E           7030
2025-02-14 09:37:03.819 E           select * from test_ns_04 order by id
2025-02-14 09:37:03.819 E           Must NOT use refetch
2025-02-14 09:37:03.820 E           Select Expression
2025-02-14 09:37:03.820 E           ....-> Sort (record length: 1036, key length: 12)
2025-02-14 09:37:03.820 E           ........-> Table "TEST_NS_04" Full Scan
2025-02-14 09:37:03.820 E           7040
2025-02-14 09:37:03.820 E           select * from test_ns_05 order by id
2025-02-14 09:37:03.820 E           MUST use refetch
2025-02-14 09:37:03.820 E           Select Expression
2025-02-14 09:37:03.820 E           ....-> Refetch
2025-02-14 09:37:03.820 E           ........-> Sort (record length: 36, key length: 12)
2025-02-14 09:37:03.820 E           ............-> Table "TEST_NS_05" Full Scan
2025-02-14 09:37:03.820 E           7050
2025-02-14 09:37:03.820 E           select * from test_ns_06 order by id
2025-02-14 09:37:03.820 E           Must NOT use refetch
2025-02-14 09:37:03.820 E           Select Expression
2025-02-14 09:37:03.820 E           ....-> Sort (record length: 1036, key length: 12)
2025-02-14 09:37:03.820 E           ........-> Table "TEST_NS_06" Full Scan
2025-02-14 09:37:03.820
2025-02-14 09:37:03.820 tests\bugs\core_2650_test.py:1199: AssertionError
2025-02-14 09:37:03.821 ---------------------------- Captured stdout setup ----------------------------
2025-02-14 09:37:03.821 Creating db: localhost:H:\QA\temp\qa2024.tmp\fbqa\test_1442\test.fdb [page_size=None, sql_dialect=None, charset='NONE', user=SYSDBA, password=masterkey]
3 #text
act = <firebird.qa.plugin.Action pytest object at [hex]>
capsys = <_pytest.capture.CaptureFixture pytest object at [hex]>

    @pytest.mark.version('>=4.0')
    def test_1(act: Action, capsys):
        with act.db.connect() as con:
    
            # 13.01.2025: test will FAIL if config parameter OptimizeForFirstRows differs from default value (i.e. is set to true).
            # To prevent this, we have to explicitly change appropriate session-level value:
            if act.is_version('<5'):
                pass
            else:
                con.execute_immediate('set optimize for all rows')
    
            cur = con.cursor()
            for q_idx, q_tuple in query_map.items():
                test_sql, qry_comment = q_tuple[:2]
                ps = cur.prepare(test_sql)
                print(q_idx)
                print(test_sql)
                print(qry_comment)
                print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
                ps.free()
    
        act.expected_stdout = fb4x_expected_out if act.is_version('<5') else fb5x_expected_out if act.is_version('<6') else fb6x_expected_out
        act.stdout = capsys.readouterr().out
>       assert act.clean_stdout == act.clean_expected_stdout
E       assert   
E           1000
E           select txt_short from test a01 order by id
E           Must NOT use refetch because length of non-key column is less than threshold
E           Select Expression
E           ....-> Sort (record length: 1036, key length: 8)
E           ........-> Table "TEST" as "A01" Full Scan
E           1010
E           select txt_broad from test a02 order by id
E           MUST use refetch because length of non-key column is greater than threshold
E           Select Expression
E           ....-> Refetch
E           ........-> Sort (record length: 28, key length: 8)
E           ............-> Table "TEST" as "A02" Full Scan
E           1020
E           select txt_short from test a03 order by id rows 1
E           MUST use refetch regardless on length of column because ROWS <N> presents
E           Select Expression
E           ....-> First N Records
E           ........-> Refetch
E           ............-> Sort (record length: 28, key length: 8)
E           ................-> Table "TEST" as "A03" Full Scan
E           2000
E           select id, computed_ts_dup from test order by id
E           Must NOT use refetch because computed column is based on txt_short with length < threshold
E           Select Expression
E           ....-> Sort (record length: 1036, key length: 8)
E           ........-> Table "TEST" Full Scan
E           2010
E           select id, computed_tb_dup from test order by id
E           MUST use refetch because computed column is based on txt_broad which has length >= threshold
E           Select Expression
E           ....-> Refetch
E           ........-> Sort (record length: 28, key length: 8)
E           ............-> Table "TEST" Full Scan
E           3000
E           select id from test a04 where '' in (select txt_short from test x04 where txt_short = '' order by id)
E           *** not [yet] commented ***
E           Sub-query (invariant)
E           ....-> Filter
E           ........-> Sort (record length: 1036, key length: 8)
E           ............-> Filter
E           ................-> Table "TEST" as "X04" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A04" Full Scan
E           3010
E           select id from test a05 where '' in (select txt_broad from test x05 where txt_broad = '' order by id)
E           *** not [yet] commented ***
E           Sub-query (invariant)
E           ....-> Filter
E           ........-> Refetch
E           ............-> Sort (record length: 28, key length: 8)
E           ................-> Filter
E           ....................-> Table "TEST" as "X05" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A05" Full Scan
E           3020
E           select id from test a06 where '' not in (select txt_short from test x06 where txt_short>'' order by id)
E           *** not [yet] commented ***
E         - Sub-query (invariant)
E         + Select Expression
E           ....-> Sort (record length: 1036, key length: 8)
E           ........-> Filter
E           ............-> Table "TEST" as "X06" Full Scan
E         - Sub-query (invariant)
E         + Select Expression
E           ....-> Sort (record length: 1036, key length: 8)
E           ........-> Filter
E           ............-> Table "TEST" as "X06" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A06" Full Scan
E           3030
E           select id from test a07 where '' not in (select txt_broad from test x07 where txt_broad>'' order by id)
E           *** not [yet] commented ***
E         - Sub-query (invariant)
E         + Select Expression
E           ....-> Refetch
E           ........-> Sort (record length: 28, key length: 8)
E           ............-> Filter
E           ................-> Table "TEST" as "X07" Full Scan
E         - Sub-query (invariant)
E         + Select Expression
E           ....-> Refetch
E           ........-> Sort (record length: 28, key length: 8)
E           ............-> Filter
E           ................-> Table "TEST" as "X07" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A07" Full Scan
E           3040
E           select id from test a08 where '' > all (select id from test x08 where txt_short>'' order by id)
E           *** not [yet] commented ***
E           Sub-query (invariant)
E           ....-> Filter
E           ........-> Sort (record length: 1036, key length: 8)
E           ............-> Filter
E           ................-> Table "TEST" as "X08" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A08" Full Scan
E           3050
E           select id from test a09 where '' > all (select id from test x09 where txt_broad>'' order by id)
E           *** not [yet] commented ***
E           Sub-query (invariant)
E           ....-> Filter
E           ........-> Refetch
E           ............-> Sort (record length: 28, key length: 8)
E           ................-> Filter
E           ....................-> Table "TEST" as "X09" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A09" Full Scan
E           3060
E           select id from test a10 where '' <> any (select id from test x10 where txt_short>'' order by id)
E           *** not [yet] commented ***
E           Sub-query (invariant)
E           ....-> Filter
E           ........-> Sort (record length: 1036, key length: 8)
E           ............-> Filter
E           ................-> Table "TEST" as "X10" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A10" Full Scan
E           3070
E           select id from test a11 where '' <> any (select id from test x11 where txt_broad>'' order by id)
E           *** not [yet] commented ***
E           Sub-query (invariant)
E           ....-> Filter
E           ........-> Refetch
E           ............-> Sort (record length: 28, key length: 8)
E           ................-> Filter
E           ....................-> Table "TEST" as "X11" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A11" Full Scan
E           4000
E           select id,txt_short from test a12 where exists(select 1 from test x12 where txt_short>'' order by id)
E           MUST use refetch: column x12.txt_short not present in order by
E           Sub-query (invariant)
E           ....-> Refetch
E           ........-> Sort (record length: 28, key length: 8)
E           ............-> Filter
E           ................-> Table "TEST" as "X12" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A12" Full Scan
E           4010
E           select id,txt_short from test a13 where exists(select 1 from test x13 where computed_id_dup > 0  order by id)
E           Must NOT use refetch: ORDER BY list contains the single element: ID, and it is base for x13.computed_id_dup column
E           Sub-query (invariant)
E           ....-> Sort (record length: 28, key length: 8)
E           ........-> Filter
E           ............-> Table "TEST" as "X13" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A13" Full Scan
E           4020
E           select id,txt_short from test a14 where exists(select 1 from test x14 where computed_id_dup > 0  order by computed_id_dup)
E           MUST use refetch! See letter from dimitr 28.12.2020 14:49
E           Sort procedure will get:
E           a KEY = result of evaluating 'computed_id_dup';
E           a VAL = value of the field 'ID' which is base for computing 'computed_id_dup'
E           Thus sorter will have a field which not equals to a key, which leads to refetch.
E           Sub-query (invariant)
E           ....-> Refetch
E           ........-> Sort (record length: 36, key length: 12)
E           ............-> Filter
E           ................-> Table "TEST" as "X14" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A14" Full Scan
E           4030
E           select id,txt_short from test a15 where exists(select 1 from test x15 where f02>0 and f01>0 order by f01, f02)
E           Must NOT use refetch: all persistent columns from WHERE expression (f01, f02) belong to ORDER BY list
E           Sub-query (invariant)
E           ....-> Sort (record length: 36, key length: 16)
E           ........-> Filter
E           ............-> Table "TEST" as "X15" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A15" Full Scan
E           4040
E           select id,txt_short from test a16 where exists(select 1 from test x16 where id>0 and f01>0 order by f01, f02)
E           Must use refetch: one of columns from WHERE expr (id) does not belong to ORDER BY list
E           Sub-query (invariant)
E           ....-> Refetch
E           ........-> Sort (record length: 36, key length: 16)
E           ............-> Filter
E           ................-> Table "TEST" as "X16" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A16" Full Scan
E           4050
E           select id,txt_short from test a17 where exists(select 1 from test x17 where computed_id_dup > 0 order by f01)
E           Must use refetch: computed column in WHERE expr does not belong to ORDER BY list
E           Sub-query (invariant)
E           ....-> Refetch
E           ........-> Sort (record length: 28, key length: 8)
E           ............-> Filter
E           ................-> Table "TEST" as "X17" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A17" Full Scan
E           4060
E           select id,txt_short from test a18 where exists(select 1 from test x18 where computed_guid > '' order by f01)
E           Must NOT use refetch: computed column x18.computed_guid does is evaluated via GUID and does not refer to any columns
E           Sub-query (invariant)
E           ....-> Sort (record length: 28, key length: 8)
E           ........-> Filter
E           ............-> Table "TEST" as "X18" Full Scan
E           Select Expression
E           ....-> Filter (preliminary)
E           ........-> Table "TEST" as "A18" Full Scan
E           4070
E           with recursive
E           r as (
E           select a19.id, a19.txt_short
E           from test a19
E           where not exists(select * from test x where x.txt_short < a19.txt_short order by id)
E           UNION ALL
E           select i.id, i.txt_short
E           from test i
E           join r on i.id > r.id
E           and not exists( select * from test x where x.txt_short between r.txt_short and i.txt_short order by id )
E           )
E           select * from r
E           MUST use refetch both in anchor and recursive parts
E           Sub-query
E           ....-> Refetch
E           ........-> Sort (record length: 28, key length: 8)
E           ............-> Filter
E           ................-> Table "TEST" as "R X" Full Scan
E           Sub-query
E           ....-> Refetch
E           ........-> Sort (record length: 28, key length: 8)
E           ............-> Filter
E           ................-> Table "TEST" as "R X" Full Scan
E           Select Expression
E           ....-> Recursion
E           ........-> Filter
E           ............-> Table "TEST" as "R A19" Full Scan
E           ........-> Filter
E           ............-> Table "TEST" as "R I" Full Scan
E           5000
E           select txt_broad from v_unioned v01 order by id
E           Must NOT use refetch because view DDL includes UNION
E           Select Expression
E           ....-> Sort (record length: 4044, key length: 8)
E           ........-> First N Records
E           ............-> Union
E           ................-> Table "TEST" as "V01 TEST" Full Scan
E           ................-> Table "RDB$DATABASE" as "V01 RDB$DATABASE" Full Scan
E           6000
E           select left(txt_broad, 50) as txt from test a21 order by id
E           MUST use refetch because expression is based on column which has length >= threshold
E           (even if final length of expression result is much less than threshold)
E           Select Expression
E           ....-> Refetch
E           ........-> Sort (record length: 28, key length: 8)
E           ............-> Table "TEST" as "A21" Full Scan
E           6010
E           select left( txt_short || txt_short, 2000) as txt from test a22 order by id
E           Must NOT use refetch because expression is based on column which has length < threshold
E           (even if final length of expression result is much bigger than threshold)
E           Select Expression
E           ....-> Sort (record length: 1036, key length: 8)
E           ........-> Table "TEST" as "A22" Full Scan
E           7000
E           select * from test_ns_01 a23 order by id
E           MUST use refetch
E           Select Expression
E           ....-> Refetch
E           ........-> Sort (record length: 44, key length: 24)
E           ............-> Table "TEST_NS_01" as "A23" Full Scan
E           7010
E           select * from test_ns_02 a24 order by id
E           Must NOT refetch
E           Select Expression
E           ....-> Sort (record length: 1052, key length: 24)
E           ........-> Table "TEST_NS_02" as "A24" Full Scan
E           7020
E           select * from test_ns_03 order by id
E           MUST use refetch
E           Select Expression
E           ....-> Refetch
E           ........-> Sort (record length: 36, key length: 12)
E           ............-> Table "TEST_NS_03" Full Scan
E           7030
E           select * from test_ns_04 order by id
E           Must NOT use refetch
E           Select Expression
E           ....-> Sort (record length: 1036, key length: 12)
E           ........-> Table "TEST_NS_04" Full Scan
E           7040
E           select * from test_ns_05 order by id
E           MUST use refetch
E           Select Expression
E           ....-> Refetch
E           ........-> Sort (record length: 36, key length: 12)
E           ............-> Table "TEST_NS_05" Full Scan
E           7050
E           select * from test_ns_06 order by id
E           Must NOT use refetch
E           Select Expression
E           ....-> Sort (record length: 1036, key length: 12)
E           ........-> Table "TEST_NS_06" Full Scan

tests\bugs\core_2650_test.py:1199: AssertionError
Full history of outcomes and elapsed time, ms:
NN SNAP_INFO CS_outcome SS_outcome CS_run_time SS_run_time CS_run_beg CS_run_end SS_run_beg SS_run_end
1 5.0.3.1657 2025.06.26 dcb8a P P 782 455 2025.06.27 10:30:13.204 2025.06.27 10:30:13.986 2025.06.27 09:37:37.473 2025.06.27 09:37:37.928
2 5.0.3.1657 2025.06.20 8b4d2 P P 780 467 2025.06.26 10:44:26.551 2025.06.26 10:44:27.331 2025.06.26 09:51:39.666 2025.06.26 09:51:40.133
3 5.0.3.1657 2025.06.19 4bd4c P P 760 452 2025.06.20 04:48:44.063 2025.06.20 04:48:44.823 2025.06.20 03:55:56.589 2025.06.20 03:55:57.041
4 5.0.3.1657 2025.06.11 dae6f P P 752 451 2025.06.19 10:20:20.227 2025.06.19 10:20:20.979 2025.06.19 09:27:32.607 2025.06.19 09:27:33.058
5 5.0.3.1657 2025.06.10 dbc92 P P 752 458 2025.06.11 08:06:16.461 2025.06.11 08:06:17.213 2025.06.11 07:13:09.968 2025.06.11 07:13:10.426
6 5.0.3.1656 2025.06.05 00512 P P 803 458 2025.06.10 09:21:33.647 2025.06.10 09:21:34.450 2025.06.10 08:28:13.620 2025.06.10 08:28:14.078
7 5.0.3.1656 2025.05.20 c4b11 P P 785 465 2025.06.03 09:08:49.715 2025.06.03 09:08:50.500 2025.06.03 08:16:43.289 2025.06.03 08:16:43.754
8 5.0.3.1652 2025.05.13 f51c6 P P 749 474 2025.05.21 05:50:14.280 2025.05.21 05:50:15.029 2025.05.21 04:57:59.729 2025.05.21 04:58:00.203
9 5.0.3.1651 2025.05.08 ee9d2 P P 754 455 2025.05.13 06:02:32.028 2025.05.13 06:02:32.782 2025.05.13 04:52:00.676 2025.05.13 04:52:01.131
10 5.0.3.1651 2025.05.04 3d914 P P 753 454 2025.05.09 03:51:35.231 2025.05.09 03:51:35.984 2025.05.09 02:41:03.194 2025.05.09 02:41:03.648
11 5.0.3.1651 2025.04.30 141ef P P 797 459 2025.05.02 03:59:29.155 2025.05.02 03:59:29.952 2025.05.02 02:48:58.589 2025.05.02 02:48:59.048
12 5.0.3.1650 2025.04.30 6253f P P 756 457 2025.05.01 03:58:26.071 2025.05.01 03:58:26.827 2025.05.01 02:47:32.389 2025.05.01 02:47:32.846
13 5.0.3.1650 2025.04.28 4cbff P P 790 449 2025.04.30 03:57:26.137 2025.04.30 03:57:26.927 2025.04.30 02:47:13.983 2025.04.30 02:47:14.432
14 5.0.3.1649 2025.04.21 5b2d0 P P 766 488 2025.04.26 09:30:45.096 2025.04.26 09:30:45.862 2025.04.26 08:19:32.848 2025.04.26 08:19:33.336
15 5.0.3.1648 2025.04.18 2f4c5 P P 760 470 2025.04.20 03:42:08.533 2025.04.20 03:42:09.293 2025.04.20 02:48:55.405 2025.04.20 02:48:55.875
16 5.0.3.1635 2025.04.03 f6bd1 P P 807 466 2025.04.18 06:09:38.230 2025.04.18 06:09:39.037 2025.04.18 04:58:02.694 2025.04.18 04:58:03.160
17 5.0.3.1635 2025.03.31 22ec6 P P 790 469 2025.04.03 09:15:15.796 2025.04.03 09:15:16.586 2025.04.03 08:02:59.637 2025.04.03 08:03:00.106
18 5.0.3.1633 2025.03.28 3123a P P 799 465 2025.03.31 09:13:43.727 2025.03.31 09:13:44.526 2025.03.31 08:01:39.424 2025.03.31 08:01:39.889
19 5.0.3.1633 2025.03.27 e0fb8 P P 888 514 2025.03.28 09:42:24.605 2025.03.28 09:42:25.493 2025.03.28 08:27:04.947 2025.03.28 08:27:05.461
20 5.0.3.1631 2025.03.25 bda65 P P 804 512 2025.03.27 09:23:08.890 2025.03.27 09:23:09.694 2025.03.27 08:26:14.738 2025.03.27 08:26:15.250
21 5.0.3.1631 2025.03.21 1925b P P 821 463 2025.03.25 05:57:10.655 2025.03.25 05:57:11.476 2025.03.25 05:00:08.446 2025.03.25 05:00:08.909
22 5.0.3.1629 2025.03.18 506d7 P P 771 476 2025.03.20 08:57:52.231 2025.03.20 08:57:53.002 2025.03.20 08:01:07.831 2025.03.20 08:01:08.307
23 5.0.3.1628 2025.03.14 16d05 P P 805 469 2025.03.18 08:52:43.882 2025.03.18 08:52:44.687 2025.03.18 07:56:48.703 2025.03.18 07:56:49.172
24 5.0.3.1627 2025.02.26 4e218 P P 792 522 2025.03.13 09:10:19.843 2025.03.13 09:10:20.635 2025.03.13 08:13:23.593 2025.03.13 08:13:24.115
25 5.0.3.1624 2025.02.25 dc3b2 P P 826 510 2025.02.26 14:43:26.157 2025.02.26 14:43:26.983 2025.02.26 13:46:39.567 2025.02.26 13:46:40.077
26 5.0.2.1615 2025.02.20 4a726 P P 756 463 2025.02.25 07:58:30.987 2025.02.25 07:58:31.743 2025.02.25 06:45:00.945 2025.02.25 06:45:01.408
27 5.0.2.1615 2025.02.14 9cb76 P P 766 506 2025.02.15 03:26:31.651 2025.02.15 03:26:32.417 2025.02.15 02:30:55.159 2025.02.15 02:30:55.665
28 5.0.2.1577 2025.02.07 f50a2 F F 1140 847 2025.02.14 05:40:18.285 2025.02.14 05:40:19.425 2025.02.14 04:46:25.677 2025.02.14 04:46:26.524
29 5.0.2.1577 2024.12.24 3c80e F F 1112 837 2025.02.06 08:53:53.090 2025.02.06 08:53:54.202 2025.02.06 07:59:58.160 2025.02.06 07:59:58.997
30 5.0.2.1576 2024.12.17 646b0 P P 777 470 2024.12.24 08:37:02.396 2024.12.24 08:37:03.173 2024.12.24 07:43:22.302 2024.12.24 07:43:22.772
31 5.0.2.1575 2024.12.09 9af52 P P 809 467 2024.12.16 08:34:08.662 2024.12.16 08:34:09.471 2024.12.16 07:40:35.300 2024.12.16 07:40:35.767
32 5.0.2.1575 2024.12.08 63d39 P P 721 409 2024.12.09 14:26:28.657 2024.12.09 14:26:29.378 2024.12.09 13:38:26.657 2024.12.09 13:38:27.066
33 5.0.2.1571 2024.12.08 8d11a P P 703 414 2024.12.09 05:38:17.724 2024.12.09 05:38:18.427 2024.12.09 04:47:15.659 2024.12.09 04:47:16.073
34 5.0.2.1567 2024.12.07 b01a2 P P 745 444 2024.12.08 01:15:11.801 2024.12.08 01:15:12.546 2024.12.08 00:22:56.046 2024.12.08 00:22:56.490
35 5.0.2.1567 2024.12.02 6ae74 P P 694 392 2024.12.04 08:21:49.517 2024.12.04 08:21:50.211 2024.12.04 07:33:31.796 2024.12.04 07:33:32.188
36 5.0.2.1567 2024.11.26 56e63 P P 801 487 2024.11.30 08:24:33.389 2024.11.30 08:24:34.190 2024.11.30 07:31:56.147 2024.11.30 07:31:56.634
37 5.0.2.1567 2024.11.21 96f61 P P 775 488 2024.11.27 08:21:41.472 2024.11.27 08:21:42.247 2024.11.27 07:29:10.345 2024.11.27 07:29:10.833
38 5.0.2.1567 2024.11.18 e1289 P P 768 489 2024.11.21 08:40:14.238 2024.11.21 08:40:15.006 2024.11.21 07:25:38.131 2024.11.21 07:25:38.620
39 5.0.2.1533 2024.10.23 0ec43 P P 808 473 2024.11.18 08:15:18.709 2024.11.18 08:15:19.517 2024.11.18 07:22:37.019 2024.11.18 07:22:37.492
40 5.0.2.1533 2024.10.22 8af7a P P 805 477 2024.10.23 08:23:13.414 2024.10.23 08:23:14.219 2024.10.23 07:30:46.515 2024.10.23 07:30:46.992
41 5.0.2.1532 2024.10.15 36dc0 P P 812 481 2024.10.22 14:25:24.261 2024.10.22 14:25:25.073 2024.10.22 13:33:14.254 2024.10.22 13:33:14.735
42 5.0.2.1518 2024.10.04 259ba P P 822 474 2024.10.15 08:19:39.269 2024.10.15 08:19:40.091 2024.10.15 07:27:20.476 2024.10.15 07:27:20.950
43 5.0.2.1518 2024.09.26 703cd P P 775 483 2024.10.03 08:24:07.032 2024.10.03 08:24:07.807 2024.10.03 07:31:44.599 2024.10.03 07:31:45.082
44 5.0.2.1489 2024.08.31 994a6 P P 771 482 2024.09.26 08:42:57.704 2024.09.26 08:42:58.475 2024.09.26 07:31:48.341 2024.09.26 07:31:48.823
45 5.0.2.1476 2024.08.09 843ea P P 707 458 2024.09.01 08:30:14.856 2024.09.01 08:30:15.563 2024.09.01 07:17:49.858 2024.09.01 07:17:50.316
46 5.0.1.1454 2024.08.08 30f9f P P 705 449 2024.08.09 08:12:35.955 2024.08.09 08:12:36.660 2024.08.09 07:21:20.571 2024.08.09 07:21:21.020
47 5.0.1.1453 2024.08.07 ebbc3 P P 733 449 2024.08.08 19:26:34.500 2024.08.08 19:26:35.233 2024.08.08 18:35:22.932 2024.08.08 18:35:23.381
48 5.0.1.1453 2024.08.06 1b9d0 P P 533 342 2024.08.07 07:44:24.244 2024.08.07 07:44:24.777 2024.08.07 06:56:25.482 2024.08.07 06:56:25.824
49 5.0.1.1453 2024.07.30 48044 P P 535 366 2024.08.03 07:43:27.918 2024.08.03 07:43:28.453 2024.08.03 06:55:24.026 2024.08.03 06:55:24.392
50 5.0.1.1453 2024.07.28 8d956 P P 524 354 2024.07.30 07:24:47.672 2024.07.30 07:24:48.196 2024.07.30 06:38:32.841 2024.07.30 06:38:33.195
51 5.0.1.1429 2024.07.19 8ee90 P P 614 338 2024.07.27 07:23:36.288 2024.07.27 07:23:36.902 2024.07.27 06:37:34.005 2024.07.27 06:37:34.343
52 5.0.1.1428 2024.07.15 00392 P P 551 342 2024.07.19 07:18:58.096 2024.07.19 07:18:58.647 2024.07.19 06:33:16.382 2024.07.19 06:33:16.724
53 5.0.1.1428 2024.06.30 67a31 P P 531 357 2024.07.15 07:30:24.707 2024.07.15 07:30:25.238 2024.07.15 06:43:28.639 2024.07.15 06:43:28.996
54 5.0.1.1415 2024.06.12 f8731 P P 523 339 2024.06.22 04:29:27.415 2024.06.22 04:29:27.938 2024.06.22 03:43:56.780 2024.06.22 03:43:57.119
55 5.0.1.1415 2024.06.11 31d74 P P 484 266 2024.06.11 23:01:41.909 2024.06.11 23:01:42.393 2024.06.11 22:21:11.869 2024.06.11 22:21:12.135
56 5.0.1.1401 2024.06.05 f9b76 P P 500 266 2024.06.11 03:52:21.760 2024.06.11 03:52:22.260 2024.06.11 03:11:49.407 2024.06.11 03:11:49.673
57 5.0.1.1398 2024.05.10 5e3ce P P 484 266 2024.05.25 04:47:28.439 2024.05.25 04:47:28.923 2024.05.25 04:07:08.368 2024.05.25 04:07:08.634
58 5.0.1.1397 2024.05.09 ee2ef P P 500 266 2024.05.10 04:43:59.026 2024.05.10 04:43:59.526 2024.05.10 04:04:00.667 2024.05.10 04:04:00.933
59 5.0.1.1392 2024.04.29 7dbc2 P P 500 265 2024.05.09 16:33:32.822 2024.05.09 16:33:33.322 2024.05.09 15:53:28.153 2024.05.09 15:53:28.418
60 5.0.1.1386 2024.04.25 c0328 P P 500 250 2024.04.29 04:43:02.188 2024.04.29 04:43:02.688 2024.04.29 04:03:10.605 2024.04.29 04:03:10.855
61 5.0.1.1386 2024.04.20 80571 P P 487 266 2024.04.25 04:44:51.404 2024.04.25 04:44:51.891 2024.04.25 04:04:52.520 2024.04.25 04:04:52.786
62 5.0.1.1378 2024.04.07 5292b P P 484 266 2024.04.20 05:04:53.609 2024.04.20 05:04:54.093 2024.04.20 04:03:32.341 2024.04.20 04:03:32.607
63 5.0.1.1346 2024.03.16 fe320 P P 438 234 2024.04.07 03:07:26.823 2024.04.07 03:07:27.261 2024.04.07 02:29:39.201 2024.04.07 02:29:39.435
64 5.0.1.1346 2024.03.02 da408 P P 421 233 2024.03.25 21:55:06.228 2024.03.25 21:55:06.649 2024.03.25 21:17:20.591 2024.03.25 21:17:20.824
65 5.0.1.1340 2024.02.17 08a71 P P 421 233 2024.03.25 23:24:49.380 2024.03.25 23:24:49.801 2024.03.25 22:47:07.721 2024.03.25 22:47:07.954
66 5.0.1.1325 2024.02.06 c98fb P P 437 249 2024.03.26 01:56:16.091 2024.03.26 01:56:16.528 2024.03.26 00:57:25.355 2024.03.26 00:57:25.604
67 5.0.1.1325 2024.02.02 1d438 P P 468 264 2024.03.26 07:12:58.126 2024.03.26 07:12:58.594 2024.03.26 06:13:14.792 2024.03.26 06:13:15.056
68 5.0.1.1325 2024.01.26 cffb6 P P 437 249 2024.03.26 09:39:26.995 2024.03.26 09:39:27.432 2024.03.26 08:40:32.620 2024.03.26 08:40:32.869
69 5.0.1.1325 2024.01.26 d3810 P P 436 233 2024.03.26 11:31:20.755 2024.03.26 11:31:21.191 2024.03.26 10:32:45.517 2024.03.26 10:32:45.750
70 5.0.1.1318 2024.01.21 a7ca3 P P 437 249 2024.03.26 13:00:22.251 2024.03.26 13:00:22.688 2024.03.26 12:22:53.785 2024.03.26 12:22:54.034
71 5.0.1.1318 2024.01.21 d429d P P 436 249 2024.03.26 14:42:02.917 2024.03.26 14:42:03.353 2024.03.26 14:04:30.580 2024.03.26 14:04:30.829
72 5.0.1.1318 2024.01.21 f1ab5 P P 436 249 2024.03.26 16:57:58.830 2024.03.26 16:57:59.266 2024.03.26 16:20:31.291 2024.03.26 16:20:31.540
73 5.0.1.1318 2024.01.20 46722 P P 437 233 2024.03.26 18:25:01.307 2024.03.26 18:25:01.744 2024.03.26 17:47:37.005 2024.03.26 17:47:37.238

Elapsed time, ms. Chart for last 73 runs:

Last commits information (all timestamps in UTC):