[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=13310597#comment-13310597 ] 

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

The same filtering logic that is used when performing update/delete on a child object in order to delete/update the correct child can be used on select queries.   This will mean the  criteria for a child object (1-to-many) will be processed twice.   Pushed down to ensure it filters the correct parent object for those children that's returned and then again when converting the children objects into tuples. 

> 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: Misc. Connectors
>    Affects Versions: 8.12.x, 9.2
>            Reporter: Van Halbert
>            Assignee: Steven Hawkins
>            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
(v6.4.11#64026)