BadJpqlGrammarException in 3.1.0 when using "round" in a select query

[The-Arne]

When I'm using @Query("SELECT ROUND<.....>") in a jpa repository with version 3.1.0, I'm getting the following exception:

Caused by: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:74 extraneous input 'ROUND' expecting {'(', '+', '-', ':', '?', ALL, AND, ANY, AS, ASC, AVG, BETWEEN, BOTH, BREADTH, BY, CASE, CAST, COLLATE, COUNT, CROSS, CUBE, CURRENT, CURRENT_DATE, CURRENT_INSTANT, CURRENT_TIME, CURRENT_TIMESTAMP, CYCLE, DATE, DATETIME, DAY, DEFAULT, DELETE, DEPTH, DESC, DISTINCT, ELEMENT, ELEMENTS, ELSE, EMPTY, END, ENTRY, EPOCH, ERROR, ESCAPE, EVERY, EXCEPT, EXCLUDE, EXISTS, EXTRACT, FALSE, FETCH, FILTER, FIRST, FOLLOWING, FOR, FORMAT, FROM, FULL, FUNCTION, GROUP, GROUPS, HAVING, HOUR, ID, IGNORE, ILIKE, IN, INDEX, INDICES, INNER, INSERT, INSTANT, INTERSECT, INTO, IS, JOIN, KEY, LAST, LEADING, LEFT, LIKE, LIMIT, LIST, LISTAGG, LOCAL, LOCAL_DATE, LOCAL_DATETIME, LOCAL_TIME, MAP, MATERIALIZED, MAX, MAXELEMENT, MAXINDEX, MEMBER, MICROSECOND, MILLISECOND, MIN, MINELEMENT, MININDEX, MINUTE, MONTH, NANOSECOND, NATURALID, NEW, NEXT, NO, NOT, NULL, NULLS, OBJECT, OF, OFFSET, OFFSET_DATETIME, ON, ONLY, OR, ORDER, OTHERS, OUTER, OVER, OVERFLOW, OVERLAY, PAD, PARTITION, PERCENT, PLACING, POSITION, PRECEDING, QUARTER, RANGE, RESPECT, RIGHT, ROLLUP, ROW, ROWS, SEARCH, SECOND, SELECT, SET, SIZE, SOME, SUBSTRING, SUM, THEN, TIES, TIME, TIMESTAMP, TIMEZONE_HOUR, TIMEZONE_MINUTE, TO, TRAILING, TREAT, TRIM, TRUE, TRUNC, TRUNCATE, TYPE, UNBOUNDED, UNION, UPDATE, USING, VALUE, VALUES, VERSION, VERSIONED, WEEK, WHEN, WHERE, WITH, WITHIN, WITHOUT, YEAR, CHARACTER, STRINGLITERAL, JAVASTRINGLITERAL, INTEGER_LITERAL, FLOAT_LITERAL, HEXLITERAL, IDENTIFICATION_VARIABLE}; Bad JPQL grammar [SELECT ROUND<.....>]
	at org.springframework.data.jpa.repository.query.BadJpqlGrammarErrorListener.syntaxError(BadJpqlGrammarErrorListener.java:39)
	at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)
	at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:543)
	at org.antlr.v4.runtime.DefaultErrorStrategy.reportUnwantedToken(DefaultErrorStrategy.java:377)
	at org.antlr.v4.runtime.DefaultErrorStrategy.singleTokenDeletion(DefaultErrorStrategy.java:548)
	at org.antlr.v4.runtime.DefaultErrorStrategy.sync(DefaultErrorStrategy.java:266)
	at org.springframework.data.jpa.repository.query.HqlParser.instantiationArgument(HqlParser.java:9224)
	at org.springframework.data.jpa.repository.query.HqlParser.instantiationArguments(HqlParser.java:9157)
	at org.springframework.data.jpa.repository.query.HqlParser.instantiation(HqlParser.java:2395)
	at org.springframework.data.jpa.repository.query.HqlParser.selectExpression(HqlParser.java:3275)

ROUND should probably still work. At least I didn't find anything in the release notes that this feature was removed. One reason for that could be that the links to the documentation under https://github.com/spring-projects/spring-data-jpa/releases are dead, though.

[mp911de]

Can you please provide the entire SQL statement instead of the trimmed down content?

[The-Arne]

@Query("select round(count(ri) * 100 / max(ri.receipt.positions), 0) as perc from StockOrderItem oi right join StockReceiptItem ri on ri.article = oi.article")

leads to:

