Projection fails on TPCDS Q13

[comphead]

After latest merge with main I'm getting

thread 'main' (115794474) panicked at datafusion/physical-expr/src/projection.rs:374:22:
Expected column reference in projection

on Q13, investigating

Originally posted by @comphead in #18985 (comment)

[comphead]

To reproduce

DataFusion CLI v51.0.0
> -- Script to register TPC-DS parquet tables
-- Run this script in datafusion-cli to register all TPC-DS tables
-- Usage: datafusion-cli -f register_tpcds_tables.sql

CREATE EXTERNAL TABLE call_center
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/call_center.parquet';

CREATE EXTERNAL TABLE catalog_page
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/catalog_page.parquet';

CREATE EXTERNAL TABLE catalog_returns
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/catalog_returns.parquet';

CREATE EXTERNAL TABLE catalog_sales
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/catalog_sales.parquet';

CREATE EXTERNAL TABLE customer_address
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/customer_address.parquet';

CREATE EXTERNAL TABLE customer_demographics
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/customer_demographics.parquet';

CREATE EXTERNAL TABLE customer
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/customer.parquet';

CREATE EXTERNAL TABLE date_dim
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/date_dim.parquet';

CREATE EXTERNAL TABLE household_demographics
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/household_demographics.parquet';

CREATE EXTERNAL TABLE income_band
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/income_band.parquet';

CREATE EXTERNAL TABLE inventory
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/inventory.parquet';

CREATE EXTERNAL TABLE item
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/item.parquet';

CREATE EXTERNAL TABLE promotion
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/promotion.parquet';

CREATE EXTERNAL TABLE reason
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/reason.parquet';

CREATE EXTERNAL TABLE ship_mode
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/ship_mode.parquet';

CREATE EXTERNAL TABLE store_returns
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/store_returns.parquet';

CREATE EXTERNAL TABLE store_sales
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/store_sales.parquet';

CREATE EXTERNAL TABLE store
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/store.parquet';

CREATE EXTERNAL TABLE time_dim
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/time_dim.parquet';

CREATE EXTERNAL TABLE warehouse
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/warehouse.parquet';

CREATE EXTERNAL TABLE web_page
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/web_page.parquet';

CREATE EXTERNAL TABLE web_returns
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/web_returns.parquet';

CREATE EXTERNAL TABLE web_sales
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/web_sales.parquet';

CREATE EXTERNAL TABLE web_site
STORED AS PARQUET
LOCATION '../datafusion-benchmarks/tpcds/data_parquet/web_site.parquet';
0 row(s) fetched. 
Elapsed 0.029 seconds.

0 row(s) fetched. 
Elapsed 0.004 seconds.

0 row(s) fetched. 
Elapsed 0.019 seconds.

0 row(s) fetched. 
Elapsed 0.036 seconds.

0 row(s) fetched. 
Elapsed 0.009 seconds.

0 row(s) fetched. 
Elapsed 0.007 seconds.

0 row(s) fetched. 
Elapsed 0.011 seconds.

0 row(s) fetched. 
Elapsed 0.003 seconds.

0 row(s) fetched. 
Elapsed 0.002 seconds.

0 row(s) fetched. 
Elapsed 0.001 seconds.

0 row(s) fetched. 
Elapsed 0.012 seconds.

0 row(s) fetched. 
Elapsed 0.003 seconds.

0 row(s) fetched. 
Elapsed 0.002 seconds.

0 row(s) fetched. 
Elapsed 0.001 seconds.

0 row(s) fetched. 
Elapsed 0.002 seconds.

0 row(s) fetched. 
Elapsed 0.013 seconds.

0 row(s) fetched. 
Elapsed 0.033 seconds.

0 row(s) fetched. 
Elapsed 0.003 seconds.

0 row(s) fetched. 
Elapsed 0.002 seconds.

0 row(s) fetched. 
Elapsed 0.002 seconds.

0 row(s) fetched. 
Elapsed 0.002 seconds.

0 row(s) fetched. 
Elapsed 0.013 seconds.

0 row(s) fetched. 
Elapsed 0.024 seconds.

0 row(s) fetched. 
Elapsed 0.003 seconds.

