[
https://issues.apache.org/jira/browse/OPENJPA-2555?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14269269#comment-14269269
]
Ancoron Luciferis edited comment on OPENJPA-2555 at 1/8/15 12:35 PM:
---------------------------------------------------------------------
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 (see
http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html):
{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}
was (Author: ancoron):
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)
