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 count(*) from test1 q1_a
    where
    q1_a.id in (
    select q1_b.pid from test2 q1_b
    where
    q1_b.id in (
    select q1_c.pid from test3 q1_c
    where q1_c.name like '%ABC%'
    )
    )
    Both sub-queries can (and should) be unnested.
    Select Expression
    ....-> Aggregate
    ........-> Filter
    ............-> Hash Join (semi)
  - ................-> Table "TEST1" as "Q1_A" Full Scan
  + ................-> Table "PUBLIC"."TEST1" as "Q1_A" Full Scan
    ................-> Record Buffer (record length: NN)
    ....................-> Filter
    ........................-> Hash Join (semi)
  - ............................-> Table "TEST2" as "Q1_B" Full Scan
  + ............................-> Table "PUBLIC"."TEST2" as "Q1_B" Full Scan
    ............................-> Record Buffer (record length: NN)
    ................................-> Filter
  - ....................................-> Table "TEST3" as "Q1_C" Full Scan
  + ....................................-> Table "PUBLIC"."TEST3" as "Q1_C" Full Scan
    10
    2000
    select count(*) from test1 q2_a
    where
    q2_a.id in (
    select q2_b.pid from test2 q2_b
    where
    1=1 or q2_b.id in (
    select q2_c.pid from test3 q2_c
    where q2_c.name like '%ABC%'
    )
    )
    Inner sub-query can NOT be unnested due to `OR` condition present, but the outer sub-query can
    Sub-query
    ....-> Filter
    ........-> Filter
  - ............-> Table "TEST3" as "Q2_C" Full Scan
  + ............-> Table "PUBLIC"."TEST3" as "Q2_C" Full Scan
    Select Expression
    ....-> Aggregate
    ........-> Filter
    ............-> Hash Join (semi)
  - ................-> Table "TEST1" as "Q2_A" Full Scan
  + ................-> Table "PUBLIC"."TEST1" as "Q2_A" Full Scan
    ................-> Record Buffer (record length: NN)
    ....................-> Filter
  - ........................-> Table "TEST2" as "Q2_B" Full Scan
  + ........................-> Table "PUBLIC"."TEST2" as "Q2_B" Full Scan
    10
    3000
    select count(*) from test1 q3_a
    where
    1=1 or q3_a.id in (
    select q3_b.pid from test2 q3_b
    where q3_b.id in (
    select id from test3 q3_c
    where q3_c.name like '%ABC%'
    )
    )
    Outer sub-query can NOT be unnested due to `OR` condition present, so the inner sub-query should NOT be unnested too
    Sub-query
    ....-> Filter
    ........-> Filter
  - ............-> Table "TEST3" as "Q3_C" Full Scan
  + ............-> Table "PUBLIC"."TEST3" as "Q3_C" Full Scan
    Sub-query
    ....-> Filter
    ........-> Filter
  - ............-> Table "TEST2" as "Q3_B" Full Scan
  + ............-> Table "PUBLIC"."TEST2" as "Q3_B" Full Scan
    Select Expression
    ....-> Aggregate
    ........-> Filter
  - ............-> Table "TEST1" as "Q3_A" Full Scan
  + ............-> Table "PUBLIC"."TEST1" as "Q3_A" Full Scan
    10
    4000
    select count(*) from test1 q4_a
    where
    1=1 or q4_a.id in (
    select id from test2 q4_b
    where
    1=1 or q4_b.id in (
    select id from test3 q4_c
    where q4_c.name like '%ABC%'
    )
    )
    Both sub-queries can NOT be unnested due to OR conditions present
    Sub-query
    ....-> Filter
    ........-> Filter
  - ............-> Table "TEST3" as "Q4_C" Full Scan
  + ............-> Table "PUBLIC"."TEST3" as "Q4_C" Full Scan
    Sub-query
    ....-> Filter
    ........-> Filter
  - ............-> Table "TEST2" as "Q4_B" Full Scan
  + ............-> Table "PUBLIC"."TEST2" as "Q4_B" Full Scan
    Select Expression
    ....-> Aggregate
    ........-> Filter
  - ............-> Table "TEST1" as "Q4_A" Full Scan
  + ............-> Table "PUBLIC"."TEST1" as "Q4_A" Full Scan
    10

LOG DETAILS:

2025-07-04 01:17:17.724
2025-07-04 01:17:17.724 act = <firebird.qa.plugin.Action object at [hex]>
2025-07-04 01:17:17.724 capsys = <_pytest.capture.CaptureFixture object at [hex]>
2025-07-04 01:17:17.724
2025-07-04 01:17:17.724     @pytest.mark.version('>=5.0.2')
2025-07-04 01:17:17.730     def test_1(act: Action, capsys):
2025-07-04 01:17:17.730
2025-07-04 01:17:17.730         srv_cfg = driver_config.register_server(name = f'srv_cfg_8265', config = '')
2025-07-04 01:17:17.730         db_cfg_name = f'db_cfg_8265'
2025-07-04 01:17:17.730         db_cfg_object = driver_config.register_database(name = db_cfg_name)
2025-07-04 01:17:17.730         db_cfg_object.server.value = srv_cfg.name
2025-07-04 01:17:17.730         db_cfg_object.database.value = str(act.db.db_path)
2025-07-04 01:17:17.730         if act.is_version('<6'):
2025-07-04 01:17:17.730             db_cfg_object.config.value = f"""
2025-07-04 01:17:17.730                 SubQueryConversion = true
2025-07-04 01:17:17.730             """
2025-07-04 01:17:17.730
2025-07-04 01:17:17.730         with connect(db_cfg_name, user = act.db.user, password = act.db.password) as con:
2025-07-04 01:17:17.730             cur = con.cursor()
2025-07-04 01:17:17.730             for q_idx, q_tuple in query_map.items():
2025-07-04 01:17:17.730                 test_sql, qry_comment = q_tuple[:2]
2025-07-04 01:17:17.730                 ps,rs = None, None
2025-07-04 01:17:17.731                 try:
2025-07-04 01:17:17.731                     ps = cur.prepare(test_sql)
2025-07-04 01:17:17.731                     print(q_idx)
2025-07-04 01:17:17.731                     print(test_sql)
2025-07-04 01:17:17.731                     print(qry_comment)
2025-07-04 01:17:17.731
2025-07-04 01:17:17.731                     # Print explained plan with padding eash line by dots in order to see indentations:
2025-07-04 01:17:17.731                     print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
2025-07-04 01:17:17.731                     rs = cur.execute(ps)
2025-07-04 01:17:17.731                     # Print data:
2025-07-04 01:17:17.731                     for r in rs:
2025-07-04 01:17:17.731                         print(r[0])
2025-07-04 01:17:17.731                 except DatabaseError as e:
2025-07-04 01:17:17.731                     print(e.__str__())
2025-07-04 01:17:17.731                     print(e.gds_codes)
2025-07-04 01:17:17.731                 finally:
2025-07-04 01:17:17.731                     # explained by hvlad, 26.10.24 17:42
2025-07-04 01:17:17.731                     if rs:
2025-07-04 01:17:17.731                         rs.close()
2025-07-04 01:17:17.731                     if ps:
2025-07-04 01:17:17.732                         ps.free()
2025-07-04 01:17:17.732
2025-07-04 01:17:17.732         act.expected_stdout = f"""
2025-07-04 01:17:17.732             1000
2025-07-04 01:17:17.732             {query_map[1000][0]}
2025-07-04 01:17:17.732             {query_map[1000][1]}
2025-07-04 01:17:17.732             Select Expression
2025-07-04 01:17:17.732             ....-> Aggregate
2025-07-04 01:17:17.732             ........-> Filter
2025-07-04 01:17:17.732             ............-> Hash Join (semi) (keys: 1, total key length: 4)
2025-07-04 01:17:17.732             ................-> Table "TEST1" as "Q1_A" Full Scan
2025-07-04 01:17:17.732             ................-> Record Buffer (record length: 82)
2025-07-04 01:17:17.732             ....................-> Filter
2025-07-04 01:17:17.732             ........................-> Hash Join (semi) (keys: 1, total key length: 4)
2025-07-04 01:17:17.732             ............................-> Table "TEST2" as "Q1_B" Full Scan
2025-07-04 01:17:17.732             ............................-> Record Buffer (record length: 57)
2025-07-04 01:17:17.732             ................................-> Filter
2025-07-04 01:17:17.732             ....................................-> Table "TEST3" as "Q1_C" Full Scan
2025-07-04 01:17:17.732             10
2025-07-04 01:17:17.733
2025-07-04 01:17:17.733             2000
2025-07-04 01:17:17.733             {query_map[2000][0]}
2025-07-04 01:17:17.733             {query_map[2000][1]}
2025-07-04 01:17:17.733             Sub-query
2025-07-04 01:17:17.733             ....-> Filter
2025-07-04 01:17:17.733             ........-> Filter
2025-07-04 01:17:17.733             ............-> Table "TEST3" as "Q2_C" Full Scan
2025-07-04 01:17:17.733             Select Expression
2025-07-04 01:17:17.733             ....-> Aggregate
2025-07-04 01:17:17.733             ........-> Filter
2025-07-04 01:17:17.733             ............-> Hash Join (semi) (keys: 1, total key length: 4)
2025-07-04 01:17:17.733             ................-> Table "TEST1" as "Q2_A" Full Scan
2025-07-04 01:17:17.733             ................-> Record Buffer (record length: 33)
2025-07-04 01:17:17.733             ....................-> Filter
2025-07-04 01:17:17.733             ........................-> Table "TEST2" as "Q2_B" Full Scan
2025-07-04 01:17:17.733             10
2025-07-04 01:17:17.733
2025-07-04 01:17:17.733             3000
2025-07-04 01:17:17.733             {query_map[3000][0]}
2025-07-04 01:17:17.733             {query_map[3000][1]}
2025-07-04 01:17:17.733             Sub-query
2025-07-04 01:17:17.734             ....-> Filter
2025-07-04 01:17:17.734             ........-> Filter
2025-07-04 01:17:17.734             ............-> Table "TEST3" as "Q3_C" Full Scan
2025-07-04 01:17:17.734             Sub-query
2025-07-04 01:17:17.734             ....-> Filter
2025-07-04 01:17:17.734             ........-> Filter
2025-07-04 01:17:17.734             ............-> Table "TEST2" as "Q3_B" Full Scan
2025-07-04 01:17:17.734             Select Expression
2025-07-04 01:17:17.734             ....-> Aggregate
2025-07-04 01:17:17.734             ........-> Filter
2025-07-04 01:17:17.734             ............-> Table "TEST1" as "Q3_A" Full Scan
2025-07-04 01:17:17.734             10
2025-07-04 01:17:17.734
2025-07-04 01:17:17.734             4000
2025-07-04 01:17:17.734             {query_map[4000][0]}
2025-07-04 01:17:17.734             {query_map[4000][1]}
2025-07-04 01:17:17.734             Sub-query
2025-07-04 01:17:17.734             ....-> Filter
2025-07-04 01:17:17.734             ........-> Filter
2025-07-04 01:17:17.734             ............-> Table "TEST3" as "Q4_C" Full Scan
2025-07-04 01:17:17.734             Sub-query
2025-07-04 01:17:17.735             ....-> Filter
2025-07-04 01:17:17.735             ........-> Filter
2025-07-04 01:17:17.735             ............-> Table "TEST2" as "Q4_B" Full Scan
2025-07-04 01:17:17.735             Select Expression
2025-07-04 01:17:17.735             ....-> Aggregate
2025-07-04 01:17:17.735             ........-> Filter
2025-07-04 01:17:17.735             ............-> Table "TEST1" as "Q4_A" Full Scan
2025-07-04 01:17:17.735             10
2025-07-04 01:17:17.735         """
2025-07-04 01:17:17.735         act.stdout = capsys.readouterr().out
2025-07-04 01:17:17.735 >       assert act.clean_stdout == act.clean_expected_stdout
2025-07-04 01:17:17.735 E       assert
2025-07-04 01:17:17.735 E           1000
2025-07-04 01:17:17.735 E           select count(*) from test1 q1_a
2025-07-04 01:17:17.735 E           where
2025-07-04 01:17:17.735 E           q1_a.id in (
2025-07-04 01:17:17.735 E           select q1_b.pid from test2 q1_b
2025-07-04 01:17:17.735 E           where
2025-07-04 01:17:17.735 E           q1_b.id in (
2025-07-04 01:17:17.735 E           select q1_c.pid from test3 q1_c
2025-07-04 01:17:17.735 E           where q1_c.name like '%ABC%'
2025-07-04 01:17:17.736 E           )
2025-07-04 01:17:17.736 E           )
2025-07-04 01:17:17.736 E           Both sub-queries can (and should) be unnested.
2025-07-04 01:17:17.736 E           Select Expression
2025-07-04 01:17:17.736 E           ....-> Aggregate
2025-07-04 01:17:17.736 E           ........-> Filter
2025-07-04 01:17:17.736 E           ............-> Hash Join (semi)
2025-07-04 01:17:17.736 E         - ................-> Table "TEST1" as "Q1_A" Full Scan
2025-07-04 01:17:17.736 E         + ................-> Table "PUBLIC"."TEST1" as "Q1_A" Full Scan
2025-07-04 01:17:17.736 E           ................-> Record Buffer (record length: NN)
2025-07-04 01:17:17.736 E           ....................-> Filter
2025-07-04 01:17:17.736 E           ........................-> Hash Join (semi)
2025-07-04 01:17:17.736 E         - ............................-> Table "TEST2" as "Q1_B" Full Scan
2025-07-04 01:17:17.736 E         + ............................-> Table "PUBLIC"."TEST2" as "Q1_B" Full Scan
2025-07-04 01:17:17.736 E           ............................-> Record Buffer (record length: NN)
2025-07-04 01:17:17.736 E           ................................-> Filter
2025-07-04 01:17:17.736 E         - ....................................-> Table "TEST3" as "Q1_C" Full Scan
2025-07-04 01:17:17.736 E         + ....................................-> Table "PUBLIC"."TEST3" as "Q1_C" Full Scan
2025-07-04 01:17:17.737 E           10
2025-07-04 01:17:17.737 E           2000
2025-07-04 01:17:17.737 E           select count(*) from test1 q2_a
2025-07-04 01:17:17.737 E           where
2025-07-04 01:17:17.737 E           q2_a.id in (
2025-07-04 01:17:17.737 E           select q2_b.pid from test2 q2_b
2025-07-04 01:17:17.737 E           where
2025-07-04 01:17:17.737 E           1=1 or q2_b.id in (
2025-07-04 01:17:17.737 E           select q2_c.pid from test3 q2_c
2025-07-04 01:17:17.737 E           where q2_c.name like '%ABC%'
2025-07-04 01:17:17.737 E           )
2025-07-04 01:17:17.737 E           )
2025-07-04 01:17:17.737 E           Inner sub-query can NOT be unnested due to `OR` condition present, but the outer sub-query can
2025-07-04 01:17:17.737 E           Sub-query
2025-07-04 01:17:17.737 E           ....-> Filter
2025-07-04 01:17:17.737 E           ........-> Filter
2025-07-04 01:17:17.737 E         - ............-> Table "TEST3" as "Q2_C" Full Scan
2025-07-04 01:17:17.737 E         + ............-> Table "PUBLIC"."TEST3" as "Q2_C" Full Scan
2025-07-04 01:17:17.737 E           Select Expression
2025-07-04 01:17:17.738 E           ....-> Aggregate
2025-07-04 01:17:17.738 E           ........-> Filter
2025-07-04 01:17:17.738 E           ............-> Hash Join (semi)
2025-07-04 01:17:17.738 E         - ................-> Table "TEST1" as "Q2_A" Full Scan
2025-07-04 01:17:17.738 E         + ................-> Table "PUBLIC"."TEST1" as "Q2_A" Full Scan
2025-07-04 01:17:17.738 E           ................-> Record Buffer (record length: NN)
2025-07-04 01:17:17.738 E           ....................-> Filter
2025-07-04 01:17:17.738 E         - ........................-> Table "TEST2" as "Q2_B" Full Scan
2025-07-04 01:17:17.738 E         + ........................-> Table "PUBLIC"."TEST2" as "Q2_B" Full Scan
2025-07-04 01:17:17.738 E           10
2025-07-04 01:17:17.738 E           3000
2025-07-04 01:17:17.738 E           select count(*) from test1 q3_a
2025-07-04 01:17:17.738 E           where
2025-07-04 01:17:17.738 E           1=1 or q3_a.id in (
2025-07-04 01:17:17.738 E           select q3_b.pid from test2 q3_b
2025-07-04 01:17:17.738 E           where q3_b.id in (
2025-07-04 01:17:17.738 E           select id from test3 q3_c
2025-07-04 01:17:17.738 E           where q3_c.name like '%ABC%'
2025-07-04 01:17:17.739 E           )
2025-07-04 01:17:17.739 E           )
2025-07-04 01:17:17.739 E           Outer sub-query can NOT be unnested due to `OR` condition present, so the inner sub-query should NOT be unnested too
2025-07-04 01:17:17.739 E           Sub-query
2025-07-04 01:17:17.739 E           ....-> Filter
2025-07-04 01:17:17.739 E           ........-> Filter
2025-07-04 01:17:17.739 E         - ............-> Table "TEST3" as "Q3_C" Full Scan
2025-07-04 01:17:17.739 E         + ............-> Table "PUBLIC"."TEST3" as "Q3_C" Full Scan
2025-07-04 01:17:17.739 E           Sub-query
2025-07-04 01:17:17.739 E           ....-> Filter
2025-07-04 01:17:17.739 E           ........-> Filter
2025-07-04 01:17:17.739 E         - ............-> Table "TEST2" as "Q3_B" Full Scan
2025-07-04 01:17:17.739 E         + ............-> Table "PUBLIC"."TEST2" as "Q3_B" Full Scan
2025-07-04 01:17:17.739 E           Select Expression
2025-07-04 01:17:17.739 E           ....-> Aggregate
2025-07-04 01:17:17.739 E           ........-> Filter
2025-07-04 01:17:17.739 E         - ............-> Table "TEST1" as "Q3_A" Full Scan
2025-07-04 01:17:17.739 E         + ............-> Table "PUBLIC"."TEST1" as "Q3_A" Full Scan
2025-07-04 01:17:17.740 E           10
2025-07-04 01:17:17.740 E           4000
2025-07-04 01:17:17.740 E           select count(*) from test1 q4_a
2025-07-04 01:17:17.740 E           where
2025-07-04 01:17:17.740 E           1=1 or q4_a.id in (
2025-07-04 01:17:17.740 E           select id from test2 q4_b
2025-07-04 01:17:17.740 E           where
2025-07-04 01:17:17.740 E           1=1 or q4_b.id in (
2025-07-04 01:17:17.740 E           select id from test3 q4_c
2025-07-04 01:17:17.740 E           where q4_c.name like '%ABC%'
2025-07-04 01:17:17.740 E           )
2025-07-04 01:17:17.740 E           )
2025-07-04 01:17:17.740 E           Both sub-queries can NOT be unnested due to OR conditions present
2025-07-04 01:17:17.740 E           Sub-query
2025-07-04 01:17:17.740 E           ....-> Filter
2025-07-04 01:17:17.740 E           ........-> Filter
2025-07-04 01:17:17.740 E         - ............-> Table "TEST3" as "Q4_C" Full Scan
2025-07-04 01:17:17.740 E         + ............-> Table "PUBLIC"."TEST3" as "Q4_C" Full Scan
2025-07-04 01:17:17.740 E           Sub-query
2025-07-04 01:17:17.741 E           ....-> Filter
2025-07-04 01:17:17.744 E           ........-> Filter
2025-07-04 01:17:17.744 E         - ............-> Table "TEST2" as "Q4_B" Full Scan
2025-07-04 01:17:17.744 E         + ............-> Table "PUBLIC"."TEST2" as "Q4_B" Full Scan
2025-07-04 01:17:17.744 E           Select Expression
2025-07-04 01:17:17.744 E           ....-> Aggregate
2025-07-04 01:17:17.744 E           ........-> Filter
2025-07-04 01:17:17.744 E         - ............-> Table "TEST1" as "Q4_A" Full Scan
2025-07-04 01:17:17.744 E         + ............-> Table "PUBLIC"."TEST1" as "Q4_A" Full Scan
2025-07-04 01:17:17.744 E           10
2025-07-04 01:17:17.744
2025-07-04 01:17:17.744 tests\bugs\gh_8265_test.py:272: AssertionError
2025-07-04 01:17:17.745 ---------------------------- Captured stdout setup ----------------------------
2025-07-04 01:17:17.745 Creating db: localhost:H:\QA\temp\qa2024.tmp\fbqa\test_11746\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('>=5.0.2')
    def test_1(act: Action, capsys):
    
        srv_cfg = driver_config.register_server(name = f'srv_cfg_8265', config = '')
        db_cfg_name = f'db_cfg_8265'
        db_cfg_object = driver_config.register_database(name = db_cfg_name)
        db_cfg_object.server.value = srv_cfg.name
        db_cfg_object.database.value = str(act.db.db_path)
        if act.is_version('<6'):
            db_cfg_object.config.value = f"""
                SubQueryConversion = true
            """
    
        with connect(db_cfg_name, user = act.db.user, password = act.db.password) as con:
            cur = con.cursor()
            for q_idx, q_tuple in query_map.items():
                test_sql, qry_comment = q_tuple[:2]
                ps,rs = None, None
                try:
                    ps = cur.prepare(test_sql)
                    print(q_idx)
                    print(test_sql)
                    print(qry_comment)
    
                    # Print explained plan with padding eash line by dots in order to see indentations:
                    print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
                    rs = cur.execute(ps)
                    # Print data:
                    for r in rs:
                        print(r[0])
                except DatabaseError as e:
                    print(e.__str__())
                    print(e.gds_codes)
                finally:
                    # explained by hvlad, 26.10.24 17:42
                    if rs:
                        rs.close()
                    if ps:
                        ps.free()
    
        act.expected_stdout = f"""
            1000
            {query_map[1000][0]}
            {query_map[1000][1]}
            Select Expression
            ....-> Aggregate
            ........-> Filter
            ............-> Hash Join (semi) (keys: 1, total key length: 4)
            ................-> Table "TEST1" as "Q1_A" Full Scan
            ................-> Record Buffer (record length: 82)
            ....................-> Filter
            ........................-> Hash Join (semi) (keys: 1, total key length: 4)
            ............................-> Table "TEST2" as "Q1_B" Full Scan
            ............................-> Record Buffer (record length: 57)
            ................................-> Filter
            ....................................-> Table "TEST3" as "Q1_C" Full Scan
            10
    
            2000
            {query_map[2000][0]}
            {query_map[2000][1]}
            Sub-query
            ....-> Filter
            ........-> Filter
            ............-> Table "TEST3" as "Q2_C" Full Scan
            Select Expression
            ....-> Aggregate
            ........-> Filter
            ............-> Hash Join (semi) (keys: 1, total key length: 4)
            ................-> Table "TEST1" as "Q2_A" Full Scan
            ................-> Record Buffer (record length: 33)
            ....................-> Filter
            ........................-> Table "TEST2" as "Q2_B" Full Scan
            10
    
            3000
            {query_map[3000][0]}
            {query_map[3000][1]}
            Sub-query
            ....-> Filter
            ........-> Filter
            ............-> Table "TEST3" as "Q3_C" Full Scan
            Sub-query
            ....-> Filter
            ........-> Filter
            ............-> Table "TEST2" as "Q3_B" Full Scan
            Select Expression
            ....-> Aggregate
            ........-> Filter
            ............-> Table "TEST1" as "Q3_A" Full Scan
            10
    
            4000
            {query_map[4000][0]}
            {query_map[4000][1]}
            Sub-query
            ....-> Filter
            ........-> Filter
            ............-> Table "TEST3" as "Q4_C" Full Scan
            Sub-query
            ....-> Filter
            ........-> Filter
            ............-> Table "TEST2" as "Q4_B" Full Scan
            Select Expression
            ....-> Aggregate
            ........-> Filter
            ............-> Table "TEST1" as "Q4_A" Full Scan
            10
        """
        act.stdout = capsys.readouterr().out
