Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0OIORl25425 for ; Thu, 24 Jan 2002 13:24:27 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g0OIONf16342; Thu, 24 Jan 2002 13:24:23 -0500 (EST) To: Markus Bertheau cc: postgres sql list Subject: Re: why can a named subselect not be used in a where condition? In-reply-to: <1011894031.29186.24.camel@entwicklung01.cenes.de> References: <1011894031.29186.24.camel@entwicklung01.cenes.de> Comments: In-reply-to Markus Bertheau message dated "24 Jan 2002 18:40:30 +0100" Date: Thu, 24 Jan 2002 13:24:22 -0500 Message-ID: <16339.1011896662@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200201/274 X-Sequence-Number: 6358 Markus Bertheau 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