Parameter binding malfunctions when native query contains question marks

[ThomasKasene]

In a PostgreSQL database I've set up a JSONB-column containing an array of string values, and my end goal is to use a native @Query in a Spring Data JPA repository to retrieve the entity where this column's array contains a certain value.

Unfortunately, I'm stuck on what I assume is an issue with Spring Data JPA's interpretation of the query, and more specifically, how it handles the ?-marks in the query. I tried using the escaping (\\?\\?) that was implemented in #2551, and while I don't get the error message that was mentioned in that issue, I get others instead:

Case 1:

Required name for ParameterBinding [name: null, position: 1, expression: null] not available!

The query I'm trying to make work looks like this:

@Query(nativeQuery = true, value = """
        select *
        from PET
        where TAGS_JSON -> 'tags' \\?\\? :tag
        """)
Pet findByTag(String tag);

Case 2:

If I try to hardcode the value instead of passing it as a parameter, I get a different error message:

At least 1 parameter(s) provided but only 0 parameter(s) present in query.

Query:

@Query(nativeQuery = true, value = """
        select *
        from PET
        where TAGS_JSON -> 'tags' \\?\\? '2222'
        """)
Pet findByTagEquals2222();

I've put together a reproducible example project with some tests that illustrate the symptoms, and tried running them with Spring Data JPA 2.6.7 and 2.7.3. Both give the same results. Please let me know if there's something I'm doing wrong 😃

[ThomasKasene]

I should point out that these errors occur during calls to the repository methods, not during the context initialization. Unless I completely misunderstand the tests that were introduced as part of #2551, they only test context initialization, which might be how the errors escaped detection in that situation.

[gregturn]

Thanks for doing the extra research @ThomasKasene. I created two extra @Query method in that test case...

@Query("SELECT * FROM table WHERE (json_col->'jsonKey')::jsonb \\?\\? '2222'")
List<User> customQueryWithQuestionMarksAndNoArgs();

@Query(nativeQuery = true, value = "SELECT * FROM table WHERE (json_col->'jsonKey')::jsonb \\?\\? '2222'")
List<User> customQueryWithQuestionMarksAndNoArgsAndNative();

Turns out, the first one works just fine but the second one blows up as an invalid query. This suggests the problem is our native query handler. Looking back at all the test cases written, not one uses native queries.

[gregturn]

For native queries, we delegate to JSqlParser, and in this scenario, it's JSqlParser that is throwing an exception:

net.sf.jsqlparser.JSQLParserException: Lexical error at line 1, column 56.  Encountered: "\\" (92), after : ""

It turns out, you're not the first one to ask for support from JSqlParser to support such a query. See JSQLParser/JSqlParser#1434

[ThomasKasene]

Hmm... If I try to replicate your query, but in that example project I linked to, then the context initialization fails. Note that I'm no PostgreSQL expert and I don't know whether this change actually makes any sense:

@Query(value = """
        select *
        from PET
        where (TAGS_JSON -> 'tags')::jsonb \\?\\? :tag
        """)
Pet findByTag(String tag);

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract com.thomaskasene.springdatajpa.Pet com.thomaskasene.springdatajpa.PetRepository.findByTag(java.lang.String)!
	at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:96)
	at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:66)
	at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:51)
	at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:169)
	at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:253)
	at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:93)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:103)
	... 110 more
Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: unexpected char: '\' [select *
from PET
where (TAGS_JSON -> 'tags')::jsonb \?\? :tag
]
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:757)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:114)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362)
	at jdk.proxy2/jdk.proxy2.$Proxy121.createQuery(Unknown Source)
	at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:90)
	... 116 more
Caused by: org.hibernate.QueryException: unexpected char: '\' [select *
from PET
where (TAGS_JSON -> 'tags')::jsonb \?\? :tag
]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:236)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
	at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162)
	at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:636)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:748)
	... 124 more

[gregturn]

Sorry, I was running these tests against Spring Data JPA 3.0 (main branch). Running the following against Spring Data JPA's 2.7.x branch...

Method otherMethod = UserRepository.class.getMethod("customQueryWithQuestionMarksAndNoArgs", String.class);
RepositoryMetadata otherMetadata = new DefaultRepositoryMetadata(UserRepository.class);

strategy.resolveQuery(otherMethod, otherMetadata, projectionFactory, namedQueries);

@Query(value = "select * from PET where (TAGS_JSON -> 'tags')::jsonb \\?\\? :tag")
List<User> customQueryWithQuestionMarksAndNoArgs(String tag);

My test case went green. Naturally, I switched back to Java 8 to do this test run and dropped the multi-line string to a single line string.

[gregturn]

Let me grab the reproducer and work from there.

[gregturn]

Hmm...I can't tell where the problem is. I reached out to the person involved in #2551, because that solution worked from them, but they never posted any details.

[ThomasKasene]

Thanks for your efforts so far 😃

Let me know if there's anything else I can do, although I hope it won't come to submitting a PR with JSqlParser project!

At the risk of going a little off-topic (but it's the reason why I'm defining my query the way I am,) I'm wondering if you know whether the suggested query (where (TAGS_JSON -> 'tags')::jsonb \\?\\? :tag) would be able to hit an index like this?

CREATE INDEX PET_TAG ON PET USING gin ((TAGS_JSON -> 'tags'));

[gregturn]

I frankly don't know Postgres handles these. My instinct would be somewhere along the way, surely there's an opportunity to hit an index. I guess I'd have to dig in deep on Google Fu, or at least the Postgres user groups. I never managed indexes directly. We always had someone from DBA team (previous position before Spring team) to do that and suitably coach us.

[ThomasKasene]

Just a little notice that they replied in #2551:
#2551 (comment)

[usr42]

The best solution/workaround which worked for me was not to use the ? operator, but using it's alias function jsonb_exists(jsonb, text). How to find these alias funcions see e.g. yiisoft/yii2#15873 (comment)

[ThomasKasene]

Yeah, but from what I've read (I haven't tested it myself,) the aliases don't utilize the indexes. For the moment I've resorted to using JdbcTemplate directly, but I'm still hoping this issue will get resolved someday too!

[kubav182]

You are right, the function does not hit the index, while "?" operator does. There is workaround to implement this select via JdbcTemplate or EntityManager. I provided solution to fix this error in Spring Data JPA #2551 as mentioned above.

[gregturn]

This situation appears too complicated for QueryUtils to handle, and based on all the back and forth, I don't see a likely change that will be added to QueryUtils that supports your needs directly, especially with such database-specific custom functions.

Your best best moving forward may be the custom implementation you've to implement a custom implementation. Check out https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.custom-implementations if you need more details about how that works.

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

[spring-projects-issues]

Closing due to lack of requested feedback. If you would like us to look at this issue, please provide the requested information and we will re-open the issue.