Cannot nest multiple queries on the same table: Invalid path: 'generatedAlias1.name'

[molexx]

This query is trying to find all Droids and show their friends where the friend has Luke Skywalker as a friend:

query {
  Droids {
     select {
      name
      friends (where:{friends:{name:{EQ:"Luke Skywalker"}}}) {
        name
      }
    } 
  }
}

It errors with:

Exception while fetching data (/Droids) : org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.name' 
[select distinct droid from com.introproventures.graphql.jpa.query.schema.model.starwars.Droid as droid left join fetch droid.friends as generatedAlias0 where generatedAlias1.name=:param0 order by droid.id asc]

This is on master 963826f.

[igdianov]

@molexx I had investigated this use case and realized that Hibernate does not map the results correctly with a fetch query that loads all friends nodes with their friends of friends nodes into the JPA persistence context. This is because the JPA spec requires that the same entity instance will exist only once in the same persistence context with all references will point to the same instance in the fetch graph. The result is that we get the whole hierarchy interrelated so the filter friends of friends is not working.

I will disable this option to query same nested relationships in nested where criteria expression.

@ManyToMany to self is also rather confusing because the way it is modeled differs from the "Hibernate" way. Since Character entity represents both ends of a relationship we need separate "friends" and "friendsOf" collections with joinColumns / inverseJoinColumns being swapped,

@Entity
public abstract class Character {

    @Id
    @GraphQLDescription("Primary Key for the Character Class")
    String id;

    @ManyToMany(fetch=FetchType.LAZY)
    @JoinTable(name="character_friends",
            joinColumns=@JoinColumn(name="source_id", referencedColumnName="id"),
            inverseJoinColumns=@JoinColumn(name="friend_id", referencedColumnName="id"))
    Set<Character> friends; 

    @ManyToMany(fetch=FetchType.LAZY)
    @JoinTable(name="character_friends",
            joinColumns=@JoinColumn(name="id", referencedColumnName="source_id"),
            inverseJoinColumns=@JoinColumn(name="id", referencedColumnName="friend_id"))
    Set<Character> friendsOf; 

}

[molexx]

the JPA spec requires that the same entity instance will exist only once in the same persistence context

Even if each instance has a unique alias provided?

I see that if aliases are not provided JPA/Hibernate is making them up - e.g. character_1 - maybe it can get those wrong but if they were provided it might be ok?

[igdianov]

The JPA provider distinguishes each entity instance by id regardless of the alias used in the fetch join is auto generated or not. It maps the result of the SQL query into object graph in the persistent context using entity id and uses the same reference in persistent collections of many associations. It works fine if your query graph does not contain cyclical references, i.e. Droid->friends

But once you try to fetch Droid->friends->friends the query result is not mapped correctly into Persistent context by Hibernate provider. I will provide the snapshots of the JPQL, SQL queries and result snapshot created by Hibernate.

[igdianov]

query {
  Droids {
     select {
      name
      friends (where:{friends:{name:{EQ:"Luke Skywalker"}}}) {
        name
        friends {
          name
        }
      }
    } 
  }
}

Here is the generated JPQL query:

select distinct droid 
from Droid as droid 
left join fetch droid.friends as generatedAlias0 
left join fetch generatedAlias0.friends as generatedAlias1 
where generatedAlias1.name=:param0 
order by droid.id asc

SQL Query:

Hibernate: 
    select
        droid0_.id as id2_0_0_,
        character2_.id as id2_0_1_,
        character4_.id as id2_0_2_,
        droid0_.name as name3_0_0_,
        droid0_.primary_function as primary_5_0_0_,
        character2_.name as name3_0_1_,
        character2_.primary_function as primary_5_0_1_,
        character2_.favorite_droid_id as favorite6_0_1_,
        character2_.gender_code_id as gender_c7_0_1_,
        character2_.homePlanet as homePlan4_0_1_,
        character2_.DTYPE as DTYPE1_0_1_,
        friends1_.source_id as source_i1_2_0__,
        friends1_.friend_id as friend_i2_2_0__,
        character4_.name as name3_0_2_,
        character4_.primary_function as primary_5_0_2_,
        character4_.favorite_droid_id as favorite6_0_2_,
        character4_.gender_code_id as gender_c7_0_2_,
        character4_.homePlanet as homePlan4_0_2_,
        character4_.DTYPE as DTYPE1_0_2_,
        friends3_.source_id as source_i1_2_1__,
        friends3_.friend_id as friend_i2_2_1__ 
    from
        Character droid0_ 
    left outer join
        character_friends friends1_ 
            on droid0_.id=friends1_.source_id 
    left outer join
        Character character2_ 
            on friends1_.friend_id=character2_.id 
    left outer join
        character_friends friends3_ 
            on character2_.id=friends3_.source_id 
    left outer join
        Character character4_ 
            on friends3_.friend_id=character4_.id 
    where
        droid0_.DTYPE='Droid' 
        and character4_.name=? 
    order by
        droid0_.id asc,
        character2_.name asc,
        character4_.name asc

