Rounding when casting to decimal

SQL-Fighter

Hi,

 

I'm seeing some slightly odd behaviour when casting to decimal. Please see the below example. Why is it that if we provide an explicit figure it rounds and then truncates whereas if we provide and expression it decides to simply truncate?

 

 

select cast(99.05 as decimal(10,1)) as Ans1
        ,cast(5943.00/60.00 as decimal(10,1)) as Ans2

 

 

 Result

Ans1Ans2
99.199.0
1 ACCEPTED SOLUTION

Xpert

Hi,

I´d say it´s rooted in the fact that your first input is a decimal, while the type of your second input ( the expression 5943.00/60.00  is double ).

View solution in original post

8 REPLIES 8

Xpert

and one final one from me: 

mwellbro_0-1592558494744.png

 

Xpert

Hi,

I´d say it´s rooted in the fact that your first input is a decimal, while the type of your second input ( the expression 5943.00/60.00  is double ).

View solution in original post

SQL-Fighter

I've just found these resources that mention a slight issue with DOUBLE rounding.

https://community.exasol.com/t5/database-features/double-values/ta-p/213

https://www.exasol.com/support/browse/SOL-102

https://www.exasol.com/support/browse/EXASOL-1609 (https://www.exasol.com/support/secure/ReleaseNote.jspa?projectId=10090&version=14215)

Example.

create or replace view my_schema.DataTypes
as
select ROUND(    ((71222-65504)/65504*100)                  ,1) as approximate
	  ,ROUND(CAST((71222-65504)/65504*100 AS DECIMAL(16,3)) ,1) as exact

 

Resultset 1

ApproximateExact
8.78.7

 

Resultset 2

COLUMN_NAMESQL_TYPE
ApproximateDOUBLE
ExactDECIMAL(15,1)

SQL-Fighter

Thanks for your input, it really helped demystify what's happening behind the scenes. It looks to be the divisor operator is returning double return value...
There is no way to retain that behaviour of round then truncate when using decimal values in a arithmetical calculation as below.

 

 

 

create or replace view my_schema.DataTypes
as
select cast(99.05 as decimal(10,1)) as Ans1
        ,cast(5943.00/60.00 as decimal(10,1)) as Ans2
        ,5943.00/60.00 as Ans3
        ,cast(5943.00 as decimal(10,2))/60.00 as Ans4
        ,5943.00/cast(60.00 as decimal(10,2)) as Ans5
        ,cast(5943.00 as decimal(10,2))/cast(60.00 as decimal(10,2)) as Ans6
        ,cast(cast(5943.00 as decimal(10,2))/cast(60.00 as decimal(10,2)) as decimal(10,1)) as Ans7

 

 

 

  gives the following

 

 

 

select *
from my_schema.DATATYPES;

desc my_schema.DATATYPES

 

 

 

Resultset 1

Ans1Ans2Ans3Ans4Ans5Ans6Ans7
99.19999.0599.0599.0599.0599

 

Resultset 2

COLUMN_NAMESQL_TYPE
Ans1DECIMAL(10,1)
Ans2DECIMAL(10,1)
Ans3DOUBLE
Ans4DOUBLE
Ans5DOUBLE
Ans6DOUBLE
Ans7DECIMAL(10,1)

Xpert

what about cast(5943.00/cast(60.00 as decimal(10,2)) as decimal(10,2)) as Ans8 ?

it´ll yield a decimal(10,2) type with a result of 99.05 , which is what you wanted - right ?

SQL-Fighter

I just read this on the following doc link https://docs.exasol.com/sql_references/data_types/datatypedetails.htm

"Numeric Data Types

Exasol supports approximately and exact numerical data types. The difference when using the approximate data types arises from the possible rounding differences, which can occur due to the type of storage and the way computations are performed. Information losses of this nature do not occur when using exact numeric types because of the type of storage"

 

This would explain the random rounding behaviour.

 

It has just tripped me a little as to why Exasol interprets the 99.05 as decimal but then the 5943.00/60.00 result as double [precision] (floating point).

I've tested this with the following and it's still dumbfounded me. Why the difference in interpretation between the last two; y and z?

 

create or replace view my_schema.DataTypes
as
select 100 as w
		,100.00 as x
		,9905.00/100.00 as y
		,5943.00/60.00 as z

 

 

Resultset 1

wxyz
10010099.0599.05

 

Resultset 2

COLUMN_NAMESQL_TYPE
wDECIMAL(3,0)
xDECIMAL(3,0)
yDECIMAL(4,2)
zDOUBLE

 

SQL-Fighter

I was after the result '99.1' after it having been rounded up to 1 decimal place. Key is to use the explicit round function before casting.

create or replace view my_schema.DataTypes
as
select cast(5943.00/cast(60.00 as decimal(10,2)) as decimal(10,2)) as Ans8
		,round(5943.00/60.00, 1) as Ans9
		,cast(round(5943.00/60.00, 1)  as decimal(10,1)) as Ans10

So it would yield the following results

from my_schema.DATATYPES;

desc my_schema.DATATYPES

 

Resultset 1

Ans8Ans9Ans6
99.0599.199.1

 

Result 2

COLUMN_NAMESQL_TYPE
Ans8DECIMAL(10,2)
Ans9 DOUBLE
Ans10DECIMAL(10,1)

Xpert

mwellbro_0-1592558349016.png