MySQL Bugs: #75210: Using rand() in UPSERT may result in wrong result

Bug #75210	Using rand() in UPSERT may result in wrong result
Submitted:	15 Dec 2014 6:10	Modified:	13 Jan 2016 9:24
Reporter:	Calvin Sun	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.5, 5.6, 5.6.22, 5.7.9, 5.5.48, 5.6.29	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Using rand() in UPSERT, along with a big number, the results are unpredictable -- resulting in data corruption.

How to repeat:
mysql> create table t1 (col1 bigint, col2 tinyint, primary key(col1, col2));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (12345678901234567, 1) ON DUPLICATE KEY UPDATE col1 = if(false, RAND()*1000, col1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+-------------------+------+
| col1              | col2 |
+-------------------+------+
| 12345678901234567 |    1 |
+-------------------+------+
1 row in set (0.00 sec)

mysql> insert into t1 values (12345678901234567, 1) ON DUPLICATE KEY UPDATE col1 = if(false, RAND()*1000, col1);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1;
+-------------------+------+
| col1              | col2 |
+-------------------+------+
| 12345678901234568 |    1 |
+-------------------+------+
1 row in set (0.00 sec)

mysql> insert into t1 values (123456789012345678, 1) ON DUPLICATE KEY UPDATE col1 = if(false, RAND()*1000, col1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+--------------------+------+
| col1               | col2 |
+--------------------+------+
|  12345678901234568 |    1 |
| 123456789012345678 |    1 |
+--------------------+------+
2 rows in set (0.00 sec)

mysql> insert into t1 values (123456789012345678, 1) ON DUPLICATE KEY UPDATE col1 = if(false, RAND()*1000, col1);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1;
+--------------------+------+
| col1               | col2 |
+--------------------+------+
|  12345678901234568 |    1 |
| 123456789012345680 |    1 |
+--------------------+------+
2 rows in set (0.00 sec)

In this case, even "false" is specified, col1 is still updated to different value.

Hello Calvin,

Thank you for the report and test case.

Thanks,
Umesh

The user's case is:

create table t1 (col1 bigint, col2 tinyint, primary key(col1, col2));

mysql> insert into t1 values (12345678901234567, 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values (12345678901234567, 1) ON DUPLICATE KEY UPDATE
col1
= if(false, RAND()*1000, col1);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1;
+-------------------+------+
| col1              | col2 |
+-------------------+------+
| 12345678901234568 |    1 |
+-------------------+------+
1 row in set (0.00 sec)

His complaint is : "I never intended for the last digit to be 8..."

What is happening internally:
The use of the RAND function function here converts the whole if condition
into a double. Since double trumps bigint, we first convert col1 to double,
losing precision, which is not regained when converting back to bigint again
on the assignment.

Why does double lose precision?
A double can't accurately hold values greater than 2^53. See
https://en.wikipedia.org/wiki/Double-precision_floating-point_format

"Between 2^52=4,503,599,627,370,496 and 253=9,007,199,254,740,992 the
representable numbers are exactly the integers. For the next range, from 2^53
to 2^54, everything is multiplied by 2, so the representable numbers are the
even ones, etc."

So is there a bug?
No.

See sql-1992 standard section 4.6:
"If least significant digits are lost, implementation-defined rounding or
truncating occurs with no exception condition being raised."

Any workarounds?
Casting to decimal always helps. Since there is no loss of precision for
longlong.

insert into t1 values (12345678901234567, 1) ON DUPLICATE KEY UPDATE col1=
if(false, CAST(RAND()*1000 AS DECIMAL(20)), col1);

Action:
I will be closing this as not a bug, after mentioning the workaround.

Closed as not a bug.