Supporting NULL elements in arrays

Hello,

it's great news. My personal opinion about formating NULL values
'{a,"",b} -- wrong, "" means empty string, isn't null
'{a,,b} ' -- ok, maybe not unique,
'{a, NULL, b}' -- longer, clean NULL is NULL
'{a, "NULL", b}' -- "NULL" is not null varchar 'NULL'

Flags for array? Maybe bit isHash? So, hash array can be in line

array->null array->hash array

or flag for nor regular array (sparse array), not type unique arrays (array
can contains different types)

Regards
Pavel Stehule

_________________________________________________________________
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/

and so the most straightforward thing to do is define an empty element
as meaning a NULL. But this might be objected to on a couple of

grounds:

Can you use a default to allow the user to specify the default value for
an element? May look a little strange, though, if the user specifies a
default array and a default element value, like:

CREATE TABLE ARR(ARR CHAR(30)[] DEFAULT '{"hello", "good bye"}' NULL);

So the first default is the array default; specify NULL if you don't
want one but do want an array element default; the second, if present,
is the array element default. I'm not sure I like this or not, but it's
an idea.

Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc

"Pavel Stehule" <pavel.stehule@hotmail.com> writes:

it's great news. My personal opinion about formating NULL values
'{a, NULL, b}' -- longer, clean NULL is NULL

Unfortunately, that already has a meaning, and it's not that.

regards, tom lane

Tom Lane wrote:

"Pavel Stehule" <pavel.stehule@hotmail.com> writes:

it's great news. My personal opinion about formating NULL values
'{a, NULL, b}' -- longer, clean NULL is NULL

Unfortunately, that already has a meaning, and it's not that.

What a pity. I don't see any alternative to the empty element proposal -
it's worth the possible breakage.

cheers

andrew

and so the most straightforward thing to do is define an empty element
as meaning a NULL. But this might be objected to on a couple of

grounds:

Can you use a default to allow the user to specify the default value for
an element? May look a little strange, though, if the user specifies a
default array and a default element value, like:

CREATE TABLE ARR(ARR CHAR(30)[] DEFAULT '{"hello", "good bye"}' NULL);

So the first default is the array default; specify NULL if you don't
want one but do want an array element default; the second, if present,
is the array element default. I'm not sure I like this or not, but it's
an idea.

Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc

In article <4370B990.7090100@dunslane.net>,
Andrew Dunstan <andrew@dunslane.net> writes:

Tom Lane wrote:

"Pavel Stehule" <pavel.stehule@hotmail.com> writes:

it's great news. My personal opinion about formating NULL values
'{a, NULL, b}' -- longer, clean NULL is NULL

Unfortunately, that already has a meaning, and it's not that.

What a pity. I don't see any alternative to the empty element proposal
-
it's worth the possible breakage.

How about '{a, \N, b}'?

"Pollard, Mike" <mpollard@cincom.com> writes:

Can you use a default to allow the user to specify the default value for
an element?

There's no mechanism for specifying a default value for individual
elements within an array; and I've never heard one asked for. Certainly
it's far less interesting than allowing nulls within an array. So
I don't think we need to give pride of syntax place to that.

regards, tom lane

Harald Fuchs <hf0923x@protecting.net> writes:

Tom Lane wrote:

Unfortunately, that already has a meaning, and it's not that.

How about '{a, \N, b}'?

That's valid syntax too, ie, adopting that syntax would break
applications that are not broken today.

Not to mention that it would be gratuitously different from the notation
for NULLs in composite-type literals.

regards, tom lane

I wrote:

... the most straightforward thing to do is define an empty element
as meaning a NULL. But this might be objected to on a couple of grounds:

I just thought of another, potentially fatal objection: it's ambiguous
whether '{}'::text[] should be taken to mean an empty (zero-length)
array or an array containing a single NULL element.

For backwards compatibility it should mean an empty array, but then
there's no way to represent ARRAY(NULL) in data dumps, which won't
do either.

The only workaround that comes to mind is to allow explicit
specification of what's meant: '[1:1]{}' would be needed to represent
the one-null case. Ugly.

Ideas anyone?

regards, tom lane

On Tue, Nov 08, 2005 at 07:21:34PM -0500, Tom Lane wrote:

I wrote:

... the most straightforward thing to do is define an empty element
as meaning a NULL. But this might be objected to on a couple of grounds:

