Re: Do we want a hashset type?

On 2023-06-23 Fr 04:23, Joel Jacobson wrote:
> 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