Skip site navigation (1) Skip section navigation (2)

Wrong output for 7.0.3 from NULL fields in CASE statement

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Wrong output for 7.0.3 from NULL fields in CASE statement
Date: 2001-03-01 04:48:18
Message-ID: 200103010448.f214mIB38112@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Justin Clift (aa2(at)bigpond(dot)net(dot)au) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Wrong output for 7.0.3 from NULL fields in CASE statement

Long Description
I am getting output that doesn't make sense from a simple SQL statement.  I am expecting to get a '0'::text returned by this statement, but instead I'm getting very, very small numbers, or other weird things.  It's a catch for NULL values and isn't interpreting them correctly when it gets them.

I have tested this on a machine with PostgreSQL 7.0.3 compiled from source, and I have tested this on a machine with the postgresql-7.0.3-2mdk RPMS available from ftp.postgresql.org... so this isn't due to the known problems with Mandrake's dodgy 7.0.2 RPM's.

Please see the attached code to see what I mean.

Sample Code
The fields "cashback" and "surcharge" are NULL for this query, but it seems to be returning very small numbers instead.

foobar> select * from transaction_payments;

idnum | payment_type | currency | voucher_idnum | exchange_rate | amount | cashback | surcharge |    gst
-------+--------------+----------+---------------+---------------+--------+----------+-----------+-----------
  139 |            6 |       18 |               |             1 |    170 |          |           |        17

foobar=# select text(CASE WHEN (cashback = NULL or cashback = 0) THEN '0' ELSE cashback END) from transaction_payments where idnum = 139 and payment_type = 6;
         text
-----------------------
 1.01855797968803e-312
(1 row)
 
foobar=# select text(CASE WHEN (surcharge = NULL or surcharge = 0) THEN '0' ELSE surcharge END) from transaction_payments where idnum = 139 and payment_type = 6;
         text
-----------------------
 1.01855797968803e-312
(1 row)
 
foobar=# select text(CASE WHEN (surcharge ISNULL or surcharge = 0) THEN '0' ELSE surcharge END) from transaction_payments where idnum = 139 and payment_type = 6;
 text
------
 NaN
(1 row)

foobar=# select float4(CASE WHEN (cashback = NULL or cashback = 0) THEN '0' ELSE cashback END) from transaction_payments where idnum = 139 and payment_type = 6;
ERROR:  Bad float4 input format -- underflow


No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-03-01 05:56:26
Subject: Re: Wrong output for 7.0.3 from NULL fields in CASE statement
Previous:From: Lamar OwenDate: 2001-02-28 20:38:05
Subject: Re: RPM postinstall script fails in postgresql-server-7.0.3-2.i386.rpm

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group