Snowflake - fix on merge statement corrupts SQL

[WittierDinosaur]

Search before asking

• I searched the issues and found no similar issues.

What Happened

I ran sqlfluff fix on the below minimum reproducible example. Note, removing any of the sections below seems to void the issue.

merge into foo.bar as tgt
using (
select
  foo::date as bar
from foo.bar
where
split(foo, '|')[2] REGEXP '^\\d+\\-\\d+\\-\\d+ \\d+\\:\\d+$'
OR
foo IN ('BAR','FOO')
) as src
on
  src.foo = tgt.foo
when matched then
update set
  tgt.foo = src.foo
;

Expected Behaviour

sqlfluff fix should fix all fixable linting errors, and not throw the above warning

Observed Behaviour

sqlfluff fix test.sql
==== finding fixable violations ====
WARNING    After fixes were applied, segment <SelectStatementSegment: ([L:  3, P:  1])> failed the match() parser check. Result: MatchResult(matched_segments=(), unmatched_segments=(<SelectClauseSegment: ([L:  3, P:  1])>, <NewlineSegment: ([L:  4, P: 19]) '\n'>, <WhitespaceSegment: ([L:  5, P:  1]) '  '>, <FromClauseSegment: ([L:  5, P:  1])>, <NewlineSegment: ([L:  5, P: 13]) '\n'>, <WhitespaceSegment: ([L:  6, P:  1]) '  '>, <WhereClauseSegment: ([L:  6, P:  1])>)) Fixes: [<LintFix: create_before @[L:  5, P:  1] create:'  '>, <LintFix: create_before @[L:  6, P:  1] create:'  '>] 
WARNING    After fixes were applied, segment <MergeMatchedClauseSegment: ([L: 13, P:  1])> failed the match() parser check. Result: MatchResult(matched_segments=(), unmatched_segments=(<KeywordSegment: ([L: 13, P:  1]) 'WHEN'>, <WhitespaceSegment: ([L: 13, P:  5]) ' '>, <KeywordSegment: ([L: 13, P:  6]) 'MATCHED'>, <WhitespaceSegment: ([L: 13, P: 13]) ' '>, <KeywordSegment: ([L: 13, P: 14]) 'THEN'>, <NewlineSegment: ([L: 13, P: 18]) '\n'>, <MergeUpdateClauseSegment: ([L: 14, P:  1])>)) Fixes: [<LintFix: replace @[L: 13, P:  1] edt:'when'->'WHEN'>, <LintFix: replace @[L: 13, P:  6] edt:'matched'->'MATCHED'>, <LintFix: replace @[L: 13, P: 14] edt:'then'->'THEN'>] 
WARNING    After fixes were applied, segment <SelectStatementSegment: ([L:  3, P:  1])> failed the match() parser check. Result: MatchResult(matched_segments=(), unmatched_segments=(<SelectClauseSegment: ([L:  3, P:  1])>, <NewlineSegment: ([L:  4, P: 19]) '\n'>, <WhitespaceSegment: ([L:  5, P:  1]) '  '>, <FromClauseSegment: ([L:  5, P:  1])>, <NewlineSegment: ([L:  5, P: 13]) '\n'>, <WhitespaceSegment: ([L:  6, P:  1]) '  '>, <WhereClauseSegment: ([L:  6, P:  1])>)) Fixes: [<LintFix: create_after @[L:  3, P:  1] create:'\n'>, <LintFix: delete @[L:  4, P: 19] delete:'\n'>] 
WARNING    One fix for L003 not applied, it would re-cause the same error.                                                                                                                                              
WARNING    One fix for L001 not applied, it would re-cause the same error.                                                                                                                                              
WARNING    One fix for L001 not applied, it would re-cause the same error.                                                                                                                                              
WARNING    One fix for L001 not applied, it would re-cause the same error.                                                                                                                                              
WARNING    One fix for L007 not applied, it would re-cause the same error.                                                                                                                                              
WARNING    One fix for L003 not applied, it would re-cause the same error.                                                                                                                                              
WARNING    One fix for L001 not applied, it would re-cause the same error.                                                                                                                                              
WARNING    Loop limit on fixes reached [10].                                                                                                                                                                            
==== no fixable linting violations found ====                                                                                                                                                                           
All Finished 📜 🎉!
  [25 unfixable linting violations found]

How to reproduce

Run sqlfluff fix on the above with the below config.

Dialect

Snowflake

Version

0.11.0

Configuration

[sqlfluff]
verbose = 0
nocolor = False
dialect = snowflake
templater = jinja
rules = None
exclude_rules = L011,L016,L031,L034,L035
recurse = 0
output_line_length = 120
runaway_limit = 10
ignore = None
ignore_templated_areas = True
encoding = autodetect
disable_noqa = False
sql_file_exts = .sql,.sql.j2,.dml
fix_even_unparsable = False

[sqlfluff:indentation]
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]
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 = 2
max_line_length = 120
indent_unit = space
comma_style = leading
allow_scalar = True
single_table_references = consistent
unquoted_identifiers_policy = all

[sqlfluff:rules:L003]
lint_templated_tokens = True

[sqlfluff:rules:L007]  # Keywords
operator_new_lines = after

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

[sqlfluff:rules:L011]  # Table aliasing
aliasing = implicit

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

[sqlfluff:rules:L013]  # Column expressions
allow_scalar = False

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

[sqlfluff:rules:L016]
ignore_comment_lines = False

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

[sqlfluff:rules:L030]  # Function names
extended_capitalisation_policy = lower

[sqlfluff:rules:L038]
select_clause_trailing_comma = forbid

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

[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]  # Statements must end with a semi-colon
multiline_newline = True
require_final_semicolon = True

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