Caused by: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:7 extraneous input 'round' expecting {'(', '+', '-', ':', '?', ALL, AND, ANY, AS, ASC, AVG, BETWEEN, BOTH, BREADTH, BY, CASE, CAST, COLLATE, COUNT, CROSS, CUBE, CURRENT, CURRENT_DATE, CURRENT_INSTANT, CURRENT_TIME, CURRENT_TIMESTAMP, CYCLE, DATE, DATETIME, DAY, DEFAULT, DELETE, DEPTH, DESC, DISTINCT, ELEMENT, ELEMENTS, ELSE, EMPTY, END, ENTRY, EPOCH, ERROR, ESCAPE, EVERY, EXCEPT, EXCLUDE, EXISTS, EXTRACT, FALSE, FETCH, FILTER, FIRST, FOLLOWING, FOR, FORMAT, FROM, FULL, FUNCTION, GROUP, GROUPS, HAVING, HOUR, ID, IGNORE, ILIKE, IN, INDEX, INDICES, INNER, INSERT, INSTANT, INTERSECT, INTO, IS, JOIN, KEY, LAST, LEADING, LEFT, LIKE, LIMIT, LIST, LISTAGG, LOCAL, LOCAL_DATE, LOCAL_DATETIME, LOCAL_TIME, MAP, MATERIALIZED, MAX, MAXELEMENT, MAXINDEX, MEMBER, MICROSECOND, MILLISECOND, MIN, MINELEMENT, MININDEX, MINUTE, MONTH, NANOSECOND, NATURALID, NEW, NEXT, NO, NOT, NULL, NULLS, OBJECT, OF, OFFSET, OFFSET_DATETIME, ON, ONLY, OR, ORDER, OTHERS, OUTER, OVER, OVERFLOW, OVERLAY, PAD, PARTITION, PERCENT, PLACING, POSITION, PRECEDING, QUARTER, RANGE, RESPECT, RIGHT, ROLLUP, ROW, ROWS, SEARCH, SECOND, SELECT, SET, SIZE, SOME, SUBSTRING, SUM, THEN, TIES, TIME, TIMESTAMP, TIMEZONE_HOUR, TIMEZONE_MINUTE, TO, TRAILING, TREAT, TRIM, TRUE, TRUNC, TRUNCATE, TYPE, UNBOUNDED, UNION, UPDATE, USING, VALUE, VALUES, VERSION, VERSIONED, WEEK, WHEN, WHERE, WITH, WITHIN, WITHOUT, YEAR, CHARACTER, STRINGLITERAL, JAVASTRINGLITERAL, INTEGER_LITERAL, FLOAT_LITERAL, HEXLITERAL, IDENTIFICATION_VARIABLE}; Bad JPQL grammar [select round(count(ri) * 100 / max(ri.receipt.positions), 0) as perc from StockOrderItem oi right join StockReceiptItem ri on ri.article = oi.article]
	at org.springframework.data.jpa.repository.query.BadJpqlGrammarErrorListener.syntaxError(BadJpqlGrammarErrorListener.java:39)
	at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)
	at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:543)
	at org.antlr.v4.runtime.DefaultErrorStrategy.reportUnwantedToken(DefaultErrorStrategy.java:377)
	at org.antlr.v4.runtime.DefaultErrorStrategy.singleTokenDeletion(DefaultErrorStrategy.java:548)
	at org.antlr.v4.runtime.DefaultErrorStrategy.sync(DefaultErrorStrategy.java:266)
	at org.springframework.data.jpa.repository.query.HqlParser.selectClause(HqlParser.java:3087)
	at org.springframework.data.jpa.repository.query.HqlParser.query(HqlParser.java:633)
	at org.springframework.data.jpa.repository.query.HqlParser.orderedQuery(HqlParser.java:507)
	at org.springframework.data.jpa.repository.query.HqlParser.queryExpression(HqlParser.java:434)
	at org.springframework.data.jpa.repository.query.HqlParser.selectStatement(HqlParser.java:380)
	at org.springframework.data.jpa.repository.query.HqlParser.ql_statement(HqlParser.java:311)
	at org.springframework.data.jpa.repository.query.HqlParser.start(HqlParser.java:250)
	at org.springframework.data.jpa.repository.query.HqlQueryParser.parseQuery(HqlQueryParser.java:53)
	at org.springframework.data.jpa.repository.query.HqlQueryParser.parse(HqlQueryParser.java:63)
	at org.springframework.data.jpa.repository.query.JpaQueryParserSupport$ParseState.lambda$new$0(JpaQueryParserSupport.java:182)
	at org.springframework.data.util.Lazy.getNullable(Lazy.java:245)
	at org.springframework.data.util.Lazy.get(Lazy.java:114)
	at org.springframework.data.jpa.repository.query.JpaQueryParserSupport$ParseState.getContext(JpaQueryParserSupport.java:194)
	at org.springframework.data.jpa.repository.query.JpaQueryParserSupport.findAlias(JpaQueryParserSupport.java:96)
	at org.springframework.data.jpa.repository.query.JpaQueryEnhancer.detectAlias(JpaQueryEnhancer.java:109)
	at org.springframework.data.jpa.repository.query.StringQuery.<init>(StringQuery.java:86)
	at org.springframework.data.jpa.repository.query.ExpressionBasedStringQuery.<init>(ExpressionBasedStringQuery.java:63)
	at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.<init>(AbstractStringBasedJpaQuery.java:78)
	at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:68)
	... 126 more

[gregturn]

That's merged to main and backported to 3.1.x.

[The-Arne]

Thank you.