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

MongoDB: failed to translate to mongo query with boolean value expression

    XMLWordPrintable

Details

    • Hide

      Create database"test" and collection "grades" and here is the sample data:

      { "_id":

      { "$oid" : "525cec2972913d2ed302a03c" }

      ,
      "student_id": 0,
      "state": "CA",
      "name": "Doug",
      "score": 97,
      "grade": "A"}

      { "_id":

      { "$oid" : "525cec2972913d2ed302a03d" }

      ,
      "student_id": 1,
      "state": "FL",
      "name": "Sam",
      "score": 90,
      "grade": "A"}

      { "_id":

      { "$oid" : "525cec2972913d2ed302a03e" }

      ,
      "student_id": 2,
      "state": "NY",
      "name": "Alex",
      "score": 55,
      "grade": "F"}

      Create VDB with mongoDB connector:

      schemaText = "CREATE FOREIGN TABLE grades(state varchar(25), name varchar(25), score integer, grade varchar(5)) OPTIONS(UPDATABLE 'TRUE');"
      database ="test" />

      Run the following SQL with Teiid:

      select "grade",
      "name",
      "score",
      "state",
      "grade" as "test",
      ("name" = "state") as "name2"
      from "mongoDBDS"."grades"
      where "grade" = 'B'

      LOG:
      Processing NEW request:
      SELECT mongoDBDS.grades.grade, mongoDBDS.grades.name, mongoDBDS.grades.score, m
      ongoDBDS.grades.state, mongoDBDS.grades.name = mongoDBDS.grades.state FROM mongoDBDS.grades WHERE mongoDBDS.grades.grade = 'B'

      java.util.EmptyStackException
      at java.util.Stack.peek(Stack.java:102)
      at java.util.Stack.pop(Stack.java:84)
      at org.teiid.translator.mongodb.MongoDBSelectVisitor.visit(MongoDBSelectVisitor.java:150)
      at org.teiid.language.DerivedColumn.acceptVisitor(DerivedColumn.java:46)
      at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51)
      at org.teiid.translator.mongodb.MongoDBSelectVisitor.append(MongoDBSelectVisitor.java:94)
      at org.teiid.translator.mongodb.MongoDBSelectVisitor.append(MongoDBSelectVisitor.java:107)
      at org.teiid.translator.mongodb.MongoDBSelectVisitor.visit(MongoDBSelectVisitor.java:487)
      at org.teiid.language.Select.acceptVisitor(Select.java:103)
      at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51)
      at org.teiid.translator.mongodb.MongoDBQueryExecution.execute(MongoDBQueryExecution.java:65)
      at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:317)
      at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
      at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
      at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
      at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
      at java.util.concurrent.FutureTask.run(FutureTask.java:166)
      at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
      at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:272)
      at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
      at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
      at java.lang.Thread.run(Thread.java:722)

      However, if I run another similar query with group by, and it works.

      SQL:
      select "grade" as "test",
      "grade",
      ("name" = "state") as "name2",
      sum("score") as "sum_score"
      from "mongoDBDS"."grades"
      where "grade" = 'B'
      group by "grade", "grade", ("name" = "state")
      order by "grade", "test", "name2"

      LOG:
      Processing NEW request:
      SELECT mongoDBDS.grades.grade, mongoDBDS.grades.name, mongoDBDS.grades.state,
      mongoDBDS.grades.score FROM mongoDBDS.grades WHERE mongoDBDS.grades.grade = 'B'
      Obtained execution:
      SELECT grades.grade, grades.name, grades.state, grades.score FROM grades WHERE grades.grade = 'B'
      $match:

      { "grade" : "B"}

      $project:

      { "_m0" : "$grade" , "_m1" : "$name" , "_m2" : "$state" , "_m3" : "$score"}
      Show
      Create database"test" and collection "grades" and here is the sample data: { "_id": { "$oid" : "525cec2972913d2ed302a03c" } , "student_id": 0, "state": "CA", "name": "Doug", "score": 97, "grade": "A"} { "_id": { "$oid" : "525cec2972913d2ed302a03d" } , "student_id": 1, "state": "FL", "name": "Sam", "score": 90, "grade": "A"} { "_id": { "$oid" : "525cec2972913d2ed302a03e" } , "student_id": 2, "state": "NY", "name": "Alex", "score": 55, "grade": "F"} Create VDB with mongoDB connector: schemaText = "CREATE FOREIGN TABLE grades(state varchar(25), name varchar(25), score integer, grade varchar(5)) OPTIONS(UPDATABLE 'TRUE');" database ="test" /> Run the following SQL with Teiid: select "grade", "name", "score", "state", "grade" as "test", ("name" = "state") as "name2" from "mongoDBDS"."grades" where "grade" = 'B' LOG: Processing NEW request: SELECT mongoDBDS.grades.grade, mongoDBDS.grades.name, mongoDBDS.grades.score, m ongoDBDS.grades.state, mongoDBDS.grades.name = mongoDBDS.grades.state FROM mongoDBDS.grades WHERE mongoDBDS.grades.grade = 'B' java.util.EmptyStackException at java.util.Stack.peek(Stack.java:102) at java.util.Stack.pop(Stack.java:84) at org.teiid.translator.mongodb.MongoDBSelectVisitor.visit(MongoDBSelectVisitor.java:150) at org.teiid.language.DerivedColumn.acceptVisitor(DerivedColumn.java:46) at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51) at org.teiid.translator.mongodb.MongoDBSelectVisitor.append(MongoDBSelectVisitor.java:94) at org.teiid.translator.mongodb.MongoDBSelectVisitor.append(MongoDBSelectVisitor.java:107) at org.teiid.translator.mongodb.MongoDBSelectVisitor.visit(MongoDBSelectVisitor.java:487) at org.teiid.language.Select.acceptVisitor(Select.java:103) at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51) at org.teiid.translator.mongodb.MongoDBQueryExecution.execute(MongoDBQueryExecution.java:65) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:317) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) at java.util.concurrent.FutureTask.run(FutureTask.java:166) at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:272) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) at java.lang.Thread.run(Thread.java:722) However, if I run another similar query with group by, and it works. SQL: select "grade" as "test", "grade", ("name" = "state") as "name2", sum("score") as "sum_score" from "mongoDBDS"."grades" where "grade" = 'B' group by "grade", "grade", ("name" = "state") order by "grade", "test", "name2" LOG: Processing NEW request: SELECT mongoDBDS.grades.grade, mongoDBDS.grades.name, mongoDBDS.grades.state, mongoDBDS.grades.score FROM mongoDBDS.grades WHERE mongoDBDS.grades.grade = 'B' Obtained execution: SELECT grades.grade, grades.name, grades.state, grades.score FROM grades WHERE grades.grade = 'B' $match: { "grade" : "B"} $project: { "_m0" : "$grade" , "_m1" : "$name" , "_m2" : "$state" , "_m3" : "$score"}

    Description

      Fail to translate the following SQL to mongo query:

      select "grade",
      "name",
      "score",
      "state",
      "grade" as "test",
      ("name" = "state") as "name2"
      from "mongoDBDS"."grades"
      where "grade" = 'B'

      However, it works with the query contains group by and boolean value expression:

      elect "grade" as "test",
      "grade",
      ("name" = "state") as "name2",
      sum("score") as "sum_score"
      from "mongoDBDS"."grades"
      where "grade" = 'B'
      group by "grade", "grade", ("name" = "state")
      order by "grade", "test", "name2"

      Attachments

        Activity

          People

            rhn-engineering-rareddy Ramesh Reddy
            mchantibco Ivan Chan (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: