db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeremy Boynes (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-7) Bug in NULLIF Function
Date Tue, 28 Dec 2004 01:12:12 GMT
     [ 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


Mime
View raw message