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 😃
          
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.
          
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.
          
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
          
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
          
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.
          
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'));
          
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.
          
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.
  status: feedback-reminder
  We've sent a reminder that we need additional information before we can continue
 label
    May 5, 2023
  status: feedback-reminder
  We've sent a reminder that we need additional information before we can continue
  labels
    May 12, 2023