Planning is not deterministic about trino HOT 3 CLOSED

marked release blocker. not because join pushdown in SQL server is so important (it is not), but because of the engine side of the problem.

the planner apparently puts

n.nationkey = c.nationkey AND n.regionkey IS DISTINCT FROM c.custkey

as a join condition.
but then, sometimes after ReorderJoin, the IS DISTINCT FROM moves to FilterNode above the JoinNode.
the plan is equivalent, but

• some pushdowns may start or stop to be applied
• when pushdown doesn't happen, the join operator will produce more data, unless we put the filter back into JoinNode.

from trino.

The non-determinism is because the SQL server connector is returning stats non-deterministically and that the connector is handling pushdown inconsistently for filter(join(r, l), <f>) vs join(r, l, <f>).

If I recall correctly, for the first execution, the connector doesn't return column stats. For the second one, it does. This results in a different join order and slightly different query plan that the connector refuses to push down.

from trino.

Yes, the plan are different.
When SQL Server connector is offered a join pushdown with n.nationkey = c.nationkey AND n.regionkey IS DISTINCT FROM c.custkey condition, it rejects the offer (it does not support IS DISTINCT FROM predicates).

When ReorderJoins kicks in, the join condition becomes n.nationkey = c.nationkey and the resst (n.regionkey IS DISTINCT FROM c.custkey) becomes a filter above. When SQL Server connector is offered a join pushdown with n.nationkey = c.nationkey, it can accept it (cost-based, or eaerly depending on configuration).

Both plans are obviously equivalent (produce same relations) and correct. The fact that engine sometimes presents more and sometimes fewer join conditions to the connector is something undesirable.

from trino.