bug: unable to get field named in GROUP BY ROLLUP when group_by_shuffle_mode = 'before_partial' #18338
Description
Summary
TPC-DS Query-5 has been consistently failing in cluster mode across multiple versions, including very old releases. This is NOT a recent regression since I have try the old version also have this issue.
The query executes successfully in single-node mode.
Error:
1006=>Unable to get field named "407". Valid fields: ["400", "401", "402", "403", "404"]
TPC-DS Query-5/14/18/22/67/70/77/80/86/
Steps to Reproduce
10GB setup:
https://github.com/databendlabs/wizard/blob/main/checksb/sql/tpcds/bend/setup.sql
python checksb.py --case tpcds --check-only --runbend
Cluster plan(Q5):
[0] INFO - 8588fa1af05e4adfa2debd27fa2bb7c6 - Final s_expr:
Limit
├── limit: [100]
├── offset: [0]
└── Sort
├── sort keys: [derived.channel (#400) ASC NULLS FIRST, derived.id (#401) ASC NULLS FIRST]
├── limit: [100]
└── Exchange(MergeSort)
└── Sort
├── sort keys: [derived.channel (#400) ASC NULLS FIRST, derived.id (#401) ASC NULLS FIRST]
├── limit: [100]
└── Aggregate(Final)
├── group items: [x.channel (#400) AS (#400), x.id (#401) AS (#401), _grouping_id (#407) AS (#407)]
├── aggregate functions: [sum(sales) AS (#408), sum(returns_) AS (#409), sum(profit) AS (#410)]
└── Aggregate(Partial)
├── group items: [x.channel (#400) AS (#400), x.id (#401) AS (#401), _grouping_id (#407) AS (#407)]
├── aggregate functions: [sum(sales) AS (#408), sum(returns_) AS (#409), sum(profit) AS (#410)]
└── Exchange(Hash)
├── Exchange(Hash): keys: [x.channel (#400), x.id (#401), _grouping_id (#407)]
└── UnionAll
├── output: [derived.channel (#400), derived.id (#401), derived.sales (#402), derived.returns_ (#403), derived.profit (#404)]
├── left: [derived.channel (#232), derived.id (#233), derived.sales (#234), derived.returns_ (#235), derived.profit (#236)]
├── right: [derived.channel (#397), derived.id (#398), derived.sum(sales_price) (#393), derived.sum(return_amt) (#395), derived.profit (#399)]
├── cte_scan_names: []
├── UnionAll
│ ├── output: [derived.channel (#232), derived.id (#233), derived.sales (#234), derived.returns_ (#235), derived.profit (#236)]
│ ├── left: [derived.channel (#114), derived.id (#115), derived.sum(sales_price) (#110), derived.sum(return_amt) (#112), derived.profit (#116)]
│ ├── right: [derived.channel (#229), derived.id (#230), derived.sum(sales_price) (#225), derived.sum(return_amt) (#227), derived.profit (#231)]
│ ├── cte_scan_names: []
│ ├── EvalScalar
│ │ ├── scalars: [store.s_store_id (#82) AS (#82), ssr.sales (#110) AS (#110), sum(sales_price) (#110) AS (#110), sum(profit) (#111) AS (#111), ssr.returns_ (#112) AS (#112), sum(return_amt) (#112) AS (#112), sum(net_loss) (#113) AS (#113), 'store channel' AS (#114), concat('store', ssr.s_store_id (#82)) AS (#115), minus(ssr.profit (#111), ssr.profit_loss (#113)) AS (#116)]
│ │ └── Aggregate(Final)
│ │ ├── group items: [store.s_store_id (#82) AS (#82)]
│ │ ├── aggregate functions: [sum(sales_price) AS (#110), sum(profit) AS (#111), sum(return_amt) AS (#112), sum(net_loss) AS (#113)]
│ │ └── Aggregate(Partial)
│ │ ├── group items: [store.s_store_id (#82) AS (#82)]
│ │ ├── aggregate functions: [sum(sales_price) AS (#110), sum(profit) AS (#111), sum(return_amt) AS (#112), sum(net_loss) AS (#113)]
│ │ └── Exchange(Hash)
│ │ ├── Exchange(Hash): keys: [store.s_store_id (#82)]
│ │ └── EvalScalar
│ │ ├── scalars: [salesreturns.sales_price (#49) AS (#49), salesreturns.profit (#50) AS (#50), salesreturns.return_amt (#51) AS (#51), salesreturns.net_loss (#52) AS (#52), store.s_store_id (#82) AS (#82), salesreturns.date_sk (#48) AS (#411), date_dim.d_date_sk (#53) AS (#412), date_dim.d_date (#55) AS (#413), salesreturns.store_sk (#47) AS (#414), store.s_store_sk (#81) AS (#415)]
│ │ └── Join(Inner)
│ │ ├── build keys: [store.s_store_sk (#81)]
│ │ ├── probe keys: [salesreturns.store_sk (#47)]
│ │ ├── other filters: []
│ │ ├── Join(Inner)
│ │ │ ├── build keys: [date_dim.d_date_sk (#53)]
│ │ │ ├── probe keys: [salesreturns.date_sk (#48)]
│ │ │ ├── other filters: []
│ │ │ ├── UnionAll
│ │ │ │ ├── output: [derived.store_sk (#47), derived.date_sk (#48), derived.sales_price (#49), derived.profit (#50), derived.return_amt (#51), derived.net_loss (#52)]
│ │ │ │ ├── left: [checksb_db.store_sales.ss_store_sk (#7), checksb_db.store_sales.ss_sold_date_sk (#0), checksb_db.store_sales.ss_ext_sales_price (#15), checksb_db.store_sales.ss_net_profit (#22), CAST(return_amt (#23) AS Decimal(7, 2) NULL), CAST(net_loss (#24) AS Decimal(7, 2) NULL)]
│ │ │ │ ├── right: [checksb_db.store_returns.sr_store_sk (#32), checksb_db.store_returns.sr_returned_date_sk (#25), CAST(sales_price (#45) AS Decimal(7, 2) NULL), CAST(profit (#46) AS Decimal(7, 2) NULL), checksb_db.store_returns.sr_return_amt (#36), checksb_db.store_returns.sr_net_loss (#44)]
│ │ │ │ ├── cte_scan_names: []
│ │ │ │ ├── EvalScalar
│ │ │ │ │ ├── scalars: [store_sales.ss_sold_date_sk (#0) AS (#0), store_sales.ss_store_sk (#7) AS (#7), store_sales.ss_ext_sales_price (#15) AS (#15), store_sales.ss_net_profit (#22) AS (#22), CAST(0 AS Decimal(7, 2)) AS (#23), CAST(0 AS Decimal(7, 2)) AS (#24)]
│ │ │ │ │ └── Scan
│ │ │ │ │ ├── table: checksb_db.store_sales (#0)
│ │ │ │ │ ├── filters: []
│ │ │ │ │ ├── order by: []
│ │ │ │ │ └── limit: NONE
│ │ │ │ └── EvalScalar
│ │ │ │ ├── scalars: [store_returns.sr_returned_date_sk (#25) AS (#25), store_returns.sr_store_sk (#32) AS (#32), store_returns.sr_return_amt (#36) AS (#36), store_returns.sr_net_loss (#44) AS (#44), CAST(0 AS Decimal(7, 2)) AS (#45), CAST(0 AS Decimal(7, 2)) AS (#46)]
│ │ │ │ └── Scan
│ │ │ │ ├── table: checksb_db.store_returns (#1)
│ │ │ │ ├── filters: []
│ │ │ │ ├── order by: []
│ │ │ │ └── limit: NONE
│ │ │ └── Exchange(Broadcast)
│ │ │ └── Filter
│ │ │ ├── filters: [gte(date_dim.d_date (#55), '2000-08-23'), lte(date_dim.d_date (#55), '2000-09-06')]
│ │ │ └── Scan
│ │ │ ├── table: checksb_db.date_dim (#2)
│ │ │ ├── filters: [gte(date_dim.d_date (#55), '2000-08-23'), lte(date_dim.d_date (#55), '2000-09-06')]
│ │ │ ├── order by: []
│ │ │ └── limit: NONE
│ │ └── Exchange(Broadcast)
│ │ └── Scan
│ │ ├── table: checksb_db.store (#3)
│ │ ├── filters: []
│ │ ├── order by: []
│ │ └── limit: NONE
│ └── EvalScalar
│ ├── scalars: [catalog_page.cp_catalog_page_id (#217) AS (#217), csr.sales (#225) AS (#225), sum(sales_price) (#225) AS (#225), sum(profit) (#226) AS (#226), csr.returns_ (#227) AS (#227), sum(return_amt) (#227) AS (#227), sum(net_loss) (#228) AS (#228), 'catalog channel' AS (#229), concat('catalog_page', csr.cp_catalog_page_id (#217)) AS (#230), minus(csr.profit (#226), csr.profit_loss (#228)) AS (#231)]
│ └── Aggregate(Final)
│ ├── group items: [catalog_page.cp_catalog_page_id (#217) AS (#217)]
│ ├── aggregate functions: [sum(sales_price) AS (#225), sum(profit) AS (#226), sum(return_amt) AS (#227), sum(net_loss) AS (#228)]
│ └── Aggregate(Partial)
│ ├── group items: [catalog_page.cp_catalog_page_id (#217) AS (#217)]
│ ├── aggregate functions: [sum(sales_price) AS (#225), sum(profit) AS (#226), sum(return_amt) AS (#227), sum(net_loss) AS (#228)]
│ └── Exchange(Hash)
│ ├── Exchange(Hash): keys: [catalog_page.cp_catalog_page_id (#217)]
│ └── EvalScalar
│ ├── scalars: [salesreturns.sales_price (#184) AS (#184), salesreturns.profit (#185) AS (#185), salesreturns.return_amt (#186) AS (#186), salesreturns.net_loss (#187) AS (#187), catalog_page.cp_catalog_page_id (#217) AS (#217), salesreturns.date_sk (#183) AS (#416), date_dim.d_date_sk (#188) AS (#417), date_dim.d_date (#190) AS (#418), salesreturns.page_sk (#182) AS (#419), catalog_page.cp_catalog_page_sk (#216) AS (#420)]
│ └── Join(Inner)
│ ├── build keys: [catalog_page.cp_catalog_page_sk (#216)]
│ ├── probe keys: [salesreturns.page_sk (#182)]
│ ├── other filters: []
│ ├── Join(Inner)
│ │ ├── build keys: [date_dim.d_date_sk (#188)]
│ │ ├── probe keys: [salesreturns.date_sk (#183)]
│ │ ├── other filters: []
│ │ ├── UnionAll
│ │ │ ├── output: [derived.page_sk (#182), derived.date_sk (#183), derived.sales_price (#184), derived.profit (#185), derived.return_amt (#186), derived.net_loss (#187)]
│ │ │ ├── left: [checksb_db.catalog_sales.cs_catalog_page_sk (#129), checksb_db.catalog_sales.cs_sold_date_sk (#117), checksb_db.catalog_sales.cs_ext_sales_price (#140), checksb_db.catalog_sales.cs_net_profit (#150), CAST(return_amt (#151) AS Decimal(7, 2) NULL), CAST(net_loss (#152) AS Decimal(7, 2) NULL)]
│ │ │ ├── right: [checksb_db.catalog_returns.cr_catalog_page_sk (#165), checksb_db.catalog_returns.cr_returned_date_sk (#153), CAST(sales_price (#180) AS Decimal(7, 2) NULL), CAST(profit (#181) AS Decimal(7, 2) NULL), checksb_db.catalog_returns.cr_return_amount (#171), checksb_db.catalog_returns.cr_net_loss (#179)]
│ │ │ ├── cte_scan_names: []
│ │ │ ├── EvalScalar
│ │ │ │ ├── scalars: [catalog_sales.cs_sold_date_sk (#117) AS (#117), catalog_sales.cs_catalog_page_sk (#129) AS (#129), catalog_sales.cs_ext_sales_price (#140) AS (#140), catalog_sales.cs_net_profit (#150) AS (#150), CAST(0 AS Decimal(7, 2)) AS (#151), CAST(0 AS Decimal(7, 2)) AS (#152)]
│ │ │ │ └── Scan
│ │ │ │ ├── table: checksb_db.catalog_sales (#4)
│ │ │ │ ├── filters: []
│ │ │ │ ├── order by: []
│ │ │ │ └── limit: NONE
│ │ │ └── EvalScalar
│ │ │ ├── scalars: [catalog_returns.cr_returned_date_sk (#153) AS (#153), catalog_returns.cr_catalog_page_sk (#165) AS (#165), catalog_returns.cr_return_amount (#171) AS (#171), catalog_returns.cr_net_loss (#179) AS (#179), CAST(0 AS Decimal(7, 2)) AS (#180), CAST(0 AS Decimal(7, 2)) AS (#181)]
│ │ │ └── Scan
│ │ │ ├── table: checksb_db.catalog_returns (#5)
│ │ │ ├── filters: []
│ │ │ ├── order by: []
│ │ │ └── limit: NONE
│ │ └── Exchange(Broadcast)
│ │ └── Filter
│ │ ├── filters: [gte(date_dim.d_date (#190), '2000-08-23'), lte(date_dim.d_date (#190), '2000-09-06')]
│ │ └── Scan
│ │ ├── table: checksb_db.date_dim (#6)
│ │ ├── filters: [gte(date_dim.d_date (#190), '2000-08-23'), lte(date_dim.d_date (#190), '2000-09-06')]
│ │ ├── order by: []
│ │ └── limit: NONE
│ └── Exchange(Broadcast)
│ └── Scan
│ ├── table: checksb_db.catalog_page (#7)
│ ├── filters: []
│ ├── order by: []
│ └── limit: NONE
└── EvalScalar
├── scalars: [web_site.web_site_id (#368) AS (#368), wsr.sales (#393) AS (#393), sum(sales_price) (#393) AS (#393), sum(profit) (#394) AS (#394), wsr.returns_ (#395) AS (#395), sum(return_amt) (#395) AS (#395), sum(net_loss) (#396) AS (#396), 'web channel' AS (#397), concat('web_site', wsr.web_site_id (#368)) AS (#398), minus(wsr.profit (#394), wsr.profit_loss (#396)) AS (#399)]
└── Aggregate(Final)
├── group items: [web_site.web_site_id (#368) AS (#368)]
├── aggregate functions: [sum(sales_price) AS (#393), sum(profit) AS (#394), sum(return_amt) AS (#395), sum(net_loss) AS (#396)]
└── Aggregate(Partial)
├── group items: [web_site.web_site_id (#368) AS (#368)]
├── aggregate functions: [sum(sales_price) AS (#393), sum(profit) AS (#394), sum(return_amt) AS (#395), sum(net_loss) AS (#396)]
└── Exchange(Hash)
├── Exchange(Hash): keys: [web_site.web_site_id (#368)]
└── EvalScalar
├── scalars: [salesreturns.sales_price (#335) AS (#335), salesreturns.profit (#336) AS (#336), salesreturns.return_amt (#337) AS (#337), salesreturns.net_loss (#338) AS (#338), web_site.web_site_id (#368) AS (#368), salesreturns.date_sk (#334) AS (#421), date_dim.d_date_sk (#339) AS (#422), date_dim.d_date (#341) AS (#423), salesreturns.wsr_web_site_sk (#333) AS (#424), web_site.web_site_sk (#367) AS (#425)]
└── Join(Inner)
├── build keys: [web_site.web_site_sk (#367)]
├── probe keys: [salesreturns.wsr_web_site_sk (#333)]
├── other filters: []
├── Join(Inner)
│ ├── build keys: [date_dim.d_date_sk (#339)]
│ ├── probe keys: [salesreturns.date_sk (#334)]
│ ├── other filters: []
│ ├── UnionAll
│ │ ├── output: [derived.wsr_web_site_sk (#333), derived.date_sk (#334), derived.sales_price (#335), derived.profit (#336), derived.return_amt (#337), derived.net_loss (#338)]
│ │ ├── left: [checksb_db.web_sales.ws_web_site_sk (#250), checksb_db.web_sales.ws_sold_date_sk (#237), checksb_db.web_sales.ws_ext_sales_price (#260), checksb_db.web_sales.ws_net_profit (#270), CAST(return_amt (#271) AS Decimal(7, 2) NULL), CAST(net_loss (#272) AS Decimal(7, 2) NULL)]
│ │ ├── right: [checksb_db.web_sales.ws_web_site_sk (#310), checksb_db.web_returns.wr_returned_date_sk (#273), CAST(sales_price (#331) AS Decimal(7, 2) NULL), CAST(profit (#332) AS Decimal(7, 2) NULL), checksb_db.web_returns.wr_return_amt (#288), checksb_db.web_returns.wr_net_loss (#296)]
│ │ ├── cte_scan_names: []
│ │ ├── EvalScalar
│ │ │ ├── scalars: [web_sales.ws_sold_date_sk (#237) AS (#237), web_sales.ws_web_site_sk (#250) AS (#250), web_sales.ws_ext_sales_price (#260) AS (#260), web_sales.ws_net_profit (#270) AS (#270), CAST(0 AS Decimal(7, 2)) AS (#271), CAST(0 AS Decimal(7, 2)) AS (#272)]
│ │ │ └── Scan
│ │ │ ├── table: checksb_db.web_sales (#8)
│ │ │ ├── filters: []
│ │ │ ├── order by: []
│ │ │ └── limit: NONE
│ │ └── EvalScalar
│ │ ├── scalars: [web_returns.wr_returned_date_sk (#273) AS (#273), web_returns.wr_return_amt (#288) AS (#288), web_returns.wr_net_loss (#296) AS (#296), web_sales.ws_web_site_sk (#310) AS (#310), CAST(0 AS Decimal(7, 2)) AS (#331), CAST(0 AS Decimal(7, 2)) AS (#332)]
│ │ └── Join(Right)
│ │ ├── build keys: [web_returns.wr_item_sk (#275), web_returns.wr_order_number (#286)]
│ │ ├── probe keys: [web_sales.ws_item_sk (#300), web_sales.ws_order_number (#314)]
│ │ ├── other filters: []
│ │ ├── Exchange(Hash)
│ │ │ ├── Exchange(Hash): keys: [web_sales.ws_item_sk (#300)]
│ │ │ └── Scan
│ │ │ ├── table: checksb_db.web_sales (#10)
│ │ │ ├── filters: []
│ │ │ ├── order by: []
│ │ │ └── limit: NONE
│ │ └── Exchange(Hash)
│ │ ├── Exchange(Hash): keys: [web_returns.wr_item_sk (#275)]
│ │ └── Scan
│ │ ├── table: checksb_db.web_returns (#9)
│ │ ├── filters: []
│ │ ├── order by: []
│ │ └── limit: NONE
│ └── Exchange(Broadcast)
│ └── Filter
│ ├── filters: [gte(date_dim.d_date (#341), '2000-08-23'), lte(date_dim.d_date (#341), '2000-09-06')]
│ └── Scan
│ ├── table: checksb_db.date_dim (#11)
│ ├── filters: [gte(date_dim.d_date (#341), '2000-08-23'), lte(date_dim.d_date (#341), '2000-09-06')]
│ ├── order by: []
│ └── limit: NONE
└── Exchange(Broadcast)
└── Scan
├── table: checksb_db.web_site (#12)
├── filters: []
├── order by: []
└── limit: NONE
Standard plan(Q5):
Limit
├── output columns: [sum(sales) (#408), sum(returns_) (#409), sum(profit) (#410), channel (#400), id (#401), _grouping_id (#407)]
├── limit: 100
├── offset: 0
├── estimated rows: 100.00
└── Sort
├── output columns: [sum(sales) (#408), sum(returns_) (#409), sum(profit) (#410), channel (#400), id (#401), _grouping_id (#407)]
├── sort keys: [channel ASC NULLS FIRST, id ASC NULLS FIRST]
├── estimated rows: 12072.00
└── AggregateFinal
├── output columns: [sum(sales) (#408), sum(returns_) (#409), sum(profit) (#410), channel (#400), id (#401), _grouping_id (#407)]
├── group by: [channel, id, _grouping_id]
├── aggregate functions: [sum(sales), sum(returns_), sum(profit)]
├── estimated rows: 12072.00
└── AggregatePartial
├── group by: [channel, id, _grouping_id]
├── aggregate functions: [sum(sales), sum(returns_), sum(profit)]
├── estimated rows: 12072.00
└── AggregateExpand
├── output columns: [channel (#400), id (#401), sales (#402), returns_ (#403), profit (#404), _dup_group_item_0 (#405), _dup_group_item_1 (#406), _grouping_id (#407)]
├── grouping sets: [(channel, id), (channel), ()]
├── estimated rows: 12072.00
└── UnionAll
├── output columns: [channel (#400), id (#401), sales (#402), returns_ (#403), profit (#404)]
├── estimated rows: 12072.00
├── UnionAll
│ ├── output columns: [channel (#232), id (#233), sales (#234), returns_ (#235), profit (#236)]
│ ├── estimated rows: 12051.00
│ ├── EvalScalar
│ │ ├── output columns: [sum(sales_price) (#110), sum(return_amt) (#112), channel (#114), id (#115), profit (#116)]
│ │ ├── expressions: ['store channel', concat('store', ssr.s_store_id (#82)), ssr.profit (#111) - ssr.profit_loss (#113)]
│ │ ├── estimated rows: 51.00
│ │ └── AggregateFinal
│ │ ├── output columns: [sum(sales_price) (#110), sum(profit) (#111), sum(return_amt) (#112), sum(net_loss) (#113), store.s_store_id (#82)]
│ │ ├── group by: [s_store_id]
│ │ ├── aggregate functions: [sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)]
│ │ ├── estimated rows: 51.00
│ │ └── AggregatePartial
│ │ ├── group by: [s_store_id]
│ │ ├── aggregate functions: [sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)]
│ │ ├── estimated rows: 51.00
│ │ └── HashJoin
│ │ ├── output columns: [sales_price (#49), profit (#50), return_amt (#51), net_loss (#52), store.s_store_id (#82)]
│ │ ├── join type: INNER
│ │ ├── build keys: [store.s_store_sk (#81)]
│ │ ├── probe keys: [salesreturns.store_sk (#47)]
│ │ ├── keys is null equal: [false]
│ │ ├── filters: []
│ │ ├── estimated rows: 97553510961.46
│ │ ├── TableScan(Build)
│ │ │ ├── table: default.checksb_db.store
│ │ │ ├── output columns: [s_store_sk (#81), s_store_id (#82)]
│ │ │ ├── read rows: 102
│ │ │ ├── read size: < 1 KiB
│ │ │ ├── partitions total: 1
│ │ │ ├── partitions scanned: 1
│ │ │ ├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 1 to 1>]
│ │ │ ├── push downs: [filters: [], limit: NONE]
│ │ │ └── estimated rows: 102.00
│ │ └── HashJoin(Probe)
│ │ ├── output columns: [store_sk (#47), sales_price (#49), profit (#50), return_amt (#51), net_loss (#52)]
│ │ ├── join type: INNER
│ │ ├── build keys: [date_dim.d_date_sk (#53)]
│ │ ├── probe keys: [salesreturns.date_sk (#48)]
│ │ ├── keys is null equal: [false]
│ │ ├── filters: []
│ │ ├── estimated rows: 956406970.21
│ │ ├── Filter(Build)
│ │ │ ├── output columns: [date_dim.d_date_sk (#53)]
│ │ │ ├── filters: [is_true(date_dim.d_date (#55) >= '2000-08-23'), is_true(date_dim.d_date (#55) <= '2000-09-06')]
│ │ │ ├── estimated rows: 30.19
│ │ │ └── TableScan
│ │ │ ├── table: default.checksb_db.date_dim
│ │ │ ├── output columns: [d_date_sk (#53), d_date (#55)]
│ │ │ ├── read rows: 29728
│ │ │ ├── read size: 145.71 KiB
│ │ │ ├── partitions total: 3
│ │ │ ├── partitions scanned: 1
│ │ │ ├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 3 to 1>]
│ │ │ ├── push downs: [filters: [and_filters(date_dim.d_date (#55) >= '2000-08-23', date_dim.d_date (#55) <= '2000-09-06')], limit: NONE]
│ │ │ └── estimated rows: 73049.00
│ │ └── UnionAll(Probe)
│ │ ├── output columns: [store_sk (#47), date_sk (#48), sales_price (#49), profit (#50), return_amt (#51), net_loss (#52)]
│ │ ├── estimated rows: 31676423.00
│ │ ├── EvalScalar
│ │ │ ├── output columns: [store_sales.ss_sold_date_sk (#0), store_sales.ss_store_sk (#7), store_sales.ss_ext_sales_price (#15), store_sales.ss_net_profit (#22), return_amt (#23), net_loss (#24)]
│ │ │ ├── expressions: [0.00, 0.00]
│ │ │ ├── estimated rows: 28800991.00
│ │ │ └── TableScan
│ │ │ ├── table: default.checksb_db.store_sales
│ │ │ ├── output columns: [ss_sold_date_sk (#0), ss_store_sk (#7), ss_ext_sales_price (#15), ss_net_profit (#22)]
│ │ │ ├── read rows: 28800991
│ │ │ ├── read size: 172.99 MiB
│ │ │ ├── partitions total: 32
│ │ │ ├── partitions scanned: 32
│ │ │ ├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 32 to 32>]
│ │ │ ├── push downs: [filters: [], limit: NONE]
│ │ │ └── estimated rows: 28800991.00
│ │ └── EvalScalar
│ │ ├── output columns: [store_returns.sr_returned_date_sk (#25), store_returns.sr_store_sk (#32), store_returns.sr_return_amt (#36), store_returns.sr_net_loss (#44), sales_price (#45), profit (#46)]
│ │ ├── expressions: [0.00, 0.00]
│ │ ├── estimated rows: 2875432.00
│ │ └── TableScan
│ │ ├── table: default.checksb_db.store_returns
│ │ ├── output columns: [sr_returned_date_sk (#25), sr_store_sk (#32), sr_return_amt (#36), sr_net_loss (#44)]
│ │ ├── read rows: 2875432
│ │ ├── read size: 23.80 MiB
│ │ ├── partitions total: 8
│ │ ├── partitions scanned: 8
│ │ ├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 8 to 8>]
│ │ ├── push downs: [filters: [], limit: NONE]
│ │ └── estimated rows: 2875432.00
│ └── EvalScalar
│ ├── output columns: [sum(sales_price) (#225), sum(return_amt) (#227), channel (#229), id (#230), profit (#231)]
│ ├── expressions: ['catalog channel', concat('catalog_page', csr.cp_catalog_page_id (#217)), csr.profit (#226) - csr.profit_loss (#228)]
│ ├── estimated rows: 12000.00
│ └── AggregateFinal
│ ├── output columns: [sum(sales_price) (#225), sum(profit) (#226), sum(return_amt) (#227), sum(net_loss) (#228), catalog_page.cp_catalog_page_id (#217)]
│ ├── group by: [cp_catalog_page_id]
│ ├── aggregate functions: [sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)]
│ ├── estimated rows: 12000.00
│ └── AggregatePartial
│ ├── group by: [cp_catalog_page_id]
│ ├── aggregate functions: [sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)]
│ ├── estimated rows: 12000.00
│ └── HashJoin
│ ├── output columns: [sales_price (#184), profit (#185), return_amt (#186), net_loss (#187), catalog_page.cp_catalog_page_id (#217)]
│ ├── join type: INNER
│ ├── build keys: [catalog_page.cp_catalog_page_sk (#216)]
│ ├── probe keys: [salesreturns.page_sk (#182)]
│ ├── keys is null equal: [false]
│ ├── filters: []
│ ├── estimated rows: 5739455763362.82
│ ├── TableScan(Build)
│ │ ├── table: default.checksb_db.catalog_page
│ │ ├── output columns: [cp_catalog_page_sk (#216), cp_catalog_page_id (#217)]
│ │ ├── read rows: 12000
│ │ ├── read size: 35.15 KiB
│ │ ├── partitions total: 1
│ │ ├── partitions scanned: 1
│ │ ├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 1 to 1>]
│ │ ├── push downs: [filters: [], limit: NONE]
│ │ └── estimated rows: 12000.00
│ └── HashJoin(Probe)
│ ├── output columns: [page_sk (#182), sales_price (#184), profit (#185), return_amt (#186), net_loss (#187)]
│ ├── join type: INNER
│ ├── build keys: [date_dim.d_date_sk (#188)]
│ ├── probe keys: [salesreturns.date_sk (#183)]
│ ├── keys is null equal: [false]
│ ├── filters: []
│ ├── estimated rows: 478287980.28
│ ├── Filter(Build)
│ │ ├── output columns: [date_dim.d_date_sk (#188)]
│ │ ├── filters: [is_true(date_dim.d_date (#190) >= '2000-08-23'), is_true(date_dim.d_date (#190) <= '2000-09-06')]
│ │ ├── estimated rows: 30.19
│ │ └── TableScan
│ │ ├── table: default.checksb_db.date_dim
│ │ ├── output columns: [d_date_sk (#188), d_date (#190)]
│ │ ├── read rows: 29728
│ │ ├── read size: 145.71 KiB
│ │ ├── partitions total: 3
│ │ ├── partitions scanned: 1
│ │ ├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 3 to 1>]
│ │ ├── push downs: [filters: [and_filters(date_dim.d_date (#190) >= '2000-08-23', date_dim.d_date (#190) <= '2000-09-06')], limit: NONE]
│ │ └── estimated rows: 73049.00
│ └── UnionAll(Probe)
│ ├── output columns: [page_sk (#182), date_sk (#183), sales_price (#184), profit (#185), return_amt (#186), net_loss (#187)]
│ ├── estimated rows: 15841010.00
│ ├── EvalScalar
│ │ ├── output columns: [catalog_sales.cs_sold_date_sk (#117), catalog_sales.cs_catalog_page_sk (#129), catalog_sales.cs_ext_sales_price (#140), catalog_sales.cs_net_profit (#150), return_amt (#151), net_loss (#152)]
│ │ ├── expressions: [0.00, 0.00]
│ │ ├── estimated rows: 14401261.00
│ │ └── TableScan
│ │ ├── table: default.checksb_db.catalog_sales
│ │ ├── output columns: [cs_sold_date_sk (#117), cs_catalog_page_sk (#129), cs_ext_sales_price (#140), cs_net_profit (#150)]
│ │ ├── read rows: 14401261
│ │ ├── read size: 104.09 MiB
│ │ ├── partitions total: 32
│ │ ├── partitions scanned: 32
│ │ ├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 32 to 32>]
│ │ ├── push downs: [filters: [], limit: NONE]
│ │ └── estimated rows: 14401261.00
│ └── EvalScalar
│ ├── output columns: [catalog_returns.cr_returned_date_sk (#153), catalog_returns.cr_catalog_page_sk (#165), catalog_returns.cr_return_amount (#171), catalog_returns.cr_net_loss (#179), sales_price (#180), profit (#181)]
│ ├── expressions: [0.00, 0.00]
│ ├── estimated rows: 1439749.00
│ └── TableScan
│ ├── table: default.checksb_db.catalog_returns
│ ├── output columns: [cr_returned_date_sk (#153), cr_catalog_page_sk (#165), cr_return_amount (#171), cr_net_loss (#179)]
│ ├── read rows: 1439749
│ ├── read size: 11.73 MiB
│ ├── partitions total: 5
│ ├── partitions scanned: 5
│ ├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 5 to 5>]
│ ├── push downs: [filters: [], limit: NONE]
│ └── estimated rows: 1439749.00
└── EvalScalar
├── output columns: [sum(sales_price) (#393), sum(return_amt) (#395), channel (#397), id (#398), profit (#399)]
├── expressions: ['web channel', concat('web_site', wsr.web_site_id (#368)), wsr.profit (#394) - wsr.profit_loss (#396)]
├── estimated rows: 21.00
└── AggregateFinal
├── output columns: [sum(sales_price) (#393), sum(profit) (#394), sum(return_amt) (#395), sum(net_loss) (#396), web_site.web_site_id (#368)]
├── group by: [web_site_id]
├── aggregate functions: [sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)]
├── estimated rows: 21.00
└── AggregatePartial
├── group by: [web_site_id]
├── aggregate functions: [sum(sales_price), sum(profit), sum(return_amt), sum(net_loss)]
├── estimated rows: 21.00
└── HashJoin
├── output columns: [sales_price (#335), profit (#336), return_amt (#337), net_loss (#338), web_site.web_site_id (#368)]
├── join type: INNER
├── build keys: [web_site.web_site_sk (#367)]
├── probe keys: [salesreturns.wsr_web_site_sk (#333)]
├── keys is null equal: [false]
├── filters: []
├── estimated rows: 20067753890.59
├── TableScan(Build)
│ ├── table: default.checksb_db.web_site
│ ├── output columns: [web_site_sk (#367), web_site_id (#368)]
│ ├── read rows: 42
│ ├── read size: < 1 KiB
│ ├── partitions total: 1
│ ├── partitions scanned: 1
│ ├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 1 to 1>]
│ ├── push downs: [filters: [], limit: NONE]
│ └── estimated rows: 42.00
└── HashJoin(Probe)
├── output columns: [wsr_web_site_sk (#333), sales_price (#335), profit (#336), return_amt (#337), net_loss (#338)]
├── join type: INNER
├── build keys: [date_dim.d_date_sk (#339)]
├── probe keys: [salesreturns.date_sk (#334)]
├── keys is null equal: [false]
├── filters: []
├── estimated rows: 477803664.06
├── Filter(Build)
│ ├── output columns: [date_dim.d_date_sk (#339)]
│ ├── filters: [is_true(date_dim.d_date (#341) >= '2000-08-23'), is_true(date_dim.d_date (#341) <= '2000-09-06')]
│ ├── estimated rows: 30.19
│ └── TableScan
│ ├── table: default.checksb_db.date_dim
│ ├── output columns: [d_date_sk (#339), d_date (#341)]
│ ├── read rows: 29728
│ ├── read size: 145.71 KiB
│ ├── partitions total: 3
│ ├── partitions scanned: 1
│ ├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 3 to 1>]
│ ├── push downs: [filters: [and_filters(date_dim.d_date (#341) >= '2000-08-23', date_dim.d_date (#341) <= '2000-09-06')], limit: NONE]
│ └── estimated rows: 73049.00
└── UnionAll(Probe)
├── output columns: [wsr_web_site_sk (#333), date_sk (#334), sales_price (#335), profit (#336), return_amt (#337), net_loss (#338)]
├── estimated rows: 15824969.33
├── EvalScalar
│ ├── output columns: [web_sales.ws_sold_date_sk (#237), web_sales.ws_web_site_sk (#250), web_sales.ws_ext_sales_price (#260), web_sales.ws_net_profit (#270), return_amt (#271), net_loss (#272)]
│ ├── expressions: [0.00, 0.00]
│ ├── estimated rows: 7197566.00
│ └── TableScan
│ ├── table: default.checksb_db.web_sales
│ ├── output columns: [ws_sold_date_sk (#237), ws_web_site_sk (#250), ws_ext_sales_price (#260), ws_net_profit (#270)]
│ ├── read rows: 7197566
│ ├── read size: 49.55 MiB
│ ├── partitions total: 16
│ ├── partitions scanned: 16
│ ├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 16 to 16>]
│ ├── push downs: [filters: [], limit: NONE]
│ └── estimated rows: 7197566.00
└── EvalScalar
├── output columns: [web_sales.ws_web_site_sk (#310), web_returns.wr_returned_date_sk (#273), web_returns.wr_return_amt (#288), web_returns.wr_net_loss (#296), sales_price (#331), profit (#332)]
├── expressions: [0.00, 0.00]
├── estimated rows: 8627403.33
└── HashJoin
├── output columns: [web_sales.ws_web_site_sk (#310), web_returns.wr_returned_date_sk (#273), web_returns.wr_return_amt (#288), web_returns.wr_net_loss (#296)]
├── join type: RIGHT OUTER
├── build keys: [web_returns.wr_item_sk (#275), web_returns.wr_order_number (#286)]
├── probe keys: [web_sales.ws_item_sk (#300), web_sales.ws_order_number (#314)]
├── keys is null equal: [false, false]
├── filters: []
├── build join filters:
│ ├── filter id:0, build key:web_returns.wr_item_sk (#275), probe key:web_sales.ws_item_sk (#300), filter type:inlist,min_max
│ └── filter id:1, build key:web_returns.wr_order_number (#286), probe key:web_sales.ws_order_number (#314), filter type:inlist,min_max
├── estimated rows: 8627403.33
├── TableScan(Build)
│ ├── table: default.checksb_db.web_returns
│ ├── output columns: [wr_returned_date_sk (#273), wr_item_sk (#275), wr_order_number (#286), wr_return_amt (#288), wr_net_loss (#296)]
│ ├── read rows: 719217
│ ├── read size: 7.91 MiB
│ ├── partitions total: 3
│ ├── partitions scanned: 3
│ ├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 3 to 3>]
│ ├── push downs: [filters: [], limit: NONE]
│ └── estimated rows: 719217.00
└── TableScan(Probe)
├── table: default.checksb_db.web_sales
├── output columns: [ws_item_sk (#300), ws_web_site_sk (#310), ws_order_number (#314)]
├── read rows: 7197566
├── read size: 25.86 MiB
├── partitions total: 16
├── partitions scanned: 16
├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 16 to 16>]
├── push downs: [filters: [], limit: NONE]
├── apply join filters: [#0, #1]
└── estimated rows: 7197566.00