Boolean functions and operators

The Boolean constants

Function	HTSQL	SQL	Javascript	Python
The FALSE constant	false()	FALSE	false	False
The TRUE constant	true()	TRUE	true	True

Signatures:

• htsql:false() -> Boolean
• htsql:true() -> Boolean

The Boolean checkers

Function	HTSQL	SQL	Javascript	Python
Check if the value is FALSE	is_false(value)	value IS FALSE	value === false	value is False
Check if the value is TRUE	is_true(value)	value IS TRUE	value === true	value is True
Check if the value is not FALSE	!is_false(value)	value IS NOT FALSE	value !== false	value is not False
Check if the value is not TRUE	!is_true(value)	value IS NOT TRUE	value !== true	value is not True

Signatures:

• htsql:is_false(Boolean value) -> Boolean
• htsql:is_true(Boolean value) -> Boolean

A possible and rejected alternative:

• htsql:is_false(Any value) -> Boolean
• htsql:is_true(Any value) -> Boolean

In this variant, the argument is converted to a Boolean value using htsql:boolean before the check is make. However this variant is questionable since for a non-Boolean value, the same result could be obtained using the !value and boolean(value) expressions.

The functions is_false(value) and is_true(value) are equivalent to the expressions value == false() and value == true().

The following table indicates the difference between the functions is_false(value) and is_true(value) and the expressions value = false() and value = true():

Expression / Value	NULL	FALSE	TRUE
is_null(value)	TRUE	FALSE	FALSE
value = null()	NULL	NULL	NULL
is_false(value)	FALSE	TRUE	FALSE
value = false()	NULL	TRUE	FALSE
is_true(value)	FALSE	FALSE	TRUE
value = true()	NULL	FALSE	TRUE

Note that the functions is_null(value), is_false(value) and is_true(value) never produces a NULL value.

Boolean cast

Function	HTSQL	SQL	Javascript	Python
Cast to Boolean	boolean(value)	--	Boolean(value)	bool(value)

Signature: htsql:boolean(Any value) -> Boolean

A question: Should we stick to full or abbreviated function names? That is, is it boolean(value) or bool(value)?

Note that the Boolean cast is often performed implicitly.

SQL supports some form of Boolean cast, but we do not follow it.

The cast is performed according to the following rule:

• A value of the Boolean domain is passed intact, including a NULL value.
• An Untyped NULL value remains NULL, 'true' becomes true(), 'false' becomes false(), any other Untyped values are unacceptable.
• Otherwise, NULL, 0, 0.0, '', binary(''), bitstring(''), time('00:00:00'), timedelta('00:00:00'), array() are converted to FALSE.
• All the other values are converted to TRUE.

In HTSQL, the way a value is converted to Boolean depends on the value domain. The convertion is done according to the following table:

Domain	Values converted to FALSE	Values converted to TRUE	Values converted to NULL
Boolean	FALSE	TRUE	NULL
Number	NULL, 0, 0.0	all the others	none
String	NULL, ''	all the others	none
Binary	NULL, E''	all the others	none
BitString	NULL, B''	all the others	none
Date	NULL	all the others	none
Time	NULL, TIME '00:00:00'	all the others	none
DateTime	NULL	all the others	none
TimeDelta	NULL, INTERVAL '00:00:00'	all the others	none
Array	NULL, ARRAY[]	all the others	none
Composite	NULL	all the others	none
Unknown	NULL	all the others	none
Null	NULL	none	none
Untyped	'false'	'true'	NULL
Tuple	none	all	none

Note that is_null(boolean(value)) is true if and only if the domain of value is Boolean or Untyped and the value itself is NULL.

In Javascript, 0, null, false, NaN, undefined, and the empty string ("") are converted to false. All the other values are converted to true.

In Python, None, 0, False, the empty string(""), the empty list ([]) and other values that could be considered "empty" are converted to False. All the other values are converted to True.

Logical operations

Function	HTSQL	SQL	Javascript	Python
Boolean OR	value1 | value2 | ...	value1 OR value2 OR ...	value1 || value2 || ...	value1 or value2 or ...
Boolean AND	value1 & value2 & ...	value1 AND value2 AND ...	value1 && value2 && ...	value1 and value2 and ...
Boolean NOT	!value	NOT value	!value	not value

Signatures:

• htsql:operator:"|"(Any1 value1, Any2 value2, ...) -> Boolean
• htsql:operator:"&"(Any1 value1, Any2 value2, ...) -> Boolean
• htsql:operator:"!()"(Any value) -> Boolean

The arguments of a Boolean operation are converted to Boolean using the htsql:boolean cast before the operation is applied.

The operations follow the SQL rules for evaluation of Boolean operations:

value1	value2	value1 | value2	value1 & value2
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	TRUE	FALSE
TRUE	NULL	TRUE	NULL
FALSE	FALSE	FALSE	FALSE
FALSE	NULL	NULL	FALSE
NULL	NULL	NULL	NULL

and

value	!value
TRUE	FALSE
FALSE	TRUE
NULL	NULL

