[MDEV-20305] Data loss on DOUBLE and DECIMAL conversion to INT Created: 2019-08-09  Updated: 2020-06-06  Resolved: 2020-06-06

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.5.4

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None


 Description    

CREATE OR REPLACE TABLE t1 (a BIGINT UNSIGNED, b DOUBLE, c DECIMAL(30,0));
 
INSERT INTO t1 VALUES(18446744073709551615,18446744073709551615,18446744073709551615);
 
SELECT a, b, c, a << 0, b << 0, c << 0 FROM t1\G


MariaDB [test]> SELECT a, b, c, a << 0, b << 0, c << 0 FROM t1\G
 
*************************** 1. row ***************************
 
     a: 18446744073709551615
 
     b: 1.8446744073709552e19
 
     c: 18446744073709551615
 
a << 0: 18446744073709551615
 
b << 0: 9223372036854775807
 
c << 0: 9223372036854775807

Notice, the result for `b << 0` and `c << 0` is wrong. It should be 18446744073709551615.

The problem happens because Field_double::val_int() and Field_new_decimal::val_int() truncate to max signed bigint value.

It's not easy to fix this in the current code base.
It seems we need to change this Field and Item method:

virtual longlong val_int();

to:

virtual Longlong_hybrid to_longlong_hybrid();

so the underlying Field or Item can return the entire range -9223372036854775808 .. 18446744073709551615, i.e. from the minimum possible SIGNED value to maximum possible UNSIGNED value.

 Comments   
Comment by Alexander Barkov [ 2019-08-09 ]

Unexpected truncation is also repeatable in this script:

SELECT CAST(CAST(18446744073709551615 AS UNSIGNED) AS DECIMAL(32))<<0;


+----------------------------------------------------------------+
 
| CAST(CAST(18446744073709551615 AS UNSIGNED) AS DECIMAL(32))<<0 |
 
+----------------------------------------------------------------+
 
|                                            9223372036854775807 |
 
+----------------------------------------------------------------+
 
1 row in set, 1 warning (0.000 sec)

And in this script:

SELECT CAST(CAST(18446744073709551615 AS UNSIGNED) AS DOUBLE)<<0;


+-----------------------------------------------------------+
 
| CAST(CAST(18446744073709551615 AS UNSIGNED) AS DOUBLE)<<0 |
 
+-----------------------------------------------------------+
 
|                                       9223372036854775807 |
 
+-----------------------------------------------------------+
 
1 row in set (0.000 sec)

Note, there is a inconsistency:

  • DECIMALS produces a warning
  • DOUBLE truncates silently
Comment by Alexander Barkov [ 2019-08-09 ]

Similar problem:

SELECT BIT_COUNT(18446744073709551615) AS c1, BIT_COUNT(18446744073709551615.0) AS c2, BIT_COUNT(18446744073709551615e0) AS c3\G


*************************** 1. row ***************************
 
c1: 64
 
c2: 63
 
c3: 63
 
1 row in set, 1 warning (0.000 sec)

It's expected to return 64 (at least for the DECIMAL literal).

Comment by Alexander Barkov [ 2019-08-09 ]

Similar problem:

SELECT 18446744073709551615 ^ 1, 18446744073709551615.0 ^ 1;


+--------------------------+----------------------------+
 
| 18446744073709551615 ^ 1 | 18446744073709551615.0 ^ 1 |
 
+--------------------------+----------------------------+
 
|     18446744073709551614 |        9223372036854775806 |
 
+--------------------------+----------------------------+
 
1 row in set, 1 warning (0.000 sec)

Comment by Alexander Barkov [ 2019-08-09 ] 

SELECT LAST_VALUE(18446744073709551615) ^1, LAST_VALUE(18446744073709551615.0) ^ 1;


+-------------------------------------+----------------------------------------+
 
| LAST_VALUE(18446744073709551615) ^1 | LAST_VALUE(18446744073709551615.0) ^ 1 |
 
+-------------------------------------+----------------------------------------+
 
|                18446744073709551614 |                    9223372036854775806 |
 
+-------------------------------------+----------------------------------------+
 
1 row in set, 1 warning (0.000 sec)

Comment by Alexander Barkov [ 2019-08-10 ]

Same problem:

SELECT COALESCE(CAST(CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED) AS DECIMAL(32))) << 0;


+--------------------------------------------------------------------------+
 
| COALESCE(CAST(CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED) AS DECIMAL(32))) << 0 |
 
+--------------------------------------------------------------------------+
 
|                                                      9223372036854775807 |
 
+--------------------------------------------------------------------------+
 
1 row in set, 1 warning (0.00 sec)


SELECT COALESCE(CAST(CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED) AS DOUBLE)) << 0;


+---------------------------------------------------------------------+
 
| COALESCE(CAST(CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED) AS DOUBLE)) << 0 |
 
+---------------------------------------------------------------------+
 
|                                                 9223372036854775807 |
 
+---------------------------------------------------------------------+
 
1 row in set (0.00 sec)

Comment by Alexander Barkov [ 2020-06-04 ]

Similar problem:

SELECT
 
  18446744073709551615 & 18446744073709551615 AS c1,
 
  18446744073709551615 & 18446744073709551615.0 AS c2,
 
  0 | 18446744073709551615 AS c3,
 
  0 | 18446744073709551615.0 AS c4\G


*************************** 1. row ***************************
 
c1: 18446744073709551615
 
c2: 9223372036854775807
 
c3: 18446744073709551615
 
c4: 9223372036854775807

Notice, values for c2 and c4 were truncated.

Comment by Alexander Barkov [ 2020-06-04 ]

Similar problem:

SELECT ~18446744073709551615, ~18446744073709551615.0;
 
SHOW WARNINGS;


+-----------------------+-------------------------+
 
| ~18446744073709551615 | ~18446744073709551615.0 |
 
+-----------------------+-------------------------+
 
|                     0 |     9223372036854775808 |
 
+-----------------------+-------------------------+
 
1 row in set, 1 warning (0.001 sec)


+---------+------+-------------------------------------------------------------------------------+
 
| Level   | Code | Message                                                                       |
 
+---------+------+-------------------------------------------------------------------------------+
 
| Warning | 1916 | Got overflow when converting '18446744073709551615.0' to INT. Value truncated |
 
+---------+------+-------------------------------------------------------------------------------+

Expect 0 in both columns.

Generated at Thu Feb 08 08:58:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.