[
https://issues.apache.org/jira/browse/OPENJPA-2966?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18106355#comment-18106355
]
Richard Zowalla commented on OPENJPA-2966:
------------------------------------------
I think both of your observations point at the same answer.
On the first point, {{decimalTypeName}} does not "perform flawlessly", it just
fails quietly. An unsized {{DECIMAL}} is {{DECIMAL(10,0)}} on MySQL/MariaDB, so
{{CAST(12345678901 AS DECIMAL)}} yields {{9999999999}} — clamped, with only a
warning in the default non-strict handling of a cast, and an out-of-range error
under stricter settings. Either way that is any id past ten billion, silently
corrupted or failing for the wrong reason. It is already worse elsewhere:
Derby's unsized {{DECIMAL}} is {{(5,0)}} and SQL Server's default {{decimal}}
precision is 18, so both raise a range error for ordinary long values today.
Your second point is the key one (imho): {{CAST(x AS BIGINT)}} really is
invalid on MySQL/MariaDB, and the correct form is {{SIGNED}} ({{{}SIGNED
INTEGER{}}} is accepted as a synonym). That target is a full 64-bit signed
value, so it covers exactly the Java {{long}} range including
{{{}Long.MIN_VALUE{}}}/{{{}Long.MAX_VALUE{}}}. Your worry about overflow with a
64-bit target is real, but that is the right behaviour: the JPQL result is
converted to a Java {{long}} anyway, so a value that does not fit a 64-bit cast
does not fit a Java {{long}} either, and a loud error beats a silently
truncated number, IMHO. Using {{SIGNED}} also lines up with what
{{MySQLDictionary}} already does for the int case.
On {{bigintTypeName = "DECIMAL(30)"}} for MySQL/MariaDB - please don't. That
field is a DDL type name: {{DBDictionary.getTypeName(int)}} returns it for
{{{}Types.BIGINT{}}}, which feeds {{getDeclareColumnSQL()}} (so {{CREATE
TABLE}} and {{{}ADD COLUMN{}}}) and the schema comparison in
{{{}Column.equalsColumn(){}}}. Redefining it would turn every {{long}} column
into {{{}DECIMAL(30){}}}, break {{AUTO_INCREMENT}} on
{{@GeneratedValue(IDENTITY) Long id}} (MySQL: {{{}ERROR 1063 Incorrect column
specifier for column{}}}), and flag every existing {{BIGINT}} column as
incompatible on schema validation. The dictionaries that do point
{{bigintTypeName}} at a DECIMAL type (DB2 z/OS v8, Sybase, Informix) do it
because their database had no {{BIGINT}} column type when the dictionary was
written - that is a DDL problem, not a cast problem, and it is not our
situation here.
The right place to fix this is the one this codebase already established for
exactly this case: a cast-only field, alongside {{integerCastTypeName}} ({{{}=
"SIGNED"{}}} in {{{}MySQLDictionary{}}}), {{typecastToStringTypeName}} and
{{{}supportsUnsizedCharOnCast{}}}. Add {{{}longCastTypeName{}}}, default it to
{{bigintTypeName}} resolved _lazily_ (not in a field initializer because
{{DB2Dictionary}} assigns {{bigintTypeName}} in {{connectedConfiguration()}}
for z/OS v8, and Oracle/Sybase/Informix assign it in their constructors, all
after field initialization), set it to {{"SIGNED"}} in {{MySQLDictionary}} and
{{{}MariaDBDictionary{}}}, and leave every DDL field untouched.
I also found, that {{MariaDBDictionary}} extends {{{}DBDictionary{}}}, not
{{{}MySQLDictionary{}}}, so it inherits none of MySQL's cast fixes, i.e. every
one has to be applied twice.
I have opened a PR for discussion.
> Long cast uses decimalTypeName instead of bigint
> ------------------------------------------------
>
> Key: OPENJPA-2966
> URL: https://issues.apache.org/jira/browse/OPENJPA-2966
> Project: OpenJPA
> Issue Type: Sub-task
> Components: jpa
> Affects Versions: 4.2.0
> Reporter: Maxim Solodovnik
> Assignee: Maxim Solodovnik
> Priority: Major
> Fix For: 4.2.0
>
>
> Discussion thread:
> https://github.com/apache/openjpa/pull/144#discussion_r3683002577
> **(medium)** Casting to Long uses {{dict.decimalTypeName}}; on MySQL {{CAST(x
> AS DECIMAL)}} defaults to DECIMAL(10,0), so large long values
> overflow/truncate - why not {{bigintTypeName}} for the long case?
> Also {{getDbNumberTargetTypeName}} sanitizes the {{{0}}} size suffix while
> the sibling {{TypecastAsString.java:152}} appends {{dict.varcharTypeName}}
> raw - the two siblings should share the same sanitize logic.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
