Use `long long` rather than `int` when converting string to number [CORE4547]

[firebird-automations]

Submitted by: @pavel-zotov

Cast string to int not possible if it contains too many digits in fraction part:

SQL> select cast('1.0000000000' as int) from rdb$database;

    CAST

============ Statement failed, SQLSTATE = 22003 arithmetic exception, numeric overflow, or string truncation -numeric value is out of range

It will be useful if FB will internally do first transformation of string to `long long` rather than to int.

[firebird-automations]

Modified by: @dyemanov

Version: 2\.5\.3 \[ 10461 \] Version: 2\.1\.6 \[ 10460 \] Version: 3\.0 Alpha 1 \[ 10331 \] Version: 2\.5\.2 Update 1 \[ 10521 \] Version: 2\.1\.5 Update 1 \[ 10522 \] Version: 2\.5\.2 \[ 10450 \] Version: 2\.1\.5 \[ 10420 \] summary: Cast any string to \`long long\` rather than to \`int\` =\> Use \`long long\` rather than \`int\` when converting string to number

[firebird-automations]

Commented by: @mrotteveel

I'd like to suggest that this this issue is edited to describe the problem (and then especially the title), and not a - potentially incorrect - solution of the observed problem.

[firebird-automations]

Commented by: @pavel-zotov

Mark,

the problem was following: in my test database amounts of wares are stored in field NUMERIC(12,3) but for some reasons I need to get running total in *integer* rather than in original datatype (numeric).

DDL: recreate table ttt(id int primary key, x numeric(12,3) ); commit; insert into ttt values(1, 700000); insert into ttt values(2, 700000); insert into ttt values(3, 700000); --insert into ttt values(4, 100000); commit;

There is no problem to get running total in FB 3.0:

select id,x,sum(cast(x as int))over(order by id) from ttt;

But, unfortunately, it generates PLAN SORT (ignoring presence of index on field ID). Moreover, I need also get such result in 2.5. And I need to get it FAST, i.e. only using single table scan. And it is very desirable to do it via SQL (to join with other datasource) rather than separate cursor or SP call.

So, the solution was like this (I show simplified EB but in fact there was much more complex SQL):

execute block returns(id int, x numeric(12,3), run_total int) as begin rdb$set_context('USER_TRANSACTION', 'RUN_TOTAL',0); for select id, x, 0 * rdb$set_context( 'USER_TRANSACTION', 'RUN_TOTAL', cast(rdb$get_context('USER_TRANSACTION', 'RUN_TOTAL') as int) + x ) + cast(rdb$get_context('USER_TRANSACTION', 'RUN_TOTAL') as int) from ttt into id, x, run_total do suspend; end

So, if we UNCOMMENT this line in DDL:

--insert into ttt values(4, 100000);

- we get subj error. Correct result must be: ID X SUM 1 700000 700000 2 700000 1400000 3 700000 2100000 4 100000 2200000