[
https://issues.apache.org/jira/browse/OPENJPA-2555?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14269269#comment-14269269
]
Ancoron Luciferis commented on OPENJPA-2555:
--------------------------------------------
Found some other problems in some tests with MySQL and newer JDBC driver
versions caused by the MySQL default precision of "0", as stated also in the
official documentation:
{quote}
To define a column that includes a fractional seconds part, use the syntax
{{type_name(fsp)}}, where {{type_name}} is {{TIME}}, {{DATETIME}}, or
{{TIMESTAMP}}, and {{fsp}} is the fractional seconds precision. For example:
{noformat}CREATE TABLE t1 (t TIME(3), dt DATETIME(6));{noformat}
The {{fsp}} value, if given, must be in the range 0 to 6. A value of 0
signifies that there is no fractional part. *If omitted, the default precision
is 0*. (This differs from the standard SQL default of 6, for compatibility with
previous MySQL versions.)
{quote}
> Timestamp precision from manual schema not respected
> ----------------------------------------------------
>
> Key: OPENJPA-2555
> URL: https://issues.apache.org/jira/browse/OPENJPA-2555
> Project: OpenJPA
> Issue Type: Bug
> Components: jdbc, jpa, sql
> Affects Versions: 2.2.2, 2.3.0
> Reporter: Ancoron Luciferis
> Fix For: 2.2.3, 2.3.1, 2.4.0
>
> Attachments: 2.2.x-Enable-timestamp-precision-handling.patch,
> 2.3.x-Enable-timestamp-precision-handling.patch,
> trunk-Enable-timestamp-precision-handling.patch
>
>
> The use cases here are the following:
> # JPA entities are to-be-created for an existing database schema which
> includes several timestamp columns with explicit precision
> # A developer wants to specify timestamp precision inside JPA entities to
> better specify column data type information for the generated schema
> \\
> In both cases, the result will be that any query executed for a timestamp
> column that is configured for less than millisecond precision (e.g. deci- or
> centi-seconds) will fail to find appropriate rows.
> One of the reasons for that is that the precision used for rounding a
> timestamp value before it goes into a query is configured for a whole
> database type (using the dictionary) or the whole persistence context (using
> the configuration parameter).
> This makes it impossible to have different column configurations, e.g. some
> without any precision declaration (where it's not important) but some with.
> In addition, the default precision for the standard timestamp data type is 6
> (microseconds), which is not respected by some databases (most prominently
> MySQL, which defaults to a precision of "0" instead).
> However, even if respected, when using timestamps generated by the database
> itself, which include the relevant precision, using those values for later
> comparison often fails because of precision mismatch and also for different
> behavior of different databases regarding fractional handling and the way how
> comparisons on timestamps work.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
