BUG #14733: unexpected query result

From: mtv(dot)spec(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14733: unexpected query result
Date: 2017-07-04 11:19:14
Message-ID: 20170704111914.27954.71162@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14733
Logged by: Timur Lastaev
Email address: mtv(dot)spec(at)gmail(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message xm 2017-07-04 12:58:23 2017-07-04_205352
Previous Message Pavel Tavoda 2017-07-04 10:59:46 Re: BUG #14729: Between operator is slow when same value used for low and high margin