MySQL Bugs: #114741: Inconsistent query results caused by different execution of subqueries

Bug #114741	Inconsistent query results caused by different execution of subqueries
Submitted:	23 Apr 2024 9:43	Modified:	23 Apr 2024 12:59
Reporter:	John Jove	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Run the following statements, in which two queries are expected to return the same results. However, due to the different execution of the same sub-query, Q1 returns an empty result, while Q2 returns a non-empty result.

How to repeat:
CREATE TABLE t1 (c1 decimal(13, 13) unsigned DEFAULT NULL);
CREATE TABLE t2 (c2 decimal, UNIQUE KEY i1 (c2));
INSERT INTO t1(c1) VALUES (0.9999999999999), (0.9999999999999), (NULL);
INSERT INTO t2(c2) VALUES (NULL), (NULL), (NULL), (1);

-- Q1
SELECT /*+ JOIN_ORDER(t2,t1) */ ca4
FROM (SELECT ca7 AS ca4
      FROM (SELECT c2 AS ca7 FROM t2 USE INDEX (i1)) AS ta1
      WHERE (('A' NOT RLIKE ca7) IN (SELECT ca12 FROM (SELECT c1 AS ca12 FROM t1) AS ta2))) AS ta3
         NATURAL JOIN (SELECT c1 AS ca14 FROM t1) AS ta4; -- {}
-- Q2
SELECT /*+ JOIN_ORDER(t1,t2) */ ca4
FROM (SELECT ca7 AS ca4
      FROM (SELECT c2 AS ca7 FROM t2 USE INDEX (i1)) AS ta1
      WHERE (('A' NOT RLIKE ca7) IN (SELECT ca12 FROM (SELECT c1 AS ca12 FROM t1) AS ta2))) AS ta3
         NATURAL JOIN (SELECT c1 AS ca14 FROM t1) AS ta4; -- {1,1,1}

Hi Mr. Jove,

Thank you for your bug report.

However, we do not see what bug are you reporting.

The only difference in the two queries is that you were forcing a join order in two different ways. When you leave the Optimiser to do its job on itself, it works fine.

However, optimiser hints are enforced on the optimiser. Simply, optimiser has to obey programmer's commands.  So, when you specify a wrong join order , you will get wrong results. That is expected behaviour. 

Hence, we do not se why should this be considered a bug.

Hi Mr. Jove,

We have decided that this is a bug. A low severity bug, but still a bug.

The bug lies in the fact that a query does not contain a single explicit join statement. Some of the nested queries are optimised as a join, but this optimiser hint should not apply in those cases.

We have repeated your results with 8.0.36, 8.0.37 and 8.3.0.

This is now a verified bug report.

Thank you for the quick response. Actually, the above two queries do have NATURAL JOIN clauses.
I quite agree that optimizer hints should not be abused to enforce a potential error behavior of the query optimizer.
By the way, I will attach the original bug report returned by my tool, in which the same query is executed by different plans without enforcing query hints. That means that these above two executions can also be chosen by the optimizer without query hints.

For easy of reproducing this issue, I add such hints in the query, since the runtime environment could be different. Thanks again.

You are truly welcome.