Обсуждение: referring to calculated column in sub select

Hi,

why column "acoltest" is not found by the subselect in this select:


SELECT
      acol + 100 as acoltest,
     (select max(t) from mytab where anothercol=acoltest) as col2
FROM mytab2
group by somet
???
Only columns belonging to a table can be used in a subselect??? What about "calculated" columns?
Thank you

On Mon, May 18, 2009 at 06:49:30AM -0700, Scara Maccai wrote:
> why column "acoltest" is not found by the subselect in this select:

The "acoltest" identifier is only visible from outside the query, not
within its defining query or any of its sub-queries.  If you're trying
to solve a problem like the example, it would probably be easiest to
swap the inner and outer queries around, i.e. something like:

  SELECT acoltest, MAX(b.t)
  FROM (
    SELECT acol + 100 as acoltest
    FROM mytab2
    GROUP BY 1) a LEFT JOIN mytab b ON a.acoltest = b.anothercol
  GROUP BY acoltest;

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

Using Mysql the query works, so I expected it to be somehow "standard" (I know, Mysql sometimes allows much more than
thestandard...) 

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

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

Thank you

--- Lun 18/5/09, Sam Mason <sam@samason.me.uk> ha scritto:

> Da: Sam Mason <sam@samason.me.uk>
> Oggetto: Re: [GENERAL] referring to calculated column in sub select
> A: pgsql-general@postgresql.org
> Data: Lunedì 18 maggio 2009, 19:10
> On Mon, May 18, 2009 at 06:49:30AM
> -0700, Scara Maccai wrote:
> > why column "acoltest" is not found by the subselect in
> this select:
>
> The "acoltest" identifier is only visible from outside the
> query, not
> within its defining query or any of its sub-queries. 
> If you're trying
> to solve a problem like the example, it would probably be
> easiest to
> swap the inner and outer queries around, i.e. something
> like:
>
>   SELECT acoltest, MAX(b.t)
>   FROM (
>     SELECT acol + 100 as acoltest
>     FROM mytab2
>     GROUP BY 1) a LEFT JOIN mytab b ON a.acoltest
> = b.anothercol
>   GROUP BY acoltest;
>
> --
>   Sam  http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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/

> Hi,
>
> why column "acoltest" is not found by the subselect in this select:
>
>
> SELECT
>       acol + 100 as acoltest,
>      (select max(t) from mytab where anothercol=acoltest) as col2
> FROM mytab2
> group by somet
> ???
> Only columns belonging to a table can be used in a subselect??? What about "calculated" columns?
> Thank you

Looks like you are trying to do a correlated subquery. You can do them
in both Postgres and MySQL. It needs to be done in the where clause on
postgres but can be used in the select clause on MySQL and Oracle. The
problem with correlated subqueries is that they are executed once for
every row in the outer query, so they are usually quite a bit slower
than doing a join like Sam suggested.

Scott