DISTINCT Types in Distributed Operations

Cross-server and cross-database operations can return DISTINCT types whose base types are built-in atomic data types, if the DISTINCT values are explicitly cast to a built-in data type. The base-type hierarchy and the casts must be identically defined in every participating database.

Restrictions on base types and on casts

Only a subset of built-in opaque types, however, can be the base type of a DISTINCT value that a distributed query returns. Complex DISTINCT types defined on a ROW base type can be returned only from the local database.

DISTINCT column values cannot be retrieved from another database of the same HCL OneDB™ instance by a distributed query (nor modified by INSERT, DELETE, MERGE, or UPDATE cross-database distributed operations), nor by a function call, unless all of the following conditions are true:

The DISTINCT type is defined on one of the following base types:
- an non-opaque built-in atomic data type
- a BOOLEAN, BSON, JSON, or LVARCHAR data type
- a DISTINCT type defined on BOOLEAN, on BSON, on JSON, on LVARCHAR, or on a non-opaque built-in data type.
(This condition also applies recursively to DISTINCT types of DISTINCT types, where the ultimate base type is BOOLEAN, BSON, JSON, or LVARCHAR, or a non-opaque built-in data type.)
the DISTINCT type is explicitly cast to BOOLEAN, to BSON, to JSON, to LVARCHAR, or to a non-opaque built-in type
the DISTINCT type, its type hierarchy, and its explicit cast to a built-in type are defined exactly the same way in all participating databases.

For DISTINCT data types in distributed operations, the data type hierarchy must have one of the following forms, which cannot vary across the participating databases:

Base-type hierarchy for Distributed DISTINCT Types


Element	Description	Restrictions	Syntax
built-in _ non-opaque_type	Atomic built-in data type that is not opaque	Type cannot be complex, serial, BYTE, or TEXT	Data Type
distinct_type	DISTINCT type whose base type is another DISTINCT type	Root of this hierarchy must be a BSON, BOOLEAN, JSON, LVARCHAR, or an atomic built-in non-opaque data type	Data Type

Important:

The diagram above shows the generalized logical hierarchy of the base types for any DISTINCT data type. Using the DISTINCT OF keywords recursively, however, as in the diagram above, is not valid SQL syntax. The CREATE DISTINCT TYPE statement must specify exactly one base type for the new DISTINCT type. To create a hierarchy of DISTINCT data types, you must issue a separate CREATE DISTINCT TYPE statement for every DISTINCT type in the hierarchy. For the SQL syntax to define a new DISTINCT data type, see the topic CREATE DISTINCT TYPE statement.

A user-defined routine can return to the local database a DISTINCT data type from another database of the same HCL OneDB instance only if all of the conditions listed above are true, and the UDR is defined in all of the participating databases.

The built-in DISTINCT type IDSSECURITYLABEL

The IDSSECURITYLABEL data type, which stores the security label in rows of protected tables, is a built-in DISTINCT type that satisfies this requirement, because its base type is the built-in VARCHAR(128) data type.

In queries that access a protected table in an HCL OneDB database, the security label stored in the IDSSECURITYLABEL column prevents protected rows from being returned by the query, unless the user issuing the query holds sufficient LBAC credentials for the security policy of which that label is a component.

DISTINCT types in cross-server operations

The same rules that apply to DISTINCT data types in distributed operations across databases of the same HCL OneDB instance also apply to DISTINCT data types in cross-server distributed operations on databases of different HCL OneDB instances.

For additional information about the data types that HCL OneDB supports in distributed operations, see Data Types in Distributed Queries.