Skip to content

[BUG] MERGE silently rewrites illegal WHEN NOT MATCHED clause pairings into the opposite semantics #2480

Description

@fudianchn

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)

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions