DELIMITER on MySQL is not understood as expected
Search before asking
- I searched the issues and found no similar issues.
What Happened
On MySQL, it is possible to run the statement DELIMITER to specify a new delimiter that separates the next statements. This is useful for defining procedures for instance where a single statement (create the procedure) must contain several statements (statements within the procedure).
But even outside a procedure, such DELIMITER statement is not taken into account by SQLFluff.
Expected Behaviour
Try this online with "mysql" dialect:
DELIMITER $$
SELECT 1 $$
DELIMITER ;This is valid MySQL snippet that should not trigger any SQLFluff error about the character that must terminate a statement.
Observed Behaviour
Nevertheless, the observed behavior is the following: https://online.sqlfluff.com/fluffed?sql=H4sIAFoVzWIC_3Nx9fH09QxxDVJQUeHl4uUKdvVxdQ5RMITwXOCy1gD_88-jKQAAAA%3D%3D&dialect=mysql
PRS | 1 / 10 | Line 1, Position 10: Found unparsable section: ' $ SELECT 1$ DELIMITER'
L052 | 5 / 10 | Statements must end with a semi-colon.Because of the 1st delimiter section not ending with a semi-column, the content analysed for parsing is the whole snippet (until the 1st semi-column found), which is not the correct way to parse such MySQL statement. Therefore, no specific delimiter is understood in the context of SQLFluff, and this causes the L052 error.
How to reproduce
To reproduce, please try the following snippet in mysql dialect:
DELIMITER $$
SELECT 1$$
DELIMITER ;Dialect
mysql
Version
I'm using the latest version of SQLFluff: currently set to 1.1.0 (when I wrote this issue).
Configuration
== Raw Config:
core:
dialect: mysql
disable_noqa: False
encoding: utf-8
exclude_rules: L042
fix_even_unparsable:False
ignore: ['parsing']
ignore_templated_areas:True
nocolor: False
output_line_length: 80
recurse: True
rules: all
runaway_limit: 10
sql_file_exts: .sql,.sql.j2,.dml,.ddl
templater: raw
verbose: 2
indentation:
indented_ctes: False
indented_joins: True
indented_on_contents:True
indented_using_on: True
template_blocks_indent:True
rules:
allow_scalar: True
comma_style: trailing
indent_unit: space
max_line_length: 120
single_table_references:consistent
tab_space_size: 4
unquoted_identifiers_policy:all
L003:
hanging_indents: True
L007:
operator_new_lines: after
L010:
capitalisation_policy:upper
L011:
aliasing: explicit
L012:
aliasing: explicit
L014:
extended_capitalisation_policy:upper
L016:
ignore_comment_clauses:False
ignore_comment_lines:True
L026:
force_enable: False
L027:
L028:
force_enable: False
L029:
unquoted_identifiers_policy:all
L030:
capitalisation_policy:upper
extended_capitalisation_policy:consistent
L031:
force_enable: False
L036:
wildcard_policy: single
L038:
select_clause_trailing_comma:forbid
L040:
capitalisation_policy:upper
L042:
forbid_subquery_in: join
L047:
prefer_count_0: False
prefer_count_1: True
L051:
fully_qualify_join_types:inner
L052:
multiline_newline: False
require_final_semicolon:True
L054:
group_by_and_order_by_style:consistent
L057:
additional_allowed_characters:""
allow_space_in_identifier:False
quoted_identifiers_policy:all
unquoted_identifiers_policy:all
L059:
force_enable: False
prefer_quoted_identifiers:True
L062:
L063:
extended_capitalisation_policy:upper
L064:
force_enable: False
preferred_quoted_literal_style:single_quotes
L066:
templater:
unwrap_wrapped_queries:True
jinja:
apply_dbt_builtins: True Are you willing to work on and submit a PR to address the issue?
- Yes I am willing to submit a PR!
I did not check this as I'm not sure I could help, but if I have the time, I'll try to take a look. As this is parser related however, I don't want to break anything and entering into complicated stuff.
Code of Conduct
- I agree to follow this project's Code of Conduct
Source: sqlfluff/sqlfluff