[ 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
