Explain plan support for Hibernate6 / postgresql

[jtduffy]

Description

From slack discussion:

customer is seeing DB errors after upgrading to Hibernate 6. They noticed that New Relic modifies Hibernate 6-generated SQL by prepending EXPLAIN (FORMAT JSON), which is causing syntax errors at the DB level, not in the application.

EXPLAIN (FORMAT JSON) select...where f1_0.grant_app_id = any (?) and (f1_0.grant_app_field_row_id is null)

ERROR: syntax error at or near ")"
LINE 1: ...g3_0.grant_app_id where f1_0.grant_app_id = any (?) and (f1_...
^

SQL state: 42601
Character: 1361

Customer mentioned Hibernate 5 issued SQL of the format "in (?)" whereas Hibernate has "= any (?)" and suspected the Java agent (they’re currently using 8.18.0) doesn't support this format.
Customer's environment - DB is PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit

Workaround: Disable running the explain plans by setting the transaction_tracer.explain_enabled property to false

Internal slack discussion

Update
Example SQL statement

select * from grant_app_field f1_0 join standard_field_grant_app f1_1 on f1_1.id = f1_0.standard_field_grant_app_id
left join grant_app_field_row g3_0 on g3_0.id = f1_0.grant_app_field_row_id 
left join grant_app g4_0 on g4_0.id = g3_0.grant_app_id 
where f1_0.grant_app_id = any (?) and (f1_0.grant_app_field_row_id is null)

[workato-integration]

https://new-relic.atlassian.net/browse/NR-379077