[ http://nagoya.apache.org/jira/browse/DERBY-7?page=comments#action_57079 ]
Jeremy Boynes commented on DERBY-7:
-----------------------------------
According to the SQL spec (ISO/IEC 9075-2:2003)
NULLIF (V1, V2) is equivalent to the following <case specification>:
CASE WHEN V1=V2 THEN NULL ELSE V1 END
and
The declared type of a <case specification> is determined by applying Subclause
9.3, "Data types of results of aggregations", to the declared types of all
<result expression>s in the <case specification>.
9.3 is long but basically says that the type of the result will be the type of
V1 as the type of NULL is undefined.
As part of this V1 and V2 must be comparable. Therefore, as I read it,
NULLIF('a', 1) should raise an exception as 'a' cannot be converted to a
numeric but NULLIF('1', 1) should return NULL with type CHAR(1) and NULLIF(1,
'1') should return NULL with type INTEGER. Of course the type of NULL is fairly
meaningless but it would have an impact if we were referencing columns instead.
> 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
