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

note that:

select i,
  case
    when i < 0 then 'minus'
    when i = 0 then 'zero'
    when i > 0 then  'plus'
  end
from t1;

also causes the same error.
---
Tatsuo Ishii

Sat, 11 Sep 1999 17:01:27 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
case bug?
> Following case statement is legal but fails in 6.5.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

Hmm. Works OK when *any* of the result values have a type associated
with them, and has trouble when they are all of unspecified type,
which afaik can only happen with strings. Patch enclosed; I haven't
tested much but it *should* be very safe; I had protected against this
case elsewhere in the same routine.

(different test values, but same schema)

Original code:

 select i,
   case
     when i < 0 then 'minus'
     when i = 0 then 'zero'
     when i > 0 then  'plus'::text
     else null
   end
 from t1;
i|case
-+----
1|plus
2|plus
3|plus
(3 rows)

After patching:

 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|plus
2|plus
3|plus
(3 rows)

Can you please exercise it and let me know if you are happy? After
that I'll commit to CURRENT and RELEASE trees...

Oh, I've found another case which has trouble, and have not yet fixed
it:

 insert into t2(i)
 select case when i > 0 then '0' else null end from t1;
INSERT 0 3
postgres=> select * from t2;
        i|  x
---------+---
137173488|   
137173488|   
137173488|   

It's never doing a conversion at all, and is putting (probably) the
pointer to the character string into the int4 result :(

Works OK when the string type is coerced:

 insert into t2(i)
 select case when i > 0 then '0'::int4 else null end from t1;
postgres=> select * from t2;
        i|  x
---------+---
        0|   
        0|   
        0|   

                - Tom

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California
Sat, 11 Sep 1999 13:41:59 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] case bug?
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> Following case statement is legal but fails in 6.5.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

Still there in current sources, too.  Looks like it's the "else null"
that triggers the problem --- probably the code that is resolving the
final output type of the CASE expression isn't coping with a null.

I think this is Lockhart's turf, but I can have a go at it if he hasn't
got time to work on it...

			regards, tom lane

Sat, 11 Sep 1999 11:34:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] case bug? 
Thomas,

> Can you please exercise it and let me know if you are happy? After
> that I'll commit to CURRENT and RELEASE trees...

Looks ok for me. Thanks.
---
Tatsuo Ishii

Sun, 12 Sep 1999 09:57:04 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [HACKERS] case bug? 