[MCOL-4420] DATE_ADD() returns a wrong result for DECIMAL input - Jira

XML

Word

Printable

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DECIMAL(18,7)) ENGINE=ColumnStore;

INSERT INTO t1 VALUES (50.1);

INSERT INTO t1 VALUES (50.12);

INSERT INTO t1 VALUES (50.123);

INSERT INTO t1 VALUES (50.1234);

INSERT INTO t1 VALUES (50.12345);

INSERT INTO t1 VALUES (50.123456);

INSERT INTO t1 VALUES (50.1234567);

SELECT DATE_ADD(TIMESTAMP'2001-01-01 00:00:00', INTERVAL a SECOND) AS c FROM t1;

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

| c                          |

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

| 2001-01-01 00:00:50.000000 |

| 2001-01-01 00:00:50.000000 |

| 2001-01-01 00:00:50.000000 |

| 2001-01-01 00:00:50.000000 |

| 2001-01-01 00:00:50.000000 |

| 2001-01-01 00:00:50.000000 |

| 2001-01-01 00:00:50.000000 |

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

This result looks wrong.

The correct result is:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DECIMAL(18,7)) ENGINE=MyISAM;

INSERT INTO t1 VALUES (50.1);

INSERT INTO t1 VALUES (50.12);

INSERT INTO t1 VALUES (50.123);

INSERT INTO t1 VALUES (50.1234);

INSERT INTO t1 VALUES (50.12345);

INSERT INTO t1 VALUES (50.123456);

INSERT INTO t1 VALUES (50.1234567);

SELECT DATE_ADD(TIMESTAMP'2001-01-01 00:00:00', INTERVAL a SECOND) AS c FROM t1;

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

| c                          |

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

| 2001-01-01 00:00:50.100000 |

| 2001-01-01 00:00:50.120000 |

| 2001-01-01 00:00:50.123000 |

| 2001-01-01 00:00:50.123400 |

| 2001-01-01 00:00:50.123450 |

| 2001-01-01 00:00:50.123456 |

| 2001-01-01 00:00:50.123456 |

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

relates to

MCOL-4419 MAKETIME() returns a wrong result for DECIMAL input

MCOL-4416 FROM_UNIXTIME() returns a bad result for DECIMAL input

MCOL-4418 SEC_TO_TIME() returns a wrong result for DECIMAL input

MCOL-4514 DATE + INTERVAL a SECOND is not like in MariaDB

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