Re: Sv: how to build this query ??? Please help !!! - Mailing list 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 bFROM x WHERE a IN (SELECT b FROM y)GROUP BY aHAVING a IN (SELECT b
FROMy WHERE b > 1);   a     |    b
 
----------+----------2.000000 | 3.0000003.000000 | 4.0000004.000000 | 5.0000005.000000 | 6.0000006.000000 |
7.0000007.000000| 8.0000008.000000 | 9.0000009.000000 |
 
(8 rows)           

-- this crashes
select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS bFROM x WHERE a IN (SELECT b FROM y)GROUP BY a,bHAVING a IN (SELECT
bFROM 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/ ------------