Additional CTE when using an alias

[max-sixty]

This is extremely minor, to the extent I hesitated to put an issue in, but generally I think the bar should be quite low, so:

Here we get a CTE which I think we could avoid:

from employees
select fname = first_name
filter fname == "Fred"

WITH table_1 AS (
  SELECT
    first_name AS fname
  FROM
    employees
)
SELECT
  fname
FROM
  table_1
WHERE
  fname = 'Fred'

Possibly this could be:

SELECT
  first_name as fname
FROM
  employees
WHERE
  first_name = 'Fred'

...since in SQL we can still use first_name within that select statement.

[aljazerzen]

I'd argue that this would be incorrect, because fname should be computed before filtering. In your second SQL statement, WHERE is evaluated before SELECT's projections, so this does not hold.

[max-sixty]

To confirm, are you saying that while the SQL above is equivalent in this specific case, it won't be in the general case? Or that the SQL above isn't equivalent? Because at least in SQL, I was fairly confident that aliases from the SELECT statement are equivalent in the WHERE statement.

For example, it won't be the case here obv:

from employees
select fname = first_name
take 5
filter fname == "Fred"

---

I probably think we should close since implementing this to discriminate between those cases would not be trivial, and would just be aesthetics, feel free to hit the button if you agree

[aljazerzen]

while the SQL above is equivalent in this specific case, it won't be in the general case

Exactly.

Consider this: select projection has to be computed before WHERE

from employees
select rn = row_number
filter rn <= 3

[max-sixty]

Great, agree. We could try and discriminate between where it matters vs. doesn't, but seems extremely low priority