Issue with oracle_fdw on postgres 11.1 while it works perfectly fine on 10.6

[ThanushaJ]

Hi Laurenz,

I have an issue in fetching data from Oracle inPostgreSQL 11.1, whereas I am not facing any problem when I run the same query from PostgreSQL 10.6.

I am facing an issue whenever I'm using a column name twice in the query. I'm explaining with the example below:

Query executed on Postgres:

select distinct cp.name,
   case when ct.effective_date_end is null then 1 else 0 end is_current,
   cp.id AS profile_id_nk,
   ct.id AS cers_term_id_nk,
   coalesce(ct.effective_date_begin,to_date('19000101','YYYYMMDD')) AS effective_start_date,
   coalesce(ct.effective_date_end,to_date('20990101','YYYYMMDD')) AS effective_end_date
from cers_term ct,cers_profile cp where ct.cers_profile_id = cp.id;

Query passed to Oracle:

When executed from PostgreSQL 10.6:

SELECT /*01d8a3fa9935c329666b0efb498da80e*/
   r1."EFFECTIVE_DATE_END", r1."ID", r1."EFFECTIVE_DATE_BEGIN", r2."NAME", r2."ID"
FROM ("PIUPMCQA"."CERS_TERM" r1
         INNER JOIN "PIUPMCQA"."CERS_PROFILE" r2 ON (r1."CERS_PROFILE_ID" = r2."ID"))

When executed from PostgreSQL 11.1:

SELECT /*356c2adb91626bb97aa77908de2385e1*/
   r2."NAME", r1."EFFECTIVE_DATE_END", r2."ID", r1."ID", r1."EFFECTIVE_DATE_BEGIN", r1."EFFECTIVE_DATE_END"
FROM ("PIUPMCQA"."CERS_TERM" r1
         INNER JOIN "PIUPMCQA"."CERS_PROFILE" r2 ON (r1."CERS_PROFILE_ID" = r2."ID"))

---

I see a difference in the query that is passed from PostgreSQL 11.1, i.e. the same column is being selected more than once, which shouldn't be the case.

Considering the fact that oracle_fdw does not match columns by name, but by position: passing a column name more than once is causing issue in fetching data from oracle. Can you please help in identifying why the query is not getting converted appropriately when it is run from PostgreSQL 11.1?

Thanks,
Thanusha Jakkula

[laurenz]

I assume you are using oracle_fdw 2.1.0, right?

There has been a recent fix (839b125) that affects which columns are fetched from Oracle.

Could you try with current Git HEAD and see if the problem persists?

Also, what exactly are the problems you are experiencing?
Do you get an error message?

[ThanushaJ]

accidentally closed

[ThanushaJ]

Hi Laurenz,

Yes, I'm using oracle_fdw version 2.1.0 and I have tried with current GITHEAD, but the problem still exists.

I don't see any error message as such. It's a data issue. When I'm passing a query, inappropriate results are being displayed as explained initially.

[laurenz]

Ok, this looks like a bug then.

Can you provide CREATE TABLE statements for Oracle and CREATE FOREIGN TABLE statements for PostgreSQL (and maybe INSERT statements to demonstrate the bad results), so that I can reproduce the problem here? Otherwise it is hard to fix it.

[smanikandans]

Hi Laurenz,

We face this issue under this specific condition.

1. Join between two Oracle tables over FDW
2. Same column of a table is selected twice.

FDW Diagnosis

SELECT oracle_diag();
"oracle_fdw 2.1.0, PostgreSQL 11.1, Oracle client 12.1.0.2.0, Oracle server 12.1.0.2.0"

---

ORACLE table:

drop table test_postgres_fdw;

create table test_postgres_fdw (id number,
   name varchar2(100),
   description varchar2(1000),
   date_created date,
   date_updated date);

insert into test_postgres_fdw
   select rownum, 'test'|| rownum, 'test desc', sysdate, sysdate from dual
   connect by level <= 1000;

commit;

create table test1_postgres_fdw (id number,
   name varchar2(100),
   description varchar2(1000),
   date_created date,
   date_updated date);

insert into test1_postgres_fdw
   select rownum, 'test'|| rownum, 'test desc', sysdate, sysdate from dual
   connect by level <= 1000;

commit;

---

POSTGRES table:

IMPORT FOREIGN SCHEMA <Oracle_Schema_Name>
LIMIT TO  (test_postgres_fdw)
FROM SERVER <Oracle Foreign server name> INTO public;    

IMPORT FOREIGN SCHEMA <Oracle_Schema_Name>
LIMIT TO  (test1_postgres_fdw)
FROM SERVER <Oracle Foreign server name> INTO public;    

Querying Column once Returns correct data

select t1.id, t1.name , t2.name from test_postgres_fdw t1
   join test1_postgres_fdw t2 on t1.id = t2.id;

Querying Same Column twice Returns all rows empty

select t1.id, t1.name , t1.name, t2.name from test_postgres_fdw t1
   join test1_postgres_fdw t2 on t1.id = t2.id;

[laurenz]

Thanks! I will look at that.

[laurenz]

I could reproduce the problem, but only with oracle_fdw 2.1.0.

Git HEAD works fine, and a bisect shows that 839b125 fixes the problem. So this is a duplicate of #279.

I have adapted the change log entry so that it becomes easier to rediscover the bug.

You say that you can reproduce the problem with Get HEAD, which would contradict my findings. Can you try again to verify that?

[smanikandans]

Thanks Laurenz! We will retry the patch and update further.

[ThanushaJ]

Thank you Laurenz for the help. The issue has been resolved.