Comparison Operations
The following operations can take operands with multiple data types but always return a Boolean value (sometimes nullable):
| Operation | Definition | Observation |
|---|---|---|
= | equality test | rejected for ROW values |
<> | inequality test | rejected for ROW values |
!= | inequality test, same as above | rejected for ROW values |
> | greater than | |
< | less than | |
>= | greater or equal | |
<= | less or equal | |
IS NULL | true if operand is NULL | |
IS NOT NULL | true if operand is not NULL | |
<=> | equality check that treats NULL values as equal | result is not nullable |
IS DISTINCT FROM | check if two values are not equal, treating NULL as equal | result is not nullable |
IS NOT DISTINCT FROM | check if two values are the same, treating NULL values as equal | result is not nullable |
BETWEEN [ASYMMETRIC] ... AND ... | x BETWEEN a AND b is the same as a <= x AND x <= b | inclusive at both endpoints |
NOT BETWEEN [ASYMMETRIC] ... AND ... | The NOT of the previous operator | not inclusive at either endpoint |
BETWEEN SYMMETRIC ... AND ... | x BETWEEN SYMMETRIC a AND b is the same as (a <= x AND x <= b) OR (b <= x AND x <= a) | inclusive at both endpoints; order of endpoints does not matter |
NOT BETWEEN SYMMETRIC ... AND ... | The NOT of the previous operator | not inclusive at either endpoint |
... [NOT] IN ... | checks whether value appears/does not appear in a list or set | |
EXISTS query | check whether query results have at least one row |
Note that the SQL standard mandates IS NULL to return true for a
ROW object where all fields are NULL (similarly, IS NOT NULL is
required to return false). Our compiler diverges from the standard,
returning false for ROW(null) is null.
Comparing complex values
Comparison operations (=, <>, !=, <, >, <=, >=, <=>,
IS NULL, IS NOT NULL) are defined on all data types, even generic
and recursive data types (including ARRAY, MAP, ROW, VARIANT,
user-defined types). The one exception is that =, <>, and !=
are rejected on ROW values.
Equality needs no notion of order. Two ARRAY values are equal when
they have the same length and equal elements at each index; two MAP
values are equal when they have exactly the same keys, each with an
equal value.
The ordering comparisons <, <=, > and >= do need one, and they
are lexicographic on the structure of the type: the two values are
walked in parallel, and the result is decided by the first position
where they differ. Which positions are walked, and in what order,
depends on the type:
- the fields of a
ROW, in the order they are declared; - the elements of an
ARRAY, by increasing index; - the entries of a
MAP, by increasing key; each entry contributes its key first and then its value. A map has no order of its own, so the order in which a literal writes its entries never matters:MAP['b', 1, 'a', 2]andMAP['a', 2, 'b', 1]are the same value.
A NULL is smaller than any other value at the position where it
occurs, whatever the NULLS FIRST or NULLS LAST clause of an
enclosing ORDER BY says; that clause orders the rows, not the
insides of a value. For example, ARRAY[NULL] < ARRAY[1] is true.
Map keys are never NULL, so this affects fields, array elements, and
map values.
Comparing ARRAY and MAP values
For ARRAY and MAP values = is equivalent to IS NOT DISTINCT FROM, and <> is equivalent to IS DISTINCT FROM: two NULL
elements compare as equal instead of producing NULL. For example,
ARRAY[1, NULL] = ARRAY[1, NULL] is true, whereas the three-valued
logic of the SQL standard would give NULL. Comparing two non-NULL
arrays or maps therefore never produces NULL.
The equivalence covers the elements, not the operands themselves.
When one operand is a NULL array or a NULL map, = produces
NULL as any other comparison does, while IS NOT DISTINCT FROM
produces false.
Comparing ROW values
Feldera rejects =, <>, and != between ROW values.
The reason is that the standard meaning of these operators on ROW
values surprises most users. The standard compares the fields
pairwise under three-valued logic: the result is false as soon as
one pair of fields differs, true when every pair is equal, and
NULL otherwise. A NULL field therefore does not simply make the
whole comparison NULL:
| Comparison | Standard result | Why |
|---|---|---|
ROW(1, NULL) = ROW(2, NULL) | false | the first fields differ, so the rest does not matter |
ROW(1, NULL) = ROW(1, NULL) | NULL | the first fields are equal, the second pair is unknown |
ROW(1, NULL) <> ROW(2, NULL) | true | one pair differs, so the two rows are known to differ |
Two different values thus compare as false, while two identical ones
compare as NULL: a ROW value is not equal to itself once any field
is NULL. A query that gets this wrong silently drops or keeps the
wrong rows, because WHERE treats NULL as false. Rejecting the
comparison forces the choice to be explicit.
Write IS NOT DISTINCT FROM in place of =, and IS DISTINCT FROM in place of <> and !=. Both treat
NULL values as equal and always produce a Boolean, never NULL.
<=> is a shorthand for IS NOT DISTINCT FROM, and is
accepted as well:
CREATE TYPE point AS (x INT, y INT);
CREATE TABLE T(p point, q point);
-- Rejected:
-- CREATE VIEW v AS SELECT p = q, p <> q FROM T;
CREATE VIEW v AS SELECT p IS NOT DISTINCT FROM q, p IS DISTINCT FROM q FROM T;
CREATE VIEW w AS SELECT p <=> q FROM T;
A user-defined type declared with CREATE TYPE ... AS (...) is a
ROW type (see user-defined types), so
the restriction applies to values of such types as well.
A program can request ROW equality without writing =. The
compiler rejects each of the following forms; the last column gives
the accepted rewrite:
| Rejected | Why | Write instead |
|---|---|---|
r = s | explicit equality | r IS NOT DISTINCT FROM s, or r <=> s |
r <> s, r != s | explicit inequality | r IS DISTINCT FROM s |
l JOIN r ON l.p = r.p | the join condition is an equality test | l JOIN r ON l.p IS NOT DISTINCT FROM r.p |
l NATURAL JOIN r, l JOIN r USING (p) | equality on the shared ROW columns is implied | an explicit ON ... IS NOT DISTINCT FROM ... condition |
r IN (v1, v2) | expands to r = v1 OR r = v2 | r IS NOT DISTINCT FROM v1 OR r IS NOT DISTINCT FROM v2 |
r IN (SELECT p FROM s) | expands to an equality test | EXISTS (SELECT 1 FROM s WHERE s.p IS NOT DISTINCT FROM r) |
CASE r WHEN v THEN a ELSE b END | expands to r = v | CASE WHEN r IS NOT DISTINCT FROM v THEN a ELSE b END |
NULLIF(r, v) | returns NULL when r = v | CASE WHEN r IS NOT DISTINCT FROM v THEN NULL ELSE r END |
(a, b) = (c, d), (a, b) <> (c, d) | a row constructor builds a ROW value | (a, b) IS [NOT] DISTINCT FROM (c, d) |
A join on ROW values matches two rows when their fields are pairwise
not distinct, so two NULL fields match, and so do two NULL rows:
CREATE TYPE point AS (x INT, y INT);
CREATE TABLE l(p point);
CREATE TABLE r(p point);
-- Rejected: SELECT * FROM l JOIN r ON l.p = r.p;
CREATE VIEW v AS SELECT * FROM l JOIN r ON l.p IS NOT DISTINCT FROM r.p;
An IN subquery becomes a join. SQL has no IS NOT DISTINCT FROM ANY, so rewrite the subquery as a correlated EXISTS:
-- Rejected: SELECT * FROM l WHERE l.p IN (SELECT p FROM r);
CREATE VIEW w AS SELECT * FROM l
WHERE EXISTS (SELECT 1 FROM r WHERE r.p IS NOT DISTINCT FROM l.p);
The following remain legal on ROW values:
- the ordering comparisons
<,<=,>,>=; IS [NOT] DISTINCT FROM,<=>, andIS [NOT] NULL;- constructs that group values rather than compare them, because they
use distinctness and not equality:
GROUP BY,SELECT DISTINCT,PARTITION BY,UNION,INTERSECT, andEXCEPT.
The restriction covers a comparison written directly between two row
constructors, such as (a, b) = (c, d): a row constructor builds a
ROW value like any other. Compare the fields, a = c AND b = d, if
that is what you mean.