Bug #99354 Nondeterministic stored function returns incorrect results
Submitted: 25 Apr 2020 13:34 Modified: 25 Apr 2020 18:10
Reporter: Jacob Chafik Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.26, 8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[25 Apr 2020 13:34] Jacob Chafik
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;
[25 Apr 2020 18:10] MySQL Verification Team
Thank you for the bug report.