-
-
Notifications
You must be signed in to change notification settings - Fork 674
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
RF03 fixes can cause incorrect alias assumptions in certain cases #5983
Comments
I think in this particular instance this may be occurring because the table in the subquery has an alias, but the parent's from clause is itself a subquery with no alias. So according to sqlfluff/src/sqlfluff/rules/references/RF03.py Lines 122 to 125 in d4ec93f
sqlfluff/src/sqlfluff/rules/references/RF03.py Lines 107 to 108 in d4ec93f
Therefore it counts the subquery's aliases (1) and the parent query's non-empty aliases (0) and assumes the |
I've got some code that MOSTLY works. Here are the relevant changes from def _iter_available_targets(
self,
query: Query,
include_empty: bool = False,
) -> Iterator[str]:
"""Iterate along a list of valid alias targets."""
for selectable in query.selectables:
select_info = selectable.select_info
for alias in select_info.table_aliases:
if alias.ref_str or include_empty:
yield alias.ref_str
def _visit_queries(self, query: Query, visited: set) -> Iterator[LintResult]:
select_info: Optional[SelectStatementColumnsAndTables] = None
if query.selectables:
select_info = query.selectables[0].select_info
# How many table names are visible from here? If more than one then do
# nothing.
if select_info and len(select_info.table_aliases) == 1:
fixable = True
# :TRICKY: Subqueries in the column list of a SELECT can see tables
# in the FROM list of the containing query. Thus, count tables at
# the *parent* query level.
possible_ref_tables = list(self._iter_available_targets(query))
if query.parent:
possible_ref_tables = list(
# Due to https://github.com/sqlfluff/sqlfluff/issues/5983
# include empty aliases for the purposes of our count
# to see if this is auto-fixable, but make sure we
# don't count the subquery itself
self._iter_available_targets(
query.parent,
include_empty=True,
)
) This seems to fix the issue but, this causes another test to begin failing, namely |
@TheCleric did you see my #5980 ? There as well a qualified reference is used by accident as the the subquery has an alias. |
I did not. Could possibly be related. |
Search before asking
What Happened
When fixing some of our sql code we began seeing join like where clauses in subqueries suddenly "joining" a table to itself via fixed aliases. Here is a minimal reproducible example:
I know that this query could be cleaned up and made more readable, but it is a very stripped down version of our real query which is much less easily changed.
Expected Behaviour
The code above should be left relatively (or completely) unchanged.
Observed Behaviour
SQLFluff applies the following fix that renders the query valid SQL, but without the intended results. Note that the previously unqualified column
CustomerId
which was referencing the outer table, has been given a qualification of the inner tableC
which renders it invalid.How to reproduce
Minimal reproducible example provided in the 'What Happened` section.
Any SQLfluff config with RF03 enabled should trigger it.
Dialect
ANSI (though discovered in Databricks).
Version
sqlfluff, version 3.0.7
Python 3.10.12
Configuration
Default configuration
Are you willing to work on and submit a PR to address the issue?
Code of Conduct
The text was updated successfully, but these errors were encountered: