Uploaded image for project: 'Tajo'
  1. Tajo
  2. TAJO-1445

Optimizer removes some filter in where clause.

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: 0.11.0
    • Component/s: None
    • Labels:
      None

      Description

      select ca_zip
             ,sum(cs_sales_price)
       from catalog_sales
           ,customer
           ,customer_address
           ,date_dim
       where cs_bill_customer_sk = c_customer_sk
         and c_current_addr_sk = ca_address_sk 
         and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475',
                                         '85392', '85460', '80348', '81792')
               or ca_state in ('CA','WA','GA')
               or cs_sales_price > 500)
         and cs_sold_date_sk = d_date_sk
         and d_qoy = 2 and d_year = 2000
       group by ca_zip
       order by ca_zip
      limit 100;
      

      The above query is TPC-DS query15. The logical planner generates the plan as the following. Some filters are in the SELECTION(4) but after optimizing there are no filters in the plan. Optimizer may removes some filters.

      PROJECTION(8)
        => Targets: tpcds.customer_address.ca_zip (TEXT), ?sum (FLOAT8)
        => out schema: {(2) tpcds.customer_address.ca_zip (TEXT), ?sum (FLOAT8)}
        => in  schema: {(2) tpcds.customer_address.ca_zip (TEXT), ?sum (FLOAT8)}
         LIMIT(7) 100
            SORT(6)
              => Sort Keys: tpcds.customer_address.ca_zip (TEXT) (asc)
               GROUP_BY(5)(ca_zip)
                 => exprs: (sum(tpcds.catalog_sales.cs_sales_price (FLOAT4)))
                 => target list: tpcds.customer_address.ca_zip (TEXT), ?sum (FLOAT8)
                 => out schema:{(2) tpcds.customer_address.ca_zip (TEXT), ?sum (FLOAT8)}
                 => in schema:{(95) tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.catalog_sales.cs_sold_time_sk (INT4), tpcds.catalog_sales.cs_ship_date_sk (INT4), tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_bill_cdemo_sk (INT4), tpcds.catalog_sales.cs_bill_hdemo_sk (INT4), tpcds.catalog_sales.cs_bill_addr_sk (INT4), tpcds.catalog_sales.cs_ship_customer_sk (INT4), tpcds.catalog_sales.cs_ship_cdemo_sk (INT4), tpcds.catalog_sales.cs_ship_hdemo_sk (INT4), tpcds.catalog_sales.cs_ship_addr_sk (INT4), tpcds.catalog_sales.cs_call_center_sk (INT4), tpcds.catalog_sales.cs_catalog_page_sk (INT4), tpcds.catalog_sales.cs_ship_mode_sk (INT4), tpcds.catalog_sales.cs_warehouse_sk (INT4), tpcds.catalog_sales.cs_item_sk (INT4), tpcds.catalog_sales.cs_promo_sk (INT4), tpcds.catalog_sales.cs_order_number (INT4), tpcds.catalog_sales.cs_quantity (INT4), tpcds.catalog_sales.cs_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_list_price (FLOAT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT4), tpcds.catalog_sales.cs_ext_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_ext_list_price (FLOAT4), tpcds.catalog_sales.cs_ext_tax (FLOAT4), tpcds.catalog_sales.cs_coupon_amt (FLOAT4), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT4), tpcds.catalog_sales.cs_net_paid (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT4), tpcds.catalog_sales.cs_net_profit (FLOAT4), tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT4), tpcds.customer.c_current_hdemo_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer.c_first_shipto_date_sk (INT4), tpcds.customer.c_first_sales_date_sk (INT4), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT4), tpcds.customer.c_birth_month (INT4), tpcds.customer.c_birth_year (INT4), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date (TEXT), tpcds.customer_address.ca_zip (TEXT), ?and_1 (BOOLEAN), tpcds.customer_address.ca_address_sk (INT4), tpcds.customer_address.ca_address_id (TEXT), tpcds.customer_address.ca_street_number (TEXT), tpcds.customer_address.ca_street_name (TEXT), tpcds.customer_address.ca_street_type (TEXT), tpcds.customer_address.ca_suite_number (TEXT), tpcds.customer_address.ca_city (TEXT), tpcds.customer_address.ca_county (TEXT), tpcds.customer_address.ca_state (TEXT), ?fake0 (TEXT), tpcds.customer_address.ca_country (TEXT), tpcds.customer_address.ca_gmt_offset (FLOAT4), tpcds.customer_address.ca_location_type (TEXT), tpcds.date_dim.d_date_sk (INT4), tpcds.date_dim.d_date_id (TEXT), tpcds.date_dim.d_date (DATE), tpcds.date_dim.d_month_seq (INT4), tpcds.date_dim.d_week_seq (INT4), tpcds.date_dim.d_quarter_seq (INT4), tpcds.date_dim.d_year (INT4), tpcds.date_dim.d_dow (INT4), tpcds.date_dim.d_moy (INT4), tpcds.date_dim.d_dom (INT4), tpcds.date_dim.d_qoy (INT4), tpcds.date_dim.d_fy_year (INT4), tpcds.date_dim.d_fy_quarter_seq (INT4), tpcds.date_dim.d_fy_week_seq (INT4), tpcds.date_dim.d_day_name (TEXT), tpcds.date_dim.d_quarter_name (TEXT), tpcds.date_dim.d_holiday (TEXT), tpcds.date_dim.d_weekend (TEXT), tpcds.date_dim.d_following_holiday (TEXT), tpcds.date_dim.d_first_dom (INT4), tpcds.date_dim.d_last_dom (INT4), tpcds.date_dim.d_same_day_ly (INT4), tpcds.date_dim.d_same_day_lq (INT4), tpcds.date_dim.d_current_day (TEXT), tpcds.date_dim.d_current_week (TEXT), tpcds.date_dim.d_current_month (TEXT), tpcds.date_dim.d_current_quarter (TEXT), tpcds.date_dim.d_current_year (TEXT)}
                  SELECTION(4)
                    => Search Cond: tpcds.catalog_sales.cs_bill_customer_sk (INT4) = tpcds.customer.c_customer_sk (INT4) AND tpcds.customer.c_current_addr_sk (INT4) = tpcds.customer_address.ca_address_sk (INT4) AND substr(tpcds.customer_address.ca_zip (TEXT),1,5) IN (85669, 86197, 88274, 83405, 86475, 85392, 85460, 80348, 81792) OR tpcds.customer_address.ca_state (TEXT) IN (CA, WA, GA) OR tpcds.catalog_sales.cs_sales_price (FLOAT4) > 500.0 AND tpcds.catalog_sales.cs_sold_date_sk (INT4) = tpcds.date_dim.d_date_sk (INT4) AND tpcds.date_dim.d_qoy (INT4) = 2 AND tpcds.date_dim.d_year (INT4) = 2000
                     JOIN(11)(CROSS)
                       => target list: tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.catalog_sales.cs_sold_time_sk (INT4), tpcds.catalog_sales.cs_ship_date_sk (INT4), tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_bill_cdemo_sk (INT4), tpcds.catalog_sales.cs_bill_hdemo_sk (INT4), tpcds.catalog_sales.cs_bill_addr_sk (INT4), tpcds.catalog_sales.cs_ship_customer_sk (INT4), tpcds.catalog_sales.cs_ship_cdemo_sk (INT4), tpcds.catalog_sales.cs_ship_hdemo_sk (INT4), tpcds.catalog_sales.cs_ship_addr_sk (INT4), tpcds.catalog_sales.cs_call_center_sk (INT4), tpcds.catalog_sales.cs_catalog_page_sk (INT4), tpcds.catalog_sales.cs_ship_mode_sk (INT4), tpcds.catalog_sales.cs_warehouse_sk (INT4), tpcds.catalog_sales.cs_item_sk (INT4), tpcds.catalog_sales.cs_promo_sk (INT4), tpcds.catalog_sales.cs_order_number (INT4), tpcds.catalog_sales.cs_quantity (INT4), tpcds.catalog_sales.cs_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_list_price (FLOAT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT4), tpcds.catalog_sales.cs_ext_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_ext_list_price (FLOAT4), tpcds.catalog_sales.cs_ext_tax (FLOAT4), tpcds.catalog_sales.cs_coupon_amt (FLOAT4), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT4), tpcds.catalog_sales.cs_net_paid (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT4), tpcds.catalog_sales.cs_net_profit (FLOAT4), tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT4), tpcds.customer.c_current_hdemo_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer.c_first_shipto_date_sk (INT4), tpcds.customer.c_first_sales_date_sk (INT4), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT4), tpcds.customer.c_birth_month (INT4), tpcds.customer.c_birth_year (INT4), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date (TEXT), tpcds.customer_address.ca_zip (TEXT), ?and_1 (BOOLEAN), tpcds.customer_address.ca_address_sk (INT4), tpcds.customer_address.ca_address_id (TEXT), tpcds.customer_address.ca_street_number (TEXT), tpcds.customer_address.ca_street_name (TEXT), tpcds.customer_address.ca_street_type (TEXT), tpcds.customer_address.ca_suite_number (TEXT), tpcds.customer_address.ca_city (TEXT), tpcds.customer_address.ca_county (TEXT), tpcds.customer_address.ca_state (TEXT), ?fake0 (TEXT), tpcds.customer_address.ca_country (TEXT), tpcds.customer_address.ca_gmt_offset (FLOAT4), tpcds.customer_address.ca_location_type (TEXT), tpcds.date_dim.d_date_sk (INT4), tpcds.date_dim.d_date_id (TEXT), tpcds.date_dim.d_date (DATE), tpcds.date_dim.d_month_seq (INT4), tpcds.date_dim.d_week_seq (INT4), tpcds.date_dim.d_quarter_seq (INT4), tpcds.date_dim.d_year (INT4), tpcds.date_dim.d_dow (INT4), tpcds.date_dim.d_moy (INT4), tpcds.date_dim.d_dom (INT4), tpcds.date_dim.d_qoy (INT4), tpcds.date_dim.d_fy_year (INT4), tpcds.date_dim.d_fy_quarter_seq (INT4), tpcds.date_dim.d_fy_week_seq (INT4), tpcds.date_dim.d_day_name (TEXT), tpcds.date_dim.d_quarter_name (TEXT), tpcds.date_dim.d_holiday (TEXT), tpcds.date_dim.d_weekend (TEXT), tpcds.date_dim.d_following_holiday (TEXT), tpcds.date_dim.d_first_dom (INT4), tpcds.date_dim.d_last_dom (INT4), tpcds.date_dim.d_same_day_ly (INT4), tpcds.date_dim.d_same_day_lq (INT4), tpcds.date_dim.d_current_day (TEXT), tpcds.date_dim.d_current_week (TEXT), tpcds.date_dim.d_current_month (TEXT), tpcds.date_dim.d_current_quarter (TEXT), tpcds.date_dim.d_current_year (TEXT)
                       => out schema: {(95) tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.catalog_sales.cs_sold_time_sk (INT4), tpcds.catalog_sales.cs_ship_date_sk (INT4), tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_bill_cdemo_sk (INT4), tpcds.catalog_sales.cs_bill_hdemo_sk (INT4), tpcds.catalog_sales.cs_bill_addr_sk (INT4), tpcds.catalog_sales.cs_ship_customer_sk (INT4), tpcds.catalog_sales.cs_ship_cdemo_sk (INT4), tpcds.catalog_sales.cs_ship_hdemo_sk (INT4), tpcds.catalog_sales.cs_ship_addr_sk (INT4), tpcds.catalog_sales.cs_call_center_sk (INT4), tpcds.catalog_sales.cs_catalog_page_sk (INT4), tpcds.catalog_sales.cs_ship_mode_sk (INT4), tpcds.catalog_sales.cs_warehouse_sk (INT4), tpcds.catalog_sales.cs_item_sk (INT4), tpcds.catalog_sales.cs_promo_sk (INT4), tpcds.catalog_sales.cs_order_number (INT4), tpcds.catalog_sales.cs_quantity (INT4), tpcds.catalog_sales.cs_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_list_price (FLOAT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT4), tpcds.catalog_sales.cs_ext_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_ext_list_price (FLOAT4), tpcds.catalog_sales.cs_ext_tax (FLOAT4), tpcds.catalog_sales.cs_coupon_amt (FLOAT4), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT4), tpcds.catalog_sales.cs_net_paid (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT4), tpcds.catalog_sales.cs_net_profit (FLOAT4), tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT4), tpcds.customer.c_current_hdemo_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer.c_first_shipto_date_sk (INT4), tpcds.customer.c_first_sales_date_sk (INT4), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT4), tpcds.customer.c_birth_month (INT4), tpcds.customer.c_birth_year (INT4), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date (TEXT), tpcds.customer_address.ca_zip (TEXT), ?and_1 (BOOLEAN), tpcds.customer_address.ca_address_sk (INT4), tpcds.customer_address.ca_address_id (TEXT), tpcds.customer_address.ca_street_number (TEXT), tpcds.customer_address.ca_street_name (TEXT), tpcds.customer_address.ca_street_type (TEXT), tpcds.customer_address.ca_suite_number (TEXT), tpcds.customer_address.ca_city (TEXT), tpcds.customer_address.ca_county (TEXT), tpcds.customer_address.ca_state (TEXT), ?fake0 (TEXT), tpcds.customer_address.ca_country (TEXT), tpcds.customer_address.ca_gmt_offset (FLOAT4), tpcds.customer_address.ca_location_type (TEXT), tpcds.date_dim.d_date_sk (INT4), tpcds.date_dim.d_date_id (TEXT), tpcds.date_dim.d_date (DATE), tpcds.date_dim.d_month_seq (INT4), tpcds.date_dim.d_week_seq (INT4), tpcds.date_dim.d_quarter_seq (INT4), tpcds.date_dim.d_year (INT4), tpcds.date_dim.d_dow (INT4), tpcds.date_dim.d_moy (INT4), tpcds.date_dim.d_dom (INT4), tpcds.date_dim.d_qoy (INT4), tpcds.date_dim.d_fy_year (INT4), tpcds.date_dim.d_fy_quarter_seq (INT4), tpcds.date_dim.d_fy_week_seq (INT4), tpcds.date_dim.d_day_name (TEXT), tpcds.date_dim.d_quarter_name (TEXT), tpcds.date_dim.d_holiday (TEXT), tpcds.date_dim.d_weekend (TEXT), tpcds.date_dim.d_following_holiday (TEXT), tpcds.date_dim.d_first_dom (INT4), tpcds.date_dim.d_last_dom (INT4), tpcds.date_dim.d_same_day_ly (INT4), tpcds.date_dim.d_same_day_lq (INT4), tpcds.date_dim.d_current_day (TEXT), tpcds.date_dim.d_current_week (TEXT), tpcds.date_dim.d_current_month (TEXT), tpcds.date_dim.d_current_quarter (TEXT), tpcds.date_dim.d_current_year (TEXT)}
                       => in schema: {(95) tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.catalog_sales.cs_sold_time_sk (INT4), tpcds.catalog_sales.cs_ship_date_sk (INT4), tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_bill_cdemo_sk (INT4), tpcds.catalog_sales.cs_bill_hdemo_sk (INT4), tpcds.catalog_sales.cs_bill_addr_sk (INT4), tpcds.catalog_sales.cs_ship_customer_sk (INT4), tpcds.catalog_sales.cs_ship_cdemo_sk (INT4), tpcds.catalog_sales.cs_ship_hdemo_sk (INT4), tpcds.catalog_sales.cs_ship_addr_sk (INT4), tpcds.catalog_sales.cs_call_center_sk (INT4), tpcds.catalog_sales.cs_catalog_page_sk (INT4), tpcds.catalog_sales.cs_ship_mode_sk (INT4), tpcds.catalog_sales.cs_warehouse_sk (INT4), tpcds.catalog_sales.cs_item_sk (INT4), tpcds.catalog_sales.cs_promo_sk (INT4), tpcds.catalog_sales.cs_order_number (INT4), tpcds.catalog_sales.cs_quantity (INT4), tpcds.catalog_sales.cs_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_list_price (FLOAT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT4), tpcds.catalog_sales.cs_ext_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_ext_list_price (FLOAT4), tpcds.catalog_sales.cs_ext_tax (FLOAT4), tpcds.catalog_sales.cs_coupon_amt (FLOAT4), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT4), tpcds.catalog_sales.cs_net_paid (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT4), tpcds.catalog_sales.cs_net_profit (FLOAT4), tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT4), tpcds.customer.c_current_hdemo_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer.c_first_shipto_date_sk (INT4), tpcds.customer.c_first_sales_date_sk (INT4), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT4), tpcds.customer.c_birth_month (INT4), tpcds.customer.c_birth_year (INT4), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date (TEXT), tpcds.customer_address.ca_zip (TEXT), ?and_1 (BOOLEAN), tpcds.customer_address.ca_address_sk (INT4), tpcds.customer_address.ca_address_id (TEXT), tpcds.customer_address.ca_street_number (TEXT), tpcds.customer_address.ca_street_name (TEXT), tpcds.customer_address.ca_street_type (TEXT), tpcds.customer_address.ca_suite_number (TEXT), tpcds.customer_address.ca_city (TEXT), tpcds.customer_address.ca_county (TEXT), tpcds.customer_address.ca_state (TEXT), ?fake0 (TEXT), tpcds.customer_address.ca_country (TEXT), tpcds.customer_address.ca_gmt_offset (FLOAT4), tpcds.customer_address.ca_location_type (TEXT), tpcds.date_dim.d_date_sk (INT4), tpcds.date_dim.d_date_id (TEXT), tpcds.date_dim.d_date (DATE), tpcds.date_dim.d_month_seq (INT4), tpcds.date_dim.d_week_seq (INT4), tpcds.date_dim.d_quarter_seq (INT4), tpcds.date_dim.d_year (INT4), tpcds.date_dim.d_dow (INT4), tpcds.date_dim.d_moy (INT4), tpcds.date_dim.d_dom (INT4), tpcds.date_dim.d_qoy (INT4), tpcds.date_dim.d_fy_year (INT4), tpcds.date_dim.d_fy_quarter_seq (INT4), tpcds.date_dim.d_fy_week_seq (INT4), tpcds.date_dim.d_day_name (TEXT), tpcds.date_dim.d_quarter_name (TEXT), tpcds.date_dim.d_holiday (TEXT), tpcds.date_dim.d_weekend (TEXT), tpcds.date_dim.d_following_holiday (TEXT), tpcds.date_dim.d_first_dom (INT4), tpcds.date_dim.d_last_dom (INT4), tpcds.date_dim.d_same_day_ly (INT4), tpcds.date_dim.d_same_day_lq (INT4), tpcds.date_dim.d_current_day (TEXT), tpcds.date_dim.d_current_week (TEXT), tpcds.date_dim.d_current_month (TEXT), tpcds.date_dim.d_current_quarter (TEXT), tpcds.date_dim.d_current_year (TEXT)}
                        SCAN(3) on tpcds.date_dim
                          => target list: tpcds.date_dim.d_date_sk (INT4), tpcds.date_dim.d_date_id (TEXT), tpcds.date_dim.d_date (DATE), tpcds.date_dim.d_month_seq (INT4), tpcds.date_dim.d_week_seq (INT4), tpcds.date_dim.d_quarter_seq (INT4), tpcds.date_dim.d_year (INT4), tpcds.date_dim.d_dow (INT4), tpcds.date_dim.d_moy (INT4), tpcds.date_dim.d_dom (INT4), tpcds.date_dim.d_qoy (INT4), tpcds.date_dim.d_fy_year (INT4), tpcds.date_dim.d_fy_quarter_seq (INT4), tpcds.date_dim.d_fy_week_seq (INT4), tpcds.date_dim.d_day_name (TEXT), tpcds.date_dim.d_quarter_name (TEXT), tpcds.date_dim.d_holiday (TEXT), tpcds.date_dim.d_weekend (TEXT), tpcds.date_dim.d_following_holiday (TEXT), tpcds.date_dim.d_first_dom (INT4), tpcds.date_dim.d_last_dom (INT4), tpcds.date_dim.d_same_day_ly (INT4), tpcds.date_dim.d_same_day_lq (INT4), tpcds.date_dim.d_current_day (TEXT), tpcds.date_dim.d_current_week (TEXT), tpcds.date_dim.d_current_month (TEXT), tpcds.date_dim.d_current_quarter (TEXT), tpcds.date_dim.d_current_year (TEXT)
                          => out schema: {(28) tpcds.date_dim.d_date_sk (INT4), tpcds.date_dim.d_date_id (TEXT), tpcds.date_dim.d_date (DATE), tpcds.date_dim.d_month_seq (INT4), tpcds.date_dim.d_week_seq (INT4), tpcds.date_dim.d_quarter_seq (INT4), tpcds.date_dim.d_year (INT4), tpcds.date_dim.d_dow (INT4), tpcds.date_dim.d_moy (INT4), tpcds.date_dim.d_dom (INT4), tpcds.date_dim.d_qoy (INT4), tpcds.date_dim.d_fy_year (INT4), tpcds.date_dim.d_fy_quarter_seq (INT4), tpcds.date_dim.d_fy_week_seq (INT4), tpcds.date_dim.d_day_name (TEXT), tpcds.date_dim.d_quarter_name (TEXT), tpcds.date_dim.d_holiday (TEXT), tpcds.date_dim.d_weekend (TEXT), tpcds.date_dim.d_following_holiday (TEXT), tpcds.date_dim.d_first_dom (INT4), tpcds.date_dim.d_last_dom (INT4), tpcds.date_dim.d_same_day_ly (INT4), tpcds.date_dim.d_same_day_lq (INT4), tpcds.date_dim.d_current_day (TEXT), tpcds.date_dim.d_current_week (TEXT), tpcds.date_dim.d_current_month (TEXT), tpcds.date_dim.d_current_quarter (TEXT), tpcds.date_dim.d_current_year (TEXT)}
                          => in schema: {(28) tpcds.date_dim.d_date_sk (INT4), tpcds.date_dim.d_date_id (TEXT), tpcds.date_dim.d_date (DATE), tpcds.date_dim.d_month_seq (INT4), tpcds.date_dim.d_week_seq (INT4), tpcds.date_dim.d_quarter_seq (INT4), tpcds.date_dim.d_year (INT4), tpcds.date_dim.d_dow (INT4), tpcds.date_dim.d_moy (INT4), tpcds.date_dim.d_dom (INT4), tpcds.date_dim.d_qoy (INT4), tpcds.date_dim.d_fy_year (INT4), tpcds.date_dim.d_fy_quarter_seq (INT4), tpcds.date_dim.d_fy_week_seq (INT4), tpcds.date_dim.d_day_name (TEXT), tpcds.date_dim.d_quarter_name (TEXT), tpcds.date_dim.d_holiday (TEXT), tpcds.date_dim.d_weekend (TEXT), tpcds.date_dim.d_following_holiday (TEXT), tpcds.date_dim.d_first_dom (INT4), tpcds.date_dim.d_last_dom (INT4), tpcds.date_dim.d_same_day_ly (INT4), tpcds.date_dim.d_same_day_lq (INT4), tpcds.date_dim.d_current_day (TEXT), tpcds.date_dim.d_current_week (TEXT), tpcds.date_dim.d_current_month (TEXT), tpcds.date_dim.d_current_quarter (TEXT), tpcds.date_dim.d_current_year (TEXT)}
                        JOIN(10)(CROSS)
                          => target list: tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.catalog_sales.cs_sold_time_sk (INT4), tpcds.catalog_sales.cs_ship_date_sk (INT4), tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_bill_cdemo_sk (INT4), tpcds.catalog_sales.cs_bill_hdemo_sk (INT4), tpcds.catalog_sales.cs_bill_addr_sk (INT4), tpcds.catalog_sales.cs_ship_customer_sk (INT4), tpcds.catalog_sales.cs_ship_cdemo_sk (INT4), tpcds.catalog_sales.cs_ship_hdemo_sk (INT4), tpcds.catalog_sales.cs_ship_addr_sk (INT4), tpcds.catalog_sales.cs_call_center_sk (INT4), tpcds.catalog_sales.cs_catalog_page_sk (INT4), tpcds.catalog_sales.cs_ship_mode_sk (INT4), tpcds.catalog_sales.cs_warehouse_sk (INT4), tpcds.catalog_sales.cs_item_sk (INT4), tpcds.catalog_sales.cs_promo_sk (INT4), tpcds.catalog_sales.cs_order_number (INT4), tpcds.catalog_sales.cs_quantity (INT4), tpcds.catalog_sales.cs_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_list_price (FLOAT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT4), tpcds.catalog_sales.cs_ext_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_ext_list_price (FLOAT4), tpcds.catalog_sales.cs_ext_tax (FLOAT4), tpcds.catalog_sales.cs_coupon_amt (FLOAT4), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT4), tpcds.catalog_sales.cs_net_paid (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT4), tpcds.catalog_sales.cs_net_profit (FLOAT4), tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT4), tpcds.customer.c_current_hdemo_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer.c_first_shipto_date_sk (INT4), tpcds.customer.c_first_sales_date_sk (INT4), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT4), tpcds.customer.c_birth_month (INT4), tpcds.customer.c_birth_year (INT4), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date (TEXT), tpcds.customer_address.ca_zip (TEXT), ?and_1 (BOOLEAN), tpcds.customer_address.ca_address_sk (INT4), tpcds.customer_address.ca_address_id (TEXT), tpcds.customer_address.ca_street_number (TEXT), tpcds.customer_address.ca_street_name (TEXT), tpcds.customer_address.ca_street_type (TEXT), tpcds.customer_address.ca_suite_number (TEXT), tpcds.customer_address.ca_city (TEXT), tpcds.customer_address.ca_county (TEXT), tpcds.customer_address.ca_state (TEXT), ?fake0 (TEXT), tpcds.customer_address.ca_country (TEXT), tpcds.customer_address.ca_gmt_offset (FLOAT4), tpcds.customer_address.ca_location_type (TEXT)
                          => out schema: {(67) tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.catalog_sales.cs_sold_time_sk (INT4), tpcds.catalog_sales.cs_ship_date_sk (INT4), tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_bill_cdemo_sk (INT4), tpcds.catalog_sales.cs_bill_hdemo_sk (INT4), tpcds.catalog_sales.cs_bill_addr_sk (INT4), tpcds.catalog_sales.cs_ship_customer_sk (INT4), tpcds.catalog_sales.cs_ship_cdemo_sk (INT4), tpcds.catalog_sales.cs_ship_hdemo_sk (INT4), tpcds.catalog_sales.cs_ship_addr_sk (INT4), tpcds.catalog_sales.cs_call_center_sk (INT4), tpcds.catalog_sales.cs_catalog_page_sk (INT4), tpcds.catalog_sales.cs_ship_mode_sk (INT4), tpcds.catalog_sales.cs_warehouse_sk (INT4), tpcds.catalog_sales.cs_item_sk (INT4), tpcds.catalog_sales.cs_promo_sk (INT4), tpcds.catalog_sales.cs_order_number (INT4), tpcds.catalog_sales.cs_quantity (INT4), tpcds.catalog_sales.cs_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_list_price (FLOAT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT4), tpcds.catalog_sales.cs_ext_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_ext_list_price (FLOAT4), tpcds.catalog_sales.cs_ext_tax (FLOAT4), tpcds.catalog_sales.cs_coupon_amt (FLOAT4), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT4), tpcds.catalog_sales.cs_net_paid (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT4), tpcds.catalog_sales.cs_net_profit (FLOAT4), tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT4), tpcds.customer.c_current_hdemo_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer.c_first_shipto_date_sk (INT4), tpcds.customer.c_first_sales_date_sk (INT4), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT4), tpcds.customer.c_birth_month (INT4), tpcds.customer.c_birth_year (INT4), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date (TEXT), tpcds.customer_address.ca_zip (TEXT), ?and_1 (BOOLEAN), tpcds.customer_address.ca_address_sk (INT4), tpcds.customer_address.ca_address_id (TEXT), tpcds.customer_address.ca_street_number (TEXT), tpcds.customer_address.ca_street_name (TEXT), tpcds.customer_address.ca_street_type (TEXT), tpcds.customer_address.ca_suite_number (TEXT), tpcds.customer_address.ca_city (TEXT), tpcds.customer_address.ca_county (TEXT), tpcds.customer_address.ca_state (TEXT), ?fake0 (TEXT), tpcds.customer_address.ca_country (TEXT), tpcds.customer_address.ca_gmt_offset (FLOAT4), tpcds.customer_address.ca_location_type (TEXT)}
                          => in schema: {(67) tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.catalog_sales.cs_sold_time_sk (INT4), tpcds.catalog_sales.cs_ship_date_sk (INT4), tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_bill_cdemo_sk (INT4), tpcds.catalog_sales.cs_bill_hdemo_sk (INT4), tpcds.catalog_sales.cs_bill_addr_sk (INT4), tpcds.catalog_sales.cs_ship_customer_sk (INT4), tpcds.catalog_sales.cs_ship_cdemo_sk (INT4), tpcds.catalog_sales.cs_ship_hdemo_sk (INT4), tpcds.catalog_sales.cs_ship_addr_sk (INT4), tpcds.catalog_sales.cs_call_center_sk (INT4), tpcds.catalog_sales.cs_catalog_page_sk (INT4), tpcds.catalog_sales.cs_ship_mode_sk (INT4), tpcds.catalog_sales.cs_warehouse_sk (INT4), tpcds.catalog_sales.cs_item_sk (INT4), tpcds.catalog_sales.cs_promo_sk (INT4), tpcds.catalog_sales.cs_order_number (INT4), tpcds.catalog_sales.cs_quantity (INT4), tpcds.catalog_sales.cs_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_list_price (FLOAT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT4), tpcds.catalog_sales.cs_ext_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_ext_list_price (FLOAT4), tpcds.catalog_sales.cs_ext_tax (FLOAT4), tpcds.catalog_sales.cs_coupon_amt (FLOAT4), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT4), tpcds.catalog_sales.cs_net_paid (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT4), tpcds.catalog_sales.cs_net_profit (FLOAT4), tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT4), tpcds.customer.c_current_hdemo_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer.c_first_shipto_date_sk (INT4), tpcds.customer.c_first_sales_date_sk (INT4), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT4), tpcds.customer.c_birth_month (INT4), tpcds.customer.c_birth_year (INT4), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date (TEXT), tpcds.customer_address.ca_zip (TEXT), ?and_1 (BOOLEAN), tpcds.customer_address.ca_address_sk (INT4), tpcds.customer_address.ca_address_id (TEXT), tpcds.customer_address.ca_street_number (TEXT), tpcds.customer_address.ca_street_name (TEXT), tpcds.customer_address.ca_street_type (TEXT), tpcds.customer_address.ca_suite_number (TEXT), tpcds.customer_address.ca_city (TEXT), tpcds.customer_address.ca_county (TEXT), tpcds.customer_address.ca_state (TEXT), ?fake0 (TEXT), tpcds.customer_address.ca_country (TEXT), tpcds.customer_address.ca_gmt_offset (FLOAT4), tpcds.customer_address.ca_location_type (TEXT)}
                           SCAN(2) on tpcds.customer_address
                             => target list: tpcds.customer_address.ca_address_sk (INT4), tpcds.customer_address.ca_address_id (TEXT), tpcds.customer_address.ca_street_number (TEXT), tpcds.customer_address.ca_street_name (TEXT), tpcds.customer_address.ca_street_type (TEXT), tpcds.customer_address.ca_suite_number (TEXT), tpcds.customer_address.ca_city (TEXT), tpcds.customer_address.ca_county (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_country (TEXT), tpcds.customer_address.ca_gmt_offset (FLOAT4), tpcds.customer_address.ca_location_type (TEXT)
                             => out schema: {(13) tpcds.customer_address.ca_address_sk (INT4), tpcds.customer_address.ca_address_id (TEXT), tpcds.customer_address.ca_street_number (TEXT), tpcds.customer_address.ca_street_name (TEXT), tpcds.customer_address.ca_street_type (TEXT), tpcds.customer_address.ca_suite_number (TEXT), tpcds.customer_address.ca_city (TEXT), tpcds.customer_address.ca_county (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_country (TEXT), tpcds.customer_address.ca_gmt_offset (FLOAT4), tpcds.customer_address.ca_location_type (TEXT)}
                             => in schema: {(13) tpcds.customer_address.ca_address_sk (INT4), tpcds.customer_address.ca_address_id (TEXT), tpcds.customer_address.ca_street_number (TEXT), tpcds.customer_address.ca_street_name (TEXT), tpcds.customer_address.ca_street_type (TEXT), tpcds.customer_address.ca_suite_number (TEXT), tpcds.customer_address.ca_city (TEXT), tpcds.customer_address.ca_county (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_country (TEXT), tpcds.customer_address.ca_gmt_offset (FLOAT4), tpcds.customer_address.ca_location_type (TEXT)}
                           JOIN(9)(CROSS)
                             => target list: tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.catalog_sales.cs_sold_time_sk (INT4), tpcds.catalog_sales.cs_ship_date_sk (INT4), tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_bill_cdemo_sk (INT4), tpcds.catalog_sales.cs_bill_hdemo_sk (INT4), tpcds.catalog_sales.cs_bill_addr_sk (INT4), tpcds.catalog_sales.cs_ship_customer_sk (INT4), tpcds.catalog_sales.cs_ship_cdemo_sk (INT4), tpcds.catalog_sales.cs_ship_hdemo_sk (INT4), tpcds.catalog_sales.cs_ship_addr_sk (INT4), tpcds.catalog_sales.cs_call_center_sk (INT4), tpcds.catalog_sales.cs_catalog_page_sk (INT4), tpcds.catalog_sales.cs_ship_mode_sk (INT4), tpcds.catalog_sales.cs_warehouse_sk (INT4), tpcds.catalog_sales.cs_item_sk (INT4), tpcds.catalog_sales.cs_promo_sk (INT4), tpcds.catalog_sales.cs_order_number (INT4), tpcds.catalog_sales.cs_quantity (INT4), tpcds.catalog_sales.cs_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_list_price (FLOAT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT4), tpcds.catalog_sales.cs_ext_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_ext_list_price (FLOAT4), tpcds.catalog_sales.cs_ext_tax (FLOAT4), tpcds.catalog_sales.cs_coupon_amt (FLOAT4), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT4), tpcds.catalog_sales.cs_net_paid (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT4), tpcds.catalog_sales.cs_net_profit (FLOAT4), tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT4), tpcds.customer.c_current_hdemo_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer.c_first_shipto_date_sk (INT4), tpcds.customer.c_first_sales_date_sk (INT4), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT4), tpcds.customer.c_birth_month (INT4), tpcds.customer.c_birth_year (INT4), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date (TEXT), tpcds.customer_address.ca_zip (TEXT), tpcds.catalog_sales.cs_bill_customer_sk (INT4) = tpcds.customer.c_customer_sk (INT4) AND tpcds.customer.c_current_addr_sk (INT4) = tpcds.customer_address.ca_address_sk (INT4) AND substr(tpcds.customer_address.ca_zip (TEXT),1,5) IN (85669, 86197, 88274, 83405, 86475, 85392, 85460, 80348, 81792) OR tpcds.customer_address.ca_state (TEXT) IN (CA, WA, GA) OR tpcds.catalog_sales.cs_sales_price (FLOAT4) > 500.0 AND tpcds.catalog_sales.cs_sold_date_sk (INT4) = tpcds.date_dim.d_date_sk (INT4) AND tpcds.date_dim.d_qoy (INT4) = 2 AND tpcds.date_dim.d_year (INT4) = 2000 as ?and_1
                             => out schema: {(54) tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.catalog_sales.cs_sold_time_sk (INT4), tpcds.catalog_sales.cs_ship_date_sk (INT4), tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_bill_cdemo_sk (INT4), tpcds.catalog_sales.cs_bill_hdemo_sk (INT4), tpcds.catalog_sales.cs_bill_addr_sk (INT4), tpcds.catalog_sales.cs_ship_customer_sk (INT4), tpcds.catalog_sales.cs_ship_cdemo_sk (INT4), tpcds.catalog_sales.cs_ship_hdemo_sk (INT4), tpcds.catalog_sales.cs_ship_addr_sk (INT4), tpcds.catalog_sales.cs_call_center_sk (INT4), tpcds.catalog_sales.cs_catalog_page_sk (INT4), tpcds.catalog_sales.cs_ship_mode_sk (INT4), tpcds.catalog_sales.cs_warehouse_sk (INT4), tpcds.catalog_sales.cs_item_sk (INT4), tpcds.catalog_sales.cs_promo_sk (INT4), tpcds.catalog_sales.cs_order_number (INT4), tpcds.catalog_sales.cs_quantity (INT4), tpcds.catalog_sales.cs_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_list_price (FLOAT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT4), tpcds.catalog_sales.cs_ext_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_ext_list_price (FLOAT4), tpcds.catalog_sales.cs_ext_tax (FLOAT4), tpcds.catalog_sales.cs_coupon_amt (FLOAT4), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT4), tpcds.catalog_sales.cs_net_paid (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT4), tpcds.catalog_sales.cs_net_profit (FLOAT4), tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT4), tpcds.customer.c_current_hdemo_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer.c_first_shipto_date_sk (INT4), tpcds.customer.c_first_sales_date_sk (INT4), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT4), tpcds.customer.c_birth_month (INT4), tpcds.customer.c_birth_year (INT4), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date (TEXT), tpcds.customer_address.ca_zip (TEXT), ?and_1 (BOOLEAN)}
                             => in schema: {(52) tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.catalog_sales.cs_sold_time_sk (INT4), tpcds.catalog_sales.cs_ship_date_sk (INT4), tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_bill_cdemo_sk (INT4), tpcds.catalog_sales.cs_bill_hdemo_sk (INT4), tpcds.catalog_sales.cs_bill_addr_sk (INT4), tpcds.catalog_sales.cs_ship_customer_sk (INT4), tpcds.catalog_sales.cs_ship_cdemo_sk (INT4), tpcds.catalog_sales.cs_ship_hdemo_sk (INT4), tpcds.catalog_sales.cs_ship_addr_sk (INT4), tpcds.catalog_sales.cs_call_center_sk (INT4), tpcds.catalog_sales.cs_catalog_page_sk (INT4), tpcds.catalog_sales.cs_ship_mode_sk (INT4), tpcds.catalog_sales.cs_warehouse_sk (INT4), tpcds.catalog_sales.cs_item_sk (INT4), tpcds.catalog_sales.cs_promo_sk (INT4), tpcds.catalog_sales.cs_order_number (INT4), tpcds.catalog_sales.cs_quantity (INT4), tpcds.catalog_sales.cs_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_list_price (FLOAT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT4), tpcds.catalog_sales.cs_ext_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_ext_list_price (FLOAT4), tpcds.catalog_sales.cs_ext_tax (FLOAT4), tpcds.catalog_sales.cs_coupon_amt (FLOAT4), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT4), tpcds.catalog_sales.cs_net_paid (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT4), tpcds.catalog_sales.cs_net_profit (FLOAT4), tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT4), tpcds.customer.c_current_hdemo_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer.c_first_shipto_date_sk (INT4), tpcds.customer.c_first_sales_date_sk (INT4), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT4), tpcds.customer.c_birth_month (INT4), tpcds.customer.c_birth_year (INT4), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date (TEXT)}
                              SCAN(1) on tpcds.customer
                                => target list: tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT4), tpcds.customer.c_current_hdemo_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer.c_first_shipto_date_sk (INT4), tpcds.customer.c_first_sales_date_sk (INT4), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT4), tpcds.customer.c_birth_month (INT4), tpcds.customer.c_birth_year (INT4), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date (TEXT)
                                => out schema: {(18) tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT4), tpcds.customer.c_current_hdemo_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer.c_first_shipto_date_sk (INT4), tpcds.customer.c_first_sales_date_sk (INT4), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT4), tpcds.customer.c_birth_month (INT4), tpcds.customer.c_birth_year (INT4), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date (TEXT)}
                                => in schema: {(18) tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT4), tpcds.customer.c_current_hdemo_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer.c_first_shipto_date_sk (INT4), tpcds.customer.c_first_sales_date_sk (INT4), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT4), tpcds.customer.c_birth_month (INT4), tpcds.customer.c_birth_year (INT4), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date (TEXT)}
                              SCAN(0) on tpcds.catalog_sales
                                => target list: tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.catalog_sales.cs_sold_time_sk (INT4), tpcds.catalog_sales.cs_ship_date_sk (INT4), tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_bill_cdemo_sk (INT4), tpcds.catalog_sales.cs_bill_hdemo_sk (INT4), tpcds.catalog_sales.cs_bill_addr_sk (INT4), tpcds.catalog_sales.cs_ship_customer_sk (INT4), tpcds.catalog_sales.cs_ship_cdemo_sk (INT4), tpcds.catalog_sales.cs_ship_hdemo_sk (INT4), tpcds.catalog_sales.cs_ship_addr_sk (INT4), tpcds.catalog_sales.cs_call_center_sk (INT4), tpcds.catalog_sales.cs_catalog_page_sk (INT4), tpcds.catalog_sales.cs_ship_mode_sk (INT4), tpcds.catalog_sales.cs_warehouse_sk (INT4), tpcds.catalog_sales.cs_item_sk (INT4), tpcds.catalog_sales.cs_promo_sk (INT4), tpcds.catalog_sales.cs_order_number (INT4), tpcds.catalog_sales.cs_quantity (INT4), tpcds.catalog_sales.cs_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_list_price (FLOAT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT4), tpcds.catalog_sales.cs_ext_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_ext_list_price (FLOAT4), tpcds.catalog_sales.cs_ext_tax (FLOAT4), tpcds.catalog_sales.cs_coupon_amt (FLOAT4), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT4), tpcds.catalog_sales.cs_net_paid (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT4), tpcds.catalog_sales.cs_net_profit (FLOAT4)
                                => out schema: {(34) tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.catalog_sales.cs_sold_time_sk (INT4), tpcds.catalog_sales.cs_ship_date_sk (INT4), tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_bill_cdemo_sk (INT4), tpcds.catalog_sales.cs_bill_hdemo_sk (INT4), tpcds.catalog_sales.cs_bill_addr_sk (INT4), tpcds.catalog_sales.cs_ship_customer_sk (INT4), tpcds.catalog_sales.cs_ship_cdemo_sk (INT4), tpcds.catalog_sales.cs_ship_hdemo_sk (INT4), tpcds.catalog_sales.cs_ship_addr_sk (INT4), tpcds.catalog_sales.cs_call_center_sk (INT4), tpcds.catalog_sales.cs_catalog_page_sk (INT4), tpcds.catalog_sales.cs_ship_mode_sk (INT4), tpcds.catalog_sales.cs_warehouse_sk (INT4), tpcds.catalog_sales.cs_item_sk (INT4), tpcds.catalog_sales.cs_promo_sk (INT4), tpcds.catalog_sales.cs_order_number (INT4), tpcds.catalog_sales.cs_quantity (INT4), tpcds.catalog_sales.cs_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_list_price (FLOAT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT4), tpcds.catalog_sales.cs_ext_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_ext_list_price (FLOAT4), tpcds.catalog_sales.cs_ext_tax (FLOAT4), tpcds.catalog_sales.cs_coupon_amt (FLOAT4), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT4), tpcds.catalog_sales.cs_net_paid (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT4), tpcds.catalog_sales.cs_net_profit (FLOAT4)}
                                => in schema: {(34) tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.catalog_sales.cs_sold_time_sk (INT4), tpcds.catalog_sales.cs_ship_date_sk (INT4), tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_bill_cdemo_sk (INT4), tpcds.catalog_sales.cs_bill_hdemo_sk (INT4), tpcds.catalog_sales.cs_bill_addr_sk (INT4), tpcds.catalog_sales.cs_ship_customer_sk (INT4), tpcds.catalog_sales.cs_ship_cdemo_sk (INT4), tpcds.catalog_sales.cs_ship_hdemo_sk (INT4), tpcds.catalog_sales.cs_ship_addr_sk (INT4), tpcds.catalog_sales.cs_call_center_sk (INT4), tpcds.catalog_sales.cs_catalog_page_sk (INT4), tpcds.catalog_sales.cs_ship_mode_sk (INT4), tpcds.catalog_sales.cs_warehouse_sk (INT4), tpcds.catalog_sales.cs_item_sk (INT4), tpcds.catalog_sales.cs_promo_sk (INT4), tpcds.catalog_sales.cs_order_number (INT4), tpcds.catalog_sales.cs_quantity (INT4), tpcds.catalog_sales.cs_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_list_price (FLOAT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT4), tpcds.catalog_sales.cs_ext_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_ext_list_price (FLOAT4), tpcds.catalog_sales.cs_ext_tax (FLOAT4), tpcds.catalog_sales.cs_coupon_amt (FLOAT4), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT4), tpcds.catalog_sales.cs_net_paid (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT4), tpcds.catalog_sales.cs_net_profit (FLOAT4)}
      

      The next plan is generated by Optimizer.

      LIMIT(7) 100
         SORT(6)
           => Sort Keys: tpcds.customer_address.ca_zip (TEXT) (asc)
            GROUP_BY(5)(ca_zip)
              => exprs: (sum(tpcds.catalog_sales.cs_sales_price (FLOAT4)))
              => target list: tpcds.customer_address.ca_zip (TEXT), ?sum (FLOAT8)
              => out schema:{(2) tpcds.customer_address.ca_zip (TEXT), ?sum (FLOAT8)}
              => in schema:{(2) tpcds.customer_address.ca_zip (TEXT), tpcds.catalog_sales.cs_sales_price (FLOAT4)}
               JOIN(15)(INNER)
                 => Join Cond: tpcds.catalog_sales.cs_bill_customer_sk (INT4) = tpcds.customer.c_customer_sk (INT4)
                 => target list: tpcds.customer_address.ca_zip (TEXT), tpcds.catalog_sales.cs_sales_price (FLOAT4)
                 => out schema: {(2) tpcds.customer_address.ca_zip (TEXT), tpcds.catalog_sales.cs_sales_price (FLOAT4)}
                 => in schema: {(4) tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.customer_address.ca_zip (TEXT), tpcds.customer.c_customer_sk (INT4)}
                  JOIN(13)(INNER)
                    => Join Cond: tpcds.customer.c_current_addr_sk (INT4) = tpcds.customer_address.ca_address_sk (INT4)
                    => target list: tpcds.customer_address.ca_zip (TEXT), tpcds.customer.c_customer_sk (INT4)
                    => out schema: {(2) tpcds.customer_address.ca_zip (TEXT), tpcds.customer.c_customer_sk (INT4)}
                    => in schema: {(4) tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_address_sk (INT4)}
                     SCAN(2) on tpcds.customer_address
                       => target list: tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_address_sk (INT4)
                       => out schema: {(2) tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_address_sk (INT4)}
                       => in schema: {(13) tpcds.customer_address.ca_address_sk (INT4), tpcds.customer_address.ca_address_id (TEXT), tpcds.customer_address.ca_street_number (TEXT), tpcds.customer_address.ca_street_name (TEXT), tpcds.customer_address.ca_street_type (TEXT), tpcds.customer_address.ca_suite_number (TEXT), tpcds.customer_address.ca_city (TEXT), tpcds.customer_address.ca_county (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_country (TEXT), tpcds.customer_address.ca_gmt_offset (FLOAT4), tpcds.customer_address.ca_location_type (TEXT)}
                     SCAN(1) on tpcds.customer
                       => target list: tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_current_addr_sk (INT4)
                       => out schema: {(2) tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_current_addr_sk (INT4)}
                       => in schema: {(18) tpcds.customer.c_customer_sk (INT4), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT4), tpcds.customer.c_current_hdemo_sk (INT4), tpcds.customer.c_current_addr_sk (INT4), tpcds.customer.c_first_shipto_date_sk (INT4), tpcds.customer.c_first_sales_date_sk (INT4), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT4), tpcds.customer.c_birth_month (INT4), tpcds.customer.c_birth_year (INT4), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date (TEXT)}
                  JOIN(14)(INNER)
                    => Join Cond: tpcds.catalog_sales.cs_sold_date_sk (INT4) = tpcds.date_dim.d_date_sk (INT4)
                    => target list: tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_sales_price (FLOAT4)
                    => out schema: {(2) tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_sales_price (FLOAT4)}
                    => in schema: {(4) tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.date_dim.d_date_sk (INT4)}
                     SCAN(3) on tpcds.date_dim
                       => filter: tpcds.date_dim.d_year (INT4) = 2000 AND tpcds.date_dim.d_qoy (INT4) = 2
                       => target list: tpcds.date_dim.d_date_sk (INT4)
                       => out schema: {(1) tpcds.date_dim.d_date_sk (INT4)}
                       => in schema: {(28) tpcds.date_dim.d_date_sk (INT4), tpcds.date_dim.d_date_id (TEXT), tpcds.date_dim.d_date (DATE), tpcds.date_dim.d_month_seq (INT4), tpcds.date_dim.d_week_seq (INT4), tpcds.date_dim.d_quarter_seq (INT4), tpcds.date_dim.d_year (INT4), tpcds.date_dim.d_dow (INT4), tpcds.date_dim.d_moy (INT4), tpcds.date_dim.d_dom (INT4), tpcds.date_dim.d_qoy (INT4), tpcds.date_dim.d_fy_year (INT4), tpcds.date_dim.d_fy_quarter_seq (INT4), tpcds.date_dim.d_fy_week_seq (INT4), tpcds.date_dim.d_day_name (TEXT), tpcds.date_dim.d_quarter_name (TEXT), tpcds.date_dim.d_holiday (TEXT), tpcds.date_dim.d_weekend (TEXT), tpcds.date_dim.d_following_holiday (TEXT), tpcds.date_dim.d_first_dom (INT4), tpcds.date_dim.d_last_dom (INT4), tpcds.date_dim.d_same_day_ly (INT4), tpcds.date_dim.d_same_day_lq (INT4), tpcds.date_dim.d_current_day (TEXT), tpcds.date_dim.d_current_week (TEXT), tpcds.date_dim.d_current_month (TEXT), tpcds.date_dim.d_current_quarter (TEXT), tpcds.date_dim.d_current_year (TEXT)}
                     SCAN(0) on tpcds.catalog_sales
                       => target list: tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_sold_date_sk (INT4)
                       => out schema: {(3) tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_sold_date_sk (INT4)}
                       => in schema: {(34) tpcds.catalog_sales.cs_sold_date_sk (INT4), tpcds.catalog_sales.cs_sold_time_sk (INT4), tpcds.catalog_sales.cs_ship_date_sk (INT4), tpcds.catalog_sales.cs_bill_customer_sk (INT4), tpcds.catalog_sales.cs_bill_cdemo_sk (INT4), tpcds.catalog_sales.cs_bill_hdemo_sk (INT4), tpcds.catalog_sales.cs_bill_addr_sk (INT4), tpcds.catalog_sales.cs_ship_customer_sk (INT4), tpcds.catalog_sales.cs_ship_cdemo_sk (INT4), tpcds.catalog_sales.cs_ship_hdemo_sk (INT4), tpcds.catalog_sales.cs_ship_addr_sk (INT4), tpcds.catalog_sales.cs_call_center_sk (INT4), tpcds.catalog_sales.cs_catalog_page_sk (INT4), tpcds.catalog_sales.cs_ship_mode_sk (INT4), tpcds.catalog_sales.cs_warehouse_sk (INT4), tpcds.catalog_sales.cs_item_sk (INT4), tpcds.catalog_sales.cs_promo_sk (INT4), tpcds.catalog_sales.cs_order_number (INT4), tpcds.catalog_sales.cs_quantity (INT4), tpcds.catalog_sales.cs_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_list_price (FLOAT4), tpcds.catalog_sales.cs_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT4), tpcds.catalog_sales.cs_ext_sales_price (FLOAT4), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT4), tpcds.catalog_sales.cs_ext_list_price (FLOAT4), tpcds.catalog_sales.cs_ext_tax (FLOAT4), tpcds.catalog_sales.cs_coupon_amt (FLOAT4), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT4), tpcds.catalog_sales.cs_net_paid (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT4), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT4), tpcds.catalog_sales.cs_net_profit (FLOAT4)}
      

        Issue Links

          Activity

          Hide
          jihoonson Jihoon Son added a comment -

          Thia may be related to the problem in join ordering. I'm resolving it in TAJO-1352. The work is almost done at https://github.com/jihoonson/tajo-2/tree/TAJO-1352_4. Would you test again with this patch?

          Show
          jihoonson Jihoon Son added a comment - Thia may be related to the problem in join ordering. I'm resolving it in TAJO-1352 . The work is almost done at https://github.com/jihoonson/tajo-2/tree/TAJO-1352_4 . Would you test again with this patch?
          Hide
          hjkim Hyoungjun Kim added a comment -

          Jihoon Son. After applying that patch, PlanningException occurs. Please check the next url for full stack message.

          https://drive.google.com/file/d/0B50TOi1HKdTwclZJdHBYN0pBZDA/view?usp=sharing

          Show
          hjkim Hyoungjun Kim added a comment - Jihoon Son . After applying that patch, PlanningException occurs. Please check the next url for full stack message. https://drive.google.com/file/d/0B50TOi1HKdTwclZJdHBYN0pBZDA/view?usp=sharing
          Hide
          jihoonson Jihoon Son added a comment -

          Thanks. I'll investigate it.

          Show
          jihoonson Jihoon Son added a comment - Thanks. I'll investigate it.
          Hide
          sirpkt Keuntae Park added a comment - - edited

          This problem seems to be related with bug in FilterPushDown.
          FilterPushDown should push filter down only when it can be handled in the child LogicalNodes,
          However, it sometimes makes a wrong decision and, in my thought, it is from the wrong behavior of checkIfBeEvaluatedAtJoin() in LogicalPlanner.

          As the method name represents, checkifBeEvaluatedAtJoin() seems to check whether the given predicate can be evaluated within Join and its children. It checks whether all columns used in the predicate is included in the incoming schema of JoinNode or not. It can successfully check the case of join qualifier, however, it wrongly includes the the case that columns from both left and right children of JoinNode are included but they cannot be join qualifier. Following is the example query:

          select
            n1.n_nationkey,
            n1.n_name,
            n2.n_name
          from nation n1, nation n2 where n1.n_name = n2.n_name and (n1.n_nationkey in (1, 2) or n2.n_nationkey in (2))
          order by n1.n_nationkey;
          

          At above case, checkifBeEvaluatedAtJoin() tries to push down all the entries of CNF array of selection expression,
          1) n1.n_name = n2.n_name and 2) n1.n_nationkey in (1, 2) or n2.n_nationkey in (2), to the child LogicalNode, cross join node between n1 and n2 because all columns in each predicate are included in either n1 or n2.
          The first predicate can be actually handled by cross join as a join qualifier, however, the second predicate cannot be handled in join or its children because it needs the result of join operation.

          So, I think checkifBeEvaluatedAtJoin() or calling part of checkifBeEvaluatedAtJoin() in FilterPushDown should be changed to check if

          • Not all columns in the given predicate is included in the incoming schema of child JoinNode
          • But the given predicate can be the join qualifier of any join operation included in the child LogicalNode tree
          Show
          sirpkt Keuntae Park added a comment - - edited This problem seems to be related with bug in FilterPushDown. FilterPushDown should push filter down only when it can be handled in the child LogicalNodes, However, it sometimes makes a wrong decision and, in my thought, it is from the wrong behavior of checkIfBeEvaluatedAtJoin() in LogicalPlanner. As the method name represents, checkifBeEvaluatedAtJoin() seems to check whether the given predicate can be evaluated within Join and its children. It checks whether all columns used in the predicate is included in the incoming schema of JoinNode or not. It can successfully check the case of join qualifier, however, it wrongly includes the the case that columns from both left and right children of JoinNode are included but they cannot be join qualifier. Following is the example query: select n1.n_nationkey, n1.n_name, n2.n_name from nation n1, nation n2 where n1.n_name = n2.n_name and (n1.n_nationkey in (1, 2) or n2.n_nationkey in (2)) order by n1.n_nationkey; At above case, checkifBeEvaluatedAtJoin() tries to push down all the entries of CNF array of selection expression, 1) n1.n_name = n2.n_name and 2) n1.n_nationkey in (1, 2) or n2.n_nationkey in (2), to the child LogicalNode, cross join node between n1 and n2 because all columns in each predicate are included in either n1 or n2. The first predicate can be actually handled by cross join as a join qualifier, however, the second predicate cannot be handled in join or its children because it needs the result of join operation. So, I think checkifBeEvaluatedAtJoin() or calling part of checkifBeEvaluatedAtJoin() in FilterPushDown should be changed to check if Not all columns in the given predicate is included in the incoming schema of child JoinNode But the given predicate can be the join qualifier of any join operation included in the child LogicalNode tree
          Hide
          sirpkt Keuntae Park added a comment -

          For the query of this issue, it seems that FilterPushDown wrongly tries to push down the following predicate:

          ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475',
                                             '85392', '85460', '80348', '81792')
                   or ca_state in ('CA','WA','GA')
                   or cs_sales_price > 500)
          

          As shown in the plan, this predicate includes columns from two tables, customer_address and catalog_sales, included in relation list. So, FilterPushDown tries to push down it.
          However, it cannot be a join qualifier of any joins and also cannot be handled in the ScanNode of any tables,
          which means Tajo cannot determine the target LogicalNode related with that predicate.
          I think that's the reason why we lost that predicate.

          Show
          sirpkt Keuntae Park added a comment - For the query of this issue, it seems that FilterPushDown wrongly tries to push down the following predicate: ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792') or ca_state in ('CA','WA','GA') or cs_sales_price > 500) As shown in the plan, this predicate includes columns from two tables, customer_address and catalog_sales, included in relation list. So, FilterPushDown tries to push down it. However, it cannot be a join qualifier of any joins and also cannot be handled in the ScanNode of any tables, which means Tajo cannot determine the target LogicalNode related with that predicate. I think that's the reason why we lost that predicate.
          Hide
          jihoonson Jihoon Son added a comment -

          Keuntae Park, thank you for nice finding! This is definitely a bug.
          I wonder that you alread started this work.
          If you don't mind, I'd like to fix this bug in TAJO-1350.
          Since that issue is to refactor the filter pushdown for join, this bug will be fixed easily.
          (Honestly, it seems that the bug can be fixed by adding few lines to the patch for TAJO-1350. I'll attach the patch.)

          Show
          jihoonson Jihoon Son added a comment - Keuntae Park , thank you for nice finding! This is definitely a bug. I wonder that you alread started this work. If you don't mind, I'd like to fix this bug in TAJO-1350 . Since that issue is to refactor the filter pushdown for join, this bug will be fixed easily. (Honestly, it seems that the bug can be fixed by adding few lines to the patch for TAJO-1350 . I'll attach the patch.)
          Hide
          jihoonson Jihoon Son added a comment -

          This patch contains changes for TAJO-1350 and bug fixes for TAJO-1445.

          Show
          jihoonson Jihoon Son added a comment - This patch contains changes for TAJO-1350 and bug fixes for TAJO-1445 .
          Hide
          sirpkt Keuntae Park added a comment -

          Sure, Jihoon Son

          Actually, I made a patch for the issue but it is only a few lines of modification as you said.
          And I totally agree with you that TAJO-1350 also includes the patch code for this issue.

          Show
          sirpkt Keuntae Park added a comment - Sure, Jihoon Son Actually, I made a patch for the issue but it is only a few lines of modification as you said. And I totally agree with you that TAJO-1350 also includes the patch code for this issue.
          Hide
          jihoonson Jihoon Son added a comment -

          Thanks!
          I'll put a patch on TAJO-1350 after adding a test case for this issue.
          It will be my pleasure if you review the patch.

          Show
          jihoonson Jihoon Son added a comment - Thanks! I'll put a patch on TAJO-1350 after adding a test case for this issue. It will be my pleasure if you review the patch.
          Hide
          jihoonson Jihoon Son added a comment -

          Hyoungjun Kim, would you mind testing the DS query with the patch TAJO-1350_for_1445.patch?
          I verified the patch with Keuntae Park's query, but it would be great if you also test it.

          Show
          jihoonson Jihoon Son added a comment - Hyoungjun Kim , would you mind testing the DS query with the patch TAJO-1350 _for_1445.patch? I verified the patch with Keuntae Park 's query, but it would be great if you also test it.
          Hide
          jihoonson Jihoon Son added a comment -

          Here is the query plan after applying the patch of TAJO-1350.

          LIMIT(7) 100
             SORT(6)
               => Sort Keys: tpcds.customer_address.ca_zip (TEXT) (asc)
                GROUP_BY(5)(ca_zip)
                  => exprs: (sum(tpcds.catalog_sales.cs_sales_price (FLOAT8)))
                  => target list: tpcds.customer_address.ca_zip (TEXT), ?sum (FLOAT8)
                  => out schema:{(2) tpcds.customer_address.ca_zip (TEXT), ?sum (FLOAT8)}
                  => in schema:{(3) tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.catalog_sales.cs_sales_price (FLOAT8)}
                   SELECTION(4)
                     => Search Cond: substr(tpcds.customer_address.ca_zip (TEXT),1,5) IN (85669, 86197, 88274, 83405, 86475, 85392, 85460, 80348, 81792) OR tpcds.customer_address.ca_state (TEXT) IN (CA, WA, GA) OR tpcds.catalog_sales.cs_sales_price (FLOAT8) > 500.0
                      JOIN(15)(INNER)
                        => Join Cond: tpcds.catalog_sales.cs_bill_customer_sk (INT8) = tpcds.customer.c_customer_sk (INT8)
                        => target list: tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.catalog_sales.cs_sales_price (FLOAT8)
                        => out schema: {(3) tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.catalog_sales.cs_sales_price (FLOAT8)}
                        => in schema: {(5) tpcds.catalog_sales.cs_sales_price (FLOAT8), tpcds.catalog_sales.cs_bill_customer_sk (INT8), tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer.c_customer_sk (INT8)}
                         JOIN(13)(INNER)
                           => Join Cond: tpcds.customer.c_current_addr_sk (INT8) = tpcds.customer_address.ca_address_sk (INT8)
                           => target list: tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer.c_customer_sk (INT8)
                           => out schema: {(3) tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer.c_customer_sk (INT8)}
                           => in schema: {(5) tpcds.customer.c_customer_sk (INT8), tpcds.customer.c_current_addr_sk (INT8), tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer_address.ca_address_sk (INT8)}
                            SCAN(2) on tpcds.customer_address
                              => target list: tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer_address.ca_address_sk (INT8)
                              => out schema: {(3) tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer_address.ca_address_sk (INT8)}
                              => in schema: {(13) tpcds.customer_address.ca_address_sk (INT8), tpcds.customer_address.ca_address_id (TEXT), tpcds.customer_address.ca_street_number (TEXT), tpcds.customer_address.ca_street_name (TEXT), tpcds.customer_address.ca_street_type (TEXT), tpcds.customer_address.ca_suite_number (TEXT), tpcds.customer_address.ca_city (TEXT), tpcds.customer_address.ca_county (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_country (TEXT), tpcds.customer_address.ca_gmt_offset (FLOAT8), tpcds.customer_address.ca_location_type (TEXT)}
                            SCAN(1) on tpcds.customer
                              => target list: tpcds.customer.c_customer_sk (INT8), tpcds.customer.c_current_addr_sk (INT8)
                              => out schema: {(2) tpcds.customer.c_customer_sk (INT8), tpcds.customer.c_current_addr_sk (INT8)}
                              => in schema: {(18) tpcds.customer.c_customer_sk (INT8), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT8), tpcds.customer.c_current_hdemo_sk (INT8), tpcds.customer.c_current_addr_sk (INT8), tpcds.customer.c_first_shipto_date_sk (INT8), tpcds.customer.c_first_sales_date_sk (INT8), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT8), tpcds.customer.c_birth_month (INT8), tpcds.customer.c_birth_year (INT8), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date_sk (INT8)}
                         JOIN(14)(INNER)
                           => Join Cond: tpcds.catalog_sales.cs_sold_date_sk (INT8) = tpcds.date_dim.d_date_sk (INT8)
                           => target list: tpcds.catalog_sales.cs_sales_price (FLOAT8), tpcds.catalog_sales.cs_bill_customer_sk (INT8)
                           => out schema: {(2) tpcds.catalog_sales.cs_sales_price (FLOAT8), tpcds.catalog_sales.cs_bill_customer_sk (INT8)}
                           => in schema: {(4) tpcds.catalog_sales.cs_sales_price (FLOAT8), tpcds.catalog_sales.cs_bill_customer_sk (INT8), tpcds.catalog_sales.cs_sold_date_sk (INT8), tpcds.date_dim.d_date_sk (INT8)}
                            SCAN(3) on tpcds.date_dim
                              => filter: tpcds.date_dim.d_year (INT8) = 2000 AND tpcds.date_dim.d_qoy (INT8) = 2
                              => target list: tpcds.date_dim.d_date_sk (INT8)
                              => out schema: {(1) tpcds.date_dim.d_date_sk (INT8)}
                              => in schema: {(28) tpcds.date_dim.d_date_sk (INT8), tpcds.date_dim.d_date_id (TEXT), tpcds.date_dim.d_date (DATE), tpcds.date_dim.d_month_seq (INT8), tpcds.date_dim.d_week_seq (INT8), tpcds.date_dim.d_quarter_seq (INT8), tpcds.date_dim.d_year (INT8), tpcds.date_dim.d_dow (INT8), tpcds.date_dim.d_moy (INT8), tpcds.date_dim.d_dom (INT8), tpcds.date_dim.d_qoy (INT8), tpcds.date_dim.d_fy_year (INT8), tpcds.date_dim.d_fy_quarter_seq (INT8), tpcds.date_dim.d_fy_week_seq (INT8), tpcds.date_dim.d_day_name (TEXT), tpcds.date_dim.d_quarter_name (TEXT), tpcds.date_dim.d_holiday (TEXT), tpcds.date_dim.d_weekend (TEXT), tpcds.date_dim.d_following_holiday (TEXT), tpcds.date_dim.d_first_dom (INT8), tpcds.date_dim.d_last_dom (INT8), tpcds.date_dim.d_same_day_ly (INT8), tpcds.date_dim.d_same_day_lq (INT8), tpcds.date_dim.d_current_day (TEXT), tpcds.date_dim.d_current_week (TEXT), tpcds.date_dim.d_current_month (TEXT), tpcds.date_dim.d_current_quarter (TEXT), tpcds.date_dim.d_current_year (TEXT)}
                            SCAN(0) on tpcds.catalog_sales
                              => target list: tpcds.catalog_sales.cs_sales_price (FLOAT8), tpcds.catalog_sales.cs_bill_customer_sk (INT8), tpcds.catalog_sales.cs_sold_date_sk (INT8)
                              => out schema: {(3) tpcds.catalog_sales.cs_sales_price (FLOAT8), tpcds.catalog_sales.cs_bill_customer_sk (INT8), tpcds.catalog_sales.cs_sold_date_sk (INT8)}
                              => in schema: {(34) tpcds.catalog_sales.cs_sold_date_sk (INT8), tpcds.catalog_sales.cs_sold_time_sk (INT8), tpcds.catalog_sales.cs_ship_date_sk (INT8), tpcds.catalog_sales.cs_bill_customer_sk (INT8), tpcds.catalog_sales.cs_bill_cdemo_sk (INT8), tpcds.catalog_sales.cs_bill_hdemo_sk (INT8), tpcds.catalog_sales.cs_bill_addr_sk (INT8), tpcds.catalog_sales.cs_ship_customer_sk (INT8), tpcds.catalog_sales.cs_ship_cdemo_sk (INT8), tpcds.catalog_sales.cs_ship_hdemo_sk (INT8), tpcds.catalog_sales.cs_ship_addr_sk (INT8), tpcds.catalog_sales.cs_call_center_sk (INT8), tpcds.catalog_sales.cs_catalog_page_sk (INT8), tpcds.catalog_sales.cs_ship_mode_sk (INT8), tpcds.catalog_sales.cs_warehouse_sk (INT8), tpcds.catalog_sales.cs_item_sk (INT8), tpcds.catalog_sales.cs_promo_sk (INT8), tpcds.catalog_sales.cs_order_number (INT8), tpcds.catalog_sales.cs_quantity (INT8), tpcds.catalog_sales.cs_wholesale_cost (FLOAT8), tpcds.catalog_sales.cs_list_price (FLOAT8), tpcds.catalog_sales.cs_sales_price (FLOAT8), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT8), tpcds.catalog_sales.cs_ext_sales_price (FLOAT8), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT8), tpcds.catalog_sales.cs_ext_list_price (FLOAT8), tpcds.catalog_sales.cs_ext_tax (FLOAT8), tpcds.catalog_sales.cs_coupon_amt (FLOAT8), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT8), tpcds.catalog_sales.cs_net_paid (FLOAT8), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT8), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT8), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT8), tpcds.catalog_sales.cs_net_profit (FLOAT8)}
          
          Show
          jihoonson Jihoon Son added a comment - Here is the query plan after applying the patch of TAJO-1350 . LIMIT(7) 100 SORT(6) => Sort Keys: tpcds.customer_address.ca_zip (TEXT) (asc) GROUP_BY(5)(ca_zip) => exprs: (sum(tpcds.catalog_sales.cs_sales_price (FLOAT8))) => target list: tpcds.customer_address.ca_zip (TEXT), ?sum (FLOAT8) => out schema:{(2) tpcds.customer_address.ca_zip (TEXT), ?sum (FLOAT8)} => in schema:{(3) tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.catalog_sales.cs_sales_price (FLOAT8)} SELECTION(4) => Search Cond: substr(tpcds.customer_address.ca_zip (TEXT),1,5) IN (85669, 86197, 88274, 83405, 86475, 85392, 85460, 80348, 81792) OR tpcds.customer_address.ca_state (TEXT) IN (CA, WA, GA) OR tpcds.catalog_sales.cs_sales_price (FLOAT8) > 500.0 JOIN(15)(INNER) => Join Cond: tpcds.catalog_sales.cs_bill_customer_sk (INT8) = tpcds.customer.c_customer_sk (INT8) => target list: tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.catalog_sales.cs_sales_price (FLOAT8) => out schema: {(3) tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.catalog_sales.cs_sales_price (FLOAT8)} => in schema: {(5) tpcds.catalog_sales.cs_sales_price (FLOAT8), tpcds.catalog_sales.cs_bill_customer_sk (INT8), tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer.c_customer_sk (INT8)} JOIN(13)(INNER) => Join Cond: tpcds.customer.c_current_addr_sk (INT8) = tpcds.customer_address.ca_address_sk (INT8) => target list: tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer.c_customer_sk (INT8) => out schema: {(3) tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer.c_customer_sk (INT8)} => in schema: {(5) tpcds.customer.c_customer_sk (INT8), tpcds.customer.c_current_addr_sk (INT8), tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer_address.ca_address_sk (INT8)} SCAN(2) on tpcds.customer_address => target list: tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer_address.ca_address_sk (INT8) => out schema: {(3) tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer_address.ca_address_sk (INT8)} => in schema: {(13) tpcds.customer_address.ca_address_sk (INT8), tpcds.customer_address.ca_address_id (TEXT), tpcds.customer_address.ca_street_number (TEXT), tpcds.customer_address.ca_street_name (TEXT), tpcds.customer_address.ca_street_type (TEXT), tpcds.customer_address.ca_suite_number (TEXT), tpcds.customer_address.ca_city (TEXT), tpcds.customer_address.ca_county (TEXT), tpcds.customer_address.ca_state (TEXT), tpcds.customer_address.ca_zip (TEXT), tpcds.customer_address.ca_country (TEXT), tpcds.customer_address.ca_gmt_offset (FLOAT8), tpcds.customer_address.ca_location_type (TEXT)} SCAN(1) on tpcds.customer => target list: tpcds.customer.c_customer_sk (INT8), tpcds.customer.c_current_addr_sk (INT8) => out schema: {(2) tpcds.customer.c_customer_sk (INT8), tpcds.customer.c_current_addr_sk (INT8)} => in schema: {(18) tpcds.customer.c_customer_sk (INT8), tpcds.customer.c_customer_id (TEXT), tpcds.customer.c_current_cdemo_sk (INT8), tpcds.customer.c_current_hdemo_sk (INT8), tpcds.customer.c_current_addr_sk (INT8), tpcds.customer.c_first_shipto_date_sk (INT8), tpcds.customer.c_first_sales_date_sk (INT8), tpcds.customer.c_salutation (TEXT), tpcds.customer.c_first_name (TEXT), tpcds.customer.c_last_name (TEXT), tpcds.customer.c_preferred_cust_flag (TEXT), tpcds.customer.c_birth_day (INT8), tpcds.customer.c_birth_month (INT8), tpcds.customer.c_birth_year (INT8), tpcds.customer.c_birth_country (TEXT), tpcds.customer.c_login (TEXT), tpcds.customer.c_email_address (TEXT), tpcds.customer.c_last_review_date_sk (INT8)} JOIN(14)(INNER) => Join Cond: tpcds.catalog_sales.cs_sold_date_sk (INT8) = tpcds.date_dim.d_date_sk (INT8) => target list: tpcds.catalog_sales.cs_sales_price (FLOAT8), tpcds.catalog_sales.cs_bill_customer_sk (INT8) => out schema: {(2) tpcds.catalog_sales.cs_sales_price (FLOAT8), tpcds.catalog_sales.cs_bill_customer_sk (INT8)} => in schema: {(4) tpcds.catalog_sales.cs_sales_price (FLOAT8), tpcds.catalog_sales.cs_bill_customer_sk (INT8), tpcds.catalog_sales.cs_sold_date_sk (INT8), tpcds.date_dim.d_date_sk (INT8)} SCAN(3) on tpcds.date_dim => filter: tpcds.date_dim.d_year (INT8) = 2000 AND tpcds.date_dim.d_qoy (INT8) = 2 => target list: tpcds.date_dim.d_date_sk (INT8) => out schema: {(1) tpcds.date_dim.d_date_sk (INT8)} => in schema: {(28) tpcds.date_dim.d_date_sk (INT8), tpcds.date_dim.d_date_id (TEXT), tpcds.date_dim.d_date (DATE), tpcds.date_dim.d_month_seq (INT8), tpcds.date_dim.d_week_seq (INT8), tpcds.date_dim.d_quarter_seq (INT8), tpcds.date_dim.d_year (INT8), tpcds.date_dim.d_dow (INT8), tpcds.date_dim.d_moy (INT8), tpcds.date_dim.d_dom (INT8), tpcds.date_dim.d_qoy (INT8), tpcds.date_dim.d_fy_year (INT8), tpcds.date_dim.d_fy_quarter_seq (INT8), tpcds.date_dim.d_fy_week_seq (INT8), tpcds.date_dim.d_day_name (TEXT), tpcds.date_dim.d_quarter_name (TEXT), tpcds.date_dim.d_holiday (TEXT), tpcds.date_dim.d_weekend (TEXT), tpcds.date_dim.d_following_holiday (TEXT), tpcds.date_dim.d_first_dom (INT8), tpcds.date_dim.d_last_dom (INT8), tpcds.date_dim.d_same_day_ly (INT8), tpcds.date_dim.d_same_day_lq (INT8), tpcds.date_dim.d_current_day (TEXT), tpcds.date_dim.d_current_week (TEXT), tpcds.date_dim.d_current_month (TEXT), tpcds.date_dim.d_current_quarter (TEXT), tpcds.date_dim.d_current_year (TEXT)} SCAN(0) on tpcds.catalog_sales => target list: tpcds.catalog_sales.cs_sales_price (FLOAT8), tpcds.catalog_sales.cs_bill_customer_sk (INT8), tpcds.catalog_sales.cs_sold_date_sk (INT8) => out schema: {(3) tpcds.catalog_sales.cs_sales_price (FLOAT8), tpcds.catalog_sales.cs_bill_customer_sk (INT8), tpcds.catalog_sales.cs_sold_date_sk (INT8)} => in schema: {(34) tpcds.catalog_sales.cs_sold_date_sk (INT8), tpcds.catalog_sales.cs_sold_time_sk (INT8), tpcds.catalog_sales.cs_ship_date_sk (INT8), tpcds.catalog_sales.cs_bill_customer_sk (INT8), tpcds.catalog_sales.cs_bill_cdemo_sk (INT8), tpcds.catalog_sales.cs_bill_hdemo_sk (INT8), tpcds.catalog_sales.cs_bill_addr_sk (INT8), tpcds.catalog_sales.cs_ship_customer_sk (INT8), tpcds.catalog_sales.cs_ship_cdemo_sk (INT8), tpcds.catalog_sales.cs_ship_hdemo_sk (INT8), tpcds.catalog_sales.cs_ship_addr_sk (INT8), tpcds.catalog_sales.cs_call_center_sk (INT8), tpcds.catalog_sales.cs_catalog_page_sk (INT8), tpcds.catalog_sales.cs_ship_mode_sk (INT8), tpcds.catalog_sales.cs_warehouse_sk (INT8), tpcds.catalog_sales.cs_item_sk (INT8), tpcds.catalog_sales.cs_promo_sk (INT8), tpcds.catalog_sales.cs_order_number (INT8), tpcds.catalog_sales.cs_quantity (INT8), tpcds.catalog_sales.cs_wholesale_cost (FLOAT8), tpcds.catalog_sales.cs_list_price (FLOAT8), tpcds.catalog_sales.cs_sales_price (FLOAT8), tpcds.catalog_sales.cs_ext_discount_amt (FLOAT8), tpcds.catalog_sales.cs_ext_sales_price (FLOAT8), tpcds.catalog_sales.cs_ext_wholesale_cost (FLOAT8), tpcds.catalog_sales.cs_ext_list_price (FLOAT8), tpcds.catalog_sales.cs_ext_tax (FLOAT8), tpcds.catalog_sales.cs_coupon_amt (FLOAT8), tpcds.catalog_sales.cs_ext_ship_cost (FLOAT8), tpcds.catalog_sales.cs_net_paid (FLOAT8), tpcds.catalog_sales.cs_net_paid_inc_tax (FLOAT8), tpcds.catalog_sales.cs_net_paid_inc_ship (FLOAT8), tpcds.catalog_sales.cs_net_paid_inc_ship_tax (FLOAT8), tpcds.catalog_sales.cs_net_profit (FLOAT8)}
          Hide
          hjkim Hyoungjun Kim added a comment -

          Jihoon Son Sorry I missed your comment. TAJO-1350 issue was resolved and committed with trunk. The query 15 of TPC-DS passed the TPC-DS test suit(https://github.com/babokim/tajo-tcpds).
          TAJO-1350 already contains test case for this case. So I'll close this issue.

          Show
          hjkim Hyoungjun Kim added a comment - Jihoon Son Sorry I missed your comment. TAJO-1350 issue was resolved and committed with trunk. The query 15 of TPC-DS passed the TPC-DS test suit( https://github.com/babokim/tajo-tcpds ). TAJO-1350 already contains test case for this case. So I'll close this issue.

            People

            • Assignee:
              Unassigned
              Reporter:
              hjkim Hyoungjun Kim
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development