[SQL] Failing query...

[Shaun]

Hi All,

I was hoping someone here would be able to help me with a query I have
that is failing with the error:

psql:query:1: ERROR:  ExecEvalExpr: unknown expression type 501

The query is:

SELECT a.auction_id, a.user_id, c.other_names,
   c.surname, c.email, a.reserve, a.close_time, a.short_desc,
   a.long_desc, a.start_time,
   (COALESCE((select MAX(bid) from bid where auction_id = a.auction_id
   group by auction_id), 0)) as max_bid
FROM Auction a, Customer c
WHERE a.user_id = c.user_id
AND a.auction_id = 754;

If I run the following however, 

SELECT a.auction_id, a.user_id, c.other_names,
   c.surname, c.email, a.reserve, a.close_time, a.short_desc,
   a.long_desc, a.start_time
FROM Auction a, Customer c
WHERE a.user_id = c.user_id
AND a.auction_id = 754;

I get:

 auction_id | user_id | other_names |  surname  |email
|
reserve  |   close_time   |  short_desc   |
long_de
sc  |   start_time
+-+-+---+-+-
-++---+-
+
754 |   4 | raymond | villarica |
[EMAIL PROTECTED] |
22000.00 | 2000-08-17 17:12:16+10 | 8 Avona Av Glebe 2037 | other
residential 70
8 square metres | 1990-01-01 00:00:00+11
(1 row)

If I run:

select MAX(bid) from bid where auction_id = 754;

I get:

max

 310.00
(1 row)

Can someone please help me understand what I'm doing wrong. If there is no
value in the select max() subselect, the query works, but if there is, it
doesn't.

Please CC: me to any reply as I've emailed to join the list but the reply
hasn't yet reached me from majordomo.

Thanks,
Shaun

Re: [SQL] Failing query...

[Tom Lane]

Shaun <[EMAIL PROTECTED]> writes:
> SELECT a.auction_id, a.user_id, c.other_names,
>c.surname, c.email, a.reserve, a.close_time, a.short_desc,
>a.long_desc, a.start_time,
>(COALESCE((select MAX(bid) from bid where auction_id = a.auction_id
>group by auction_id), 0)) as max_bid
> FROM Auction a, Customer c
> WHERE a.user_id = c.user_id
> AND a.auction_id = 754;

Sub-selects inside COALESCE don't work :-(.  This was just fixed about a
week ago --- it will be in 7.1.  In the meantime you might try it the
other way round:

   (select COALESCE(MAX(bid), 0) from bid where auction_id = a.auction_id)
   as max_bid

regards, tom lane

Re: [SQL] Tuple size limit.

[Tom Lane]

Christopher Sawtell <[EMAIL PROTECTED]> writes:
> I understand that the 7.1 release currently in CVS does not have this
> limitation. So I'd like to know if this 7.1 release is imminent;
> i.e. < ~2 to 3 months?

If it's not out in <3 months, you won't be the only person who's very
unhappy ;-).  I don't want to see us hold up TOAST that long, even if
it means not having some of the other features originally planned for
7.1...

regards, tom lane