Bug Report: too many rows fetched by multi-shard left join

[GrahamCampbell]

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

[systay]

Thanks for reporting this, @GrahamCampbell!

The planner was doing the safe thing here, we've just never invested time in making this better.

I'm changing this to a feature request - we don't want to backport these kind of planner enhancements.

[GrahamCampbell]

Fair enough. Looking at the PR, I think it may have a bug - it's incorrect to push down the limit in the query for foo for the following query:

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

I see there's a check against a null check condition, but I don't think that's enough. The bar.id cannot be pushed into the join condition and it does have the effect of filtering null bar.id rows.

[systay]

Fair enough. Looking at the PR, I think it may have a bug - it's incorrect to push down the limit in the query for foo for the following query:

I suggest we chat on the PR. You can point out the plan you think is not correct. Hard to understand without seeing the plan you are thinking of.