[#MDEV-20343] CAST("18446744073709552001" AS INT) returns a wrong result

CAST("string-with-a-huge-number" AS INT) erroneously performs a binary complement:

SELECT CAST("18446744073709552001" AS INT) AS c1;

SHOW WARNINGS;

+----+

| c1 |

+----+

| -1 |

+----+

1 row in set, 1 warning (0.000 sec)

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

| Level   | Code | Message                                                   |

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

| Warning | 1292 | Truncated incorrect INTEGER value: '18446744073709552001' |

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

The same problem is repeatable with CAST in dynamic columns:

SELECT COLUMN_GET(COLUMN_CREATE(1, "18446744073709552001" AS CHAR), 1 AS INT) AS c1;

SHOW WARNINGS;

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

| Level   | Code | Message                                                                            |

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

| Warning | 1918 | Encountered illegal value '18446744073709552001' when converting to INT            |

| Note    | 1105 | Cast to signed converted positive out-of-range integer to it's negative complement |

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

Applying binary complement to string->int conversion is wrong. Binary complement should only be applied when converting between integer types of different signness.

Note, implicit conversion works fine though:

CREATE OR REPLACE TABLE t1 (a BIGINT);

INSERT IGNORE INTO t1 VALUES ("18446744073709552001");

SELECT * FROM t1;

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

| a                   |

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

| 9223372036854775807 |

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

Notice, unlike explicit CAST, implicit conversion does not apply binary complement. Instead, it correctly cuts the number to the maximum possible signed 64-bit value, which is 9223372036854775807.

Explicit CAST should be fixed accordingly.

[MDEV-20343] CAST("18446744073709552001" AS INT) returns a wrong result Created: 2019-08-14 Updated: 2022-08-15
Status:	Open
Project:	MariaDB Server
Component/s:	Data types
Affects Version/s:	5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s:	10.4

[MDEV-20343] CAST("18446744073709552001" AS INT) returns a wrong result Created: 2019-08-14 Updated: 2022-08-15