unexpected "table does not have column" error

[knutwannheden]

In relation to #2203 which added support for explicit join tree syntax there is still a problem with slightly more complex queries, hinting at an underlying problem.

When executing the following script:

create table t(x int, y int, z int);

select *
from t
left join (t t1 join t t2 on t1.y = t2.y) on t1.y = t.y
left join (t t3 join t t4 on t3.x is not null) on t3.z = t.z
;

There is an error [HY000][1105] table "t3" does not have column "x" where x apparently refers to the t3.x reference in the last line.

What is interesting is that if the first JOIN is removed then the query is executed successfully. Also when changing the join predicate from t3.x is not null to e.g. t3.x = t4.x there is no problem.

[timsehn]

Thanks. @max-hoffman will look at this issue when he gets a chance.

[max-hoffman]

Hi @knutwannheden, I am looking at this now. I will follow up today with more information and a timeline for fixing this.

[max-hoffman]

Hi @knutwannheden, here's the fix for this one.
dolthub/go-mysql-server#1202

When a join condition is specific to a single table, we hoist the filter for later pushdown. Our condition for filter placement accidentally moved the filter to every child of the join's parent. It works when you change the IS NULL because we can't hoist the filter. It works when you remove the parent join because then there is no sibling node that absorbs a filter it doesn't know how to execute.

Keep them coming.