Wrong result set for query against Mongo and MSSql datasources
andriyg Jan 24, 2018 8:37 AMhello,
I have two datasorces: MongoDB and MS SQL Server. And trying to perform join across these datasources with TEIID
here is DDL generated for tables I'm trying to join
CREATE FOREIGN TABLE test_table_mssql (
id_column string(250) OPTIONS (NAMEINSOURCE '"id_column"', NATIVE_TYPE 'varchar'),
numeric_col integer OPTIONS (NAMEINSOURCE '"numeric_col"', NATIVE_TYPE 'int')
) OPTIONS (NAMEINSOURCE '"foodmart"."dbo"."test_table_mssql"', UPDATABLE TRUE, CARDINALITY 31);
CREATE FOREIGN TABLE MONGO_COLUMNS (
MONGO_ID_COLUMN string(255) OPTIONS (NAMEINSOURCE '"MONGO_ID_COLUMN"', CHAR_OCTET_LENGTH 510, NATIVE_TYPE 'STRING'),
"_ID" string(24) NOT NULL OPTIONS (NAMEINSOURCE '"_ID"', CHAR_OCTET_LENGTH 48, NATIVE_TYPE 'STRING'),
MONGO_NUMERIC_COLUMN integer OPTIONS (NAMEINSOURCE '"MONGO_NUMERIC_COLUMN"', NATIVE_TYPE 'INTEGER'),
CONSTRAINT SYS_PK_10147 PRIMARY KEY("_ID"),
CONSTRAINT SYS_IDX_10146 UNIQUE("_ID")
) OPTIONS (NAMEINSOURCE '"TEST"."MONGO_COLUMNS"', UPDATABLE TRUE);
Query to select MONGO_COLUMNS.MONGO_ID_COLUMNS
select "mongodb_jdbc_ds_TEST_MONGO_COLUMNS"."MONGO_ID_COLUMN" as "mongodb_jdbc_ds_TEST_MONGO_COLUMNS_MONGO_ID_COLUMN"
from "mongodb_jdbc_ds_TEST"."MONGO_COLUMNS" "mongodb_jdbc_ds_TEST_MONGO_COLUMNS"
limit 1000
returns 15 records: id1 id3 id5 id7 id9 11 13 15 17 19 id21 id23 id25 id27 id29
while query to select "sql_serv_native_jdbc_dbo_test_table_mssql"."id_column"
select "sql_serv_native_jdbc_dbo_test_table_mssql"."id_column" as "sql_serv_native_jdbc_dbo_test_table_mssql_id_column"
from "sql_serv_native_jdbc_dbo"."test_table_mssql" "sql_serv_native_jdbc_dbo_test_table_mssql"
limit 1000
returns 31 record: id99 id1 id2 id3 id4 id5 id6 id7 id8 id9 id10 11 12 13 14 15 16 17 18 19 id20 id21 id22 id23 id24 id25 id26 id27 id28 id29 id30
FINALLY query from both joined datasources
select "mongodb_jdbc_ds_TEST_MONGO_COLUMNS"."MONGO_ID_COLUMN" as "mongodb_jdbc_ds_TEST_MONGO_COLUMNS_MONGO_ID_COLUMN",
"sql_serv_native_jdbc_dbo_test_table_mssql"."id_column" as "sql_serv_native_jdbc_dbo_test_table_mssql_id_column"
from "mongodb_jdbc_ds_TEST"."MONGO_COLUMNS" "mongodb_jdbc_ds_TEST_MONGO_COLUMNS"
inner join "sql_serv_native_jdbc_dbo"."test_table_mssql" "sql_serv_native_jdbc_dbo_test_table_mssql" on (("mongodb_jdbc_ds_TEST_MONGO_COLUMNS"."MONGO_ID_COLUMN" = "sql_serv_native_jdbc_dbo_test_table_mssql"."id_column"))
limit 1000
returns only 10 records: id1 id3 id5 id7 id9 id21 id23 id25 id27 id29
skipping integral values: numbers like 11, 13, 15 etc, while it contains string values: id1, id3 etc
This issue is reproduced only when I try to perform join across MondDB and MS SQL Server datasources
I was NOT able to reproduce it for
1. Mysql to MongoDB join
2. Mysql to MS SQL join
3. MS Sql to MS Sql joins through teiid