Hi,
 
Just got back from 3-weeks vacation and I see that patch for Derby-7 NULLIF function is still waiting to be committed/reviewed for a long time now. I was wondering if someone can take a look and see if it is all ready to be committed.
 
thanks,
Mamta
 
On 6/12/05, Mamta Satoor <msatoor@gmail.com> wrote:
Hi,
 
I have attached a patch in JIRA to Derby-7 to fix the NULLIF problem. The test suites have run fine with the exception of miserrors. Since the time I posted the patch, I saw subsequent emails about miscerror failure possibly related to checkin from David Van Couvering. David I think has already posted a patch on the list to fix the failure. 
 
I have copied the description of the fix from the JIRA here for reference.  
 
"Following is a brief description of what the proposed fix involves.
For NULLIF(L,R), parser will generate CASE L=R then untyped NULL else L. Notice that the NULL does not have any type associated with it during parsing. This change went into sqlgrammar.jj
The existing code in ConditionalNode.bindExpression first binds the condition L=R. At the end of L=R condition binding, we will have determined the type of operand L. I have added cod into ConditionalNode to CAST untyped NULL to the type of L. The exisiting code for L=R binding enforces the Reference Guide Table 1. Comparisons allowed by Derby on page 111 and 112. So answers for what datatypes can be combined in NULLIF can be explained by that table.
I have added some tests to see what the return type of NULLIF would be for various combinations of :L and R datatypes. I have also added test cases where the first operand to NULLIF is a parameter.

All the tests pass except one failure below which I don't think is related to my changes
*** Start: miscerrors jdk1.4.2_07 derbyall:derbylang 2005-06-10 01:43:31 ***
35 del
< xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |92 |0 | wombat | null |Cleanup action starting
36 del
< xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |92 |0 | wombat | null |Failed Statement is: --
37 del
< xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |97 |0 | wombat | null |Cleanup action starting
38 del
< xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |97 |0 | wombat | null |Failed Statement is: create table a (one int, two int)
39 del
< xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |100 |0 | wombat | null |Cleanup action starting
40 del
< xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |100 |0 | wombat | null |Failed Statement is: create table a (one int)
40a35,40
> xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |91 |0 | wombat | null |Cleanup action starting
> xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |91 |0 | wombat | null |Failed Statement is: --
> xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |96 |0 | wombat | null |Cleanup action starting
> xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |96 |0 | wombat | null |Failed Statement is: create table a (one int, two int)
> xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |99 |0 | wombat | null |Cleanup action starting
> xxxxxxFILTERED-TIMESTAMPxxxxx|main,5,main |99 |0 | wombat | null |Failed Statement is: create table a (one int)
Test Failed.
*** End: miscerrors jdk1.4.2_07 derbyall:derbylang 2005-06-10 01:43:48 ***


Output of svn stat
M java\engine\org\apache\derby\impl\sql\compile\ConditionalNode.java
M java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj
M java\testing\org\apache\derbyTesting\functionTests\tests\lang\coalesceTests.java
M java\testing\org\apache\derbyTesting\functionTests\tests\jdbcapi\resultset.java
M java\testing\org\apache\derbyTesting\functionTests\master\DerbyNet\resultset.out
M java\testing\org\apache\derbyTesting\functionTests\master\DerbyNetClient\resultset.out
M java\testing\org\apache\derbyTesting\functionTests\master\resultset.out
 
If everything looks good and no one has comments, can a commiter please commit this patch?
Mamta