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

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

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Blocker Blocker
    • 8.12.7.6_3, 9.2
    • 8.12.6.6_3, 9.2
    • Misc. Connectors
    • None
      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

      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.

              van.halbert Van Halbert (Inactive)
              jstastny@redhat.com Jan Stastny
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Created:
                Updated:
                Resolved: