Decimal datatype precision


#1

Hi all,
I am using a DECIMAL (19,8) column, in a division having approximate results, example:

select col1, col1/1000 as col1div from table1 where col3 = 173286399

result:

col1	col1div
1.000097	0.10001

In addition, the original value of col1 in the csv file, was :

;1.9700;

but importend into the DECIMAL(19,8) datatype, it becomes: 1.000097 (using COPY FROM) .

Does the DECIMAL datatype provide the same - not exact - numerical representation as the FLOAT datatype in MapD despite having 8 bytes for memory size?

In that case, the only exact decimal representation would be the DOUBLE datatype with the performance issue raised in this post.

Strange enough, I still didn’t get to find a way to use a both fast AND exact decimal representation in this analytical, performance oriented database.


#2

Hi,

Your issue is strange, if I do the same thing

mapdql> create table t1 (n1 decimal (19,8));
mapdql> insert into t1 values (1.000097);
mapdql> select * from t1;
n1
1.000097
mapdql> select n1, n1/1000 as coldiv from t1;
n1|coldiv
1.000097|0.001000

I get the expected result truncated to the mapdql display precision.

To your question about precision of DECIMAL, the calculations are done internally using bigint with scale management, so assuming your scale is manageable the results should remain precise. In the transport layer the result is shipped as a DOUBLE so there could be some loss there but your example should not suffer there?

regards


#3

Thanks for your support Dwayne,
but I still have some issues:

using this import statement:

mapdql> COPY table1 from '/home/mapd/ingesting/table1.csv' WITH (delimiter = ';', quoted = 'false', header = 'false');
Result
Loaded: 10 recs, Rejected: 0 recs in 0.197000 secs

and having this table 1 definition

and this data ,

I get this wrong results after the import:

select qta, prz_ces from table1

qta	prz_ces
24	1.000027
12	1.000027
5	0.000068
5	0.000068
12	1.000043
72	1.000027
2	0.000068
24	1.000015
36	1.000015
12	0.000061

could you just try to replicate the import with my data and definition?


#4

Well, in the meantime I updated to 3.2.3 and the problem of wrong import is gone away.

Still , I was having some incorrect result. Deepening the trouble I found that importing with COPY from the value:

-0.5000

into a DECIMAL(19,4) column, lead to a 0.5 (positive! ) wrong value.

Here the data

and this the COPY statement :

COPY table1 from '/home/mapd/ingesting/importerr.csv' WITH (delimiter = ';', quoted = 'false', header = 'false');


#5

OK, so after some other tests, it seems that the DECIMAL datatype in MapD does not accept NEGATIVE values.

Any imported values become positive.

Anyone tried this?

Is this a (major) bug ?


#6

Hi,

I can reproduce your issue.

neg.csv

1.00012
2.00013
-3.456
-11.34
-0.500
-.6

my test

mapdql> \d t1
CREATE TABLE t1 (
n1 DECIMAL(19,8))
mapdql> copy t1 from '/data/neg/neg.csv' with (header = 'false');
Result
Loaded: 5 recs, Rejected: 0 recs in 0.054000 secs

mapdql> select n1,n1/1000 from t1;
n1|EXPR$1
1.000120|0.001000
2.000130|0.002000
-3.456000|-0.003456
-11.340000|-0.011340
0.500000|0.000500

The issue is with any negative value with no whole unit . The case with no leading 0 appears to be discarding the value.

I will raise an issue and get his resolved

Regards


#7

Thank you very much Dwayne.

Hope this will be raised in High Priority, I assume a case of a database engine responsible for wrong data should.


#8

Hi,

This has been resolved via issue

If you build from Open Source the fix is available now. If using the community edition it will become available next release.

regards


#9

GREAT news!

Please, could you just point me to the guidelines for building from open source?

Thank you.


#10

just found this: