[jira] [Commented] (SPARK-12718) SQL generation support for window functions
[ https://issues.apache.org/jira/browse/SPARK-12718?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15198063#comment-15198063 ] Yin Huai commented on SPARK-12718: -- https://github.com/apache/spark/pull/11555 has resolved this issue. > SQL generation support for window functions > --- > > Key: SPARK-12718 > URL: https://issues.apache.org/jira/browse/SPARK-12718 > Project: Spark > Issue Type: Sub-task > Components: SQL >Affects Versions: 2.0.0 >Reporter: Cheng Lian >Assignee: Wenchen Fan > Fix For: 2.0.0 > > > {{HiveWindowFunctionQuerySuite}} and {{HiveWindowFunctionQueryFileSuite}} can > be useful for bootstrapping test coverage. Please refer to SPARK-11012 for > more details. -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-12718) SQL generation support for window functions
[ https://issues.apache.org/jira/browse/SPARK-12718?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15182676#comment-15182676 ] Wenchen Fan commented on SPARK-12718: - Hi [~smilegator], It seems that I underestimate the difficulty of this job. I have a simple PR which works fine for common cases, do you mind take a look and see what's missing? You can send out your PR to explain your approach and how to handle special cases. > SQL generation support for window functions > --- > > Key: SPARK-12718 > URL: https://issues.apache.org/jira/browse/SPARK-12718 > Project: Spark > Issue Type: Sub-task > Components: SQL >Affects Versions: 2.0.0 >Reporter: Cheng Lian >Assignee: Xiao Li > > {{HiveWindowFunctionQuerySuite}} and {{HiveWindowFunctionQueryFileSuite}} can > be useful for bootstrapping test coverage. Please refer to SPARK-11012 for > more details. -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-12718) SQL generation support for window functions
[ https://issues.apache.org/jira/browse/SPARK-12718?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15182670#comment-15182670 ] Apache Spark commented on SPARK-12718: -- User 'cloud-fan' has created a pull request for this issue: https://github.com/apache/spark/pull/11555 > SQL generation support for window functions > --- > > Key: SPARK-12718 > URL: https://issues.apache.org/jira/browse/SPARK-12718 > Project: Spark > Issue Type: Sub-task > Components: SQL >Affects Versions: 2.0.0 >Reporter: Cheng Lian >Assignee: Xiao Li > > {{HiveWindowFunctionQuerySuite}} and {{HiveWindowFunctionQueryFileSuite}} can > be useful for bootstrapping test coverage. Please refer to SPARK-11012 for > more details. -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-12718) SQL generation support for window functions
[ https://issues.apache.org/jira/browse/SPARK-12718?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15182644#comment-15182644 ] Xiao Li commented on SPARK-12718: - So far, SQL generation support for Window functions can work well. However, qualifier-related issues break a few test cases. Because RecoverScopingInfo adds extra subqueries, we need to add a new rule after the batch `Canonicalizer` to add/populate correct qualifiers for the AttributeReference. Now, I am adding this rule. Thanks! > SQL generation support for window functions > --- > > Key: SPARK-12718 > URL: https://issues.apache.org/jira/browse/SPARK-12718 > Project: Spark > Issue Type: Sub-task > Components: SQL >Affects Versions: 2.0.0 >Reporter: Cheng Lian >Assignee: Xiao Li > > {{HiveWindowFunctionQuerySuite}} and {{HiveWindowFunctionQueryFileSuite}} can > be useful for bootstrapping test coverage. Please refer to SPARK-11012 for > more details. -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-12718) SQL generation support for window functions
[ https://issues.apache.org/jira/browse/SPARK-12718?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15182584#comment-15182584 ] Xiao Li commented on SPARK-12718: - Sure, Thanks! > SQL generation support for window functions > --- > > Key: SPARK-12718 > URL: https://issues.apache.org/jira/browse/SPARK-12718 > Project: Spark > Issue Type: Sub-task > Components: SQL >Affects Versions: 2.0.0 >Reporter: Cheng Lian >Assignee: Xiao Li > > {{HiveWindowFunctionQuerySuite}} and {{HiveWindowFunctionQueryFileSuite}} can > be useful for bootstrapping test coverage. Please refer to SPARK-11012 for > more details. -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-12718) SQL generation support for window functions
[ https://issues.apache.org/jira/browse/SPARK-12718?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15182583#comment-15182583 ] Wenchen Fan commented on SPARK-12718: - Then finish it, we can consolidate them later. > SQL generation support for window functions > --- > > Key: SPARK-12718 > URL: https://issues.apache.org/jira/browse/SPARK-12718 > Project: Spark > Issue Type: Sub-task > Components: SQL >Affects Versions: 2.0.0 >Reporter: Cheng Lian >Assignee: Xiao Li > > {{HiveWindowFunctionQuerySuite}} and {{HiveWindowFunctionQueryFileSuite}} can > be useful for bootstrapping test coverage. Please refer to SPARK-11012 for > more details. -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-12718) SQL generation support for window functions
[ https://issues.apache.org/jira/browse/SPARK-12718?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15182578#comment-15182578 ] Xiao Li commented on SPARK-12718: - Hi, [~cloud_fan] Yeah. Almost done. Just let me know if I should continue it. Thanks! > SQL generation support for window functions > --- > > Key: SPARK-12718 > URL: https://issues.apache.org/jira/browse/SPARK-12718 > Project: Spark > Issue Type: Sub-task > Components: SQL >Affects Versions: 2.0.0 >Reporter: Cheng Lian >Assignee: Xiao Li > > {{HiveWindowFunctionQuerySuite}} and {{HiveWindowFunctionQueryFileSuite}} can > be useful for bootstrapping test coverage. Please refer to SPARK-11012 for > more details. -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-12718) SQL generation support for window functions
[ https://issues.apache.org/jira/browse/SPARK-12718?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15182496#comment-15182496 ] Wenchen Fan commented on SPARK-12718: - Hi, [~xiaol], are you still working on it? I was working on it before and forgot to update this JIRA... > SQL generation support for window functions > --- > > Key: SPARK-12718 > URL: https://issues.apache.org/jira/browse/SPARK-12718 > Project: Spark > Issue Type: Sub-task > Components: SQL >Affects Versions: 2.0.0 >Reporter: Cheng Lian >Assignee: Xiao Li > > {{HiveWindowFunctionQuerySuite}} and {{HiveWindowFunctionQueryFileSuite}} can > be useful for bootstrapping test coverage. Please refer to SPARK-11012 for > more details. -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-12718) SQL generation support for window functions
[ https://issues.apache.org/jira/browse/SPARK-12718?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15182445#comment-15182445 ] Xiao Li commented on SPARK-12718: - In Window Spec, the possible inputs are: 1. partition by + order by, 2. order by 3. distribute by + sort by 4. sort by 5. cluster by > SQL generation support for window functions > --- > > Key: SPARK-12718 > URL: https://issues.apache.org/jira/browse/SPARK-12718 > Project: Spark > Issue Type: Sub-task > Components: SQL >Affects Versions: 2.0.0 >Reporter: Cheng Lian >Assignee: Xiao Li > > {{HiveWindowFunctionQuerySuite}} and {{HiveWindowFunctionQueryFileSuite}} can > be useful for bootstrapping test coverage. Please refer to SPARK-11012 for > more details. -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-12718) SQL generation support for window functions
[ https://issues.apache.org/jira/browse/SPARK-12718?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15182441#comment-15182441 ] Xiao Li commented on SPARK-12718: - If users use cluster by clauses, or DISTRIBUTE BY + SORT BY clauses, the generated SQL will convert them to Partition By + Order By. > SQL generation support for window functions > --- > > Key: SPARK-12718 > URL: https://issues.apache.org/jira/browse/SPARK-12718 > Project: Spark > Issue Type: Sub-task > Components: SQL >Affects Versions: 2.0.0 >Reporter: Cheng Lian >Assignee: Xiao Li > > {{HiveWindowFunctionQuerySuite}} and {{HiveWindowFunctionQueryFileSuite}} can > be useful for bootstrapping test coverage. Please refer to SPARK-11012 for > more details. -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-12718) SQL generation support for window functions
[ https://issues.apache.org/jira/browse/SPARK-12718?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15182439#comment-15182439 ] Xiao Li commented on SPARK-12718: - Will not add extra subquery here. Trying to rebuild the original Window SQL > SQL generation support for window functions > --- > > Key: SPARK-12718 > URL: https://issues.apache.org/jira/browse/SPARK-12718 > Project: Spark > Issue Type: Sub-task > Components: SQL >Affects Versions: 2.0.0 >Reporter: Cheng Lian >Assignee: Xiao Li > > {{HiveWindowFunctionQuerySuite}} and {{HiveWindowFunctionQueryFileSuite}} can > be useful for bootstrapping test coverage. Please refer to SPARK-11012 for > more details. -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-12718) SQL generation support for window functions
[ https://issues.apache.org/jira/browse/SPARK-12718?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15181973#comment-15181973 ] Xiao Li commented on SPARK-12718: - Just confirmed that no optimizer rule needs to be added. {code} SELECT t.p_mfgr, t.p_name, t.p_size, t.dr, rank() OVER (distribute BY p_mfgr sort BY p_name, p_mfgr) AS r FROM( SELECT p_mfgr, p_name, p_size, dense_rank() OVER (distribute BY p_mfgr sort BY p_name) AS dr FROMpart) t {code} {code} == Analyzed Logical Plan == p_mfgr: string, p_name: string, p_size: int, dr: int, r: int Project [p_mfgr#60,p_name#59,p_size#63,dr#28,r#29] +- Project [p_mfgr#60,p_name#59,p_size#63,dr#28,r#29,r#29] +- Window [p_mfgr#60,p_name#59,p_size#63,dr#28], [rank(p_name#59, p_mfgr#60) windowspecdefinition(p_mfgr#60, p_name#59 ASC, p_mfgr#60 ASC, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS r#29], [p_mfgr#60], [p_name#59 ASC,p_mfgr#60 ASC] +- Project [p_mfgr#60,p_name#59,p_size#63,dr#28] +- SubqueryAlias t +- Project [p_mfgr#60,p_name#59,p_size#63,dr#28] +- Project [p_mfgr#60,p_name#59,p_size#63,dr#28,dr#28] +- Window [p_mfgr#60,p_name#59,p_size#63], [denserank(p_name#59) windowspecdefinition(p_mfgr#60, p_name#59 ASC, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS dr#28], [p_mfgr#60], [p_name#59 ASC] +- Project [p_mfgr#60,p_name#59,p_size#63] +- MetastoreRelation default, part, None {code} {code} == Optimized Logical Plan == Window [p_mfgr#60,p_name#59,p_size#63,dr#28], [rank(p_name#59, p_mfgr#60) windowspecdefinition(p_mfgr#60, p_name#59 ASC, p_mfgr#60 ASC, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS r#29], [p_mfgr#60], [p_name#59 ASC,p_mfgr#60 ASC] +- Window [p_mfgr#60,p_name#59,p_size#63], [denserank(p_name#59) windowspecdefinition(p_mfgr#60, p_name#59 ASC, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS dr#28], [p_mfgr#60], [p_name#59 ASC] +- Project [p_mfgr#60,p_name#59,p_size#63] +- MetastoreRelation default, part, None {code} > SQL generation support for window functions > --- > > Key: SPARK-12718 > URL: https://issues.apache.org/jira/browse/SPARK-12718 > Project: Spark > Issue Type: Sub-task > Components: SQL >Affects Versions: 2.0.0 >Reporter: Cheng Lian >Assignee: Xiao Li > > {{HiveWindowFunctionQuerySuite}} and {{HiveWindowFunctionQueryFileSuite}} can > be useful for bootstrapping test coverage. Please refer to SPARK-11012 for > more details. -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-12718) SQL generation support for window functions
[ https://issues.apache.org/jira/browse/SPARK-12718?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15181967#comment-15181967 ] Xiao Li commented on SPARK-12718: - select key, value, avg(c_int) over (partition by key), sum(c_float) over(partition by value) from t1 -> select key, value, avg(c_int), t2._w0 over (partition by key) from (select key, value, sum(c_float) over(partition by value) as _w0 from t1 where value < 10) t2 When window specifications are different, we will split the whole one to multiple. However, to do it, we need to have the corresponding optimizer rule to combine them back. Let me check if we need to add a rule. In addition, I plan to add the predicate pushdown for window into Optimizer first. This is still missing now. I also found Hive just added one. Thanks! > SQL generation support for window functions > --- > > Key: SPARK-12718 > URL: https://issues.apache.org/jira/browse/SPARK-12718 > Project: Spark > Issue Type: Sub-task > Components: SQL >Affects Versions: 2.0.0 >Reporter: Cheng Lian >Assignee: Xiao Li > > {{HiveWindowFunctionQuerySuite}} and {{HiveWindowFunctionQueryFileSuite}} can > be useful for bootstrapping test coverage. Please refer to SPARK-11012 for > more details. -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org