GV

GeezerVison

The World Wide Web, restored to 800 pixels of glory.

Home|About|Gaming cabinet|Archives|How it works|Starter sites

On this site

  • Sign in
  • ActionsCI/CD and automation
  • CodespacesCloud development environments
  • All Features
  • Documentation
  • Training
  • Certifications
  • Build with Copilot
  • Customize Copilot
  • Integrations
  • Trending repos
  • GitHub Sponsors
  • Developer articles
  • Enterprises

This view

  • Original page
  • New address

GeezerVison is showing github.com — feat: support MERGE ... WHEN NOT MATCHED BY TARGET / BY SOURCE (#2421) by fudianchn · Pull Request #2453 · JSQLParser/JSqlParser

Conversation

@fudianchn

@fudianchn fudianchn commented Aug 7, 2026

Copy link
Copy Markdown
Contributor

Problem

BigQuery and SQL Server allow the MERGE ... WHEN NOT MATCHED clause to be qualified with BY TARGET (default, allows INSERT) or BY SOURCE (allows UPDATE/DELETE). JSQLParser currently rejects it as an UnsupportedStatement (#2421):

MERGE INTO target_table AS tt
USING (SELECT key, field FROM source_table) AS st ON tt.key = st.key
WHEN NOT MATCHED BY TARGET THEN INSERT (key, field) VALUES (st.key, st.field)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

The grammar's MergeWhenNotMatched had no BY branch and was hard-wired to INSERT.

Change

  • Grammar (JSqlParserCC.jjt): add an optional [BY TARGET|SOURCE] to WHEN NOT MATCHED, and let that branch produce INSERT, UPDATE, or DELETE (extracted MergeInsertClause helper so all three branches stay simple, mirroring MergeWhenMatched). Standard SQL WHEN [NOT] MATCHED (no BY) is unchanged.
  • Model: new MergeSide { TARGET, SOURCE } enum + a side field on MergeInsert/MergeUpdate/MergeDelete, rendered by both toString() and the MergeDeParser visitor.
  • TARGET/SOURCE are matched as identifiers (S_IDENTIFIER) rather than reserved keywords, so existing SQL that uses them as column/table names is unaffected.

Verification

  • New tests in MergeTest cover BY TARGET (INSERT) + BY SOURCE (DELETE), and BY SOURCE ... THEN UPDATE with an AND predicate — each with a round-trip assertSqlCanBeParsedAndDeparsed plus AST assertions on the side field.
  • Full suite: 4659 tests, 0 failures. checkstyle / pmd / license:check-file-header clean.
  • Backward compatible: existing WHEN MATCHED ... UPDATE/DELETE and WHEN NOT MATCHED ... INSERT tests keep passing (23 pre-existing Merge tests unaffected).

Fixes #2421.

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

manticore-projects reacted with thumbs up emoji
All reactions
  • 👍 1 reaction
@fudianchn
feat: support MERGE ... WHEN NOT MATCHED BY TARGET / BY SOURCE
56147f7
BigQuery and SQL Server allow the MERGE NOT MATCHED clause to be
qualified with BY TARGET (allows INSERT) or BY SOURCE (allows
UPDATE/DELETE), e.g.:

    MERGE INTO t USING s ON ...
    WHEN NOT MATCHED BY TARGET THEN INSERT (...)
    WHEN NOT MATCHED BY SOURCE THEN DELETE

Previously JSQLParser rejected the BY TARGET/BY SOURCE qualifier as an
UnsupportedStatement (JSQLParser#2421).

Add an optional [BY TARGET|SOURCE] to the WHEN NOT MATCHED production,
allow INSERT/update/delete on that branch, and store the side on the
MergeInsert/MergeUpdate/MergeDelete operations via a new MergeSide enum.
Standard WHEN [NOT] MATCHED (no BY) keeps working unchanged.

TARGET/SOURCE are matched as identifiers (not reserved keywords), so
existing SQL using them as column/table names is unaffected.

Fixes JSQLParser#2421

Signed-off-by: 付典 <[email protected]>
@manticore-projects

manticore-projects commented Aug 7, 2026 •
edited
Loading

Uh oh!

There was an error while loading. Please reload this page.

Copy link
Copy Markdown
Contributor

Run './gradlew spotlessApply' to fix all violations.
Good implementation otherwise, I would merge when formatting will comply.

All reactions

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

@fudianchn
style: apply spotlessApply formatting to merge classes
69916fd
Run ./gradlew spotlessApply per review feedback.
Pure formatting (Eclipse Google Style): line wrapping, Javadoc <p>
placement, enum constant grouping. No logic changes.

Signed-off-by: 付典 <[email protected]>
@fudianchn

fudianchn commented Aug 8, 2026

Copy link
Copy Markdown
Contributor Author

Thanks! I ran ./gradlew spotlessApply and pushed the formatting fixes (69916fd). It was purely cosmetic — line wrapping, Javadoc <p> placement, and enum constant grouping per the Eclipse Google Style config. The CI Gradle Check should pass now.

All reactions

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

@manticore-projects

manticore-projects commented Aug 8, 2026

Copy link
Copy Markdown
Contributor

Very nice, thank you much for work and effort!

All reactions

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

@manticore-projects
manticore-projects merged commit 25f3040 into JSQLParser:master Aug 8, 2026
7 checks passed

Uh oh!

There was an error while loading. Please reload this page.

This was referenced Aug 17, 2026
[BUG] MERGE silently rewrites illegal WHEN NOT MATCHED clause pairings into the opposite semantics #2480
Closed
fix: reject illegal MERGE WHEN NOT MATCHED clause pairings (#2480) #2481
Merged
@fudianchn
fudianchn deleted the merge-not-matched-by-target-source branch August 21, 2026 00:34
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Uh oh!

There was an error while loading. Please reload this page.

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

[BUG] BigQuery statement MERGE ... WHEN NOT MATCHED BY TARGET

Uh oh!

There was an error while loading. Please reload this page.

2 participants

@fudianchn @manticore-projects
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
You are visitor 01402520 — GeezerVison does not store the pages you look up.
Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.