Re: [HACKERS] case bug? - Mailing list pgsql-hackers

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