Optimizer adds redundant projections on casts with confusing alias

[andygrove]

Describe the bug

#3764 fixed one regression but introduced another.

I do not understand why this additional projection is being added.

Before

Inner Join: store_sales.ss_sold_date_sk = date_dim.d_date_sk

After

Projection: CAST(date_dim.d_moy AS Int64) AS CAST(date_dim.d_moy AS Int64)date_dim.d_moy, store_sales.ss_customer_sk, date_dim.d_year
  Inner Join: store_sales.ss_sold_date_sk = date_dim.d_date_sk

To Reproduce
No repro available yet

Expected behavior

Additional context

[alamb]

Note I tried to explain in #3794 why aliases are needed for optimizer rewrite passes in general.

This may also be related to #3770

[alamb]

Using EXPLAIN VERBOSE on the plan should reveal which optimizer pass is making this change. If I had to guess, I would guess CommonSubexprEliminate

[liukun4515]

Maybe related to this issue #3635 which will produce redundant projection after run the optimizer

[andygrove]

Another example:

Projection: household_demographics.hd_vehicle_count > Int32(0) AS household_demographics.hd_vehicle_count > Int32(0)Int32(0)household_demographics.hd_vehicle_count

The alias name should just be:

household_demographics.hd_vehicle_count > Int32(0)

and not

household_demographics.hd_vehicle_count > Int32(0)Int32(0)household_demographics.hd_vehicle_count

[andygrove]

Another example in #3884 (comment)

[andygrove]

I confirmed that this does happen in common_sub_expression_eliminate