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

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(dot)klaver(at)aklaver(dot)com
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com