search

dolthub / dolt

Dolt – Git for Data
Apache License 2.0
17.83k stars 505 forks source link

Alias selection and conflated results when the same alias name is projected multiple times before a subquery #4537

Open fulghum opened 2 years ago

fulghum commented 2 years ago

When projection expressions define multiple aliases with the same name and project a subquery using that alias name, we don't match MySQL's behavior.

Example Query: select 0 as a, 1 as a, (SELECT x from xy where x = a); MySQL Results: {0, 1, 0} GMS Results: {1, 1, 1}

GMS currently chooses the second alias when it is referenced in the subquery, and the first column gets conflated with the second alias and returns incorrect results, too. This bug doesn't happen without the subquery projection expression, so seems likely that as part of manipulating the plan, we mess up the projection expressions.

max-hoffman commented 5 months ago

As of dolt 1.35.9 we error on this:

tmp/main*> create table xy (x int primary key, y int);
tmp/main*> insert into xy values (0,0);
Query OK, 1 row affected (0.01 sec)
tmp/main*> select 0 as a, 1 as a, (SELECT x from xy where x = a);
ambiguous column or alias name "a"