PG 7.0 crash on SELECT

Started by Louis-David Mitterrandalmost 26 years ago2 messageshackers
Jump to latest
#1Louis-David Mitterrand
cunctator@apartia.ch

Issuing the followin SELECT crashes PG 7.0:

auction=# SELECT a.id,a.title,a.id,(select CASE WHEN a.stopdate < 'now' THEN 'closed' ELSE 'open' end) as status,to_char(a.time,'DD-MM HH24:MI'),b.price FROM auction* a, bid b WHERE a.id = b.auctionid AND b.login = 'mito2';
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!# \q

Apparently PG doesn't like the (SELECT CASE ... ) statement, until I
added it everything went well.

Here are the table descriptions:

                               Table "auction"
  Attribute   |   Type    |                     Modifier                     
--------------+-----------+--------------------------------------------------
 id           | integer   | not null default nextval('auction_id_seq'::text)
 login        | text      | not null
 startdate    | timestamp | not null default now()
 stopdate     | timestamp | not null
 description  | text      | not null
 startprice   | float8    | not null
 reserveprice | float8    | 
 category     | text      | not null
 imageurl     | text      | 
 title        | text      | not null
 quantity     | integer   | default 1
 time         | timestamp | not null default now()
 option       | bigint    | 
Index: auction_pkey
Constraints: (quantity > 0)
             (imageurl ~ '^http://'::text)
             (stopdate > startdate)
             ((reserveprice ISNULL) OR (reserveprice > startprice))
                  Table "bid"
 Attribute |   Type    |        Modifier        
-----------+-----------+------------------------
 login     | text      | not null
 auctionid | integer   | not null
 price     | float8    | not null
 quantity  | integer   | not null default 1
 time      | timestamp | not null default now()
Index: bid_pkey

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr

WARNING TO ALL PERSONNEL: Firings will continue until morale improves.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Louis-David Mitterrand (#1)
Re: PG 7.0 crash on SELECT

Louis-David Mitterrand <cunctator@apartia.ch> writes:

Issuing the followin SELECT crashes PG 7.0:
auction=# SELECT a.id,a.title,a.id,(select CASE WHEN a.stopdate < 'now' THEN 'closed' ELSE 'open' end) as status,to_char(a.time,'DD-MM HH24:MI'),b.price FROM auction* a, bid b WHERE a.id = b.auctionid AND b.login = 'mito2';
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!# \q

Apparently PG doesn't like the (SELECT CASE ... ) statement, until I
added it everything went well.

The crash certainly is a bug, but you could get around it for now
by not using an unnecessary sub-SELECT. Why not just
...,a.id,(CASE WHEN ...

regards, tom lane