search

impossibl / pgjdbc-ng

A new JDBC driver for PostgreSQL aimed at supporting the advanced features of JDBC and Postgres
https://impossibl.github.io/pgjdbc-ng
Other
596 stars 108 forks source link

Parameter Parsing fails on concat operator in SQLText#parse #590

Open FrostbittenKing opened 1 year ago

FrostbittenKing commented 1 year ago

Hi, Since changes in spring boot 3.1.1 (to be precise, in spring-data-jpa 3.1.1), SQLText fails to correctly parse expressions like ....where my_number like('%'||?||'%'). spring boot replaces where my_number like %:paramName% with where my_number like CONCAT('%',:paramName,'%') and hibernate in turn translates this to where my_number like ('%'||?||'%'). Unfortunately SQLText incorrectly detects here the jsonb operator ?| / ?&, instead of recognizing it as a concat operation. As far as I can tell, this regression was introduced in this commit. https://github.com/impossibl/pgjdbc-ng/commit/b41dac82b1f4b7fc7ae2f2315d49eaf1c0459d69

I have experimented with some workarounds.

  1. Patch hibernates class ConcatPipeFunction, to generate a translated statement of where my_number like ('%' || ? || '%') (added whitespaces), which makes SQLText again correctly parse the expressions parameters.
  2. Patch SQLText to lookahead another character if there is another pipe character, suggesting a concat. Idk if this works all the time, or if there need to be more checks.
  3. Replace jpql queries with || expression directly so, instead of hibernate translating it to '%'||?||'%', I write it myself exactly as '%' || ? || '%').

I guesss, Quickest fix for me would be Nr. 1. This fix has less complexity than Nr 2, But Nr 2. would be better in the long term.