Re: referring to calculated column in sub select

On Tue, May 19, 2009 at 12:04:28AM -0700, Scara Maccai wrote:
> Using Mysql the query works, so I expected it to be somehow "standard"
> (I know, Mysql sometimes allows much more than the standard...)

No, as far as I know the standard explicitly says that the MySQL
behaviour is incorrect.

> Does this has something to do with Support LATERAL subqueries" in the
> to do list?

No, a lateral sub-query would be something like the following:

SELECT f.a, b.b
FROM foo a, getrows(a.a) b(b);

I.e. in order to execute the "getrows" function you need to have values
out of "foo" first. You can do this in some specific instances at the
moment in PG, but not in general.

> It's a very shame it's not supported...

another rewrite of your query would be to have a sub-select:

SELECT acoltest,
(SELECT max(t) FROM mytab WHERE anothercol=acoltest) as col2
FROM (SELECT somet, acol+100 AS acoltest FROM mytab2)
GROUP BY somet;

Hope that helps!

--
Sam http://samason.me.uk/