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

Amit Handa 11 Jan 2005 03:30:08 -0000

Thanks Christian for the reply!


Satheesh,

> The conclusion is that the expression
>  CASE WHEN ... THEN intValue ELSE charValue END
> is non-conforming SQL and may be processed in an implementation-dependent 
manner.

I hope this clears your doubts.

Going by comments on this bug, there are two people
who have voted for the patch.

Please let me know if this patch can be accepted,
or else let's work out for a better solution.

thanks,
Amit

Christian d'Heureuse (JIRA) wrote:

[ http://issues.apache.org/jira/browse/DERBY-7?page=comments#action_57488 ] Christian d'Heureuse commented on DERBY-7: ------------------------------------------

I have a feeling we are not confirming to SQL standard

Are you referring to the syntax rule that states that all arguments "shall" be 
character strings, if any argument is character string?

"9075-2 9.3 Data types of results of aggregations
...
Syntax Rules
3. a) If any of the data types in DTS is character string, then all data types in 
DTS shall be character string, ..."

The term "shall" is defined as:

"9075-1 6.2.3.3 Terms denoting rule requirements
In the Syntax Rules, the term shall defines conditions that are required to be 
true of syntactically conforming SQL language. ...
The treatment of language that does not conform to the SQL Formats and Syntax Rules 
is implementation-dependent. ..."

The conclusion is that the expression
 CASE WHEN ... THEN intValue ELSE charValue END
is non-conforming SQL and may be processed in an implementation-dependent 
manner.

Bug in NULLIF Function
----------------------

        Key: DERBY-7
        URL: http://issues.apache.org/jira/browse/DERBY-7
    Project: Derby
       Type: Bug
 Components: SQL
   Versions: 10.0.2.0
   Reporter: Tulika Agrawal
   Priority: Minor
Attachments: Derby-7.txt

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.)"

--
Regards,

Amit Handa,
Java Web Services.
Sun Microsystems Inc.

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

Reply via email to