The query looks correct, but the mapped result is not correct. Notice that Luke Skywalker is not a friend of himself but still listed in the results and friends of friends are not filtered in the result.

{
  "data": {
    "Droids": {
      "select": [
        {
          "name": "C-3PO",
          "friends": [
            {
              "name": "R2-D2",
              "friends": [
                {
                  "name": "Han Solo"
                },
                {
                  "name": "Leia Organa"
                },
                {
                  "name": "Luke Skywalker"
                }
              ]
            }
          ]
        },
        {
          "name": "R2-D2",
          "friends": [
            {
              "name": "Han Solo",
              "friends": [
                {
                  "name": "Leia Organa"
                },
                {
                  "name": "Luke Skywalker"
                },
                {
                  "name": "R2-D2"
                }
              ]
            },
            {
              "name": "Leia Organa",
              "friends": [
                {
                  "name": "C-3PO"
                },
                {
                  "name": "Han Solo"
                },
                {
                  "name": "Luke Skywalker"
                },
                {
                  "name": "R2-D2"
                }
              ]
            },
            {
              "name": "Luke Skywalker",
              "friends": [
                {
                  "name": "C-3PO"
                },
                {
                  "name": "Han Solo"
                },
                {
                  "name": "Leia Organa"
                },
                {
                  "name": "R2-D2"
                }
              ]
            }
          ]
        }
      ]
    }
  }
}

Once Hibernate maps the entity instance from SQL query result, it will not create another instance and will use the same instance as reference in object graph.

[igdianov]

I take it back. The query is mapped correctly, I think. I discovered that there is a weird Hibernate bug that causes the results of the query to cache in the persistent context. I have to call entityManager.clear() before List<?> resultList = query.getResultList(); to make sure there is no side effects from previous query.

query {
  Droids {
     select {
      name
      friends(where:{friends:{name:{EQ:"Luke Skywalker"}}}) {
        name
        friends {
          name
        }
      }
    } 
  }  
}

Acctually generates the following result with Luke Skywalker being a friend of himself?

{
  "data": {
    "b": {
      "select": [
        {
          "name": "C-3PO",
          "friends": [
            {
              "name": "Han Solo",
              "friends": [
                {
                  "name": "Luke Skywalker"
                }
              ]
            },
            {
              "name": "Leia Organa",
              "friends": [
                {
                  "name": "Luke Skywalker"
                }
              ]
            },
            {
              "name": "R2-D2",
              "friends": [
                {
                  "name": "Luke Skywalker"
                },
                {
                  "name": "Han Solo"
                },
                {
                  "name": "Leia Organa"
                }
              ]
            }
          ]
        },
        {
          "name": "R2-D2",
          "friends": [
            {
              "name": "Luke Skywalker",
              "friends": [
                {
                  "name": "C-3PO"
                },
                {
                  "name": "Han Solo"
                },
                {
                  "name": "Leia Organa"
                },
                {
                  "name": "R2-D2"
                }
              ]
            },
            {
              "name": "Han Solo",
              "friends": [
                {
                  "name": "Luke Skywalker"
                }
              ]
            },
            {
              "name": "Leia Organa",
              "friends": [
                {
                  "name": "Luke Skywalker"
                }
              ]
            }
          ]
        }
      ]
    }
  }
}

[molexx]

Great, thanks for investigating this.

Acctually generates the following result with Luke Skywalker being a friend of himself?

