SQL BOOLEAN type
From Ingres Community Wiki
This is an Ingres 10.0 DBMS project to implement the SQL predefined data type named BOOLEAN.
ISO/IEC 9075-2:2003 Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation)
From section 4.5 "Boolean types":
"The data type boolean comprises the distinct truth values True and False. Unless prohibited by a NOT NULL constraint, the boolean data type also supports the truth value Unknown as the null value. This specification does not make a distinction between the null value of the boolean data type and the truth value Unknown that is the result of an SQL <predicate>, <search condition>, or <boolean value expression>; they may be used interchangeably to mean exactly the same thing."
Section 4.5 also describes comparison and assignment of booleans and operations involving booleans.
MySQL implements BOOLEAN (and BOOL) as "synonyms for TINYINT(1) with non-zero values equating to true. It also implements the boolean constants TRUE and FALSE as "aliases for 1 and 0."
PostgreSQL implements a BOOLEAN type as well as the the constants TRUE and FALSE. For input or assignment, either the unquoted constant keywords or character strings such as 'y', 'true', 'no', or '0' can be used. The canonical output is unquoted 't' or 'f'.
Oracle implements BOOLEAN for variables in PL/SQL, but does not support the type for table column definition.
SQL Server and IBM DB2 do not support BOOLEAN.
The iicommon.h header defines DB_BOO_TYPE "boolean" and describes it as follows:
"Datatype ID 1 is used for the internal "boolean" type. This is only used as the result type of the comparison function instances, when executed via the adf_func() call."
The project aims to extend the use of the internal type so that it can be used in CREATE TABLE statements, database procedures and other appropriate contexts. For example,
CREATE TABLE example (column1 BOOLEAN NOT NULL); CREATE PROCEDURE example_proc (flag BOOLEAN NOT NULL) AS DECLARE var1 BOOLEAN; BEGIN ... END;
In addition, the keywords TRUE and FALSE will be implemented so that they can be used in various SQL contexts and the parser will understand the boolean nature of a BOOLEAN column or variable. For example,
INSERT INTO example VALUES (FALSE); UPDATE example SET column1 = TRUE; SELECT * FROM example WHERE column1 IS TRUE; ... var1 = TRUE; WHILE var1 IS NOT FALSE ....
Connectivity Driver Implementations
- Information regarding the ODBC implementation is available here.
- Information regarding the .NET data provider implementation is available here.
- Information regarding the PHP driver implementation is available here
Also supported with OpenAPI & JDBC.
Ingres Enhancement Number
Issue 132541 requests implementation of "Search condition boolean and boolean data types."
Issue 133014 requests implementation of a BOOLEAN data type to ease migrations to Ingres.
DDS Review Summary
- The strings 'FALSE' and 'TRUE' will be accepted for input into a BOOLEAN column, in addition to the SQL literals FALSE and TRUE.
- The unquoted strings FALSE and TRUE will be shown in the SQL Terminal Monitor output for a BOOLEAN column.
- CAST(integer AS BOOLEAN) will be accepted for values 0 and 1. No other non-standard coercions will be supported.
- UNKNOWN will only be supported as part of IS UNKNOWN or IS NOT UNKNOWN operators.
- Product Management will be asked to decide whether support for BOOLEAN will be extended to the character-based front-ends.
- Other items discussed:
- ORDER BY boolean will result in grouping rows such that FALSE will come first, TRUE second and NULL last.
- A pre-10.0 client will get an error if it tries to access a 10.0+ server result that includes a BOOLEAN column.
- The various drivers developed or supported by Ingres Corp. (PHP, Python, ODBC, etc.) will be updated to support BOOLEAN. We cannot guarantee the same for third-party drivers such as Perl.
This will require testing through SQL Terminal Monitor, embedded SQL, OpenAPI, JDBC, .NET, and ODBC.
BOOLEAN Data Type
BOOLEAN can be used as a data type when defining a column in a table or a variable in a database procedure.
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'.
IS UNKNOWN is a synonym for IS NULL when dealing with Boolean values.
The input is not case sensitive.
Terminal Monitor output for a BOOLEAN column shows the unquoted strings FALSE and TRUE.
ORDER BY BOOLEAN results in grouping rows in this order: FALSE, TRUE, NULL.
CASE expressions can be used with BOOLEAN columns or literals. For example:
CASE expr WHEN cond1 THEN expr2 and CASE WHEN search_cond1 THEN expr1
accept FALSE or TRUE in condN or search_condN or part thereof, and exprN can include BOOLEAN columns or literals.
The CAST function supports casting BOOLEAN to and from character types and from the integer values 0 and 1. For example:
- CAST (BOOLEAN AS character_type) is allowed.
- CAST(character_type AS BOOLEAN) is accepted if the character type is the string 'FALSE' or 'TRUE', regardless of case.
- CAST(integer AS BOOLEAN) is accepted for values 0 and 1.
- CAST(integer_type AS BOOLEAN) is accepted if the integer type has the value 0 or 1.
For casting to strings, the data type must be of sufficient length (for example, CHAR(5) for FALSE) or silent truncation occurs (unless the string_truncation=fail is used at connect time). The shortcut CHAR(expr) returns a single character (that is, 'F' or 'T') because it is interpreted as CAST(expr AS CHAR(1)).
Internally, the BOOLEAN type is stored as a single-byte integer that can take only the values 0 and 1.
Ingres Star, Ingres Replicator, and OpenAPI support the BOOLEAN data type.
The BOOLEAN data type is supported by Ingres connectivity drivers, including ODBC, JDBC, .NET Data Provider, PHP, and Python.
Note: A pre-10.0 client will get an error if it tries to access a 10.0 or higher server result that includes a BOOLEAN column.
This feature adds to or changes the syntax of many statements, including:
- ALTER TABLE
- COPY TABLE
- CREATE INTEGRITY
- CREATE TABLE
- CREATE TABLE…AS SELECT
- DECLARE GLOBAL TEMPORARY TABLE
- INSERT INTO
- REGISTER TABLE
- WHERE clause of SELECT, DELETE, UPDATE
- JOIN source ON search_condition
The CREATE INDEX statement allows an index to be created on BOOLEAN columns.
Support of the BOOLEAN data type helps migrations from other database products.
Here are examples of using the BOOLEAN data type when creating a table or procedure:
CREATE TABLE example (column1 BOOLEAN NOT NULL);
CREATE PROCEDURE example_proc (flag BOOLEAN NOT NULL) AS DECLARE var1 BOOLEAN; BEGIN ... END;
Here is an example of using the literals FALSE and TRUE in an SQL context:
INSERT INTO example VALUES (FALSE); UPDATE example SET column1 = TRUE; SELECT * FROM example WHERE column1 IS TRUE; ... var1 = TRUE; WHILE var1 IS NOT FALSE ...
Connectivity Guide Updates
The Ingres Connectivity Guide for Ingres 10, chapter "Understanding ODBC Connectivity" will be updated to reflect support for the Boolean data type.
The Ingres ODBC driver supports SQL_C_BIT and SQL_BIT for BOOLEAN data types. Use unsigned char (UCHAR) to define Boolean fields. Also acceptable are char, CHAR, or SCHAR.
The Ingres Connectivity Guide for Ingres 10, chapter "Understanding .NET Data Provider Connectivity" will be updated to add the IngresType.IngresDate enumeration value to the Data Types Mapping Table.
The following information will be added to the Data Types Mapping Table:
|IngresType||IngresData Type||Description||.NET Data Type|
|Boolean||boolean||Boolean values of true and false||Boolean|