Bug #84573 Call to rand() in a can cause an empty set to be erroneously returned
Submitted: 19 Jan 2017 23:41 Modified: 20 Jan 2017 6:57
Reporter: Paul Williams Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.17 OS:Debian (latest official mysql docker image at the time)
Assigned to: CPU Architecture:Any
Tags: empty, rand

[19 Jan 2017 23:41] Paul Williams
Description:
Using a RAND() call in a condition can cause an empty set to be returned, even if the result of the condition should not be able to result in an empty set.

I have reproduced this behavior in the following situations:
using temp tables in an AWS Aurora instance (mysql version 5.6.10)
using temp tables in Ubuntu (mysql version 5.6.33)
using non-temp tables created with InnoDB, MyISAM, and Memory engines on the latest official mysql docker container(mysql version 5.7.17)

How to repeat:
# First, we need to set up a couple tables
CREATE TABLE tbl_a (
    `id` int unsigned NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=InnoDB;
INSERT INTO tbl_a (id) VALUES (3);
CREATE TABLE tbl_b (
    `id` int unsigned NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=InnoDB;
INSERT INTO tbl_b (id) VALUES (3);

# This select demonstrates the behavior.
# The RAND condition is on the left join, so the result should only determine whether id_b is null or 3.
# Expected results:
# ~50% where id_b = null
# ~50% where id_b = 3
#
# My actual (observed) results have been:
# ~50% where id_b = null
# ~25% where id_b = 3
# ~25% where an empty set is returned
SELECT tbl_a.id AS id_a, tbl_b.id AS id_b
FROM tbl_a
LEFT JOIN tbl_b ON tbl_a.id = tbl_b.id
AND tbl_b.id < RAND()*2+2
WHERE tbl_a.id = 3;

Suggested fix:
(Based on my understanding of mysql) an empty set should never be returned from the above SELECT query.
I don't understand what is going on internally to cause this behavior, so I can't suggest an exact fix.
[19 Jan 2017 23:44] Paul Williams
Note that since there is a random element to these results, you may have to run the SELECT statement several times before seeing an empty set.
[20 Jan 2017 6:57] Umesh Shastry
Hello Paul Williams,

Thank you for the report and test case.

Thanks,
Umesh