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.klaver@aklaver.com
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com