bug: "Dataform: Format Document" not working

[ivan-toriya]

Describe the bug

[Error formatting]: Ran: sqlfluff fix -q --config=/home/ivan/my-project/.vscode-dataform-tools/.sqlfluff /tmp/format.sql. Error: Error: [1 templating/parsing errors found]

To Reproduce
Run "Dataform: Format Document" on a file.

Note: file compiled successfully

.sqlfluff (default)

[sqlfluff]
templater = placeholder
dialect = bigquery
output_line_length = 120

# EXCLUDED RULES
# ==============
# AL07 - Avoid table aliases in from clauses and join conditions.
# ST06 - Select wildcards then simple targets before calculations and aggregates.
# ST07 - Prefer specifying join keys instead of using USING.
# AM03 - Ambiguous ordering directions for columns in order by clause.
# ST04 - Dont mess up nested CASE statement
# LT05 - Line is too long
# AL05 - Tables should not be aliased if that alias is not used.
exclude_rules = AL07, ST06, ST07, AM03, ST04, LT05, AL05

[sqlfluff:rules]
allow_scalar = False

[sqlfluff:indentation]
# See https://docs.sqlfluff.com/en/stable/layout.html#configuring-indent-locations
indent_unit = space
tab_space_size = 2
indented_joins = False
indented_ctes = False
indented_using_on = True
indented_on_contents = True
indented_then = True
indented_then_contents = True
allow_implicit_indents = False
template_blocks_indent = True
# This is a comma separated list of elements to skip
# indentation edits to.
skip_indentation_in = script_content
# If comments are found at the end of long lines, we default to moving
# them to the line _before_ their current location as the convention is
# that a comment precedes the line it describes. However if you prefer
# comments moved _after_, this configuration setting can be set to "after".
trailing_comments = before
# To exclude comment lines from indentation entirely set this to "True".
ignore_comment_lines = False

[sqlfluff:layout:type:where_clause]
line_position = alone:strict

[sqlfluff:layout:type:binary_operator]
line_position = leading

[sqlfluff:layout:type:comparison_operator]
line_position = trailing

[sqlfluff:layout:type:alias_expression]
# We want non-default spacing _before_ the alias expressions.
spacing_before = align
align_within = select_clause
align_scope = bracketed

[sqlfluff:rules:capitalisation.keywords]
# Keywords must be capitalised
capitalisation_policy = upper

[sqlfluff:rules:capitalisation.literals]
# Null & Boolean Literals eg: NULL, TRUE, FALSE
capitalisation_policy = upper

[sqlfluff:rules:capitalisation.types]
# Data Types eg: INT, STR
extended_capitalisation_policy = upper

[sqlfluff:rules:capitalisation.identifiers]
# Unquoted identifiers
extended_capitalisation_policy = upper
unquoted_identifiers_policy=all

[sqlfluff:rules:capitalisation.functions]
# Function names
capitalisation_policy = upper
extended_capitalisation_policy = upper

[sqlfluff:rules:ambiguous.join]
# Fully qualify JOIN clause
fully_qualify_join_types = inner

[sqlfluff:rules:aliasing.length]
# Minimum string length when creating an alias
min_alias_length = 3

[sqlfluff:rules:aliasing.table]
# Aliasing preference for tables, ie needs an AS
aliasing = explicit

[sqlfluff:rules:aliasing.column]
# Aliasing preference for columns, ie needs an AS
aliasing = explicit

[sqlfluff:rules:layout.commas]
# Leading or trailing commas
line_position = leading

[sqlfluff:layout:type:comma]
line_position = leading


[sqlfluff:rules:convention.select_trailing_comma]
# No trailing comma at end of SELECT, ie before FROM (after last column name)
select_clause_trailing_comma = forbid

[sqlfluff:rules:ambiguous.column_references]
# GROUP BY/ORDER BY column references (i.e. implicit by position or explicit by name)
group_by_and_order_by_style = explicit

[sqlfluff:rules:references.special_chars]
# Special characters in identifiers
unquoted_identifiers_policy = all
quoted_identifiers_policy = all
allow_space_in_identifier = False
additional_allowed_characters = ["", $]

[sqlfluff:rules:references.keywords]
# Keywords should not be used as identifiers.
unquoted_identifiers_policy = all
quoted_identifiers_policy = none

[sqlfluff:templater:placeholder]
param_regex = (?s)\${\s*(?P<param_name>[\w_]+)(?P<rec>(?:[^{}]+|{(?&rec)})*+)}|(?P<param_name>config|pre_operations|post_operations)\s*{(?&rec)}
ref = ref_table_placeholder
self = self_table_placeholder
when =
config =
pre_operations =

Expected behavior
sqlfluff formats the file

Environment:

• Operating system: Linux
• Dataform cli version: 3.0.19
• Dataform core version: 3.0.19
• Version of the extension: 0.12.3

[ashish10alex]

Hi @ivan-toriya ,
Thanks for creating the issue.

1. Are you not able to format any sqlx file or just a particular one ? . If it is a particular one, could you please share a minimum reproducible example sqlx file.
2. Could you try running formatting sqlx files from this repro repository to see if you are able to reproduce the issue

[ivan-toriya]

1. Yeah, it turns out it's this particular one.

I have this sqlfluff parse error

[L:360, P:  5]      |                    unparsable:                               !! Expected: "<Ref: 'SelectableGrammar'> to start sequence. Found <WordSegment: ([L:360, P:  5]) 'macros'>"
[L:360, P:  5]      |                        word:                                 'macros'
...
==== parsing violations ====
L: 360 | P:   5 |  PRS | Line 360, Position 5: Found unparsable section: 'macros'

# /tmp/format.sql

-- some code before
  some_cte as (
    ${macros.myFunc({
        foo: "bar"
    })}
)
-- some code after

// includes/macros.js

function myFunc(params) {
// implementation
return sql;
};

module.exports = {
    myFunc
};

[ashish10alex]

Thanks @ivan-toriya for providing the repro. The sqlfluff file does not seem to handle the case when the js file produces the whole cte. I was able to make it work by replacing the [sqlfluff:templater:placeholder] section in your .sqlfluff with the following.

[sqlfluff:templater:placeholder]
param_regex = (?s)\${\s*(?P<param_name>[\w\.]+)(?P<rec>(?:[^{}]+|{(?&rec)})*+)}|(?P<param_name>config|pre_operations|post_operations)\s*{(?&rec)}
ref = ref_table_placeholder
self = self_table_placeholder
when =
config =
pre_operations =
macros.myFunc = 

Note that, I have manually set the macros.myFunc to be empty. However, you do not need to specify every single function call only the ones where you encounter this edge case. Let me know if this works ?

it might be possible to create a more generic solution instead of having to hard code as I have in the example above.Please, do contribute to the repo if you happen to create a generic solution 🙂