[SOLVED] IS TRUE operator

IS TRUE operator

How strict is the IS TRUE SQL operator supposed to be? For example, BigQuery only allows an actual BOOL value, whereas every other dialect I've seen allows numerics as well, for example mysql or postgres allows select '1' is true, though perhaps that's just for historical reasons because many databases don't include an actual BOOL data type.

What then should the IS TRUE operator allow as the operand?

Solution

Strictly by the SQL-99 standard, IS may compare only to TRUE, FALSE, or UNKNOWN. Read https://sql-99.readthedocs.io/en/latest/chapters/09.html for details.

Any vendor's implementation may diverge from the standard. In MySQL for example, true is just an alias for the integer 1, and false is an alias for the integer 0.

mysql> select true is true;
+--------------+
| true is true |
+--------------+
|            1 |
+--------------+
1 row in set (0.05 sec)

mysql> select 1 is true;
+-----------+
| 1 is true |
+-----------+
|         1 |
+-----------+
1 row in set (0.03 sec)

mysql> select '1' is true;
+-------------+
| '1' is true |
+-------------+
|           1 |
+-------------+
1 row in set (0.03 sec)

SQLite also permits some flexibility in data types.

sqlite> select true is true;
1
sqlite> select 1 is true;
1
sqlite> select '1' is true;
1

PostgreSQL is in between. A string '1' is treated as true, but the integer 1 is not a valid operand:

postgres=# select true is true;
 ?column? 
----------
 t
(1 row)

postgres=# select '1' is true;
 ?column? 
----------
 t
(1 row)

postgres=# select 1 is true;
ERROR:  argument of IS TRUE must be type boolean, not type integer
LINE 1: select 1 is true;
               ^

SQL implementations always have their own idiosyncrasies. You need to study the documentation for the respective brand you use, and even the exact version.