Check firebird.log [no messages found 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 14:31:14.382
2025-02-14 14:31:14.391 act = <firebird.qa.plugin.Action object at [hex]>
2025-02-14 14:31:14.399 capsys = <_pytest.capture.CaptureFixture object at [hex]>
2025-02-14 14:31:14.408
2025-02-14 14:31:14.421 @pytest.mark.version('>=4.0')
2025-02-14 14:31:14.430 def test_1(act: Action, capsys):
2025-02-14 14:31:14.441 with act.db.connect() as con:
2025-02-14 14:31:14.451
2025-02-14 14:31:14.461 # 13.01.2025: test will FAIL if config parameter OptimizeForFirstRows differs from default value (i.e. is set to true).
2025-02-14 14:31:14.470 # To prevent this, we have to explicitly change appropriate session-level value:
2025-02-14 14:31:14.481 if act.is_version('<5'):
2025-02-14 14:31:14.491 pass
2025-02-14 14:31:14.500 else:
2025-02-14 14:31:14.509 con.execute_immediate('set optimize for all rows')
2025-02-14 14:31:14.517
2025-02-14 14:31:14.525 cur = con.cursor()
2025-02-14 14:31:14.533 for q_idx, q_tuple in query_map.items():
2025-02-14 14:31:14.542 test_sql, qry_comment = q_tuple[:2]
2025-02-14 14:31:14.550 ps = cur.prepare(test_sql)
2025-02-14 14:31:14.558 print(q_idx)
2025-02-14 14:31:14.566 print(test_sql)
2025-02-14 14:31:14.574 print(qry_comment)
2025-02-14 14:31:14.582 print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
2025-02-14 14:31:14.590 ps.free()
2025-02-14 14:31:14.600
2025-02-14 14:31:14.610 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 14:31:14.617 act.stdout = capsys.readouterr().out
2025-02-14 14:31:14.626 >       assert act.clean_stdout == act.clean_expected_stdout
2025-02-14 14:31:14.635 E       assert
2025-02-14 14:31:14.645 E           1000
2025-02-14 14:31:14.656 E           select txt_short from test a01 order by id
2025-02-14 14:31:14.666 E           Must NOT use refetch because length of non-key column is less than threshold
2025-02-14 14:31:14.674 E           Select Expression
2025-02-14 14:31:14.684 E           ....-> Sort (record length: 1036, key length: 8)
2025-02-14 14:31:14.697 E           ........-> Table "TEST" as "A01" Full Scan
2025-02-14 14:31:14.705 E           1010
2025-02-14 14:31:14.713 E           select txt_broad from test a02 order by id
2025-02-14 14:31:14.720 E           MUST use refetch because length of non-key column is greater than threshold
2025-02-14 14:31:14.727 E           Select Expression
2025-02-14 14:31:14.736 E           ....-> Refetch
2025-02-14 14:31:14.744 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:14.753 E           ............-> Table "TEST" as "A02" Full Scan
2025-02-14 14:31:14.765 E           1020
2025-02-14 14:31:14.780 E           select txt_short from test a03 order by id rows 1
2025-02-14 14:31:14.791 E           MUST use refetch regardless on length of column because ROWS <N> presents
2025-02-14 14:31:14.799 E           Select Expression
2025-02-14 14:31:14.808 E           ....-> First N Records
2025-02-14 14:31:14.822 E           ........-> Refetch
2025-02-14 14:31:14.833 E           ............-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:14.841 E           ................-> Table "TEST" as "A03" Full Scan
2025-02-14 14:31:14.848 E           2000
2025-02-14 14:31:14.856 E           select id, computed_ts_dup from test order by id
2025-02-14 14:31:14.865 E           Must NOT use refetch because computed column is based on txt_short with length < threshold
2025-02-14 14:31:14.874 E           Select Expression
2025-02-14 14:31:14.883 E           ....-> Sort (record length: 1036, key length: 8)
2025-02-14 14:31:14.893 E           ........-> Table "TEST" Full Scan
2025-02-14 14:31:14.901 E           2010
2025-02-14 14:31:14.908 E           select id, computed_tb_dup from test order by id
2025-02-14 14:31:14.915 E           MUST use refetch because computed column is based on txt_broad which has length >= threshold
2025-02-14 14:31:14.921 E           Select Expression
2025-02-14 14:31:14.928 E           ....-> Refetch
2025-02-14 14:31:14.935 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:14.943 E           ............-> Table "TEST" Full Scan
2025-02-14 14:31:14.955 E           3000
2025-02-14 14:31:14.969 E           select id from test a04 where '' in (select txt_short from test x04 where txt_short = '' order by id)
2025-02-14 14:31:14.980 E           *** not [yet] commented ***
2025-02-14 14:31:14.989 E           Sub-query (invariant)
2025-02-14 14:31:15.005 E           ....-> Filter
2025-02-14 14:31:15.016 E           ........-> Sort (record length: 1036, key length: 8)
2025-02-14 14:31:15.025 E           ............-> Filter
2025-02-14 14:31:15.033 E           ................-> Table "TEST" as "X04" Full Scan
2025-02-14 14:31:15.040 E           Select Expression
2025-02-14 14:31:15.048 E           ....-> Filter (preliminary)
2025-02-14 14:31:15.056 E           ........-> Table "TEST" as "A04" Full Scan
2025-02-14 14:31:15.063 E           3010
2025-02-14 14:31:15.070 E           select id from test a05 where '' in (select txt_broad from test x05 where txt_broad = '' order by id)
2025-02-14 14:31:15.079 E           *** not [yet] commented ***
2025-02-14 14:31:15.092 E           Sub-query (invariant)
2025-02-14 14:31:15.102 E           ....-> Filter
2025-02-14 14:31:15.116 E           ........-> Refetch
2025-02-14 14:31:15.127 E           ............-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:15.138 E           ................-> Filter
2025-02-14 14:31:15.148 E           ....................-> Table "TEST" as "X05" Full Scan
2025-02-14 14:31:15.155 E           Select Expression
2025-02-14 14:31:15.173 E           ....-> Filter (preliminary)
2025-02-14 14:31:15.184 E           ........-> Table "TEST" as "A05" Full Scan
2025-02-14 14:31:15.193 E           3020
2025-02-14 14:31:15.206 E           select id from test a06 where '' not in (select txt_short from test x06 where txt_short>'' order by id)
2025-02-14 14:31:15.215 E           *** not [yet] commented ***
2025-02-14 14:31:15.222 E         - Sub-query (invariant)
2025-02-14 14:31:15.229 E         + Select Expression
2025-02-14 14:31:15.235 E           ....-> Sort (record length: 1036, key length: 8)
2025-02-14 14:31:15.242 E           ........-> Filter
2025-02-14 14:31:15.251 E           ............-> Table "TEST" as "X06" Full Scan
2025-02-14 14:31:15.259 E         - Sub-query (invariant)
2025-02-14 14:31:15.267 E         + Select Expression
2025-02-14 14:31:15.276 E           ....-> Sort (record length: 1036, key length: 8)
2025-02-14 14:31:15.286 E           ........-> Filter
2025-02-14 14:31:15.295 E           ............-> Table "TEST" as "X06" Full Scan
2025-02-14 14:31:15.304 E           Select Expression
2025-02-14 14:31:15.312 E           ....-> Filter (preliminary)
2025-02-14 14:31:15.320 E           ........-> Table "TEST" as "A06" Full Scan
2025-02-14 14:31:15.327 E           3030
2025-02-14 14:31:15.335 E           select id from test a07 where '' not in (select txt_broad from test x07 where txt_broad>'' order by id)
2025-02-14 14:31:15.342 E           *** not [yet] commented ***
2025-02-14 14:31:15.355 E         - Sub-query (invariant)
2025-02-14 14:31:15.364 E         + Select Expression
2025-02-14 14:31:15.373 E           ....-> Refetch
2025-02-14 14:31:15.381 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:15.389 E           ............-> Filter
2025-02-14 14:31:15.397 E           ................-> Table "TEST" as "X07" Full Scan
2025-02-14 14:31:15.405 E         - Sub-query (invariant)
2025-02-14 14:31:15.413 E         + Select Expression
2025-02-14 14:31:15.421 E           ....-> Refetch
2025-02-14 14:31:15.429 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:15.441 E           ............-> Filter
2025-02-14 14:31:15.451 E           ................-> Table "TEST" as "X07" Full Scan
2025-02-14 14:31:15.465 E           Select Expression
2025-02-14 14:31:15.475 E           ....-> Filter (preliminary)
2025-02-14 14:31:15.484 E           ........-> Table "TEST" as "A07" Full Scan
2025-02-14 14:31:15.495 E           3040
2025-02-14 14:31:15.503 E           select id from test a08 where '' > all (select id from test x08 where txt_short>'' order by id)
2025-02-14 14:31:15.510 E           *** not [yet] commented ***
2025-02-14 14:31:15.517 E           Sub-query (invariant)
2025-02-14 14:31:15.524 E           ....-> Filter
2025-02-14 14:31:15.534 E           ........-> Sort (record length: 1036, key length: 8)
2025-02-14 14:31:15.547 E           ............-> Filter
2025-02-14 14:31:15.558 E           ................-> Table "TEST" as "X08" Full Scan
2025-02-14 14:31:15.567 E           Select Expression
2025-02-14 14:31:15.575 E           ....-> Filter (preliminary)
2025-02-14 14:31:15.581 E           ........-> Table "TEST" as "A08" Full Scan
2025-02-14 14:31:15.589 E           3050
2025-02-14 14:31:15.601 E           select id from test a09 where '' > all (select id from test x09 where txt_broad>'' order by id)
2025-02-14 14:31:15.611 E           *** not [yet] commented ***
2025-02-14 14:31:15.620 E           Sub-query (invariant)
2025-02-14 14:31:15.629 E           ....-> Filter
2025-02-14 14:31:15.636 E           ........-> Refetch
2025-02-14 14:31:15.644 E           ............-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:15.651 E           ................-> Filter
2025-02-14 14:31:15.659 E           ....................-> Table "TEST" as "X09" Full Scan
2025-02-14 14:31:15.668 E           Select Expression
2025-02-14 14:31:15.677 E           ....-> Filter (preliminary)
2025-02-14 14:31:15.685 E           ........-> Table "TEST" as "A09" Full Scan
2025-02-14 14:31:15.692 E           3060
2025-02-14 14:31:15.701 E           select id from test a10 where '' <> any (select id from test x10 where txt_short>'' order by id)
2025-02-14 14:31:15.710 E           *** not [yet] commented ***
2025-02-14 14:31:15.717 E           Sub-query (invariant)
2025-02-14 14:31:15.725 E           ....-> Filter
2025-02-14 14:31:15.733 E           ........-> Sort (record length: 1036, key length: 8)
2025-02-14 14:31:15.740 E           ............-> Filter
2025-02-14 14:31:15.748 E           ................-> Table "TEST" as "X10" Full Scan
2025-02-14 14:31:15.756 E           Select Expression
2025-02-14 14:31:15.764 E           ....-> Filter (preliminary)
2025-02-14 14:31:15.772 E           ........-> Table "TEST" as "A10" Full Scan
2025-02-14 14:31:15.780 E           3070
2025-02-14 14:31:15.789 E           select id from test a11 where '' <> any (select id from test x11 where txt_broad>'' order by id)
2025-02-14 14:31:15.803 E           *** not [yet] commented ***
2025-02-14 14:31:15.813 E           Sub-query (invariant)
2025-02-14 14:31:15.820 E           ....-> Filter
2025-02-14 14:31:15.831 E           ........-> Refetch
2025-02-14 14:31:15.843 E           ............-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:15.852 E           ................-> Filter
2025-02-14 14:31:15.860 E           ....................-> Table "TEST" as "X11" Full Scan
2025-02-14 14:31:15.868 E           Select Expression
2025-02-14 14:31:15.875 E           ....-> Filter (preliminary)
2025-02-14 14:31:15.882 E           ........-> Table "TEST" as "A11" Full Scan
2025-02-14 14:31:15.889 E           4000
2025-02-14 14:31:15.897 E           select id,txt_short from test a12 where exists(select 1 from test x12 where txt_short>'' order by id)
2025-02-14 14:31:15.907 E           MUST use refetch: column x12.txt_short not present in order by
2025-02-14 14:31:15.919 E           Sub-query (invariant)
2025-02-14 14:31:15.929 E           ....-> Refetch
2025-02-14 14:31:15.944 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:15.953 E           ............-> Filter
2025-02-14 14:31:15.962 E           ................-> Table "TEST" as "X12" Full Scan
2025-02-14 14:31:15.970 E           Select Expression
2025-02-14 14:31:15.977 E           ....-> Filter (preliminary)
2025-02-14 14:31:15.989 E           ........-> Table "TEST" as "A12" Full Scan
2025-02-14 14:31:15.999 E           4010
2025-02-14 14:31:16.008 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 14:31:16.014 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 14:31:16.023 E           Sub-query (invariant)
2025-02-14 14:31:16.030 E           ....-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:16.038 E           ........-> Filter
2025-02-14 14:31:16.045 E           ............-> Table "TEST" as "X13" Full Scan
2025-02-14 14:31:16.053 E           Select Expression
2025-02-14 14:31:16.067 E           ....-> Filter (preliminary)
2025-02-14 14:31:16.074 E           ........-> Table "TEST" as "A13" Full Scan
2025-02-14 14:31:16.081 E           4020
2025-02-14 14:31:16.089 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 14:31:16.098 E           MUST use refetch! See letter from dimitr 28.12.2020 14:49
2025-02-14 14:31:16.106 E           Sort procedure will get:
2025-02-14 14:31:16.116 E           a KEY = result of evaluating 'computed_id_dup';
2025-02-14 14:31:16.124 E           a VAL = value of the field 'ID' which is base for computing 'computed_id_dup'
2025-02-14 14:31:16.133 E           Thus sorter will have a field which not equals to a key, which leads to refetch.
2025-02-14 14:31:16.142 E           Sub-query (invariant)
2025-02-14 14:31:16.151 E           ....-> Refetch
2025-02-14 14:31:16.159 E           ........-> Sort (record length: 36, key length: 12)
2025-02-14 14:31:16.167 E           ............-> Filter
2025-02-14 14:31:16.174 E           ................-> Table "TEST" as "X14" Full Scan
2025-02-14 14:31:16.182 E           Select Expression
2025-02-14 14:31:16.193 E           ....-> Filter (preliminary)
2025-02-14 14:31:16.203 E           ........-> Table "TEST" as "A14" Full Scan
2025-02-14 14:31:16.210 E           4030
2025-02-14 14:31:16.220 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 14:31:16.229 E           Must NOT use refetch: all persistent columns from WHERE expression (f01, f02) belong to ORDER BY list
2025-02-14 14:31:16.237 E           Sub-query (invariant)
2025-02-14 14:31:16.245 E           ....-> Sort (record length: 36, key length: 16)
2025-02-14 14:31:16.255 E           ........-> Filter
2025-02-14 14:31:16.267 E           ............-> Table "TEST" as "X15" Full Scan
2025-02-14 14:31:16.278 E           Select Expression
2025-02-14 14:31:16.288 E           ....-> Filter (preliminary)
2025-02-14 14:31:16.298 E           ........-> Table "TEST" as "A15" Full Scan
2025-02-14 14:31:16.305 E           4040
2025-02-14 14:31:16.316 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 14:31:16.325 E           Must use refetch: one of columns from WHERE expr (id) does not belong to ORDER BY list
2025-02-14 14:31:16.333 E           Sub-query (invariant)
2025-02-14 14:31:16.340 E           ....-> Refetch
2025-02-14 14:31:16.348 E           ........-> Sort (record length: 36, key length: 16)
2025-02-14 14:31:16.356 E           ............-> Filter
2025-02-14 14:31:16.364 E           ................-> Table "TEST" as "X16" Full Scan
2025-02-14 14:31:16.373 E           Select Expression
2025-02-14 14:31:16.381 E           ....-> Filter (preliminary)
2025-02-14 14:31:16.389 E           ........-> Table "TEST" as "A16" Full Scan
2025-02-14 14:31:16.401 E           4050
2025-02-14 14:31:16.410 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 14:31:16.419 E           Must use refetch: computed column in WHERE expr does not belong to ORDER BY list
2025-02-14 14:31:16.427 E           Sub-query (invariant)
2025-02-14 14:31:16.435 E           ....-> Refetch
2025-02-14 14:31:16.444 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:16.453 E           ............-> Filter
2025-02-14 14:31:16.463 E           ................-> Table "TEST" as "X17" Full Scan
2025-02-14 14:31:16.473 E           Select Expression
2025-02-14 14:31:16.486 E           ....-> Filter (preliminary)
2025-02-14 14:31:16.496 E           ........-> Table "TEST" as "A17" Full Scan
2025-02-14 14:31:16.505 E           4060
2025-02-14 14:31:16.514 E           select id,txt_short from test a18 where exists(select 1 from test x18 where computed_guid > '' order by f01)
2025-02-14 14:31:16.523 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 14:31:16.531 E           Sub-query (invariant)
2025-02-14 14:31:16.540 E           ....-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:16.549 E           ........-> Filter
2025-02-14 14:31:16.557 E           ............-> Table "TEST" as "X18" Full Scan
2025-02-14 14:31:16.565 E           Select Expression
2025-02-14 14:31:16.572 E           ....-> Filter (preliminary)
2025-02-14 14:31:16.579 E           ........-> Table "TEST" as "A18" Full Scan
2025-02-14 14:31:16.586 E           4070
2025-02-14 14:31:16.596 E           with recursive
2025-02-14 14:31:16.603 E           r as (
2025-02-14 14:31:16.610 E           select a19.id, a19.txt_short
2025-02-14 14:31:16.617 E           from test a19
2025-02-14 14:31:16.626 E           where not exists(select * from test x where x.txt_short < a19.txt_short order by id)
2025-02-14 14:31:16.635 E           UNION ALL
2025-02-14 14:31:16.647 E           select i.id, i.txt_short
2025-02-14 14:31:16.657 E           from test i
2025-02-14 14:31:16.667 E           join r on i.id > r.id
2025-02-14 14:31:16.676 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 14:31:16.685 E           )
2025-02-14 14:31:16.695 E           select * from r
2025-02-14 14:31:16.704 E           MUST use refetch both in anchor and recursive parts
2025-02-14 14:31:16.714 E           Sub-query
2025-02-14 14:31:16.723 E           ....-> Refetch
2025-02-14 14:31:16.733 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:16.742 E           ............-> Filter
2025-02-14 14:31:16.751 E           ................-> Table "TEST" as "R X" Full Scan
2025-02-14 14:31:16.759 E           Sub-query
2025-02-14 14:31:16.767 E           ....-> Refetch
2025-02-14 14:31:16.775 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:16.782 E           ............-> Filter
2025-02-14 14:31:16.791 E           ................-> Table "TEST" as "R X" Full Scan
2025-02-14 14:31:16.799 E           Select Expression
2025-02-14 14:31:16.807 E           ....-> Recursion
2025-02-14 14:31:16.819 E           ........-> Filter
2025-02-14 14:31:16.832 E           ............-> Table "TEST" as "R A19" Full Scan
2025-02-14 14:31:16.843 E           ........-> Filter
2025-02-14 14:31:16.852 E           ............-> Table "TEST" as "R I" Full Scan
2025-02-14 14:31:16.861 E           5000
2025-02-14 14:31:16.868 E           select txt_broad from v_unioned v01 order by id
2025-02-14 14:31:16.875 E           Must NOT use refetch because view DDL includes UNION
2025-02-14 14:31:16.888 E           Select Expression
2025-02-14 14:31:16.898 E           ....-> Sort (record length: 4044, key length: 8)
2025-02-14 14:31:16.906 E           ........-> First N Records
2025-02-14 14:31:16.914 E           ............-> Union
2025-02-14 14:31:16.922 E           ................-> Table "TEST" as "V01 TEST" Full Scan
2025-02-14 14:31:16.930 E           ................-> Table "RDB$DATABASE" as "V01 RDB$DATABASE" Full Scan
2025-02-14 14:31:16.940 E           6000
2025-02-14 14:31:16.949 E           select left(txt_broad, 50) as txt from test a21 order by id
2025-02-14 14:31:16.957 E           MUST use refetch because expression is based on column which has length >= threshold
2025-02-14 14:31:16.966 E           (even if final length of expression result is much less than threshold)
2025-02-14 14:31:16.974 E           Select Expression
2025-02-14 14:31:16.980 E           ....-> Refetch
2025-02-14 14:31:16.986 E           ........-> Sort (record length: 28, key length: 8)
2025-02-14 14:31:16.993 E           ............-> Table "TEST" as "A21" Full Scan
2025-02-14 14:31:17.005 E           6010
2025-02-14 14:31:17.013 E           select left( txt_short || txt_short, 2000) as txt from test a22 order by id
2025-02-14 14:31:17.020 E           Must NOT use refetch because expression is based on column which has length < threshold
2025-02-14 14:31:17.028 E           (even if final length of expression result is much bigger than threshold)
2025-02-14 14:31:17.037 E           Select Expression
2025-02-14 14:31:17.045 E           ....-> Sort (record length: 1036, key length: 8)
2025-02-14 14:31:17.054 E           ........-> Table "TEST" as "A22" Full Scan
2025-02-14 14:31:17.062 E           7000
2025-02-14 14:31:17.069 E           select * from test_ns_01 a23 order by id
2025-02-14 14:31:17.076 E           MUST use refetch
2025-02-14 14:31:17.083 E           Select Expression
2025-02-14 14:31:17.091 E           ....-> Refetch
2025-02-14 14:31:17.099 E           ........-> Sort (record length: 44, key length: 24)
2025-02-14 14:31:17.109 E           ............-> Table "TEST_NS_01" as "A23" Full Scan
2025-02-14 14:31:17.118 E           7010
2025-02-14 14:31:17.124 E           select * from test_ns_02 a24 order by id
2025-02-14 14:31:17.134 E           Must NOT refetch
2025-02-14 14:31:17.142 E           Select Expression
2025-02-14 14:31:17.151 E           ....-> Sort (record length: 1052, key length: 24)
2025-02-14 14:31:17.159 E           ........-> Table "TEST_NS_02" as "A24" Full Scan
2025-02-14 14:31:17.166 E           7020
2025-02-14 14:31:17.173 E           select * from test_ns_03 order by id
2025-02-14 14:31:17.180 E           MUST use refetch
2025-02-14 14:31:17.188 E           Select Expression
2025-02-14 14:31:17.199 E           ....-> Refetch
2025-02-14 14:31:17.212 E           ........-> Sort (record length: 36, key length: 12)
2025-02-14 14:31:17.225 E           ............-> Table "TEST_NS_03" Full Scan
2025-02-14 14:31:17.237 E           7030
2025-02-14 14:31:17.247 E           select * from test_ns_04 order by id
2025-02-14 14:31:17.259 E           Must NOT use refetch
2025-02-14 14:31:17.273 E           Select Expression
2025-02-14 14:31:17.288 E           ....-> Sort (record length: 1036, key length: 12)
2025-02-14 14:31:17.303 E           ........-> Table "TEST_NS_04" Full Scan
2025-02-14 14:31:17.314 E           7040
2025-02-14 14:31:17.325 E           select * from test_ns_05 order by id
2025-02-14 14:31:17.333 E           MUST use refetch
2025-02-14 14:31:17.347 E           Select Expression
2025-02-14 14:31:17.359 E           ....-> Refetch
2025-02-14 14:31:17.368 E           ........-> Sort (record length: 36, key length: 12)
2025-02-14 14:31:17.378 E           ............-> Table "TEST_NS_05" Full Scan
2025-02-14 14:31:17.387 E           7050
2025-02-14 14:31:17.396 E           select * from test_ns_06 order by id
2025-02-14 14:31:17.414 E           Must NOT use refetch
2025-02-14 14:31:17.427 E           Select Expression
2025-02-14 14:31:17.444 E           ....-> Sort (record length: 1036, key length: 12)
2025-02-14 14:31:17.457 E           ........-> Table "TEST_NS_06" Full Scan
2025-02-14 14:31:17.468
2025-02-14 14:31:17.483 tests/bugs/core_2650_test.py:1199: AssertionError
2025-02-14 14:31:17.493 ---------------------------- Captured stdout setup -----------------------------
2025-02-14 14:31:17.506 Creating db: localhost:/var/tmp/qa_2024/test_1441/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.1674 2025.06.27 3ee5c P P 985 580 2025.07.03 10:07:10.681 2025.07.03 10:07:11.666 2025.07.03 08:58:10.529 2025.07.03 08:58:11.109
2 5.0.3.1657 2025.06.19 4bd4c P P 844 482 2025.06.27 11:31:51.093 2025.06.27 11:31:51.937 2025.06.27 10:24:10.824 2025.06.27 10:24:11.306
3 5.0.3.1657 2025.06.11 dae6f P P 1007 551 2025.06.17 06:36:07.178 2025.06.17 06:36:08.185 2025.06.17 05:27:14.959 2025.06.17 05:27:15.510
4 5.0.3.1657 2025.06.10 dbc92 P P 1716 684 2025.06.11 11:25:47.350 2025.06.11 11:25:49.066 2025.06.11 10:03:11.015 2025.06.11 10:03:11.699
5 5.0.3.1656 2025.05.20 c4b11 P P 989 590 2025.06.10 11:17:58.263 2025.06.10 11:17:59.252 2025.06.10 10:05:56.742 2025.06.10 10:05:57.332
6 5.0.3.1652 2025.05.13 f51c6 P P 1183 617 2025.05.20 04:15:18.286 2025.05.20 04:15:19.469 2025.05.20 02:53:49.028 2025.05.20 02:53:49.645
7 5.0.3.1651 2025.04.30 141ef P P 1222 584 2025.05.13 11:13:27.927 2025.05.13 11:13:29.149 2025.05.13 09:49:33.298 2025.05.13 09:49:33.882
8 5.0.3.1650 2025.04.28 4cbff P P 1133 656 2025.05.01 10:47:07.607 2025.05.01 10:47:08.740 2025.05.01 09:24:50.103 2025.05.01 09:24:50.759
9 5.0.3.1649 2025.04.21 5b2d0 P P 1080 633 2025.04.28 04:46:10.752 2025.04.28 04:46:11.832 2025.04.28 03:25:58.249 2025.04.28 03:25:58.882
10 5.0.3.1648 2025.04.18 2f4c5 P P 1080 598 2025.04.20 04:38:32.872 2025.04.20 04:38:33.952 2025.04.20 03:21:01.434 2025.04.20 03:21:02.032
11 5.0.3.1635 2025.03.31 22ec6 P P 1107 710 2025.04.18 10:59:28.116 2025.04.18 10:59:29.223 2025.04.18 09:37:32.401 2025.04.18 09:37:33.111
12 5.0.3.1633 2025.03.28 3123a P P 1018 562 2025.03.31 09:37:05.140 2025.03.31 09:37:06.158 2025.03.31 08:20:54.978 2025.03.31 08:20:55.540
13 5.0.3.1633 2025.03.27 e0fb8 P P 924 518 2025.03.28 10:08:47.056 2025.03.28 10:08:47.980 2025.03.28 08:48:37.597 2025.03.28 08:48:38.115
14 5.0.3.1631 2025.03.21 1925b P P 1096 590 2025.03.27 09:57:36.695 2025.03.27 09:57:37.791 2025.03.27 08:38:33.943 2025.03.27 08:38:34.533
15 5.0.3.1628 2025.03.14 16d05 P P 929 461 2025.03.17 07:25:25.257 2025.03.17 07:25:26.186 2025.03.17 05:58:01.639 2025.03.17 05:58:02.100
16 5.0.3.1627 2025.02.26 4e218 P P 940 511 2025.03.14 10:34:50.548 2025.03.14 10:34:51.488 2025.03.14 09:11:05.988 2025.03.14 09:11:06.499
17 5.0.3.1624 2025.02.25 dc3b2 P P 921 488 2025.02.26 09:32:27.325 2025.02.26 09:32:28.246 2025.02.26 08:10:25.990 2025.02.26 08:10:26.478
18 5.0.2.1615 2025.02.20 4a726 P P 941 437 2025.02.25 09:59:17.495 2025.02.25 09:59:18.436 2025.02.25 08:35:57.903 2025.02.25 08:35:58.340
19 5.0.2.1615 2025.02.14 9cb76 P P 881 487 2025.02.20 06:50:31.860 2025.02.20 06:50:32.741 2025.02.20 05:29:58.557 2025.02.20 05:29:59.044
20 5.0.2.1577 2024.12.24 3c80e F F 986 456 2025.02.14 10:04:32.200 2025.02.14 10:04:33.186 2025.02.14 08:45:52.901 2025.02.14 08:45:53.357
21 5.0.2.1576 2024.12.17 646b0 P P 663 340 2024.12.24 17:41:21.621 2024.12.24 17:41:22.284 2024.12.24 16:40:44.968 2024.12.24 16:40:45.308
22 5.0.2.1575 2024.12.08 63d39 P P 695 337 2024.12.16 07:23:30.851 2024.12.16 07:23:31.546 2024.12.16 06:24:29.244 2024.12.16 06:24:29.581
23 5.0.2.1567 2024.11.26 56e63 P P 688 347 2024.12.04 07:03:02.445 2024.12.04 07:03:03.133 2024.12.04 06:01:44.807 2024.12.04 06:01:45.154
24 5.0.2.1567 2024.11.21 96f61 P P 675 349 2024.11.26 04:46:41.132 2024.11.26 04:46:41.807 2024.11.26 03:48:56.642 2024.11.26 03:48:56.991
25 5.0.2.1567 2024.11.18 e1289 P P 701 348 2024.11.21 06:51:49.021 2024.11.21 06:51:49.722 2024.11.21 05:53:53.096 2024.11.21 05:53:53.444
26 5.0.2.1533 2024.10.23 0ec43 P P 682 363 2024.11.18 07:06:38.296 2024.11.18 07:06:38.978 2024.11.18 06:07:22.129 2024.11.18 06:07:22.492
27 5.0.2.1533 2024.10.22 8af7a P P 772 341 2024.10.23 09:03:05.359 2024.10.23 09:03:06.131 2024.10.23 08:03:09.172 2024.10.23 08:03:09.513
28 5.0.2.1532 2024.10.15 36dc0 P P 754 357 2024.10.22 19:14:59.892 2024.10.22 19:15:00.646 2024.10.22 18:15:36.920 2024.10.22 18:15:37.277
29 5.0.2.1518 2024.10.04 259ba P P 706 355 2024.10.15 08:48:25.058 2024.10.15 08:48:25.764 2024.10.15 07:48:48.958 2024.10.15 07:48:49.313
30 5.0.2.1518 2024.09.26 703cd P P 762 364 2024.10.03 07:14:08.547 2024.10.03 07:14:09.309 2024.10.03 06:13:34.283 2024.10.03 06:13:34.647
31 5.0.2.1489 2024.09.05 40838 P P 754 359 2024.09.25 07:15:02.650 2024.09.25 07:15:03.404 2024.09.25 06:12:47.036 2024.09.25 06:12:47.395
32 5.0.2.1489 2024.08.31 994a6 P P 1525 515 2024.09.05 11:06:53.959 2024.09.05 11:06:55.484 2024.09.05 09:47:41.361 2024.09.05 09:47:41.876
33 5.0.2.1476 2024.08.09 843ea P P 1596 1307 2024.08.31 13:21:10.511 2024.08.31 13:21:12.107 2024.08.31 11:32:39.374 2024.08.31 11:32:40.681
34 5.0.1.1454 2024.08.08 30f9f P P 870 530 2024.08.09 07:50:51.430 2024.08.09 07:50:52.300 2024.08.09 06:48:33.683 2024.08.09 06:48:34.213
35 5.0.1.1453 2024.08.06 1b9d0 P P 894 525 2024.08.08 20:29:46.387 2024.08.08 20:29:47.281 2024.08.08 19:26:34.430 2024.08.08 19:26:34.955
36 5.0.1.1453 2024.07.30 48044 P P 598 314 2024.08.06 06:37:36.930 2024.08.06 06:37:37.528 2024.08.06 05:42:05.946 2024.08.06 05:42:06.260
37 5.0.1.1453 2024.07.28 8d956 P P 646 356 2024.07.30 06:32:24.841 2024.07.30 06:32:25.487 2024.07.30 05:37:24.001 2024.07.30 05:37:24.357
38 5.0.1.1429 2024.07.19 8ee90 P P 594 323 2024.07.27 06:31:57.531 2024.07.27 06:31:58.125 2024.07.27 05:35:31.752 2024.07.27 05:35:32.075
39 5.0.1.1428 2024.07.15 00392 P P 614 323 2024.07.19 06:26:09.972 2024.07.19 06:26:10.586 2024.07.19 05:31:32.468 2024.07.19 05:31:32.791
40 5.0.1.1428 2024.06.30 67a31 P P 614 354 2024.07.15 06:27:29.420 2024.07.15 06:27:30.034 2024.07.15 05:31:52.504 2024.07.15 05:31:52.858
41 5.0.1.1415 2024.06.11 31d74 P P 587 308 2024.06.22 06:16:36.039 2024.06.22 06:16:36.626 2024.06.22 05:23:42.990 2024.06.22 05:23:43.298

Elapsed time, ms. Chart for last 41 runs:

Last commits information (all timestamps in UTC):