[jira] [Created] (OPENJPA-3009) JPQL LIKE with an escaped wildcard matches only on Derby

["Richard Zowalla (Jira)" <ji...@apache.org>]

Richard Zowalla created OPENJPA-3009:
----------------------------------------

             Summary: JPQL LIKE with an escaped wildcard matches only on Derby
                 Key: OPENJPA-3009
                 URL: https://issues.apache.org/jira/browse/OPENJPA-3009
             Project: OpenJPA
          Issue Type: Bug
          Components: jpa
            Reporter: Richard Zowalla


TestEJBQLCondExpression.testLikeExpr fails on every database except Derby, which means OpenJPA's handling of the JPQL LIKE escape is Derby specific. It is not caught by CI because only Derby and H2 are run there, and the H2 failure has been treated as flakiness.

Measured with the JPQL test package (193 tests) on each profile, all on unmodified master:

  Derby          pass
  H2             testLikeExpr fails, expected:<1> but was:<0>
  PostgreSQL 11  testLikeExpr fails, expected:<1> but was:<0>
  MariaDB 11.8   testLikeExpr fails, expected:<1> but was:<0>
  MySQL 8.0      testLikeExpr fails, expected:<1> but was:<0>

The failing assertion is the one using a backslash escape in the pattern, i.e. the 'tes\%' case in openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jpql/expressions/TestEJBQLCondExpression.java (testLikeExpr). The query returns no row where one is expected, so the pattern is not matching the literal percent sign.

Worth checking: whether the generated SQL emits an ESCAPE clause at all, whether the escape character is passed through or doubled, and how each dictionary treats a backslash in a string literal (MySQL and MariaDB treat backslash as an escape character inside string literals by default, PostgreSQL does not with standard_conforming_strings on, Derby does not at all). DBDictionary.searchStringEscape and the LIKE handling in the expression tree are the places to start.

Also worth deciding whether the test itself encodes a Derby specific expectation, in which case the test needs splitting per dictionary and the behaviour documenting; the point either way is that today the same JPQL query gives different results depending on the database.

PostgreSQL additionally fails TestEJBQLFunction.testExtractHourFromLocalTime (expected:<-12> but was:<-11>), also on unmodified master; that one looks clock or timezone dependent and may be a separate issue.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)