Failing SQL Feature:
MERGE accepts illegal WHEN NOT MATCHED side / clause pairings and silently rewrites them into semantically different, legal SQL. SQL Server and BigQuery (the dialects providing BY TARGET / BY SOURCE) reject these pairings as syntax errors.
Full behavior matrix on master f41c0b8:
| Clause pairing |
Legal? |
Result on master |
WHEN MATCHED THEN UPDATE/DELETE |
yes |
accepted, round-trips |
WHEN MATCHED THEN INSERT |
no |
rejected |
WHEN NOT MATCHED THEN INSERT |
yes |
accepted, round-trips |
WHEN NOT MATCHED THEN UPDATE/DELETE |
no |
accepted, deparses as WHEN MATCHED (condition inverted) |
WHEN NOT MATCHED BY TARGET THEN INSERT |
yes |
accepted, round-trips |
WHEN NOT MATCHED BY TARGET THEN UPDATE/DELETE |
no |
accepted, deparses as WHEN MATCHED (condition inverted) |
WHEN NOT MATCHED BY SOURCE THEN UPDATE/DELETE |
yes |
accepted, round-trips |
WHEN NOT MATCHED BY SOURCE THEN INSERT |
no |
accepted, deparses as WHEN NOT MATCHED (BY SOURCE dropped, different row set) |
The parser therefore silently changes the meaning of the statement instead of failing, which is the worst failure mode for auditing / firewall tools built on the parser.
Root cause: MergeWhenNotMatched (JSqlParserCC.jjt:4472) accepts any clause for any side, while the AST can only represent the legal pairings: MergeUpdate:82 / MergeDelete:55 render WHEN MATCHED for any side other than SOURCE, and MergeInsert:77 always renders WHEN NOT MATCHED. Introduced with the BY TARGET / BY SOURCE support from #2421 / #2453, which validated the six legal combinations but not the illegal ones.
SQL Example:
// accepted on master, silently rewritten:
CCJSqlParserUtil.parse("MERGE INTO t USING s ON t.id = s.id"
+ " WHEN NOT MATCHED BY TARGET THEN UPDATE SET a = 1").toString()
// => MERGE INTO t USING s ON t.id = s.id WHEN MATCHED THEN UPDATE SET a = 1
CCJSqlParserUtil.parse("MERGE INTO t USING s ON t.id = s.id"
+ " WHEN NOT MATCHED BY SOURCE THEN INSERT (a) VALUES (1)").toString()
// => MERGE INTO t USING s ON t.id = s.id WHEN NOT MATCHED THEN INSERT (a) VALUES (1)
Software Information:
- JSqlParser version: 5.4-SNAPSHOT (master f41c0b8)
- Database: SQL Server / BigQuery (
WHEN NOT MATCHED [BY TARGET|BY SOURCE] dialects)
Failing SQL Feature:
MERGEaccepts illegalWHEN NOT MATCHEDside / clause pairings and silently rewrites them into semantically different, legal SQL. SQL Server and BigQuery (the dialects providingBY TARGET/BY SOURCE) reject these pairings as syntax errors.Full behavior matrix on master
f41c0b8:WHEN MATCHED THEN UPDATE/DELETEWHEN MATCHED THEN INSERTWHEN NOT MATCHED THEN INSERTWHEN NOT MATCHED THEN UPDATE/DELETEWHEN MATCHED(condition inverted)WHEN NOT MATCHED BY TARGET THEN INSERTWHEN NOT MATCHED BY TARGET THEN UPDATE/DELETEWHEN MATCHED(condition inverted)WHEN NOT MATCHED BY SOURCE THEN UPDATE/DELETEWHEN NOT MATCHED BY SOURCE THEN INSERTWHEN NOT MATCHED(BY SOURCEdropped, different row set)The parser therefore silently changes the meaning of the statement instead of failing, which is the worst failure mode for auditing / firewall tools built on the parser.
Root cause:
MergeWhenNotMatched(JSqlParserCC.jjt:4472) accepts any clause for any side, while the AST can only represent the legal pairings:MergeUpdate:82/MergeDelete:55renderWHEN MATCHEDfor any side other thanSOURCE, andMergeInsert:77always rendersWHEN NOT MATCHED. Introduced with theBY TARGET/BY SOURCEsupport from #2421 / #2453, which validated the six legal combinations but not the illegal ones.SQL Example:
Software Information:
WHEN NOT MATCHED [BY TARGET|BY SOURCE]dialects)