This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch 2.1-tmp
in repository https://gitbox.apache.org/repos/asf/doris.git
commit d61b9f709164d689bdfb4f5960c81a3ad038f264
Author: LiBinfeng <46676950+libinfeng...@users.noreply.github.com>
AuthorDate: Sun Apr 7 14:24:47 2024 +0800
[chore](test) nereids support window function but some cases does not open
yet (#33098)
---
.../data/nereids_p0/aggregate/aggregate.out | 322 +++++++-
.../window_functions/test_window_fn.out | 46 +-
.../window_functions/test_window_function.out | 132 ++--
.../suites/nereids_p0/aggregate/aggregate.groovy | 99 +--
.../suites/nereids_p0/join/test_join_15.groovy | 10 +-
.../suites/nereids_p0/keyword/order_group.groovy | 34 +-
.../test_select_stddev_variance_window.groovy | 258 +++----
.../sql_functions/window_functions/test_sum.groovy | 11 +-
.../window_functions/test_window_fn.groovy | 482 ++++++------
.../window_functions/test_window_function.groovy | 840 ++++++++++-----------
10 files changed, 1247 insertions(+), 987 deletions(-)
diff --git a/regression-test/data/nereids_p0/aggregate/aggregate.out
b/regression-test/data/nereids_p0/aggregate/aggregate.out
index f81a14f0ce0..9578f6bff09 100644
--- a/regression-test/data/nereids_p0/aggregate/aggregate.out
+++ b/regression-test/data/nereids_p0/aggregate/aggregate.out
@@ -36,7 +36,7 @@ TESTING AGAIN
14 99.8777 3309.4
-- !aggregate --
-3037.0783723835643 35.210273031541995
+3037.078372383564 35.210273031541995
-- !aggregate --
3145.0576394647455 35.21027303154199
@@ -45,7 +45,7 @@ TESTING AGAIN
3037.0783723835643 40.09062957713574
-- !aggregate --
-3143.674778708147 36.44609513116206
+3143.6747787081463 36.44609513116205
-- !aggregate --
3335.837376132116 36.446095131162046
@@ -60,28 +60,28 @@ TESTING AGAIN
32097 455.9169
-- !aggregate --
-9223845.040000001 1239.7633269557336
+9223845.039999997 1239.7633269557334
-- !aggregate --
-9891387.555555556 1239.763326955733
+9891387.555555556 1239.7633269557332
-- !aggregate --
9223845.04 1607.2585798911114
-- !aggregate --
-9882691.114285717 1328.3178503097147
+9882691.114285711 1328.3178503097145
-- !aggregate --
-1.1127811E7 1328.3178503097138
+1.1127811E7 1328.317850309714
-- !aggregate --
9882691.114285713 1808.1659023775003
-- !aggregate --
-9223845.040000001 1239.7633269557336
+9223845.039999997 1239.7633269557334
-- !aggregate --
-9891387.555555556 1239.763326955733
+9891387.555555556 1239.7633269557332
-- !aggregate --
9223845.04 1607.2585798911114
@@ -126,6 +126,24 @@ TESTING AGAIN
-- !aggregate --
12 12.25 String1 1999-01-08 1999-01-08T02:05:06 1999-01-08
1999-01-08T02:05:06 \N 1999-01-08T02:05:06.111111 true \N
123456789012345678.0123456780
+-- !aggregate1 --
+\N \N
+-32767 2147484649
+-32767 2147484649
+255 2147483750
+255 2147483750
+1985 5014
+1986 1001
+1989 27702
+1989 27702
+1989 27702
+1991 -2147475612
+1991 -2147475612
+1991 -2147475612
+1992 3021
+32767 -2147480626
+32767 -2147480626
+
-- !aggregate2 --
-32767 2147484649
-32767 2147484649
@@ -143,6 +161,42 @@ TESTING AGAIN
32767 -2147480626
32767 -2147480626
+-- !aggregate3 --
+\N \N
+-32767 2147484649
+-32767 2147484649
+255 2147483750
+255 2147483750
+1985 5014
+1986 1001
+1989 27702
+1989 27702
+1989 27702
+1991 -2147475612
+1991 -2147475612
+1991 -2147475612
+1992 3021
+32767 -2147480626
+32767 -2147480626
+
+-- !aggregate4 --
+\N \N
+-32767 1002
+-32767 1002
+1985 5014
+1986 1001
+1989 1001
+1989 1001
+1989 1001
+1991 -2147483647
+1991 -2147483647
+1991 -2147483647
+1992 3021
+255 103
+255 103
+32767 -2147483647
+32767 -2147483647
+
-- !aggregate5 --
-32767 1002
-32767 1002
@@ -160,6 +214,24 @@ TESTING AGAIN
32767 -2147483647
32767 -2147483647
+-- !aggregate6 --
+\N \N
+-32767 2147483647
+-32767 2147483647
+255 2147483647
+255 2147483647
+1985 5014
+1986 1001
+1989 25699
+1989 25699
+1989 25699
+1991 5014
+1991 5014
+1991 5014
+1992 3021
+32767 3021
+32767 3021
+
-- !aggregate7 --
-32767 2147483647
-32767 2147483647
@@ -177,6 +249,24 @@ TESTING AGAIN
32767 3021
32767 3021
+-- !aggregate8 --
+\N 0
+-32767 2
+-32767 2
+255 2
+255 2
+1985 1
+1986 1
+1989 3
+1989 3
+1989 3
+1991 3
+1991 3
+1991 3
+1992 1
+32767 2
+32767 2
+
-- !aggregate9 --
-32767 2
-32767 2
@@ -194,6 +284,60 @@ TESTING AGAIN
32767 2
32767 2
+-- !aggregate10 --
+\N \N 0
+-32767 jiw3n4 1
+-32767 wenlsfnl 1
+255 1
+255 wangjuoo5 1
+1985 du3lnvl 1
+1986 wangynnsf 1
+1989 wangjuoo4 1
+1989 yunlj8@nk 2
+1989 yunlj8@nk 2
+1991 wangjuoo4 1
+1991 wangynnsf 1
+1991 yanvjldjlll 1
+1992 1
+32767 lifsno 1
+32767 yanavnd 1
+
+-- !aggregate11 --
+\N 0
+-32767 2
+-32767 2
+255 2
+255 2
+1985 1
+1986 1
+1989 3
+1989 3
+1989 3
+1991 3
+1991 3
+1991 3
+1992 1
+32767 2
+32767 2
+
+-- !aggregate12 --
+\N 0
+-32767 2
+-32767 2
+255 2
+255 2
+1985 1
+1986 1
+1989 3
+1989 3
+1989 3
+1991 3
+1991 3
+1991 3
+1992 1
+32767 2
+32767 2
+
-- !aggregate13 --
-32767 2
-32767 2
@@ -228,6 +372,60 @@ TESTING AGAIN
32767 lifsno 1
32767 yanavnd 1
+-- !aggregate15 --
+\N \N \N
+-32767 jiw3n4 1002
+-32767 wenlsfnl 2147483647
+255 103
+255 wangjuoo5 2147483647
+1985 du3lnvl 5014
+1986 wangynnsf 1001
+1989 wangjuoo4 1001
+1989 yunlj8@nk 25699
+1989 yunlj8@nk 25699
+1991 wangjuoo4 -2147483647
+1991 wangynnsf 5014
+1991 yanvjldjlll 3021
+1992 3021
+32767 lifsno -2147483647
+32767 yanavnd 3021
+
+-- !aggregate16 --
+\N \N
+-32767 2147483647
+-32767 2147483647
+255 2147483647
+255 2147483647
+1985 5014
+1986 1001
+1989 25699
+1989 25699
+1989 25699
+1991 5014
+1991 5014
+1991 5014
+1992 3021
+32767 3021
+32767 3021
+
+-- !aggregate17 --
+\N \N
+-32767 2147483647
+-32767 2147483647
+255 2147483647
+255 2147483647
+1985 5014
+1986 1001
+1989 25699
+1989 25699
+1989 25699
+1991 5014
+1991 5014
+1991 5014
+1992 3021
+32767 3021
+32767 3021
+
-- !aggregate18 --
-32767 2147483647
-32767 2147483647
@@ -262,6 +460,60 @@ TESTING AGAIN
32767 lifsno -2147483647
32767 yanavnd 3021
+-- !aggregate20 --
+\N \N \N
+-32767 jiw3n4 1002
+-32767 wenlsfnl 2147483647
+255 103
+255 wangjuoo5 2147483647
+1985 du3lnvl 5014
+1986 wangynnsf 1001
+1989 wangjuoo4 1001
+1989 yunlj8@nk 1002
+1989 yunlj8@nk 1002
+1991 wangjuoo4 -2147483647
+1991 wangynnsf 5014
+1991 yanvjldjlll 3021
+1992 3021
+32767 lifsno -2147483647
+32767 yanavnd 3021
+
+-- !aggregate21 --
+\N \N
+-32767 1002
+-32767 1002
+255 103
+255 103
+1985 5014
+1986 1001
+1989 1001
+1989 1001
+1989 1001
+1991 -2147483647
+1991 -2147483647
+1991 -2147483647
+1992 3021
+32767 -2147483647
+32767 -2147483647
+
+-- !aggregate22 --
+\N \N
+-32767 1002
+-32767 1002
+255 103
+255 103
+1985 5014
+1986 1001
+1989 1001
+1989 1001
+1989 1001
+1991 -2147483647
+1991 -2147483647
+1991 -2147483647
+1992 3021
+32767 -2147483647
+32767 -2147483647
+
-- !aggregate23 --
-32767 1002
-32767 1002
@@ -296,6 +548,60 @@ TESTING AGAIN
32767 lifsno -2147483647
32767 yanavnd 3021
+-- !aggregate25 --
+\N \N \N
+-32767 jiw3n4 1002
+-32767 wenlsfnl 2147483647
+255 103
+255 wangjuoo5 2147483647
+1985 du3lnvl 5014
+1986 wangynnsf 1001
+1989 wangjuoo4 1001
+1989 yunlj8@nk 26701
+1989 yunlj8@nk 26701
+1991 wangjuoo4 -2147483647
+1991 wangynnsf 5014
+1991 yanvjldjlll 3021
+1992 3021
+32767 lifsno -2147483647
+32767 yanavnd 3021
+
+-- !aggregate26 --
+\N \N
+-32767 2147484649
+-32767 2147484649
+255 2147483750
+255 2147483750
+1985 5014
+1986 1001
+1989 27702
+1989 27702
+1989 27702
+1991 -2147475612
+1991 -2147475612
+1991 -2147475612
+1992 3021
+32767 -2147480626
+32767 -2147480626
+
+-- !aggregate27 --
+\N \N
+-32767 2147484649
+-32767 2147484649
+255 2147483750
+255 2147483750
+1985 5014
+1986 1001
+1989 27702
+1989 27702
+1989 27702
+1991 -2147475612
+1991 -2147475612
+1991 -2147475612
+1992 3021
+32767 -2147480626
+32767 -2147480626
+
-- !aggregate28 --
-32767 2147484649
-32767 2147484649
diff --git
a/regression-test/data/nereids_p0/sql_functions/window_functions/test_window_fn.out
b/regression-test/data/nereids_p0/sql_functions/window_functions/test_window_fn.out
index 06b1d657cda..ebb0ebed2f2 100644
---
a/regression-test/data/nereids_p0/sql_functions/window_functions/test_window_fn.out
+++
b/regression-test/data/nereids_p0/sql_functions/window_functions/test_window_fn.out
@@ -27,11 +27,11 @@
0 0 0
0 0 0
0 4 0
+0 0 2
1 1 1
1 1 1
1 7 1
1 9 1
-0 0 2
1 1 3
1 3 3
@@ -64,8 +64,8 @@
0 0 0
0 0 2
1 1 1
-1 1 3
1 1 1
+1 1 3
3 3 3
4 4 0
7 7 1
@@ -74,14 +74,14 @@
-- !sql --
0 0 0
0 0 0
-4 4 0
+0 0 2
1 1 1
1 1 1
-7 7 1
-9 9 1
-0 0 2
1 1 3
3 3 3
+4 4 0
+7 7 1
+9 9 1
-- !sql --
0 0 0 0
@@ -329,6 +329,30 @@ develop 11 2007-08-15 5200 9
1 1 3
2 3 3
+-- !sql --
+0.0 0 0
+0.0 0 0
+1.0 4 0
+0.0 1 1
+0.0 1 1
+0.6666666666666666 7 1
+1.0 9 1
+0.0 0 2
+0.0 1 3
+1.0 3 3
+
+-- !sql --
+0.6666666666666666 0 0
+0.6666666666666666 0 0
+1.0 4 0
+0.5 1 1
+0.5 1 1
+0.75 7 1
+1.0 9 1
+1.0 0 2
+0.5 1 3
+1.0 3 3
+
-- !sql --
0 9976146 4
1 10114187 9
@@ -625,3 +649,13 @@ sales 4 4800 14600
10 30
11 21
+-- !sql_window_last_value --
+1 gz 30000 \N
+2 gz 25000 30000
+3 gz 17000 25000
+4 gz 32000 17000
+5 sz 40000 \N
+6 sz 27000 40000
+7 sz 27000 27000
+8 sz 33000 27000
+
diff --git
a/regression-test/data/nereids_p0/sql_functions/window_functions/test_window_function.out
b/regression-test/data/nereids_p0/sql_functions/window_functions/test_window_function.out
index fe0ee1764e2..5cfe5a4280b 100644
---
a/regression-test/data/nereids_p0/sql_functions/window_functions/test_window_function.out
+++
b/regression-test/data/nereids_p0/sql_functions/window_functions/test_window_function.out
@@ -1,21 +1,12 @@
-- This file is automatically generated. You should know what you did if you
want to edit this
-- !sql --
-1
-2
-3
-4
-5
-6
-7
-
--- !sql --
-JDR 2014-10-02T00:00 12.86 12.875
-JDR 2014-10-03T00:00 12.89 12.896666667
-JDR 2014-10-04T00:00 12.94 12.793333333
-JDR 2014-10-05T00:00 12.55 13.173333333
-JDR 2014-10-06T00:00 14.03 13.776666667
-JDR 2014-10-07T00:00 14.75 14.253333333
-JDR 2014-10-08T00:00 13.98 14.365
+JDR 2014-10-02T00:00 12.86 12.8750
+JDR 2014-10-03T00:00 12.89 12.8966
+JDR 2014-10-04T00:00 12.94 12.7933
+JDR 2014-10-05T00:00 12.55 13.1733
+JDR 2014-10-06T00:00 14.03 13.7766
+JDR 2014-10-07T00:00 14.75 14.2533
+JDR 2014-10-08T00:00 13.98 14.3650
-- !sql --
JDR 2014-10-02T00:00 12.86 higher
@@ -36,7 +27,7 @@ JDR 2014-10-08T00:00 13.98 flat or lower
2014-10-02T00:00
-- !sql --
-JDR 2014-10-02T00:00 12.86 0
+JDR 2014-10-02T00:00 12.86 0.00
JDR 2014-10-03T00:00 12.89 12.86
JDR 2014-10-04T00:00 12.94 12.89
JDR 2014-10-05T00:00 12.55 12.94
@@ -45,13 +36,13 @@ JDR 2014-10-07T00:00 14.75 14.03
JDR 2014-10-08T00:00 13.98 14.75
-- !sql --
-JDR 2014-10-02T00:00 12.86 12.875
-JDR 2014-10-03T00:00 12.89 12.896666667
-JDR 2014-10-04T00:00 12.94 12.793333333
-JDR 2014-10-05T00:00 12.55 13.173333333
-JDR 2014-10-06T00:00 14.03 13.776666667
-JDR 2014-10-07T00:00 14.75 14.253333333
-JDR 2014-10-08T00:00 13.98 14.365
+JDR 2014-10-02T00:00 12.86 12.8750
+JDR 2014-10-03T00:00 12.89 12.8966
+JDR 2014-10-04T00:00 12.94 12.7933
+JDR 2014-10-05T00:00 12.55 13.1733
+JDR 2014-10-06T00:00 14.03 13.7766
+JDR 2014-10-07T00:00 14.75 14.2533
+JDR 2014-10-08T00:00 13.98 14.3650
-- !sql --
JDR 2014-10-02T00:00 12.86 higher
@@ -72,7 +63,7 @@ JDR 2014-10-08T00:00 13.98 flat or lower
2014-10-02T00:00
-- !sql --
-JDR 2014-10-02T00:00 12.86 0
+JDR 2014-10-02T00:00 12.86 0.00
JDR 2014-10-03T00:00 12.89 12.86
JDR 2014-10-04T00:00 12.94 12.89
JDR 2014-10-05T00:00 12.55 12.94
@@ -81,13 +72,13 @@ JDR 2014-10-07T00:00 14.75 14.03
JDR 2014-10-08T00:00 13.98 14.75
-- !sql --
-JDR 2014-10-02T00:00:00.111 12.86 12.875
-JDR 2014-10-03T00:00:00.111 12.89 12.896666667
-JDR 2014-10-04T00:00:00.111 12.94 12.793333333
-JDR 2014-10-05T00:00:00.111 12.55 13.173333333
-JDR 2014-10-06T00:00:00.111 14.03 13.776666667
-JDR 2014-10-07T00:00:00.111 14.75 14.253333333
-JDR 2014-10-08T00:00:00.111 13.98 14.365
+JDR 2014-10-02T00:00:00.111 12.86 12.8750
+JDR 2014-10-03T00:00:00.111 12.89 12.8966
+JDR 2014-10-04T00:00:00.111 12.94 12.7933
+JDR 2014-10-05T00:00:00.111 12.55 13.1733
+JDR 2014-10-06T00:00:00.111 14.03 13.7766
+JDR 2014-10-07T00:00:00.111 14.75 14.2533
+JDR 2014-10-08T00:00:00.111 13.98 14.3650
-- !sql --
JDR 2014-10-02T00:00:00.111 12.86 higher
@@ -108,7 +99,7 @@ JDR 2014-10-08T00:00:00.111 13.98 flat or lower
2014-10-02T00:00
-- !sql --
-JDR 2014-10-02T00:00:00.111 12.86 0
+JDR 2014-10-02T00:00:00.111 12.86 0.00
JDR 2014-10-03T00:00:00.111 12.89 12.86
JDR 2014-10-04T00:00:00.111 12.94 12.89
JDR 2014-10-05T00:00:00.111 12.55 12.94
@@ -117,13 +108,13 @@ JDR 2014-10-07T00:00:00.111 14.75 14.03
JDR 2014-10-08T00:00:00.111 13.98 14.75
-- !sql --
-JDR 2014-10-02T00:00:00.111111 12.86 12.875
-JDR 2014-10-03T00:00:00.111111 12.89 12.896666667
-JDR 2014-10-04T00:00:00.111111 12.94 12.793333333
-JDR 2014-10-05T00:00:00.111111 12.55 13.173333333
-JDR 2014-10-06T00:00:00.111111 14.03 13.776666667
-JDR 2014-10-07T00:00:00.111111 14.75 14.253333333
-JDR 2014-10-08T00:00:00.111111 13.98 14.365
+JDR 2014-10-02T00:00:00.111111 12.86 12.8750
+JDR 2014-10-03T00:00:00.111111 12.89 12.8966
+JDR 2014-10-04T00:00:00.111111 12.94 12.7933
+JDR 2014-10-05T00:00:00.111111 12.55 13.1733
+JDR 2014-10-06T00:00:00.111111 14.03 13.7766
+JDR 2014-10-07T00:00:00.111111 14.75 14.2533
+JDR 2014-10-08T00:00:00.111111 13.98 14.3650
-- !sql --
JDR 2014-10-02T00:00:00.111111 12.86 higher
@@ -144,7 +135,7 @@ JDR 2014-10-08T00:00:00.111111 13.98 flat or lower
2014-10-02T00:00
-- !sql --
-JDR 2014-10-02T00:00:00.111111 12.86 0
+JDR 2014-10-02T00:00:00.111111 12.86 0.00
JDR 2014-10-03T00:00:00.111111 12.89 12.86
JDR 2014-10-04T00:00:00.111111 12.94 12.89
JDR 2014-10-05T00:00:00.111111 12.55 12.94
@@ -214,6 +205,28 @@ JDR 2014-10-08T00:00:00.111111 13.98 14.75
3 1 1
3 2 2
+-- !sql --
+1 1 0.0
+1 2 0.5
+1 2 0.5
+2 1 0.0
+2 2 0.5
+2 3 1.0
+3 1 0.0
+3 1 0.0
+3 2 1.0
+
+-- !sql --
+1 1 0.3333333333333333
+1 2 1.0
+1 2 1.0
+2 1 0.3333333333333333
+2 2 0.6666666666666666
+2 3 1.0
+3 1 0.6666666666666666
+3 1 0.6666666666666666
+3 2 1.0
+
-- !sql --
1 1 1
1 2 2
@@ -526,41 +539,6 @@ USA Pete Hello
9223372036854775807 1
9223372036854775807 2
--- !window_hang2 --
--9223372036854775807 1
--9223372036854775807 2
--11011907 1
--11011903 1
-123456 1
-7210457 1
-11011902 1
-11011902 2
-11011902 3
-11011903 1
-11011905 1
-11011920 1
-11011920 2
-9223372036854775807 1
-9223372036854775807 2
-
--- !hujie1 --
-\N \N
-1 true
-2 false
-3 false
-4 false
-5 true
-6 true
-7 false
-8 true
-9 true
-10 false
-11 true
-12 false
-13 false
-14 false
-15 true
-
-- !hujie2 --
\N \N
1 true
@@ -618,5 +596,3 @@ USA Pete Hello
32767 6 12 32767
32767 6 6 32767
-
-
diff --git a/regression-test/suites/nereids_p0/aggregate/aggregate.groovy
b/regression-test/suites/nereids_p0/aggregate/aggregate.groovy
index 760333f3e78..c69e80eca35 100644
--- a/regression-test/suites/nereids_p0/aggregate/aggregate.groovy
+++ b/regression-test/suites/nereids_p0/aggregate/aggregate.groovy
@@ -178,8 +178,7 @@ suite("aggregate") {
// test_query_normal_aggression
String k1 = fields[1]
String k2 = fields[2]
- // Nereids does't support window function
- // qt_aggregate1"select ${k1}, sum(${k2}) over (partition by ${k1}) as wj
from baseall order by ${k1}, wj"
+ qt_aggregate1"select ${k1}, sum(${k2}) over (partition by ${k1}) as wj
from baseall order by ${k1}, wj"
qt_aggregate2"""
select t1.${k1}, t2.mysum from baseall t1,
(select ${k1}, sum(${k2}) as mysum from baseall
@@ -187,26 +186,22 @@ suite("aggregate") {
order by t1.${k1}, t2.mysum
"""
- // Nereids does't support window function
- // qt_aggregate3"select * from (select ${k1}, sum(${k2}) over (partition
by ${k1}) as wj from baseall) b order by ${k1}, wj"
- // Nereids does't support window function
- // order_qt_aggregate4"select ${k1}, min(${k2}) over (partition by ${k1})
as wj from baseall order by ${k1}, wj"
+ qt_aggregate3"select * from (select ${k1}, sum(${k2}) over (partition by
${k1}) as wj from baseall) b order by ${k1}, wj"
+ order_qt_aggregate4"select ${k1}, min(${k2}) over (partition by ${k1}) as
wj from baseall order by ${k1}, wj"
qt_aggregate5"""
select t1.${k1}, t2.mysum from baseall t1,
(select ${k1}, min(${k2}) as mysum from baseall
group by ${k1}) t2 where t1.${k1}=t2.${k1}
order by t1.${k1}, t2.mysum
"""
- // Nereids does't support window function
- // qt_aggregate6"select ${k1}, max(${k2}) over (partition by ${k1}) as wj
from baseall order by ${k1}, wj"
+ qt_aggregate6"select ${k1}, max(${k2}) over (partition by ${k1}) as wj
from baseall order by ${k1}, wj"
qt_aggregate7"""
select t1.${k1}, t2.mysum from baseall t1,
(select ${k1}, max(${k2}) as mysum from baseall
group by ${k1}) t2 where t1.${k1}=t2.${k1}
order by t1.${k1}, t2.mysum
"""
- // Nereids does't support window function
- // qt_aggregate8"select ${k1}, count(${k2}) over (partition by ${k1}) as
wj from baseall order by ${k1}, wj"
+ qt_aggregate8"select ${k1}, count(${k2}) over (partition by ${k1}) as wj
from baseall order by ${k1}, wj"
qt_aggregate9"""select t1.${k1}, t2.mysum from baseall t1,
(select ${k1}, count(${k2}) as mysum from baseall
group by ${k1}) t2 where t1.${k1}=t2.${k1}
@@ -216,18 +211,15 @@ suite("aggregate") {
// test_query_normal_order_aggression
String k3 = fields[8]
String k8 = fields[9]
- // Nereids does't support window function
- // qt_aggregate10"select ${k1}, ${k3}, count(${k2}) over (partition by
${k1}, ${k3} order by ${k3}) as wj from baseall order by ${k1}, ${k3}, wj"
- // Nereids does't support window function
- // qt_aggregate11"""select ${k1}, count(${k2}) over (partition by ${k1}
order by ${k3}
- // range between unbounded preceding and unbounded following)
- // as wj from baseall order by ${k1}, wj"""
- // Nereids does't support window function
- // qt_aggregate12"""
- // select ${k1}, count(${k2}) over (partition by ${k1} order by
${k3}
- // rows between unbounded preceding and unbounded following)
- // as wj from baseall order by ${k1}, wj
- // """
+ qt_aggregate10"select ${k1}, ${k3}, count(${k2}) over (partition by ${k1},
${k3} order by ${k3}) as wj from baseall order by ${k1}, ${k3}, wj"
+ qt_aggregate11"""select ${k1}, count(${k2}) over (partition by ${k1} order
by ${k3}
+ range between unbounded preceding and unbounded following)
+ as wj from baseall order by ${k1}, wj"""
+ qt_aggregate12"""
+ select ${k1}, count(${k2}) over (partition by ${k1} order by ${k3}
+ rows between unbounded preceding and unbounded following)
+ as wj from baseall order by ${k1}, wj
+ """
qt_aggregate13"""
select t1.${k1}, t2.mysum from baseall t1,
(select ${k1}, count(${k2}) as mysum from baseall
@@ -240,17 +232,14 @@ suite("aggregate") {
group by ${k1}, ${k3}) t2 where t1.${k1}=t2.${k1} and
t1.${k3}=t2.${k3}
order by t1.${k1}, t1.${k3}, t2.mysum
"""
- // Nereids does't support window function
- // qt_aggregate15"""select ${k1}, ${k3}, max(${k2}) over (partition by
${k1}, ${k3} order by ${k3})
- // as wj from baseall order by ${k1}, ${k3}, wj"""
- // Nereids does't support window function
- // qt_aggregate16"""select ${k1}, max(${k2}) over (partition by ${k1}
order by ${k3}
- // range between unbounded preceding and unbounded following)
- // as wj from baseall order by ${k1}, wj"""
- // Nereids does't support window function
- // qt_aggregate17"""select ${k1}, max(${k2}) over (partition by ${k1}
order by ${k3}
- // rows between unbounded preceding and unbounded following)
- // as wj from baseall order by ${k1}, wj"""
+ qt_aggregate15"""select ${k1}, ${k3}, max(${k2}) over (partition by ${k1},
${k3} order by ${k3})
+ as wj from baseall order by ${k1}, ${k3}, wj"""
+ qt_aggregate16"""select ${k1}, max(${k2}) over (partition by ${k1} order
by ${k3}
+ range between unbounded preceding and unbounded following)
+ as wj from baseall order by ${k1}, wj"""
+ qt_aggregate17"""select ${k1}, max(${k2}) over (partition by ${k1} order
by ${k3}
+ rows between unbounded preceding and unbounded following)
+ as wj from baseall order by ${k1}, wj"""
qt_aggregate18"""select t1.${k1}, t2.mysum from baseall t1,
(select ${k1}, max(${k2}) as mysum from baseall
group by ${k1}) t2 where t1.${k1}=t2.${k1}
@@ -259,17 +248,14 @@ suite("aggregate") {
(select ${k1}, ${k3}, max(${k2}) as mysum from baseall
group by ${k1}, ${k3}) t2 where t1.${k1}=t2.${k1} and
t1.${k3}=t2.${k3}
order by t1.${k1}, t1.${k3}, t2.mysum"""
- // Nereids does't support window function
- // qt_aggregate20"""select ${k1}, ${k3}, min(${k2}) over (partition by
${k1}, ${k3} order by ${k3})
- // as wj from baseall order by ${k1}, ${k3}, wj"""
- // Nereids does't support window function
- // qt_aggregate21"""select ${k1}, min(${k2}) over (partition by ${k1}
order by ${k3}
- // range between unbounded preceding and unbounded following)
- // as wj from baseall order by ${k1}, wj"""
- // Nereids does't support window function
- // qt_aggregate22"""select ${k1}, min(${k2}) over (partition by ${k1}
order by ${k3}
- // rows between unbounded preceding and unbounded following)
- // as wj from baseall order by ${k1}, wj"""
+ qt_aggregate20"""select ${k1}, ${k3}, min(${k2}) over (partition by ${k1},
${k3} order by ${k3})
+ as wj from baseall order by ${k1}, ${k3}, wj"""
+ qt_aggregate21"""select ${k1}, min(${k2}) over (partition by ${k1} order
by ${k3}
+ range between unbounded preceding and unbounded following)
+ as wj from baseall order by ${k1}, wj"""
+ qt_aggregate22"""select ${k1}, min(${k2}) over (partition by ${k1} order
by ${k3}
+ rows between unbounded preceding and unbounded following)
+ as wj from baseall order by ${k1}, wj"""
qt_aggregate23"""select t1.${k1}, t2.mysum from baseall t1,
(select ${k1}, min(${k2}) as mysum from baseall
group by ${k1}) t2 where t1.${k1}=t2.${k1}
@@ -278,20 +264,17 @@ suite("aggregate") {
(select ${k1}, ${k3}, min(${k2}) as mysum from baseall
group by ${k1}, ${k3}) t2 where t1.${k1}=t2.${k1} and
t1.${k3}=t2.${k3}
order by t1.${k1}, t1.${k3}, t2.mysum"""
- // Nereids does't support window function
- // qt_aggregate25"""select ${k1}, ${k3}, sum(${k2}) over (partition by
${k1}, ${k3} order by ${k3})
- // as wj from baseall order by ${k1}, ${k3}, wj
- // """
- // Nereids does't support window function
- // qt_aggregate26"""select ${k1}, sum(${k2}) over (partition by ${k1}
order by ${k3}
- // range between unbounded preceding and unbounded following)
- // as wj from baseall order by ${k1}, wj
- // """
- // Nereids does't support window function
- // qt_aggregate27"""select ${k1}, sum(${k2}) over (partition by ${k1}
order by ${k3}
- // rows between unbounded preceding and unbounded following)
- // as wj from baseall order by ${k1}, wj
- // """
+ qt_aggregate25"""select ${k1}, ${k3}, sum(${k2}) over (partition by ${k1},
${k3} order by ${k3})
+ as wj from baseall order by ${k1}, ${k3}, wj
+ """
+ qt_aggregate26"""select ${k1}, sum(${k2}) over (partition by ${k1} order
by ${k3}
+ range between unbounded preceding and unbounded following)
+ as wj from baseall order by ${k1}, wj
+ """
+ qt_aggregate27"""select ${k1}, sum(${k2}) over (partition by ${k1} order
by ${k3}
+ rows between unbounded preceding and unbounded following)
+ as wj from baseall order by ${k1}, wj
+ """
qt_aggregate28"""select t1.${k1}, t2.mysum from baseall t1,
(select ${k1}, sum(${k2}) as mysum from baseall
group by ${k1}) t2 where t1.${k1}=t2.${k1}
diff --git a/regression-test/suites/nereids_p0/join/test_join_15.groovy
b/regression-test/suites/nereids_p0/join/test_join_15.groovy
index 22e6f8a06a3..2a1487b79f7 100644
--- a/regression-test/suites/nereids_p0/join/test_join_15.groovy
+++ b/regression-test/suites/nereids_p0/join/test_join_15.groovy
@@ -208,11 +208,9 @@ suite("test_join_15", "nereids_p0") {
select * from join_null_safe_equal_1 a left join
join_null_safe_equal_1 b on b.k3<=>a.k3 and b.k1 > a.k1 order by a.k1, b.k1;
"""
// windows
- // Nereids does't support window function
- // def res97 = sql"""select * from (select k1, k2, sum(k2) over (partition
by k1) as ss from ${null_table_2})a
- // left join ${null_table_1} b on a.k2=b.k2 and a.k1 >b.k1 order by
a.k1, b.k1"""
+ def res97 = sql"""select * from (select k1, k2, sum(k2) over (partition
by k1) as ss from ${null_table_2})a
+ left join ${null_table_1} b on a.k2=b.k2 and a.k1 >b.k1 order by
a.k1, b.k1"""
def res98 = sql"""select * from (select k1, k2, k5 from ${null_table_2}) a
left join ${null_table_1} b
on a.k2=b.k2 and a.k1 >b.k1 order by a.k1, b.k1"""
- // Nereids does't support window function
- // check2_doris(res97, res98)
-}
\ No newline at end of file
+ check2_doris(res97, res98)
+}
diff --git a/regression-test/suites/nereids_p0/keyword/order_group.groovy
b/regression-test/suites/nereids_p0/keyword/order_group.groovy
index ddf81b9cdd2..e84b5421814 100644
--- a/regression-test/suites/nereids_p0/keyword/order_group.groovy
+++ b/regression-test/suites/nereids_p0/keyword/order_group.groovy
@@ -168,16 +168,13 @@ suite("order_group", "query,p0") {
qt_orderBy_withNull_7 "select k6 + k5 as nu, sum(1) from test group by nu
order by nu limit 5"
// 窗口函数对NULL的处理
- // Nereids does't support window function
- // def res3 = sql"select k1, k2, nu from (select k1, k2, k5, k5 + k6 as
nu,\
- // sum(k2) over (partition by k5 + k6)\
- // as ss from ${tableName2})s where s.k5 > 2000 order by k1 nulls
first"
- // Nereids does't support window function
- // def res4 = sql"select k1, k2, nu from (select k1, k2, k5, k5 + k6 as
nu,\
- // sum(k2) over (partition by k5 + k6)\
- // as ss from ${tableName2} where k5 > 2000 )s order by k1"
- // Nereids does't support window function
- // check2_doris(res3, res4)
+ def res3 = sql"select k1, k2, nu from (select k1, k2, k5, k5 + k6 as nu,\
+ sum(k2) over (partition by k5 + k6)\
+ as ss from ${tableName2})s where s.k5 > 2000 order by k1 nulls first"
+ def res4 = sql"select k1, k2, nu from (select k1, k2, k5, k5 + k6 as nu,\
+ sum(k2) over (partition by k5 + k6)\
+ as ss from ${tableName2} where k5 > 2000 )s order by k1"
+ check2_doris(res3, res4)
// 2
// 非NULL结果
@@ -203,16 +200,13 @@ suite("order_group", "query,p0") {
def res6 = order_sql"""select k6 + k5 as nu, sum(k1) from ${tableName1}
group by nu order by nu, sum(k1)"""
check2_doris(res5, res6)
//issue https://github.com/apache/doris/issues/2142
- // Nereids does't support window function
- // def res7 = sql "select k1, k2, nu from (select k1, k2, k5, k5 + k6 as
nu,\
- // sum(k2) over (partition by k5 + k6)\
- // as ss from ${tableName2})s where s.k5 > 2000 order by k1,k2 nulls
last"
- // Nereids does't support window function
- // def res8 = sql "select k1, k2, nu from (select k1, k2, k5, k5 + k6 as
nu,\
- // sum(k2) over (partition by k5 + k6)\
- // as ss from ${tableName2} where k5 > 2000 )s order by k1,k2 "
- // Nereids does't support window function
- // check2_doris(res7, res8)
+ def res7 = sql "select k1, k2, nu from (select k1, k2, k5, k5 + k6 as nu,\
+ sum(k2) over (partition by k5 + k6)\
+ as ss from ${tableName2})s where s.k5 > 2000 order by k1,k2 nulls
last"
+ def res8 = sql "select k1, k2, nu from (select k1, k2, k5, k5 + k6 as nu,\
+ sum(k2) over (partition by k5 + k6)\
+ as ss from ${tableName2} where k5 > 2000 )s order by k1,k2 "
+ check2_doris(res7, res8)
qt_group31 "select count(*) from ${tableName1} where (k11='2015-03-13
12:36:38' or k11 = '2000-01-01 00:00:00')\
and k5 is not null group by k1%2, k2%2, k3%3, k4%3, k11%2
order by count(*)"
diff --git
a/regression-test/suites/nereids_p0/sql_functions/window_functions/test_select_stddev_variance_window.groovy
b/regression-test/suites/nereids_p0/sql_functions/window_functions/test_select_stddev_variance_window.groovy
index ca62aa14834..17d634a3bcc 100644
---
a/regression-test/suites/nereids_p0/sql_functions/window_functions/test_select_stddev_variance_window.groovy
+++
b/regression-test/suites/nereids_p0/sql_functions/window_functions/test_select_stddev_variance_window.groovy
@@ -80,135 +80,135 @@ suite("test_select_stddev_variance_window") {
}
sql "sync"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and unbounded following) from
${tableName} order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 preceding) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 following) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_pop(k2) over (partition by k6
order by k1 rows between current row and current row) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_pop(k2) over (partition by k6
order by k1 rows between current row and unbounded following) from
${tableName} order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_pop(k2) over (partition by k6
order by k1) from ${tableName} order by k1;"
-
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and unbounded following) from
${tableName} order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 preceding) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 following) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_samp(k2) over (partition by k6
order by k1 rows between current row and current row) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_samp(k2) over (partition by k6
order by k1 rows between current row and unbounded following) from
${tableName} order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_samp(k2) over (partition by k6
order by k1) from ${tableName} order by k1;"
-
- // Nereids does't support window function
- // qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and unbounded following) from
${tableName} order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 preceding) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 following) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between current row and current row) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between current row and unbounded following) from
${tableName} order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1) from ${tableName} order by k1;"
-
- // Nereids does't support window function
- // qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and unbounded following) from
${tableName} order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 preceding) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 following) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between current row and current row) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between current row and unbounded following) from
${tableName} order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1) from ${tableName} order by k1;"
-
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and unbounded following) from ${tableName}
order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 preceding) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 following) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_pop(k2) over (partition by k6
order by k1 rows between current row and current row) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_pop(k2) over (partition by k6
order by k1 rows between current row and unbounded following) from ${tableName}
order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_pop(k2) over (partition by k6
order by k1) from ${tableName} order by k1;"
-
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and unbounded following) from ${tableName}
order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 preceding) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 following) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_samp(k2) over (partition by k6
order by k1 rows between current row and current row) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_samp(k2) over (partition by k6
order by k1 rows between current row and unbounded following) from ${tableName}
order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, stddev_samp(k2) over (partition by k6
order by k1) from ${tableName} order by k1;"
-
- // Nereids does't support window function
- // qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and unbounded following) from ${tableName}
order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 preceding) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 following) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between current row and current row) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between current row and unbounded following) from ${tableName}
order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1) from ${tableName} order by k1;"
-
- // Nereids does't support window function
- // qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and unbounded following) from ${tableName}
order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 preceding) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 following) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between current row and current row) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between current row and unbounded following) from ${tableName}
order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1) from ${tableName} order by k1;"
-
- // Nereids does't support window function
- // qt_select_default "select k1, percentile(k2,0.8) over (partition by k6
order by k1 rows between 3 preceding and unbounded following) from ${tableName}
order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, percentile(k2,0.8) over (partition by k6
order by k1 rows between 3 preceding and 1 preceding) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, percentile(k2,0.8) over (partition by k6
order by k1 rows between 3 preceding and 1 following) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, percentile(k2,0.8) over (partition by k6
order by k1 rows between current row and current row) from ${tableName} order
by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, percentile(k2,0.8) over (partition by k6
order by k1 rows between current row and unbounded following) from ${tableName}
order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, percentile(k2,0.8) over (partition by k6
order by k1) from ${tableName} order by k1;"
-
- // Nereids does't support window function
- // qt_select_default "select k1, percentile_approx(k2,0.5,4096) over
(partition by k6 order by k1 rows between 3 preceding and unbounded following)
from ${tableName} order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, percentile_approx(k2,0.5,4096) over
(partition by k6 order by k1 rows between 3 preceding and 1 preceding) from
${tableName} order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, percentile_approx(k2,0.5,4096) over
(partition by k6 order by k1 rows between 3 preceding and 1 following) from
${tableName} order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, percentile_approx(k2,0.5,4096) over
(partition by k6 order by k1 rows between current row and current row) from
${tableName} order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, percentile_approx(k2,0.5,4096) over
(partition by k6 order by k1 rows between current row and unbounded following)
from ${tableName} order by k1;"
- // Nereids does't support window function
- // qt_select_default "select k1, percentile_approx(k2,0.5,4096) over
(partition by k6 order by k1) from ${tableName} order by k1;"
+
+ qt_select_default "select k1, stddev_pop(k2) over (partition by k6 order
by k1 rows between 3 preceding and unbounded following) from ${tableName}
order by k1;"
+
+ qt_select_default "select k1, stddev_pop(k2) over (partition by k6 order
by k1 rows between 3 preceding and 1 preceding) from ${tableName} order by
k1;"
+
+ qt_select_default "select k1, stddev_pop(k2) over (partition by k6 order
by k1 rows between 3 preceding and 1 following) from ${tableName} order by
k1;"
+
+ qt_select_default "select k1, stddev_pop(k2) over (partition by k6 order
by k1 rows between current row and current row) from ${tableName} order by
k1;"
+
+ qt_select_default "select k1, stddev_pop(k2) over (partition by k6 order
by k1 rows between current row and unbounded following) from ${tableName}
order by k1;"
+
+ qt_select_default "select k1, stddev_pop(k2) over (partition by k6 order
by k1) from ${tableName} order by k1;"
+
+
+ qt_select_default "select k1, stddev_samp(k2) over (partition by k6 order
by k1 rows between 3 preceding and unbounded following) from ${tableName}
order by k1;"
+
+ qt_select_default "select k1, stddev_samp(k2) over (partition by k6 order
by k1 rows between 3 preceding and 1 preceding) from ${tableName} order by
k1;"
+
+ qt_select_default "select k1, stddev_samp(k2) over (partition by k6 order
by k1 rows between 3 preceding and 1 following) from ${tableName} order by
k1;"
+
+ qt_select_default "select k1, stddev_samp(k2) over (partition by k6 order
by k1 rows between current row and current row) from ${tableName} order by
k1;"
+
+ qt_select_default "select k1, stddev_samp(k2) over (partition by k6 order
by k1 rows between current row and unbounded following) from ${tableName}
order by k1;"
+
+ qt_select_default "select k1, stddev_samp(k2) over (partition by k6 order
by k1) from ${tableName} order by k1;"
+
+
+ qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and unbounded following) from
${tableName} order by k1;"
+
+ qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 preceding) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 following) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between current row and current row) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between current row and unbounded following) from
${tableName} order by k1;"
+
+ qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1) from ${tableName} order by k1;"
+
+
+ qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and unbounded following) from
${tableName} order by k1;"
+
+ qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 preceding) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 following) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between current row and current row) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between current row and unbounded following) from
${tableName} order by k1;"
+
+ qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1) from ${tableName} order by k1;"
+
+
+ qt_select_default "select k1, stddev_pop(k2) over (partition by k6 order
by k1 rows between 3 preceding and unbounded following) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, stddev_pop(k2) over (partition by k6 order
by k1 rows between 3 preceding and 1 preceding) from ${tableName} order by k1;"
+
+ qt_select_default "select k1, stddev_pop(k2) over (partition by k6 order
by k1 rows between 3 preceding and 1 following) from ${tableName} order by k1;"
+
+ qt_select_default "select k1, stddev_pop(k2) over (partition by k6 order
by k1 rows between current row and current row) from ${tableName} order by k1;"
+
+ qt_select_default "select k1, stddev_pop(k2) over (partition by k6 order
by k1 rows between current row and unbounded following) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, stddev_pop(k2) over (partition by k6 order
by k1) from ${tableName} order by k1;"
+
+
+ qt_select_default "select k1, stddev_samp(k2) over (partition by k6 order
by k1 rows between 3 preceding and unbounded following) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, stddev_samp(k2) over (partition by k6 order
by k1 rows between 3 preceding and 1 preceding) from ${tableName} order by k1;"
+
+ qt_select_default "select k1, stddev_samp(k2) over (partition by k6 order
by k1 rows between 3 preceding and 1 following) from ${tableName} order by k1;"
+
+ qt_select_default "select k1, stddev_samp(k2) over (partition by k6 order
by k1 rows between current row and current row) from ${tableName} order by k1;"
+
+ qt_select_default "select k1, stddev_samp(k2) over (partition by k6 order
by k1 rows between current row and unbounded following) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, stddev_samp(k2) over (partition by k6 order
by k1) from ${tableName} order by k1;"
+
+
+ qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and unbounded following) from ${tableName}
order by k1;"
+
+ qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 preceding) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 following) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between current row and current row) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1 rows between current row and unbounded following) from ${tableName}
order by k1;"
+
+ qt_select_default "select k1, variance_pop(k2) over (partition by k6
order by k1) from ${tableName} order by k1;"
+
+
+ qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and unbounded following) from ${tableName}
order by k1;"
+
+ qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 preceding) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between 3 preceding and 1 following) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between current row and current row) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1 rows between current row and unbounded following) from ${tableName}
order by k1;"
+
+ qt_select_default "select k1, variance_samp(k2) over (partition by k6
order by k1) from ${tableName} order by k1;"
+
+
+ qt_select_default "select k1, percentile(k2,0.8) over (partition by k6
order by k1 rows between 3 preceding and unbounded following) from ${tableName}
order by k1;"
+
+ qt_select_default "select k1, percentile(k2,0.8) over (partition by k6
order by k1 rows between 3 preceding and 1 preceding) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, percentile(k2,0.8) over (partition by k6
order by k1 rows between 3 preceding and 1 following) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, percentile(k2,0.8) over (partition by k6
order by k1 rows between current row and current row) from ${tableName} order
by k1;"
+
+ qt_select_default "select k1, percentile(k2,0.8) over (partition by k6
order by k1 rows between current row and unbounded following) from ${tableName}
order by k1;"
+
+ qt_select_default "select k1, percentile(k2,0.8) over (partition by k6
order by k1) from ${tableName} order by k1;"
+
+
+ qt_select_default "select k1, percentile_approx(k2,0.5,4096) over
(partition by k6 order by k1 rows between 3 preceding and unbounded following)
from ${tableName} order by k1;"
+
+ qt_select_default "select k1, percentile_approx(k2,0.5,4096) over
(partition by k6 order by k1 rows between 3 preceding and 1 preceding) from
${tableName} order by k1;"
+
+ qt_select_default "select k1, percentile_approx(k2,0.5,4096) over
(partition by k6 order by k1 rows between 3 preceding and 1 following) from
${tableName} order by k1;"
+
+ qt_select_default "select k1, percentile_approx(k2,0.5,4096) over
(partition by k6 order by k1 rows between current row and current row) from
${tableName} order by k1;"
+
+ qt_select_default "select k1, percentile_approx(k2,0.5,4096) over
(partition by k6 order by k1 rows between current row and unbounded following)
from ${tableName} order by k1;"
+
+ qt_select_default "select k1, percentile_approx(k2,0.5,4096) over
(partition by k6 order by k1) from ${tableName} order by k1;"
}
diff --git
a/regression-test/suites/nereids_p0/sql_functions/window_functions/test_sum.groovy
b/regression-test/suites/nereids_p0/sql_functions/window_functions/test_sum.groovy
index dae12da9821..2c9de1d6cbf 100644
---
a/regression-test/suites/nereids_p0/sql_functions/window_functions/test_sum.groovy
+++
b/regression-test/suites/nereids_p0/sql_functions/window_functions/test_sum.groovy
@@ -18,11 +18,10 @@
suite("test_sum") {
sql "SET enable_nereids_planner=true"
sql "SET enable_fallback_to_original_planner=false"
- // Nereids does't support window function
- // qt_select """
- // select k1, sum(k5) over
- // (partition by k1 order by k3 range between current
row and unbounded following) as w
- // from nereids_test_query_db.test order by k1, w
- // """
+ qt_select """
+ select k1, sum(k5) over
+ (partition by k1 order by k3 range between current row
and unbounded following) as w
+ from nereids_test_query_db.test order by k1, w
+ """
}
diff --git
a/regression-test/suites/nereids_p0/sql_functions/window_functions/test_window_fn.groovy
b/regression-test/suites/nereids_p0/sql_functions/window_functions/test_window_fn.groovy
index a3854c68622..87b9c7f7dc0 100644
---
a/regression-test/suites/nereids_p0/sql_functions/window_functions/test_window_fn.groovy
+++
b/regression-test/suites/nereids_p0/sql_functions/window_functions/test_window_fn.groovy
@@ -118,260 +118,288 @@ suite("test_window_fn") {
}
// first_value
- // Nereids does't support window function
- // qt_sql """
- // select first_value(salary) over(order by salary range between
UNBOUNDED preceding and UNBOUNDED following), lead(salary, 1, 0) over(order by
salary) as l, salary from ${tbName1} order by l, salary;
- // """
- // Nereids does't support window function
- // qt_sql """
- // select first_value(salary) over(order by enroll_date range between
unbounded preceding and UNBOUNDED following), last_value(salary) over(order by
enroll_date range between unbounded preceding and UNBOUNDED following), salary,
enroll_date from ${tbName1} order by salary, enroll_date;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT first_value(unique1) over (order by four range between
current row and unbounded following),
- // last_value(unique1) over (order by four range between current row
and unbounded following), unique1, four
- // FROM ${tbName2} WHERE unique1 < 10 order by unique1, four;
- // """
+ qt_sql """
+ select first_value(salary) over(order by salary range between
UNBOUNDED preceding and UNBOUNDED following), lead(salary, 1, 0) over(order by
salary) as l, salary from ${tbName1} order by 1, l, salary;
+ """
+ qt_sql """
+ select
+ first_value(salary) over(order by enroll_date range between
unbounded preceding and UNBOUNDED following)
+ , last_value(salary) over(order by enroll_date range between
unbounded preceding and UNBOUNDED following)
+ , salary, enroll_date
+ from ${tbName1}
+ order by 1, 2, salary, enroll_date;
+ """
+ qt_sql """
+ SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10 order by 1, four, ten;
+ """
+ qt_sql """
+ SELECT first_value(unique1) over (order by four range between current
row and unbounded following),
+ last_value(unique1) over (order by four, unique1 desc range between
current row and unbounded following), unique1, four
+ FROM ${tbName2} WHERE unique1 < 10 order by 1, 2, unique1, four;
+ """
// last_value
- // Nereids does't support window function
- // qt_sql """
- // select last_value(salary) over(order by salary range between
UNBOUNDED preceding and UNBOUNDED following), lag(salary, 1, 0) over(order by
salary) as l, salary from ${tbName1} order by l, salary;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT last_value(ten) OVER (ORDER BY ten), ten, four FROM
${tbName2} WHERE unique2 < 10;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT last_value(ten) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM
- // (SELECT * FROM ${tbName2} WHERE unique2 < 10 ORDER BY four, ten)s
ORDER BY four, ten;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT four, ten, sum(ten) over (partition by four order by ten),
last_value(ten) over (partition by four order by ten)
- // FROM (select distinct ten, four from ${tbName2}) ss order by four,
ten;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT four, ten, sum(ten) over (partition by four order by ten
range between unbounded preceding and current row),
- // last_value(ten) over (partition by four order by ten range between
unbounded preceding and current row)
- // FROM (select distinct ten, four from ${tbName2}) ss order by four,
ten;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT four, ten, sum(ten) over (partition by four order by ten
range between unbounded preceding and unbounded following),
- // last_value(ten) over (partition by four order by ten range between
unbounded preceding and unbounded following)
- // FROM (select distinct ten, four from ${tbName2}) ss order by four,
ten;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT four, ten/4 as two, sum(ten/4) over (partition by four order
by ten/4 range between unbounded preceding and current row),
- // last_value(ten/4) over (partition by four order by ten/4 range
between unbounded preceding and current row)
- // FROM (select distinct ten, four from ${tbName2}) ss order by four,
two;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT four, ten/4 as two, sum(ten/4) over (partition by four order
by ten/4 rows between unbounded preceding and current row),
- // last_value(ten/4) over (partition by four order by ten/4 rows
between unbounded preceding and current row)
- // FROM (select distinct ten, four from ${tbName2}) ss order by four,
two;
- // """
+ qt_sql """
+ select last_value(salary) over(order by salary range between UNBOUNDED
preceding and UNBOUNDED following), lag(salary, 1, 0) over(order by salary) as
l, salary from ${tbName1} order by 1, l, salary;
+ """
+ qt_sql """
+ SELECT last_value(ten) OVER (ORDER BY ten), ten, four FROM ${tbName2}
WHERE unique2 < 10 order by 1, 2, 3;
+ """
+ qt_sql """
+ SELECT last_value(ten) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM
+ (SELECT * FROM ${tbName2} WHERE unique2 < 10 ORDER BY four, ten)s
ORDER BY 1, 2, four, ten;
+ """
+ qt_sql """
+ SELECT four, ten, sum(ten) over (partition by four order by ten),
last_value(ten) over (partition by four order by ten)
+ FROM (select distinct ten, four from ${tbName2}) ss order by four,
ten, 3, 4;
+ """
+ qt_sql """
+ SELECT four, ten, sum(ten) over (partition by four order by ten range
between unbounded preceding and current row),
+ last_value(ten) over (partition by four order by ten range between
unbounded preceding and current row)
+ FROM (select distinct ten, four from ${tbName2}) ss order by four,
ten, 3, 4;
+ """
+ qt_sql """
+ SELECT four, ten, sum(ten) over (partition by four order by ten range
between unbounded preceding and unbounded following),
+ last_value(ten) over (partition by four order by ten range between
unbounded preceding and unbounded following)
+ FROM (select distinct ten, four from ${tbName2}) ss order by four,
ten, 3, 4;
+ """
+ qt_sql """
+ SELECT four, ten/4 as two, sum(ten/4) over (partition by four order by
ten/4 range between unbounded preceding and current row),
+ last_value(ten/4) over (partition by four order by ten/4 range between
unbounded preceding and current row)
+ FROM (select distinct ten, four from ${tbName2}) ss order by 1, 2,
four, two;
+ """
+ qt_sql """
+ SELECT four, ten/4 as two, sum(ten/4) over (partition by four order by
ten/4 rows between unbounded preceding and current row),
+ last_value(ten/4) over (partition by four order by ten/4 rows between
unbounded preceding and current row)
+ FROM (select distinct ten, four from ${tbName2}) ss order by four,
two, 3, 4;
+ """
// min_max
- // Nereids does't support window function
- // qt_sql """
- // SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER
(ORDER BY empno), MAX(depadj) OVER ()
- // FROM( SELECT *, CASE WHEN enroll_date < '2008-01-01' THEN 2008 -
extract(YEAR FROM enroll_date) END * 500 AS bonus,
- // CASE WHEN AVG(salary) OVER (PARTITION BY depname) < salary THEN 200
END AS depadj FROM ${tbName1})s order by empno;
- // """
- // Nereids does't support window function
- // qt_sql """
- // select max(enroll_date) over (order by enroll_date range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
- // """
- // Nereids does't support window function
- // qt_sql """
- // select max(enroll_date) over (order by salary range between
UNBOUNDED preceding and UNBOUNDED following ), salary, enroll_date from
${tbName1} order by salary, enroll_date;
- // """
- // Nereids does't support window function
- // qt_sql """
- // select max(enroll_date) over (order by enroll_date range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
- // """
+ qt_sql """
+ SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER
BY empno), MAX(depadj) OVER ()
+ FROM( SELECT *, CASE WHEN enroll_date < '2008-01-01' THEN 2008 -
extract(YEAR FROM enroll_date) END * 500 AS bonus,
+ CASE WHEN AVG(salary) OVER (PARTITION BY depname) < salary THEN 200
END AS depadj FROM ${tbName1})s order by empno, depname, salary, bonus, depadj;
+ """
+ qt_sql """
+ select max(enroll_date) over (order by enroll_date range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
+ """
+ qt_sql """
+ select max(enroll_date) over (order by salary range between UNBOUNDED
preceding and UNBOUNDED following ), salary, enroll_date from ${tbName1} order
by salary, enroll_date;
+ """
+ qt_sql """
+ select max(enroll_date) over (order by enroll_date range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
+ """
// rank
- // Nereids does't support window function
- // qt_sql """
- // SELECT depname, empno, salary, rank() OVER (PARTITION BY depname
ORDER BY salary) FROM ${tbName1} order by depname
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT depname, empno, salary, rank() OVER (PARTITION BY depname
ORDER BY salary, empno)
- // FROM ${tbName1} ORDER BY rank() OVER (PARTITION BY depname ORDER BY
salary, empno);
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT sum(salary) as s, row_number() OVER (ORDER BY depname) as
r, sum(sum(salary)) OVER (ORDER BY depname DESC) as ss
- // FROM ${tbName1} GROUP BY depname order by s, r, ss;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT sum(salary) OVER (PARTITION BY depname ORDER BY salary DESC)
as s, rank() OVER (PARTITION BY depname ORDER BY salary DESC) as r
- // FROM ${tbName1} order by s, r;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT * FROM ( select *, row_number() OVER (ORDER BY salary) as a
from ${tbName1} ) as t where t.a < 10;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT row_number() OVER (ORDER BY unique2) FROM ${tbName2} WHERE
unique2 < 10;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten,
four FROM ${tbName2} WHERE unique2 < 10;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10;
- // """
- // Nereids does't support window function
- // qt_sql """
- // select ten, sum(unique1) + sum(unique2) as res, rank() over
(order by sum(unique1) + sum(unique2)) as rank from ${tbName2} group by ten
order by ten;
- // """
+ qt_sql """
+ SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER
BY salary) FROM ${tbName1} order by depname
+ """
+ qt_sql """
+ SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER
BY salary, empno)
+ FROM ${tbName1} ORDER BY rank() OVER (PARTITION BY depname ORDER BY
salary, empno), depname;
+ """
+ qt_sql """
+ SELECT sum(salary) as s, row_number() OVER (ORDER BY depname) as r,
sum(sum(salary)) OVER (ORDER BY depname DESC) as ss
+ FROM ${tbName1} GROUP BY depname order by s, r, ss;
+ """
+ qt_sql """
+ SELECT sum(salary) OVER (PARTITION BY depname ORDER BY salary DESC) as
s, rank() OVER (PARTITION BY depname ORDER BY salary DESC) as r
+ FROM ${tbName1} order by s, r;
+ """
+ qt_sql """
+ SELECT * FROM ( select *, row_number() OVER (ORDER BY salary) as a
from ${tbName1} ) as t where t.a < 10;
+ """
+ qt_sql """
+ SELECT row_number() OVER (ORDER BY unique2) FROM ${tbName2} WHERE
unique2 < 10;
+ """
+ qt_sql """
+ SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten,
four FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ """
+ qt_sql """
+ SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten), ten, four
FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ """
+ qt_sql """
+ SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four
FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ """
+ qt_sql """
+ SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four
FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ """
+ qt_sql """
+ select ten, sum(unique1) + sum(unique2) as res, rank() over (order
by sum(unique1) + sum(unique2)) as rank from ${tbName2} group by ten order by
ten;
+ """
// sum_avg_count
- // Nereids does't support window function
- // qt_sql """
- // SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY
depname) FROM ${tbName1} order by depname,empno,salary;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT sum(salary) OVER (ORDER BY salary) as s, count(1) OVER
(ORDER BY salary) as c FROM ${tbName1} order by s, c;
- // """
- // Nereids does't support window function
- // qt_sql """
- // select sum(salary) over (order by enroll_date range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
- // """
- // Nereids does't support window function
- // qt_sql """
- // select sum(salary) over (order by enroll_date desc range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
- // """
- // Nereids does't support window function
- // qt_sql """
- // select sum(salary) over (order by enroll_date desc range between
UNBOUNDED preceding and current row) as s, salary, enroll_date from ${tbName1}
order by s, salary;
- // """
- // Nereids does't support window function
- // qt_sql """
- // select sum(salary) over (order by enroll_date, salary range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
- // """
- // Nereids does't support window function
- // qt_sql """
- // select sum(salary) over (order by depname range between UNBOUNDED
preceding and UNBOUNDED following ), salary, enroll_date from ${tbName1} order
by salary, enroll_date;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten)
FROM ${tbName2}
- // GROUP BY four, ten ORDER BY four, ten;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT COUNT(1) OVER () FROM ${tbName2} WHERE unique2 < 10;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1,
ten, four FROM ${tbName2} WHERE unique2 < 10 order by ten, four;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER
(PARTITION BY two ORDER BY ten) AS wsum FROM ${tbName2} GROUP BY ten, two order
by gsum, wsum;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT count(1) OVER (PARTITION BY four) as c, four FROM (SELECT *
FROM ${tbName2} WHERE two = 1)s WHERE unique2 < 10 order by c, four;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100)
FROM ${tbName2} WHERE unique2 < 10;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT count(1) OVER (PARTITION BY four) FROM (SELECT * FROM
${tbName2} WHERE FALSE)s;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT sum(unique1) over (order by four range between current row
and unbounded following) as s, unique1, four
- // FROM ${tbName2} WHERE unique1 < 10 order by s;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT count() OVER () FROM ${tbName2} limit 5;
- // """
+ qt_sql """
+ SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname)
FROM ${tbName1} order by depname,empno,salary;
+ """
+ qt_sql """
+ SELECT sum(salary) OVER (ORDER BY salary) as s, count(1) OVER (ORDER
BY salary) as c FROM ${tbName1} order by s, c;
+ """
+ qt_sql """
+ select sum(salary) over (order by enroll_date range between UNBOUNDED
preceding and UNBOUNDED following), salary, enroll_date from ${tbName1} order
by salary, enroll_date;
+ """
+ qt_sql """
+ select sum(salary) over (order by enroll_date desc range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
+ """
+ qt_sql """
+ select sum(salary) over (order by enroll_date desc range between
UNBOUNDED preceding and current row) as s, salary, enroll_date from ${tbName1}
order by s, salary;
+ """
+ qt_sql """
+ select sum(salary) over (order by enroll_date, salary range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
+ """
+ qt_sql """
+ select sum(salary) over (order by depname range between UNBOUNDED
preceding and UNBOUNDED following ), salary, enroll_date from ${tbName1} order
by salary, enroll_date;
+ """
+ qt_sql """
+ SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten)
FROM ${tbName2}
+ GROUP BY four, ten ORDER BY four, ten;
+ """
+ qt_sql """
+ SELECT COUNT(1) OVER () FROM ${tbName2} WHERE unique2 < 10;
+ """
+ qt_sql """
+ SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1,
ten, four FROM ${tbName2} WHERE unique2 < 10 order by ten, four;
+ """
+ qt_sql """
+ SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER
(PARTITION BY two ORDER BY ten) AS wsum FROM ${tbName2} GROUP BY ten, two order
by gsum, wsum;
+ """
+ qt_sql """
+ SELECT count(1) OVER (PARTITION BY four) as c, four FROM (SELECT *
FROM ${tbName2} WHERE two = 1)s WHERE unique2 < 10 order by c, four;
+ """
+ qt_sql """
+ SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100) FROM
${tbName2} WHERE unique2 < 10 order by four;
+ """
+ qt_sql """
+ SELECT count(1) OVER (PARTITION BY four) FROM (SELECT * FROM
${tbName2} WHERE FALSE)s;
+ """
+ qt_sql """
+ SELECT sum(unique1) over (order by four range between current row and
unbounded following) as s, unique1, four
+ FROM ${tbName2} WHERE unique1 < 10 order by s, unique1;
+ """
+ qt_sql """
+ SELECT count() OVER () FROM ${tbName2} limit 5;
+ """
// ntile
- // Nereids does't support window function
- // qt_sql """
- // SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM
${tbName2} WHERE unique2 < 10;
- // """
+ qt_sql """
+ SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM ${tbName2}
WHERE unique2 < 10;
+ """
// lag
- // Nereids does't support window function
- // qt_sql """
- // SELECT lag(ten, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10;
- // """
+ qt_sql """
+ SELECT lag(ten, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten, four
FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ """
// lead
- // Nereids does't support window function
- // qt_sql """
- // SELECT lead(ten, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT lead(ten * 2, 1, 0) OVER (PARTITION BY four ORDER BY ten),
ten, four FROM ${tbName2} WHERE unique2 < 10;
- // """
- // Nereids does't support window function
- // qt_sql """
- // SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten),
ten, four FROM ${tbName2} WHERE unique2 < 10;
- // """
+ qt_sql """
+ SELECT lead(ten, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ """
+ qt_sql """
+ SELECT lead(ten * 2, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ """
+ qt_sql """
+ SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten),
ten, four FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ """
// sub query
- // Nereids does't support window function
- // qt_sql """
- // SELECT * FROM( SELECT count(1) OVER (PARTITION BY four ORDER BY
ten) + sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total,
- // count(1) OVER (PARTITION BY four ORDER BY ten) AS fourcount,
- // sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum FROM
${tbName2} )sub
- // WHERE total <> fourcount + twosum;
- // """
+ qt_sql """
+ SELECT * FROM( SELECT count(1) OVER (PARTITION BY four ORDER BY ten)
+ sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total,
+ count(1) OVER (PARTITION BY four ORDER BY ten) AS fourcount,
+ sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum FROM
${tbName2} )sub
+ WHERE total <> fourcount + twosum;
+ """
// cte
- // Nereids does't support window function
- // qt_sql """
- // with cte as (select empno as x from ${tbName1})
- // SELECT x, (sum(x) over (ORDER BY x range between UNBOUNDED
preceding and UNBOUNDED following)) FROM cte;
- // """
- // Nereids does't support window function
- // qt_sql """
- // with cte as (select empno as x from ${tbName1})
- // SELECT x, (sum(x) over (ORDER BY x range between UNBOUNDED
preceding and CURRENT ROW)) FROM cte;
- // """
- // Nereids does't support window function
- // qt_sql """
- // WITH cte AS (
- // select 1 as x union all select 1 as x union all select 1 as x union
all
- // SELECT empno as x FROM ${tbName1})
- // SELECT x, (sum(x) over (ORDER BY x rows between 1 preceding and 1
following)) FROM cte;
- // """
+ qt_sql """
+ with cte as (select empno as x from ${tbName1})
+ SELECT x, (sum(x) over (ORDER BY x range between UNBOUNDED preceding
and UNBOUNDED following)) FROM cte;
+ """
+ qt_sql """
+ with cte as (select empno as x from ${tbName1})
+ SELECT x, (sum(x) over (ORDER BY x range between UNBOUNDED preceding
and CURRENT ROW)) FROM cte;
+ """
+ qt_sql """
+ WITH cte AS (
+ select 1 as x union all select 1 as x union all select 1 as x union all
+ SELECT empno as x FROM ${tbName1})
+ SELECT x, (sum(x) over (ORDER BY x rows between 1 preceding and 1
following)) FROM cte;
+ """
sql "DROP TABLE IF EXISTS ${tbName1};"
sql "DROP TABLE IF EXISTS ${tbName2};"
+
+ sql """ DROP TABLE IF EXISTS example_window_tb """
+ sql """
+ CREATE TABLE IF NOT EXISTS example_window_tb (
+ u_id int NULL COMMENT "",
+ u_city varchar(20) NULL COMMENT "",
+ u_salary int NULL COMMENT ""
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`u_id`, `u_city`, `u_salary`)
+ COMMENT ""
+ DISTRIBUTED BY HASH(`u_id`, `u_city`, `u_salary`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql """
+ INSERT INTO example_window_tb(u_id, u_city, u_salary) VALUES
+ ('1', 'gz', 30000),
+ ('2', 'gz', 25000),
+ ('3', 'gz', 17000),
+ ('4', 'gz', 32000),
+ ('5', 'sz', 40000),
+ ('6', 'sz', 27000),
+ ('7', 'sz', 27000),
+ ('8', 'sz', 33000);
+ """
+
+ qt_sql_window_last_value """
+ select u_id, u_city, u_salary,
+ last_value(u_salary) over (partition by u_city order by u_id rows
between unbounded preceding and 1 preceding) last_value_test
+ from example_window_tb order by u_id;
+ """
+
+ sql """
+ create view v as select row_number() over(partition by u_city order by
u_salary) as wf from example_window_tb
+ """
+
+ sql """
+ drop view v
+ """
+
+ sql "DROP TABLE IF EXISTS example_window_tb;"
+
+ sql """
+ CREATE TABLE IF NOT EXISTS test_window_in_agg
+ (
+ `c1` int ,
+ `c2` int ,
+ `c3` int
+ )
+ ENGINE=OLAP
+ DUPLICATE KEY(`c1`)
+ COMMENT ""
+ DISTRIBUTED BY HASH(`c1`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+ sql """set enable_nereids_planner=true;"""
+ sql """SELECT SUM(MAX(c1) OVER (PARTITION BY c2, c3)) FROM
test_window_in_agg;"""
+
+ sql "DROP TABLE IF EXISTS test_window_in_agg;"
}
diff --git
a/regression-test/suites/nereids_p0/sql_functions/window_functions/test_window_function.groovy
b/regression-test/suites/nereids_p0/sql_functions/window_functions/test_window_function.groovy
index bcaaefe7031..35dea6797bd 100644
---
a/regression-test/suites/nereids_p0/sql_functions/window_functions/test_window_function.groovy
+++
b/regression-test/suites/nereids_p0/sql_functions/window_functions/test_window_function.groovy
@@ -24,210 +24,189 @@ suite("test_window_function") {
sql """ INSERT INTO ${windowFunctionTable1} VALUES
('JDR',12.86,'2014-10-02 00:00:00','2014-10-02 00:00:00.111111','2014-10-02
00:00:00.111111','2014-10-02 00:00:00.111111'),('JDR',12.89,'2014-10-03
00:00:00','2014-10-03 00:00:00.111111','2014-10-03 00:00:00.111111','2014-10-03
00:00:00.111111'),('JDR',12.94,'2014-10-04 00:00:00','2014-10-04
00:00:00.111111','2014-10-04 00:00:00.111111','2014-10-04
00:00:00.111111'),('JDR',12.55,'2014-10-05 00:00:00','2014-10-05
00:00:00.111111','201 [...]
- qt_sql """SELECT row_number() OVER (partition by 1 order by 2) from
${windowFunctionTable1} order by 1; """
-
- // test constant folding by be on OrderExpression
- sql """set enable_fold_constant_by_be = true"""
- sql """SELECT row_number() OVER (partition by 1 order by 2) from
${windowFunctionTable1} order by 1; """
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // stock_symbol,
- // closing_date,
- // closing_price,
- // avg( closing_price ) over ( PARTITION BY stock_symbol ORDER
BY closing_date rows BETWEEN 1 preceding AND 1 following ) AS moving_average
- // FROM
- // ${windowFunctionTable1}
- // ORDER BY
- // stock_symbol,
- // closing_date,
- // closing_price
- // """
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ stock_symbol,
+ closing_date,
+ closing_price,
+ avg( closing_price ) over ( PARTITION BY stock_symbol ORDER BY
closing_date rows BETWEEN 1 preceding AND 1 following ) AS moving_average
+ FROM
+ ${windowFunctionTable1}
+ ORDER BY
+ stock_symbol,
+ closing_date,
+ closing_price
+ """
// LEAD
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // stock_symbol,
- // closing_date,
- // closing_price,
- // CASE ( lead( closing_price, 1, 0 ) over ( PARTITION BY
stock_symbol ORDER BY closing_date )- closing_price ) > 0
- // WHEN TRUE THEN "higher"
- // WHEN FALSE THEN "flat or lower" END AS "trending"
- // FROM
- // ${windowFunctionTable1}
- // ORDER BY
- // closing_date;
- // """
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ stock_symbol,
+ closing_date,
+ closing_price,
+ CASE ( lead( closing_price, 1, 0 ) over ( PARTITION BY
stock_symbol ORDER BY closing_date )- closing_price ) > 0
+ WHEN TRUE THEN "higher"
+ WHEN FALSE THEN "flat or lower" END AS "trending"
+ FROM
+ ${windowFunctionTable1}
+ ORDER BY
+ closing_date;
+ """
// LEAD not nullable coredump
- // Nereids does't support window function
- // qt_sql """
- // select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
t1.new_time from (select closing_date, lead(closing_date, 1, '2014-10-02
00:00:00') over () as new_time from ${windowFunctionTable1}) as t1 left join
${windowFunctionTable1} t2 on t2.closing_date = t1.closing_date order by
t1.new_time desc;
- // """
+ qt_sql """
+ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
t1.new_time from (select closing_date, lead(closing_date, 1, '2014-10-02
00:00:00') over () as new_time from ${windowFunctionTable1}) as t1 left join
${windowFunctionTable1} t2 on t2.closing_date = t1.closing_date order by
t1.new_time desc;
+ """
// LAG
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // stock_symbol,
- // closing_date,
- // closing_price,
- // lag( closing_price, 1, 0 ) over ( PARTITION BY stock_symbol
ORDER BY closing_date ) AS "yesterday closing"
- // FROM
- // ${windowFunctionTable1}
- // ORDER BY
- // closing_date;
- // """
-
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // stock_symbol,
- // closing_date1,
- // closing_price,
- // avg( closing_price ) over ( PARTITION BY stock_symbol ORDER
BY closing_date1 rows BETWEEN 1 preceding AND 1 following ) AS moving_average
- // FROM
- // ${windowFunctionTable1}
- // ORDER BY
- // stock_symbol,
- // closing_date1,
- // closing_price
- // """
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ stock_symbol,
+ closing_date,
+ closing_price,
+ lag( closing_price, 1, 0 ) over ( PARTITION BY stock_symbol
ORDER BY closing_date ) AS "yesterday closing"
+ FROM
+ ${windowFunctionTable1}
+ ORDER BY
+ closing_date;
+ """
+
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ stock_symbol,
+ closing_date1,
+ closing_price,
+ avg( closing_price ) over ( PARTITION BY stock_symbol ORDER BY
closing_date1 rows BETWEEN 1 preceding AND 1 following ) AS moving_average
+ FROM
+ ${windowFunctionTable1}
+ ORDER BY
+ stock_symbol,
+ closing_date1,
+ closing_price
+ """
// LEAD
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // stock_symbol,
- // closing_date1,
- // closing_price,
- // CASE ( lead( closing_price, 1, 0 ) over ( PARTITION BY
stock_symbol ORDER BY closing_date1 )- closing_price ) > 0
- // WHEN TRUE THEN "higher"
- // WHEN FALSE THEN "flat or lower" END AS "trending"
- // FROM
- // ${windowFunctionTable1}
- // ORDER BY
- // closing_date1;
- // """
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ stock_symbol,
+ closing_date1,
+ closing_price,
+ CASE ( lead( closing_price, 1, 0 ) over ( PARTITION BY
stock_symbol ORDER BY closing_date1 )- closing_price ) > 0
+ WHEN TRUE THEN "higher"
+ WHEN FALSE THEN "flat or lower" END AS "trending"
+ FROM
+ ${windowFunctionTable1}
+ ORDER BY
+ closing_date1;
+ """
// LEAD not nullable coredump
- // Nereids does't support window function
- // qt_sql """
- // select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
t1.new_time from (select closing_date1, lead(closing_date1, 1, '2014-10-02
00:00:00') over () as new_time from ${windowFunctionTable1}) as t1 left join
${windowFunctionTable1} t2 on t2.closing_date1 = t1.closing_date1 order by
t1.new_time desc;
- // """
+ qt_sql """
+ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
t1.new_time from (select closing_date1, lead(closing_date1, 1, '2014-10-02
00:00:00') over () as new_time from ${windowFunctionTable1}) as t1 left join
${windowFunctionTable1} t2 on t2.closing_date1 = t1.closing_date1 order by
t1.new_time desc;
+ """
// LAG
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // stock_symbol,
- // closing_date1,
- // closing_price,
- // lag( closing_price, 1, 0 ) over ( PARTITION BY stock_symbol
ORDER BY closing_date1 ) AS "yesterday closing"
- // FROM
- // ${windowFunctionTable1}
- // ORDER BY
- // closing_date1;
- // """
-
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // stock_symbol,
- // closing_date2,
- // closing_price,
- // avg( closing_price ) over ( PARTITION BY stock_symbol ORDER
BY closing_date2 rows BETWEEN 1 preceding AND 1 following ) AS moving_average
- // FROM
- // ${windowFunctionTable1}
- // ORDER BY
- // stock_symbol,
- // closing_date2,
- // closing_price
- // """
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ stock_symbol,
+ closing_date1,
+ closing_price,
+ lag( closing_price, 1, 0 ) over ( PARTITION BY stock_symbol
ORDER BY closing_date1 ) AS "yesterday closing"
+ FROM
+ ${windowFunctionTable1}
+ ORDER BY
+ closing_date1;
+ """
+
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ stock_symbol,
+ closing_date2,
+ closing_price,
+ avg( closing_price ) over ( PARTITION BY stock_symbol ORDER BY
closing_date2 rows BETWEEN 1 preceding AND 1 following ) AS moving_average
+ FROM
+ ${windowFunctionTable1}
+ ORDER BY
+ stock_symbol,
+ closing_date2,
+ closing_price
+ """
// LEAD
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // stock_symbol,
- // closing_date2,
- // closing_price,
- // CASE ( lead( closing_price, 1, 0 ) over ( PARTITION BY
stock_symbol ORDER BY closing_date2 )- closing_price ) > 0
- // WHEN TRUE THEN "higher"
- // WHEN FALSE THEN "flat or lower" END AS "trending"
- // FROM
- // ${windowFunctionTable1}
- // ORDER BY
- // closing_date2;
- // """
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ stock_symbol,
+ closing_date2,
+ closing_price,
+ CASE ( lead( closing_price, 1, 0 ) over ( PARTITION BY
stock_symbol ORDER BY closing_date2 )- closing_price ) > 0
+ WHEN TRUE THEN "higher"
+ WHEN FALSE THEN "flat or lower" END AS "trending"
+ FROM
+ ${windowFunctionTable1}
+ ORDER BY
+ closing_date2;
+ """
// LEAD not nullable coredump
- // Nereids does't support window function
- // qt_sql """
- // select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
t1.new_time from (select closing_date2, lead(closing_date2, 1, '2014-10-02
00:00:00') over () as new_time from ${windowFunctionTable1}) as t1 left join
${windowFunctionTable1} t2 on t2.closing_date2 = t1.closing_date2 order by
t1.new_time desc;
- // """
+ qt_sql """
+ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
t1.new_time from (select closing_date2, lead(closing_date2, 1, '2014-10-02
00:00:00') over () as new_time from ${windowFunctionTable1}) as t1 left join
${windowFunctionTable1} t2 on t2.closing_date2 = t1.closing_date2 order by
t1.new_time desc;
+ """
// LAG
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // stock_symbol,
- // closing_date2,
- // closing_price,
- // lag( closing_price, 1, 0 ) over ( PARTITION BY stock_symbol
ORDER BY closing_date2 ) AS "yesterday closing"
- // FROM
- // ${windowFunctionTable1}
- // ORDER BY
- // closing_date2;
- // """
-
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // stock_symbol,
- // closing_date3,
- // closing_price,
- // avg( closing_price ) over ( PARTITION BY stock_symbol ORDER
BY closing_date3 rows BETWEEN 1 preceding AND 1 following ) AS moving_average
- // FROM
- // ${windowFunctionTable1}
- // ORDER BY
- // stock_symbol,
- // closing_date3,
- // closing_price
- // """
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ stock_symbol,
+ closing_date2,
+ closing_price,
+ lag( closing_price, 1, 0 ) over ( PARTITION BY stock_symbol
ORDER BY closing_date2 ) AS "yesterday closing"
+ FROM
+ ${windowFunctionTable1}
+ ORDER BY
+ closing_date2;
+ """
+
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ stock_symbol,
+ closing_date3,
+ closing_price,
+ avg( closing_price ) over ( PARTITION BY stock_symbol ORDER BY
closing_date3 rows BETWEEN 1 preceding AND 1 following ) AS moving_average
+ FROM
+ ${windowFunctionTable1}
+ ORDER BY
+ stock_symbol,
+ closing_date3,
+ closing_price
+ """
// LEAD
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // stock_symbol,
- // closing_date3,
- // closing_price,
- // CASE ( lead( closing_price, 1, 0 ) over ( PARTITION BY
stock_symbol ORDER BY closing_date3 )- closing_price ) > 0
- // WHEN TRUE THEN "higher"
- // WHEN FALSE THEN "flat or lower" END AS "trending"
- // FROM
- // ${windowFunctionTable1}
- // ORDER BY
- // closing_date3;
- // """
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ stock_symbol,
+ closing_date3,
+ closing_price,
+ CASE ( lead( closing_price, 1, 0 ) over ( PARTITION BY
stock_symbol ORDER BY closing_date3 )- closing_price ) > 0
+ WHEN TRUE THEN "higher"
+ WHEN FALSE THEN "flat or lower" END AS "trending"
+ FROM
+ ${windowFunctionTable1}
+ ORDER BY
+ closing_date3;
+ """
// LEAD not nullable coredump
- // Nereids does't support window function
- // qt_sql """
- // select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
t1.new_time from (select closing_date3, lead(closing_date3, 1, '2014-10-02
00:00:00') over () as new_time from ${windowFunctionTable1}) as t1 left join
${windowFunctionTable1} t2 on t2.closing_date3 = t1.closing_date3 order by
t1.new_time desc;
- // """
+ qt_sql """
+ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
t1.new_time from (select closing_date3, lead(closing_date3, 1, '2014-10-02
00:00:00') over () as new_time from ${windowFunctionTable1}) as t1 left join
${windowFunctionTable1} t2 on t2.closing_date3 = t1.closing_date3 order by
t1.new_time desc;
+ """
// LAG
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // stock_symbol,
- // closing_date3,
- // closing_price,
- // lag( closing_price, 1, 0 ) over ( PARTITION BY stock_symbol
ORDER BY closing_date3 ) AS "yesterday closing"
- // FROM
- // ${windowFunctionTable1}
- // ORDER BY
- // closing_date3;
- // """
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ stock_symbol,
+ closing_date3,
+ closing_price,
+ lag( closing_price, 1, 0 ) over ( PARTITION BY stock_symbol
ORDER BY closing_date3 ) AS "yesterday closing"
+ FROM
+ ${windowFunctionTable1}
+ ORDER BY
+ closing_date3;
+ """
sql """ drop table ${windowFunctionTable1} """
@@ -237,74 +216,69 @@ suite("test_window_function") {
sql """ insert into ${windowFunctionTable2} values
(2,'even'),(4,'even'),(6,'even'),(8,'even'),(10,'even'),(1,'odd'),(3,'odd'),(5,'odd'),(7,'odd'),(9,'odd');
"""
// SUM
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // x,
- // property,
- // sum( x ) over ( PARTITION BY property ORDER BY x rows
BETWEEN 1 preceding AND 1 following ) AS 'moving total'
- // FROM
- // ${windowFunctionTable2}
- // WHERE
- // property IN ( 'odd', 'even' )
- // ORDER BY
- // property, x;
- // """
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ x,
+ property,
+ sum( x ) over ( PARTITION BY property ORDER BY x rows
BETWEEN 1 preceding AND 1 following ) AS 'moving total'
+ FROM
+ ${windowFunctionTable2}
+ WHERE
+ property IN ( 'odd', 'even' )
+ ORDER BY
+ property, x;
+ """
// AVG
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // x,
- // property,
- // avg( x ) over ( PARTITION BY property ORDER BY x rows
BETWEEN 1 preceding AND 1 following ) AS 'moving average'
- // FROM
- // ${windowFunctionTable2}
- // WHERE
- // property IN ( 'odd', 'even' )
- // ORDER BY
- // property, x;
- // """
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ x,
+ property,
+ avg( x ) over ( PARTITION BY property ORDER BY x rows
BETWEEN 1 preceding AND 1 following ) AS 'moving average'
+ FROM
+ ${windowFunctionTable2}
+ WHERE
+ property IN ( 'odd', 'even' )
+ ORDER BY
+ property, x;
+ """
// COUNT
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // x,
- // property,
- // count( x ) over ( PARTITION BY property ORDER BY x rows
BETWEEN unbounded preceding AND current ROW ) AS 'cumulative total'
- // FROM
- // ${windowFunctionTable2}
- // WHERE
- // property IN ( 'odd', 'even' )
- // ORDER BY
- // property, x;
- // """
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ x,
+ property,
+ count( x ) over ( PARTITION BY property ORDER BY x rows
BETWEEN unbounded preceding AND current ROW ) AS 'cumulative total'
+ FROM
+ ${windowFunctionTable2}
+ WHERE
+ property IN ( 'odd', 'even' )
+ ORDER BY
+ property, x;
+ """
sql """ truncate table ${windowFunctionTable2} """
sql """ insert into ${windowFunctionTable2} values
(2,'even'),(4,'even'),(6,'even'),(8,'even'),(10,'even'),(1,'odd'),(3,'odd'),(5,'odd'),(7,'odd'),(9,'odd');
"""
// MIN
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // x,
- // property,
- // min( x ) over ( ORDER BY property, x DESC rows BETWEEN
unbounded preceding AND 1 following ) AS 'local minimum'
- // FROM
- // ${windowFunctionTable2}
- // WHERE
- // property IN ( 'prime', 'square' );
- // """
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ x,
+ property,
+ min( x ) over ( ORDER BY property, x DESC rows BETWEEN
unbounded preceding AND 1 following ) AS 'local minimum'
+ FROM
+ ${windowFunctionTable2}
+ WHERE
+ property IN ( 'prime', 'square' );
+ """
// MAX
- // Nereids does't support window function
- // qt_sql """
- // SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
- // x,
- // property,
- // max( x ) over ( ORDER BY property, x rows BETWEEN
unbounded preceding AND 1 following ) AS 'local maximum'
- // FROM
- // ${windowFunctionTable2}
- // WHERE
- // property IN ( 'prime', 'square' );
- // """
+ qt_sql """
+ SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
+ x,
+ property,
+ max( x ) over ( ORDER BY property, x rows BETWEEN unbounded
preceding AND 1 following ) AS 'local maximum'
+ FROM
+ ${windowFunctionTable2}
+ WHERE
+ property IN ( 'prime', 'square' );
+ """
sql """ drop table ${windowFunctionTable2} """
@@ -314,14 +288,15 @@ suite("test_window_function") {
sql """ insert into ${windowFunctionTable3} values
(1,1),(1,2),(1,2),(2,1),(2,2),(2,3),(3,1),(3,1),(3,2); """
// RANK
- // Nereids does't support window function
- // qt_sql """ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
x, y, rank() over(partition by x order by y) as rank from
${windowFunctionTable3} order by x, y; """
+ qt_sql """ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */ x,
y, rank() over(partition by x order by y) as rank from ${windowFunctionTable3}
order by x, y; """
// DENSE_RANK
- // Nereids does't support window function
- // qt_sql """ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
x, y, dense_rank() over(partition by x order by y) as rank from
${windowFunctionTable3} order by x, y; """
+ qt_sql """ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */ x,
y, dense_rank() over(partition by x order by y) as rank from
${windowFunctionTable3} order by x, y; """
+ // PERCENT_RANK
+ qt_sql """ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */ x,
y, percent_rank() over(partition by x order by y) as rank from
${windowFunctionTable3} order by x, y; """
+ // CUME_DIST
+ qt_sql """ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */ x,
y, cume_dist() over(partition by x order by y) as rank from
${windowFunctionTable3} order by x, y; """
// ROW_NUMBER
- // Nereids does't support window function
- // qt_sql """ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
x, y, row_number() over(partition by x order by y) as rank from
${windowFunctionTable3} order by x, y; """
+ qt_sql """ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */ x,
y, row_number() over(partition by x order by y) as rank from
${windowFunctionTable3} order by x, y; """
sql """ drop table ${windowFunctionTable3} """
@@ -332,11 +307,9 @@ suite("test_window_function") {
sql """ insert into ${windowFunctionTable4} VALUES
('Pete','USA','Hello'),('John','USA','Hi'),('Boris','Germany','Guten
tag'),('Michael','Germany','Guten
morgen'),('Bjorn','Sweden','Hej'),('Mats','Sweden','Tja')"""
// first_value
- // Nereids does't support window function
- // qt_sql """ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
country, name,first_value(greeting) over (partition by country order by name,
greeting) as greeting from ${windowFunctionTable4} order by country, name; """
+ qt_sql """ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
country, name,first_value(greeting) over (partition by country order by name,
greeting) as greeting from ${windowFunctionTable4} order by country, name; """
// last_value
- // Nereids does't support window function
- // qt_sql """ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
country, name,last_value(greeting) over (partition by country order by name,
greeting) as greeting from ${windowFunctionTable4} order by country, name; """
+ qt_sql """ select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
country, name,last_value(greeting) over (partition by country order by name,
greeting) as greeting from ${windowFunctionTable4} order by country, name; """
sql """ drop table ${windowFunctionTable4} """
@@ -347,200 +320,173 @@ suite("test_window_function") {
String k1 = fields[3]
String k2 = fields[5]
String k3 = fields[3]
- // Nereids does't support window function
- // qt_first_value1"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, first_value(${k2})
over (partition by ${k1} order by ${k3}, ${k2})
- // as wj from baseall order by ${k1}, wj"""
- // Nereids does't support window function
- // qt_first_value2"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, first_value(${k2})
over (partition by ${k1} order by ${k3}, ${k2}
- // range between unbounded preceding and current row)
- // as wj from baseall order by ${k1}, wj"""
- // Nereids does't support window function
- // qt_first_value3"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, first_value(${k2})
over (partition by ${k1} order by ${k3}, ${k2}
- // rows between unbounded preceding and current row)
- // as wj from baseall order by ${k1}, wj"""
- // Nereids does't support this type
- // qt_first_value4"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ a, min(d) as wjj from
- // (select t1.k1 as k1, t1.k2 as k2, t1.k3 as k3,
- // t1.k4 as k4, t1.k5 as k5,t1.k6 as k6,
- // t1.k7 as k7, t1.k8 as k8, t1.k9 as k9,
- // t1.k10 as k10, t1.k11 as k11,
- // t1.${k1} as a, t1.${k2} as b, t2.${k2} as c, t2.${k3} as d
- // from baseall t1 join baseall t2
- // where t1.${k1}=t2.${k1} and t1.${k3}>=t2.${k3}) T
- // group by k1, k2, k3, k4, k5, k6, k7, k8, k9, k10, k11, a, b
- // order by a, wjj"""
+ qt_first_value1"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ ${k1}, first_value(${k2}) over (partition by ${k1} order by ${k3}, ${k2})
+ as wj from baseall order by ${k1}, wj"""
+ qt_first_value2"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ ${k1}, first_value(${k2}) over (partition by ${k1} order by ${k3}, ${k2}
+ range between unbounded preceding and current row)
+ as wj from baseall order by ${k1}, wj"""
+ qt_first_value3"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ ${k1}, first_value(${k2}) over (partition by ${k1} order by ${k3}, ${k2}
+ rows between unbounded preceding and current row)
+ as wj from baseall order by ${k1}, wj"""
+ qt_first_value4"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ a, min(d) as wjj from
+ (select t1.k1 as k1, t1.k2 as k2, t1.k3 as k3,
+ t1.k4 as k4, t1.k5 as k5,t1.k6 as k6,
+ t1.k7 as k7, t1.k8 as k8, t1.k9 as k9,
+ t1.k10 as k10, t1.k11 as k11,
+ t1.${k1} as a, t1.${k2} as b, t2.${k2} as c, t2.${k3} as d
+ from baseall t1 join baseall t2
+ where t1.${k1}=t2.${k1} and t1.${k3}>=t2.${k3}) T
+ group by k1, k2, k3, k4, k5, k6, k7, k8, k9, k10, k11, a, b
+ order by a, wjj"""
// test_query_last_value
- // Nereids does't support window function
- // qt_last_value1"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, last_value(${k2})
over (partition by ${k1} order by ${k3},${k2})
- // as wj from baseall order by ${k1}, wj"""
- // Nereids does't support window function
- // qt_last_value2"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, last_value(${k2})
over (partition by ${k1} order by ${k3},${k2}
- // range between unbounded preceding and current row)
- // as wj from baseall order by ${k1}, wj"""
- // Nereids does't support window function
- // qt_last_value3"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, last_value(${k2})
over (partition by ${k1} order by ${k3},${k2}
- // rows between unbounded preceding and current row)
- // as wj from baseall order by ${k1}, wj"""
- // Nereids does't support this type
- // qt_last_value4"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ a, max(d) as wjj from
- // (select t1.k1 as k1, t1.k2 as k2, t1.k3 as k3,
- // t1.k4 as k4, t1.k5 as k5,t1.k6 as k6,
- // t1.k7 as k7, t1.k8 as k8, t1.k9 as k9,
- // t1.k10 as k10, t1.k11 as k11,
- // t1.${k1} as a, t1.${k2} as b, t2.${k2} as c, t2.${k3} as d
- // from baseall t1 join baseall t2
- // where t1.${k1}=t2.${k1} and t1.${k3}>=t2.${k3}) T
- // group by k1, k2, k3, k4, k5, k6, k7, k8, k9, k10, k11, a, b
- // order by a, wjj"""
+ qt_last_value1"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ ${k1}, last_value(${k2}) over (partition by ${k1} order by ${k3},${k2})
+ as wj from baseall order by ${k1}, wj"""
+ qt_last_value2"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ ${k1}, last_value(${k2}) over (partition by ${k1} order by ${k3},${k2}
+ range between unbounded preceding and current row)
+ as wj from baseall order by ${k1}, wj"""
+ qt_last_value3"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ ${k1}, last_value(${k2}) over (partition by ${k1} order by ${k3},${k2}
+ rows between unbounded preceding and current row)
+ as wj from baseall order by ${k1}, wj"""
+ qt_last_value4"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ a, max(d) as wjj from
+ (select t1.k1 as k1, t1.k2 as k2, t1.k3 as k3,
+ t1.k4 as k4, t1.k5 as k5,t1.k6 as k6,
+ t1.k7 as k7, t1.k8 as k8, t1.k9 as k9,
+ t1.k10 as k10, t1.k11 as k11,
+ t1.${k1} as a, t1.${k2} as b, t2.${k2} as c, t2.${k3} as d
+ from baseall t1 join baseall t2
+ where t1.${k1}=t2.${k1} and t1.${k3}>=t2.${k3}) T
+ group by k1, k2, k3, k4, k5, k6, k7, k8, k9, k10, k11, a, b
+ order by a, wjj"""
// test_query_row_number
- // Nereids does't support window function
- // qt_row_number1"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, row_number() over
(partition by ${k1} order by ${k3})
- // as wj from baseall order by ${k1}, wj"""
- // Nereids does't support window function
- // qt_row_number2"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, count(k1) over
(partition by ${k1} order by ${k3}
- // rows between unbounded preceding and current row)
- // as wj from baseall order by ${k1}, wj"""
+ qt_row_number1"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ ${k1}, row_number() over (partition by ${k1} order by ${k3})
+ as wj from baseall order by ${k1}, wj"""
+ qt_row_number2"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ ${k1}, count(k1) over (partition by ${k1} order by ${k3}
+ rows between unbounded preceding and current row)
+ as wj from baseall order by ${k1}, wj"""
// test error
- // Nereids does't support window function
- // test {
- // sql("select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, lag(${k2}) over (partition by ${k1} order by ${k3}) from baseall")
- // exception "errCode = 2, detailMessage = Lag/offset must have three
parameters"
- // }
- // Nereids does't support window function
- // test {
- // sql"select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, lag(${k2}, -1, 1) over (partition by ${k1} order by ${k3}) from baseall"
- // check { result, exception, startTime, endTime ->
- // assertTrue(exception != null)
- // }
- // }
- // Nereids does't support window function
- // test {
- // sql"select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, lag(${k2}, 1) over (partition by ${k1} order by ${k3}) from baseall"
- // check { result, exception, startTime, endTime ->
- // assertTrue(exception != null)
- // }
- // }
- // Nereids does't support window function
- // test {
- // sql"select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, lead(${k2}) over (partition by ${k1} order by ${k3}) from baseall"
- // check { result, exception, startTime, endTime ->
- // assertTrue(exception != null)
- // }
- // }
- // Nereids does't support window function
- // test {
- // sql"select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, lead(${k2}, -1, 1) over (partition by ${k1} order by ${k3}) from baseall"
- // check { result, exception, startTime, endTime ->
- // assertTrue(exception != null)
- // }
- // }
- // Nereids does't support window function
- // test {
- // sql"select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, lead(${k2}, 1) over (partition by ${k1} order by ${k3}) from baseall"
- // check { result, exception, startTime, endTime ->
- // assertTrue(exception != null)
- // }
- // }
- // Nereids does't support window function
- // qt_window_error1"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, first_value(${k2})
over (partition by ${k1}) from baseall order by ${k1}"""
- // Nereids does't support window function
- // qt_window_error2"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, first_value(${k2})
over (order by ${k3}) from baseall"""
- // Nereids does't support window function
- // qt_window_error3"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, max(${k2}) over
(order by ${k3}) from baseall"""
- // Nereids does't support window function
- // test {
- // sql"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, sum(${k2}) over (partition by ${k1} order by ${k3} rows
- // between current row and unbounded preceding) as wj
- // from baseall order by ${k1}, wj"""
- // check { result, exception, startTime, endTime ->
- // assertTrue(exception != null)
- // }
- // }
- // Nereids does't support window function
- // test {
- // sql"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, sum(${k2}) over (partition by ${k1} order by ${k3} rows
- // between 0 preceding and 1 following) as wj
- // from baseall order by ${k1}, wj"""
- // check { result, exception, startTime, endTime ->
- // assertTrue(exception != null)
- // }
- // }
- // Nereids does't support window function
- // test {
- // sql"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, sum(${k2}) over (partition by ${k1} order by ${k3} rows
- // between unbounded following and current row) as wj
- // from baseall order by ${k1}, wj"""
- // check { result, exception, startTime, endTime ->
- // assertTrue(exception != null)
- // }
- // }
- // Nereids does't support window function
- // test {
- // sql"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, rank(${k2}) over (partition by ${k1} order by ${k3}) as wj
- // from baseall order by ${k1}, wj"""
- // check { result, exception, startTime, endTime ->
- // assertTrue(exception != null)
- // }
- // }
- // Nereids does't support window function
- // test {
- // sql"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, max() over (partition by ${k1} order by ${k3}) as wj
- // from baseall order by ${k1}, wj"""
- // check { result, exception, startTime, endTime ->
- // assertTrue(exception != null)
- // }
- // }
- // Nereids does't support window function
- // test {
- // sql"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, count(*) over (partition by ${k1} order by ${k3}) as wj
- // from baseall order by ${k1}, wj"""
- // check { result, exception, startTime, endTime ->
- // assertTrue(exception != null)
- // }
- // }
- // Nereids does't support window function
- // test {
- // sql"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, count(${k2}) over (order by ${k1} rows partition by ${k3}) as wj
- // from baseall order by ${k1}, wj"""
- // check { result, exception, startTime, endTime ->
- // assertTrue(exception != null)
- // }
- // }
+ test {
+ sql("select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, lag(${k2}) over (partition by ${k1} order by ${k3}) from baseall")
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+ test {
+ sql"select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1},
lag(${k2}, -1, 1) over (partition by ${k1} order by ${k3}) from baseall"
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+ test {
+ sql"select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1},
lag(${k2}, 1) over (partition by ${k1} order by ${k3}) from baseall"
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+ test {
+ sql"select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1},
lead(${k2}) over (partition by ${k1} order by ${k3}) from baseall"
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+ test {
+ sql"select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1},
lead(${k2}, -1, 1) over (partition by ${k1} order by ${k3}) from baseall"
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+ test {
+ sql"select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1},
lead(${k2}, 1) over (partition by ${k1} order by ${k3}) from baseall"
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+ qt_window_error1"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, first_value(${k2})
over (partition by ${k1}) from baseall order by ${k1}"""
+ qt_window_error2"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, first_value(${k2})
over (order by ${k3}) from baseall"""
+ qt_window_error3"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, max(${k2}) over
(order by ${k3}) from baseall"""
+ test {
+ sql"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, sum(${k2}) over (partition by ${k1} order by ${k3} rows
+ between current row and unbounded preceding) as wj
+ from baseall order by ${k1}, wj"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+ test {
+ sql"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, sum(${k2}) over (partition by ${k1} order by ${k3} rows
+ between 0 preceding and 1 following) as wj
+ from baseall order by ${k1}, wj"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+ test {
+ sql"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, sum(${k2}) over (partition by ${k1} order by ${k3} rows
+ between unbounded following and current row) as wj
+ from baseall order by ${k1}, wj"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+ test {
+ sql"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, rank(${k2}) over (partition by ${k1} order by ${k3}) as wj
+ from baseall order by ${k1}, wj"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+ test {
+ sql"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, max() over (partition by ${k1} order by ${k3}) as wj
+ from baseall order by ${k1}, wj"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+ test {
+ sql"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, count(*) over (partition by ${k1} order by ${k3}) as wj
+ from baseall order by ${k1}, wj"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+ test {
+ sql"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, count(${k2}) over (order by ${k1} rows partition by ${k3}) as wj
+ from baseall order by ${k1}, wj"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
// test_query_rank
k3 = fields[7]
- // Nereids does't support window function
- // qt_rank1"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, rank() over (partition by ${k1} order by ${k3}) as wj
- // from baseall order by ${k1}, wj"""
- // Nereids does't support this type
- // qt_rank2"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
F2.${k1}, (F1.wj - F2.basewj + 1) as wj from
- // (select a, c, count(*) as wj from
- // (select t1.k1 as k1, t1.k2 as k2, t1.k3 as k3,
- // t1.k4 as k4, t1.k5 as k5,t1.k6 as k6,
- // t1.k7 as k7, t1.k8 as k8, t1.k9 as k9,
- // t1.k10 as k10, t1.k11 as k11,
- // t1.${k1} as a, t1.${k3} as c
- // from baseall t1 join baseall t2
- // where t1.${k1}=t2.${k1} and t1.${k3}>=t2.${k3}) T
- // group by k1, k2, k3, k4, k5, k6, k7, k8, k9, k10, k11, a, c)
as F1 join
- // (select ${k1}, ${k3}, count(*) as basewj from baseall group by
${k1}, ${k3}) as F2
- // where F1.a=F2.${k1} and F1.c = F2.${k3} order by F2.${k1},
wj"""
+ qt_rank1"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
${k1}, rank() over (partition by ${k1} order by ${k3}) as wj
+ from baseall order by ${k1}, wj"""
+ qt_rank2"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
F2.${k1}, (F1.wj - F2.basewj + 1) as wj from
+ (select a, c, count(*) as wj from
+ (select t1.k1 as k1, t1.k2 as k2, t1.k3 as k3,
+ t1.k4 as k4, t1.k5 as k5,t1.k6 as k6,
+ t1.k7 as k7, t1.k8 as k8, t1.k9 as k9,
+ t1.k10 as k10, t1.k11 as k11,
+ t1.${k1} as a, t1.${k3} as c
+ from baseall t1 join baseall t2
+ where t1.${k1}=t2.${k1} and t1.${k3}>=t2.${k3}) T
+ group by k1, k2, k3, k4, k5, k6, k7, k8, k9, k10, k11, a, c) as
F1 join
+ (select ${k1}, ${k3}, count(*) as basewj from baseall group by
${k1}, ${k3}) as F2
+ where F1.a=F2.${k1} and F1.c = F2.${k3} order by F2.${k1}, wj"""
//test_hang
- // Nereids does't support window function
- // qt_window_hang1"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ ${k1}, row_number() over
(partition by ${k1} order by ${k3}) as wj from
- // baseall order by ${k1}, wj"""
+ qt_window_hang1"""select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ ${k1}, row_number() over (partition by ${k1} order by ${k3}) as wj from
+ baseall order by ${k1}, wj"""
String line = "("
String cur
for (p in range(0, 829)) {
if (p == 0) {
- cur = "(select /*+SET_VAR(parallel_fragment_exec_instance_num=1,
parallel_pipeline_task_num=1) */ ${k1}, 1 as wj from baseall order by ${k1},
${k3} limit 1)".toString()
+ cur = "(select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ ${k1}, 1 as wj from baseall order by ${k1}, ${k3} limit 1)".toString()
}
else {
- cur = """(select /*+SET_VAR(parallel_fragment_exec_instance_num=1,
parallel_pipeline_task_num=1) */ ${k1}, ${p+1} as wj from baseall order by
${k1} , ${k3}
+ cur = """(select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ ${k1}, ${p+1} as wj from baseall order by ${k1} , ${k3}
limit ${p}, 1 ) """.toString()
}
@@ -554,19 +500,18 @@ suite("test_window_function") {
sql """ admin set frontend
config("remote_fragment_exec_timeout_ms"="300000"); """
- // Nereids does't support this type
- // qt_window_hang2"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ A.${k1}, A.wj - B.dyk + 1
as num from
- // (select ${k1}, wj from ${line} as W1) as A join
- // (select ${k1}, min(wj) as dyk from ${line} as W2 group by ${k1}) as
B
- // where A.${k1}=B.${k1} order by A.${k1}, num"""
+ //qt_window_hang2"""select
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ A.${k1}, A.wj - B.dyk + 1
as num from
+ // (select ${k1}, wj from ${line} as W1) as A join
+ // (select ${k1}, min(wj) as dyk from ${line} as W2 group by ${k1}) as B
+ // where A.${k1}=B.${k1} order by A.${k1}, num"""
//test_hujie
line = "("
for (p in range(0, 829)) {
if (p == 0 ) {
- cur = "(select /*+SET_VAR(parallel_fragment_exec_instance_num=1,
parallel_pipeline_task_num=1) */ * from baseall order by k1, k6 limit 1)"
+ cur = "(select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ * from baseall order by k1, k6 limit 1)"
} else {
- cur = "(select /*+SET_VAR(parallel_fragment_exec_instance_num=1,
parallel_pipeline_task_num=1) */ * from baseall order by k1, k6 limit ${p}, 1)"
+ cur = "(select /*+SET_VAR(parallel_fragment_exec_instance_num=1)
*/ * from baseall order by k1, k6 limit ${p}, 1)"
}
if (p < 828) {
line = line + cur + " union all "
@@ -575,17 +520,14 @@ suite("test_window_function") {
}
}
// qt_hujie1"select T.k1, T.k6 from ${line} as T order by T.k1, T.k6"
- // Nereids does't support this type
- // qt_hujie2"select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */
k1, k6 from baseall order by k1, k6"
+ qt_hujie2"select /*+SET_VAR(parallel_fragment_exec_instance_num=1) */ k1,
k6 from baseall order by k1, k6"
// test_bug
- // Nereids does't support window function
- // order_qt_window_bug1"""SELECT
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ wj FROM (SELECT
row_number() over (PARTITION BY k6 ORDER BY k1) AS wj
- // FROM baseall ) AS A where wj = 2"""
- // Nereids does't support window function
- // order_qt_window_bug2"""SELECT
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ A.k2 AS a, A.k1 as b, B.k1
as c, B.k2 as d FROM
- // ( SELECT k2, k1, row_number () over (PARTITION BY k2 ORDER BY
k3) AS wj
- // FROM baseall ) AS A JOIN ( SELECT k2, k1, row_number () over
- // (PARTITION BY k2 ORDER BY k3) AS wj FROM baseall ) AS B WHERE
A.k2=B.k2"""
+ order_qt_window_bug1"""SELECT
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ wj FROM (SELECT
row_number() over (PARTITION BY k6 ORDER BY k1) AS wj
+ FROM baseall ) AS A where wj = 2"""
+ order_qt_window_bug2"""SELECT
/*+SET_VAR(parallel_fragment_exec_instance_num=1) */ A.k2 AS a, A.k1 as b, B.k1
as c, B.k2 as d FROM
+ ( SELECT k2, k1, row_number () over (PARTITION BY k2 ORDER BY k3)
AS wj
+ FROM baseall ) AS A JOIN ( SELECT k2, k1, row_number () over
+ (PARTITION BY k2 ORDER BY k3) AS wj FROM baseall ) AS B WHERE
A.k2=B.k2"""
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
