Subquery alias naming conflict

[max-hoffman]

This is an alias name resolution conflict. A subquery expression has access to the parent scope definitions, including aliases. If the parent scope defines an alias with the same name as an unqualified table column in the subquery, we should qualify the column with the subquery data source, not project the outer scope alias definition.

repro:

-- setup
create table xy (x int primary key, y int, key `y` (y));
create table uv (u int primary key, v int, key `v` (v));
insert into xy values (0,0),(1,1),(2,2),(3,3);
insert into uv values (0,3),(3,0),(2,1),(1,2);

-- main query, we should disambiguate v
Select
  x as v,
  (Select u from uv where v = y) as u
  From xy;

-- manually disambiguated query
Select
  xy.x as v,
  (Select uv.u from uv where uv.v = xy.y) as u
  From xy;

-- error query we are mistakenly building
Select
  xy.x as v1,
  (Select uv.u from uv where v1 = xy.y) as u
  From xy;
ERROR 1242 (21000): Subquery returns more than 1 row

[fulghum]

I spent some time debugging through this one on Friday. In the qualifyExpression function, we detect that there is an alias and a column with the same name, and while we should be selecting the column, the current code sees that conflict and prevents it from using any symbols at that layer, which make it select the alias. Unfortunately, simply changing this code breaks other queries that are relying on this behavior in order to execute correctly.

I'll keep digging into this one, but my gut says that it's going to be difficult to solve this specific with a quick bug fix, and it will likely require the larger alias identification changes that we're working on.

[fulghum]

quick update... I've got this query, and several other alias edge cases, working in a branch with my new alias resolution logic. Enginetests are all passing now and I'm going through and doing a little more testing and cleaning up. I expect to open a PR for those changes tomorrow and get it shipped next week.

[fulghum]

I just merged in a change to go-mysql-server that improves how we handle aliases, which includes the fix for this query. I'll get that code into Dolt next, then this fix will go out with the next release.

[fulghum]

This fix ended up landing in Dolt just in time to make today's 0.41.6 release.