db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "RPost" <rp0...@pacbell.net>
Subject Re: [jira] Commented: (DERBY-7) Bug in NULLIF Function
Date Mon, 27 Dec 2004 17:48:38 GMT
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');
2. nullif(1,2);
3. nullif('a', 1);
4. nullif(1, 'a');
5. nullif(CAST(NULL as Char(1)), 1)
6. nullif(1, CAST(NULL as Char(1)))

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

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

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



----- Original Message ----- 
From: "Christian d'Heureuse (JIRA)" <derby-dev@db.apache.org>
To: <derby-dev@db.apache.org>
Sent: Monday, December 27, 2004 7:07 AM
Subject: [jira] Commented: (DERBY-7) Bug in NULLIF Function


>      [
http://nagoya.apache.org/jira/browse/DERBY-7?page=comments#action_57072 ]
>
> Christian d'Heureuse commented on DERBY-7:
> ------------------------------------------
>
> I agree with the comments of Amit.
>
> The following code in ConditionalNode.bindExpression() determines the
result type of NULLIF():
>
> /*
> ** Set the result type of this conditional to be the dominant type
> ** of the result expressions.
> */
> setType(thenElseList.getDominantTypeServices());
>
> DataTypeDescriptor.getDominantType() returns the type with the higher
typePrecedence value.
> The typePrecedence values for CHAR and INT are defined in TypeId.java:
>  CHAR_PRECEDENCE = 0
>  INT_PRECEDENCE = 50
>
> So the "dominant" type between CHAR(1) and INT is INT.
> The result type of NULLIF() is set to INT.
> At the end of bindExpression(), a CAST node is inserted into
thenExpression, to convert the NULL constant from CHAR to INT.
>
> ---
>
> In ConditionalNode.bindExpression() there is the following comment:
>
> /*
> ** If it is comparable, then we are ok.  Note that we
> ** could in fact allow any expressions that are convertible()
> ** since we are going to generate a cast node, but that might
> ** be confusing to users...
> */
>
> Maybe this explains the reason, why the type compatibility check (the one
that Amit suggests to comment out) was added to bindExpression(): It "might
be confusing to users..."?
>
> To prevent this "user confusion", the term "built-in broadening
conversion" should be clarified.
> What is a "broadening conversion"? Is the return value type of NULLIF()
the "broadened type"?
> But then, why is a conversion from CHAR to INT a "broadening conversion"?
CHAR would be the "broader" type than INT. (All INT values can be converted
to CHAR, but not all CHAR values can be converted to INT.)
> Or is the return type the "narrower" type?
>
> An better solution would be to define that the result type of NULLIF() is
the "dominant" type, which is the one that comes first in the following
list:
> USER-defined, BLOB, LONGVARBIT, VARBIT, BIT, BOOLEAN, TIME, TIMESTAMP,
DATE, DOUBLE, REAL, DECIMAL, NUMERIC, LONGINT, INT, SMALLINT, TINYINT, REF,
NATIONAL_LONGVARCHAR, NATIONAL_VARCHAR, NATIONAL_CHAR, CLOB, NCLOB,
LONGVARCHAR, VARCHAR, CHAR.
> For DECIMAL/NUMERIC types, scale and precision are broadened to matche
both input types.
>
> > 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