Thread: Failing query...

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 |
ray@woodstock.tabnsw.com.au |
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
------------3100000.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

Shaun <delius@progsoc.uts.edu.au> 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