Sample on sqlfiddle -

Where I run a query like below -
select CONCAT(id, rev) as new_id from DOCS where new_id>1
i am getting below error -

Unknown column 'new_id' in 'where clause'

Why does not sql allow to use the column alias name in the where clause. Any strong reason for that. I expect it should be smart enough to run an outer query again based on the alias after it is done with processing/filtering/aggregation based on the original column names.

I am instead required to do the below "modified query"-

select * from (select CONCAT(id, rev) as new_id from DOCS) as t where new_id>1

Is there a better approach then the modified query.

Note: I agree -

1. select CONCAT(id, rev) as new_id, new_id 1 as latest_id from DOCS where new_id>1 could be little confusing to the engine, but even that seems possible.

2. select CONCAT(id, rev) as new_id from DOCS where new_id>1 and DOCS.rev = 1 - seems confusing.

Are these the reasons or there is more to it.

CodePudding user response：

Why not just do this:

select CONCAT(id, rev) as new_id 
from DOCS 
where CONCAT(id, rev) > 1

This will work the way you expected your original code to work in terms of performance CONCAT(id, rev) will only be calculated once per row.

CodePudding user response：

I expect it should be smart enough to run an outer query again based on the alias after it is done with processing/filtering/aggregation based on the original column names

I suggest you carefully and thoughtfully read that sentence you wrote on your question. You expect the DBMS to create a query to select ALL the possible rows (selectively) excluding WHERE clauses, then assemble a resultset with your aliased, calculated, aggregated, etc... columnns, and then run ANOTHER query on said resultset to apply the WHERE clause so you can use the alias on it.

Both DBMS and Database Admins have come a LONG way trying to run queries as smooth and efficient as possible; what you "expect" the database to do is actually a very slow and innefficient method to run that query.

In other words: you cannot do what you want not because the DBMS is not "smart enough" to filter based on an alias, but because it IS smart enough to first run the JOIN and WHERE clauses so it can reduce the read/recovered rows as much as possible, THEN process those rows to create the resultset exactly as specified on the SELECT clause.