db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-7) Bug in NULLIF Function
Date Fri, 10 Jun 2005 20:57:51 GMT
    [ http://issues.apache.org/jira/browse/DERBY-7?page=comments#action_12313328 ] 

Mamta A. Satoor commented on DERBY-7:
-------------------------------------

I have been working on this bug for a little while and have a patch which I think resolves
issues with NULLIF. Please find the patch Derby7NullIf061005svndiff.txt attached to this bug.


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


> Bug in NULLIF Function
> ----------------------
>
>          Key: DERBY-7
>          URL: http://issues.apache.org/jira/browse/DERBY-7
>      Project: Derby
>         Type: Bug
>   Components: SQL
>     Versions: 10.0.2.0
>     Reporter: Tulika Agrawal
>     Priority: Minor
>  Attachments: Derby-7.txt, derby-nullif.patch
>
> Reporting for Christian d'Heureuse, filed on derby-dev list.
> The NULLIF built-in function of Cloudscape 10.0.1.0 beta seems to accept
> only string values.
> Examples:
>  values nullif('a','b');
>  --> OK
>  values nullif(1,2);
>  --> Error message: "ERROR 42X89: Types 'CHAR' and
>      'INTEGER' are not type compatible. (Neither type
>      is assignable to the other type.)"

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message