JSqlParser fails for modifying queries

[rolfwoll]

Using Spring Boot 2.7 which uses Spring Data JPA 2.7.0 and I have a problem that occurred after upgrading from Spring Boot 2.3.12.

I have a simple modifying query something like this:

@Modifying(clearAutomatically = true) @Query(value = "update userinfo user set user.is_in_treatment = false where user.id = :userId", nativeQuery = true) void setNotInTreatment(@Param("userId") int userId);

We are using JSqlParser for other things in our project, so it is in the classpath, and that parser is therefore used according to the log:

o.s.d.j.r.q.QueryEnhancerFactory - JSqlParser is in classpath. If applicable JSqlParser will be used.

At startup the parsing of the above query fails with the following error message:

Could not create query for public abstract void com.norse.api.repository.UserRepository.setNotInTreatment(int)! Reason: class net.sf.jsqlparser.statement.update.Update cannot be cast to class net.sf.jsqlparser.statement.select.Select

I'm assume the error is related to the fact that I have a modifying query?

[DiegoKrupitza]

Yeah you are right! The current implementation does not detect if it is a modifying query or not. It assumes that all the queries are "selects".

The problems is here:

private static Select parseSelectStatement(String query) {
  try {
	  return (Select) CCJSqlParserUtil.parse(query);
  } catch (JSQLParserException e) {
	  throw new IllegalArgumentException("The query you provided is not a valid SQL Query!", e);
  }
} 

[DiegoKrupitza]

When I implemented the JSqlParserQueryEnhancer it did not made sense to me that other types of statements (DELETE, UPDATE, ... ) will call this methods. But It seems those call are desired although the results are not used anywhere. The default implementation QueryUtils even returns null or "" on all those queries.

[gregturn]

@DiegoKrupitza I don't know if modified queries is possible/makes sense, or if (at least) we simply need a more complete error message?

[DiegoKrupitza]

@DiegoKrupitza I don't know if modified queries is possible/makes sense, or if (at least) we simply need a more complete error message?

@gregturn
The main problem here arises that some parts of the code do not distinguish between "normal" @Queries (aka selects) and @Queries that do some kind of modification (delete, update, ...).

Cause of Problem:

• Nr. 1: StringQuery (because of DeclaredQuery)
  No matter what kind of @Query("...", native=true) we use it will be stored into a StringQuery (to keep it simple we just ignore EmptyDeclaredQuery and ExpressionBasedStringQuery for now). Because we stored the original query into a StringQuery for later processing, all the functions such as getAlias(), hasNamedParameter() and many more are "accessible" (even though it does not make sense for a modifying query such as update userinfo user set ... to have such methods). Since inside the StringQuery constructor we call some of those methods (e.g getAlias()) to precompute certain fields, that are accessed frequently, they will be also called on queries such as update userinfo user set ... where it clearly makes no sense. This is also why the issues/exception from above occurs. The query string gets stored into a StringQuery. The code then wants to call getAlias() and the current JSqlParserQueryEnhancer expect and select statement to parse, but it got an update statement and therefore throws an exception.

• Nr. 2: AbstractStringBasedJpaQuery
  Another reason why this happened is that inside AbstractStringBasedJpaQuery we do not care what kind of query it is (select, update, delete, ...). This happens within few of the methods of AbstractStringBasedJpaQuery.
  The line 97&98 of AbstractStringBasedJpaQuery show this quite well:

String sortedQueryString = QueryEnhancerFactory.forQuery(query) //
				.applySorting(accessor.getSort(), query.getAlias());

No matter what kind of query it is, we call applySorting. Although it is possible to add sorting to an update statement (see here ) or a delete (see here ), this does not make much sense. For selects this two lines are important.

Proposed Solutions:

• 1:
  A solution for this would be to introduce some kind of DeclaredQuery implementation that resembles queries that are not selects. And consider this within the whole data-JPA project. This would require somehow a more sophisticated "refactoring" and rewrite of some parts of the code that are build upon the "DeclaredQuery" interface.

• 2:
  Another solution is presented in my PR JSqlParserQueryEnhancer works with non Select Statements. #2559. Since this distinction of query types is only important to the JSqlParserQueryEnhancer this is a quick fix. The DefaultQueryEnhancer implementation which uses QueryUtils does not care what type of query it is and returns emtpy set/list, null or "" for non-select statements.

[gregturn]

Thanks @DiegoKrupitza.

To be honest, your proposed PR is along the lines of what I was thinking. Sniff out the query "type", and cut out early from any sorting, etc.

[gregturn]

Thanks @DiegoKrupitza!

[gregturn]

Backported to 2.7.x.

[fabriziodelfranco]

I have the same issue with an insert query, using 2.7.2-SNAPSHOT.
Is there any specific reason for which only UPDATE and DELETE queries have been fixed?

Thanks.

[DiegoKrupitza]

I have the same issue with an insert query, using 2.7.2-SNAPSHOT.

Is there any specific reason for which only UPDATE and DELETE queries have been fixed?

Thanks.

@fabriziodelfranco there is an issue (#2593) that covers this problem. I already created a PR (#2595) that fixes this issue

[fabriziodelfranco]

Thank you @DiegoKrupitza

[rolfwoll]

Thanks @DiegoKrupitza and @gregturn for quick response, followup and fix.