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

[Jeremy Boynes]

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