Bug #110819 EXISTS with dependent subquery can return incorrect results

Description:
Sometimes a query using EXISTS with dependent subquery can return wrong results.

How to repeat:
#The issue can be reproduced on 8.0.16 and above.

docker run --name bug_mysql -p 6603:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql:8.0.33

# Wait a few seconds.
mysql -uroot -h 127.0.0.1 -P 6603

# Copy the following SQL statements into mysql.
CREATE DATABASE bug_db;
CREATE TABLE bug_db.bug_t(id INT NOT NULL AUTO_INCREMENT, col_str VARCHAR(255), col_int INT, PRIMARY KEY (id), INDEX col_str (col_str), INDEX col_int_ind (col_int));

INSERT INTO bug_db.bug_t(id, col_str, col_int) VALUES (1, "a", 1), (2, "a", 1), (3, "a", 1);

SELECT id
FROM bug_db.bug_t t1
WHERE EXISTS (
SELECT 1
FROM bug_db.bug_t t2
WHERE t1.id > t2.id
AND t1.col_str = t2.col_str
AND t1.col_int = t2.col_int);

# The expected result is 2 and 3, but the actual result is 2.

exit;

# Clean up
docker stop bug_mysql && docker rm bug_mysql

Suggested fix:
This bug is not reproducible on 8.0.15 and below. The changelog of 8.0.16 has the following:

"An EXISTS subquery is converted to a semijoin operation. The optimizer chooses a materialization lookup strategy for this semijoin, but because the subquery is not correlated with the outer query block, there were no keys to use for the lookup, which caused the parent query to fail. To solve this issue, we use two equal constant items as keys, to ensure that the materialized query gets the constant as a key (and so that the materialized table consists of at most one row). (Bug #28805105)"

Maybe the fix to Bug #28805105 has a regression.

Disabling semijoin (set optimizer_switch='semijoin=off';) can fix the issue.

Hello Chehai Wu,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

Posted by developer:
 

Bisect says:

d538f0b38d147e574d6833b14a79dcfd7a8ce4b1 is the first bad commit
commit d538f0b38d147e574d6833b14a79dcfd7a8ce4b1
Author: Roy Lyseng <roy.lyseng@oracle.com>
Date:   Wed Nov 14 14:03:16 2018 +0100

    WL#4389 Transform EXISTS subqueries to semi-join
    
    Extend semi-join check to accept EXISTS subqueries in addition to IN.
    Filter out non-deterministic subqueries.