Skip to content

ignore nulls / respect nulls not recognised in tsql dialect #5212

@LaCools

Description

@LaCools

Search before asking

  • I searched the issues and found no similar issues.

What Happened

When using the tsql function FIRST_VALUE() with parameter ignore nulls / respect nulls, sqlfluff failes to parse.

Expected Behaviour

==== finding fixable violations ====
=== [dbt templater] Sorting Nodes...
=== [dbt templater] Compiling dbt project...
=== [dbt templater] Project Compiled.
==== no fixable linting violations found ====
All Finished 📜 🎉!

Observed Behaviour

Upon adding the parameter ignore nulls, following output is received:

==== finding fixable violations ====
=== [dbt templater] Sorting Nodes...
=== [dbt templater] Compiling dbt project...
=== [dbt templater] Project Compiled.
WARNING Unable to lint mymodel due to an internal error. Please report this as an issue with your query's contents and stacktrace below!
To hide this warning, add the failing file to .sqlfluffignore
RuntimeError: Grammar refers to the 'Nulls' keyword which was not found in the tsql dialect.

The syntax in the query is not (yet?) supported. Try to narrow down your query to a minimal, reproducible case and raise an issue on GitHub.

Or, even better, see this guide on how to help contribute keyword and/or dialect updates:
https://github.com/sqlfluff/sqlfluff/wiki/Contributing-Dialect-Changes#keywords

==== no fixable linting violations found ====
All Finished 📜 🎉!

How to reproduce

Add the option ignore nulls / respect nulls to a function and run sqlfluff lint or sqlfluff fix

Dialect

tsql

Version

sqlfluff version: 2.3.2
dbt 1.4.7

Configuration

[sqlfluff]
# Rules guide: https://docs.sqlfluff.com/en/stable/rules.html
# 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 = tsql
# One of [raw|jinja|python|placeholder]
templater = dbt
# Comma separated list of rules to check, default to all
rules = all
# Very large files can make the parser effectively hang.
# The more efficient check is the _byte_ limit check which
# is enabled by default. The previous _character_ limit check
# is still present for backward compatibility. This will be
# removed in a future version.
# Set either to 0 to disable.
large_file_skip_byte_limit = 20000
# CPU processes to use while linting.
# If positive, just implies number of processes.
# If negative or zero, implies number_of_cpus - specifed_number.
# e.g. -1 means use all processors but one. 0  means all cpus.
processes = -1

max_line_length = 150

exclude_rules = structure.column_order

[sqlfluff:templater]
unwrap_wrapped_queries = True

[sqlfluff:templater:dbt]
project_dir = ./

# Some rules can be configured directly from the config common to other rules
[sqlfluff:rules]
allow_scalar = True
single_table_references = unqualified
unquoted_identifiers_policy = all

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

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

[sqlfluff:rules:capitalisation.keywords]
# Keywords
capitalisation_policy = upper
# Comma separated list of words to ignore for this rule
ignore_words = None
ignore_words_regex = None

[sqlfluff:rules:capitalisation.literals]
# Null & Boolean Literals
capitalisation_policy = upper
# Comma separated list of words to ignore for this rule
ignore_words = None
ignore_words_regex = None

[sqlfluff:rules:references.quoting]
# Policy on quoted and unquoted identifiers
prefer_quoted_identifiers = True

[sqlfluff:rules:references.special_chars]
# Special characters in identifiers
unquoted_identifiers_policy = all
quoted_identifiers_policy = none
allow_space_in_identifier = True
additional_allowed_characters = all

[sqlfluff:rules:convention.quoted_literals]
# Consistent usage of preferred quotes for quoted literals
preferred_quoted_literal_style = single_quotes
# Disabled for dialects that do not support single and double quotes for quoted literals (e.g. Postgres)
force_enable = True

[sqlfluff:rules:aliasing.length]
min_alias_length = 5
max_alias_length = 45

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

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingt-sqlIssues related to the T-SQL/TSQL/Transact SQL dialect

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions