[BUGS] BUG #14733: unexpected query result

The following bug has been logged on the website:

Bug reference:      14733
Logged by:          Timur Lastaev
Email address:      mtv.spec@gmail.com
PostgreSQL version: 9.6.3
Operating system:   Mac OS
Description:

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
);

Data:

INSERT INTO test.e_order (e_customer_id, amount)
VALUES (1, 1000);

INSERT INTO test.e_order (amount)
VALUES (2000);

Query:

SELECT o.id, c.name, o.amount
FROM test.e_order o, test.e_customer c
WHERE o.e_customer_id = c.id OR
o.e_customer_id IS NULL
ORDER BY o.id ASC;

Expected result:

---------------------
| id | name | amount |
---------------------
| 1 | John    |     1000 |
---------------------
| 2 |            |      2000 |
---------------------

PostgreSQL result:

---------------------
| id | name | amount |
---------------------
| 1 | John    |     1000 |
---------------------
| 1 | Jane    |     2000 |
---------------------
| 2 | John   |     2000 |

Oracle result:

---------------------
| id | name | amount |
---------------------
| 1 | John    |     1000 |
---------------------
| 2 |            |      2000 |
---------------------

Why PostgreSQL and Oracle return different results?
And which result is "correct"?

Thank you for any response.


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs