I seem to have a CASE statement implemented (examples at end). Is anyone
in the middle of preparing a big patch, or can I go ahead and commit my
changes? They touch roughly a dozen files.

Also, I added arbitrary code to keep the rewriter from complaining, and
have not made changes to calculate costs for the optimizer. Would the
resident experts (Jan and Bruce?) be willing to look at that once the
patches are in?

                      - Tom

Here is the table:

postgres=> select * from t2;
i|  f
-+---
1|2.2
3|2.2
4|  4
(3 rows)

And here is a CASE, including promoting ints to floats for the mixed
types in the WHERE clause:

postgres=> select case when i > f then f else i end from t2;
?column?
--------
       1
     2.2
       4
(3 rows)

Shows filling with NULLs if no default clause is specified:

postgres=> select case when i > f then f end from t2;
?column?
--------

     2.2

(3 rows)

And this last one shows that CASE is allowed inside a qualification
clause, though I'm having a hard time picturing how that might be
useful:

postgres=> select * from t2 where case when i > f then f end is not
null;
i|  f
-+---
3|2.2
(1 row)

Fri, 04 Dec 1998 06:59:45 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Case statement ready?
"Thomas G. Lockhart" wrote:
> 
> I seem to have a CASE statement implemented (examples at end). Is anyone
> in the middle of preparing a big patch, or can I go ahead and commit my
> changes? They touch roughly a dozen files.

These are files I'm changing for MVCC now:

gist.c          indexam.c       pg_am.h         tqual.c
Makefile.lmgr   gistscan.c      ipc.h           pg_class.h
tqual.h         hashpage.c      ipci.c          proc.h
transsup.c      buf_init.c      heapam.c        lmgr.c
rtree.c         xact.c          buf_internals.h heapam.h
lmgr.h          rtscan.c        bufmgr.c        hio.c
lock.c          s_lock.c        bufmgr.h        hio.h
lock.h          s_lock.h        execUtils.c     htup.h
nbtpage.c       shmem.c

Vadim

Fri, 04 Dec 1998 14:10:52 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Case statement ready?
> > I seem to have a CASE statement implemented...

OK, it looked to me that I'm not touching any files Vadim is working on,
so I've gone ahead and committed changes for the CASE construct to the
CURRENT (main) tree.

The changes don't actually affect the system catalogs, but I would
assume that we would not want this to be a feature added to the RELEASE
tree. Speak up if you think it should be added there too.

I've put in a rudimentary regression test for CASE, but I'll update that
soon with some better tests.

If anyone has a chance to look at the new code wrt the rewriter and
optimizer costing that would be great. For the costing I would assume
that the cost should be related to the sum of the costs of the
individual clauses within the CASE statement (or maybe half of the sum).

                     - Tom

Fri, 04 Dec 1998 15:44:11 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Case statement ready?
> If anyone has a chance to look at the new code wrt the rewriter and
> optimizer costing that would be great. For the costing I would assume
> that the cost should be related to the sum of the costs of the
> individual clauses within the CASE statement (or maybe half of the sum).

Isn't only one of the case values going to be used?  Wouldn't you want
the average of the case entry costs?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 4 Dec 1998 11:36:58 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Case statement ready?
>
> > > I seem to have a CASE statement implemented...
>
> OK, it looked to me that I'm not touching any files Vadim is working on,
> so I've gone ahead and committed changes for the CASE construct to the
> CURRENT (main) tree.
>
> The changes don't actually affect the system catalogs, but I would
> assume that we would not want this to be a feature added to the RELEASE
> tree. Speak up if you think it should be added there too.

    It's  feature  and  thus must NOT be committed to the RELEASE
    tree.  But if you send me the patches, I could add it to  the
    v6.4.1   feature  patch  which  I'll  put  into  the  patches
    directory on the ftp server after v6.4.1 is out.

> If anyone has a chance to look at the new code wrt the rewriter and
> optimizer costing that would be great. For the costing I would assume
> that the cost should be related to the sum of the costs of the
> individual clauses within the CASE statement (or maybe half of the sum).

    Did it add new node types? If not, the rewrite  system  might
    already be happy with it. Anyway - I'll take a look at it and
    check/extend/fix for the rewrite corner.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Fri, 4 Dec 1998 20:45:43 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Case statement ready?
> > For the costing I would assume
> > that the cost should be related to the sum of the costs of the
> > individual clauses within the CASE statement (or maybe half of the 
> > sum).
> Isn't only one of the case values going to be used?  Wouldn't you want
> the average of the case entry costs?

Hmm. Actually, on average half of the conditional clauses will be
evaluated, and then one of the result clauses will be evaluated. So
perhaps the cost should be:

  (0.5*sum(conditional clauses))+avg(result clauses)

Or perhaps we should be pessimistic and leave off the 0.5.

                - Tom

Sat, 05 Dec 1998 06:01:13 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Case statement ready?
>     It's  feature  and  thus must NOT be committed to the RELEASE
>     tree.  But if you send me the patches, I could add it to  the
>     v6.4.1   feature  patch  which  I'll  put  into  the  patches
>     directory on the ftp server after v6.4.1 is out.

I'm thinking that it will lead to nothing but trouble. How about waiting
for v6.5?

> > If anyone has a chance to look at the new code wrt the rewriter
>     Did it add new node types? If not, the rewrite  system  might
>     already be happy with it. Anyway - I'll take a look at it and
>     check/extend/fix for the rewrite corner.

Yes, it added new node types (CaseExpr and CaseWhen), and I added a few
lines to rewriteHandler.c to pretty much ignore them. Not sure if that
was the right thing to do.

                  - Tom

Sat, 05 Dec 1998 06:03:22 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Case statement ready?
>
> > > If anyone has a chance to look at the new code wrt the rewriter
> >     Did it add new node types? If not, the rewrite  system  might
> >     already be happy with it. Anyway - I'll take a look at it and
> >     check/extend/fix for the rewrite corner.
>
> Yes, it added new node types (CaseExpr and CaseWhen), and I added a few
> lines to rewriteHandler.c to pretty much ignore them. Not sure if that
> was the right thing to do.

    I expect that the structures below the two new nodes can also
    contain Var nodes. And then it's definitely wrong  to  ignore
    them.   They  must  be handled properly in rewriteHandler and
    rewriteManip.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Mon, 7 Dec 1998 10:57:17 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Case statement ready?
> > > > If anyone has a chance to look at the new code wrt the rewriter
>     I expect that the structures below the two new nodes can also
>     contain Var nodes. And then it's definitely wrong  to  ignore
>     them.   They  must  be handled properly in rewriteHandler and
>     rewriteManip.

I've just committed changes to the main development tree which allow
CASE statement handling for at least some cases of rules and views.

Still have trouble (crashes) with CASE statements involving mixing
columns from multiple tables during joins. But the single-table stuff
(and multi-table when the case statement is confined to a single table)
seems to work for my testing.

I have an update to the CASE regression test which has many more tests,
and will put it in the tree and on this list fairly soon (to get some
Oracle/etc testing of results).

                    - Tom

Mon, 14 Dec 1998 00:29:15 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Case statement ready!