pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSv: how to build this query ??? Please help !!!
12+ messages / 5 participants
[nested] [flat]
* Sv: how to build this query ??? Please help !!!
@ 2001-01-03 15:40 Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
2001-01-04 07:18 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
0 siblings, 1 reply; 12+ messages in thread
From: Nikolaj Lundsgaard @ 2001-01-03 15:40 UTC (permalink / raw)
To: pgsql-sql
Something like this oughtta work (not tested)
SELECT a_nr,
(SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder,
(SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zdr') AS #zdr,
(SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zcu') AS #zcu,
(SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zcr') AS #zcr,
product, state
FROM orders
/Nikolaj
-----Oprindelig meddelelse-----
Fra: juerg.rietmann@pup.ch <juerg.rietmann@pup.ch>
Til: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Dato: 3. januar 2001 16:16
Emne: [SQL] how to build this query ??? Please help !!!
>Hello there
>
>I have a problem, building a query. Who can help ?
>I use postgreSQL 7.0.3.
>
>Thanks for any help and suggestions ... jr
>
>orders
>a_nr ¦ product ¦ state
>-----------------------------
>11 ¦ tp ¦ aaaa
>12 ¦ fi ¦ bbbb
>13 ¦ tp ¦ cccc
>14 ¦ ok ¦ eeee
>15 ¦ dm ¦ aaaa
>
>cylinders
>z_a_nr ¦ z_status
>------------------------------
>11 ¦ zdr
>11 ¦ zdr
>12 ¦ zdr
>12 ¦ zcu
>13 ¦ zdr
>13 ¦ zcu
>13 ¦ zcu
>13 ¦ zcr
>15 ¦ zcu
>15 ¦ zcu
>15 ¦ zdr
>
>I need a query for the following output :
>
>a_nr ¦ #cylinder #zdr #zcu #zcr product state
>----------------------------------------------------------------------
>11 2 2 0 0 dm aaaa
>12 2 1 1 0 tp aaaa
>13 4 1 2 1 fi bbbb
>14 0 0 0 0 ok eeee
>15 3 1 2 0 dm aaaa
>
>
>
>
>
>============================================
>PFISTER + PARTNER, SYSTEM - ENGINEERING AG
>Juerg Rietmann
>Grundstrasse 22a
>6343 Rotkreuz
>Switzerland
>
>phone: +4141 790 4040
>fax: +4141 790 2545
>mobile: +4179 211 0315
>============================================
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Sv: how to build this query ??? Please help !!!
2001-01-03 15:40 Sv: how to build this query ??? Please help !!! Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
@ 2001-01-04 07:18 ` Jens Hartwig <jhartwig@debis.com>
2001-01-04 07:53 ` Re: Sv: how to build this query ??? Please help !!! Tod McQuillin <devin@spamcop.net>
0 siblings, 1 reply; 12+ messages in thread
From: Jens Hartwig @ 2001-01-04 07:18 UTC (permalink / raw)
To: Nikolaj Lundsgaard <Nikolaj@konfus.dk>; +Cc: pgsql-sql
> [...]
> SELECT a_nr,
> (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder,
> [...]
> FROM orders
> [...]
Is this a documented feature? Where can I find more information about
this? I do not want to join the actual discussion about documentation
of PostgreSQL but I never before have seen such a construct in SQL!
Best regards, Jens Hartwig
=============================================
Jens Hartwig
---------------------------------------------
debis Systemhaus GEI mbH
10875 Berlin
Tel. : +49 (0)30 2554-3282
Fax : +49 (0)30 2554-3187
Mobil : +49 (0)170 167-2648
E-Mail : jhartwig@debis.com
=============================================
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Sv: how to build this query ??? Please help !!!
2001-01-03 15:40 Sv: how to build this query ??? Please help !!! Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
2001-01-04 07:18 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
@ 2001-01-04 07:53 ` Tod McQuillin <devin@spamcop.net>
2001-01-04 08:31 ` Re: Sv: how to build this query ??? Please help !!! Tom Lane <tgl@sss.pgh.pa.us>
2001-01-04 08:42 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
0 siblings, 2 replies; 12+ messages in thread
From: Tod McQuillin @ 2001-01-04 07:53 UTC (permalink / raw)
To: Jens Hartwig <jhartwig@debis.com>; +Cc: Nikolaj Lundsgaard <Nikolaj@konfus.dk>; pgsql-sql
On Thu, 4 Jan 2001, Jens Hartwig wrote:
> > SELECT a_nr,
> > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder,
> > [...]
> > FROM orders
>
> Is this a documented feature? Where can I find more information about
> this? I do not want to join the actual discussion about documentation
> of PostgreSQL but I never before have seen such a construct in SQL!
Subqueries are covered in Bruce Momjian's book:
http://www.postgresql.org/docs/aw_pgsql_book/node93.html
I don't think the PostgreSQL User's Manual mentions sub-selects.
--
Tod McQuillin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Sv: how to build this query ??? Please help !!!
2001-01-03 15:40 Sv: how to build this query ??? Please help !!! Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
2001-01-04 07:18 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 07:53 ` Re: Sv: how to build this query ??? Please help !!! Tod McQuillin <devin@spamcop.net>
@ 2001-01-04 08:31 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 12+ messages in thread
From: Tom Lane @ 2001-01-04 08:31 UTC (permalink / raw)
To: Tod McQuillin <devin@spamcop.net>; +Cc: Jens Hartwig <jhartwig@debis.com>; Nikolaj Lundsgaard <Nikolaj@konfus.dk>; pgsql-sql
Tod McQuillin <devin@spamcop.net> writes:
> I don't think the PostgreSQL User's Manual mentions sub-selects.
I think you are right :-(. Geez, that's a pretty big hole in the
docs coverage.
There is some stuff in the Tutorial --- see
http://www.postgresql.org/devel-corner/docs/postgres/sql-language.htm#SELECT
and page down to "Subqueries". Note that the subquery-in-FROM feature
is new in 7.1.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Sv: how to build this query ??? Please help !!!
2001-01-03 15:40 Sv: how to build this query ??? Please help !!! Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
2001-01-04 07:18 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 07:53 ` Re: Sv: how to build this query ??? Please help !!! Tod McQuillin <devin@spamcop.net>
@ 2001-01-04 08:42 ` Jens Hartwig <jhartwig@debis.com>
2001-01-04 09:46 ` Re: Sv: how to build this query ??? Please help !!! Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 12+ messages in thread
From: Jens Hartwig @ 2001-01-04 08:42 UTC (permalink / raw)
To: Tod McQuillin <devin@spamcop.net>; +Cc: Nikolaj Lundsgaard <Nikolaj@konfus.dk>; pgsql-sql
> [...]
> Subqueries are covered in Bruce Momjian's book:
> http://www.postgresql.org/docs/aw_pgsql_book/node93.html
> [...]
Thanks for the information, but Bruce (which I have already read) and
all other docs I´ve seen "only" mention normal subselects. I know
subselects (what a poor database engineer I would be otherwise ;-)) but
I have never seen a statement like
SELECT a, (SELECT b)
FROM xyz;
IMHO this is no normal subselect and it does not correlate with the
SQL-standard. Is it a trick?
Best regards, Jens
=============================================
Jens Hartwig
---------------------------------------------
debis Systemhaus GEI mbH
10875 Berlin
Tel. : +49 (0)30 2554-3282
Fax : +49 (0)30 2554-3187
Mobil : +49 (0)170 167-2648
E-Mail : jhartwig@debis.com
=============================================
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Sv: how to build this query ??? Please help !!!
2001-01-03 15:40 Sv: how to build this query ??? Please help !!! Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
2001-01-04 07:18 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 07:53 ` Re: Sv: how to build this query ??? Please help !!! Tod McQuillin <devin@spamcop.net>
2001-01-04 08:42 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
@ 2001-01-04 09:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-04 11:13 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2001-01-04 09:46 UTC (permalink / raw)
To: Jens Hartwig <jhartwig@debis.com>; +Cc: Tod McQuillin <devin@spamcop.net>; Nikolaj Lundsgaard <Nikolaj@konfus.dk>; pgsql-sql
Jens Hartwig <jhartwig@debis.com> writes:
> I have never seen a statement like
> SELECT a, (SELECT b)
> FROM xyz;
> IMHO this is no normal subselect and it does not correlate with the
> SQL-standard. Is it a trick?
I think it's OK (we're assuming that a and b are columns of xyz, right?)
The inner select sees values from the outer select as constants. This
is not really different from
SELECT x FROM xyz WHERE y IN
(SELECT a FROM abc WHERE b = xyz.z);
where the inner WHERE relies upon an outer reference to the current
xyz tuple.
It is true that "SELECT b" isn't a valid SQL92 SELECT because it
hasn't got a FROM clause, but that's a minor quibble that just about
every vendor has invented a workaround for.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Sv: how to build this query ??? Please help !!!
2001-01-03 15:40 Sv: how to build this query ??? Please help !!! Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
2001-01-04 07:18 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 07:53 ` Re: Sv: how to build this query ??? Please help !!! Tod McQuillin <devin@spamcop.net>
2001-01-04 08:42 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 09:46 ` Re: Sv: how to build this query ??? Please help !!! Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-01-04 11:13 ` Jens Hartwig <jhartwig@debis.com>
2001-01-04 11:55 ` Re: Sv: how to build this query ??? Please help !!! Tod McQuillin <devin@spamcop.net>
2001-01-08 22:24 ` Re: Sv: how to build this query ??? Please help !!! Robert B. Easter <reaster@comptechnews.com>
0 siblings, 2 replies; 12+ messages in thread
From: Jens Hartwig @ 2001-01-04 11:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tod McQuillin <devin@spamcop.net>; Nikolaj Lundsgaard <Nikolaj@konfus.dk>; pgsql-sql
Hello Tom,
> [...]
> > SELECT a, (SELECT b)
> > FROM xyz;
> [...]
> I think it's OK (we're assuming that a and b are columns of xyz, right?)
> [...]
immediately after having sent my message I realized my fault: a and b
are not of the same table! Correctly, the statement had to be something
like:
SELECT a, (SELECT b FROM z WHERE b = a)
FROM x;
> [...]
> This is not really different from
> SELECT x FROM xyz WHERE y IN
> (SELECT a FROM abc WHERE b = xyz.z);
> [...]
Now it is :-) In a subquery, the inner query is only used for things
like comparison (as it is in your example). In my example the result
shows me two columns (in one record!!) which belong to different tables.
Mmmmh ... I tested the following:
create table x (a numeric);
create table y (b numeric);
insert into x values (1);
insert into x values (2);
insert into y values (1);
insert into y values (2);
select a, (select b from y) from x;
=> ERROR: More than one tuple returned by a subselect used as an
expression.
This is ok, anything else would have shocked me.
select a, (select b from y where b = a) from x;
a | ?column?
----------+----------
1.000000 | 1.000000
2.000000 | 2.000000
This result made me understanding that this special case of "subqueries"
is possibly nothing more than a special form of joins between tables:
select a, b
from x, y
where x.a = y.b;
brings the same result. Now, back to the first example (of Nikolaj):
SELECT a_nr,
(SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder,
(SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
'zdr') AS #zdr,
(SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
'zcu') AS #zcu,
(SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
'zcr') AS #zcr,
product, state
FROM orders;
This would be a self-join of one table like:
select ord.a_nr,
c1.count(*),
c2.count(*),
...
from orders ord,
cylinders c1,
cylinders c2,
...
where c1.z_a_nr = ord.a_nr
and c2.z_status = 'zdr'
and ...
This in fact is not possible in PostgreSQL (it seems that the table
alias "c1" cannot be prefixed to the aggregate-function "count(*)") and
AFAIK in no other relational database. I really cannot imagine any
equivalent join-statement (or anything else like a subquery) which
brings the same results! Does this at all correlate with the philosophy
of a relational database?
Best regards, Jens
=============================================
Jens Hartwig
---------------------------------------------
debis Systemhaus GEI mbH
10875 Berlin
Tel. : +49 (0)30 2554-3282
Fax : +49 (0)30 2554-3187
Mobil : +49 (0)170 167-2648
E-Mail : jhartwig@debis.com
=============================================
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Sv: how to build this query ??? Please help !!!
2001-01-03 15:40 Sv: how to build this query ??? Please help !!! Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
2001-01-04 07:18 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 07:53 ` Re: Sv: how to build this query ??? Please help !!! Tod McQuillin <devin@spamcop.net>
2001-01-04 08:42 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 09:46 ` Re: Sv: how to build this query ??? Please help !!! Tom Lane <tgl@sss.pgh.pa.us>
2001-01-04 11:13 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
@ 2001-01-04 11:55 ` Tod McQuillin <devin@spamcop.net>
1 sibling, 0 replies; 12+ messages in thread
From: Tod McQuillin @ 2001-01-04 11:55 UTC (permalink / raw)
To: Jens Hartwig <jhartwig@debis.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Nikolaj Lundsgaard <Nikolaj@konfus.dk>; pgsql-sql
On Thu, 4 Jan 2001, Jens Hartwig wrote:
> This would be a self-join of one table like:
>
> select ord.a_nr,
> c1.count(*),
> c2.count(*),
> ...
> from orders ord,
> cylinders c1,
> cylinders c2,
> ...
> where c1.z_a_nr = ord.a_nr
> and c2.z_status = 'zdr'
> and ...
>
> This in fact is not possible in PostgreSQL (it seems that the table
> alias "c1" cannot be prefixed to the aggregate-function "count(*)")
That's true, and you can't say count(c1.*) either, but you *can* say
count(c1.z_status)...
Continuing from your example using tables x and y:
# select count(x1.a) as ones, count(x2.a) as twos
from x x1, x x2 where x1.a = 1 and x2.a = 2;
ones | twos
------+------
1 | 1
(1 row)
But it doesn't do what you want:
# insert into x(a) values(2);
INSERT 313887 1
# select count(x1.a) as ones, count(x2.a) as twos from x x1, x x2
where x1.a = 1 and x2.a = 2;
ones | twos
------+------
2 | 2
(1 row)
An inspection of the cartesian product (select x1.a, x2.a from x x1, x x2)
will make it clear why it doesn't work.
I can't think of any way to get this:
ones | twos
------+------
1 | 2
without subqueries like so:
# select (select count(*) from x where a = 1) as ones,
(select count(*) from x where a = 2) as twos;
But, to answer your question, "Does this at all correlate with the
philosophy of a relational database?" ... My answer is yes! After all,
isn't it just the same as "select a, count(a) from x group by a" turned
sideways?
If you can think of how to do this "the hard way" (i.e. without subselects
or temp tables etc.) please share.
--
Tod McQuillin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Sv: how to build this query ??? Please help !!!
2001-01-03 15:40 Sv: how to build this query ??? Please help !!! Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
2001-01-04 07:18 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 07:53 ` Re: Sv: how to build this query ??? Please help !!! Tod McQuillin <devin@spamcop.net>
2001-01-04 08:42 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 09:46 ` Re: Sv: how to build this query ??? Please help !!! Tom Lane <tgl@sss.pgh.pa.us>
2001-01-04 11:13 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
@ 2001-01-08 22:24 ` Robert B. Easter <reaster@comptechnews.com>
2001-01-08 23:36 ` Re: Sv: how to build this query ??? Please help !!! Robert B. Easter <reaster@comptechnews.com>
2001-01-09 04:09 ` Re: Sv: how to build this query ??? Please help !!! Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 12+ messages in thread
From: Robert B. Easter @ 2001-01-08 22:24 UTC (permalink / raw)
To: Jens Hartwig <jhartwig@debis.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tod McQuillin <devin@spamcop.net>; Nikolaj Lundsgaard <Nikolaj@konfus.dk>; pgsql-sql
I was playing around with subselects and managed to crash the backend (if you
try, it can be done I guess):
create table x (a numeric);
create table y (b numeric);
insert into x values (1);
insert into x values (2);
insert into x values (3);
insert into x values (4);
insert into x values (5);
insert into x values (6);
insert into x values (7);
insert into x values (8);
insert into x values (9);
insert into x values (10);
insert into x values (11);
insert into y values (1);
insert into y values (2);
insert into y values (3);
insert into y values (4);
insert into y values (5);
insert into y values (6);
insert into y values (7);
insert into y values (8);
insert into y values (9);
select a, (select b from y) from x;
select a, (select b from y where b = a) from x;
-- this is ok ...
select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b
FROM x WHERE a IN (SELECT b FROM y)
GROUP BY a
HAVING a IN (SELECT b FROM y WHERE b > 1);
a | b
----------+----------
2.000000 | 3.000000
3.000000 | 4.000000
4.000000 | 5.000000
5.000000 | 6.000000
6.000000 | 7.000000
7.000000 | 8.000000
8.000000 | 9.000000
9.000000 |
(8 rows)
-- this crashes
select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b
FROM x WHERE a IN (SELECT b FROM y)
GROUP BY a,b
HAVING a IN (SELECT b FROM y WHERE b > 1);
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!# \connect subselects
No Postgres username specified in startup packet.
!> \connect subselects pgcvs
You are now connected to database subselects as user pgcvs.
subselects=#
This example is probably ridiculous and might break syntax!? Else maybe a
bug in not liking "AS b".
On Thursday 04 January 2001 06:13, Jens Hartwig wrote:
> Hello Tom,
>
> > [...]
> >
> > > SELECT a, (SELECT b)
> > > FROM xyz;
> >
> > [...]
> > I think it's OK (we're assuming that a and b are columns of xyz, right?)
> > [...]
>
> immediately after having sent my message I realized my fault: a and b
> are not of the same table! Correctly, the statement had to be something
> like:
>
> SELECT a, (SELECT b FROM z WHERE b = a)
> FROM x;
>
> > [...]
> > This is not really different from
> > SELECT x FROM xyz WHERE y IN
> > (SELECT a FROM abc WHERE b = xyz.z);
> > [...]
>
> Now it is :-) In a subquery, the inner query is only used for things
> like comparison (as it is in your example). In my example the result
> shows me two columns (in one record!!) which belong to different tables.
> Mmmmh ... I tested the following:
>
> create table x (a numeric);
> create table y (b numeric);
>
> insert into x values (1);
> insert into x values (2);
>
> insert into y values (1);
> insert into y values (2);
>
> select a, (select b from y) from x;
>
> => ERROR: More than one tuple returned by a subselect used as an
> expression.
>
> This is ok, anything else would have shocked me.
>
> select a, (select b from y where b = a) from x;
>
> a | ?column?
> ----------+----------
> 1.000000 | 1.000000
> 2.000000 | 2.000000
>
> This result made me understanding that this special case of "subqueries"
> is possibly nothing more than a special form of joins between tables:
>
> select a, b
> from x, y
> where x.a = y.b;
>
> brings the same result. Now, back to the first example (of Nikolaj):
>
> SELECT a_nr,
> (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder,
> (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
> 'zdr') AS #zdr,
> (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
> 'zcu') AS #zcu,
> (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
> 'zcr') AS #zcr,
> product, state
> FROM orders;
>
> This would be a self-join of one table like:
>
> select ord.a_nr,
> c1.count(*),
> c2.count(*),
> ...
> from orders ord,
> cylinders c1,
> cylinders c2,
> ...
> where c1.z_a_nr = ord.a_nr
> and c2.z_status = 'zdr'
> and ...
>
> This in fact is not possible in PostgreSQL (it seems that the table
> alias "c1" cannot be prefixed to the aggregate-function "count(*)") and
> AFAIK in no other relational database. I really cannot imagine any
> equivalent join-statement (or anything else like a subquery) which
> brings the same results! Does this at all correlate with the philosophy
> of a relational database?
>
> Best regards, Jens
>
> =============================================
> Jens Hartwig
> ---------------------------------------------
> debis Systemhaus GEI mbH
> 10875 Berlin
> Tel. : +49 (0)30 2554-3282
> Fax : +49 (0)30 2554-3187
> Mobil : +49 (0)170 167-2648
> E-Mail : jhartwig@debis.com
> =============================================
--
-------- Robert B. Easter reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Sv: how to build this query ??? Please help !!!
2001-01-03 15:40 Sv: how to build this query ??? Please help !!! Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
2001-01-04 07:18 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 07:53 ` Re: Sv: how to build this query ??? Please help !!! Tod McQuillin <devin@spamcop.net>
2001-01-04 08:42 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 09:46 ` Re: Sv: how to build this query ??? Please help !!! Tom Lane <tgl@sss.pgh.pa.us>
2001-01-04 11:13 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-08 22:24 ` Re: Sv: how to build this query ??? Please help !!! Robert B. Easter <reaster@comptechnews.com>
@ 2001-01-08 23:36 ` Robert B. Easter <reaster@comptechnews.com>
2001-01-08 23:54 ` Re: Sv: how to build this query ??? Please help !!! Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 12+ messages in thread
From: Robert B. Easter @ 2001-01-08 23:36 UTC (permalink / raw)
To: Jens Hartwig <jhartwig@debis.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tod McQuillin <devin@spamcop.net>; Nikolaj Lundsgaard <Nikolaj@konfus.dk>; pgsql-sql
Syntax Rules
1) Each <column reference> in the <group by clause> shall
unambiguously reference a column of the table resulting from
the <from clause>. A column referenced in a <group by clause> is
a grouping column.
NOTE 101 - "Column reference" is defined in Subclause 6.6,
"<column reference>".
1) Every <column reference> has a qualifying table and a qualifying
scope, as defined in succeeding Syntax Rules.
The query I did was bad syntax I guess. That b only exists in the result
table, not in the table made in the FROM clause. Still, crashes so not good.
subselects=# select a, (SELECT b FROM y WHERE y.b = x.a + 1) from x WHERE a
IN (SELECT b FROM y) GROUP BY a, b HAVING a IN (SELECT b FROM y WHERE y.b >
1);
ERROR: Attribute 'b' not found
subselects=#
That's what should be expected (right?) and I get it if I remove "AS b".
On Monday 08 January 2001 17:24, Robert B. Easter wrote:
> I was playing around with subselects and managed to crash the backend (if
> you try, it can be done I guess):
>
> create table x (a numeric);
> create table y (b numeric);
>
> insert into x values (1);
> insert into x values (2);
> insert into x values (3);
> insert into x values (4);
> insert into x values (5);
> insert into x values (6);
> insert into x values (7);
> insert into x values (8);
> insert into x values (9);
> insert into x values (10);
> insert into x values (11);
>
> insert into y values (1);
> insert into y values (2);
> insert into y values (3);
> insert into y values (4);
> insert into y values (5);
> insert into y values (6);
> insert into y values (7);
> insert into y values (8);
> insert into y values (9);
>
> select a, (select b from y) from x;
> select a, (select b from y where b = a) from x;
>
> -- this is ok ...
> select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b
> FROM x WHERE a IN (SELECT b FROM y)
> GROUP BY a
> HAVING a IN (SELECT b FROM y WHERE b > 1);
> a | b
> ----------+----------
> 2.000000 | 3.000000
> 3.000000 | 4.000000
> 4.000000 | 5.000000
> 5.000000 | 6.000000
> 6.000000 | 7.000000
> 7.000000 | 8.000000
> 8.000000 | 9.000000
> 9.000000 |
> (8 rows)
>
> -- this crashes
> select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b
> FROM x WHERE a IN (SELECT b FROM y)
> GROUP BY a,b
> HAVING a IN (SELECT b FROM y WHERE b > 1);
>
> pqReadData() -- backend closed the channel unexpectedly.
> This probably means the backend terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !# \connect subselects
> No Postgres username specified in startup packet.
> !> \connect subselects pgcvs
> You are now connected to database subselects as user pgcvs.
> subselects=#
>
> This example is probably ridiculous and might break syntax!? Else maybe a
> bug in not liking "AS b".
>
> On Thursday 04 January 2001 06:13, Jens Hartwig wrote:
> > Hello Tom,
> >
> > > [...]
> > >
> > > > SELECT a, (SELECT b)
> > > > FROM xyz;
> > >
> > > [...]
> > > I think it's OK (we're assuming that a and b are columns of xyz,
> > > right?) [...]
> >
> > immediately after having sent my message I realized my fault: a and b
> > are not of the same table! Correctly, the statement had to be something
> > like:
> >
> > SELECT a, (SELECT b FROM z WHERE b = a)
> > FROM x;
> >
> > > [...]
> > > This is not really different from
> > > SELECT x FROM xyz WHERE y IN
> > > (SELECT a FROM abc WHERE b = xyz.z);
> > > [...]
> >
> > Now it is :-) In a subquery, the inner query is only used for things
> > like comparison (as it is in your example). In my example the result
> > shows me two columns (in one record!!) which belong to different tables.
> > Mmmmh ... I tested the following:
> >
> > create table x (a numeric);
> > create table y (b numeric);
> >
> > insert into x values (1);
> > insert into x values (2);
> >
> > insert into y values (1);
> > insert into y values (2);
> >
> > select a, (select b from y) from x;
> >
> > => ERROR: More than one tuple returned by a subselect used as an
> > expression.
> >
> > This is ok, anything else would have shocked me.
> >
> > select a, (select b from y where b = a) from x;
> >
> > a | ?column?
> > ----------+----------
> > 1.000000 | 1.000000
> > 2.000000 | 2.000000
> >
> > This result made me understanding that this special case of "subqueries"
> > is possibly nothing more than a special form of joins between tables:
> >
> > select a, b
> > from x, y
> > where x.a = y.b;
> >
> > brings the same result. Now, back to the first example (of Nikolaj):
> >
> > SELECT a_nr,
> > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder,
> > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
> > 'zdr') AS #zdr,
> > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
> > 'zcu') AS #zcu,
> > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
> > 'zcr') AS #zcr,
> > product, state
> > FROM orders;
> >
> > This would be a self-join of one table like:
> >
> > select ord.a_nr,
> > c1.count(*),
> > c2.count(*),
> > ...
> > from orders ord,
> > cylinders c1,
> > cylinders c2,
> > ...
> > where c1.z_a_nr = ord.a_nr
> > and c2.z_status = 'zdr'
> > and ...
> >
> > This in fact is not possible in PostgreSQL (it seems that the table
> > alias "c1" cannot be prefixed to the aggregate-function "count(*)") and
> > AFAIK in no other relational database. I really cannot imagine any
> > equivalent join-statement (or anything else like a subquery) which
> > brings the same results! Does this at all correlate with the philosophy
> > of a relational database?
> >
> > Best regards, Jens
> >
> > =============================================
> > Jens Hartwig
> > ---------------------------------------------
> > debis Systemhaus GEI mbH
> > 10875 Berlin
> > Tel. : +49 (0)30 2554-3282
> > Fax : +49 (0)30 2554-3187
> > Mobil : +49 (0)170 167-2648
> > E-Mail : jhartwig@debis.com
> > =============================================
--
-------- Robert B. Easter reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Sv: how to build this query ??? Please help !!!
2001-01-03 15:40 Sv: how to build this query ??? Please help !!! Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
2001-01-04 07:18 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 07:53 ` Re: Sv: how to build this query ??? Please help !!! Tod McQuillin <devin@spamcop.net>
2001-01-04 08:42 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 09:46 ` Re: Sv: how to build this query ??? Please help !!! Tom Lane <tgl@sss.pgh.pa.us>
2001-01-04 11:13 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-08 22:24 ` Re: Sv: how to build this query ??? Please help !!! Robert B. Easter <reaster@comptechnews.com>
2001-01-08 23:36 ` Re: Sv: how to build this query ??? Please help !!! Robert B. Easter <reaster@comptechnews.com>
@ 2001-01-08 23:54 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2001-01-08 23:54 UTC (permalink / raw)
To: Robert B. Easter <reaster@comptechnews.com>; +Cc: Jens Hartwig <jhartwig@debis.com>; Tod McQuillin <devin@spamcop.net>; Nikolaj Lundsgaard <Nikolaj@konfus.dk>; pgsql-sql
"Robert B. Easter" <reaster@comptechnews.com> writes:
> The query I did was bad syntax I guess.
No, it's just a bug. PG accepts GROUP BY items that are references to
output columns, cf.
http://www.postgresql.org/devel-corner/docs/postgres/sql-select.htm
Looks like it gets confused when the grouped-by item contains a
subselect, however.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Sv: how to build this query ??? Please help !!!
2001-01-03 15:40 Sv: how to build this query ??? Please help !!! Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
2001-01-04 07:18 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 07:53 ` Re: Sv: how to build this query ??? Please help !!! Tod McQuillin <devin@spamcop.net>
2001-01-04 08:42 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-04 09:46 ` Re: Sv: how to build this query ??? Please help !!! Tom Lane <tgl@sss.pgh.pa.us>
2001-01-04 11:13 ` Re: Sv: how to build this query ??? Please help !!! Jens Hartwig <jhartwig@debis.com>
2001-01-08 22:24 ` Re: Sv: how to build this query ??? Please help !!! Robert B. Easter <reaster@comptechnews.com>
@ 2001-01-09 04:09 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 12+ messages in thread
From: Tom Lane @ 2001-01-09 04:09 UTC (permalink / raw)
To: Robert B. Easter <reaster@comptechnews.com>; +Cc: pgsql-sql
"Robert B. Easter" <reaster@comptechnews.com> writes:
> -- this crashes
> select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b
> FROM x WHERE a IN (SELECT b FROM y)
> GROUP BY a,b
> HAVING a IN (SELECT b FROM y WHERE b > 1);
Fixed. 'Twas another case of trying to close a subplan twice ...
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2001-01-09 04:09 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-03 15:40 Sv: how to build this query ??? Please help !!! Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
2001-01-04 07:18 ` Jens Hartwig <jhartwig@debis.com>
2001-01-04 07:53 ` Tod McQuillin <devin@spamcop.net>
2001-01-04 08:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-04 08:42 ` Jens Hartwig <jhartwig@debis.com>
2001-01-04 09:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-04 11:13 ` Jens Hartwig <jhartwig@debis.com>
2001-01-04 11:55 ` Tod McQuillin <devin@spamcop.net>
2001-01-08 22:24 ` Robert B. Easter <reaster@comptechnews.com>
2001-01-08 23:36 ` Robert B. Easter <reaster@comptechnews.com>
2001-01-08 23:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-09 04:09 ` 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