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-03 05:19:31.358
2025-07-03 05:19:31.371 act = <firebird.qa.plugin.Action object at [hex]>
2025-07-03 05:19:31.382 capsys = <_pytest.capture.CaptureFixture object at [hex]>
2025-07-03 05:19:31.392
2025-07-03 05:19:31.398 @pytest.mark.version('>=5.0.2')
2025-07-03 05:19:31.402 def test_1(act: Action, capsys):
2025-07-03 05:19:31.408
2025-07-03 05:19:31.414 srv_cfg = driver_config.register_server(name = f'srv_cfg_8265', config = '')
2025-07-03 05:19:31.421 db_cfg_name = f'db_cfg_8265'
2025-07-03 05:19:31.428 db_cfg_object = driver_config.register_database(name = db_cfg_name)
2025-07-03 05:19:31.435 db_cfg_object.server.value = srv_cfg.name
2025-07-03 05:19:31.442 db_cfg_object.database.value = str(act.db.db_path)
2025-07-03 05:19:31.452 if act.is_version('<6'):
2025-07-03 05:19:31.464 db_cfg_object.config.value = f"""
2025-07-03 05:19:31.474 SubQueryConversion = true
2025-07-03 05:19:31.482 """
2025-07-03 05:19:31.488
2025-07-03 05:19:31.494 with connect(db_cfg_name, user = act.db.user, password = act.db.password) as con:
2025-07-03 05:19:31.502 cur = con.cursor()
2025-07-03 05:19:31.513 for q_idx, q_tuple in query_map.items():
2025-07-03 05:19:31.522 test_sql, qry_comment = q_tuple[:2]
2025-07-03 05:19:31.533 ps,rs = None, None
2025-07-03 05:19:31.543 try:
2025-07-03 05:19:31.557 ps = cur.prepare(test_sql)
2025-07-03 05:19:31.566 print(q_idx)
2025-07-03 05:19:31.573 print(test_sql)
2025-07-03 05:19:31.579 print(qry_comment)
2025-07-03 05:19:31.587
2025-07-03 05:19:31.597 # Print explained plan with padding eash line by dots in order to see indentations:
2025-07-03 05:19:31.605 print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
2025-07-03 05:19:31.612 rs = cur.execute(ps)
2025-07-03 05:19:31.618 # Print data:
2025-07-03 05:19:31.623 for r in rs:
2025-07-03 05:19:31.628 print(r[0])
2025-07-03 05:19:31.632 except DatabaseError as e:
2025-07-03 05:19:31.638 print(e.__str__())
2025-07-03 05:19:31.645 print(e.gds_codes)
2025-07-03 05:19:31.653 finally:
2025-07-03 05:19:31.658 # explained by hvlad, 26.10.24 17:42
2025-07-03 05:19:31.663 if rs:
2025-07-03 05:19:31.669 rs.close()
2025-07-03 05:19:31.675 if ps:
2025-07-03 05:19:31.682 ps.free()
2025-07-03 05:19:31.689
2025-07-03 05:19:31.696 act.expected_stdout = f"""
2025-07-03 05:19:31.702 1000
2025-07-03 05:19:31.707 {query_map[1000][0]}
2025-07-03 05:19:31.712 {query_map[1000][1]}
2025-07-03 05:19:31.718 Select Expression
2025-07-03 05:19:31.723 ....-> Aggregate
2025-07-03 05:19:31.729 ........-> Filter
2025-07-03 05:19:31.734 ............-> Hash Join (semi) (keys: 1, total key length: 4)
2025-07-03 05:19:31.740 ................-> Table "TEST1" as "Q1_A" Full Scan
2025-07-03 05:19:31.749 ................-> Record Buffer (record length: 82)
2025-07-03 05:19:31.761 ....................-> Filter
2025-07-03 05:19:31.769 ........................-> Hash Join (semi) (keys: 1, total key length: 4)
2025-07-03 05:19:31.775 ............................-> Table "TEST2" as "Q1_B" Full Scan
2025-07-03 05:19:31.781 ............................-> Record Buffer (record length: 57)
2025-07-03 05:19:31.787 ................................-> Filter
2025-07-03 05:19:31.794 ....................................-> Table "TEST3" as "Q1_C" Full Scan
2025-07-03 05:19:31.804 10
2025-07-03 05:19:31.812
2025-07-03 05:19:31.818 2000
2025-07-03 05:19:31.824 {query_map[2000][0]}
2025-07-03 05:19:31.831 {query_map[2000][1]}
2025-07-03 05:19:31.842 Sub-query
2025-07-03 05:19:31.852 ....-> Filter
2025-07-03 05:19:31.863 ........-> Filter
2025-07-03 05:19:31.873 ............-> Table "TEST3" as "Q2_C" Full Scan
2025-07-03 05:19:31.880 Select Expression
2025-07-03 05:19:31.887 ....-> Aggregate
2025-07-03 05:19:31.895 ........-> Filter
2025-07-03 05:19:31.907 ............-> Hash Join (semi) (keys: 1, total key length: 4)
2025-07-03 05:19:31.916 ................-> Table "TEST1" as "Q2_A" Full Scan
2025-07-03 05:19:31.922 ................-> Record Buffer (record length: 33)
2025-07-03 05:19:31.933 ....................-> Filter
2025-07-03 05:19:31.942 ........................-> Table "TEST2" as "Q2_B" Full Scan
2025-07-03 05:19:31.948 10
2025-07-03 05:19:31.956
2025-07-03 05:19:31.967 3000
2025-07-03 05:19:31.976 {query_map[3000][0]}
2025-07-03 05:19:31.983 {query_map[3000][1]}
2025-07-03 05:19:31.994 Sub-query
2025-07-03 05:19:32.002 ....-> Filter
2025-07-03 05:19:32.010 ........-> Filter
2025-07-03 05:19:32.019 ............-> Table "TEST3" as "Q3_C" Full Scan
2025-07-03 05:19:32.027 Sub-query
2025-07-03 05:19:32.034 ....-> Filter
2025-07-03 05:19:32.042 ........-> Filter
2025-07-03 05:19:32.053 ............-> Table "TEST2" as "Q3_B" Full Scan
2025-07-03 05:19:32.062 Select Expression
2025-07-03 05:19:32.070 ....-> Aggregate
2025-07-03 05:19:32.076 ........-> Filter
2025-07-03 05:19:32.082 ............-> Table "TEST1" as "Q3_A" Full Scan
2025-07-03 05:19:32.088 10
2025-07-03 05:19:32.095
2025-07-03 05:19:32.101 4000
2025-07-03 05:19:32.108 {query_map[4000][0]}
2025-07-03 05:19:32.115 {query_map[4000][1]}
2025-07-03 05:19:32.121 Sub-query
2025-07-03 05:19:32.128 ....-> Filter
2025-07-03 05:19:32.135 ........-> Filter
2025-07-03 05:19:32.142 ............-> Table "TEST3" as "Q4_C" Full Scan
2025-07-03 05:19:32.148 Sub-query
2025-07-03 05:19:32.154 ....-> Filter
2025-07-03 05:19:32.160 ........-> Filter
2025-07-03 05:19:32.166 ............-> Table "TEST2" as "Q4_B" Full Scan
2025-07-03 05:19:32.172 Select Expression
2025-07-03 05:19:32.179 ....-> Aggregate
2025-07-03 05:19:32.184 ........-> Filter
2025-07-03 05:19:32.190 ............-> Table "TEST1" as "Q4_A" Full Scan
2025-07-03 05:19:32.196 10
2025-07-03 05:19:32.202 """
2025-07-03 05:19:32.207 act.stdout = capsys.readouterr().out
2025-07-03 05:19:32.216 >       assert act.clean_stdout == act.clean_expected_stdout
2025-07-03 05:19:32.226 E       assert
2025-07-03 05:19:32.233 E           1000
2025-07-03 05:19:32.239 E           select count(*) from test1 q1_a
2025-07-03 05:19:32.245 E           where
2025-07-03 05:19:32.250 E           q1_a.id in (
2025-07-03 05:19:32.257 E           select q1_b.pid from test2 q1_b
2025-07-03 05:19:32.263 E           where
2025-07-03 05:19:32.271 E           q1_b.id in (
2025-07-03 05:19:32.281 E           select q1_c.pid from test3 q1_c
2025-07-03 05:19:32.287 E           where q1_c.name like '%ABC%'
2025-07-03 05:19:32.293 E           )
2025-07-03 05:19:32.299 E           )
2025-07-03 05:19:32.305 E           Both sub-queries can (and should) be unnested.
2025-07-03 05:19:32.311 E           Select Expression
2025-07-03 05:19:32.317 E           ....-> Aggregate
2025-07-03 05:19:32.323 E           ........-> Filter
2025-07-03 05:19:32.329 E           ............-> Hash Join (semi)
2025-07-03 05:19:32.334 E         - ................-> Table "TEST1" as "Q1_A" Full Scan
2025-07-03 05:19:32.340 E         + ................-> Table "PUBLIC"."TEST1" as "Q1_A" Full Scan
2025-07-03 05:19:32.354 E           ................-> Record Buffer (record length: NN)
2025-07-03 05:19:32.360 E           ....................-> Filter
2025-07-03 05:19:32.365 E           ........................-> Hash Join (semi)
2025-07-03 05:19:32.371 E         - ............................-> Table "TEST2" as "Q1_B" Full Scan
2025-07-03 05:19:32.377 E         + ............................-> Table "PUBLIC"."TEST2" as "Q1_B" Full Scan
2025-07-03 05:19:32.390 E           ............................-> Record Buffer (record length: NN)
2025-07-03 05:19:32.396 E           ................................-> Filter
2025-07-03 05:19:32.403 E         - ....................................-> Table "TEST3" as "Q1_C" Full Scan
2025-07-03 05:19:32.409 E         + ....................................-> Table "PUBLIC"."TEST3" as "Q1_C" Full Scan
2025-07-03 05:19:32.421 E           10
2025-07-03 05:19:32.426 E           2000
2025-07-03 05:19:32.432 E           select count(*) from test1 q2_a
2025-07-03 05:19:32.438 E           where
2025-07-03 05:19:32.444 E           q2_a.id in (
2025-07-03 05:19:32.451 E           select q2_b.pid from test2 q2_b
2025-07-03 05:19:32.457 E           where
2025-07-03 05:19:32.463 E           1=1 or q2_b.id in (
2025-07-03 05:19:32.469 E           select q2_c.pid from test3 q2_c
2025-07-03 05:19:32.475 E           where q2_c.name like '%ABC%'
2025-07-03 05:19:32.481 E           )
2025-07-03 05:19:32.487 E           )
2025-07-03 05:19:32.495 E           Inner sub-query can NOT be unnested due to `OR` condition present, but the outer sub-query can
2025-07-03 05:19:32.501 E           Sub-query
2025-07-03 05:19:32.507 E           ....-> Filter
2025-07-03 05:19:32.513 E           ........-> Filter
2025-07-03 05:19:32.520 E         - ............-> Table "TEST3" as "Q2_C" Full Scan
2025-07-03 05:19:32.528 E         + ............-> Table "PUBLIC"."TEST3" as "Q2_C" Full Scan
2025-07-03 05:19:32.541 E           Select Expression
2025-07-03 05:19:32.552 E           ....-> Aggregate
2025-07-03 05:19:32.561 E           ........-> Filter
2025-07-03 05:19:32.568 E           ............-> Hash Join (semi)
2025-07-03 05:19:32.576 E         - ................-> Table "TEST1" as "Q2_A" Full Scan
2025-07-03 05:19:32.584 E         + ................-> Table "PUBLIC"."TEST1" as "Q2_A" Full Scan
2025-07-03 05:19:32.598 E           ................-> Record Buffer (record length: NN)
2025-07-03 05:19:32.604 E           ....................-> Filter
2025-07-03 05:19:32.610 E         - ........................-> Table "TEST2" as "Q2_B" Full Scan
2025-07-03 05:19:32.616 E         + ........................-> Table "PUBLIC"."TEST2" as "Q2_B" Full Scan
2025-07-03 05:19:32.627 E           10
2025-07-03 05:19:32.632 E           3000
2025-07-03 05:19:32.636 E           select count(*) from test1 q3_a
2025-07-03 05:19:32.641 E           where
2025-07-03 05:19:32.647 E           1=1 or q3_a.id in (
2025-07-03 05:19:32.652 E           select q3_b.pid from test2 q3_b
2025-07-03 05:19:32.663 E           where q3_b.id in (
2025-07-03 05:19:32.675 E           select id from test3 q3_c
2025-07-03 05:19:32.686 E           where q3_c.name like '%ABC%'
2025-07-03 05:19:32.691 E           )
2025-07-03 05:19:32.697 E           )
2025-07-03 05:19:32.703 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-03 05:19:32.708 E           Sub-query
2025-07-03 05:19:32.713 E           ....-> Filter
2025-07-03 05:19:32.718 E           ........-> Filter
2025-07-03 05:19:32.724 E         - ............-> Table "TEST3" as "Q3_C" Full Scan
2025-07-03 05:19:32.730 E         + ............-> Table "PUBLIC"."TEST3" as "Q3_C" Full Scan
2025-07-03 05:19:32.742 E           Sub-query
2025-07-03 05:19:32.749 E           ....-> Filter
2025-07-03 05:19:32.756 E           ........-> Filter
2025-07-03 05:19:32.762 E         - ............-> Table "TEST2" as "Q3_B" Full Scan
2025-07-03 05:19:32.768 E         + ............-> Table "PUBLIC"."TEST2" as "Q3_B" Full Scan
2025-07-03 05:19:32.780 E           Select Expression
2025-07-03 05:19:32.785 E           ....-> Aggregate
2025-07-03 05:19:32.790 E           ........-> Filter
2025-07-03 05:19:32.795 E         - ............-> Table "TEST1" as "Q3_A" Full Scan
2025-07-03 05:19:32.800 E         + ............-> Table "PUBLIC"."TEST1" as "Q3_A" Full Scan
2025-07-03 05:19:32.810 E           10
2025-07-03 05:19:32.815 E           4000
2025-07-03 05:19:32.822 E           select count(*) from test1 q4_a
2025-07-03 05:19:32.828 E           where
2025-07-03 05:19:32.835 E           1=1 or q4_a.id in (
2025-07-03 05:19:32.841 E           select id from test2 q4_b
2025-07-03 05:19:32.853 E           where
2025-07-03 05:19:32.865 E           1=1 or q4_b.id in (
2025-07-03 05:19:32.873 E           select id from test3 q4_c
2025-07-03 05:19:32.879 E           where q4_c.name like '%ABC%'
2025-07-03 05:19:32.885 E           )
2025-07-03 05:19:32.891 E           )
2025-07-03 05:19:32.897 E           Both sub-queries can NOT be unnested due to OR conditions present
2025-07-03 05:19:32.903 E           Sub-query
2025-07-03 05:19:32.909 E           ....-> Filter
2025-07-03 05:19:32.914 E           ........-> Filter
2025-07-03 05:19:32.922 E         - ............-> Table "TEST3" as "Q4_C" Full Scan
2025-07-03 05:19:32.938 E         + ............-> Table "PUBLIC"."TEST3" as "Q4_C" Full Scan
2025-07-03 05:19:32.953 E           Sub-query
2025-07-03 05:19:32.960 E           ....-> Filter
2025-07-03 05:19:32.965 E           ........-> Filter
2025-07-03 05:19:32.972 E         - ............-> Table "TEST2" as "Q4_B" Full Scan
2025-07-03 05:19:32.977 E         + ............-> Table "PUBLIC"."TEST2" as "Q4_B" Full Scan
2025-07-03 05:19:32.989 E           Select Expression
2025-07-03 05:19:32.994 E           ....-> Aggregate
2025-07-03 05:19:33.000 E           ........-> Filter
2025-07-03 05:19:33.012 E         - ............-> Table "TEST1" as "Q4_A" Full Scan
2025-07-03 05:19:33.020 E         + ............-> Table "PUBLIC"."TEST1" as "Q4_A" Full Scan
2025-07-03 05:19:33.035 E           10
2025-07-03 05:19:33.047
2025-07-03 05:19:33.054 tests/bugs/gh_8265_test.py:272: AssertionError
2025-07-03 05:19:33.060 ---------------------------- Captured stdout setup -----------------------------
2025-07-03 05:19:33.066 Creating db: localhost:/var/tmp/qa_2024/test_11723/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.02 10f7d F F 990 727 2025.07.03 01:46:38.014 2025.07.03 01:46:39.004 2025.07.02 23:11:52.712 2025.07.02 23:11:53.439
2 6.0.0.889 2025.07.01 969ac F F 1069 434 2025.07.02 02:14:39.318 2025.07.02 02:14:40.387 2025.07.01 23:24:34.116 2025.07.01 23:24:34.550
3 6.0.0.884 2025.06.30 f7e5f F F 1007 648 2025.07.01 02:14:52.852 2025.07.01 02:14:53.859 2025.06.30 23:33:48.570 2025.06.30 23:33:49.218
4 6.0.0.881 2025.06.27 7035d F F 1182 802 2025.06.30 02:05:14.190 2025.06.30 02:05:15.372 2025.06.29 23:20:57.926 2025.06.29 23:20:58.728
5 6.0.0.877 2025.06.26 8e38f F F 941 721 2025.06.27 01:44:08.519 2025.06.27 01:44:09.460 2025.06.26 23:11:52.050 2025.06.26 23:11:52.771
6 6.0.0.876 2025.06.25 b1bec F F 918 538 2025.06.26 01:47:59.977 2025.06.26 01:48:00.895 2025.06.25 23:13:48.162 2025.06.25 23:13:48.700
7 6.0.0.863 2025.06.24 c3c20 F F 962 702 2025.06.25 01:48:18.014 2025.06.25 01:48:18.976 2025.06.24 23:13:35.118 2025.06.24 23:13:35.820
8 6.0.0.858 2025.06.23 8d6f7 F F 1009 715 2025.06.24 01:48:40.030 2025.06.24 01:48:41.039 2025.06.23 23:13:42.535 2025.06.23 23:13:43.250
9 6.0.0.849 2025.06.20 7b79c F F 1078 754 2025.06.21 02:00:10.743 2025.06.21 02:00:11.821 2025.06.20 23:22:50.144 2025.06.20 23:22:50.898
10 6.0.0.848 2025.06.19 c483c F F 1038 903 2025.06.20 01:56:33.209 2025.06.20 01:56:34.247 2025.06.19 23:19:36.295 2025.06.19 23:19:37.198
11 6.0.0.845 2025.06.18 22b12 F F 992 431 2025.06.19 02:05:11.763 2025.06.19 02:05:12.755 2025.06.18 23:22:53.255 2025.06.18 23:22:53.686
12 6.0.0.843 2025.06.16 995f4 F F 1109 587 2025.06.18 02:03:28.246 2025.06.18 02:03:29.355 2025.06.17 23:24:38.366 2025.06.17 23:24:38.953
13 6.0.0.840 2025.06.14 29bca F F 1088 761 2025.06.16 01:55:17.178 2025.06.16 01:55:18.266 2025.06.15 23:17:07.748 2025.06.15 23:17:08.509
14 6.0.0.838 2025.06.13 0e28a F F 1002 760 2025.06.14 02:08:21.824 2025.06.14 02:08:22.826 2025.06.13 23:24:38.991 2025.06.13 23:24:39.751
15 6.0.0.835 2025.06.12 2cf29 F F 991 571 2025.06.13 02:08:15.452 2025.06.13 02:08:16.443 2025.06.12 23:28:43.294 2025.06.12 23:28:43.865
16 6.0.0.834 2025.06.11 e889f F F 1014 412 2025.06.12 02:03:29.765 2025.06.12 02:03:30.779 2025.06.11 23:24:56.138 2025.06.11 23:24:56.550
17 6.0.0.800 2025.06.10 1f226 P P 884 777 2025.06.11 00:31:34.315 2025.06.11 00:31:35.199 2025.06.10 22:23:58.790 2025.06.10 22:23:59.567
18 6.0.0.799 2025.06.07 be644 P P 912 754 2025.06.10 00:33:55.575 2025.06.10 00:33:56.487 2025.06.09 22:23:38.313 2025.06.09 22:23:39.067
19 6.0.0.797 2025.06.06 303e8 P P 1704 653 2025.06.07 00:37:11.538 2025.06.07 00:37:13.242 2025.06.06 22:23:12.582 2025.06.06 22:23:13.235
20 6.0.0.795 2025.05.29 7a71a P P 1008 856 2025.06.06 00:31:25.086 2025.06.06 00:31:26.094 2025.06.05 22:22:18.783 2025.06.05 22:22:19.639
21 6.0.0.792 2025.05.28 b4327 P P 1916 798 2025.05.29 00:45:43.103 2025.05.29 00:45:45.019 2025.05.28 22:25:28.269 2025.05.28 22:25:29.067
22 6.0.0.791 2025.05.27 02db8 P P 1054 785 2025.05.28 00:43:45.997 2025.05.28 00:43:47.051 2025.05.27 22:24:31.435 2025.05.27 22:24:32.220
23 6.0.0.789 2025.05.21 64051 P P 1798 777 2025.05.25 00:36:12.438 2025.05.25 00:36:14.236 2025.05.24 22:23:37.133 2025.05.24 22:23:37.910
24 6.0.0.787 2025.05.20 230ad P P 1008 806 2025.05.21 00:33:25.351 2025.05.21 00:33:26.359 2025.05.20 22:22:00.861 2025.05.20 22:22:01.667
25 6.0.0.783 2025.05.12 37320 P P 1128 577 2025.05.19 12:06:19.166 2025.05.19 12:06:20.294 2025.05.19 10:10:10.928 2025.05.19 10:10:11.505
26 6.0.0.779 2025.05.11 136fa P P 1050 731 2025.05.12 00:28:12.528 2025.05.12 00:28:13.578 2025.05.11 22:21:26.792 2025.05.11 22:21:27.523
27 6.0.0.778 2025.05.07 d735e P P 1018 733 2025.05.08 00:30:17.909 2025.05.08 00:30:18.927 2025.05.07 22:20:10.274 2025.05.07 22:20:11.007
28 6.0.0.776 2025.05.06 007cd P P 1038 743 2025.05.07 00:24:37.043 2025.05.07 00:24:38.081 2025.05.06 22:20:00.756 2025.05.06 22:20:01.499
29 6.0.0.770 2025.05.05 82c4a P P 1825 594 2025.05.06 00:23:53.329 2025.05.06 00:23:55.154 2025.05.05 22:19:04.281 2025.05.05 22:19:04.875
30 6.0.0.767 2025.05.01 cdd29 P P 977 732 2025.05.02 00:20:35.431 2025.05.02 00:20:36.408 2025.05.01 22:18:19.455 2025.05.01 22:18:20.187
31 6.0.0.762 2025.04.30 5cb15 P P 997 400 2025.05.01 00:16:45.906 2025.05.01 00:16:46.903 2025.04.30 22:17:09.239 2025.04.30 22:17:09.639
32 6.0.0.755 2025.04.29 739c6 P P 1492 665 2025.04.30 00:20:29.098 2025.04.30 00:20:30.590 2025.04.29 22:18:22.930 2025.04.29 22:18:23.595
33 6.0.0.753 2025.04.27 29ab3 P P 1670 689 2025.04.28 00:21:54.130 2025.04.28 00:21:55.800 2025.04.27 22:17:39.777 2025.04.27 22:17:40.466
34 6.0.0.745 2025.04.21 78ad8 P P 1126 539 2025.04.26 00:18:30.562 2025.04.26 00:18:31.688 2025.04.25 22:19:35.381 2025.04.25 22:19:35.920
35 6.0.0.744 2025.04.19 e883a P P 895 663 2025.04.20 00:20:42.271 2025.04.20 00:20:43.166 2025.04.19 22:18:47.611 2025.04.19 22:18:48.274
36 6.0.0.742 2025.04.17 abc3b P P 1066 547 2025.04.19 00:22:40.175 2025.04.19 00:22:41.241 2025.04.18 22:19:09.100 2025.04.18 22:19:09.647
37 6.0.0.737 2025.04.16 fe52b P P 1732 423 2025.04.17 00:18:22.738 2025.04.17 00:18:24.470 2025.04.16 22:19:11.887 2025.04.16 22:19:12.310

Elapsed time, ms. Chart for last 37 runs:

Last commits information (all timestamps in UTC):