pingcap
RANDOM_BYTES function is not random
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
This is the easiest SQL to reproduce:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`col1` varbinary(1024) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`col1`)
);
INSERT INTO t1
SELECT NULL, RANDOM_BYTES(1024) FROM dual;
INSERT INTO t1
SELECT NULL, RANDOM_BYTES(1024) FROM t1;
INSERT INTO t1
SELECT NULL, RANDOM_BYTES(1024) FROM t1;
The value is cached incorrectly. See SELECT RANDOM_BYTES(n) for another example: every row always has the same value.
2. What did you expect to see? (Required)
All inserts should succeed
3. What did you see instead (Required)
mysql> INSERT INTO t1
-> SELECT NULL, RANDOM_BYTES(1024) FROM dual;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1
-> SELECT NULL, RANDOM_BYTES(1024) FROM t1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1
-> SELECT NULL, RANDOM_BYTES(1024) FROM t1;
ERROR 1062 (23000): Duplicate entry 't��Gt�IgN��C5
4. What is your TiDB version? (Required)
mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.3.0-alpha-85-ga8f524b31
Edition: Community
Git Commit Hash: a8f524b31b26cfa4073f2c687bc6ffe55eade37c
Git Branch: master
UTC Build Time: 2022-08-26 15:29:13
GoVersion: go1.19
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore
1 row in set (0.00 sec)
IMHO, remove random_bytes from DeferredFunctions to unFoldableFunctions solves the problem because the result of random_bytes differs when its argument is constant, but I'm not quite sure about it. Looking for opinions from someone else.
Following is the query result from MySQL.
mysql> select null, random_bytes(10) from t1;
+------+------------------------------------+
| NULL | random_bytes(10) |
+------+------------------------------------+
| NULL | 0xD72FB252C5A796940D45 |
| NULL | 0x395CA0EB2CCB2F53059A |
+------+------------------------------------+
I don't think fixing INSERT is enough to solve the issue. For instance the following does not involve any INSERT afaict.
select hex(random_bytes(16)) from (select 1 union select 2) _;
/*
+----------------------------------+
| hex(random_bytes(16)) |
+----------------------------------+
| CC1968EDE7FADF590ECAA89E73375AE0 |
| CC1968EDE7FADF590ECAA89E73375AE0 |
+----------------------------------+
2 rows in set (0.00 sec)
*/
explain select hex(random_bytes(16)) from (select 1 union select 2) _;
/*
+----------------------------+---------+------+---------------+------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------+---------+------+---------------+------------------------------------------------+
| Projection_10 | 2.00 | root | | 937FB536D2123AEFB5E64B364AC4C265->Column#4 |
| └─HashAgg_14 | 2.00 | root | | group by:Column#3, funcs:firstrow(1)->Column#9 |
| └─Union_18 | 2.00 | root | | |
| ├─Projection_20 | 1.00 | root | | 1->Column#3 |
| │ └─TableDual_21 | 1.00 | root | | rows:1 |
| └─Projection_22 | 1.00 | root | | 2->Column#3 |
| └─TableDual_23 | 1.00 | root | | rows:1 |
+----------------------------+---------+------+---------------+------------------------------------------------+
7 rows in set (0.01 sec)
*/
it seems the main issue is random_bytes() got const-evaluated when building the Projection plan (compare with rand()).
