This is an automated email from the ASF dual-hosted git repository.
dataroaring pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 6b6d548df9 [enhancement](test) add more p0 cases (#12285)
6b6d548df9 is described below
commit 6b6d548df964e1758b584b14a48d39746b52ebdd
Author: Yongqiang YANG <98214048+dataroar...@users.noreply.github.com>
AuthorDate: Thu Sep 29 10:45:17 2022 +0800
[enhancement](test) add more p0 cases (#12285)
---
.../data/correctness/test_unique_varchar.out | 19 ++++
.../data_model_p0/aggregate/test_replace_table.out | 34 +++++++
regression-test/data/delete_p0/test_seq_column.out | 16 +++
.../sql/likeOperatorWorks_order.out | 6 ++
.../window_functions/sf0.01/nation.csv | 25 +++++
.../window_functions/sf0.01/region.csv | 5 +
.../sql_functions/window_functions/sql/leadLag.out | 35 +++++++
.../sql/noOrderAllRowsPeers_order.out | 35 +++++++
.../window_functions/sql/rangeCurrent.out | 28 ++++++
.../window_functions/sql/rangeCurrent_order.out | 28 ++++++
.../sql/rangeNoPartition_order.out | 35 +++++++
.../window_functions/sql/rangeUnbounded.out | 28 ++++++
.../window_functions/sql/rangeUnbounded_order.out | 28 ++++++
.../window_functions/sql/rowsDifferentOrders.out | 35 +++++++
.../window_functions/sql/rowsDisjointWindows.out | 35 +++++++
.../window_functions/sql/rowsFirstLast.out | 35 +++++++
.../window_functions/sql/rowsFrame2F3F.out | 35 +++++++
.../window_functions/sql/rowsFrame2FUBF.out | 35 +++++++
.../window_functions/sql/rowsFrame3P2P.out | 35 +++++++
.../window_functions/sql/rowsFrameUBP2P.out | 35 +++++++
.../window_functions/sql/rowsIdenticalWindows.out | 35 +++++++
.../sql/rowsIntersectingWindows.out | 35 +++++++
.../window_functions/sql/rowsUBPImplicitCR.out | 35 +++++++
.../window_functions/sql/rowsUnbounded_order.out | 28 ++++++
.../sql/windowSpecificationAAcpA.out | 37 +++++++
.../sql/windowSpecificationABA.out | 37 +++++++
.../sql/windowSpecificationABcpA.out | 37 +++++++
.../window_functions/window_functions.out | 112 +++++++++++++++++++++
.../data/types/complex_types/basic_agg_test.out | 16 +++
...multipleWithExprsReferencedFromParent_order.out | 8 ++
.../withExprAccessingAnotherAndAlsoFromParents.out | 4 +
.../sql/withExprReferencedInWhereClause.out | 4 +
.../with_clause_p0/sql/withExprRepeated_order.out | 28 ++++++
.../sql/withExprUsingAnotherWithExpr.out | 9 ++
.../sql/withExprUsingOrderbyLimit.out | 8 ++
.../sql/withExprWithNoReferenceInParent.out | 4 +
.../sql/withSimpleExprInNestedFromClause.out | 4 +
.../org/apache/doris/regression/Config.groovy | 4 +-
.../query_p0/set_operations/ddl/lineitem.sql | 18 ++++
.../suites/query_p0/set_operations/ddl/nation.sql | 12 +++
.../suites/query_p0/set_operations/ddl/part.sql | 11 ++
.../query_p0/set_operations/ddl/partsupp.sql | 7 ++
.../suites/query_p0/set_operations/ddl/region.sql | 10 ++
.../query_p0/set_operations/ddl/supplier.sql | 9 ++
.../suites/query_p0/set_operations/ddl/workers.sql | 10 ++
.../suites/query_p0/set_operations/load.groovy | 62 ++++++++++++
.../query_p0/set_operations/sql/except_1_order.sql | 10 ++
.../query_p0/set_operations/sql/except_2_order.sql | 10 ++
.../query_p0/set_operations/sql/except_3_order.sql | 3 +
.../set_operations/sql/intersect_1_order.sql | 8 ++
.../set_operations/sql/intersect_2_order.sql | 8 ++
.../string_functions/ddl/lineitem.sql | 18 ++++
.../sql_functions/string_functions/ddl/nation.sql | 12 +++
.../sql_functions/string_functions/ddl/part.sql | 11 ++
.../string_functions/ddl/partsupp.sql | 7 ++
.../sql_functions/string_functions/ddl/region.sql | 10 ++
.../string_functions/ddl/supplier.sql | 9 ++
.../sql_functions/string_functions/ddl/workers.sql | 10 ++
.../sql_functions/string_functions/load.groovy | 62 ++++++++++++
.../sql/likeOperatorWorks_order.sql | 1 +
.../window_functions/ddl/lineitem.sql | 18 ++++
.../sql_functions/window_functions/ddl/nation.sql | 12 +++
.../sql_functions/window_functions/ddl/part.sql | 11 ++
.../window_functions/ddl/partsupp.sql | 7 ++
.../sql_functions/window_functions/ddl/region.sql | 10 ++
.../window_functions/ddl/supplier.sql | 9 ++
.../sql_functions/window_functions/ddl/workers.sql | 10 ++
.../sql_functions/window_functions/load.groovy | 64 ++++++++++++
.../sql_functions/window_functions/sql/leadLag.sql | 11 ++
.../sql/noOrderAllRowsPeers_order.sql | 3 +
.../window_functions/sql/rangeCurrent_order.sql | 1 +
.../sql/rangeNoPartition_order.sql | 4 +
.../window_functions/sql/rangeUnbounded_order.sql | 1 +
.../window_functions/sql/rowsDifferentOrders.sql | 10 ++
.../window_functions/sql/rowsDisjointWindows.sql | 9 ++
.../window_functions/sql/rowsFirstLast.sql | 8 ++
.../window_functions/sql/rowsFrame2F3F.sql | 7 ++
.../window_functions/sql/rowsFrame2FUBF.sql | 3 +
.../window_functions/sql/rowsFrame3P2P.sql | 7 ++
.../window_functions/sql/rowsFrameUBP2P.sql | 7 ++
.../window_functions/sql/rowsIdenticalWindows.sql | 13 +++
.../sql/rowsIntersectingWindows.sql | 14 +++
.../window_functions/sql/rowsUBPImplicitCR.sql | 7 ++
.../window_functions/sql/rowsUnbounded_order.sql | 1 +
.../sql/windowSpecificationAAcpA.sql | 11 ++
.../sql/windowSpecificationABA.sql | 11 ++
.../sql/windowSpecificationABcpA.sql | 10 ++
.../types/complex_types/basic_agg_test.groovy | 32 ++++++
.../suites/with_clause_p0/ddl/lineitem.sql | 18 ++++
.../suites/with_clause_p0/ddl/nation.sql | 12 +++
regression-test/suites/with_clause_p0/ddl/part.sql | 11 ++
.../suites/with_clause_p0/ddl/partsupp.sql | 7 ++
.../suites/with_clause_p0/ddl/region.sql | 10 ++
.../suites/with_clause_p0/ddl/supplier.sql | 9 ++
.../suites/with_clause_p0/ddl/workers.sql | 10 ++
regression-test/suites/with_clause_p0/load.groovy | 63 ++++++++++++
...multipleWithExprsReferencedFromParent_order.sql | 5 +
.../withExprAccessingAnotherAndAlsoFromParents.sql | 4 +
.../sql/withExprReferencedInWhereClause.sql | 3 +
.../with_clause_p0/sql/withExprRepeated_order.sql | 4 +
.../sql/withExprUsingAnotherWithExpr.sql | 6 ++
.../sql/withExprUsingOrderbyLimit.sql | 3 +
.../sql/withExprWithNoReferenceInParent.sql | 2 +
.../sql/withSimpleExprInNestedFromClause.sql | 2 +
104 files changed, 1808 insertions(+), 2 deletions(-)
diff --git a/regression-test/data/correctness/test_unique_varchar.out
b/regression-test/data/correctness/test_unique_varchar.out
new file mode 100644
index 0000000000..6de944f110
--- /dev/null
+++ b/regression-test/data/correctness/test_unique_varchar.out
@@ -0,0 +1,19 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !test_unique_varchar --
+0
+
+-- !test_unique_varchar_2 --
+0
+
+-- !test_unique_varchar_3 --
+1
+
+-- !test_unique_varchar_4 --
+1
+
+-- !test_unique_varchar_5 --
+1
+
+-- !test_unique_varchar_6 --
+10022251573354 10047151170936 36407 39576 85 15
+
diff --git
a/regression-test/data/data_model_p0/aggregate/test_replace_table.out
b/regression-test/data/data_model_p0/aggregate/test_replace_table.out
new file mode 100644
index 0000000000..9ac35dc756
--- /dev/null
+++ b/regression-test/data/data_model_p0/aggregate/test_replace_table.out
@@ -0,0 +1,34 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !test_replace_table --
+0
+
+-- !test_replace_table_2 --
+0
+
+-- !test_replace_table_3 --
+1
+
+-- !test_replace_table_4 --
+1
+
+-- !test_replace_table_5 --
+1
+
+-- !test_replace_table_6 --
+1
+
+-- !test_replace_table_7 --
+1
+
+-- !test_replace_table_8 --
+1
+
+-- !test_replace_table_9 --
+1
+
+-- !test_replace_table_10 --
+2
+
+-- !test_replace_table_11 --
+2
+
diff --git a/regression-test/data/delete_p0/test_seq_column.out
b/regression-test/data/delete_p0/test_seq_column.out
new file mode 100644
index 0000000000..b0c64a77cd
--- /dev/null
+++ b/regression-test/data/delete_p0/test_seq_column.out
@@ -0,0 +1,16 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !test_seq_column --
+0
+
+-- !test_seq_column_2 --
+0
+
+-- !test_seq_column_3 --
+1
+
+-- !test_seq_column_4 --
+0
+
+-- !test_seq_column_5 --
+0
+
diff --git
a/regression-test/data/query_p0/sql_functions/string_functions/sql/likeOperatorWorks_order.out
b/regression-test/data/query_p0/sql_functions/string_functions/sql/likeOperatorWorks_order.out
new file mode 100644
index 0000000000..5f16be37cd
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/string_functions/sql/likeOperatorWorks_order.out
@@ -0,0 +1,6 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !likeOperatorWorks_order --
+IRAN
+JAPAN
+JORDAN
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sf0.01/nation.csv
b/regression-test/data/query_p0/sql_functions/window_functions/sf0.01/nation.csv
new file mode 100644
index 0000000000..d92875f2e4
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sf0.01/nation.csv
@@ -0,0 +1,25 @@
+0,ALGERIA,0, haggle. carefully final deposits detect slyly agai
+1,ARGENTINA,1,al foxes promise slyly according to the regular accounts. bold
requests alon
+2,BRAZIL,1,y alongside of the pending deposits. carefully special packages are
about the ironic forges. slyly special
+3,CANADA,1,eas hang ironic silent packages. slyly regular packages are
furiously over the tithes. fluffily bold
+4,EGYPT,4,y above the carefully unusual theodolites. final dugouts are quickly
across the furiously regular d
+5,ETHIOPIA,0,ven packages wake quickly. regu
+6,FRANCE,3,refully final requests. regular ironi
+7,GERMANY,3,l platelets. regular accounts x-ray: unusual regular acco
+8,INDIA,2,ss excuses cajole slyly across the packages. deposits print aroun
+9,INDONESIA,2, slyly express asymptotes. regular deposits haggle slyly.
carefully ironic hockey players sleep blithely. carefull
+10,IRAN,4,efully alongside of the slyly final dependencies.
+11,IRAQ,4,nic deposits boost atop the quickly final requests? quickly regula
+12,JAPAN,2,ously. final express gifts cajole a
+13,JORDAN,4,ic deposits are blithely about the carefully regular pa
+14,KENYA,0, pending excuses haggle furiously deposits. pending express pinto
beans wake fluffily past t
+15,MOROCCO,0,rns. blithely bold courts among the closely regular packages use
furiously bold platelets?
+16,MOZAMBIQUE,0,s. ironic unusual asymptotes wake blithely r
+17,PERU,1,platelets. blithely pending dependencies use fluffily across the
even pinto beans. carefully silent accoun
+18,CHINA,2,c dependencies. furiously express notornis sleep slyly regular
accounts. ideas sleep. depos
+19,ROMANIA,3,ular asymptotes are about the furious multipliers. express
dependencies nag above the ironically ironic account
+20,SAUDI ARABIA,4,ts. silent requests haggle. closely express packages sleep
across the blithely
+21,VIETNAM,2,hely enticingly express accounts. even final
+22,RUSSIA,3, requests against the platelets use never according to the quickly
regular pint
+23,UNITED KINGDOM,3,eans boost carefully special requests. accounts are.
carefull
+24,UNITED STATES,1,y final packages. slow foxes cajole quickly. quickly silent
platelets breach ironic accounts. unusual pinto be
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sf0.01/region.csv
b/regression-test/data/query_p0/sql_functions/window_functions/sf0.01/region.csv
new file mode 100644
index 0000000000..26c343b5c7
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sf0.01/region.csv
@@ -0,0 +1,5 @@
+0,AFRICA,lar deposits. blithely final packages cajole. regular waters are
final requests. regular accounts are according to
+1,AMERICA,hs use ironic even requests. s
+2,ASIA,ges. thinly even pinto beans ca
+3,EUROPE,ly final courts cajole furiously final excuse
+4,MIDDLE EAST,uickly special accounts cajole carefully blithely close
requests. carefully final asymptotes haggle furiousl
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/leadLag.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/leadLag.out
new file mode 100644
index 0000000000..8d65a34a47
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/leadLag.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !leadLag --
+1509 27 0.04 \N 16411.78 2344.54
+1985 54 0.1 \N 38684.91 \N
+8673 54 0.02 0.1 57441.23 38684.91
+10727 73 0.02 \N 19928.59 10550.43
+12901 27 0.04 0.04 53924.42 45718.53
+14918 27 0.04 0.04 38684.91 16411.78
+17092 54 0.02 0.02 33995.83 24617.67
+20135 100 0.0 \N 28134.48 \N
+21056 100 0.02 0.0 38684.91 33995.83
+21314 27 0.07 0.04 28134.48 19928.59
+21346 27 0.09 0.07 4689.08 \N
+24643 54 0.03 0.02 35168.1 23445.4
+27014 27 0.02 0.09 10550.43 3516.81
+27394 54 0.05 0.03 22273.13 \N
+28326 73 0.09 0.02 42201.72 4689.08
+30406 73 0.04 0.09 45718.53 38684.91
+30822 100 0.07 0.02 58613.5 56268.96
+31911 100 0.02 0.07 3516.81 \N
+33600 100 0.05 0.02 37512.64 22273.13
+37063 100 0.06 0.05 14067.24 \N
+37185 73 0.07 0.04 42201.72 41029.45
+37186 54 0.07 0.05 5861.35 \N
+44387 27 0.03 0.02 23445.4 14067.24
+47365 100 0.04 0.06 2344.54 \N
+48032 27 0.02 0.03 22273.13 19928.59
+49095 27 0.02 0.02 24617.67 22273.13
+50304 100 0.07 0.04 56268.96 42201.72
+52067 54 0.0 0.07 44546.26 28134.48
+52258 54 0.07 0.0 19928.59 5861.35
+52736 27 0.08 0.02 15239.51 \N
+56898 27 0.07 0.08 41029.45 28134.48
+58279 54 0.03 0.07 14067.24 \N
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/noOrderAllRowsPeers_order.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/noOrderAllRowsPeers_order.out
new file mode 100644
index 0000000000..8c0f2fe75b
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/noOrderAllRowsPeers_order.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !noOrderAllRowsPeers_order --
+10727 73 0.02 1
+12901 27 0.04 1
+14918 27 0.04 1
+1509 27 0.04 1
+17092 54 0.02 1
+1985 54 0.1 1
+20135 100 0.0 1
+21056 100 0.02 1
+21314 27 0.07 1
+21346 27 0.09 1
+24643 54 0.03 1
+27014 27 0.02 1
+27394 54 0.05 1
+28326 73 0.09 1
+30406 73 0.04 1
+30822 100 0.07 1
+31911 100 0.02 1
+33600 100 0.05 1
+37063 100 0.06 1
+37185 73 0.07 1
+37186 54 0.07 1
+44387 27 0.03 1
+47365 100 0.04 1
+48032 27 0.02 1
+49095 27 0.02 1
+50304 100 0.07 1
+52067 54 0.0 1
+52258 54 0.07 1
+52736 27 0.08 1
+56898 27 0.07 1
+58279 54 0.03 1
+8673 54 0.02 1
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeCurrent.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeCurrent.out
new file mode 100644
index 0000000000..62189e74eb
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeCurrent.out
@@ -0,0 +1,28 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rangeCurrent --
+0 0
+14 0
+15 0
+16 0
+5 0
+1 1
+3 1
+17 1
+2 1
+24 1
+9 9
+18 9
+21 9
+12 9
+8 8
+22 22
+23 22
+7 7
+6 6
+19 6
+10 10
+13 10
+11 10
+20 10
+4 4
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeCurrent_order.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeCurrent_order.out
new file mode 100644
index 0000000000..e410112508
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeCurrent_order.out
@@ -0,0 +1,28 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rangeCurrent_order --
+0 0
+1 1
+10 10
+11 10
+12 9
+13 10
+14 0
+15 0
+16 0
+17 1
+18 9
+19 6
+2 1
+20 10
+21 9
+22 22
+23 22
+24 1
+3 1
+4 4
+5 0
+6 6
+7 7
+8 8
+9 9
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeNoPartition_order.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeNoPartition_order.out
new file mode 100644
index 0000000000..ca53a61c3c
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeNoPartition_order.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rangeNoPartition_order --
+10727 0.02 19928.59 3516.81 57441.23
+12901 0.04 53924.42 2344.54 53924.42
+14918 0.04 38684.91 2344.54 53924.42
+1509 0.04 16411.78 2344.54 53924.42
+17092 0.02 33995.83 3516.81 57441.23
+1985 0.1 38684.91 38684.91 38684.91
+20135 0.0 28134.48 28134.48 44546.26
+21056 0.02 38684.91 3516.81 57441.23
+21314 0.07 28134.48 5861.35 58613.5
+21346 0.09 4689.08 4689.08 42201.72
+24643 0.03 35168.1 14067.24 35168.1
+27014 0.02 10550.43 3516.81 57441.23
+27394 0.05 22273.13 22273.13 37512.64
+28326 0.09 42201.72 4689.08 42201.72
+30406 0.04 45718.53 2344.54 53924.42
+30822 0.07 58613.5 5861.35 58613.5
+31911 0.02 3516.81 3516.81 57441.23
+33600 0.05 37512.64 22273.13 37512.64
+37063 0.06 14067.24 14067.24 14067.24
+37185 0.07 42201.72 5861.35 58613.5
+37186 0.07 5861.35 5861.35 58613.5
+44387 0.03 23445.4 14067.24 35168.1
+47365 0.04 2344.54 2344.54 53924.42
+48032 0.02 22273.13 3516.81 57441.23
+49095 0.02 24617.67 3516.81 57441.23
+50304 0.07 56268.96 5861.35 58613.5
+52067 0.0 44546.26 28134.48 44546.26
+52258 0.07 19928.59 5861.35 58613.5
+52736 0.08 15239.51 15239.51 15239.51
+56898 0.07 41029.45 5861.35 58613.5
+58279 0.03 14067.24 14067.24 35168.1
+8673 0.02 57441.23 3516.81 57441.23
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeUnbounded.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeUnbounded.out
new file mode 100644
index 0000000000..0548c8e723
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeUnbounded.out
@@ -0,0 +1,28 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rangeUnbounded --
+0 0
+14 0
+15 0
+16 0
+5 0
+1 1
+3 1
+17 1
+2 1
+24 1
+9 8
+18 8
+21 8
+12 8
+8 8
+22 6
+23 6
+7 6
+6 6
+19 6
+10 4
+13 4
+11 4
+20 4
+4 4
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeUnbounded_order.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeUnbounded_order.out
new file mode 100644
index 0000000000..43d1c78fc6
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rangeUnbounded_order.out
@@ -0,0 +1,28 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rangeUnbounded_order --
+0 0
+1 1
+10 4
+11 4
+12 8
+13 4
+14 0
+15 0
+16 0
+17 1
+18 8
+19 6
+2 1
+20 4
+21 8
+22 6
+23 6
+24 1
+3 1
+4 4
+5 0
+6 6
+7 6
+8 8
+9 8
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsDifferentOrders.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsDifferentOrders.out
new file mode 100644
index 0000000000..c6618283b7
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsDifferentOrders.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rowsDifferentOrders --
+1509 27 16411.78 279000.26 0.04 0.04
+12901 27 53924.42 262588.48 0.04 0.04
+14918 27 38684.91 208664.06 0.04 0.04
+21314 27 28134.48 169979.15 0.07 0.0475
+21346 27 4689.08 141844.67 0.09 0.056
+27014 27 10550.43 137155.59 0.02 0.05
+44387 27 23445.4 126605.16 0.03 0.04714
+48032 27 22273.13 103159.76 0.02 0.04375
+49095 27 24617.67 80886.63 0.02 0.04111
+52736 27 15239.51 56268.96 0.08 0.045
+56898 27 41029.45 41029.45 0.07 0.04727
+1985 54 38684.91 271966.64 0.1 0.1
+8673 54 57441.23 233281.73 0.02 0.06
+17092 54 33995.83 175840.5 0.02 0.04667
+24643 54 35168.1 141844.67 0.03 0.0425
+27394 54 22273.13 106676.57 0.05 0.044
+37186 54 5861.35 84403.44 0.07 0.04833
+52067 54 44546.26 78542.09 0.0 0.04143
+52258 54 19928.59 33995.83 0.07 0.045
+58279 54 14067.24 14067.24 0.03 0.04333
+10727 73 19928.59 150050.56 0.02 0.02
+28326 73 42201.72 130121.97 0.09 0.055
+30406 73 45718.53 87920.25 0.04 0.05
+37185 73 42201.72 42201.72 0.07 0.055
+20135 100 28134.48 239143.08 0.0 0.0
+21056 100 38684.91 211008.6 0.02 0.01
+30822 100 58613.5 172323.69 0.07 0.03
+31911 100 3516.81 113710.19 0.02 0.0275
+33600 100 37512.64 110193.38 0.05 0.032
+37063 100 14067.24 72680.74 0.06 0.03667
+47365 100 2344.54 58613.5 0.04 0.03714
+50304 100 56268.96 56268.96 0.07 0.04125
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsDisjointWindows.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsDisjointWindows.out
new file mode 100644
index 0000000000..72fe94253f
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsDisjointWindows.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rowsDisjointWindows --
+1509 27 14.0 \N 16411.78 70336.2 0.04 0.05
+12901 27 46.0 14.0 53924.42 92609.33 0.04 0.04714
+14918 27 33.0 60.0 38684.91 66819.39 0.04 0.04
+21314 27 24.0 93.0 28134.48 32823.56 0.07 0.044
+21346 27 4.0 117.0 4689.08 15239.51 0.09 0.0475
+27014 27 9.0 121.0 10550.43 33995.83 0.02 0.05667
+44387 27 20.0 130.0 23445.4 45718.53 0.03 0.075
+48032 27 19.0 150.0 22273.13 46890.8 0.02 0.07
+49095 27 21.0 169.0 24617.67 39857.18 0.02 \N
+52736 27 13.0 190.0 15239.51 56268.96 0.08 \N
+56898 27 35.0 203.0 41029.45 41029.45 0.07 \N
+1985 54 33.0 \N 38684.91 96126.14 0.1 0.04167
+8673 54 49.0 33.0 57441.23 91437.06 0.02 0.044
+17092 54 29.0 82.0 33995.83 69163.93 0.02 0.0425
+24643 54 30.0 111.0 35168.1 57441.23 0.03 0.03333
+27394 54 19.0 141.0 22273.13 28134.48 0.05 0.05
+37186 54 5.0 160.0 5861.35 50407.61 0.07 0.03
+52067 54 38.0 165.0 44546.26 64474.85 0.0 \N
+52258 54 17.0 203.0 19928.59 33995.83 0.07 \N
+58279 54 12.0 220.0 14067.24 14067.24 0.03 \N
+10727 73 17.0 \N 19928.59 62130.31 0.02 0.07
+28326 73 36.0 17.0 42201.72 87920.25 0.09 \N
+30406 73 39.0 53.0 45718.53 87920.25 0.04 \N
+37185 73 36.0 92.0 42201.72 42201.72 0.07 \N
+20135 100 24.0 \N 28134.48 66819.39 0.0 0.048
+21056 100 33.0 24.0 38684.91 97298.41 0.02 0.055
+30822 100 50.0 57.0 58613.5 62130.31 0.07 0.05667
+31911 100 3.0 107.0 3516.81 41029.45 0.02 0.055
+33600 100 32.0 110.0 37512.64 51579.88 0.05 0.07
+37063 100 12.0 142.0 14067.24 16411.78 0.06 \N
+47365 100 2.0 154.0 2344.54 58613.5 0.04 \N
+50304 100 48.0 156.0 56268.96 56268.96 0.07 \N
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFirstLast.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFirstLast.out
new file mode 100644
index 0000000000..a5d06256ea
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFirstLast.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rowsFirstLast --
+1509 27 16411.78 53924.42 4689.08
+12901 27 53924.42 53924.42 4689.08
+14918 27 38684.91 53924.42 4689.08
+21314 27 28134.48 53924.42 4689.08
+21346 27 4689.08 53924.42 4689.08
+27014 27 10550.43 53924.42 4689.08
+44387 27 23445.4 53924.42 4689.08
+48032 27 22273.13 53924.42 4689.08
+49095 27 24617.67 53924.42 4689.08
+52736 27 15239.51 53924.42 4689.08
+56898 27 41029.45 53924.42 4689.08
+1985 54 38684.91 57441.23 5861.35
+8673 54 57441.23 57441.23 5861.35
+17092 54 33995.83 57441.23 5861.35
+24643 54 35168.1 57441.23 5861.35
+27394 54 22273.13 57441.23 5861.35
+37186 54 5861.35 57441.23 5861.35
+52067 54 44546.26 57441.23 5861.35
+52258 54 19928.59 57441.23 5861.35
+58279 54 14067.24 57441.23 5861.35
+10727 73 19928.59 45718.53 19928.59
+28326 73 42201.72 45718.53 19928.59
+30406 73 45718.53 45718.53 19928.59
+37185 73 42201.72 45718.53 19928.59
+20135 100 28134.48 58613.5 2344.54
+21056 100 38684.91 58613.5 2344.54
+30822 100 58613.5 58613.5 2344.54
+31911 100 3516.81 58613.5 2344.54
+33600 100 37512.64 58613.5 2344.54
+37063 100 14067.24 58613.5 2344.54
+47365 100 2344.54 58613.5 2344.54
+50304 100 56268.96 58613.5 2344.54
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFrame2F3F.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFrame2F3F.out
new file mode 100644
index 0000000000..6cde85fa96
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFrame2F3F.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rowsFrame2F3F --
+1509 27 14.0 57.0
+12901 27 46.0 28.0
+14918 27 33.0 13.0
+21314 27 24.0 29.0
+21346 27 4.0 39.0
+27014 27 9.0 40.0
+44387 27 20.0 34.0
+48032 27 19.0 48.0
+49095 27 21.0 35.0
+52736 27 13.0 \N
+56898 27 35.0 \N
+1985 54 33.0 59.0
+8673 54 49.0 49.0
+17092 54 29.0 24.0
+24643 54 30.0 43.0
+27394 54 19.0 55.0
+37186 54 5.0 29.0
+52067 54 38.0 12.0
+52258 54 17.0 \N
+58279 54 12.0 \N
+10727 73 17.0 75.0
+28326 73 36.0 36.0
+30406 73 39.0 \N
+37185 73 36.0 \N
+20135 100 24.0 53.0
+21056 100 33.0 35.0
+30822 100 50.0 44.0
+31911 100 3.0 14.0
+33600 100 32.0 50.0
+37063 100 12.0 48.0
+47365 100 2.0 \N
+50304 100 48.0 \N
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFrame2FUBF.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFrame2FUBF.out
new file mode 100644
index 0000000000..7a9770e156
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFrame2FUBF.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rowsFrame2FUBF --
+1509 27 14.0 178.0
+12901 27 46.0 145.0
+14918 27 33.0 121.0
+21314 27 24.0 117.0
+21346 27 4.0 108.0
+27014 27 9.0 88.0
+44387 27 20.0 69.0
+48032 27 19.0 48.0
+49095 27 21.0 35.0
+52736 27 13.0 \N
+56898 27 35.0 \N
+1985 54 33.0 150.0
+8673 54 49.0 121.0
+17092 54 29.0 91.0
+24643 54 30.0 72.0
+27394 54 19.0 67.0
+37186 54 5.0 29.0
+52067 54 38.0 12.0
+52258 54 17.0 \N
+58279 54 12.0 \N
+10727 73 17.0 75.0
+28326 73 36.0 36.0
+30406 73 39.0 \N
+37185 73 36.0 \N
+20135 100 24.0 147.0
+21056 100 33.0 97.0
+30822 100 50.0 94.0
+31911 100 3.0 62.0
+33600 100 32.0 50.0
+37063 100 12.0 48.0
+47365 100 2.0 \N
+50304 100 48.0 \N
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFrame3P2P.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFrame3P2P.out
new file mode 100644
index 0000000000..6ec2683e28
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFrame3P2P.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rowsFrame3P2P --
+1509 27 14.0 \N
+12901 27 46.0 \N
+14918 27 33.0 14.0
+21314 27 24.0 60.0
+21346 27 4.0 79.0
+27014 27 9.0 57.0
+44387 27 20.0 28.0
+48032 27 19.0 13.0
+49095 27 21.0 29.0
+52736 27 13.0 39.0
+56898 27 35.0 40.0
+1985 54 33.0 \N
+8673 54 49.0 \N
+17092 54 29.0 33.0
+24643 54 30.0 82.0
+27394 54 19.0 78.0
+37186 54 5.0 59.0
+52067 54 38.0 49.0
+52258 54 17.0 24.0
+58279 54 12.0 43.0
+10727 73 17.0 \N
+28326 73 36.0 \N
+30406 73 39.0 17.0
+37185 73 36.0 53.0
+20135 100 24.0 \N
+21056 100 33.0 \N
+30822 100 50.0 24.0
+31911 100 3.0 57.0
+33600 100 32.0 83.0
+37063 100 12.0 53.0
+47365 100 2.0 35.0
+50304 100 48.0 44.0
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFrameUBP2P.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFrameUBP2P.out
new file mode 100644
index 0000000000..31d33d3c5a
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsFrameUBP2P.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rowsFrameUBP2P --
+1509 27 14.0 \N
+12901 27 46.0 \N
+14918 27 33.0 14.0
+21314 27 24.0 60.0
+21346 27 4.0 93.0
+27014 27 9.0 117.0
+44387 27 20.0 121.0
+48032 27 19.0 130.0
+49095 27 21.0 150.0
+52736 27 13.0 169.0
+56898 27 35.0 190.0
+1985 54 33.0 \N
+8673 54 49.0 \N
+17092 54 29.0 33.0
+24643 54 30.0 82.0
+27394 54 19.0 111.0
+37186 54 5.0 141.0
+52067 54 38.0 160.0
+52258 54 17.0 165.0
+58279 54 12.0 203.0
+10727 73 17.0 \N
+28326 73 36.0 \N
+30406 73 39.0 17.0
+37185 73 36.0 53.0
+20135 100 24.0 \N
+21056 100 33.0 \N
+30822 100 50.0 24.0
+31911 100 3.0 57.0
+33600 100 32.0 107.0
+37063 100 12.0 110.0
+47365 100 2.0 142.0
+50304 100 48.0 154.0
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsIdenticalWindows.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsIdenticalWindows.out
new file mode 100644
index 0000000000..75ebb2a784
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsIdenticalWindows.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rowsIdenticalWindows --
+1509 27 14.0 14.0 16411.78 16411.78 0.04 0.04
+12901 27 46.0 60.0 53924.42 70336.2 0.04 0.04
+14918 27 33.0 93.0 38684.91 109021.11 0.04 0.04
+21314 27 24.0 117.0 28134.48 137155.59 0.07 0.0475
+21346 27 4.0 121.0 4689.08 141844.67 0.09 0.056
+27014 27 9.0 130.0 10550.43 152395.1 0.02 0.05
+44387 27 20.0 150.0 23445.4 175840.5 0.03 0.04714
+48032 27 19.0 169.0 22273.13 198113.63 0.02 0.04375
+49095 27 21.0 190.0 24617.67 222731.3 0.02 0.04111
+52736 27 13.0 203.0 15239.51 237970.81 0.08 0.045
+56898 27 35.0 238.0 41029.45 279000.26 0.07 0.04727
+1985 54 33.0 33.0 38684.91 38684.91 0.1 0.1
+8673 54 49.0 82.0 57441.23 96126.14 0.02 0.06
+17092 54 29.0 111.0 33995.83 130121.97 0.02 0.04667
+24643 54 30.0 141.0 35168.1 165290.07 0.03 0.0425
+27394 54 19.0 160.0 22273.13 187563.2 0.05 0.044
+37186 54 5.0 165.0 5861.35 193424.55 0.07 0.04833
+52067 54 38.0 203.0 44546.26 237970.81 0.0 0.04143
+52258 54 17.0 220.0 19928.59 257899.4 0.07 0.045
+58279 54 12.0 232.0 14067.24 271966.64 0.03 0.04333
+10727 73 17.0 17.0 19928.59 19928.59 0.02 0.02
+28326 73 36.0 53.0 42201.72 62130.31 0.09 0.055
+30406 73 39.0 92.0 45718.53 107848.84 0.04 0.05
+37185 73 36.0 128.0 42201.72 150050.56 0.07 0.055
+20135 100 24.0 24.0 28134.48 28134.48 0.0 0.0
+21056 100 33.0 57.0 38684.91 66819.39 0.02 0.01
+30822 100 50.0 107.0 58613.5 125432.89 0.07 0.03
+31911 100 3.0 110.0 3516.81 128949.7 0.02 0.0275
+33600 100 32.0 142.0 37512.64 166462.34 0.05 0.032
+37063 100 12.0 154.0 14067.24 180529.58 0.06 0.03667
+47365 100 2.0 156.0 2344.54 182874.12 0.04 0.03714
+50304 100 48.0 204.0 56268.96 239143.08 0.07 0.04125
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsIntersectingWindows.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsIntersectingWindows.out
new file mode 100644
index 0000000000..2f45d32527
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsIntersectingWindows.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rowsIntersectingWindows --
+1509 27 14.0 60.0 16411.78 109021.11 0.04 0.04727
+12901 27 46.0 93.0 53924.42 137155.59 0.04 0.048
+14918 27 33.0 117.0 38684.91 125432.89 0.04 0.04889
+21314 27 24.0 121.0 28134.48 82058.9 0.07 0.05
+21346 27 4.0 116.0 4689.08 66819.39 0.09 0.04714
+27014 27 9.0 90.0 10550.43 60958.04 0.02 0.04
+44387 27 20.0 76.0 23445.4 80886.63 0.03 0.044
+48032 27 19.0 73.0 22273.13 85575.71 0.02 0.0475
+49095 27 21.0 82.0 24617.67 103159.76 0.02 0.05667
+52736 27 13.0 108.0 15239.51 80886.63 0.08 0.075
+56898 27 35.0 88.0 41029.45 56268.96 0.07 0.07
+1985 54 33.0 82.0 38684.91 130121.97 0.1 0.04333
+8673 54 49.0 111.0 57441.23 165290.07 0.02 0.03625
+17092 54 29.0 141.0 33995.83 148878.29 0.02 0.03857
+24643 54 30.0 160.0 35168.1 97298.41 0.03 0.04167
+27394 54 19.0 132.0 22273.13 107848.84 0.05 0.044
+37186 54 5.0 121.0 5861.35 92609.33 0.07 0.0425
+52067 54 38.0 109.0 44546.26 84403.44 0.0 0.03333
+52258 54 17.0 91.0 19928.59 78542.09 0.07 0.05
+58279 54 12.0 72.0 14067.24 33995.83 0.03 0.03
+10727 73 17.0 53.0 19928.59 107848.84 0.02 0.055
+28326 73 36.0 92.0 42201.72 150050.56 0.09 0.06667
+30406 73 39.0 128.0 45718.53 130121.97 0.04 0.055
+37185 73 36.0 128.0 42201.72 87920.25 0.07 0.07
+20135 100 24.0 57.0 28134.48 125432.89 0.0 0.04125
+21056 100 33.0 107.0 38684.91 128949.7 0.02 0.04714
+30822 100 50.0 110.0 58613.5 138327.86 0.07 0.05167
+31911 100 3.0 142.0 3516.81 113710.19 0.02 0.048
+33600 100 32.0 130.0 37512.64 57441.23 0.05 0.055
+37063 100 12.0 99.0 14067.24 110193.38 0.06 0.05667
+47365 100 2.0 97.0 2344.54 72680.74 0.04 0.055
+50304 100 48.0 94.0 56268.96 58613.5 0.07 0.07
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsUBPImplicitCR.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsUBPImplicitCR.out
new file mode 100644
index 0000000000..a10fbcbf2e
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsUBPImplicitCR.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rowsUBPImplicitCR --
+1509 27 14.0 14.0
+12901 27 46.0 60.0
+14918 27 33.0 93.0
+21314 27 24.0 117.0
+21346 27 4.0 121.0
+27014 27 9.0 130.0
+44387 27 20.0 150.0
+48032 27 19.0 169.0
+49095 27 21.0 190.0
+52736 27 13.0 203.0
+56898 27 35.0 238.0
+1985 54 33.0 33.0
+8673 54 49.0 82.0
+17092 54 29.0 111.0
+24643 54 30.0 141.0
+27394 54 19.0 160.0
+37186 54 5.0 165.0
+52067 54 38.0 203.0
+52258 54 17.0 220.0
+58279 54 12.0 232.0
+10727 73 17.0 17.0
+28326 73 36.0 53.0
+30406 73 39.0 92.0
+37185 73 36.0 128.0
+20135 100 24.0 24.0
+21056 100 33.0 57.0
+30822 100 50.0 107.0
+31911 100 3.0 110.0
+33600 100 32.0 142.0
+37063 100 12.0 154.0
+47365 100 2.0 156.0
+50304 100 48.0 204.0
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsUnbounded_order.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsUnbounded_order.out
new file mode 100644
index 0000000000..0d9d5cf7c0
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/rowsUnbounded_order.out
@@ -0,0 +1,28 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !rowsUnbounded_order --
+0 0
+1 1
+10 4
+11 4
+12 8
+13 4
+14 0
+15 0
+16 0
+17 1
+18 8
+19 6
+2 1
+20 4
+21 8
+22 6
+23 6
+24 1
+3 1
+4 4
+5 0
+6 6
+7 6
+8 8
+9 8
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/windowSpecificationAAcpA.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/windowSpecificationAAcpA.out
new file mode 100644
index 0000000000..b7431581aa
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/windowSpecificationAAcpA.out
@@ -0,0 +1,37 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !windowSpecificationAAcpA --
+54 1985 272 33.0 33.0 0.1
+54 8673 272 82.0 33.0 0.12
+54 17092 272 111.0 33.0 0.14
+54 24643 272 141.0 33.0 0.17
+54 27394 272 160.0 33.0 0.22
+54 37186 272 165.0 33.0 0.29
+54 52067 272 203.0 33.0 0.29
+54 52258 272 220.0 33.0 0.36
+54 58279 272 232.0 33.0 0.39
+55 11876 273 14.0 14.0 0.05
+55 22243 273 50.0 14.0 0.05
+55 25826 273 89.0 14.0 0.08
+55 26496 273 102.0 14.0 0.11
+55 36582 273 125.0 14.0 0.2
+55 55011 273 138.0 14.0 0.26
+73 10727 272 17.0 17.0 0.02
+73 28326 272 53.0 17.0 0.11
+73 30406 272 92.0 17.0 0.15
+73 37185 272 128.0 17.0 0.22
+74 455 273 44.0 44.0 0.05
+74 14661 273 76.0 44.0 0.05
+74 20037 273 118.0 44.0 0.14
+74 22113 273 140.0 44.0 0.19
+74 43782 273 186.0 44.0 0.24
+74 45319 273 202.0 44.0 0.24
+74 53056 273 251.0 44.0 0.3
+100 20135 272 24.0 24.0 0.0
+100 21056 272 57.0 24.0 0.02
+100 30822 272 107.0 24.0 0.09
+100 31911 272 110.0 24.0 0.11
+100 33600 272 142.0 24.0 0.16
+100 37063 272 154.0 24.0 0.22
+100 47365 272 156.0 24.0 0.26
+100 50304 272 204.0 24.0 0.33
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/windowSpecificationABA.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/windowSpecificationABA.out
new file mode 100644
index 0000000000..65347b378f
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/windowSpecificationABA.out
@@ -0,0 +1,37 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !windowSpecificationABA --
+54 1985 272 33.0 33.0 0.1
+54 8673 272 82.0 49.0 0.12
+54 17092 272 111.0 29.0 0.14
+54 24643 272 141.0 30.0 0.17
+54 27394 272 160.0 19.0 0.22
+54 37186 272 165.0 5.0 0.29
+54 52067 272 203.0 38.0 0.29
+54 52258 272 220.0 17.0 0.36
+54 58279 272 232.0 12.0 0.39
+55 11876 273 14.0 14.0 0.05
+55 22243 273 50.0 36.0 0.05
+55 25826 273 89.0 39.0 0.08
+55 26496 273 102.0 13.0 0.11
+55 36582 273 125.0 23.0 0.2
+55 55011 273 138.0 13.0 0.26
+73 10727 272 17.0 17.0 0.02
+73 28326 272 53.0 36.0 0.11
+73 30406 272 92.0 39.0 0.15
+73 37185 272 128.0 36.0 0.22
+74 455 273 44.0 44.0 0.05
+74 14661 273 76.0 32.0 0.05
+74 20037 273 118.0 42.0 0.14
+74 22113 273 140.0 22.0 0.19
+74 43782 273 186.0 46.0 0.24
+74 45319 273 202.0 16.0 0.24
+74 53056 273 251.0 49.0 0.3
+100 20135 272 24.0 24.0 0.0
+100 21056 272 57.0 33.0 0.02
+100 30822 272 107.0 50.0 0.09
+100 31911 272 110.0 3.0 0.11
+100 33600 272 142.0 32.0 0.16
+100 37063 272 154.0 12.0 0.22
+100 47365 272 156.0 2.0 0.26
+100 50304 272 204.0 48.0 0.33
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/sql/windowSpecificationABcpA.out
b/regression-test/data/query_p0/sql_functions/window_functions/sql/windowSpecificationABcpA.out
new file mode 100644
index 0000000000..a5ccfc1fcf
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/sql/windowSpecificationABcpA.out
@@ -0,0 +1,37 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !windowSpecificationABcpA --
+54 1985 272 33.0 0.0 0.1
+54 8673 272 82.0 33.0 0.12
+54 17092 272 111.0 17.0 0.14
+54 24643 272 141.0 33.0 0.17
+54 27394 272 160.0 30.0 0.22
+54 37186 272 165.0 36.0 0.29
+54 52067 272 203.0 48.0 0.29
+54 52258 272 220.0 38.0 0.36
+54 58279 272 232.0 17.0 0.39
+55 11876 273 14.0 44.0 0.05
+55 22243 273 50.0 22.0 0.05
+55 25826 273 89.0 36.0 0.08
+55 26496 273 102.0 39.0 0.11
+55 36582 273 125.0 13.0 0.2
+55 55011 273 138.0 49.0 0.26
+73 10727 272 17.0 49.0 0.02
+73 28326 272 53.0 19.0 0.11
+73 30406 272 92.0 36.0 0.15
+73 37185 272 128.0 12.0 0.22
+74 455 273 44.0 0.0 0.05
+74 14661 273 76.0 14.0 0.05
+74 20037 273 118.0 32.0 0.14
+74 22113 273 140.0 42.0 0.19
+74 43782 273 186.0 23.0 0.24
+74 45319 273 202.0 46.0 0.24
+74 53056 273 251.0 16.0 0.3
+100 20135 272 24.0 29.0 0.0
+100 21056 272 57.0 24.0 0.02
+100 30822 272 107.0 39.0 0.09
+100 31911 272 110.0 50.0 0.11
+100 33600 272 142.0 3.0 0.16
+100 37063 272 154.0 32.0 0.22
+100 47365 272 156.0 5.0 0.26
+100 50304 272 204.0 2.0 0.33
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/window_functions.out
b/regression-test/data/query_p0/sql_functions/window_functions/window_functions.out
new file mode 100644
index 0000000000..3924defbbc
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/window_functions.out
@@ -0,0 +1,112 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !window_functions --
+
+-- !window_functions --
+
+-- !window_functions --
+0 0
+14 0
+15 0
+16 0
+5 0
+1 1
+3 1
+17 1
+2 1
+24 1
+9 9
+18 9
+21 9
+12 9
+8 8
+22 22
+23 22
+7 7
+6 6
+19 6
+10 10
+13 10
+11 10
+20 10
+4 4
+
+-- !window_functions --
+0 0
+14 0
+15 0
+16 0
+5 0
+1 1
+3 1
+17 1
+2 1
+24 1
+9 8
+18 8
+21 8
+12 8
+8 8
+22 6
+23 6
+7 6
+6 6
+19 6
+10 4
+13 4
+11 4
+20 4
+4 4
+
+-- !window_functions --
+
+-- !window_functions --
+
+-- !window_functions --
+
+-- !window_functions --
+
+-- !window_functions --
+
+-- !window_functions --
+
+-- !window_functions --
+
+-- !window_functions --
+
+-- !window_functions --
+
+-- !window_functions --
+
+-- !window_functions --
+0 0
+14 0
+15 0
+16 0
+5 0
+1 1
+3 1
+17 1
+2 1
+24 1
+9 8
+18 8
+21 8
+12 8
+8 8
+22 6
+23 6
+7 6
+6 6
+19 6
+10 4
+13 4
+11 4
+20 4
+4 4
+
+-- !window_functions --
+
+-- !window_functions --
+
+-- !window_functions --
+
diff --git a/regression-test/data/types/complex_types/basic_agg_test.out
b/regression-test/data/types/complex_types/basic_agg_test.out
new file mode 100644
index 0000000000..767a1dc038
--- /dev/null
+++ b/regression-test/data/types/complex_types/basic_agg_test.out
@@ -0,0 +1,16 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql_bitmap --
+1 \N
+2 \N
+3 \N
+
+-- !sql_hll --
+1 \N
+2 \N
+3 \N
+
+-- !sql_hll_cardinality --
+1 0
+2 1
+3 2
+
diff --git
a/regression-test/data/with_clause_p0/sql/multipleWithExprsReferencedFromParent_order.out
b/regression-test/data/with_clause_p0/sql/multipleWithExprsReferencedFromParent_order.out
new file mode 100644
index 0000000000..4a27ccdabf
--- /dev/null
+++
b/regression-test/data/with_clause_p0/sql/multipleWithExprsReferencedFromParent_order.out
@@ -0,0 +1,8 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !multipleWithExprsReferencedFromParent_order --
+0 0
+14 0
+15 0
+16 0
+5 0
+
diff --git
a/regression-test/data/with_clause_p0/sql/withExprAccessingAnotherAndAlsoFromParents.out
b/regression-test/data/with_clause_p0/sql/withExprAccessingAnotherAndAlsoFromParents.out
new file mode 100644
index 0000000000..7ee5a5d72e
--- /dev/null
+++
b/regression-test/data/with_clause_p0/sql/withExprAccessingAnotherAndAlsoFromParents.out
@@ -0,0 +1,4 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !withExprAccessingAnotherAndAlsoFromParents --
+625
+
diff --git
a/regression-test/data/with_clause_p0/sql/withExprReferencedInWhereClause.out
b/regression-test/data/with_clause_p0/sql/withExprReferencedInWhereClause.out
new file mode 100644
index 0000000000..58d17bf25a
--- /dev/null
+++
b/regression-test/data/with_clause_p0/sql/withExprReferencedInWhereClause.out
@@ -0,0 +1,4 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !withExprReferencedInWhereClause --
+1 AMERICA
+
diff --git a/regression-test/data/with_clause_p0/sql/withExprRepeated_order.out
b/regression-test/data/with_clause_p0/sql/withExprRepeated_order.out
new file mode 100644
index 0000000000..eb773d413f
--- /dev/null
+++ b/regression-test/data/with_clause_p0/sql/withExprRepeated_order.out
@@ -0,0 +1,28 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !withExprRepeated_order --
+ALGERIA ALGERIA
+ALGERIA ETHIOPIA
+ALGERIA KENYA
+ALGERIA MOROCCO
+ALGERIA MOZAMBIQUE
+ARGENTINA ARGENTINA
+ARGENTINA BRAZIL
+ARGENTINA CANADA
+ARGENTINA PERU
+ARGENTINA UNITED STATES
+BRAZIL CHINA
+BRAZIL INDIA
+BRAZIL INDONESIA
+BRAZIL JAPAN
+BRAZIL VIETNAM
+CANADA FRANCE
+CANADA GERMANY
+CANADA ROMANIA
+CANADA RUSSIA
+CANADA UNITED KINGDOM
+EGYPT EGYPT
+EGYPT IRAN
+EGYPT IRAQ
+EGYPT JORDAN
+EGYPT SAUDI ARABIA
+
diff --git
a/regression-test/data/with_clause_p0/sql/withExprUsingAnotherWithExpr.out
b/regression-test/data/with_clause_p0/sql/withExprUsingAnotherWithExpr.out
new file mode 100644
index 0000000000..828f2bfcfe
--- /dev/null
+++ b/regression-test/data/with_clause_p0/sql/withExprUsingAnotherWithExpr.out
@@ -0,0 +1,9 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !withExprUsingAnotherWithExpr --
+5 0
+5 1
+5 2
+5 3
+0 4
+5 4
+
diff --git
a/regression-test/data/with_clause_p0/sql/withExprUsingOrderbyLimit.out
b/regression-test/data/with_clause_p0/sql/withExprUsingOrderbyLimit.out
new file mode 100644
index 0000000000..fe00e3537e
--- /dev/null
+++ b/regression-test/data/with_clause_p0/sql/withExprUsingOrderbyLimit.out
@@ -0,0 +1,8 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !withExprUsingOrderbyLimit --
+0 0 ALGERIA
+1 1 ARGENTINA
+2 1 BRAZIL
+3 1 CANADA
+4 4 EGYPT
+
diff --git
a/regression-test/data/with_clause_p0/sql/withExprWithNoReferenceInParent.out
b/regression-test/data/with_clause_p0/sql/withExprWithNoReferenceInParent.out
new file mode 100644
index 0000000000..3641c4ca80
--- /dev/null
+++
b/regression-test/data/with_clause_p0/sql/withExprWithNoReferenceInParent.out
@@ -0,0 +1,4 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !withExprWithNoReferenceInParent --
+ALGERIA
+
diff --git
a/regression-test/data/with_clause_p0/sql/withSimpleExprInNestedFromClause.out
b/regression-test/data/with_clause_p0/sql/withSimpleExprInNestedFromClause.out
new file mode 100644
index 0000000000..9a35974394
--- /dev/null
+++
b/regression-test/data/with_clause_p0/sql/withSimpleExprInNestedFromClause.out
@@ -0,0 +1,4 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !withSimpleExprInNestedFromClause --
+25
+
diff --git
a/regression-test/framework/src/main/groovy/org/apache/doris/regression/Config.groovy
b/regression-test/framework/src/main/groovy/org/apache/doris/regression/Config.groovy
index 42b3b561d6..56f2c24106 100644
---
a/regression-test/framework/src/main/groovy/org/apache/doris/regression/Config.groovy
+++
b/regression-test/framework/src/main/groovy/org/apache/doris/regression/Config.groovy
@@ -418,8 +418,8 @@ class Config {
// e.g.
// suites/tpcds_sf1/load.groovy
// suites/tpcds_sf1/sql/q01.sql
- if (dir.indexOf(File.separator + "sql") > 0 && dir.endsWith("sql")) {
- dir = dir.substring(0, dir.indexOf(File.separator + "sql"))
+ if (dir.indexOf(File.separator + "sql", dir.length() - 4) > 0 &&
dir.endsWith("sql")) {
+ dir = dir.substring(0, dir.indexOf(File.separator + "sql",
dir.length() - 4))
}
dir = dir.replace('-', '_')
diff --git a/regression-test/suites/query_p0/set_operations/ddl/lineitem.sql
b/regression-test/suites/query_p0/set_operations/ddl/lineitem.sql
new file mode 100644
index 0000000000..530befceb1
--- /dev/null
+++ b/regression-test/suites/query_p0/set_operations/ddl/lineitem.sql
@@ -0,0 +1,18 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_lineitem (
+ orderkey bigint,
+ partkey bigint,
+ suppkey bigint,
+ linenumber integer,
+ quantity double,
+ extendedprice double,
+ discount double,
+ tax double,
+ returnflag varchar(1),
+ linestatus varchar(1),
+ shipdate date,
+ commitdate date,
+ receiptdate date,
+ shipinstruct varchar(25),
+ shipmode varchar(10),
+ comment varchar(44)
+) DUPLICATE KEY(orderkey) DISTRIBUTED BY HASH(orderkey) BUCKETS 3 PROPERTIES
("replication_num" = "1")
\ No newline at end of file
diff --git a/regression-test/suites/query_p0/set_operations/ddl/nation.sql
b/regression-test/suites/query_p0/set_operations/ddl/nation.sql
new file mode 100644
index 0000000000..074618815f
--- /dev/null
+++ b/regression-test/suites/query_p0/set_operations/ddl/nation.sql
@@ -0,0 +1,12 @@
+
+CREATE TABLE IF NOT EXISTS tpch_tiny_nation (
+ nationkey BIGINT NOT NULL,
+ name VARCHAR(25) NOT NULL,
+ regionkey BIGINT NOT NULL,
+ comment VARCHAR(152)
+)
+DUPLICATE KEY(nationkey)
+DISTRIBUTED BY HASH(nationkey) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+)
\ No newline at end of file
diff --git a/regression-test/suites/query_p0/set_operations/ddl/part.sql
b/regression-test/suites/query_p0/set_operations/ddl/part.sql
new file mode 100644
index 0000000000..f8d952c9e8
--- /dev/null
+++ b/regression-test/suites/query_p0/set_operations/ddl/part.sql
@@ -0,0 +1,11 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_part (
+ p_partkey INTEGER NOT NULL,
+ p_name VARCHAR(55) NOT NULL,
+ p_mfgr CHAR(25) NOT NULL,
+ p_brand CHAR(10) NOT NULL,
+ p_type VARCHAR(25) NOT NULL,
+ p_size INTEGER NOT NULL,
+ p_container CHAR(10) NOT NULL,
+ p_retailprice DECIMAL(15, 2) NOT NULL,
+ p_comment VARCHAR(23) NOT NULL
+) DUPLICATE KEY(p_partkey) DISTRIBUTED BY HASH(p_partkey) BUCKETS 3 PROPERTIES
("replication_num" = "1")
\ No newline at end of file
diff --git a/regression-test/suites/query_p0/set_operations/ddl/partsupp.sql
b/regression-test/suites/query_p0/set_operations/ddl/partsupp.sql
new file mode 100644
index 0000000000..43a0af0575
--- /dev/null
+++ b/regression-test/suites/query_p0/set_operations/ddl/partsupp.sql
@@ -0,0 +1,7 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_partsupp (
+ partkey bigint,
+ suppkey bigint,
+ availqty integer,
+ supplycost double,
+ comment varchar(199)
+) DUPLICATE KEY(partkey, suppkey) DISTRIBUTED BY HASH(partkey) BUCKETS 3
PROPERTIES ("replication_num" = "1")
diff --git a/regression-test/suites/query_p0/set_operations/ddl/region.sql
b/regression-test/suites/query_p0/set_operations/ddl/region.sql
new file mode 100644
index 0000000000..36e282bb70
--- /dev/null
+++ b/regression-test/suites/query_p0/set_operations/ddl/region.sql
@@ -0,0 +1,10 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_region (
+ r_regionkey INTEGER NOT NULL,
+ r_name CHAR(25) NOT NULL,
+ r_comment VARCHAR(152)
+)
+DUPLICATE KEY(r_regionkey)
+DISTRIBUTED BY HASH(r_regionkey) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+)
\ No newline at end of file
diff --git a/regression-test/suites/query_p0/set_operations/ddl/supplier.sql
b/regression-test/suites/query_p0/set_operations/ddl/supplier.sql
new file mode 100644
index 0000000000..ff594b41a1
--- /dev/null
+++ b/regression-test/suites/query_p0/set_operations/ddl/supplier.sql
@@ -0,0 +1,9 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_supplier (
+ s_suppkey INTEGER NOT NULL,
+ s_name CHAR(25) NOT NULL,
+ s_address VARCHAR(40) NOT NULL,
+ s_nationkey INTEGER NOT NULL,
+ s_phone CHAR(15) NOT NULL,
+ s_acctbal DECIMAL(15, 2) NOT NULL,
+ s_comment VARCHAR(101) NOT NULL
+) DUPLICATE KEY(s_suppkey) DISTRIBUTED BY HASH(s_suppkey) BUCKETS 3 PROPERTIES
("replication_num" = "1")
\ No newline at end of file
diff --git a/regression-test/suites/query_p0/set_operations/ddl/workers.sql
b/regression-test/suites/query_p0/set_operations/ddl/workers.sql
new file mode 100644
index 0000000000..1ba2c331bc
--- /dev/null
+++ b/regression-test/suites/query_p0/set_operations/ddl/workers.sql
@@ -0,0 +1,10 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_workers (
+ id_employee INT,
+ first_name VARCHAR(32),
+ last_name VARCHAR(32),
+ date_of_employment DATE,
+ department TINYINT(1),
+ id_department INT,
+ name VARCHAR(32),
+ salary INT
+) DUPLICATE KEY(id_employee) DISTRIBUTED BY HASH(first_name) BUCKETS 3
PROPERTIES ("replication_num" = "1")
diff --git a/regression-test/suites/query_p0/set_operations/load.groovy
b/regression-test/suites/query_p0/set_operations/load.groovy
new file mode 100644
index 0000000000..0086ed87a5
--- /dev/null
+++ b/regression-test/suites/query_p0/set_operations/load.groovy
@@ -0,0 +1,62 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+// The cases is copied from https://github.com/trinodb/trino/tree/master
+//
/testing/trino-product-tests/src/main/resources/sql-tests/testcases/window_functions
+// and modified by Doris.
+suite("load") {
+ def tables = ['lineitem', 'region', 'nation', 'part', 'supplier',
'partsupp', 'workers']
+
+ for (String table in tables) {
+ sql """ DROP TABLE IF EXISTS tpch_tiny_${table} """
+ }
+
+ for (String table in tables) {
+ sql new File("""${context.file.parent}/ddl/${table}.sql""").text
+ }
+
+ for (String tableName in tables) {
+ streamLoad {
+ // you can skip declare db, because a default db already specify
in ${DORIS_HOME}/conf/regression-conf.groovy
+ // db 'regression_test'
+ table "tpch_tiny_${tableName}"
+
+ // default column_separator is specify in doris fe config, usually
is '\t'.
+ // this line change to ','
+ set 'column_separator', '|'
+ set 'compress_type', 'GZ'
+ // relate to
${DORIS_HOME}/regression-test/data/demo/streamload_input.csv.
+ // also, you can stream load a http stream, e.g.
http://xxx/some.csv
+ file """${getS3Url() + '/regression/tpch/sf0.01/' +
tableName}.csv.gz"""
+
+ time 10000 // limit inflight 10s
+
+ // if declared a check callback, the default check condition will
ignore.
+ // So you must check all condition
+ check { result, exception, startTime, endTime ->
+ if (exception != null) {
+ throw exception
+ }
+ log.info("Stream load result: ${result}".toString())
+ def json = parseJson(result)
+ assertEquals('success', json.Status.toLowerCase())
+ assertEquals(json.NumberTotalRows, json.NumberLoadedRows)
+ assertTrue(json.NumberLoadedRows > 0 && json.LoadBytes > 0)
+ }
+ }
+ }
+}
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/set_operations/sql/except_1_order.sql
b/regression-test/suites/query_p0/set_operations/sql/except_1_order.sql
new file mode 100644
index 0000000000..d3ebdd9c58
--- /dev/null
+++ b/regression-test/suites/query_p0/set_operations/sql/except_1_order.sql
@@ -0,0 +1,10 @@
+-- database: presto; tables: nation, workers; groups: set_operation;
+-- delimiter: |; ignoreOrder: true;
+--! name: except_uniointersect
+SELECT name FROM tpch_tiny_nation WHERE nationkey = 17
+EXCEPT
+SELECT name FROM tpch_tiny_nation WHERE regionkey = 2
+UNION
+(SELECT name FROM tpch_tiny_nation WHERE regionkey = 2
+INTERSECT
+SELECT name FROM tpch_tiny_nation WHERE nationkey > 15)
diff --git
a/regression-test/suites/query_p0/set_operations/sql/except_2_order.sql
b/regression-test/suites/query_p0/set_operations/sql/except_2_order.sql
new file mode 100644
index 0000000000..952ff1c491
--- /dev/null
+++ b/regression-test/suites/query_p0/set_operations/sql/except_2_order.sql
@@ -0,0 +1,10 @@
+-- database: presto; tables: nation, workers; groups: set_operation;
+-- delimiter: |; ignoreOrder: true;
+--! name: except_uniointersect
+SELECT name FROM tpch_tiny_nation WHERE nationkey = 17
+EXCEPT
+SELECT name FROM tpch_tiny_nation WHERE regionkey = 2
+UNION ALL
+(SELECT name FROM tpch_tiny_nation WHERE regionkey = 2
+INTERSECT
+SELECT name FROM tpch_tiny_nation WHERE nationkey > 15)
diff --git
a/regression-test/suites/query_p0/set_operations/sql/except_3_order.sql
b/regression-test/suites/query_p0/set_operations/sql/except_3_order.sql
new file mode 100644
index 0000000000..94e3646fdf
--- /dev/null
+++ b/regression-test/suites/query_p0/set_operations/sql/except_3_order.sql
@@ -0,0 +1,3 @@
+SELECT id_employee FROM tpch_tiny_workers
+EXCEPT
+SELECT department FROM tpch_tiny_workers where department IS NOT NULL
diff --git
a/regression-test/suites/query_p0/set_operations/sql/intersect_1_order.sql
b/regression-test/suites/query_p0/set_operations/sql/intersect_1_order.sql
new file mode 100644
index 0000000000..f2c060dba2
--- /dev/null
+++ b/regression-test/suites/query_p0/set_operations/sql/intersect_1_order.sql
@@ -0,0 +1,8 @@
+-- database: presto; tables: nation, workers; groups: set_operation;
+-- delimiter: |; ignoreOrder: true;
+--! name: intersect_and_union
+SELECT name FROM tpch_tiny_nation WHERE nationkey = 17
+INTERSECT
+SELECT name FROM tpch_tiny_nation WHERE regionkey = 1
+UNION
+SELECT name FROM tpch_tiny_nation WHERE regionkey = 2
diff --git
a/regression-test/suites/query_p0/set_operations/sql/intersect_2_order.sql
b/regression-test/suites/query_p0/set_operations/sql/intersect_2_order.sql
new file mode 100644
index 0000000000..0643c0af08
--- /dev/null
+++ b/regression-test/suites/query_p0/set_operations/sql/intersect_2_order.sql
@@ -0,0 +1,8 @@
+-- database: presto; tables: nation, workers; groups: set_operation;
+-- delimiter: |; ignoreOrder: true;
+--! name: intersect_and_union
+SELECT id_employee
+FROM tpch_tiny_workers
+INTERSECT
+SELECT department
+FROM tpch_tiny_workers
diff --git
a/regression-test/suites/query_p0/sql_functions/string_functions/ddl/lineitem.sql
b/regression-test/suites/query_p0/sql_functions/string_functions/ddl/lineitem.sql
new file mode 100644
index 0000000000..530befceb1
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/string_functions/ddl/lineitem.sql
@@ -0,0 +1,18 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_lineitem (
+ orderkey bigint,
+ partkey bigint,
+ suppkey bigint,
+ linenumber integer,
+ quantity double,
+ extendedprice double,
+ discount double,
+ tax double,
+ returnflag varchar(1),
+ linestatus varchar(1),
+ shipdate date,
+ commitdate date,
+ receiptdate date,
+ shipinstruct varchar(25),
+ shipmode varchar(10),
+ comment varchar(44)
+) DUPLICATE KEY(orderkey) DISTRIBUTED BY HASH(orderkey) BUCKETS 3 PROPERTIES
("replication_num" = "1")
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/string_functions/ddl/nation.sql
b/regression-test/suites/query_p0/sql_functions/string_functions/ddl/nation.sql
new file mode 100644
index 0000000000..074618815f
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/string_functions/ddl/nation.sql
@@ -0,0 +1,12 @@
+
+CREATE TABLE IF NOT EXISTS tpch_tiny_nation (
+ nationkey BIGINT NOT NULL,
+ name VARCHAR(25) NOT NULL,
+ regionkey BIGINT NOT NULL,
+ comment VARCHAR(152)
+)
+DUPLICATE KEY(nationkey)
+DISTRIBUTED BY HASH(nationkey) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+)
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/string_functions/ddl/part.sql
b/regression-test/suites/query_p0/sql_functions/string_functions/ddl/part.sql
new file mode 100644
index 0000000000..f8d952c9e8
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/string_functions/ddl/part.sql
@@ -0,0 +1,11 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_part (
+ p_partkey INTEGER NOT NULL,
+ p_name VARCHAR(55) NOT NULL,
+ p_mfgr CHAR(25) NOT NULL,
+ p_brand CHAR(10) NOT NULL,
+ p_type VARCHAR(25) NOT NULL,
+ p_size INTEGER NOT NULL,
+ p_container CHAR(10) NOT NULL,
+ p_retailprice DECIMAL(15, 2) NOT NULL,
+ p_comment VARCHAR(23) NOT NULL
+) DUPLICATE KEY(p_partkey) DISTRIBUTED BY HASH(p_partkey) BUCKETS 3 PROPERTIES
("replication_num" = "1")
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/string_functions/ddl/partsupp.sql
b/regression-test/suites/query_p0/sql_functions/string_functions/ddl/partsupp.sql
new file mode 100644
index 0000000000..43a0af0575
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/string_functions/ddl/partsupp.sql
@@ -0,0 +1,7 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_partsupp (
+ partkey bigint,
+ suppkey bigint,
+ availqty integer,
+ supplycost double,
+ comment varchar(199)
+) DUPLICATE KEY(partkey, suppkey) DISTRIBUTED BY HASH(partkey) BUCKETS 3
PROPERTIES ("replication_num" = "1")
diff --git
a/regression-test/suites/query_p0/sql_functions/string_functions/ddl/region.sql
b/regression-test/suites/query_p0/sql_functions/string_functions/ddl/region.sql
new file mode 100644
index 0000000000..36e282bb70
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/string_functions/ddl/region.sql
@@ -0,0 +1,10 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_region (
+ r_regionkey INTEGER NOT NULL,
+ r_name CHAR(25) NOT NULL,
+ r_comment VARCHAR(152)
+)
+DUPLICATE KEY(r_regionkey)
+DISTRIBUTED BY HASH(r_regionkey) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+)
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/string_functions/ddl/supplier.sql
b/regression-test/suites/query_p0/sql_functions/string_functions/ddl/supplier.sql
new file mode 100644
index 0000000000..ff594b41a1
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/string_functions/ddl/supplier.sql
@@ -0,0 +1,9 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_supplier (
+ s_suppkey INTEGER NOT NULL,
+ s_name CHAR(25) NOT NULL,
+ s_address VARCHAR(40) NOT NULL,
+ s_nationkey INTEGER NOT NULL,
+ s_phone CHAR(15) NOT NULL,
+ s_acctbal DECIMAL(15, 2) NOT NULL,
+ s_comment VARCHAR(101) NOT NULL
+) DUPLICATE KEY(s_suppkey) DISTRIBUTED BY HASH(s_suppkey) BUCKETS 3 PROPERTIES
("replication_num" = "1")
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/string_functions/ddl/workers.sql
b/regression-test/suites/query_p0/sql_functions/string_functions/ddl/workers.sql
new file mode 100644
index 0000000000..1ba2c331bc
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/string_functions/ddl/workers.sql
@@ -0,0 +1,10 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_workers (
+ id_employee INT,
+ first_name VARCHAR(32),
+ last_name VARCHAR(32),
+ date_of_employment DATE,
+ department TINYINT(1),
+ id_department INT,
+ name VARCHAR(32),
+ salary INT
+) DUPLICATE KEY(id_employee) DISTRIBUTED BY HASH(first_name) BUCKETS 3
PROPERTIES ("replication_num" = "1")
diff --git
a/regression-test/suites/query_p0/sql_functions/string_functions/load.groovy
b/regression-test/suites/query_p0/sql_functions/string_functions/load.groovy
new file mode 100644
index 0000000000..0086ed87a5
--- /dev/null
+++ b/regression-test/suites/query_p0/sql_functions/string_functions/load.groovy
@@ -0,0 +1,62 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+// The cases is copied from https://github.com/trinodb/trino/tree/master
+//
/testing/trino-product-tests/src/main/resources/sql-tests/testcases/window_functions
+// and modified by Doris.
+suite("load") {
+ def tables = ['lineitem', 'region', 'nation', 'part', 'supplier',
'partsupp', 'workers']
+
+ for (String table in tables) {
+ sql """ DROP TABLE IF EXISTS tpch_tiny_${table} """
+ }
+
+ for (String table in tables) {
+ sql new File("""${context.file.parent}/ddl/${table}.sql""").text
+ }
+
+ for (String tableName in tables) {
+ streamLoad {
+ // you can skip declare db, because a default db already specify
in ${DORIS_HOME}/conf/regression-conf.groovy
+ // db 'regression_test'
+ table "tpch_tiny_${tableName}"
+
+ // default column_separator is specify in doris fe config, usually
is '\t'.
+ // this line change to ','
+ set 'column_separator', '|'
+ set 'compress_type', 'GZ'
+ // relate to
${DORIS_HOME}/regression-test/data/demo/streamload_input.csv.
+ // also, you can stream load a http stream, e.g.
http://xxx/some.csv
+ file """${getS3Url() + '/regression/tpch/sf0.01/' +
tableName}.csv.gz"""
+
+ time 10000 // limit inflight 10s
+
+ // if declared a check callback, the default check condition will
ignore.
+ // So you must check all condition
+ check { result, exception, startTime, endTime ->
+ if (exception != null) {
+ throw exception
+ }
+ log.info("Stream load result: ${result}".toString())
+ def json = parseJson(result)
+ assertEquals('success', json.Status.toLowerCase())
+ assertEquals(json.NumberTotalRows, json.NumberLoadedRows)
+ assertTrue(json.NumberLoadedRows > 0 && json.LoadBytes > 0)
+ }
+ }
+ }
+}
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/string_functions/sql/likeOperatorWorks_order.sql
b/regression-test/suites/query_p0/sql_functions/string_functions/sql/likeOperatorWorks_order.sql
new file mode 100644
index 0000000000..cfb44c8240
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/string_functions/sql/likeOperatorWorks_order.sql
@@ -0,0 +1 @@
+select name from tpch_tiny_nation where name like '%AN'
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/ddl/lineitem.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/ddl/lineitem.sql
new file mode 100644
index 0000000000..530befceb1
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/ddl/lineitem.sql
@@ -0,0 +1,18 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_lineitem (
+ orderkey bigint,
+ partkey bigint,
+ suppkey bigint,
+ linenumber integer,
+ quantity double,
+ extendedprice double,
+ discount double,
+ tax double,
+ returnflag varchar(1),
+ linestatus varchar(1),
+ shipdate date,
+ commitdate date,
+ receiptdate date,
+ shipinstruct varchar(25),
+ shipmode varchar(10),
+ comment varchar(44)
+) DUPLICATE KEY(orderkey) DISTRIBUTED BY HASH(orderkey) BUCKETS 3 PROPERTIES
("replication_num" = "1")
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/ddl/nation.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/ddl/nation.sql
new file mode 100644
index 0000000000..074618815f
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/ddl/nation.sql
@@ -0,0 +1,12 @@
+
+CREATE TABLE IF NOT EXISTS tpch_tiny_nation (
+ nationkey BIGINT NOT NULL,
+ name VARCHAR(25) NOT NULL,
+ regionkey BIGINT NOT NULL,
+ comment VARCHAR(152)
+)
+DUPLICATE KEY(nationkey)
+DISTRIBUTED BY HASH(nationkey) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+)
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/ddl/part.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/ddl/part.sql
new file mode 100644
index 0000000000..f8d952c9e8
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/ddl/part.sql
@@ -0,0 +1,11 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_part (
+ p_partkey INTEGER NOT NULL,
+ p_name VARCHAR(55) NOT NULL,
+ p_mfgr CHAR(25) NOT NULL,
+ p_brand CHAR(10) NOT NULL,
+ p_type VARCHAR(25) NOT NULL,
+ p_size INTEGER NOT NULL,
+ p_container CHAR(10) NOT NULL,
+ p_retailprice DECIMAL(15, 2) NOT NULL,
+ p_comment VARCHAR(23) NOT NULL
+) DUPLICATE KEY(p_partkey) DISTRIBUTED BY HASH(p_partkey) BUCKETS 3 PROPERTIES
("replication_num" = "1")
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/ddl/partsupp.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/ddl/partsupp.sql
new file mode 100644
index 0000000000..43a0af0575
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/ddl/partsupp.sql
@@ -0,0 +1,7 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_partsupp (
+ partkey bigint,
+ suppkey bigint,
+ availqty integer,
+ supplycost double,
+ comment varchar(199)
+) DUPLICATE KEY(partkey, suppkey) DISTRIBUTED BY HASH(partkey) BUCKETS 3
PROPERTIES ("replication_num" = "1")
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/ddl/region.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/ddl/region.sql
new file mode 100644
index 0000000000..36e282bb70
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/ddl/region.sql
@@ -0,0 +1,10 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_region (
+ r_regionkey INTEGER NOT NULL,
+ r_name CHAR(25) NOT NULL,
+ r_comment VARCHAR(152)
+)
+DUPLICATE KEY(r_regionkey)
+DISTRIBUTED BY HASH(r_regionkey) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+)
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/ddl/supplier.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/ddl/supplier.sql
new file mode 100644
index 0000000000..ff594b41a1
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/ddl/supplier.sql
@@ -0,0 +1,9 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_supplier (
+ s_suppkey INTEGER NOT NULL,
+ s_name CHAR(25) NOT NULL,
+ s_address VARCHAR(40) NOT NULL,
+ s_nationkey INTEGER NOT NULL,
+ s_phone CHAR(15) NOT NULL,
+ s_acctbal DECIMAL(15, 2) NOT NULL,
+ s_comment VARCHAR(101) NOT NULL
+) DUPLICATE KEY(s_suppkey) DISTRIBUTED BY HASH(s_suppkey) BUCKETS 3 PROPERTIES
("replication_num" = "1")
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/ddl/workers.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/ddl/workers.sql
new file mode 100644
index 0000000000..1ba2c331bc
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/ddl/workers.sql
@@ -0,0 +1,10 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_workers (
+ id_employee INT,
+ first_name VARCHAR(32),
+ last_name VARCHAR(32),
+ date_of_employment DATE,
+ department TINYINT(1),
+ id_department INT,
+ name VARCHAR(32),
+ salary INT
+) DUPLICATE KEY(id_employee) DISTRIBUTED BY HASH(first_name) BUCKETS 3
PROPERTIES ("replication_num" = "1")
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/load.groovy
b/regression-test/suites/query_p0/sql_functions/window_functions/load.groovy
new file mode 100644
index 0000000000..4aa82c49ad
--- /dev/null
+++ b/regression-test/suites/query_p0/sql_functions/window_functions/load.groovy
@@ -0,0 +1,64 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+// The cases is copied from https://github.com/trinodb/trino/tree/master
+//
/testing/trino-product-tests/src/main/resources/sql-tests/testcases/window_functions
+// and modified by Doris.
+
+suite("load") {
+ def tables = ['lineitem', 'region', 'nation', 'part', 'supplier',
'partsupp', 'workers']
+
+ for (String table in tables) {
+ sql """ DROP TABLE IF EXISTS tpch_tiny_${table} """
+ }
+
+ for (String table in tables) {
+ sql new File("""${context.file.parent}/ddl/${table}.sql""").text
+ }
+
+ for (String table_name in tables) {
+ streamLoad {
+ // you can skip declare db, because a default db already specify in
${DORIS_HOME}/conf/regression-conf.groovy
+ // db 'regression_test'
+ table "tpch_tiny_${table_name}"
+
+ // default column_separator is specify in doris fe config, usually is
'\t'.
+ // this line change to ','
+ set 'column_separator', '|'
+ set 'compress_type', 'GZ'
+ // relate to
${DORIS_HOME}/regression-test/data/demo/streamload_input.csv.
+ // also, you can stream load a http stream, e.g. http://xxx/some.csv
+ file """${getS3Url()+'/regression/tpch/sf0.01/'+table_name}.csv.gz"""
+
+ time 10000 // limit inflight 10s
+
+ // if declared a check callback, the default check condition will ignore.
+ // So you must check all condition
+ check { result, exception, startTime, endTime ->
+ if (exception != null) {
+ throw exception
+ }
+ log.info("Stream load result: ${result}".toString())
+ def json = parseJson(result)
+ assertEquals('success', json.Status.toLowerCase())
+ assertEquals(json.NumberTotalRows, json.NumberLoadedRows)
+ assertTrue(json.NumberLoadedRows > 0 && json.LoadBytes > 0)
+ }
+ }
+ }
+ sql 'sync'
+}
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/leadLag.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/leadLag.sql
new file mode 100644
index 0000000000..072e2a2492
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/leadLag.sql
@@ -0,0 +1,11 @@
+select *
+from
+ (select orderkey, suppkey,
+ discount,
+ lead(discount, 1, null) over (partition by suppkey order by orderkey
desc) next_discount,
+ extendedprice,
+ lag(extendedprice, 1, null) over (partition by discount order by
extendedprice) previous_extendedprice
+ from tpch_tiny_lineitem
+ where partkey = 272)
+ as T
+order by orderkey
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/noOrderAllRowsPeers_order.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/noOrderAllRowsPeers_order.sql
new file mode 100644
index 0000000000..3cecb64805
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/noOrderAllRowsPeers_order.sql
@@ -0,0 +1,3 @@
+select orderkey, suppkey, discount,
+rank() over (partition by suppkey)
+from tpch_tiny_lineitem where partkey = 272
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/rangeCurrent_order.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rangeCurrent_order.sql
new file mode 100644
index 0000000000..b91b88e8d6
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rangeCurrent_order.sql
@@ -0,0 +1 @@
+SELECT nationkey, min(nationkey) OVER (PARTITION BY regionkey ORDER BY comment
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS min FROM tpch_tiny_nation
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/rangeNoPartition_order.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rangeNoPartition_order.sql
new file mode 100644
index 0000000000..6bf8132a33
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rangeNoPartition_order.sql
@@ -0,0 +1,4 @@
+ select orderkey, discount, extendedprice,
+ min(extendedprice) over (partition by discount order by discount)
min_extendedprice,
+ max(extendedprice) over (partition by discount order by discount)
max_extendedprice
+ from tpch_tiny_lineitem as T where partkey = 272
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/rangeUnbounded_order.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rangeUnbounded_order.sql
new file mode 100644
index 0000000000..0aea743974
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rangeUnbounded_order.sql
@@ -0,0 +1 @@
+SELECT nationkey, min(nationkey) OVER (PARTITION BY regionkey ORDER BY comment
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min FROM
tpch_tiny_nation
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsDifferentOrders.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsDifferentOrders.sql
new file mode 100644
index 0000000000..53d2ae5469
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsDifferentOrders.sql
@@ -0,0 +1,10 @@
+select *
+from (
+ select orderkey, suppkey,
+ extendedprice,
+ round(sum(extendedprice) over (partition by suppkey order by orderkey desc
rows between unbounded preceding and current row), 5) total_extendedprice,
+ discount,
+ round(avg(discount) over (partition by suppkey order by orderkey asc rows
between unbounded preceding and current row), 5) avg_discount
+ from tpch_tiny_lineitem where partkey = 272
+) as T
+order by suppkey, orderkey
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsDisjointWindows.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsDisjointWindows.sql
new file mode 100644
index 0000000000..2a629f1559
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsDisjointWindows.sql
@@ -0,0 +1,9 @@
+select orderkey, suppkey,
+quantity,
+round(sum(quantity) over (partition by suppkey order by orderkey rows between
unbounded preceding and 1 preceding), 5) total_quantity,
+extendedprice,
+round(sum(extendedprice) over (partition by suppkey order by orderkey rows
between current row and 1 following), 5)
+total_extendedprice,
+discount,
+round(avg(discount) over (partition by suppkey order by orderkey rows between
3 following and unbounded following), 5) avg_discount
+from tpch_tiny_lineitem where partkey = 272
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFirstLast.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFirstLast.sql
new file mode 100644
index 0000000000..01be666056
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFirstLast.sql
@@ -0,0 +1,8 @@
+select *
+from (
+ select orderkey, suppkey, extendedprice,
+first_value(extendedprice) over (partition by suppkey order by extendedprice
desc rows between unbounded preceding and unbounded following),
+last_value(extendedprice) over (partition by suppkey order by extendedprice
desc rows between unbounded preceding and unbounded following)
+from tpch_tiny_lineitem where partkey = 272
+) as T
+order by suppkey, orderkey
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFrame2F3F.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFrame2F3F.sql
new file mode 100644
index 0000000000..cee1484864
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFrame2F3F.sql
@@ -0,0 +1,7 @@
+select *
+from (
+ select orderkey, suppkey, quantity,
+ round(sum(quantity) over (partition by suppkey order by orderkey rows
between 2 following and 3 following), 5) total_quantity
+ from tpch_tiny_lineitem where partkey = 272
+) as t
+order by suppkey, orderkey
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFrame2FUBF.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFrame2FUBF.sql
new file mode 100644
index 0000000000..a2e2c88fc7
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFrame2FUBF.sql
@@ -0,0 +1,3 @@
+select orderkey, suppkey, quantity,
+round(sum(quantity) over (partition by suppkey order by orderkey rows between
2 following and unbounded following), 5) total_quantity
+from tpch_tiny_lineitem where partkey = 272 order by suppkey, orderkey
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFrame3P2P.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFrame3P2P.sql
new file mode 100644
index 0000000000..9b36a588f9
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFrame3P2P.sql
@@ -0,0 +1,7 @@
+select *
+from (
+ select orderkey, suppkey, quantity,
+ round(sum(quantity) over (partition by suppkey order by orderkey rows
between 3 preceding and 2 preceding), 5) total_quantity
+ from tpch_tiny_lineitem where partkey = 272
+)as T
+order by suppkey, orderkey
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFrameUBP2P.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFrameUBP2P.sql
new file mode 100644
index 0000000000..d6c3bc988e
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsFrameUBP2P.sql
@@ -0,0 +1,7 @@
+select *
+from (
+ select orderkey, suppkey, quantity,
+ round(sum(quantity) over (partition by suppkey order by orderkey rows
between unbounded preceding and 2 preceding), 5) total_quantity
+ from tpch_tiny_lineitem where partkey = 272
+)as t
+order by suppkey, orderkey
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsIdenticalWindows.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsIdenticalWindows.sql
new file mode 100644
index 0000000000..502a2d593d
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsIdenticalWindows.sql
@@ -0,0 +1,13 @@
+select * from
+ (
+ select orderkey, suppkey,
+ quantity,
+ round(sum(quantity) over (partition by suppkey order by orderkey rows
between unbounded preceding and current row), 5) total_quantity,
+ extendedprice,
+ round(sum(extendedprice) over (partition by suppkey order by orderkey rows
between unbounded preceding and current row), 5)
+ total_extendedprice,
+ discount,
+ round(avg(discount) over (partition by suppkey order by orderkey rows
between unbounded preceding and current row), 5) avg_discount
+ from tpch_tiny_lineitem where partkey = 272
+ ) as T
+order by suppkey, orderkey
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsIntersectingWindows.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsIntersectingWindows.sql
new file mode 100644
index 0000000000..bef8aaf89d
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsIntersectingWindows.sql
@@ -0,0 +1,14 @@
+select *
+from (
+ select orderkey, suppkey,
+ quantity,
+ round(sum(quantity) over (partition by suppkey order by orderkey
rows between 3 preceding and 1 following), 5) total_quantity,
+ extendedprice,
+ round(sum(extendedprice) over (partition by suppkey order by
orderkey rows between 1 preceding and 2 following), 5)
+ total_extendedprice,
+ discount,
+ round(avg(discount) over (partition by suppkey order by orderkey
rows between current row and unbounded following), 5) avg_discount
+ from tpch_tiny_lineitem
+ where partkey = 272
+)as T
+order by suppkey, orderkey
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsUBPImplicitCR.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsUBPImplicitCR.sql
new file mode 100644
index 0000000000..ac8830d0b2
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsUBPImplicitCR.sql
@@ -0,0 +1,7 @@
+select *
+from (
+ select orderkey, suppkey, quantity,
+ round(sum(quantity) over (partition by suppkey order by orderkey rows
unbounded preceding), 5) total_quantity
+ from tpch_tiny_lineitem where partkey = 272
+)as T
+order by suppkey, orderkey
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsUnbounded_order.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsUnbounded_order.sql
new file mode 100644
index 0000000000..b6e2f3b0cd
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/rowsUnbounded_order.sql
@@ -0,0 +1 @@
+SELECT nationkey, min(nationkey) OVER (PARTITION BY regionkey ORDER BY comment
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min FROM
tpch_tiny_nation
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/windowSpecificationAAcpA.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/windowSpecificationAAcpA.sql
new file mode 100644
index 0000000000..14045be169
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/windowSpecificationAAcpA.sql
@@ -0,0 +1,11 @@
+select *
+from
+ (select
+ suppkey, orderkey, partkey,
+ round(sum(quantity) over (partition by suppkey order by orderkey rows
between unbounded preceding and current row), 3) sum_quantity_A,
+ first_value(quantity) over (partition by suppkey order by orderkey
rows between unbounded preceding and current row) first_value_quantity_A,
+ round(sum(discount) over (partition by suppkey order by orderkey rows
between unbounded preceding and current row), 3) sum_discount_A
+ from tpch_tiny_lineitem
+ where (partkey = 272 or partkey = 273) and suppkey > 50
+ ) as T
+order by suppkey, orderkey
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/windowSpecificationABA.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/windowSpecificationABA.sql
new file mode 100644
index 0000000000..83f1ad0a02
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/windowSpecificationABA.sql
@@ -0,0 +1,11 @@
+select *
+from (
+ select
+ suppkey, orderkey, partkey,
+ round(sum(quantity) over (partition by suppkey order by orderkey rows
between unbounded preceding and current row), 3) sum_quantity_A,
+ round(sum(quantity) over (partition by orderkey order by shipdate rows
between UNBOUNDED preceding and CURRENT ROW), 3) sum_quantity_B,
+ round(sum(discount) over (partition by suppkey order by orderkey rows
between unbounded preceding and current row), 3) sum_discount_A
+
+ from tpch_tiny_lineitem where (partkey = 272 or partkey = 273) and suppkey
> 50
+)as t
+order by suppkey, orderkey
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/sql/windowSpecificationABcpA.sql
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/windowSpecificationABcpA.sql
new file mode 100644
index 0000000000..0dcc365b5f
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/sql/windowSpecificationABcpA.sql
@@ -0,0 +1,10 @@
+select *
+from (
+ select
+ suppkey, orderkey, partkey,
+ round(sum(quantity) over (partition by suppkey order by orderkey rows
between unbounded preceding and current row), 3) sum_quantity_A,
+ lag(quantity, 1, 0.0) over (partition by partkey order by orderkey)
lag_quantity_B,
+ round(sum(discount) over (partition by suppkey order by orderkey rows
between unbounded preceding and current row), 3) sum_discount_A
+ from tpch_tiny_lineitem where (partkey = 272 or partkey = 273) and suppkey
> 50
+) as T
+order by suppkey, orderkey
diff --git a/regression-test/suites/types/complex_types/basic_agg_test.groovy
b/regression-test/suites/types/complex_types/basic_agg_test.groovy
new file mode 100644
index 0000000000..06ddf9a383
--- /dev/null
+++ b/regression-test/suites/types/complex_types/basic_agg_test.groovy
@@ -0,0 +1,32 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("basic_agg_test", "types") {
+ def tables=["bitmap_basic_agg","hll_basic_agg"]
+
+ for (String table in tables) {
+ sql """drop table if exists ${table};"""
+ sql new File("""regression-test/common/table/${table}.sql""").text
+ sql new File("""regression-test/common/load/${table}.sql""").text
+ }
+
+ qt_sql_bitmap """select * from bitmap_basic_agg;"""
+
+ qt_sql_hll """select * from hll_basic_agg;"""
+
+ qt_sql_hll_cardinality """select k1, hll_cardinality(hll_union(k2)) from
hll_basic_agg group by k1 order by k1;"""
+}
diff --git a/regression-test/suites/with_clause_p0/ddl/lineitem.sql
b/regression-test/suites/with_clause_p0/ddl/lineitem.sql
new file mode 100644
index 0000000000..530befceb1
--- /dev/null
+++ b/regression-test/suites/with_clause_p0/ddl/lineitem.sql
@@ -0,0 +1,18 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_lineitem (
+ orderkey bigint,
+ partkey bigint,
+ suppkey bigint,
+ linenumber integer,
+ quantity double,
+ extendedprice double,
+ discount double,
+ tax double,
+ returnflag varchar(1),
+ linestatus varchar(1),
+ shipdate date,
+ commitdate date,
+ receiptdate date,
+ shipinstruct varchar(25),
+ shipmode varchar(10),
+ comment varchar(44)
+) DUPLICATE KEY(orderkey) DISTRIBUTED BY HASH(orderkey) BUCKETS 3 PROPERTIES
("replication_num" = "1")
\ No newline at end of file
diff --git a/regression-test/suites/with_clause_p0/ddl/nation.sql
b/regression-test/suites/with_clause_p0/ddl/nation.sql
new file mode 100644
index 0000000000..074618815f
--- /dev/null
+++ b/regression-test/suites/with_clause_p0/ddl/nation.sql
@@ -0,0 +1,12 @@
+
+CREATE TABLE IF NOT EXISTS tpch_tiny_nation (
+ nationkey BIGINT NOT NULL,
+ name VARCHAR(25) NOT NULL,
+ regionkey BIGINT NOT NULL,
+ comment VARCHAR(152)
+)
+DUPLICATE KEY(nationkey)
+DISTRIBUTED BY HASH(nationkey) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+)
\ No newline at end of file
diff --git a/regression-test/suites/with_clause_p0/ddl/part.sql
b/regression-test/suites/with_clause_p0/ddl/part.sql
new file mode 100644
index 0000000000..f8d952c9e8
--- /dev/null
+++ b/regression-test/suites/with_clause_p0/ddl/part.sql
@@ -0,0 +1,11 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_part (
+ p_partkey INTEGER NOT NULL,
+ p_name VARCHAR(55) NOT NULL,
+ p_mfgr CHAR(25) NOT NULL,
+ p_brand CHAR(10) NOT NULL,
+ p_type VARCHAR(25) NOT NULL,
+ p_size INTEGER NOT NULL,
+ p_container CHAR(10) NOT NULL,
+ p_retailprice DECIMAL(15, 2) NOT NULL,
+ p_comment VARCHAR(23) NOT NULL
+) DUPLICATE KEY(p_partkey) DISTRIBUTED BY HASH(p_partkey) BUCKETS 3 PROPERTIES
("replication_num" = "1")
\ No newline at end of file
diff --git a/regression-test/suites/with_clause_p0/ddl/partsupp.sql
b/regression-test/suites/with_clause_p0/ddl/partsupp.sql
new file mode 100644
index 0000000000..43a0af0575
--- /dev/null
+++ b/regression-test/suites/with_clause_p0/ddl/partsupp.sql
@@ -0,0 +1,7 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_partsupp (
+ partkey bigint,
+ suppkey bigint,
+ availqty integer,
+ supplycost double,
+ comment varchar(199)
+) DUPLICATE KEY(partkey, suppkey) DISTRIBUTED BY HASH(partkey) BUCKETS 3
PROPERTIES ("replication_num" = "1")
diff --git a/regression-test/suites/with_clause_p0/ddl/region.sql
b/regression-test/suites/with_clause_p0/ddl/region.sql
new file mode 100644
index 0000000000..36e282bb70
--- /dev/null
+++ b/regression-test/suites/with_clause_p0/ddl/region.sql
@@ -0,0 +1,10 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_region (
+ r_regionkey INTEGER NOT NULL,
+ r_name CHAR(25) NOT NULL,
+ r_comment VARCHAR(152)
+)
+DUPLICATE KEY(r_regionkey)
+DISTRIBUTED BY HASH(r_regionkey) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+)
\ No newline at end of file
diff --git a/regression-test/suites/with_clause_p0/ddl/supplier.sql
b/regression-test/suites/with_clause_p0/ddl/supplier.sql
new file mode 100644
index 0000000000..ff594b41a1
--- /dev/null
+++ b/regression-test/suites/with_clause_p0/ddl/supplier.sql
@@ -0,0 +1,9 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_supplier (
+ s_suppkey INTEGER NOT NULL,
+ s_name CHAR(25) NOT NULL,
+ s_address VARCHAR(40) NOT NULL,
+ s_nationkey INTEGER NOT NULL,
+ s_phone CHAR(15) NOT NULL,
+ s_acctbal DECIMAL(15, 2) NOT NULL,
+ s_comment VARCHAR(101) NOT NULL
+) DUPLICATE KEY(s_suppkey) DISTRIBUTED BY HASH(s_suppkey) BUCKETS 3 PROPERTIES
("replication_num" = "1")
\ No newline at end of file
diff --git a/regression-test/suites/with_clause_p0/ddl/workers.sql
b/regression-test/suites/with_clause_p0/ddl/workers.sql
new file mode 100644
index 0000000000..1ba2c331bc
--- /dev/null
+++ b/regression-test/suites/with_clause_p0/ddl/workers.sql
@@ -0,0 +1,10 @@
+CREATE TABLE IF NOT EXISTS tpch_tiny_workers (
+ id_employee INT,
+ first_name VARCHAR(32),
+ last_name VARCHAR(32),
+ date_of_employment DATE,
+ department TINYINT(1),
+ id_department INT,
+ name VARCHAR(32),
+ salary INT
+) DUPLICATE KEY(id_employee) DISTRIBUTED BY HASH(first_name) BUCKETS 3
PROPERTIES ("replication_num" = "1")
diff --git a/regression-test/suites/with_clause_p0/load.groovy
b/regression-test/suites/with_clause_p0/load.groovy
new file mode 100644
index 0000000000..e4ceb8d383
--- /dev/null
+++ b/regression-test/suites/with_clause_p0/load.groovy
@@ -0,0 +1,63 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+// The cases is copied from https://github.com/trinodb/trino/tree/master
+//
/testing/trino-product-tests/src/main/resources/sql-tests/testcases/window_functions
+// and modified by Doris.
+
+suite("load") {
+ def tables = ['lineitem', 'region', 'nation', 'part', 'supplier',
'partsupp', 'workers']
+
+ for (String table in tables) {
+ sql """ DROP TABLE IF EXISTS tpch_tiny_${table} """
+ }
+
+ for (String table in tables) {
+ sql new File("""${context.file.parent}/ddl/${table}.sql""").text
+ }
+
+ for (String tableName in tables) {
+ streamLoad {
+ // you can skip declare db, because a default db already specify
in ${DORIS_HOME}/conf/regression-conf.groovy
+ // db 'regression_test'
+ table "tpch_tiny_${tableName}"
+
+ // default column_separator is specify in doris fe config, usually
is '\t'.
+ // this line change to ','
+ set 'column_separator', '|'
+ set 'compress_type', 'GZ'
+ // relate to
${DORIS_HOME}/regression-test/data/demo/streamload_input.csv.
+ // also, you can stream load a http stream, e.g.
http://xxx/some.csv
+ file """${getS3Url() + '/regression/tpch/sf0.01/' +
tableName}.csv.gz"""
+
+ time 10000 // limit inflight 10s
+
+ // if declared a check callback, the default check condition will
ignore.
+ // So you must check all condition
+ check { result, exception, startTime, endTime ->
+ if (exception != null) {
+ throw exception
+ }
+ log.info("Stream load result: ${result}".toString())
+ def json = parseJson(result)
+ assertEquals('success', json.Status.toLowerCase())
+ assertEquals(json.NumberTotalRows, json.NumberLoadedRows)
+ assertTrue(json.NumberLoadedRows > 0 && json.LoadBytes > 0)
+ }
+ }
+ }
+}
\ No newline at end of file
diff --git
a/regression-test/suites/with_clause_p0/sql/multipleWithExprsReferencedFromParent_order.sql
b/regression-test/suites/with_clause_p0/sql/multipleWithExprsReferencedFromParent_order.sql
new file mode 100644
index 0000000000..cd5e48c002
--- /dev/null
+++
b/regression-test/suites/with_clause_p0/sql/multipleWithExprsReferencedFromParent_order.sql
@@ -0,0 +1,5 @@
+-- database: presto; groups: with_clause; tables: nation,region; queryType:
SELECT
+WITH wnation AS (SELECT nationkey, regionkey FROM tpch_tiny_nation),
+wregion AS (SELECT r_regionkey, r_name FROM tpch_tiny_region)
+select n.nationkey, r.r_regionkey from wnation n join wregion r on n.regionkey
= r.r_regionkey
+where r.r_name = 'AFRICA'
diff --git
a/regression-test/suites/with_clause_p0/sql/withExprAccessingAnotherAndAlsoFromParents.sql
b/regression-test/suites/with_clause_p0/sql/withExprAccessingAnotherAndAlsoFromParents.sql
new file mode 100644
index 0000000000..0a286630ac
--- /dev/null
+++
b/regression-test/suites/with_clause_p0/sql/withExprAccessingAnotherAndAlsoFromParents.sql
@@ -0,0 +1,4 @@
+-- database: presto; groups: with_clause; tables: nation; queryType: SELECT
+WITH w1 AS (select * from tpch_tiny_nation),
+w2 AS (select * from w1)
+select count(*) from w1, w2
diff --git
a/regression-test/suites/with_clause_p0/sql/withExprReferencedInWhereClause.sql
b/regression-test/suites/with_clause_p0/sql/withExprReferencedInWhereClause.sql
new file mode 100644
index 0000000000..cda270d7ed
--- /dev/null
+++
b/regression-test/suites/with_clause_p0/sql/withExprReferencedInWhereClause.sql
@@ -0,0 +1,3 @@
+-- database: presto; groups: with_clause; tables: nation,region; queryType:
SELECT
+WITH wregion AS (select min(regionkey) from tpch_tiny_nation where name >= 'N')
+select r_regionkey, r_name from tpch_tiny_region where r_regionkey IN (SELECT
* FROM wregion)
diff --git
a/regression-test/suites/with_clause_p0/sql/withExprRepeated_order.sql
b/regression-test/suites/with_clause_p0/sql/withExprRepeated_order.sql
new file mode 100644
index 0000000000..374f44603f
--- /dev/null
+++ b/regression-test/suites/with_clause_p0/sql/withExprRepeated_order.sql
@@ -0,0 +1,4 @@
+-- database: presto; groups: with_clause; tables: nation; queryType: SELECT
+WITH wnation AS (SELECT name, nationkey, regionkey FROM tpch_tiny_nation)
+SELECT n1.name, n2.name FROM wnation n1 JOIN wnation n2
+ON n1.nationkey=n2.regionkey
diff --git
a/regression-test/suites/with_clause_p0/sql/withExprUsingAnotherWithExpr.sql
b/regression-test/suites/with_clause_p0/sql/withExprUsingAnotherWithExpr.sql
new file mode 100644
index 0000000000..0214cbff3d
--- /dev/null
+++ b/regression-test/suites/with_clause_p0/sql/withExprUsingAnotherWithExpr.sql
@@ -0,0 +1,6 @@
+-- database: presto; groups: with_clause; tables: nation; queryType: SELECT
+WITH w1 AS (select min(nationkey) as x , max(regionkey) as y from
tpch_tiny_nation),
+w2 AS (select x, y from w1)
+select count(*) count, regionkey from tpch_tiny_nation group by regionkey
+union all
+(select * from w2) order by regionkey, count
diff --git
a/regression-test/suites/with_clause_p0/sql/withExprUsingOrderbyLimit.sql
b/regression-test/suites/with_clause_p0/sql/withExprUsingOrderbyLimit.sql
new file mode 100644
index 0000000000..409c5d31bd
--- /dev/null
+++ b/regression-test/suites/with_clause_p0/sql/withExprUsingOrderbyLimit.sql
@@ -0,0 +1,3 @@
+-- database: presto; groups: with_clause; tables: nation; queryType: SELECT
+WITH ordered AS (select nationkey a, regionkey b, name c from tpch_tiny_nation
order by 1,2 limit 10)
+select * from ordered order by 1,2 limit 5
diff --git
a/regression-test/suites/with_clause_p0/sql/withExprWithNoReferenceInParent.sql
b/regression-test/suites/with_clause_p0/sql/withExprWithNoReferenceInParent.sql
new file mode 100644
index 0000000000..603323658a
--- /dev/null
+++
b/regression-test/suites/with_clause_p0/sql/withExprWithNoReferenceInParent.sql
@@ -0,0 +1,2 @@
+-- database: presto; groups: with_clause; tables: nation; queryType: SELECT
+WITH ct AS (SELECT * FROM tpch_tiny_region) SELECT name FROM tpch_tiny_nation
where nationkey = 0
diff --git
a/regression-test/suites/with_clause_p0/sql/withSimpleExprInNestedFromClause.sql
b/regression-test/suites/with_clause_p0/sql/withSimpleExprInNestedFromClause.sql
new file mode 100644
index 0000000000..395c3610f7
--- /dev/null
+++
b/regression-test/suites/with_clause_p0/sql/withSimpleExprInNestedFromClause.sql
@@ -0,0 +1,2 @@
+-- database: presto; groups: with_clause; tables: nation; queryType: SELECT
+WITH nested AS (SELECT * FROM tpch_tiny_nation) SELECT count(*) FROM (select *
FROM nested) as a
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
