[teiid-issues] [JBoss JIRA] (TEIID-4534) Querying 1-to-many relationship in JDG data source may return the wrong results

[Van Halbert (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4534?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13379146#comment-13379146 ] 

Van Halbert commented on TEIID-4534:
------------------------------------

To ensure the correct number of rows are returned, the logic will be changed so that the criteria is applied twice, first used in the DSL query to JDG, and then the second time the criteria will be applied on the child objects before the rows are created for the resultset.

> Querying 1-to-many relationship in JDG data source may return the wrong results
> -------------------------------------------------------------------------------
>
>                 Key: TEIID-4534
>                 URL: https://issues.jboss.org/browse/TEIID-4534
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDG Connector
>    Affects Versions: 8.12.x, 9.2
>            Reporter: Van Halbert
>            Assignee: Van Halbert
>            Priority: Critical
>
> When there is a 1-to-many relationship defined in a JDG schema, the results from querying the "many" relationship may return the wrong results.   
> Example:  
> Person --> Phone Numbers
> INSERT INTO Person(id,name,email) VALUES (1,'name1','email1')
> INSERT INTO PhoneNumber(id, number) VALUES (1, '001234567');
> INSERT INTO PhoneNumber(id, number) VALUES (1, '001234568');
> SELECT p.name, p.id, pn.number FROM Person p, PhoneNumber pn WHERE p.id=pn.id AND p.id=1 and number = '001234568'
> or 
> Select * from PhoneNumber where number = '001234568'
> return 2 rows, 1 for each phonenumber.
> Why?  Because JDG returns the Person object that meets those conditions.  It doesn't filter the internal data to the Person object.  Therefore, all phonenumbers are returned for the same person, generating a row for each.
> This doesn't happen with 1-to-1 relationships because there's only ever 1 child object.



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)