[MCOL-4668] PERIOD_DIFF(dec_or_double1,dec_or_double2) is not as in InnoDB - Jira

XML

Word

Printable

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 |

+------------------------+

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 |

+------------------------+

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 |

+--------------------+-----------------------+

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 |

+--------------------+-----------------------+

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 |

+----------------------+-----------------------+

blocks

MCOL-4361 Replace pow(10.0, (double)scale) expressions with a static dictionary lookup.

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.