Allow casting of integers 0/1 to new boolean data type false/true [CORE3849]

[firebird-automations]

Submitted by: lacak (lacak)

Votes: 1

AFAIK current implementation allows casting of character strings 'false' / 'true' to boolean values false / true. (it is compliant with sql standard)

It will be nice if there will be supported also casting from 0 / 1 to false / true like it is supported in other RDBMSs (it will increase cross database portability and compatibility with others RDBMSs where exists native BOOLEAN data type or where is "emulated" using other data types like MSSQL: BIT (0/1) or MySQL:BOOL (synonym for TINYINT))

Here are some examples why 0/1 :

PostgreSQL: http://www.postgresql.org/docs/9.0/interactive/datatype-boolean.html

Ingres: "Boolean columns accept as input the literal values FALSE and TRUE, 0 and 1 (which correspond to false and true, respectively), and the strings 'FALSE' and 'TRUE'." ... "CAST(integer AS BOOLEAN) is accepted for values 0 and 1."

MySQL: "However, the values TRUE and FALSE are merely aliases for 1 and 0" http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

From sql standard: --------------------------- "In comparisons of boolean values, True is greater than False"

(from subclause 13.4 Calls to an <externally-invoked procedure>) "If the caller language of EP is C, then a reference to PN that assigns the value False to PN implicitly assigns the value 0 (zero) to PI; a reference to PN that assigns the value True implicitly assigns the value 1 (one) to PI."

From programing languages world: ------------------------------------------------- in C99 standard (ISO/IEC 9899) is _Bool : "An object declared as type _Bool is large enough to store the values 0 and 1." "true which expands to the integer constant 1, false which expands to the integer constant 0"

in C++ (ISO/IEC 14882) is Bool: "An rvalue of type bool can be converted to an rvalue of type int, with false becoming zero and true becoming one." "If the source type is bool, the value false is converted to zero and the value true is converted to one."

in Pascal (ISO/IEC 10206:1990): "The ordinal numbers of the truth values denoted by false and true shall be the integer values 0 and 1 respectively."

[firebird-automations]

Commented by: @AlexPeshkoff

It could be good idea to discuss this in devel before adding this issue. I remember that the boolean type was discussed, and it was decided not to have automatic conversions from integers to boolean. Please review devel archive.

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Alex, as I read this, the post does not refer to automatic casting of 0/1 to FALSE/TRUE.

Rather the issue is about allowing operations like CAST( Integer AS BOOLEAN) where integer = 0 would result in FALSE and all non-zero values = TRUE.

[firebird-automations]

Commented by: lacak (lacak)

My request is also about implicit casting (because of portability of SQL commands like "INSERT INTO tab (boolean_column) VALUES(0)" where I can not use "... VALUES (CAST(0 AS BOOLEAN))" in databases which does not have BOOLEAN data type)

[firebird-automations]

Commented by: @AlexPeshkoff

According to SQL specification only character string True and False may be converted to boolean, even explicit(!!!) casting of numerics is disabled.I suppose that we may sligtly violate standard when doing explicit casting (on the other hand, "INSERT INTO tab (boolean_column) VALUES(integerValue != 0)" will work fine). But automatic conversion of datatypes is bad - you can easily get unwanted side effects - at least I've seen them in C/C++.

BTW, your problem with CAST(0 AS BOOLEAN) has trivial solution - just create domain boolean.

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

@Lacak,

As Alex pointed out, implicit casting has already been discussed and rejected by the development list.

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

@Alex,

How does creating a boolean domain solve the problem with casting 0/1 to boolean?

[firebird-automations]

Commented by: @AlexPeshkoff

Sean, this helps to have same syntax for both kinds of databases: supporting native boolean type (provided explicit cast (int as boolean) works) or not. Where you want to cast integer to boolean, you do:

create domain boolean as smallint; -- just in case when native boolean is not supported

-- this will work no matter supported native boolean or not create table tb (b boolean); insert into tb values(cast(0 as boolean));

[firebird-automations]

Commented by: lacak (lacak)

Only comment: Now I noticed, that Interbase also allows 0/1 for boolean columns. I know, that SQL standard does not define such casting, but it seems, that in practical world other databases supports it.