Обсуждение: Wrong output for 7.0.3 from NULL fields in CASE statement

Justin Clift (aa2@bigpond.net.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
thisstatement, but instead I'm getting very, very small numbers, or other weird things.  It's a catch for NULL values
andisn'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
thepostgresql-7.0.3-2mdk RPMS available from ftp.postgresql.org... so this isn't due to the known problems with
Mandrake'sdodgy 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
whereidnum = 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_paymentswhere 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_paymentswhere 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_paymentswhere idnum = 139 and payment_type = 6; 
ERROR:  Bad float4 input format -- underflow


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> foobar=# select text(CASE WHEN (cashback = NULL or cashback = 0) THEN '0' ELSE cashback END) from
transaction_paymentswhere idnum = 139 and payment_type = 6; 
>          text
> -----------------------
>  1.01855797968803e-312
> (1 row)

Try not quoting the '0'.  For reasons that I don't recall at the moment,
7.0 has a problem coercing unknown-type literals to the proper datatype
in CASE expressions.  It seems to work OK in 7.1 though.

            regards, tom lane

> 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.

For a float8 field "d", I can reproduce this with a slightly simpler
case:

lockhart=# select case when (d = null) then 0 else d end from t1;
 text
------
 0
(1 row)

lockhart=# select case when (d = null) then '0' else d end from t1;
         text
-----------------------
 1.11784577978351e+253
(1 row)

I haven't tracked it down, but I'll guess that the automatic type
conversion logic is getting confused with the stringy form of zero.

I do not see the symptom in the current development tree.

                    - Thomas