Re: Sv: how to build this query ??? Please help !!! - Mailing list pgsql-sql

Syntax Rules        1) Each <column reference> in the <group by clause> shall           unambiguously
referencea 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
SyntaxRules.   
 


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/ ------------