Repository navigation
[BUG] DDL clause rewriting removes DELETE and UPDATE WHERE clauses after SQL comments #58
Description
Description
The Databricks DDL compatibility rewrite is applied to every SQL statement and matches text inside comments. A valid DELETE or UPDATE containing a comment with ) USING DELTA loses everything after that text, including the real WHERE clause on the next line. MiniLake reports success after modifying every row.
Verified on MiniLake 1.9.0, using a frozen source snapshot at local commit e24f46a7753ff0be30df3d83978c370c817f89cd, based on upstream fe8837dcfac9df621dfabc440abc07ebb4cdbcbb. The affected code is identical on upstream main. Reproduced against a real HTTP server with fresh disposable data, Python 3.14.3 and DuckDB 1.5.6. The original SQL was also executed directly in DuckDB as a control.
Affected Components
- services/sql_statements.py:308 —
_CREATE_TABLE_USING_CLAUSE_RE - services/sql_statements.py:311 —
_strip_databricks_ddl_clauses - services/sql_statements.py:739 —
_execute_sql_real_typed - Statement Execution, SQL jobs and MCP SQL calls all use this execution path.
Steps to Reproduce
Use a fresh disposable instance. Create warehouse W with POST /api/2.0/sql/warehouses, body {"name":"ddl-repro"}.
Send the following SQL through POST /api/2.0/sql/statements with W as warehouse_id, one statement per request:
-
CREATE TABLE comment_guard AS SELECT 1 AS id UNION ALL SELECT 2;
-
DELETE FROM comment_guard -- DDL example: ) USING DELTA WHERE id = 1
-
SELECT * FROM comment_guard;
The request body for step 2 is:
{"warehouse_id":"W","statement":"DELETE FROM comment_guard -- DDL example: ) USING DELTA\nWHERE id = 1"}A verified UPDATE variant starts with rows 1 and 2 and executes:
UPDATE comment_guard SET id = id + 10 -- DDL example: ) USING DELTA
WHERE id = 1Expected Behavior
Comments have no effect on execution. DELETE removes row 1 and leaves row 2. The UPDATE variant changes only row 1, leaving sorted rows [[2],[11]].
Direct execution of the original DELETE in the same DuckDB version correctly leaves row 2.
Actual Behavior
DELETE returns HTTP 200 / SUCCEEDED and removes both rows: the following SELECT returns []. UPDATE modifies both rows and returns [[11],[12]].
Root Cause Analysis
The unanchored, DOTALL regex \)\s*USING\s+\w+.*\Z matches from the closing parenthesis inside the line comment to the end of the entire statement. The replacement leaves:
DELETE FROM comment_guard -- DDL example: )The WHERE clause has been erased. The remaining SQL is valid and unconditional. The helper does not establish that the statement is CREATE TABLE or that the match is outside a quoted/comment token.
Impact
A harmless SQL comment can turn a bounded DELETE/UPDATE into a whole-table mutation without any warning or failed status. This causes direct data loss or corruption in supported local SQL, job and CI workflows. No EXTERNAL table or Delta runtime is required.
Suggested Fix
Apply the compatibility rewrite only to a parsed CREATE TABLE statement, and only to the actual trailing USING/storage clauses outside strings, quoted identifiers and comments. Reuse/tokenize with the existing lexical handling rather than applying a raw regex to arbitrary statements.
Test Coverage Gaps
Existing SQL tests thoroughly cover backticks in strings/comments, but do not exercise DDL-tail stripping on non-DDL statements. Add DELETE/UPDATE comment cases, SELECT/string cases and legitimate CREATE TABLE clauses. Assert exact remaining rows, not merely SUCCEEDED status.
Severity
High
Confidence
High — reproduced through real HTTP, compared with direct DuckDB execution, and verified for both DELETE and UPDATE.
Recommended Labels
bug, high-priority
Existing Issue Matches
No reasonable duplicate found. Reviewed all open issues, the closed-issue search (no results), and open PRs; searched the helper name, USING/WHERE, comments and destructive SQL symptoms. Open PR #54 only fixes Arrow column serialization; #29 updates virtualenv.
#41 concerns a different rewrite: replacing EXTERNAL table names inside string literals. This defect is in the DDL-clause stripper, does not require an EXTERNAL table, and removes the actual DELETE/UPDATE predicate.
Activity
fix(sql): match DDL tails outside comments only (dmux#58)
fix(sql): restrict DDL tail rewrite to CREATE TABLE (#58)