db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeremy Boynes <jboy...@apache.org>
Subject Re: [jira] Commented: (DERBY-7) Bug in NULLIF Function
Date Tue, 28 Dec 2004 01:19:09 GMT
RPost wrote:
> The discussion now seems to involve comparisons between 'char' and 'int'.
> 
> Please clarify what result Derby should provide for these examples:
> 
> 1. nullif('a','b');
    'a' with type CHAR(1)
> 2. nullif(1,2);
    1 with type INTEGER
> 3. nullif('a', 1);
    error as 'a' is not a valid numeric
> 4. nullif(1, 'a');
    error as 'a' is not a valid numeric
> 5. nullif(CAST(NULL as Char(1)), 1)
    NULL with type CHAR(1)
> 6. nullif(1, CAST(NULL as Char(1)))
    1 with type INTEGER

> 
> The original bug report says that example 1 works ok but example 2 gives an
> error message:
> 
> 
>> --> Error message: "ERROR 42X89: Types 'CHAR' and
>>     'INTEGER' are not type compatible. (Neither type
>>     is assignable to the other type.)"
> 
> 
> Is it being suggested that Derby allow examples 3, 4, 5 and 6?
> 
> Both Oracle and DB2 report an error for examples 3, 4, 5 and 6.
> 
> Oracle reports:
> 
> ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
> 

Which I believe is a problem with Oracle being stricter about the 
comparability of the parameters to NULLIF(). If you re-write the NULLIF 
using a CASE expression then it does allow the different datatypes.

For example,
SELECT nullif('1', 2) FROM DUAL;
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

SELECT CASE WHEN '1' = 2 THEN NULL ELSE '1' END FROM DUAL
C
-
1


> DB2 reports:
> 
> DBA2191E SQL execution error.
> 
> com.ibm.db.DataException: A database manager error occurred. : [IBM][CLI
> Driver][DB2/NT] SQL0171N  The data type, length or value of argument "2" of
> routine "SYSIBM.NULLIF" is incorrect.  SQLSTATE=42815
> 

I don't have a DB2 instance to play with, however, for SQL Server 2000:
SELECT NULLIF('1', 1)
   NULL
SELECT NULLIF('a', 1)
   Syntax error converting the varchar value 'a' to a column of data 
type int.

> Shouldn't Derby return the error originally reported for the last 4
> examples?
> 

No, I think it should error just for 3) and 4) as the values are not 
comparable.

--
Jeremy

Mime
View raw message