Re: [BUGS] Can't join on null values

#1

David Newall wrote:

Quote:

PostgreSQL version: 7.4.3 (RPMs from ftp.au.postgresql.org)

Operating Sysem: Fedora Core 1

CREATE TABLE t1 (i INTEGER, j INTEGER);
INSERT INTO t1 VALUES (1, NULL);
CREATE TABLE t2 AS SELECT * FROM t1;
SELECT * FROM t1 JOIN t2 USING (i, j);
i | j
---+---
(0 rows)

I believe the one row, which is identically present in both table,
should be selected. The problem occurs because of the NULL value.

A join happens when two values are equal in the sense of the operator =.
But "NULL = NULL" is not true, so the behavior is correct.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#2

On Tue, 6 Jul 2004, David Newall wrote:

Quote:

PostgreSQL version: 7.4.3 (RPMs from ftp.au.postgresql.org)

Operating Sysem: Fedora Core 1

CREATE TABLE t1 (i INTEGER, j INTEGER);
INSERT INTO t1 VALUES (1, NULL);
CREATE TABLE t2 AS SELECT * FROM t1;
SELECT * FROM t1 JOIN t2 USING (i, j);
i | j
---+---
(0 rows)

I believe the one row, which is identically present in both table,
should be selected. The problem occurs because of the NULL value.

NULL is not equal to NULL so I don't think the values for j meet
the join condition "for which the corresponding join columns have equal
values."

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#3

Hello,

its not error. Only SQL specific :-) NULL <> NULL

Regards
Pavel Stehule

On Tue, 6 Jul 2004, David Newall wrote:

Quote:

PostgreSQL version: 7.4.3 (RPMs from ftp.au.postgresql.org)

Operating Sysem: Fedora Core 1

CREATE TABLE t1 (i INTEGER, j INTEGER);
INSERT INTO t1 VALUES (1, NULL);
CREATE TABLE t2 AS SELECT * FROM t1;
SELECT * FROM t1 JOIN t2 USING (i, j);
i | j
---+---
(0 rows)

I believe the one row, which is identically present in both table,
should be selected. The problem occurs because of the NULL value.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

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

http://archives.postgresql.org

#4

Pavel Stehule wrote:

Quote:

its not error. Only SQL specific :-) NULL <> NULL

Au contraire, neither

NULL = NULL

nor

NULL <> NULL

is true.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#5

People,

Thanks for your help with my problem with NULL values. Also, particular
thanks for a hint on where to find a copy of SQL-92 standard, something
I didn't already have. It was annoying to discover that UNIQUE didn't
have what I felt was the "obvious" meaning, but it doesn't and PostgreSQL
does operate correctly.

David

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] Can't join on null values

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] Can't join on null values - 07-07-2004 , 02:42 PM

Re: [BUGS] Can't join on null values - 07-07-2004 , 02:48 PM

Re: [BUGS] Can't join on null values - 07-07-2004 , 02:52 PM

Re: [BUGS] Can't join on null values - 07-08-2004 , 12:40 AM

Re: [BUGS] Can't join on null values - 07-16-2004 , 01:57 PM