[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16959527#comment-16959527
]
benj commented on CALCITE-3402:
---
[~danny0405], yes of course. Thx.
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16959365#comment-16959365
]
Danny Chen commented on CALCITE-3402:
-
[~benj641], do you think we can close this issue now, it seems not a bug ~
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16957656#comment-16957656
]
benj commented on CALCITE-3402:
---
Only try from Apache Drill (1.16 + latest master), the only working ROWS clause
is "ROWS BETWEEN UNBOUNDED PRECEDING AND current row", but yes with this clause
it's possible to have compound ORDER BY
{code:sql}
/* ROWS is not possible with "BETWEEN x PRECEDING AND y FOLLOWING" (RANGE
either) */
apache drill> SELECT *, sum(a) OVER(ORDER BY b ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING) FROM (SELECT 1 a, 1 b, 1 c);
Error: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not
supported
See Apache Drill JIRA: DRILL-3188
/* ROWS is not possible with "BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING" (it's possible with RANGE but with single ORDER BY only ) */
apache drill> SELECT *, sum(a) OVER(ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) FROM (SELECT 1 a, 1 b, 1 c);
Error: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not
supported
See Apache Drill JIRA: DRILL-3188
/* ROWS is possible with "BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" */
apache drill> SELECT *, sum(a) OVER(ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) FROM (SELECT 1 a, 1 b, 1 c);
+---+---+---++
| a | b | c | EXPR$3 |
+---+---+---++
| 1 | 1 | 1 | 1 |
+---+---+---++
1 row selected (1.357 seconds)
/* ROWS followed by "BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" is possible
with compound ORDER BY */
apache drill> SELECT *, sum(a) OVER(ORDER BY b, c ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) FROM (SELECT 1 a, 1 b, 1 c);
+---+---+---++
| a | b | c | EXPR$3 |
+---+---+---++
| 1 | 1 | 1 | 1 |
+---+---+---++
1 row selected (0.145 seconds)
{code}
But maybe the problem is not from Apache Calcite and only in Apache Drill side.
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16957245#comment-16957245
]
Julian Hyde commented on CALCITE-3402:
--
We probably already support ROWS with composite ORDER BY, because it makes
perfect sense. Did you check?! I feel you're wasting people's time by not doing
your research.
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16956993#comment-16956993
]
benj commented on CALCITE-3402:
---
I Just check MSSQL 2017 and Oracle 11 that work like Postgres and SqlLite.
So Maybe the initial topic should be rename to express a request for
* Allowing ROWS clause with other frame than "ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW", like
** ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING
** ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
** ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
* Allowing ROWS clause with compound ORDER BY clause
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16956862#comment-16956862
]
Danny Chen commented on CALCITE-3402:
-
It's not surprise the cases you gave for RANGE and ROWS output differently.
Because RANGE filter columns by the column value range (for your example it's
[-1, 1]) but ROWS filter columns by the row numbers(for your case, always the 3
rows with current row in the middle).
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16956811#comment-16956811
]
benj commented on CALCITE-3402:
---
Your explanation make senses. I'm agree for the RANGE.
But it's seem's different with ROWS
# Postgres (10.10 (doesn't try in 11 nor 12 but the documentation doesn't
mention it)) doesn't implement the RANGE/ROWS *interval*
# Postgres doesn't implement _RANGE BETWEEN 3 PRECEDING and 5 FOLLOWING_
# BUT Postgres implement ROWS +offset+ PRECEDING/FOLLOWING. The +offset+
doesn't apply just on rows preceding/following in the order specified by ORDER
BY (taking only the position, not the value)
Ok, (1) and (2) are missing implementation for Postgres.
For the point (3) specifically about ROWS, I don't know if Postgres follow
rightly the standard (that is not fully clear for me)
{noformat}
b) If ROWS is specified, then the declared type of UVS shall be exact numeric
with scale 0 (zero).
{noformat}
But Note that at least sqlite3 (from 3.26) adopt the same behaviour (that
naturally allows compound ORDER BY)
Don't know if it's exactly the standard way, but for sure it's very useful. And
it makes sense that RANGE and ROWS do not work the same.
{code:sql}
a,b
1,1
4,3
7,5
8,7
10,11
SELECT a, sum(b) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS
x FROM t5
/* Postgres and sqlite3 (>=3.26) */
a;x
1;4
4;9
7;15
8;23
10;18
/* /!\ work with Sqlite3 (>=3.26) BUT does not work with Postgres*/
SELECT a, sum(b) OVER (ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS
x FROM t5 /* using OF RANGE instead of ROWS */
a;x
1;1
4;3
7;12 /* aggregation done with next rows (a(current)==7 && a==8) => 5+7*/
8;12 /* aggregation done with previous rows (a(current)==8 && a==7) => 5+7 */
10;11
{code}
So Sqlite3 implement 2 distinct behaviour for ROWS and RANGE...
I'd like to check for other database system to see how it behave.
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16956606#comment-16956606
]
Julian Hyde commented on CALCITE-3402:
--
Only allowing a single key makes a lot of sense.
If {{i}} is an INTEGER column, you can have {{ORDER BY i RANGE BETWEEN 3
PRECEDING and 5 FOLLOWING}}. The range is an INTEGER expression. Why? Because
when you subtract an INTEGER from an INTEGER, the result is an INTEGER.
If {{t}} is a TIMESTAMP column, you can have {{ORDER BY t RANGE BETWEEN
INTERVAL '3' DAY PRECEDING and INTERVAL '5' DAY FOLLOWING}}. Why? Because when
you subtract a TIMESTAMP from a TIMESTAMP, the result is an INTERVAL.
If you have {{x}} and {{y}} are columns of any type, you can't have {{ORDER BY
x, y RANGE BETWEEN anything AND anything}}, because you can't subtract (x1, y1)
from (x2, y2).
(By the way, I'm pretty sure that PostgreSQL does allow {{ORDER BY t RANGE
BETWEEN INTERVAL '3' DAY PRECEDING and INTERVAL '5' DAY FOLLOWING}}, despite
your assertion that it does not.)
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16954457#comment-16954457
]
benj commented on CALCITE-3402:
---
I think the RANGE with compound may be problematic when the "" is an interval.
But do not see why it would prevent to work in other cases.
Not read any limitation that avoid ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16954417#comment-16954417
]
Wang Yanlin commented on CALCITE-3402:
--
I think you are right about "SQL03 7.10 Rule 11a".
The code in *SqlWindow* follows the stand, but I cannot see why the standard do
not allow range with compoud order by.
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16954409#comment-16954409
]
benj commented on CALCITE-3402:
---
"SQL03 7.10 Rule 11a" is probably a reference of the SQL 2003 standard §7.10
please find a link to a sql 2003 standard here:
[http://grigoras.perso.enseeiht.fr/ens/bd/5WD-02-Foundation-2003-09.pdf]
Note that I think in this version it's more probably § *7.11* Rulle 11a
(because 7.10 is for HAVING clause and 7.11 for WINDOW clause)
{noformat}
11)If WDEF's specifies or
,then let UVS be the
simply contained in the or.
Case:
a)If RANGE is specified, then WDEF's shall contain a
single SK.The declared type of SK shall be numeric, datetime, or
interval. The declared type of UVS shall benumeric if the declared type of SK
is numeric; otherwise, it shall be an interval type that may be addedto or
subtracted from the declared type of SK according to the Syntax Rules of
Subclause 6.30,“”, and Subclause 6.32, “”, in this part ofISO/IEC 9075.
b)If ROWS is specified, then the declared type of UVS shall be exact numeric
with scale 0 (zero).
{noformat}
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16954386#comment-16954386
]
Wang Yanlin commented on CALCITE-3402:
--
Add this test case in *JdbcTest* to reproduce
{code:java}
@Test public void testCompoudGroupBy() {
CalciteAssert.that()
.with(CalciteAssert.Config.FOODMART_CLONE)
.query("select x, count(*) over (partition by x order by x, y RANGE
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as num from\n"
+ "(select x, y from (values (1, 'a'), (2, 'b'), (3, 'c')) as t(x,
y))")
.returns(""
+ "the_year=1997; C=365; M=April\n"
+ "the_year=1998; C=365; M=April\n");
}
{code}
the related code is in SqlWindow.java
{code:java}
// SQL03 7.10 Rule 11a
if (orderList.size() > 0) {
// if order by is a compound list then range not allowed
if (orderList.size() > 1 && !isRows()) {
throw validator.newValidationError(isRows,
RESOURCE.compoundOrderByProhibitsRange());
}
{code}
What is "SQL03 7.10 Rule 11a"?
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16954354#comment-16954354
]
benj commented on CALCITE-3402:
---
Yes of course, these 2 expressions are well equivalent
* RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
* ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
I'm not sure of what you would expect with _RANGE BETWEEN INTERVAL '1' DAY
PRECEDING AND '2' DAY FOLLOWING_, but I like the ideas I imagine.
But not, for Postgres you can only do _value PRECEDING_ where +_value_+ is a
positive integer.
RANGE is only supported with the clause _UNBOUNDED PRECEDING/FOLLOWING_ and the
clause_CURRENT ROW_.
So As you say first, it will be ok if it will be possible to do _ROWS BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING_ with DRILL.
Unfortunately Apache Drill give an error with this expression:
{code:sql}
SELECT a
, last_value(c) OVER(PARTITION BY a
ORDER BY c, b DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM (SELECT 1 a, 'b' b, 3 c
UNION SELECT 2, 'c', 4
UNION SELECT 1, 'c', 4
/* UNION ... */
) x;
Error: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not
supported
See Apache Drill JIRA: DRILL-3188
{code}
Apparently, totday, with Drill, it's only possible to do :
* *ROWS* BETWEEN UNBOUNDED PRECEDING AND *CURRENT ROW* - with possibility of
+several ORDER BY+ clause
* *RANGE* BETWEEN UNBOUNDED PRECEDING AND *CURRENT ROW* - with possibility of
+only 1 ORDER BY+ clause
* *RANGE* BETWEEN UNBOUNDED PRECEDING AND *UNBOUNDED FOLLOWING* - with
possibility of +only 1 ORDER BY+ clause
The missing possibility is "_... AND UNBOUNDED FOLLOWING_" in relation with
several _ORDER BY_ clause
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16954089#comment-16954089
]
Julian Hyde commented on CALCITE-3402:
--
Maybe "{{RANGE BETWEEN unbounded PRECEDING AND unbounded FOLLOWING}}" is a
special case for PostgreSQL, because it can be rewritten to "{{ROWS BETWEEN
unbounded PRECEDING AND unbounded FOLLOWING}}".
Can PostgreSQL handle a composite ORDER BY with a finite RANGE, say "{{ORDER BY
orderDate, price RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND '2' DAY
FOLLOWING}}"?
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16952633#comment-16952633
]
benj commented on CALCITE-3402:
---
With Drill 1.16 and 1.17 (from 2019-10-14) ROWS is not either possible.
{code:sql}
Error: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not
supported
See Apache Drill JIRA: DRILL-3188
{code}
More precisely with ORDER BY, the only possibilities to write ROWS clause are
(the 2 possibilities are identical) :
* ROWS BETWEEN UNBOUNDED PRECEDING AND current row
* ROWS UNBOUNDED PRECEDING
>>If you use {{RANGE}} with a composite {{ORDER BY}}, does PostgreSQL apply the
>>range to just the first key? The documentation you referenced does not say.
No Postgres apply all the key and then apply the range on
{noformat}
The ORDER BY option determines the order in which the rows of a partition are
processed by the window function. It works similarly to a query-level ORDER BY
clause, but likewise cannot use output-column names or numbers. Without ORDER
BY, rows are processed in an unspecified order.{noformat}
in practice, with a table _tex_
{noformat}
a,b,c
1,1,1
2,2,1
3,2,2
4,3,1
5,3,2
6,3,3{noformat}
you can do
{code:sql}
-- Postgres
SELECT *
, last_value(a) OVER(ORDER BY b RANGE BETWEEN unbounded PRECEDING AND unbounded
FOLLOWING) sort_b
, last_value(a) OVER(ORDER BY b, c DESC RANGE BETWEEN unbounded PRECEDING AND
unbounded FOLLOWING) sort_b_and_c
FROM tex
WHERE a = 4
-- =>
4;3;1;6;4
-- The result of columns 4 and 5 well depends of all compound of the ORDER BY
-- !Postgres
{code}
toto
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause
[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16952372#comment-16952372
]
Julian Hyde commented on CALCITE-3402:
--
Did you try changing {{RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING}} to {{ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING}}?
That should work.
If you use {{RANGE}} with a composite {{ORDER BY}}, does PostgreSQL apply the
range to just the first key? The documentation you referenced does not say.
> Allow RANGE with compoud ORDER BY clause
>
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.18.0, 1.19.0
>Reporter: benj
>Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
