Repository: sqlfluff/sqlfluff
Pull request: Exasol: Allow multiple `LOCAL` keywords in `WHERE` clause
<!--Thanks for adding this feature!-->

<!--Please give the Pull Request a meaningful title for the release notes-->

### Brief summary of the change made
<!--Please include `fixes #XXXX` to automatically close any corresponding issue when the pull request is merged. Alternatively if not fully closed you can say `makes progress on #XXXX`.-->
Fixes #2577 

### Are there any other side effects of this change that we should be aware of?

### Pull Request checklist
- [x] Please confirm you have completed any of the necessary steps below.

- Included test cases to demonstrate any code changes, which may be one or more of the following:
  - `.yml` rule test cases in `test/fixtures/rules/std_rule_cases`.
  - `.sql`/`.yml` parser test cases in `test/fixtures/dialects` (note YML files can be auto generated with `tox -e generate-fixture-yml`).
  - Full autofix test cases in `test/fixtures/linter/autofix`.
  - Other.
- Added appropriate documentation for the change.
- Created GitHub issues for any relevant followup/future enhancements if appropriate.


Problem statement:
Exasol: `LOCAL` may not working in `WHERE` clause
<!--If this is a parsing or linting issue, please include a minimal SQL example which reproduces the issue, along with the `sqlfluff parse` output, `sqlfluff lint` output and `sqlfluff fix` output when relevant.-->

## Expected Behaviour
Statement must be parsable

## Observed Behaviour
Statement is not parsable when using `LOCAL` keyword in `WHERE` clause when used in `SELECT` context.

## Steps to Reproduce
```sql
SELECT * FROM T
WHERE (LOCAL.c1, LOCAL.c2)
NOT IN  (SELECT c1,c2 FROM  b)
;
```

## Dialect
Exasol

## Version
<!-- Include the output of `sqlfluff --version` along with your Python version.
If you are using dbt then please additionally include the dbt version. -->
0.9.2

## Configuration
<!--
Include your SQLFluff configuration here
-->

Hints from issue discussion:
This is not strictly true. It parses the first one, but not the second.

```
[L:  2, P:  7]      |                        start_bracket:                        '('
[L:  2, P:  8]      |                        [META] indent:
[L:  2, P:  8]      |                        expression:
[L:  2, P:  8]      |                            local_alias_segment:
[L:  2, P:  8]      |                                keyword:                      'LOCAL'
[L:  2, P: 13]      |                                dot:                          '.'
[L:  2, P: 14]      |                                identifier:                   'c1'
[L:  2, P: 16]      |                        unparsable:                           !! Expected: 'Nothing...'
[L:  2, P: 16]      |                            comma:                            ','
[L:  2, P: 17]      |                            whitespace:                       ' '
[L:  2, P: 18]      |                            raw:                              'LOCAL'
[L:  2, P: 23]      |                            raw:                              '.'
[L:  2, P: 24]      |                            raw:                              'c2'
[L:  2, P: 26]      |                        [META] dedent:
[L:  2, P: 26]      |                        end_bracket:                          ')'
...
==== parsing violations ====
L:   2 | P:  16 |  PRS | Line 2, Position 16: Found unparsable section: ', LOCAL.c2'
```

Doing a search for `local_alias_segment` quickly shows the problem and replacing this:

```py
    type = "local_alias_segment"
    match_grammar = Sequence("LOCAL", Ref("DotSegment"), Ref("SingleIdentifierGrammar"))
```

with this:

```py
    type = "local_alias_segment"
    match_grammar = Delimited(Sequence("LOCAL", Ref("DotSegment"), Ref("SingleIdentifierGrammar")))
```

Fixes the issue. Though I guess that's not strictly correct as I guess we should force brackets if more than one answer. So needs a slightly different fix...

Btw this is why the full parsing output, rather than just saying it doesn't parse, helps.

I know, the first one gets parsed.. The fix is already in the pipeline. I only wait for #2579 to prevent merge conflicts. 
I quickly added this issue as my colleague informed my about that. To keep it on track with an example.
I've got a fix too. A better one that the one above. Will submit the PR and tag you to look.

Commit message: Add explanation

Co-authored-by: Barry Pollard <barry_pollard@hotmail.com>

Patch to review:
diff --git a/src/sqlfluff/dialects/dialect_ansi.py b/src/sqlfluff/dialects/dialect_ansi.py
index a994276dbb7..cefa673855b 100644
--- a/src/sqlfluff/dialects/dialect_ansi.py
+++ b/src/sqlfluff/dialects/dialect_ansi.py
@@ -1715,6 +1715,7 @@ class CaseExpressionSegment(BaseSegment):
                             "FunctionSegment"
                         ),  # WHERE (a, substr(b,1,3)) IN (select c,d FROM...)
                         Ref("LiteralGrammar"),  # WHERE (a, 2) IN (SELECT b, c FROM ...)
