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.