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.
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 |