On 27-05-2020 16:31, Emil Totev wrote:
It seems that it is only possible to CAST() from/to boolean as
char/varchar (values 'TRUE' and 'FALSE'), and the same also applies to
the new SET BIND OF BOOLEAN TO type-to.
Is this restricted by the SQL standard? Wouldn't it also make sense to
be able to cast /set bind of booleans to any numeric type as 0 (false)
and 1 (true)? Or maybe even the often used Y/N char(1)?
Yes, SQL:2016 specifies that CAST supports the following
from character types to boolean
from boolean to boolean
from boolean to character types
For casts to boolean, it specifies:
(TD: Target Domain, SD: Source Domain, SV: Source Value, VE: Value
Expression, TV: Target Value)
"""
21) If TD is boolean, then
Case:
a) If SD is character string, then SV is replaced by
TRIM ( BOTH ' ' FROM VE )
Case:
i) If the rules for <literal> in Subclause 5.3, “<literal>”, can be
applied to SV to determine a valid value of the data type TD, then let
TV be that value.
ii) Otherwise, an exception condition is raised: data exception —
invalid character value for cast.
b) If SD is boolean, then TV is SV.
"""
It looks like Firebird might be violating this rule a little bit
(specifically 21.a.i), because CAST('unknown' TO BOOLEAN) does not
result in a null (or unknown) value. That might be my interpretation though.
For casts from boolean, it specifies (quoted from rules for
variable-length character string or large object character string, but
it is similar for character):
(MLTD: Maximum Length Target Domain)
"""
f) If SD is boolean, then
Case:
i) If SV is True and MLTD is not less than 4, then TV is 'TRUE'.
ii) If SV is False and MLTD is not less than 5, then TV is 'FALSE'.
iii) Otherwise, an exception condition is raised: data exception —
invalid character value for cast.
"""
Again, Firebird has a minor violation of the spec here by reporting a
truncation error if the MLTD is too small, instead of an equivalent of
invalid character value for cast.
Also this error message is now quite confusing:
SQL> select cast(true as smallint) from rdb$database;
CAST
=======
Statement failed, SQLSTATE = 22018
conversion error from string "BOOLEAN"
The error message is indeed wrong. at minimum I would expect "TRUE" here
(even though it isn't a string value, but reporting the datatype here
seems wrong to me). It should something like 'conversion error from
boolean', 'conversion error from boolean to smallint', or 'invalid cast
specification', or something like that.
Could you report it in the tracker. This is BTW als reproducible in
Firebird 3.
This might be a historical problem. For example, CAST(1 AS TIMESTAMP)
will result in a similar error "conversion error from string "1"", while
it is not a string, but an integer.
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
