select 30.0 - 0.025; conversion to decimal type gives error

[monetdb-team]

Date: 2011-07-04 12:47:29 +0200
From: @bartscheers
To: SQL devs <>
Version: 11.3.3 (Apr2011-SP1) [obsolete]
CC: @njnes, @drstmane

Last updated: 2011-07-29 10:52:36 +0200

Comment 15883

Date: 2011-07-04 12:47:29 +0200
From: @bartscheers

User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-GB; rv:1.9.2.17) Gecko/20110428 Fedora/3.6.17-1.fc14 Firefox/3.6.17
Build Identifier:

Trying to use floor function failed:
select floor(30.-0.025);
and also:
select 30. - 0.025;
with message: ERROR = !SQLException:convert:too many digits (5 > 3)

Reproducible: Always

Steps to Reproduce:

1. select 30. - 0.025;

Actual Results:

ERROR = !SQLException:convert:too many digits (5 > 3)

Expected Results:

29.975

workaround possible :
select cast(30. as double) - 0.025;

Comment 15911

Date: 2011-07-07 18:08:22 +0200
From: @sjoerdmullender

Also an issue on the Apr2011 branch.

Comment 15917

Date: 2011-07-08 11:23:38 +0200
From: @drstmane

It seems that the function that decimals to scaled integers is called with incorrect parameters, i.e., ignoring the number of digits of the original value;
in this case,
calc.sht(0,30:bte,3,3);
should be replaced by
calc.sht(0,30:bte,5,3);

30 shifted by 3 digits to the left (*1000) results in 5 digits, not in 3 digits.

sql>plan select 30. - 0.025;
+----------------------------------------------------------------+
| rel |
+================================================================+
| [ sql_sub(convert(30 from decimal(2) to decimal(3,3)), 25) ] |
+----------------------------------------------------------------+
1 tuple (0.513ms)
sql>explain select 30. - 0.025;
+--------------------------------------------------------------------------+
| mal |
+==========================================================================+
| function user.s1_1{autoCommit=true}():void; |
| _4 := calc.sht(0,30:bte,3,3); |
| _8 := calc.-(_4,25:sht); |
| sql.exportValue(1,".","sql_sub_single_value","decimal",4,3,8,_8,""); |
| end s1_1; |
+--------------------------------------------------------------------------+
5 tuples (1.253ms)
sql>trace select 30. - 0.025;
SQLException:convert:too many digits (5 > 3)
sql>debug select 30. - 0.025;
mdb> mdb.start();
mdb>n
mdb> user.s0_1(30,25:sht);
mdb>
mdb> _5 := calc.sht(0,A0=30,3,3);
mdb>
ERROR: SQLException:convert:too many digits (5 > 3)
ERROR: SQLException:convert:too many digits (5 > 3)
SQLException:convert:too many digits (5 > 3)
mdb>

Comment 15946

Date: 2011-07-13 22:37:52 +0200
From: @njnes

corrected coercion to supertype of left and right

Comment 15993

Date: 2011-07-29 10:52:36 +0200
From: @sjoerdmullender

The Apr2011-SP2 bugfix release is out.