Justin Clift ([EMAIL PROTECTED]) 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
