Re: BUG #14733: unexpected query result

On Tue, Jul 4, 2017 at 8:51 PM, bricklen <bricklen(at)gmail(dot)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