You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by "itiels (via GitHub)" <gi...@apache.org> on 2024/06/04 07:45:59 UTC
[PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
itiels opened a new pull request, #3811:
URL: https://github.com/apache/calcite/pull/3811
This PR adds support for frame exclusion clause in window functions.
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3811:
URL: https://github.com/apache/calcite/pull/3811#issuecomment-2150463801
## [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3811) **Quality Gate passed**
Issues
 [26 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3811&resolved=false&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3811&resolutions=WONTFIX)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3811&resolved=false&sinceLeakPeriod=true)
 [75.5% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3811&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3811&metric=new_duplicated_lines_density&view=list)
[See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3811)
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3811:
URL: https://github.com/apache/calcite/pull/3811#issuecomment-2156430167
## [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3811) **Quality Gate passed**
Issues
 [13 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3811&resolved=false&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3811&resolutions=WONTFIX)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3811&resolved=false&sinceLeakPeriod=true)
 [74.9% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3811&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3811&metric=new_duplicated_lines_density&view=list)
[See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3811)
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "asolimando (via GitHub)" <gi...@apache.org>.
asolimando commented on code in PR #3811:
URL: https://github.com/apache/calcite/pull/3811#discussion_r1627054403
##########
core/src/main/java/org/apache/calcite/rex/RexBuilder.java:
##########
@@ -389,6 +389,7 @@ public RexNode makeOver(
ImmutableList<RexFieldCollation> orderKeys,
RexWindowBound lowerBound,
RexWindowBound upperBound,
+ RexWindowExclusion exclude,
Review Comment:
The current policy is to leave the old API with a default value for the new parameter as you suggest. At that point the number of files changed for this PR will significantly drop.
If the old API is deprecated after the current changes, you can mark it as such with the `@Deprecated` annotation (see [SqlDrop.java#L37](https://github.com/apache/calcite/blob/3c4d54a82e21ec6926e6aa724ce94ead63d19083/core/src/main/java/org/apache/calcite/sql/SqlDrop.java#L37) for instance).
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu merged PR #3811:
URL: https://github.com/apache/calcite/pull/3811
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "asolimando (via GitHub)" <gi...@apache.org>.
asolimando commented on code in PR #3811:
URL: https://github.com/apache/calcite/pull/3811#discussion_r1627279119
##########
core/src/main/java/org/apache/calcite/rex/RexBuilder.java:
##########
@@ -389,6 +389,7 @@ public RexNode makeOver(
ImmutableList<RexFieldCollation> orderKeys,
RexWindowBound lowerBound,
RexWindowBound upperBound,
+ RexWindowExclusion exclude,
Review Comment:
From a cursory look, the SQL parser still accepts the same syntax (without `exclude` statement) after your modifications.
If that's confirmed, then I think there is no need to deprecate the old APIs, as they would basically mimic the same (legal) old behaviour.
In a nut-shell, you are adding an optional functionality, what was working before should still be working as-is, it's not a change in behaviour.
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3811:
URL: https://github.com/apache/calcite/pull/3811#issuecomment-2156431316
Please retry analysis of this Pull-Request directly on SonarCloud
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #3811:
URL: https://github.com/apache/calcite/pull/3811#discussion_r1628088990
##########
testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java:
##########
@@ -5780,6 +5780,22 @@ private static Matcher<SqlNode> isCharLiteral(String s) {
+ "FROM `BIDS`");
}
+ @Test void testWindowSpecExclusion() {
+ // parses correctly
+ sql("select sum(x) over (order by x rows between 2 preceding and 2 following exclude group) "
+ + "from emp")
+ .ok("SELECT (SUM(`X`) OVER (ORDER BY `X` ROWS BETWEEN 2 PRECEDING AND 2 "
+ + "FOLLOWING EXCLUDE GROUP))\n"
+ + "FROM `EMP`");
+ // doesn't parse without frame definition
+ sql("select sum(x) over (order by x ^exclude^ current row) from bids")
Review Comment:
@asolimando this is a convention used to indicate the error position reported by the parser in the statement
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3811:
URL: https://github.com/apache/calcite/pull/3811#issuecomment-2150851782
## [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3811) **Quality Gate passed**
Issues
 [18 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3811&resolved=false&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3811&resolutions=WONTFIX)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3811&resolved=false&sinceLeakPeriod=true)
 [74.9% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3811&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3811&metric=new_duplicated_lines_density&view=list)
[See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3811)
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "NobiGo (via GitHub)" <gi...@apache.org>.
NobiGo commented on code in PR #3811:
URL: https://github.com/apache/calcite/pull/3811#discussion_r1632126797
##########
core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java:
##########
@@ -66,10 +67,10 @@ public final class LogicalWindow extends Window {
*
* <p>Use {@link #create} unless you know what you're doing.
*
- * @param cluster Cluster
+ * @param cluster Cluster
Review Comment:
Small corrections:There is an extra space here
##########
core/src/test/resources/sql/winagg.iq:
##########
@@ -839,5 +839,68 @@ EnumerableCalc(expr#0..3=[{inputs}], GENDER=[$t2], $1=[$t3])
+--------+--------+
(6 rows)
+!ok
+
+# Test excluding. Verified against Postgres
Review Comment:
Adding the Jira summary is better.
##########
site/_docs/history.md:
##########
@@ -49,6 +49,8 @@ Guava versions 21.0 to 32.1.3-jre;
other software versions as specified in gradle.properties.
#### New features
+* [<a href="https://issues.apache.org/jira/browse/CALCITE-5855">CALCITE-5855</a>]
Review Comment:
This file is a template file, we don't need to change this. The release manager will modify it.
##########
core/src/test/resources/sql/winagg.iq:
##########
@@ -839,5 +839,68 @@ EnumerableCalc(expr#0..3=[{inputs}], GENDER=[$t2], $1=[$t3])
+--------+--------+
(6 rows)
+!ok
+
+# Test excluding. Verified against Postgres
Review Comment:
PostgreSQL may be better.
##########
site/_docs/algebra.md:
##########
@@ -530,8 +530,6 @@ call and then call its `over()` method, for instance `count().over()`.
To further modify the `OverCall`, call its methods:
-| Method | Description
Review Comment:
This change is necessary?
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "asolimando (via GitHub)" <gi...@apache.org>.
asolimando commented on code in PR #3811:
URL: https://github.com/apache/calcite/pull/3811#discussion_r1627060170
##########
core/src/main/java/org/apache/calcite/rex/RexBuilder.java:
##########
@@ -389,6 +389,7 @@ public RexNode makeOver(
ImmutableList<RexFieldCollation> orderKeys,
RexWindowBound lowerBound,
RexWindowBound upperBound,
+ RexWindowExclusion exclude,
Review Comment:
The current policy is exactly what @mihaibudiu suggests, you should leave the old API around with a default value for the new parameter (exclude no others?), at that point no need to document in `history.md` IMO.
By doing this, the number of files to be updated in the scope of this PR will drop.
If you want to deprecate the old API, you can do so with the `@Deprecated` annotation (see [SqlDrop.java#L37](https://github.com/apache/calcite/blob/3c4d54a82e21ec6926e6aa724ce94ead63d19083/core/src/main/java/org/apache/calcite/sql/SqlDrop.java#L37) for an example), but I don't think it's necessary here.
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "asolimando (via GitHub)" <gi...@apache.org>.
asolimando commented on code in PR #3811:
URL: https://github.com/apache/calcite/pull/3811#discussion_r1628075025
##########
core/src/test/resources/sql/winagg.iq:
##########
@@ -839,5 +839,66 @@ EnumerableCalc(expr#0..3=[{inputs}], GENDER=[$t2], $1=[$t3])
+--------+--------+
(6 rows)
+!ok
+
+# Test excluding. Verified against Postgres
+!use scott
+
+select empno,
+ avg(comm) over (order by empno rows 2 preceding exclude current row) as e1,
+ avg(comm) over (order by empno rows 2 preceding exclude group) as e2,
+ avg(comm) over (order by empno rows 2 preceding exclude ties) as e3,
+ avg(comm) over (order by empno rows 2 preceding exclude no others) as e4
Review Comment:
Can you also add the same line but without "exclude no others" to verify they results in the same resultset? (here and in similar added tests)
##########
core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java:
##########
@@ -66,13 +67,13 @@ public final class LogicalWindow extends Window {
*
* <p>Use {@link #create} unless you know what you're doing.
*
- * @param cluster Cluster
- * @param traitSet Trait set
- * @param hints Hints for this node
- * @param input Input relational expression
+ * @param cluster Cluster
Review Comment:
In the codebase we generally don't format javadoc as you do here, can you please revert these changes?
##########
testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java:
##########
@@ -5780,6 +5780,22 @@ private static Matcher<SqlNode> isCharLiteral(String s) {
+ "FROM `BIDS`");
}
+ @Test void testWindowSpecExclusion() {
+ // parses correctly
+ sql("select sum(x) over (order by x rows between 2 preceding and 2 following exclude group) "
+ + "from emp")
+ .ok("SELECT (SUM(`X`) OVER (ORDER BY `X` ROWS BETWEEN 2 PRECEDING AND 2 "
+ + "FOLLOWING EXCLUDE GROUP))\n"
+ + "FROM `EMP`");
+ // doesn't parse without frame definition
+ sql("select sum(x) over (order by x ^exclude^ current row) from bids")
Review Comment:
Why do we need the `'^'` here?
##########
testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java:
##########
@@ -5780,6 +5780,22 @@ private static Matcher<SqlNode> isCharLiteral(String s) {
+ "FROM `BIDS`");
}
+ @Test void testWindowSpecExclusion() {
+ // parses correctly
+ sql("select sum(x) over (order by x rows between 2 preceding and 2 following exclude group) "
+ + "from emp")
+ .ok("SELECT (SUM(`X`) OVER (ORDER BY `X` ROWS BETWEEN 2 PRECEDING AND 2 "
+ + "FOLLOWING EXCLUDE GROUP))\n"
+ + "FROM `EMP`");
+ // doesn't parse without frame definition
+ sql("select sum(x) over (order by x ^exclude^ current row) from bids")
+ .fails("(?s).*Encountered \"exclude\".*");
+ // EXCLUDE NO OTHERS is the default behavior, and ommited from UNPARSE
+ sql("select sum(x) over (order by x rows between 2 preceding and 2 following exclude no others)"
+ + " from emp")
+ .ok("SELECT (SUM(`X`) OVER (ORDER BY `X` ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING))\n"
+ + "FROM `EMP`");
+ }
Review Comment:
Please add a newline here
##########
core/src/main/java/org/apache/calcite/rex/RexWindowExclusion.java:
##########
@@ -0,0 +1,64 @@
+/*
+ * 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.
+ */
+package org.apache.calcite.rex;
+
+import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlWindow;
+
+/**
+ * Representation of different kinds of exclude clause in window functions.
+ */
+public enum RexWindowExclusion {
+ EXCLUDE_NO_OTHER("EXCLUDE NO OTHER"),
+ EXCLUDE_CURRENT_ROW("EXCLUDE CURRENT ROW"),
+ EXCLUDE_TIES("EXCLUDE TIES"),
+ EXCLUDE_GROUP("EXCLUDE GROUP");
+
+ private final String sql;
+
+ RexWindowExclusion(String sql) {
+ this.sql = sql;
+ }
+
+ @Override public String toString() {
+ return sql;
+ }
+
+ /**
+ * Creates a window exclusion from a {@link SqlNode}.
+ *
+ * @param node SqlLiteral of the exclusion clause
+ * @return RexWindowExclusion
+ */
+ public static RexWindowExclusion create(SqlNode node) {
+ SqlLiteral exclude = (SqlLiteral) node;
+ if (SqlWindow.isExcludeCurrentRow(exclude)) {
Review Comment:
Nit: what about a switch similar to what you wrote for `SqlImplementor`?
##########
testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java:
##########
@@ -5780,6 +5780,22 @@ private static Matcher<SqlNode> isCharLiteral(String s) {
+ "FROM `BIDS`");
}
+ @Test void testWindowSpecExclusion() {
+ // parses correctly
Review Comment:
The comment is probably not needed as we have the `ok` method just after
##########
core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java:
##########
@@ -958,6 +959,16 @@ public SqlNode toSql(RexWindowBound rexWindowBound) {
}
}
+ public SqlLiteral toSql(RexWindowExclusion exclude) {
+ switch (exclude) {
+ case EXCLUDE_CURRENT_ROW: return SqlWindow.createExcludeCurrentRow(POS);
+ case EXCLUDE_GROUP: return SqlWindow.createExcludeGroup(POS);
+ case EXCLUDE_TIES: return SqlWindow.createExcludeTies(POS);
+ }
+ assert exclude == RexWindowExclusion.EXCLUDE_NO_OTHER;
Review Comment:
Nit: why not a regular case and throwing on `default`?
I am also wondering if we want to omit printing the exclusion when there is no exclusion, it would keep the generated SQL backward compatible to what we have been generating now. WDYT?
##########
site/_docs/algebra.md:
##########
@@ -530,19 +530,20 @@ call and then call its `over()` method, for instance `count().over()`.
To further modify the `OverCall`, call its methods:
-| Method | Description
-|:-------------------- |:-----------
-| `rangeUnbounded()` | Creates an unbounded range-based window, `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`
-| `rangeFrom(lower)` | Creates a range-based window bounded below, `RANGE BETWEEN lower AND CURRENT ROW`
-| `rangeTo(upper)` | Creates a range-based window bounded above, `RANGE BETWEEN CURRENT ROW AND upper`
-| `rangeBetween(lower, upper)` | Creates a range-based window, `RANGE BETWEEN lower AND upper`
-| `rowsUnbounded()` | Creates an unbounded row-based window, `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`
-| `rowsFrom(lower)` | Creates a row-based window bounded below, `ROWS BETWEEN lower AND CURRENT ROW`
-| `rowsTo(upper)` | Creates a row-based window bounded above, `ROWS BETWEEN CURRENT ROW AND upper`
-| `rowsBetween(lower, upper)` | Creates a rows-based window, `ROWS BETWEEN lower AND upper`
+| Method | Description
+|:---------------------------------------------------|:-----------
+| `rangeUnbounded()` | Creates an unbounded range-based window, `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`
+| `rangeFrom(lower)` | Creates a range-based window bounded below, `RANGE BETWEEN lower AND CURRENT ROW`
+| `rangeTo(upper)` | Creates a range-based window bounded above, `RANGE BETWEEN CURRENT ROW AND upper`
+| `rangeBetween(lower, upper)` | Creates a range-based window, `RANGE BETWEEN lower AND upper`
+| `rowsUnbounded()` | Creates an unbounded row-based window, `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`
+| `rowsFrom(lower)` | Creates a row-based window bounded below, `ROWS BETWEEN lower AND CURRENT ROW`
+| `rowsTo(upper)` | Creates a row-based window bounded above, `ROWS BETWEEN CURRENT ROW AND upper`
+| `exclude(excludeType)` | Exclude certain rows from the frame (see SQL `EXCLUDE`)
+| `rowsBetween(lower, upper)` | Creates a rows-based window, `ROWS BETWEEN lower AND upper`
| `partitionBy(expr...)`<br/>`partitionBy(exprList)` | Partitions the window on the given expressions (see SQL `PARTITION BY`)
-| `orderBy(expr...)`<br/>`sort(exprList)` | Sorts the rows in the window (see SQL `ORDER BY`)
-| `allowPartial(b)` | Sets whether to allow partial width windows; default true
-| `nullWhenCountZero(b)` | Sets whether whether the aggregate function should evaluate to null if no rows are in the window; default false
-| `as(alias)` | Assigns a column alias (see SQL `AS`) and converts this `OverCall` to a `RexNode`
-| `toRex()` | Converts this `OverCall` to a `RexNode`
+| `orderBy(expr...)`<br/>`sort(exprList)` | Sorts the rows in the window (see SQL `ORDER BY`)
Review Comment:
Nit: can you remove the extra spaces at the end of these lines?
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "itiels (via GitHub)" <gi...@apache.org>.
itiels commented on PR #3811:
URL: https://github.com/apache/calcite/pull/3811#issuecomment-2150803443
Thanks again @asolimando!
I've fixed according to your requests, and fixed some of the sonar cloud issues (some were indeed out of scope).
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "itiels (via GitHub)" <gi...@apache.org>.
itiels commented on code in PR #3811:
URL: https://github.com/apache/calcite/pull/3811#discussion_r1628116428
##########
core/src/main/java/org/apache/calcite/rex/RexWindowExclusion.java:
##########
@@ -0,0 +1,64 @@
+/*
+ * 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.
+ */
+package org.apache.calcite.rex;
+
+import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlWindow;
+
+/**
+ * Representation of different kinds of exclude clause in window functions.
+ */
+public enum RexWindowExclusion {
+ EXCLUDE_NO_OTHER("EXCLUDE NO OTHER"),
+ EXCLUDE_CURRENT_ROW("EXCLUDE CURRENT ROW"),
+ EXCLUDE_TIES("EXCLUDE TIES"),
+ EXCLUDE_GROUP("EXCLUDE GROUP");
+
+ private final String sql;
+
+ RexWindowExclusion(String sql) {
+ this.sql = sql;
+ }
+
+ @Override public String toString() {
+ return sql;
+ }
+
+ /**
+ * Creates a window exclusion from a {@link SqlNode}.
+ *
+ * @param node SqlLiteral of the exclusion clause
+ * @return RexWindowExclusion
+ */
+ public static RexWindowExclusion create(SqlNode node) {
+ SqlLiteral exclude = (SqlLiteral) node;
+ if (SqlWindow.isExcludeCurrentRow(exclude)) {
Review Comment:
Maybe I'm missing something, but I think I can't do that here because I have `SqlLiteral` and not an Enum.
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on PR #3811:
URL: https://github.com/apache/calcite/pull/3811#issuecomment-2158912898
@NobiGo if you are satisfied we can merge this
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "itiels (via GitHub)" <gi...@apache.org>.
itiels commented on PR #3811:
URL: https://github.com/apache/calcite/pull/3811#issuecomment-2156422082
Thanks for the review @NobiGo , I've fixes the issues.
@asolimando I've squashed the commits. Thank you.
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "itiels (via GitHub)" <gi...@apache.org>.
itiels commented on code in PR #3811:
URL: https://github.com/apache/calcite/pull/3811#discussion_r1628113810
##########
core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java:
##########
@@ -958,6 +959,16 @@ public SqlNode toSql(RexWindowBound rexWindowBound) {
}
}
+ public SqlLiteral toSql(RexWindowExclusion exclude) {
+ switch (exclude) {
+ case EXCLUDE_CURRENT_ROW: return SqlWindow.createExcludeCurrentRow(POS);
+ case EXCLUDE_GROUP: return SqlWindow.createExcludeGroup(POS);
+ case EXCLUDE_TIES: return SqlWindow.createExcludeTies(POS);
+ }
+ assert exclude == RexWindowExclusion.EXCLUDE_NO_OTHER;
Review Comment:
I've replace this assert with exception.
Regarding generated SQL, we do ignore the exclude in case of `Exclude no others` in `SqlWindow.unparse`.
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #3811:
URL: https://github.com/apache/calcite/pull/3811#discussion_r1626558428
##########
core/src/test/java/org/apache/calcite/rel/logical/ToLogicalConverterTest.java:
##########
@@ -467,6 +467,22 @@ private void verify(RelNode rel, String expectedPhysical, String expectedLogical
verify(rel(sql), expectedPhysical, expectedLogical);
}
+ @Test void testWindowExclude() {
+ String sql = "SELECT sum(\"salary\") over (order by \"hire_date\" rows between "
+ + "unbounded preceding and current row exclude group) FROM \"employee\"";
+ String expectedPhysical = ""
+ + "EnumerableProject($0=[$17])\n"
+ + " EnumerableWindow(window#0=[window(order by [9] rows between"
+ + " UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP aggs [SUM($11)])])\n"
+ + " JdbcToEnumerableConverter\n"
+ + " JdbcTableScan(table=[[foodmart, employee]])\n";
+ String expectedLogical = ""
+ + "LogicalProject($0=[$17])\n"
+ + " LogicalWindow(window#0=[window(order by [9] rows between"
+ + " UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP aggs [SUM($11)])])\n"
+ + " LogicalTableScan(table=[[foodmart, employee]])\n";
+ verify(rel(sql), expectedPhysical, expectedLogical);
+ }
Review Comment:
missing empty line?
do you want to test the other kinds of exclusions too?
##########
core/src/main/java/org/apache/calcite/rex/RexBuilder.java:
##########
@@ -389,6 +389,7 @@ public RexNode makeOver(
ImmutableList<RexFieldCollation> orderKeys,
RexWindowBound lowerBound,
RexWindowBound upperBound,
+ RexWindowExclusion exclude,
Review Comment:
Thes look like breaking API changes which have to be documented in history.md.
I don't know if the policy is to leave around the old API with a default value for the new parameter.
##########
core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java:
##########
@@ -969,6 +977,25 @@ private void addRexFieldCollationList(List<RexFieldCollation> list,
}
}
+ private @Nullable RexWindowExclusion toRexWindowExclusion(@Nullable Map<String, Object> map) {
+ if (map == null) {
+ return null;
+ }
+ final String type = get(map, "type");
+ switch (type) {
+ case "CURRENT_ROW":
+ return RexWindowExclusion.EXCLUDE_CURRENT_ROW;
+ case "GROUP":
+ return RexWindowExclusion.EXCLUDE_GROUP;
+ case "TIES":
+ return RexWindowExclusion.EXCLUDE_TIES;
+ case "NO OTHERS":
+ return RexWindowExclusion.EXCLUDE_NO_OTHER;
+ default:
+ throw new UnsupportedOperationException(
+ "cannot convert type to rex window exclusion " + type);
Review Comment:
I would write this as "cannot convert '" + type + "'to rex window exclusion"
##########
core/src/main/java/org/apache/calcite/rel/core/Window.java:
##########
@@ -358,7 +365,8 @@ public RelCollation collation() {
public boolean isAlwaysNonEmpty() {
int lowerKey = lowerBound.getOrderKey();
int upperKey = upperBound.getOrderKey();
- return lowerKey > -1 && lowerKey <= upperKey;
+ return lowerKey > -1 && lowerKey <= upperKey
Review Comment:
wouldn't the "no ties" also apply here?
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "itiels (via GitHub)" <gi...@apache.org>.
itiels commented on code in PR #3811:
URL: https://github.com/apache/calcite/pull/3811#discussion_r1627181513
##########
core/src/main/java/org/apache/calcite/rex/RexBuilder.java:
##########
@@ -389,6 +389,7 @@ public RexNode makeOver(
ImmutableList<RexFieldCollation> orderKeys,
RexWindowBound lowerBound,
RexWindowBound upperBound,
+ RexWindowExclusion exclude,
Review Comment:
Thanks for reviewing @mihaibudiu and @asolimando .
So should I add overload option for each public method that I've changed? And how do I decide if the old versions should be deprecated or not?
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3811:
URL: https://github.com/apache/calcite/pull/3811#issuecomment-2156425650
## [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3811) **Quality Gate passed**
Issues
 [13 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3811&resolved=false&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3811&resolutions=WONTFIX)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3811&resolved=false&sinceLeakPeriod=true)
 [74.9% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3811&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3811&metric=new_duplicated_lines_density&view=list)
[See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3811)
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "itiels (via GitHub)" <gi...@apache.org>.
itiels commented on code in PR #3811:
URL: https://github.com/apache/calcite/pull/3811#discussion_r1628030077
##########
core/src/main/java/org/apache/calcite/rex/RexBuilder.java:
##########
@@ -389,6 +389,7 @@ public RexNode makeOver(
ImmutableList<RexFieldCollation> orderKeys,
RexWindowBound lowerBound,
RexWindowBound upperBound,
+ RexWindowExclusion exclude,
Review Comment:
Ok. I've pushed the changes, please let me know if that is what you had in mind.
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "itiels (via GitHub)" <gi...@apache.org>.
itiels commented on code in PR #3811:
URL: https://github.com/apache/calcite/pull/3811#discussion_r1627184125
##########
core/src/main/java/org/apache/calcite/rel/core/Window.java:
##########
@@ -358,7 +365,8 @@ public RelCollation collation() {
public boolean isAlwaysNonEmpty() {
int lowerKey = lowerBound.getOrderKey();
int upperKey = upperBound.getOrderKey();
- return lowerKey > -1 && lowerKey <= upperKey;
+ return lowerKey > -1 && lowerKey <= upperKey
Review Comment:
You are correct, great catch.
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "asolimando (via GitHub)" <gi...@apache.org>.
asolimando commented on code in PR #3811:
URL: https://github.com/apache/calcite/pull/3811#discussion_r1627054403
##########
core/src/main/java/org/apache/calcite/rex/RexBuilder.java:
##########
@@ -389,6 +389,7 @@ public RexNode makeOver(
ImmutableList<RexFieldCollation> orderKeys,
RexWindowBound lowerBound,
RexWindowBound upperBound,
+ RexWindowExclusion exclude,
Review Comment:
The current policy is to leave the old API with a default value for the new parameter as you suggest. At that point the number of files changed for this PR will significantly drop.
If the old API is deprecated after the current changes, you can mark it as such with the `@Deprecated` annotation (see [SqlDrop.java#L37](https://github.com/apache/calcite/blob/3c4d54a82e21ec6926e6aa724ce94ead63d19083/core/src/main/java/org/apache/calcite/sql/SqlDrop.java#L37) for instance).
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-5855] Implement frame exclusion in window functions [calcite]
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3811:
URL: https://github.com/apache/calcite/pull/3811#issuecomment-2151426357
## [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3811) **Quality Gate passed**
Issues
 [18 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3811&resolved=false&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3811&resolutions=WONTFIX)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3811&resolved=false&sinceLeakPeriod=true)
 [74.9% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3811&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3811&metric=new_duplicated_lines_density&view=list)
[See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3811)
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org