This is an automated email from the ASF dual-hosted git repository. jacktengg pushed a commit to branch dev-timestamp-ns in repository https://gitbox.apache.org/repos/asf/doris.git
commit ccb2fb21b533de7b76ca443d6000c27dbba620a4 Author: jacktengg <[email protected]> AuthorDate: Sun Aug 23 23:02:17 2026 +0800 [fix](timestamp_ns) Complete temporal boundary and type handling ### What problem does this PR solve? Issue Number: None Related PR: #66761 Problem Summary: TIMESTAMP_NS was missing boundary handling in automatic range partition routing, UTC_TIMESTAMP precision 7-9 execution, exact mixed temporal operations, signed ASOF indexing, and predefined VARIANT subtype registration. Generic signature and comparison coercion also rejected valid string and date-like uses. This change scopes date_trunc clamping to automatic partition routing, adds nanosecond UTC execution and mixed DATEDIFF and ASOF paths, narrows the signature guard [...] ### Release note Complete TIMESTAMP_NS support for automatic range partition boundaries, UTC_TIMESTAMP(7..9), mixed DATEDIFF and comparisons, ASOF JOIN, and typed VARIANT fields. ### Check List (For Author) - Test: Regression test / Unit Test - ./run-regression-test.sh --run -d datatype_p0/timestamp_ns -s test_timestamp_ns_literal - Timestamp NS datatype regression suites and datetime function regression suite - Targeted FE unit tests and AsofIndexVariantTest BE unit tests - ./build.sh --be --fe and ./build.sh --fe - Behavior changed: Yes. Valid TIMESTAMP_NS boundary, mixed temporal, UTC precision, ASOF, and typed VARIANT operations are now supported. - Does this need documentation: No --- be/src/exec/common/join_op_utils.h | 10 +- be/src/exec/common/join_utils.h | 14 ++- be/src/exec/operator/hashjoin_build_sink.cpp | 2 +- .../operator/join/process_hash_table_probe_impl.h | 4 + be/src/exec/pipeline/dependency.h | 3 +- be/src/exec/sink/vrow_distribution.h | 1 + .../function_date_or_datetime_computation.cpp | 7 ++ .../function_date_or_datetime_computation.h | 46 +++++++++ .../function/function_other_types_to_date.cpp | 19 +++- be/src/exprs/function_context.cpp | 1 + be/src/exprs/function_context.h | 5 + be/src/exprs/vexpr_context.h | 6 ++ be/test/exec/operator/asof_join_test.cpp | 11 +++ .../apache/doris/analysis/PartitionExprUtil.java | 38 +++++++- .../functions/DateTimeWithPrecision.java | 7 +- .../expressions/functions/SearchSignature.java | 12 ++- .../functions/executable/DateTimeAcquire.java | 19 +++- .../functions/executable/DateTimeArithmetic.java | 10 ++ .../expressions/functions/scalar/DateDiff.java | 4 + .../expressions/functions/scalar/UtcTimestamp.java | 18 +--- .../doris/nereids/util/TypeCoercionUtils.java | 101 +++++++++++++++----- .../java/org/apache/doris/catalog/TypeTest.java | 5 + .../scalar/TimestampNsFunctionSignatureTest.java | 34 +++++-- .../doris/nereids/util/TypeCoercionMatrixTest.java | 7 +- .../doris/nereids/util/TypeCoercionUtilsTest.java | 59 ++++++++---- .../main/java/org/apache/doris/catalog/Type.java | 1 + .../test_timestamp_ns_complex_type.out | 6 ++ .../timestamp_ns/test_timestamp_ns_join.out | 32 +++++++ .../timestamp_ns/test_timestamp_ns_literal.out | 9 ++ ...est_timestamp_ns_mixed_datetime_expressions.out | 60 ++++++++++++ .../test_timestamp_ns_partition_bucket.out | 11 +++ .../test_timestamp_ns_complex_type.groovy | 35 +++++++ .../timestamp_ns/test_timestamp_ns_join.groovy | 96 +++++++++++++++++++ .../timestamp_ns/test_timestamp_ns_literal.groovy | 27 ++++++ ..._timestamp_ns_mixed_datetime_expressions.groovy | 105 +++++++++++++++------ .../test_timestamp_ns_partition_bucket.groovy | 29 ++++++ .../datetime_functions/test_date_function.groovy | 5 +- 37 files changed, 741 insertions(+), 118 deletions(-) diff --git a/be/src/exec/common/join_op_utils.h b/be/src/exec/common/join_op_utils.h index 7b6636f88dc..c35d7815e19 100644 --- a/be/src/exec/common/join_op_utils.h +++ b/be/src/exec/common/join_op_utils.h @@ -61,7 +61,8 @@ inline constexpr bool is_asof_outer_join_op_v = JoinOpType == TJoinOp::ASOF_LEFT // ASOF JOIN index with inline values for cache-friendly branchless binary search. // IntType is the integer representation of the ASOF column value: -// uint32_t for DateV2, uint64_t for DateTimeV2 and TimestampTZ. +// uint32_t for DateV2, uint64_t for DateTimeV2 and TimestampTZ, +// int64_t for TimestampNs. // Rows are sorted by asof_value during build, then materialized into SoA arrays // so probe-side binary search only touches the ASOF values hot path. template <typename IntType> @@ -150,8 +151,9 @@ struct AsofIndexGroup { }; // Type-erased container for all ASOF index groups. -// DateV2 -> uint32_t, DateTimeV2/TimestampTZ -> uint64_t. -using AsofIndexVariant = std::variant<std::monostate, std::vector<AsofIndexGroup<uint32_t>>, - std::vector<AsofIndexGroup<uint64_t>>>; +// DateV2 -> uint32_t, DateTimeV2/TimestampTZ -> uint64_t, TimestampNs -> int64_t. +using AsofIndexVariant = + std::variant<std::monostate, std::vector<AsofIndexGroup<uint32_t>>, + std::vector<AsofIndexGroup<uint64_t>>, std::vector<AsofIndexGroup<int64_t>>>; } // namespace doris diff --git a/be/src/exec/common/join_utils.h b/be/src/exec/common/join_utils.h index fc0ae935adc..fcbe7136528 100644 --- a/be/src/exec/common/join_utils.h +++ b/be/src/exec/common/join_utils.h @@ -28,8 +28,18 @@ namespace doris { +template <typename ColumnType> +struct AsofColumnIntType { + using type = typename ColumnType::value_type::underlying_value; +}; + +template <> +struct AsofColumnIntType<ColumnTimeStampNs> { + using type = int64_t; +}; + // Devirtualize compare_at for ASOF JOIN supported column types. -// ASOF JOIN only supports DateV2, DateTimeV2, and TimestampTZ. +// ASOF JOIN only supports DateV2, DateTimeV2, TimestampNs, and TimestampTZ. // Dispatches to the concrete ColumnVector<T> once so that all compare_at // calls inside `func` are direct (non-virtual) calls. // `func` receives a single argument: a const pointer to the concrete column @@ -40,6 +50,8 @@ decltype(auto) asof_column_dispatch(const IColumn* col, Func&& func) { return std::forward<Func>(func)(c_dv2); } else if (const auto* c_dtv2 = check_and_get_column<ColumnDateTimeV2>(col)) { return std::forward<Func>(func)(c_dtv2); + } else if (const auto* c_tsns = check_and_get_column<ColumnTimeStampNs>(col)) { + return std::forward<Func>(func)(c_tsns); } else if (const auto* c_tstz = check_and_get_column<ColumnTimeStampTz>(col)) { return std::forward<Func>(func)(c_tstz); } else { diff --git a/be/src/exec/operator/hashjoin_build_sink.cpp b/be/src/exec/operator/hashjoin_build_sink.cpp index 2d0aea94ffc..a851e5ef51f 100644 --- a/be/src/exec/operator/hashjoin_build_sink.cpp +++ b/be/src/exec/operator/hashjoin_build_sink.cpp @@ -409,7 +409,7 @@ Status HashJoinBuildSinkLocalState::build_asof_index(Block& block) { throw Exception(ErrorCode::INTERNAL_ERROR, "Unsupported ASOF column type for inline optimization"); } else { - using IntType = typename ColType::value_type::underlying_value; + using IntType = typename AsofColumnIntType<ColType>::type; const auto& col_data = typed_col->get_data(); auto& groups = _shared_state->asof_index_groups diff --git a/be/src/exec/operator/join/process_hash_table_probe_impl.h b/be/src/exec/operator/join/process_hash_table_probe_impl.h index e3713f8a00f..57d99fce6b3 100644 --- a/be/src/exec/operator/join/process_hash_table_probe_impl.h +++ b/be/src/exec/operator/join/process_hash_table_probe_impl.h @@ -482,6 +482,10 @@ uint32_t ProcessHashTableProbe<JoinOpType>:: } return probe_with_index(groups, assert_cast<const ColumnTimeStampTz*>(probe_col)); + }, + [&](std::vector<AsofIndexGroup<int64_t>>& groups) -> uint32_t { + return probe_with_index(groups, + assert_cast<const ColumnTimeStampNs*>(probe_col)); }}, shared_state->asof_index_groups); diff --git a/be/src/exec/pipeline/dependency.h b/be/src/exec/pipeline/dependency.h index 53f9ed9281b..5802a8ddefc 100644 --- a/be/src/exec/pipeline/dependency.h +++ b/be/src/exec/pipeline/dependency.h @@ -670,7 +670,8 @@ struct HashJoinSharedState : public JoinSharedState { bool asof_inequality_is_strict = false; // ASOF JOIN pre-sorted index with inline values for O(log K) branchless lookup - // Typed AsofIndexGroups stored in a variant (uint32_t for DateV2, uint64_t for DateTimeV2/TimestampTZ) + // Typed AsofIndexGroups stored in a variant (uint32_t for DateV2, uint64_t for + // DateTimeV2/TimestampTZ, int64_t for TimestampNs) AsofIndexVariant asof_index_groups; // build_row_index -> bucket_id for O(1) reverse lookup std::vector<uint32_t> asof_build_row_to_bucket; diff --git a/be/src/exec/sink/vrow_distribution.h b/be/src/exec/sink/vrow_distribution.h index abb50cc886d..009ed1d8a08 100644 --- a/be/src/exec/sink/vrow_distribution.h +++ b/be/src/exec/sink/vrow_distribution.h @@ -130,6 +130,7 @@ public: auto [part_ctxs, part_funcs] = _get_partition_function(); for (auto part_ctx : part_ctxs) { RETURN_IF_ERROR(part_ctx->prepare(_state, *output_row_desc)); + part_ctx->set_auto_partition_boundary_context(); RETURN_IF_ERROR(part_ctx->open(_state)); } } diff --git a/be/src/exprs/function/function_date_or_datetime_computation.cpp b/be/src/exprs/function/function_date_or_datetime_computation.cpp index e5216241e3c..72aa6ed59ee 100644 --- a/be/src/exprs/function/function_date_or_datetime_computation.cpp +++ b/be/src/exprs/function/function_date_or_datetime_computation.cpp @@ -237,6 +237,11 @@ ALL_FUNCTION_TIME_DIFF(FunctionDatetimeDaysDiff, DaysDiffImpl) ALL_FUNCTION_TIME_DIFF(FunctionDatetimeMilliSecondsDiff, MilliSecondsDiffImpl) ALL_FUNCTION_TIME_DIFF(FunctionDatetimeMicroSecondsDiff, MicroSecondsDiffImpl) +using FunctionDateDiffTimeStampNsDateTimeV2 = + FunctionDateOrDateTimeComputation<MixedDateDiffImpl<TYPE_TIMESTAMP_NS, TYPE_DATETIMEV2>>; +using FunctionDateDiffDateTimeV2TimeStampNs = + FunctionDateOrDateTimeComputation<MixedDateDiffImpl<TYPE_DATETIMEV2, TYPE_TIMESTAMP_NS>>; + using FunctionDatetimeToYearWeekTwoArgs = FunctionDateOrDateTimeComputation<ToYearWeekTwoArgsImpl<TYPE_DATETIMEV2>>; using FunctionDatetimeToWeekTwoArgs = @@ -400,6 +405,8 @@ void register_function_date_time_computation(SimpleFunctionFactory& factory) { REGISTER_ALL_DATEV2_FUNCTIONS_DIFF(FunctionDatetimeDaysDiff) REGISTER_ALL_DATEV2_FUNCTIONS_DIFF(FunctionDatetimeMilliSecondsDiff) REGISTER_ALL_DATEV2_FUNCTIONS_DIFF(FunctionDatetimeMicroSecondsDiff) + factory.register_function<FunctionDateDiffTimeStampNsDateTimeV2>(); + factory.register_function<FunctionDateDiffDateTimeV2TimeStampNs>(); factory.register_function<FunctionToYearWeekTwoArgs>(); factory.register_function<FunctionToWeekTwoArgs>(); diff --git a/be/src/exprs/function/function_date_or_datetime_computation.h b/be/src/exprs/function/function_date_or_datetime_computation.h index c9500598cc4..82cc4e653aa 100644 --- a/be/src/exprs/function/function_date_or_datetime_computation.h +++ b/be/src/exprs/function/function_date_or_datetime_computation.h @@ -565,6 +565,30 @@ struct SubtractSecondMicrosecondImpl }; DECLARE_DATE_FUNCTIONS(DateDiffImpl, datediff, TYPE_INT, (ts0.daynr() - ts1.daynr())); + +template <PrimitiveType LeftType, PrimitiveType RightType> +struct MixedDateDiffImpl { + static constexpr PrimitiveType ArgPType = LeftType; + static constexpr PrimitiveType IntervalPType = RightType; + static constexpr PrimitiveType ReturnType = TYPE_INT; + static constexpr auto name = "datediff"; + + using LeftFieldType = typename PrimitiveTypeTraits<LeftType>::DataType::FieldType; + using RightFieldType = typename PrimitiveTypeTraits<RightType>::DataType::FieldType; + using LeftValueType = typename PrimitiveTypeTraits<LeftType>::CppType; + using RightValueType = typename PrimitiveTypeTraits<RightType>::CppType; + + static inline int32_t execute(const LeftFieldType& left, const RightFieldType& right) { + const auto& left_value = reinterpret_cast<const LeftValueType&>(left); + const auto& right_value = reinterpret_cast<const RightValueType&>(right); + return left_value.daynr() - right_value.daynr(); + } + + static DataTypes get_variadic_argument_types() { + return {std::make_shared<typename PrimitiveTypeTraits<LeftType>::DataType>(), + std::make_shared<typename PrimitiveTypeTraits<RightType>::DataType>()}; + } +}; // DECLARE_DATE_FUNCTIONS(TimeDiffImpl, timediff, DataTypeTime, ts0.datetime_diff_in_seconds(ts1)); // Expands to below here because it use Time type which need some special deal. template <PrimitiveType DateType> @@ -1336,6 +1360,9 @@ struct TimestampToDateTime : IFunction { template <PrimitiveType UTCType> struct UtcImpl { static constexpr PrimitiveType ReturnType = UTCType; + static constexpr int DATETIMEV2_MAX_SCALE = 6; + + static bool skip_return_type_check() { return ReturnType == TYPE_DATETIMEV2; } static constexpr const char* get_function_name() { if constexpr (ReturnType == TYPE_DATETIMEV2 || ReturnType == TYPE_DATETIME) { @@ -1358,6 +1385,25 @@ struct UtcImpl { block.get_by_position(arguments[0]).column.get()); scale = col->get_element(0); } + if constexpr (ReturnType == TYPE_DATETIMEV2) { + if (block.get_by_position(result).type->get_primitive_type() == TYPE_TIMESTAMP_NS) { + auto col_to = ColumnTimeStampNs::create(); + const int32_t nanos = context->state()->nano_seconds(); + const int64_t factor = + common::exp10_i64(TimeStampNsValue::FRACTIONAL_DIGITS - scale); + const int32_t truncated_nanos = cast_set<int32_t>(nanos / factor * factor); + DateV2Value<DateTimeV2ValueType> utc_datetime; + utc_datetime.from_unixtime(context->state()->timestamp_ms() / 1000, nanos, "+00:00", + DATETIMEV2_MAX_SCALE); + TimeStampNsValue timestamp; + DCHECK(timestamp.from_datetime(utc_datetime, + cast_set<uint16_t>(truncated_nanos % 1000))); + col_to->insert_value(timestamp); + block.get_by_position(result).column = + ColumnConst::create(std::move(col_to), input_rows_count); + return Status::OK(); + } + } auto col_to = PrimitiveTypeTraits<ReturnType>::ColumnType::create(); DateV2Value<DateTimeV2ValueType> dtv; if (dtv.from_unixtime(context->state()->timestamp_ms() / 1000, diff --git a/be/src/exprs/function/function_other_types_to_date.cpp b/be/src/exprs/function/function_other_types_to_date.cpp index 5be39b471a2..429418f0e9a 100644 --- a/be/src/exprs/function/function_other_types_to_date.cpp +++ b/be/src/exprs/function/function_other_types_to_date.cpp @@ -23,6 +23,7 @@ #include <climits> #include <cstdint> #include <cstring> +#include <limits> #include <memory> #include <string> #include <tuple> @@ -453,10 +454,11 @@ struct DateTrunc { using DateValueType = typename PrimitiveTypeTraits<PType>::CppType; struct State { - using CallbackFunction = - std::function<void(const ColumnPtr&, ColumnType&, size_t, const cctz::time_zone&)>; + using CallbackFunction = std::function<void(const ColumnPtr&, ColumnType&, size_t, + const cctz::time_zone&, bool)>; CallbackFunction callback_function; cctz::time_zone timezone; + bool clamp_to_timestamp_ns_min = false; }; static bool is_variadic() { return true; } @@ -491,6 +493,7 @@ struct DateTrunc { std::shared_ptr<State> state = std::make_shared<State>(); state->timezone = context->state()->timezone_obj(); + state->clamp_to_timestamp_ns_min = context->is_auto_partition_boundary_context(); if (std::strncmp("year", lower_str.data(), 4) == 0) { state->callback_function = &execute_impl_right_const<TimeUnit::YEAR>; } else if (std::strncmp("quarter", lower_str.data(), 7) == 0) { @@ -526,7 +529,8 @@ struct DateTrunc { auto* state = reinterpret_cast<State*>( context->get_function_state(FunctionContext::THREAD_LOCAL)); DCHECK(state != nullptr); - state->callback_function(datetime_column, *res, input_rows_count, state->timezone); + state->callback_function(datetime_column, *res, input_rows_count, state->timezone, + state->clamp_to_timestamp_ns_min); block.replace_by_position(result, std::move(res)); return Status::OK(); } @@ -535,7 +539,8 @@ private: template <TimeUnit Unit> static void execute_impl_right_const(const ColumnPtr& datetime_column, ColumnType& result_column, size_t input_rows_count, - const cctz::time_zone& timezone) { + const cctz::time_zone& timezone, + bool clamp_to_timestamp_ns_min) { auto& data = static_cast<const ColumnType*>(datetime_column.get())->get_data(); auto& res = result_column.get_data(); for (size_t i = 0; i < input_rows_count; ++i) { @@ -557,7 +562,11 @@ private: } else { if constexpr (PType == TYPE_TIMESTAMP_NS) { if (!dt.template datetime_trunc<Unit>()) { - throw_out_of_bound_one_date<DateValueType>("date_trunc", data[i]); + if (clamp_to_timestamp_ns_min) { + dt = DateValueType(std::numeric_limits<int64_t>::min()); + } else { + throw_out_of_bound_one_date<DateValueType>("date_trunc", data[i]); + } } } else { dt.template datetime_trunc<Unit>(); diff --git a/be/src/exprs/function_context.cpp b/be/src/exprs/function_context.cpp index 3592bcf4be8..8c1faa7d441 100644 --- a/be/src/exprs/function_context.cpp +++ b/be/src/exprs/function_context.cpp @@ -56,6 +56,7 @@ std::unique_ptr<FunctionContext> FunctionContext::clone() { new_context->_fragment_local_fn_state = _fragment_local_fn_state; new_context->_check_overflow_for_decimal = _check_overflow_for_decimal; new_context->_enable_strict_mode = _enable_strict_mode; + new_context->_is_auto_partition_boundary_context = _is_auto_partition_boundary_context; new_context->_string_as_jsonb_string = _string_as_jsonb_string; new_context->_jsonb_string_as_string = _jsonb_string_as_string; return new_context; diff --git a/be/src/exprs/function_context.h b/be/src/exprs/function_context.h index ca0f1ed4610..69d6dfa6187 100644 --- a/be/src/exprs/function_context.h +++ b/be/src/exprs/function_context.h @@ -84,6 +84,8 @@ public: bool enable_strict_mode() const { return _enable_strict_mode; } + bool is_auto_partition_boundary_context() const { return _is_auto_partition_boundary_context; } + bool set_check_overflow_for_decimal(bool check_overflow_for_decimal) { return _check_overflow_for_decimal = check_overflow_for_decimal; } @@ -92,6 +94,8 @@ public: return _enable_strict_mode = enable_strict_mode; } + void set_auto_partition_boundary_context() { _is_auto_partition_boundary_context = true; } + void set_string_as_jsonb_string(bool string_as_jsonb_string) { _string_as_jsonb_string = string_as_jsonb_string; } @@ -198,6 +202,7 @@ private: RuntimeProfile::Counter* _udf_execute_timer = nullptr; bool _check_overflow_for_decimal = false; bool _enable_strict_mode = false; + bool _is_auto_partition_boundary_context = false; bool _string_as_jsonb_string = false; bool _jsonb_string_as_string = false; diff --git a/be/src/exprs/vexpr_context.h b/be/src/exprs/vexpr_context.h index 3f5e33510cd..3b493b84e57 100644 --- a/be/src/exprs/vexpr_context.h +++ b/be/src/exprs/vexpr_context.h @@ -262,6 +262,12 @@ public: return _fn_contexts[i].get(); } + void set_auto_partition_boundary_context() { + for (auto& fn_context : _fn_contexts) { + fn_context->set_auto_partition_boundary_context(); + } + } + // execute expr with inverted index which column a, b has inverted indexes // but some situation although column b has indexes, but apply index is not useful, we should // skip this expr, just do not apply index anymore. diff --git a/be/test/exec/operator/asof_join_test.cpp b/be/test/exec/operator/asof_join_test.cpp index accf929fd5c..5b3157b6b4a 100644 --- a/be/test/exec/operator/asof_join_test.cpp +++ b/be/test/exec/operator/asof_join_test.cpp @@ -390,4 +390,15 @@ TEST_F(AsofIndexVariantTest, EmplaceUint64Groups) { 99999999999ULL); } +TEST_F(AsofIndexVariantTest, EmplaceInt64Groups) { + AsofIndexVariant variant; + auto& groups = variant.emplace<std::vector<AsofIndexGroup<int64_t>>>(); + groups.emplace_back(); + groups[0].add_row(-1, 1); + groups[0].add_row(0, 2); + groups[0].sort_and_finalize(); + EXPECT_TRUE(std::holds_alternative<std::vector<AsofIndexGroup<int64_t>>>(variant)); + EXPECT_EQ(std::get<std::vector<AsofIndexGroup<int64_t>>>(variant)[0].asof_values[0], -1); +} + } // namespace doris diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/PartitionExprUtil.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/PartitionExprUtil.java index 96421206125..7c68e807e39 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/PartitionExprUtil.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/PartitionExprUtil.java @@ -175,7 +175,10 @@ public class PartitionExprUtil { beginLocalDateTime.getYear(), beginLocalDateTime.getMonthValue(), beginLocalDateTime.getDayOfMonth(), beginLocalDateTime.getHour(), beginLocalDateTime.getMinute(), beginLocalDateTime.getSecond()); - LocalDateTime endLocalDateTime = getRangeEnd(beginLocalDateTime, intervalInfo); + LocalDateTime rangeStart = isMinimumTimeStampNs(beginDateTime) + && "date_trunc".equals(intervalInfo.fnName) + ? dateTrunc(beginLocalDateTime, intervalInfo.timeUnit) : beginLocalDateTime; + LocalDateTime endLocalDateTime = getRangeEnd(rangeStart, intervalInfo); LiteralExpr endDateTime = beginDateTime instanceof DateLiteral ? new DateLiteral(endLocalDateTime, beginDateTime.getType()) : new TimeStampNsLiteral(endLocalDateTime); @@ -265,6 +268,10 @@ public class PartitionExprUtil { return PartitionValue.MAX_VALUE; } + if (isMinimumTimeStampNs(dateLiteral)) { + return new PartitionValue(dateLiteral.getStringValue()); + } + LocalDateTime dateTime = dateLiteral instanceof DateLiteral ? ((DateLiteral) dateLiteral).getTimeFormatter() : ((TimeStampNsLiteral) dateLiteral).toLocalDateTime(); @@ -286,6 +293,35 @@ public class PartitionExprUtil { return new PartitionValue(timeString); } + private static boolean isMinimumTimeStampNs(LiteralExpr value) { + return value instanceof TimeStampNsLiteral + && ((Number) value.getRealValue()).longValue() == Long.MIN_VALUE; + } + + private static LocalDateTime dateTrunc(LocalDateTime value, String timeUnit) throws AnalysisException { + switch (timeUnit) { + case "year": + return LocalDateTime.of(value.getYear(), 1, 1, 0, 0); + case "quarter": + return LocalDateTime.of(value.getYear(), (value.getMonthValue() - 1) / 3 * 3 + 1, 1, 0, 0); + case "month": + return LocalDateTime.of(value.getYear(), value.getMonthValue(), 1, 0, 0); + case "week": + return value.minusDays(value.getDayOfWeek().getValue() - 1L) + .withHour(0).withMinute(0).withSecond(0).withNano(0); + case "day": + return value.withHour(0).withMinute(0).withSecond(0).withNano(0); + case "hour": + return value.withMinute(0).withSecond(0).withNano(0); + case "minute": + return value.withSecond(0).withNano(0); + case "second": + return value.withNano(0); + default: + throw new AnalysisException("Unsupported date_trunc time unit: " + timeUnit); + } + } + private static String getFormatPartitionValue(String value) { StringBuilder sb = new StringBuilder(); // When the value is negative diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/DateTimeWithPrecision.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/DateTimeWithPrecision.java index 679b624bf15..adb30f4a1fc 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/DateTimeWithPrecision.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/DateTimeWithPrecision.java @@ -26,6 +26,8 @@ import org.apache.doris.nereids.trees.expressions.literal.IntegerLikeLiteral; import org.apache.doris.nereids.types.DateTimeV2Type; import org.apache.doris.nereids.types.TimeStampNsType; +import java.util.Locale; + /** * TimeWithPrecision. fill precision to the return type. * @@ -49,8 +51,9 @@ public abstract class DateTimeWithPrecision extends ScalarFunction { if (getArgument(0) instanceof IntegerLikeLiteral) { IntegerLikeLiteral integerLikeLiteral = (IntegerLikeLiteral) getArgument(0); int precision = integerLikeLiteral.getIntValue(); - if (precision > TimeStampNsType.SCALE) { - throw new AnalysisException("Precision of NOW must be between 0 and " + if (precision < 0 || precision > TimeStampNsType.SCALE) { + throw new AnalysisException("Precision of " + getName().toUpperCase(Locale.ROOT) + + " must be between 0 and " + TimeStampNsType.SCALE + ". Precision was set to: " + precision); } signature = signature.withReturnType(precision > DateTimeV2Type.MAX_SCALE diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/SearchSignature.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/SearchSignature.java index 56c91c2d5b4..f1d10b2699c 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/SearchSignature.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/SearchSignature.java @@ -51,6 +51,7 @@ public class SearchSignature { private final List<Expression> arguments; private final boolean hasTimeStampNsArgument; private final boolean hasOtherDateLikeArgument; + private final boolean hasDateLikeSignature; // param1: signature type // param2: real argument type @@ -68,6 +69,9 @@ public class SearchSignature { this.hasOtherDateLikeArgument = arguments.stream() .anyMatch(argument -> argument.getDataType().isDateLikeType() && !argument.getDataType().isTimeStampNsType()); + this.hasDateLikeSignature = signatures.stream().anyMatch(signature -> + signature.argumentsTypes.stream().anyMatch(DataType::isDateLikeType) + || signature.getVarArgType().filter(DataType::isDateLikeType).isPresent()); } public static SearchSignature from(ComputeSignature computeSignature, @@ -262,11 +266,17 @@ public class SearchSignature { DataType sigArgType = sig.getArgType(i); Expression argument = arguments.get(i); DataType realType = argument.getDataType(); - if (hasTimeStampNsArgument && hasOtherDateLikeArgument + if (hasTimeStampNsArgument && hasOtherDateLikeArgument && hasDateLikeSignature && realType.isDateLikeType() && !sigArgType.isDateLikeType()) { // Do not bypass temporal exactness checks through a generic string overload. return Pair.of(false, Pair.of(stringLiteralCoersionCount, timeZoneCoersionScore)); } + if (hasTimeStampNsArgument && hasOtherDateLikeArgument && hasDateLikeSignature + && realType.isTimeStampTzType() && !sigArgType.isTimeStampTzType()) { + // TIMESTAMP_TZ is a distinct temporal domain and must not bind through a mixed + // TIMESTAMP_NS/DATETIMEV2 signature. + return Pair.of(false, Pair.of(stringLiteralCoersionCount, timeZoneCoersionScore)); + } if (sigArgType.isTimeStampNsType() && !hasTimeStampNsArgument) { // TIMESTAMP_NS overloads preserve a typed nanosecond argument. They must not // change the historical binding of character or other temporal input. diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeAcquire.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeAcquire.java index e9d9471225b..4df36d3bfd4 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeAcquire.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeAcquire.java @@ -66,7 +66,11 @@ public class DateTimeAcquire { } private static Expression currentTimestamp(int precision) { - LocalDateTime dateTime = currentDateTime(); + return currentTimestamp(precision, DateUtils.getTimeZone()); + } + + private static Expression currentTimestamp(int precision, ZoneId zoneId) { + LocalDateTime dateTime = currentDateTime(zoneId); if (precision <= DateTimeV2Type.MAX_SCALE) { return DateTimeV2Literal.fromJavaDateType(dateTime, precision); } @@ -76,10 +80,14 @@ public class DateTimeAcquire { } private static LocalDateTime currentDateTime() { + return currentDateTime(DateUtils.getTimeZone()); + } + + private static LocalDateTime currentDateTime(ZoneId zoneId) { ConnectContext connectContext = ConnectContext.get(); // Executable functions are also invoked by evaluators without a session context. Instant currentTime = connectContext == null ? Instant.now() : connectContext.getStartTimeInstant(); - return LocalDateTime.ofInstant(currentTime, DateUtils.getTimeZone()); + return LocalDateTime.ofInstant(currentTime, zoneId); } /** @@ -144,6 +152,11 @@ public class DateTimeAcquire { */ @ExecFunction(name = "utc_timestamp") public static Expression utcTimestamp() { - return DateTimeV2Literal.fromJavaDateType(LocalDateTime.now(ZoneId.of("UTC+0")), 0); + return currentTimestamp(0, ZoneId.of("UTC+0")); + } + + @ExecFunction(name = "utc_timestamp") + public static Expression utcTimestamp(IntegerLiteral precision) { + return currentTimestamp(precision.getValue(), ZoneId.of("UTC+0")); } } diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java index 9f5090cb657..e6b9c797744 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java @@ -1135,6 +1135,16 @@ public class DateTimeArithmetic { return new IntegerLiteral(dateDiffByDayNumber(date1, date2)); } + @ExecFunction(name = "datediff") + public static Expression dateDiff(TimeStampNsLiteral date1, DateTimeV2Literal date2) { + return new IntegerLiteral(dateDiffByDayNumber(date1, date2)); + } + + @ExecFunction(name = "datediff") + public static Expression dateDiff(DateTimeV2Literal date1, TimeStampNsLiteral date2) { + return new IntegerLiteral(dateDiffByDayNumber(date1, date2)); + } + private static int dateDiffByDayNumber(DateLiteral date1, DateLiteral date2) { return (int) (DateTimeExtractAndTransform.calcDayNumber(date1.getYear(), date1.getMonth(), date1.getDay()) - DateTimeExtractAndTransform.calcDayNumber(date2.getYear(), date2.getMonth(), date2.getDay())); diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateDiff.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateDiff.java index ab0e1e69170..4a7d5c4a1cc 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateDiff.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateDiff.java @@ -45,6 +45,10 @@ public class DateDiff extends ScalarFunction .args(TimeStampTzType.WILDCARD, TimeStampTzType.WILDCARD), FunctionSignature.ret(IntegerType.INSTANCE) .args(TimeStampNsType.INSTANCE, TimeStampNsType.INSTANCE), + FunctionSignature.ret(IntegerType.INSTANCE) + .args(TimeStampNsType.INSTANCE, DateTimeV2Type.WILDCARD), + FunctionSignature.ret(IntegerType.INSTANCE) + .args(DateTimeV2Type.WILDCARD, TimeStampNsType.INSTANCE), FunctionSignature.ret(IntegerType.INSTANCE) .args(DateTimeV2Type.WILDCARD, DateTimeV2Type.WILDCARD), FunctionSignature.ret(IntegerType.INSTANCE).args(DateV2Type.INSTANCE, DateV2Type.INSTANCE)); diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/UtcTimestamp.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/UtcTimestamp.java index 4e35392ce63..8938899b2ca 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/UtcTimestamp.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/UtcTimestamp.java @@ -21,8 +21,8 @@ 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.AlwaysNotNullable; +import org.apache.doris.nereids.trees.expressions.functions.DateTimeWithPrecision; import org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature; -import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral; import org.apache.doris.nereids.trees.expressions.shape.LeafExpression; import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor; import org.apache.doris.nereids.types.DateTimeV2Type; @@ -36,7 +36,7 @@ import java.util.List; /** * ScalarFunction 'utc_timestamp'. This class is generated by GenerateFunction. */ -public class UtcTimestamp extends ScalarFunction +public class UtcTimestamp extends DateTimeWithPrecision implements LeafExpression, ExplicitlyCastableSignature, AlwaysNotNullable { public static final List<FunctionSignature> SIGNATURES = ImmutableList.of( @@ -63,20 +63,6 @@ public class UtcTimestamp extends ScalarFunction super(functionParams); } - @Override - public FunctionSignature computeSignature(FunctionSignature signature) { - signature = super.computeSignature(signature); - if (arity() == 1 && getArgument(0) instanceof IntegerLiteral) { - int scale = ((IntegerLiteral) getArgument(0)).getValue(); - if (scale < 0 || scale > 6) { - throw new AnalysisException("scale must be between 0 and 6"); - } - return signature.withReturnType(DateTimeV2Type.of(scale)); - } - - return signature; - } - @Override public void checkLegalityAfterRewrite() { if (arity() == 1) { diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java index 6d0cfe7139b..c616ea236ac 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java @@ -35,6 +35,7 @@ import org.apache.doris.nereids.trees.expressions.CaseWhen; import org.apache.doris.nereids.trees.expressions.Cast; import org.apache.doris.nereids.trees.expressions.ComparisonPredicate; import org.apache.doris.nereids.trees.expressions.Divide; +import org.apache.doris.nereids.trees.expressions.EqualTo; import org.apache.doris.nereids.trees.expressions.Expression; import org.apache.doris.nereids.trees.expressions.InPredicate; import org.apache.doris.nereids.trees.expressions.IntegralDivide; @@ -1422,11 +1423,13 @@ public class TypeCoercionUtils { if (isTimeStampNsAndDateLikePair(left.getDataType(), right.getDataType())) { commonType = findExactCommonTypeForTimeStampNsAndDateLike( ImmutableList.of(left, right)); - // The BE comparison kernel compares the two physical types exactly. Keep a mixed - // TIMESTAMP_NS/DATETIMEV2 comparison only when no exact literal conversion exists. - if (!commonType.isPresent() - && isTimeStampNsAndDateTimeV2Pair(left.getDataType(), right.getDataType())) { - return comparisonPredicate; + // The BE comparison kernel compares TIMESTAMP_NS and DATETIMEV2 exactly. Date-like + // peers with no exact common type can first be widened losslessly to DATETIMEV2. + if (!commonType.isPresent()) { + Optional<Expression> normalized = normalizeTimeStampNsDateLikeComparison(comparisonPredicate); + if (normalized.isPresent()) { + return normalized.get(); + } } } else if (GlobalVariable.enableNewTypeCoercionBehavior) { commonType = findWiderTypeForTwo(left.getDataType(), right.getDataType(), false, false); @@ -1513,7 +1516,7 @@ public class TypeCoercionUtils { fmtInPredicate.getCompareExpr(), fmtInPredicate.getOptions().toArray(new Expression[0]))); } else { - Optional<Expression> normalized = normalizeTimeStampNsDateTimeV2InOptions(fmtInPredicate); + Optional<Expression> normalized = normalizeTimeStampNsDateLikeInOptions(fmtInPredicate); if (normalized.isPresent()) { return normalized.get(); } @@ -1530,35 +1533,83 @@ public class TypeCoercionUtils { .orElse(fmtInPredicate); } + private static Optional<Expression> normalizeTimeStampNsDateLikeComparison( + ComparisonPredicate comparisonPredicate) { + Expression left = comparisonPredicate.left(); + Expression right = comparisonPredicate.right(); + if (left.getDataType() instanceof TimeStampNsType + && canWidenLosslesslyToDateTimeV2(right.getDataType())) { + return Optional.of(comparisonPredicate.withChildren( + left, castIfNotSameType(right, DateTimeV2Type.forType(right.getDataType())))); + } + if (right.getDataType() instanceof TimeStampNsType + && canWidenLosslesslyToDateTimeV2(left.getDataType())) { + return Optional.of(comparisonPredicate.withChildren( + castIfNotSameType(left, DateTimeV2Type.forType(left.getDataType())), right)); + } + return Optional.empty(); + } + + private static boolean canWidenLosslesslyToDateTimeV2(DataType dataType) { + return dataType instanceof DateTimeV2Type + || dataType instanceof DateTimeType + || dataType instanceof DateV2Type + || dataType instanceof DateType; + } + + private static DateTimeV2Type widestDateTimeV2Type(List<Expression> expressions) { + int scale = 0; + for (Expression expression : expressions) { + if (expression.getDataType() instanceof DateTimeV2Type) { + scale = Math.max(scale, ((DateTimeV2Type) expression.getDataType()).getScale()); + } + } + return DateTimeV2Type.of(scale); + } + /** - * Normalize mixed TIMESTAMP_NS/DATETIMEV2 literal options independently when the whole IN list - * has no common type. Exactly representable options are cast to the compare expression's type. - * A valid literal that is not representable in that type can never match and is removed. If no - * option remains, false-or-null preserves the original result for a nullable compare expression - * and therefore also preserves NOT IN semantics. + * Normalize mixed TIMESTAMP_NS/date-like options independently when the whole IN list has no + * common type. DATE, DATEV2, and DATETIME operands are widened losslessly to DATETIMEV2. + * Exactly representable literals are cast to the compare expression's type, impossible literals + * are removed, and non-literal mixed options are lowered to exact mixed equalities. Keeping NULL + * in the homogeneous IN portion preserves IN and NOT IN three-valued semantics. */ - private static Optional<Expression> normalizeTimeStampNsDateTimeV2InOptions(InPredicate inPredicate) { - Expression compareExpr = inPredicate.getCompareExpr(); - DataType compareType = compareExpr.getDataType(); - if (!(compareType instanceof TimeStampNsType) && !(compareType instanceof DateTimeV2Type)) { + private static Optional<Expression> normalizeTimeStampNsDateLikeInOptions(InPredicate inPredicate) { + Expression originalCompareExpr = inPredicate.getCompareExpr(); + DataType originalCompareType = originalCompareExpr.getDataType(); + if (!(originalCompareType instanceof TimeStampNsType) + && !canWidenLosslesslyToDateTimeV2(originalCompareType)) { return Optional.empty(); } + DateTimeV2Type dateTimeV2Type = widestDateTimeV2Type(inPredicate.children()); + Expression compareExpr = originalCompareType instanceof TimeStampNsType + ? originalCompareExpr : castIfNotSameType(originalCompareExpr, dateTimeV2Type); + DataType compareType = compareExpr.getDataType(); List<Expression> normalizedOptions = new ArrayList<>(inPredicate.getOptions().size()); + List<Expression> mixedEqualities = new ArrayList<>(); for (Expression option : inPredicate.getOptions()) { if (option.isNullLiteral() || option.getDataType().equals(compareType)) { normalizedOptions.add(castIfNotSameType(option, compareType)); continue; } - if (!isTimeStampNsAndDateTimeV2Pair(compareType, option.getDataType())) { + Expression normalizedOption = option; + if (canWidenLosslesslyToDateTimeV2(option.getDataType())) { + normalizedOption = castIfNotSameType(option, dateTimeV2Type); + } + if (normalizedOption.getDataType().equals(compareType)) { + normalizedOptions.add(normalizedOption); + continue; + } + if (!isTimeStampNsAndDateTimeV2Pair(compareType, normalizedOption.getDataType())) { return Optional.empty(); } // Only a successfully evaluated literal proves that a failed exact conversion means - // the equality is impossible. Keep non-literals and invalid explicit casts on the - // original analysis-error path. + // the equality is impossible. Non-literals use the exact mixed comparison kernel. Optional<Literal> optionLiteral = getLiteralAfterExplicitCast(option); if (!optionLiteral.isPresent()) { - return Optional.empty(); + mixedEqualities.add(processComparisonPredicate(new EqualTo(compareExpr, normalizedOption))); + continue; } Literal literal = optionLiteral.get(); if (literal.isNullLiteral()) { @@ -1574,10 +1625,18 @@ public class TypeCoercionUtils { normalizedOptions.add(castIfNotSameType(option, compareType)); } } - if (normalizedOptions.isEmpty()) { + List<Expression> disjunctions = new ArrayList<>(); + if (!normalizedOptions.isEmpty()) { + disjunctions.add(new InPredicate(compareExpr, normalizedOptions)); + } + disjunctions.addAll(mixedEqualities); + if (disjunctions.isEmpty()) { return Optional.of(ExpressionUtils.falseOrNull(compareExpr)); } - return Optional.of(new InPredicate(compareExpr, normalizedOptions)); + if (disjunctions.size() > 1 && compareExpr.containsVolatileExpression()) { + return Optional.empty(); + } + return Optional.of(ExpressionUtils.or(disjunctions)); } /** diff --git a/fe/fe-core/src/test/java/org/apache/doris/catalog/TypeTest.java b/fe/fe-core/src/test/java/org/apache/doris/catalog/TypeTest.java index 7abf30282b1..6ba59460d7c 100644 --- a/fe/fe-core/src/test/java/org/apache/doris/catalog/TypeTest.java +++ b/fe/fe-core/src/test/java/org/apache/doris/catalog/TypeTest.java @@ -38,6 +38,11 @@ public class TypeTest { .contains(org.joda.time.DateTime.class)); } + @Test + public void testTimestampNsVariantSubtype() { + Assert.assertTrue(Type.getVariantSubTypes().contains(Type.TIMESTAMP_NS)); + } + // ===================== ArrayType ===================== @Test public void testArrayOfArrayExactMatch() { diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/scalar/TimestampNsFunctionSignatureTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/scalar/TimestampNsFunctionSignatureTest.java index 2dbc35bb8cc..35639c93a75 100644 --- a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/scalar/TimestampNsFunctionSignatureTest.java +++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/scalar/TimestampNsFunctionSignatureTest.java @@ -99,6 +99,18 @@ class TimestampNsFunctionSignatureTest { () -> ExpressionAnalyzer.analyzeFunction(null, null, parser.parseExpression("now(10)"))); } + @Test + void testUtcTimestampUsesTimestampNsForNanosecondPrecision() { + assertAnalyzedType("utc_timestamp(0)", DateTimeV2Type.SYSTEM_DEFAULT); + assertAnalyzedType("utc_timestamp(6)", DateTimeV2Type.MAX); + assertAnalyzedType("utc_timestamp(7)", TimeStampNsType.INSTANCE); + assertAnalyzedType("utc_timestamp(8)", TimeStampNsType.INSTANCE); + assertAnalyzedType("utc_timestamp(9)", TimeStampNsType.INSTANCE); + Assertions.assertThrows(AnalysisException.class, + () -> ExpressionAnalyzer.analyzeFunction( + null, null, parser.parseExpression("utc_timestamp(10)"))); + } + @Test void testUntypedDatetimeInputsDoNotSelectTimestampNsSignatures() { VarcharLiteral first = new VarcharLiteral("2010-01-01 01:00:00"); @@ -139,11 +151,13 @@ class TimestampNsFunctionSignatureTest { } @Test - void testMixedDateLikeColumnsRequireExplicitCast() { + void testMixedDateLikeColumnsRequireExplicitCastExceptDateDiff() { Expression datetime = SlotReference.of("datetime", DateTimeV2Type.MAX); Expression timestampTz = SlotReference.of("timestamp_tz", TimeStampTzType.MAX); - Assertions.assertThrows(AnalysisException.class, - () -> new DateDiff(timestampNs, datetime).getSignature()); + assertSignature(new DateDiff(timestampNs, datetime), IntegerType.INSTANCE, + TimeStampNsType.INSTANCE, DateTimeV2Type.MAX); + assertSignature(new DateDiff(datetime, timestampNs), IntegerType.INSTANCE, + DateTimeV2Type.MAX, TimeStampNsType.INSTANCE); Assertions.assertThrows(AnalysisException.class, () -> new TimeDiff(timestampNs, datetime).getSignature()); Assertions.assertThrows(AnalysisException.class, @@ -177,9 +191,9 @@ class TimestampNsFunctionSignatureTest { 2500, 1, 2, 3, 4, 5, 123456); assertSignature(new DateDiff(timestampNs, insideRange), IntegerType.INSTANCE, - TimeStampNsType.INSTANCE, TimeStampNsType.INSTANCE); - Assertions.assertThrows(AnalysisException.class, - () -> new DateDiff(timestampNs, outsideRange).getSignature()); + TimeStampNsType.INSTANCE, DateTimeV2Type.MAX); + assertSignature(new DateDiff(timestampNs, outsideRange), IntegerType.INSTANCE, + TimeStampNsType.INSTANCE, DateTimeV2Type.MAX); Expression datetime = SlotReference.of("datetime", DateTimeV2Type.MAX); TimeStampNsLiteral exactTimestampNs = new TimeStampNsLiteral( @@ -187,15 +201,15 @@ class TimestampNsFunctionSignatureTest { TimeStampNsLiteral inexactTimestampNs = new TimeStampNsLiteral( "2024-01-02 03:04:05.123456001"); assertSignature(new DateDiff(datetime, exactTimestampNs), IntegerType.INSTANCE, - DateTimeV2Type.MAX, DateTimeV2Type.MAX); + DateTimeV2Type.MAX, TimeStampNsType.INSTANCE); Expression exactTimestampNsCast = new Cast( new VarcharLiteral("2024-01-02 03:04:05.123456000"), TimeStampNsType.INSTANCE); Expression coerced = TypeCoercionUtils.processBoundFunction( new DateDiff(datetime, exactTimestampNsCast)); - Assertions.assertEquals(DateTimeV2Type.MAX, coerced.child(1).getDataType()); + Assertions.assertEquals(TimeStampNsType.INSTANCE, coerced.child(1).getDataType()); Assertions.assertTrue(coerced.checkInputDataTypes().success()); - Assertions.assertThrows(AnalysisException.class, - () -> new DateDiff(datetime, inexactTimestampNs).getSignature()); + assertSignature(new DateDiff(datetime, inexactTimestampNs), IntegerType.INSTANCE, + DateTimeV2Type.MAX, TimeStampNsType.INSTANCE); Assertions.assertThrows(AnalysisException.class, () -> new SecondFloor(timestampNs, datetime).getSignature()); diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionMatrixTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionMatrixTest.java index ab6d58c5be6..82c4e4696c1 100644 --- a/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionMatrixTest.java +++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionMatrixTest.java @@ -702,7 +702,12 @@ public class TypeCoercionMatrixTest { testProcessComparisonPredicate(DateTimeV2Type.of(4), StringType.INSTANCE, DateTimeV2Type.of(6)); testProcessComparisonPredicate(TimeStampNsType.INSTANCE, DecimalV2Type.SYSTEM_DEFAULT, TimeStampNsType.INSTANCE); - testProcessComparisonPredicate(TimeStampNsType.INSTANCE, DateV2Type.INSTANCE, null); + Expression timestampNsDate = TypeCoercionUtils.processComparisonPredicate( + new EqualTo(new SlotReference("left", TimeStampNsType.INSTANCE), + new SlotReference("right", DateV2Type.INSTANCE))); + Assertions.assertEquals(TimeStampNsType.INSTANCE, timestampNsDate.child(0).getDataType()); + Assertions.assertEquals(DateTimeV2Type.SYSTEM_DEFAULT, + timestampNsDate.child(1).getDataType()); Expression timestampNsDateTime = TypeCoercionUtils.processComparisonPredicate( new EqualTo(new SlotReference("left", TimeStampNsType.INSTANCE), new SlotReference("right", DateTimeV2Type.MAX))); diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionUtilsTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionUtilsTest.java index 2013fc6a5be..b7205232d7f 100644 --- a/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionUtilsTest.java +++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionUtilsTest.java @@ -233,15 +233,22 @@ public class TypeCoercionUtilsTest { timestampNsAndTime.right().getDataType()); for (DataType widerDateLikeType : ImmutableList.of( - DateType.INSTANCE, DateV2Type.INSTANCE, DateTimeType.INSTANCE, TimeStampTzType.MAX)) { - Assertions.assertThrows(AnalysisException.class, - () -> TypeCoercionUtils.processComparisonPredicate( - new EqualTo(new SlotReference("ts", TimeStampNsType.INSTANCE), - new SlotReference("wider", widerDateLikeType)))); + DateType.INSTANCE, DateV2Type.INSTANCE, DateTimeType.INSTANCE)) { + EqualTo normalizedComparison = (EqualTo) TypeCoercionUtils.processComparisonPredicate( + new EqualTo(new SlotReference("ts", TimeStampNsType.INSTANCE), + new SlotReference("wider", widerDateLikeType))); + Assertions.assertEquals(TimeStampNsType.INSTANCE, + normalizedComparison.left().getDataType()); + Assertions.assertEquals(DateTimeV2Type.SYSTEM_DEFAULT, + normalizedComparison.right().getDataType()); Assertions.assertThrows(AnalysisException.class, () -> org.apache.doris.nereids.trees.plans.logical.LogicalSetOperation .getAssignmentCompatibleType(TimeStampNsType.INSTANCE, widerDateLikeType)); } + Assertions.assertThrows(AnalysisException.class, + () -> TypeCoercionUtils.processComparisonPredicate( + new EqualTo(new SlotReference("ts", TimeStampNsType.INSTANCE), + new SlotReference("tz", TimeStampTzType.MAX)))); Assertions.assertEquals(Optional.empty(), TypeCoercionUtils.findWiderCommonTypeByVariable( @@ -271,9 +278,12 @@ public class TypeCoercionUtilsTest { () -> new If(BooleanLiteral.TRUE, timestampNs, timestampTz).getSignature()); Assertions.assertEquals(TimeStampNsType.INSTANCE, new NullIf(timestampNs, time).getSignature().returnType); - Assertions.assertThrows(AnalysisException.class, - () -> TypeCoercionUtils.processInPredicate(new InPredicate(timestampNs, - ImmutableList.of(datetime)))); + EqualTo mixedColumnIn = (EqualTo) TypeCoercionUtils.processInPredicate( + new InPredicate(timestampNs, ImmutableList.of(datetime))); + Assertions.assertEquals(TimeStampNsType.INSTANCE, + mixedColumnIn.left().getDataType()); + Assertions.assertEquals(DateTimeV2Type.MAX, + mixedColumnIn.right().getDataType()); Assertions.assertThrows(AnalysisException.class, () -> new Nvl(timestampNs, datetime).getSignature()); Assertions.assertThrows(AnalysisException.class, @@ -327,9 +337,12 @@ public class TypeCoercionUtilsTest { safeDateLiteralComparison.left().getDataType()); Assertions.assertEquals(TimeStampNsType.INSTANCE, safeDateLiteralComparison.right().getDataType()); - Assertions.assertThrows(AnalysisException.class, - () -> TypeCoercionUtils.processComparisonPredicate( - new EqualTo(timestampNs, outsideDate))); + EqualTo outsideDateComparison = (EqualTo) TypeCoercionUtils.processComparisonPredicate( + new EqualTo(timestampNs, outsideDate)); + Assertions.assertEquals(TimeStampNsType.INSTANCE, + outsideDateComparison.left().getDataType()); + Assertions.assertEquals(DateTimeV2Type.SYSTEM_DEFAULT, + outsideDateComparison.right().getDataType()); EqualTo exactTimestampLiteralComparison = (EqualTo) TypeCoercionUtils.processComparisonPredicate( new EqualTo(dateTimeV2, exactTimestampNs)); @@ -344,9 +357,13 @@ public class TypeCoercionUtilsTest { exactDateTimestampLiteralComparison.left().getDataType()); Assertions.assertEquals(DateV2Type.INSTANCE, exactDateTimestampLiteralComparison.right().getDataType()); - Assertions.assertThrows(AnalysisException.class, - () -> TypeCoercionUtils.processComparisonPredicate( - new EqualTo(dateV2, inexactDateTimestampNs))); + EqualTo inexactDateTimestampComparison = (EqualTo) + TypeCoercionUtils.processComparisonPredicate( + new EqualTo(dateV2, inexactDateTimestampNs)); + Assertions.assertEquals(DateTimeV2Type.SYSTEM_DEFAULT, + inexactDateTimestampComparison.left().getDataType()); + Assertions.assertEquals(TimeStampNsType.INSTANCE, + inexactDateTimestampComparison.right().getDataType()); EqualTo lowerBoundaryComparison = (EqualTo) TypeCoercionUtils.processComparisonPredicate( new EqualTo(timestampNs, lowerOutsideDateTime)); @@ -390,8 +407,8 @@ public class TypeCoercionUtilsTest { new InPredicate(timestampNs, ImmutableList.of(insideDate))); safeDateIn.children().forEach(child -> Assertions.assertEquals( TimeStampNsType.INSTANCE, child.getDataType())); - Assertions.assertThrows(AnalysisException.class, - () -> TypeCoercionUtils.processInPredicate( + Assertions.assertEquals(ExpressionUtils.falseOrNull(timestampNs), + TypeCoercionUtils.processInPredicate( new InPredicate(timestampNs, ImmutableList.of(outsideDate)))); InPredicate exactTimestampIn = (InPredicate) TypeCoercionUtils.processInPredicate( new InPredicate(dateTimeV2, ImmutableList.of(exactTimestampNs))); @@ -401,9 +418,13 @@ public class TypeCoercionUtilsTest { new InPredicate(dateV2, ImmutableList.of(exactDateTimestampNs))); exactDateTimestampIn.children().forEach(child -> Assertions.assertEquals( DateV2Type.INSTANCE, child.getDataType())); - Assertions.assertThrows(AnalysisException.class, - () -> TypeCoercionUtils.processInPredicate( - new InPredicate(dateV2, ImmutableList.of(inexactDateTimestampNs)))); + Assertions.assertEquals(BooleanType.INSTANCE, + TypeCoercionUtils.processInPredicate( + new InPredicate(dateV2, ImmutableList.of(inexactDateTimestampNs))) + .getDataType()); + Assertions.assertEquals(BooleanType.INSTANCE, + TypeCoercionUtils.processInPredicate(new InPredicate(dateV2, + ImmutableList.of(insideDate, inexactDateTimestampNs))).getDataType()); Assertions.assertEquals(ExpressionUtils.falseOrNull(timestampNs), TypeCoercionUtils.processInPredicate( new InPredicate(timestampNs, ImmutableList.of(lowerOutsideDateTime)))); diff --git a/fe/fe-type/src/main/java/org/apache/doris/catalog/Type.java b/fe/fe-type/src/main/java/org/apache/doris/catalog/Type.java index fae3a32f551..f01936b4f55 100644 --- a/fe/fe-type/src/main/java/org/apache/doris/catalog/Type.java +++ b/fe/fe-type/src/main/java/org/apache/doris/catalog/Type.java @@ -293,6 +293,7 @@ public abstract class Type { variantSubTypes.add(DECIMAL256); variantSubTypes.add(DATEV2); variantSubTypes.add(DATETIMEV2); + variantSubTypes.add(TIMESTAMP_NS); variantSubTypes.add(TIMESTAMP_TZ); variantSubTypes.add(IPV4); variantSubTypes.add(IPV6); diff --git a/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_complex_type.out b/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_complex_type.out index 5f32ab26fd2..79b6eb78aca 100644 --- a/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_complex_type.out +++ b/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_complex_type.out @@ -5,3 +5,9 @@ -- !json_round_trip -- 1970-01-01 00:00:00.123456789 + +-- !typed_variant_timestamp_ns -- +1 1677-09-21 00:12:43.145224192 1969-12-31 23:59:59.999999999 1677-09-21 00:12:43.145224192 1969-12-31 23:59:59.999999999 +2 1970-01-01 00:00:00.000000001 2262-04-11 23:47:16.854775807 1970-01-01 00:00:00.000000001 2262-04-11 23:47:16.854775807 +3 \N \N \N \N + diff --git a/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_join.out b/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_join.out index 810fdc32fc4..90f03c40f9b 100644 --- a/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_join.out +++ b/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_join.out @@ -25,3 +25,35 @@ 2 1970-01-01 00:00:00.000000000 22 1970-01-01T00:00 4 \N 100 \N +-- !timestamp_ns_asof_ge -- +1 1677-09-21 00:12:43.145224192 11 1677-09-21 00:12:43.145224192 +2 1969-12-31 23:59:59.999999999 12 1969-12-31 23:59:59.999999999 +3 1970-01-01 00:00:00.000000000 13 1970-01-01 00:00:00.000000000 +4 1970-01-01 00:00:00.000000001 14 1970-01-01 00:00:00.000000001 +5 2262-04-11 23:47:16.854775807 15 2262-04-11 23:47:16.854775807 + +-- !timestamp_ns_asof_gt -- +1 1677-09-21 00:12:43.145224192 \N \N +2 1969-12-31 23:59:59.999999999 11 1677-09-21 00:12:43.145224192 +3 1970-01-01 00:00:00.000000000 12 1969-12-31 23:59:59.999999999 +4 1970-01-01 00:00:00.000000001 13 1970-01-01 00:00:00.000000000 +5 2262-04-11 23:47:16.854775807 14 1970-01-01 00:00:00.000000001 + +-- !timestamp_ns_asof_le -- +1 1677-09-21 00:12:43.145224192 11 1677-09-21 00:12:43.145224192 +2 1969-12-31 23:59:59.999999999 12 1969-12-31 23:59:59.999999999 +3 1970-01-01 00:00:00.000000000 13 1970-01-01 00:00:00.000000000 +4 1970-01-01 00:00:00.000000001 14 1970-01-01 00:00:00.000000001 +5 2262-04-11 23:47:16.854775807 15 2262-04-11 23:47:16.854775807 + +-- !timestamp_ns_asof_lt -- +1 1677-09-21 00:12:43.145224192 12 1969-12-31 23:59:59.999999999 +2 1969-12-31 23:59:59.999999999 13 1970-01-01 00:00:00.000000000 +3 1970-01-01 00:00:00.000000000 14 1970-01-01 00:00:00.000000001 +4 1970-01-01 00:00:00.000000001 15 2262-04-11 23:47:16.854775807 +5 2262-04-11 23:47:16.854775807 \N \N + +-- !timestamp_ns_asof_nullable -- +1 1970-01-01 00:00:00.000000000 11 1969-12-31 23:59:59.999999999 +2 \N \N \N + diff --git a/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_literal.out b/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_literal.out index 57ef2f48b75..c1bb05c72c7 100644 --- a/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_literal.out +++ b/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_literal.out @@ -18,6 +18,15 @@ -- !current_timestamp_support -- true true true true true true true true true true true +-- !utc_timestamp_precision_fold -- +true true true true true true true true + +-- !utc_timestamp_precision_runtime -- +true true true true true true true true + +-- !utc_timestamp_statement_snapshot -- +true + -- !timestamp_ns_defaults -- 1 1970-01-01 00:00:00.000000001 true true true true true true 2 1970-01-01 00:00:00.000000001 true true true true true true diff --git a/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_mixed_datetime_expressions.out b/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_mixed_datetime_expressions.out index 6e0b472abb7..3dcda6ff56e 100644 --- a/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_mixed_datetime_expressions.out +++ b/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_mixed_datetime_expressions.out @@ -17,6 +17,15 @@ 6 false true false true 7 \N \N \N false +-- !mixed_string_functions -- +1 2024-02-29 12:34:56.123456789|2024-02-29 12:34:56.123456 2024-02-29 12:34:56.123456|2024-02-29 12:34:56.123456789 2024-02-29 12:34:56.123456789|2024-02-29 12:34:56.123456 2024-02-29 12:34:56.123456|2024-02-29 12:34:56.123456789 +2 2024-02-29 12:34:56.123456000|2024-02-29 12:34:56.123456 2024-02-29 12:34:56.123456|2024-02-29 12:34:56.123456000 2024-02-29 12:34:56.123456000|2024-02-29 12:34:56.123456 2024-02-29 12:34:56.123456|2024-02-29 12:34:56.123456000 +3 1969-12-31 23:59:59.999999999|1969-12-31 23:59:59.999999 1969-12-31 23:59:59.999999|1969-12-31 23:59:59.999999999 1969-12-31 23:59:59.999999999|1969-12-31 23:59:59.999999 1969-12-31 23:59:59.999999|1969-12-31 23:59:59.999999999 +4 1677-09-21 00:12:43.145224192|1677-09-21 00:12:43.145224 1677-09-21 00:12:43.145224|1677-09-21 00:12:43.145224192 1677-09-21 00:12:43.145224192|1677-09-21 00:12:43.145224 1677-09-21 00:12:43.145224|1677-09-21 00:12:43.145224192 +5 2262-04-11 23:47:16.854775807|2262-04-11 23:47:16.854776 2262-04-11 23:47:16.854776|2262-04-11 23:47:16.854775807 2262-04-11 23:47:16.854775807|2262-04-11 23:47:16.854776 2262-04-11 23:47:16.854776|2262-04-11 23:47:16.854775807 +6 1970-01-01 00:00:00.000000000|2500-01-01 00:00:00.000000 2500-01-01 00:00:00.000000|1970-01-01 00:00:00.000000000 1970-01-01 00:00:00.000000000|2500-01-01 00:00:00.000000 2500-01-01 00:00:00.000000|1970-01-01 00:00:00.000000000 +7 \N \N + -- !mixed_range_literals -- true true true true @@ -39,6 +48,15 @@ true true true true -- !mixed_diff_to_datetimev2 -- 1 00:00:01.000000 1 1 1 1 +-- !mixed_column_datediff -- +1 0 0 +2 0 0 +3 0 0 +4 0 0 +5 0 0 +6 -193579 193579 +7 \N \N + -- !mixed_floor_ceil_origin -- 2023-03-01 01:02:03.123456000 2024-03-01 01:02:03.123456000 2023-12-01 01:02:03.123456000 2024-03-01 01:02:03.123456000 2024-02-01 01:02:03.123456000 2024-03-01 01:02:03.123456000 2024-02-23 01:02:03.123456000 2024-03-01 01:02:03.123456000 2024-02-29 01:02:03.123456000 2024-03-01 01:02:03.123456000 2024-02-29 12:02:03.123456000 2024-02-29 13:02:03.123456000 2024-02-29 12:34:03.123456000 2024-02-29 12:35:03.123456000 2024-02-29 12:34:56.123456000 2024-02-29 12:34:57.123456000 @@ -95,6 +113,48 @@ true 1 2024-02-29 12:34:56.123456789 ["2024-02-29 12:34:56.123456789", "2024-02- 2 1 2 2 +-- !mixed_in_columns -- +1 false true false true +2 true false true false +3 false true false true +4 false true false true +5 false true false true +6 false true false true +7 \N \N \N \N + +-- !mixed_datediff_without_lossy_cast -- +1 -173797 0 +2 -173797 0 +3 -193580 -19783 +4 -300331 -126534 +5 -86828 86969 +6 -193579 173797 +7 \N \N + +-- !mixed_date_family_comparisons -- +1 true true false false true true false false true true false false +2 false false false true false false false true false false false true +3 false false false true false false false true false false false true +4 false false false true false false false true false false false true +5 false false true false false false true false false false true false +6 \N \N \N \N \N \N \N \N \N \N \N \N + +-- !mixed_date_family_in -- +1 true true true true true true true +2 false false false false false false false +3 false false false false false false false +4 false false false false false false false +5 false false false false false false false +6 \N \N \N \N \N \N \N + +-- !mixed_date_family_boundary_literals -- +1 true \N \N +2 true \N \N +3 true \N \N +4 true \N \N +5 true true false +6 \N \N \N + -- !explicit_cast_directions -- 1 0 0 false false 2 0 0 true true diff --git a/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_partition_bucket.out b/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_partition_bucket.out index ad2433ad0eb..30f3ecde427 100644 --- a/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_partition_bucket.out +++ b/regression-test/data/datatype_p0/timestamp_ns/test_timestamp_ns_partition_bucket.out @@ -22,3 +22,14 @@ timestamp_ns \N \N 9 \N -- !list_partition_rounding -- 1970-01-01 00:00:00.123456790 1 1970-01-01 00:00:01.000000000 2 + +-- !auto_range_boundary_rows -- +1 1677-09-21 00:12:43.145224192 +2 1677-09-21 00:12:43.145224193 +3 1677-09-21 23:59:59.999999999 +4 2262-04-11 23:47:16.854775807 + +-- !auto_range_boundary_partitions -- +p16770921001243 [('1677-09-21 00:12:43.145224192'), ('1677-09-22 00:00:00.000000000')) +p22620411000000 [('2262-04-11 00:00:00.000000000'), (MAXVALUE)) + diff --git a/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_complex_type.groovy b/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_complex_type.groovy index a7a5acac50c..4fd35b5386d 100644 --- a/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_complex_type.groovy +++ b/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_complex_type.groovy @@ -57,4 +57,39 @@ suite("test_timestamp_ns_complex_type") { where dt_json is not null order by id """ + + def variantV2Function = getFeConfig("enable_variant_v2").toBoolean() ? "parse_to_variant" : "" + sql "set default_variant_enable_doc_mode = false" + sql "drop table if exists timestamp_ns_typed_variant" + sql """ + create table timestamp_ns_typed_variant ( + id int, + v variant< + 'ordinary':timestamp_ns, + 'sparse':timestamp_ns, + properties( + "variant_max_subcolumns_count" = "1", + "variant_enable_typed_paths_to_sparse" = "true") + > + ) + duplicate key(id) + distributed by hash(id) buckets 1 + properties("replication_num" = "1") + """ + sql """ + insert into timestamp_ns_typed_variant values + (1, ${variantV2Function}('{"ordinary":"1677-09-21 00:12:43.145224192","sparse":"1969-12-31 23:59:59.999999999"}')), + (2, ${variantV2Function}('{"ordinary":"1970-01-01 00:00:00.000000001","sparse":"2262-04-11 23:47:16.854775807"}')), + (3, null) + """ + sql "sync" + order_qt_typed_variant_timestamp_ns """ + select id, + cast(v['ordinary'] as timestamp_ns), + cast(v['sparse'] as timestamp_ns), + cast(cast(v['ordinary'] as timestamp_ns) as string), + cast(cast(v['sparse'] as timestamp_ns) as string) + from timestamp_ns_typed_variant + order by id + """ } diff --git a/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_join.groovy b/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_join.groovy index ba6d8dd41f1..90d700f07be 100644 --- a/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_join.groovy +++ b/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_join.groovy @@ -102,4 +102,100 @@ suite("test_timestamp_ns_join") { join timestamp_ns_join_datetimev2 r on l.dt <=> r.dt order by l.id, r.id """ + + sql "drop table if exists timestamp_ns_asof_left" + sql "drop table if exists timestamp_ns_asof_right" + for (def tableName : ["timestamp_ns_asof_left", "timestamp_ns_asof_right"]) { + sql """ + create table ${tableName} ( + id int, + k int, + dt timestamp_ns not null + ) + duplicate key(id) + distributed by hash(id) buckets 1 + properties("replication_num" = "1") + """ + } + sql """ + insert into timestamp_ns_asof_left values + (1, 1, '1677-09-21 00:12:43.145224192'), + (2, 1, '1969-12-31 23:59:59.999999999'), + (3, 1, '1970-01-01 00:00:00.000000000'), + (4, 1, '1970-01-01 00:00:00.000000001'), + (5, 1, '2262-04-11 23:47:16.854775807') + """ + sql """ + insert into timestamp_ns_asof_right values + (11, 1, '1677-09-21 00:12:43.145224192'), + (12, 1, '1969-12-31 23:59:59.999999999'), + (13, 1, '1970-01-01 00:00:00.000000000'), + (14, 1, '1970-01-01 00:00:00.000000001'), + (15, 1, '2262-04-11 23:47:16.854775807') + """ + order_qt_timestamp_ns_asof_ge """ + select l.id, l.dt, r.id, r.dt + from timestamp_ns_asof_left l + asof left join timestamp_ns_asof_right r + match_condition(l.dt >= r.dt) + on l.k = r.k + order by l.id + """ + order_qt_timestamp_ns_asof_gt """ + select l.id, l.dt, r.id, r.dt + from timestamp_ns_asof_left l + asof left join timestamp_ns_asof_right r + match_condition(l.dt > r.dt) + on l.k = r.k + order by l.id + """ + order_qt_timestamp_ns_asof_le """ + select l.id, l.dt, r.id, r.dt + from timestamp_ns_asof_left l + asof left join timestamp_ns_asof_right r + match_condition(l.dt <= r.dt) + on l.k = r.k + order by l.id + """ + order_qt_timestamp_ns_asof_lt """ + select l.id, l.dt, r.id, r.dt + from timestamp_ns_asof_left l + asof left join timestamp_ns_asof_right r + match_condition(l.dt < r.dt) + on l.k = r.k + order by l.id + """ + + sql "drop table if exists timestamp_ns_asof_nullable_left" + sql "drop table if exists timestamp_ns_asof_nullable_right" + for (def tableName : ["timestamp_ns_asof_nullable_left", "timestamp_ns_asof_nullable_right"]) { + sql """ + create table ${tableName} ( + id int, + k int, + dt timestamp_ns + ) + duplicate key(id) + distributed by hash(id) buckets 1 + properties("replication_num" = "1") + """ + } + sql """ + insert into timestamp_ns_asof_nullable_left values + (1, 1, '1970-01-01 00:00:00.000000000'), + (2, 1, null) + """ + sql """ + insert into timestamp_ns_asof_nullable_right values + (11, 1, '1969-12-31 23:59:59.999999999'), + (12, 1, null) + """ + order_qt_timestamp_ns_asof_nullable """ + select l.id, l.dt, r.id, r.dt + from timestamp_ns_asof_nullable_left l + asof left join timestamp_ns_asof_nullable_right r + match_condition(l.dt >= r.dt) + on l.k = r.k + order by l.id + """ } diff --git a/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_literal.groovy b/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_literal.groovy index f254fc06da1..89f8a17ebb6 100644 --- a/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_literal.groovy +++ b/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_literal.groovy @@ -125,6 +125,33 @@ suite("test_timestamp_ns_literal") { right(cast(current_timestamp(8) as string), 1) = '0' """ + def utcTimestampPrecisionSql = """ + select + utc_timestamp(0) is not null, + utc_timestamp(6) is not null, + left(cast(utc_timestamp(7) as string), 27) + = left(cast(utc_timestamp(9) as string), 27), + right(cast(utc_timestamp(7) as string), 2) = '00', + left(cast(utc_timestamp(8) as string), 28) + = left(cast(utc_timestamp(9) as string), 28), + right(cast(utc_timestamp(8) as string), 1) = '0', + length(substring_index(cast(utc_timestamp(9) as string), '.', -1)) = 9, + utc_timestamp(9) = utc_timestamp(9) + """ + sql "set debug_skip_fold_constant = false" + qt_utc_timestamp_precision_fold utcTimestampPrecisionSql + sql "set debug_skip_fold_constant = true" + qt_utc_timestamp_precision_runtime utcTimestampPrecisionSql + sql "set debug_skip_fold_constant = false" + qt_utc_timestamp_statement_snapshot """ + select count(distinct utc_timestamp(9)) = 1 + from numbers("number" = "4") + """ + test { + sql "select utc_timestamp(10)" + exception "must be between 0 and 9" + } + sql "drop table if exists test_timestamp_ns_current_default" sql """ create table test_timestamp_ns_current_default ( diff --git a/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_mixed_datetime_expressions.groovy b/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_mixed_datetime_expressions.groovy index a5fab8f0dd5..92d4356f588 100644 --- a/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_mixed_datetime_expressions.groovy +++ b/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_mixed_datetime_expressions.groovy @@ -62,6 +62,15 @@ suite("test_timestamp_ns_mixed_datetime_expressions") { from timestamp_ns_mixed_datetime_expressions order by id """ + order_qt_mixed_string_functions """ + select id, + concat(ts, '|', dt), + concat(dt, '|', ts), + concat_ws('|', ts, dt), + concat_ws('|', dt, ts) + from timestamp_ns_mixed_datetime_expressions + order by id + """ qt_mixed_range_literals """ select cast('1677-09-21 00:12:43.145224192' as timestamp_ns) @@ -155,6 +164,11 @@ suite("test_timestamp_ns_mixed_datetime_expressions") { from timestamp_ns_mixed_datetime_expressions where id = 1 """ + order_qt_mixed_column_datediff """ + select id, datediff(ts, dt), datediff(dt, ts) + from timestamp_ns_mixed_datetime_expressions + order by id + """ // All floor/ceil units share the same exact-literal coercion rule for the custom origin. qt_mixed_floor_ceil_origin """ @@ -320,10 +334,10 @@ suite("test_timestamp_ns_mixed_datetime_expressions") { contains "NestedLoopJoin" } - // Two non-literal temporal columns cannot be converted to either existing type without a - // possible range or precision loss. Every homogeneous diff signature must reject the pair. + // DATEDIFF consumes the two civil day numbers independently. Other difference functions still + // require a common physical type and must reject two lossily convertible columns. def diffFunctions = [ - "datediff", "timediff", "microseconds_diff", "milliseconds_diff", + "timediff", "microseconds_diff", "milliseconds_diff", "seconds_diff", "minutes_diff", "hours_diff", "days_diff", "weeks_diff", "months_diff", "quarters_diff", "years_diff" ] @@ -363,13 +377,11 @@ suite("test_timestamp_ns_mixed_datetime_expressions") { } } - test { - sql """ - select ts in (dt) - from timestamp_ns_mixed_datetime_expressions - """ - exception "unsupported in predicate" - } + order_qt_mixed_in_columns """ + select id, ts in (dt), ts not in (dt), dt in (ts), dt not in (ts) + from timestamp_ns_mixed_datetime_expressions + order by id + """ test { sql """ select case when id = 1 then ts else dt end @@ -412,24 +424,61 @@ suite("test_timestamp_ns_mixed_datetime_expressions") { exception "Can not find compatible type" } - // Literal conversions must also reject values that cannot be represented by the selected - // target because of TIMESTAMP_NS range or DATETIMEV2 scale. - test { - sql """ - select datediff( - ts, cast('2500-01-01 00:00:00.000000' as datetimev2(6))) - from timestamp_ns_mixed_datetime_expressions - """ - exception "Can not find the compatibility function signature" - } - test { - sql """ - select datediff( - dt, cast('2024-02-29 12:34:56.123456001' as timestamp_ns)) - from timestamp_ns_mixed_datetime_expressions - """ - exception "Can not find the compatibility function signature" - } + order_qt_mixed_datediff_without_lossy_cast """ + select id, + datediff(ts, cast('2500-01-01 00:00:00.000000' as datetimev2(6))), + datediff(dt, cast('2024-02-29 12:34:56.123456001' as timestamp_ns)) + from timestamp_ns_mixed_datetime_expressions + order by id + """ + + sql "drop table if exists timestamp_ns_mixed_date_families" + sql """ + create table timestamp_ns_mixed_date_families ( + id int, + ts timestamp_ns, + d date, + d2 datev2, + dt datetime + ) + duplicate key(id) + distributed by hash(id) buckets 1 + properties("replication_num" = "1") + """ + sql """ + insert into timestamp_ns_mixed_date_families values + (1, '2024-01-02 00:00:00.000000000', '2024-01-02', '2024-01-02', '2024-01-02 00:00:00'), + (2, '2024-01-02 00:00:00.000000001', '2024-01-02', '2024-01-02', '2024-01-02 00:00:00'), + (3, '1677-09-21 00:12:43.145224192', '1677-09-21', '1677-09-21', '1677-09-21 00:00:00'), + (4, '2262-04-11 23:47:16.854775807', '2262-04-11', '2262-04-11', '2262-04-11 00:00:00'), + (5, '1970-01-01 00:00:00.000000000', '2500-01-01', '2500-01-01', '2500-01-01 00:00:00'), + (6, null, null, null, null) + """ + order_qt_mixed_date_family_comparisons """ + select id, + ts = d, d = ts, ts < d, d < ts, + ts = d2, d2 = ts, ts < d2, d2 < ts, + ts = dt, dt = ts, ts < dt, dt < ts + from timestamp_ns_mixed_date_families + order by id + """ + order_qt_mixed_date_family_in """ + select id, + ts in (d), d in (ts), + ts in (d2), d2 in (ts), + ts in (dt), dt in (ts), + ts in (d, d2, dt) + from timestamp_ns_mixed_date_families + order by id + """ + order_qt_mixed_date_family_boundary_literals """ + select id, + ts < cast('2500-01-01' as datev2), + ts in (cast('2500-01-01' as datev2), cast('1970-01-01' as datev2), null), + ts not in (cast('2500-01-01' as datev2), cast('1970-01-01' as datev2), null) + from timestamp_ns_mixed_date_families + order by id + """ // Single-temporal-argument functions do not perform mixed-type resolution and are covered by // test_timestamp_ns_functions.groovy. Explicit casts remain the user-controlled escape hatch. diff --git a/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_partition_bucket.groovy b/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_partition_bucket.groovy index 653e7059720..28ee62bad00 100644 --- a/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_partition_bucket.groovy +++ b/regression-test/suites/datatype_p0/timestamp_ns/test_timestamp_ns_partition_bucket.groovy @@ -143,4 +143,33 @@ suite("test_timestamp_ns_partition_bucket") { order_qt_list_partition_rounding """ select dt, id from timestamp_ns_list_partition_rounding order by id """ + + sql "drop table if exists timestamp_ns_auto_range_boundary" + sql """ + create table timestamp_ns_auto_range_boundary ( + id int, + dt timestamp_ns not null + ) + duplicate key(id) + auto partition by range (date_trunc(dt, 'day')) () + distributed by hash(id) buckets 1 + properties("replication_num" = "1") + """ + sql """ + insert into timestamp_ns_auto_range_boundary values + (1, '1677-09-21 00:12:43.145224192'), + (2, '1677-09-21 00:12:43.145224193'), + (3, '1677-09-21 23:59:59.999999999'), + (4, '2262-04-11 23:47:16.854775807') + """ + order_qt_auto_range_boundary_rows """ + select id, dt from timestamp_ns_auto_range_boundary order by id + """ + order_qt_auto_range_boundary_partitions """ + select partition_name, partition_description + from information_schema.partitions + where table_schema = '${context.dbName}' + and table_name = 'timestamp_ns_auto_range_boundary' + order by partition_name + """ } 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 338eb2629ae..f01894e6e21 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 @@ -649,10 +649,7 @@ suite("test_date_function") { sql "select /*+SET_VAR(debug_skip_fold_constant=true)*/ utc_timestamp(),utc_timestamp() + 1;" utc_timestamp_str = sql """ select utc_timestamp(6), utc_timestamp(6) + 1 """ assertTrue(utc_timestamp_str[0].size() == 2) - test { - sql """ select utc_timestamp(7) """ - exception "scale must be between 0 and 6" - } + sql """ select utc_timestamp(7) """ test { sql """ SELECT UTC_TIMESTAMP(NULL); """ exception "UTC_TIMESTAMP argument cannot be NULL." --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
