Re: BUG #14733: unexpected query result

mtv(dot)spec(at)gmail(dot)com writes:
> Table 1 - Customer:

> CREATE TABLE test.e_customer (
> id SERIAL PRIMARY KEY,
> name VARCHAR NOT NULL
> );

> Data:

> INSERT INTO test.e_customer (name)
> VALUES ('John'), ('Jane');

> Table 2 - Order:

> CREATE TABLE test.e_order (
> id SERIAL PRIMARY KEY,
> e_customer_id INTEGER REFERENCES customer (id),
> amount NUMERIC NOT NULL
> );

This example fails right here for me, because there's no table named
"customer". When I change the clause to REFERENCES test.e_customer (id)
then it works, and I get the output you show for "Postgres". It looks
correct to me: given the way you wrote the WHERE clause, the row in
e_order with a null e_customer_id will join with every row in e_customer.
What you show as desired output could be achieved with a left join,
but this isn't one.

=# SELECT
o.id,
c.name,
o.amount
FROM
test.e_order o LEFT JOIN
test.e_customer c
ON o.e_customer_id = c.id
ORDER BY o.id ASC;
id | name | amount
----+------+--------
1 | John | 1000
2 | | 2000
(2 rows)

> Why PostgreSQL and Oracle return different results?

You'd have to take that up with an Oracle person. But I wonder if
you weren't confusing yourself with tables of slightly different
names, so that you weren't actually testing the same case in both
DBMSes. Or maybe you were using Oracle's nonstandard left join syntax
and mistranslating that to Postgres.

regards, tom lane