Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5237

Issue With Matching Against MongoDB Documents With a Mixed Type (string and integer) Column

    Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 8.12.3
    • Fix Version/s: 10.2
    • Component/s: Misc. Connectors
    • Labels:
      None
    • Environment:

      TEIID Embedded Server 8.12.3
      Datasource DB - MongoDB 3.4
      Java 1.7.0_71

    • Steps to Reproduce:
      Hide

      There is a Mongo document with following JDBC schema

      CREATE FOREIGN TABLE MONGO_COLUMNS (
      MONGO_ID_COLUMN string(255) OPTIONS (NAMEINSOURCE '"MONGO_ID_COLUMN"', CHAR_OCTET_LENGTH 510, NATIVE_TYPE 'STRING'),
      ) OPTIONS (NAMEINSOURCE '"TEST"."MONGO_COLUMNS"', UPDATABLE TRUE);

      Document field MONGO_ID_COLUMN in mongodb contains mixed type values - both string and integer values:

      "id1", "id3" ,"id5", "id7", "id9", "11", "13", "15", "17", "19", "id21", "id23", "id25", "id27", "id29"

      If such mongodb instance is deploed as TEIID VDB, then SELECT statement for this table:

      SELECT g_0.MONGO_ID_COLUMN AS c_0 FROM mongodb_jdbc_ds_TEST.MONGO_COLUMNS AS g_0

      return right resultset, containing all 15 values described above.

      But if one issues following query

      SELECT g_0.MONGO_ID_COLUMN AS c_0 FROM mongodb_jdbc_ds_TEST.MONGO_COLUMNS AS g_0 WHERE g_0.MONGO_ID_COLUMN IN ('id1', '11', '13', '15')

      resultset contains only one value: "id1"
      OMITTING ALL INTEGER VALUES

      Show
      There is a Mongo document with following JDBC schema CREATE FOREIGN TABLE MONGO_COLUMNS ( MONGO_ID_COLUMN string(255) OPTIONS (NAMEINSOURCE '"MONGO_ID_COLUMN"', CHAR_OCTET_LENGTH 510, NATIVE_TYPE 'STRING'), ) OPTIONS (NAMEINSOURCE '"TEST"."MONGO_COLUMNS"', UPDATABLE TRUE); Document field MONGO_ID_COLUMN in mongodb contains mixed type values - both string and integer values: "id1", "id3" ,"id5", "id7", "id9", "11", "13", "15", "17", "19", "id21", "id23", "id25", "id27", "id29" If such mongodb instance is deploed as TEIID VDB, then SELECT statement for this table: SELECT g_0.MONGO_ID_COLUMN AS c_0 FROM mongodb_jdbc_ds_TEST.MONGO_COLUMNS AS g_0 return right resultset, containing all 15 values described above. But if one issues following query SELECT g_0.MONGO_ID_COLUMN AS c_0 FROM mongodb_jdbc_ds_TEST.MONGO_COLUMNS AS g_0 WHERE g_0.MONGO_ID_COLUMN IN ('id1', '11', '13', '15') resultset contains only one value: "id1" OMITTING ALL INTEGER VALUES

      Description

      Issue to search for values using IN operator against MongoDB document containing field values both string and integer. Resultset simply filters out INTEGER values that stored in that field

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                andriyg Andriy G
                Involved:
                Ramesh Reddy, Steven Hawkins
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: