Thread

Re: [HACKERS] case bug?

Keith Parks <emkxp01@mtcc.demon.co.uk> — 1999-09-18T16:39:46Z

>From: Tatsuo Ishii <t-ishii@sra.co.jp>
>
>Following case statement is legal but fails in 6.5.1.
>
>drop table t1;
>DROP
>create table t1(i int);
>CREATE
>insert into t1 values(-1);
>INSERT 4047465 1
>insert into t1 values(0);
>INSERT 4047466 1
>insert into t1 values(1);
>INSERT 4047467 1
>
>select i,
>  case
>    when i < 0 then 'minus'
>    when i = 0 then 'zero'
>    when i > 0 then  'plus'
>    else null
>  end
>from t1;
>ERROR:  Unable to locate type oid 0 in catalog

I'd kept this as an example of case usage and tried it on
the latest source, where it worked fine.

Then I tried inserting a NULL into the table, which the
case statement then treated as 0 and not null.

insert into t1 values(null);
INSERT 150412 1
select i,
  case
    when i < 0 then 'minus'
    when i = 0 then 'zero'
    when i > 0 then  'plus'
    else null
  end
from t1;
 i|case
--+-----
-1|minus
 0|zero
 1|plus
  |zero
(4 rows)    

Was this discussed?

Keith.