Обсуждение: Case statement ready?
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)
"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
> > 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
> 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, Pennsylvania19026
>
> > > 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) #
> > 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
> 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
>
> > > 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) #
> > > > 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