Bug #107789 can not get only one row when select where id=round()
Submitted: 7 Jul 2022 3:05 Modified: 7 Jul 2022 11:50
Reporter: jack jack Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0 OS:Ubuntu
Assigned to: CPU Architecture:x86
Tags: =, ROUND(), SELECT

[7 Jul 2022 3:05] jack jack
Description:

select * from test where id = (select round(rand()*25 + 1));

run the sql,can not get only one row.

why?

How to repeat:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `uid` int NULL DEFAULT NULL,
  `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 31 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, 'Jamie Black', 2, 'OYxqicEWUi');
INSERT INTO `test` VALUES (2, 'Xu Xiaoming', 6, 'VBKrVdvfWH');
INSERT INTO `test` VALUES (3, 'Yuen Chi Yuen', 24, '3J4u9lNfWV');
INSERT INTO `test` VALUES (4, 'Lau Wai Lam', 30, 'JQ8Hj779na');
INSERT INTO `test` VALUES (5, 'Xue Lu', 18, 'zGVHSz4HA4');
INSERT INTO `test` VALUES (6, 'Huang Rui', 13, 's2gvt0bbt9');
INSERT INTO `test` VALUES (7, 'Clara Coleman', 4, 'XJhXHRyZNa');
INSERT INTO `test` VALUES (8, 'Cao Jiehong', 28, 'h5UAClJEfS');
INSERT INTO `test` VALUES (9, 'Anna King', 26, 'GDIIJWwQYg');
INSERT INTO `test` VALUES (10, 'Cao Lan', 10, 'u4ZrFzzVA9');
INSERT INTO `test` VALUES (11, 'Lu Lu', 14, 'O3PvH90sZ0');
INSERT INTO `test` VALUES (12, 'Duan Xiuying', 1, '4QqPkwQuIU');
INSERT INTO `test` VALUES (13, 'Lau Sze Yu', 11, '3GoPut9BC4');
INSERT INTO `test` VALUES (14, 'Wu Zitao', 21, 'JdMog0rKvD');
INSERT INTO `test` VALUES (15, 'Cai Xiuying', 22, 'Xg9S4vol4E');
INSERT INTO `test` VALUES (16, 'Kwong Wai Man', 3, '7hB8cHGBJo');
INSERT INTO `test` VALUES (17, 'Liao Rui', 9, 'rypj6943Db');
INSERT INTO `test` VALUES (18, 'Luo Xiuying', 15, 'LHtvjuVzar');
INSERT INTO `test` VALUES (19, 'Deng Yuning', 16, 'R7wIEgAIZQ');
INSERT INTO `test` VALUES (20, 'Huang Yunxi', 27, '0TUIc4m9JP');
INSERT INTO `test` VALUES (21, 'Miguel Hawkins', 19, '5fJc06AN61');
INSERT INTO `test` VALUES (22, 'Mao Zitao', 8, 'l3NtTMkePs');
INSERT INTO `test` VALUES (23, 'Micheal Foster', 29, 'DjN5EfyyLt');
INSERT INTO `test` VALUES (24, 'Liao Anqi', 20, 'MgXUpBup9S');
INSERT INTO `test` VALUES (25, 'Walter Ferguson', 5, 'JUD7GXEI3D');
INSERT INTO `test` VALUES (26, 'Wei Rui', 17, 'X2dRVejypB');
INSERT INTO `test` VALUES (27, 'Jason Diaz', 23, 'nvauJRcgZ5');
INSERT INTO `test` VALUES (28, 'Tong Ho Yin', 25, 'U9ul58AqYa');
INSERT INTO `test` VALUES (29, 'Dai Kar Yan', 12, 'ruSvjfg8xA');
INSERT INTO `test` VALUES (30, 'Tang On Kay', 7, 'BbdLq6LBa1');

SET FOREIGN_KEY_CHECKS = 1;

select * from test where id = (select round(rand()*25 + 1));
[7 Jul 2022 3:07] jack jack
run result

Attachment: mysqlRound.gif (image/gif, text), 933.01 KiB.

[7 Jul 2022 3:07] jack jack
test data

Attachment: test.sql (application/octet-stream, text), 2.60 KiB.

[7 Jul 2022 11:50] MySQL Verification Team
Hi Mr. jack,

Thank you for your bug report.

We have managed to repeat your test case, without problems .....

We have got sometimes zero results, but we got even worse behaviour. The query would return several rows, sometimes even five. This happened even if we used CAST or CONVERT to avoid type mismatch.

That means that nested query was treated as a dependent one, which is not a case here. There is an easy workaround. You save the result of the nested query in a local variable, which you then use in the query instead.

Verified as reported.
[12 Jul 2022 11:50] MySQL Verification Team
This is the original bug for the bug:

https://bugs.mysql.com/bug.php?id=107840