Thread: 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