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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.