I think the reason for that is that they are all outer joins. Setting optional:false on the where removes some of the outers but not all. Maybe we should be able to specify optional:false on each clause in the where rather than just once?

[molexx]

I'm not sure about the outer joins now...

The above creates this SQL:

   select
        droid0_.id as id2_0_0_,
        character2_.id as id2_0_1_,
        character4_.id as id2_0_2_,
        droid0_.name as name3_0_0_,
        droid0_.primary_function as primary_7_0_0_,
        character2_.name as name3_0_1_,
        character2_.favorite_droid_id as favorite5_0_1_,
        character2_.gender_code_id as gender_c6_0_1_,
        character2_.home_planet as home_pla4_0_1_,
        character2_.primary_function as primary_7_0_1_,
        character2_.dtype as dtype1_0_1_,
        friends1_.source_id as source_i1_2_0__,
        friends1_.friend_id as friend_i2_2_0__,
        character4_.name as name3_0_2_,
        character4_.favorite_droid_id as favorite5_0_2_,
        character4_.gender_code_id as gender_c6_0_2_,
        character4_.home_planet as home_pla4_0_2_,
        character4_.primary_function as primary_7_0_2_,
        character4_.dtype as dtype1_0_2_,
        friends3_.source_id as source_i1_2_1__,
        friends3_.friend_id as friend_i2_2_1__ 
    from
        character droid0_ 
    left outer join
        character_friends friends1_ 
            on droid0_.id=friends1_.source_id 
    left outer join
        character character2_ 
            on friends1_.friend_id=character2_.id 
    left outer join
        character_friends friends3_ 
            on character2_.id=friends3_.source_id 
    left outer join
        character character4_ 
            on friends3_.friend_id=character4_.id 
    where
        droid0_.dtype='Droid' 
        and character4_.name='Luke Skywalker'
    order by
        droid0_.id asc,
        character2_.name asc,
        character4_.name asc

which if you run in HSQL console looks like this:

these results look correct?

There are 5 rows - 3 for C3PO and 2 for R2-D2, column name3_0_1_ correctly lists R2's friends Han and Leia but not Luke, where is he coming from?

[igdianov]

It looks like Hibernate loads R2-D2 entity and then uses its reference in the collection of C-3PO friends. The expected result in the C-3PO friends collection should have R2-D2 with a single Luke Skywalker entity. The R2-D2 references its own friends that makes the query result mapping into object graph incorrect.

However, I don't have a clue where Luke comes from in R2-D2 friends list since it is not part of SQL result set:

Another problem I see is that Luke Skywalker appears in the list of R2-D2 friends but it is not loaded in the persistent context. The Hibernates loads it as soon as I inspect Luke's friends. This is rather side effect:

Hibernate: 
    select
        friends0_.source_id as source_i1_2_0_,
        friends0_.friend_id as friend_i2_2_0_,
        character1_.id as id2_0_1_,
        character1_.name as name3_0_1_,
        character1_.primary_function as primary_5_0_1_,
        character1_.favorite_droid_id as favorite6_0_1_,
        character1_.gender_code_id as gender_c7_0_1_,
        character1_.homePlanet as homePlan4_0_1_,
        character1_.DTYPE as DTYPE1_0_1_ 
    from
        character_friends friends0_ 
    inner join
        Character character1_ 
            on friends0_.friend_id=character1_.id 
    where
        friends0_.source_id=? 
    order by
        character1_.name asc

[igdianov]

It does not really matter if there is INNER or OUTER join. The SQL result looks correct, but the mapped result is not. Smell like a bug in Hibernate to me... :)

My guts feeling is that Hibernate uses wrong columns to map nested fetch joins results

[molexx]

@igdianov thanks again for your investigation, yes I agree.

I will try to submit a test case to the Hibernate project using their template.

[molexx]

I think it may be an unnecessary 'fetch'.

The above creates the JPQL:

select distinct droid
from com.introproventures.graphql.jpa.query.schema.model.starwars.Droid as droid
left join fetch droid.friends as generatedAlias0
left join fetch generatedAlias0.friends as generatedAlias1
where generatedAlias1.name='Luke Skywalker' 
order by droid.id asc

If the second 'fetch' is removed and turned into just a join:

