Open
Description
Describe the bug
Many SQL languages support a syntax more or less similar to this :
- Trino :
WITH FUNCTION meaning_of_life()
RETURNS tinyint
BEGIN
DECLARE a tinyint DEFAULT CAST(6 as tinyint);
DECLARE b tinyint DEFAULT CAST(7 as tinyint);
RETURN a * b;
END
SELECT meaning_of_life()
CREATE OR REPLACE PROCEDURE mydataset.create_customer()
BEGIN
DECLARE id STRING;
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id)
VALUES(id);
SELECT FORMAT("Created customer %s", id);
END
which is incorrectly parsed by sqlparse, because it should ignore the semicolons inside the BEGIN ... END statement.
To Reproduce
Running this code :
sql = """
WITH
FUNCTION meaning_of_life()
RETURNS tinyint
BEGIN
DECLARE a tinyint DEFAULT CAST(6 as tinyint);
DECLARE b tinyint DEFAULT CAST(7 as tinyint);
RETURN a * b;
END
SELECT meaning_of_life();
"""
for stmt in sqlparse.parse(sql):
print("========== " + stmt.value)
Will print this:
==========
WITH
FUNCTION meaning_of_life()
RETURNS tinyint
BEGIN
DECLARE a tinyint DEFAULT CAST(6 as tinyint);
==========
DECLARE b tinyint DEFAULT CAST(7 as tinyint);
==========
RETURN a * b;
==========
END
SELECT meaning_of_life();
Expected behavior
It should print this instead
==========
WITH
FUNCTION meaning_of_life()
RETURNS tinyint
BEGIN
DECLARE a tinyint DEFAULT CAST(6 as tinyint);
DECLARE b tinyint DEFAULT CAST(7 as tinyint);
RETURN a * b;
END
SELECT meaning_of_life();
Versions (please complete the following information):
- Python: 3.12.3
- sqlparse: 0.5.3
Additional context
This issue is at the root of several issues previously opened in this project :
- Query formatting between BEGIN and END? #195
- Incorrect dump analysis of myqsl_dump #565
- Incorrect parsing oracle declare queries #614
- Split does not work for REPLACE PROCEDURE #691
- Split does not work properly #692
and in other projects as well, such as Superset: apache/superset#26162
Recommended fix
I suggest making the following change:
- Semicolons when inside a BEGIN ... END statement should be ignored
If you agree, I can work on a MR next week.
Metadata
Metadata
Assignees
Labels
No labels