[JBoss JIRA] (TEIID-4656) parseTimestamp issue with Oracle
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4656?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4656.
-----------------------------------
Resolution: Done
This behavior looks correct. Parsetime is not being pushed - in the pushdown query you can see nested converts, not the parsetime function. The translator handling of a string column to a time time is to_char with the expected time format. The exception should mean that the string column is not correctly formatted as a time value.
> parseTimestamp issue with Oracle
> --------------------------------
>
> Key: TEIID-4656
> URL: https://issues.jboss.org/browse/TEIID-4656
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector, Query Engine
> Affects Versions: 8.12
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.7.10.6_2, 9.2
>
>
> parseTimestamp is pushed as to_timestamp. However TO_TIMESTAMP is not generally valid unless the format string covers the entire string input value otherwise there is an exception about the picture ending.
> This means in general that we cannot pushdown the parseDate/Time/Timestamp operations to oracle as that is behavioral different with the java format routines, which will process just to the end of the format string.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 5 months
[JBoss JIRA] (TEIID-4639) Mongo translator - embeddable tables - NULL primary key of embedded table in result
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4639?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-4639:
-------------------------------------
The names were assumed to be same in parent and child tables which is incorrect. Corrected the the look up.
> Mongo translator - embeddable tables - NULL primary key of embedded table in result
> -----------------------------------------------------------------------------------
>
> Key: TEIID-4639
> URL: https://issues.jboss.org/browse/TEIID-4639
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Ramesh Reddy
> Labels: Beta1
> Fix For: 9.2
>
>
> Issuing query like \[1\] on embeddable tables scenario \[2\], in the result, primary key of embedded table is NULL \[3\].
> {code:sql|title=\[1\] Sample query}
> SELECT a.id AS a_id, a.company_id AS a_cid, b.id AS b_id FROM Person a INNER JOIN Company b IN a.company_id = b.id
> {code}
> {code:sql|title=\[2\] DDL}
> CREATE FOREIGN TABLE Person (
> id integer PRIMARY KEY,
> name varchar(25),
> company_id integer,
> FOREIGN KEY (company_id) REFERENCES Company (id)
> ) OPTIONS(UPDATABLE 'TRUE');
> CREATE FOREIGN TABLE Company (
> id integer PRIMARY KEY,
> name varchar(25)
> ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:EMBEDDABLE" 'TRUE');
> {code}
> *\[3\] Result*
> ||a_id||a_cid||b_id||
> |11|11|<null>|
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 5 months
[JBoss JIRA] (TEIID-4638) Mongo translator - nested embeddable tables - INNER JOIN is not symetric
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4638?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-4638.
---------------------------------
Resolution: Done
Labels: Beta1 (was: )
The column names were assumed to be "same" on both parent and child tables, which is incorrect. Corrected the lookup based on type of inclusion either it is MERGE vs EMBEDDABLE. Also corrected name resolution in case of nested Embeddable.
> Mongo translator - nested embeddable tables - INNER JOIN is not symetric
> ------------------------------------------------------------------------
>
> Key: TEIID-4638
> URL: https://issues.jboss.org/browse/TEIID-4638
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Ramesh Reddy
> Labels: Beta1
> Fix For: 9.2
>
>
> I have three tables bound together in nested EMBEDDABLE scenario \[1\]. I have three respective collections in Mongo \[2\]. Issuing SQL query with INNER JOIN on first two tables, result depends on order of tables in JOIN (i.e. _a JOIN b_ vs. _b JOIN a_) \[3, 4\].
> {code:sql|title=\[1\] DDL}
> CREATE FOREIGN TABLE Issue (
> id integer PRIMARY KEY,
> name varchar(25),
> reporter_id integer,
> FOREIGN KEY (reporter_id) REFERENCES Person (id)
> ) OPTIONS(UPDATABLE 'TRUE');
> CREATE FOREIGN TABLE Person (
> id integer PRIMARY KEY,
> name varchar(25),
> company_id integer,
> FOREIGN KEY (company_id) REFERENCES Company (id)
> ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:EMBEDDABLE" 'TRUE');
> CREATE FOREIGN TABLE Company (
> id integer PRIMARY KEY,
> name varchar(25)
> ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:EMBEDDABLE" 'TRUE');
> {code}
> {code:plain|title=\[2\] Mongo data}
> > db.Company.find()
> { "_id" : 11, "name" : "comp11" }
> > db.Person.find()
> { "_id" : 11, "name" : "p11", "company_id" : 11, "Company" : { "name" : "comp11" } }
> > db.Issue.find()
> { "_id" : 11, "name" : "i11", "reporter_id" : 11, "Person" : { "name" : "p11", "company_id" : 11, "Company" : { "name" : "comp11" } } }
> {code}
> \[3\]
> *Query 1*
> {code:sql}
> SELECT a.*, b.* FROM Company a INNER JOIN Person b ON a.id = b.company_id
> {code}
> *Result*
> ||id||name||id||name||company_id||
> \[4\]
> *Query 2*
> {code:sql}
> SELECT a.*, b.* FROM Person b INNER JOIN Company a ON a.id = b.company_id
> {code}
> *Result*
> ||id||name||id||name||company_id||
> |<null>|comp11|11|p11|11|
> *Note 1*
> Person <--> Issue relationship is not affected by this issue.
> *Note 2*
> In \[4\], first _id_ in result is _null_. I will report this as a separate issue.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 5 months
[JBoss JIRA] (TEIID-4645) Mongo translator - fail to initialize translator wiith property SupportsAggregatesCount
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4645?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-4645.
---------------------------------
Resolution: Done
Labels: Beta1 (was: )
The @TranslatorProperty annotation was placed on the "set" method, instead of "supports" method. Corrected and moved the annotation to correct method.
> Mongo translator - fail to initialize translator wiith property SupportsAggregatesCount
> ---------------------------------------------------------------------------------------
>
> Key: TEIID-4645
> URL: https://issues.jboss.org/browse/TEIID-4645
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Ramesh Reddy
> Labels: Beta1
> Fix For: 9.2
>
>
> If translator has *SupportsAggregatesCount* property set, initialization fails.
> {code:plain|title=Exception}
> 08:15:26,455 ERROR [org.jboss.msc.service.fail] (MSC service thread 1-4) MSC000001: Failed to start service jboss.teiid.vdb.mongo_enhancements.1: org.jboss.msc.service.StartException in service jboss.teiid.vdb.mongo_enhancements.1: TEIID50035 TEIID40028 No setSetSupportsAggregatesCount method found for translator property setSupportsAggregatesCount
> at org.teiid.jboss.VDBService.createConnectorManagers(VDBService.java:299)
> at org.teiid.jboss.VDBService.start(VDBService.java:129)
> at org.jboss.msc.service.ServiceControllerImpl$StartTask.startService(ServiceControllerImpl.java:1980) [jboss-msc-1.1.6.Final-redhat-1.jar:1.1.6.Final-redhat-1]
> at org.jboss.msc.service.ServiceControllerImpl$StartTask.run(ServiceControllerImpl.java:1913) [jboss-msc-1.1.6.Final-redhat-1.jar:1.1.6.Final-redhat-1]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0-internal]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0-internal]
> at java.lang.Thread.run(Thread.java:744) [rt.jar:1.8.0-internal]
> Caused by: org.teiid.core.TeiidException: TEIID40028 No setSetSupportsAggregatesCount method found for translator property setSupportsAggregatesCount
> at org.teiid.deployers.TranslatorUtil.getSetter(TranslatorUtil.java:207)
> at org.teiid.deployers.TranslatorUtil.injectProperties(TranslatorUtil.java:167)
> at org.teiid.deployers.TranslatorUtil.buildExecutionFactory(TranslatorUtil.java:123)
> at org.teiid.jboss.VDBService.getExecutionFactory(VDBService.java:320)
> at org.teiid.jboss.VDBService$5.getExecutionFactory(VDBService.java:292)
> at org.teiid.dqp.internal.datamgr.ConnectorManagerRepository.createConnectorManager(ConnectorManagerRepository.java:134)
> at org.teiid.dqp.internal.datamgr.ConnectorManagerRepository.createConnectorManagers(ConnectorManagerRepository.java:108)
> at org.teiid.jboss.VDBService.createConnectorManagers(VDBService.java:296)
> ... 6 more
> {code}
> {code:xml|title=Translator override}
> <translator name="mongodb-no-count-over" type="mongodb">
> <property name="SupportsAggregatesCount" value="false"/>
> </translator>
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 5 months
[JBoss JIRA] (TEIID-4639) Mongo translator - embeddable tables - NULL primary key of embedded table in result
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4639?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-4639.
---------------------------------
Resolution: Done
Labels: Beta1 (was: )
> Mongo translator - embeddable tables - NULL primary key of embedded table in result
> -----------------------------------------------------------------------------------
>
> Key: TEIID-4639
> URL: https://issues.jboss.org/browse/TEIID-4639
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Ramesh Reddy
> Labels: Beta1
> Fix For: 9.2
>
>
> Issuing query like \[1\] on embeddable tables scenario \[2\], in the result, primary key of embedded table is NULL \[3\].
> {code:sql|title=\[1\] Sample query}
> SELECT a.id AS a_id, a.company_id AS a_cid, b.id AS b_id FROM Person a INNER JOIN Company b IN a.company_id = b.id
> {code}
> {code:sql|title=\[2\] DDL}
> CREATE FOREIGN TABLE Person (
> id integer PRIMARY KEY,
> name varchar(25),
> company_id integer,
> FOREIGN KEY (company_id) REFERENCES Company (id)
> ) OPTIONS(UPDATABLE 'TRUE');
> CREATE FOREIGN TABLE Company (
> id integer PRIMARY KEY,
> name varchar(25)
> ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:EMBEDDABLE" 'TRUE');
> {code}
> *\[3\] Result*
> ||a_id||a_cid||b_id||
> |11|11|<null>|
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 5 months
[JBoss JIRA] (TEIID-4640) Mongo translator - embeddable tables - creation of collection fails
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4640?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-4640.
---------------------------------
Resolution: Done
Labels: Beta1 (was: )
Looks like "name space" property is removing when creating the index, so removed from the code.
> Mongo translator - embeddable tables - creation of collection fails
> -------------------------------------------------------------------
>
> Key: TEIID-4640
> URL: https://issues.jboss.org/browse/TEIID-4640
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Ramesh Reddy
> Labels: Beta1
> Fix For: 9.2
>
>
> I use Teiid to create collection in MongoDB (with first INSERT).
> This scenario works for simple tables. However, if I have embeddable tables \[1\], parent table creation fails \[2, 3\].
> {code:sql|title=\[1\] DDL}
> CREATE FOREIGN TABLE Person (
> id integer PRIMARY KEY,
> name varchar(25),
> company_id integer,
> FOREIGN KEY (company_id) REFERENCES Company (id)
> ) OPTIONS(UPDATABLE 'TRUE');
> CREATE FOREIGN TABLE Company (
> id integer PRIMARY KEY,
> name varchar(25)
> ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:EMBEDDABLE" 'TRUE');
> {code}
> {code:sql|title=\[2\] Insert queries}
> INSERT INTO Company (id, name) VALUES (11, 'comp11');
> INSERT INTO Person(id, name, company_id) VALUES (11, 'p11', 11);
> {code}
> {code:plain|title=\[3\] Exception}
> 09:23:19,306 WARN [org.teiid.PROCESSOR] (Worker33_QueryProcessorQueue527) TEIID30020 Processing exception for request 7MaF5Ey1GjEO.32 'TEIID30504 local: { "serverUsed" : "dvqe03.mw.lab.eng.bos.redhat.com:27017" , "spec" : { "name" : "FK0" , "ns" : "Person" , "key" : { "company_id" : 1}} , "ok" : 0.0 , "errmsg" : "namespace mismatch"}'. Originally TeiidProcessingException CommandResult.java:76.: org.teiid.core.TeiidProcessingException: TEIID30504 local: { "serverUsed" : "dvqe03.mw.lab.eng.bos.redhat.com:27017" , "spec" : { "name" : "FK0" , "ns" : "Person" , "key" : { "company_id" : 1}} , "ok" : 0.0 , "errmsg" : "namespace mismatch"}
> at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:401) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:161) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:391) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:472) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0-internal]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0-internal]
> at java.lang.Thread.run(Thread.java:744) [rt.jar:1.8.0-internal]
> Caused by: org.teiid.translator.TranslatorException: { "serverUsed" : "dvqe03.mw.lab.eng.bos.redhat.com:27017" , "spec" : { "name" : "FK0" , "ns" : "Person" , "key" : { "company_id" : 1}} , "ok" : 0.0 , "errmsg" : "namespace mismatch"}
> at org.teiid.translator.mongodb.MongoDBUpdateExecution.execute(MongoDBUpdateExecution.java:79)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:402) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at sun.reflect.GeneratedMethodAccessor91.invoke(Unknown Source) [:1.8.0-internal]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0-internal]
> at java.lang.reflect.Method.invoke(Method.java:483) [rt.jar:1.8.0-internal]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at com.sun.proxy.$Proxy47.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0-internal]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> ... 6 more
> Caused by: com.mongodb.CommandFailureException: { "serverUsed" : "dvqe03.mw.lab.eng.bos.redhat.com:27017" , "spec" : { "name" : "FK0" , "ns" : "Person" , "key" : { "company_id" : 1}} , "ok" : 0.0 , "errmsg" : "namespace mismatch"}
> at com.mongodb.CommandResult.getException(CommandResult.java:76)
> at com.mongodb.CommandResult.throwOnError(CommandResult.java:140)
> at com.mongodb.DBCollectionImpl.createIndex(DBCollectionImpl.java:399)
> at com.mongodb.DBCollection.createIndex(DBCollection.java:597)
> at org.teiid.translator.mongodb.MongoDBUpdateExecution.createIndex(MongoDBUpdateExecution.java:608)
> at org.teiid.translator.mongodb.MongoDBUpdateExecution.getCollection(MongoDBUpdateExecution.java:578)
> at org.teiid.translator.mongodb.MongoDBUpdateExecution.executeInternal(MongoDBUpdateExecution.java:85)
> at org.teiid.translator.mongodb.MongoDBUpdateExecution.execute(MongoDBUpdateExecution.java:77)
> ... 18 more
> {code}
> If collection in Mongo exists, no exception is thrown and all data are properly inserted.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 5 months
[JBoss JIRA] (TEIID-4638) Mongo translator - nested embeddable tables - INNER JOIN is not symetric
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4638?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-4638:
-------------------------------------
This I could not duplicate after fixing TEIID-4639
> Mongo translator - nested embeddable tables - INNER JOIN is not symetric
> ------------------------------------------------------------------------
>
> Key: TEIID-4638
> URL: https://issues.jboss.org/browse/TEIID-4638
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Ramesh Reddy
> Fix For: 9.2
>
>
> I have three tables bound together in nested EMBEDDABLE scenario \[1\]. I have three respective collections in Mongo \[2\]. Issuing SQL query with INNER JOIN on first two tables, result depends on order of tables in JOIN (i.e. _a JOIN b_ vs. _b JOIN a_) \[3, 4\].
> {code:sql|title=\[1\] DDL}
> CREATE FOREIGN TABLE Issue (
> id integer PRIMARY KEY,
> name varchar(25),
> reporter_id integer,
> FOREIGN KEY (reporter_id) REFERENCES Person (id)
> ) OPTIONS(UPDATABLE 'TRUE');
> CREATE FOREIGN TABLE Person (
> id integer PRIMARY KEY,
> name varchar(25),
> company_id integer,
> FOREIGN KEY (company_id) REFERENCES Company (id)
> ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:EMBEDDABLE" 'TRUE');
> CREATE FOREIGN TABLE Company (
> id integer PRIMARY KEY,
> name varchar(25)
> ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:EMBEDDABLE" 'TRUE');
> {code}
> {code:plain|title=\[2\] Mongo data}
> > db.Company.find()
> { "_id" : 11, "name" : "comp11" }
> > db.Person.find()
> { "_id" : 11, "name" : "p11", "company_id" : 11, "Company" : { "name" : "comp11" } }
> > db.Issue.find()
> { "_id" : 11, "name" : "i11", "reporter_id" : 11, "Person" : { "name" : "p11", "company_id" : 11, "Company" : { "name" : "comp11" } } }
> {code}
> \[3\]
> *Query 1*
> {code:sql}
> SELECT a.*, b.* FROM Company a INNER JOIN Person b ON a.id = b.company_id
> {code}
> *Result*
> ||id||name||id||name||company_id||
> \[4\]
> *Query 2*
> {code:sql}
> SELECT a.*, b.* FROM Person b INNER JOIN Company a ON a.id = b.company_id
> {code}
> *Result*
> ||id||name||id||name||company_id||
> |<null>|comp11|11|p11|11|
> *Note 1*
> Person <--> Issue relationship is not affected by this issue.
> *Note 2*
> In \[4\], first _id_ in result is _null_. I will report this as a separate issue.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 5 months
[JBoss JIRA] (TEIID-4638) Mongo translator - nested embeddable tables - INNER JOIN is not symetric
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4638?page=com.atlassian.jira.plugin... ]
Work on TEIID-4638 started by Ramesh Reddy.
-------------------------------------------
> Mongo translator - nested embeddable tables - INNER JOIN is not symetric
> ------------------------------------------------------------------------
>
> Key: TEIID-4638
> URL: https://issues.jboss.org/browse/TEIID-4638
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Ramesh Reddy
> Fix For: 9.2
>
>
> I have three tables bound together in nested EMBEDDABLE scenario \[1\]. I have three respective collections in Mongo \[2\]. Issuing SQL query with INNER JOIN on first two tables, result depends on order of tables in JOIN (i.e. _a JOIN b_ vs. _b JOIN a_) \[3, 4\].
> {code:sql|title=\[1\] DDL}
> CREATE FOREIGN TABLE Issue (
> id integer PRIMARY KEY,
> name varchar(25),
> reporter_id integer,
> FOREIGN KEY (reporter_id) REFERENCES Person (id)
> ) OPTIONS(UPDATABLE 'TRUE');
> CREATE FOREIGN TABLE Person (
> id integer PRIMARY KEY,
> name varchar(25),
> company_id integer,
> FOREIGN KEY (company_id) REFERENCES Company (id)
> ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:EMBEDDABLE" 'TRUE');
> CREATE FOREIGN TABLE Company (
> id integer PRIMARY KEY,
> name varchar(25)
> ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:EMBEDDABLE" 'TRUE');
> {code}
> {code:plain|title=\[2\] Mongo data}
> > db.Company.find()
> { "_id" : 11, "name" : "comp11" }
> > db.Person.find()
> { "_id" : 11, "name" : "p11", "company_id" : 11, "Company" : { "name" : "comp11" } }
> > db.Issue.find()
> { "_id" : 11, "name" : "i11", "reporter_id" : 11, "Person" : { "name" : "p11", "company_id" : 11, "Company" : { "name" : "comp11" } } }
> {code}
> \[3\]
> *Query 1*
> {code:sql}
> SELECT a.*, b.* FROM Company a INNER JOIN Person b ON a.id = b.company_id
> {code}
> *Result*
> ||id||name||id||name||company_id||
> \[4\]
> *Query 2*
> {code:sql}
> SELECT a.*, b.* FROM Person b INNER JOIN Company a ON a.id = b.company_id
> {code}
> *Result*
> ||id||name||id||name||company_id||
> |<null>|comp11|11|p11|11|
> *Note 1*
> Person <--> Issue relationship is not affected by this issue.
> *Note 2*
> In \[4\], first _id_ in result is _null_. I will report this as a separate issue.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 5 months