Bug #107840 MySQL 5.6.20

• Submitted: 11 Jul 2022 21:26
• Modified: 12 Jul 2022 11:49
• Reporter: Uma Dj
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• OS: Windows (XP SP3)
• CPU Architecture: Any

[11 Jul 2022 21:26] Uma Dj

Description:
Blank set or doubled when sampled by a random unique identifier
The result of the queries:

mysql> drop table if exists test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test (
    ->   id int not null auto_increment,
    ->   dsc char(12),
    ->   constraint pk_id primary key(id)
    -> ) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(dsc) values ('one'), ('two'), ('three'), ('four'), ('five');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+-------+
| id | dsc   |
+----+-------+
|  1 | one   |
|  2 | two   |
|  3 | three |
|  4 | four  |
|  5 | five  |
+----+-------+
5 rows in set (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select floor(1 + rand() * 5) id;
+----+
| id |
+----+
|  5 |
+----+
1 row in set (0.00 sec)

mysql> select id, dsc from test where id = (select floor(1 + rand() * 5) id);
Empty set (0.00 sec)    (***** Why? *****)

mysql> select floor(1 + rand() * 5) id;
+----+
| id |
+----+
|  4 |
+----+
1 row in set (0.00 sec)

mysql> select id, dsc from test where id = (select floor(1 + rand() * 5) id);
Empty set (0.00 sec)  (***** Why? *****)

mysql> select floor(1 + rand() * 5) id;
+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

mysql> select id, dsc from test where id = (select floor(1 + rand() * 5) id);
+----+------+
| id | dsc  |
+----+------+
|  4 | four |
+----+------+
1 row in set (0.00 sec)

mysql> select floor(1 + rand() * 5) id;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> select id, dsc from test where id = (select floor(1 + rand() * 5) id);
+----+------+
| id | dsc  |
+----+------+
|  4 | four |
|  5 | five |
+----+------+
2 rows in set (0.00 sec)  (***** Why? *****)

mysql> select floor(1 + rand() * 5) id;
+----+
| id |
+----+
|  5 |
+----+
1 row in set (0.00 sec)

mysql> select id, dsc from test where id = (select floor(1 + rand() * 5) id);
+----+------+
| id | dsc  |
+----+------+
|  4 | four |
|  5 | five |
+----+------+
2 rows in set (0.00 sec)  (***** Why? *****)

How to repeat:
drop table if exists test;
create table test (
  id int not null auto_increment,
  dsc char(12),
  constraint pkGood_id primary key(id)
) engine=myisam;
insert into test(dsc) values ('one'), ('two'), ('three'), ('four'), ('five');
select * from test;
select count(*) from test;
select floor(1 + rand() * 5) id;
select id, dsc from test where id = (select floor(1 + rand() * 5) id);

select floor(1 + rand() * 5) id;
select id, dsc from test where id = (select floor(1 + rand() * 5) id);

select floor(1 + rand() * 5) id;
select id, dsc from test where id = (select floor(1 + rand() * 5) id);

select floor(1 + rand() * 5) id;
select id, dsc from test where id = (select floor(1 + rand() * 5) id);

select floor(1 + rand() * 5) id;
select id, dsc from test where id = (select floor(1 + rand() * 5) id);

Suggested fix:
no

[12 Jul 2022 11:51] MySQL Verification Team

Hi Mrs. Dj.,

Thank you for your bug report.

We repeated your test and got even worse results then what you get. Hence, this is truly a proper bug in our Optimiser.

However, we already have a bug with the exact same problem. Hence, your bug is a duplicate of the following one:

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

We shall put the notice in the original bug, so that you get informed when it is fixed. However, a fix will not come in the version 5.6, since it is not maintained any more.

Duplicate.