Re: Do we want a hashset type?

On Tue, Jun 27, 2023, at 04:35, jian he wrote:
> in SQLMultiSets.pdf(previously thread) I found a related explanation
> on page 45, 46.
>
> (CASE WHEN OP1 IS NULL OR OP2 IS NULL THEN NULL ELSE MULTISET ( SELECT
> T1.V FROM UNNEST (OP1) AS T1 (V) INTERSECT SQ SELECT T2.V FROM UNNEST
> (OP2) AS T2 (V) ) END)
>
> CASE WHEN OP1 IS NULL OR OP2 IS NULL THEN NULL ELSE MULTISET ( SELECT
> T1.V FROM UNNEST (OP1) AS T1 (V) UNION SQ SELECT T2.V FROM UNNEST
> (OP2) AS T2 (V) ) END
>
> (CASE WHEN OP1 IS NULL OR OP2 IS NULL THEN NULL ELSE MULTISET ( SELECT
> T1.V FROM UNNEST (OP1) AS T1 (V) EXCEPT SQ SELECT T2.V FROM UNNEST
> (OP2) AS T2 (V) ) END)

Thanks! This was exactly what I was looking for, I knew I've seen it but failed to find it.

Attached is a new incremental patch as well as a full patch, since this is a substantial change:

Align null semantics with SQL:2023 array and multiset standards

* Introduced a new boolean field, null_element, in the int4hashset_t type.

* Rename hashset_count() to hashset_cardinality().

* Rename hashset_merge() to hashset_union().

* Rename hashset_equals() to hashset_eq().

* Rename hashset_neq() to hashset_ne().

* Add hashset_to_sorted_array().

* Handle null semantics to work as in arrays and multisets.

* Update int4hashset_add() to allow creating a new set if none exists.

* Use more portable int32 typedef instead of int32_t.

This also adds a thorough test suite in array-and-multiset-semantics.sql,
which aims to test all relevant combinations of operations and values.

Makefile | 2 +-
README.md | 6 ++--
hashset--0.0.1.sql | 37 +++++++++++---------
hashset-api.c | 208 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--------------------------
hashset.c | 12 ++++++-
hashset.h | 11 +++---
test/expected/array-and-multiset-semantics.out | 365 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
test/expected/basic.out | 12 +++----
test/expected/reported_bugs.out | 6 ++--
test/expected/strict.out | 114 ------------------------------------------------------------
test/expected/table.out | 8 ++---
test/sql/array-and-multiset-semantics.sql | 232 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
test/sql/basic.sql | 4 +--
test/sql/benchmark.sql | 14 ++++----
test/sql/reported_bugs.sql | 6 ++--
test/sql/strict.sql | 32 -----------------
test/sql/table.sql | 2 +-
17 files changed, 823 insertions(+), 248 deletions(-)

/Joel