Overview of the Issue

Left joins with a limit that hit two shards, one for each side of the join, over-fetch rows even when a limit is present.

Reproduction Steps

Consider two tables, foo and bar, with primary vindexes hash indexes on columns a and b, respectively. The query:

SELECT `bar`.`id`
FROM `foo`
LEFT JOIN `bar`
  ON `bar`.`b` = 2
  AND `foo`.`c` = `bar`.`c`
WHERE `foo`.`a` = 1
LIMIT 1;

The planner will first select from foo from the first shard without a limit, and then select from bar, similarly without a limit. If there lots of rows in foo such that a = 1, we fetch far too many rows, resulting in bad performance and/or exceeding the max row limit. Similarly the planner then selects from bar from the second shard without a limit. Due to the presence of the left join, it is never necessary to select more than one row, and so the planner should be placing a limit 1 in the queries to each shard. This optimization extends for any limit n, not just 1, and similar queries where the right table in the left join is not constrained outside of the join clause, and actually more complex queries where everything left of the final left join can be pushed down into one shard, and then we have just this somewhat independent left join to run at the end.

Binary Version

Vitess 19.0.3

Operating System and Environment details

N/A

Log Fragments

No response