xiong duan created CALCITE-6935:
-----------------------------------
Summary: JdbcJoinRule should support SEARCH in JdbcJoin condition
Key: CALCITE-6935
URL: https://issues.apache.org/jira/browse/CALCITE-6935
Project: Calcite
Issue Type: Bug
Reporter: xiong duan
Assignee: xiong duan
JdbcJoinRule doesn't support SEARCH in JdbcJoin's condition, which makes
Tpcds-Q13 unable to push down.
SQL:
{code:java}
--query 13
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 = 'D'
and cd_education_status = '2 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 = 'Secondary'
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 = 'W'
and cd_education_status = 'Advanced 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', 'IL', '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 ('OH', 'MT', 'NM')
and ss_net_profit between 150 and 300
) or
(ss_addr_sk = ca_address_sk
and ca_country = 'United States'
and ca_state in ('TX', 'MO', 'MI')
and ss_net_profit between 50 and 250
)) {code}
Plan:
{code:java}
EnumerableProject(ss_sold_date_sk=[$0], ss_sold_time_sk=[$1], ss_item_sk=[$2],
ss_customer_sk=[$3], ss_cdemo_sk=[$4], ss_hdemo_sk=[$5], ss_addr_sk=[$6],
ss_store_sk=[$7], ss_promo_sk=[$8], ss_ticket_number=[$9], ss_quantity=[$10],
ss_wholesale_cost=[$11], ss_list_price=[$12], ss_sales_price=[$13],
ss_ext_discount_amt=[$14], ss_ext_sales_price=[$15],
ss_ext_wholesale_cost=[$16], ss_ext_list_price=[$17], ss_ext_tax=[$18],
ss_coupon_amt=[$19], ss_net_paid=[$20], ss_net_paid_inc_tax=[$21],
ss_net_profit=[$22], s_store_sk=[$23], s_store_id=[$24],
s_rec_start_date=[$25], s_rec_end_date=[$26], s_closed_date_sk=[$27],
s_store_name=[$28], s_number_employees=[$29], s_floor_space=[$30],
s_hours=[$31], s_manager=[$32], s_market_id=[$33], s_geography_class=[$34],
s_market_desc=[$35], s_market_manager=[$36], s_division_id=[$37],
s_division_name=[$38], s_company_id=[$39], s_company_name=[$40],
s_street_number=[$41], s_street_name=[$42], s_street_type=[$43],
s_suite_number=[$44], s_city=[$45], s_county=[$46], s_state=[$47], s_zip=[$48],
s_country=[$49], s_gmt_offset=[$50], s_tax_precentage=[$51], cd_demo_sk=[$52],
cd_gender=[$53], cd_marital_status=[$54], cd_education_status=[$55],
cd_purchase_estimate=[$56], cd_credit_rating=[$57], cd_dep_count=[$58],
cd_dep_employed_count=[$59], cd_dep_college_count=[$60], hd_demo_sk=[$74],
hd_income_band_sk=[$75], hd_buy_potential=[$76], hd_dep_count=[$77],
hd_vehicle_count=[$78], ca_address_sk=[$61], ca_address_id=[$62],
ca_street_number=[$63], ca_street_name=[$64], ca_street_type=[$65],
ca_suite_number=[$66], ca_city=[$67], ca_county=[$68], ca_state=[$69],
ca_zip=[$70], ca_country=[$71], ca_gmt_offset=[$72], ca_location_type=[$73])
EnumerableNestedLoopJoin(condition=[OR(AND(=($5, $74), =($52, $4), =($54,
'D'), =($55, '2 yr Degree '), SEARCH($13, Sarg[[100.00:DECIMAL(5,
2)..150.00:DECIMAL(5, 2)]]:DECIMAL(5, 2)), =($77, 3)), AND(=($5, $74), =($52,
$4), =($54, 'S'), =($55, 'Secondary '), SEARCH($13,
Sarg[[50.00:DECIMAL(5, 2)..100.00:DECIMAL(5, 2)]]:DECIMAL(5, 2)), =($77, 1)),
AND(=($5, $74), =($52, $4), =($54, 'W'), =($55, 'Advanced Degree '),
SEARCH($13, Sarg[[150.00:DECIMAL(5, 2)..200.00:DECIMAL(5, 2)]]:DECIMAL(5, 2)),
=($77, 1)))], joinType=[inner])
JdbcToEnumerableConverter
JdbcProject(ss_sold_date_sk=[$0], ss_sold_time_sk=[$1], ss_item_sk=[$2],
ss_customer_sk=[$3], ss_cdemo_sk=[$4], ss_hdemo_sk=[$5], ss_addr_sk=[$6],
ss_store_sk=[$7], ss_promo_sk=[$8], ss_ticket_number=[$9], ss_quantity=[$10],
ss_wholesale_cost=[$11], ss_list_price=[$12], ss_sales_price=[$13],
ss_ext_discount_amt=[$14], ss_ext_sales_price=[$15],
ss_ext_wholesale_cost=[$16], ss_ext_list_price=[$17], ss_ext_tax=[$18],
ss_coupon_amt=[$19], ss_net_paid=[$20], ss_net_paid_inc_tax=[$21],
ss_net_profit=[$22], s_store_sk=[$23], s_store_id=[$24],
s_rec_start_date=[$25], s_rec_end_date=[$26], s_closed_date_sk=[$27],
s_store_name=[$28], s_number_employees=[$29], s_floor_space=[$30],
s_hours=[$31], s_manager=[$32], s_market_id=[$33], s_geography_class=[$34],
s_market_desc=[$35], s_market_manager=[$36], s_division_id=[$37],
s_division_name=[$38], s_company_id=[$39], s_company_name=[$40],
s_street_number=[$41], s_street_name=[$42], s_street_type=[$43],
s_suite_number=[$44], s_city=[$45], s_county=[$46], s_state=[$47], s_zip=[$48],
s_country=[$49], s_gmt_offset=[$50], s_tax_precentage=[$51], cd_demo_sk=[$65],
cd_gender=[$66], cd_marital_status=[$67], cd_education_status=[$68],
cd_purchase_estimate=[$69], cd_credit_rating=[$70], cd_dep_count=[$71],
cd_dep_employed_count=[$72], cd_dep_college_count=[$73], ca_address_sk=[$52],
ca_address_id=[$53], ca_street_number=[$54], ca_street_name=[$55],
ca_street_type=[$56], ca_suite_number=[$57], ca_city=[$58], ca_county=[$59],
ca_state=[$60], ca_zip=[$61], ca_country=[$62], ca_gmt_offset=[$63],
ca_location_type=[$64])
JdbcJoin(condition=[true], joinType=[inner])
JdbcProject(ss_sold_date_sk=[$0], ss_sold_time_sk=[$1],
ss_item_sk=[$2], ss_customer_sk=[$3], ss_cdemo_sk=[$4], ss_hdemo_sk=[$5],
ss_addr_sk=[$6], ss_store_sk=[$7], ss_promo_sk=[$8], ss_ticket_number=[$9],
ss_quantity=[$10], ss_wholesale_cost=[$11], ss_list_price=[$12],
ss_sales_price=[$13], ss_ext_discount_amt=[$14], ss_ext_sales_price=[$15],
ss_ext_wholesale_cost=[$16], ss_ext_list_price=[$17], ss_ext_tax=[$18],
ss_coupon_amt=[$19], ss_net_paid=[$20], ss_net_paid_inc_tax=[$21],
ss_net_profit=[$22], s_store_sk=[$36], s_store_id=[$37],
s_rec_start_date=[$38], s_rec_end_date=[$39], s_closed_date_sk=[$40],
s_store_name=[$41], s_number_employees=[$42], s_floor_space=[$43],
s_hours=[$44], s_manager=[$45], s_market_id=[$46], s_geography_class=[$47],
s_market_desc=[$48], s_market_manager=[$49], s_division_id=[$50],
s_division_name=[$51], s_company_id=[$52], s_company_name=[$53],
s_street_number=[$54], s_street_name=[$55], s_street_type=[$56],
s_suite_number=[$57], s_city=[$58], s_county=[$59], s_state=[$60], s_zip=[$61],
s_country=[$62], s_gmt_offset=[$63], s_tax_precentage=[$64],
ca_address_sk=[$23], ca_address_id=[$24], ca_street_number=[$25],
ca_street_name=[$26], ca_street_type=[$27], ca_suite_number=[$28],
ca_city=[$29], ca_county=[$30], ca_state=[$31], ca_zip=[$32], ca_country=[$33],
ca_gmt_offset=[$34], ca_location_type=[$35])
JdbcJoin(condition=[=($36, $7)], joinType=[inner])
JdbcJoin(condition=[=($6, $23)], joinType=[inner])
JdbcFilter(condition=[SEARCH(CAST($22):DECIMAL(12, 2),
Sarg[[100.00:DECIMAL(12, 2)..200.00:DECIMAL(12, 2)]]:DECIMAL(12, 2))])
JdbcTableScan(table=[[tpc_ds_data, store_sales]])
JdbcFilter(condition=[AND(=($10, 'United States'), SEARCH($8,
Sarg['CO', 'IL', 'MN']:CHAR(2)))])
JdbcTableScan(table=[[tpc_ds_data, customer_address]])
JdbcTableScan(table=[[tpc_ds_data, store]])
JdbcTableScan(table=[[tpc_ds_data, customer_demographics]])
JdbcToEnumerableConverter
JdbcTableScan(table=[[tpc_ds_data, household_demographics]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
