You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by "wangyum (via GitHub)" <gi...@apache.org> on 2023/01/21 16:14:15 UTC
[GitHub] [spark] wangyum opened a new pull request, #39691: [SPARK-31561][SQL] Add QUALIFY clause
wangyum opened a new pull request, #39691:
URL: https://github.com/apache/spark/pull/39691
### What changes were proposed in this pull request?
The `QUALIFY` clause is used to filter the results of [window functions](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-window.html). For example:
```sql
-- QUALIFY with window functions in the SELECT list.
SELECT
city,
car_model,
RANK() OVER (PARTITION BY car_model ORDER BY quantity) AS rank
FROM dealer
QUALIFY rank = 1;
-- QUALIFY with window functions in the QUALIFY clause.
SELECT city, car_model
FROM dealer
QUALIFY RANK() OVER (PARTITION BY car_model ORDER BY quantity) = 1;
+-----------+---------------+
| city | car_model |
+-----------+---------------+
| San Jose | Honda Accord |
| Dublin | Honda CRV |
| San Jose | Honda Civic |
+-----------+---------------+
```
### Why are the changes needed?
To easily migrate SQL queries to Spark SQL.
https://docs.databricks.com/sql/language-manual/sql-ref-syntax-qry-select-qualify.html
https://docs.snowflake.com/en/sql-reference/constructs/qualify.html
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause
https://duckdb.org/docs/sql/query_syntax/qualify.html
https://docs.teradata.com/reader/2_MC9vCtAJRlKle2Rpb0mA/19NnI91neorAi7LX6SJXBw
### Does this PR introduce _any_ user-facing change?
No.
### How was this patch tested?
Unit test.
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] wangyum commented on a diff in pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "wangyum (via GitHub)" <gi...@apache.org>.
wangyum commented on code in PR #39691:
URL: https://github.com/apache/spark/pull/39691#discussion_r1083632796
##########
sql/core/src/test/resources/sql-tests/results/window.sql.out:
##########
@@ -1342,3 +1342,139 @@ org.apache.spark.sql.AnalysisException
"windowName" : "w"
}
}
+
+
+-- !query
+SELECT val_long,
+ val_date,
+ max(val) OVER (partition BY val_date) AS m
+FROM testdata
+WHERE val_long > 2
+QUALIFY m > 2 AND m < 10
+-- !query schema
+struct<val_long:bigint,val_date:date,m:int>
+-- !query output
+2147483650 2020-12-31 3
+2147483650 2020-12-31 3
+
+
+-- !query
+SELECT val_long,
+ val_date,
+ val
+FROM testdata QUALIFY max(val) OVER (partition BY val_date) >= 3
+-- !query schema
+struct<val_long:bigint,val_date:date,val:int>
+-- !query output
+1 2017-08-01 1
+1 2017-08-01 3
+1 2017-08-01 NULL
+2147483650 2020-12-31 2
+2147483650 2020-12-31 3
+NULL 2017-08-01 1
+
+
+-- !query
+SELECT val_date,
+ val * sum(val) OVER (partition BY val_date) AS w
+FROM testdata
+QUALIFY w > 10
+-- !query schema
+struct<val_date:date,w:bigint>
+-- !query output
+2017-08-01 15
+2020-12-31 15
+
+
+-- !query
+SELECT w.val_date
+FROM testdata w
+JOIN testdata w2 ON w.val_date=w2.val_date
+QUALIFY row_number() OVER (partition BY w.val_date ORDER BY w.val) IN (2)
+-- !query schema
+struct<val_date:date>
+-- !query output
+2017-08-01
+2020-12-31
+
+
+-- !query
+SELECT val_date,
+ count(val_long) OVER (partition BY val_date) AS w
+FROM testdata
+GROUP BY val_date,
+ val_long
+HAVING Sum(val) > 1
+QUALIFY w = 1
+-- !query schema
+struct<val_date:date,w:bigint>
+-- !query output
+2017-08-01 1
+2017-08-03 1
+2020-12-31 1
+
+
+-- !query
+SELECT val_date,
+ val_long,
+ Sum(val)
+FROM testdata
+GROUP BY val_date,
+ val_long
+HAVING Sum(val) > 1
+QUALIFY count(val_long) OVER (partition BY val_date) IN(SELECT 1)
+-- !query schema
+struct<val_date:date,val_long:bigint,sum(val):bigint>
+-- !query output
+2017-08-01 1 4
+2017-08-03 3 2
+2020-12-31 2147483650 5
+
+
+-- !query
+SELECT val_date,
+ val_long
+FROM testdata
+QUALIFY count(val_long) OVER (partition BY val_date) > 1 AND val > 1
+-- !query schema
+struct<val_date:date,val_long:bigint>
+-- !query output
+2017-08-01 1
+2020-12-31 2147483650
+2020-12-31 2147483650
+
+
+-- !query
+SELECT val_date,
+ val_long
+FROM testdata
+QUALIFY val > 1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1032",
Review Comment:
+1 for follow-up.
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-31561][SQL] Add QUALIFY clause [spark]
Posted by "jboarman (via GitHub)" <gi...@apache.org>.
jboarman commented on PR #39691:
URL: https://github.com/apache/spark/pull/39691#issuecomment-2128159029
This is sad to see this PR being closed. What is needed to raise the priority on this issue?
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-31561][SQL] Add QUALIFY clause [spark]
Posted by "kjstultz14 (via GitHub)" <gi...@apache.org>.
kjstultz14 commented on PR #39691:
URL: https://github.com/apache/spark/pull/39691#issuecomment-2792550749
Would love to have this one re-opened. I'm probably not alone in trying to migrate code from other sources to using Databricks/Spark, and having this functionality would help quite a bit with the process.
@wangyum
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] wangyum commented on a diff in pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "wangyum (via GitHub)" <gi...@apache.org>.
wangyum commented on code in PR #39691:
URL: https://github.com/apache/spark/pull/39691#discussion_r1131138491
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryCompilationErrors.scala:
##########
@@ -571,7 +571,7 @@ private[sql] object QueryCompilationErrors extends QueryErrorsBase {
messageParameters = Map.empty)
}
- def expressionWithoutWindowExpressionError(expr: NamedExpression): Throwable = {
+ def expressionWithoutWindowExpressionError(expr: Expression): Throwable = {
Review Comment:
The new message:
```
[QUALIFY_EXPRESSION_MUST_CONTAIN_WINDOW_FUNCTION] QUALIFY expression '(id > 1)' must contain a window function.; line 1 pos 21
```
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] itholic commented on a diff in pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "itholic (via GitHub)" <gi...@apache.org>.
itholic commented on code in PR #39691:
URL: https://github.com/apache/spark/pull/39691#discussion_r1083665941
##########
sql/core/src/test/resources/sql-tests/results/window.sql.out:
##########
@@ -1342,3 +1342,139 @@ org.apache.spark.sql.AnalysisException
"windowName" : "w"
}
}
+
+
+-- !query
+SELECT val_long,
+ val_date,
+ max(val) OVER (partition BY val_date) AS m
+FROM testdata
+WHERE val_long > 2
+QUALIFY m > 2 AND m < 10
+-- !query schema
+struct<val_long:bigint,val_date:date,m:int>
+-- !query output
+2147483650 2020-12-31 3
+2147483650 2020-12-31 3
+
+
+-- !query
+SELECT val_long,
+ val_date,
+ val
+FROM testdata QUALIFY max(val) OVER (partition BY val_date) >= 3
+-- !query schema
+struct<val_long:bigint,val_date:date,val:int>
+-- !query output
+1 2017-08-01 1
+1 2017-08-01 3
+1 2017-08-01 NULL
+2147483650 2020-12-31 2
+2147483650 2020-12-31 3
+NULL 2017-08-01 1
+
+
+-- !query
+SELECT val_date,
+ val * sum(val) OVER (partition BY val_date) AS w
+FROM testdata
+QUALIFY w > 10
+-- !query schema
+struct<val_date:date,w:bigint>
+-- !query output
+2017-08-01 15
+2020-12-31 15
+
+
+-- !query
+SELECT w.val_date
+FROM testdata w
+JOIN testdata w2 ON w.val_date=w2.val_date
+QUALIFY row_number() OVER (partition BY w.val_date ORDER BY w.val) IN (2)
+-- !query schema
+struct<val_date:date>
+-- !query output
+2017-08-01
+2020-12-31
+
+
+-- !query
+SELECT val_date,
+ count(val_long) OVER (partition BY val_date) AS w
+FROM testdata
+GROUP BY val_date,
+ val_long
+HAVING Sum(val) > 1
+QUALIFY w = 1
+-- !query schema
+struct<val_date:date,w:bigint>
+-- !query output
+2017-08-01 1
+2017-08-03 1
+2020-12-31 1
+
+
+-- !query
+SELECT val_date,
+ val_long,
+ Sum(val)
+FROM testdata
+GROUP BY val_date,
+ val_long
+HAVING Sum(val) > 1
+QUALIFY count(val_long) OVER (partition BY val_date) IN(SELECT 1)
+-- !query schema
+struct<val_date:date,val_long:bigint,sum(val):bigint>
+-- !query output
+2017-08-01 1 4
+2017-08-03 3 2
+2020-12-31 2147483650 5
+
+
+-- !query
+SELECT val_date,
+ val_long
+FROM testdata
+QUALIFY count(val_long) OVER (partition BY val_date) > 1 AND val > 1
+-- !query schema
+struct<val_date:date,val_long:bigint>
+-- !query output
+2017-08-01 1
+2020-12-31 2147483650
+2020-12-31 2147483650
+
+
+-- !query
+SELECT val_date,
+ val_long
+FROM testdata
+QUALIFY val > 1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1032",
Review Comment:
Sure! Will follow-up after merging this PR jus in case to avoid unexpected conflicts.
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] wangyum commented on pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "wangyum (via GitHub)" <gi...@apache.org>.
wangyum commented on PR #39691:
URL: https://github.com/apache/spark/pull/39691#issuecomment-1443328844
CALCITE added support for it too: https://issues.apache.org/jira/browse/CALCITE-5268.
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] wangyum commented on pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "wangyum (via GitHub)" <gi...@apache.org>.
wangyum commented on PR #39691:
URL: https://github.com/apache/spark/pull/39691#issuecomment-1399282308
cc @xinrong-meng @MaxGekk @gengliangwang @cloud-fan
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] wangyum commented on a diff in pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "wangyum (via GitHub)" <gi...@apache.org>.
wangyum commented on code in PR #39691:
URL: https://github.com/apache/spark/pull/39691#discussion_r1083632654
##########
docs/sql-ref-syntax-qry-select-qualify.md:
##########
@@ -0,0 +1,98 @@
+---
+layout: global
+title: QUALIFY Clause
+displayTitle: QUALIFY Clause
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+### Description
+
+The `QUALIFY` clause is used to filter the results of
+[window functions](sql-ref-syntax-qry-select-window.md). To use QUALIFY,
+at least one window function is required to be present in the SELECT list or the QUALIFY clause.
Review Comment:
OK.
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-31561][SQL] Add QUALIFY clause [spark]
Posted by "ebonnal (via GitHub)" <gi...@apache.org>.
ebonnal commented on PR #39691:
URL: https://github.com/apache/spark/pull/39691#issuecomment-2719284145
Any plans to finalize this work? The review doesn’t seem to highlight any major blockers, right? Was it abandoned following an internal discussion?
cc @wangyum 🙏🏻
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] wangyum commented on a diff in pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "wangyum (via GitHub)" <gi...@apache.org>.
wangyum commented on code in PR #39691:
URL: https://github.com/apache/spark/pull/39691#discussion_r1131137466
##########
sql/core/src/test/resources/sql-tests/inputs/window.sql:
##########
@@ -465,3 +465,60 @@ SELECT
SUM(salary) OVER w sum_salary
FROM
basic_pays;
+
+-- Test QUALIFY clause
Review Comment:
OK
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] jimmyzzxhlh commented on pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "jimmyzzxhlh (via GitHub)" <gi...@apache.org>.
jimmyzzxhlh commented on PR #39691:
URL: https://github.com/apache/spark/pull/39691#issuecomment-1583566431
^ Same question -- Any plan to release this feature?
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] gatorsmile commented on pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "gatorsmile (via GitHub)" <gi...@apache.org>.
gatorsmile commented on PR #39691:
URL: https://github.com/apache/spark/pull/39691#issuecomment-1475704320
@cloud-fan @allisonwang-db take another look?
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-31561][SQL] Add QUALIFY clause [spark]
Posted by "uplsh580 (via GitHub)" <gi...@apache.org>.
uplsh580 commented on PR #39691:
URL: https://github.com/apache/spark/pull/39691#issuecomment-2239207994
Are there any plans to reopen?
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] github-actions[bot] commented on pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #39691:
URL: https://github.com/apache/spark/pull/39691#issuecomment-1722349673
We're closing this PR because it hasn't been updated in a while. This isn't a judgement on the merit of the PR in any way. It's just a way of keeping the PR queue manageable.
If you'd like to revive this PR, please reopen it and ask a committer to remove the Stale tag!
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-31561][SQL] Add QUALIFY clause [spark]
Posted by "jboarman (via GitHub)" <gi...@apache.org>.
jboarman commented on PR #39691:
URL: https://github.com/apache/spark/pull/39691#issuecomment-2239848704
It seems we need someone to potentially pick up where @wangyum left off to address some open nitpicks identified by @jaceklaskowski. I didn't see any specific issues that were not addressed otherwise in the review.
Anyone? I wish there was a more direct bounty system in GitHub! :P
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] wangyum commented on a diff in pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "wangyum (via GitHub)" <gi...@apache.org>.
wangyum commented on code in PR #39691:
URL: https://github.com/apache/spark/pull/39691#discussion_r1130962983
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala:
##########
@@ -1729,6 +1729,23 @@ class Analyzer(override val catalogManager: CatalogManager)
resolveExpressionByPlanChildren(resolvedWithAgg, u, allowOuter = true)
}
+ case u @ UnresolvedQualify(cond, child) if !u.resolved && child.resolved =>
+ if (!u.containsPattern(WINDOW_EXPRESSION)) {
+ throw QueryCompilationErrors.expressionWithoutWindowExpressionError(cond)
+ } else {
+ if (u.missingInput.nonEmpty) {
+ val (newCond, newChild) = resolveExprsAndAddMissingAttrs(Seq(cond), child)
Review Comment:
Behavior looks consistent with aggregate:
```sql
CREATE TABLE dealer (id INT, city STRING, car_model STRING, quantity INT) using parquet;
SELECT ROW_NUMBER() OVER(PARTITION BY city ORDER BY id) AS id FROM dealer QUALIFY id > 0;
SELECT city, max(id) AS id FROM dealer GROUP BY city HAVING id > 0;
```
```
== Analyzed Logical Plan ==
id: int
Filter (id#0 > 0)
+- Project [id#0]
+- Project [city#3, id#2, id#0, id#0]
+- Window [row_number() windowspecdefinition(city#3, id#2 ASC NULLS FIRST, specifiedwindowframe(RowFrame, unboundedpreceding$(), currentrow$())) AS id#0], [city#3], [id#2 ASC NULLS FIRST]
+- Project [city#3, id#2]
+- SubqueryAlias spark_catalog.default.dealer
+- Relation spark_catalog.default.dealer[id#2,city#3,car_model#4,quantity#5] parquet
```
```
== Analyzed Logical Plan ==
city: string, id: int
Filter (id#0 > 0)
+- Aggregate [city#2], [city#2, max(id#1) AS id#0]
+- SubqueryAlias spark_catalog.default.dealer
+- Relation spark_catalog.default.dealer[id#1,city#2,car_model#3,quantity#4] parquet
```
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] gatorsmile commented on pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "gatorsmile (via GitHub)" <gi...@apache.org>.
gatorsmile commented on PR #39691:
URL: https://github.com/apache/spark/pull/39691#issuecomment-1439636445
cc @allisonwang-db
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] itholic commented on a diff in pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "itholic (via GitHub)" <gi...@apache.org>.
itholic commented on code in PR #39691:
URL: https://github.com/apache/spark/pull/39691#discussion_r1083550178
##########
sql/core/src/test/resources/sql-tests/results/window.sql.out:
##########
@@ -1342,3 +1342,139 @@ org.apache.spark.sql.AnalysisException
"windowName" : "w"
}
}
+
+
+-- !query
+SELECT val_long,
+ val_date,
+ max(val) OVER (partition BY val_date) AS m
+FROM testdata
+WHERE val_long > 2
+QUALIFY m > 2 AND m < 10
+-- !query schema
+struct<val_long:bigint,val_date:date,m:int>
+-- !query output
+2147483650 2020-12-31 3
+2147483650 2020-12-31 3
+
+
+-- !query
+SELECT val_long,
+ val_date,
+ val
+FROM testdata QUALIFY max(val) OVER (partition BY val_date) >= 3
+-- !query schema
+struct<val_long:bigint,val_date:date,val:int>
+-- !query output
+1 2017-08-01 1
+1 2017-08-01 3
+1 2017-08-01 NULL
+2147483650 2020-12-31 2
+2147483650 2020-12-31 3
+NULL 2017-08-01 1
+
+
+-- !query
+SELECT val_date,
+ val * sum(val) OVER (partition BY val_date) AS w
+FROM testdata
+QUALIFY w > 10
+-- !query schema
+struct<val_date:date,w:bigint>
+-- !query output
+2017-08-01 15
+2020-12-31 15
+
+
+-- !query
+SELECT w.val_date
+FROM testdata w
+JOIN testdata w2 ON w.val_date=w2.val_date
+QUALIFY row_number() OVER (partition BY w.val_date ORDER BY w.val) IN (2)
+-- !query schema
+struct<val_date:date>
+-- !query output
+2017-08-01
+2020-12-31
+
+
+-- !query
+SELECT val_date,
+ count(val_long) OVER (partition BY val_date) AS w
+FROM testdata
+GROUP BY val_date,
+ val_long
+HAVING Sum(val) > 1
+QUALIFY w = 1
+-- !query schema
+struct<val_date:date,w:bigint>
+-- !query output
+2017-08-01 1
+2017-08-03 1
+2020-12-31 1
+
+
+-- !query
+SELECT val_date,
+ val_long,
+ Sum(val)
+FROM testdata
+GROUP BY val_date,
+ val_long
+HAVING Sum(val) > 1
+QUALIFY count(val_long) OVER (partition BY val_date) IN(SELECT 1)
+-- !query schema
+struct<val_date:date,val_long:bigint,sum(val):bigint>
+-- !query output
+2017-08-01 1 4
+2017-08-03 3 2
+2020-12-31 2147483650 5
+
+
+-- !query
+SELECT val_date,
+ val_long
+FROM testdata
+QUALIFY count(val_long) OVER (partition BY val_date) > 1 AND val > 1
+-- !query schema
+struct<val_date:date,val_long:bigint>
+-- !query output
+2017-08-01 1
+2020-12-31 2147483650
+2020-12-31 2147483650
+
+
+-- !query
+SELECT val_date,
+ val_long
+FROM testdata
+QUALIFY val > 1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1032",
Review Comment:
nit: Can we assign the name for `_LEGACY_ERROR_TEMP_1032` to `MISSING_WINDOW_EXPR` or something while we here ?
But it's not a necessary, and I can help it as follow-up, tho.
##########
docs/sql-ref-syntax-qry-select-qualify.md:
##########
@@ -0,0 +1,98 @@
+---
+layout: global
+title: QUALIFY Clause
+displayTitle: QUALIFY Clause
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+### Description
+
+The `QUALIFY` clause is used to filter the results of
+[window functions](sql-ref-syntax-qry-select-window.md). To use QUALIFY,
+at least one window function is required to be present in the SELECT list or the QUALIFY clause.
Review Comment:
`SELECT` -> `[SELECT](sql-ref-syntax-qry-select.html)` ?
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] wangyum commented on a diff in pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "wangyum (via GitHub)" <gi...@apache.org>.
wangyum commented on code in PR #39691:
URL: https://github.com/apache/spark/pull/39691#discussion_r1130865469
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala:
##########
@@ -1729,6 +1729,23 @@ class Analyzer(override val catalogManager: CatalogManager)
resolveExpressionByPlanChildren(resolvedWithAgg, u, allowOuter = true)
}
+ case u @ UnresolvedQualify(cond, child) if !u.resolved && child.resolved =>
+ if (!u.containsPattern(WINDOW_EXPRESSION)) {
Review Comment:
```sql
CREATE TABLE dealer (id INT, city STRING, car_model STRING, quantity INT) using parquet;
SELECT * from (SELECT *, ROW_NUMBER() OVER(PARTITION BY city ORDER BY id) AS rn FROM dealer QUALIFY id > 0) t QUALIFY t.id > 0;
```
The analyzed logical plan:
```
== Analyzed Logical Plan ==
id: int, city: string, car_model: string, quantity: int, rn: int
Filter (id#2 > 0)
+- Project [id#2, city#3, car_model#4, quantity#5, rn#0]
+- SubqueryAlias t
+- Filter (id#2 > 0)
+- Project [id#2, city#3, car_model#4, quantity#5, rn#0]
+- Project [id#2, city#3, car_model#4, quantity#5, rn#0, rn#0]
+- Window [row_number() windowspecdefinition(city#3, id#2 ASC NULLS FIRST, specifiedwindowframe(RowFrame, unboundedpreceding$(), currentrow$())) AS rn#0], [city#3], [id#2 ASC NULLS FIRST]
+- Project [id#2, city#3, car_model#4, quantity#5]
+- SubqueryAlias spark_catalog.default.dealer
+- Relation spark_catalog.default.dealer[id#2,city#3,car_model#4,quantity#5] parquet
```
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a diff in pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "allisonwang-db (via GitHub)" <gi...@apache.org>.
allisonwang-db commented on code in PR #39691:
URL: https://github.com/apache/spark/pull/39691#discussion_r1129182842
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala:
##########
@@ -1729,6 +1729,23 @@ class Analyzer(override val catalogManager: CatalogManager)
resolveExpressionByPlanChildren(resolvedWithAgg, u, allowOuter = true)
}
+ case u @ UnresolvedQualify(cond, child) if !u.resolved && child.resolved =>
+ if (!u.containsPattern(WINDOW_EXPRESSION)) {
Review Comment:
What if we have a subquery that contains a window function but not the main query:
```
SELECT (SELECT window_func w FROM t QUALIFY t.c1 > 0) FROM t QUALIFY t.c1 > 0
```
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala:
##########
@@ -1729,6 +1729,23 @@ class Analyzer(override val catalogManager: CatalogManager)
resolveExpressionByPlanChildren(resolvedWithAgg, u, allowOuter = true)
}
+ case u @ UnresolvedQualify(cond, child) if !u.resolved && child.resolved =>
+ if (!u.containsPattern(WINDOW_EXPRESSION)) {
+ throw QueryCompilationErrors.expressionWithoutWindowExpressionError(cond)
+ } else {
+ if (u.missingInput.nonEmpty) {
+ val (newCond, newChild) = resolveExprsAndAddMissingAttrs(Seq(cond), child)
Review Comment:
If we treat the resolution order for QUALIFY the same as GROUP BY, then we need to resolve the attributes in the QUALIFY clause first using the columns in the FROM clause and then using columns and aliases in the SELECT clause.
For example: `SELECT c1, window_func AS c2 FROM t QUALIFY c2 > 0;` here `c2` should be resolved as `t.c2` instead of the `window_func as c2`.
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryCompilationErrors.scala:
##########
@@ -571,7 +571,7 @@ private[sql] object QueryCompilationErrors extends QueryErrorsBase {
messageParameters = Map.empty)
}
- def expressionWithoutWindowExpressionError(expr: NamedExpression): Throwable = {
+ def expressionWithoutWindowExpressionError(expr: Expression): Throwable = {
Review Comment:
Can we use a new error for this? Currnetly the error message is not very user-friendly. E.g.:
```
SELECT * FROM t QUALIFY c1 > 0
('c1 > 0) does not have any WindowExpression.
```
##########
sql/core/src/test/resources/sql-tests/inputs/window.sql:
##########
@@ -465,3 +465,60 @@ SELECT
SUM(salary) OVER w sum_salary
FROM
basic_pays;
+
+-- Test QUALIFY clause
Review Comment:
We can split these tests into a new file.
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] jaceklaskowski commented on a diff in pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "jaceklaskowski (via GitHub)" <gi...@apache.org>.
jaceklaskowski commented on code in PR #39691:
URL: https://github.com/apache/spark/pull/39691#discussion_r1148388391
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/unresolved.scala:
##########
@@ -693,6 +693,19 @@ case class UnresolvedHaving(
copy(child = newChild)
}
+/**
+ * Represents unresolved qualify clause, It is turned by the analyzer into a Filter.
Review Comment:
nit: QUALIFY (all uppercase) + "that becomes Filter after query analysis"
##########
core/src/main/resources/error/error-classes.json:
##########
@@ -1303,6 +1303,11 @@
"Protobuf type not yet supported: <protobufType>."
]
},
+ "QUALIFY_EXPRESSION_MUST_CONTAIN_WINDOW_FUNCTION" : {
+ "message" : [
+ "QUALIFY expression '<sqlExpr>' must contain a window function."
Review Comment:
nit: `QUALIFY` is a clause so what this `expression` refers to?
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/unresolved.scala:
##########
@@ -693,6 +693,19 @@ case class UnresolvedHaving(
copy(child = newChild)
}
+/**
+ * Represents unresolved qualify clause, It is turned by the analyzer into a Filter.
+ */
+case class UnresolvedQualify(
+ qualifyCondition: Expression,
+ child: LogicalPlan)
+ extends UnaryNode {
+ override lazy val resolved: Boolean = false
Review Comment:
lazy is not needed here, is it?
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] felicienveldema commented on pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "felicienveldema (via GitHub)" <gi...@apache.org>.
felicienveldema commented on PR #39691:
URL: https://github.com/apache/spark/pull/39691#issuecomment-1564380739
Is this feature still on the planning?
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] github-actions[bot] closed pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
URL: https://github.com/apache/spark/pull/39691
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] wangyum commented on pull request #39691: [SPARK-31561][SQL] Add QUALIFY clause
Posted by "wangyum (via GitHub)" <gi...@apache.org>.
wangyum commented on PR #39691:
URL: https://github.com/apache/spark/pull/39691#issuecomment-1399281359
In fact databricks also supports this clause.
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org