[
https://issues.apache.org/jira/browse/DERBY-6584?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14008801#comment-14008801
]
Knut Anders Hatlen commented on DERBY-6584:
-------------------------------------------
DataTypeDescriptor.comparable() has this comment:
{code}
//Booleans are comparable to Boolean, string, and to
//comparable user types. As part of the work on DERYB-887,
//I removed the comparability of booleans to numerics; I don't
//understand the previous statement about comparable user types.
//I suspect that is wrong and should be addressed when we
//re-enable UDTs (see DERBY-651).
if (typeId.isBooleanTypeId())
return
(compareWithTypeID.getSQLTypeName().equals(typeId.getSQLTypeName()) ||
compareWithTypeID.isStringTypeId());
{code}
So at least it looks like allowing comparisons between boolean and string was
intentional.
The actual conversion from string to boolean is done by SQLChar.getBoolean(),
which has this comment:
{code}
/**
* Get Boolean from a SQLChar.
*
* <p>
* Return false for only "0" or "false" for false. No case insensitivity.
* Everything else is true.
* <p>
* The above matches JCC and the client driver.
{code}
The conversion rules that are used here, are different from the ones used in
explicit casts from string to boolean. So you get results like these:
{noformat}
ij> select x, x=true from (values 'true', 'false', cast(null as varchar(10)),
'unknown', '0', '1', 'abc') v(x);
X |2
----------------
true |true
false |false
NULL |NULL
unknown |true
0 |false
1 |true
abc |true
7 rows selected
{noformat}
If the conversion rules of explicit casts had been used, the above comparison
would have returned true for x='true', false for x='false', NULL for
x='unknown' and for x=NULL, and would have raised an error for the other values.
In some cases, the compiler inserts implicit casts to boolean, and in those
cases the other conversion rules do come into play:
{noformat}
ij> select x, case when true then x else false end from (values 'true',
'false', cast(null as varchar(10)), 'unknown', '0', '1', 'abc') v(x);
X |2
----------------
true |true
false |false
NULL |NULL
unknown |NULL
ERROR 22018: Invalid character string format for type BOOLEAN.
{noformat}
If we want consistency, I think we have two options:
- Make the conversions the same in both cases (I guess we would use the
conversion rules of explicit casts if we go for this option)
- Forbid these automatic conversions from string to boolean
I think forbidding them would be the safest option, since that would mean that
existing applications that depend on either of the current automatic
conversions, would fail at compile-time instead of giving new, unexpected
results at run-time.
In either case, I think we'd need a release note to warn users who upgrade.
> 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)
