This is an automated email from the ASF dual-hosted git repository.
yiguolei 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 d881d71cd1 [Bug](cast) Fix bug for cast function between datetimev2
and string (#18442)
d881d71cd1 is described below
commit d881d71cd1eec660c9a909aea0f63759a2fd2ce2
Author: Gabriel <gabrielleeb...@gmail.com>
AuthorDate: Fri Apr 7 22:02:15 2023 +0800
[Bug](cast) Fix bug for cast function between datetimev2 and string (#18442)
Fix bug for cast function between datetimev2 and string
---
be/src/vec/data_types/data_type_time_v2.cpp | 5 +++
be/src/vec/data_types/data_type_time_v2.h | 2 +
be/src/vec/runtime/vdatetime_value.cpp | 52 ++++++++++++++++++++--
be/src/vec/runtime/vdatetime_value.h | 4 ++
.../cast_function/test_cast_function.out | 16 ++++++-
.../cast_function/test_cast_with_scale_type.out | 4 +-
.../cast_function/test_cast_function.groovy | 38 +++++++++++++++-
7 files changed, 112 insertions(+), 9 deletions(-)
diff --git a/be/src/vec/data_types/data_type_time_v2.cpp
b/be/src/vec/data_types/data_type_time_v2.cpp
index 293eb2e9ec..789815ca29 100644
--- a/be/src/vec/data_types/data_type_time_v2.cpp
+++ b/be/src/vec/data_types/data_type_time_v2.cpp
@@ -145,6 +145,11 @@ Status DataTypeDateTimeV2::from_string(ReadBuffer& rb,
IColumn* column) const {
return Status::OK();
}
+void DataTypeDateTimeV2::to_pb_column_meta(PColumnMeta* col_meta) const {
+ IDataType::to_pb_column_meta(col_meta);
+ col_meta->mutable_decimal_param()->set_scale(_scale);
+}
+
MutableColumnPtr DataTypeDateTimeV2::create_column() const {
return DataTypeNumberBase<UInt64>::create_column();
}
diff --git a/be/src/vec/data_types/data_type_time_v2.h
b/be/src/vec/data_types/data_type_time_v2.h
index 97b5152b73..97928a9b23 100644
--- a/be/src/vec/data_types/data_type_time_v2.h
+++ b/be/src/vec/data_types/data_type_time_v2.h
@@ -90,6 +90,8 @@ public:
UInt32 get_scale() const { return _scale; }
+ void to_pb_column_meta(PColumnMeta* col_meta) const override;
+
static void cast_to_date(const UInt64 from, Int64& to);
static void cast_to_date_time(const UInt64 from, Int64& to);
static void cast_to_date_v2(const UInt64 from, UInt32& to);
diff --git a/be/src/vec/runtime/vdatetime_value.cpp
b/be/src/vec/runtime/vdatetime_value.cpp
index 2d4d78d1ab..16ee2b5664 100644
--- a/be/src/vec/runtime/vdatetime_value.cpp
+++ b/be/src/vec/runtime/vdatetime_value.cpp
@@ -1794,6 +1794,34 @@ bool DateV2Value<T>::is_invalid(uint32_t year, uint32_t
month, uint32_t day, uin
return false;
}
+template <typename T>
+void DateV2Value<T>::format_datetime(uint32_t* date_val) const {
+ // ms
+ DCHECK(date_val[6] < 1000000L);
+ // hour, minute, second
+ for (size_t i = 5; i > 2; i--) {
+ if (date_val[i] == MAX_TIME_PART_VALUE[i - 3] + 1) {
+ date_val[i] = 0;
+ date_val[i - 1] += 1;
+ }
+ }
+ // day
+ if (date_val[1] == 2 && doris::is_leap(date_val[0])) {
+ if (date_val[2] == 30) {
+ date_val[2] = 1;
+ date_val[1] += 1;
+ }
+ } else if (date_val[2] == s_days_in_month[date_val[1]] + 1) {
+ date_val[2] = 1;
+ date_val[1] += 1;
+ }
+ // month
+ if (date_val[1] == 13) {
+ date_val[1] = 1;
+ date_val[0] += 1;
+ }
+}
+
// The interval format is that with no delimiters
// YYYY-MM-DD HH-MM-DD.FFFFFF AM in default format
// 0 1 2 3 4 5 6 7
@@ -1803,7 +1831,6 @@ bool DateV2Value<T>::from_date_str(const char* date_str,
int len, int scale) {
const char* end = date_str + len;
// ONLY 2, 6 can follow by a space
const static int allow_space_mask = 4 | 64;
- const static int MAX_DATE_PARTS = 7;
uint32_t date_val[MAX_DATE_PARTS] = {0};
int32_t date_len[MAX_DATE_PARTS] = {0};
@@ -1845,11 +1872,27 @@ bool DateV2Value<T>::from_date_str(const char*
date_str, int len, int scale) {
}
if (field_idx == 6) {
// Microsecond
- temp_val *= std::pow(10, 6 - (end - start));
+ const auto ms_part = end - start;
+ temp_val *= std::pow(10, std::max(0L, 6 - ms_part));
if constexpr (is_datetime) {
if (scale >= 0) {
- temp_val /= std::pow(10, 6 - scale);
- temp_val *= std::pow(10, 6 - scale);
+ if (scale == 6 && ms_part > 6) {
+ if (ptr < end && isdigit(*ptr) && *ptr >= '5') {
+ temp_val += 1;
+ }
+ } else {
+ const int divisor = std::pow(10, 6 - scale);
+ int remainder = temp_val % divisor;
+ temp_val /= divisor;
+ if (scale < 6 && std::abs(remainder) >= (divisor >>
1)) {
+ temp_val += 1;
+ }
+ temp_val *= divisor;
+ if (temp_val == 1000000L) {
+ temp_val = 0;
+ date_val[field_idx - 1] += 1;
+ }
+ }
}
}
}
@@ -1912,6 +1955,7 @@ bool DateV2Value<T>::from_date_str(const char* date_str,
int len, int scale) {
}
if (num_field < 3) return false;
+ format_datetime(date_val);
return check_range_and_set_time(date_val[0], date_val[1], date_val[2],
date_val[3], date_val[4],
date_val[5], date_val[6]);
}
diff --git a/be/src/vec/runtime/vdatetime_value.h
b/be/src/vec/runtime/vdatetime_value.h
index a0e1739c36..622a4d9350 100644
--- a/be/src/vec/runtime/vdatetime_value.h
+++ b/be/src/vec/runtime/vdatetime_value.h
@@ -1119,6 +1119,10 @@ public:
bool from_date_format_str(const char* format, int format_len, const char*
value, int value_len,
const char** sub_val_end);
+ static constexpr int MAX_DATE_PARTS = 7;
+ static constexpr uint32_t MAX_TIME_PART_VALUE[3] = {23, 59, 59};
+
+ void format_datetime(uint32_t* date_val) const;
private:
static uint8_t calc_week(const uint32_t& day_nr, const uint16_t& year,
const uint8_t& month,
diff --git
a/regression-test/data/query_p0/sql_functions/cast_function/test_cast_function.out
b/regression-test/data/query_p0/sql_functions/cast_function/test_cast_function.out
index 410a0dca01..1fbc5e6502 100644
---
a/regression-test/data/query_p0/sql_functions/cast_function/test_cast_function.out
+++
b/regression-test/data/query_p0/sql_functions/cast_function/test_cast_function.out
@@ -17,6 +17,18 @@
-- !sql --
20
--- !sql_null_cast_bitmap --
-true
+-- !select1 --
+0 2022-12-01T22:23:24.123 2022-12-01 22:23:24.123 2022-12-01
2022-12-01 2022-12-01 2022-12-01 78.123 78.12345678
78.12346 78.12345678
+1 2022-12-01T22:23:24.123 2022-12-01 22:23:24.123456789 2022-12-01
2022-12-01 2022-12-01 2022-12-01 78.123 78.12345678
78.12346 78.12345678
+2 2022-12-01T22:23:24.123 2022-12-01 22:23:24.12341234 2022-12-01
2022-12-01 2022-12-01 2022-12-01 78.123 78.12341234
78.12341 78.12341234
+
+-- !select2 --
+0 2022-12-01T22:23:24.123 2022-12-01 22:23:24.123 2022-12-01
2022-12-01 2022-12-01 2022-12-01 78.123 78.123000000
78.12346 78.12346
+1 2022-12-01T22:23:24.123 2022-12-01 22:23:24.123 2022-12-01
2022-12-01 2022-12-01 2022-12-01 78.123 78.123000000
78.12346 78.12346
+2 2022-12-01T22:23:24.123 2022-12-01 22:23:24.123 2022-12-01
2022-12-01 2022-12-01 2022-12-01 78.123 78.123000000
78.12341 78.12341
+
+-- !select3 --
+0 2022-12-01 22:23:24.123 2022-12-01T22:23:24.123 2022-12-01
2022-12-01 2022-12-01 2022-12-01 78.12345678 78.12346
78.12345678 78.123457
+1 2022-12-01 22:23:24.123456789 2022-12-01T22:23:24.123460
2022-12-01 2022-12-01 2022-12-01 2022-12-01 78.12345678
78.12346 78.12345678 78.123457
+2 2022-12-01 22:23:24.12341234 2022-12-01T22:23:24.123410
2022-12-01 2022-12-01 2022-12-01 2022-12-01 78.12341234
78.12341 78.12341234 78.123412
diff --git
a/regression-test/data/query_p0/sql_functions/cast_function/test_cast_with_scale_type.out
b/regression-test/data/query_p0/sql_functions/cast_function/test_cast_with_scale_type.out
index def023304e..4fc3d0d1f1 100644
---
a/regression-test/data/query_p0/sql_functions/cast_function/test_cast_with_scale_type.out
+++
b/regression-test/data/query_p0/sql_functions/cast_function/test_cast_with_scale_type.out
@@ -4,8 +4,8 @@
2 2022-12-02T22:23:24.999 2022-12-02 22:23:24.999999
-- !select2 --
-1 2022-12-01T22:23:24.999 2022-12-01T22:23:24.999
-2 2022-12-02T22:23:24.999 2022-12-02T22:23:24.999
+1 2022-12-01T22:23:24.999 2022-12-01T22:23:25
+2 2022-12-02T22:23:24.999 2022-12-02T22:23:25
-- !select3 --
2022-12-02T22:23:24.999 2022-12-02T22:23:23.999
diff --git
a/regression-test/suites/query_p0/sql_functions/cast_function/test_cast_function.groovy
b/regression-test/suites/query_p0/sql_functions/cast_function/test_cast_function.groovy
index 8dbec6ee91..4d0142811e 100644
---
a/regression-test/suites/query_p0/sql_functions/cast_function/test_cast_function.groovy
+++
b/regression-test/suites/query_p0/sql_functions/cast_function/test_cast_function.groovy
@@ -23,6 +23,42 @@ suite("test_cast_function") {
qt_sql """ select cast (NULL AS CHAR(1)); """
qt_sql """ select cast ('20190101' AS CHAR(2)); """
- qt_sql_null_cast_bitmap """ select cast (case when BITMAP_EMPTY() is NULL
then null else null end as bitmap) is NULL; """
+ def tableName = "test_cast_function_nullable"
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql """
+ CREATE TABLE IF NOT EXISTS `${tableName}` (
+ `uid` int(11) NULL COMMENT "",
+ `datetimev2` datetimev2(3) NULL COMMENT "",
+ `datetimev2_str` varchar(30) NULL COMMENT "",
+ `datev2_val` datev2 NULL COMMENT "",
+ `datev2_str` varchar(30) NULL COMMENT "",
+ `date_val` date NULL COMMENT "",
+ `date_str` varchar(30) NULL COMMENT "",
+ `decimalv2_val` decimal(9,3) NULL COMMENT "",
+ `decimalv2_str` varchar(30) NULL COMMENT "",
+ `decimalv3_val` decimalv3(12,5) NULL COMMENT "",
+ `decimalv3_str` varchar(30) NULL COMMENT ""
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`uid`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`uid`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ )
+ """
+
+ sql """INSERT INTO ${tableName} values
+ (0,"2022-12-01 22:23:24.123",'2022-12-01
22:23:24.123','2022-12-01','2022-12-01','2022-12-01','2022-12-01','78.12345678','78.12345678','78.12345678','78.12345678'),
+ (1,"2022-12-01 22:23:24.123456789",'2022-12-01
22:23:24.123456789','2022-12-01','2022-12-01','2022-12-01','2022-12-01','78.12345678','78.12345678','78.12345678','78.12345678'),
+ (2,"2022-12-01 22:23:24.12341234",'2022-12-01
22:23:24.12341234','2022-12-01','2022-12-01','2022-12-01','2022-12-01','78.12341234','78.12341234','78.12341234','78.12341234')
+ """
+
+ qt_select1 "select * from ${tableName} order by uid"
+ // test cast date,datetimev2,decimalv2,decimalv3 to string
+ qt_select2 "select uid, datetimev2, cast(datetimev2 as string),
datev2_val, cast(datev2_val as string), date_val, cast(date_val as string),
decimalv2_val, cast(decimalv2_val as string), decimalv3_val,
cast(decimalv3_val as string) from ${tableName} order by uid"
+ // test cast from string to date,datetimev2,decimalv2,decimalv3
+ qt_select3 "select uid, datetimev2_str, cast(datetimev2_str as
datetimev2(5)), datev2_str, cast(datev2_str as datev2), date_str, cast(date_str
as date), decimalv2_str, cast(decimalv2_str as decimal(9,5)), decimalv3_str,
cast(decimalv3_str as decimalv3(12,6)) from ${tableName} order by uid"
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