Note that since the Boolean cast is applied to the arguments first, the value of a Boolean operation with NULL arguments depends on the domain of the arguments.

For instance, consider two HTSQL expressions:

• !employee.is_contractor
• !employee.hourly_rate

Suppose that the domains of the is_contractor and hourly_rate fields are Boolean and Numeric respectively. Moreover, suppose that both is_contractor and hourly_rate are NULL. For these expressions, the value of the former is NULL while the value of the latter is TRUE.

According to SQL rules, NOT NULL is equal to NULL, that is why !employee.is_contractor is evaluated to NULL. However, an implicit Numeric to Boolean cast is performed for a Numeric value (!boolean(employee.hourly_rate)) which converts the NULL value to FALSE. Then NOT FALSE is evaluated to TRUE.

Equality and identity checks

Function	HTSQL	SQL	Javascript	Python
Equal	x=y	x = y	x == y	x == y
Not equal	x!=y	x != y	x != y	x != y
Identical to	x==y	x IS NOT DISTINCT FROM y	x === y	x is y
Not identical to	x!==y	x IS DISTINCT FROM y	x !== y	x is not y

Signatures:

• htsql:operator:"="(Any x, Any y) -> Boolean
• htsql:operator:"!="(Any x, Any y) -> Boolean
• htsql:operator:"=="(Any x, Any y) -> Boolean(is_nullable=False)
• htsql:operator:"!=="(Any x, Any y) -> Boolean(is_nullable=False)

Type resolution: Arguments should be either Untyped or be of the same common domain. Untyped arguments are resolved to the common type. If both arguments are Untyped, they are resolved to String.

The operators == and !== differ from = and != by the way they treat NULL values. If one of the arguments of the = and != operators is NULL, then the result is also NULL, whereas == and !== treat NULL arguments as regular values. The == and !== operations never produce a NULL value.

The following table indicates the difference:

x	y	x=y	x==y	x!=y	x!==y
null()	null()	NULL	TRUE	NULL	FALSE
null()	any non-NULL value	NULL	FALSE	NULL	TRUE

Comparison operators

Function	HTSQL	SQL	Javascript	Python
Less than	x<y	x < y	x < y	x < y
Greater than	x>y	x > y	x > y	x > y
Less than or equal to	x<=y	x <= y	x <= y	x <= y
Great than or equal to	x>=y	x >= y	x >= y	x >= y

Signatures:

• htsql:operator:"<"(Any x, Any y) -> Boolean
• htsql:operator:">"(Any x, Any y) -> Boolean
• htsql:operator:"<="(Any x, Any y) -> Boolean
• htsql:operator:">="(Any x, Any y) -> Boolean

Type resolution: Arguments should be either Untyped or be of the same common domain. Untyped arguments are resolved to the common type. If all arguments are Untyped, they are resolved to String.

Multivalue comparison

Multivalue comparison is possible for the following operations:

    =   !=  <   <=  >   >=

Any of these operations could replace the <op> placeholder in the following table.

Function	HTSQL	SQL	Javascript	Python
All the conditions are valid (enumeration)	x <op> all(y,z,...)	x <op> ALL(ARRAY[y,z,...])	--	x not in [y,z,...] when <op> is !=
All the conditions are valid (plural value)	x <op> all(plural)	x <op> ALL(SELECT plural FROM ...)	--	--
All the conditions are valid (array)	x <op> all(array)	x <op> ALL(array)	--	--
Any of the conditions is valid (enumeration)	x <op> any(y,z,...)	x <op> ANY(ARRAY[y,z,...])	--	x in [y,z,...] when <op> is =
Any of the conditions is valid (plural value)	x <op> any(plural)	x <op> ANY(SELECT plural FROM ...)	--	--
Any of the conditions is valid (array)	x <op> any(array)	x <op> ANY(array)	--	--

Signatures:

• htsql:all(Any x, [Any y, ...]) -> Any
• htsql:any(Any x, [Any y, ...]) -> Any

Type resolution: Arguments should be either Untyped or be of the same common domain. Untyped arguments are resolved to the common type. If all arguments are Untyped, they are resolved to String.

Note: The functions htsql:all() and htsql:any() could only be used as the second term of a comparison operation. In any other context, they generate an error.

Conditional expressions

Function	HTSQL	SQL	Javascript	Python
If	if(p1,v1,p2,v2,...,default)	(CASE WHEN p1 THEN v1 WHEN p2 THEN v2 ... ELSE default)	p ? v1 : v2	v1 if p else v2
Switch	switch(expr,f1,v1,f2,v2,...default)	(CASE expr WHEN f1 THEN v1 WHEN f2 THEN v2 ... ELSE default)	--	--

Signature:

• htsql:if(Boolean p1, Any v1, Boolean p2, Any v2, ...[, Any default]) -> Any
• htsql:switch(Any expr, Any f1, Any* v1, Any f2, Any* v2, ...[, Any* default) -> Any*

Type resolution: The "value" arguments should be either Untyped or be of the same common type. If all even arguments are Untyped, they are resolved to String. The same is valid for the "expression" and "form" arguments.