Comparison Operations

The following operations can take operands with multiple data types but always return a Boolean value (sometimes nullable):

Comparison Operations

Operation	Definition	Observation
=	equality test	rejected for ROW values; warns for floating point values
<>	inequality test	rejected for ROW values; warns for floating point values
!=	inequality test, same as above	rejected for ROW values; warns for floating point values
>	greater than	rejected for ROW values
<	less than	rejected for ROW values
>=	greater or equal	rejected for ROW values
<=	less or equal	rejected for ROW values
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; warns for floating point values
IS DISTINCT FROM	check if two values are not equal, treating NULL as equal	result is not nullable; warns for floating point values
IS NOT DISTINCT FROM	check if two values are the same, treating NULL values as equal	result is not nullable; warns for floating point values
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 floating point values

Floating point arithmetic rounds its results, so two computations of the same quantity can produce REAL or DOUBLE values that differ in their last bits: 0.1e0 + 0.2e0 = 0.3e0 is false. Testing floating point values for equality, or grouping by such columns, gives results that depend on how the values were computed rather than on the quantities they represent.

The compiler therefore emits the warning Floating point equality when one of the constructs listed below compares floating point values for equality, including values nested inside ROW, ARRAY, and MAP values:

Construct	What is compared
=, <>, !=, <=>, IS [NOT] DISTINCT FROM, NULLIF	the two operands
IN, NOT IN, CASE x WHEN v	the value and each candidate
GROUP BY, PARTITION BY	the grouping keys
DISTINCT, UNION, INTERSECT [ALL], EXCEPT [ALL]	whole rows
COUNT(DISTINCT x) and other DISTINCT aggregates, MODE	the aggregated values
NATURAL JOIN, JOIN ... USING	the shared columns
RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST	the ORDER BY keys, to detect ties
PIVOT	the FOR columns and the pivot values
ARRAY_CONTAINS, ARRAY_POSITION, ARRAY_REMOVE, ARRAY_DISTINCT, ARRAY_EXCEPT, ARRAY_UNION, ARRAY_INTERSECT, ARRAYS_OVERLAP	the array elements
map[key], MAP_CONTAINS_KEY	the map keys

The analysis inspects declared types only, so it does not report a VARIANT value that holds a floating point number at run time.

To avoid the problem, store quantities that are compared or grouped as DECIMAL values, or convert them before comparing: ROUND(x, 2) = ROUND(y, 2) still compares floating point values, whereas CAST(x AS DECIMAL(10, 2)) = CAST(y AS DECIMAL(10, 2)) compares exact values. A comparison with a tolerance, such as ABS(x - y) < 1e-9, is an ordering comparison and produces no warning either.

