You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by pa...@apache.org on 2023/08/05 04:53:20 UTC
[doris] branch master updated: [BUG](date_trunc) fix date_trunc function only handle lower string (#22602)
This is an automated email from the ASF dual-hosted git repository.
panxiaolei 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 d3b50e3b2a [BUG](date_trunc) fix date_trunc function only handle lower string (#22602)
d3b50e3b2a is described below
commit d3b50e3b2a00c5a593eeee005af5e6a1ccab1271
Author: zhangstar333 <87...@users.noreply.github.com>
AuthorDate: Sat Aug 5 12:53:13 2023 +0800
[BUG](date_trunc) fix date_trunc function only handle lower string (#22602)
fix date_trunc function only handle lower string
---
be/src/vec/functions/function_timestamp.cpp | 6 +-
.../apache/doris/analysis/FunctionCallExpr.java | 14 ++
.../expressions/functions/scalar/DateTrunc.java | 17 +++
.../data/nereids_function_p0/scalar_function/D.out | 104 +++++++--------
.../datetime_functions/test_date_trunc.out | 145 +++++++++++++++++++++
.../nereids_function_p0/scalar_function/D.groovy | 8 +-
.../datetime_functions/test_date_trunc.groovy | 74 +++++++++++
7 files changed, 310 insertions(+), 58 deletions(-)
diff --git a/be/src/vec/functions/function_timestamp.cpp b/be/src/vec/functions/function_timestamp.cpp
index 64bc8d4e1f..a71062c483 100644
--- a/be/src/vec/functions/function_timestamp.cpp
+++ b/be/src/vec/functions/function_timestamp.cpp
@@ -423,10 +423,12 @@ private:
const StringRef& rdata, PaddedPODArray<ArgType>& res,
NullMap& null_map, size_t input_rows_count) {
res.resize(input_rows_count);
+ std::string lower_str(rdata.data, rdata.size);
+ std::transform(lower_str.begin(), lower_str.end(), lower_str.begin(),
+ [](unsigned char c) { return std::tolower(c); });
for (size_t i = 0; i < input_rows_count; ++i) {
auto dt = binary_cast<ArgType, DateValueType>(ldata[i]);
- const char* str_data = rdata.data;
- _execute_inner_loop(dt, str_data, res, null_map, i);
+ _execute_inner_loop(dt, lower_str.data(), res, null_map, i);
}
}
template <typename T>
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
index eff9e06aa9..7fbbfbadf4 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
@@ -1536,6 +1536,20 @@ public class FunctionCallExpr extends Expr {
.toSql());
}
}
+ if (fnName.getFunction().equalsIgnoreCase("date_trunc")) {
+ if ((children.size() != 2) || (getChild(1).isConstant() == false)
+ || !(getChild(1) instanceof StringLiteral)) {
+ throw new AnalysisException(
+ fnName.getFunction() + " needs two params, and the second is must be a string constant: "
+ + this.toSql());
+ }
+ final String constParam = ((StringLiteral) getChild(1)).getValue().toLowerCase();
+ if (!Lists.newArrayList("year", "quarter", "month", "week", "day", "hour", "minute", "second")
+ .contains(constParam)) {
+ throw new AnalysisException("date_trunc function second param only support argument is "
+ + "year|quarter|month|week|day|hour|minute|second");
+ }
+ }
if (fnName.getFunction().equalsIgnoreCase("char")) {
if (!getChild(0).isConstant()) {
throw new AnalysisException(
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateTrunc.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateTrunc.java
index cf492ffc5a..53c24ff37d 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateTrunc.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateTrunc.java
@@ -18,9 +18,11 @@
package org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
+import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.functions.AlwaysNullable;
import org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
+import org.apache.doris.nereids.trees.expressions.literal.VarcharLiteral;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.types.DateTimeType;
@@ -29,6 +31,7 @@ import org.apache.doris.nereids.types.VarcharType;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Lists;
import java.util.List;
@@ -51,6 +54,20 @@ public class DateTrunc extends ScalarFunction
super("date_trunc", arg0, arg1);
}
+ @Override
+ public void checkLegalityBeforeTypeCoercion() {
+ if (getArgument(1).isConstant() == false || !(getArgument(1) instanceof VarcharLiteral)) {
+ throw new AnalysisException("the second parameter of "
+ + getName() + " function must be a string constant: " + toSql());
+ }
+ final String constParam = ((VarcharLiteral) getArgument(1)).getStringValue().toLowerCase();
+ if (!Lists.newArrayList("year", "quarter", "month", "week", "day", "hour", "minute", "second")
+ .contains(constParam)) {
+ throw new AnalysisException("date_trunc function second param only support argument is"
+ + "year|quarter|month|week|day|hour|minute|second");
+ }
+ }
+
/**
* withChildren.
*/
diff --git a/regression-test/data/nereids_function_p0/scalar_function/D.out b/regression-test/data/nereids_function_p0/scalar_function/D.out
index d18b99ba8a..7814c1c6f9 100644
--- a/regression-test/data/nereids_function_p0/scalar_function/D.out
+++ b/regression-test/data/nereids_function_p0/scalar_function/D.out
@@ -175,61 +175,61 @@
-- !sql_date_trunc_DateTime_Varchar --
\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
+2012-01-01T00:00
+2012-01-01T00:00
+2012-01-01T00:00
+2012-01-01T00:00
+2012-01-01T00:00
+2012-01-01T00:00
+2012-01-01T00:00
+2012-01-01T00:00
+2012-01-01T00:00
+2012-01-01T00:00
+2012-01-01T00:00
+2012-01-01T00:00
-- !sql_date_trunc_DateTime_Varchar_notnull --
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
+2012-03-01T00:00
+2012-03-01T00:00
+2012-03-01T00:00
+2012-03-01T00:00
+2012-03-01T00:00
+2012-03-01T00:00
+2012-03-01T00:00
+2012-03-01T00:00
+2012-03-01T00:00
+2012-03-01T00:00
+2012-03-01T00:00
+2012-03-01T00:00
-- !sql_date_trunc_DateTimeV2_Varchar --
\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
+2012-03-01T00:00
+2012-03-02T00:00
+2012-03-03T00:00
+2012-03-04T00:00
+2012-03-05T00:00
+2012-03-06T00:00
+2012-03-07T00:00
+2012-03-08T00:00
+2012-03-09T00:00
+2012-03-10T00:00
+2012-03-11T00:00
+2012-03-12T00:00
-- !sql_date_trunc_DateTimeV2_Varchar_notnull --
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
-\N
+2012-03-01T01:00
+2012-03-02T02:00
+2012-03-03T03:00
+2012-03-04T04:00
+2012-03-05T05:00
+2012-03-06T06:00
+2012-03-07T07:00
+2012-03-08T08:00
+2012-03-09T09:00
+2012-03-10T10:00
+2012-03-11T11:00
+2012-03-12T12:00
-- !sql_datediff_DateTime_DateTime --
\N
@@ -2761,12 +2761,12 @@ Monday
-0.5228787452803376
-0.3979400086720376
-0.3010299956639812
--0.2218487496163564
+-0.22184874961635637
-0.1549019599857432
-0.09691001300805639
-0.045757490560675115
0.0
-0.04139268515822508
+0.04139268515822507
0.07918124604762482
-- !sql_dlog10_Double_notnull --
@@ -2775,12 +2775,12 @@ Monday
-0.5228787452803376
-0.3979400086720376
-0.3010299956639812
--0.2218487496163564
+-0.22184874961635637
-0.1549019599857432
-0.09691001300805639
-0.045757490560675115
0.0
-0.04139268515822508
+0.04139268515822507
0.07918124604762482
-- !sql_domain_String --
diff --git a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_trunc.out b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_trunc.out
new file mode 100644
index 0000000000..cc78cac91f
--- /dev/null
+++ b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_trunc.out
@@ -0,0 +1,145 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !select_date_trunc_second --
+\N \N
+1989-03-21T13:00 1989-03-21T13:00
+1989-03-21T13:00 1989-03-21T13:00
+2000-01-01T00:00 2000-01-01T00:00
+2015-03-13T10:30 2015-03-13T10:30
+2015-03-13T12:36:38 2015-03-13T12:36:38
+2015-03-13T12:36:38 2015-03-13T12:36:38
+1901-01-01T00:00 1901-01-01T00:00
+9999-11-11T12:12 9999-11-11T12:12
+1989-03-21T13:11 1989-03-21T13:11
+2013-04-02T15:16:52 2013-04-02T15:16:52
+1989-03-21T13:11 1989-03-21T13:11
+2013-04-02T15:16:52 2013-04-02T15:16:52
+2015-04-02T00:00 2015-04-02T00:00
+2015-04-02T00:00 2015-04-02T00:00
+2015-04-02T00:00 2015-04-02T00:00
+
+-- !select_date_trunc_minute --
+\N \N
+1989-03-21T13:00 1989-03-21T13:00
+1989-03-21T13:00 1989-03-21T13:00
+2000-01-01T00:00 2000-01-01T00:00
+2015-03-13T10:30 2015-03-13T10:30
+2015-03-13T12:36:38 2015-03-13T12:36
+2015-03-13T12:36:38 2015-03-13T12:36
+1901-01-01T00:00 1901-01-01T00:00
+9999-11-11T12:12 9999-11-11T12:12
+1989-03-21T13:11 1989-03-21T13:11
+2013-04-02T15:16:52 2013-04-02T15:16
+1989-03-21T13:11 1989-03-21T13:11
+2013-04-02T15:16:52 2013-04-02T15:16
+2015-04-02T00:00 2015-04-02T00:00
+2015-04-02T00:00 2015-04-02T00:00
+2015-04-02T00:00 2015-04-02T00:00
+
+-- !select_date_trunc_hour --
+\N \N
+1989-03-21T13:00 1989-03-21T13:00
+1989-03-21T13:00 1989-03-21T13:00
+2000-01-01T00:00 2000-01-01T00:00
+2015-03-13T10:30 2015-03-13T10:00
+2015-03-13T12:36:38 2015-03-13T12:00
+2015-03-13T12:36:38 2015-03-13T12:00
+1901-01-01T00:00 1901-01-01T00:00
+9999-11-11T12:12 9999-11-11T12:00
+1989-03-21T13:11 1989-03-21T13:00
+2013-04-02T15:16:52 2013-04-02T15:00
+1989-03-21T13:11 1989-03-21T13:00
+2013-04-02T15:16:52 2013-04-02T15:00
+2015-04-02T00:00 2015-04-02T00:00
+2015-04-02T00:00 2015-04-02T00:00
+2015-04-02T00:00 2015-04-02T00:00
+
+-- !select_date_trunc_day --
+\N \N
+1989-03-21T13:00 1989-03-21T00:00
+1989-03-21T13:00 1989-03-21T00:00
+2000-01-01T00:00 2000-01-01T00:00
+2015-03-13T10:30 2015-03-13T00:00
+2015-03-13T12:36:38 2015-03-13T00:00
+2015-03-13T12:36:38 2015-03-13T00:00
+1901-01-01T00:00 1901-01-01T00:00
+9999-11-11T12:12 9999-11-11T00:00
+1989-03-21T13:11 1989-03-21T00:00
+2013-04-02T15:16:52 2013-04-02T00:00
+1989-03-21T13:11 1989-03-21T00:00
+2013-04-02T15:16:52 2013-04-02T00:00
+2015-04-02T00:00 2015-04-02T00:00
+2015-04-02T00:00 2015-04-02T00:00
+2015-04-02T00:00 2015-04-02T00:00
+
+-- !select_date_trunc_week --
+\N \N
+1989-03-21T13:00 1989-03-20T00:00
+1989-03-21T13:00 1989-03-20T00:00
+2000-01-01T00:00 1999-12-27T00:00
+2015-03-13T10:30 2015-03-09T00:00
+2015-03-13T12:36:38 2015-03-09T00:00
+2015-03-13T12:36:38 2015-03-09T00:00
+1901-01-01T00:00 1900-12-31T00:00
+9999-11-11T12:12 9999-11-08T00:00
+1989-03-21T13:11 1989-03-20T00:00
+2013-04-02T15:16:52 2013-04-01T00:00
+1989-03-21T13:11 1989-03-20T00:00
+2013-04-02T15:16:52 2013-04-01T00:00
+2015-04-02T00:00 2015-03-30T00:00
+2015-04-02T00:00 2015-03-30T00:00
+2015-04-02T00:00 2015-03-30T00:00
+
+-- !select_date_trunc_month --
+\N \N
+1989-03-21T13:00 1989-03-01T00:00
+1989-03-21T13:00 1989-03-01T00:00
+2000-01-01T00:00 2000-01-01T00:00
+2015-03-13T10:30 2015-03-01T00:00
+2015-03-13T12:36:38 2015-03-01T00:00
+2015-03-13T12:36:38 2015-03-01T00:00
+1901-01-01T00:00 1901-01-01T00:00
+9999-11-11T12:12 9999-11-01T00:00
+1989-03-21T13:11 1989-03-01T00:00
+2013-04-02T15:16:52 2013-04-01T00:00
+1989-03-21T13:11 1989-03-01T00:00
+2013-04-02T15:16:52 2013-04-01T00:00
+2015-04-02T00:00 2015-04-01T00:00
+2015-04-02T00:00 2015-04-01T00:00
+2015-04-02T00:00 2015-04-01T00:00
+
+-- !select_date_trunc_quarter --
+\N \N
+1989-03-21T13:00 1989-01-01T00:00
+1989-03-21T13:00 1989-01-01T00:00
+2000-01-01T00:00 2000-01-01T00:00
+2015-03-13T10:30 2015-01-01T00:00
+2015-03-13T12:36:38 2015-01-01T00:00
+2015-03-13T12:36:38 2015-01-01T00:00
+1901-01-01T00:00 1901-01-01T00:00
+9999-11-11T12:12 9999-10-01T00:00
+1989-03-21T13:11 1989-01-01T00:00
+2013-04-02T15:16:52 2013-04-01T00:00
+1989-03-21T13:11 1989-01-01T00:00
+2013-04-02T15:16:52 2013-04-01T00:00
+2015-04-02T00:00 2015-04-01T00:00
+2015-04-02T00:00 2015-04-01T00:00
+2015-04-02T00:00 2015-04-01T00:00
+
+-- !select_date_trunc_year --
+\N \N
+1989-03-21T13:00 1989-01-01T00:00
+1989-03-21T13:00 1989-01-01T00:00
+2000-01-01T00:00 2000-01-01T00:00
+2015-03-13T10:30 2015-01-01T00:00
+2015-03-13T12:36:38 2015-01-01T00:00
+2015-03-13T12:36:38 2015-01-01T00:00
+1901-01-01T00:00 1901-01-01T00:00
+9999-11-11T12:12 9999-01-01T00:00
+1989-03-21T13:11 1989-01-01T00:00
+2013-04-02T15:16:52 2013-01-01T00:00
+1989-03-21T13:11 1989-01-01T00:00
+2013-04-02T15:16:52 2013-01-01T00:00
+2015-04-02T00:00 2015-01-01T00:00
+2015-04-02T00:00 2015-01-01T00:00
+2015-04-02T00:00 2015-01-01T00:00
+
diff --git a/regression-test/suites/nereids_function_p0/scalar_function/D.groovy b/regression-test/suites/nereids_function_p0/scalar_function/D.groovy
index 6b179db073..219680bf2f 100644
--- a/regression-test/suites/nereids_function_p0/scalar_function/D.groovy
+++ b/regression-test/suites/nereids_function_p0/scalar_function/D.groovy
@@ -33,10 +33,10 @@ suite("nereids_scalar_fn_D") {
qt_sql_date_format_DateTimeV2_Varchar_notnull "select date_format(kdtmv2s1, '2006-01-02 12:00:00') from fn_test_not_nullable order by kdtmv2s1"
qt_sql_date_format_DateV2_Varchar "select date_format(kdtv2, '2006-01-02') from fn_test order by kdtv2"
qt_sql_date_format_DateV2_Varchar_notnull "select date_format(kdtv2, '2006-01-02') from fn_test_not_nullable order by kdtv2"
- qt_sql_date_trunc_DateTime_Varchar "select date_trunc(kdtm, kvchrs1) from fn_test order by kdtm, kvchrs1"
- qt_sql_date_trunc_DateTime_Varchar_notnull "select date_trunc(kdtm, kvchrs1) from fn_test_not_nullable order by kdtm, kvchrs1"
- qt_sql_date_trunc_DateTimeV2_Varchar "select date_trunc(kdtmv2s1, kvchrs1) from fn_test order by kdtmv2s1, kvchrs1"
- qt_sql_date_trunc_DateTimeV2_Varchar_notnull "select date_trunc(kdtmv2s1, kvchrs1) from fn_test_not_nullable order by kdtmv2s1, kvchrs1"
+ qt_sql_date_trunc_DateTime_Varchar "select date_trunc(kdtm, 'year') from fn_test order by kdtm, kvchrs1"
+ qt_sql_date_trunc_DateTime_Varchar_notnull "select date_trunc(kdtm, 'month') from fn_test_not_nullable order by kdtm, kvchrs1"
+ qt_sql_date_trunc_DateTimeV2_Varchar "select date_trunc(kdtmv2s1, 'day') from fn_test order by kdtmv2s1, kvchrs1"
+ qt_sql_date_trunc_DateTimeV2_Varchar_notnull "select date_trunc(kdtmv2s1, 'hour') from fn_test_not_nullable order by kdtmv2s1, kvchrs1"
qt_sql_datediff_DateTime_DateTime "select datediff(kdtm, kdtm) from fn_test order by kdtm, kdtm"
qt_sql_datediff_DateTime_DateTime_notnull "select datediff(kdtm, kdtm) from fn_test_not_nullable order by kdtm, kdtm"
qt_sql_datediff_DateTimeV2_DateTimeV2 "select datediff(kdtmv2s1, kdtmv2s1) from fn_test order by kdtmv2s1, kdtmv2s1"
diff --git a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_trunc.groovy b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_trunc.groovy
new file mode 100644
index 0000000000..b6943b9670
--- /dev/null
+++ b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_trunc.groovy
@@ -0,0 +1,74 @@
+// 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_date_trunc") {
+
+ def dbName = "test_date_trunc"
+ sql "DROP DATABASE IF EXISTS ${dbName}"
+ sql "CREATE DATABASE ${dbName}"
+ sql "USE $dbName"
+
+ sql """
+ CREATE TABLE IF NOT EXISTS `baseall` (
+ `k0` boolean null comment "",
+ `k1` tinyint(4) null comment "",
+ `k2` smallint(6) null comment "",
+ `k3` int(11) null comment "",
+ `k4` bigint(20) null comment "",
+ `k5` decimal(9, 3) null comment "",
+ `k6` char(5) null comment "",
+ `k10` date null comment "",
+ `k11` datetime null comment "",
+ `k7` varchar(20) null comment "",
+ `k8` double max null comment "",
+ `k9` float sum null comment "",
+ `k12` string replace null comment "",
+ `k13` largeint(40) replace null comment ""
+ ) engine=olap
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 5 properties("replication_num" = "1")
+ """
+
+ streamLoad {
+ table "baseall"
+ db dbName
+ set 'column_separator', ','
+ file "../../baseall.txt"
+ }
+
+ sql "sync"
+
+ qt_select_date_trunc_second """ SELECT k11, date_trunc(k11,'SECoND') FROM baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_minute """ SELECT k11, date_trunc(k11,'MINutE') FROM baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_hour """ SELECT k11, date_trunc(k11,'Hour') FROM baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_day """ SELECT k11, date_trunc(k11,'DAY') FROM baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_week """ SELECT k11, date_trunc(k11,'Week') FROM baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_month """ SELECT k11, date_trunc(k11,'MONTH') FROM baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_quarter """ SELECT k11, date_trunc(k11,'quarter') FROM baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_year """ SELECT k11, date_trunc(k11,'YeaR') FROM baseall order by k1,k2,k3;"""
+
+ try {
+ sql """ SELECT date_trunc(k11,k7) FROM baseall ; """
+ } catch (Exception e) {
+ assertTrue(e.getMessage().contains("must be a string constant"), e.getMessage())
+ }
+
+ try {
+ sql """ SELECT date_trunc(k11,'AAAA') FROM baseall ; """
+ } catch (Exception e) {
+ assertTrue(e.getMessage().contains("param only support argument"), e.getMessage())
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org