db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Amit Handa (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-7) Bug in NULLIF Function
Date Mon, 27 Dec 2004 10:25:17 GMT
     [ http://nagoya.apache.org/jira/browse/DERBY-7?page=comments#action_57068 ]
     
Amit Handa commented on DERBY-7:
--------------------------------

Before I comment on Christian's note, we need to read what nullif definition says at link
below :- 
http://incubator.apache.org/derby/manuals/reference/sqlj66.html#IDX910

Quoting from the above link is the next one line 

For built-in types, this means that the types must be the same or a built-in broadening conversion
must exist between the types.

So if we have say a CHAR and an INT to compare in NULLIF, the CHAR('1') will have a built
in broadening and Hence get converted to INT(1)


> The expression
> values nullif(1,1)
> should return the value
> cast(null as int)

I agree with Christian here.  

> In sqlgrammar.jj there is the following comment:
> // "NULLIF(L, R)" is the same as "L=R ? CAST(NULL AS CHAR(1)) : L"

Also to note was the next line after that in the same file, which says

// An impl assumption here is that Cloudscape can promote CHAR to any comparable datatypes
such as numeric


> I think the problem is:
> thenExpression = CAST(NULL AS CHAR(1)) = type CHAR
> elseExpression = L = type INT
> ==> CHAR and INT are not compatible.

I disagree here. Rather it is this way

thenExpression = CAST(NULL AS CHAR(1)) = type CHAR
 elseExpression = L = type INT
check type of INT and CHAR, Since not compatible, 
promote CHAR to INT(built in broadening), 
compare INT and INT and do the comparison
==> return INT

Note that 
CAST(NULL as Char(1)) WIll return CHAR

BUT in below
NULLIF(CAST(NULL as CHAR(1),1)

the CAST(NULL as CHAR(1) still returns 1 as CHAR 
but gets converted to INT 
as soon as it has to be compared to an INT.



>To solve this problem, the data type of L (leftExpression) could be used to build the
NULL >constant node, instead of CHAR(1).

That is what is happenning, I hope the above helps in clarifying that
else I can explain that using code from ConditionalNode.bindExpression()







> 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