Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

CAST of floating point types yields unnecessary string truncation error and does not conform to SQL standard requirements [CORE6508] #6737

Open
firebird-automations opened this issue Mar 8, 2021 · 0 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant