Skip to content
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

SQL: WHERE clause doesn't utilise alias of SELECT expression/func successfully #29983

Closed
elasticmachine opened this issue Dec 5, 2017 · 1 comment

Comments

Projects
None yet
3 participants
@elasticmachine
Copy link

commented Dec 5, 2017

Original comment by @gingerwizard:

Query as follows:

SELECT respondent_id, applicant_income_000s/loan_amount_000s AS income_to_earnings_ratio FROM mortgages WHERE income_to_earnings_ratio > 1 ORDER BY income_to_earnings_ratio

The WHERE clauses here uses an alias. The query complains the income_to_earnings_ratio cannot be found i.e.

Found 1 problem(s)
line 1:101: Unknown column [income_to_earnings_ratio]

This is inconsistent with our ability to use aliases in GROUP BY and ORDER BY. Repeating the expression in the WHERE works.

@elasticmachine

This comment has been minimized.

Copy link
Author

commented Dec 10, 2017

Original comment by @costin:

This feature is not availble because at runtime the SELECT (aka Projection) occurs after WHERE, meaning the alias definition isn't not defined yet it is being used.

GROUP BY and ORDER BY are somewhat different hence why we support them even though traditional SQL does not.
You are right that it is inconsistent however I'm not sure whether this can be enabled before alpha or ever.
In which case, do we want to disable the alias reference through-out or not?

@colings86 colings86 added the >bug label Apr 25, 2018

@costin costin added >enhancement v7.0.0 and removed >bug labels Feb 5, 2019

costin added a commit to costin/elasticsearch that referenced this issue Feb 5, 2019

SQL: Allow look-ahead resolution of aliases for WHERE clause
Aliases defined in SELECT (Project or Aggregate) are now resolved in the
following WHERE clause. The Analyzer has been enhanced to identify this
rule and replace the field accordingly.

Close elastic#29983

@costin costin closed this in #38450 Feb 6, 2019

costin added a commit that referenced this issue Feb 6, 2019

SQL: Allow look-ahead resolution of aliases for WHERE clause (#38450)
Aliases defined in SELECT (Project or Aggregate) are now resolved in the
following WHERE clause. The Analyzer has been enhanced to identify this
rule and replace the field accordingly.

Close #29983

costin added a commit that referenced this issue Feb 6, 2019

SQL: Allow look-ahead resolution of aliases for WHERE clause (#38450)
Aliases defined in SELECT (Project or Aggregate) are now resolved in the
following WHERE clause. The Analyzer has been enhanced to identify this
rule and replace the field accordingly.

Close #29983

(cherry picked from commit 1a02445)

@costin costin added the v6.7.0 label Feb 6, 2019

@colings86 colings86 added v7.0.0-beta1 and removed v7.0.0 labels Feb 7, 2019

dimitris-athanasiou added a commit to dimitris-athanasiou/elasticsearch that referenced this issue Feb 12, 2019

SQL: Allow look-ahead resolution of aliases for WHERE clause (elastic…
…#38450)

Aliases defined in SELECT (Project or Aggregate) are now resolved in the
following WHERE clause. The Analyzer has been enhanced to identify this
rule and replace the field accordingly.

Close elastic#29983
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.