Re: why can a named subselect not be used in a where condition?

Markus Bertheau <twanger(at)bluetwanger(dot)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