>       assert act.clean_stdout == act.clean_expected_stdout
E       assert   
E           1000
E           select count(*) from test1 q1_a
E           where
E           q1_a.id in (
E           select q1_b.pid from test2 q1_b
E           where
E           q1_b.id in (
E           select q1_c.pid from test3 q1_c
E           where q1_c.name like '%ABC%'
E           )
E           )
E           Both sub-queries can (and should) be unnested.
E           Select Expression
E           ....-> Aggregate
E           ........-> Filter
E           ............-> Hash Join (semi)
E         - ................-> Table "TEST1" as "Q1_A" Full Scan
E         + ................-> Table "PUBLIC"."TEST1" as "Q1_A" Full Scan
E           ................-> Record Buffer (record length: NN)
E           ....................-> Filter
E           ........................-> Hash Join (semi)
E         - ............................-> Table "TEST2" as "Q1_B" Full Scan
E         + ............................-> Table "PUBLIC"."TEST2" as "Q1_B" Full Scan
E           ............................-> Record Buffer (record length: NN)
E           ................................-> Filter
E         - ....................................-> Table "TEST3" as "Q1_C" Full Scan
E         + ....................................-> Table "PUBLIC"."TEST3" as "Q1_C" Full Scan
E           10
E           2000
E           select count(*) from test1 q2_a
E           where
E           q2_a.id in (
E           select q2_b.pid from test2 q2_b
E           where
E           1=1 or q2_b.id in (
E           select q2_c.pid from test3 q2_c
E           where q2_c.name like '%ABC%'
E           )
E           )
E           Inner sub-query can NOT be unnested due to `OR` condition present, but the outer sub-query can
E           Sub-query
E           ....-> Filter
E           ........-> Filter
E         - ............-> Table "TEST3" as "Q2_C" Full Scan
E         + ............-> Table "PUBLIC"."TEST3" as "Q2_C" Full Scan
E           Select Expression
E           ....-> Aggregate
E           ........-> Filter
E           ............-> Hash Join (semi)
E         - ................-> Table "TEST1" as "Q2_A" Full Scan
E         + ................-> Table "PUBLIC"."TEST1" as "Q2_A" Full Scan
E           ................-> Record Buffer (record length: NN)
E           ....................-> Filter
E         - ........................-> Table "TEST2" as "Q2_B" Full Scan
E         + ........................-> Table "PUBLIC"."TEST2" as "Q2_B" Full Scan
E           10
E           3000
E           select count(*) from test1 q3_a
E           where
E           1=1 or q3_a.id in (
E           select q3_b.pid from test2 q3_b
E           where q3_b.id in (
E           select id from test3 q3_c
E           where q3_c.name like '%ABC%'
E           )
E           )
E           Outer sub-query can NOT be unnested due to `OR` condition present, so the inner sub-query should NOT be unnested too
E           Sub-query
E           ....-> Filter
E           ........-> Filter
E         - ............-> Table "TEST3" as "Q3_C" Full Scan
E         + ............-> Table "PUBLIC"."TEST3" as "Q3_C" Full Scan
E           Sub-query
E           ....-> Filter
E           ........-> Filter
E         - ............-> Table "TEST2" as "Q3_B" Full Scan
E         + ............-> Table "PUBLIC"."TEST2" as "Q3_B" Full Scan
E           Select Expression
E           ....-> Aggregate
E           ........-> Filter
E         - ............-> Table "TEST1" as "Q3_A" Full Scan
E         + ............-> Table "PUBLIC"."TEST1" as "Q3_A" Full Scan
E           10
E           4000
E           select count(*) from test1 q4_a
E           where
E           1=1 or q4_a.id in (
E           select id from test2 q4_b
E           where
E           1=1 or q4_b.id in (
E           select id from test3 q4_c
E           where q4_c.name like '%ABC%'
E           )
E           )
E           Both sub-queries can NOT be unnested due to OR conditions present
E           Sub-query
E           ....-> Filter
E           ........-> Filter
E         - ............-> Table "TEST3" as "Q4_C" Full Scan
E         + ............-> Table "PUBLIC"."TEST3" as "Q4_C" Full Scan
E           Sub-query
E           ....-> Filter
E           ........-> Filter
E         - ............-> Table "TEST2" as "Q4_B" Full Scan
E         + ............-> Table "PUBLIC"."TEST2" as "Q4_B" Full Scan
E           Select Expression
E           ....-> Aggregate
E           ........-> Filter
E         - ............-> Table "TEST1" as "Q4_A" Full Scan
E         + ............-> Table "PUBLIC"."TEST1" as "Q4_A" Full Scan
E           10

