Re: Do we want a hashset type?

On Fri, Jun 23, 2023, at 08:40, jian he wrote:

I played around array_func.c
many of the code can be used for multiset data type.
now I imagine multiset as something like one dimension array. (nested 
is somehow beyond the imagination...).

Are you suggesting it might be a better idea to start over completely
and work on a new code base that is based on arrayfuncs.c,
and aim for MULTISET/SET or anyhashset from start, that would not
only support int4/int8/uuid but any type?

Before we run too far down this rabbit hole, let's discuss the storage implications of using multisets. ISTM that for small base datums like integers it will be a substantial increase in size, since you'll need an addition int for the item count, unless some very clever tricks are played.

As for this older discussion referred to upthread, if the SQL Standards Committee hasn't acted on it by now it seem reasonable to think they are unlikely to.

Just for reference, Here's some description of Oracle's suport for Multisets from <https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Oracle-Support-for-Optional-Features-of-SQLFoundation2011.html#GUID-3BA98AEC-FAAD-4F21-A6AD-F696B5D36D56>:

Multisets in the standard are supported as nested table types in Oracle. The Oracle nested table data type based on a scalar type ST is equivalent, in standard terminology, to a multiset of rows having a single field of type ST and named column_value. The Oracle nested table type based on an object type is equivalent to a multiset of structured type in the standard.

Oracle supports the following elements of this feature on nested tables using the same syntax as the standard has for multisets:

    The CARDINALITY function

    The SET function

    The MEMBER predicate

    The IS A SET predicate

    The COLLECT aggregate

All other aspects of this feature are supported with non-standard syntax, as follows:

    To create an empty multiset, denoted MULTISET[] in the standard, use an empty constructor of the nested table type.

    To obtain the sole element of a multiset with one element, denoted ELEMENT (<multiset value expression>) in the standard, use a scalar subquery to select the single element from the nested table.

    To construct a multiset by enumeration, use the constructor of the nested table type.

    To construct a multiset by query, use CAST with a multiset argument, casting to the nested table type.

    To unnest a multiset, use the TABLE operator in the FROM clause.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com