[
https://issues.apache.org/jira/browse/OPENJPA-3028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18140094#comment-18140094
]
ASF subversion and git services commented on OPENJPA-3028:
----------------------------------------------------------
Commit 9d20216f36190073064feeab0006d3e66bc8187c in openjpa's branch
refs/heads/OPENJPA-3028-oracle-procedures from Richard Zowalla
[ https://gitbox.apache.org/repos/asf?p=openjpa.git;h=9d20216f3 ]
[OPENJPA-3028] Oracle: EXTRACT, CURRENT_TIME/CURRENT_DATE and RIGHT (#278)
* [OPENJPA-3028] Support EXTRACT of all JPA fields on Oracle
Oracle only allows EXTRACT(HOUR/MINUTE/SECOND) on TIMESTAMP values and
failed with ORA-30076 for time and date-time attributes held in DATE
columns; the value is now cast to TIMESTAMP first, which also keeps the
fraction of the second. Oracle has no EXTRACT(QUARTER) and no
EXTRACT(WEEK) (ORA-00907); they are now read with TO_CHAR, IW being the
ISO week that JPA defines.
Behaviour change: the cast to TIMESTAMP drops the time zone of a
TIMESTAMP WITH TIME ZONE column, which is how OffsetDateTime and other
TIMESTAMP_WITH_TIMEZONE attributes are mapped on Oracle. EXTRACT(HOUR)
and EXTRACT(MINUTE) on such an attribute already worked, but returned
the hour and minute in UTC; they now return those of the stored offset,
as OffsetDateTime.getHour() and getMinute() do. For 13:45+02:30, for
example, EXTRACT(HOUR) was 11 and is now 13.
TestExtractDateTimeField and TestEJBQLFunction no longer skip these
fields on Oracle, and TestExtractDateTimeField pins the hour and minute
of an OffsetDateTime on Oracle.
* [OPENJPA-3028] Support CURRENT_TIME and LOCAL TIME on Oracle
Oracle has no CURRENT_TIME function, so CURRENT_TIME, LOCAL TIME and
CriteriaBuilder.currentTime()/localTime() failed with ORA-00904. Times
are held in DATE columns and the driver binds a time as that time of
day on 1970-01-01, so the current time is now computed the same way from
CURRENT_DATE, which keeps comparisons with stored times correct.
The tests that skipped Oracle because of the missing function now run
there too.
* [OPENJPA-3028] Return CURRENT_DATE without the time of day on Oracle
The CURRENT_DATE of Oracle is a DATE that holds the current time of day,
so CURRENT_DATE, LOCAL DATE and CriteriaBuilder.currentDate() never
equalled a date stored for today, and comparisons such as
"d < CURRENT_DATE" also matched today's dates. The value is now
truncated to midnight with TRUNC(CURRENT_DATE).
Behaviour change on Oracle: a date of today now equals CURRENT_DATE and
is no longer less than it.
* [OPENJPA-3028] Support EXTRACT(DATE) and EXTRACT(TIME) on Oracle
EXTRACT(DATE) and EXTRACT(TIME) were always rendered as CAST(x AS DATE)
and CAST(x AS TIME). Oracle has no TIME type, so EXTRACT(TIME) failed
with ORA-00932, and an Oracle DATE has a time of day, so EXTRACT(DATE)
kept the time and did not equal the date it was extracted from.
The SQL now comes from the new DBDictionary.getExtractDateTimePartFunction,
which keeps the CAST for other databases. On Oracle the date is truncated
to midnight, and the time is the time of day on 1970-01-01, the way times
are stored and bound there. As such a time is a DATE, EXTRACT(TIME) of a
TIMESTAMP value has whole seconds only on Oracle, its fraction of the
second is dropped. The value occurs twice in the SQL of EXTRACT(TIME) on
Oracle, so a parameter is bound twice.
The tests that skipped Oracle because of the missing TIME type now run
there too.
* [OPENJPA-3028] Fix RIGHT for lengths beyond the string on Oracle
RIGHT(str, n) was rendered as SUBSTR(str, -n), which Oracle evaluates
to NULL when n is larger than the length of the string, so for example
RIGHT(name, 100) returned NULL instead of the whole name. The start
position is now computed from the length of the string, so RIGHT returns
the whole string when n exceeds its length. The string occurs twice in
the SQL, so a string parameter is bound twice.
Behaviour change on Oracle: RIGHT(str, 0) returned the whole string,
because SUBSTR(str, 0) starts at the first character; it now returns
NULL, which is the empty string of Oracle. A negative n, which JPA does
not define, returned the substring starting at position -n when given as
a parameter, and now returns NULL as well.
TestOracleDictionary checks the SQL of RIGHT, and TestEJBQLFunction
checks RIGHT with a length of 0 on every database.
* [OPENJPA-3028] Run TestEJBQLFunction.testExtractWEEK on Derby
The test still skipped Derby with the comment that Derby has no function
for the ISO week. DerbyDictionary computes the ISO week since #273 (see
TestExtractDateTimeField), so the test now runs on Derby as well.
* [OPENJPA-3028] Test SUBSTRING from a position behind the end of the string
> Jakarta Persistence 3.2 TCK failures on Derby, MySQL and SQL Server
> -------------------------------------------------------------------
>
> Key: OPENJPA-3028
> URL: https://issues.apache.org/jira/browse/OPENJPA-3028
> Project: OpenJPA
> Issue Type: Epic
> Reporter: Richard Zowalla
> Assignee: Richard Zowalla
> Priority: Major
> Attachments: run_de.tar.xz, run_ms.tar.xz, run_my.tar.xz,
> run_or.tar.xz
>
>
> Results of the Jakarta Persistence 3.2 TCK with
> {{openjpa-integration/tck/run-tck32.sh}} (reported by Maxim Solodovnik on
> dev@):
> * PostgreSQL 16: passed
> * MySQL 8.4.8: 2134 tests, 43 errors
> * SQL Server 2017-CU12: 2134 tests, 45 errors
> * Derby: 2134 tests, 184 errors
> The sub-tasks group the errors by cause. Several groups show up on more than
> one database. Where the TCK output only says "<test> failed", the cause still
> has to be found from the TCK logs.
> Note: the TCK build does not work with Maven 3.10.0; 3.9.x works.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