The statement SET FELDERA_IGNORE_WARNING_FLOATING_POINT_EQUALITY = ON silences the warning for the whole program; see the compiler options.

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 exceptions are that =, <>, !=, <, >, <=, and >= are rejected on ROW values, and that <, >, <=, and >= are rejected on ARRAY and MAP values that contain 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] and MAP['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.

ORDER BY, including the ORDER BY of a window, and SORT_ARRAY sort values in the same order, and MIN, MAX, ARG_MIN, ARG_MAX, ARRAY_MIN, and ARRAY_MAX choose values in the same order. These accept ROW values: for example, MIN over ROW(1, NULL) and ROW(1, 2) is ROW(1, NULL).

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.

Elements, keys, and values that are ROW values are compared using IS NOT DISTINCT FROM as well, so =, <>, and != are accepted on arrays and maps that contain ROW values. The ordering comparisons are rejected on ROW values, so <, <=, >, >=, BETWEEN, GREATEST, LEAST, GREATEST_IGNORE_NULLS, and LEAST_IGNORE_NULLS are also rejected on arrays and maps that contain ROW values at any depth, such as values of type ROW(x INT) ARRAY, ROW(x INT) ARRAY ARRAY, MAP<VARCHAR, ROW(x INT)>, or MAP<ROW(x INT), INT>.

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 under three-valued logic. For = and <>, the fields are compared pairwise: = is false as soon as one pair of fields differs, true when every pair is equal, and NULL otherwise. For <, <=, >, and >=, the fields are compared in order, and the first pair that is not known to be equal decides the result; when that pair contains a NULL, the result is NULL. 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
ROW(1, NULL) < ROW(2, NULL)	true	the first fields decide
ROW(1, NULL) < ROW(1, 2)	NULL	the first fields are equal, the second pair is unknown
ROW(1, NULL) <= ROW(1, NULL)	NULL	the first fields are equal, the second pair is unknown

Two different values thus compare as false, while two identical ones compare as NULL: a ROW value is not equal to itself, and not <= 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;

No operator replaces <, <=, >, and >=; compare the fields instead:

CREATE TYPE point AS (x INT, y INT);
CREATE TABLE T(p point, q point);
-- Rejected:
--   CREATE VIEW v AS SELECT p < q FROM T;
CREATE VIEW v AS SELECT T.p.x < T.q.x OR (T.p.x = T.q.x AND T.p.y < T.q.y) 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 also compare ROW values without writing one of these operators. 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
r < s, r <= s, r > s, r >= s	explicit ordering comparison	compare the fields: r.x < s.x OR (r.x = s.x AND r.y < s.y)
r BETWEEN a AND b	expands to r >= a AND r <= b	compare the fields of r with those of a and b
GREATEST(r, s), LEAST(r, s), GREATEST_IGNORE_NULLS(r, s), LEAST_IGNORE_NULLS(r, s)	the result is chosen by comparing the arguments	a CASE expression that compares the fields
a < b where a and b are arrays or maps that contain ROW values	the ROW values inside are ordered	compare the fields of the elements
(a, b) = (c, d), (a, b) <> (c, d), (a, b) < (c, d)	a row constructor builds a ROW value	(a, b) IS [NOT] DISTINCT FROM (c, d), a < c OR (a = c AND b < 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:

• IS [NOT] DISTINCT FROM, <=>, and IS [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, and EXCEPT;
• constructs that sort or choose values in the order described in comparing complex values: ORDER BY, MIN, MAX, ARG_MIN, ARG_MAX, SORT_ARRAY, ARRAY_MIN, and ARRAY_MAX;
• =, <>, and != between ARRAY or MAP values that contain ROW values, which compare the ROW values using IS NOT DISTINCT FROM.

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 or a < c OR (a = c AND b < d), if that is what you mean.

Other conditional operators

CASE value WHEN value1 [, value11 ]* THEN result1 [ WHEN valueN [, valueN1 ]* THEN resultN ]* [ ELSE resultZ ] END	Simple case expression: returns the result corresponding to the first valueN that matches value.
CASE WHEN condition1 THEN result1 [ WHEN conditionN THEN resultN ]* [ ELSE resultZ ] END	Searched case: returns result corresponding to first condition that evaluates to 'true'.
COALESCE(value0, value1 [, valueN ]*)	Returns the first non-null value. For example, COALESCE(NULL, 5) returns 5.
GREATEST( expr [, expr ]* )	The largest of a number of expressions; if any argument is NULL, the result is NULL. Rejected for ROW values.
GREATEST_IGNORE_NULLS( expr [, expr ]* )	The largest of a number of expressions; only if all arguments are NULL, the result is NULL; otherwise NULL values are ignored. Rejected for ROW values.
IF( condition, ifTrue, ifFalse )	Returns ifTrue if the condition evaluates to 'true', returns ifFalse otherwise.
IFNULL( left, right )	Equivalent to COALESCE(left, right).
LEAST( expr [, expr ]* )	The smallest of a number of expressions; if any argument is NULL, the result is NULL. Rejected for ROW values.
LEAST_IGNORE_NULLS( expr [, expr ]* )	The smallest of a number of expressions; only if all arguments are NULL, the result is NULL; otherwise NULL values are ignored. Rejected for ROW values.
NULLIF(value0, value1)	Returns NULL if value0 and value1 are the same. For example, NULLIF(5, 5) returns NULL; NULLIF(5, 0) returns 5.