Description
Submitted by: @mrotteveel
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.