| Lists: | pgsql-bugs |
|---|
| From: | David Newall <davidn-postgres(at)rebel(dot)net(dot)au> |
|---|---|
| To: | PostgreSQL bugs <pgsql-bugs(at)postgresql(dot)org> |
| Subject: | Can't join on null values |
| Date: | 2004-07-06 09:26:31 |
| Message-ID: | 1089105988.5002.12.camel@localhost.localdomain |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-bugs |
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.
| From: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
|---|---|
| To: | David Newall <davidn-postgres(at)rebel(dot)net(dot)au>, PostgreSQL bugs <pgsql-bugs(at)postgresql(dot)org> |
| Subject: | Re: Can't join on null values |
| Date: | 2004-07-07 19:25:21 |
| Message-ID: | 200407072125.21247.peter_e@gmx.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-bugs |
David Newall wrote:
> 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.
| From: | Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> |
|---|---|
| To: | David Newall <davidn-postgres(at)rebel(dot)net(dot)au> |
| Cc: | PostgreSQL bugs <pgsql-bugs(at)postgresql(dot)org> |
| Subject: | Re: Can't join on null values |
| Date: | 2004-07-07 19:38:39 |
| Message-ID: | Pine.LNX.4.44.0407072137470.10835-100000@kix.fsv.cvut.cz |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-bugs |
Hello,
its not error. Only SQL specific :-) NULL <> NULL
Regards
Pavel Stehule
On Tue, 6 Jul 2004, David Newall wrote:
> 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
>
| From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
|---|---|
| To: | David Newall <davidn-postgres(at)rebel(dot)net(dot)au> |
| Cc: | PostgreSQL bugs <pgsql-bugs(at)postgresql(dot)org> |
| Subject: | Re: Can't join on null values |
| Date: | 2004-07-07 19:41:37 |
| Message-ID: | 20040707123849.J58709@megazone.bigpanda.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-bugs |
On Tue, 6 Jul 2004, David Newall wrote:
> 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."
| From: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
|---|---|
| To: | Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz>, David Newall <davidn-postgres(at)rebel(dot)net(dot)au> |
| Cc: | PostgreSQL bugs <pgsql-bugs(at)postgresql(dot)org> |
| Subject: | Re: Can't join on null values |
| Date: | 2004-07-08 05:33:11 |
| Message-ID: | 200407080733.11798.peter_e@gmx.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-bugs |
Pavel Stehule wrote:
> its not error. Only SQL specific :-) NULL <> NULL
Au contraire, neither
NULL = NULL
nor
NULL <> NULL
is true.
| From: | davidn-postgres(at)rebel(dot)net(dot)au |
|---|---|
| To: | Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> |
| Cc: | PostgreSQL bugs <pgsql-bugs(at)postgresql(dot)org> |
| Subject: | Re: Can't join on null values |
| Date: | 2004-07-10 04:40:36 |
| Message-ID: | Pine.LNX.4.58.0407101353000.12278@localhost.localdomain |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-bugs |
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