CAST of floating point types yields unnecessary string truncation error and
does not conform to SQL standard requirements
-------------------------------------------------------------------------------------------------------------------------
Key: CORE-6508
URL: http://tracker.firebirdsql.org/browse/CORE-6508
Project: Firebird Core
Issue Type: Bug
Affects Versions: 4.0 RC 1, 3.0.7
Reporter: Mark Rotteveel
Attempts to cast floating point values to a string of a short length can result
in errors like
"""
arithmetic exception, numeric overflow, or string truncation; string right
truncation; expected length 5, actual 17
"""
For example
```
select cast(0e0 as varchar(5)) from rdb$database;
select cast(1e0 as varchar(5)) from rdb$database;
```
The problem is that Firebird renders the string as '0.000000000000000' or
'1.000000000000000'.
The SQL standard says on this subject in 6.13 <cast specification>:
"""
11) If TD is fixed-length character string, then let LTD be the length in
characters of TD.
Case:
[...]
b) If SD is approximate numeric, then:
i) Let YP be a character string as follows.
Case:
1) If SV equals 0 (zero), then YP is '0E0'.
2) Otherwise, YP is the shortest character string that conforms to the
definition of <approximate numeric literal> in Subclause 5.3, "<literal>",
whose interpreted value is equal to the absolute value of SV and whose
<mantissa> consists of a single <digit> that is not '0' (zero), followed by a
<period> and an <unsigned integer>.
ii) Case:
1) If SV is less than 0 (zero), then let Y be the result of '-' || YP.
2) Otherwise, let Y be YP.
[...]
"""
(and similar rules for variable-length character string or large object
character string).
In other words, there are three problems here:
1) 0e0 should have produced the three character string '0e0', not
0.000000000000000
2) The values produced are not "the shortest character string" (the crux of the
problem here)
3) The values produced do not conform to the <approximate numeric literal> as
they do not include an E followed by the exponent nor do they follow the rule
"[...] whose interpreted value is equal to the absolute value of SV and whose
<mantissa> consists of a single <digit> that is not '0' (zero), followed by a
<period> and an <unsigned integer>."
For the last one, for example select `cast(10e0 as char(20)) from rdb$database`
should produce the string '1.0e1' (or maybe '1e1', but that would not fullfil
the "<mantissa> consists of a single <digit> that is not '0' (zero), followed
by a <period> and an <unsigned integer>" part of the rule.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
