Improvements to SQL array support

[gavinking]

This is a spot to gather a list of missing features for our support for SQL arrays:

• support for the unnest() operation, which is logically like a join
• (maybe) support for array_agg(), which is necessary for collecting values into an array
• array literals

I'm sure there must be other things, more functions, for example.

[beikov]

In addition, I'd like to add the following:

• array(element1, element2, ...) - constructor function for arrays. no need for literals I believe.
• array_contains(array, element)
• array_position(array, element) - returns the position of the element or 0 if not found
• array_length(array)
• array_concat(array, array)
• array_prepend(element, array)
• array_append(array, element)
• array_get(array, index) - returns the element at the index
• array_set(array, index, element) - returns an array having the given index set with the given element
• array_remove(array, index) - returns an array with the element at the given index removed
• array_slice(array, startIndex, endIndex) - returns a sub-array from startIndex (inclusive) to endIndex (exclusive)

Also, wanted to note that array indexes should be 1-based to align with other functions.

The unnest function is actually a "table valued function" which I would also like to add support for, but in a separate step.

[jwgmeligmeyling]

I also use the operators:

• ARRAY_OVERLAPS(array, array)
• ARRAY_CONTAINS(array, array)
• ARRAY_IS_CONTAINED_BY(array, array)

The unnest function is actually a "table valued function" which I would also like to add support for, but in a separate step.

Supporting unnest in the select clause is pretty staightforward. People that want to select from it probably can emulate that through a subquery join anyway.

• (maybe) support for array_agg(), which is necessary for collecting values into an array

What is difficult about array_agg compared to other aggregate functions?

[gavinking]

So I see that Db2—and I believe Oracle as well—supports a sort of array type that has to be declared as a UDT.

I'm thinking we should introduce an @Array annotation similar to the @Struct one we just added in 6.2.

That's assuming these types are actually usable to us via JDBC (it's not very clear what the limitations are from the docs).

[beikov]

Well, to do that, I would need to implement this: #3811
Unless you have a different idea how we could make this happen?

[gavinking]

Well that's a possible implementation, for sure.

I don't think I have an opinion on what's the best way.

A way might be to register placeholder array types during the annotation binding process and then come back later, canonicalize them, and decide whether they need to actually be exported.

I mean, in its dumbest possible form, for example, the Dialect could just return a null string from getExportArrayTypeString() if it doesn't need to export anything. So this whole thing would really only involve the binder and the schema export tool.

If I just say it like that, it sounds pretty straightforward, but of course there might be subtleties.

[gavinking]

Actually a very similar problem here is support for enumerated types on Postgres. Similarity because:

• you need to declare the type, with a name, but
• just like arrays we can very easily infer a sensible name for the enumerated type (just the name of the Java enum), and
• similarly, you want to reuse the same declared type across multiple use-sites.

So this is some missing infrastructure that we should add, and kill these two birds.

[gavinking]

https://hibernate.atlassian.net/browse/HHH-16125

[gavinking]

FTR, HHH-16125 (Postgres enums, and Oracle array types) is now mostly done, and this subthread is no longer relevant.

[beikov]

This is done / will be done as part of https://hibernate.atlassian.net/browse/HHH-16780 and https://hibernate.atlassian.net/browse/HHH-17335.

[gavinking]

@beikov here's some suggestions for "syntax sugar" for array support:

1. Overload functions which currently accept strings, for example: length(), concat(), position(), and more broadly provide function names which don't start with array_. For example, I would much prefer overlaps() to array_overlaps().
2. Allow getting (and even setting) of elements using thing.array[index].
3. Support for the in operator.
4. Allow construction of arrays using [this, that, other].

That's all I can think of for now.

FTR, I have not thought through any of these things at all, and some might not make sense.

[beikov]

Thanks, let's continue in #7482