Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[]

2005-06-27 Thread Bruce Momjian

OK, what is the TODO item text?

---

Joe Conway wrote:
 Bruce Momjian wrote:
  Is this a TODO item?
  
 
 Probably. I posted some questions regarding whether or not to break 
 backward compatiblity, and received no replies. In the meanwhile, I've 
 been doing a major system integration in Korea for the last 2 weeks, and 
 won't get back to home, or to anything like a reasonably normal schedule 
 until after July 2. I doubt I'll have time to do much between now and 
 feature freeze.
 
 Joe
 
 ---(end of broadcast)---
 TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[]

2005-06-26 Thread Joe Conway

Bruce Momjian wrote:

Is this a TODO item?



Probably. I posted some questions regarding whether or not to break 
backward compatiblity, and received no replies. In the meanwhile, I've 
been doing a major system integration in Korea for the last 2 weeks, and 
won't get back to home, or to anything like a reasonably normal schedule 
until after July 2. I doubt I'll have time to do much between now and 
feature freeze.


Joe

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[]

2005-06-24 Thread Bruce Momjian

Is this a TODO item?

---

Markus Bertheau ? wrote:
 ? ???, 06/06/2005 ? 08:58 -0700, Joe Conway ?:
  Joe Conway wrote:
   Actually, consistent with my last post, I think array_upper() on a 
   zero-element array should return NULL. A zero-element array has a 
   defined lower bound, but its upper bound is not zero -- it is really 
   undefined.
  
  Just to clarify my response, this is what I propose:
  
  regression=# select array_upper('[2][1:]={{},{}}'::int[],1);
array_upper
  -
  2
  (1 row)
  
  regression=# select array_upper('[2][1:]={{},{}}'::int[],2) IS NULL;
?column?
  --
t
  (1 row)
 
 Hmm, this gets really complicated and inconsistent. Complicated means
 unusable. What about modifying the dimension syntax such that the second
 number means number of elements instead of upper bound? That particular
 problem would go away then, and array_upper('[0:0]={}'::int[]) can
 return the correct 0 then.
 
 What I'm actually worrying about is that array_upper(array(select 1
 where false)) returns 0.
 
 An option would be to drop the possibility to let the array start at
 another index than 0. I don't know why it was decided to do that in the
 first place. It seems a rather odd feature to me.
 
 Markus
 -- 
 Markus Bertheau ? [EMAIL PROTECTED]
 
 
 ---(end of broadcast)---
 TIP 3: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly
 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

2005-06-06 Thread Markus Bertheau ☭
 , 24/05/2005  00:06 -0400, Tom Lane :
 Joe Conway [EMAIL PROTECTED] writes:
  Markus Bertheau wrote:
  why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
  ARRAY[] resp. '{}'?
 
  Why would you expect an empty array instead of a NULL?
 
 I think he's got a good point, actually.  We document the ARRAY-with-
 parens-around-a-SELECT syntax as
 
   The resulting one-dimensional array will have an element for
   each row in the subquery result, with an element type matching
   that of the subquery's output column.
 
 To me, that implies that a subquery result of no rows generates a
 one-dimensional array of no elements, not a null array.

By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
NULL.

Markus

-- 
Markus Bertheau  [EMAIL PROTECTED]


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

2005-06-06 Thread Markus Bertheau ☭
 , 06/06/2005  10:44 -0400, Tom Lane :
 Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] writes:
  By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
  NULL.
 
 No, that doesn't follow ... we've traditionally considered '{}' to
 denote a zero-dimensional array.

But array_upper(ARRAY(SELECT 1 WHERE FALSE), 1) should return 0,
and array_upper(ARRAY[], 1), when the ARRAY[] syntax is supported,
should return 0.

Do I get that right?

Markus

-- 
Markus Bertheau  [EMAIL PROTECTED]


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

2005-06-06 Thread Tom Lane
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] writes:
 By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
 NULL.

No, that doesn't follow ... we've traditionally considered '{}' to
denote a zero-dimensional array.  A 1-D array of no elements is
'[1:0]={}', just as Joe shows ... or at least it would be except
for an overenthusiastic error check:

regression=# select '[1:0]={}' :: int[];
ERROR:  upper bound cannot be less than lower bound

I think this should be a legal boundary case.  In general, it should be
possible to form zero-size arrays of any number of dimensions.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

2005-06-06 Thread Joe Conway

Tom Lane wrote:

Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] writes:


By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
NULL.


No, that doesn't follow ... we've traditionally considered '{}' to
denote a zero-dimensional array.  A 1-D array of no elements is
'[1:0]={}', just as Joe shows ... or at least it would be except
for an overenthusiastic error check:

regression=# select '[1:0]={}' :: int[];
ERROR:  upper bound cannot be less than lower bound

I think this should be a legal boundary case.  In general, it should be
possible to form zero-size arrays of any number of dimensions.



I've been playing with exactly this over the weekend. Of course, as 
usual, the devil is in the details. For instance, using the above 
notation, how would I specify a zero-element 1D array starting at a 
lower bound index of 0? The answer following the above pattern would be:


  select '[0:-1]={}'::int[];

You could not use '[0:0]={}'::int[], because that would be a one-element 
array. I propose the following instead:


regression=# select '[1:]={}' :: int[];
 int4
--
 {}
(1 row)

regression=# select array_dims('[1:]={}' :: int[]);
 array_dims

 [1:]
(1 row)

In other words, an missing upper bound indicates zero elements.

Now the next question; what does a 2D zero-element array look like? I 
think this makes sense:


regression=# select '[1:2][1:]={{},{}}'::int[];
 int4
--
 {}
(1 row)

Except (I think) array_out() should probably output something closer to 
the input literal. Any thoughts on this?


Joe

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

2005-06-06 Thread Joe Conway

Markus Bertheau  wrote:

 , 06/06/2005  10:44 -0400, Tom Lane :

Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] writes:


By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
NULL.


No, that doesn't follow ... we've traditionally considered '{}' to
denote a zero-dimensional array.


But array_upper(ARRAY(SELECT 1 WHERE FALSE), 1) should return 0,
and array_upper(ARRAY[], 1), when the ARRAY[] syntax is supported,
should return 0.



Actually, consistent with my last post, I think array_upper() on a 
zero-element array should return NULL. A zero-element array has a 
defined lower bound, but its upper bound is not zero -- it is really 
undefined.


Joe

---(end of broadcast)---
TIP 6: Have you searched our list archives?

  http://archives.postgresql.org


Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp.

2005-06-06 Thread Joe Conway

Joe Conway wrote:
Actually, consistent with my last post, I think array_upper() on a 
zero-element array should return NULL. A zero-element array has a 
defined lower bound, but its upper bound is not zero -- it is really 
undefined.


Just to clarify my response, this is what I propose:

regression=# select array_upper('[2][1:]={{},{}}'::int[],1);
 array_upper
-
   2
(1 row)

regression=# select array_upper('[2][1:]={{},{}}'::int[],2) IS NULL;
 ?column?
--
 t
(1 row)


Joe

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match


Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[]

2005-06-06 Thread Markus Bertheau ☭
 , 06/06/2005  08:58 -0700, Joe Conway :
 Joe Conway wrote:
  Actually, consistent with my last post, I think array_upper() on a 
  zero-element array should return NULL. A zero-element array has a 
  defined lower bound, but its upper bound is not zero -- it is really 
  undefined.
 
 Just to clarify my response, this is what I propose:
 
 regression=# select array_upper('[2][1:]={{},{}}'::int[],1);
   array_upper
 -
 2
 (1 row)
 
 regression=# select array_upper('[2][1:]={{},{}}'::int[],2) IS NULL;
   ?column?
 --
   t
 (1 row)

Hmm, this gets really complicated and inconsistent. Complicated means
unusable. What about modifying the dimension syntax such that the second
number means number of elements instead of upper bound? That particular
problem would go away then, and array_upper('[0:0]={}'::int[]) can
return the correct 0 then.

What I'm actually worrying about is that array_upper(array(select 1
where false)) returns 0.

An option would be to drop the possibility to let the array start at
another index than 0. I don't know why it was decided to do that in the
first place. It seems a rather odd feature to me.

Markus
-- 
Markus Bertheau  [EMAIL PROTECTED]


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp.

2005-06-06 Thread Joe Conway

Markus Bertheau  wrote:

Hmm, this gets really complicated and inconsistent. Complicated means
unusable. What about modifying the dimension syntax such that the second
number means number of elements instead of upper bound? That particular
problem would go away then, and array_upper('[0:0]={}'::int[]) can
return the correct 0 then.

What I'm actually worrying about is that array_upper(array(select 1
where false)) returns 0.

An option would be to drop the possibility to let the array start at
another index than 0. I don't know why it was decided to do that in the
first place. It seems a rather odd feature to me.



Actually I like both of these ideas, and have advocated the second one 
myself before. But it isn't backward compatible -- anyone else have an 
opinion? SQL2003 actually specifies that an array *should* start at 1:


4.10.2 Arrays
An array is a collection A in which each element is associated with 
exactly one ordinal position in A. If n is the cardinality of A, then 
the ordinal position p of an element is an integer in the range 1 (one) 
 p  n.



Joe



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match


Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

2005-05-24 Thread Markus Bertheau
Dnia 24-05-2005, wto o godzinie 00:06 -0400, Tom Lane napisa(a):
 Joe Conway [EMAIL PROTECTED] writes:
  Markus Bertheau wrote:
  why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
  ARRAY[] resp. '{}'?
 
  Why would you expect an empty array instead of a NULL?
 
 I think he's got a good point, actually.  We document the ARRAY-with-
 parens-around-a-SELECT syntax as
 
   The resulting one-dimensional array will have an element for
   each row in the subquery result, with an element type matching
   that of the subquery's output column.
 
 To me, that implies that a subquery result of no rows generates a
 one-dimensional array of no elements, not a null array.
 
 The point Markus is complaining about seems like it should
 be easily fixable.

Great. Does this belong on the TODO?

-- 
Markus Bertheau [EMAIL PROTECTED]


signature.asc
Description: This is a digitally signed message part