search

EnterpriseDB / mongo_fdw

PostgreSQL foreign data wrapper for MongoDB
GNU Lesser General Public License v3.0
326 stars 70 forks source link

Data Not Displayed by Foreign Table where Data-type mismatched on MongoDB to EPAS #107

Closed kashifzeeshan closed 2 years ago

kashifzeeshan commented 6 years ago

Data Not Displayed by Foreign Table where Data-type mismatched on MongoDB to EPAS.

In the below example the data is stored as integer in empno on MongoDB as shown here.


> db.emp.find( {} )
{ "_id" : ObjectId("5b6c0b534a4cc4abee0b01fe"), "empno" : 7369, "ename" : "SMITH", "job" : "CLERK", "mgr" : 7902, "hiredate" : ISODate("1980-12-17T00:00:00Z"), "sal" : 800.3, "comm" : 0, "deptno" : 20 }
{ "_id" : ObjectId("5b6c0b534a4cc4abee0b01ff"), "empno" : 7499, "ename" : "ALLEN", "job" : "SALESMAN", "mgr" : 7698, "hiredate" : ISODate("1981-02-20T00:00:00Z"), "sal" : 1600, "comm" : 300, "deptno" : 30 }
{ "_id" : ObjectId("5b6c0b534a4cc4abee0b0200"), "empno" : 7521, "ename" : "WARD", "job" : "SALESMAN", "mgr" : 7698, "hiredate" : ISODate("1981-02-22T00:00:00Z"), "sal" : 1250, "comm" : 500, "deptno" : 30 }
{ "_id" : ObjectId("5b6c0b534a4cc4abee0b0201"), "empno" : 7566, "ename" : "JONES", "job" : "MANAGER", "mgr" : 7839, "hiredate" : ISODate("1981-04-02T00:00:00Z"), "sal" : 2975, "comm" : 0, "deptno" : 20 }
{ "_id" : ObjectId("5b6c0b534a4cc4abee0b0202"), "empno" : 7654, "ename" : "MARTIN", "job" : "SALESMAN", "mgr" : 7698, "hiredate" : ISODate("1981-09-28T00:00:00Z"), "sal" : 1250.23, "comm" : 1400, "deptno" : 30 }
{ "_id" : ObjectId("5b6c0b534a4cc4abee0b0203"), "empno" : 7698, "ename" : "BLAKE", "job" : "MANAGER", "mgr" : 7839, "hiredate" : ISODate("1981-05-01T00:00:00Z"), "sal" : 2850, "comm" : 0, "deptno" : 30 }
{ "_id" : ObjectId("5b6c0b534a4cc4abee0b0204"), "empno" : 7782, "ename" : "CLARK", "job" : "MANAGER", "mgr" : 7839, "hiredate" : ISODate("1981-06-09T00:00:00Z"), "sal" : 2450.34, "comm" : 0, "deptno" : 10 }
{ "_id" : ObjectId("5b6c0b534a4cc4abee0b0205"), "empno" : 7788, "ename" : "SCOTT", "job" : "ANALYST", "mgr" : 7566, "hiredate" : ISODate("1987-04-19T00:00:00Z"), "sal" : 3000, "comm" : 0, "deptno" : 20 }
{ "_id" : ObjectId("5b6c0b534a4cc4abee0b0206"), "empno" : 7839, "ename" : "KING", "job" : "PRESIDENT", "mgr" : null, "hiredate" : ISODate("1981-11-17T00:00:00Z"), "sal" : 5000, "comm" : 0, "deptno" : 10 }
{ "_id" : ObjectId("5b6c0b534a4cc4abee0b0207"), "empno" : 7844, "ename" : "TURNER", "job" : "SALESMAN", "mgr" : 7698, "hiredate" : ISODate("1980-09-08T00:00:00Z"), "sal" : 1500, "comm" : 0, "deptno" : 30 }
{ "_id" : ObjectId("5b6c0b534a4cc4abee0b0208"), "empno" : 7876, "ename" : "ADAMS", "job" : "CLERK", "mgr" : 7788, "hiredate" : ISODate("1987-05-23T00:00:00Z"), "sal" : 1100, "comm" : 0, "deptno" : 20 }
{ "_id" : ObjectId("5b6c0b534a4cc4abee0b0209"), "empno" : 7900, "ename" : "JAMES", "job" : "CLERK", "mgr" : 7698, "hiredate" : ISODate("1981-12-03T00:00:00Z"), "sal" : 950, "comm" : 0, "deptno" : 30 }
{ "_id" : ObjectId("5b6c0b534a4cc4abee0b020a"), "empno" : 7902, "ename" : "FORD", "job" : "ANALYST", "mgr" : 7566, "hiredate" : ISODate("1981-12-03T00:00:00Z"), "sal" : 3000, "comm" : 0, "deptno" : 20 }
{ "_id" : ObjectId("5b6c0b534a4cc4abee0b020b"), "empno" : 7934, "ename" : "MILLER", "job" : "CLERK", "mgr" : 7782, "hiredate" : ISODate("1982-01-23T00:00:00Z"), "sal" : 1300, "comm" : 0, "deptno" : 10 }
>

