Skip to content

Physical optimizer rule projection_pushdown undo the logical rule common_subexpr_eliminate #8453

@haohuaijin

Description

@haohuaijin

Describe the bug

I looked at the discussion in #8296 , and found we don't unify projection if the expression is non-trivial. and #8340 fix the conflicting between common_sub_expression_eliminate and push_down_projection
But I found another problem. physical optimizer rule projection_pushdown unifies the projection that contains non-trivial expr. We should not unify it.
in main

❯ create table t(a bigint);
0 rows in set. Query took 0.007 seconds.

❯ explain select a/2, a/2+1 from t;
+---------------+-----------------------------------------------------------------------------------------------------------------+
| plan_type     | plan                                                                                                            |
+---------------+-----------------------------------------------------------------------------------------------------------------+
| logical_plan  | Projection: t.a / Int64(2)Int64(2)t.a AS t.a / Int64(2), t.a / Int64(2)Int64(2)t.a AS t.a / Int64(2) + Int64(1) |
|               |   Projection: t.a / Int64(2) AS t.a / Int64(2)Int64(2)t.a                                                       |
|               |     TableScan: t projection=[a]                                                                                 |
| physical_plan | ProjectionExec: expr=[a@0 / 2 as t.a / Int64(2), a@0 / 2 + 1 as t.a / Int64(2) + Int64(1)]                      |
|               |   MemoryExec: partitions=1, partition_sizes=[0]                                                                 |
|               |                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------+
2 rows in set. Query took 0.009 seconds.
| physical_plan after OutputRequirements                     | ProjectionExec: expr=[t.a / Int64(2)Int64(2)t.a@0 as t.a / Int64(2), t.a / Int64(2)Int64(2)t.a@0 + 1 as t.a / Int64(2) + Int64(1)]                                                                    |
|                                                            |   ProjectionExec: expr=[a@0 / 2 as t.a / Int64(2)Int64(2)t.a]                                                                                                                                         |
|                                                            |     MemoryExec: partitions=1, partition_sizes=[0]                                                                                                                                                     |
|                                                            |                                                                                                                                                                                                       |
| physical_plan after PipelineChecker                        | SAME TEXT AS ABOVE                                                                                                                                                                                    |
| physical_plan after LimitAggregation                       | SAME TEXT AS ABOVE                                                                                                                                                                                    |
| physical_plan after ProjectionPushdown                     | ProjectionExec: expr=[a@0 / 2 as t.a / Int64(2), a@0 / 2 + 1 as t.a / Int64(2) + Int64(1)]                                                                                                            |
|                                                            |   MemoryExec: partitions=1, partition_sizes=[0] 

To Reproduce

No response

Expected behavior

we don't unify projection if the expression is non-trivial.

Additional context

I open a pr to fix it.

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions