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

Van Halbert edited comment on TEIID-4510 at 10/13/16 9:32 AM:
--------------------------------------------------------------

Also, I did notice how you configured the "id" columns on the child tables.   That column should not be selectable on the child.    In order to get the ID, you must join with the parent object.  If you use the import metadata option, that's the way it will be exposed.  And from the unit test it has it defined as the following on the child tables:

id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE FALSE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),


This needs to be clear in the doc's if the metadata is to be manually created.


was (Author: van.halbert):
Also, I didn't notice how you configured the "id" columns on the child tables.   That column should not be selectable on the child.    In order to get the ID, you must join with the parent object.  If you use the import metadata option, that's the way it will be exposed.  And from the unit test it has it defined as the following on the child tables:

id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE FALSE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),


This needs to be clear in the doc's if the metadata is to be manually created.

> 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)