psql12.3 + jdbc_fdw - return wrong query results by using OR

On 5/28/20 8:39 AM, emilu@encs.concordia.ca wrote:

Hello,

psql12.3 + jdbc_fdw(oracle18.x), tried:

[1]
select count(*)
from oracle_t1 as a
inner join local_t1 as b
on (a.c1 = b.c1);

return 100 records

[2]
select count(*)
from oracle_t1 as a
inner join local_t1 as b
on (a.c1 = b.c1
    OR
    a.c2 = b.c1
   );

return only 2 records

(no null values in both tables.)

May I know what may cause the error please?

I'm guessing you are seeing this:

https://www.postgresql.org/docs/12/sql-expressions.html

4.2.14. Expression Evaluation Rules

". Boolean expressions (AND/OR/NOT combinations) in those clauses can be
reorganized in any manner allowed by the laws of Boolean algebra."

It would help to see the EXPLAIN ANALYZE for the queries above.

Thanks a lot.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 5/28/20 9:59 AM, Ying Lu wrote:

Hello,

would help to see the EXPLAIN ANALYZE for the queries above.

And the EXPLAIN ANALYZE for the first query?

Also please include the entire query, for example:

EXPLAIN ANALYZE select count(*) from oracle_t1 as a inner join local_t1
as b on (a.c1 = b.c1 OR a.c2 = b.c1);

This is important because in below I see:

Filter: (yr= '2020'::text)

which I don't see in your original queries.

Please find the explain analyze info
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..736.49 rows=489 width=333) (actual time=313.495..1224.671 rows=9 loops=1)
Join Filter: ((a.c1 = b.c1) OR (a.c2 = b.c1))
Rows Removed by Join Filter: 2106
-> Seq Scan on local_t1 b (cost=0.00..1.49 rows=49 width=13) (actual time=0.016..0.029 rows=49 loops=1)
-> Foreign Scan on oracle_t1 a (cost=0.00..0.00 rows=1000 width=320) (actual time=3.445..24.977 rows=43 loops=49)
Filter: (yr= '2020'::text)
Rows Removed by Filter: 255
Planning Time: 0.532 ms
Execution Time: 1327.697 ms

Thanks.
________________________________________

psql12.3 + jdbc_fdw(oracle18.x), tried:
[1]
select count(*)
from oracle_t1 as a
inner join local_t1 as b
on (a.c1 = b.c1);

return 100 records

[2]
select count(*)
from oracle_t1 as a
inner join local_t1 as b
on (a.c1 = b.c1
OR
a.c2 = b.c1
);

return only 2 records

(no null values in both tables.)

May I know what may cause the error please?

I'm guessing you are seeing this:

https://www.postgresql.org/docs/12/sql-expressions.html

4.2.14. Expression Evaluation Rules

". Boolean expressions (AND/OR/NOT combinations) in those clauses can be
reorganized in any manner allowed by the laws of Boolean algebra."

It would help to see the EXPLAIN ANALYZE for the queries above.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

Hello,

Please find the info for both SQLs (removed yr)

For Q1:

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5.40..5.41 rows=1 width=8) (actual time=1267.001..1267.001 rows=1 loops=1)
-> Hash Join (cost=2.10..4.78 rows=245 width=0) (actual time=0.134..1265.840 rows=2650 loops=1)
Hash Cond: (a.c1 = b.c1)
-> Foreign Scan on oracle_t1 a (cost=0.00..0.00 rows=1000 width=32) (actual time=0.026..1257.823 rows=14625 loops=1)
-> Hash (cost=1.49..1.49 rows=49 width=5) (actual time=0.030..0.030 rows=49 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on local_t1 b (cost=0.00..1.49 rows=49 width=5) (actual time=0.014..0.021 rows=49 loops=1)
Planning Time: 0.178 ms
Execution Time: 1363.482 ms

For Q2:

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=737.71..737.72 rows=1 width=8) (actual time=1197.366..1197.366 rows=1 loops=1)
-> Nested Loop (cost=0.00..736.49 rows=489 width=0) (actual time=16.649..1197.292 rows=70 loops=1)
Join Filter: ((a.c1 = b.c1) OR (a.c2 = b.c1))
Rows Removed by Join Filter: 14555
-> Seq Scan on local_t1 b (cost=0.00..1.49 rows=49 width=5) (actual time=0.016..0.023 rows=49 loops=1)
-> Foreign Scan on oracle_t1 a (cost=0.00..0.00 rows=1000 width=64) (actual time=0.002..24.284 rows=298 loops=49)
Planning Time: 0.972 ms
Execution Time: 1299.896 ms

Thanks a lot.

Ying Lu <emi.lu@concordia.ca> writes:

For Q1:

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5.40..5.41 rows=1 width=8) (actual time=1267.001..1267.001 rows=1 loops=1)
-> Hash Join (cost=2.10..4.78 rows=245 width=0) (actual time=0.134..1265.840 rows=2650 loops=1)
Hash Cond: (a.c1 = b.c1)
-> Foreign Scan on oracle_t1 a (cost=0.00..0.00 rows=1000 width=32) (actual time=0.026..1257.823 rows=14625 loops=1)
-> Hash (cost=1.49..1.49 rows=49 width=5) (actual time=0.030..0.030 rows=49 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on local_t1 b (cost=0.00..1.49 rows=49 width=5) (actual time=0.014..0.021 rows=49 loops=1)
Planning Time: 0.178 ms
Execution Time: 1363.482 ms

For Q2:

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=737.71..737.72 rows=1 width=8) (actual time=1197.366..1197.366 rows=1 loops=1)
-> Nested Loop (cost=0.00..736.49 rows=489 width=0) (actual time=16.649..1197.292 rows=70 loops=1)
Join Filter: ((a.c1 = b.c1) OR (a.c2 = b.c1))
Rows Removed by Join Filter: 14555
-> Seq Scan on local_t1 b (cost=0.00..1.49 rows=49 width=5) (actual time=0.016..0.023 rows=49 loops=1)
-> Foreign Scan on oracle_t1 a (cost=0.00..0.00 rows=1000 width=64) (actual time=0.002..24.284 rows=298 loops=49)
Planning Time: 0.972 ms
Execution Time: 1299.896 ms

The numbers here are consistent with the theory that there are 14625 rows
in the foreign table, but when oracle_t1 is scanned on the inside of a
nest loop, the FDW returns all of them on the first scan and then forgets
to return any when rescanned. This'd be a bug in jdbc_fdw, and a
pretty bad one :-(. But you'd have to report it to the jdbc_fdw
author(s) --- the core Postgres project doesn't maintain that.

regards, tom lane

On Thu, 2020-05-28 at 11:39 -0400, emilu@encs.concordia.ca wrote:

psql12.3 + jdbc_fdw(oracle18.x), tried:

[got bad query results]

How about giving oracle_fdw a try?

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

psql12.3 + jdbc_fdw(oracle18.x), tried:

[got bad query results]

How about giving oracle_fdw a try?

I will try oracle_fdw then.

Thank you very much for everyone's help!