Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-4510

JDG Complex POJO 1-n relationship wrong results when joining parent and child table

    Details

    • Steps to Reproduce:
      1. create cache on JDG
      2. use POJO + .proto file + marshaller
      3. insert into Person and PhoneNumber tables
      4. try to join the tables in a query

      Description

      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:

      Unable to find source-code formatter for language: plain. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
      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;
      }
      

      Source metadata:

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

      Tested scenario:

      SELECT COUNT(*) FROM PhoneNumber WHERE id=1
      

      returns 2.

      SELECT COUNT(*) FROM Person p, PhoneNumber pn WHERE p.id=pn.id AND p.id=1
      

      or

      SELECT COUNT(*) FROM Person p INNER JOIN PhoneNumber pn ON p.id=pn.id WHERE p.id=1
      

      both return only 1.

      NOTE: issue appears also when using only nested descriptors in the proto schema.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                van.halbert Van Halbert
                Reporter:
                jstastny Jan Stastny
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: