NUMERIC/DECIMAL datatype has flaw.

[njmarshall]

ISSUE

MapD's NUMERIC/DECIMAL datatype has flaws. To reproduce the issues, run the follow sql statements on the MapDQL cursor:

CREATE TABLE t_d(id SMALLINT, val DECIMAL(2,1));
INSERT INTO t_d VALUES(1, NULL);
INSERT INTO t_d VALUES(2, 1);
INSERT INTO t_d VALUES(3, 0.1);
INSERT INTO t_d VALUES(4, 1.1);
INSERT INTO t_d VALUES(5, 15.1);
INSERT INTO t_d VALUES(6, 1.15);
INSERT INTO t_d VALUES(7, 15.15);

Results from the SELECT statement

# id|val
# 1|NULL
# 2|1.000000
# 3|0.100000
# 4|1.100000
# 5|15.100000
# 6|1.100000
# 7|15.100000

Explanation

The NUMERIC/DECIMAL datatype has 4 bytes for the maximum of 19 digits. The datatype has a precision and scale. Precision is defined as the total number of digits in a numeric or decimal type, and scale as the number of digits to the right of the decimal point. In this case, the val column is defined as DECIMAL(2,1) -> 2 digits precision and 1 digit scale. But result rows (#2 - 7) has 6 digit scales (i.e. the expected results for Row #4 should be 1.1, not 1.100000. In addition, no decimal nearest is calculated. For example, the expected results for in row #6 should be 1.2 (1.15 -> 1.2) but get 1.1.

[njmarshall]

@asuhan @andrewseidl @RalphLoen Please check this issue and let me know your consideration. Thanks

[njmarshall]

@DennisDawson Can you please add detail of this datatype to www.mapd.com/docs?

[asuhan]

These are two separate issues actually:

1. The way we print the 4th row is a client-side formatting issue.
2. The rounding is an import issue.

I don't think either is a high priority at this point, but we'll have to address them.