A select with aggretion is failing, still subtle problems with ag gregation

From: Michael J Davis <michael(dot)j(dot)davis(at)tvguide(dot)com>
To: hackers(at)postgresql(dot)org
Subject: A select with aggretion is failing, still subtle problems with ag gregation
Date: 1999-05-01 01:20:18
Message-ID: 93C04F1F5173D211A27900105AA8FCFC14545B@lambic.prevuenet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

The following select fails:

> select invoiceid + 3 as type, memberid, 1, max(TotShippingHandling)
> from InvoiceLineDetails
> where TotShippingHandling <> 0
> group by type, memberid limit 10;
ERROR: replace_agg_clause: variable not in target list

The following select works (the + 3 has been eliminated):

> select invoiceid as type, memberid, 1, max(TotShippingHandling)
> from InvoiceLineDetails
> where TotShippingHandling <> 0
> group by type, memberid limit 10;
type|memberid|?column?| max
-----+--------+--------+-----
15499| 1626| 1| 6.00
15524| 138| 1| 3.00
15647| 1083| 1|20.00
15653| 1230| 1| 4.00
15659| 1600| 1| 3.00
15671| 1276| 1| 3.50
15672| 1494| 1| 3.00
15673| 1653| 1| 4.50
15674| 1624| 1| 6.00
15675| 1406| 1| 7.00
(10 rows)

Here is a description of the view InvoiceLineDetails:

> \d InvoiceLineDetails
View = invoicelinedetails
Query = Not a view
+----------------------------------+----------------------------------+-----
--+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-----
--+
| invoicelinesid | int4 |
4 |
| invoiceid | int4 |
4 |
| dateprinted | datetime |
8 |
| ordersid | int4 |
4 |
| ordertypeid | int4 |
4 |
| totshippinghandling | numeric |
var |
| shippeddate | datetime |
8 |
| memberid | int4 |
4 |
| gift | numeric |
var |
| shippinghandling | numeric |
var |
| unitcost | numeric |
var |
| unitprice | numeric |
var |
| quantity | int4 |
4 |
| invamount | numeric |
var |
| inventoryid | int4 |
4 |
| inventoryname | varchar() |
0 |
| inventorytypeid | int4 |
4 |
| inventorytypename | varchar() |
32 |
| categoriesid | int4 |
4 |
| tapenum | int4 |
4 |
+----------------------------------+----------------------------------+-----
--+

Browse pgsql-hackers by date

  From Date Subject
Next Message Vadim Mikheev 1999-05-01 08:23:22 locking...
Previous Message Bruce Momjian 1999-04-30 20:56:59 Re: [HACKERS] Optimizer fed bad data about some system-table indexes