[JBoss JIRA] (TEIID-4510) JDG Complex POJO 1-n relationship wrong results when joining parent and child table
by Jan Stastny (JIRA)
[ https://issues.jboss.org/browse/TEIID-4510?page=com.atlassian.jira.plugin... ]
Jan Stastny commented on TEIID-4510:
------------------------------------
The queries to insert the data:
{code:sql}
INSERT INTO Person(id,name,email) VALUES (1,'name1','email1')
INSERT INTO Address(id,Address,City,State) VALUES (1,'address1','city1','state1')
INSERT INTO Address(id,Address,City,State) VALUES (1,'address2','city2','state2')
INSERT INTO PhoneNumber(id, number) VALUES (1, '001234567')
INSERT INTO PhoneNumber(id, number) VALUES (1, '001234568')
INSERT INTO Person(id,name,email) VALUES (2,'name2','email2')
INSERT INTO Address(id,Address,City,State) VALUES (2,'address1','city1','state1')
INSERT INTO PhoneNumber(id, number) VALUES (2, '002234567')
{code}
Query joining the tables:
{code:sql}
SELECT COUNT(*) FROM Person p, PhoneNumber pn WHERE p.id=pn.id AND p.id=1
{code}
returns 1.
But
{code:sql}
SELECT * FROM Person p, PhoneNumber pn WHERE p.id=pn.id AND p.id=1
{code}
returns 2 rows:
{code:plain}
p.id,p.name,p.email,pn.number,pn.id
----------
1 name1 email1 001234567 1
1 name1 email1 001234568 1
{code}
I'd say there are two issues:
# DISTINCT by default
{code:sql}
SELECT p.name FROM Person p, PhoneNumber pn WHERE p.id=pn.id AND p.id=1
{code}
returns only:
{code:plain}
name
-------
name1
{code}
which is behaving as DISTINCT by default, which is not desirable.
# Wrong COUNT implementation (may be resolved by correcting the implicit DISTINCT issue)
{code:sql}
SELECT COUNT(p.name) FROM Person p, PhoneNumber pn WHERE p.id=pn.id AND p.id=1
{code}
which returns 1, and following:
{code:sql}
SELECT COUNT(pn.number) FROM Person p, PhoneNumber pn WHERE p.id=pn.id AND p.id=1
{code}
which returns 2.
Note worth mentioning, that COUNT behaves as COUNT DISTINCT too. It is not expected/desirable (https://docs.jboss.org/author/display/TEIID/Expressions#Expressions-Aggre... : count the number of values (including nulls and duplicates) in a group). But I believe this issue is caused by the DISTINCT by default, and the COUNT\(*\) problem is due to its implementation looking only at one column I guess. If you consider these separate issues, I can log another JIRAs for it.
> 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)
9 years, 5 months
[JBoss JIRA] (TEIID-4510) JDG Complex POJO 1-n relationship wrong results when joining parent and child table
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-4510?page=com.atlassian.jira.plugin... ]
Van Halbert commented on TEIID-4510:
------------------------------------
I'm thinking a bluejean session will be needed so that I can see your environment.
> 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)
9 years, 5 months
[JBoss JIRA] (TEIID-4510) JDG Complex POJO 1-n relationship wrong results when joining parent and child table
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-4510?page=com.atlassian.jira.plugin... ]
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(a)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)
9 years, 5 months
[JBoss JIRA] (TEIID-4510) JDG Complex POJO 1-n relationship wrong results when joining parent and child table
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-4510?page=com.atlassian.jira.plugin... ]
Van Halbert commented on TEIID-4510:
------------------------------------
Jan,
Can I get all the inserts statements you used before executing the select statements?
> 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)
9 years, 5 months