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

VARCHAR of insufficient length used for set bind of decfloat to varchar [CORE6206] #6451

Closed
firebird-issue-importer opened this issue Dec 11, 2019 · 9 comments

Comments

@firebird-issue-importer

Submitted by: @mrotteveel

The bind for a decfloat(34) to varchar is two (2) characters too short to show a maximum width value:

"""
SQL> set sqlda_display on;
SQL> set bind of decfloat to varchar;
SQL> select -1.234567890123456789012345678901234E+6144 from rdb$database;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 448 VARYING scale: 0 subtype: 0 len: 40 charset: 0 NONE
: name: CONSTANT alias: CONSTANT
: table: owner:

CONSTANT

Statement failed, SQLSTATE = 22001
Dynamic SQL Error
-SQL error code = -303
-arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 40, actual 42
"""

Using set bind of decfloat to char works fine and has the required length of 42:

"""
SQL> set bind of decfloat to char;
SQL> select -1.234567890123456789012345678901234E+6144 from rdb$database;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 452 TEXT scale: 0 subtype: 0 len: 42 charset: 0 NONE
: name: CONSTANT alias: CONSTANT
: table: owner:

CONSTANT

-1.234567890123456789012345678901234E+6144
"""

Commits: bf01700

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 11, 2019

Modified by: @mrotteveel

description: The bind for a decfloat(34) to varchar is two (2) characters too short to show a maximum width value:

SQL> set sqlda_display on;
SQL> set bind of decfloat to varchar;
SQL> select -9.999999999999999999999999999999999E+6144 from rdb$database;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 448 VARYING scale: 0 subtype: 0 len: 40 charset: 0 NONE
: name: CONSTANT alias: CONSTANT
: table: owner:

CONSTANT

Statement failed, SQLSTATE = 22001
Dynamic SQL Error
-SQL error code = -303
-arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 40, actual 42

Using set bind of decfloat to char works fine and has the required length of 42:

SQL> set bind of decfloat to char;
SQL> select -9.999999999999999999999999999999999E+6144 from rdb$database;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 452 TEXT scale: 0 subtype: 0 len: 42 charset: 0 NONE
: name: CONSTANT alias: CONSTANT
: table: owner:

CONSTANT

-9.999999999999999999999999999999999E+6144

=>

The bind for a decfloat(34) to varchar is two (2) characters too short to show a maximum width value:

"""
SQL> set sqlda_display on;
SQL> set bind of decfloat to varchar;
SQL> select -9.999999999999999999999999999999999E+6144 from rdb$database;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 448 VARYING scale: 0 subtype: 0 len: 40 charset: 0 NONE
: name: CONSTANT alias: CONSTANT
: table: owner:

CONSTANT

Statement failed, SQLSTATE = 22001
Dynamic SQL Error
-SQL error code = -303
-arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 40, actual 42
"""

Using set bind of decfloat to char works fine and has the required length of 42:

"""
SQL> set bind of decfloat to char;
SQL> select -9.999999999999999999999999999999999E+6144 from rdb$database;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 452 TEXT scale: 0 subtype: 0 len: 42 charset: 0 NONE
: name: CONSTANT alias: CONSTANT
: table: owner:

CONSTANT

-9.999999999999999999999999999999999E+6144
"""

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 11, 2019

Commented by: @mrotteveel

Not sure if it is related, but a set bind of numeric(38) to char will generate a CHAR(48), while set bind of numeric(38) to varchar will generate a CHAR(46), where a maximum width of 41 is expected (39 digits + decimal separator + sign).

The 2 characters off is odd, and suggests possibly an issue with the 2-byte length prefix in a VARCHAR?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 11, 2019

Modified by: @mrotteveel

description: The bind for a decfloat(34) to varchar is two (2) characters too short to show a maximum width value:

"""
SQL> set sqlda_display on;
SQL> set bind of decfloat to varchar;
SQL> select -9.999999999999999999999999999999999E+6144 from rdb$database;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 448 VARYING scale: 0 subtype: 0 len: 40 charset: 0 NONE
: name: CONSTANT alias: CONSTANT
: table: owner:

CONSTANT

Statement failed, SQLSTATE = 22001
Dynamic SQL Error
-SQL error code = -303
-arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 40, actual 42
"""

Using set bind of decfloat to char works fine and has the required length of 42:

"""
SQL> set bind of decfloat to char;
SQL> select -9.999999999999999999999999999999999E+6144 from rdb$database;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 452 TEXT scale: 0 subtype: 0 len: 42 charset: 0 NONE
: name: CONSTANT alias: CONSTANT
: table: owner:

CONSTANT

-9.999999999999999999999999999999999E+6144
"""

=>

The bind for a decfloat(34) to varchar is two (2) characters too short to show a maximum width value:

"""
SQL> set sqlda_display on;
SQL> set bind of decfloat to varchar;
SQL> select -1.234567890123456789012345678901234E+6144 from rdb$database;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 448 VARYING scale: 0 subtype: 0 len: 40 charset: 0 NONE
: name: CONSTANT alias: CONSTANT
: table: owner:

CONSTANT

Statement failed, SQLSTATE = 22001
Dynamic SQL Error
-SQL error code = -303
-arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 40, actual 42
"""

Using set bind of decfloat to char works fine and has the required length of 42:

"""
SQL> set bind of decfloat to char;
SQL> select -1.234567890123456789012345678901234E+6144 from rdb$database;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 452 TEXT scale: 0 subtype: 0 len: 42 charset: 0 NONE
: name: CONSTANT alias: CONSTANT
: table: owner:

CONSTANT

-1.234567890123456789012345678901234E+6144
"""

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 11, 2019

Commented by: Sean Leyne (seanleyne)

Mark,

It seems that a better description of the issue would be "SET BIND OF DECFLOAT TO VARCHAR can raise "arithmetic exception, numeric overflow, or string truncation" error when working with large/maximum values"

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 11, 2019

Commented by: @mrotteveel

@sean The current description describes the fundamental problem: a BIND is two-way and the variable descriptor used is too short. When a DECFLOAT is going from server to client, this result in a string truncation error, but when going from client (string value) to server (DECFLOAT) (ie: setting a parameter value), the effect is that the space available for the parameter value is too small for the full range of values. This might result in an equivalent truncation error clientside, or result in other effects like the client program only supporting smaller values or values with less precision.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 13, 2019

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 13, 2019

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 2 [ 10888 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 16, 2019

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 16, 2019

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

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

Successfully merging a pull request may close this issue.

None yet
2 participants