Tom Lane wrote:
Greg Stark [EMAIL PROTECTED] writes:
The SQL spec has some detailed discussion of some strange null behaviours.
BTW, Teodor Sigaev pointed out today that we are also doing array
comparisons (array_eq, array_cmp) wrong.
Seems to me like at least array_eq is correct (from SQL2003):
4.10 Collection types
Let A1 and A2 be arrays of EDT. A1 and A2 are identical if and only if
A1 and A2 have the same cardinality n and if, for all i in the range 1
(one) ≤ i ≤ n, the element at ordinal position i in A1 is *identical* to
the element at ordinal position i in A2.
9.8 Determination of identical values
Function
Determine whether two instances of values are identical, that is to say,
are occurrences of the same value.
General Rules
1) Let V1 and V2 be two values specified in an application of this
Subclause.
NOTE 211 — This Subclause is invoked implicitly wherever the word
identical is used of two values.
2) Case:
a) If V1 and V2 are both null, then V1 is *identical* to V2.
regression=# select array[1,null,3] = array[1,null,3];
?column?
--
t
(1 row)
In the recent extension to make arrays support NULL entries, I had made these
functions
treat NULL as greater than all non-nulls, per btree sort order.
But this seems wrong and also counter to spec: if an array comparison
finds a NULL before determining its result, it should return NULL,
same as a row comparison would do. The problem with this is that it
breaks btree indexing of array columns (... and I think btree indexing
of rowtypes has a problem too ...). btree wants to have a well-defined
ordering of any two non-null values. Ideas?
Interestingly, I see this in SQL2003:
9.12 Ordering operations
Function
Specify the prohibitions and restrictions by data type on operations
that involve ordering of data.
[...]
3) The declared type of an operand of an ordering operation shall not be
LOB-ordered, array-ordered, multisetordered, reference-ordered,
UDT-EC-ordered, or UDT-NC-ordered.
4.1.4 Comparison and ordering
[...]
— T is a collection type and the element type of T is S-ordered.
[...]
The notion of S-ordered is applied in the following definitions:
[...]
— A type T is array-ordered if T is ARR-ordered, where ARR is the set of
array types.
Does that say arrays can't be ordered? Or does it say that the ordering
follows the say rules as the array element type? If it is the latter,
aren't we already doing the right thing?
regression=# (select 1 union all select null union all select 3) order by 1;
?column?
--
1
3
(3 rows)
regression=# (select array[1,1] union all select array[1,null] union all
select array[1,3]) order by 1;
array
--
{1,1}
{1,3}
{1,NULL}
(3 rows)
A nearby issue is that the spec seems to want IS [NOT] DISTINCT FROM
to drill down into array and row values, ie, comparing arrays with
these functions needs to consider null entries as comparable instead
of forcing a null result. AFAICS this will require special-casing
array and row types in IS [NOT] DISTINCT FROM ... anyone see a better
way?
Yup, that's the way I read it too. Of course, that seems to work too:
regression=# select array[1,null,3] is distinct from array[1,null,3];
?column?
--
f
(1 row)
regression=# select array[1,null,3] is not distinct from array[1,null,3];
?column?
--
t
(1 row)
Hmm, did I miss a commit message since you posted this?
Joe
---(end of broadcast)---
TIP 1: 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
