Join returning wrong data

[kashifzeeshan]

The Join executed on the foreign table is not returning correct data whereas the same query executed on a normal table with the same data return correct data.

Join did not returning correct data on foreign table

SELECT d.deptno, t2.empno
FROM dept_fr_tbl d
  LEFT OUTER JOIN LATERAL (
    SELECT e.empno FROM emp_fr_tbl e WHERE d.deptno = e.deptno LIMIT 1
  ) t2 ON TRUE;
 deptno | empno 
--------+-------
     10 |      
     20 |      
     30 |      
     40 |      
(4 rows)

Join did return correct data on normal table

edb=# 
edb=# SELECT d.deptno, t2.empno
edb-# FROM dept_tmp d
edb-#   LEFT OUTER JOIN LATERAL (
edb(#     SELECT e.empno FROM emp_tmp e WHERE d.deptno = e.deptno LIMIT 1
edb(#   ) t2 ON TRUE;
 deptno | empno 
--------+-------
     10 |  7782
     20 |  7369
     30 |  7499
     40 |      
(4 rows)

edb=#

Normal Table Setup

CREATE TABLE emp_tmp (
    empno           INT,
    ename           VARCHAR(10) ,
    job             CHAR(9),
    mgr             INT,
    hiredate        DATE,
    sal             DECIMAL(10,5),
    comm            INT,
    deptno          SMALLINT
);

INSERT INTO emp_tmp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800.23,NULL,20);
INSERT INTO emp_tmp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300,30);
INSERT INTO emp_tmp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp_tmp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975.12,NULL,20);
INSERT INTO emp_tmp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp_tmp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp_tmp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450.45,NULL,10);
INSERT INTO emp_tmp VALUES (7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp_tmp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp_tmp VALUES (7844,'TURNER','SALESMAN',7698,'1980-09-08',1500,0,30);
INSERT INTO emp_tmp VALUES (7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp_tmp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp_tmp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp_tmp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

edb=# 
edb=# CREATE TABLE dept_tmp (
edb(#     deptno          INT primary key,
edb(#     dname           TEXT,
edb(#     loc             TEXT
edb(# );
CREATE TABLE
edb=# 
edb=# INSERT INTO dept_tmp VALUES(10,'ACCOUNTING','NEW YORK');
INSERT 0 1
edb=# INSERT INTO dept_tmp VALUES(20,'RESEARCH','DALLAS');
INSERT 0 1
edb=# INSERT INTO dept_tmp VALUES(30,'SALES','CHICAGO');
INSERT 0 1
edb=# INSERT INTO dept_tmp VALUES(40,'OPERATIONS','BOSTON');
INSERT 0 1
edb=# 
edb=# 
edb=# 
edb=# 

Foreign Table Setup

MongoDB Data Setup

db.emp.insertMany([
   {empno: NumberInt(7369), ename : "SMITH", job :"CLERK", mgr :NumberInt(7902) ,hiredate :ISODate("1980-12-17"), sal :800.300, comm :NumberInt(0), deptno :NumberInt(20) },
   {empno: NumberInt(7499), ename : "ALLEN", job :"SALESMAN", mgr :NumberInt(7698) ,hiredate :ISODate("1981-02-20"), sal :1600, comm :NumberInt(300), deptno :NumberInt(30) },
   {empno: NumberInt(7521), ename : "WARD", job :"SALESMAN", mgr :NumberInt(7698) ,hiredate :ISODate("1981-02-22"), sal :1250, comm :NumberInt(500), deptno :NumberInt(30)  },
   {empno: NumberInt(7566), ename : "JONES", job :"MANAGER", mgr :NumberInt(7839) ,hiredate :ISODate("1981-04-02"), sal :2975, comm :NumberInt(0), deptno :NumberInt(20)  },
   {empno: NumberInt(7654), ename : "MARTIN", job :"SALESMAN", mgr :NumberInt(7698) ,hiredate :ISODate("1981-09-28"), sal :1250.23, comm :NumberInt(1400), deptno :NumberInt(30)  },
   {empno: NumberInt(7698), ename : "BLAKE", job :"MANAGER", mgr :NumberInt(7839) ,hiredate :ISODate("1981-05-01"), sal :2850, comm :NumberInt(0), deptno :NumberInt(30)  },
   {empno: NumberInt(7782), ename : "CLARK", job :"MANAGER", mgr :NumberInt(7839) ,hiredate :ISODate("1981-06-09"), sal :2450.34, comm :NumberInt(0), deptno :NumberInt(10)  },
   {empno: NumberInt(7788), ename : "SCOTT", job :"ANALYST", mgr :NumberInt(7566) ,hiredate :ISODate("1987-04-19"), sal :3000, comm :NumberInt(0), deptno :NumberInt(20)  },
   {empno: NumberInt(7839), ename : "KING", job :"PRESIDENT", mgr :null ,hiredate :ISODate("1981-11-17"), sal :5000, comm :NumberInt(0), deptno :NumberInt(10)  },
   {empno: NumberInt(7844), ename : "TURNER", job :"SALESMAN", mgr :NumberInt(7698) ,hiredate :ISODate("1980-09-08"), sal :1500, comm :NumberInt(0), deptno :NumberInt(30)  },
   {empno: NumberInt(7876), ename : "ADAMS", job :"CLERK", mgr :NumberInt(7788) ,hiredate :ISODate("1987-05-23"), sal :1100, comm :NumberInt(0), deptno :NumberInt(20)  },
   {empno: NumberInt(7900), ename : "JAMES", job :"CLERK", mgr :NumberInt(7698) ,hiredate :ISODate("1981-12-03"), sal :950.00, comm :NumberInt(0), deptno :NumberInt(30)  },
   {empno: NumberInt(7902), ename : "FORD", job :"ANALYST", mgr :NumberInt(7566) ,hiredate :ISODate("1981-12-03"), sal :3000, comm :NumberInt(0), deptno :NumberInt(20) },
   {empno: NumberInt(7934), ename : "MILLER", job :"CLERK", mgr :NumberInt(7782) ,hiredate :ISODate("1982-01-23"), sal :1300, comm :NumberInt(0), deptno :NumberInt(10)  },   
]);

db.dept.insertMany([
   {deptno : NumberInt(10), dname : "ACCOUNTING", loc :"NEW YORK" },
   {deptno: NumberInt(20), dname : "RESEARCH", loc :"DALLAS" },
   {deptno: NumberInt(30), dname : "SALES", loc :"CHICAGO"  },
   {deptno: NumberInt(40), dname : "OPERATIONS", loc :"BOSTON"  }
]);

db.dept.find( {} )

EPAS10 setup

-- Create Foreign Table.
CREATE FOREIGN TABLE emp_fr_tbl (
    _id             NAME,
    empno           INTEGER,
    ename           VARCHAR(10),
    job             CHAR(9),
    mgr             INTEGER,
    hiredate        DATE,
    sal             DECIMAL,
    comm            INTEGER,
    deptno          INTEGER
)
SERVER mongo_server OPTIONS (database 'mysql_test', collection 'emp');
CREATE FOREIGN TABLE dept_fr_tbl (
    _id             NAME,
    deptno          INTEGER,
    dname           VARCHAR(14),
    loc             VARCHAR(13)
)
SERVER mongo_server OPTIONS (database 'mysql_test', collection 'dept');

[kashifzeeshan]

The isuse is fixed.

SELECT d.deptno, t2.empno FROM dept_fr_tbl d LEFT OUTER JOIN LATERAL ( SELECT e.empno FROM emp_fr_tbl e WHERE d.deptno = e.deptno LIMIT 1 ) t2 ON TRUE; deptno | empno --------+------- 10 | 7782 20 | 7369 30 | 7499 40 |
(4 rows)