This is an automated email from the ASF dual-hosted git repository.
zclll 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 42aeb9be714 (enhanced) completed the function : century from duckdb
(#57893)
42aeb9be714 is described below
commit 42aeb9be7140f87fe6b5492cb3b23bda4b258a7a
Author: robll-v1 <[email protected]>
AuthorDate: Wed Nov 26 10:21:01 2025 +0800
(enhanced) completed the function : century from duckdb (#57893)
mysql> CREATE TABLE IF NOT EXISTS test_century_function (
-> id int,
-> test_date date NULL,
-> test_datetime datetime NULL,
-> test_datetimev2 datetimev2(3) NULL,
-> test_date_str varchar(20) NULL
-> ) ENGINE=OLAP
-> DUPLICATE KEY(id)
-> COMMENT "OLAP"
-> DISTRIBUTED BY HASH(id) BUCKETS 1
-> PROPERTIES (
-> "replication_allocation" = "tag.location.default: 1",
-> "in_memory" = "false",
-> "storage_format" = "V2"
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO test_century_function VALUES
-> (1, '1989-03-21', '1989-03-21 10:30:45', '1989-03-21 10:30:45.123',
'1989-03-21'),
-> (2, '2000-01-01', '2000-01-01 00:00:00', '2000-01-01 00:00:00.000',
'2000-01-01'),
-> (3, '2001-01-01', '2001-01-01 12:30:45', '2001-01-01 12:30:45.456',
'2001-01-01'),
-> (4, '2015-07-14', '2015-07-14 15:45:30', '2015-07-14 15:45:30.789',
'2015-07-14'),
-> (5, '1900-12-31', '1900-12-31 23:59:59', '1900-12-31 23:59:59.999',
'1900-12-31'),
-> (6, '1901-01-01', '1901-01-01 00:00:00', '1901-01-01 00:00:00.000',
'1901-01-01'),
-> (7, '1999-12-31', '1999-12-31 23:59:59', '1999-12-31 23:59:59.999',
'1999-12-31'),
-> (8, '2001-01-01', '2001-01-01 00:00:00', '2001-01-01 00:00:00.000',
'2001-01-01'),
-> (9, '0001-01-01', '0001-01-01 00:00:00', '0001-01-01 00:00:00.000',
'0001-01-01'),
-> (10, '0100-12-31', '0100-12-31 23:59:59', '0100-12-31 23:59:59.999',
'0100-12-31'),
-> (11, '0101-01-01', '0101-01-01 00:00:00', '0101-01-01 00:00:00.000',
'0101-01-01'),
-> (12, '9999-12-31', '9999-12-31 23:59:59', '9999-12-31 23:59:59.999',
'9999-12-31'),
-> (13, '2024-01-01', '2024-01-01 10:00:00', '2024-01-01 10:00:00.123',
'2024-01-01'),
-> (14, NULL, NULL, NULL, NULL);
Query OK, 14 rows affected (0.09 sec)
{'label':'label_a4cceb5cea7e4c7e_a993c81827b1f5ed', 'status':'VISIBLE',
'txnId':'12017'}
mysql> SELECT
-> century('1989-03-21') as century_1989,
-> century('2000-01-01') as century_2000,
-> century('2001-01-01') as century_2001,
-> century('2015-07-14') as century_2015;
+--------------+--------------+--------------+--------------+
| century_1989 | century_2000 | century_2001 | century_2015 |
+--------------+--------------+--------------+--------------+
| 20 | 20 | 21 | 21 |
+--------------+--------------+--------------+--------------+
1 row in set (0.01 sec)
Co-authored-by: linrrarity <[email protected]>
---
be/src/vec/functions/date_time_transforms.h | 16 ++++
be/src/vec/functions/to_time_function.cpp | 7 +-
be/test/vec/function/function_time_test.cpp | 56 +++++++++++++
.../doris/catalog/BuiltinScalarFunctions.java | 2 +
.../executable/DateTimeExtractAndTransform.java | 15 ++++
.../expressions/functions/scalar/Century.java | 91 ++++++++++++++++++++++
.../expressions/visitor/ScalarFunctionVisitor.java | 5 ++
.../datetime_functions/test_date_function.out | 57 ++++++++++++++
.../datetime_functions/test_date_function.groovy | 27 +++++++
9 files changed, 275 insertions(+), 1 deletion(-)
diff --git a/be/src/vec/functions/date_time_transforms.h
b/be/src/vec/functions/date_time_transforms.h
index 89dffe288cf..ddce29e2446 100644
--- a/be/src/vec/functions/date_time_transforms.h
+++ b/be/src/vec/functions/date_time_transforms.h
@@ -74,6 +74,22 @@ namespace doris::vectorized {
#define TO_TIME_FUNCTION(CLASS, UNIT) TIME_FUNCTION_IMPL(CLASS, UNIT, UNIT())
TO_TIME_FUNCTION(ToYearImpl, year);
+template <PrimitiveType PType>
+struct ToCenturyImpl {
+ static constexpr PrimitiveType OpArgType = PType;
+ using NativeType = typename PrimitiveTypeTraits<PType>::CppNativeType;
+ static constexpr auto name = "century";
+
+ static inline auto execute(const NativeType& t) {
+ const auto& date_time_value = (typename
PrimitiveTypeTraits<PType>::CppType&)(t);
+ int year = date_time_value.year();
+ return (year - 1) / 100 + 1;
+ }
+
+ static DataTypes get_variadic_argument_types() {
+ return {std::make_shared<typename
PrimitiveTypeTraits<PType>::DataType>()};
+ }
+};
TO_TIME_FUNCTION(ToYearOfWeekImpl, year_of_week);
TO_TIME_FUNCTION(ToQuarterImpl, quarter);
TO_TIME_FUNCTION(ToMonthImpl, month);
diff --git a/be/src/vec/functions/to_time_function.cpp
b/be/src/vec/functions/to_time_function.cpp
index 68ed6126ca8..e930a42eb4b 100644
--- a/be/src/vec/functions/to_time_function.cpp
+++ b/be/src/vec/functions/to_time_function.cpp
@@ -66,7 +66,10 @@ using FunctionDateTimeV2Date =
FunctionDateOrDateTimeToSomething<DataTypeDateV2,
DateImpl<TYPE_DATETIMEV2>>;
using FunctionTimeStampV2 =
FunctionDateOrDateTimeToSomething<DataTypeDateTimeV2,
TimeStampImpl<TYPE_DATETIMEV2>>;
-
+using FunctionCenturyV2 =
+ FunctionDateOrDateTimeToSomething<DataTypeInt16,
ToCenturyImpl<TYPE_DATEV2>>;
+using FunctionDateTimeV2Century =
+ FunctionDateOrDateTimeToSomething<DataTypeInt16,
ToCenturyImpl<TYPE_DATETIMEV2>>;
void register_function_to_time_function(SimpleFunctionFactory& factory) {
factory.register_function<FunctionTimeStampV2>();
factory.register_function<FunctionSecondV2>();
@@ -92,6 +95,8 @@ void
register_function_to_time_function(SimpleFunctionFactory& factory) {
factory.register_function<FunctionDateTimeV2Quarter>();
factory.register_function<FunctionDateTimeV2ToDate>();
factory.register_function<FunctionDateTimeV2Date>();
+ factory.register_function<FunctionCenturyV2>();
+ factory.register_function<FunctionDateTimeV2Century>();
factory.register_alias("date", "datev2");
factory.register_alias("to_date", "to_datev2");
}
diff --git a/be/test/vec/function/function_time_test.cpp
b/be/test/vec/function/function_time_test.cpp
index 939117023c8..4ba2b16bdf5 100644
--- a/be/test/vec/function/function_time_test.cpp
+++ b/be/test/vec/function/function_time_test.cpp
@@ -87,7 +87,63 @@ TEST(VTimestampFunctionsTest, year_test) {
static_cast<void>(check_function<DataTypeInt16, true>(func_name,
input_types, data_set));
}
+TEST(VTimestampFunctionsTest, century_test) {
+ std::string func_name = "century";
+ InputTypeSet input_types = {PrimitiveType::TYPE_DATETIMEV2};
+
+ DataSet data_set = {{{std::string("2024-01-01 00:00:00")}, int16_t {21}},
+ {{std::string("2000-01-01 00:00:00")}, int16_t {20}},
+ {{std::string("1999-12-31 23:59:59")}, int16_t {20}},
+ {{std::string("1900-01-01 00:00:00")}, int16_t {19}},
+ {{std::string("1800-01-01 00:00:00")}, int16_t {18}},
+ {{std::string("1700-01-01 00:00:00")}, int16_t {17}},
+ {{std::string("1600-01-01 00:00:00")}, int16_t {16}},
+ {{std::string("1500-01-01 00:00:00")}, int16_t {15}},
+ {{std::string("1400-01-01 00:00:00")}, int16_t {14}},
+ {{std::string("1300-01-01 00:00:00")}, int16_t {13}},
+ {{std::string("1200-01-01 00:00:00")}, int16_t {12}},
+ {{std::string("1100-01-01 00:00:00")}, int16_t {11}},
+ {{std::string("1000-01-01 00:00:00")}, int16_t {10}},
+ {{std::string("0900-01-01 00:00:00")}, int16_t {9}},
+ {{std::string("0800-01-01 00:00:00")}, int16_t {8}},
+ {{std::string("0700-01-01 00:00:00")}, int16_t {7}},
+ {{std::string("0600-01-01 00:00:00")}, int16_t {6}},
+ {{std::string("0500-01-01 00:00:00")}, int16_t {5}},
+ {{std::string("0400-01-01 00:00:00")}, int16_t {4}},
+ {{std::string("0300-01-01 00:00:00")}, int16_t {3}},
+ {{std::string("0200-01-01 00:00:00")}, int16_t {2}},
+ {{std::string("0100-01-01 00:00:00")}, int16_t {1}},
+ {{std::string("0001-01-01 00:00:00")}, int16_t {1}}};
+
+ static_cast<void>(check_function<DataTypeInt16, true>(func_name,
input_types, data_set));
+}
+
+TEST(VTimestampFunctionsTest, century_v2_test) {
+ std::string func_name = "century";
+
+ {
+ InputTypeSet input_types = {PrimitiveType::TYPE_DATEV2};
+
+ DataSet data_set = {{{std::string("2024-01-01")}, int16_t {21}},
+ {{std::string("2000-01-01")}, int16_t {20}},
+ {{std::string("1900-01-01")}, int16_t {19}},
+ {{std::string("0001-01-01")}, int16_t {1}}};
+
+ static_cast<void>(check_function<DataTypeInt16, true>(func_name,
input_types, data_set));
+ }
+
+ {
+ InputTypeSet input_types = {PrimitiveType::TYPE_DATETIMEV2};
+
+ DataSet data_set = {{{std::string("2024-01-01 12:34:56")}, int16_t
{21}},
+ {{std::string("2000-01-01 12:34:56.789")}, int16_t
{20}},
+ {{std::string("1900-01-01 12:34:56.123456")},
int16_t {19}},
+ {{std::string("0001-01-01 00:00:00.000001")},
int16_t {1}}};
+
+ static_cast<void>(check_function<DataTypeInt16, true>(func_name,
input_types, data_set));
+ }
+}
TEST(VTimestampFunctionsTest, quarter_test) {
std::string func_name = "quarter";
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
index c07bdb3bfe8..4a699f4cd17 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
@@ -130,6 +130,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.BitmapXorCoun
import org.apache.doris.nereids.trees.expressions.functions.scalar.Cardinality;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Cbrt;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Ceil;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.Century;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Char;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.CharacterLength;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Coalesce;
@@ -667,6 +668,7 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(Cardinality.class, "array_size", "cardinality", "size"),
scalar(Cbrt.class, "cbrt"),
scalar(Ceil.class, "ceil", "ceiling"),
+ scalar(Century.class, "century"),
scalar(Char.class, "char"),
scalar(CharacterLength.class, "char_length", "character_length"),
scalar(Coalesce.class, "coalesce"),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
index 120506221a8..3631d8e8b60 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
@@ -123,6 +123,21 @@ public class DateTimeExtractAndTransform {
return new SmallIntLiteral(((short) date.getYear()));
}
+
+ /**
+ * Executable datetime extract century
+ */
+ @ExecFunction(name = "century")
+ public static Expression century(DateV2Literal date) {
+ return new SmallIntLiteral((short) ((date.getYear() - 1) / 100 + 1));
+ }
+
+ @ExecFunction(name = "century")
+ public static Expression century(DateTimeV2Literal date) {
+ return new SmallIntLiteral((short) ((date.getYear() - 1) / 100 + 1));
+ }
+
+
/**
* Executable datetime extract quarter
*/
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Century.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Century.java
new file mode 100644
index 00000000000..9ed565d3d73
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Century.java
@@ -0,0 +1,91 @@
+// 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.
+
+package org.apache.doris.nereids.trees.expressions.functions.scalar;
+
+import org.apache.doris.catalog.FunctionSignature;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
+import org.apache.doris.nereids.trees.expressions.functions.Monotonic;
+import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
+import org.apache.doris.nereids.trees.expressions.shape.UnaryExpression;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.types.DateTimeV2Type;
+import org.apache.doris.nereids.types.DateV2Type;
+import org.apache.doris.nereids.types.SmallIntType;
+
+import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * ScalarFunction 'century'. This class is generated by GenerateFunction.
+ */
+public class Century extends ScalarFunction
+ implements UnaryExpression, ExplicitlyCastableSignature,
PropagateNullable, Monotonic {
+
+ private static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+
FunctionSignature.ret(SmallIntType.INSTANCE).args(DateV2Type.INSTANCE),
+
FunctionSignature.ret(SmallIntType.INSTANCE).args(DateTimeV2Type.WILDCARD));
+
+ /**
+ * constructor with 1 argument.
+ */
+ public Century(Expression arg) {
+ super("century", arg);
+ }
+
+ /** constructor for withChildren and reuse signature */
+ private Century(ScalarFunctionParams functionParams) {
+ super(functionParams);
+ }
+
+ /**
+ * withChildren.
+ */
+ @Override
+ public Century withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 1);
+ return new Century(getFunctionParams(children));
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitCentury(this, context);
+ }
+
+ @Override
+ public boolean isPositive() {
+ return true;
+ }
+
+ @Override
+ public int getMonotonicFunctionChildIndex() {
+ return 0;
+ }
+
+ @Override
+ public Expression withConstantArgs(Expression literal) {
+ return new Century(literal);
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
index d907d0b6599..d97081cd747 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
@@ -141,6 +141,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.BitmapXorCoun
import org.apache.doris.nereids.trees.expressions.functions.scalar.Cardinality;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Cbrt;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Ceil;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.Century;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Char;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.CharacterLength;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Coalesce;
@@ -2506,6 +2507,10 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(year, context);
}
+ default R visitCentury(Century century, C context) {
+ return visitScalarFunction(century, context);
+ }
+
default R visitYearOfWeek(YearOfWeek yearOfWeek, C context) {
return visitScalarFunction(yearOfWeek, context);
}
diff --git
a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out
b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out
index c9117deab3f..a0ec6218421 100644
---
a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out
+++
b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out
@@ -446,6 +446,63 @@ February
2019-08-01T13:21:03 2019
9999-08-01T13:21:03 9999
+-- !sql --
+19
+
+-- !sql --
+20
+
+-- !sql --
+20
+
+-- !sql --
+21
+
+-- !sql --
+21
+
+-- !sql --
+1
+
+-- !sql --
+100
+
+-- !sql --
+20
+
+-- !sql --
+20
+
+-- !sql --
+20
+
+-- !sql --
+19
+
+-- !sql --
+19
+
+-- !sql --
+20
+
+-- !sql --
+21
+
+-- !sql --
+21
+
+-- !sql --
+22
+
+-- !sql --
+\N
+
+-- !sql --
+\N
+
+-- !sql --
+\N
+
-- !sql --
1987
diff --git
a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
index c7223d076a7..69c56866832 100644
---
a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
+++
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
@@ -544,6 +544,33 @@ suite("test_date_function") {
qt_sql """ select year('2050-01-01') """
qt_sql """ select test_datetime, year(test_datetime) from ${tableName}
order by test_datetime """
+ // CENTURY
+ qt_sql """ select century('1900-01-01') """
+ qt_sql """ select century('1901-01-01') """
+ qt_sql """ select century('2000-01-01') """
+ qt_sql """ select century('2001-01-01') """
+ qt_sql """ select century('2023-12-31') """
+ qt_sql """ select century('0001-01-01') """
+ qt_sql """ select century('9999-12-31') """
+ qt_sql """ select century('1999-12-31 23:59:59') """
+ qt_sql """ select century(cast('2000-01-01' as date)) """
+ qt_sql """ select century(cast('2000-01-01 12:00:00' as datetime)) """
+
+ // Test century with different date formats
+ qt_sql """ select century('1900-12-31') """ // 19th century
+ qt_sql """ select century('1899-12-31') """ // 19th century
+ qt_sql """ select century('2000-12-31') """ // 20th century
+ qt_sql """ select century('2001-01-01') """ // 21st century
+ qt_sql """ select century('2100-01-01') """ // 21st century
+ qt_sql """ select century('2101-01-01') """ // 22nd century
+
+ // Test century with NULL values
+ qt_sql """ select century(NULL) """
+
+ // Test century with invalid dates
+ qt_sql """ select century('0000-00-00') """
+ qt_sql """ select century('2023-13-45') """
+
// YEAROFWEEK
qt_sql """ select year_of_week('1987-01-01') """
qt_sql """ select year_of_week('2050-01-01') """
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
