Description:
Under certain conditions, incorrect results are returned by a nondeterministic stored function.
This bug was introduced in 5.7.5 (2014-09-25, Milestone 15) and is present up until at least 5.7.26 as well as version 8 (tested up to 8.0.19).
How to repeat:
--disable_warnings
DROP FUNCTION IF EXISTS f1;
DROP TABLE IF EXISTS t1, t2;
--enable_warnings
# This *could* be marked as deterministic (and doing so gives correct results).
# Identity function to demonstrate bug.
CREATE FUNCTION f1(p1 INT) RETURNS INT NO SQL RETURN p1;
CREATE TABLE t1 (id INT NOT NULL); INSERT INTO t1 VALUES (1), (2);
CREATE TABLE t2 (id INT NOT NULL); INSERT INTO t2 VALUES (0), (1);
# The sub-query is contrived and should return (1).
# We would expect COUNT(t1.id) to be the size of `t1` (2).
SELECT IF(COUNT(t1.id) > 0, "Success", "Failure") "Result"
FROM t1 WHERE 1 IN (SELECT t1_inner.id FROM t1 t1_inner INNER JOIN t2 WHERE f1(t2.id) > 0);
# The only change is the removal of the wrapping f1(t2.id). This *should* have identical results to the above query.
SELECT IF(COUNT(t1.id) > 0, "Success", "Failure") "Result"
FROM t1 WHERE 1 IN (SELECT t1_inner.id FROM t1 t1_inner INNER JOIN t2 WHERE t2.id > 0);
DROP FUNCTION f1;
DROP TABLE t1, t2;
