pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwhy can a named subselect not be used in a where condition?
3+ messages / 3 participants
[nested] [flat]
* why can a named subselect not be used in a where condition?
@ 2002-01-24 17:40 Markus Bertheau <twanger@bluetwanger.de>
2002-01-24 17:58 ` Re: why can a named subselect not be used in a where Josh Berkus <josh@agliodbs.com>
2002-01-24 18:24 ` Re: why can a named subselect not be used in a where condition? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Markus Bertheau @ 2002-01-24 17:40 UTC (permalink / raw)
To: pgsql-sql
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
Attachments:
signature.asc (application/pgp-signature, 231B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: why can a named subselect not be used in a where
2002-01-24 17:40 why can a named subselect not be used in a where condition? Markus Bertheau <twanger@bluetwanger.de>
@ 2002-01-24 17:58 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2002-01-24 17:58 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; pgsql-sql
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 with
named subselects, which are well-supported by PostgreSQL (in fact, as
of 7.1.3, Postgres has better support for subselects of all sorts than
any 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 most
efficient construction, I also think you are doing your aliasing in
the wrong place:
select personen.*, (select max(last_visit) as max_visit from sessions
where sessions.personen_id = personen.personen_id) from
personen
... but I could be wrong, as I almost never find any reason to use a
sub-select in the SELECT line.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: why can a named subselect not be used in a where condition?
2002-01-24 17:40 why can a named subselect not be used in a where condition? Markus Bertheau <twanger@bluetwanger.de>
@ 2002-01-24 18:24 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2002-01-24 18:24 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-01-24 18:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-24 17:40 why can a named subselect not be used in a where condition? Markus Bertheau <twanger@bluetwanger.de>
2002-01-24 17:58 ` Re: why can a named subselect not be used in a where Josh Berkus <josh@agliodbs.com>
2002-01-24 18:24 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox