About sets

EQL represents multi-assign attributes from collections as sets.

A set consists of a group of elements, typically derived from the values of a multi-assign attribute. EQL sets are intended to behave like mathematical sets: the order of the elements within a set is not specified (and, in general, not observable). An empty set is a set that contains no elements.

All elements in a set must be of the same data type. If the elements in the set come from two multi-assign attributes (for example, by using the INTERSECTION row function), then those two multi-assign attributes must be of the same data type. Sets may not contain duplicate values and sets may not contain other sets.

Sets are constructed in an EQL statement as follows:

From a reference to a multi-assign attribute. For example, using SELECT with a multi-assign attribute will return the vales of that attribute in a set.
From a single-assign attribute, as an argument to the SET function.
From an expression that results in a set. For example, using a UNION function will return a set that is a union of two input sets. Note that these set expressions require at least one set on which to operate.
From a set constructor.

All of these methods are described in this section.

Note that sets are not persistent from one EQL query to another.

Set data types

The data types for sets are:

mdex:boolean-set for multi-assign Boolean attributes
mdex:dateTime-set for multi-assign dateTime attributes
mdex:double-set for multi-assign double attributes
mdex:duration-set for multi-assign duration attributes
mdex:geocode-set for multi-assign geocode attributes
mdex:long-set for multi-assign 32-bit integer and 64-bit long attributes
mdex:string-set for multi-assign string attributes
mdex:time-set for multi-assign time attributes

Sets are strictly typed. All of the elements of a specific set must have the same data type. For example, this set:

{3, 4.0, 'five'}

is invalid because it contains an integer, a double, and a string.

Sets and NULL

Sets may not contain NULL values. In addition, sets may not be NULL, but they may be empty. These requirements apply to both multi-assign collection attributes and other expressions of set type.

If a collection record has no assignments for a multi-assign attribute, then in an EQL query, that attribute's value for that record is the empty set.

The results of an EQL statement (whether DEFINE or RETURN) may contain sets. This means, for instance, that you can define an entity (view) that provides all of the values of a multi-assign attribute to queries that use that entity.

Note that the IS NULL and IS NOT NULL operations are not supported on sets. Instead, use the IS_EMPTY and IS_NOT_EMPTY functions to determine whether a set is empty. Likewise, the IS_EMPTY and IS_NOT_EMPTY functions cannot be used on atomic values (such as on a single-assign attribute).

Set equality

Set equality is the same as mathematical set equality: two sets are equal if and only if they contain exactly the same elements, no more, no less. The order of the elements in the set is immaterial. Two empty sets are equal.

Set equality and inequality are defined only on two sets of the same type. For example, you cannot compare an mdex:long-set and an mdex:geocode-set for equality; doing so will result in an EQL type error.

You can use the = (equal) and <> (not equal) operators to test for equality between sets. Note that the < (less than) and > (greater than) operators are not defined for sets.

Sets, functions, and operators

This chapter documents the aggregation and row functions that are used with sets.

In addition, sets can be used with the following functions that work on both sets and single-assign attributes, and are documented elsewhere in this guide:

ARB on sets looks at all of the rows (both empty sets and non-empty sets) in the group and selects the set value from one of the rows. For details on this function, see ARB.
COUNT counts all non-NULL sets (that is, all the sets in the group, including the empty ones). For details, see COUNT function.
COUNT_APPROX also counts all non-NULL sets. For details, see COUNT_APPROX.
COUNTDISTINCT counts all of the sets, including the empty ones. For details, see COUNTDISTINCT function.
APPROXCOUNTDISTINCT also counts all of the sets, including the empty ones. For details, see APPROXCOUNTDISTINCT function.
HAS_REFINEMENTS whether a particular attribute has non-implicit refinements in the current navigation state. For details, see HAS_REFINEMENTS.

As mentioned above, you can use the = (equal) and <> (not equal) operators to test for equality between sets. The other operators (such as the * multiplication operator) cannot be used on sets.