On 12 June 2013 18:22, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> +1 for having a function to return the total number of elements in an
> array, because that's something that's currently missing from SQL.
>
> However, I think that CARDINALITY() should be that function.
>
> I'm not convinced that having CARDINALITY() return the length of the
> first dimension is more spec-compatible, since our multi-dimensional
> arrays aren't nested arrays, and it seems unlikely that they ever will
> be. I'd argue that it's at least equally spec-compatible to have
> CARDINALITY() return the total number of elements in the array, if you
> think of a multi-dimensional array as a collection of elements
> arranged in a regular pattern.
It's true that our multidims aren't nested, but they are the nearest
thing we have. If we want to keep the door open for future attempts
to nudge multidim arrays into closer approximation of nested arrays,
it would be better to have the nested interpretation of CARDINALITY.
Given what we've just gone through with array_length, it seems that
once we select a behaviour for CARDINALITY, we will be stuck with it
permanently.
The problem with thinking of our multidim arrays as just a weirdly
crumpled arrangement of a single collection, is that we've already
abused the nesting syntax for declaring them.
> Also, the spec describes CARDINALITY() and UNNEST() using the same
> language, and I think it's implicit in a couple of places that
> CARDINALITY() should match the number of rows returned by UNNEST(),
> which we've already implemented as fully unnesting every element.
>
> We're about to add ORDINALITY to UNNEST(), and to me it would be very
> odd to have the resulting maximum ordinality exceed the array's
> cardinality.
Yeah, that makes sense. Well the good news is that either way,
CARDINALITY will do what people want in the most common case where the
array is one-dimensional.
Multidim arrays are why we can't have nice things.
Cheers,
BJ