Skip site navigation (1) Skip section navigation (2)

Nulls, arrays, records, IS NULL, IS DISTINCT FROM

Subject: Nulls, arrays, records, IS NULL, IS DISTINCT FROM
Date: 2006-09-29 16:53:19
Message-ID: (view raw or whole thread)
Lists: pgsql-hackers
Following up yesterday's discussion, I've been studying the SQL spec for

The rules for <distinct predicate> make it clear that you are supposed
to "drill down" into row and array values to determine distinctness.
SQL99 has

            a) If the declared type of X or Y is an array type, then "X IS
              DISTINCT FROM Y" is effectively computed as follows:

              i) Let NX be the number of elements in X; let NY be the number
                 of elements in Y.

             ii) Let EX(i) be the i-th element of X; let EY(i) be the i-th
                 element of Y.

            iii) Case:

                 1) If NX is not equal to NY, then "X IS DISTINCT FROM Y" is

                 2) If NX equals zero and NY equals zero, then "X IS
                   DISTINCT FROM Y" is false.

                 3) If "EX(i) IS DISTINCT FROM EY(i)" is false for all i
                   between 1 (one) and NX, then "X IS DISTINCT FROM Y" is

                 4) Otherwise, "X IS DISTINCT FROM Y" is true.

SQL2003 has completely rewritten the text but the meaning seems the
"if the array bounds are not identical then the arrays are distinct".
We are clearly getting this wrong since the introduction of nulls in
arrays, but I'll go fix that.

Similarly, given two row expressions, distinctness is determined
are distinct.  We are currently getting this correct only for
the case of parse-time ROW expressions, ie
This is pretty much analogous to the case Teodor noted yesterday
to handle situations where a row value is coming from a function
or some other source that's not disassemblable at parse time.

prescribes drilling down into a rowtype value to examine the individual
spec says
But it's very unclear whether
examining its contents.

Comments?  Does anyone see any guidance in the spec?  If there is none,
which behavior do we think is most useful/consistent?

			regards, tom lane


pgsql-hackers by date

Next:From: Joshua D. DrakeDate: 2006-09-29 16:56:40
Subject: Re: Win32 hard crash problem
Previous:From: Andreas PflugDate: 2006-09-29 16:49:17
Subject: Re: Backup and restore through JDBC

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group