[
https://issues.apache.org/jira/browse/OPENJPA-3009?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18125069#comment-18125069
]
ASF subversion and git services commented on OPENJPA-3009:
----------------------------------------------------------
Commit f3c41180332a463c9e3d27b53be84520835dcc90 in openjpa's branch
refs/heads/OPENJPA-3009 from Richard Zowalla
[ https://gitbox.apache.org/repos/asf?p=openjpa.git;h=f3c411803 ]
[OPENJPA-3009] Make a JPQL LIKE without ESCAPE mean the same on every database
Jakarta Persistence gives a LIKE pattern without an ESCAPE clause no escape
character at all, so a backslash in it is an ordinary character. H2, MySQL,
MariaDB and PostgreSQL default their LIKE to a backslash escape, so the same
query matched only on Derby.
OpenJPA already added an ESCAPE clause for a dictionary with
requiresSearchStringEscapeForLike, but filled it with the dictionary escape
character, which gave that character an escape meaning the query never asked
for. Write an explicit "no escape character" clause instead, and set the flag
for H2, whose LIKE has the same default. The expression used for it is the new
dictionary setting searchStringNoEscape, because the databases do not spell it
alike: '' is a non-standard spelling which H2 and PostgreSQL accept, MySQL
accepts it only while sql_mode has no NO_BACKSLASH_ESCAPES and rejects it with
"Incorrect arguments to ESCAPE" otherwise, MariaDB 11.4 and 11.8 ignore it
altogether and keep their backslash escape, and a standard conforming database
such as Derby rejects it because the standard wants exactly one character. Both
mysql family dictionaries therefore use CHAR(0), which behaves as no escape
character for every pattern without a NUL character and works in either
sql_mode, and a dictionary which turns the flag on has to supply a value its
own database accepts.
An ESCAPE clause which names the empty string is the same statement that there
is no escape character, so both the generated SQL and the in-memory evaluation
now treat it like a missing clause instead of passing '' through to the
database, which Derby rejects outright.
The pattern itself is passed through untouched, so the generated SQL still does
not depend on the pattern value and a parameterized LIKE stays eligible for the
prepared query cache. A query supplied ESCAPE clause keeps working unchanged,
and the in-memory Matches evaluation already treats a backslash literally, so
both paths now agree. A query supplied backslash escape is rendered from
searchStringEscape, which MySQL and MariaDB double, and only accept while
sql_mode has no NO_BACKSLASH_ESCAPES; the manual now says so for the migration
path it recommends.
> 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
> Priority: Major
>
> 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)
