nativeQuery "SELECT * FROM [...]" in parent interface throws grammar exception

[Andrei-Paul]

We have:

// EDIT: UPDATED

public interface GenericRepository<E extends AbstractEntity, ID> extends JpaRepository<E, ID> {
    @Query(value = "SELECT * FROM #{#entityName} ORDER BY `Id` ASC LIMIT :index, 1", nativeQuery = true)
    E findAtMost1(Integer index);
}

public interface ConcreteRepository extends GenericRepository<ConcreteEntity, UUID> {
}

public abstract class AbstractService<E extends AbstractEntity> implements GenericService<E> {
    GenericRepository<E, Object> repo;
    Integer Index = -1;

    public AbstractService(GenericRepository repo) {
        this.repo = repo;
    }

    @Override
    public E findAtMost1() {
        this.oneIndex++;
        return this.repo.findAtMost1(index);
    }
}

@Service
public class ConcreteService extends AbstractService<ConcreteEntity> {

    @Autowired
    ConcreteRepository repo;

    public ConcreteService(ConcreteRepository repo) {
        super(repo);
        this.repo = repo;
    }
}

If we call:

@Autowired
ConcreteService concreteService;
// ...
concreteEntity = concreteService.findAtMost1(0);

With 3.1.2 it results in:

org.springframework.dao.InvalidDataAccessApiUsageException: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:7 extraneous input '*' 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, FLOOR, 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, BINARY_LITERAL, IDENTIFICATION_VARIABLE}; Bad JPQL grammar [SELECT * FROM ConcreteEntityTable ORDER BY Id ASC LIMIT :index, 1]
at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:371)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:234)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:244)
at jdk.proxy2/jdk.proxy2.$Proxy137.findAtMost1(Unknown Source)
at test.example.logic.concrete.AbstractService.getOne(AbstractService.java:46)
at test.example.ConcreteEntityTests.get_ConcreteEntityTableService(ConcreteEntityTests.java:214)
at java.base/java.util.ArrayList.forEach(Unknown Source)
at java.base/java.util.ArrayList.forEach(Unknown Source)

Caused by: java.lang.IllegalArgumentException: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:7 extraneous input '' 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, FLOOR, 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, BINARY_LITERAL, IDENTIFICATION_VARIABLE}; Bad JPQL grammar [SELECT * FROM ConcreteEntityTable ORDER BY Id ASC LIMIT :index, 1]
at org.springframework.data.jpa.repository.query.JpaQueryParserSupport.renderSortedQuery(JpaQueryParserSupport.java:56)
at org.springframework.data.jpa.repository.query.JpaQueryEnhancer.applySorting(JpaQueryEnhancer.java:88)
at org.springframework.data.jpa.repository.query.JpaQueryEnhancer.applySorting(JpaQueryEnhancer.java:100)
at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.doCreateQuery(AbstractStringBasedJpaQuery.java:96)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createQuery(AbstractJpaQuery.java:234)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:223)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:92)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:148)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:136)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:136)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:120)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:164)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:72)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
... 79 more
Caused by: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:7 extraneous input '' 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, FLOOR, 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, BINARY_LITERAL, IDENTIFICATION_VARIABLE}; Bad JPQL grammar [SELECT * FROM ConcreteEntityTable ORDER BY Id ASC LIMIT :index, 1]
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:3577)
at org.springframework.data.jpa.repository.query.HqlParser.query(HqlParser.java:1194)
at org.springframework.data.jpa.repository.query.HqlParser.orderedQuery(HqlParser.java:1068)
at org.springframework.data.jpa.repository.query.HqlParser.queryExpression(HqlParser.java:454)
at org.springframework.data.jpa.repository.query.HqlParser.selectStatement(HqlParser.java:387)
at org.springframework.data.jpa.repository.query.HqlParser.ql_statement(HqlParser.java:318)
at org.springframework.data.jpa.repository.query.HqlParser.start(HqlParser.java:256)
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.renderSortedQuery(JpaQueryParserSupport.java:54)
... 99 more

[gregturn]

Since this appears to be an abstract base, what happens if you apply @NoRepositoryBean to this interface definition?

See https://docs.spring.io/spring-data-commons/reference/repositories/definition.html#repositories.definition-tuning for more details.

[Andrei-Paul]

Same result.

I've updated the example code.

I just checked with 3.0.4, 3.0.9 and it works, but 3.1.2 throws exception.

[gregturn]

Okay, I've reproduced this with a much more simplified Spring Boot sample app...

@NoRepositoryBean
public interface GenericBaseRepository<E, ID> extends JpaRepository<E, ID> {

	@Query(value = "select * from #{#entityName}", nativeQuery = true)
	List<E> custom();
}

public interface EmployeeRepository extends GenericBaseRepository<Employee, Long> {
}

@SpringBootApplication
public class SpringDataJpaWithNativeQueryInBaseInterfaceApplication {

	public static void main(String[] args) {
		SpringApplication.run(SpringDataJpaWithNativeQueryInBaseInterfaceApplication.class, args);
	}

	@Bean
	CommandLineRunner initDatabase(EmployeeRepository repository) {
		return args -> {
			repository.saveAndFlush(new Employee("Frodo Baggins"));

			repository.findAll().forEach(System.out::println);

			repository.custom().forEach(System.out::println);
		};
	}
}

Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:7 extraneous input '*' 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, FLOOR, 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, BINARY_LITERAL, IDENTIFICATION_VARIABLE}; Bad JPQL grammar [select * from Employee]
	at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:371) ~[spring-orm-6.0.11.jar:6.0.11]
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:234) ~[spring-orm-6.0.11.jar:6.0.11]
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550) ~[spring-orm-6.0.11.jar:6.0.11]
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-6.0.11.jar:6.0.11]
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) ~[spring-tx-6.0.11.jar:6.0.11]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152) ~[spring-tx-6.0.11.jar:6.0.11]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.11.jar:6.0.11]
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135) ~[spring-data-jpa-3.1.3-20230810.205911-47.jar:3.1.3-SNAPSHOT]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.11.jar:6.0.11]
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-6.0.11.jar:6.0.11]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.11.jar:6.0.11]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:244) ~[spring-aop-6.0.11.jar:6.0.11]
	at jdk.proxy2/jdk.proxy2.$Proxy99.custom(Unknown Source) ~[na:na]
	at com.example.springdatajpawithnativequeryinbaseinterface.SpringDataJpaWithNativeQueryInBaseInterfaceApplication.lambda$initDatabase$0(SpringDataJpaWithNativeQueryInBaseInterfaceApplication.java:22) ~[classes/:na]
	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:771) ~[spring-boot-3.1.3-20230810.144159-61.jar:3.1.3-SNAPSHOT]
	... 5 common frames omitted

I'm going to try and capture this as a test case inside Spring Data JPA.

[gregturn]

It looks like the processing of select * from #{#entityName} -> select * from Employee is where it loses the isNative=true attribute.

	public ExpressionBasedStringQuery(String query, JpaEntityMetadata<?> metadata, SpelExpressionParser parser,
			boolean nativeQuery) {
		super(renderQueryIfExpressionOrReturnQuery(query, metadata, parser), nativeQuery && !containsExpression(query));
	}

Looks like that additional !containsExpression(query) is where the nativeQuery aspect gets lost, hence steering it into the grammar parser.