[teiid-issues] [JBoss JIRA] (TEIID-4638) Mongo translator - nested embeddable tables - INNER JOIN is not symetric

[Ramesh Reddy (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-4638?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

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)