Issue Details (XML | Word | Printable)

Key: CORE-6291
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Alexander Peshkov
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Statement "CREATE DOMAIN [dm_name] as BIGINT" raises "numeric value is out of range" if its default value is -9223372036854775808

Created: 25/Apr/20 12:49 PM   Updated: 10/Jul/20 04:51 PM
Component/s: Engine
Affects Version/s: 4.0 Beta 1
Fix Version/s: None

Issue Links:
Duplicate
 

QA Status: Done successfully


 Description  « Hide
Run this script:
======
create domain dm_bigint_absolute_max as bigint default 9223372036854775807;
create domain dm_bigint_near_abs_min as bigint default -9223372036854775807;
create domain dm_bigint_absolute_min as bigint default -9223372036854775808;
======

Its last statement will raise:
======
Statement failed, SQLSTATE = 22003
unsuccessful metadata update
-CREATE DOMAIN DM_BIGINT_ABSOLUTE_MIN failed
-Dynamic SQL Error
-SQL error code = -104
-arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range
======

Only 4.x is affected (I've checked several 64-bit snapshots, since 4.0.0.840 and up to current 4.0.0.1935; result is the same on Windows and Linux).



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Zotov added a comment - 25/Apr/20 09:24 PM - edited
Perhaps, this problem relates not only domain definition:
=======
SQL> recreate table test(n bigint default -9223372036854775807);
SQL> alter table test add n2 bigint default -9223372036854775808;
Statement failed, SQLSTATE = 22003
unsuccessful metadata update
-ALTER TABLE TEST failed
-Dynamic SQL Error
-SQL error code = -104
-arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range
SQL>
=======

As temp. workaround one may use hex literal 0x8000000000000000 which equal to min_bigint:

SQL> set heading off;
SQL> recreate table test(n bigint default 0x8000000000000000);
SQL> insert into test default values;
SQL> select * from test;

 -9223372036854775808

SQL> create domain dm_test bigint default 0x8000000000000000;
SQL> create table test2( d dm_test );
SQL> insert into test2 default values;
SQL> select * from test2;

 -9223372036854775808

Pavel Zotov added a comment - 05/May/20 05:49 AM
Some examples still fail. Checked on WI-T4.0.0.1954 Firebird 4.0 Beta 2:

create domain dom06_13 as numeric(20,2) default -9999999999999999991; -- this is from CORE-6294; it failed before, now it runs OK

create domain dm_bigint_absolute_max as bigint default 9223372036854775807;

create domain dm_bigint_near_abs_min as bigint default -9223372036854775807;

create domain dm_bigint_absolute_min as bigint default -9223372036854775808;
Statement failed, SQLSTATE = 22003
unsuccessful metadata update
-CREATE DOMAIN DM_BIGINT_ABSOLUTE_MIN failed
-Dynamic SQL Error
-SQL error code = -104
-arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range


create table test1(n bigint default -9223372036854775808);
Statement failed, SQLSTATE = 22003
unsuccessful metadata update
-CREATE TABLE TEST1 failed
-Dynamic SQL Error
-SQL error code = -104
-arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range