You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Yeah I was kinda wondering about this too last time (#9726 (comment)).
I was thinking we don't change the SQL parser too.
Instead, I think given that the "sources" of the conversion to Expr is not only ast::Expr objects, having its return type be only ast::Expr is perhaps too restrictive? As we've seen in this case. If I understand correctly, I think another case is Expr::AggregateFunction, where we can't really convert it back to ast::Function as it's also not an enum item in ast::Expr.
So what if we extend the return type of expr_to_sql to more than ast::Expr? I think it makes sense to extend it to all the "sources" of conversions from ast::* to Expr.
For example in here, we know that we are converting a ast::OrderByExpr to a Expr::Sort, so I think it makes sense that ast::OrderByExpr should also be a valid return type for expr_to_sql, as that is really the "source" data model of Expr::Sort.
when turning the expression back into an entire query
Yeah, I was just a little curious why for Expr::Sort we ignore the ORDER BY information. It seems that for the entire query, it could address the Sort.
Maybe the code would be clearer if we made the conversion code return an Error if it encountered a SortExpr in an unexpected location 🤔
Yeah, so if we encountered a SortExpr in expr_to_sql function, does that mean unexpected location ? I think for a entire query case, the code will probably never reach that.
I know a use case would be just convert a Expr to sql. But col("a").sort(true, true) will be only "a", which doesn't match the sql semantic? 🤔
Yeah, so if we encountered a SortExpr in expr_to_sql function, does that mean unexpected location ? I think for a entire query case, the code will probably never reach that.
I think so
But col("a").sort(true, true) will be only "a", which doesn't match the sql semantic? 🤔
Creating SQL from programatically created Exprs is an interesting usecase.
it seems like the issue is that Expr in datafusion can come from both ast::Expr and ast::OrderByExpr
Maybe we could change the signature to something like
/// DataFusion's Exprs can represent either an `Expr` or an `OrderByExpr`enumUnparsed{// SQL ExpressionExpr(ast::Expr),// SQL ORDER BY expression (e.g. `col ASC NULLS FIRST`)OrderByExpr(ast::OrderByExpr),}pubfnexpr_to_sql(expr:&Expr) -> Result<Unparsed>{
...}
Good, I can try to implement it. :)
Another concern for me is changing the function signature to pub fn expr_to_sql(expr: &Expr) -> Result<Unparsed> would lead every call of expr_to_sql a convert to ast::Expr in the original code, would this be a good choice? 🤔
For example, There would be something like
let l = self.expr_to_sql(left.as_ref())?;
let expr = match l {
Unparsed::Expr(expr) => expr,
Unparsed::OrderByExpr(_) => internal_err(),
};
/// DataFusion's Exprs can represent either an `Expr` or an `OrderByExpr`enumUnparsed{// SQL ExpressionExpr(ast::Expr),// SQL ORDER BY expression (e.g. `col ASC NULLS FIRST`)OrderByExpr(ast::OrderByExpr),}/// Throws an error if Expr can not be represented by an `ast::Expr` pubfnexpr_to_sql(expr:&Expr) -> Result<sql::ast::Expr>{
...}/// Returns an `Unparsed` based on the expressionpubfnexpr_to_unparsed(expr:&Expr) -> Result<Unparsed>{
...}
And that way you example would be
let expr = self.expr_to_sql(left.as_ref())?;
In places where order by may be possible then the user could call the self.expr_to_unparsed 🤔
Activity
yyy1000 commentedon Apr 26, 2024
@kevinmingtarja @alamb What do you think? 🤔
alamb commentedon Apr 27, 2024
I don't think we should change SQL parser
In general,
ORDER BY ...isn't really a general Expr (it can't appear in arbitrary locations in SQL, only specific ones)For example, this isn't valid;
ORDER BY can appear in certain places like
I think we should special case finding
Expr::Sortexprs in those locations.Maybe the code would be clearer if we made the conversion code return an Error if it encountered a SortExpr in an unexpected location 🤔
kevinmingtarja commentedon Apr 27, 2024
Yeah I was kinda wondering about this too last time (#9726 (comment)).
I was thinking we don't change the SQL parser too.
Instead, I think given that the "sources" of the conversion to
Expris not onlyast::Exprobjects, having its return type be onlyast::Expris perhaps too restrictive? As we've seen in this case. If I understand correctly, I think another case isExpr::AggregateFunction, where we can't really convert it back to ast::Function as it's also not an enum item inast::Expr.So what if we extend the return type of
expr_to_sqlto more thanast::Expr? I think it makes sense to extend it to all the "sources" of conversions fromast::*toExpr.For example in here, we know that we are converting a
ast::OrderByExprto aExpr::Sort, so I think it makes sense thatast::OrderByExprshould also be a valid return type forexpr_to_sql, as that is really the "source" data model ofExpr::Sort.datafusion/datafusion/sql/src/expr/order_by.rs
Lines 24 to 36 in f8c623f
yyy1000 commentedon Apr 27, 2024
Thank you all for your reply!
Yeah, I was just a little curious why for Expr::Sort we ignore the ORDER BY information. It seems that for the entire query, it could address the Sort.
datafusion/datafusion/sql/src/unparser/plan.rs
Lines 222 to 231 in f8c623f
Yeah, so if we encountered a
SortExprinexpr_to_sqlfunction, does that mean unexpected location ? I think for a entire query case, the code will probably never reach that.I know a use case would be just convert a
Exprto sql. Butcol("a").sort(true, true)will be only "a", which doesn't match the sql semantic? 🤔ast::Function is an enum item in ast::Expr, see
https://github.com/sqlparser-rs/sqlparser-rs/blob/0b5722afbfb60c3e3a354bc7c7dc02cb7803b807/src/ast/mod.rs#L683-L684
So maybe the return type of expr_to_sql being ast::Expr is enough, given the
Sortwill be addressed in a special way?alamb commentedon Apr 30, 2024
I think so
Creating SQL from programatically created
Exprs is an interesting usecase.it seems like the issue is that
Exprin datafusion can come from bothast::Exprandast::OrderByExprCurrently we have
datafusion/datafusion/sql/src/unparser/expr.rs
Lines 47 to 50 in dd56837
Maybe we could change the signature to something like
yyy1000 commentedon May 3, 2024
Good, I can try to implement it. :)
Another concern for me is changing the function signature to
pub fn expr_to_sql(expr: &Expr) -> Result<Unparsed>would lead every call ofexpr_to_sqla convert to ast::Expr in the original code, would this be a good choice? 🤔For example, There would be something like
alamb commentedon May 3, 2024
Maybe we could make the API easier to use like:
And that way you example would be
In places where order by may be possible then the user could call the
self.expr_to_unparsed🤔yyy1000 commentedon May 3, 2024
That looks better! Will try to implement it soon.