Re: cardinality()

We seem to have acquired a cardinality() function with almost no
discussion, and it has semantics that are a bit surprising to me. I
should have thought cardinality(array) would be the total number of
elements in the array. Instead, it seems it is a synonym for
array_length(array,1). Is that *really* what the standard says?

cheers

andrew

On 1 Mar 2009, at 00:52, Andrew Dunstan wrote:

>
> We seem to have acquired a cardinality() function with almost no
> discussion, and it has semantics that are a bit surprising to me. I
> should have thought cardinality(array) would be the total number of
> elements in the array. Instead, it seems it is a synonym for
> array_length(array,1). Is that *really* what the standard says?

any difference between array_upper(array,1), and cardinality ?
Standart just says something like:

cardinality (a collection):
- The number of elements in that collection.
- Those elements need not necessarily have distinct values.
- The objects to which this concept applies includes tables and the
values of collection types.

Grzegorz Jaskiewicz wrote:
>
> On 1 Mar 2009, at 00:52, Andrew Dunstan wrote:
>
>>
>> We seem to have acquired a cardinality() function with almost no
>> discussion, and it has semantics that are a bit surprising to me. I
>> should have thought cardinality(array) would be the total number of
>> elements in the array. Instead, it seems it is a synonym for
>> array_length(array,1). Is that *really* what the standard says?
>
> any difference between array_upper(array,1), and cardinality ?
> Standart just says something like:
>
> cardinality (a collection):
> - The number of elements in that collection.
> - Those elements need not necessarily have distinct values.
> - The objects to which this concept applies includes tables and the
> values of collection types.
>

Well, I think that's a definition of the term as used in the standard,
rather than of a function. But in any case, I think it goes in the right
direction, and the semantics of our new function (as well as the docs)
are misleading.

I'm also a bit concerned that I could not find any real discussion of
this new function at all on this list, so our processes seem to have
slipped a bit.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Grzegorz Jaskiewicz wrote:
>> On 1 Mar 2009, at 00:52, Andrew Dunstan wrote:
>>> We seem to have acquired a cardinality() function with almost no
>>> discussion, and it has semantics that are a bit surprising to me. I
>>> should have thought cardinality(array) would be the total number of
>.> elements in the array. Instead, it seems it is a synonym for
>>> array_length(array,1). Is that *really* what the standard says?

>> Standart just says something like:
>> cardinality (a collection):
>> - The number of elements in that collection.

The standard doesn't have multi-dimensional arrays, so it's entirely
possible that somewhere in it there is wording that makes cardinality()
equivalent to the length of the first dimension. But I concur with
Andrew that this is flat wrong when extended to m-d arrays.

regards, tom lane

I wrote:
> The standard doesn't have multi-dimensional arrays, so it's entirely
> possible that somewhere in it there is wording that makes cardinality()
> equivalent to the length of the first dimension. But I concur with
> Andrew that this is flat wrong when extended to m-d arrays.

I poked around in the SQL:2008 draft a bit. AFAICT the most precise
statement about cardinality() is in 6.27 <numeric value function>:

<cardinality expression> ::=
CARDINALITY<left paren> <collection value expression> <right paren>

7) The result of <cardinality expression> is the number of elements of
the result of the <collection value expression>.

Now the standard is only considering 1-D arrays, but I fail to see any
way that it could be argued that the appropriate reading of "number of
elements" for a multi-D array is the length of the first dimension.
So I think Andrew is right and we need to fix our implementation of
cardinality() while we still can.

regards, tom lane

2009/3/1 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> I wrote:
>> The standard doesn't have multi-dimensional arrays, so it's entirely
>> possible that somewhere in it there is wording that makes cardinality()
>> equivalent to the length of the first dimension.  But I concur with
>> Andrew that this is flat wrong when extended to m-d arrays.
>
> I poked around in the SQL:2008 draft a bit.  AFAICT the most precise
> statement about cardinality() is in 6.27 <numeric value function>:
>
>  <cardinality expression> ::=
>        CARDINALITY<left paren> <collection value expression> <right paren>
>
>  7) The result of <cardinality expression> is the number of elements of
>     the result of the <collection value expression>.
>
> Now the standard is only considering 1-D arrays, but I fail to see any
> way that it could be argued that the appropriate reading of "number of
> elements" for a multi-D array is the length of the first dimension.
> So I think Andrew is right and we need to fix our implementation of
> cardinality() while we still can.

₊1

regards
Pavel Stehule

>
>                        regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Sun, 1 Mar 2009, Tom Lane wrote:

> I wrote:
> > The standard doesn't have multi-dimensional arrays, so it's entirely
> > possible that somewhere in it there is wording that makes cardinality()
> > equivalent to the length of the first dimension. But I concur with
> > Andrew that this is flat wrong when extended to m-d arrays.
>
> I poked around in the SQL:2008 draft a bit. AFAICT the most precise
> statement about cardinality() is in 6.27 <numeric value function>:
>
> <cardinality expression> ::=
> CARDINALITY<left paren> <collection value expression> <right paren>
>
> 7) The result of <cardinality expression> is the number of elements of
> the result of the <collection value expression>.
>
> Now the standard is only considering 1-D arrays, but I fail to see any
> way that it could be argued that the appropriate reading of "number of
> elements" for a multi-D array is the length of the first dimension.

Does the standard allow you to make arrays of arrays, for example with
something like ARRAY[ARRAY[1,2], ARRAY[3,4]]? If so, it might be possible
that cardinality(<that expression>) would be returning the number of
arrays in the outer array.

On Sunday 01 March 2009 19:40:16 Tom Lane wrote:
> I wrote:
> > The standard doesn't have multi-dimensional arrays, so it's entirely
> > possible that somewhere in it there is wording that makes cardinality()
> > equivalent to the length of the first dimension. But I concur with
> > Andrew that this is flat wrong when extended to m-d arrays.
>
> I poked around in the SQL:2008 draft a bit. AFAICT the most precise
> statement about cardinality() is in 6.27 <numeric value function>:
>
> <cardinality expression> ::=
> CARDINALITY<left paren> <collection value expression> <right paren>
>
> 7) The result of <cardinality expression> is the number of elements of
> the result of the <collection value expression>.
>
> Now the standard is only considering 1-D arrays,

The standard represents multidimensional arrays as arrays of arrays (like in
C). But the cardinality is only that of the first level array.

The real question here is how we want to consider mapping what the standard
has to what PostgreSQL has, and might have in the future. For example, will
we ever have arrays of arrays as distinct from multidimensional arrays? Will
we support things like array of multiset of array? What would the results be
there?

I think PostgreSQL multidimensional array support and SQL standard
multidimensional array support are pretty well in line leaving aside minor
syntax issues and the major syntax issue that the subscript order is
reversed. So I think there is not much of a need to do much redefining and
reinterpreting, unless someone has a larger and different plan in mind.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:

> The standard represents multidimensional arrays as arrays of arrays (like in
> C).

Uh, C doesn't represent multidimensional arrays as arrays of arrays so you've
lost me already.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!

Gregory Stark wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>
> > The standard represents multidimensional arrays as arrays of arrays (like in
> > C).
>
> Uh, C doesn't represent multidimensional arrays as arrays of arrays so you've
> lost me already.

I think he meant to say C _can_ represent multidimensional arrays as
arrays of arrays.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +