Re: Sv: how to build this query ??? Please help !!!

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(at)debis(dot)com
> =============================================

--
-------- Robert B. Easter reaster(at)comptechnews(dot)com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------