Re: Inconsistent behavior on Array & Is Null?

Joe Conway <mail@joeconway.com> writes:

> Greg Stark wrote:
> > I'm leaning towards suggesting that postgres should follow sql-99 here and
> > normalize all array indexes to have a lower bound of 1. Then array_lower and
> > array_upper become entirely unnecessary. Instead we just have array_length
> > which is exactly equivalent to my idea of array_upper.
> >
> 
> Now we finally have something to agree on ;-)
> 
> I do think this is the way to go, but it is a significant hit to backward
> compatibility. Same is true for supporting NULL elements of arrays -- maybe we
> should bite the bullet and make both changes at the same time?

In fact on further thought I think they *have* to be done together.

I forgot that your code did something else cool allowing updates to extend
arrays by directly updating elements outside the current bounds. Ie:

slo=> update test set a = '{}';
UPDATE 1
slo=> update test set a[1] = 1;
UPDATE 1
slo=> select * from test; a  
-----{1}
(1 row)

But if we normalize array indexes to start at 1 then this makes it hard to
fill in the array starting at higher values. For example:

slo=> update test set a = '{}';
UPDATE 1
slo=> update test set a[5] = 5;
UPDATE 1
slo=> select a[5] from test;a 
---5
(1 row)

This really ought to work, it obviously shouldn't allow you to set a[5] and
then surreptitiously move it to a[1]. But nor should it generate an error,
since I may well have a specific meaning for a[5] and may be planning to fill
in a[1]..a[4] later.

The logical thing to do, I think, is pre-fill the entries a[1]..a[4] with
null. This could be implemented by actually storing the NULLs or else storing
some notation that's used to adjust the base of the index to save space.


One thing that can't be made to work like it does now is extending the array
on the low end indefinitely:

slo=> update test set a[1] = 1;
UPDATE 1
slo=> update test set a[0] = 0;
UPDATE 1
slo=> update test set a[-1] = -1;
UPDATE 1
slo=> select * from test;   a     
----------{-1,0,1}
(1 row)


If this all looks familiar it's because Perl, and other languages, also behave
this way:

bash-2.05b$ perl -e '@a = (); $a[10]=10; print join(",",@a),"\n"'
,,,,,,,,,,10
bash-2.05b$ perl -e '@a = (); $a[-1]=-1; print join(",",@a),"\n"'
Modification of non-creatable array value attempted, subscript -1 at -e line 1.

-- 
greg