Thread: 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