Details
-
Bug
-
Resolution: Done
-
Major
-
8.12.8.6_3
Description
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].
[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');
[2] Mongo data
Unable to find source-code formatter for language: plain. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
> 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" } } }
[3]
Query 1
SELECT a.*, b.* FROM Company a INNER JOIN Person b ON a.id = b.company_id
Result
| id | name | id | name | company_id |
|---|
[4]
Query 2
SELECT a.*, b.* FROM Person b INNER JOIN Company a ON a.id = b.company_id
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.
Attachments
Issue Links
- relates to
-
-
- Resolved
-
