@taiyang-li, just tested Velox backend. It has the same issue.

(5) ProjectExecTransformer
Output [4]: [(id#3036L + cast(1 as bigint)) AS n1#3033L, (id#3036L + cast(2 as bigint)) AS n2#3034L, if (((id#3036L % cast(2 as bigint)) = cast(0 as bigint))) (id#3036L + cast(3 as bigint)) else (id#3036L + cast(4 as bigint)) AS n3#3035L, if (((((id#3036L + cast(1 as bigint)) + (id#3036L + cast(2 as bigint))) + (if (((id#3036L % cast(2 as bigint)) = cast(0 as bigint))) (id#3036L + cast(3 as bigint)) else (id#3036L + cast(4 as bigint)) % cast(2 as bigint))) = cast(0 as bigint))) 1 else 0 AS _pre_0#3065]
Input [1]: [id#3036L]

@liujiayi771, could you help take a look?

I will take a look.

@liujiayi771 in gluten we already had a rule CommonSubexpressionEliminateRule to eliminate common subexpressions, but it is applied to logical plan. Is there any possibility that PullOutPreProject rule applys to logical plan instead of physical plan.

@taiyang-li This rule was discussed a long time ago to be implemented in the logical plan, and we did indeed implement a version based on the logical plan before. There are many cases that are not supported if we only rely on the logical plan, for example, the aggregation generated by Bloom filters can only be seen in the physical plan. I remember there are quite a few such cases. Theoretically, duplicates should not appear here because there is logic to eliminate duplicates; I need to debug and take a look at this.

@PHILO-HE @taiyang-li The reason for the issue here is that after preProject is extracted, when it is merged with the previous project in the CollapseProjectExecTransformer rule, n3 must be replaced with the actual computed expression; otherwise, it will not be able to bindReference.

To eliminate duplicate executions, in this case, the pre-project that is pulled out should not be merged within the CollapseProjectExecTransformer, which would result in an additional project operator being created. However, this would prevent the expression from being computed multiple times. What do you think?

cc @ulysses-you Do you have any suggestions?

@liujiayi771 it is ok for clickhouse backend, because CH plan optimization would merge adjacent project operators. Besides, whether they are merged or not in CH doesn't have more impacts than duplicated expression evaluations.

@taiyang-li I had a discussion with @ulysses-you offline, and in this case, we need to implement a more refined collapsing strategy. We will remove the column for n3 in the child project and keep only the calculation of n3 from the pre-project. I will work on this part of the development next week.