> -- SQLBench-DS query 13 derived from TPC-DS query 13 under the terms of the TPC Fair Use Policy.
-- TPC-DS queries are Copyright 2021 Transaction Processing Performance Council.
-- This query was generated at scale factor 1.
select avg(ss_quantity)
       ,avg(ss_ext_sales_price)
       ,avg(ss_ext_wholesale_cost)
       ,sum(ss_ext_wholesale_cost)
 from store_sales
     ,store
     ,customer_demographics
     ,household_demographics
     ,customer_address
     ,date_dim
 where s_store_sk = ss_store_sk
 and  ss_sold_date_sk = d_date_sk and d_year = 2001
 and((ss_hdemo_sk=hd_demo_sk
  and cd_demo_sk = ss_cdemo_sk
  and cd_marital_status = 'U'
  and cd_education_status = '4 yr Degree'
  and ss_sales_price between 100.00 and 150.00
  and hd_dep_count = 3   
     )or
     (ss_hdemo_sk=hd_demo_sk
  and cd_demo_sk = ss_cdemo_sk
  and cd_marital_status = 'S'
  and cd_education_status = 'Unknown'
  and ss_sales_price between 50.00 and 100.00   
  and hd_dep_count = 1
     ) or 
     (ss_hdemo_sk=hd_demo_sk
  and cd_demo_sk = ss_cdemo_sk
  and cd_marital_status = 'D'
  and cd_education_status = '2 yr Degree'
  and ss_sales_price between 150.00 and 200.00 
  and hd_dep_count = 1  
     ))
 and((ss_addr_sk = ca_address_sk
  and ca_country = 'United States'
  and ca_state in ('CO', 'MI', 'MN')
  and ss_net_profit between 100 and 200  
     ) or
     (ss_addr_sk = ca_address_sk
  and ca_country = 'United States'
  and ca_state in ('NC', 'NY', 'TX')
  and ss_net_profit between 150 and 300  
     ) or
     (ss_addr_sk = ca_address_sk
  and ca_country = 'United States'
  and ca_state in ('CA', 'NE', 'TN')
  and ss_net_profit between 50 and 250  
     ))
;



thread 'main' (115968842) panicked at datafusion/physical-expr/src/projection.rs:374:22:
Expected column reference in projection

[comphead]

Looks like it comes from #18627

[comphead]

The filter expression comes among projection columns which crashes the planner

[datafusion/physical-expr/src/projection.rs:368:9] &self.exprs = [
    ProjectionExpr {
        expr: BinaryExpr {
            left: BinaryExpr {
                left: Column {
                    name: "hd_dep_count",
                    index: 3,
                },
                op: Eq,
                right: Literal {
                    value: Int64(3),
                    field: Field {
                        name: "lit",
                        data_type: Int64,
                    },
                },
                fail_on_overflow: false,
            },
            op: Or,
            right: BinaryExpr {
                left: Column {
                    name: "hd_dep_count",
                    index: 3,
                },
                op: Eq,
                right: Literal {
                    value: Int64(1),
                    field: Field {
                        name: "lit",
                        data_type: Int64,
                    },
                },
                fail_on_overflow: false,
            },
            fail_on_overflow: false,
        },
        alias: "__common_expr_2",
    },
    ProjectionExpr {
        expr: Column {
            name: "hd_demo_sk",
            index: 0,
        },
        alias: "hd_demo_sk",
    },
    ProjectionExpr {
        expr: Column {
            name: "hd_dep_count",
            index: 3,
        },
        alias: "hd_dep_count",
    },
]

[Omega359]

I think I saw the expected column reference error when testing #19078 - https://github.com/apache/datafusion/pull/19078/files#diff-524b5bd42ccd136e8e563ec7fe7b54bbe06473b17d829f06dbe792f391a26b2fR2375

[comphead]

I came to the issue start with projection pushdown

datafusion/datafusion/physical-optimizer/src/projection_pushdown.rs

Line 78 in 8dac8f1

plan.transform_down(remove_unnecessary_projections).data()

from remove_unnecessary_projections

The reason is the logical plan contains bad(or I would say weird) construction in projection expressions

datafusion/physical-plan/src/projection.rs:562:13] &projection = ProjectionExec {
    projector: Projector {
        projection: ProjectionExprs {
            exprs: [
                ProjectionExpr {
                    expr: BinaryExpr {
                        left: BinaryExpr {
                            left: Column {
                                name: "hd_dep_count",
                                index: 1,
                            },
                            op: Eq,
                            right: Literal {
                                value: Int64(3),
                                field: Field {
                                    name: "lit",
                                    data_type: Int64,
                                },
                            },
                            fail_on_overflow: false,
                        },
                        op: Or,
                        right: BinaryExpr {
                            left: Column {
                                name: "hd_dep_count",
                                index: 1,
                            },
                            op: Eq,
                            right: Literal {
                                value: Int64(1),
                                field: Field {
                                    name: "lit",
                                    data_type: Int64,
                                },
                            },
                            fail_on_overflow: false,
                        },
                        fail_on_overflow: false,
                    },
                    alias: "__common_expr_2",
                },
                ProjectionExpr {
                    expr: Column {
                        name: "hd_demo_sk",
                        index: 0,
                    },
                    alias: "hd_demo_sk",
                },
                ProjectionExpr {
                    expr: Column {
                        name: "hd_dep_count",
                        index: 1,
                    },
                    alias: "hd_dep_count",
                },
            ],
        },

@adriangb as you worked recently on projections,
WDYT is this expression is unexpected?

[comphead]

Some other changes in projection pushdown made recently https://github.com/apache/datafusion/pull/17906/files

[adriangb]

Looks like it comes from #18627

Did you run a bisect or is it a hypothesis?

[adriangb]

(I'm looking into this tonight)

[comphead]

Thanks @adriangb this is hypothesis for now, I can definitely state that in 50.3.0 the issue is not coming up.
So some PR changing projections after 50.3.0 released has caused it, I'm planning to spend some time today to figure out more details.