This is a multi-part message in MIME format.
--------------BDBB901D733FEEA4DA09F279
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
> 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 --------------BDBB901D733FEEA4DA09F279 Content-Type: text/plain; charset=us-ascii; name="parse_expr.c.patch" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="parse_expr.c.patch"*** 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)) {
--------------BDBB901D733FEEA4DA09F279--
************
-- end of forwarded message --
-- С тем, что не помешает никогда, Kittle