Sub-query returning wrong data

[kashifzeeshan]

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

Sub Query did not return data on foreign table

SELECT empno, ename, ea.deptno FROM emp_fr_tbl ea
WHERE sal = (SELECT MAX(sal) FROM emp_fr_tbl eb 
WHERE eb.deptno = ea.deptno)
ORDER BY deptno;
 empno | ename | deptno 
-------+-------+--------
(0 rows)

Sub Query did return data on normal table

edb=# SELECT empno, ename, ea.deptno FROM emp_tmp ea
edb-# WHERE sal = (SELECT MAX(sal) FROM emp_tmp eb 
edb(# WHERE eb.deptno = ea.deptno)
edb-# ORDER BY deptno;
 empno | ename | deptno 
-------+-------+--------
  7839 | KING  |     10
  7788 | SCOTT |     20
  7902 | FORD  |     20
  7698 | BLAKE |     30
(4 rows)

edb=# 
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);

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)  },   
]);

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');

[vaibhavdalvi93]

Hi @kashifzeeshan,

Can you re-verify the reported issue from your end? If it's been resolved then can you close this ticket? Otherwise, please provide us more details.

[kashifzeeshan]

The issue is fixed.

SELECT empno, ename, ea.deptno FROM emp_fr_tbl ea WHERE sal = (SELECT MAX(sal) FROM emp_fr_tbl eb WHERE eb.deptno = ea.deptno) ORDER BY deptno; empno | ename | deptno -------+-------+-------- 7839 | KING | 10 7788 | SCOTT | 20 7902 | FORD | 20 7698 | BLAKE | 30 (4 rows)