[jira] [Updated] (DERBY-6584) Comparisons between BOOLEAN and other types sometimes succeed

["Rick Hillegas (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6584?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-6584:
---------------------------------

    Bug behavior facts: Deviation from standard

Marking this as a deviation from the Standard. A BOOLEAN can only be cast to a BOOLEAN and no other built-in datatype can be cast to a BOOLEAN. This is according to the table in part 2 of the 2011 SQL Standard, section 6.13 (cast specification), syntax rule 6. If you can't explicitly cast between two datatypes, then you can't compare them.

The Derby Reference Manual section on "Data type assignments and comparison, sorting, and ordering" says that these comparisons should be illegal.

> Comparisons between BOOLEAN and other types sometimes succeed
> -------------------------------------------------------------
>
>                 Key: DERBY-6584
>                 URL: https://issues.apache.org/jira/browse/DERBY-6584
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.10.2.0
>            Reporter: Knut Anders Hatlen
>
> I would have expected the following queries to fail because of type mismatch between CHAR and BOOLEAN:
> {noformat}
> ij> values 'abc' = true, true = 'xyz', false = 'yes';
> 1    
> -----
> true 
> true 
> false
> 3 rows selected
> ij> values 'abc' in (true, false), true in ('abc', 'def');
> 1    
> -----
> true 
> true 
> 2 rows selected
> {noformat}
> Similar queries using INTEGER values instead of CHAR values fail as expected:
> {noformat}
> ij> values 1 = true;
> ERROR 42818: Comparisons between 'INTEGER' and 'BOOLEAN' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
> ij> values true = 1;
> ERROR 42818: Comparisons between 'BOOLEAN' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
> ij> values true in (1,2,3);
> ERROR 42818: Comparisons between 'BOOLEAN' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
> ij> values 1 in (true, false);
> ERROR 42818: Comparisons between 'INTEGER' and 'BOOLEAN' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.2#6252)