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.
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.