select distinct droid
from com.introproventures.graphql.jpa.query.schema.model.starwars.Droid as droid
left join fetch droid.friends as generatedAlias0
left join generatedAlias0.friends as generatedAlias1
where generatedAlias1.name='Luke Skywalker' 
order by droid.id asc

it seems to work ok.

I think this may be correct behaviour because in this case we don't want to show any data from 'generatedAlias1', we're just using it to restrict. By adding the fetch we're saying 'please get the data and include it in the collection'.
I'm not sure of the generic graphql-jpa-query fix for this though as the fetch on generatedAlias0 is required.

@igdianov what do you think?

[igdianov]

@molexx I tried that combination before and it did not work as I recall. The join fetch is used to query filtered result with predicate applied on this node. My understanding is that the fetch is only a hint for Hiberbnate to load result set into persistent collection and to avoid creating a lazy proxy for it.

I will create a few unit tests to verify the JPQL join/join fetch behaviors in any case.

[igdianov]

@molexx Check this out: 14964ec

@ManyToMany mapping needs both sides of the relationship. Think of it this way - if we're mapping "author" / "book" as many-to-many, we need "authors" collection on Book and "books" collection on Author. In our case, "Character" entity represents both ends of a relationship; so we need "friends to" and "friend of" collections.

We can still use the same association table, but note that friendsOf is mappedBy friends.

@Entity
@GraphQLDescription("Abstract representation of an entity in the Star Wars Universe")
@Getter
@Setter
@ToString
@EqualsAndHashCode(exclude={"appearsIn","friends", "friendsOf"}) // Fixes NPE in Hibernate when initializing loaded collections #1
public abstract class Character {

    @Id
    @GraphQLDescription("Primary Key for the Character Class")
    String id;

    @GraphQLDescription("Name of the character")
    String name;

    @GraphQLDescription("Who are the known friends to this character")
    @ManyToMany(fetch=FetchType.LAZY)
    @JoinTable(name="character_friends",
            joinColumns=@JoinColumn(name="source_id", referencedColumnName="id"),
            inverseJoinColumns=@JoinColumn(name="friend_id", referencedColumnName="id"))
    @OrderBy("name ASC")
    Set<Character> friends; 

    @GraphQLDescription("Who are the known friends of this character")
    @ManyToMany(fetch=FetchType.LAZY, mappedBy = "friends")
    @OrderBy("name ASC")
    Set<Character> friendsOf;     
    
    @GraphQLDescription("What Star Wars episodes does this character appear in")
    @ElementCollection(targetClass = Episode.class, fetch=FetchType.LAZY)
    @Enumerated(EnumType.ORDINAL)
    @OrderBy
    Set<Episode> appearsIn;
    
    Character() {}

}

The "friends" and "friendsOf" collections may or may not match (depending on whether "friendship" is mutual or not).

After fixing the relationship, the following query returns correct result:

query {
  Droids {
     select {
      name
      friends(where: {friendsOf:{name: {EQ: "Luke Skywalker"}}}) {
        name
        friendsOf {
          name
        }
      }
    }
  }
}

Generated JPQL:

select distinct droid from Droid as droid 
left join fetch droid.friends as generatedAlias0 
left join fetch generatedAlias0.friendsOf as generatedAlias1 
where generatedAlias1.name='Luke Skywalker'
order by droid.id asc

Result:

{
  "data": {
    "Droids": {
      "select": [
        {
          "name": "C-3PO",
          "friends": [
            {
              "name": "Han Solo",
              "friendsOf": [
                {
                  "name": "Luke Skywalker"
                }
              ]
            },
            {
              "name": "Leia Organa",
              "friendsOf": [
                {
                  "name": "Luke Skywalker"
                }
              ]
            },
            {
              "name": "R2-D2",
              "friendsOf": [
                {
                  "name": "Luke Skywalker"
                }
              ]
            }
          ]
        },
        {
          "name": "R2-D2",
          "friends": [
            {
              "name": "Han Solo",
              "friendsOf": [
                {
                  "name": "Luke Skywalker"
                }
              ]
            },
            {
              "name": "Leia Organa",
              "friendsOf": [
                {
                  "name": "Luke Skywalker"
                }
              ]
            }
          ]
        }
      ]
    }
  }
}