Thread

1. Wrong output for 7.0.3 from NULL fields in CASE statement

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-03-01T04:48:18Z

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

2. Re: Wrong output for 7.0.3 from NULL fields in CASE statement

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-01T05:56:26Z

   pgsql-bugs@postgresql.org writes:
   > 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)
   
   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
   
   

3. Re: Wrong output for 7.0.3 from NULL fields in CASE statement

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-03-01T06:59:00Z

   > 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