+                        Ref("LocalAliasSegment"),   # WHERE (LOCAL.a, LOCAL.b) IN (…)
                     ),
                     ephemeral_name="BracketedExpression",
                 ),
diff --git a/test/fixtures/dialects/exasol/SelectStatement.sql b/test/fixtures/dialects/exasol/SelectStatement.sql
index 436f26df1b2..5031db53340 100644
--- a/test/fixtures/dialects/exasol/SelectStatement.sql
+++ b/test/fixtures/dialects/exasol/SelectStatement.sql
@@ -113,3 +113,7 @@ SELECT * FROM T PREFERRING HIGH LOCAL.ranking PRIOR TO LOW LOCAL.budget PARTITIO
 SELECT * FROM T PREFERRING HIGH LOCAL.ranking PLUS LOW LOCAL.budget PARTITION BY local.c1;
 ----
 SELECT * FROM T PREFERRING HIGH LOCAL.ranking PRIOR TO LOW LOCAL.budget INVERSE col20 PARTITION BY local.c1;
+----
+SELECT * FROM T WHERE (LOCAL.c1, LOCAL.c2) NOT IN  (SELECT c1,c2 FROM  b);
+----
+SELECT 'ABC' as c1 FROM dual WHERE local.c1 = 'ABC';
diff --git a/test/fixtures/dialects/exasol/SelectStatement.yml b/test/fixtures/dialects/exasol/SelectStatement.yml
index 7abdcb62d9e..ae1f61cc0c5 100644
--- a/test/fixtures/dialects/exasol/SelectStatement.yml
+++ b/test/fixtures/dialects/exasol/SelectStatement.yml
@@ -3,7 +3,7 @@
 # computed by SQLFluff when running the tests. Please run
 # `python test/generate_parse_fixture_yml.py`  to generate them after adding or
 # altering SQL files.
-_hash: 459a397d3141917552fbc213bd4d96f832f073ce5cc8720665bec86974bb7752
+_hash: cd2b6a079124d7f43ffd94fa07de2128b16401a1f55073e1653fdc0e5d2e57b8
 file:
 - statement:
     select_statement:
@@ -1442,3 +1442,83 @@ file:
               dot: .
               identifier: c1
 - statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: T
+      where_clause:
+        keyword: WHERE
+        expression:
+        - bracketed:
+          - start_bracket: (
+          - local_alias_segment:
+              keyword: LOCAL
+              dot: .
+              identifier: c1
+          - comma: ','
+          - local_alias_segment:
+              keyword: LOCAL
+              dot: .
+              identifier: c2
+          - end_bracket: )
+        - keyword: NOT
+        - keyword: IN
+        - bracketed:
+            start_bracket: (
+            select_statement:
+              select_clause:
+              - keyword: SELECT
+              - select_clause_element:
+                  column_reference:
+                    identifier: c1
+              - comma: ','
+              - select_clause_element:
+                  column_reference:
+                    identifier: c2
+              from_clause:
+                keyword: FROM
+                from_expression:
+                  from_expression_element:
+                    table_expression:
+                      table_reference:
+                        identifier: b
+            end_bracket: )
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          literal: "'ABC'"
+          alias_expression:
+            keyword: as
+            identifier: c1
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: dual
+      where_clause:
+        keyword: WHERE
+        expression:
+          local_alias_segment:
+            keyword: local
+            dot: .
+            identifier: c1
+          comparison_operator:
+            raw_comparison_operator: '='
+          literal: "'ABC'"
+- statement_terminator: ;


Code review comments:
diff_hunk: @@ -1715,6 +1715,7 @@ class CaseExpressionSegment(BaseSegment):
                             "FunctionSegment"
                         ),  # WHERE (a, substr(b,1,3)) IN (select c,d FROM...)
                         Ref("LiteralGrammar"),  # WHERE (a, 2) IN (SELECT b, c FROM ...)
+                        Ref("LocalAliasSegment"),   # WHERE (LOCAL.a, LOCAL.b) IN (…)
path: src/sqlfluff/dialects/dialect_ansi.py
text: @user1:
Probably should keep eclipses consistent (damn auto correct!)

```suggestion
                        Ref("LocalAliasSegment"),   # WHERE (LOCAL.a, LOCAL.b) IN (...)
```