A data type used in CREATE TABLE and ALTER TABLE statements, representing a
single true/false choice.
Syntax:
In the column definition of a CREATE TABLE statement:
column_name BOOLEAN
Range: TRUE or FALSE. Do not use quotation marks around the
TRUE and FALSE literal values. You can write the literal values in
uppercase, lowercase, or mixed case. The values queried from a table are always returned in lowercase,
true or false.
Conversions: Impala does not automatically convert any other type to BOOLEAN. All
conversions must use an explicit call to the CAST() function.
You can use CAST() to convert
any integer or floating-point type to
BOOLEAN: a value of 0 represents false, and any non-zero value is converted
to true.
SELECT CAST(42 AS BOOLEAN) AS nonzero_int, CAST(99.44 AS BOOLEAN) AS nonzero_decimal,
CAST(000 AS BOOLEAN) AS zero_int, CAST(0.0 AS BOOLEAN) AS zero_decimal;
+-------------+-----------------+----------+--------------+
| nonzero_int | nonzero_decimal | zero_int | zero_decimal |
+-------------+-----------------+----------+--------------+
| true | true | false | false |
+-------------+-----------------+----------+--------------+
When you cast the opposite way, from BOOLEAN to a numeric type,
the result becomes either 1 or 0:
SELECT CAST(true AS INT) AS true_int, CAST(true AS DOUBLE) AS true_double,
CAST(false AS INT) AS false_int, CAST(false AS DOUBLE) AS false_double;
+----------+-------------+-----------+--------------+
| true_int | true_double | false_int | false_double |
+----------+-------------+-----------+--------------+
| 1 | 1 | 0 | 0 |
+----------+-------------+-----------+--------------+
You can cast DECIMAL values to BOOLEAN, with the same treatment of zero and
non-zero values as the other numeric types. You cannot cast a BOOLEAN to a
DECIMAL.
You cannot cast a STRING value to BOOLEAN, although you can cast a
BOOLEAN value to STRING, returning '1' for
true values and '0' for false values.
Although you can cast a TIMESTAMP to a BOOLEAN or a
BOOLEAN to a TIMESTAMP, the results are unlikely to be useful. Any non-zero
TIMESTAMP (that is, any value other than 1970-01-01 00:00:00) becomes
TRUE when converted to BOOLEAN, while 1970-01-01 00:00:00
becomes FALSE. A value of FALSE becomes 1970-01-01
00:00:00 when converted to BOOLEAN, and TRUE becomes one second
past this epoch date, that is, 1970-01-01 00:00:01.
NULL considerations: An expression of this type produces a NULL
value if any argument of the expression is NULL.
Partitioning:
Do not use a BOOLEAN column as a partition key. Although you can create such a table,
subsequent operations produce errors:
[localhost:21000] > create table truth_table (assertion string) partitioned by (truth boolean);
[localhost:21000] > insert into truth_table values ('Pigs can fly',false);
ERROR: AnalysisException: INSERT into table with BOOLEAN partition column (truth) is not supported: partitioning.truth_table
Examples:
SELECT 1 < 2;
SELECT 2 = 5;
SELECT 100 < NULL, 100 > NULL;
CREATE TABLE assertions (claim STRING, really BOOLEAN);
INSERT INTO assertions VALUES
("1 is less than 2", 1 < 2),
("2 is the same as 5", 2 = 5),
("Grass is green", true),
("The moon is made of green cheese", false);
SELECT claim FROM assertions WHERE really = TRUE;
HBase considerations: This data type is fully compatible with HBase tables.
Parquet considerations: This type is fully compatible with Parquet tables.
Text table considerations: Values of this type are potentially larger in text tables than in tables using Parquet or other binary formats.
Column statistics considerations: Because this type has a fixed size, the maximum
and average size fields are always filled in for column statistics, even before you run
the COMPUTE STATS statement.
Kudu considerations:
Currently, the data types BOOLEAN, FLOAT, and
DOUBLE cannot be used for primary key columns in Kudu tables.
Related information: Boolean Literals, SQL Operators, Impala Conditional Functions