Parser fails when using additional clauses in MERGE conditions

[seve-martinez]

Search before asking

• I searched the issues and found no similar issues.

What Happened

Using the MERGE command with snowflake will fail to parse if a WHEN NOT MATCHED AND clause exists.

The error

==== parsing violations ====
L:  90 | P:  38 |  PRS | Line 90, Position 38: Found unparsable section: '\n\nWHEN NOT
                       | matched\n AND S.change_type...'

The lines that failed

[L: 90, P: 38]      |                        [META] dedent:
[L: 90, P: 38]      |                unparsable:                                   !! Expected: 'Nothing...'
[L: 90, P: 38]      |                    newline:                                  '\n'
[L: 91, P:  1]      |                    newline:                                  '\n'
[L: 92, P:  1]      |                    raw:                                      'WHEN'
[L: 92, P:  5]      |                    whitespace:                               ' '
[L: 92, P:  6]      |                    raw:                                      'NOT'
[L: 92, P:  9]      |                    whitespace:                               ' '
[L: 92, P: 10]      |                    raw:                                      'matched'
[L: 92, P: 17]      |                    newline:                                  '\n'
[L: 93, P:  1]      |                    whitespace:                               '    '
[L: 93, P:  5]      |                    raw:                                      'AND'

Expected Behaviour

The file parses correctly for

MERGE INTO foo
USING
\t ...
WHEN MATCHED 
AND bar = baz 
THEN SET ...

WHEN NOT MATCHED 
AND baz > bar 
THEN INSERT ...

Observed Behaviour

The parser failed (see above)

How to reproduce

Add an AND condition to WHEN NOT MATCHED to any MERGE statement

Dialect

Snowflake

Version

0.10.0

Configuration

[sqlfluff]

# verbose is an integer (0-2) indicating the level of log output
verbose = 0

# Turn off color formatting of output
nocolor = False

# Supported dialects https://docs.sqlfluff.com/en/stable/dialects.html
# Or run 'sqlfluff dialects'
dialect = snowflake

# One of [raw|jinja|python|placeholder]
templater = jinja

# Comma separated list of rules to check, or None for all
rules = None

# Comma separated list of rules to exclude, or None
exclude_rules = None

# The depth to recursively parse to (0 for unlimited)
recurse = 0

# Below controls SQLFluff output, see max_line_length for SQL output
output_line_length = 80

# Number of passes to run before admitting defeat
runaway_limit = 10

# Ignore errors by category (one or more of the following, separated by commas: lexing,linting,parsing,templating)
ignore = None

# Ignore linting errors found within sections of code coming directly from
# templated code (e.g. from within Jinja curly braces. Note that it does not
# ignore errors from literal code found within template loops.
ignore_templated_areas = True

# can either be autodetect or a valid encoding e.g. utf-8, utf-8-sig
encoding = autodetect

# Ignore inline overrides (e.g. to test if still required)
disable_noqa = False

# Comma separated list of file extensions to lint
# NB: This config will only apply in the root folder
sql_file_exts = .sql,.sql.j2,.dml,.ddl

# Allow fix to run on files, even if they contain parsing errors
# Note altering this is NOT RECOMMENDED as can corrupt SQL
fix_even_unparsable = False

[sqlfluff:indentation]
# See https://docs.sqlfluff.com/en/stable/indentation.html
indented_joins = False
indented_ctes = False
indented_using_on = True
template_blocks_indent = True

[sqlfluff:templater]
unwrap_wrapped_queries = True

[sqlfluff:templater:jinja]
apply_dbt_builtins = True

[sqlfluff:templater:jinja:macros]
# Macros provided as builtins for dbt projects
dbt_ref = {% macro ref(model_ref) %}{{model_ref}}{% endmacro %}
dbt_source = {% macro source(source_name, table) %}{{source_name}}_{{table}}{% endmacro %}
dbt_config = {% macro config() %}{% for k in kwargs %}{% endfor %}{% endmacro %}
dbt_var = {% macro var(variable, default='') %}item{% endmacro %}
dbt_is_incremental = {% macro is_incremental() %}True{% endmacro %}

# Some rules can be configured directly from the config common to other rules
[sqlfluff:rules]
tab_space_size = 4
max_line_length = 80
indent_unit = space
comma_style = trailing
allow_scalar = True
single_table_references = consistent
unquoted_identifiers_policy = all


# Some rules have their own specific config.
[sqlfluff:rules:L007]  # Keywords
operator_new_lines = after

[sqlfluff:rules:L010]  # Keywords
capitalisation_policy = upper

[sqlfluff:rules:L011]  # Aliasing
aliasing = explicit

[sqlfluff:rules:L012]  # Aliasing
aliasing = explicit

[sqlfluff:rules:L014]  # Unquoted identifiers
extended_capitalisation_policy = lower

[sqlfluff:rules:L016]
ignore_comment_lines = False

[sqlfluff:rules:L026]
force_enable = False

[sqlfluff:rules:L028]
force_enable = False

[sqlfluff:rules:L029]  # Keyword identifiers
unquoted_identifiers_policy = aliases
quoted_identifiers_policy = none

[sqlfluff:rules:L030]  # Function names
capitalisation_policy = consistent

[sqlfluff:rules:L038]
select_clause_trailing_comma = forbid

[sqlfluff:rules:L040]  # Null & Boolean Literals
capitalisation_policy = lower

[sqlfluff:rules:L042]
# By default, allow subqueries in from clauses, but not join clauses.
forbid_subquery_in = join

[sqlfluff:rules:L047]  # Consistent syntax to count all rows
prefer_count_1 = False
prefer_count_0 = False

[sqlfluff:rules:L052]  # Semi-colon formatting approach.
multiline_newline = False
require_final_semicolon = False

[sqlfluff:rules:L054]  # GROUP BY/ORDER BY column references.
group_by_and_order_by_style = implicit

[sqlfluff:rules:L057]  # Special characters in identifiers
unquoted_identifiers_policy = all
quoted_identifiers_policy = all
allow_space_in_identifier = False

Are you willing to work on and submit a PR to address the issue?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[seve-martinez]

hi @WittierDinosaur, please assign this bug to me. After reviewing the contributor docs, I'd like to give it a shot.

[tunetheweb]

Go for it @The-Loud . We don't really use the assign functionality as only maintainers can assign which is a bit restrictive. A comment like you added is sufficient.

BTW not sure if you saw our wiki page on this, but it's a great place to start for your first dialect contribution: https://github.com/sqlfluff/sqlfluff/wiki/Contributing-Dialect-Changes

Give us a shout if you've any questions or need any help.