Skip to content

Snowflake MERGE statement does not require parentheses around the ON statement #1315

Closed

Description

@stewartbryson

Describe the bug
The Snowflake MERGE statement does not require the ON statement to be wrapped in parantheses.

To Reproduce
Steps to reproduce the behavior:

  1. Example SQL
MERGE INTO CONSULTANT T USING (
    SELECT
        *
    from
        CONSULTANT
) S ON S.ID = T.ID
WHEN MATCHED THEN
UPDATE
SET
    T.RESOURCE = S.RESOURCE
    WHEN NOT MATCHED THEN
INSERT
    (ID, RESOURCE)
values
    (S.ID, S.RESOURCE);
  1. Parsing this SQL using JSqlParser with these statements
String text = """
MERGE INTO CONSULTANT T USING (
    SELECT
        *
    from
        CONSULTANT
) S ON S.ID = T.ID
WHEN MATCHED THEN
UPDATE
SET
    T.RESOURCE = S.RESOURCE
    WHEN NOT MATCHED THEN
INSERT
    (ID, RESOURCE)
values
    (S.ID, S.RESOURCE);
"""
Statement query = CCJSqlParserUtil.parse(text)
  1. Exception
net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "S" <S_IDENTIFIER>
    at line 7, column 8.

Was expecting:

    "("

Expected behavior
When the parentheses are included around the ON, this works:

String text = """
MERGE INTO CONSULTANT T USING (
    SELECT
        *
    from
        CONSULTANT
) S ON (S.ID = T.ID)
WHEN MATCHED THEN
UPDATE
SET
    T.RESOURCE = S.RESOURCE
    WHEN NOT MATCHED THEN
INSERT
    (ID, RESOURCE)
values
    (S.ID, S.RESOURCE);
"""
Statement query = CCJSqlParserUtil.parse(text)

System

  • Database you are using: Snowflake
  • Java Version: 11
  • JSqlParser version: 4.1

Activity

  1. manticore-projects commented on Aug 26, 2021

    @manticore-projects
    Contributor

    Greetings.

    Most RDBMS require the brackets around the ON condition and JSQLParser does not support the Snowflake specific syntax yet. But you could send a PR implementing it.

  2. stewartbryson commented on Aug 27, 2021

    @stewartbryson
    Author

    Sure... I'll take a look. I'd take any advice or pointers on where to look.

  3. manticore-projects commented on Aug 27, 2021

    @manticore-projects
    Contributor

    Greetings.

    1. Amend the Merge Statement Java Class, add properties and modify the toString()
    2. Edit the Parser Grammar
    3. Amend the Deparser Java Class
    4. Add an Unit Test
    5. Ensure that all existing Unit Tests are still succeeding (do not break any stuff please)
    6. Ensure that the CI quality Assurance Test are succeeding
    7. Push your PR
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

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