== Physical Plan ==
VeloxColumnarToRow (70)
+- TakeOrderedAndProjectExecTransformer (69)
   +- ^ ProjectExecTransformer (67)
      +- ^ ShuffledHashJoinExecTransformer Inner BuildLeft (66)
         :- ^ InputIteratorTransformer (23)
         :  +- ColumnarExchange (21)
         :     +- VeloxResizeBatches (20)
         :        +- ^ ProjectExecTransformer (18)
         :           +- ^ ShuffledHashJoinExecTransformer Inner BuildRight (17)
         :              :- ^ InputIteratorTransformer (8)
         :              :  +- ColumnarExchange (6)
         :              :     +- VeloxResizeBatches (5)
         :              :        +- ^ ProjectExecTransformer (3)
         :              :           +- ^ FilterExecTransformer (2)
         :              :              +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.customer (1)
         :              +- ^ InputIteratorTransformer (16)
         :                 +- ColumnarExchange (14)
         :                    +- VeloxResizeBatches (13)
         :                       +- ^ ProjectExecTransformer (11)
         :                          +- ^ FilterExecTransformer (10)
         :                             +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.customer_address (9)
         +- ^ InputIteratorTransformer (65)
            +- ColumnarExchange (63)
               +- VeloxResizeBatches (62)
                  +- ^ ProjectExecTransformer (60)
                     +- ^ RegularHashAggregateExecTransformer (59)
                        +- ^ ProjectExecTransformer (58)
                           +- ^ ShuffledHashJoinExecTransformer Inner BuildRight (57)
                              :- ^ InputIteratorTransformer (53)
                              :  +- ColumnarExchange (51)
                              :     +- VeloxResizeBatches (50)
                              :        +- ^ ProjectExecTransformer (48)
                              :           +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (47)
                              :              :- ^ ProjectExecTransformer (39)
                              :              :  +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (38)
                              :              :     :- ^ ProjectExecTransformer (30)
                              :              :     :  +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (29)
                              :              :     :     :- ^ FilterExecTransformer (25)
                              :              :     :     :  +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store_sales (24)
                              :              :     :     +- ^ InputIteratorTransformer (28)
                              :              :     :        +- ReusedExchange (26)
                              :              :     +- ^ InputIteratorTransformer (37)
                              :              :        +- ColumnarBroadcastExchange (35)
                              :              :           +- ^ ProjectExecTransformer (33)
                              :              :              +- ^ FilterExecTransformer (32)
                              :              :                 +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store (31)
                              :              +- ^ InputIteratorTransformer (46)
                              :                 +- ColumnarBroadcastExchange (44)
                              :                    +- ^ ProjectExecTransformer (42)
                              :                       +- ^ FilterExecTransformer (41)
                              :                          +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.household_demographics (40)
                              +- ^ InputIteratorTransformer (56)
                                 +- ReusedExchange (54)


