Thread: case bug?

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

> 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*** parse_expr.c.orig    Sat Sep 11 13:27:11 1999
--- parse_expr.c    Sat Sep 11 13:24:03 1999
***************
*** 410,416 ****
                       * only bother with conversion if not NULL and
                       * different type...
                       */
!                     if (wtype && (wtype != ptype))
                      {
                          if (can_coerce_type(1, &wtype, &ptype))
                          {
--- 410,417 ----
                       * only bother with conversion if not NULL and
                       * different type...
                       */
!                     if (wtype && (wtype != UNKNOWNOID)
!                         && (wtype != ptype))
                      {
                          if (can_coerce_type(1, &wtype, &ptype))
                          {

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

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