[MDEV-8919] Wrong result for CAST(9999999999999999999.0) - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Minor
Resolution: Fixed
Affects Version/s: 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.4.5
Component/s: Data types
Labels:
None

Sprint:
10.1.15, 10.2.2-1

Description

SELECT CAST(9999999999999999999e0 AS UNSIGNED) AS c1, CAST(9999999999999999999.0 AS UNSIGNED) AS c2;

returns

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

| c1                  | c2                  |

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

| 9223372036854775807 | 9999999999999999999 |

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

The c1 value looks wrong.

Note, implicit cast on INSERT works fine:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a BIGINT UNSIGNED);

INSERT INTO t1 VALUES (9999999999999999999e0);

SELECT * FROM t1

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

| a                    |

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

| 10000000000000000000 |

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

Attachments

Issue Links

relates to

MDEV-19502 TIME_ROUND_FRACTIONAL is not respected on TIME->BIGINT conversion

Open

Activity

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2016-06-23 06:03 - edited

Negative numbers also do not convert well:

SELECT CAST(-1e0 AS UNSIGNED),CAST(-1e308 AS UNSIGNED);

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

| CAST(-1e0 AS UNSIGNED) | CAST(-1e308 AS UNSIGNED) |

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

|   18446744073709551615 |      9223372036854775808 |

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

The expected result is to return 0 in both cases, similar to what happens in case of DECIMAL:

SELECT CAST(-1.0 AS UNSIGNED);

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

| CAST(-1.0 AS UNSIGNED) |

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

|                      0 |

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

Note, implicit cast on INSERT works as expected:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a BIGINT UNSIGNED);

INSERT INTO t1 VALUES (-1e0);

SELECT * FROM t1;

+------+

| a    |

+------+

|    0 |

+------+

Alexander Barkov added a comment - 2016-06-23 06:03 - edited Negative numbers also do not convert well: SELECT CAST (-1e0 AS UNSIGNED), CAST (-1e308 AS UNSIGNED); +------------------------+--------------------------+ | CAST(-1e0 AS UNSIGNED) | CAST(-1e308 AS UNSIGNED) | +------------------------+--------------------------+ | 18446744073709551615 | 9223372036854775808 | +------------------------+--------------------------+ The expected result is to return 0 in both cases, similar to what happens in case of DECIMAL: SELECT CAST (-1.0 AS UNSIGNED); +------------------------+ | CAST(-1.0 AS UNSIGNED) | +------------------------+ | 0 | +------------------------+ Note, implicit cast on INSERT works as expected: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a BIGINT UNSIGNED); INSERT INTO t1 VALUES (-1e0); SELECT * FROM t1; +------+ | a | +------+ | 0 | +------+

Alexander Barkov added a comment - 2016-06-23 06:14 - edited

Negative TIME values also do not convert well:

SELECT CAST(TIME'-00:00:01.123' AS UNSIGNED);

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

| CAST(TIME'-00:00:01.123' AS UNSIGNED) |

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

|                  18446744073709551615 |

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

The expected result should be 0, similar to what happens in case of CAST(-1.123 AS UNSIGNED).

Note, implicit cast on INSERT works as expected:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a BIGINT UNSIGNED);

INSERT INTO t1 VALUES (TIME'-00:00:01.123');

SELECT * FROM t1;

+------+

| a    |

+------+

|    0 |

+------+

Alexander Barkov added a comment - 2016-06-23 06:14 - edited Negative TIME values also do not convert well: SELECT CAST ( TIME '-00:00:01.123' AS UNSIGNED); +---------------------------------------+ | CAST(TIME'-00:00:01.123' AS UNSIGNED) | +---------------------------------------+ | 18446744073709551615 | +---------------------------------------+ The expected result should be 0, similar to what happens in case of CAST(-1.123 AS UNSIGNED). Note, implicit cast on INSERT works as expected: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a BIGINT UNSIGNED); INSERT INTO t1 VALUES ( TIME '-00:00:01.123' ); SELECT * FROM t1; +------+ | a | +------+ | 0 | +------+

Alexander Barkov added a comment - 2016-06-27 13:21

This script:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DOUBLE UNSIGNED);

INSERT INTO t1 VALUES (1.9e19);

SELECT CAST(a AS SIGNED), CAST(MIN(a) AS SIGNED) FROM t1;

also returns a wrong result:

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

| CAST(a AS SIGNED)   | CAST(MIN(a) AS SIGNED) |

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

| 9223372036854775807 |   -9223372036854775808 |

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

The expected result is to return 9223372036854775807 in both columns.

Alexander Barkov added a comment - 2016-06-27 13:21 This script: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DOUBLE UNSIGNED); INSERT INTO t1 VALUES (1.9e19); SELECT CAST (a AS SIGNED), CAST ( MIN (a) AS SIGNED) FROM t1; also returns a wrong result: +---------------------+------------------------+ | CAST(a AS SIGNED) | CAST(MIN(a) AS SIGNED) | +---------------------+------------------------+ | 9223372036854775807 | -9223372036854775808 | +---------------------+------------------------+ The expected result is to return 9223372036854775807 in both columns.

Alexander Barkov added a comment - 2016-06-27 14:53

This script:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DECIMAL(30,1) UNSIGNED);

INSERT INTO t1 VALUES (1e19);

SELECT a, CAST(a AS SIGNED) FROM t1;

returns

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

| a                      | CAST(a AS SIGNED)    |

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

| 10000000000000000000.0 | -8446744073709551616 |

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

without warnings.

The expected result for CAST is 9223372036854775807, with a warning.

Alexander Barkov added a comment - 2016-06-27 14:53 This script: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DECIMAL (30,1) UNSIGNED); INSERT INTO t1 VALUES (1e19); SELECT a, CAST (a AS SIGNED) FROM t1; returns +------------------------+----------------------+ | a | CAST(a AS SIGNED) | +------------------------+----------------------+ | 10000000000000000000.0 | -8446744073709551616 | +------------------------+----------------------+ without warnings. The expected result for CAST is 9223372036854775807, with a warning.

Alexander Barkov added a comment - 2019-05-17 04:07

The old patch is outdated. As this is a minor issue, let's fix in 10.4 using Type_handler infrastructrure.

Alexander Barkov added a comment - 2019-05-17 04:07 The old patch is outdated. As this is a minor issue, let's fix in 10.4 using Type_handler infrastructrure.

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2015-10-08 16:04

Updated:: 2019-05-17 08:37

Resolved:: 2019-05-17 08:37

Git Integration

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

MariaDB Server