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


Fri, 30 Apr 1999 20:20:18 -0500
Michael J Davis <michael.j.davis@tvguide.com>
A select with aggretion is failing, still subtle problems with ag
	gregation
Michael J Davis <michael.j.davis@tvguide.com> writes:
> 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

Yeah, "GROUP BY" on anything but a primitive column is still pretty
hosed.  I'm going to try to work on it this weekend.

			regards, tom lane

Sat, 01 May 1999 11:39:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] A select with aggretion is failing,
	still subtle problems with aggregation
Just one more question.  If you remove the cache file so the next
backend creates it, could their be problems if another backend starts
while the file is being created by another backend?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 1 May 1999 15:21:47 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
cache startup file
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> That sounds like a big win.  1/3 second is large.  If they vacuum a
> single table, and it is not a system table, can the removal be
> skipped?

I didn't do that; I just put an unconditional remove into vac_shutdown.
If you want to improve on that, be my guest ;-).

> Just one more question.  If you remove the cache file so the next
> backend creates it, could their be problems if another backend starts
> while the file is being created by another backend?

The code in relcache.c looks to be fairly robust --- if the file seems
to be broken (ie, ends early) it will go off and rebuild the file.
So I suppose you could get an extra rebuild in that scenario.

If you wanted to be really paranoid you could have the writing code
create the file under a temporary name (using the backend's PID) and
rename it into place when done; that'd prevent any kind of worry about
the wrong things happening if two backends write the file at the same
time.  But really, it shouldn't matter.

			regards, tom lane

Sat, 01 May 1999 16:39:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: cache startup file 

Is this done?  I have added it to the list.



> Michael J Davis <michael.j.davis@tvguide.com> writes:
> > 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
> 
> Yeah, "GROUP BY" on anything but a primitive column is still pretty
> hosed.  I'm going to try to work on it this weekend.
> 
> 			regards, tom lane
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 10 May 1999 12:47:36 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] A select with aggretion is failing,
	still subtle problems with aggregation