This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new bb8c52cab86 branch-4.0: [fix](function)Let second and microsecond
functions deal time literal #56659 (#58419)
bb8c52cab86 is described below
commit bb8c52cab86de970fbf424bfe129f66e0ba6f447
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Thu Nov 27 09:13:16 2025 +0800
branch-4.0: [fix](function)Let second and microsecond functions deal time
literal #56659 (#58419)
Cherry-picked from #56659
Co-authored-by: dwdwqfwe <[email protected]>
---
.../vec/functions/function_time_value_to_field.cpp | 7 +++
.../executable/DateTimeExtractAndTransform.java | 16 ++++++
.../expressions/functions/scalar/Microsecond.java | 4 +-
.../datetime_functions/test_date_function.out | 61 ++++++++++++++++++++++
.../nereids_syntax_p0/test_cast_datetime.groovy | 8 ---
.../datetime_functions/test_date_function.groovy | 59 ++++++++++++++++++++-
6 files changed, 144 insertions(+), 11 deletions(-)
diff --git a/be/src/vec/functions/function_time_value_to_field.cpp
b/be/src/vec/functions/function_time_value_to_field.cpp
index d587307cc7f..e018b1b5edd 100644
--- a/be/src/vec/functions/function_time_value_to_field.cpp
+++ b/be/src/vec/functions/function_time_value_to_field.cpp
@@ -22,6 +22,7 @@
#include "vec/data_types/data_type_number.h"
#include "vec/data_types/data_type_time.h"
#include "vec/functions/function.h"
+#include "vec/functions/function_date_or_datetime_computation.h"
#include "vec/functions/simple_function_factory.h"
#include "vec/runtime/time_value.h"
#include "vec/utils/template_helpers.hpp"
@@ -83,10 +84,16 @@ struct SecondImpl {
static inline auto execute(const TimeValue::TimeType& t) { return
TimeValue::second(t); }
};
+struct MicroImpl {
+ constexpr static auto name = "microsecond";
+ static inline auto execute(const TimeValue::TimeType& t) { return
TimeValue::microsecond(t); }
+};
+
void register_function_time_value_field(SimpleFunctionFactory& factory) {
factory.register_function<FunctionTimeValueToField<DataTypeInt32,
HourImpl>>();
factory.register_function<FunctionTimeValueToField<DataTypeInt8,
MintuImpl>>();
factory.register_function<FunctionTimeValueToField<DataTypeInt8,
SecondImpl>>();
+ factory.register_function<FunctionTimeValueToField<DataTypeInt32,
MicroImpl>>();
}
#include "common/compile_check_end.h"
} // namespace doris::vectorized
\ No newline at end of file
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 658a6ccfd40..3aa6b0e9be0 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
@@ -201,6 +201,14 @@ public class DateTimeExtractAndTransform {
return new TinyIntLiteral(((byte) date.getSecond()));
}
+ /**
+ * Executable time extract second
+ */
+ @ExecFunction(name = "second")
+ public static Expression second(TimeV2Literal time) {
+ return new TinyIntLiteral(((byte) time.getSecond()));
+ }
+
/**
* Executable datetime extract microsecond
*/
@@ -209,6 +217,14 @@ public class DateTimeExtractAndTransform {
return new IntegerLiteral(((int) date.getMicroSecond()));
}
+ /**
+ * Executable time extract microsecond
+ */
+ @ExecFunction(name = "microsecond")
+ public static Expression microsecond(TimeV2Literal time) {
+ return new IntegerLiteral(((int) time.getMicroSecond()));
+ }
+
/**
* Executable datetime extract dayofyear
*/
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Microsecond.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Microsecond.java
index 8e9a6f9e658..3077ed41eee 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Microsecond.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Microsecond.java
@@ -29,6 +29,7 @@ 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.IntegerType;
+import org.apache.doris.nereids.types.TimeV2Type;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
@@ -42,7 +43,8 @@ public class Microsecond extends ScalarFunction
implements UnaryExpression, ExplicitlyCastableSignature,
PropagateNullable, Monotonic {
public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
-
FunctionSignature.ret(IntegerType.INSTANCE).args(DateTimeV2Type.WILDCARD)
+
FunctionSignature.ret(IntegerType.INSTANCE).args(DateTimeV2Type.WILDCARD),
+
FunctionSignature.ret(IntegerType.INSTANCE).args(TimeV2Type.WILDCARD)
);
/**
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 a0ec6218421..10b351ffb79 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
@@ -300,9 +300,39 @@ February
-- !sql --
0
+-- !sql_second_time_with_micro1 --
+1
+
+-- !sql_second_time_with_micro2 --
+59
+
+-- !sql_second_time_with_micro3 --
+0
+
+-- !sql_second_time_with_micro4 --
+45
+
-- !sql --
767890
+-- !sql_microsecond_special_format --
+\N
+
+-- !sql_microsecond_time_with_micro1 --
+123456
+
+-- !sql_microsecond_time_with_micro2 --
+999999
+
+-- !sql_microsecond_time_with_micro3 --
+1
+
+-- !sql_microsecond_time_with_micro4 --
+500000
+
+-- !sql_microsecond_time_with_micro5 --
+789000
+
-- !sql --
2014-12-21T12:34:56
@@ -600,6 +630,37 @@ February
-- !sql --
767891
+123456
+1
+999999
+
+-- !sql --
+767891 1999-01-02T10:11:12.767891
+999999 2024-12-31T23:59:59.999999
+
+-- !sql --
+1 2024-01-01T00:00:00.000001
+
+-- !sql_second_simple --
+56
+
+-- !sql_second_datetime_with_micro --
+56
+
+-- !sql_microsecond_datetime_with_micro --
+789123
+
+-- !sql_table_second_all --
+\N
+\N
+\N
+56
+
+-- !sql_table_microsecond_all --
+\N
+\N
+\N
+789123
-- !sql --
1 2022-08-01
diff --git a/regression-test/suites/nereids_syntax_p0/test_cast_datetime.groovy
b/regression-test/suites/nereids_syntax_p0/test_cast_datetime.groovy
index 2dac1058feb..d03d01d5305 100644
--- a/regression-test/suites/nereids_syntax_p0/test_cast_datetime.groovy
+++ b/regression-test/suites/nereids_syntax_p0/test_cast_datetime.groovy
@@ -73,14 +73,6 @@ suite("test_cast_datetime") {
"date_add('10-30',1)",
"date_add('10-30 10:10:10',1)",
"date_add('2020-01 00:00:00', 1)",
- "MICROSECOND('invalid_time')",
- "MICROSECOND('12.34.56.123456')",
- "MICROSECOND('12:34:56')",
- "MICROSECOND('12:34:56.1234')",
- "MICROSECOND('12345')",
- "MICROSECOND('12:34:56.1')",
- "MICROSECOND('12:34:56.01')",
- "MICROSECOND('12:34:56.abcdef')",
"MICROSECOND('NaN')",
"MonthName('abcd-ef-gh')",
"DATE('2023-02-28 24:00:00')",
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 69c56866832..23c3d85e3b1 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
@@ -428,9 +428,21 @@ suite("test_date_function") {
// SECOND
qt_sql """ select second('2018-12-31 23:59:59') """
qt_sql """ select second('2018-12-31 00:00:00') """
-
+ // Test SECOND function with time strings containing microseconds
+ qt_sql_second_time_with_micro1 """ select second('12:00:01.12') """
+ qt_sql_second_time_with_micro2 """ select second('23:59:59.999999') """
+ qt_sql_second_time_with_micro3 """ select second('00:00:00.000001') """
+ qt_sql_second_time_with_micro4 """ select second('15:30:45.5') """
+
// MICROSECOND
qt_sql """ select microsecond(cast('1999-01-02 10:11:12.767890' as
datetimev2(6))) """
+ // Test MICROSECOND function with time strings containing microseconds
+ qt_sql_microsecond_special_format """ select microsecond('120000.12') """
+ qt_sql_microsecond_time_with_micro1 """ select
microsecond('12:00:00.123456') """
+ qt_sql_microsecond_time_with_micro2 """ select
microsecond('23:59:59.999999') """
+ qt_sql_microsecond_time_with_micro3 """ select
microsecond('00:00:00.000001') """
+ qt_sql_microsecond_time_with_micro4 """ select microsecond('15:30:45.5')
"""
+ qt_sql_microsecond_time_with_micro5 """ select microsecond('12:34:56.789')
"""
// STR_TO_DATE
sql """ truncate table ${tableName} """
@@ -616,8 +628,50 @@ suite("test_date_function") {
CREATE TABLE IF NOT EXISTS ${tableName} (k1 datetimev2(6))
duplicate key(k1) distributed by hash(k1) buckets 1
properties('replication_num' = '1');
"""
sql """ insert into ${tableName} values('1999-01-02 10:11:12.767891') """
+ sql """ insert into ${tableName} values('2023-05-15 08:30:45.123456') """
+ sql """ insert into ${tableName} values('2024-01-01 00:00:00.000001') """
+ sql """ insert into ${tableName} values('2024-12-31 23:59:59.999999') """
- qt_sql """ select microsecond(k1) from ${tableName}; """
+ qt_sql """ select microsecond(k1) from ${tableName} order by k1; """
+
+ // Test microsecond extraction from different datetime formats
+ qt_sql """ select microsecond(k1), k1 from ${tableName} where
microsecond(k1) > 500000 order by k1; """
+ qt_sql """ select microsecond(k1), k1 from ${tableName} where
microsecond(k1) < 100000 order by k1; """
+
+ // Simple tests for basic functionality
+ qt_sql_second_simple """ select second('12:34:56') """
+ // qt_sql_microsecond_simple """ select microsecond('12:34:56.123456') """
+
+ // Test with datetime values
+ qt_sql_second_datetime_with_micro """ select second('2024-01-01
12:34:56.789') """
+ qt_sql_microsecond_datetime_with_micro """ select microsecond('2024-01-01
12:34:56.789123') """
+
+ // Test SECOND and MICROSECOND functions with table containing time_str
column
+ tableName = "test_time_str_functions"
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ `id` INT NOT NULL COMMENT "ID",
+ `time_str` VARCHAR(50) NOT NULL COMMENT "时间字符串"
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`)
+ DISTRIBUTED BY HASH(`id`) BUCKETS 1
+ PROPERTIES("replication_num" = "1");
+ """
+
+ // Insert key test data
+ sql """ insert into ${tableName} values
+ (1, '120000.12'),
+ (2, '12:00:01.12'),
+ (3, '23:59:59.999999'),
+ (4, '2024-01-01 12:34:56.789123');
+ """
+
+ // Test SECOND function with time_str column
+ qt_sql_table_second_all """ select second(time_str) from ${tableName}
order by id; """
+
+ // Test MICROSECOND function with time_str column
+ qt_sql_table_microsecond_all """ select microsecond(time_str) from
${tableName} order by id; """
tableName = "test_from_unixtime"
@@ -966,4 +1020,5 @@ suite("test_date_function") {
FROM date_add_test123; """
order_qt_sql2 """ SELECT invalid_col, DATE_ADD(invalid_col, INTERVAL
1+2 DAY) FROM date_add_test123 """
+
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
