Bug Report: too many rows fetched by multi-shard left join about vitess HOT 3 OPEN

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.

from vitess.

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.

from vitess.

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.

from vitess.