go-mysql-server
go-mysql-server copied to clipboard
Functional Dependency anaylsis on lookup joins doesn't handle when key expression is an Arithmetic expression
Put a breakpoint in lookupJoinSelectivity
before the call to sql.NewLookupFDs
Run the following SQL:
CREATE table xy (x int primary key, y int);
CREATE table uv (u int primary key, v int);
insert into xy values (1,0), (2,1), (0,2), (3,3);
insert into uv values (0,1), (1,1), (2,2), (3,2);
SELECT * FROM xy WHERE (
EXISTS (SELECT * FROM xy Alias1 WHERE Alias1.x = (xy.x + 1)));
As part of costing joins, we attempt to detect lookups that we know will always return at most one row. This is a perfect candidate of such a lookup: We use xy.x+1
as the key into a lookup on the primary key index Alias1.x
. This always has at most one result. So we expect that fds.HasMax1Row()
within lookupJoinSelectivity
should return true. But it returns false instead, and we fail to prioritize this execution plan.