Обсуждение: [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

mtv.spec@gmail.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


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

On Tue, Jul 4, 2017 at 8:51 PM, bricklen <bricklen@gmail.com> wrote:
>
>>
>> ---------------------
>> | id | name | amount |
>> ---------------------
>> | 1 | John    |     1000 |
>> ---------------------
>> | 2 |            |      2000 |
>> ---------------------
>
>
> If you want that result try revising your query.
> select e.id, c.name, e.amount
> from test.e_order as e
> left join test.e_customer as c on c.id=e.e_customer_id;
>

I have tested both the queries in ORACLE XE as well as in PG and
results are same and as per my expectation.

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;

PG:id | name | amount
----+------+-------- 1 | John |   1000 2 | John |   2000 2 | Jane |   2000


ORACLE XE:
ID NAME  AMOUNT
---------- ---------- ----------
1 John    1000
2 Jane    2000
2 John    2000


Query:
select e.id, c.name, e.amount
from test.e_order as e
left join test.e_customer as c on c.id=e.e_customer_id;


PG:id | name | amount
----+------+-------- 1 | John |   1000 2 |      |   2000



ORACLE XE:
ID NAME  AMOUNT
---------- ---------- ----------
1 John    1000
2             2000

Note: I have tested on the latest head on the PG.

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com


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