Description:
-- This is a valid query over three empty tables. The outer query has a leading derived table followed by `t3, t2, t1`, and the optimizer hint requests `JOIN_ORDER(t3, t2, t1)`. Inside the leading derived table, a separate query block also refers to unaliased `t1` and `t3`: `LEFT JOIN t1 ON EXISTS (SELECT 1 FROM t3)`.
-- The inner `t1` and `t3` are in nested query blocks, so they should not make the outer `JOIN_ORDER(t3, t2, t1)` hint impossible to plan. Even if the hint cannot be applied, MySQL should ignore it, warn, or return a normal diagnostic, rather than reporting an internal optimizer error.
-- Since all three base tables are empty, the correct result is an empty result set. Instead, MySQL 9.7.0 reports: ERROR 1815 (HY000): Internal error: Optimizer failed to find a plan to execute the query.
How to repeat:
CREATE TABLE t1(c0 INT);
CREATE TABLE t2(c0 INT);
CREATE TABLE t3(c0 INT);
SELECT /*+ JOIN_ORDER(t3, t2, t1)*/ 1 AS ref0
FROM (SELECT 1
FROM (SELECT 1) AS d
LEFT JOIN t1 ON EXISTS (SELECT 1 FROM t3)) AS dt,
t3, t2, t1; -- Expected correct result: empty result set -- actual: ERROR 1815
Description: -- This is a valid query over three empty tables. The outer query has a leading derived table followed by `t3, t2, t1`, and the optimizer hint requests `JOIN_ORDER(t3, t2, t1)`. Inside the leading derived table, a separate query block also refers to unaliased `t1` and `t3`: `LEFT JOIN t1 ON EXISTS (SELECT 1 FROM t3)`. -- The inner `t1` and `t3` are in nested query blocks, so they should not make the outer `JOIN_ORDER(t3, t2, t1)` hint impossible to plan. Even if the hint cannot be applied, MySQL should ignore it, warn, or return a normal diagnostic, rather than reporting an internal optimizer error. -- Since all three base tables are empty, the correct result is an empty result set. Instead, MySQL 9.7.0 reports: ERROR 1815 (HY000): Internal error: Optimizer failed to find a plan to execute the query. How to repeat: CREATE TABLE t1(c0 INT); CREATE TABLE t2(c0 INT); CREATE TABLE t3(c0 INT); SELECT /*+ JOIN_ORDER(t3, t2, t1)*/ 1 AS ref0 FROM (SELECT 1 FROM (SELECT 1) AS d LEFT JOIN t1 ON EXISTS (SELECT 1 FROM t3)) AS dt, t3, t2, t1; -- Expected correct result: empty result set -- actual: ERROR 1815