Parentheses in nested JOINs in FROM clause causes unparseable section errors with tsql dialect #6137
Description
Search before asking
- I searched the issues and found no similar issues.
What Happened
When using parentheses around tables in the FROM clause to use explicit join ordering we receive a parser error.
Expected Behaviour
sqlfluff will be able to parse attempts to force join orders in FROM clauses for TSQL
Observed Behaviour
When parsing tsql dialect queries which has tables in FROM clause wrapped in parentheses I receive parsing errors with Expected: 'Nothing else in FileSegment.' in parsing tree output and Found unparsable section: ... WARNING: Parsing errors found and dialect is set to 'tsql'. Have you configured your dialect correctly?
How to reproduce
minimal repro below:
SELECT
tst1.Name, tst2.OtherName
FROM dbo.Test1 AS tst1
LEFT OUTER JOIN (dbo.Test2 AS tst2
INNER JOIN dbo.FilterTable AS fltr1
ON tst2.Id = fltr1.Id)
ON tst1.id = tst2.id
output below and output with -vvvv file attached
sqlfluff_issue.txt
`$ sql parse test2.sql --dialect tsql`
$ sqlfluff parse test2.sql
[L: 1, P: 1] |file:
[L: 1, P: 1] | batch:
[L: 1, P: 1] | statement:
[L: 1, P: 1] | select_statement:
[L: 1, P: 1] | select_clause:
[L: 1, P: 1] | keyword: 'SELECT'
[L: 1, P: 7] | [META] indent:
[L: 1, P: 7] | newline: '\n'
[L: 2, P: 1] | whitespace: ' '
[L: 2, P: 5] | select_clause_element:
[L: 2, P: 5] | column_reference:
[L: 2, P: 5] | naked_identifier: 'tst1'
[L: 2, P: 9] | dot: '.'
[L: 2, P: 10] | naked_identifier: 'Name'
[L: 2, P: 14] | comma: ','
[L: 2, P: 15] | whitespace: ' '
[L: 2, P: 16] | select_clause_element:
[L: 2, P: 16] | column_reference:
[L: 2, P: 16] | naked_identifier: 'tst2'
[L: 2, P: 20] | dot: '.'
[L: 2, P: 21] | naked_identifier: 'OtherName'
[L: 2, P: 30] | [META] dedent:
[L: 2, P: 30] | newline: '\n'
[L: 3, P: 1] | from_clause:
[L: 3, P: 1] | keyword: 'FROM'
[L: 3, P: 5] | whitespace: ' '
[L: 3, P: 6] | from_expression:
[L: 3, P: 6] | [META] indent:
[L: 3, P: 6] | from_expression_element:
[L: 3, P: 6] | table_expression:
[L: 3, P: 6] | table_reference:
[L: 3, P: 6] | naked_identifier: 'dbo'
[L: 3, P: 9] | dot: '.'
[L: 3, P: 10] | naked_identifier: 'Test1'
[L: 3, P: 15] | whitespace: ' '
[L: 3, P: 16] | alias_expression:
[L: 3, P: 16] | [META] indent:
[L: 3, P: 16] | keyword: 'AS'
[L: 3, P: 18] | whitespace: ' '
[L: 3, P: 19] | naked_identifier: 'tst1'
[L: 3, P: 23] | [META] dedent:
[L: 3, P: 23] | [META] dedent:
[L: 3, P: 23] | newline: '\n'
[L: 4, P: 1] | whitespace: ' '
[L: 4, P: 5] | unparsable: !! Expected: 'Nothing else in FileSegment.'
[L: 4, P: 5] | word: 'LEFT'
[L: 4, P: 9] | whitespace: ' '
[L: 4, P: 10] | word: 'OUTER'
[L: 4, P: 15] | whitespace: ' '
[L: 4, P: 16] | word: 'JOIN'
[L: 4, P: 20] | whitespace: ' '
[L: 4, P: 21] | start_bracket: '('
[L: 4, P: 22] | word: 'dbo'
[L: 4, P: 25] | dot: '.'
[L: 4, P: 26] | word: 'Test2'
[L: 4, P: 31] | whitespace: ' '
[L: 4, P: 38] | word: 'AS'
[L: 4, P: 40] | whitespace: ' '
[L: 4, P: 41] | word: 'tst2'
[L: 4, P: 45] | newline: '\n'
[L: 5, P: 1] | whitespace: ' '
[L: 5, P: 27] | word: 'INNER'
[L: 5, P: 32] | whitespace: ' '
[L: 5, P: 33] | word: 'JOIN'
[L: 5, P: 37] | whitespace: ' '
[L: 5, P: 38] | word: 'dbo'
[L: 5, P: 41] | dot: '.'
[L: 5, P: 42] | word: 'FilterTable'
[L: 5, P: 53] | whitespace: ' '
[L: 5, P: 54] | word: 'AS'
[L: 5, P: 56] | whitespace: ' '
[L: 5, P: 57] | word: 'fltr1'
[L: 5, P: 62] | newline: '\n'
[L: 6, P: 1] | whitespace: ' '
[L: 6, P: 31] | word: 'ON'
[L: 6, P: 33] | whitespace: ' '
[L: 6, P: 34] | word: 'tst2'
[L: 6, P: 38] | dot: '.'
[L: 6, P: 39] | word: 'Id'
[L: 6, P: 41] | whitespace: ' '
[L: 6, P: 42] | equals: '='
[L: 6, P: 43] | whitespace: ' '
[L: 6, P: 44] | word: 'fltr1'
[L: 6, P: 49] | dot: '.'
[L: 6, P: 50] | word: 'Id'
[L: 6, P: 52] | end_bracket: ')'
[L: 6, P: 53] | newline: '\n'
[L: 7, P: 1] | whitespace: ' '
[L: 7, P: 9] | word: 'ON'
[L: 7, P: 11] | whitespace: ' '
[L: 7, P: 12] | word: 'tst1'
[L: 7, P: 16] | dot: '.'
[L: 7, P: 17] | word: 'id'
[L: 7, P: 19] | whitespace: ' '
[L: 7, P: 20] | equals: '='
[L: 7, P: 21] | whitespace: ' '
[L: 7, P: 22] | word: 'tst2'
[L: 7, P: 26] | dot: '.'
[L: 7, P: 27] | word: 'id'
[L: 7, P: 29] | newline: '\n'
[L: 8, P: 1] | [META] end_of_file:
==== parsing violations ====
L: 4 | P: 5 | PRS | Line 4, Position 5: Found unparsable section: 'LEFT OUTER JOIN
| (dbo.Test2 AS tst2...'
WARNING: Parsing errors found and dialect is set to 'tsql'. Have you configured your dialect correctly?
Dialect
tsql
Version
sqlfluff, version 3.1.0
Python 3.10.12
Configuration
~/.config/sqlfluff/.sqlfluff
# ~/.config/sqlfluff/.sqlfluff
[sqlfluff]
templater = raw
# Has a default limit of 2_000 bytes, either explicitly increase or disable it by setting value to `0`
large_file_skip_byte_limit = 0
# Supported dialects https://docs.sqlfluff.com/en/stable/perma/dialects.html
# Or run 'sqlfluff dialects'
dialect = tsql
# The standard max_line_length is 80 in line with the convention of
# other tools and several style guides. Many projects however prefer
# something a little longer.
# Set to zero or negative to disable checks.
max_line_length = 180
# CPU processes to use while linting.
# The default is "single threaded" to allow easy debugging, but this
# is often undesirable at scale.
# If positive, just implies number of processes.
# If negative or zero, implies number_of_cpus - specified_number.
# e.g. -1 means use all processors but one. 0 means all cpus.
processes = -1
[sqlfluff:rules:aliasing.length]
min_alias_length = 3
# The default configuration for capitalisation rules is "consistent"
# which will auto-detect the setting from the rest of the file. This
# is less desirable in a new project and you may find this (slightly
# more strict) setting more useful.
# Typically we find users rely on syntax highlighting rather than
# capitalisation to distinguish between keywords and identifiers.
# Clearly, if your organisation has already settled on uppercase
# formatting for any of these syntax elements then set them to "upper".
# See https://stackoverflow.com/questions/608196/why-should-i-capitalize-my-sql-keywords-is-there-a-good-reason
[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = capitalise
unquoted_identifiers_policy = column_aliases
[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = lower
[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = lower
# The default configuration for the not equal convention rule is "consistent"
# which will auto-detect the setting from the rest of the file. This
# is less desirable in a new project and you may find this (slightly
# more strict) setting more useful.
[sqlfluff:rules:convention.not_equal]
# Default to preferring the "c_style" (i.e. `! =`)
preferred_not_equal_style = ansi
[sqlfluff:rules:convention.casting_style]
preferred_type_of_casting_style = convert
[sqlfluff:layout:type:comma]
line_position = leading
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