tests\bugs\gh_8265_test.py:272: 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 6.0.0.892 2025.07.03 af84a F F 962 497 2025.07.03 21:52:52.028 2025.07.03 21:52:52.990 2025.07.03 20:10:58.496 2025.07.03 20:10:58.993
2 6.0.0.892 2025.07.02 10f7d F F 954 494 2025.07.03 03:15:16.759 2025.07.03 03:15:17.713 2025.07.03 01:30:32.237 2025.07.03 01:30:32.731
3 6.0.0.889 2025.07.02 4b4e6 F F 965 484 2025.07.02 11:12:48.509 2025.07.02 11:12:49.474 2025.07.02 09:31:07.107 2025.07.02 09:31:07.591
4 6.0.0.889 2025.07.01 969ac F F 960 493 2025.07.02 03:18:23.739 2025.07.02 03:18:24.699 2025.07.02 01:33:24.715 2025.07.02 01:33:25.208
5 6.0.0.881 2025.06.27 7035d F F 960 498 2025.07.01 03:12:12.901 2025.07.01 03:12:13.861 2025.07.01 01:30:16.148 2025.07.01 01:30:16.646
6 6.0.0.877 2025.06.26 8e38f F F 959 486 2025.06.27 03:13:34.484 2025.06.27 03:13:35.443 2025.06.27 01:31:01.623 2025.06.27 01:31:02.109
7 6.0.0.876 2025.06.25 b1bec F F 957 495 2025.06.26 03:18:52.823 2025.06.26 03:18:53.780 2025.06.26 01:36:40.512 2025.06.26 01:36:41.007
8 6.0.0.863 2025.06.24 c3c20 F F 962 485 2025.06.25 08:03:02.375 2025.06.25 08:03:03.337 2025.06.25 01:30:42.059 2025.06.25 01:30:42.544
9 6.0.0.858 2025.06.24 cbbbf F F 954 502 2025.06.24 17:45:34.821 2025.06.24 17:45:35.775 2025.06.24 16:02:39.952 2025.06.24 16:02:40.454
10 6.0.0.858 2025.06.23 d377c F F 967 494 2025.06.24 08:57:36.355 2025.06.24 08:57:37.322 2025.06.24 07:15:02.237 2025.06.24 07:15:02.731
11 6.0.0.854 2025.06.23 10b58 F F 951 498 2025.06.23 15:38:28.286 2025.06.23 15:38:29.237 2025.06.23 13:54:34.937 2025.06.23 13:54:35.435
12 6.0.0.849 2025.06.20 7b79c F F 956 516 2025.06.21 03:16:47.154 2025.06.21 03:16:48.110 2025.06.21 01:31:14.544 2025.06.21 01:31:15.060
13 6.0.0.848 2025.06.19 c483c F F 948 492 2025.06.20 03:16:25.017 2025.06.20 03:16:25.965 2025.06.20 01:30:59.846 2025.06.20 01:31:00.338
14 6.0.0.845 2025.06.18 20191 F F 947 501 2025.06.19 03:16:03.180 2025.06.19 03:16:04.127 2025.06.19 01:30:47.523 2025.06.19 01:30:48.024
15 6.0.0.843 2025.06.16 995f4 F F 998 498 2025.06.18 03:15:50.543 2025.06.18 03:15:51.541 2025.06.18 01:30:29.336 2025.06.18 01:30:29.834
16 6.0.0.840 2025.06.14 29bca F F 954 524 2025.06.15 03:24:20.360 2025.06.15 03:24:21.314 2025.06.15 01:33:32.731 2025.06.15 01:33:33.255
17 6.0.0.838 2025.06.13 0e28a F F 939 490 2025.06.14 03:22:03.852 2025.06.14 03:22:04.791 2025.06.14 01:31:52.008 2025.06.14 01:31:52.498
18 6.0.0.834 2025.06.11 e889f F F 947 496 2025.06.12 03:19:36.060 2025.06.12 03:19:37.007 2025.06.12 01:32:23.262 2025.06.12 01:32:23.758
19 6.0.0.800 2025.06.10 1f226 P P 929 429 2025.06.11 02:04:33.423 2025.06.11 02:04:34.352 2025.06.11 00:55:38.537 2025.06.11 00:55:38.966
20 6.0.0.799 2025.06.07 be644 P P 1001 443 2025.06.10 02:04:12.445 2025.06.10 02:04:13.446 2025.06.10 00:55:15.691 2025.06.10 00:55:16.134
21 6.0.0.797 2025.06.06 303e8 P P 900 441 2025.06.07 02:03:00.605 2025.06.07 02:03:01.505 2025.06.07 00:55:03.752 2025.06.07 00:55:04.193
22 6.0.0.795 2025.05.29 7a71a P P 893 439 2025.06.06 02:02:50.880 2025.06.06 02:02:51.773 2025.06.06 00:55:01.732 2025.06.06 00:55:02.171
23 6.0.0.792 2025.05.28 ee5a8 P P 885 441 2025.05.29 02:01:56.701 2025.05.29 02:01:57.586 2025.05.29 00:55:08.068 2025.05.29 00:55:08.509
24 6.0.0.791 2025.05.27 02db8 P P 909 465 2025.05.28 02:02:25.651 2025.05.28 02:02:26.560 2025.05.28 00:54:51.067 2025.05.28 00:54:51.532
25 6.0.0.789 2025.05.21 64051 P P 905 439 2025.05.25 02:02:08.536 2025.05.25 02:02:09.441 2025.05.25 00:55:16.541 2025.05.25 00:55:16.980
26 6.0.0.787 2025.05.20 230ad P P 894 444 2025.05.21 02:01:04.608 2025.05.21 02:01:05.502 2025.05.21 00:54:25.990 2025.05.21 00:54:26.434
27 6.0.0.783 2025.05.12 37320 P P 893 441 2025.05.19 02:00:51.059 2025.05.19 02:00:51.952 2025.05.19 00:54:33.200 2025.05.19 00:54:33.641
28 6.0.0.779 2025.05.11 136fa P P 894 444 2025.05.12 02:02:59.421 2025.05.12 02:03:00.315 2025.05.12 00:55:10.843 2025.05.12 00:55:11.287
29 6.0.0.778 2025.05.07 d735e P P 893 439 2025.05.11 02:02:24.063 2025.05.11 02:02:24.956 2025.05.11 00:55:24.526 2025.05.11 00:55:24.965
30 6.0.0.776 2025.05.06 007cd P P 898 452 2025.05.07 02:01:36.010 2025.05.07 02:01:36.908 2025.05.07 00:55:01.948 2025.05.07 00:55:02.400
31 6.0.0.770 2025.05.05 82c4a P P 888 457 2025.05.06 02:01:54.708 2025.05.06 02:01:55.596 2025.05.06 00:55:08.457 2025.05.06 00:55:08.914
32 6.0.0.767 2025.05.01 cdd29 P P 880 469 2025.05.02 02:02:30.996 2025.05.02 02:02:31.876 2025.05.02 00:55:58.946 2025.05.02 00:55:59.415
33 6.0.0.762 2025.04.30 5cb15 P P 887 443 2025.05.01 02:00:57.937 2025.05.01 02:00:58.824 2025.05.01 00:55:20.298 2025.05.01 00:55:20.741
34 6.0.0.755 2025.04.29 739c6 P P 905 439 2025.04.30 02:01:11.203 2025.04.30 02:01:12.108 2025.04.30 00:55:02.482 2025.04.30 00:55:02.921
35 6.0.0.753 2025.04.27 29ab3 P P 889 481 2025.04.29 02:01:21.334 2025.04.29 02:01:22.223 2025.04.29 00:55:11.365 2025.04.29 00:55:11.846
36 6.0.0.745 2025.04.21 78ad8 P P 896 433 2025.04.26 02:02:47.336 2025.04.26 02:02:48.232 2025.04.26 00:55:27.601 2025.04.26 00:55:28.034
37 6.0.0.744 2025.04.19 e883a P P 897 446 2025.04.20 02:02:14.327 2025.04.20 02:02:15.224 2025.04.20 00:55:20.408 2025.04.20 00:55:20.854
38 6.0.0.742 2025.04.17 abc3b P P 898 437 2025.04.19 02:01:42.295 2025.04.19 02:01:43.193 2025.04.19 00:54:54.802 2025.04.19 00:54:55.239
39 6.0.0.737 2025.04.16 fe52b P P 887 448 2025.04.17 02:02:05.810 2025.04.17 02:02:06.697 2025.04.17 00:55:08.280 2025.04.17 00:55:08.728

Elapsed time, ms. Chart for last 39 runs:

Last commits information (all timestamps in UTC):