This is an automated email from the ASF dual-hosted git repository.
zclllyybb 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 b9f5281b566 [fix](timestamptz) Report TIMESTAMPTZ as string to MySQL
clients (#63292)
b9f5281b566 is described below
commit b9f5281b5666e125b9ed3334d06daf61e0883b18
Author: zclllyybb <[email protected]>
AuthorDate: Mon May 18 10:21:26 2026 +0800
[fix](timestamptz) Report TIMESTAMPTZ as string to MySQL clients (#63292)
TIMESTAMPTZ result rows are serialized as timezone-aware strings in the
MySQL binary protocol. FE metadata used to advertise them as
MYSQL_TYPE_DATETIME, so Connector/J decoded the length-encoded string
bytes as a MySQL datetime binary payload and failed with Invalid length
(32) for type TIMESTAMP when ResultSet.getString read a server-prepared
result.
Change PrimitiveType.toMysqlType() to return MYSQL_TYPE_STRING for
TIMESTAMPTZ, adjust the field packet length and decimals to string
semantics, and make the FE local binary result path write TIMESTAMPTZ
through the string fallback instead of the datetime binary layout. BE
serialization already uses push_timestamptz -> push_string, so no BE
payload change is needed.
Add a field-packet unit test and a regression suite that forces
ServerPreparedStatement and compares direct ResultSet.getString(ts) with
CAST(ts AS STRING).
---
.../org/apache/doris/mysql/MysqlSerializer.java | 7 +-
.../java/org/apache/doris/qe/StmtExecutor.java | 1 -
.../doris/mysql/MysqlSerializerVarbinaryTest.java | 30 ++++++++
.../org/apache/doris/catalog/PrimitiveType.java | 5 +-
.../timestamptz/test_cast_timestamptz.out | 4 +-
.../timestamptz/test_timestamptz_cast.out | 12 +--
.../sql-functions/doc_date_functions_test.out | 48 ++++++------
.../paimon/test_paimon_catalog_timestamp_tz.out | 12 +--
.../test_timestamptz_jdbc_binary_protocol.groovy | 85 ++++++++++++++++++++++
9 files changed, 160 insertions(+), 44 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/mysql/MysqlSerializer.java
b/fe/fe-core/src/main/java/org/apache/doris/mysql/MysqlSerializer.java
index 4c8d1824104..4469830f320 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/mysql/MysqlSerializer.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/mysql/MysqlSerializer.java
@@ -288,9 +288,11 @@ public class MysqlSerializer {
case DATEV2:
case DATE:
return 10;
+ case TIMESTAMPTZ:
+ // yyyy-MM-dd HH:mm:ss[.ffffff]+HH:mm
+ return 32;
case DATETIME:
- case DATETIMEV2:
- case TIMESTAMPTZ: {
+ case DATETIMEV2: {
if (type.getPrimitiveType().isTimeType()) {
return 10;
} else {
@@ -338,7 +340,6 @@ public class MysqlSerializer {
case DECIMAL256:
case TIMEV2:
case DATETIMEV2:
- case TIMESTAMPTZ:
return ((ScalarType) type).decimalScale();
case FLOAT:
case DOUBLE:
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/StmtExecutor.java
b/fe/fe-core/src/main/java/org/apache/doris/qe/StmtExecutor.java
index 11d633a5644..3ebd2f69307 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/StmtExecutor.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/StmtExecutor.java
@@ -1870,7 +1870,6 @@ public class StmtExecutor {
break;
case DATETIME:
case DATETIMEV2:
- case TIMESTAMPTZ:
DateTimeV2Literal datetime = new
DateTimeV2Literal(item);
long microSecond = datetime.getMicroSecond();
//
https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_com_query_response_text_resultset.html
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/mysql/MysqlSerializerVarbinaryTest.java
b/fe/fe-core/src/test/java/org/apache/doris/mysql/MysqlSerializerVarbinaryTest.java
index 86688666feb..9f949a566f0 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/mysql/MysqlSerializerVarbinaryTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/mysql/MysqlSerializerVarbinaryTest.java
@@ -17,6 +17,7 @@
package org.apache.doris.mysql;
+import org.apache.doris.catalog.MysqlColType;
import org.apache.doris.catalog.ScalarType;
import org.apache.doris.catalog.Type;
@@ -118,6 +119,35 @@ public class MysqlSerializerVarbinaryTest {
Assertions.assertEquals(0, flags); // not BINARY
}
+ @Test
+ public void testFieldPacketForTimestampTzUsesStringMetadata() {
+ MysqlSerializer ser = MysqlSerializer.newInstance();
+ Type type = ScalarType.createTimeStampTzType(6);
+ ser.writeField("ts", type);
+ byte[] out = ser.toArray();
+
+ Assertions.assertEquals(MysqlColType.MYSQL_TYPE_STRING,
type.getPrimitiveType().toMysqlType());
+
+ int off = skipFieldHeaderStrings(out);
+ int charset = leUInt2(out, off);
+ Assertions.assertEquals(33, charset); // utf8_general_ci
+ off += 2;
+
+ long displayLen = leUInt4(out, off);
+ Assertions.assertEquals(32L, displayLen);
+ off += 4;
+
+ int colType = out[off] & 0xFF;
+ Assertions.assertEquals(MysqlColType.MYSQL_TYPE_STRING.getCode(),
colType);
+ off += 1;
+
+ int flags = leUInt2(out, off);
+ Assertions.assertEquals(0, flags);
+ off += 2;
+
+ Assertions.assertEquals(0, out[off] & 0xFF);
+ }
+
@Test
public void testWriteLenEncodedBytesPreservesNullByte() {
MysqlSerializer ser = MysqlSerializer.newInstance();
diff --git
a/fe/fe-type/src/main/java/org/apache/doris/catalog/PrimitiveType.java
b/fe/fe-type/src/main/java/org/apache/doris/catalog/PrimitiveType.java
index deec4343659..216db92e216 100644
--- a/fe/fe-type/src/main/java/org/apache/doris/catalog/PrimitiveType.java
+++ b/fe/fe-type/src/main/java/org/apache/doris/catalog/PrimitiveType.java
@@ -427,9 +427,10 @@ public enum PrimitiveType {
case DATE:
case DATEV2:
return MysqlColType.MYSQL_TYPE_DATE;
+ case TIMESTAMPTZ:
+ return MysqlColType.MYSQL_TYPE_STRING;
case DATETIME:
- case DATETIMEV2:
- case TIMESTAMPTZ: {
+ case DATETIMEV2: {
if (isTimeType) {
return MysqlColType.MYSQL_TYPE_TIME;
} else {
diff --git
a/regression-test/data/datatype_p0/timestamptz/test_cast_timestamptz.out
b/regression-test/data/datatype_p0/timestamptz/test_cast_timestamptz.out
index 0e2afc198e0..d9021431ae7 100644
--- a/regression-test/data/datatype_p0/timestamptz/test_cast_timestamptz.out
+++ b/regression-test/data/datatype_p0/timestamptz/test_cast_timestamptz.out
@@ -15,7 +15,7 @@
\N \N \N \N \N \N \N \N
-- !sql --
-2020-01-01T00:00:00.000000124
+2020-01-01 00:00:00.124+07:00
-- !sql --
2020-01-01T04:00:00.124
@@ -33,7 +33,7 @@
2020-01-01T04:00 2020-06-01T16:34:56 2021-01-01T03:59:59
2020-01-01T10:00 2020-06-01T22:34:56 2021-01-01T09:59:59
2020-01-01T07:00 2020-06-01T19:34:56 2021-01-01T06:59:59
-- !sql --
-2020-01-01T00:00:00.000000124
+2020-01-01 00:00:00.124+07:00
-- !sql --
2020-01-01T04:00:00.124
diff --git
a/regression-test/data/datatype_p0/timestamptz/test_timestamptz_cast.out
b/regression-test/data/datatype_p0/timestamptz/test_timestamptz_cast.out
index 5774f9f82aa..983496b5601 100644
--- a/regression-test/data/datatype_p0/timestamptz/test_timestamptz_cast.out
+++ b/regression-test/data/datatype_p0/timestamptz/test_timestamptz_cast.out
@@ -12,12 +12,12 @@
2020-01-02 00:00:00.00000+08:00
-- !cast_1 --
-1 2020-01-01T05:00:00.000000123 2020-01-01 05:00:00.12300+08:00
-2 2020-06-01T15:00:00.000000456 2020-06-01 15:00:00.45600+08:00
-3 2020-01-01T07:59:59.000000789 2020-01-01 07:59:59.78900+08:00
+1 2020-01-01 05:00:00.123+08:00 2020-01-01 05:00:00.12300+08:00
+2 2020-06-01 15:00:00.456+08:00 2020-06-01 15:00:00.45600+08:00
+3 2020-01-01 07:59:59.789+08:00 2020-01-01 07:59:59.78900+08:00
-- !cast_2 --
-1 2020-01-01 05:00:00.12345+08:00 2020-01-01T05:00:00.000000123
-2 2020-06-01 15:00:00.45678+08:00 2020-06-01T15:00:00.000000457
-3 2020-01-01 07:59:59.78901+08:00 2020-01-01T07:59:59.000000789
+1 2020-01-01 05:00:00.12345+08:00 2020-01-01 05:00:00.123+08:00
+2 2020-06-01 15:00:00.45678+08:00 2020-06-01 15:00:00.457+08:00
+3 2020-01-01 07:59:59.78901+08:00 2020-01-01 07:59:59.789+08:00
diff --git
a/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
b/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
index 63cdecd6d68..65f09452a2d 100644
---
a/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
+++
b/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
@@ -681,13 +681,13 @@ gdaskpdp
2023-01-02 08:00:00+08:00
-- !hours_add_8 --
-2024-03-01T13:30:45.000000123
+2024-03-01 13:30:45.123+08:00
-- !hours_add_9 --
0001-01-03 08:00:00+08:00
-- !hours_add_10 --
-2023-06-15T08:45:30.000000555
+2023-06-15 08:45:30.555+08:00
-- !hours_add_11 --
\N
@@ -735,13 +735,13 @@ gdaskpdp
2022-12-31 08:00:00+08:00
-- !hours_sub_8 --
-2024-02-29T13:30:45.000000123
+2024-02-29 13:30:45.123+08:00
-- !hours_sub_9 --
0000-12-30 08:00:00+08:00
-- !hours_sub_10 --
-2023-06-15T20:45:30.000000555
+2023-06-15 20:45:30.555+08:00
-- !hours_sub_11 --
\N
@@ -1113,13 +1113,13 @@ gdaskpdp
2023-01-01 09:00:00+08:00
-- !minutes_add_9 --
-2024-03-01T02:19:56.000000123
+2024-03-01 02:19:56.123+08:00
-- !minutes_add_10 --
0001-01-01 08:01:00+08:00
-- !minutes_add_11 --
-2023-06-15T14:15:30.000000555
+2023-06-15 14:15:30.555+08:00
-- !minutes_add_12 --
\N
@@ -1167,13 +1167,13 @@ gdaskpdp
2023-01-01 07:00:00+08:00
-- !minutes_sub_8 --
-2024-03-01T00:49:56.000000123
+2024-03-01 00:49:56.123+08:00
-- !minutes_sub_9 --
0001-01-01 07:59:00+08:00
-- !minutes_sub_10 --
-2023-06-15T15:15:30.000000555
+2023-06-15 15:15:30.555+08:00
-- !minutes_sub_11 --
\N
@@ -1647,13 +1647,13 @@ da fanadur
2023-07-01 08:00:00+08:00
-- !quarters_add_10 --
-2024-05-30T01:30:45.000000123
+2024-05-30 01:30:45.123+08:00
-- !quarters_add_11 --
0002-01-01 08:00:00+08:00
-- !quarters_add_12 --
-2022-12-15T14:45:30.000000555
+2022-12-15 14:45:30.555+08:00
-- !quarters_add_13 --
\N
@@ -1686,13 +1686,13 @@ da fanadur
2022-07-01 08:00:00+08:00
-- !quarters_sub_10 --
-2023-11-30T01:30:45.000000123
+2023-11-30 01:30:45.123+08:00
-- !quarters_sub_11 --
0000-01-01 08:00:00+08:00
-- !quarters_sub_12 --
-2023-12-15T14:45:30.000000555
+2023-12-15 14:45:30.555+08:00
-- !quarters_sub_13 --
\N
@@ -1776,13 +1776,13 @@ da fanadur
2023-01-01 09:00:00+08:00
-- !seconds_add_9 --
-2024-03-01T01:35:11.000000123
+2024-03-01 01:35:11.123+08:00
-- !seconds_add_10 --
0001-01-01 08:01:00+08:00
-- !seconds_add_11 --
-2023-06-15T14:45:00.000000555
+2023-06-15 14:45:00.555+08:00
-- !seconds_add_12 --
\N
@@ -1830,13 +1830,13 @@ da fanadur
2023-01-01 07:00:00+08:00
-- !seconds_sub_9 --
-2024-03-01T01:34:41.000000123
+2024-03-01 01:34:41.123+08:00
-- !seconds_sub_10 --
0001-01-01 07:59:00+08:00
-- !seconds_sub_11 --
-2023-06-15T14:46:00.000000555
+2023-06-15 14:46:00.555+08:00
-- !seconds_sub_12 --
\N
@@ -2232,13 +2232,13 @@ da fanadur
2023-01-15 08:00:00+08:00
-- !weeks_add_8 --
-2024-03-08T01:30:45.000000123
+2024-03-08 01:30:45.123+08:00
-- !weeks_add_9 --
0001-01-29 08:00:00+08:00
-- !weeks_add_10 --
-2023-06-01T14:45:30.000000555
+2023-06-01 14:45:30.555+08:00
-- !weeks_add_11 --
\N
@@ -2292,13 +2292,13 @@ da fanadur
2022-12-18 08:00:00+08:00
-- !weeks_sub_8 --
-2024-02-23T01:30:45.000000123
+2024-02-23 01:30:45.123+08:00
-- !weeks_sub_9 --
0000-12-04 08:00:00+08:00
-- !weeks_sub_10 --
-2023-06-29T14:45:30.000000555
+2023-06-29 14:45:30.555+08:00
-- !weeks_sub_11 --
\N
@@ -2418,13 +2418,13 @@ da fanadur
2025-01-01 08:00:00+08:00
-- !years_add_9 --
-2025-03-01T01:30:45.000000123
+2025-03-01 01:30:45.123+08:00
-- !years_add_10 --
0011-01-01 08:00:00+08:00
-- !years_add_11 --
-2020-06-15T14:45:30.000000555
+2020-06-15 14:45:30.555+08:00
-- !years_add_12 --
\N
@@ -2472,13 +2472,13 @@ da fanadur
2021-01-01 08:00:00+08:00
-- !years_sub_8 --
-2023-03-01T01:30:45.000000123
+2023-03-01 01:30:45.123+08:00
-- !years_sub_9 --
9989-01-01 08:00:00+08:00
-- !years_sub_10 --
-2026-06-15T14:45:30.000000555
+2026-06-15 14:45:30.555+08:00
-- !years_sub_11 --
0003-01-01 08:00:00+08:00
diff --git
a/regression-test/data/external_table_p0/paimon/test_paimon_catalog_timestamp_tz.out
b/regression-test/data/external_table_p0/paimon/test_paimon_catalog_timestamp_tz.out
index 4f9965aaed9..66207238741 100644
---
a/regression-test/data/external_table_p0/paimon/test_paimon_catalog_timestamp_tz.out
+++
b/regression-test/data/external_table_p0/paimon/test_paimon_catalog_timestamp_tz.out
@@ -4,9 +4,9 @@ id int Yes true \N
ts_ltz timestamptz(3) Yes true \N WITH_TIMEZONE
-- !jni_1 --
-1 2024-01-01T10:00
-2 2026-01-06T16:13:12
-3 2024-11-11T11:11:11.000000123
+1 2024-01-01 10:00:00.000+08:00
+2 2026-01-06 16:13:12.000+08:00
+3 2024-11-11 11:11:11.123+08:00
-- !jni_1_cast --
1 2024-01-01 10:00:00.000+08:00
@@ -18,9 +18,9 @@ id int Yes true \N
ts_ltz timestamptz(3) Yes true \N WITH_TIMEZONE
-- !native_1 --
-1 2024-01-01T10:00
-2 2026-01-06T16:13:12
-3 2024-11-11T11:11:11.000000123
+1 2024-01-01 10:00:00.000+08:00
+2 2026-01-06 16:13:12.000+08:00
+3 2024-11-11 11:11:11.123+08:00
-- !native_1_cast --
1 2024-01-01 10:00:00.000+08:00
diff --git
a/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_jdbc_binary_protocol.groovy
b/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_jdbc_binary_protocol.groovy
new file mode 100644
index 00000000000..c04cc516770
--- /dev/null
+++
b/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_jdbc_binary_protocol.groovy
@@ -0,0 +1,85 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+import com.mysql.cj.jdbc.ServerPreparedStatement
+
+import java.sql.PreparedStatement
+import java.sql.ResultSet
+import java.sql.SQLException
+
+suite("test_timestamptz_jdbc_binary_protocol") {
+ String tableName = "test_timestamptz_jdbc_binary_protocol"
+ String dbName = "regression_test_datatype_p0_timestamptz"
+ def user = context.config.jdbcUser
+ def password = context.config.jdbcPassword
+
+ sql "SET time_zone = '+00:00'"
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql """
+ CREATE TABLE ${tableName} (
+ id INT,
+ ts TIMESTAMPTZ(6),
+ note VARCHAR(16)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES("replication_num" = "1")
+ """
+ sql """
+ INSERT INTO ${tableName} VALUES
+ (1, NULL, 'null'),
+ (2, '2024-01-01 08:00:00.123456 +08:00', 'equiv_utc'),
+ (3, '2024-01-01 00:00:01.654321 +00:00', 'micro')
+ """
+
+ String url = getServerPrepareJdbcUrl(context.config.jdbcUrl, dbName) +
+ "&emulateUnsupportedPstmts=true&useLocalSessionState=true"
+ logger.info("jdbc prepare statement url: ${url}")
+
+ connect(user, password, url) {
+ sql "SET time_zone = '+00:00'"
+
+ PreparedStatement stmt = prepareStatement("""
+ SELECT id, ts, CAST(ts AS STRING) AS ts_text, note
+ FROM ${tableName}
+ ORDER BY id
+ """)
+ assertEquals(ServerPreparedStatement, stmt.class)
+
+ ResultSet rs = stmt.executeQuery()
+ int rowCount = 0
+ try {
+ while (rs.next()) {
+ rowCount++
+ String direct
+ try {
+ direct = rs.getString(2)
+ } catch (SQLException e) {
+ logger.info("failed to read TIMESTAMPTZ directly with
ResultSet.getString", e)
+ throw e
+ }
+
+ String castText = rs.getString(3)
+ assertEquals(castText, direct)
+ }
+ assertEquals(3, rowCount)
+ } finally {
+ rs.close()
+ stmt.close()
+ }
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
