Root join on @OneToMany property, not working while sorting in SortUtils.pathToExpression(final Root<?> root, final String path)

[alancruzcgi]

Hello dear RSQL JPA Spec team,

Hoping that your day is going well. I have the next Exception:

"An unexpected error occurred: Illegal attempt to dereference path source [null.criteria] of basic type" while trying to sort by a mapped property called qualityTarget to criteria.qualityTarget.

• 1st level, Root: Review.class

...
/** Criteria. */
  @OneToMany(mappedBy = "review", cascade = CascadeType.ALL, orphanRemoval = true,
    targetEntity = Criterion.class)
  private List<Criterion> criteria = new ArrayList<>();
...

• 2nd Level: Criterion.class

...
  /** Quality target. */
  private String qualityTarget;

  /** Review. */
  @ManyToOne private Review review;
...

• Issue Whenever I try to generate the Expression from the passed string path, it fails on line 51 on the screenshot from SortUtils.java

Could you please provide some insights, as we have the same approach in other classes that work, with the only difference that we only map properties to @OneToOne relations, and not @OneToMany like in this case with List<Criterion> criteria.

Thanks in advance for any help and wish you a cool day ahead, Alan

[alancruzcgi]

I did not like this approach, -but I think it worked to avoid the issue/exception (I do have the information back)-.

So instead of the Parent class as Root (Review.class), I used Root as the child class (Criterion.class) then extract from there the required information "backwards" from the Criterion -> @ManyToOne Review.

• final var review = root.join("review", JoinType.LEFT);

UPDATE: Error still present for different @OneToMany List<Issue> issues; on Criterion.class

[alancruzcgi]

Now we have the same exception on a different property as @OneToMany List<Issues> issues that belongs to Criterion.class

[perplexhub]

Hi Alan, could you share the exception, model classes and query? Thanks.

[alancruzcgi]

Thanks for your answer, brother. Also please:

• Exception (same as above, reported on SortUtils Line 51): An unexpected error occurred: Illegal attempt to dereference path source [null.issues] of basic type"

• Model

  • Review.class

REMOVED - non-disclosure agreement.

• Criterion.class

REMOVED - non-disclosure agreement.

• Issue.class

REMOVED - non-disclosure agreement.

• User.class

REMOVED - non-disclosure agreement.

• QueryManager (CriteriaBuilder, Sorting, Mapper)

REMOVED - non-disclosure agreement.

• On the handler for request

/*
/!\ /!\ /!\

- All properties from mapper with "hierarchies" like issues.id (OneToMany), review.id (ManyToOne), issues.editor.displayName etc. will FAIL on SortUtils.java
- This does not happen with no deeper hierarchies (Root object), like qualityTarget and id from Criterion.class.

/!\ /!\ /!\
*/
REMOVED - non-disclosure agreement.

I am still trying to fix this, so I will be here if you need some assistance and or help.

Thanks, Alan

[perplexhub]

@borsch Do you have any idea?

[perplexhub]

https://ngdeveloper.com/illegal-attempt-dereference-path-source-null-categorylist-basic-type/

Could you try to change SortUtils line 49 from root.get to root.join?

[alancruzcgi]

@perplexhub Thanks for the answer, and I think the above message is for you @borsch.

// Still, I will try the function locally and let you know.

[alancruzcgi]

@perplexhub @borsch I isolated the function on my code like as QueryManager.pathToExpression(root, "issues.id");

public static Expression<?> pathToExpression(final Root<?> root, final String path) {
    final String[] properties = path.split(PROPERTY_PATH_SEPARATOR);

    // Path<?> expression = root.get(properties[0]);
    Path<?> expression = root.join(properties[0]);
    for (int i = 1; i < properties.length; ++i) {
      expression = expression.get(properties[i]);
    }
    return expression;
  }

And it went through without the exception c:

[alancruzcgi]

@perplexhub @borsch The only thing is, that after running my JUnit test suite. Now I am failing on other joins using the same updated function get -> join: Cannot join to attribute of basic type

[alancruzcgi]

@perplexhub @borsch I tried this approach:

