Thanks for the PR! It looks really good. If you want to make it even faster you can try to replace the RHS with a 1 row scan of NULL values and turn the join into a cross product. Currently a Nested block join is still planned, in certain cases this is even slower than an inner join with matches.

If you plan a cross join with a 1 row table of NULLS, then the performance should be even faster than any hash join. In addition, the relations are more freely moved around for join order optimization as well.

Thank you, that's a really great suggestion! I'll fix it as you recommended.

@Tmonster, I plan to use the LogicalDummyScan to generate "1 row scan of NULL values", but I don't know how to set the table index of the operator. It seems that LogicalDummyScan is not suitable to be used here. So am I on the right way?

@Tmonster, I plan to use the LogicalDummyScan to generate "1 row scan of NULL values", but I don't know how to set the table index of the operator. It seems that LogicalDummyScan is not suitable to be used here. So am I on the right way?

I think you are on the right path. You have two options I think here.

1. You can use the binder in the optimizer class to Generate a new binding like they do here. However, you can't use this for the dummy scan since it's possible the RHS is a product of multiple tables and therefore projects expressions with different table indexes. To fix that you will need to use the ColumnBindingReplacer to replace the original RHS columns with new ones from the LogicalDummyScan. You can find an example here.

2. Create multiple LogicalDummyScans for each table index you find in the bindings of the RHS. Make each scan produce 1 row of NULLS with the correct types and cross product them all together. You can assign the table index from the original RHS tables and the plan will still be valid.

I think 2 might be easier since the column binding replacer process can get confusing, although 1 is faster execution wise since it is only 1 logicalDummy scan instead of multiple extra cross products.

Let me know if this helps 👍

@Tmonster, thank you for the great suggestions! They are really useful. I have adopted the second one. I have worked on the first one and it was almost done, but when I applied the ColumnBindingReplacer to the root plan, it failed on null pointer because the processing operator of the plan was moved into the Rewrite function call and meanwhile new FilterPushdowns can be generated with child operator moved in during the process. ColumnBindingReplacer can't process the incomplete plan and it will be a little complicated to work that around so I chosen the second suggestion.

Code has updated as comment.

Thanks!