MySQL Bugs: #108934: MySQL-8.0 select query about rand() in Subquery result is wrong

Bug #108934	MySQL-8.0 select query about rand() in Subquery result is wrong
Submitted:	31 Oct 2022 9:32	Modified:	31 Oct 2022 11:59
Reporter:	Yazhou Xiao	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	mysql-8.0.18/5.7.25	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	select query about rand() in Subquery

Description:
Business SQL requires that a random value be returned each time, which is implemented through the subquery select rand() of query criteria. As follows:

select COL01 from testtable where COL01 in (select floor(1+rand() * 10));

The SQL query condition COL01 is the primary key, and the sub query select floor (1+rand() * 10) limits the return range [1-10].

The normal return should be 1 line, but the query result is more than 1 line. There are blank lines and multiple lines returned.

For details, please refer to the following contents.

How to repeat:
1、Statement of table creation
CREATE TABLE `testtable` (
  `COL01` bigint(20) NOT NULL AUTO_INCREMENT,
  `COL02` varchar(300) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL03` decimal(10,0) NOT NULL,
  `COL04` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL05` decimal(1,0) NOT NULL DEFAULT '0',
  `COL06` decimal(3,0) NOT NULL,
  `COL07` date NOT NULL,
  `COL08` varchar(40) COLLATE utf8mb4_bin NOT NULL,
  `COL09` varchar(20) COLLATE utf8mb4_bin NOT NULL,
  `COL10` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL11` decimal(5,0) NOT NULL DEFAULT '0',
  `COL12` decimal(1,0) NOT NULL DEFAULT '0',
  `COL13` decimal(1,0) DEFAULT '9',
  `COL14` decimal(2,0) NOT NULL DEFAULT '1',
  `COL15` date NOT NULL,
  `COL16` varchar(7) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL17` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL18` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL19` decimal(8,0) NOT NULL DEFAULT '0',
  `COL20` varchar(14) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL21` decimal(1,0) NOT NULL DEFAULT '0',
  `COL22` decimal(1,0) NOT NULL DEFAULT '0',
  `COL23` decimal(1,0) NOT NULL DEFAULT '0',
  `COL24` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL25` decimal(1,0) DEFAULT '0',
  `COL26` decimal(2,0) DEFAULT '0',
  `COL27` decimal(1,0) NOT NULL DEFAULT '0',
  `COL28` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '10000000',
  `COL29` varchar(14) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL30` decimal(1,0) NOT NULL DEFAULT '0',
  `COL31` varchar(6) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL32` decimal(2,0) NOT NULL DEFAULT '0',
  `COL33` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL34` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL35` int(11) NOT NULL DEFAULT '0',
  `COL36` int(11) NOT NULL DEFAULT '4194304',
  `COL37` int(11) NOT NULL DEFAULT '4194304',
  `COL38` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL39` int(11) NOT NULL DEFAULT '0',
  `COL40` decimal(32,0) NOT NULL DEFAULT '0',
  `COL41` decimal(10,0) NOT NULL DEFAULT '0',
  `COL42` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL43` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL44` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL45` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL46` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL47` varchar(4000) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL48` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL49` decimal(1,0) NOT NULL DEFAULT '0',
  `COL50` date NOT NULL DEFAULT '1970-01-01',
  `COL51` decimal(32,0) DEFAULT NULL,
  `COL52` decimal(1,0) DEFAULT NULL,
  `COL53` varchar(14) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`COL01`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2、Add 10 rows of data
insert into testtable values(1,'test_col', '1', 'test_col', '1', '1', now(),'87788',  '130raVFN1497', 'jpg', '122', '1', '1', '1',  now(),'col1', 'test_col', 'test_col', '1', 'test_col', '0', '1', '1', 'test_col','1', '1', '0', '100000', 'test_col', '1', '', '1', 'test_col', 'test_col','1', '1', '1', 'test_col', '0', '1', '1', 'test_col', 'test_col', 'test_col','test_col', '130raVFN1497', 'test_col', '130raVFN1497', '1',  now(), '1', '1', 'test_col');

insert into testtable values(2,'test_col', '1', 'test_col', '1', '1', now(),'87788',  '130raVFN1497', 'jpg', '122', '1', '1', '1',  now(),'col1', 'test_col', 'test_col', '1', 'test_col', '0', '1', '1', 'test_col','1', '1', '0', '100000', 'test_col', '1', '', '1', 'test_col', 'test_col','1', '1', '1', 'test_col', '0', '1', '1', 'test_col', 'test_col', 'test_col','test_col', '130raVFN1497', 'test_col', '130raVFN1497', '1',  now(), '1', '1', 'test_col');

insert into testtable values(3,'test_col', '1', 'test_col', '1', '1', now(),'87788',  '130raVFN1497', 'jpg', '122', '1', '1', '1',  now(),'col1', 'test_col', 'test_col', '1', 'test_col', '0', '1', '1', 'test_col','1', '1', '0', '100000', 'test_col', '1', '', '1', 'test_col', 'test_col','1', '1', '1', 'test_col', '0', '1', '1', 'test_col', 'test_col', 'test_col','test_col', '130raVFN1497', 'test_col', '130raVFN1497', '1',  now(), '1', '1', 'test_col');

insert into testtable values(4,'test_col', '1', 'test_col', '1', '1', now(),'87788',  '130raVFN1497', 'jpg', '122', '1', '1', '1',  now(),'col1', 'test_col', 'test_col', '1', 'test_col', '0', '1', '1', 'test_col','1', '1', '0', '100000', 'test_col', '1', '', '1', 'test_col', 'test_col','1', '1', '1', 'test_col', '0', '1', '1', 'test_col', 'test_col', 'test_col','test_col', '130raVFN1497', 'test_col', '130raVFN1497', '1',  now(), '1', '1', 'test_col');

insert into testtable values(5,'test_col', '1', 'test_col', '1', '1', now(),'87788',  '130raVFN1497', 'jpg', '122', '1', '1', '1',  now(),'col1', 'test_col', 'test_col', '1', 'test_col', '0', '1', '1', 'test_col','1', '1', '0', '100000', 'test_col', '1', '', '1', 'test_col', 'test_col','1', '1', '1', 'test_col', '0', '1', '1', 'test_col', 'test_col', 'test_col','test_col', '130raVFN1497', 'test_col', '130raVFN1497', '1',  now(), '1', '1', 'test_col');

insert into testtable values(6,'test_col', '1', 'test_col', '1', '1', now(),'87788',  '130raVFN1497', 'jpg', '122', '1', '1', '1',  now(),'col1', 'test_col', 'test_col', '1', 'test_col', '0', '1', '1', 'test_col','1', '1', '0', '100000', 'test_col', '1', '', '1', 'test_col', 'test_col','1', '1', '1', 'test_col', '0', '1', '1', 'test_col', 'test_col', 'test_col','test_col', '130raVFN1497', 'test_col', '130raVFN1497', '1',  now(), '1', '1', 'test_col');

insert into testtable values(7,'test_col', '1', 'test_col', '1', '1', now(),'87788',  '130raVFN1497', 'jpg', '122', '1', '1', '1',  now(),'col1', 'test_col', 'test_col', '1', 'test_col', '0', '1', '1', 'test_col','1', '1', '0', '100000', 'test_col', '1', '', '1', 'test_col', 'test_col','1', '1', '1', 'test_col', '0', '1', '1', 'test_col', 'test_col', 'test_col','test_col', '130raVFN1497', 'test_col', '130raVFN1497', '1',  now(), '1', '1', 'test_col');

insert into testtable values(8,'test_col', '1', 'test_col', '1', '1', now(),'87788',  '130raVFN1497', 'jpg', '122', '1', '1', '1',  now(),'col1', 'test_col', 'test_col', '1', 'test_col', '0', '1', '1', 'test_col','1', '1', '0', '100000', 'test_col', '1', '', '1', 'test_col', 'test_col','1', '1', '1', 'test_col', '0', '1', '1', 'test_col', 'test_col', 'test_col','test_col', '130raVFN1497', 'test_col', '130raVFN1497', '1',  now(), '1', '1', 'test_col');

insert into testtable values(9,'test_col', '1', 'test_col', '1', '1', now(),'87788',  '130raVFN1497', 'jpg', '122', '1', '1', '1',  now(),'col1', 'test_col', 'test_col', '1', 'test_col', '0', '1', '1', 'test_col','1', '1', '0', '100000', 'test_col', '1', '', '1', 'test_col', 'test_col','1', '1', '1', 'test_col', '0', '1', '1', 'test_col', 'test_col', 'test_col','test_col', '130raVFN1497', 'test_col', '130raVFN1497', '1',  now(), '1', '1', 'test_col');

insert into testtable values(10,'test_col', '1', 'test_col', '1', '1', now(),'87788',  '130raVFN1497', 'jpg', '122', '1', '1', '1',  now(),'col1', 'test_col', 'test_col', '1', 'test_col', '0', '1', '1', 'test_col','1', '1', '0', '100000', 'test_col', '1', '', '1', 'test_col', 'test_col','1', '1', '1', 'test_col', '0', '1', '1', 'test_col', 'test_col', 'test_col','test_col', '130raVFN1497', 'test_col', '130raVFN1497', '1',  now(), '1', '1', 'test_col');

3、Perform sql query and reproduce the problem
mysql> select COL01 from testtable;
+-------+
| COL01 |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
|     5 |
|     6 |
|     7 |
|     8 |
|     9 |
|    10 |
+-------+
10 rows in set (0.00 sec)

mysql> select COL01 from testtable where COL01 in (select floor(1+rand() * 10));
+-------+
| COL01 |
+-------+
|     4 |
+-------+
1 row in set (0.04 sec)

mysql> select COL01 from testtable where COL01 in (select floor(1+rand() * 10));
Empty set (0.00 sec)

mysql> select COL01 from testtable where COL01 in (select floor(1+rand() * 10));
+-------+
| COL01 |
+-------+
|     3 |
|     7 |
+-------+
2 rows in set (0.00 sec)

mysql> select COL01 from testtable where COL01 in (select floor(1+rand() * 10));
+-------+
| COL01 |
+-------+
|     4 |
|     6 |
+-------+
2 rows in set (0.00 sec)

4、View the SQL execution plan
mysql> explain select COL01 from testtable where COL01 in (select floor(1+rand() * 10));
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | testtable | NULL       | index | NULL          | PRIMARY | 8       | NULL |   10 |    10.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 2 warnings (0.03 sec)

mysql>

5、View the execution plan through format=json
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3.00"
    },
    "table": {
      "table_name": "testtable",
      "access_type": "index",
      "key": "PRIMARY",
      "used_key_parts": [
        "COL01"
      ],
      "key_length": "8",
      "rows_examined_per_scan": 10,
      "rows_produced_per_join": 1,
      "filtered": "10.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "0.20",
        "prefix_cost": "3.00",
        "data_read_per_join": "40K"
      },
      "used_columns": [
        "COL01"
      ],
      "attached_condition": "(`bugs`.`testtable`.`COL01` = floor((1 + (rand() * 10))))"
    }
  }
} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 2 warnings (0.01 sec)

Suggested fix:
We can see that MySQL pushed down the conditions, and the calculation was wrong.
MySQL should calculate the value in the operator query first and then push down.

Hello Yazhou Xiao,

Thank you for the report and test case.

regards,
Umesh