This is an automated email from the ASF dual-hosted git repository.
jacktengg 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 533c3ed6563 [fix](test) fix unstable regression test cases (#34902)
533c3ed6563 is described below
commit 533c3ed6563d1e0ab4a12ff140bfbfe83e64cc3c
Author: TengJianPing <[email protected]>
AuthorDate: Wed May 15 16:54:41 2024 +0800
[fix](test) fix unstable regression test cases (#34902)
---
.../data/nereids_p0/keyword/test_keyword.out | 2 +-
.../window_functions/test_window_fn.out | 36 ++++++++---------
.../data/query_p0/keyword/test_keyword.out | 2 +-
.../window_functions/test_window_fn.out | 36 ++++++++---------
.../get_assignment_compatible_type.groovy | 2 +-
.../suites/empty_relation/eliminate_empty.groovy | 2 +-
.../window_function/window_fn.groovy | 8 ++--
.../suites/nereids_p0/aggregate/aggregate.groovy | 2 +-
.../suites/nereids_p0/keyword/test_keyword.groovy | 2 +-
.../window_functions/test_window_fn.groovy | 46 +++++++++++-----------
.../suites/nereids_syntax_p0/bind_priority.groovy | 2 +-
.../suites/nereids_syntax_p0/cte.groovy | 2 +-
.../suites/query_p0/keyword/test_keyword.groovy | 2 +-
.../window_functions/test_window_fn.groovy | 46 +++++++++++-----------
14 files changed, 95 insertions(+), 95 deletions(-)
diff --git a/regression-test/data/nereids_p0/keyword/test_keyword.out
b/regression-test/data/nereids_p0/keyword/test_keyword.out
index 34f3a576ecc..9d174576d4a 100644
--- a/regression-test/data/nereids_p0/keyword/test_keyword.out
+++ b/regression-test/data/nereids_p0/keyword/test_keyword.out
@@ -636,8 +636,8 @@ true 15 1992 3021 11011920 0.000
true 9999-12-12 2015-04-02T00:00 3.141592653 2
\N 2
-- !alias21 --
-1 \N
\N 2
+1 \N
-- !distinct --
FALSE
diff --git
a/regression-test/data/nereids_p0/sql_functions/window_functions/test_window_fn.out
b/regression-test/data/nereids_p0/sql_functions/window_functions/test_window_fn.out
index ebb0ebed2f2..f8a572401f9 100644
---
a/regression-test/data/nereids_p0/sql_functions/window_functions/test_window_fn.out
+++
b/regression-test/data/nereids_p0/sql_functions/window_functions/test_window_fn.out
@@ -365,7 +365,7 @@ develop 11 2007-08-15 5200 9
8 9991305 6
9 10040184 7
--- !sql --
+-- !sql_sum_avg_count_1 --
develop 7 4200 25100
develop 8 6000 25100
develop 9 4500 25100
@@ -377,7 +377,7 @@ sales 1 5000 14600
sales 3 4800 14600
sales 4 4800 14600
--- !sql --
+-- !sql_sum_avg_count_2 --
3500 1
7400 2
11600 3
@@ -389,7 +389,7 @@ sales 4 4800 14600
41100 9
47100 10
--- !sql --
+-- !sql_sum_avg_count_3 --
47100 3500 2007-12-10
47100 3900 2006-12-23
47100 4200 2008-01-01
@@ -401,7 +401,7 @@ sales 4 4800 14600
47100 5200 2007-08-15
47100 6000 2006-10-01
--- !sql --
+-- !sql_sum_avg_count_4 --
47100 3500 2007-12-10
47100 3900 2006-12-23
47100 4200 2008-01-01
@@ -413,7 +413,7 @@ sales 4 4800 14600
47100 5200 2007-08-15
47100 6000 2006-10-01
--- !sql --
+-- !sql_sum_avg_count_5 --
8700 4200 2008-01-01
8700 4500 2008-01-01
12200 3500 2007-12-10
@@ -425,7 +425,7 @@ sales 4 4800 14600
47100 5000 2006-10-01
47100 6000 2006-10-01
--- !sql --
+-- !sql_sum_avg_count_6 --
47100 3500 2007-12-10
47100 3900 2006-12-23
47100 4200 2008-01-01
@@ -437,7 +437,7 @@ sales 4 4800 14600
47100 5200 2007-08-15
47100 6000 2006-10-01
--- !sql --
+-- !sql_sum_avg_count_7 --
47100 3500 2007-12-10
47100 3900 2006-12-23
47100 4200 2008-01-01
@@ -449,7 +449,7 @@ sales 4 4800 14600
47100 5200 2007-08-15
47100 6000 2006-10-01
--- !sql --
+-- !sql_sum_sum --
0 0 0 0.0
0 2 0 2.0
0 4 0 4.0
@@ -471,7 +471,7 @@ sales 4 4800 14600
3 7 7500 7.0
3 9 7500 9.0
--- !sql --
+-- !sql_count --
10
10
10
@@ -483,7 +483,7 @@ sales 4 4800 14600
10
10
--- !sql --
+-- !sql_sum --
0 0 0
0 0 0
2 0 2
@@ -548,7 +548,7 @@ sales 4 4800 14600
10000
10000
--- !sql --
+-- !sql_ntile_1 --
1 0 0
1 0 0
1 0 2
@@ -560,7 +560,7 @@ sales 4 4800 14600
3 7 1
3 9 1
--- !sql --
+-- !sql_lag_1 --
0 0 0
0 0 0
0 4 0
@@ -572,7 +572,7 @@ sales 4 4800 14600
0 1 3
1 3 3
--- !sql --
+-- !sql_lead_1 --
0 0 0
4 0 0
0 4 0
@@ -584,7 +584,7 @@ sales 4 4800 14600
3 1 3
0 3 3
--- !sql --
+-- !sql_lead_2 --
0 0 0
8 0 0
0 4 0
@@ -596,7 +596,7 @@ sales 4 4800 14600
6 1 3
0 3 3
--- !sql --
+-- !sql_lead_3 --
0 0 0
8 0 0
-1 4 0
@@ -610,7 +610,7 @@ sales 4 4800 14600
-- !sql --
--- !sql --
+-- !sql_cte_1 --
1 60
2 60
3 60
@@ -622,7 +622,7 @@ sales 4 4800 14600
10 60
11 60
--- !sql --
+-- !sql_cte_2 --
1 1
2 3
3 6
@@ -634,7 +634,7 @@ sales 4 4800 14600
10 49
11 60
--- !sql --
+-- !sql_cte_3 --
1 2
1 3
1 3
diff --git a/regression-test/data/query_p0/keyword/test_keyword.out
b/regression-test/data/query_p0/keyword/test_keyword.out
index 5fea725b424..8581b6339cc 100644
--- a/regression-test/data/query_p0/keyword/test_keyword.out
+++ b/regression-test/data/query_p0/keyword/test_keyword.out
@@ -639,8 +639,8 @@ true 15 1992 3021 11011920 0.000
true 9999-12-12 2015-04-02T00:00 3.141592653 2
\N 2
-- !alias21 --
-1 \N
\N 2
+1 \N
-- !distinct --
FALSE
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/test_window_fn.out
b/regression-test/data/query_p0/sql_functions/window_functions/test_window_fn.out
index ebb0ebed2f2..f8a572401f9 100644
---
a/regression-test/data/query_p0/sql_functions/window_functions/test_window_fn.out
+++
b/regression-test/data/query_p0/sql_functions/window_functions/test_window_fn.out
@@ -365,7 +365,7 @@ develop 11 2007-08-15 5200 9
8 9991305 6
9 10040184 7
--- !sql --
+-- !sql_sum_avg_count_1 --
develop 7 4200 25100
develop 8 6000 25100
develop 9 4500 25100
@@ -377,7 +377,7 @@ sales 1 5000 14600
sales 3 4800 14600
sales 4 4800 14600
--- !sql --
+-- !sql_sum_avg_count_2 --
3500 1
7400 2
11600 3
@@ -389,7 +389,7 @@ sales 4 4800 14600
41100 9
47100 10
--- !sql --
+-- !sql_sum_avg_count_3 --
47100 3500 2007-12-10
47100 3900 2006-12-23
47100 4200 2008-01-01
@@ -401,7 +401,7 @@ sales 4 4800 14600
47100 5200 2007-08-15
47100 6000 2006-10-01
--- !sql --
+-- !sql_sum_avg_count_4 --
47100 3500 2007-12-10
47100 3900 2006-12-23
47100 4200 2008-01-01
@@ -413,7 +413,7 @@ sales 4 4800 14600
47100 5200 2007-08-15
47100 6000 2006-10-01
--- !sql --
+-- !sql_sum_avg_count_5 --
8700 4200 2008-01-01
8700 4500 2008-01-01
12200 3500 2007-12-10
@@ -425,7 +425,7 @@ sales 4 4800 14600
47100 5000 2006-10-01
47100 6000 2006-10-01
--- !sql --
+-- !sql_sum_avg_count_6 --
47100 3500 2007-12-10
47100 3900 2006-12-23
47100 4200 2008-01-01
@@ -437,7 +437,7 @@ sales 4 4800 14600
47100 5200 2007-08-15
47100 6000 2006-10-01
--- !sql --
+-- !sql_sum_avg_count_7 --
47100 3500 2007-12-10
47100 3900 2006-12-23
47100 4200 2008-01-01
@@ -449,7 +449,7 @@ sales 4 4800 14600
47100 5200 2007-08-15
47100 6000 2006-10-01
--- !sql --
+-- !sql_sum_sum --
0 0 0 0.0
0 2 0 2.0
0 4 0 4.0
@@ -471,7 +471,7 @@ sales 4 4800 14600
3 7 7500 7.0
3 9 7500 9.0
--- !sql --
+-- !sql_count --
10
10
10
@@ -483,7 +483,7 @@ sales 4 4800 14600
10
10
--- !sql --
+-- !sql_sum --
0 0 0
0 0 0
2 0 2
@@ -548,7 +548,7 @@ sales 4 4800 14600
10000
10000
--- !sql --
+-- !sql_ntile_1 --
1 0 0
1 0 0
1 0 2
@@ -560,7 +560,7 @@ sales 4 4800 14600
3 7 1
3 9 1
--- !sql --
+-- !sql_lag_1 --
0 0 0
0 0 0
0 4 0
@@ -572,7 +572,7 @@ sales 4 4800 14600
0 1 3
1 3 3
--- !sql --
+-- !sql_lead_1 --
0 0 0
4 0 0
0 4 0
@@ -584,7 +584,7 @@ sales 4 4800 14600
3 1 3
0 3 3
--- !sql --
+-- !sql_lead_2 --
0 0 0
8 0 0
0 4 0
@@ -596,7 +596,7 @@ sales 4 4800 14600
6 1 3
0 3 3
--- !sql --
+-- !sql_lead_3 --
0 0 0
8 0 0
-1 4 0
@@ -610,7 +610,7 @@ sales 4 4800 14600
-- !sql --
--- !sql --
+-- !sql_cte_1 --
1 60
2 60
3 60
@@ -622,7 +622,7 @@ sales 4 4800 14600
10 60
11 60
--- !sql --
+-- !sql_cte_2 --
1 1
2 3
3 6
@@ -634,7 +634,7 @@ sales 4 4800 14600
10 49
11 60
--- !sql --
+-- !sql_cte_3 --
1 2
1 3
1 3
diff --git
a/regression-test/suites/datatype_p0/scalar_types/get_assignment_compatible_type.groovy
b/regression-test/suites/datatype_p0/scalar_types/get_assignment_compatible_type.groovy
index afbf3746259..f982c01cda3 100644
---
a/regression-test/suites/datatype_p0/scalar_types/get_assignment_compatible_type.groovy
+++
b/regression-test/suites/datatype_p0/scalar_types/get_assignment_compatible_type.groovy
@@ -48,5 +48,5 @@ suite("get_assignment_compatible_type") {
"replication_allocation" = "tag.location.default: 1"
); """
sql "insert into test_decimal_boolean_union
values(1,true,0.0),(1,false,0.0)"
- qt_test_union "select c1 from test_decimal_boolean_union union select c2
from test_decimal_boolean_union order by 1"
+ qt_test_union "select * from (select c1 from test_decimal_boolean_union
union select c2 from test_decimal_boolean_union) a order by 1"
}
\ No newline at end of file
diff --git a/regression-test/suites/empty_relation/eliminate_empty.groovy
b/regression-test/suites/empty_relation/eliminate_empty.groovy
index 0aaa2bad06b..679f1d456a1 100644
--- a/regression-test/suites/empty_relation/eliminate_empty.groovy
+++ b/regression-test/suites/empty_relation/eliminate_empty.groovy
@@ -24,7 +24,7 @@ suite("eliminate_empty") {
sql 'set enable_fallback_to_original_planner=false'
sql 'set forbid_unknown_col_stats=false'
qt_onerow_union """
- select * from (select 1, 2 union select 3, 4) T
+ select * from (select 1, 2 union select 3, 4) T order by 1, 2
"""
qt_join """
diff --git
a/regression-test/suites/nereids_function_p0/window_function/window_fn.groovy
b/regression-test/suites/nereids_function_p0/window_function/window_fn.groovy
index 903c59d2282..e92cc62190a 100644
---
a/regression-test/suites/nereids_function_p0/window_function/window_fn.groovy
+++
b/regression-test/suites/nereids_function_p0/window_function/window_fn.groovy
@@ -611,7 +611,7 @@ suite("nereids_win_fn") {
// qt_sql_ntile_ob '''
// select kint, ntile(3) over(order by kint) as wf from fn_test'''
qt_sql_ntile_pb '''
- select kstr, ntile(3) over(partition by kstr) as wf from
fn_test order by kstr'''
+ select kstr, ntile(3) over(partition by kstr) as wf from
fn_test order by kstr, wf'''
qt_sql_ntile_pb_ob '''
select kstr, kint, ntile(3) over(partition by kstr order by
kint) as wf from fn_test order by kstr, kint'''
// qt_sql_ntile_notnull '''
@@ -619,7 +619,7 @@ suite("nereids_win_fn") {
// qt_sql_ntile_ob_notnull '''
// select kint, ntile(3) over(order by kint) as wf from
fn_test_not_nullable'''
qt_sql_ntile_pb_notnull '''
- select kstr, ntile(3) over(partition by kstr) as wf from
fn_test_not_nullable order by kstr'''
+ select kstr, ntile(3) over(partition by kstr) as wf from
fn_test_not_nullable order by kstr, wf'''
qt_sql_ntile_pb_ob_notnull '''
select kstr, kint, ntile(3) over(partition by kstr order by
kint) as wf from fn_test_not_nullable order by kstr, kint'''
@@ -665,7 +665,7 @@ suite("nereids_win_fn") {
// qt_sql_row_number_ob '''
// select kint, row_number() over(order by kint) as wf from
fn_test'''
qt_sql_row_number_pb '''
- select kstr, row_number() over(partition by kstr) as wf from
fn_test order by kstr'''
+ select kstr, row_number() over(partition by kstr) as wf from
fn_test order by kstr, wf'''
qt_sql_row_number_pb_ob '''
select kstr, kint, row_number() over(partition by kstr order by
kint) as wf from fn_test order by kstr, kint'''
// qt_sql_row_number_notnull '''
@@ -673,7 +673,7 @@ suite("nereids_win_fn") {
// qt_sql_row_number_ob_notnull '''
// select kint, row_number() over(order by kint) as wf from
fn_test_not_nullable'''
qt_sql_row_number_pb_notnull '''
- select kstr, row_number() over(partition by kstr) as wf from
fn_test_not_nullable order by kstr'''
+ select kstr, row_number() over(partition by kstr) as wf from
fn_test_not_nullable order by kstr, wf'''
qt_sql_row_number_pb_ob_notnull '''
select kstr, kint, row_number() over(partition by kstr order by
kint) as wf from fn_test_not_nullable order by kstr, kint'''
diff --git a/regression-test/suites/nereids_p0/aggregate/aggregate.groovy
b/regression-test/suites/nereids_p0/aggregate/aggregate.groovy
index c69e80eca35..9cc0dc9a727 100644
--- a/regression-test/suites/nereids_p0/aggregate/aggregate.groovy
+++ b/regression-test/suites/nereids_p0/aggregate/aggregate.groovy
@@ -330,6 +330,6 @@ suite("aggregate") {
sql " set parallel_pipeline_task_num = 1; "
sql " set enable_pipeline_x_engine = 1; "
qt_having_with_limit """
- select k1 as k, avg(k2) as k2 from tempbaseall group by k1 having k2
< -32765 limit 1;
+ select k1 as k, avg(k2) as k2 from tempbaseall group by k1 having k2
< -32765 order by k1 limit 1;
"""
}
diff --git a/regression-test/suites/nereids_p0/keyword/test_keyword.groovy
b/regression-test/suites/nereids_p0/keyword/test_keyword.groovy
index 3b231a91dea..85f5b9f9593 100644
--- a/regression-test/suites/nereids_p0/keyword/test_keyword.groovy
+++ b/regression-test/suites/nereids_p0/keyword/test_keyword.groovy
@@ -107,7 +107,7 @@ suite("test_keyword", "query,p0") {
qt_alias18 "select k1 as a, k2 as b, k3 as c from baseall t group by a, b,
c order by a, b, c;"
qt_alias19 "select k1 as a, k2 as b, k3 as c from baseall t group by a, b,
c having a > 5 order by a, b, c;"
qt_alias20 "select * from (select 1 as a) b right join (select 2 as a) c
using(a);"
- qt_alias21 "select * from (select 1 as a) b full join (select 2 as a) c
using(a);"
+ qt_alias21 "select * from (select 1 as a) b full join (select 2 as a) c
using(a) order by 1, 2;"
try_sql "select k1 as k7, k2 as k8, k3 as k9 from baseall t group by k7,
k8, k9 having k7 > 5 \
order by k7;"
try_sql "select k1 as k7, k2 as k8, k3 as k9 from baseall t where k8 > 0
group by k7, k8, k9 having k7 > 5 order by k7;"
diff --git
a/regression-test/suites/nereids_p0/sql_functions/window_functions/test_window_fn.groovy
b/regression-test/suites/nereids_p0/sql_functions/window_functions/test_window_fn.groovy
index 87b9c7f7dc0..6b3b5328b16 100644
---
a/regression-test/suites/nereids_p0/sql_functions/window_functions/test_window_fn.groovy
+++
b/regression-test/suites/nereids_p0/sql_functions/window_functions/test_window_fn.groovy
@@ -231,36 +231,36 @@ suite("test_window_fn") {
// sum_avg_count
- qt_sql """
+ qt_sql_sum_avg_count_1 """
SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname)
FROM ${tbName1} order by depname,empno,salary;
"""
- qt_sql """
+ qt_sql_sum_avg_count_2 """
SELECT sum(salary) OVER (ORDER BY salary) as s, count(1) OVER (ORDER
BY salary) as c FROM ${tbName1} order by s, c;
"""
- qt_sql """
+ qt_sql_sum_avg_count_3 """
select sum(salary) over (order by enroll_date range between UNBOUNDED
preceding and UNBOUNDED following), salary, enroll_date from ${tbName1} order
by salary, enroll_date;
"""
- qt_sql """
+ qt_sql_sum_avg_count_4 """
select sum(salary) over (order by enroll_date desc range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
"""
- qt_sql """
+ qt_sql_sum_avg_count_5 """
select sum(salary) over (order by enroll_date desc range between
UNBOUNDED preceding and current row) as s, salary, enroll_date from ${tbName1}
order by s, salary;
"""
- qt_sql """
+ qt_sql_sum_avg_count_6 """
select sum(salary) over (order by enroll_date, salary range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
"""
- qt_sql """
+ qt_sql_sum_avg_count_7 """
select sum(salary) over (order by depname range between UNBOUNDED
preceding and UNBOUNDED following ), salary, enroll_date from ${tbName1} order
by salary, enroll_date;
"""
- qt_sql """
+ qt_sql_sum_sum """
SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten)
FROM ${tbName2}
GROUP BY four, ten ORDER BY four, ten;
"""
- qt_sql """
+ qt_sql_count """
SELECT COUNT(1) OVER () FROM ${tbName2} WHERE unique2 < 10;
"""
- qt_sql """
- SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1,
ten, four FROM ${tbName2} WHERE unique2 < 10 order by ten, four;
+ qt_sql_sum """
+ SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1,
ten, four FROM ${tbName2} WHERE unique2 < 10 order by ten, four, sum_1;
"""
qt_sql """
SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER
(PARTITION BY two ORDER BY ten) AS wsum FROM ${tbName2} GROUP BY ten, two order
by gsum, wsum;
@@ -284,26 +284,26 @@ suite("test_window_fn") {
// ntile
- qt_sql """
+ qt_sql_ntile_1 """
SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM ${tbName2}
WHERE unique2 < 10;
"""
// lag
- qt_sql """
- SELECT lag(ten, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten, four
FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ qt_sql_lag_1 """
+ SELECT lag(ten, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten, four
FROM ${tbName2} WHERE unique2 < 10 order by four, ten, 1;
"""
// lead
- qt_sql """
- SELECT lead(ten, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ qt_sql_lead_1 """
+ SELECT lead(ten, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10 order by four, ten, 1;
"""
- qt_sql """
- SELECT lead(ten * 2, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ qt_sql_lead_2 """
+ SELECT lead(ten * 2, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10 order by four, ten, 1;
"""
- qt_sql """
- SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten),
ten, four FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ qt_sql_lead_3 """
+ SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten),
ten, four FROM ${tbName2} WHERE unique2 < 10 order by four, ten, 1;
"""
@@ -316,15 +316,15 @@ suite("test_window_fn") {
"""
// cte
- qt_sql """
+ qt_sql_cte_1 """
with cte as (select empno as x from ${tbName1})
SELECT x, (sum(x) over (ORDER BY x range between UNBOUNDED preceding
and UNBOUNDED following)) FROM cte;
"""
- qt_sql """
+ qt_sql_cte_2 """
with cte as (select empno as x from ${tbName1})
SELECT x, (sum(x) over (ORDER BY x range between UNBOUNDED preceding
and CURRENT ROW)) FROM cte;
"""
- qt_sql """
+ qt_sql_cte_3 """
WITH cte AS (
select 1 as x union all select 1 as x union all select 1 as x union all
SELECT empno as x FROM ${tbName1})
diff --git a/regression-test/suites/nereids_syntax_p0/bind_priority.groovy
b/regression-test/suites/nereids_syntax_p0/bind_priority.groovy
index 769f1771982..c4b02d691fe 100644
--- a/regression-test/suites/nereids_syntax_p0/bind_priority.groovy
+++ b/regression-test/suites/nereids_syntax_p0/bind_priority.groovy
@@ -42,7 +42,7 @@ suite("bind_priority") {
"""
qt_select """
- select coalesce(a, 'all') as a, count(*) as cnt from (select null as
a union all select 'a' as a ) t group by grouping sets ((a),()) order by a;
+ select coalesce(a, 'all') as a, count(*) as cnt from (select null as
a union all select 'a' as a ) t group by grouping sets ((a),()) order by a,
cnt;
"""
qt_select """
diff --git a/regression-test/suites/nereids_syntax_p0/cte.groovy
b/regression-test/suites/nereids_syntax_p0/cte.groovy
index 56056117bc5..5402ffb8e21 100644
--- a/regression-test/suites/nereids_syntax_p0/cte.groovy
+++ b/regression-test/suites/nereids_syntax_p0/cte.groovy
@@ -327,7 +327,7 @@ suite("cte") {
GROUP BY Id, UpdateDateTime, Value
) rolling ON temptable.Id = rolling.Id AND temptable.UpdateDateTime =
rolling.UpdateDateTime
) tab
- WHERE Id IN (1, 2)
+ WHERE Id IN (1, 2) order by 1, 2, 3, 4
"""
// rewrite cte children should work well with cost based rewrite rule.
rely on rewrite rule: InferSetOperatorDistinct
diff --git a/regression-test/suites/query_p0/keyword/test_keyword.groovy
b/regression-test/suites/query_p0/keyword/test_keyword.groovy
index dd8f072043e..780aa39e2dd 100644
--- a/regression-test/suites/query_p0/keyword/test_keyword.groovy
+++ b/regression-test/suites/query_p0/keyword/test_keyword.groovy
@@ -105,7 +105,7 @@ suite("test_keyword", "query,p0") {
qt_alias18 "select k1 as a, k2 as b, k3 as c from baseall t group by a, b,
c order by a, b, c;"
qt_alias19 "select k1 as a, k2 as b, k3 as c from baseall t group by a, b,
c having a > 5 order by a, b, c;"
qt_alias20 "select * from (select 1 as a) b right join (select 2 as a) c
using(a);"
- qt_alias21 "select * from (select 1 as a) b full join (select 2 as a) c
using(a);"
+ qt_alias21 "select * from (select 1 as a) b full join (select 2 as a) c
using(a) order by 1, 2;"
try_sql "select k1 as k7, k2 as k8, k3 as k9 from baseall t group by k7,
k8, k9 having k7 > 5 \
order by k7;"
try_sql "select k1 as k7, k2 as k8, k3 as k9 from baseall t where k8 > 0
group by k7, k8, k9 having k7 > 5 order by k7;"
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/test_window_fn.groovy
b/regression-test/suites/query_p0/sql_functions/window_functions/test_window_fn.groovy
index 66bbab89d24..a84989f5b2e 100644
---
a/regression-test/suites/query_p0/sql_functions/window_functions/test_window_fn.groovy
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/test_window_fn.groovy
@@ -231,36 +231,36 @@ suite("test_window_fn") {
// sum_avg_count
- qt_sql """
+ qt_sql_sum_avg_count_1 """
SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname)
FROM ${tbName1} order by depname,empno,salary;
"""
- qt_sql """
+ qt_sql_sum_avg_count_2 """
SELECT sum(salary) OVER (ORDER BY salary) as s, count(1) OVER (ORDER
BY salary) as c FROM ${tbName1} order by s, c;
"""
- qt_sql """
+ qt_sql_sum_avg_count_3 """
select sum(salary) over (order by enroll_date range between UNBOUNDED
preceding and UNBOUNDED following), salary, enroll_date from ${tbName1} order
by salary, enroll_date;
"""
- qt_sql """
+ qt_sql_sum_avg_count_4 """
select sum(salary) over (order by enroll_date desc range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
"""
- qt_sql """
+ qt_sql_sum_avg_count_5 """
select sum(salary) over (order by enroll_date desc range between
UNBOUNDED preceding and current row) as s, salary, enroll_date from ${tbName1}
order by s, salary;
"""
- qt_sql """
+ qt_sql_sum_avg_count_6 """
select sum(salary) over (order by enroll_date, salary range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
"""
- qt_sql """
+ qt_sql_sum_avg_count_7 """
select sum(salary) over (order by depname range between UNBOUNDED
preceding and UNBOUNDED following ), salary, enroll_date from ${tbName1} order
by salary, enroll_date;
"""
- qt_sql """
+ qt_sql_sum_sum """
SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten)
FROM ${tbName2}
GROUP BY four, ten ORDER BY four, ten;
"""
- qt_sql """
+ qt_sql_count """
SELECT COUNT(1) OVER () FROM ${tbName2} WHERE unique2 < 10;
"""
- qt_sql """
- SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1,
ten, four FROM ${tbName2} WHERE unique2 < 10 order by ten, four;
+ qt_sql_sum """
+ SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1,
ten, four FROM ${tbName2} WHERE unique2 < 10 order by ten, four, sum_1;
"""
qt_sql """
SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER
(PARTITION BY two ORDER BY ten) AS wsum FROM ${tbName2} GROUP BY ten, two order
by gsum, wsum;
@@ -284,26 +284,26 @@ suite("test_window_fn") {
// ntile
- qt_sql """
+ qt_sql_ntile_1 """
SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM ${tbName2}
WHERE unique2 < 10;
"""
// lag
- qt_sql """
- SELECT lag(ten, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten, four
FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ qt_sql_lag_1 """
+ SELECT lag(ten, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten, four
FROM ${tbName2} WHERE unique2 < 10 order by four, ten, 1;
"""
// lead
- qt_sql """
- SELECT lead(ten, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ qt_sql_lead_1 """
+ SELECT lead(ten, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10 order by four, ten, 1;
"""
- qt_sql """
- SELECT lead(ten * 2, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ qt_sql_lead_2 """
+ SELECT lead(ten * 2, 1, 0) OVER (PARTITION BY four ORDER BY ten), ten,
four FROM ${tbName2} WHERE unique2 < 10 order by four, ten, 1;
"""
- qt_sql """
- SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten),
ten, four FROM ${tbName2} WHERE unique2 < 10 order by four, ten;
+ qt_sql_lead_3 """
+ SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten),
ten, four FROM ${tbName2} WHERE unique2 < 10 order by four, ten, 1;
"""
@@ -316,15 +316,15 @@ suite("test_window_fn") {
"""
// cte
- qt_sql """
+ qt_sql_cte_1 """
with cte as (select empno as x from ${tbName1})
SELECT x, (sum(x) over (ORDER BY x range between UNBOUNDED preceding
and UNBOUNDED following)) FROM cte;
"""
- qt_sql """
+ qt_sql_cte_2 """
with cte as (select empno as x from ${tbName1})
SELECT x, (sum(x) over (ORDER BY x range between UNBOUNDED preceding
and CURRENT ROW)) FROM cte;
"""
- qt_sql """
+ qt_sql_cte_3 """
WITH cte AS (
select 1 as x union all select 1 as x union all select 1 as x union all
SELECT empno as x FROM ${tbName1})
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