I just thought of another, potentially fatal objection: it's ambiguous
whether '{}'::text[] should be taken to mean an empty (zero-length)
array or an array containing a single NULL element.

To take another perspective on this, though - it isn't possible to have
NULL array elements right now, therefore, there is no precident, and who
is to say that {NULL} <> {}?

For example:

vhosts=> select ('{1, 3}'::text[])[4];
text
------

(1 row)

vhosts=> select ('{}'::text[])[4];
text
------

(1 row)

Perhaps NULL at end of array never needs to be stored, and arrays can
be considered to have an infinite number of NULL values at the end?

For array operations that require a length, such as cross-product, or
whatever, the 'length' of the array, would be the number of elements
before the infinite number of NULL values at the end.

Cheers,
mark

--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada

One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...

http://mark.mielke.cc/

On Tue, Nov 08, 2005 at 07:21:34PM -0500, Tom Lane wrote:

I wrote:

... the most straightforward thing to do is define an empty element
as meaning a NULL. But this might be objected to on a couple of grounds:

I just thought of another, potentially fatal objection: it's ambiguous
whether '{}'::text[] should be taken to mean an empty (zero-length)
array or an array containing a single NULL element.

For backwards compatibility it should mean an empty array, but then
there's no way to represent ARRAY(NULL) in data dumps, which won't
do either.

The only workaround that comes to mind is to allow explicit
specification of what's meant: '[1:1]{}' would be needed to represent
the one-null case. Ugly.

Instead of bending over backwards to try and support older cases, would
a compatability mode be possible? Seems that would solve a lot of
problems.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Jim C. Nasby wrote:

On Tue, Nov 08, 2005 at 07:21:34PM -0500, Tom Lane wrote:

... the most straightforward thing to do is define an empty element
as meaning a NULL. But this might be objected to on a couple of grounds:

I just thought of another, potentially fatal objection: it's ambiguous
whether '{}'::text[] should be taken to mean an empty (zero-length)
array or an array containing a single NULL element.

For backwards compatibility it should mean an empty array, but then
there's no way to represent ARRAY(NULL) in data dumps, which won't
do either.

The only workaround that comes to mind is to allow explicit
specification of what's meant: '[1:1]{}' would be needed to represent
the one-null case. Ugly.

Instead of bending over backwards to try and support older cases, would
a compatability mode be possible? Seems that would solve a lot of
problems.

Last time I thought about this problem, that's what I concluded. I don't
think there is a reasonable and backward compatible solution.

I also think the best non-compatible solution is to require non-numeric
elements to be delimited (double quotes, configurable?), and use NULL
unadorned to represent NULL.

Joe

I also think the best non-compatible solution is to require non-numeric
elements to be delimited (double quotes, configurable?), and use NULL
unadorned to represent NULL.

I think the ultimate solution should have null values represented by
NULL... I mean NULL is NULL :)

Chris

How about representing null array elements with a special-case cast-like
null? Something like {::null}

__
Marc

On Tue, 2005-11-08 at 23:30 -0400, pgsql-hackers-owner@postgresql.org
wrote:

Marc Munro <marc@bloodnok.com> writes:

How about representing null array elements with a special-case cast-like
null? Something like {::null}

The problem with *any* proposal for let's-spell-NULL-like-this
is that "this" is inevitably a string that is valid as a text
data value. Making the string weirder may reduce the odds of
a failure but the risk is still there, and meanwhile you're making
the null marker less readable. (The above has the additional problem
of being indistinguishable from a sub-array.)

I think we really need something that is rejected as syntactically
invalid by the existing array input parser.

regards, tom lane

On Wed, Nov 09, 2005 at 11:22:37AM -0500, Tom Lane wrote:

Marc Munro <marc@bloodnok.com> writes:

How about representing null array elements with a special-case cast-like
null? Something like {::null}

The problem with *any* proposal for let's-spell-NULL-like-this
is that "this" is inevitably a string that is valid as a text
data value. Making the string weirder may reduce the odds of
a failure but the risk is still there, and meanwhile you're making
the null marker less readable. (The above has the additional problem
of being indistinguishable from a sub-array.)

I think we really need something that is rejected as syntactically
invalid by the existing array input parser.

Hehehe... Feeling silly here:

{1,2,}NULL{,4,5,}NULL

What are the feelings on representing it similar to how it would be
in memory?

