Обсуждение: why can a named subselect not be used in a where condition?

Hello,

cenes_test=> select version();                          version
-------------------------------------------------------------PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC
2.96
(1 row)

cenes_test=> select personen.*, (select max(last_visit) from sessions
where sessions.personen_id = personen.personen_id) as max from personen
where max between 1009148400 and 1011913200 order by max desc;
ERROR:  Attribute 'max' not found

I again think that the table structure is not neccesary to understand my
question.

Well, why can't I use max in the where clause?

<comment>
I do know, thanks to Josh Berkus, that this query should be reformed
using sth like

select personen.f1, personen.f2, max(last_visit) from personen join
sessions on sessions.personen_id = personen.personen_id group by
personen.f1, personen.f2

(I know that this query doesn't include persons that don't have a row
with their personen_id in sessions)
</comment>

Related:

cenes=> select version();                           version
---------------------------------------------------------------PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc
2.95.2
(1 row)

cenes=> select personen.*, (select max(last_visit) from sessions where
sessions.personen_id = personen.personen_id) from personen where (select
max(zeitstempel) from sessions where sessions.personen_id =
personen.personen_id) between 1009148400 and 1011913200;
ERROR:  ExecEvalExpr: unknown expression type 501

That is a query we have tried on 7.0.2 because of curiosity. But the
error message looks weird. What does it mean?

Thank you for you information.

Markus Bertheau

Markus,

> cenes_test=> select personen.*, (select max(last_visit) from sessions
> where sessions.personen_id = personen.personen_id) as max from
>  personen
> where max between 1009148400 and 1011913200 order by max desc;
> ERROR:  Attribute 'max' not found
> 
> I again think that the table structure is not neccesary to understand
>  my
> question.
> 
> Well, why can't I use max in the where clause?

There's two problems with the above query that have nothing to do withnamed subselects, which are well-supported by
PostgreSQL(in fact, asof 7.1.3, Postgres has better support for subselects of all sorts thanany other major RDBMS
platform).

1. "Max" is a reserved word in SQL.  You are confusing the parser. Pick another name.

2. While you are already aware that the example query is not the mostefficient construction, I also think you are doing
youraliasing inthe wrong place:
 
select personen.*, (select max(last_visit) as max_visit from sessionswhere sessions.personen_id = personen.personen_id)
frompersonen
 
... but I could be wrong, as I almost never find any reason to use asub-select in the SELECT line.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

Markus Bertheau <twanger@bluetwanger.de> writes:
> cenes_test=3D> select personen.*, (select max(last_visit) from sessions
> where sessions.personen_id =3D personen.personen_id) as max from personen
> where max between 1009148400 and 1011913200 order by max desc;
> ERROR:  Attribute 'max' not found

> Well, why can't I use max in the where clause?

Because max is computed by the select's output list, which logically is
a stage of processing after WHERE.  Perhaps you need to buy an SQL
textbook.

> cenes=> select personen.*, (select max(last_visit) from sessions where
> sessions.personen_id = personen.personen_id) from personen where (select
> max(zeitstempel) from sessions where sessions.personen_id =
> personen.personen_id) between 1009148400 and 1011913200;
> ERROR:  ExecEvalExpr: unknown expression type 501

> That is a query we have tried on 7.0.2 because of curiosity. But the
> error message looks weird. What does it mean?

This is an internal error.  I couldn't reproduce the error using 7.0.2
and straightforward table definitions.  I suspect you've not told us
something important, like that one of these things is a view with a
nontrivial definition.  In any case, 7.0.2 is a long ways back and I'm
not very concerned about figuring out exactly why it fails.  If you can
reproduce the problem on a current release (7.1.3 or 7.2beta) I'd be
interested...
        regards, tom lane