Problem altering numeric field type [CORE1162]

[firebird-automations]

Submitted by: prenosil (prenosil)

Assigned to: Claudio Valderrama C. (robocop)

Is related to QA152

Votes: 1

create table tab ( a numeric(4,2) );

insert into tab values (99.99);

select * from tab;

  A

=======
99.99

alter table tab alter a type numeric(4,3);

select * from tab;

Statement failed, SQLCODE = -802
arithmetic exception, numeric overflow, or string truncation

Btw. the database is not "corrupted" too badly - you can revert the change back by alter table tab alter a type numeric(4,2);
and the engine is clever enough to convert data from stored format to requested one directly, not through all intermediate format versions.

Commits: 01864d3

[firebird-automations]

Modified by: @pcisar

reporter: Carlos H. Cantu [ warmbooter ] => prenosil [ prenosil ]

[firebird-automations]

Modified by: @pcisar

description: This bug was found by Ivan Prenosil. I'm just posting it here because he is busy to post it now, and authorized me to post in his name:

create table tab ( a numeric(4,2) );

insert into tab values (99.99);

select * from tab;

  A

=======
99.99

alter table tab alter a type numeric(4,3);

select * from tab;

Statement failed, SQLCODE = -802
arithmetic exception, numeric overflow, or string truncation

Btw. the database is not "corrupted" too badly - you can revert the change back by alter table tab alter a type numeric(4,2);
and the engine is clever enough to convert data from stored format to requested one directly, not through all intermediate format versions.

=>

create table tab ( a numeric(4,2) );

insert into tab values (99.99);

select * from tab;

  A

=======
99.99

alter table tab alter a type numeric(4,3);

select * from tab;

Statement failed, SQLCODE = -802
arithmetic exception, numeric overflow, or string truncation

Btw. the database is not "corrupted" too badly - you can revert the change back by alter table tab alter a type numeric(4,2);
and the engine is clever enough to convert data from stored format to requested one directly, not through all intermediate format versions.

[firebird-automations]

Modified by: Claudio Valderrama C. (robocop)

assignee: Claudio Valderrama C. [ robocop ]

[firebird-automations]

Commented by: Claudio Valderrama C. (robocop)

Again, dyn_mod.epp. This time, check_update_fld_type().

[firebird-automations]

Modified by: @dyemanov

Fix Version: 2.1 [ 10041 ]

description:
create table tab ( a numeric(4,2) );

insert into tab values (99.99);

select * from tab;

  A

=======
99.99

alter table tab alter a type numeric(4,3);

select * from tab;

Statement failed, SQLCODE = -802
arithmetic exception, numeric overflow, or string truncation

Btw. the database is not "corrupted" too badly - you can revert the change back by alter table tab alter a type numeric(4,2);
and the engine is clever enough to convert data from stored format to requested one directly, not through all intermediate format versions.

=>

create table tab ( a numeric(4,2) );

insert into tab values (99.99);

select * from tab;

  A

=======
99.99

alter table tab alter a type numeric(4,3);

select * from tab;

Statement failed, SQLCODE = -802
arithmetic exception, numeric overflow, or string truncation

Btw. the database is not "corrupted" too badly - you can revert the change back by alter table tab alter a type numeric(4,2);
and the engine is clever enough to convert data from stored format to requested one directly, not through all intermediate format versions.

[firebird-automations]

Modified by: @pcisar

Fix Version: 2.1 Beta 1 [ 10141 ]

Fix Version: 2.1.0 [ 10041 ] =>

[firebird-automations]

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA152 [ QA152 ]

[firebird-automations]

Commented by: @pmakowski

Q/A test in 2.0.1 ok
test file made

[firebird-automations]

Modified by: @pmakowski

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

[firebird-automations]

Commented by: @pmakowski

Here what I get under FB-SS-Win32-2.0.1.12855 :
Statement failed, SQLCODE = -802
arithmetic exception, numeric overflow, or string truncation

And here what I get under FB-SS-Win32-2.1.0.15999 :

  Statement failed, SQLCODE = \-607
  unsuccessful metadata update
  \-New scale specified for column A must be at most 2\.

details :

SQL> create table tab ( a numeric(4,2) );
SQL> insert into tab values (99.99);
SQL> alter table tab alter a type numeric(4,3);
Statement failed, SQLCODE = -607
unsuccessful metadata update
-New scale specified for column A must be at most 2.
SQL> select * from tab;

  A

=======
99.99

SQL> commit;
SQL> show table tab;
A NUMERIC(4, 2) Nullable
SQL>

[firebird-automations]

Modified by: @pmakowski

status: Closed [ 6 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

[firebird-automations]

Commented by: @pmakowski

Closed and test ok and made for 2.1

[firebird-automations]

Modified by: @pmakowski

status: Reopened [ 4 ] => Closed [ 6 ]

resolution: Fixed [ 1 ]

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 11680 ] => Firebird [ 15209 ]

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test => Done successfully