[
https://issues.apache.org/jira/browse/OPENJPA-2935?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18142819#comment-18142819
]
ASF subversion and git services commented on OPENJPA-2935:
----------------------------------------------------------
Commit 1044eba94eeaeda8fd98a9d88d351849db8a87a8 in openjpa's branch
refs/heads/OPENJPA-3074 from Richard Zowalla
[ https://gitbox.apache.org/repos/asf?p=openjpa.git;h=1044eba94 ]
[OPENJPA-2935] Timestamp + coalesce + orderBy + Postgres makes Postgres syntax
errors (#296)
* [OPENJPA-2935] Bind temporal values of COALESCE, CASE and NULLIF as parameters
Literals in COALESCE, CASE and NULLIF expressions are rendered into the
SQL text. For a java.util.Date, Calendar or java.time value of a
criteria literal this wrote its toString() unquoted, e.g.
COALESCE(t0.due,2025-04-08 11:56:02.93), which every database rejects.
Such values are now bound as parameters.
* [OPENJPA-2935] Write temporal values of COALESCE, CASE and NULLIF as
date-time literals
Binding a temporal value as a parameter gave each copy of the expression
its own placeholder, so PostgreSQL and Derby rejected it with DISTINCT
and ORDER BY or with GROUP BY, and a CASE with only temporal results had
no known type. Dates, times and timestamps, including Calendar and the
java.time local types, are now written as JDBC escaped date-time
literals, which are typed and identical in every clause. Other values
without a literal form, such as a Character, are bound instead of being
written as their unquoted toString(), and GROUP BY keeps the parameters
of the grouped expression.
* [OPENJPA-2935] Add the issue to the release notes
* [OPENJPA-2935] Write date-time literals as typed literals on MySQL and MariaDB
Connector/J and MariaDB Connector/J translate a JDBC escaped date-time
literal into a plain string literal, so COALESCE or CASE of a DATETIME
column and such a literal had a string result, which could not be
converted back into a Timestamp. MySQL and MariaDB now write the SQL
standard typed literals DATE '...', TIME '...' and TIMESTAMP '...',
which keep the date-time type. A Timestamp result of COALESCE, CASE or
NULLIF is loaded with getTimestamp, like a Date or Time result, as
Connector/J returns a LocalDateTime for a DATETIME value.
* [OPENJPA-2935] Load a java.util.Date result of COALESCE, CASE and NULLIF like
a java.util.Date field
A java.util.Date result was read with getObject, and Connector/J returns
a LocalDateTime for a DATETIME value, which could not be converted into
a java.util.Date. It is now read through the dictionary like a
java.util.Date field.
---------
Co-authored-by: Maxim Solodovnik <[email protected]>
> Timestamp + coalesce + orderBy + Postgres makes Postgres syntax errors
> ----------------------------------------------------------------------
>
> Key: OPENJPA-2935
> URL: https://issues.apache.org/jira/browse/OPENJPA-2935
> Project: OpenJPA
> Issue Type: Bug
> Components: jdbc
> Affects Versions: 3.1.2
> Reporter: Pawel Veselov
> Assignee: Richard Zowalla
> Priority: Major
> Fix For: 4.2.0
>
>
> {code:java}
> public class E {
> @Column(name = "due")
> private Timestamp due;
> }
> {code}
>
> Using criteria API, when querying from E, say:
> Timestamp ts = new Timestamp();
> {{query.orderBy(cb.asc(cb.coalesce(root.get(E_.due), ts)))}}
> Same if using {{cb.literal()}}
> Results in:
> {noformat}
> Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: syntax
> error at or near "11"
> Position: 77 {prepstmnt 575413488 SELECT t0.X, COALESCE(t0.due,2025-04-08
> 11:56:02.93), t0.id FROM E t0 ORDER BY COALESCE(t0.due,2025-04-08
> 11:56:02.93) ASC, t0.id ASC LIMIT ? [params=(long)
> 1]} [code=0, state=42601]
> {noformat}
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
