[teiid-issues] [JBoss JIRA] (TEIID-2841) MongoDB: Wrong results returned with MERGE in ONE-1-ONE relation

[Ramesh Reddy (JIRA)]

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

Ramesh Reddy resolved TEIID-2841.
---------------------------------

        Labels: Alpha3 mongodb  (was: )
    Resolution: Done


Added filter to native query where it only returns the documents that has child document present to removed the <null> based row.

{code}
 {$match : {Address : {"$exists": "true"}}}
 {$project : ...}
{code}
                
> MongoDB: Wrong results returned with MERGE in ONE-1-ONE relation
> ----------------------------------------------------------------
>
>                 Key: TEIID-2841
>                 URL: https://issues.jboss.org/browse/TEIID-2841
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 8.4
>            Reporter: Ramesh Reddy
>            Assignee: Ramesh Reddy
>              Labels: mongodb, Alpha3
>             Fix For: 8.7
>
>
> If Schema is like 
> {code}
>             CREATE FOREIGN TABLE  Customer (
>                 CustomerId integer PRIMARY KEY,
>                 FirstName varchar(25),
>                 LastName varchar(25)
>             ) OPTIONS(UPDATABLE 'TRUE');
>              
>             CREATE FOREIGN TABLE Address (
>                 CustomerId integer PRIMARY KEY,
>                 Street varchar(50),
>                 City varchar(25),
>                 State varchar(25),
>                 Zipcode varchar(6),
>                 FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId)
>              ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:MERGE" 'Customer');
> {code}
> and we did inserts like
> {code}
> insert into Customer (customerId ,firstname,lastname) values ('1002','Syed','Iqbal');
> insert into Address (customerId,street,city,state,zipcode) values ('1002','123 Main St','Chicago','IL','60659');
> insert into Customer (customerId ,firstname,lastname) values ('1000','Ramesh','Reddy');
> {code}
> the data in mongo is in form 
> {code}
> {
> 	"Address" : {
> 		"Street" : "123 Main St",
> 		"City" : "Chicago",
> 		"State" : "IL",
> 		"Zipcode" : "60659",
> 		"_id" : DBRef("Customer", 1002)
> 	},
> 	"FirstName" : "Syed",
> 	"LastName" : "Iqbal",
> 	"Notes" : [ ],
> 	"_id" : 1002
> }
> { "_id" : 1000, "FirstName" : "Ramesh", "LastName" : "Reddy" }
> {code}
> and if you issue
> {code}
> SELECT * FROM Address
> {code}
> you will see
> ||CustomerId||Street||City||State||
> |1002|123 Main St|Chicago|IL|
> |<null>|<null>|<null>|<null>|

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira