Fix the test failures related to the logical optimizer.

[HaoYang670]

Describe the bug
There are several test failures when setting the config skip_failing_rules to false.

• filter_pushdown_view (The filters expressions in TableScan may contain fields not included in schema. #4793)
• window_frame_creation (enhance the checking of type errors in the test window_frame_creation #5257 )
• right_anti_filter_push_down/right_semi_with_alias_filter (simplify_expr() invoke nullable() exist bug #5191)
• The test in_list_types_struct_literal fails when setting skip_failed_rules as false #5217
• test sort_on_window_null_string failed after disable skip_fail. #5233
• tpcds_logical_q10/q35 Schema error creating logical/physical plan for TPC-DS q10, q35 #4794
• tpcds_logical_q41
• Compute error: Overflow happened in tpcds_logical_q58 #5396
• tpcds_logical_q8 ambiguous name. #5334
• The rule common_sub_expression_eliminate removes non-duplicate expressions #4887
• decorrelate_where_in reports error when optimizing limit subquery #5808
• SimplifyExpressions errors when simplifying power fn #5996

Some tests failures are caused by earlier type checking, which are expected, others are caused by some bugs in the optimizer, and we should fix them.

To Reproduce
Steps to reproduce the behavior:

1. set the skip_failing_rules to false
2. run cargo test --all

Expected behavior
A clear and concise description of what you expected to happen.

Additional context
Add any other context about the problem here.
https://github.com/apache/arrow-datafusion/blob/ac2e5d15e5452e83c835d793a95335e87bf35569/datafusion/optimizer/src/optimizer.rs#L325-L340

[HaoYang670]

I reran cargo test --all with skip_failed_rule = false. It seems like some test failures has been fixed, but there are some newly generated. I've updated the task list.

[HaoYang670]

Full error messages for the tpcds failures:

---- tpcds_logical_q10 stdout ----
Error: Context("type_coercion", Internal("Optimizer rule 'type_coercion' failed due to unexpected error: Schema error: No field named 'c'.'c_customer_sk'. Valid fields are 'web_sales'.'ws_sold_date_sk', 'web_sales'.'ws_sold_time_sk', 'web_sales'.'ws_ship_date_sk', 'web_sales'.'ws_item_sk', 'web_sales'.'ws_bill_customer_sk', 'web_sales'.'ws_bill_cdemo_sk', 'web_sales'.'ws_bill_hdemo_sk', 'web_sales'.'ws_bill_addr_sk', 'web_sales'.'ws_ship_customer_sk', 'web_sales'.'ws_ship_cdemo_sk', 'web_sales'.'ws_ship_hdemo_sk', 'web_sales'.'ws_ship_addr_sk', 'web_sales'.'ws_web_page_sk', 'web_sales'.'ws_web_site_sk', 'web_sales'.'ws_ship_mode_sk', 'web_sales'.'ws_warehouse_sk', 'web_sales'.'ws_promo_sk', 'web_sales'.'ws_order_number', 'web_sales'.'ws_quantity', 'web_sales'.'ws_wholesale_cost', 'web_sales'.'ws_list_price', 'web_sales'.'ws_sales_price', 'web_sales'.'ws_ext_discount_amt', 'web_sales'.'ws_ext_sales_price', 'web_sales'.'ws_ext_wholesale_cost', 'web_sales'.'ws_ext_list_price', 'web_sales'.'ws_ext_tax', 'web_sales'.'ws_coupon_amt', 'web_sales'.'ws_ext_ship_cost', 'web_sales'.'ws_net_paid', 'web_sales'.'ws_net_paid_inc_tax', 'web_sales'.'ws_net_paid_inc_ship', 'web_sales'.'ws_net_paid_inc_ship_tax', 'web_sales'.'ws_net_profit', 'date_dim'.'d_date_sk', 'date_dim'.'d_date_id', 'date_dim'.'d_date', 'date_dim'.'d_month_seq', 'date_dim'.'d_week_seq', 'date_dim'.'d_quarter_seq', 'date_dim'.'d_year', 'date_dim'.'d_dow', 'date_dim'.'d_moy', 'date_dim'.'d_dom', 'date_dim'.'d_qoy', 'date_dim'.'d_fy_year', 'date_dim'.'d_fy_quarter_seq', 'date_dim'.'d_fy_week_seq', 'date_dim'.'d_day_name', 'date_dim'.'d_quarter_name', 'date_dim'.'d_holiday', 'date_dim'.'d_weekend', 'date_dim'.'d_following_holiday', 'date_dim'.'d_first_dom', 'date_dim'.'d_last_dom', 'date_dim'.'d_same_day_ly', 'date_dim'.'d_same_day_lq', 'date_dim'.'d_current_day', 'date_dim'.'d_current_week', 'date_dim'.'d_current_month', 'date_dim'.'d_current_quarter', 'date_dim'.'d_current_year', 'customer'.'c_customer_sk', 'customer'.'c_current_cdemo_sk', 'customer'.'c_current_addr_sk'."))

---- tpcds_logical_q35 stdout ----
Error: Context("type_coercion", Internal("Optimizer rule 'type_coercion' failed due to unexpected error: Schema error: No field named 'c'.'c_customer_sk'. Valid fields are 'web_sales'.'ws_sold_date_sk', 'web_sales'.'ws_sold_time_sk', 'web_sales'.'ws_ship_date_sk', 'web_sales'.'ws_item_sk', 'web_sales'.'ws_bill_customer_sk', 'web_sales'.'ws_bill_cdemo_sk', 'web_sales'.'ws_bill_hdemo_sk', 'web_sales'.'ws_bill_addr_sk', 'web_sales'.'ws_ship_customer_sk', 'web_sales'.'ws_ship_cdemo_sk', 'web_sales'.'ws_ship_hdemo_sk', 'web_sales'.'ws_ship_addr_sk', 'web_sales'.'ws_web_page_sk', 'web_sales'.'ws_web_site_sk', 'web_sales'.'ws_ship_mode_sk', 'web_sales'.'ws_warehouse_sk', 'web_sales'.'ws_promo_sk', 'web_sales'.'ws_order_number', 'web_sales'.'ws_quantity', 'web_sales'.'ws_wholesale_cost', 'web_sales'.'ws_list_price', 'web_sales'.'ws_sales_price', 'web_sales'.'ws_ext_discount_amt', 'web_sales'.'ws_ext_sales_price', 'web_sales'.'ws_ext_wholesale_cost', 'web_sales'.'ws_ext_list_price', 'web_sales'.'ws_ext_tax', 'web_sales'.'ws_coupon_amt', 'web_sales'.'ws_ext_ship_cost', 'web_sales'.'ws_net_paid', 'web_sales'.'ws_net_paid_inc_tax', 'web_sales'.'ws_net_paid_inc_ship', 'web_sales'.'ws_net_paid_inc_ship_tax', 'web_sales'.'ws_net_profit', 'date_dim'.'d_date_sk', 'date_dim'.'d_date_id', 'date_dim'.'d_date', 'date_dim'.'d_month_seq', 'date_dim'.'d_week_seq', 'date_dim'.'d_quarter_seq', 'date_dim'.'d_year', 'date_dim'.'d_dow', 'date_dim'.'d_moy', 'date_dim'.'d_dom', 'date_dim'.'d_qoy', 'date_dim'.'d_fy_year', 'date_dim'.'d_fy_quarter_seq', 'date_dim'.'d_fy_week_seq', 'date_dim'.'d_day_name', 'date_dim'.'d_quarter_name', 'date_dim'.'d_holiday', 'date_dim'.'d_weekend', 'date_dim'.'d_following_holiday', 'date_dim'.'d_first_dom', 'date_dim'.'d_last_dom', 'date_dim'.'d_same_day_ly', 'date_dim'.'d_same_day_lq', 'date_dim'.'d_current_day', 'date_dim'.'d_current_week', 'date_dim'.'d_current_month', 'date_dim'.'d_current_quarter', 'date_dim'.'d_current_year', 'customer'.'c_customer_sk', 'customer'.'c_current_cdemo_sk', 'customer'.'c_current_addr_sk'."))

---- tpcds_logical_q41 stdout ----
Error: Context("scalar_subquery_to_join", Internal("Optimizer rule 'scalar_subquery_to_join' failed due to unexpected error: Error during planning: Optimizing disjunctions not supported! at datafusion/optimizer/src/utils.rs:243"))

---- tpcds_logical_q58 stdout ----
Error: Context("simplify_expressions", Internal("Optimizer rule 'simplify_expressions' failed due to unexpected error: Arrow error: Compute error: Overflow happened on: 100 * 100000000000000000000000000000000000000"))

---- tpcds_logical_q8 stdout ----
Error: Context("type_coercion", Internal("Optimizer rule 'type_coercion' failed due to unexpected error: Schema error: Ambiguous reference to unqualified field 'ca_zip'"))

[askoa]

@jackwener You might want to add the issue #4887 to the list.

[alamb]

I wonder how this project is going 🤔

[HaoYang670]

Hi @alamb ,I've justed run cargo test with skip_failed_rules=false, and found 2 new failures. I'll update the task list to add them in.

failures:

---- sql::subqueries::support_limit_subquery stdout ----
Error: Context("decorrelate_where_in", Internal("Optimizer rule 'decorrelate_where_in' failed due to unexpected error: a projection is required at datafusion/optimizer/src/decorrelate_where_in.rs:152\ncaused by\nError during planning: Could not coerce into Projection! at datafusion/expr/src/logical_plan/plan.rs:1430"))

---- sql::window::window_frame_creation_type_checking stdout ----
thread 'sql::window::window_frame_creation_type_checking' panicked at 'Can not find expected in actual.

Expected:
Internal error: Operator - is not implemented for types UInt32(1) and Utf8("1 DAY").

Actual:
type_coercion
caused by
Internal error: Optimizer rule 'type_coercion' failed due to unexpected error: Execution error: Cannot cast Utf8("1 DAY") to UInt32. This was likely caused by a bug in DataFusion's code and we would welcome that you file an bug report in our issue tracker', datafusion/core/tests/sql/window.rs:47:9


failures:
    sql::subqueries::support_limit_subquery
    sql::window::window_frame_creation_type_checking

[alamb]

Thank you @HaoYang670

[HaoYang670]

I've justed run cargo test with skip_failed_rules=false, and found 2 new failures. I'll update the task list to add them in.

Sorry, the window_frame_creation_type_checking doesn't fail. I made a mistake in the testing environment.
Have removed it from the task lists

[wolffcm]

I found another issue in this vein: #5996

[alamb]

Added to list

[alamb]

We have enabled checking by default in #6265 so I think we can claim this ticket is complete