Cannot use column alias in where clause

[bedla.czech]

Hi,
I want to use calculated column alias in where clause. While I use it
in order by clause, everything is ok.

Error:
select *, distanceEarth(lat, lon, 50.245284, 16.163576) as dist
from location
where dist < 50
order by dist

Workaround:
select *, distanceEarth(lat, lon, 50.245284, 16.163576) as dist
from location
where distanceEarth(lat, lon, 50.245284, 16.163576) < 50
order by dist

Note: distanceEarth is custom function alias.

In workaround you can see that I use calculation two times: first for
order by clause and second for where clause.

Am I missing something?

Thanks for response
Ivos

Ps.: I originaly posted this question at h2 bug tracker, sorry for
that.

[mb]

I think this is a feature, not a bug. Does any other RDBMS allow using
aliases in where clause? Oracle 11 doesn't.

It depends on query optimizer whether distanceEarth is calculated
twice or not. If user function is deterministic and query optimizer
it's smart enough, it should be able to figure out that distanceEarth
is called twice with the same arguments and use result of first call
when distanceEarth is called second time.

M.

[Andreas Henningsson]

No this is the way SQL works.

Basicly the select part of the query is done after the where filter is applied.
So you can't use an alias created in the select part in the where part
of the query.
You have to do it like in your Workaround example.

/Andreas

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>
>

--
Andreas Henningsson

"Vanligt sunt förnuft är inte särkilt vanligt." -- Voltaire

[Maaartin G]

On Thursday, April 28, 2011 7:55:32 AM UTC+2, mb wrote:
> I think this is a feature, not a bug.

I wouldn't call something as stupid as forcing repeating expressions a feature. It's the way SQL works, so we can't blame H2; it's a standardized misfeature.

On Thursday, April 28, 2011 8:39:25 AM UTC+2, Andreas Henningsson wrote:
> Basicly the select part of the query is done after the where filter is applied.
> So you can't use an alias created in the select part in the where part
> of the query.

Sacrificing logic for a sentence-like look is a bad idea. Nonetheless, it could work, you'd just need to look at the SELECT part twice (first for finding the aliases). Or invent an alternative syntax for defining aliases elsewhere. It'd be nice, but it's a lot of work.

> You have to do it like in your Workaround example.

A subquery would do as well, but it may or may not cost performance.

[Shammat]

On Apr 27, 10:35 pm, "bedla.czech" <bedla.cz...@gmail.com> wrote:

> Hi,
> I want to use calculated column alias in where clause. While I use it
> in order by clause, everything is ok.
>
> Error:
> select *, distanceEarth(lat, lon, 50.245284, 16.163576) as dist
> from location
> where dist < 50
> order by dist
>
> Workaround:
> select *, distanceEarth(lat, lon, 50.245284, 16.163576) as dist
> from location
> where distanceEarth(lat, lon, 50.245284, 16.163576) < 50
> order by dist
>
> Note: distanceEarth is custom function alias.
>
> In workaround you can see that I use calculation two times: first for
> order by clause and second for where clause.

As others have said: that's how SQL works, but there is a more elegant
workaround:

SELECT *
FROM (
SELECT *, distanceEarth(lat, lon, 50.245284, 16.163576) as dist
FROM location
) t
WHERE dist < 50
ORDER BY dist