(1) FileSourceScanExecTransformer parquet spark_catalog.default.customer
Output [4]: [c_customer_sk#1, c_current_addr_sk#2, c_first_name#3, c_last_name#4]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/customer]
PushedFilters: [IsNotNull(c_customer_sk), IsNotNull(c_current_addr_sk)]
ReadSchema: struct<c_customer_sk:int,c_current_addr_sk:int,c_first_name:string,c_last_name:string>

(2) FilterExecTransformer
Input [4]: [c_customer_sk#1, c_current_addr_sk#2, c_first_name#3, c_last_name#4]
Arguments: (isnotnull(c_customer_sk#1) AND isnotnull(c_current_addr_sk#2))

(3) ProjectExecTransformer
Output [5]: [hash(c_current_addr_sk#2, 42) AS hash_partition_key#5, c_customer_sk#1, c_current_addr_sk#2, c_first_name#3, c_last_name#4]
Input [4]: [c_customer_sk#1, c_current_addr_sk#2, c_first_name#3, c_last_name#4]

(4) WholeStageCodegenTransformer (1)
Input [5]: [hash_partition_key#5, c_customer_sk#1, c_current_addr_sk#2, c_first_name#3, c_last_name#4]
Arguments: false

(5) VeloxResizeBatches
Input [5]: [hash_partition_key#5, c_customer_sk#1, c_current_addr_sk#2, c_first_name#3, c_last_name#4]
Arguments: 1024, 2147483647, 10485760

(6) ColumnarExchange
Input [5]: [hash_partition_key#5, c_customer_sk#1, c_current_addr_sk#2, c_first_name#3, c_last_name#4]
Arguments: hashpartitioning(c_current_addr_sk#2, 1), ENSURE_REQUIREMENTS, [c_customer_sk#1, c_current_addr_sk#2, c_first_name#3, c_last_name#4], [plan_id=1], [shuffle_writer_type=hash]

(7) InputAdapter
Input [4]: [c_customer_sk#1, c_current_addr_sk#2, c_first_name#3, c_last_name#4]

(8) InputIteratorTransformer
Input [4]: [c_customer_sk#1, c_current_addr_sk#2, c_first_name#3, c_last_name#4]

(9) FileSourceScanExecTransformer parquet spark_catalog.default.customer_address
Output [2]: [ca_address_sk#6, ca_city#7]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/customer_address]
PushedFilters: [IsNotNull(ca_address_sk), IsNotNull(ca_city)]
ReadSchema: struct<ca_address_sk:int,ca_city:string>

(10) FilterExecTransformer
Input [2]: [ca_address_sk#6, ca_city#7]
Arguments: (isnotnull(ca_address_sk#6) AND isnotnull(ca_city#7))

(11) ProjectExecTransformer
Output [3]: [hash(ca_address_sk#6, 42) AS hash_partition_key#8, ca_address_sk#6, ca_city#7]
Input [2]: [ca_address_sk#6, ca_city#7]

(12) WholeStageCodegenTransformer (2)
Input [3]: [hash_partition_key#8, ca_address_sk#6, ca_city#7]
Arguments: false

(13) VeloxResizeBatches
Input [3]: [hash_partition_key#8, ca_address_sk#6, ca_city#7]
Arguments: 1024, 2147483647, 10485760

(14) ColumnarExchange
Input [3]: [hash_partition_key#8, ca_address_sk#6, ca_city#7]
Arguments: hashpartitioning(ca_address_sk#6, 1), ENSURE_REQUIREMENTS, [ca_address_sk#6, ca_city#7], [plan_id=2], [shuffle_writer_type=hash]

(15) InputAdapter
Input [2]: [ca_address_sk#6, ca_city#7]

(16) InputIteratorTransformer
Input [2]: [ca_address_sk#6, ca_city#7]

(17) ShuffledHashJoinExecTransformer
Left keys [1]: [c_current_addr_sk#2]
Right keys [1]: [ca_address_sk#6]
Join type: Inner
Join condition: None

(18) ProjectExecTransformer
Output [5]: [hash(c_customer_sk#1, 42) AS hash_partition_key#9, c_customer_sk#1, c_first_name#3, c_last_name#4, ca_city#7]
Input [6]: [c_customer_sk#1, c_current_addr_sk#2, c_first_name#3, c_last_name#4, ca_address_sk#6, ca_city#7]

(19) WholeStageCodegenTransformer (3)
Input [5]: [hash_partition_key#9, c_customer_sk#1, c_first_name#3, c_last_name#4, ca_city#7]
Arguments: false

(20) VeloxResizeBatches
Input [5]: [hash_partition_key#9, c_customer_sk#1, c_first_name#3, c_last_name#4, ca_city#7]
Arguments: 1024, 2147483647, 10485760

(21) ColumnarExchange
Input [5]: [hash_partition_key#9, c_customer_sk#1, c_first_name#3, c_last_name#4, ca_city#7]
Arguments: hashpartitioning(c_customer_sk#1, 1), ENSURE_REQUIREMENTS, [c_customer_sk#1, c_first_name#3, c_last_name#4, ca_city#7], [plan_id=3], [shuffle_writer_type=hash]

(22) InputAdapter
Input [4]: [c_customer_sk#1, c_first_name#3, c_last_name#4, ca_city#7]

(23) InputIteratorTransformer
Input [4]: [c_customer_sk#1, c_first_name#3, c_last_name#4, ca_city#7]

(24) FileSourceScanExecTransformer parquet spark_catalog.default.store_sales
Output [8]: [ss_customer_sk#10, ss_hdemo_sk#11, ss_addr_sk#12, ss_store_sk#13, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16, ss_sold_date_sk#17]
Batched: true
Location: InMemoryFileIndex []
PartitionFilters: [isnotnull(ss_sold_date_sk#17), dynamicpruningexpression(ss_sold_date_sk#17 IN dynamicpruning#18)]
PushedFilters: [IsNotNull(ss_store_sk), IsNotNull(ss_hdemo_sk), IsNotNull(ss_addr_sk), IsNotNull(ss_customer_sk)]
ReadSchema: struct<ss_customer_sk:int,ss_hdemo_sk:int,ss_addr_sk:int,ss_store_sk:int,ss_ticket_number:int,ss_coupon_amt:decimal(7,2),ss_net_profit:decimal(7,2)>

(25) FilterExecTransformer
Input [8]: [ss_customer_sk#10, ss_hdemo_sk#11, ss_addr_sk#12, ss_store_sk#13, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16, ss_sold_date_sk#17]
Arguments: (((isnotnull(ss_store_sk#13) AND isnotnull(ss_hdemo_sk#11)) AND isnotnull(ss_addr_sk#12)) AND isnotnull(ss_customer_sk#10))

(26) ReusedExchange [Reuses operator id: 75]
Output [1]: [d_date_sk#19]

(27) InputAdapter
Input [1]: [d_date_sk#19]

(28) InputIteratorTransformer
Input [1]: [d_date_sk#19]

(29) BroadcastHashJoinExecTransformer
Left keys [1]: [ss_sold_date_sk#17]
Right keys [1]: [d_date_sk#19]
Join type: Inner
Join condition: None

(30) ProjectExecTransformer
Output [7]: [ss_customer_sk#10, ss_hdemo_sk#11, ss_addr_sk#12, ss_store_sk#13, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16]
Input [9]: [ss_customer_sk#10, ss_hdemo_sk#11, ss_addr_sk#12, ss_store_sk#13, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16, ss_sold_date_sk#17, d_date_sk#19]

(31) FileSourceScanExecTransformer parquet spark_catalog.default.store
Output [2]: [s_store_sk#20, s_city#21]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/store]
PushedFilters: [In(s_city, [Fairview,Midway]), IsNotNull(s_store_sk)]
ReadSchema: struct<s_store_sk:int,s_city:string>

(32) FilterExecTransformer
Input [2]: [s_store_sk#20, s_city#21]
Arguments: (s_city#21 IN (Fairview,Midway) AND isnotnull(s_store_sk#20))

(33) ProjectExecTransformer
Output [1]: [s_store_sk#20]
Input [2]: [s_store_sk#20, s_city#21]

(34) WholeStageCodegenTransformer (6)
Input [1]: [s_store_sk#20]
Arguments: false

(35) ColumnarBroadcastExchange
Input [1]: [s_store_sk#20]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)),false), [plan_id=4]

(36) InputAdapter
Input [1]: [s_store_sk#20]

(37) InputIteratorTransformer
Input [1]: [s_store_sk#20]

(38) BroadcastHashJoinExecTransformer
Left keys [1]: [ss_store_sk#13]
Right keys [1]: [s_store_sk#20]
Join type: Inner
Join condition: None

(39) ProjectExecTransformer
Output [6]: [ss_customer_sk#10, ss_hdemo_sk#11, ss_addr_sk#12, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16]
Input [8]: [ss_customer_sk#10, ss_hdemo_sk#11, ss_addr_sk#12, ss_store_sk#13, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16, s_store_sk#20]

(40) FileSourceScanExecTransformer parquet spark_catalog.default.household_demographics
Output [3]: [hd_demo_sk#22, hd_dep_count#23, hd_vehicle_count#24]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/household_demographics]
PushedFilters: [Or(EqualTo(hd_dep_count,4),EqualTo(hd_vehicle_count,3)), IsNotNull(hd_demo_sk)]
ReadSchema: struct<hd_demo_sk:int,hd_dep_count:int,hd_vehicle_count:int>

(41) FilterExecTransformer
Input [3]: [hd_demo_sk#22, hd_dep_count#23, hd_vehicle_count#24]
Arguments: (((hd_dep_count#23 = 4) OR (hd_vehicle_count#24 = 3)) AND isnotnull(hd_demo_sk#22))

(42) ProjectExecTransformer
Output [1]: [hd_demo_sk#22]
Input [3]: [hd_demo_sk#22, hd_dep_count#23, hd_vehicle_count#24]

(43) WholeStageCodegenTransformer (7)
Input [1]: [hd_demo_sk#22]
Arguments: false

(44) ColumnarBroadcastExchange
Input [1]: [hd_demo_sk#22]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)),false), [plan_id=5]

(45) InputAdapter
Input [1]: [hd_demo_sk#22]

(46) InputIteratorTransformer
Input [1]: [hd_demo_sk#22]

(47) BroadcastHashJoinExecTransformer
Left keys [1]: [ss_hdemo_sk#11]
Right keys [1]: [hd_demo_sk#22]
Join type: Inner
Join condition: None

(48) ProjectExecTransformer
Output [6]: [hash(ss_addr_sk#12, 42) AS hash_partition_key#25, ss_customer_sk#10, ss_addr_sk#12, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16]
Input [7]: [ss_customer_sk#10, ss_hdemo_sk#11, ss_addr_sk#12, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16, hd_demo_sk#22]

(49) WholeStageCodegenTransformer (8)
Input [6]: [hash_partition_key#25, ss_customer_sk#10, ss_addr_sk#12, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16]
Arguments: false

(50) VeloxResizeBatches
Input [6]: [hash_partition_key#25, ss_customer_sk#10, ss_addr_sk#12, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16]
Arguments: 1024, 2147483647, 10485760

(51) ColumnarExchange
Input [6]: [hash_partition_key#25, ss_customer_sk#10, ss_addr_sk#12, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16]
Arguments: hashpartitioning(ss_addr_sk#12, 1), ENSURE_REQUIREMENTS, [ss_customer_sk#10, ss_addr_sk#12, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16], [plan_id=6], [shuffle_writer_type=hash]

(52) InputAdapter
Input [5]: [ss_customer_sk#10, ss_addr_sk#12, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16]

(53) InputIteratorTransformer
Input [5]: [ss_customer_sk#10, ss_addr_sk#12, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16]

(54) ReusedExchange [Reuses operator id: 14]
Output [2]: [ca_address_sk#26, ca_city#27]

(55) InputAdapter
Input [2]: [ca_address_sk#26, ca_city#27]

(56) InputIteratorTransformer
Input [2]: [ca_address_sk#26, ca_city#27]

(57) ShuffledHashJoinExecTransformer
Left keys [1]: [ss_addr_sk#12]
Right keys [1]: [ca_address_sk#26]
Join type: Inner
Join condition: None

(58) ProjectExecTransformer
Output [6]: [ss_customer_sk#10, ss_addr_sk#12, ss_ticket_number#14, ca_city#27, UnscaledValue(ss_coupon_amt#15) AS _pre_1#28, UnscaledValue(ss_net_profit#16) AS _pre_2#29]
Input [7]: [ss_customer_sk#10, ss_addr_sk#12, ss_ticket_number#14, ss_coupon_amt#15, ss_net_profit#16, ca_address_sk#26, ca_city#27]

(59) RegularHashAggregateExecTransformer
Input [6]: [ss_customer_sk#10, ss_addr_sk#12, ss_ticket_number#14, ca_city#27, _pre_1#28, _pre_2#29]
Keys [4]: [ss_ticket_number#14, ss_customer_sk#10, ss_addr_sk#12, ca_city#27]
Functions [2]: [sum(_pre_1#28), sum(_pre_2#29)]
Aggregate Attributes [2]: [sum(UnscaledValue(ss_coupon_amt#15))#30, sum(UnscaledValue(ss_net_profit#16))#31]
Results [6]: [ss_ticket_number#14, ss_customer_sk#10, ss_addr_sk#12, ca_city#27, sum(UnscaledValue(ss_coupon_amt#15))#30, sum(UnscaledValue(ss_net_profit#16))#31]

(60) ProjectExecTransformer
Output [6]: [hash(ss_customer_sk#10, 42) AS hash_partition_key#32, ss_ticket_number#14, ss_customer_sk#10, ca_city#27 AS bought_city#33, MakeDecimal(sum(UnscaledValue(ss_coupon_amt#15))#30,17,2) AS amt#34, MakeDecimal(sum(UnscaledValue(ss_net_profit#16))#31,17,2) AS profit#35]
Input [6]: [ss_ticket_number#14, ss_customer_sk#10, ss_addr_sk#12, ca_city#27, sum(UnscaledValue(ss_coupon_amt#15))#30, sum(UnscaledValue(ss_net_profit#16))#31]

(61) WholeStageCodegenTransformer (10)
Input [6]: [hash_partition_key#32, ss_ticket_number#14, ss_customer_sk#10, bought_city#33, amt#34, profit#35]
Arguments: false

(62) VeloxResizeBatches
Input [6]: [hash_partition_key#32, ss_ticket_number#14, ss_customer_sk#10, bought_city#33, amt#34, profit#35]
Arguments: 1024, 2147483647, 10485760

(63) ColumnarExchange
Input [6]: [hash_partition_key#32, ss_ticket_number#14, ss_customer_sk#10, bought_city#33, amt#34, profit#35]
Arguments: hashpartitioning(ss_customer_sk#10, 1), ENSURE_REQUIREMENTS, [ss_ticket_number#14, ss_customer_sk#10, bought_city#33, amt#34, profit#35], [plan_id=7], [shuffle_writer_type=hash]

(64) InputAdapter
Input [5]: [ss_ticket_number#14, ss_customer_sk#10, bought_city#33, amt#34, profit#35]

(65) InputIteratorTransformer
Input [5]: [ss_ticket_number#14, ss_customer_sk#10, bought_city#33, amt#34, profit#35]

(66) ShuffledHashJoinExecTransformer
Left keys [1]: [c_customer_sk#1]
Right keys [1]: [ss_customer_sk#10]
Join type: Inner
Join condition: NOT (ca_city#7 = bought_city#33)

(67) ProjectExecTransformer
Output [7]: [c_last_name#4, c_first_name#3, ca_city#7, bought_city#33, ss_ticket_number#14, amt#34, profit#35]
Input [9]: [c_customer_sk#1, c_first_name#3, c_last_name#4, ca_city#7, ss_ticket_number#14, ss_customer_sk#10, bought_city#33, amt#34, profit#35]

(68) WholeStageCodegenTransformer (11)
Input [7]: [c_last_name#4, c_first_name#3, ca_city#7, bought_city#33, ss_ticket_number#14, amt#34, profit#35]
Arguments: false

(69) TakeOrderedAndProjectExecTransformer
Input [7]: [c_last_name#4, c_first_name#3, ca_city#7, bought_city#33, ss_ticket_number#14, amt#34, profit#35]
Arguments: 100, [c_last_name#4 ASC NULLS FIRST, c_first_name#3 ASC NULLS FIRST, ca_city#7 ASC NULLS FIRST, bought_city#33 ASC NULLS FIRST, ss_ticket_number#14 ASC NULLS FIRST], [c_last_name#4, c_first_name#3, ca_city#7, bought_city#33, ss_ticket_number#14, amt#34, profit#35], 0

(70) VeloxColumnarToRow
Input [7]: [c_last_name#4, c_first_name#3, ca_city#7, bought_city#33, ss_ticket_number#14, amt#34, profit#35]

===== Subqueries =====

Subquery:1 Hosting operator id = 24 Hosting Expression = ss_sold_date_sk#17 IN dynamicpruning#18
ColumnarBroadcastExchange (75)
+- ^ ProjectExecTransformer (73)
   +- ^ FilterExecTransformer (72)
      +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.date_dim (71)


(71) FileSourceScanExecTransformer parquet spark_catalog.default.date_dim
Output [3]: [d_date_sk#19, d_year#36, d_dow#37]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/date_dim]
PushedFilters: [In(d_dow, [0,6]), In(d_year, [1999,2000,2001]), IsNotNull(d_date_sk)]
ReadSchema: struct<d_date_sk:int,d_year:int,d_dow:int>

(72) FilterExecTransformer
Input [3]: [d_date_sk#19, d_year#36, d_dow#37]
Arguments: ((d_dow#37 IN (6,0) AND d_year#36 IN (1999,2000,2001)) AND isnotnull(d_date_sk#19))

(73) ProjectExecTransformer
Output [1]: [d_date_sk#19]
Input [3]: [d_date_sk#19, d_year#36, d_dow#37]

(74) WholeStageCodegenTransformer (4)
Input [1]: [d_date_sk#19]
Arguments: false

(75) ColumnarBroadcastExchange
Input [1]: [d_date_sk#19]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)),false), [plan_id=8]


