Thread: Re: [HACKERS] case bug?

>From: Tatsuo Ishii <t-ishii@sra.co.jp>
>
>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

I'd kept this as an example of case usage and tried it on
the latest source, where it worked fine.

Then I tried inserting a NULL into the table, which the
case statement then treated as 0 and not null.

insert into t1 values(null);
INSERT 150412 1
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|minus0|zero1|plus |zero
(4 rows)    

Was this discussed?

Keith.

Keith Parks <emkxp01@mtcc.demon.co.uk> writes:
> Then I tried inserting a NULL into the table, which the
> case statement then treated as 0 and not null.

This is a bug: the test expressions i < 0 etc are actually returning
NULL, but ExecEvalCase is failing to check for a NULL condition result.
It should treat a NULL as false, I expect, just as WHERE does.
        regards, tom lane

Keith Parks <emkxp01@mtcc.demon.co.uk> writes:
>> Then I tried inserting a NULL into the table, which the
>> case statement then treated as 0 and not null.

> This is a bug: the test expressions i < 0 etc are actually returning
> NULL, but ExecEvalCase is failing to check for a NULL condition result.
> It should treat a NULL as false, I expect, just as WHERE does.

Fixed --- here is the patch for REL6_5.
        regards, tom lane


*** src/backend/executor/execQual.c.orig    Sat Jun 12 15:22:40 1999
--- src/backend/executor/execQual.c    Sat Sep 18 19:28:46 1999
***************
*** 1128,1136 ****          /*          * if we have a true test, then we return the result, since the
!          * case statement is satisfied.          */
!         if (DatumGetInt32(const_value) != 0)         {             const_value = ExecEvalExpr((Node *)
wclause->result,                                       econtext,
 
--- 1128,1137 ----          /*          * if we have a true test, then we return the result, since the
!          * case statement is satisfied.  A NULL result from the test is
!          * not considered true.          */
!         if (DatumGetInt32(const_value) != 0 && ! *isNull)         {             const_value = ExecEvalExpr((Node *)
wclause->result,                                       econtext,
 

> Fixed --- here is the patch for REL6_5.

Thanks. I'll keep poking at join syntax instead of looking at this...
                    - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California