>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|minus
 0|zero
 1|plus
  |zero
(4 rows)    

Was this discussed?

Keith.


Sat, 18 Sep 1999 17:39:46 +0100 (BST)
Keith Parks <emkxp01@mtcc.demon.co.uk>
Re: [HACKERS] case bug?
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

Sat, 18 Sep 1999 15:37:17 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] case bug? 
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,

Sat, 18 Sep 1999 19:31:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] case bug? 
> 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

Sun, 19 Sep 1999 05:39:54 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] case bug?