[teiid-issues] [JBoss JIRA] (TEIID-4510) JDG Complex POJO 1-n relationship wrong results when joining parent and child table

[Van Halbert (JIRA)]

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

Van Halbert commented on TEIID-4510:
------------------------------------

I just ran thru 4  scenarios with the PhoneNumber message nested and non-nested and using metadata import or defined (as above) , using the following queries, and got back 2 results for each of the select queries:

Insert into Person (id, name, email) Values (200, 'TestPerson2', 'test2 at person.com')
Insert into PhoneNumber (id, number) Values (200, '603-351-3022')
Insert into PhoneNumber (id, number) Values (200, '314-351-3022')

A) select * from PhoneNumber where id = 200

B) Select p.name, p.email, n.number from Person p, PhoneNumber n where p.id = n.id and p.id = 200

> JDG Complex POJO 1-n relationship wrong results when joining parent and child table
> -----------------------------------------------------------------------------------
>
>                 Key: TEIID-4510
>                 URL: https://issues.jboss.org/browse/TEIID-4510
>             Project: Teiid
>          Issue Type: Bug
>          Components: Misc. Connectors
>    Affects Versions: 8.12.6.6_3
>            Reporter: Jan Stastny
>            Assignee: Van Halbert
>            Priority: Blocker
>
> When using infinispan-cache-dsl translator to access JDG cache incorrect results are returned for joins between the parent and child table.
> POJO proto schema:
> {code:plain}
> package org.jboss.qe.jdg.remote.protobuf.complex;
> /* @Indexed */
> message Person {
>     
>     /* @IndexedField(index=true, store=false) */
>     required int32 id = 1;
>     /* @IndexedField */
>     required string name = 2;
>     
>     /* @IndexedField */
>     optional string email = 3;
>     
>     /* @IndexedField(index=true, store=false) */
>     repeated PhoneNumber phones = 4;
>     /* @Indexed */
>     message Address {
>         /* @IndexedField */
>         required string Address = 1;
>         
>         /* @IndexedField(index=true, store=false) */
>         required string City = 2;
>         
>         /* @IndexedField(index=true, store=false) */
>         required string State = 3;
>     }
>     /* @IndexedField(index=true, store=false) */
>     optional Address address = 5;
> }
> /* @Indexed */
> message PhoneNumber {
>     /* @IndexedField */
>     required string number = 1;
> }
> {code}
> Source metadata:
> {code:sql}
> CREATE FOREIGN TABLE Address (
>     Address string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'address.Address', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
>     City string NOT NULL OPTIONS (NAMEINSOURCE 'address.City', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
>     State string NOT NULL OPTIONS (NAMEINSOURCE 'address.State', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
>     id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE TRUE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
>     CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'address')
> ) OPTIONS (UPDATABLE TRUE);
> CREATE FOREIGN TABLE Person (
>     id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
>     name string NOT NULL OPTIONS (NAMEINSOURCE 'name', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
>     email string OPTIONS (NAMEINSOURCE 'email', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
>     CONSTRAINT PK_ID PRIMARY KEY(id)
> ) OPTIONS (UPDATABLE TRUE);
> CREATE FOREIGN TABLE PhoneNumber (
>     number string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'phones.number', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
>     id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE TRUE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
>     CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'phones')
> ) OPTIONS (UPDATABLE TRUE);
> {code}
> Tested scenario:
> {code:sql}
> SELECT COUNT(*) FROM PhoneNumber WHERE id=1
> {code}
> returns 2.
> {code:sql}
> SELECT COUNT(*) FROM Person p, PhoneNumber pn WHERE p.id=pn.id AND p.id=1
> {code}
> or
> {code:sql}
> SELECT COUNT(*) FROM Person p INNER JOIN PhoneNumber pn ON p.id=pn.id WHERE p.id=1
> {code}
> both return only 1.
> NOTE: issue appears also when using only nested descriptors in the proto schema.



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)