The Foreign table is created with DECIMAL datatype for empno column, and when select statement is executed then wrong data is displayed.

-- Create Foreign Table.
CREATE FOREIGN TABLE emp_fr_tbl (
    _id             NAME,
    empno           DECIMAL,
    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');
-- Retrive Data from Foreign Table using SELECT Statement.
SELECT * FROM emp_fr_tbl;
           _id            | empno | ename  |    job    | mgr  |      hiredate       |   sal   | comm | deptno 
--------------------------+-------+--------+-----------+------+---------------------+---------+------+--------
 5b6c0b534a4cc4abee0b01fe |     0 | SMITH  | CLERK     | 7902 | 17/12/1980 00:00:00 |   800.3 |    0 |     20
 5b6c0b534a4cc4abee0b01ff |     0 | ALLEN  | SALESMAN  | 7698 | 20/02/1981 00:00:00 |    1600 |  300 |     30
 5b6c0b534a4cc4abee0b0200 |     0 | WARD   | SALESMAN  | 7698 | 22/02/1981 00:00:00 |    1250 |  500 |     30
 5b6c0b534a4cc4abee0b0201 |     0 | JONES  | MANAGER   | 7839 | 02/04/1981 00:00:00 |    2975 |    0 |     20
 5b6c0b534a4cc4abee0b0202 |     0 | MARTIN | SALESMAN  | 7698 | 28/09/1981 00:00:00 | 1250.23 | 1400 |     30
 5b6c0b534a4cc4abee0b0203 |     0 | BLAKE  | MANAGER   | 7839 | 01/05/1981 00:00:00 |    2850 |    0 |     30
 5b6c0b534a4cc4abee0b0204 |     0 | CLARK  | MANAGER   | 7839 | 09/06/1981 00:00:00 | 2450.34 |    0 |     10
 5b6c0b534a4cc4abee0b0205 |     0 | SCOTT  | ANALYST   | 7566 | 19/04/1987 00:00:00 |    3000 |    0 |     20
 5b6c0b534a4cc4abee0b0206 |     0 | KING   | PRESIDENT |      | 17/11/1981 00:00:00 |    5000 |    0 |     10
 5b6c0b534a4cc4abee0b0207 |     0 | TURNER | SALESMAN  | 7698 | 08/09/1980 00:00:00 |    1500 |    0 |     30
 5b6c0b534a4cc4abee0b0208 |     0 | ADAMS  | CLERK     | 7788 | 23/05/1987 00:00:00 |    1100 |    0 |     20
 5b6c0b534a4cc4abee0b0209 |     0 | JAMES  | CLERK     | 7698 | 03/12/1981 00:00:00 |     950 |    0 |     30
 5b6c0b534a4cc4abee0b020a |     0 | FORD   | ANALYST   | 7566 | 03/12/1981 00:00:00 |    3000 |    0 |     20
 5b6c0b534a4cc4abee0b020b |     0 | MILLER | CLERK     | 7782 | 23/01/1982 00:00:00 |    1300 |    0 |     10
(14 rows)

In some case e.g. when DATE is used then no data is displayed.

CREATE FOREIGN TABLE emp_fr_tbl (
    _id             NAME,
    empno           DATE,
    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');
SELECT * FROM emp_fr_tbl;
           _id            | empno | ename  |    job    | mgr  |      hiredate       |   sal   | comm | deptno 
--------------------------+-------+--------+-----------+------+---------------------+---------+------+--------
 5b6c0b534a4cc4abee0b01fe |       | SMITH  | CLERK     | 7902 | 17/12/1980 00:00:00 |   800.3 |    0 |     20
 5b6c0b534a4cc4abee0b01ff |       | ALLEN  | SALESMAN  | 7698 | 20/02/1981 00:00:00 |    1600 |  300 |     30
 5b6c0b534a4cc4abee0b0200 |       | WARD   | SALESMAN  | 7698 | 22/02/1981 00:00:00 |    1250 |  500 |     30
 5b6c0b534a4cc4abee0b0201 |       | JONES  | MANAGER   | 7839 | 02/04/1981 00:00:00 |    2975 |    0 |     20
 5b6c0b534a4cc4abee0b0202 |       | MARTIN | SALESMAN  | 7698 | 28/09/1981 00:00:00 | 1250.23 | 1400 |     30
 5b6c0b534a4cc4abee0b0203 |       | BLAKE  | MANAGER   | 7839 | 01/05/1981 00:00:00 |    2850 |    0 |     30
 5b6c0b534a4cc4abee0b0204 |       | CLARK  | MANAGER   | 7839 | 09/06/1981 00:00:00 | 2450.34 |    0 |     10
 5b6c0b534a4cc4abee0b0205 |       | SCOTT  | ANALYST   | 7566 | 19/04/1987 00:00:00 |    3000 |    0 |     20
 5b6c0b534a4cc4abee0b0206 |       | KING   | PRESIDENT |      | 17/11/1981 00:00:00 |    5000 |    0 |     10
 5b6c0b534a4cc4abee0b0207 |       | TURNER | SALESMAN  | 7698 | 08/09/1980 00:00:00 |    1500 |    0 |     30
 5b6c0b534a4cc4abee0b0208 |       | ADAMS  | CLERK     | 7788 | 23/05/1987 00:00:00 |    1100 |    0 |     20
 5b6c0b534a4cc4abee0b0209 |       | JAMES  | CLERK     | 7698 | 03/12/1981 00:00:00 |     950 |    0 |     30
 5b6c0b534a4cc4abee0b020a |       | FORD   | ANALYST   | 7566 | 03/12/1981 00:00:00 |    3000 |    0 |     20
 5b6c0b534a4cc4abee0b020b |       | MILLER | CLERK     | 7782 | 23/01/1982 00:00:00 |    1300 |    0 |     10
(14 rows)

Correct data-type is used then correct data is returned.

SELECT * FROM emp_fr_tbl;
           _id            | empno | ename  |    job    | mgr  |      hiredate       |   sal   | comm | deptno 
--------------------------+-------+--------+-----------+------+---------------------+---------+------+--------
 5b6c0b534a4cc4abee0b01fe |  7369 | SMITH  | CLERK     | 7902 | 17/12/1980 00:00:00 |   800.3 |    0 |     20
 5b6c0b534a4cc4abee0b01ff |  7499 | ALLEN  | SALESMAN  | 7698 | 20/02/1981 00:00:00 |    1600 |  300 |     30
 5b6c0b534a4cc4abee0b0200 |  7521 | WARD   | SALESMAN  | 7698 | 22/02/1981 00:00:00 |    1250 |  500 |     30
 5b6c0b534a4cc4abee0b0201 |  7566 | JONES  | MANAGER   | 7839 | 02/04/1981 00:00:00 |    2975 |    0 |     20
 5b6c0b534a4cc4abee0b0202 |  7654 | MARTIN | SALESMAN  | 7698 | 28/09/1981 00:00:00 | 1250.23 | 1400 |     30
 5b6c0b534a4cc4abee0b0203 |  7698 | BLAKE  | MANAGER   | 7839 | 01/05/1981 00:00:00 |    2850 |    0 |     30
 5b6c0b534a4cc4abee0b0204 |  7782 | CLARK  | MANAGER   | 7839 | 09/06/1981 00:00:00 | 2450.34 |    0 |     10
 5b6c0b534a4cc4abee0b0205 |  7788 | SCOTT  | ANALYST   | 7566 | 19/04/1987 00:00:00 |    3000 |    0 |     20
 5b6c0b534a4cc4abee0b0206 |  7839 | KING   | PRESIDENT |      | 17/11/1981 00:00:00 |    5000 |    0 |     10
 5b6c0b534a4cc4abee0b0207 |  7844 | TURNER | SALESMAN  | 7698 | 08/09/1980 00:00:00 |    1500 |    0 |     30
 5b6c0b534a4cc4abee0b0208 |  7876 | ADAMS  | CLERK     | 7788 | 23/05/1987 00:00:00 |    1100 |    0 |     20
 5b6c0b534a4cc4abee0b0209 |  7900 | JAMES  | CLERK     | 7698 | 03/12/1981 00:00:00 |     950 |    0 |     30
 5b6c0b534a4cc4abee0b020a |  7902 | FORD   | ANALYST   | 7566 | 03/12/1981 00:00:00 |    3000 |    0 |     20
 5b6c0b534a4cc4abee0b020b |  7934 | MILLER | CLERK     | 7782 | 23/01/1982 00:00:00 |    1300 |    0 |     10
(14 rows)
ibrarahmad commented 6 years ago

What you expect that, when value 7369 will pass to a data type DATE which is not valid date. I think behaviour is correct.

vaibhavdalvi93 commented 2 years ago

Hi @kashifzeeshan ,

AFAIK, when there is compatible data type of foreign table in Postgres and remote table in MongoDB, then only correct result can be guaranteed. I think, reported behaviour is correct. Can you please close the ticket if you're agree with our observation?

kashifzeeshan commented 2 years ago

The issue is fixed.

CREATE FOREIGN TABLE emp_fr_tbl ( _id NAME, empno DECIMAL, ename VARCHAR(10), job CHAR(9), mgr INTEGER, hiredate DATE, sal DECIMAL, comm INTEGER, deptno INTEGER ) SERVER mongo_server OPTIONS (database 'fdw_test', collection 'emp'); SELECT * FROM emp_fr_tbl; _id | empno | ename | job | mgr | hiredate | sal | comm | deptno --------------------------+-------+--------+-----------+------+--------------------+---------+------+-------- 623c652a337ed99fef52f071 | 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.23 | 0 | 20 623c652a337ed99fef52f072 | 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600 | 300 | 30 623c652a337ed99fef52f073 | 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250 | 500 | 30 623c652a337ed99fef52f074 | 7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.12 | 0 | 20 623c652a337ed99fef52f075 | 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250 | 1400 | 30 623c652a337ed99fef52f076 | 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850 | 0 | 30 623c652a337ed99fef52f077 | 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.45 | 0 | 10 623c652a337ed99fef52f078 | 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000 | 0 | 20 623c652a337ed99fef52f079 | 7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000 | 0 | 10 623c652a337ed99fef52f07a | 7844 | TURNER | SALESMAN | 7698 | 08-SEP-80 00:00:00 | 1500 | 0 | 30 623c652a337ed99fef52f07b | 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100 | 0 | 20 623c652a337ed99fef52f07c | 7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950 | 0 | 30 623c652a337ed99fef52f07d | 7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000 | 0 | 20 623c652a337ed99fef52f07e | 7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300 | 0 | 10 (14 rows)