[CH] Duplicated expression evaluation in project before aggregate operator due to PullOutPreProject Rule

[taiyang-li]

Backend

CH (ClickHouse)

Bug description

In below query, expr if(id % 2 = 0, id+3, id+4) was evaluated twice both in n3 and _pre_5. I find that _pre_5 is introduced by PullOutPreProject in #4213. cc @PHILO-HE @zhztheplayer can you find similar issue in Velox?

explain extended 
select max(n1), max(n2), sum(if(n1+n2+n3 % 2 = 0, 1, 0)) from (select id+1 as n1, id+2 as n2, if(id % 2 = 0, id+3, id+4) as n3 from range(10)) 

== Optimized Logical Plan ==
Aggregate [max(n1#375L) AS max(n1)#386L, max(n2#376L) AS max(n2)#387L, sum(if ((((n1#375L + n2#376L) + (n3#377L % 2)) = 0)) 1 else 0) AS sum((IF((((n1 + n2) + (n3 % 2)) = 0), 1, 0)))#389L]
+- Project [(id#383L + 1) AS n1#375L, (id#383L + 2) AS n2#376L, if (((id#383L % 2) = 0)) (id#383L + 3) else (id#383L + 4) AS n3#377L]
   +- Range (0, 10, step=1, splits=None)

== Physical Plan ==
CHNativeColumnarToRow
+- ^(9) HashAggregateTransformer(keys=[], functions=[max(n1#375L), max(n2#376L), sum(_pre_5#396)], isStreamingAgg=false, output=[max(n1)#386L, max(n2)#387L, sum((IF((((n1 + n2) + (n3 % 2)) = 0), 1, 0)))#389L])
   +- ^(9) ProjectExecTransformer [(id#383L + 1) AS n1#375L, (id#383L + 2) AS n2#376L, if (((id#383L % 2) = 0)) (id#383L + 3) else (id#383L + 4) AS n3#377L, if (((((id#383L + 1) + (id#383L + 2)) + (if (((id#383L % 2) = 0)) (id#383L + 3) else (id#383L + 4) % 2)) = 0)) 1 else 0 AS _pre_5#396]
      +- ^(9) InputIteratorTransformer[id#383L]
         +- RowToCHNativeColumnar
            +- *(1) Range (0, 10, step=1, splits=1)

Spark version

None

Spark configurations

No response

System information

No response

Relevant logs

No response

[PHILO-HE]

@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]