This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-doris.git
The following commit(s) were added to refs/heads/master by this push:
new 41b693e1df [test] Add window cast bitmap digital_masking function
regression test. (#9924)
41b693e1df is described below
commit 41b693e1dfdac0a3b88e29538b024094c13e038f
Author: smallhibiscus <844981...@qq.com>
AuthorDate: Thu Jun 16 19:14:51 2022 +0800
[test] Add window cast bitmap digital_masking function regression test.
(#9924)
---
.../sql-functions/bitmap-functions/to_bitmap.md | 2 +-
.../sql-functions/bitmap-functions/to_bitmap.md | 2 +-
.../bitmap_functions/test_bitmap_function.out | 211 +++++++++++++++++++++
.../cast_function/test_cast_function.out | 7 +
.../digital-masking/test_digital_masking.out | 4 +
.../window_functions/test_window_function.out | 117 ++++++++++++
.../bitmap_functions/test_bitmap_function.groovy | 147 ++++++++++++++
.../cast_function/test_cast_function.groovy | 25 +++
.../digital-masking/test_digital_masking.groovy | 24 +++
.../window_functions/test_window_function.groovy | 158 +++++++++++++++
10 files changed, 695 insertions(+), 2 deletions(-)
diff --git
a/docs/en/docs/sql-manual/sql-functions/bitmap-functions/to_bitmap.md
b/docs/en/docs/sql-manual/sql-functions/bitmap-functions/to_bitmap.md
index ea0e8bbdee..62571390c9 100644
--- a/docs/en/docs/sql-manual/sql-functions/bitmap-functions/to_bitmap.md
+++ b/docs/en/docs/sql-manual/sql-functions/bitmap-functions/to_bitmap.md
@@ -52,7 +52,7 @@ MySQL> select bitmap_to_string(to_bitmap(-1));
+---------------------------------+
| bitmap_to_string(to_bitmap(-1)) |
+---------------------------------+
-| NULL |
+| |
+---------------------------------+
```
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/bitmap-functions/to_bitmap.md
b/docs/zh-CN/docs/sql-manual/sql-functions/bitmap-functions/to_bitmap.md
index a7f06c8d85..1fa3ec151b 100644
--- a/docs/zh-CN/docs/sql-manual/sql-functions/bitmap-functions/to_bitmap.md
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/bitmap-functions/to_bitmap.md
@@ -52,7 +52,7 @@ MySQL> select bitmap_to_string(to_bitmap(-1));
+---------------------------------+
| bitmap_to_string(to_bitmap(-1)) |
+---------------------------------+
-| NULL |
+| |
+---------------------------------+
```
diff --git
a/regression-test/data/query/sql_functions/bitmap_functions/test_bitmap_function.out
b/regression-test/data/query/sql_functions/bitmap_functions/test_bitmap_function.out
new file mode 100644
index 0000000000..7826056e57
--- /dev/null
+++
b/regression-test/data/query/sql_functions/bitmap_functions/test_bitmap_function.out
@@ -0,0 +1,211 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+0
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1,2
+
+-- !sql --
+
+
+-- !sql --
+\N
+
+-- !sql --
+false
+
+-- !sql --
+true
+
+-- !sql --
+0
+
+-- !sql --
+
+
+-- !sql --
+0,1,2
+
+-- !sql --
+\N
+
+-- !sql --
+false
+
+-- !sql --
+true
+
+-- !sql --
+true
+
+-- !sql --
+false
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+0
+
+-- !sql --
+2
+
+-- !sql --
+1
+
+-- !sql --
+1,2
+
+-- !sql --
+\N
+
+-- !sql --
+0,1,2,10
+
+-- !sql --
+1,2,3,4,5,10
+
+-- !sql --
+0
+
+-- !sql --
+3
+
+-- !sql --
+1
+
+-- !sql --
+2
+
+-- !sql --
+0
+
+-- !sql --
+\N
+
+-- !sql --
+3
+
+-- !sql --
+3
+
+-- !sql --
+5
+
+-- !sql --
+6
+
+-- !sql --
+\N
+
+-- !sql --
+2
+
+-- !sql --
+1,4
+
+-- !sql --
+1,3,5
+
+-- !sql --
+1,3,5
+
+-- !sql --
+\N
+
+-- !sql --
+4
+
+-- !sql --
+0
+
+-- !sql --
+6
+
+-- !sql --
+3
+
+-- !sql --
+3
+
+-- !sql --
+\N
+
+-- !sql --
+0
+
+-- !sql --
+5
+
+-- !sql --
+2
+
+-- !sql --
+2
+
+-- !sql --
+1,2,3,4,5
+
+-- !sql --
+2
+
+-- !sql --
+1,2,3
+
+-- !sql --
+4,5
+
+-- !sql --
+0,1,2
+
+-- !sql --
+2,3
+
+-- !sql --
+2,3,5
+
+-- !sql --
+\N
+
+-- !sql --
+
+
+-- !sql --
+1
+
+-- !sql --
+1,2
+
+-- !sql --
+1 \N
+2 \N
+
+-- !sql --
+1 3
+2 2
+
+-- !sql --
+1 3
+2 2
+
+-- !sql --
+1
+
+-- !sql --
+
+
+-- !sql --
+\N
+
+-- !sql --
+9999999999
+
diff --git
a/regression-test/data/query/sql_functions/cast_function/test_cast_function.out
b/regression-test/data/query/sql_functions/cast_function/test_cast_function.out
new file mode 100644
index 0000000000..6584f8cc9c
--- /dev/null
+++
b/regression-test/data/query/sql_functions/cast_function/test_cast_function.out
@@ -0,0 +1,7 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+1
+
+-- !sql --
+11
+
diff --git
a/regression-test/data/query/sql_functions/digital-masking/test_digital_masking.out
b/regression-test/data/query/sql_functions/digital-masking/test_digital_masking.out
new file mode 100644
index 0000000000..f0720ba245
--- /dev/null
+++
b/regression-test/data/query/sql_functions/digital-masking/test_digital_masking.out
@@ -0,0 +1,4 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+138****5678
+
diff --git
a/regression-test/data/query/sql_functions/window_functions/test_window_function.out
b/regression-test/data/query/sql_functions/window_functions/test_window_function.out
new file mode 100644
index 0000000000..57d5dd3e86
--- /dev/null
+++
b/regression-test/data/query/sql_functions/window_functions/test_window_function.out
@@ -0,0 +1,117 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+JDR 2014-10-02T00:00 12.86 12.875
+JDR 2014-10-03T00:00 12.89 12.896666667
+JDR 2014-10-04T00:00 12.94 12.793333333
+JDR 2014-10-05T00:00 12.55 13.173333333
+JDR 2014-10-06T00:00 14.03 13.776666667
+JDR 2014-10-07T00:00 14.75 14.253333333
+JDR 2014-10-08T00:00 13.98 14.365
+
+-- !sql --
+JDR 2014-10-02T00:00 12.86 higher
+JDR 2014-10-03T00:00 12.89 higher
+JDR 2014-10-04T00:00 12.94 flat or lower
+JDR 2014-10-05T00:00 12.55 higher
+JDR 2014-10-06T00:00 14.03 higher
+JDR 2014-10-07T00:00 14.75 flat or lower
+JDR 2014-10-08T00:00 13.98 flat or lower
+
+-- !sql --
+JDR 2014-10-02T00:00 12.86 0
+JDR 2014-10-03T00:00 12.89 12.86
+JDR 2014-10-04T00:00 12.94 12.89
+JDR 2014-10-05T00:00 12.55 12.94
+JDR 2014-10-06T00:00 14.03 12.55
+JDR 2014-10-07T00:00 14.75 14.03
+JDR 2014-10-08T00:00 13.98 14.75
+
+-- !sql --
+2 even 6
+4 even 12
+6 even 18
+8 even 24
+10 even 18
+1 odd 4
+3 odd 9
+5 odd 15
+7 odd 21
+9 odd 16
+
+-- !sql --
+2 even 3.0
+4 even 4.0
+6 even 6.0
+8 even 8.0
+10 even 9.0
+1 odd 2.0
+3 odd 3.0
+5 odd 5.0
+7 odd 7.0
+9 odd 8.0
+
+-- !sql --
+2 even 1
+4 even 2
+6 even 3
+8 even 4
+10 even 5
+1 odd 1
+3 odd 2
+5 odd 3
+7 odd 4
+9 odd 5
+
+-- !sql --
+
+-- !sql --
+
+-- !sql --
+1 1 1
+1 2 2
+1 2 2
+2 1 1
+2 2 2
+2 3 3
+3 1 1
+3 1 1
+3 2 3
+
+-- !sql --
+1 1 1
+1 2 2
+1 2 2
+2 1 1
+2 2 2
+2 3 3
+3 1 1
+3 1 1
+3 2 2
+
+-- !sql --
+1 1 1
+1 2 2
+1 2 3
+2 1 1
+2 2 2
+2 3 3
+3 1 1
+3 1 2
+3 2 3
+
+-- !sql --
+Germany Boris Guten tag
+Germany Michael Guten tag
+Sweden Bjorn Hej
+Sweden Mats Hej
+USA John Hi
+USA Pete Hi
+
+-- !sql --
+Germany Boris Guten tag
+Germany Michael Guten morgen
+Sweden Bjorn Hej
+Sweden Mats Tja
+USA John Hi
+USA Pete Hello
+
diff --git
a/regression-test/suites/query/sql_functions/bitmap_functions/test_bitmap_function.groovy
b/regression-test/suites/query/sql_functions/bitmap_functions/test_bitmap_function.groovy
new file mode 100644
index 0000000000..a2b5549a62
--- /dev/null
+++
b/regression-test/suites/query/sql_functions/bitmap_functions/test_bitmap_function.groovy
@@ -0,0 +1,147 @@
+// 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("test_bitmap_function", "query") {
+
+ sql """ SET enable_vectorized_engine = TRUE; """
+
+ // BITMAP_AND
+ qt_sql """ select bitmap_count(bitmap_and(to_bitmap(1), to_bitmap(2))) cnt
"""
+ qt_sql """ select bitmap_count(bitmap_and(to_bitmap(1), to_bitmap(1))) cnt
"""
+ qt_sql """ select bitmap_to_string(bitmap_and(to_bitmap(1), to_bitmap(1)))
"""
+ qt_sql """ select bitmap_to_string(bitmap_and(bitmap_from_string('1,2,3'),
bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'))) """
+ qt_sql """ select bitmap_to_string(bitmap_and(bitmap_from_string('1,2,3'),
bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'),bitmap_empty())) """
+ qt_sql """ select bitmap_to_string(bitmap_and(bitmap_from_string('1,2,3'),
bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'),NULL)) """
+
+ // BITMAP_CONTAINS
+ qt_sql """ select bitmap_contains(to_bitmap(1),2) cnt """
+ qt_sql """ select bitmap_contains(to_bitmap(1),1) cnt """
+
+ // BITMAP_EMPTY
+ qt_sql """ select bitmap_count(bitmap_empty()) """
+
+ // BITMAP_FROM_STRING
+ qt_sql """ select bitmap_to_string(bitmap_empty()) """
+ qt_sql """ select bitmap_to_string(bitmap_from_string("0, 1, 2")) """
+ qt_sql """ select bitmap_from_string("-1, 0, 1, 2") """
+
+ // BITMAP_HAS_ANY
+ qt_sql """ select bitmap_has_any(to_bitmap(1),to_bitmap(2)) cnt """
+ qt_sql """ select bitmap_has_any(to_bitmap(1),to_bitmap(1)) cnt """
+
+ // BITMAP_HAS_ALL
+ qt_sql """ select bitmap_has_all(bitmap_from_string("0, 1, 2"),
bitmap_from_string("1, 2")) cnt """
+ qt_sql """ select bitmap_has_all(bitmap_empty(), bitmap_from_string("1,
2")) cnt """
+
+ // BITMAP_HASH
+ qt_sql """ select bitmap_count(bitmap_hash('hello')) """
+ qt_sql """ select bitmap_count(bitmap_hash('')) """
+ qt_sql """ select bitmap_count(bitmap_hash(null)) """
+
+ // BITMAP_OR
+ qt_sql """ select bitmap_count(bitmap_or(to_bitmap(1), to_bitmap(2))) cnt
"""
+ qt_sql """ select bitmap_count(bitmap_or(to_bitmap(1), to_bitmap(1))) cnt
"""
+ qt_sql """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2)))
"""
+ qt_sql """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2),
to_bitmap(10), to_bitmap(0), NULL)) """
+ qt_sql """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2),
to_bitmap(10), to_bitmap(0), bitmap_empty())) """
+ qt_sql """ select bitmap_to_string(bitmap_or(to_bitmap(10),
bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'))) """
+
+ // bitmap_and_count
+ qt_sql """ select
bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_empty()) """
+ qt_sql """ select
bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_from_string('1,2,3')) """
+ qt_sql """ select
bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) """
+ qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'),
bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5')) """
+ qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'),
bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'),bitmap_empty()) """
+ qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'),
bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'), NULL) """
+
+ // bitmap_or_count
+ qt_sql """ select
bitmap_or_count(bitmap_from_string('1,2,3'),bitmap_empty()) """
+ qt_sql """ select
bitmap_or_count(bitmap_from_string('1,2,3'),bitmap_from_string('1,2,3'))"""
+ qt_sql """ select
bitmap_or_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) """
+ qt_sql """ select bitmap_or_count(bitmap_from_string('1,2,3'),
bitmap_from_string('3,4,5'), to_bitmap(100), bitmap_empty()) """
+ qt_sql """ select bitmap_or_count(bitmap_from_string('1,2,3'),
bitmap_from_string('3,4,5'), to_bitmap(100), NULL) """
+
+ // BITMAP_XOR
+ qt_sql """ select
bitmap_count(bitmap_xor(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4')))
cnt """
+ qt_sql """ select
bitmap_to_string(bitmap_xor(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4')))
"""
+ qt_sql """ select
bitmap_to_string(bitmap_xor(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5')))
"""
+ qt_sql """ select
bitmap_to_string(bitmap_xor(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'),bitmap_empty()))
"""
+ qt_sql """ select
bitmap_to_string(bitmap_xor(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'),NULL))
"""
+
+ // BITMAP_XOR_COUNT
+ qt_sql """ select
bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) """
+ qt_sql """ select
bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('1,2,3')) """
+ qt_sql """ select
bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('4,5,6')) """
+ qt_sql """ select
(bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5')))
"""
+ qt_sql """ select
(bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'),bitmap_empty()))
"""
+ qt_sql """ select
(bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'),NULL))
"""
+
+ // BITMAP_NOT
+ qt_sql """ select
bitmap_count(bitmap_not(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4')))
cnt """
+ qt_sql """ select
bitmap_to_string(bitmap_not(bitmap_from_string('2,3,5'),bitmap_from_string('1,2,3,4')))
"""
+
+ // BITMAP_AND_NOT
+ qt_sql """ select
bitmap_count(bitmap_and_not(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')))
cnt """
+
+ // BITMAP_AND_NOT_COUNT
+ qt_sql """ select
bitmap_and_not_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5'))
cnt """
+
+ // BITMAP_SUBSET_IN_RANGE
+ qt_sql """ select
bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,2,3,4,5'), 0, 9))
value """
+ qt_sql """ select
bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,2,3,4,5'), 2, 3))
value """
+
+ // BITMAP_SUBSET_LIMIT
+ qt_sql """ select
bitmap_to_string(bitmap_subset_limit(bitmap_from_string('1,2,3,4,5'), 0, 3))
value """
+ qt_sql """ select
bitmap_to_string(bitmap_subset_limit(bitmap_from_string('1,2,3,4,5'), 4, 3))
value """
+
+ // SUB_BITMAP
+ qt_sql """ select
bitmap_to_string(sub_bitmap(bitmap_from_string('1,0,1,2,3,1,5'), 0, 3)) value
"""
+ qt_sql """ select
bitmap_to_string(sub_bitmap(bitmap_from_string('1,0,1,2,3,1,5'), -3, 2)) value
"""
+ qt_sql """ select
bitmap_to_string(sub_bitmap(bitmap_from_string('1,0,1,2,3,1,5'), 2, 100)) value
"""
+
+ // BITMAP_TO_STRING
+ qt_sql """ select bitmap_to_string(null) """
+ qt_sql """ select bitmap_to_string(bitmap_empty()) """
+ qt_sql """ select bitmap_to_string(to_bitmap(1)) """
+ qt_sql """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2)))
"""
+
+ // BITMAP_UNION
+ def bitmapUnionTable = "test_bitmap_union"
+ sql """ DROP TABLE IF EXISTS ${bitmapUnionTable} """
+ sql """ create table ${bitmapUnionTable} (page_id int,user_id bitmap
bitmap_union) aggregate key (page_id) distributed by hash (page_id)
PROPERTIES("replication_num" = "1") """
+
+ sql """ insert into ${bitmapUnionTable} values(1, to_bitmap(1)); """
+ sql """ insert into ${bitmapUnionTable} values(1, to_bitmap(2)); """
+ sql """ insert into ${bitmapUnionTable} values(1, to_bitmap(3)); """
+ sql """ insert into ${bitmapUnionTable} values(2, to_bitmap(1)); """
+ sql """ insert into ${bitmapUnionTable} values(2, to_bitmap(2)); """
+
+ qt_sql """ select page_id, bitmap_union(user_id) from ${bitmapUnionTable}
group by page_id """
+ qt_sql """ select page_id, bitmap_count(bitmap_union(user_id)) from
${bitmapUnionTable} group by page_id """
+ qt_sql """ select page_id, count(distinct user_id) from
${bitmapUnionTable} group by page_id """
+
+ sql """ drop table ${bitmapUnionTable} """
+
+ // TO_BITMAP
+ qt_sql """ select bitmap_count(to_bitmap(10)) """
+ qt_sql """ select bitmap_to_string(to_bitmap(-1)) """
+
+ // BITMAP_MAX
+ qt_sql """ select bitmap_max(bitmap_from_string('')) value; """
+ qt_sql """ select bitmap_max(bitmap_from_string('1,9999999999')) value """
+
+}
diff --git
a/regression-test/suites/query/sql_functions/cast_function/test_cast_function.groovy
b/regression-test/suites/query/sql_functions/cast_function/test_cast_function.groovy
new file mode 100644
index 0000000000..cc62493b95
--- /dev/null
+++
b/regression-test/suites/query/sql_functions/cast_function/test_cast_function.groovy
@@ -0,0 +1,25 @@
+// 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("test_cast_function", "query") {
+ sql """ SET enable_vectorized_engine = TRUE; """
+
+ qt_sql """ select cast (1 as BIGINT) """
+ qt_sql """ select cast(cast ("11.2" as double) as bigint) """
+
+}
+
diff --git
a/regression-test/suites/query/sql_functions/digital-masking/test_digital_masking.groovy
b/regression-test/suites/query/sql_functions/digital-masking/test_digital_masking.groovy
new file mode 100644
index 0000000000..0d102a3ac4
--- /dev/null
+++
b/regression-test/suites/query/sql_functions/digital-masking/test_digital_masking.groovy
@@ -0,0 +1,24 @@
+// 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("test_digital_masking", "query") {
+ sql """ SET enable_vectorized_engine = TRUE; """
+
+ qt_sql """ select digital_masking(13812345678) """
+
+}
+
diff --git
a/regression-test/suites/query/sql_functions/window_functions/test_window_function.groovy
b/regression-test/suites/query/sql_functions/window_functions/test_window_function.groovy
new file mode 100644
index 0000000000..69e4a4726c
--- /dev/null
+++
b/regression-test/suites/query/sql_functions/window_functions/test_window_function.groovy
@@ -0,0 +1,158 @@
+// 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("test_window_function", "query") {
+ sql """ SET enable_vectorized_engine = TRUE; """
+
+ def windowFunctionTable1 = "test_window_function1"
+ sql """ DROP TABLE IF EXISTS ${windowFunctionTable1} """
+ sql """ create table ${windowFunctionTable1} (stock_symbol varchar(64),
closing_price decimal(8,2), closing_date datetime) duplicate key (stock_symbol)
distributed by hash (stock_symbol) PROPERTIES("replication_num" = "1") """
+
+ sql """ INSERT INTO ${windowFunctionTable1} VALUES
('JDR',12.86,'2014-10-02 00:00:00'),('JDR',12.89,'2014-10-03
00:00:00'),('JDR',12.94,'2014-10-04 00:00:00'),('JDR',12.55,'2014-10-05
00:00:00'),('JDR',14.03,'2014-10-06 00:00:00'),('JDR',14.75,'2014-10-07
00:00:00'),('JDR',13.98,'2014-10-08 00:00:00') """
+
+ qt_sql """
+ SELECT
+ stock_symbol,
+ closing_date,
+ closing_price,
+ avg( closing_price ) over ( PARTITION BY stock_symbol ORDER BY
closing_date rows BETWEEN 1 preceding AND 1 following ) AS moving_average
+ FROM
+ ${windowFunctionTable1}
+ """
+ // LEAD
+ qt_sql """
+ SELECT
+ stock_symbol,
+ closing_date,
+ closing_price,
+ CASE ( lead( closing_price, 1, 0 ) over ( PARTITION BY
stock_symbol ORDER BY closing_date )- closing_price ) > 0
+ WHEN TRUE THEN "higher"
+ WHEN FALSE THEN "flat or lower" END AS "trending"
+ FROM
+ ${windowFunctionTable1}
+ ORDER BY
+ closing_date;
+ """
+ // LAG
+ qt_sql """
+ SELECT
+ stock_symbol,
+ closing_date,
+ closing_price,
+ lag( closing_price, 1, 0 ) over ( PARTITION BY stock_symbol
ORDER BY closing_date ) AS "yesterday closing"
+ FROM
+ ${windowFunctionTable1}
+ ORDER BY
+ closing_date;
+ """
+ sql """ drop table ${windowFunctionTable1} """
+
+
+ def windowFunctionTable2 = "test_window_function2"
+ sql """ DROP TABLE IF EXISTS ${windowFunctionTable2} """
+ sql """ create table ${windowFunctionTable2} (x int, property
varchar(64)) duplicate key (x) distributed by hash (x)
PROPERTIES("replication_num" = "1") """
+ sql """ insert into ${windowFunctionTable2} values
(2,'even'),(4,'even'),(6,'even'),(8,'even'),(10,'even'),(1,'odd'),(3,'odd'),(5,'odd'),(7,'odd'),(9,'odd');
"""
+
+ // SUM
+ qt_sql """
+ SELECT
+ x,
+ property,
+ sum( x ) over ( PARTITION BY property ORDER BY x rows
BETWEEN 1 preceding AND 1 following ) AS 'moving total'
+ FROM
+ ${windowFunctionTable2}
+ WHERE
+ property IN ( 'odd', 'even' );
+ """
+ // AVG
+ qt_sql """
+ SELECT
+ x,
+ property,
+ avg( x ) over ( PARTITION BY property ORDER BY x rows
BETWEEN 1 preceding AND 1 following ) AS 'moving average'
+ FROM
+ ${windowFunctionTable2}
+ WHERE
+ property IN ( 'odd', 'even' );
+ """
+ // COUNT
+ qt_sql """
+ SELECT
+ x,
+ property,
+ count( x ) over ( PARTITION BY property ORDER BY x rows
BETWEEN unbounded preceding AND current ROW ) AS 'cumulative total'
+ FROM
+ ${windowFunctionTable2}
+ WHERE
+ property IN ( 'odd', 'even' );
+ """
+ sql """ truncate table ${windowFunctionTable2} """
+ sql """ insert into ${windowFunctionTable2} values
(2,'even'),(4,'even'),(6,'even'),(8,'even'),(10,'even'),(1,'odd'),(3,'odd'),(5,'odd'),(7,'odd'),(9,'odd');
"""
+
+ // MIN
+ qt_sql """
+ SELECT
+ x,
+ property,
+ min( x ) over ( ORDER BY property, x DESC rows BETWEEN
unbounded preceding AND 1 following ) AS 'local minimum'
+ FROM
+ ${windowFunctionTable2}
+ WHERE
+ property IN ( 'prime', 'square' );
+ """
+ // MAX
+ qt_sql """
+ SELECT
+ x,
+ property,
+ max( x ) over ( ORDER BY property, x rows BETWEEN unbounded
preceding AND 1 following ) AS 'local maximum'
+ FROM
+ ${windowFunctionTable2}
+ WHERE
+ property IN ( 'prime', 'square' );
+ """
+ sql """ drop table ${windowFunctionTable2} """
+
+
+ def windowFunctionTable3 = "test_window_function3"
+ sql """ DROP TABLE IF EXISTS ${windowFunctionTable3} """
+ sql """ create table ${windowFunctionTable3} (x int, y int) duplicate key
(x) distributed by hash (x) PROPERTIES("replication_num" = "1") """
+ sql """ insert into ${windowFunctionTable3} values
(1,1),(1,2),(1,2),(2,1),(2,2),(2,3),(3,1),(3,1),(3,2); """
+
+ // RANK
+ qt_sql """ select x, y, rank() over(partition by x order by y) as rank
from ${windowFunctionTable3} ; """
+ // DENSE_RANK
+ qt_sql """ select x, y, dense_rank() over(partition by x order by y) as
rank from ${windowFunctionTable3} ; """
+ // ROW_NUMBER
+ qt_sql """ select x, y, row_number() over(partition by x order by y) as
rank from ${windowFunctionTable3} ; """
+
+ sql """ drop table ${windowFunctionTable3} """
+
+
+ def windowFunctionTable4 = "test_window_function4"
+ sql """ DROP TABLE IF EXISTS ${windowFunctionTable4} """
+ sql """ create table ${windowFunctionTable4} (name varchar(64),country
varchar(64),greeting varchar(64)) duplicate key (name) distributed by hash
(name) PROPERTIES("replication_num" = "1") """
+ sql """ insert into ${windowFunctionTable4} VALUES
('Pete','USA','Hello'),('John','USA','Hi'),('Boris','Germany','Guten
tag'),('Michael','Germany','Guten
morgen'),('Bjorn','Sweden','Hej'),('Mats','Sweden','Tja')"""
+
+ // first_value
+ qt_sql """ select country, name,first_value(greeting) over (partition by
country order by name, greeting) as greeting from ${windowFunctionTable4}; """
+ // last_value
+ qt_sql """ select country, name,last_value(greeting) over (partition by
country order by name, greeting) as greeting from ${windowFunctionTable4} ; """
+
+ sql """ drop table ${windowFunctionTable4} """
+}
+
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
