Re: [BUGS] BUG #14733: unexpected query result

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