Re: [HACKERS] 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(at)alumni(dot)caltech(dot)edu
South Pasadena, California