Remove redundant LIMIT 1 to enable decorrelation of correlated scalar subqueries #64200
Description
Enhancement
When a correlated scalar subquery satisfies the condition of a “unique point lookup with no row expansion,” it produces at most one row per outer row, making LIMIT 1 semantically redundant. Currently, the optimizer fails to decorrelate due to the presence of LIMIT, leaving the plan as Apply + Limit, which causes significant amplification and repeated probing when the outer relation has many rows.
1. Minimal reproduce step (Required)
CREATE TABLE IF NOT EXISTS employees (
id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT,
salary DECIMAL(10, 2)
);
2. What did you expect to see? (Required)
mysql> explain SELECT e.name, e.salary, (SELECT e2.salary FROM employees e2 WHERE e2.dept_id = e.dept_id limit 1) AS avg_dept_salary FROM employees e WHERE e.dept_id = 1;
+--------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------+
| HashJoin_21 | 80.00 | root | | CARTESIAN left outer join, left side:TableReader_25 |
| ├─HashAgg_34(Build) | 8.00 | root | | group by:test.employees.dept_id, funcs:avg(test.employees.salary)->Column#16 |
| │ └─TableReader_44 | 10.00 | root | | data:Selection_43 |
| │ └─Selection_43 | 10.00 | cop[tikv] | | eq(1, test.employees.dept_id) |
| │ └─TableFullScan_42 | 10000.00 | cop[tikv] | table:e2 | keep order:false, stats:pseudo |
| └─TableReader_25(Probe) | 10.00 | root | | data:Selection_24 |
| └─Selection_24 | 10.00 | cop[tikv] | | eq(test.employees.dept_id, 1) |
| └─TableFullScan_23 | 10000.00 | cop[tikv] | table:e | keep order:false, stats:pseudo |
+--------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
3. What did you see instead (Required)
mysql> explain SELECT e.name, e.salary, (SELECT e2.salary FROM employees e2 WHERE e2.dept_id = e.dept_id limit 1) AS avg_dept_salary FROM employees e WHERE e.dept_id = 1;
+----------------------------------+----------+-----------+---------------+-------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+----------+-----------+---------------+-------------------------------------------------------------------+
| Projection_14 | 10.00 | root | | test.employees.name, test.employees.salary, test.employees.salary |
| └─Apply_18 | 10.00 | root | | CARTESIAN left outer join, left side:TableReader_21 |
| ├─TableReader_21(Build) | 10.00 | root | | data:Selection_20 |
| │ └─Selection_20 | 10.00 | cop[tikv] | | eq(test.employees.dept_id, 1) |
| │ └─TableFullScan_19 | 10000.00 | cop[tikv] | table:e | keep order:false, stats:pseudo |
| └─Limit_22(Probe) | 10.00 | root | | offset:0, count:1 |
| └─TableReader_29 | 10.00 | root | | data:Limit_28 |
| └─Limit_28 | 10.00 | cop[tikv] | | offset:0, count:1 |
| └─Selection_27 | 10.00 | cop[tikv] | | eq(test.employees.dept_id, test.employees.dept_id) |
| └─TableFullScan_26 | 10000.00 | cop[tikv] | table:e2 | keep order:false, stats:pseudo |
+----------------------------------+----------+-----------+---------------+-------------------------------------------------------------------+
10 rows in set (0.00 sec)