HTSQL Functions and Operators

  1. HTSQL Functions and Operators
    1. Preliminaries
      1. Domains
      2. Operators
    2. List of HTSQL Macros, Functions and Operators
      1. NULL-related functions and operators
      2. Boolean functions and operators

Preliminaries

We need to decide on the names and signatures of the HTSQL functions. Here is the list of HTSQL functions and operators together with their SQL/PostgreSQL definitions. We will also give their counterparts in other languages, like Python and Javascript.

There are a number of requirements for HTSQL functions:

  • the name and the signature of a function should be plain and clear. SQL functions rarely possess these qualities, that's why we should also take a look to similar functions in Python and Javascript land.
  • a function or an operator must be implementable in SQL with all the function arguments being evaluated only once.

Domains

In HTSQL, types are called domains.

An HTSQL function accepts some (maybe none) arguments of specific domains and produces a value of another domain.

There is also another kind of functions, called macros, which transform the syntax tree. The difference between ordinary functions and macros is that a function evaluates the syntax nodes passed as arguments into code objects first, then processes the code objects. Each code object has a domain. A macro does not convert the arguments into code objects, it operates on the syntax tree directly. The effect of this is that it does not make sense to talk about a macro argument domain.

The following domains are defined in HTSQL:

domains.Boolean
represents SQL's BOOLEAN.
domains.Numeric
represents SQL's INTEGER, DECIMAL and FLOAT.
domains.String
represents SQL's CHAR, VARCHAR and TEXT.
domains.Binary
represents SQL's BLOB.
domains.BitString
represents SQL's BIT and VARBIT.
domains.Date
represents SQL's DATE.
domains.Time
represents SQL's TIME.
domains.DateTime
represents SQL's TIMESTAMP.
domains.TimeDelta
represents SQL's INTERVAL.
domains.Array
represents SQL's ARRAY.
domains.Composite
represents SQL composite data types.
domains.Unknown
represents any other SQL data type, which HTSQL does not support directly.
domains.Null
the result domain of the HTSQL function null(). A column domain could not be Null.
domains.Tuple
represents the type of multicolumn values, such as those produced by SELECT statements and by HTSQL's select() commands. It is also the domain of the codes.Table objects.

The names of the domains mostly borrowed from Python.

Operators

The following operators are defined in HTSQL: 

    &       |       !
    =       ==      !=      !==
    <       <=      >       >=
    ~       ~=      !~      !~=
    +       -       *       /
    div     mod

In HTSQL, operators are functions defined in the htsql:operator namespace. That is, 2+2 is equivalent to htsql:operator:"+"(2,2). Ordinary functions and macros are defined in the htsql namespace.

List of HTSQL Macros, Functions and Operators

NULL-related functions and operators

Function HTSQL SQL Javascript Python
The NULL constant null() value is NULL null None

Signature: htsql:null() -> Null

In Javascript, there are two constants of null-ish kind: null and undefined.

Function HTSQL SQL Javascript Python
Check if the value is NULL is_null(value) value IS NULL value === null value is None

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

There are several alternative names for the HTSQL function:

  • is_null(value)
  • isnull(value)
  • value=null() (it's not clear if it could work)

The SQL expression value IS NOT NULL is transformed to HTSQL as !is_null(value).

Note that in Javascript, value == null is also true if value is undefined.

Function HTSQL SQL Javascript Python
Replace a NULL value ??? COALESCE(value, null_replacement) -- --

Does this function belong to HTSQL? If so, should be retain the name COALESCE (it sounds a bit odd)?

Function HTSQL SQL Javascript Python
Return the first non-NULL argument ??? COALESCE(value1, value2, ...) -- --

The same question: do we leave this function in HTSQL and under what name?

Boolean functions and operators

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
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

Signatures:

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

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.

The SQL expressions value IS NOT FALSE and value IS NOT TRUE are converted to HTSQL as !is_false(value) and !is_true(value).

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

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

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.
  • Otherwise, NULL, 0, 0.0, '', binary(''), bitstring(''), 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
Numeric 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 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

Note that is_null(boolean(value)) is true if and only if the domain of value is Boolean 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.

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.