Обсуждение: "select ('{}'::text[])[1]" returns NULL -- is it correct?
Thinking about XPath's output in cases such as 'SELECT xpath('/a', '<b
/>');' I've realized that in such cases an empty array should be
returned (now we have NULL for such cases).
Why? Because database _knows_ that there is no element -- this is not
NULL's case ("unknown").
Then I've examined how the work with arrays in Postgres is organized.
And now the result of the following query seems to be a little bit
strange for me:
xmltest=# select ('{}'::text[])[1] IS NULL;?column?
----------t
(1 row)
As I can see here, when I ask for element that doesn't exist, the
database returns NULL for me. Maybe it's well-known issue (and
actually I understood this behaviour before), but strictly speaking it
seems wrong for me: the database _knows_ that there is no element, so
why NULL?
Actually, I do not know what output value would be the best for this
case (and I understand that it'd be very painful to change the
behaviour because of compatibility issues), so my questions are:1. is it worth to trigger at least notice message
(WARNING?)for such cases?2. what should I do with XPath function? There is strong analogy
between its case and array's case in my mind... Should I leave NULLs,
or empty arrays are better?
BTW, is there any better way to select empty array as a constant
(better then my "'{}'::text[]")?
--
Best regards,
Nikolay
"Nikolay Samokhvalov" <samokhvalov@gmail.com> writes: > As I can see here, when I ask for element that doesn't exist, the > database returns NULL for me. Maybe it's well-known issue (and > actually I understood this behaviour before), but strictly speaking it > seems wrong for me: the database _knows_ that there is no element, so > why NULL? This is operating as designed, per http://www.postgresql.org/docs/8.2/static/arrays.html : An array subscript expression will return null if either the array : itself or any of the subscript expressions are null. Also, null is : returned if a subscript is outside the array bounds (this case does not : raise an error). For example, if schedule currently has the dimensions : [1:3][1:2] then referencing schedule[3][3] yields NULL. Similarly, an : array reference with the wrong number of subscripts yields a null rather : than an error. AFAIR it's always been like that, so changing it seems exceedingly likely to break some peoples' applications. It's not completely without analogies in SQL, anyway: consider the behavior of INSERT when fewer columns are provided than the table has. Pretending that elements outside the stored range of the array are null is not all that different from silently adding nulls to a row-to-be-stored. regards, tom lane
Nikolay Samokhvalov wrote: > 2. what should I do with XPath function? There is strong analogy > between its case and array's case in my mind... Should I leave NULLs, > or empty arrays are better? Empty array appears to be correct. The fact that arrays don't appear to work as you might like is a different problem that should not affect the specification of the XPath functionality. -- Peter Eisentraut http://developer.postgresql.org/~petere/
"Nikolay Samokhvalov" <nikolay@samokhvalov.com> writes:
> I remember several cases when people (e.g. me :-) ) were spending some
> time trying to find an error in some pl/pgsql function and the reason
> lied in incorrect work with arrays (i.e. messages like "index is out
> of bounds" and "index cannot be negative number" would help, surely).
Well, if indexes *couldn't* be negative numbers then that might be
helpful, but they can.
regards, tom lane
On 4/10/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Nikolay Samokhvalov" <nikolay@samokhvalov.com> writes: > > I remember several cases when people (e.g. me :-) ) were spending some > > time trying to find an error in some pl/pgsql function and the reason > > lied in incorrect work with arrays (i.e. messages like "index is out > > of bounds" and "index cannot be negative number" would help, surely). > > Well, if indexes *couldn't* be negative numbers then that might be > helpful, but they can. > Ooops :-) OK, my proposal is narrowing to very simple one: what about triggering WARNINGs when user tries to access nonexistent element of array? -- Best regards, Nikolay
On 4/9/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Nikolay Samokhvalov" <samokhvalov@gmail.com> writes: > > As I can see here, when I ask for element that doesn't exist, the > > database returns NULL for me. Maybe it's well-known issue (and > > actually I understood this behaviour before), but strictly speaking it > > seems wrong for me: the database _knows_ that there is no element, so > > why NULL? > [...] > > AFAIR it's always been like that, so changing it seems exceedingly > likely to break some peoples' applications. It's not completely without > analogies in SQL, anyway: consider the behavior of INSERT when fewer > columns are provided than the table has. Pretending that elements > outside the stored range of the array are null is not all that different > from silently adding nulls to a row-to-be-stored. OK, I see. But if I try to INSERT to column that doesn't exist in the table, I have an error. Why pg's arrays are designed so that postgres doesn't produce errors for attempts to access nonexistent element of array? Why there is no simple sanity check (SELECT (ARRAY[6,8])[-1] -- works w/o an error)? I remember several cases when people (e.g. me :-) ) were spending some time trying to find an error in some pl/pgsql function and the reason lied in incorrect work with arrays (i.e. messages like "index is out of bounds" and "index cannot be negative number" would help, surely). -- Best regards, Nikolay
Nikolay Samokhvalov wrote: > On 4/10/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> "Nikolay Samokhvalov" <nikolay@samokhvalov.com> writes: >> > I remember several cases when people (e.g. me :-) ) were spending some >> > time trying to find an error in some pl/pgsql function and the reason >> > lied in incorrect work with arrays (i.e. messages like "index is out >> > of bounds" and "index cannot be negative number" would help, surely). >> >> Well, if indexes *couldn't* be negative numbers then that might be >> helpful, but they can. >> > Ooops :-) OK, my proposal is narrowing to very simple one: what about > triggering WARNINGs when user tries to access nonexistent element of > array? Please don't ;-) There are two sane options - return an error, or return NULL. Both are sensible, and different programming languages make different choices. The only reason for a WARNING would be a long-term plan to change the existing behaviour. But this will cause lots of pain, for no real gain, because no matter which behaviour you pick, there are always situations where the other would be more convenient. Just look at the mess PHP has created by altering fundamental aspects of the language (4.4 -> 5.0). greetings, Florian Pflug