You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by GitBox <gi...@apache.org> on 2021/08/28 09:04:59 UTC
[GitHub] [arrow-datafusion] waynexia opened a new pull request #952: Change compound column field name rules
waynexia opened a new pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952
# Which issue does this PR close?
<!--
We generally require a GitHub issue to be filed for all bug fixes and enhancements and this helps us generate change logs for our releases. You can link an issue to this PR using the GitHub syntax. For example `Closes #123` indicates that this PR will close issue #123.
-->
Closes #.
# Rationale for this change
<!--
Why are you proposing this change? If this is already explained clearly in the issue then this section is not needed.
Explaining clearly why changes are proposed helps reviewers understand your changes and offer better suggestions for fixes.
-->
Origin in #792, some discussions here: https://github.com/apache/arrow-datafusion/pull/792#issuecomment-902936271
In the current implementation and spec., all field names don't contain the table or relation name. It might be better to hold those qualifiers in compound columns resulting from projection with calculating expr (`select t1 + 1`) or aggr expr (`sum(t2)`) etc. This can improve readability by figuring out where a column is from. And also in some parts of inner processing (like the logical plan phase), we use this qualified form to identify a column.
# What changes are included in this PR?
<!--
There is no need to duplicate the description in the issue here but it is sometimes worth providing a summary of the individual changes in this PR.
-->
This PR changes the rule of field name generation. For now, all compound columns' (columns with other outer expressions) field names will be qualified form.
# Are there any user-facing changes?
<!--
If there are user-facing changes then we may require documentation to be updated before approving the PR.
-->
<!--
If there are any breaking changes to public APIs, please add the `api change` label.
-->
Yes.
Compound columns' field name will now contain table / relation name, like `AVG(table.c1)`.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] houqp commented on pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
houqp commented on pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952#issuecomment-907638442
Thanks @waynexia for the PR, I am on vacation this week, but I will try to find some time to review it today or tomorrow.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] jorgecarleitao commented on pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
jorgecarleitao commented on pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952#issuecomment-907598940
Thanks for the proposal.
imo it is a bit counter-intuitive to write `SELECT MIN(c1) FROM table1` and get `MIN(table.c1)` as the schema. Are there other engine doing this?
I am curious how this interacts with the dataframe API. E.g. (pseudo-code)
```rust
df = ctx.create_in_memory_table("table1", batches)?;
df = df.agg(sum(df["c1"]))?;
df.collect()
```
would yield the schema `"SUM(table1.c1)"` or `"SUM(c1)"`? Would I need to write `df = df.agg(sum(df["table.c1"]))?;` instead?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] waynexia commented on pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
waynexia commented on pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952#issuecomment-908385954
Thanks @alamb and @houqp for reviewing! I pushed a commit to address the comments.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] houqp merged pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
houqp merged pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] waynexia commented on pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
waynexia commented on pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952#issuecomment-908385954
Thanks @alamb and @houqp for reviewing! I pushed a commit to address the comments.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] waynexia commented on a change in pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
waynexia commented on a change in pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952#discussion_r698532728
##########
File path: datafusion/src/physical_plan/planner.rs
##########
@@ -76,15 +75,25 @@ fn create_function_physical_name(
Ok(format!("{}({}{})", fun, distinct_str, names.join(",")))
}
-fn physical_name(e: &Expr, input_schema: &DFSchema) -> Result<String> {
+fn physical_name(e: &Expr) -> Result<String> {
+ create_physical_name(e, true)
+}
+
+fn create_physical_name(e: &Expr, is_first_expr: bool) -> Result<String> {
Review comment:
I picked the first one and explained how it is used in the doc comment
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] waynexia commented on a change in pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
waynexia commented on a change in pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952#discussion_r698536194
##########
File path: datafusion/src/physical_plan/planner.rs
##########
@@ -76,15 +75,25 @@ fn create_function_physical_name(
Ok(format!("{}({}{})", fun, distinct_str, names.join(",")))
}
-fn physical_name(e: &Expr, input_schema: &DFSchema) -> Result<String> {
+fn physical_name(e: &Expr) -> Result<String> {
Review comment:
It's a good point to remove the wrapper. `physical_name()` is an interior helper function and adding that argument won't be too verbose.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] houqp edited a comment on pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
houqp edited a comment on pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952#issuecomment-908069027
Our current output field name semantics mostly aligns with what spark has, which strips column qualifiers in all cases.
This PR changes the semantics to handle compound and bare column names differently. For bare names, we strip column qualifiers, but not for compound columns.
Unlike Spark, relational databases like MySQL, Posgresql and Sqlite all treat compound and bare column names differently. MySQL, Postgresql and Sqlite strip qualifers for bare column names like we do. But MySQL and Sqlite use raw user query input for compound column names. Postgresql on the other hand just uses `?column?` for all compound column names.
In all compute engines, users are not expected to reference compound columns by generated names because these names are not guaranteed to be valid sql expressions. Instead, they should always manually alias them. As a result, the output filed name for compound columns are just there for display/debug purpose only. See also @Dandandan 's comment at https://github.com/apache/arrow-datafusion/pull/280#issuecomment-834805975.
@jorgecarleitao as for the counter-intuitive example you mentioned, the current implementation will output field name `SUM(id)` column name for query `SELECT SUM(t1.id)`, while the proposed new behavior will output `SUM(t1.id)` field name for query `SELECT SUM(id)`. So both of them will not use the exact user query input as the output field name. Either way, it should have no impact to how users construct queries.
The proposed new behavior provides better UX compared to Postgresql's `?column?` column name. I think it's also an improvement over the current (spark's) behavior because it will produce an unambiguous column name for queries like `SELECT t1.id * t2.id FROM t1 JOIN t2 USING id`. The current behavior will output `id * id`, which is not as good as `t1.id * t2.id` IMO.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
alamb commented on pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952#issuecomment-916819000
Thanks @waynexia !
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] houqp commented on pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
houqp commented on pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952#issuecomment-908069027
Our current output field name semantics mostly aligns with what spark has, which strips column qualifiers in all cases.
This PR changes the semantics to handle compound and bare column names differently. For bare names, we strip column qualifiers, but not for compound columns.
Unlike Spark, relational databases like MySQL, Posgresql and Sqlite all treat compound and bare column names differently. MySQL, Postgresql and Sqlite strip qualifers for bare column names like we do. But MySQL and Sqlite use raw user query input for compound column names. Postgresql on the other hand just uses `?column?` for all compound column names.
In all compute engines, users are not expected to reference compound columns by generated names because these names are not guaranteed to be valid sql expressions. Instead, they should always manually alias them. As a result, the output filed name for compound columns are just there for display/debug purpose only. See also @Dandandan 's comment at https://github.com/apache/arrow-datafusion/pull/280#issuecomment-834805975.
@jorgecarleitao as for the counter-intuitive example you mentioned, the current implementation will output field name `SUM(id)` column name for query `SELECT SUM(t1.id)`, while the proposed new behavior will output `SUM(t1.id)` field name for query `SELECT SUM(id)`. So both of them will not use the exact user query input as the output field name. Either way, it should have no impact to how users construct queries.
The proposed new behavior provides better UX compared to Postgresql's `?column?` column name. I think it's also an improvement over the current behavior because it will produce an unambiguous column name for queries like `SELECT t1.id * t2.id FROM t1 JOIN t2 USING id`. The current behavior will output `id * id`, which is not as good as `t1.id * t2.id` IMO.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] waynexia commented on pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
waynexia commented on pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952#issuecomment-907602518
Thanks for your question @jorgecarleitao.
> imo it is a bit counter-intuitive to write SELECT MIN(c1) FROM table1 and get MIN(table.c1) as the schema. Are there other engine doing this?
Not that I know of for now. There are some other engines' behavior [here](https://github.com/apache/arrow-datafusion/blob/master/docs/specification/output-field-name-semantic.md#function-transformed-columns). This proposal does change a lot. From eliminating all qualifiers to bring all to those composite expressions.
> would yield the schema "SUM(table1.c1)" or "SUM(c1)"
It will be `SUM(table1.c1)`. The way of interacting with dataframe doesn't change. Only the result field names are different. There is one [example](https://github.com/apache/arrow-datafusion/pull/952/files#diff-1ca1a4d1e2c60e21a139b211b92f05889ae035c2fa9aa87bc878c32f26606fabR262-R291) in `execution/dataframe_impl.rs`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] waynexia commented on pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
waynexia commented on pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952#issuecomment-907598056
Hi @houqp, could you please take a look at this?
And according to the PR template, this PR should be labeled as "API change". But I can't add the label myself.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] waynexia commented on a change in pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
waynexia commented on a change in pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952#discussion_r698532728
##########
File path: datafusion/src/physical_plan/planner.rs
##########
@@ -76,15 +75,25 @@ fn create_function_physical_name(
Ok(format!("{}({}{})", fun, distinct_str, names.join(",")))
}
-fn physical_name(e: &Expr, input_schema: &DFSchema) -> Result<String> {
+fn physical_name(e: &Expr) -> Result<String> {
+ create_physical_name(e, true)
+}
+
+fn create_physical_name(e: &Expr, is_first_expr: bool) -> Result<String> {
Review comment:
I picked the first one and explained how it is used in the doc comment
##########
File path: datafusion/src/physical_plan/planner.rs
##########
@@ -76,15 +75,25 @@ fn create_function_physical_name(
Ok(format!("{}({}{})", fun, distinct_str, names.join(",")))
}
-fn physical_name(e: &Expr, input_schema: &DFSchema) -> Result<String> {
+fn physical_name(e: &Expr) -> Result<String> {
Review comment:
It's a good point to remove the wrapper. `physical_name()` is an interior helper function and adding that argument won't be too verbose.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] houqp merged pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
houqp merged pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] houqp commented on a change in pull request #952: Change compound column field name rules
Posted by GitBox <gi...@apache.org>.
houqp commented on a change in pull request #952:
URL: https://github.com/apache/arrow-datafusion/pull/952#discussion_r698152370
##########
File path: datafusion/src/physical_plan/planner.rs
##########
@@ -76,15 +75,25 @@ fn create_function_physical_name(
Ok(format!("{}({}{})", fun, distinct_str, names.join(",")))
}
-fn physical_name(e: &Expr, input_schema: &DFSchema) -> Result<String> {
+fn physical_name(e: &Expr) -> Result<String> {
+ create_physical_name(e, true)
+}
+
+fn create_physical_name(e: &Expr, is_first_expr: bool) -> Result<String> {
Review comment:
nitpick, couple other naming suggestions for the `is_first_expr` argument:
* is_root_expr
* strip_qualifer
* strip_relation
##########
File path: datafusion/src/physical_plan/planner.rs
##########
@@ -76,15 +75,25 @@ fn create_function_physical_name(
Ok(format!("{}({}{})", fun, distinct_str, names.join(",")))
}
-fn physical_name(e: &Expr, input_schema: &DFSchema) -> Result<String> {
+fn physical_name(e: &Expr) -> Result<String> {
Review comment:
nitpick, by just looking at the function names, it's not easy to tell the difference between `physical_name` and `create_physical_name`. Perhaps rename `physical_name` to `root_physical_name`?
Alternatively, we could get rid of the wrapper, then use `physical_name` and explicitly pass in the `is_first_expr` argument everywhere.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org