Question mark not escaped in a query (using PostgreSQL JSON data)

[s4iko]

Hello,

I tried to do a query on a PostgresSQL database with JSON content, something like:
select * from mytable where mytable -> 'myelement' ? 'key123';
It doesn't work as this in a native query because the question mark is interpreted as a parameter.
I searched for help on this and apparently, the question mark could be escaped using one of:

• ?? to escape in JDBC
• \\?\\? to escape in Hibernate

None of those 2 solutions worked but should it really work ? I didn't see anything about this in the documentation... may be I missed something.

If escaping the character is not an acceptable solution, may be it is possible to add a parameter in a future version to disable the "JDBC-style parameters" and force to use :myparam or ?1 ?
I saw in the documentation that "JDBC-style parameters" have a skull head, it probably means it is a devil feature or at least a feature that we should not use so optionally not interpreting this seems to be an acceptable solution ?

As a reference, the same issue fixed in Spring Data JPA: spring-projects/spring-data-jpa#2551

Quarkus: 3.15.1
Hibernate Reactive: 2.4.0

Thank you for your help.

[DavideD]

Doesn't this work?

select * from mytable where mytable -> 'myelement' \\? 'key123'

[gavinking]

I just tried it in Hibernate ORM 7, and \\?\\? appears to work as expected.

[s4iko]

Thanks for all your answers.

I confirm that it works with hibernate ORM but not with Hibernate Reactive.
I made 2 reproducers by generating new projects using Quarkus starter, 1st for ORM, 2nd for Reactive version.
Both contains those tests:

1- query with ?
2- query with \\?
3- query with \\?\\?
4- query with \\\\?\\\\?

The #3 work with Hibernate ORM.
Unfortunately, none work with Hibernate Reactive.

I attached the reproducers I hope it can help :)

code-with-quarkus-postgres.zip
code-with-quarkus-postgres-reactive.zip

You can run ITs with the command: mvn verify -DskipITs=false

[DavideD]

Thanks for the tests! I think what's happening is that Hibernate Reactive receives the query after Hibernate ORM already did some transformation to it. I'm having a look.

[DavideD]

As a workaround, this should work:

jsonb_content -> 'key1' \\\\\\? 'k123'

[DavideD]

I think at the moment the workaround I suggested is the easiest way to solve this issue.

The problem is that Hibernate ORM doesn't replace parameters markers for native queries (HHH-16283). So we need to still run our parameter processor to replace the ? (with $1 in Postgres, for example).

I think we should wait until HHH-16283 has been solved. And then we can remove the extra processing of the query.

I'm open to suggestions though.