From kyle@actarg.com Wed Apr 4 15:29:42 2001 Received: from actarg.com (host25.actarg.com [209.180.91.25] (may be forged)) by postgresql.org (8.11.3/8.11.1) with ESMTP id f34FTg843134 for ; Wed, 4 Apr 2001 11:29:42 -0400 (EDT) (envelope-from kyle@actarg.com) Received: from chi.actarg.com (IDENT:root@chi.actarg.com [192.168.2.16]) by actarg.com (8.9.3/8.9.3) with ESMTP id JAA08825; Wed, 4 Apr 2001 09:24:35 -0600 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id JAA01482; Wed, 4 Apr 2001 09:24:42 -0600 Message-ID: <3ACB3CB9.CB5F0C46@actarg.com> Date: Wed, 04 Apr 2001 09:24:41 -0600 From: Kyle Organization: Action Target Inc. X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org CC: tgl@sss.pgh.pa.us Subject: Query broken under 7.1RC2 Content-Type: multipart/mixed; boundary="------------79F5B8AB3B7C171A123A2959" X-Archive-Number: 200104/43 X-Sequence-Number: 2382 This is a multi-part message in MIME format. --------------79F5B8AB3B7C171A123A2959 Content-Type: multipart/alternative; boundary="------------61907854A28E5C5CE5323D19" --------------61907854A28E5C5CE5323D19 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit This query worked under 7.0.3 but yields an error under 7.1RC1 or RC2. The error message is: psql:outbug.sql:43: ERROR: Sub-SELECT uses un-GROUPed attribute h.ordnum from outer query Tom, you patched util/clauses.c (near line 540) a while back to prevent the same error message on a different query. This may be related. The SQL is a little strange because the subquery tries to reference individual records from the outer query and then sum them. The more I look at it, I wonder if it is not just bad SQL. But the last one I found like this turned out to reveal a bug, so here it is: drop table ord_hdr; drop table cinv_hdr; drop table cinv_items; drop table inc_link; create table ord_hdr ( ordnum int4, proj int4 ); create table cinv_hdr ( ordnum int4, hinum int4, status varchar ); create table cinv_items ( ordnum int4, hinum int4, quant int4, unit_price numeric(12,2) ); create table inc_link ( ordnum int4, hinum int4, amount numeric(12,2) ); select sum(i.quant*i.unit_price::float8), (select coalesce(sum(amount),0) from inc_link where ordnum = h.ordnum and hinum = h.hinum) from cinv_hdr h, cinv_items i, ord_hdr o where o.ordnum = h.ordnum and h.ordnum = i.ordnum and h.hinum = i.hinum and o.proj = 1051 and h.status = 'open' ; --------------61907854A28E5C5CE5323D19 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit This query worked under 7.0.3 but yields an error under 7.1RC1 or RC2.

The error message is:
psql:outbug.sql:43: ERROR:  Sub-SELECT uses un-GROUPed attribute h.ordnum from outer query

Tom, you patched util/clauses.c (near line 540) a while back to prevent the same error message on a different query.  This may be related.

The SQL is a little strange because the subquery tries to reference individual records from the outer query and then sum them.
The more I look at it, I wonder if it is not just bad SQL. But the last one I found like this turned out to reveal a bug, so here it is:

drop table ord_hdr;
drop table cinv_hdr;
drop table cinv_items;
drop table inc_link;

create table ord_hdr (
    ordnum      int4,
    proj        int4
);
 

create table cinv_hdr (
    ordnum      int4,
    hinum       int4,
    status      varchar
);

create table cinv_items (
    ordnum      int4,
    hinum       int4,
    quant       int4,
    unit_price  numeric(12,2)
);

create table inc_link (
    ordnum      int4,
    hinum       int4,
    amount      numeric(12,2)
);

select sum(i.quant*i.unit_price::float8),
   (select coalesce(sum(amount),0) from inc_link where ordnum = h.ordnum and hinum = h.hinum)

    from cinv_hdr h, cinv_items i, ord_hdr o where

    o.ordnum = h.ordnum and
    h.ordnum = i.ordnum and
    h.hinum = i.hinum and

    o.proj = 1051 and
    h.status = 'open'
; --------------61907854A28E5C5CE5323D19-- --------------79F5B8AB3B7C171A123A2959 Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Content-Disposition: attachment; filename="kyle.vcf" begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;;;;;; version:2.1 email;internet:kyle@actarg.com title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard --------------79F5B8AB3B7C171A123A2959-- From tgl@sss.pgh.pa.us Wed Apr 4 15:54:36 2001 Received: from sss.pgh.pa.us ([216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f34Fsa847368 for ; Wed, 4 Apr 2001 11:54:36 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f34FsNw10794; Wed, 4 Apr 2001 11:54:23 -0400 (EDT) To: Kyle cc: pgsql-sql@postgresql.org Subject: Re: Query broken under 7.1RC2 In-reply-to: <3ACB3CB9.CB5F0C46@actarg.com> References: <3ACB3CB9.CB5F0C46@actarg.com> Comments: In-reply-to Kyle message dated "Wed, 04 Apr 2001 09:24:41 -0600" Date: Wed, 04 Apr 2001 11:54:22 -0400 Message-ID: <10791.986399662@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200104/47 X-Sequence-Number: 2386 Kyle writes: > The SQL is a little strange because the subquery tries to reference > individual records from the outer query and then sum them. > The more I look at it, I wonder if it is not just bad SQL. It is bad SQL, but pre-7.1 Postgres failed to catch it. Here's the CVS log entry: 2000-09-25 14:09 tgl * src/backend/optimizer/plan/planner.c: System neglected to complain about ungrouped variables passed to sublinks when outer query contained aggregates but no GROUP clause. Since you have a SUM(), the outer query is a grouped query, and so references to the ungrouped h.* columns in the SELECT targetlist are not well defined. Lord knows what result you were getting from 7.0 ... regards, tom lane