[Epic] Complete pulling out special SQL planning from the Sql Parser

[alamb]

Is your feature request related to a problem or challenge?

As discussed in #10534, @jayzhan211 added a UserDefinedSQLPlanner in #11180 so that the translation of certain SQL sytanx to LogicalPlans and Exprs are not hard coded in SqlToRel but instead are controlled by a UserDefinedSQLPlanner

Now that we have the pattern, we need to move the other remaining functionality that is hard coded (e.g. looking up a function "date_part" by name) in SqlToRel to the UserDefinedSQLPlanner

Describe the solution you'd like

To rewrite with sql planner

• Implement user defined planner for extract #11215
• Implement user defined planner for date_part #11220
• Implement user defined planner for create_struct #11221
• Implement user defined planner for create_named_struct #11222
• Implement ExprPlanner for sql_overlay_to_expr #11223
• Consider renaming UserDefinedSQLPlanner to ExprPlanner #11304
• sql_position_to_expr
• sql_substring_to_expr
• Move sql_compound_identifier_to_expr to ExprPlanner #11473
• Register SQL planners in SessionState::new() #11216
• Add supporting functions for user defined sql planners to FunctionRegistry #11294
• Consolidate SQL expr planners so there are one in each module (like a UnicodePlanner rather than a PositionPlanner? #11305
• Revert "remove derive(Copy) from Operator (#11132)" #11341

Describe alternatives you've considered

No response

Additional context

Discussion is here: #10534

[samuelcolvin]

#11208 allows user defined sql planners to be defined.

[samuelcolvin]

See datafusion-contrib/datafusion-functions-json#26 - support for custom SQL operators in datafusion-functions-json using #11208.

[dharanad]

hello @alamb Just checking in on the remaining tasks. Is there anything specific we're waiting on before we create issues ?
If we're all set, i would be happy to jump in and get started to pick up few tasks.

[alamb]

hello @alamb Just checking in on the remaining tasks. Is there anything specific we're waiting on before we create issues ? If we're all set, i would be happy to jump in and get started to pick up few tasks.

Hi @dharanad I don't think there is anything from my perspective. Thank you for offering

In fact it seems as if @xinlifoobar has already started with #11215 ❤️

[dharanad]

I've created issues for a couple of tasks. Please let me know if you think anything needs updating in the descriptions. I'm new here and learning from shadowing the experienced folks

• date_part : Implement user defined planner for date_part #11220
• create_struct : Implement user defined planner for create_struct #11221
• create_named_struct : Implement user defined planner for create_named_struct #11222
• sql_overlay_to_expr : Implement ExprPlanner for sql_overlay_to_expr #11223

[alamb]

I've created issues for a couple of tasks. Please let me know if you think anything needs updating in the descriptions. I'm new here and learning from shadowing the experienced folks

thank you @dharanad -- this is very helpful 🙏

[alamb]

FWIW in general @dharanad I have had the best luck with writing a description on tickets that requires as little context as possible (aka distill down what is needed into the the description, rather than assuming the new contributor will read the epic and get all the backstory)

The rationale for this duplication is to lower the barrier to new contrbutors

[dharanad]

FWIW in general @dharanad I have had the best luck with writing a description on tickets that requires as little context as possible (aka distill down what is needed into the the description, rather than assuming the new contributor will read the epic and get all the backstory)

The rationale for this duplication is to lower the barrier to new contrbutors

Thanks for the feedback! I really appreciate. You're right, making the ticket description concise and self-contained will definitely help reduce the barrier for new contributors. I'll update the description to include the necessary context. Thanks you

[dharanad]

Create issues for the remaining tasks, tried adding a description based on my understanding of the issue. Also update the same for the older ones

• sql_compound_identifier_to_expr: Implement user defined planner for sql_compound_identifier_to_expr #11244
• sql_substring_to_expr : Implement user defined planner for sql_substring_to_expr  #11245
• sql_position_to_expr: Implement user defined planner for sql_position_to_expr #11246

[samuelcolvin]

Given how much UserDefinedSQLPlanner is being used for existing stuff within datafusion, perhaps it should be called just SQLPlanner or CustomSQLPlanner?

[alamb]

Given how much UserDefinedSQLPlanner is being used for existing stuff within datafusion, perhaps it should be called just SQLPlanner or CustomSQLPlanner?

I agree

Or maybe something like ExprPlanner 🤔 as it is being used to plan specific exprs.

[samuelcolvin]

ExprPlanner sounds good.

[xinlifoobar]

Given #11220 and #11243, those are very similar APIs with UDF plans. I am trying to draft an API, e.g.,

    // Plan the user defined function, returns origin expression arguments if not possible
    fn plan_udf(
        &self,
        _sql: &sqlparser::ast::Expr,
        args: Vec<Expr>,
    ) -> Result<PlannerResult<Vec<Expr>>> {
        Ok(PlannerResult::Original(args))
    }

to uniform the usages.

I have created a draft PR #11263 to discuss this. The flaw here is that the parameter sql is partially borrowed and has to be cloned at the very beginning. Maybe we should consider using references if possible.

[xinlifoobar]

Given #11220 and #11243, those are very similar APIs with UDF plans. I am trying to draft an API, e.g.,

    // Plan the user defined function, returns origin expression arguments if not possible
    fn plan_udf(
        &self,
        _sql: &sqlparser::ast::Expr,
        args: Vec<Expr>,
    ) -> Result<PlannerResult<Vec<Expr>>> {
        Ok(PlannerResult::Original(args))
    }

to uniform the usages.

I have created a draft PR #11263 to discuss this. The flaw here is that the parameter sql is partially borrowed and has to be cloned at the very beginning. Maybe we should consider using references if possible.

Eventually, I made this #11263, please let me know your thoughts. Thanks :)

CC @jayzhan211 @dharanad @alamb

[alamb]

ExprPlanner sounds good.

Filed #11304

[alamb]

I think we are pretty close to calling this done.

I just double checked and sql_compound_identifier_to_expr is the only thing that needs this treatment to remove the call to get_function_meta:

datafusion/datafusion/sql/src/expr/identifier.rs

Lines 138 to 139 in bfd8156

	if let Some(udf) =
	self.context_provider.get_function_meta("get_field")

That appears to be the last issue
https://github.com/search?q=repo%3Aapache%2Fdatafusion+get_function_meta+path%3A%2F%5Edatafusion%5C%2Fsql%5C%2F%2F&type=code

[alamb]

Filed #11473

[alamb]

I think we can claim we are done 🎉

thanks everyone