SuperDB

Comparisons

Comparison expressions follow customary syntax and semantics and result in a truth value of type bool or an error.

The binary comparison operators have the form

<expr> <op> <expr>

where <op> is one of

• < for less than,
• <= for less than or equal,
• > for greater than
• >= for greater than or equal,
• == or = for equal,
• != or <> for not equal, or
• like or not like for the SQL string pattern matching.

The between comparator has the form

<expr> between <lower> and <upper>

where <expr>, <lower>, and <upper> are any expressions that result in an orderable type and are type compatible. This is shorthand for

<expr> >= <lower> and <expr> <= <upper>

The null comparators have the form

<expr> is null

and is true if <expr> is a null value of any type. Likewise,

<expr> is not null

if true if <expr> is not a null value of any type. As with SQL, any comparison of a null value to any other value is a null value of type bool, i.e., null::bool. This is because comparing an unknown value with any other value has an unknown result.

The like comparator has the form

<expr> like <pattern>

where <expr> is any expression that produces a string type and <pattern> is a constant expression that results in a string type.

Note

Currently, <pattern> must be a constant value and cannot depend on the input. Also, the ilike operator for case-insensitive matching is not yet supported. These capabilities will be included in a future version of SuperSQL.

The like comparator is true if the <pattern> matches <expr> where pattern consists of literal characters, _ for matching any single letter, and % for matching any sequence of characters.

The not like comparator has the form

<expr> not like <pattern>

and is true when the pattern does not match the expression.

String values are compared via byte order in accordance with C/POSIX collation as found in other SQL databases such as Postgres.

Note

SuperSQL does net yet support SQL COLLATE keyword and variations.

When the operands are coercible to like types, the result is the truth value of the comparison. Otherwise, the result is false. To compare values of different types, consider the compare function.

If either operand to a comparison is error("missing"), then the result is error("missing").

Examples

Various scalar comparisons

# spq
values 1 > 2, 1 < 2, "b" > "a", 1 > "a", 1 > x
# input

# expected output
false
true
true
false
error("missing")

Null comparisons

# spq
values {isNull:this is null,isNotNull:this is not null}
# input
1
null
2
error("missing")
# expected output
{isNull:false,isNotNull:true}
{isNull:true,isNotNull:false}
{isNull:false,isNotNull:true}
{isNull:error("missing"),isNotNull:error("missing")}

Comparisons using a like pattern

# spq
values f"{this} like '%abc_e': {this like '%abc_e'}"
# input
"abcde"
"xabcde"
"abcdex"
"abcdd"
null
error("missing")
# expected output
"abcde like '%abc_e': true"
"xabcde like '%abc_e': true"
"abcdex like '%abc_e': false"
"abcdd like '%abc_e': false"
null::string
error("missing")