Weird result with FLOAT operations

[Josh]

Hi,

We are getting the below strange error while we do the subtraction. When we query to substract the same value, we are getting some floating value...

Code: Select all

vertica=> select sb.curr_inv_bal_1mo , sb.sch_principal_1mo, sb.curr_inv_bal_1mo - sb.sch_principal_1mo as MINUS_V from etl_2.sch_bal_principal_3mo sb where sb.loan_id = 'YgIntL' and sb.period_no= 280;

curr_inv_bal_1mo | sch_principal_1mo |       MINUS_V
------------------+-------------------+----------------------
            90.88 |             90.88 | -1.4210854715202e-14
(1 row)

For the same query we use the ROUND(), this returns the excepted result...

Code: Select all

 vertica=> select sb.curr_inv_bal_1mo , sb.sch_principal_1mo, round(sb.curr_inv_bal_1mo,2) - round(sb.sch_principal_1mo,2) as MINUS_V
vertica-> from etl_2.sch_bal_principal_3mo sb
vertica-> where sb.loan_id = 'YgIntL' and sb.period_no= 280;
curr_inv_bal_1mo | sch_principal_1mo | MINUS_V
------------------+-------------------+---------
            90.88 |             90.88 |       0
(1 row)

Below is the table create script. Both the columns were declared as float

Code: Select all

CREATE TABLE etl_2.sch_bal_principal_3mo
(
    period_no int,
    filename varchar(12),
    loan_id char(6),
    man_id int,
    sch_principal_1mo float,
    sch_principal_3mo float,
    curr_inv_bal float,
    curr_inv_bal_1mo float,
    curr_inv_bal_2mo float,
    curr_inv_bal_3mo float

);

Is there bug in Vertica that gives odd results when performing a subtraction operations on floats?

[NorbertKrupa]

What version are you running?

[Josh]

This is occurring on version 6.1.3-9