[FRG-209] inserting into float or double sometimes results in incorrect value

[dynamobi-build]

[reporter="elin", created="Wed, 20 Sep 2006 00:36:07 -0500 (GMT-05:00)"] I'm not sure if you're the right person to point this to, but can you take a look? thanks.


-- to repro: (using only java calc)
create schema bogus;
set schema 'bogus';
create table rt ( n1 float, n2 real, n3 real, n4 double, n5 double);
insert into rt VALUES(1.001, 2.002, 3.003, 4.004, 5.005);
insert into rt VALUES(2.002, 1.001, 3.003, 5.005, 4.004);
select * from rt;

-- will lead to this table:
'N1','N2','N3','N4','N5'
'1.0010000000000001','2.002','3.003','4.0040000000000004','5.005'
'2.0020000000000002','1.001','3.003','5.005','4.0040000000000004'

-- if you change to fennel calc and insert again, you'll get
'N1','N2','N3','N4','N5'
'1.0010000000000001','2.002','3.003','4.0040000000000004','5.005'
'2.0020000000000002','1.001','3.003','5.005','4.0040000000000004'
'1.001','2.002','3.003','4.004','5.005'
'2.002','1.001','3.003','5.005','4.004'

-- here's what the plans look like:
0: jdbc:luciddb:> explain plan for insert into rt VALUES(1.001, 2.002, 3.003, 4.004, 5.005);
'column0'
'FennelToIteratorConverter'
' LcsTableAppendRel(table=[[LOCALDB, BOGUS, RT]])'
' IteratorToFennelConverter'
' IterCalcRel(expr#0..4=[{inputs}], expr#5=[Reinterpret($t0)], expr#6=[CAST($t5):DOUBLE NOT NULL], expr#7=[1E3], expr#8=[/($t6, $t7)], expr#9=[CAST($t8):DOUBLE], expr#10=[Reinterpret($t1)], expr#11=[CAST($t10):DOUBLE NOT NULL], expr#12=[/($t11, $t7)], expr#13=[CAST($t12):REAL], expr#14=[Reinterpret($t2)], expr#15=[CAST($t14):DOUBLE NOT NULL], expr#16=[/($t15, $t7)], expr#17=[CAST($t16):REAL], expr#18=[Reinterpret($t3)], expr#19=[CAST($t18):DOUBLE NOT NULL], expr#20=[/($t19, $t7)], expr#21=[CAST($t20):DOUBLE], expr#22=[Reinterpret($t4)],
expr#23=[CAST($t22):DOUBLE NOT NULL], expr#24=[/($t23, $t7)], expr#25=[CAST($t24):DOUBLE], N1=[$t9], N2=[$t13], N3=[$t17], N4=[$t21], N5=[$t25])'
'FennelToIteratorConverter'
' FennelRenameRel(fieldNames=[[N1, N2, N3, N4, N5]])'
' IteratorToFennelConverter'
' IterCalcRel(expr#0=[{inputs}], expr#1=[1.001], expr#2=[2.002], expr#3=[3.003], expr#4=[4.004], expr#5=[5.005], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3], EXPR$3=[$t4], EXPR$4=[$t5])'
'IterOneRowRel'
9 rows selected (0.019 seconds)


------ and for fennel calc:
0: jdbc:luciddb:> explain plan for insert into rt VALUES(1.001, 2.002, 3.003, 4.004, 5.005);
'column0'
'FennelToIteratorConverter'
' LcsTableAppendRel(table=[[LOCALDB, BOGUS, RT]])'
' FennelCalcRel(expr#0..4=[{inputs}], expr#5=[Reinterpret($t0)], expr#6=[CAST($t5):DOUBLE NOT NULL], expr#7=[1E3], expr#8=[/($t6, $t7)], expr#9=[CAST($t8):DOUBLE], expr#10=[Reinterpret($t1)], expr#11=[CAST($t10):DOUBLE NOT NULL], expr#12=[/($t11, $t7)], expr#13=[CAST($t12):REAL], expr#14=[Reinterpret($t2)], expr#15=[CAST($t14):DOUBLE NOT NULL], expr#16=[/($t15, $t7)], expr#17=[CAST($t16):REAL], expr#18=[Reinterpret($t3)], expr#19=[CAST($t18):DOUBLE NOT NULL], expr#20=[/($t19, $t7)], expr#21=[CAST($t20):DOUBLE], expr#22=[Reinterpret($t4)], expr#23=[CAST($t22):DOUBLE NOT NULL], expr#24=[/($t23, $t7)], expr#25=[CAST($t24):DOUBLE], N1=[$t9], N2=[$t13], N3=[$t17], N4=[$t21], N5=[$t25])'
' FennelRenameRel(fieldNames=[[N1, N2, N3, N4, N5]])'
'FennelCalcRel(expr#0=[{inputs}], expr#1=[1.001], expr#2=[2.002], expr#3=[3.003], expr#4=[4.004], expr#5=[5.005], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3], EXPR$3=[$t4], EXPR$4=[$t5])'
' FennelValuesRel(tuples=[[{ 0 }]])'
6 rows selected (0.025 seconds)



(affected tests: calc.cretables,calc.mathBinary,calc.mathUnary,
calc.caseStmt,calc.funcMathCover,setops.union1,
setops.unionall1,setops.unionall2,tpch.8)

[dynamobi-build]

[author="jpham", created="Sun, 24 Sep 2006 15:40:57 -0500 (GMT-05:00)"] As of 7760 the output seems to have returned to its original state since Khanh checked in the reference files. There are still some values that print out in an ugly way, like: 2170.0000000000005

I'm not sure where this comes from yet. I'm also not quite sure what caused some of the values to change back. I think I might have changed some of the rounding, but I'm not sure what difference that would have made.

I'm enabling the calc tests, but not setops or tpch.

[dynamobi-build]

[author="jpham", created="Thu, 19 Oct 2006 17:14:51 -0500 (GMT-05:00)"] The generated Java code for this situation is something like:

Long l = 1001
Double d = (Double) l;
Double d2 = (Double) (d / 1000)
then AbstractResultSet calls d2.toString() to return a string value.

Apparently, Java implementations vary in the 16th digit after the decimal point, depending on the JVM.

1.001 != 1.0010000000000001, but...
1001/1000 = 1.001 or 1.0010000000000001, depending on JVM

Liz mentions the strictfp Java flag which is more or less described as "less efficient and less precise, but more compliant". This seems helpful, but not so important. So I told Liz not to turn it on, since it might slow things down. But to limit the SqllineEngine output since this issue was causing diffs.

When outputting strings we should never print that many digits anyways. First of all, the raw datatype does not really support that many digits that well. Second, ResultSet.getString() is supposed to apply a cast to string, and a cast to string looks more like 1.001E0 and for Farrago has a maximum of 15 fractional digits. (10 for real values.) So the most we should ever do is probably 15 or less.