[jira] Commented: (DERBY-7) Bug in NULLIF Function

["Amit Handa (JIRA)" <de...@db.apache.org>]

     [ http://nagoya.apache.org/jira/browse/DERBY-7?page=comments#action_56929 ]
     
Amit Handa commented on DERBY-7:
--------------------------------


The following needs to be commented out in ConditionalNode.bindExpression() as a solution for NULLIF to accept INT
In trunk/java/engine/org/pacahe/derby/impl/sql/compile/ConditionalNode.java



	/* ALL COMMENTED OUT BELOW, NO NEED FOR FOLLOWING VALIDATION BETWEEN 
         * thenExpression and elseExpression

	
        if (! thenExpression.getTypeCompiler().
			 comparable(elseExpression.getTypeId(), false, getClassFactory()) &&
			! cu.assignableTo(thenExpression.getTypeId().getCorrespondingJavaTypeName(),
							  elseExpression.getTypeId().getCorrespondingJavaTypeName()) &&
			! cu.assignableTo(elseExpression.getTypeId().getCorrespondingJavaTypeName(),
							  thenExpression.getTypeId().getCorrespondingJavaTypeName()))
		{
			throw StandardException.newException(SQLState.LANG_NOT_TYPE_COMPATIBLE, 
						thenExpression.getTypeId().getSQLTypeName(),
						elseExpression.getTypeId().getSQLTypeName()
						);
		
}
        * ALL COMMENTED OUT ABOVE
        */


Also I have checked various cases where the following code if it is removed may cause problems.
The cases are:-

1. An int and a char e.g. NULLIF(1,'pqr'); where pqr is a generic string and not a name of column.
It returns the message ERROR 42818: Comparisons between 'INTEGER' and 'CHAR' are not supported.

2. An int and a column_name passed. If column_type and other expression are of same type, it works. If type of column_name passed and the other expression are of differrent types it fails with a proper message ERROR 42818: Comparisons between 'INTEGER' and 'CHAR' are not supported.
i.e it works between type compatible expressions.

I will submit a patch soon.

Kindly verify and post any comments to this proposed solution.


> Bug in NULLIF Function
> ----------------------
>
>          Key: DERBY-7
>          URL: http://nagoya.apache.org/jira/browse/DERBY-7
>      Project: Derby
>         Type: Bug
>   Components: SQL
>     Versions: 10.0.2.0
>     Reporter: Tulika Agrawal
>     Priority: Minor

>
> 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://nagoya.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira