You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2022/05/30 01:43:05 UTC
[incubator-doris] branch master updated: [feature](function) Add nvl function (#9726)
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 7b98dd438d [feature](function) Add nvl function (#9726)
7b98dd438d is described below
commit 7b98dd438d246701770b5e5c44e51b479222b0ac
Author: Jing Shen <sh...@bytedance.com>
AuthorDate: Mon May 30 09:43:00 2022 +0800
[feature](function) Add nvl function (#9726)
---
be/src/exprs/expr.cpp | 3 +-
be/src/vec/functions/function_ifnull.cpp | 1 +
be/test/vec/function/function_ifnull_test.cpp | 8 ++++
docs/.vuepress/sidebar/en.js | 1 +
docs/.vuepress/sidebar/zh-CN.js | 1 +
.../sql-functions/conditional-functions/nvl.md | 54 ++++++++++++++++++++++
.../sql-functions/conditional-functions/nvl.md | 54 ++++++++++++++++++++++
.../java/org/apache/doris/rewrite/FEFunctions.java | 14 ++++++
gensrc/script/doris_builtins_functions.py | 33 ++++++-------
.../conditional_functions/test_nvl.out | 6 +++
.../conditional_functions/test_nvl.groovy | 11 +----
11 files changed, 160 insertions(+), 26 deletions(-)
diff --git a/be/src/exprs/expr.cpp b/be/src/exprs/expr.cpp
index ab65adb6e7..cda5bdec88 100644
--- a/be/src/exprs/expr.cpp
+++ b/be/src/exprs/expr.cpp
@@ -359,7 +359,8 @@ Status Expr::create_expr(ObjectPool* pool, const TExprNode& texpr_node, Expr** e
*expr = pool->add(new IfExpr(texpr_node));
} else if (texpr_node.fn.name.function_name == "nullif") {
*expr = pool->add(new NullIfExpr(texpr_node));
- } else if (texpr_node.fn.name.function_name == "ifnull") {
+ } else if (texpr_node.fn.name.function_name == "ifnull" ||
+ texpr_node.fn.name.function_name == "nvl") {
*expr = pool->add(new IfNullExpr(texpr_node));
} else if (texpr_node.fn.name.function_name == "coalesce") {
*expr = pool->add(new CoalesceExpr(texpr_node));
diff --git a/be/src/vec/functions/function_ifnull.cpp b/be/src/vec/functions/function_ifnull.cpp
index 341745693e..bbd66e51e4 100644
--- a/be/src/vec/functions/function_ifnull.cpp
+++ b/be/src/vec/functions/function_ifnull.cpp
@@ -23,5 +23,6 @@
namespace doris::vectorized {
void register_function_function_ifnull(SimpleFunctionFactory& factory) {
factory.register_function<FunctionIfNull>();
+ factory.register_alias(FunctionIfNull::name, "nvl");
}
} // namespace doris::vectorized
\ No newline at end of file
diff --git a/be/test/vec/function/function_ifnull_test.cpp b/be/test/vec/function/function_ifnull_test.cpp
index f7507de3e5..0264d79a05 100644
--- a/be/test/vec/function/function_ifnull_test.cpp
+++ b/be/test/vec/function/function_ifnull_test.cpp
@@ -35,6 +35,14 @@ TEST(IfNullTest, Int_Test) {
check_function<DataTypeInt32, true>(func_name, input_types, data_set);
}
+TEST(NvlTest, Int_Test) {
+ std::string func_name = "nvl";
+ InputTypeSet input_types = {TypeIndex::Int32, TypeIndex::Int32};
+ DataSet data_set = {{{4, 10}, 4}, {{-4, 10}, -4}, {{Null(), 5}, 5}};
+
+ check_function<DataTypeInt32, true>(func_name, input_types, data_set);
+}
+
TEST(IfNullTest, Float_Test) {
std::string func_name = "ifnull";
InputTypeSet input_types = {TypeIndex::Float64, TypeIndex::Float64};
diff --git a/docs/.vuepress/sidebar/en.js b/docs/.vuepress/sidebar/en.js
index 48b27573cd..e405848ab5 100644
--- a/docs/.vuepress/sidebar/en.js
+++ b/docs/.vuepress/sidebar/en.js
@@ -473,6 +473,7 @@ module.exports = [
"coalesce",
"if",
"ifnull",
+ "nvl",
"nullif"
],
},
diff --git a/docs/.vuepress/sidebar/zh-CN.js b/docs/.vuepress/sidebar/zh-CN.js
index d52f9480b0..6d5b9c4d63 100644
--- a/docs/.vuepress/sidebar/zh-CN.js
+++ b/docs/.vuepress/sidebar/zh-CN.js
@@ -473,6 +473,7 @@ module.exports = [
"coalesce",
"if",
"ifnull",
+ "nvl",
"nullif"
],
},
diff --git a/docs/en/sql-manual/sql-functions/conditional-functions/nvl.md b/docs/en/sql-manual/sql-functions/conditional-functions/nvl.md
new file mode 100644
index 0000000000..18ddc2e295
--- /dev/null
+++ b/docs/en/sql-manual/sql-functions/conditional-functions/nvl.md
@@ -0,0 +1,54 @@
+---
+{
+ "title": "nvl",
+ "language": "en"
+}
+---
+
+<!--
+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.
+-->
+
+## nvl
+### description
+#### Syntax
+
+`nvl(expr1, expr2)`
+
+
+If the value of expr1 is not null, expr1 is returned, otherwise expr2 is returned
+
+### example
+
+```
+mysql> select nvl(1,0);
++--------------+
+| nvl(1, 0) |
++--------------+
+| 1 |
++--------------+
+
+mysql> select nvl(null,10);
++------------------+
+| nvl(NULL, 10) |
++------------------+
+| 10 |
++------------------+
+```
+### keywords
+NVL
diff --git a/docs/zh-CN/sql-manual/sql-functions/conditional-functions/nvl.md b/docs/zh-CN/sql-manual/sql-functions/conditional-functions/nvl.md
new file mode 100644
index 0000000000..4789523f9c
--- /dev/null
+++ b/docs/zh-CN/sql-manual/sql-functions/conditional-functions/nvl.md
@@ -0,0 +1,54 @@
+---
+{
+ "title": "nvl",
+ "language": "zh-CN"
+}
+---
+
+<!--
+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.
+-->
+
+## nvl
+### description
+#### Syntax
+
+`nvl(expr1, expr2)`
+
+
+如果 expr1 的值不为 NULL 则返回 expr1，否则返回 expr2
+
+### example
+
+```
+mysql> select nvl(1,0);
++--------------+
+| nvl(1, 0) |
++--------------+
+| 1 |
++--------------+
+
+mysql> select nvl(null,10);
++------------------+
+| nvl(NULL, 10) |
++------------------+
+| 10 |
++------------------+
+```
+### keywords
+NVL
diff --git a/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java b/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java
index 795fa447de..1f9f695a34 100755
--- a/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java
@@ -545,6 +545,20 @@ public class FEFunctions {
return first instanceof NullLiteral ? second : first;
}
+ // maybe use alias info to reduce redundant code
+ @FEFunctionList({
+ @FEFunction(name = "nvl", argTypes = {"VARCHAR", "VARCHAR"}, returnType = "VARCHAR"),
+ @FEFunction(name = "nvl", argTypes = {"TINYINT", "TINYINT"}, returnType = "TINYINT"),
+ @FEFunction(name = "nvl", argTypes = {"INT", "INT"}, returnType = "INT"),
+ @FEFunction(name = "nvl", argTypes = {"BIGINT", "BIGINT"}, returnType = "BIGINT"),
+ @FEFunction(name = "nvl", argTypes = {"DATETIME", "DATETIME"}, returnType = "DATETIME"),
+ @FEFunction(name = "nvl", argTypes = { "DATE", "DATETIME" }, returnType = "DATETIME"),
+ @FEFunction(name = "nvl", argTypes = { "DATETIME", "DATE" }, returnType = "DATETIME")
+ })
+ public static LiteralExpr nvl(LiteralExpr first, LiteralExpr second) throws AnalysisException {
+ return first instanceof NullLiteral ? second : first;
+ }
+
@FEFunctionList({
@FEFunction(name = "array", argTypes = {"INT"}, returnType = "ARRAY"),
@FEFunction(name = "array", argTypes = {"VARCHAR"}, returnType = "ARRAY")
diff --git a/gensrc/script/doris_builtins_functions.py b/gensrc/script/doris_builtins_functions.py
index 1831e09cf6..2bf9802a83 100755
--- a/gensrc/script/doris_builtins_functions.py
+++ b/gensrc/script/doris_builtins_functions.py
@@ -825,23 +825,23 @@ visible_functions = [
[['nullif'], 'VARCHAR', ['VARCHAR', 'VARCHAR'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
[['nullif'], 'STRING', ['STRING', 'STRING'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
- [['ifnull'], 'BOOLEAN', ['BOOLEAN', 'BOOLEAN'], '', '', '', 'vec', 'CUSTOM'],
- [['ifnull'], 'TINYINT', ['TINYINT', 'TINYINT'], '', '', '', 'vec', 'CUSTOM'],
- [['ifnull'], 'SMALLINT', ['SMALLINT', 'SMALLINT'], '', '', '', 'vec', 'CUSTOM'],
- [['ifnull'], 'INT', ['INT', 'INT'], '', '', '', 'vec', 'CUSTOM'],
- [['ifnull'], 'BIGINT', ['BIGINT', 'BIGINT'], '', '', '', 'vec', 'CUSTOM'],
- [['ifnull'], 'LARGEINT', ['LARGEINT', 'LARGEINT'], '', '', '', 'vec', 'CUSTOM'],
- [['ifnull'], 'FLOAT', ['FLOAT', 'FLOAT'], '', '', '', 'vec', 'CUSTOM'],
- [['ifnull'], 'DOUBLE', ['DOUBLE', 'DOUBLE'], '', '', '', 'vec', 'CUSTOM'],
- [['ifnull'], 'DATE', ['DATE', 'DATE'], '', '', '', 'vec', 'CUSTOM'],
- [['ifnull'], 'DATETIME', ['DATETIME', 'DATETIME'], '', '', '', 'vec', 'CUSTOM'],
- [['ifnull'], 'DATETIME', ['DATE', 'DATETIME'], '', '', '', 'vec', 'CUSTOM'],
- [['ifnull'], 'DATETIME', ['DATETIME', 'DATE'], '', '', '', 'vec', 'CUSTOM'],
- [['ifnull'], 'DECIMALV2', ['DECIMALV2', 'DECIMALV2'], '', '', '', 'vec', 'CUSTOM'],
- [['ifnull'], 'BITMAP', ['BITMAP', 'BITMAP'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'BOOLEAN', ['BOOLEAN', 'BOOLEAN'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'TINYINT', ['TINYINT', 'TINYINT'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'SMALLINT', ['SMALLINT', 'SMALLINT'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'INT', ['INT', 'INT'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'BIGINT', ['BIGINT', 'BIGINT'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'LARGEINT', ['LARGEINT', 'LARGEINT'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'FLOAT', ['FLOAT', 'FLOAT'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'DOUBLE', ['DOUBLE', 'DOUBLE'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'DATE', ['DATE', 'DATE'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'DATETIME', ['DATETIME', 'DATETIME'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'DATETIME', ['DATE', 'DATETIME'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'DATETIME', ['DATETIME', 'DATE'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'DECIMALV2', ['DECIMALV2', 'DECIMALV2'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'BITMAP', ['BITMAP', 'BITMAP'], '', '', '', 'vec', 'CUSTOM'],
# The priority of varchar should be lower than decimal in IS_SUPERTYPE_OF mode.
- [['ifnull'], 'VARCHAR', ['VARCHAR', 'VARCHAR'], '', '', '', 'vec', 'CUSTOM'],
- [['ifnull'], 'STRING', ['STRING', 'STRING'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'VARCHAR', ['VARCHAR', 'VARCHAR'], '', '', '', 'vec', 'CUSTOM'],
+ [['ifnull', 'nvl'], 'STRING', ['STRING', 'STRING'], '', '', '', 'vec', 'CUSTOM'],
[['coalesce'], 'BOOLEAN', ['BOOLEAN', '...'], '', '', '', 'vec', 'CUSTOM'],
[['coalesce'], 'TINYINT', ['TINYINT', '...'], '', '', '', 'vec', 'CUSTOM'],
@@ -1460,6 +1460,7 @@ non_null_result_with_null_param_functions = [
'hll_hash',
'concat_ws',
'ifnull',
+ 'nvl',
'nullif',
'null_or_empty',
'coalesce',
diff --git a/regression-test/data/query/sql_functions/conditional_functions/test_nvl.out b/regression-test/data/query/sql_functions/conditional_functions/test_nvl.out
new file mode 100644
index 0000000000..5c39db61b8
--- /dev/null
+++ b/regression-test/data/query/sql_functions/conditional_functions/test_nvl.out
@@ -0,0 +1,6 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !select --
+true
+false
+false
+
diff --git a/be/src/vec/functions/function_ifnull.cpp b/regression-test/suites/query/sql_functions/conditional_functions/test_nvl.groovy
similarity index 69%
copy from be/src/vec/functions/function_ifnull.cpp
copy to regression-test/suites/query/sql_functions/conditional_functions/test_nvl.groovy
index 341745693e..978d2c7178 100644
--- a/be/src/vec/functions/function_ifnull.cpp
+++ b/regression-test/suites/query/sql_functions/conditional_functions/test_nvl.groovy
@@ -14,14 +14,7 @@
// KIND, either express or implied. See the License for the
// specific language governing permissions and limitations
// under the License.
-// This file is copied from
-// https://github.com/ClickHouse/ClickHouse/blob/master/src/Functions/Ifnull.cpp
-// and modified by Doris
-#include "function_ifnull.h"
-
-namespace doris::vectorized {
-void register_function_function_ifnull(SimpleFunctionFactory& factory) {
- factory.register_function<FunctionIfNull>();
+suite("test_nvl", "query") {
+ qt_select "select nvl(k6, \"false\") k from test_query_db.test order by k1"
}
-} // namespace doris::vectorized
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org