Describe the bug
sqlparse.split() does not correctly split Redshift transaction blocks that use BEGIN READ WRITE
This is still reproducible in sqlparse==0.5.5. It looks similar to the BEGIN TRANSACTION regression fixed after 0.5.4 in #826, but BEGIN READ WRITE remains affected
Appreciate for help
To Reproduce
import sqlparse
sql = """
BEGIN READ WRITE;
DELETE FROM schema.table_a USING table_a_temp
WHERE schema.table_a.id = table_a_temp.id;
INSERT INTO schema.table_a SELECT * FROM table_a_temp;
END TRANSACTION;
"""
statements = sqlparse.split(sql)
print(f'Statements found: {len(statements)}')
for idx, stmt in enumerate(statements, 1):
print(f'{idx}: {stmt}')Output from 0.5.3 version:
❯ uv run --with sqlparse==0.5.3 python
Python 3.12.8 (main, Jan 5 2025, 06:55:30) [Clang 19.1.6 ] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlparse
>>>
>>> sql = """
... BEGIN READ WRITE;
... DELETE FROM schema.table_a USING table_a_temp
... WHERE schema.table_a.id = table_a_temp.id;
... INSERT INTO schema.table_a SELECT * FROM table_a_temp;
... END TRANSACTION;
... """
>>>
>>> statements = sqlparse.split(sql)
>>>
>>> print(f'Statements found: {len(statements)}')
Statements found: 4
>>> for idx, stmt in enumerate(statements, 1):
... print(f'{idx}: {stmt}')
...
1: BEGIN READ WRITE;
2: DELETE FROM schema.table_a USING table_a_temp
WHERE schema.table_a.id = table_a_temp.id;
3: INSERT INTO schema.table_a SELECT * FROM table_a_temp;
4: END TRANSACTION;
>>>
Output from 0.5.4 and 0.5.5 version:
❯ uv run --with sqlparse==0.5.4 python
Python 3.12.8 (main, Jan 5 2025, 06:55:30) [Clang 19.1.6 ] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlparse
>>>
>>> sql = """
... BEGIN READ WRITE;
... DELETE FROM schema.table_a USING table_a_temp
... WHERE schema.table_a.id = table_a_temp.id;
... INSERT INTO schema.table_a SELECT * FROM table_a_temp;
... END TRANSACTION;
... """
>>>
>>> statements = sqlparse.split(sql)
>>>
>>> print(f'Statements found: {len(statements)}')
Statements found: 1
>>> for idx, stmt in enumerate(statements, 1):
... print(f'{idx}: {stmt}')
...
1: BEGIN READ WRITE;
DELETE FROM schema.table_a USING table_a_temp
WHERE schema.table_a.id = table_a_temp.id;
INSERT INTO schema.table_a SELECT * FROM table_a_temp;
END TRANSACTION;
>>>
Expected behavior
With sqlparse==0.5.5, this returns 1 statement containing the whole block.
Related variants:
BEGIN READ WRITE;
SELECT 1;
END;
also returns 1 statement in 0.5.5.
Expected behavior sqlparse.split() should split the block into individual statements:
1. BEGIN READ WRITE;
2. DELETE ...
3. INSERT ...
4. END TRANSACTION;
Additional context
According to Redshift documentation it's valid syntax, but it's broken since 0.5.4 version - https://docs.aws.amazon.com/redshift/latest/dg/r_BEGIN.html
| Version |
BEGIN TRANSACTION |
BEGIN READ WRITE |
| 0.5.1 |
OK |
OK |
| 0.5.2 |
OK |
OK |
| 0.5.3 |
OK |
OK |
| 0.5.4 |
broken |
broken |
| 0.5.5 |
fixed |
still broken |
Describe the bug
sqlparse.split()does not correctly split Redshift transaction blocks that useBEGIN READ WRITEThis is still reproducible in
sqlparse==0.5.5. It looks similar to theBEGIN TRANSACTIONregression fixed after0.5.4in #826, butBEGIN READ WRITEremains affectedAppreciate for help
To Reproduce
Output from 0.5.3 version:
Output from 0.5.4 and 0.5.5 version:
Expected behavior
With
sqlparse==0.5.5, this returns 1 statement containing the whole block.Related variants:
also returns 1 statement in 0.5.5.
Expected behavior sqlparse.split() should split the block into individual statements:
Additional context
According to Redshift documentation it's valid syntax, but it's broken since 0.5.4 version - https://docs.aws.amazon.com/redshift/latest/dg/r_BEGIN.html