public static Expression<?> pathToExpression(final Root<?> root, final String path) {
    final String[] properties = path.split(PROPERTY_PATH_SEPARATOR);
    Path<?> expression;
    if (properties.length == 1) {
      // Is basic type
      expression = root.get(properties[0]);
    } else {
      // Is join on property
      expression = root.join(properties[0], JoinType.LEFT);
    }
    for (int i = 1; i < properties.length; ++i) {
      expression = expression.get(properties[i]);
    }
    return expression;
  }

But now, I have an error on my result set towards the property used by the sort part issueId:

    {"@timestamp":"2022-08-04T10:46:08.008Z","ecs.version":"1.2.0","log.level":"ERROR","message":"Column \"ISSUES3_.ID\" must be in the GROUP BY list; SQL statement
:\nselect issues2_.id as col_0_0_, criterion0_.id as col_1_0_, review1_.id as col_2_0_, issues2_.title as col_3_0_, criterion0_.quality_target as col_4_0_, issues2_.creator_user_id as col_5_0_, issues2_.editor_user_id as col_6_0_, issues2_.criticality as col_7_0_, issues2_.prm_relevant as col_8_0_, issues2_.state as col_9_0_, issues2_.relation as col_10_0_ from criterion criterion0_ left outer join review review1_ on criterion0_.review_id=review1_.id left outer join issue issues2_ on criterion0_.id=issues2_.criterion_id and (issues2_.relation=?) left outer join issue issues3_ on criterion0_.id=issues3_.criterion_id inner join users user4_ on issues2_.creator_user_id=user4_.user_id inner join users user5_ on issues2_.editor_user_id=user5_.user_id where criterion0_.review_id=1 group by issues2_.id order by issues3_.id desc limit ? [90016-200]","process.thread.name":"Test worker","log.logger":"org.hibernate.engine.jdbc.spi.SqlExceptionHelper","HOME_DIR":"${env:HOME}"}
    {"@timestamp":"2022-08-04T10:46:08.010Z","ecs.version":"1.2.0","log.level":"ERROR","message":"An unexpected error occurred: org.hibernate.exception.SQLGrammarException: could not extract ResultSet","process.thread.name":"Test worker","log.logger":"de.audi.seqt.infrastructure.middleware.LoggingBehavior","HOME_DIR":"${env:HOME}"}

[borsch]

Hi @perplexhub @alancruzcgi I'll review this issue over the weekend

[alancruzcgi]

Thanks in advance @borsch, have a good one ahead and let me know if I can support you with something.

[perplexhub]

Should we use root.join only for @OneToMany?

[borsch]

Should we use root.join only for @OneToMany?

I think it should be used for all type of relationship mappings

[alancruzcgi]

I do not know if this is affecting the queries. As now, and reported with the issueId above. When I do the nested joins (Review (root) -> Criterion (criteria) -> Issue (issues)), after trying to sort with a different property like criteria.qualityTarget. I got a similar error:

{"@timestamp":"2022-08-05T12:42:33.448Z","ecs.version":"1.2.0","log.level":"ERROR","message":"Column \"CRITERIA3_.QUALITY_TARGET\" must be in the GROUP BY list; SQL statement:\nselect issues2_.id as col_0_0_, criteria1_.id as col_1_0_, review0_.id as col_2_0_, issues2_.title as col_3_0_, max(criteria1_.quality_target) as col_4_0_, issues2_.creator_user_id as col_5_0_, issues2_.editor_user_id as col_6_0_, issues2_.criticality as col_7_0_, issues2_.prm_relevant as col_8_0_, issues2_.state as col_9_0_, issues2_.relation as col_10_0_ from review review0_ inner join criterion criteria1_ on review0_.id=criteria1_.review_id and (review0_.id=criteria1_.review_id) inner join issue issues2_ on criteria1_.id=issues2_.criterion_id and (issues2_.criterion_id=criteria1_.id and issues2_.relation=?) left outer join criterion criteria3_ on review0_.id=criteria3_.review_id inner join users user4_ on issues2_.creator_user_id=user4_.user_id inner join users user5_ on issues2_.editor_user_id=user5_.user_id where review0_.id=1 group by issues2_.id , criteria1_.quality_target order by criteria3_.quality_target asc limit ? [90016-200]","process.thread.name":"Test worker","log.logger":"org.hibernate.engine.jdbc.spi.SqlExceptionHelper","HOME_DIR":"${env:HOME}"} 
{"@timestamp":"2022-08-05T12:42:33.448Z","ecs.version":"1.2.0","log.level":"ERROR","message":"An unexpected error occurred: org.hibernate.exception.SQLGrammarException: could not extract ResultSet","process.thread.name":"Test worker","log.logger":"de.audi.seqt.infrastructure.middleware.LoggingBehavior","HOME_DIR":"${env:HOME}"}

[borsch]

I do not know if this is affecting the queries. As now, and reported with the issueId above. When I do the nested joins (Review (root) -> Criterion (criteria) -> Issue (issues)), after trying to sort with a different property like criteria.qualityTarget. I got a similar error:

{"@timestamp":"2022-08-05T12:42:33.448Z","ecs.version":"1.2.0","log.level":"ERROR","message":"Column \"CRITERIA3_.QUALITY_TARGET\" must be in the GROUP BY list; SQL statement:\nselect issues2_.id as col_0_0_, criteria1_.id as col_1_0_, review0_.id as col_2_0_, issues2_.title as col_3_0_, max(criteria1_.quality_target) as col_4_0_, issues2_.creator_user_id as col_5_0_, issues2_.editor_user_id as col_6_0_, issues2_.criticality as col_7_0_, issues2_.prm_relevant as col_8_0_, issues2_.state as col_9_0_, issues2_.relation as col_10_0_ from review review0_ inner join criterion criteria1_ on review0_.id=criteria1_.review_id and (review0_.id=criteria1_.review_id) inner join issue issues2_ on criteria1_.id=issues2_.criterion_id and (issues2_.criterion_id=criteria1_.id and issues2_.relation=?) left outer join criterion criteria3_ on review0_.id=criteria3_.review_id inner join users user4_ on issues2_.creator_user_id=user4_.user_id inner join users user5_ on issues2_.editor_user_id=user5_.user_id where review0_.id=1 group by issues2_.id , criteria1_.quality_target order by criteria3_.quality_target asc limit ? [90016-200]","process.thread.name":"Test worker","log.logger":"org.hibernate.engine.jdbc.spi.SqlExceptionHelper","HOME_DIR":"${env:HOME}"} 
{"@timestamp":"2022-08-05T12:42:33.448Z","ecs.version":"1.2.0","log.level":"ERROR","message":"An unexpected error occurred: org.hibernate.exception.SQLGrammarException: could not extract ResultSet","process.thread.name":"Test worker","log.logger":"de.audi.seqt.infrastructure.middleware.LoggingBehavior","HOME_DIR":"${env:HOME}"}

Hi Are you getting same exception without sorting?

[alancruzcgi]

Let me see, then I let you know @borsch (in-loop @perplexhub).

[alancruzcgi]

@borsch @perplexhub So after deactivating sorting, the exception is not thrown. But after running the JUnits, obviously, the ones expecting ordered elements fail on AssertionError for expected values.

Any suggestions, or how can I help you guys on this one ?. Thanks, Alan

[alancruzcgi]

UPDATE: @borsch @perplexhub "Extra" joins were added after using the RSQLSupport > SortUtils's suggested modification at pathToExpression function.

• That is what is causing the: Column\"CHILD4_.PROPERTY\" must be in the GROUP BY list;

select distinct grandchildren2_.id as col_0_0_,

...

from parent parent0_
-- First join to child object from root parent
         inner join child children1_ on parent0_.id = children1_.parent_id
-- Second join to grandchildren object from child
         inner join grandchild grandchildren2_
                    on children1_.id = grandchildren2_.child_id and (grandchildren2_.relation=0)
-- /!\ Path to expression ADDED extra joins, so now the order by would require to use the alias child4_
          left outer join child children3_ on parent0_.id = children3_.parent_id
          cross join child child4_
-- /!\
where grandchildren2_.child_id = child4_.id
and parent0_.id = 1
-- ERROR from pathToExpression 
order by child4_.property asc

[borsch]

Hi @alancruzcgi, @perplexhub Sorry for long delay, was busy with main work activities. Check this PR - https://github.com/perplexhub/rsql-jpa-specification/pull/66

[alancruzcgi]

Hey hi @borsch ! good day. No worries at all, as I do know how the situation is (hope fam and friends are doing ok).

I just left a comment in the PR, so probably, it does make sense to add that specific JUnit test case. Then we are set ok ;D thanks again for your support !

[perplexhub]

Thanks guys. The changes was released .