{1,2,,4,5,}(,,NULL,,,NULL)

Then, there is always the possibility of including a new array text format
for arrays that contain nulls (Q for quote?):

Q{1,2,NULL,4,5,NULL}

This would activate a mode that would fully quote any non-numeric,
non-array arguments, allowing NULL to be unambiguous?

Q{'a','b',NULL,'c','d',NULL}

Cheers,
mark

--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada

One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...

http://mark.mielke.cc/

Joe Conway wrote:

Last time I thought about this problem, that's what I concluded. I don't
think there is a reasonable and backward compatible solution.

I also think the best non-compatible solution is to require non-numeric
elements to be delimited (double quotes, configurable?), and use NULL
unadorned to represent NULL.

If we're going non-computable, would something that's a superset
of the SQL Standard's array value constructor be useful; or is
the standard's arrays so limited as to not even address the
things that cause issues for postgresql arrays?

If I read the confusing thing right, I think the standard
does use NULL for nulls in arrays, single-quotes for strings,
etc. like "ARRAY['FOO',null,'BAR']" and unadorned numbers
for numbers in arrays. That's similar to what I think Joe
suggested, but with single rather than double quotes?

Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:

If we're going non-computable, would something that's a superset
of the SQL Standard's array value constructor be useful;

I think you've confused ARRAY[] expressions with the I/O representation
for array values. There are many good reasons why those are not
identical, starting with the fact that string literals inside an ARRAY[]
would be subject to different escaping conventions than an I/O value is.

regards, tom lane

Joe Conway <mail@joeconway.com> writes:

Jim C. Nasby wrote:

Instead of bending over backwards to try and support older cases, would
a compatability mode be possible? Seems that would solve a lot of
problems.

Last time I thought about this problem, that's what I concluded. I don't
think there is a reasonable and backward compatible solution.

I also think the best non-compatible solution is to require non-numeric
elements to be delimited (double quotes, configurable?), and use NULL
unadorned to represent NULL.

After further thought I'm starting to agree with this point of view as
well. I propose the following details:

1. A null element is represented as the unquoted string NULL
(case-insensitive on input). Any use of quotes or backslashes
turns it into a simple string value "NULL" instead. array_out
will need to be careful to quote any string that matches NULL.

2. For backwards compatibility, we'll invent a GUC parameter
enable_array_nulls that defeats recognition of NULL in array_in.
(Any better ideas about the name of the parameter?)

This isn't ideal because of the compatibility risk, but once we get past
the transition period it's a reasonable definition.

regards, tom lane

I wrote:

... Now, when the bitmap is present, it would be fairly tedious
to calculate the length of the bitmap to determine the offset to the
actual data; and that's an operation that we'd like to be cheap. What
I'm thinking of doing is commandeering the whole "flags" field of
ArrayType (which is currently unused and should be always zero), and
redefining it thus:
zero: bitmap not present (fully backwards-compatible case)
not zero: bitmap is present; value is offset to start of data
I wouldn't propose doing this if I thought we had any pressing reason
to save some array flag bits for some other purpose; but I can't think
of anything else we might want 'em for. Did anyone have any pet ideas
this would foreclose?

On trying to recompile things, I find that contrib/intarray is broken
by this change, because it's using the flags field for its own purposes:

/*
* flags for gist__int_ops, use ArrayType->flags
* which is unused (see array.h)
*/
#define LEAFKEY (1<<31)
#define ISLEAFKEY(x) ( ((ArrayType*)(x))->flags & LEAFKEY )

It seems likely that intarray is going to need some rather significant
work anyway to deal with null elements, so this seems to me to be not
necessarily a fatal objection. But why exactly does intarray need to
play games with the contents of an array value?

regards, tom lane

On trying to recompile things, I find that contrib/intarray is broken
by this change, because it's using the flags field for its own purposes:

/*
* flags for gist__int_ops, use ArrayType->flags
* which is unused (see array.h)
*/
#define LEAFKEY (1<<31)
#define ISLEAFKEY(x) ( ((ArrayType*)(x))->flags & LEAFKEY )

It seems likely that intarray is going to need some rather significant
work anyway to deal with null elements, so this seems to me to be not
necessarily a fatal objection. But why exactly does intarray need to
play games with the contents of an array value?

Sorry, intarray was first our module for PgSQL. I'll remove usage of
ArrayType->flags soon.

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/