Thread: Should array_length() Return NULL
Hackers,
This surprised me:
david=# select array_length('{}'::text[], 1); array_length -------------- [null]
I had expecte dit to retur 0. I might expect NULL for a NULL param, but not one that's defined but has no elements.
Best,
David
"David E. Wheeler" <david@justatheory.com> writes:
> This surprised me:
> david=# select array_length('{}'::text[], 1);
> array_length
> --------------
> [null]
> I had expecte dit to retur 0. I might expect NULL for a NULL param, but not one that's defined but has no elements.
The thing is that that syntax creates an array of zero dimensions,
not one that has 1 dimension and zero elements. So "0" would be
incorrect.
Our handling of empty arrays leaves something to be desired, I agree,
but making it more consistent seems like a large task. Hacking
array_length in isolation will certainly not help.
regards, tom lane
On Mar 15, 2013, at 3:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > The thing is that that syntax creates an array of zero dimensions, > not one that has 1 dimension and zero elements. So "0" would be > incorrect. > > Our handling of empty arrays leaves something to be desired, I agree, > but making it more consistent seems like a large task. Hacking > array_length in isolation will certainly not help. Oh. Is there a way to declare an empty 1-dimension array? Thanks, David
"David E. Wheeler" <david@justatheory.com> writes:
> Oh. Is there a way to declare an empty 1-dimension array?
Doesn't look like it:
regression=# select '[1:0]={}'::text[];
ERROR: upper bound cannot be less than lower bound
LINE 1: select '[1:0]={}'::text[]; ^
Possibly we should allow that, but just as with the other point, it
would require some thought and investigation to make sure we weren't
creating more problems than we solved.
regards, tom lane
On Mar 15, 2013, at 3:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Oh. Is there a way to declare an empty 1-dimension array?
>
> Doesn't look like it:
>
> regression=# select '[1:0]={}'::text[];
> ERROR: upper bound cannot be less than lower bound
> LINE 1: select '[1:0]={}'::text[];
> ^
>
> Possibly we should allow that, but just as with the other point, it
> would require some thought and investigation to make sure we weren't
> creating more problems than we solved.
Right, okay. I added a link to your email to https://wiki.postgresql.org/wiki/Todo#Arrays. Looks like better dealing
withempty arrays was already on the list, including this gem:
http://www.postgresql.org/message-id/28026.1224611437@sss.pgh.pa.us
Thanks,
David
On 16 March 2013 09:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David E. Wheeler" <david@justatheory.com> writes:
>> This surprised me:
>
>> david=# select array_length('{}'::text[], 1);
>> array_length
>> --------------
>> [null]
>
>> I had expecte dit to retur 0. I might expect NULL for a NULL param, but not one that's defined but has no elements.
>
> The thing is that that syntax creates an array of zero dimensions,
> not one that has 1 dimension and zero elements. So "0" would be
> incorrect.
>
I'm going to ask the question that immediately comes to mind: Is there
anything good at all about being able to define a zero-dimensional
array?
I would have thought that anything deserving the name "array" has
one-or-more dimensions, and that a "zero-dimensional array" is a weird
way of talking about a scalar value. In which case '{}'::text[] would
not be a legitimate way to declare one anyway. Am I missing
something?
Cheers,
BJ
Brendan Jurd <direvus@gmail.com> writes:
> On 16 March 2013 09:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The thing is that that syntax creates an array of zero dimensions,
>> not one that has 1 dimension and zero elements.
> I'm going to ask the question that immediately comes to mind: Is there
> anything good at all about being able to define a zero-dimensional
> array?
Perhaps not. I think for most uses, a 1-D zero-length array would be
just as good. I guess what I'd want to know is whether we also need
to support higher-dimensional zero-size arrays, and if so, what does
the I/O syntax for those look like?
Another fly in the ointment is that if we do redefine '{}' as meaning
something other than a zero-D array, how will we handle existing
database entries that are zero-D arrays?
regards, tom lane
On Mar 16, 2013, at 11:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Perhaps not. I think for most uses, a 1-D zero-length array would be
> just as good. I guess what I'd want to know is whether we also need
> to support higher-dimensional zero-size arrays, and if so, what does
> the I/O syntax for those look like?
No.
> Another fly in the ointment is that if we do redefine '{}' as meaning
> something other than a zero-D array, how will we handle existing
> database entries that are zero-D arrays?
NULL. About as useful. ;-P
David
2013/3/16 Tom Lane <tgl@sss.pgh.pa.us>:
> Brendan Jurd <direvus@gmail.com> writes:
>> On 16 March 2013 09:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> The thing is that that syntax creates an array of zero dimensions,
>>> not one that has 1 dimension and zero elements.
>
>> I'm going to ask the question that immediately comes to mind: Is there
>> anything good at all about being able to define a zero-dimensional
>> array?
>
> Perhaps not. I think for most uses, a 1-D zero-length array would be
> just as good. I guess what I'd want to know is whether we also need
> to support higher-dimensional zero-size arrays, and if so, what does
> the I/O syntax for those look like?
>
> Another fly in the ointment is that if we do redefine '{}' as meaning
> something other than a zero-D array, how will we handle existing
> database entries that are zero-D arrays?
>
a issue with zero dimension array is long story and I'' be really
happy when this story finish
Has somebody any useful example with zero dimensional array ?? Use
other programming languages zero dim array ??
Regards
Pavel
> regards, tom lane
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
On 17 March 2013 05:19, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Brendan Jurd <direvus@gmail.com> writes:
>> On 16 March 2013 09:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> The thing is that that syntax creates an array of zero dimensions,
>>> not one that has 1 dimension and zero elements.
>
>> I'm going to ask the question that immediately comes to mind: Is there
>> anything good at all about being able to define a zero-dimensional
>> array?
>
> Perhaps not. I think for most uses, a 1-D zero-length array would be
> just as good. I guess what I'd want to know is whether we also need
> to support higher-dimensional zero-size arrays, and if so, what does
> the I/O syntax for those look like?
If I'm reading right, in our current implementation of array
dimensionality, there can be no such thing as a higher-dimensional
zero-length array anyhow. Postgres doesn't care about how many
dimensions you define for an array, it uses the "quacks like a duck"
test for number of dimensions. For example:
postgres=# SELECT ARRAY[1]::int[][], array_dims(ARRAY[1]::int[][]);array | array_dims
-------+------------{1} | [1:1]
postgres=# SELECT ARRAY[ARRAY[1]]::int[];array
-------{{1}}
postgres=# SELECT ARRAY[ARRAY[1]]::int[][];array
-------{{1}}
Some array functions just plain don't work with multiple dimensions:
postgres=# SELECT array_append(ARRAY[ARRAY[1]]::int[][], ARRAY[2]);
ERROR: function array_append(integer[], integer[]) does not exist
So, to answer your question, no, I don't think we would need to
support it, at least not unless/until there is a major change and
number of dimensions becomes more meaningful. You can start out with
a zero-length array (which has one dimension) and then add nested
arrays to it if you want to -- it will then ipso facto have multiple
dimensions.
> Another fly in the ointment is that if we do redefine '{}' as meaning
> something other than a zero-D array, how will we handle existing
> database entries that are zero-D arrays?
>
I would go with zero-length 1-D. It's almost certainly what the
author intended.
I'd be more worried about the possibility of, say, PL/pg functions in
the field that rely on our existing bizarre behaviours to test for an
"empty" array, like IF array_length(A) IS NULL, or IF array_dims(A) IS
NULL. I'm pretty sure I have some such tests in my applications, and
I still think breaking them is a reasonable price to pay for greater
sanity.
Cheers,
BJ
Brendan Jurd <direvus@gmail.com> writes:
> On 17 March 2013 05:19, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Perhaps not. I think for most uses, a 1-D zero-length array would be
>> just as good. I guess what I'd want to know is whether we also need
>> to support higher-dimensional zero-size arrays, and if so, what does
>> the I/O syntax for those look like?
> If I'm reading right, in our current implementation of array
> dimensionality, there can be no such thing as a higher-dimensional
> zero-length array anyhow. Postgres doesn't care about how many
> dimensions you define for an array, it uses the "quacks like a duck"
> test for number of dimensions. For example:
> postgres=# SELECT ARRAY[1]::int[][], array_dims(ARRAY[1]::int[][]);
> array | array_dims
> -------+------------
> {1} | [1:1]
Um, this seems to be conflating the issue with a different one, which
is that the type system doesn't care how many dimensions arrays have.
So "int[]" and "int[][]" are the same type. That's slightly annoying
but I'm not sure it's really worth changing.
What I'm concerned about here is whether these expressions shouldn't
be yielding different data values:
regression=# select array[]::int[];array
-------{}
(1 row)
regression=# select array[array[]]::int[];array
-------{}
(1 row)
regression=# select array[array[],array[]]::int[];array
-------{}
(1 row)
Right now, if we did make them produce what they appear to mean, the
array I/O functions would have a problem with representing the results:
regression=# select '{}'::int[];int4
------{}
(1 row)
regression=# select '{{}}'::int[];
ERROR: malformed array literal: "{{}}"
LINE 1: select '{{}}'::int[]; ^
regression=# select '{{},{}}'::int[];
ERROR: malformed array literal: "{{},{}}"
LINE 1: select '{{},{}}'::int[]; ^
So I think we'd need to fix that before we could go very far in this
direction.
regards, tom lane
On 17 March 2013 06:27, Tom Lane <tgl@sss.pgh.pa.us> wrote: > What I'm concerned about here is whether these expressions shouldn't > be yielding different data values: > > > Right now, if we did make them produce what they appear to mean, the > array I/O functions would have a problem with representing the results: > > So I think we'd need to fix that before we could go very far in this > direction. I agree. I am starting to work on that very thing. I noticed that there are a whole bunch of errmsgs in ArrayCount and ReadArrayStr that just say "malformed array literal" with no detail message at all. Not very helpful. I'm tempted to improve that on my way past. Cheers, BJ
Brendan Jurd <direvus@gmail.com> writes:
> I noticed that there are a whole bunch of errmsgs in ArrayCount and
> ReadArrayStr that just say "malformed array literal" with no detail
> message at all. Not very helpful. I'm tempted to improve that on my
> way past.
+1, regardless of whether we end up changing the semantics ...
regards, tom lane