|
The problem is repeatable with narrow decimal (in 5.6.1 and 6.1.1) and wide decimal (in 6.1.1).
Narrow decimal rounding
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(18,1)) ENGINE=ColumnStore;
|
INSERT INTO t1 VALUES (200101.9);
|
SELECT PERIOD_DIFF(a, 200101) FROM t1;
|
+------------------------+
|
| PERIOD_DIFF(a, 200101) |
|
+------------------------+
|
| 0 |
|
+------------------------+
|
Looks wrong. The expected result is:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(18,1)) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (200101.9);
|
SELECT PERIOD_DIFF(a, 200101) FROM t1;
|
+------------------------+
|
| PERIOD_DIFF(a, 200101) |
|
+------------------------+
|
| 1 |
|
+------------------------+
|
Wide decimal rounding
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(30,1)) ENGINE=ColumnStore;
|
INSERT INTO t1 VALUES (200101.9);
|
SELECT PERIOD_DIFF(a, 200101) FROM t1;
|
+------------------------+
|
| PERIOD_DIFF(a, 200101) |
|
+------------------------+
|
| 0 |
|
+------------------------
|
Looks wrong. The expected result is:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(30,1)) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (200101.9);
|
SELECT PERIOD_DIFF(a, 200101) FROM t1;
|
+------------------------+
|
| PERIOD_DIFF(a, 200101) |
|
+------------------------+
|
| 1 |
|
+------------------------+
|
Huge narrow decimal
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(18,0)) ENGINE=ColumnStore;
|
INSERT INTO t1 VALUES (999999999999999999);
|
SELECT a, PERIOD_DIFF(200101,a) FROM t1;
|
+--------------------+-----------------------+
|
| a | PERIOD_DIFF(200101,a) |
|
+--------------------+-----------------------+
|
| 999999999999999999 | -2147483648 |
|
+--------------------+-----------------------+
|
Looks wrong. The expected result is:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(18,0)) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (999999999999999999);
|
SELECT a, PERIOD_DIFF(200101,a) FROM t1;
|
+--------------------+-----------------------+
|
| a | PERIOD_DIFF(200101,a) |
|
+--------------------+-----------------------+
|
| 999999999999999999 | 24012 |
|
+--------------------+-----------------------+
|
Huge wide decimal
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(30,0)) ENGINE=ColumnStore;
|
INSERT INTO t1 VALUES (999999999999999999);
|
SELECT a, PERIOD_DIFF(200101,a) FROM t1;
|
+--------------------+-----------------------+
|
| a | PERIOD_DIFF(200101,a) |
|
+--------------------+-----------------------+
|
| 999999999999999999 | -2147483648 |
|
+--------------------+-----------------------+
|
Looks wrong. The expected result is:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(30,0)) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (999999999999999999);
|
SELECT a, PERIOD_DIFF(200101,a) FROM t1;
|
+--------------------+-----------------------+
|
| a | PERIOD_DIFF(200101,a) |
|
+--------------------+-----------------------+
|
| 999999999999999999 | 24012 |
|
+--------------------+-----------------------+
|
Huge double
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DOUBLE) ENGINE=ColumnStore;
|
INSERT INTO t1 VALUES (9223372036854775807.0-1000);
|
INSERT INTO t1 VALUES (9223372036854775807.0+1000);
|
SELECT a, PERIOD_DIFF(200101,a) FROM t1 ORDER BY 2;
|
+----------------------+-----------------------+
|
| a | PERIOD_DIFF(200101,a) |
|
+----------------------+-----------------------+
|
| 9.223372036854775e18 | -2147483648 |
|
| 9.223372036854776e18 | 2147483647 |
|
+----------------------+-----------------------+
|
Looks wrong. The expected result is:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DOUBLE) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (9223372036854775807.0-1000);
|
INSERT INTO t1 VALUES (9223372036854775807.0+1000);
|
SELECT a, PERIOD_DIFF(200101,a) FROM t1 ORDER BY 2;
|
+----------------------+-----------------------+
|
| a | PERIOD_DIFF(200101,a) |
|
+----------------------+-----------------------+
|
| 9.223372036854775e18 | 24012 |
|
| 9.223372036854776e18 | 24012 |
|
+----------------------+-----------------------+
|
|