[CH] Add pre-projection for hash shuffle with expressions failed.

[lgbo-ustc]

(you don't have to strictly follow this form)

Describe the unexpected behaviour

Following query failed

select count(1) from ( select '2023-03-07' as sday
,abflag
,coalesce(country,'all') as country
,coalesce(usertype,'all') as usertype
,count(a.uid) as dau
,cast(null as bigint) as retention_1d_uv
,cast(null as bigint) as retention_2d_uv
,cast(null as bigint) as retention_3d_uv
,cast(null as bigint) as retention_4d_uv
,cast(null as bigint) as retention_5d_uv
,cast(null as bigint) as retention_6d_uv
,cast(null as bigint) as retention_7d_uv

,count(b.uid)/count(a.uid) as retention_1d_rate
,cast (null as float) as retention_2d_rate
,count(c.uid)/count(a.uid) as retention_3d_rate
,cast (null as float) as retention_4d_rate
,cast (null as float) as retention_5d_rate
,cast (null as float) as retention_6d_rate
,count(d.uid)/count(a.uid) as retention_7d_rate

from
    (select a.day
    ,a.abflag
    ,b.country
    ,if(c.uid is not null,'new','old') as usertype
    ,a.uid
    from
        (select day,abflag,app_uid as uid
        from swh_client_abflag
        where day = '2023-03-07'
        group by day,abflag,app_uid
        ) a
    join
        (select day,app_uid as uid,country
        from  com_dim_snapshot_user
        where day = '2023-03-07'
        and app_use_time_order_desc_rank=1
        ) b
    on a.day=b.day and a.uid=b.uid
    left join
        (select day,uid
        from account_registrations
        where day = '2023-03-07'
        and num_deleted=0
        group by day,uid
        ) c
    on a.day=c.day and a.uid=c.uid
    ) a
left join
    (
    select day,app_uid as uid
    from com_dim_snapshot_user
    where day = date_add('2023-03-07',1)
    and app_use_time_order_desc_rank=1
    ) b
on a.uid=b.uid
left join
    (
    select day,app_uid as uid
    from com_dim_snapshot_user
    where day = date_add('2023-03-07',3)
    and app_use_time_order_desc_rank=1
    ) c
on a.uid=c.uid
left join
    (-- 七留
    select day,app_uid as uid
    from com_dim_snapshot_user
    where day = date_add('2023-03-07',7)
    and app_use_time_order_desc_rank=1
    ) d
on a.uid=d.uid
group by abflag,country,usertype
grouping sets(
    (abflag),
    (abflag,country),
    (abflag,usertype),
    (abflag,country,usertype)
) )

Catch an exception

java.lang.IllegalStateException: Couldn't find uid#0 in [day#121,abflag#113,imo_uid#115]
	at org.apache.spark.sql.catalyst.expressions.BindReferences$$anonfun$bindReference$1.applyOrElse(BoundAttribute.scala:80)
	at org.apache.spark.sql.catalyst.expressions.BindReferences$$anonfun$bindReference$1.applyOrElse(BoundAttribute.scala:73)
	at org.apache.spark.sql.catalyst.trees.TreeNode.$anonfun$transformDownWithPruning$1(TreeNode.scala:481)
	at org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:82)
	at org.apache.spark.sql.catalyst.trees.TreeNode.transformDownWithPruning(TreeNode.scala:481)
	at org.apache.spark.sql.catalyst.trees.TreeNode.transformDown(TreeNode.scala:457)
	at org.apache.spark.sql.catalyst.trees.TreeNode.transform(TreeNode.scala:425)
	at org.apache.spark.sql.catalyst.expressions.BindReferences$.bindReference(BoundAttribute.scala:73)
	at io.glutenproject.extension.TransformPreOverrides.$anonfun$addProjectionForShuffleExchange$1(ColumnarOverrides.scala:182)
	at io.glutenproject.extension.TransformPreOverrides.$anonfun$addProjectionForShuffleExchange$1$adapted(ColumnarOverrides.scala:174)
	at scala.collection.immutable.List.foreach(List.scala:431)
	at io.glutenproject.extension.TransformPreOverrides.selectExpressions$1(ColumnarOverrides.scala:174)
	at io.glutenproject.extension.TransformPreOverrides.addProjectionForShuffleExchange(ColumnarOverrides.scala:194)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:359)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:382)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:275)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:382)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:275)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:340)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:382)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:275)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:382)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:275)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:382)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:275)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:327)
	at io.glutenproject.extension.TransformPreOverrides.genHashAggregateExec(ColumnarOverrides.scala:87)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:281)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:340)
	at io.glutenproject.extension.TransformPreOverrides.genHashAggregateExec(ColumnarOverrides.scala:87)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:281)
	at io.glutenproject.extension.TransformPreOverrides.genHashAggregateExec(ColumnarOverrides.scala:87)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:281)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:340)
	at io.glutenproject.extension.TransformPreOverrides.genHashAggregateExec(ColumnarOverrides.scala:87)
	at io.glutenproject.extension.TransformPreOverrides.replaceWithTransformerPlan(ColumnarOverrides.scala:281)
	at io.glutenproject.extension.TransformPreOverrides.apply(ColumnarOverrides.scala:548)
	at io.glutenproject.extension.TransformPreOverrides.apply(ColumnarOverrides.scala:48)
	at io.glutenproject.extension.ColumnarOverrideRules.$anonfun$preColumnarTransitions$5(ColumnarOverrides.scala:720)
	at io.glutenproject.extension.ColumnarOverrideRules.$anonfun$preColumnarTransitions$5$adapted(ColumnarOverrides.scala:719)
	at scala.collection.immutable.List.foreach(List.scala:431)

[lgbo-ustc]

@liuneng1994