Repository: sqlfluff/sqlfluff
Pull request: Fix L026 false positive on "SELECT INTO" statement
<!--Firstly, thanks for adding this feature! Secondly, please check the key steps against the checklist below to make your contribution easy to merge.-->

<!--Please give the Pull Request a meaningful title (including the dialect this PR is for if it is dialect specific), as this will automatically be added to the release notes, and then the Change Log.-->

### Brief summary of the change made
Fixes #1830
<!--If there is an open issue for this, then please include `fixes #XXXX` or `closes #XXXX` replacing `XXXX` with the issue number and it will automatically close the issue when the pull request is merged. Alternatively if not fully closed you can say `makes progress on #XXXX` to create a link on that issue without closing it.-->


### 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 L026 - Fails on SELECT INTO TABLE statement
In Exasol we could insert data into a table like this:

```
select * into table schm.tab from schm.tab2;
```

## Expected Behaviour
Rule L026 should pass

## Observed Behaviour
Rule was raised
```
L:   2 | P:  21 | L026 | Reference 'schm.tab' refers to table/view not found in
                       | the FROM clause or found in parent subquery
```

## Steps to Reproduce
Lint the statement above

## Dialect
exasol

## Version
Include the output of `sqlfluff --version` along with your Python version
sqlfluff 0.7.1
python 3.8.12
## Configuration
default

Hints from issue discussion:
This is a great first issue for anyone looking to contribute to SQLFluff: https://github.com/sqlfluff/sqlfluff/wiki/Contributing-Dialect-Changes
As noted by @jpy-git, this is a similar issue as #2265. There's an open PR for that issue (#2336). If we don't address it in that PR, it should be a pretty straightforward enhancement to that fix.

Commit message: Fix L026 false positive on Exasol "SELECT INTO" statement

Patch to review:
diff --git a/src/sqlfluff/rules/L026.py b/src/sqlfluff/rules/L026.py
index 6bb12287743..b55a596824a 100644
--- a/src/sqlfluff/rules/L026.py
+++ b/src/sqlfluff/rules/L026.py
@@ -122,12 +122,13 @@ def _analyze_table_references(
                 # Try and resolve each reference to a value in query.aliases (or
                 # in an ancestor query).
                 for r in select_info.reference_buffer:
-                    # This function walks up the query's parent stack if necessary.
-                    violation = self._resolve_reference(
-                        r, self._get_table_refs(r, dialect), dml_target_table, query
-                    )
-                    if violation:
-                        violations.append(violation)
+                    if not self._should_ignore_reference(r, selectable):
+                        # This function walks up the query's parent stack if necessary.
+                        violation = self._resolve_reference(
+                            r, self._get_table_refs(r, dialect), dml_target_table, query
+                        )
+                        if violation:
+                            violations.append(violation)
 
         # Visit children.
         for child in query.children:
@@ -135,6 +136,19 @@ def _analyze_table_references(
                 cast(L026Query, child), dml_target_table, dialect, violations
             )
 
+    @staticmethod
+    def _should_ignore_reference(reference, selectable):
+        if selectable.dialect.name == "exasol":
+            ref_path = selectable.selectable.path_to(reference)
+            # Ignore references occurring in an "INTO" clause:
+            # - They are table references, not column references.
+            # - They are the target table, similar to an INSERT or UPDATE
+            #   statement, thus not expected to match a table in the FROM
+            #   clause.
+            if any(seg.is_type("into_table_clause") for seg in ref_path):
+                return True
+        return False
+
     @staticmethod
     def _get_table_refs(ref, dialect):
         """Given ObjectReferenceSegment, determine possible table references."""
diff --git a/test/fixtures/rules/std_rule_cases/L026.yml b/test/fixtures/rules/std_rule_cases/L026.yml
index 95bb019e240..e05221d99b7 100644
--- a/test/fixtures/rules/std_rule_cases/L026.yml
+++ b/test/fixtures/rules/std_rule_cases/L026.yml
@@ -171,3 +171,12 @@ test_pass_two_part_reference_11:
   pass_str: |
     select * from agent1
     where public.agent1.agent_code <> '3'
+
+test_pass_exasol_select_into:
+  pass_str: |
+    select *
+    into table schm.tab
+    from schm.tab2
+  configs:
+    core:
+      dialect: exasol


Code review comments:
diff_hunk: @@ -122,19 +122,33 @@ def _analyze_table_references(
                 # Try and resolve each reference to a value in query.aliases (or
                 # in an ancestor query).
                 for r in select_info.reference_buffer:
-                    # This function walks up the query's parent stack if necessary.
-                    violation = self._resolve_reference(
-                        r, self._get_table_refs(r, dialect), dml_target_table, query
-                    )
-                    if violation:
-                        violations.append(violation)
+                    if not self._should_ignore_reference(r, selectable):
+                        # This function walks up the query's parent stack if necessary.
+                        violation = self._resolve_reference(
+                            r, self._get_table_refs(r, dialect), dml_target_table, query
+                        )
+                        if violation:
+                            violations.append(violation)
 
         # Visit children.
         for child in query.children:
             self._analyze_table_references(
                 cast(L026Query, child), dml_target_table, dialect, violations
             )
 
+    @staticmethod
+    def _should_ignore_reference(reference, selectable):
+        if selectable.dialect.name == "exasol":
path: src/sqlfluff/rules/L026.py
text: @user1:
Why just exasol? Surely this is common to many (all?) dialects?

T-SQL has this for example.

The other dialects SHOULD have type set up IMHO this but don't. Whether we want to change that as part of this PR, or just leave that for later is up to you, but either way I think we should remove this `if` statement.

@user2:
Yeah I think removing it makes sense

@author:
Good catch! `SELECT INTO` sounded like it _might_ be generic, but I didn't think to check.

I'll remove the `if` statement, but would rather not make any dialect changes in this PR (scope creep). Want to create an issue for that, or should we wait until a user reports it?

@user1:
Happy to wait for reports.

L026 is disabled by default for some dialects anyway ("bigquery", "hive", "redshift").