2 @message |
assert
select * from test where 5000 in (x, y)
Select Expression
....-> Filter
........-> Table "TEST" Access By ID
............-> Bitmap Or
................-> Bitmap
+ ....................-> Index "TEST_Y_ASC" Range Scan (full match)
+ ................-> Bitmap
....................-> Index "TEST_X_ASC" Range Scan (full match)
+ select * from test where 5000 in (u, v)
+ Select Expression
+ ....-> Filter
+ ........-> Table "TEST" Access By ID
+ ............-> Bitmap Or
................-> Bitmap
- ....................-> Index "TEST_Y_ASC" Range Scan (full match)
+ ....................-> Index "TEST_V_DEC" Range Scan (full match)
+ ................-> Bitmap
+ ....................-> Index "TEST_U_DEC" Range Scan (full match)
- select * from test where 5000 in (u, v)
+ select * from test where 5000 in (x, u)
Select Expression
....-> Filter
........-> Table "TEST" Access By ID
............-> Bitmap Or
................-> Bitmap
....................-> Index "TEST_U_DEC" Range Scan (full match)
................-> Bitmap
- ....................-> Index "TEST_V_DEC" Range Scan (full match)
- select * from test where 5000 in (x, u)
- Select Expression
- ....-> Filter
- ........-> Table "TEST" Access By ID
- ............-> Bitmap Or
- ................-> Bitmap
....................-> Index "TEST_X_ASC" Range Scan (full match)
- ................-> Bitmap
- ....................-> Index "TEST_U_DEC" Range Scan (full match)
select * from test where 5000 in (v, y)
Select Expression
....-> Filter
........-> Table "TEST" Access By ID
............-> Bitmap Or
................-> Bitmap
+ ....................-> Index "TEST_Y_ASC" Range Scan (full match)
+ ................-> Bitmap
....................-> Index "TEST_V_DEC" Range Scan (full match)
- ................-> Bitmap
- ....................-> Index "TEST_Y_ASC" Range Scan (full match)
select * from test where 5000 in (x+y, u-v)
Select Expression
....-> Filter
+ ........-> Table "TEST" Full Scan
- ........-> Table "TEST" Access By ID
- ............-> Bitmap Or
- ................-> Bitmap
- ....................-> Index "TEST_C_ASC" Range Scan (full match)
- ................-> Bitmap
- ....................-> Index "TEST_C_DEC" Range Scan (full match)
select * from test where 5000 in (p, q) and p < 5001 and q > 4999
Select Expression
....-> Filter
........-> Table "TEST" Access By ID
............-> Bitmap Or
................-> Bitmap
+ ....................-> Index "TEST_Q_ASC" Range Scan (full match)
+ ................-> Bitmap
....................-> Index "TEST_P_ASC" Range Scan (full match)
- ................-> Bitmap
- ....................-> Index "TEST_Q_ASC" Range Scan (full match)
select * from test where 5000 in (p, q) and p > 4999 and q < 5001
Select Expression
....-> Filter
........-> Table "TEST" Access By ID
............-> Bitmap Or
................-> Bitmap
+ ....................-> Index "TEST_Q_DEC" Range Scan (full match)
+ ................-> Bitmap
....................-> Index "TEST_P_DEC" Range Scan (full match)
- ................-> Bitmap
- ....................-> Index "TEST_Q_DEC" Range Scan (full match)
LOG DETAILS:
2025-02-14 14:31:18.616
2025-02-14 14:31:18.624 act = <firebird.qa.plugin.Action object at [hex]>
2025-02-14 14:31:18.633 capsys = <_pytest.capture.CaptureFixture object at [hex]>
2025-02-14 14:31:18.639
2025-02-14 14:31:18.646 @pytest.mark.version('>=5.0.2')
2025-02-14 14:31:18.652 def test_1(act: Action, capsys):
2025-02-14 14:31:18.659 queries_map = { i : x for i,x in enumerate
2025-02-14 14:31:18.666 (
2025-02-14 14:31:18.676 [
2025-02-14 14:31:18.684 'select * from test where 5000 in (x, y)'
2025-02-14 14:31:18.691 ,'select * from test where 5000 in (u, v)'
2025-02-14 14:31:18.697 ,'select * from test where 5000 in (x, u)'
2025-02-14 14:31:18.704 ,'select * from test where 5000 in (v, y)'
2025-02-14 14:31:18.709 ,'select * from test where 5000 in (x+y, u-v)'
2025-02-14 14:31:18.715 ,'select * from test where 5000 in (p, q) and p < 5001 and q > 4999'
2025-02-14 14:31:18.721 ,'select * from test where 5000 in (p, q) and p > 4999 and q < 5001'
2025-02-14 14:31:18.727 ]
2025-02-14 14:31:18.733 )
2025-02-14 14:31:18.738 }
2025-02-14 14:31:18.744 with act.db.connect() as con:
2025-02-14 14:31:18.749 cur = con.cursor()
2025-02-14 14:31:18.755 for qry_idx, qry_txt in queries_map.items():
2025-02-14 14:31:18.761 ps = None
2025-02-14 14:31:18.766 try:
2025-02-14 14:31:18.771 ps = cur.prepare(qry_txt)
2025-02-14 14:31:18.776
2025-02-14 14:31:18.782 # Print explained plan with padding eash line by dots in order to see indentations:
2025-02-14 14:31:18.787 print(qry_txt)
2025-02-14 14:31:18.794 print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
2025-02-14 14:31:18.800 print('\n')
2025-02-14 14:31:18.807 except DatabaseError as e:
2025-02-14 14:31:18.813 print(e.__str__())
2025-02-14 14:31:18.819 print(e.gds_codes)
2025-02-14 14:31:18.826 finally:
2025-02-14 14:31:18.832 if ps:
2025-02-14 14:31:18.840 ps.free()
2025-02-14 14:31:18.846
2025-02-14 14:31:18.852
2025-02-14 14:31:18.860 expected_stdout = f"""
2025-02-14 14:31:18.866 {queries_map[ 0 ]}
2025-02-14 14:31:18.873 Select Expression
2025-02-14 14:31:18.881 ....-> Filter
2025-02-14 14:31:18.888 ........-> Table "TEST" Access By ID
2025-02-14 14:31:18.896 ............-> Bitmap Or
2025-02-14 14:31:18.903 ................-> Bitmap
2025-02-14 14:31:18.910 ....................-> Index "TEST_X_ASC" Range Scan (full match)
2025-02-14 14:31:18.916 ................-> Bitmap
2025-02-14 14:31:18.925 ....................-> Index "TEST_Y_ASC" Range Scan (full match)
2025-02-14 14:31:18.931
2025-02-14 14:31:18.939 {queries_map[ 1 ]}
2025-02-14 14:31:18.946 Select Expression
2025-02-14 14:31:18.953 ....-> Filter
2025-02-14 14:31:18.960 ........-> Table "TEST" Access By ID
2025-02-14 14:31:18.968 ............-> Bitmap Or
2025-02-14 14:31:18.976 ................-> Bitmap
2025-02-14 14:31:18.991 ....................-> Index "TEST_U_DEC" Range Scan (full match)
2025-02-14 14:31:19.007 ................-> Bitmap
2025-02-14 14:31:19.017 ....................-> Index "TEST_V_DEC" Range Scan (full match)
2025-02-14 14:31:19.024
2025-02-14 14:31:19.031 {queries_map[ 2 ]}
2025-02-14 14:31:19.038 Select Expression
2025-02-14 14:31:19.046 ....-> Filter
2025-02-14 14:31:19.054 ........-> Table "TEST" Access By ID
2025-02-14 14:31:19.063 ............-> Bitmap Or
2025-02-14 14:31:19.071 ................-> Bitmap
2025-02-14 14:31:19.078 ....................-> Index "TEST_X_ASC" Range Scan (full match)
2025-02-14 14:31:19.086 ................-> Bitmap
2025-02-14 14:31:19.093 ....................-> Index "TEST_U_DEC" Range Scan (full match)
2025-02-14 14:31:19.100
2025-02-14 14:31:19.107 {queries_map[ 3 ]}
2025-02-14 14:31:19.115 Select Expression
2025-02-14 14:31:19.122 ....-> Filter
2025-02-14 14:31:19.130 ........-> Table "TEST" Access By ID
2025-02-14 14:31:19.137 ............-> Bitmap Or
2025-02-14 14:31:19.144 ................-> Bitmap
2025-02-14 14:31:19.151 ....................-> Index "TEST_V_DEC" Range Scan (full match)
2025-02-14 14:31:19.157 ................-> Bitmap
2025-02-14 14:31:19.165 ....................-> Index "TEST_Y_ASC" Range Scan (full match)
2025-02-14 14:31:19.171
2025-02-14 14:31:19.178 {queries_map[ 4 ]}
2025-02-14 14:31:19.184 Select Expression
2025-02-14 14:31:19.191 ....-> Filter
2025-02-14 14:31:19.198 ........-> Table "TEST" Access By ID
2025-02-14 14:31:19.205 ............-> Bitmap Or
2025-02-14 14:31:19.212 ................-> Bitmap
2025-02-14 14:31:19.220 ....................-> Index "TEST_C_ASC" Range Scan (full match)
2025-02-14 14:31:19.226 ................-> Bitmap
2025-02-14 14:31:19.233 ....................-> Index "TEST_C_DEC" Range Scan (full match)
2025-02-14 14:31:19.240
2025-02-14 14:31:19.247 {queries_map[ 5 ]}
2025-02-14 14:31:19.254 Select Expression
2025-02-14 14:31:19.261 ....-> Filter
2025-02-14 14:31:19.267 ........-> Table "TEST" Access By ID
2025-02-14 14:31:19.276 ............-> Bitmap Or
2025-02-14 14:31:19.284 ................-> Bitmap
2025-02-14 14:31:19.291 ....................-> Index "TEST_P_ASC" Range Scan (full match)
2025-02-14 14:31:19.297 ................-> Bitmap
2025-02-14 14:31:19.305 ....................-> Index "TEST_Q_ASC" Range Scan (full match)
2025-02-14 14:31:19.312
2025-02-14 14:31:19.319 {queries_map[ 6 ]}
2025-02-14 14:31:19.326 Select Expression
2025-02-14 14:31:19.334 ....-> Filter
2025-02-14 14:31:19.341 ........-> Table "TEST" Access By ID
2025-02-14 14:31:19.349 ............-> Bitmap Or
2025-02-14 14:31:19.355 ................-> Bitmap
2025-02-14 14:31:19.361 ....................-> Index "TEST_P_DEC" Range Scan (full match)
2025-02-14 14:31:19.368 ................-> Bitmap
2025-02-14 14:31:19.374 ....................-> Index "TEST_Q_DEC" Range Scan (full match)
2025-02-14 14:31:19.380 """
2025-02-14 14:31:19.386
2025-02-14 14:31:19.391 act.expected_stdout = expected_stdout
2025-02-14 14:31:19.397 act.stdout = capsys.readouterr().out
2025-02-14 14:31:19.403 > assert act.clean_stdout == act.clean_expected_stdout
2025-02-14 14:31:19.409 E assert
2025-02-14 14:31:19.414 E select * from test where 5000 in (x, y)
2025-02-14 14:31:19.422 E Select Expression
2025-02-14 14:31:19.429 E ....-> Filter
2025-02-14 14:31:19.437 E ........-> Table "TEST" Access By ID
2025-02-14 14:31:19.444 E ............-> Bitmap Or
2025-02-14 14:31:19.459 E ................-> Bitmap
2025-02-14 14:31:19.470 E + ....................-> Index "TEST_Y_ASC" Range Scan (full match)
2025-02-14 14:31:19.482 E + ................-> Bitmap
2025-02-14 14:31:19.497 E ....................-> Index "TEST_X_ASC" Range Scan (full match)
2025-02-14 14:31:19.505 E + select * from test where 5000 in (u, v)
2025-02-14 14:31:19.514 E + Select Expression
2025-02-14 14:31:19.522 E + ....-> Filter
2025-02-14 14:31:19.532 E + ........-> Table "TEST" Access By ID
2025-02-14 14:31:19.548 E + ............-> Bitmap Or
2025-02-14 14:31:19.559 E ................-> Bitmap
2025-02-14 14:31:19.571 E - ....................-> Index "TEST_Y_ASC" Range Scan (full match)
2025-02-14 14:31:19.594 E + ....................-> Index "TEST_V_DEC" Range Scan (full match)
2025-02-14 14:31:19.610 E + ................-> Bitmap
2025-02-14 14:31:19.625 E + ....................-> Index "TEST_U_DEC" Range Scan (full match)
2025-02-14 14:31:19.636 E - select * from test where 5000 in (u, v)
2025-02-14 14:31:19.660 E + select * from test where 5000 in (x, u)
2025-02-14 14:31:19.680 E Select Expression
2025-02-14 14:31:19.688 E ....-> Filter
2025-02-14 14:31:19.695 E ........-> Table "TEST" Access By ID
2025-02-14 14:31:19.703 E ............-> Bitmap Or
2025-02-14 14:31:19.711 E ................-> Bitmap
2025-02-14 14:31:19.721 E ....................-> Index "TEST_U_DEC" Range Scan (full match)
2025-02-14 14:31:19.730 E ................-> Bitmap
2025-02-14 14:31:19.737 E - ....................-> Index "TEST_V_DEC" Range Scan (full match)
2025-02-14 14:31:19.744 E - select * from test where 5000 in (x, u)
2025-02-14 14:31:19.750 E - Select Expression
2025-02-14 14:31:19.757 E - ....-> Filter
2025-02-14 14:31:19.764 E - ........-> Table "TEST" Access By ID
2025-02-14 14:31:19.771 E - ............-> Bitmap Or
2025-02-14 14:31:19.780 E - ................-> Bitmap
2025-02-14 14:31:19.790 E ....................-> Index "TEST_X_ASC" Range Scan (full match)
2025-02-14 14:31:19.799 E - ................-> Bitmap
2025-02-14 14:31:19.808 E - ....................-> Index "TEST_U_DEC" Range Scan (full match)
2025-02-14 14:31:19.817 E select * from test where 5000 in (v, y)
2025-02-14 14:31:19.828 E Select Expression
2025-02-14 14:31:19.841 E ....-> Filter
2025-02-14 14:31:19.851 E ........-> Table "TEST" Access By ID
2025-02-14 14:31:19.862 E ............-> Bitmap Or
2025-02-14 14:31:19.873 E ................-> Bitmap
2025-02-14 14:31:19.883 E + ....................-> Index "TEST_Y_ASC" Range Scan (full match)
2025-02-14 14:31:19.893 E + ................-> Bitmap
2025-02-14 14:31:19.909 E ....................-> Index "TEST_V_DEC" Range Scan (full match)
2025-02-14 14:31:19.921 E - ................-> Bitmap
2025-02-14 14:31:19.931 E - ....................-> Index "TEST_Y_ASC" Range Scan (full match)
2025-02-14 14:31:19.942 E select * from test where 5000 in (x+y, u-v)
2025-02-14 14:31:19.957 E Select Expression
2025-02-14 14:31:19.973 E ....-> Filter
2025-02-14 14:31:19.983 E + ........-> Table "TEST" Full Scan
2025-02-14 14:31:19.998 E - ........-> Table "TEST" Access By ID
2025-02-14 14:31:20.009 E - ............-> Bitmap Or
2025-02-14 14:31:20.019 E - ................-> Bitmap
2025-02-14 14:31:20.028 E - ....................-> Index "TEST_C_ASC" Range Scan (full match)
2025-02-14 14:31:20.036 E - ................-> Bitmap
2025-02-14 14:31:20.044 E - ....................-> Index "TEST_C_DEC" Range Scan (full match)
2025-02-14 14:31:20.052 E select * from test where 5000 in (p, q) and p < 5001 and q > 4999
2025-02-14 14:31:20.060 E Select Expression
2025-02-14 14:31:20.068 E ....-> Filter
2025-02-14 14:31:20.076 E ........-> Table "TEST" Access By ID
2025-02-14 14:31:20.083 E ............-> Bitmap Or
2025-02-14 14:31:20.096 E ................-> Bitmap
2025-02-14 14:31:20.110 E + ....................-> Index "TEST_Q_ASC" Range Scan (full match)
2025-02-14 14:31:20.123 E + ................-> Bitmap
2025-02-14 14:31:20.134 E ....................-> Index "TEST_P_ASC" Range Scan (full match)
2025-02-14 14:31:20.143 E - ................-> Bitmap
2025-02-14 14:31:20.158 E - ....................-> Index "TEST_Q_ASC" Range Scan (full match)
2025-02-14 14:31:20.171 E select * from test where 5000 in (p, q) and p > 4999 and q < 5001
2025-02-14 14:31:20.180 E Select Expression
2025-02-14 14:31:20.191 E ....-> Filter
2025-02-14 14:31:20.199 E ........-> Table "TEST" Access By ID
2025-02-14 14:31:20.212 E ............-> Bitmap Or
2025-02-14 14:31:20.221 E ................-> Bitmap
2025-02-14 14:31:20.229 E + ....................-> Index "TEST_Q_DEC" Range Scan (full match)
2025-02-14 14:31:20.239 E + ................-> Bitmap
2025-02-14 14:31:20.247 E ....................-> Index "TEST_P_DEC" Range Scan (full match)
2025-02-14 14:31:20.258 E - ................-> Bitmap
2025-02-14 14:31:20.272 E - ....................-> Index "TEST_Q_DEC" Range Scan (full match)
2025-02-14 14:31:20.285
2025-02-14 14:31:20.298 tests/bugs/gh_8109_test.py:155: AssertionError
2025-02-14 14:31:20.307 ---------------------------- Captured stdout setup -----------------------------
2025-02-14 14:31:20.316 Creating db: localhost:/var/tmp/qa_2024/test_11647/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):
queries_map = { i : x for i,x in enumerate
(
[
'select * from test where 5000 in (x, y)'
,'select * from test where 5000 in (u, v)'
,'select * from test where 5000 in (x, u)'
,'select * from test where 5000 in (v, y)'
,'select * from test where 5000 in (x+y, u-v)'
,'select * from test where 5000 in (p, q) and p < 5001 and q > 4999'
,'select * from test where 5000 in (p, q) and p > 4999 and q < 5001'
]
)
}
with act.db.connect() as con:
cur = con.cursor()
for qry_idx, qry_txt in queries_map.items():
ps = None
try:
ps = cur.prepare(qry_txt)
# Print explained plan with padding eash line by dots in order to see indentations:
print(qry_txt)
print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
print('\n')
except DatabaseError as e:
print(e.__str__())
print(e.gds_codes)
finally:
if ps:
ps.free()
expected_stdout = f"""
{queries_map[ 0 ]}
Select Expression
....-> Filter
........-> Table "TEST" Access By ID
............-> Bitmap Or
................-> Bitmap
....................-> Index "TEST_X_ASC" Range Scan (full match)
................-> Bitmap
....................-> Index "TEST_Y_ASC" Range Scan (full match)
{queries_map[ 1 ]}
Select Expression
....-> Filter
........-> Table "TEST" Access By ID
............-> Bitmap Or
................-> Bitmap
....................-> Index "TEST_U_DEC" Range Scan (full match)
................-> Bitmap
....................-> Index "TEST_V_DEC" Range Scan (full match)
{queries_map[ 2 ]}
Select Expression
....-> Filter
........-> Table "TEST" Access By ID
............-> Bitmap Or
................-> Bitmap
....................-> Index "TEST_X_ASC" Range Scan (full match)
................-> Bitmap
....................-> Index "TEST_U_DEC" Range Scan (full match)
{queries_map[ 3 ]}
Select Expression
....-> Filter
........-> Table "TEST" Access By ID
............-> Bitmap Or
................-> Bitmap
....................-> Index "TEST_V_DEC" Range Scan (full match)
................-> Bitmap
....................-> Index "TEST_Y_ASC" Range Scan (full match)
{queries_map[ 4 ]}
Select Expression
....-> Filter
........-> Table "TEST" Access By ID
............-> Bitmap Or
................-> Bitmap
....................-> Index "TEST_C_ASC" Range Scan (full match)
................-> Bitmap
....................-> Index "TEST_C_DEC" Range Scan (full match)
{queries_map[ 5 ]}
Select Expression
....-> Filter
........-> Table "TEST" Access By ID
............-> Bitmap Or
................-> Bitmap
....................-> Index "TEST_P_ASC" Range Scan (full match)
................-> Bitmap
....................-> Index "TEST_Q_ASC" Range Scan (full match)
{queries_map[ 6 ]}
Select Expression
....-> Filter
........-> Table "TEST" Access By ID
............-> Bitmap Or
................-> Bitmap
....................-> Index "TEST_P_DEC" Range Scan (full match)
................-> Bitmap
....................-> Index "TEST_Q_DEC" Range Scan (full match)
"""
act.expected_stdout = expected_stdout
act.stdout = capsys.readouterr().out
> assert act.clean_stdout == act.clean_expected_stdout
E assert
E select * from test where 5000 in (x, y)
E Select Expression
E ....-> Filter
E ........-> Table "TEST" Access By ID
E ............-> Bitmap Or
E ................-> Bitmap
E + ....................-> Index "TEST_Y_ASC" Range Scan (full match)
E + ................-> Bitmap
E ....................-> Index "TEST_X_ASC" Range Scan (full match)
E + select * from test where 5000 in (u, v)
E + Select Expression
E + ....-> Filter
E + ........-> Table "TEST" Access By ID
E + ............-> Bitmap Or
E ................-> Bitmap
E - ....................-> Index "TEST_Y_ASC" Range Scan (full match)
E + ....................-> Index "TEST_V_DEC" Range Scan (full match)
E + ................-> Bitmap
E + ....................-> Index "TEST_U_DEC" Range Scan (full match)
E - select * from test where 5000 in (u, v)
E + select * from test where 5000 in (x, u)
E Select Expression
E ....-> Filter
E ........-> Table "TEST" Access By ID
E ............-> Bitmap Or
E ................-> Bitmap
E ....................-> Index "TEST_U_DEC" Range Scan (full match)
E ................-> Bitmap
E - ....................-> Index "TEST_V_DEC" Range Scan (full match)
E - select * from test where 5000 in (x, u)
E - Select Expression
E - ....-> Filter
E - ........-> Table "TEST" Access By ID
E - ............-> Bitmap Or
E - ................-> Bitmap
E ....................-> Index "TEST_X_ASC" Range Scan (full match)
E - ................-> Bitmap
E - ....................-> Index "TEST_U_DEC" Range Scan (full match)
E select * from test where 5000 in (v, y)
E Select Expression
E ....-> Filter
E ........-> Table "TEST" Access By ID
E ............-> Bitmap Or
E ................-> Bitmap
E + ....................-> Index "TEST_Y_ASC" Range Scan (full match)
E + ................-> Bitmap
E ....................-> Index "TEST_V_DEC" Range Scan (full match)
E - ................-> Bitmap
E - ....................-> Index "TEST_Y_ASC" Range Scan (full match)
E select * from test where 5000 in (x+y, u-v)
E Select Expression
E ....-> Filter
E + ........-> Table "TEST" Full Scan
E - ........-> Table "TEST" Access By ID
E - ............-> Bitmap Or
E - ................-> Bitmap
E - ....................-> Index "TEST_C_ASC" Range Scan (full match)
E - ................-> Bitmap
E - ....................-> Index "TEST_C_DEC" Range Scan (full match)
E select * from test where 5000 in (p, q) and p < 5001 and q > 4999
E Select Expression
E ....-> Filter
E ........-> Table "TEST" Access By ID
E ............-> Bitmap Or
E ................-> Bitmap
E + ....................-> Index "TEST_Q_ASC" Range Scan (full match)
E + ................-> Bitmap
E ....................-> Index "TEST_P_ASC" Range Scan (full match)
E - ................-> Bitmap
E - ....................-> Index "TEST_Q_ASC" Range Scan (full match)
E select * from test where 5000 in (p, q) and p > 4999 and q < 5001
E Select Expression
E ....-> Filter
E ........-> Table "TEST" Access By ID
E ............-> Bitmap Or
E ................-> Bitmap
E + ....................-> Index "TEST_Q_DEC" Range Scan (full match)
E + ................-> Bitmap
E ....................-> Index "TEST_P_DEC" Range Scan (full match)
E - ................-> Bitmap
E - ....................-> Index "TEST_Q_DEC" Range Scan (full match)
tests/bugs/gh_8109_test.py:155: AssertionError
|