This is an automated email from the ASF dual-hosted git repository.
csun5285 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 eaef55a7764 [fix](jdbc) reject a PostgreSQL datetime value the cached
precision cannot hold (#66677)
eaef55a7764 is described below
commit eaef55a7764257070478a6a451b1f5bac246c50c
Author: Chenyang Sun <[email protected]>
AuthorDate: Mon Aug 17 14:37:11 2026 +0800
[fix](jdbc) reject a PostgreSQL datetime value the cached precision cannot
hold (#66677)
Problem Summary:
After a DDL on the remote side, the JDBC catalog's cached schema still
reports the old type until it is refreshed.
1. PostgreSQL: create a table with a `timestamp(0)` column.
2. Doris: create the JDBC catalog and read that table once. This caches
`datetime(0)`, and is the step that is easy to miss -- without it the
read in step 4 fetches the metadata fresh and nothing goes wrong.
3. PostgreSQL: `ALTER COLUMN ... TYPE timestamp(6)` and insert two rows
differing only in the microseconds. Do not refresh the catalog.
4. Doris: `INSERT INTO ... SELECT` into a unique table whose key
includes that column.
### Release note
Reading a JDBC catalog table now fails with a clear error when the
remote column was widened after the catalog cached its schema.
Run `REFRESH TABLE <catalog>.<db>.<table>` to reconcile it.
---
.../apache/doris/jdbc/PostgreSQLTypeHandler.java | 38 ++++-
.../jdbc/test_jdbc_stale_schema_precision.out | 38 +++++
.../jdbc/test_jdbc_stale_schema_precision.groovy | 184 +++++++++++++++++++++
3 files changed, 256 insertions(+), 4 deletions(-)
diff --git
a/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/PostgreSQLTypeHandler.java
b/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/PostgreSQLTypeHandler.java
index d5589604257..44eac6e648c 100644
---
a/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/PostgreSQLTypeHandler.java
+++
b/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/PostgreSQLTypeHandler.java
@@ -103,9 +103,9 @@ public class PostgreSQLTypeHandler extends
DefaultTypeHandler {
case DATETIMEV2:
return createConverter(input -> {
if (input instanceof Timestamp) {
- return ((Timestamp) input).toLocalDateTime();
+ return checkSubSecondFits(((Timestamp)
input).toLocalDateTime(), columnType);
} else if (input instanceof OffsetDateTime) {
- return ((OffsetDateTime) input).toLocalDateTime();
+ return checkSubSecondFits(((OffsetDateTime)
input).toLocalDateTime(), columnType);
} else if (input instanceof java.sql.Date) {
return ((java.sql.Date)
input).toLocalDate().atStartOfDay();
}
@@ -114,8 +114,8 @@ public class PostgreSQLTypeHandler extends
DefaultTypeHandler {
case TIMESTAMPTZ:
return createConverter(input -> {
if (input instanceof Timestamp) {
- return LocalDateTime.ofInstant(
- ((Timestamp) input).toInstant(),
java.time.ZoneOffset.UTC);
+ return checkSubSecondFits(LocalDateTime.ofInstant(
+ ((Timestamp) input).toInstant(),
java.time.ZoneOffset.UTC), columnType);
}
return input;
}, LocalDateTime.class);
@@ -230,4 +230,34 @@ public class PostgreSQLTypeHandler extends
DefaultTypeHandler {
return input;
}
}
+
+ // A nanosecond value fits n sub-second digits when it divides
SUB_SECOND_UNIT[n] evenly.
+ // It stops at six because PostgreSQL keeps no more than that, so a column
declared with six
+ // can always hold what arrives.
+ private static final int[] SUB_SECOND_UNIT = {
+ 1000000000, 100000000, 10000000, 1000000, 100000, 10000};
+
+ /**
+ * Fail when the value carries more sub-second digits than the column
declares.
+ *
+ * <p>A PostgreSQL table is created with timestamp(0), so the schema cache
holds datetime(0).
+ * The column is then altered to timestamp(6), but the cache does not
change. Fail here, so
+ * the user sees it.
+ *
+ * <p>PostgreSQL only: other drivers report a column's precision less
faithfully, and the same
+ * check there would reject healthy rows.
+ */
+ private static LocalDateTime checkSubSecondFits(LocalDateTime v,
ColumnType columnType) {
+ int precision = columnType.getPrecision();
+ if (precision < 0 || precision >= SUB_SECOND_UNIT.length) {
+ return v;
+ }
+ if (v.getNano() % SUB_SECOND_UNIT[precision] != 0) {
+ throw new RuntimeException(String.format(
+ "Column '%s' is datetime precision %d but the value
carries more digits than"
+ + " that. The cached external schema may be stale
-- refresh the catalog.",
+ columnType.getName(), precision));
+ }
+ return v;
+ }
}
diff --git
a/regression-test/data/external_table_p0/jdbc/test_jdbc_stale_schema_precision.out
b/regression-test/data/external_table_p0/jdbc/test_jdbc_stale_schema_precision.out
new file mode 100644
index 00000000000..454ae790bfa
--- /dev/null
+++
b/regression-test/data/external_table_p0/jdbc/test_jdbc_stale_schema_precision.out
@@ -0,0 +1,38 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !cached_datetime_type --
+mchid text Yes true \N
+orderid text Yes true \N
+createtime datetime Yes true \N
+status int Yes true \N
+
+-- !stale_datetime_type --
+mchid text Yes true \N
+orderid text Yes true \N
+createtime datetime Yes true \N
+status int Yes true \N
+
+-- !cached_whole_seconds_type --
+id int Yes true \N
+ts datetime Yes true \N
+
+-- !drifted_but_fits --
+2026-06-19T12:23:23
+
+-- !refreshed_datetime_type --
+mchid text Yes true \N
+orderid text Yes true \N
+createtime datetime(6) Yes true \N
+status int Yes true \N
+
+-- !refreshed_datetime_value --
+2026-06-19T12:23:23.486067
+
+-- !narrowed_source --
+2026-06-19T12:23:23
+
+-- !after_refresh_dedup --
+M001 ORD1 2026-06-19T12:23:23 0
+
+-- !wide_destination --
+2026-06-19T12:23:23.486067 486067
+
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_jdbc_stale_schema_precision.groovy
b/regression-test/suites/external_table_p0/jdbc/test_jdbc_stale_schema_precision.groovy
new file mode 100644
index 00000000000..6638eb1e2fb
--- /dev/null
+++
b/regression-test/suites/external_table_p0/jdbc/test_jdbc_stale_schema_precision.groovy
@@ -0,0 +1,184 @@
+// 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 java.sql.Connection
+import java.sql.DriverManager
+
+// A JDBC catalog caches the remote column definition. When the remote DDL
widens a column, the
+// cache keeps reporting the narrow one until it is refreshed, so the planner
sees source and
+// target as the same type and emits no cast. The value then arrives carrying
more than the
+// planned column can hold.
+//
+// For a DATETIMEV2 key column that is not cosmetic: the sub-second digits
live in the same 64-bit
+// word the storage layer encodes as a key, so two rows the column claims are
equal become two
+// distinct keys and a unique table stops deduplicating them.
+//
+// The scanner checks the value against the column it is writing into, not the
source schema
+// against the cached one. A value that fits is written whatever the source
now says, so these
+// cases assert on values rather than on declarations.
+suite("test_jdbc_stale_schema_precision", "p0,external") {
+ String enabled = context.config.otherConfigs.get("enableJdbcTest")
+ if (enabled == null || !enabled.equalsIgnoreCase("true")) {
+ return
+ }
+
+ String externalEnvIp = context.config.otherConfigs.get("externalEnvIp")
+ String pg_port = context.config.otherConfigs.get("pg_14_port")
+ String s3_endpoint = getS3Endpoint()
+ String bucket = getS3BucketName()
+ String driver_url =
"https://${bucket}.${s3_endpoint}/regression/jdbc_driver/postgresql-42.5.0.jar";
+
+ String catalog_name = "test_jdbc_stale_schema_precision_catalog"
+ String internal_db = "regression_test_jdbc_stale_schema_precision"
+ String pg_schema = "stale_precision"
+ String pg_url =
"jdbc:postgresql://${externalEnvIp}:${pg_port}/postgres?useSSL=false"
+
+ Class.forName("org.postgresql.Driver")
+
+ // The catalog cannot issue DDL against the source, so drive PostgreSQL
directly.
+ def onPostgres = { List<String> statements ->
+ Connection conn = DriverManager.getConnection(pg_url, "postgres",
"123456")
+ try {
+ def stmt = conn.createStatement()
+ try {
+ statements.each { stmt.execute(it) }
+ } finally {
+ stmt.close()
+ }
+ } finally {
+ conn.close()
+ }
+ }
+
+ sql """drop catalog if exists ${catalog_name}"""
+ sql """drop database if exists internal.${internal_db}"""
+ sql """create database internal.${internal_db}"""
+
+ onPostgres([
+ "DROP SCHEMA IF EXISTS ${pg_schema} CASCADE",
+ "CREATE SCHEMA ${pg_schema}",
+ // Declared narrow to begin with: this is what the catalog will cache.
+ """CREATE TABLE ${pg_schema}.orders (
+ mchid varchar, orderid varchar, createtime timestamp(0), status
int)""",
+ "INSERT INTO ${pg_schema}.orders VALUES ('M001','ORD1','2026-06-19
12:00:00',0)",
+ ])
+
+ sql """create catalog ${catalog_name} properties(
+ "type"="jdbc",
+ "user"="postgres",
+ "password"="123456",
+ "jdbc_url" = "${pg_url}¤tSchema=${pg_schema}",
+ "driver_url" = "${driver_url}",
+ "driver_class" = "org.postgresql.Driver"
+ );"""
+
+ // Populate the catalog's schema cache while the remote column is still
narrow.
+ qt_cached_datetime_type """desc ${catalog_name}.${pg_schema}.orders"""
+
+ // Widen the remote column and write values only the wider type can hold.
The catalog is
+ // deliberately not refreshed, so from here on its cache disagrees with
the source.
+ onPostgres([
+ "ALTER TABLE ${pg_schema}.orders ALTER COLUMN createtime TYPE
timestamp(6)",
+ "DELETE FROM ${pg_schema}.orders",
+ """INSERT INTO ${pg_schema}.orders VALUES
+ ('M001','ORD1','2026-06-19 12:23:23.486067',1),
+ ('M001','ORD1','2026-06-19 12:23:23.000000',2)""",
+ ])
+
+ // Still the narrow type -- the cache has not caught up.
+ qt_stale_datetime_type """desc ${catalog_name}.${pg_schema}.orders"""
+
+ sql """create table internal.${internal_db}.orders_target (
+ `mchid` varchar(65533) NOT NULL,
+ `createtime` datetime NOT NULL,
+ `orderid` varchar(65533) NOT NULL,
+ `status` int NULL
+ ) ENGINE=OLAP
+ UNIQUE KEY(`mchid`, `createtime`, `orderid`)
+ DISTRIBUTED BY HASH(`mchid`) BUCKETS 1
+ PROPERTIES ("replication_num" = "1",
"enable_unique_key_merge_on_write" = "true");"""
+
+ // The plan was built from the cached, narrower type, so no cast exists
and the extra digits
+ // would land in a column that cannot hold them -- splitting one logical
key into two and
+ // breaking dedup on the unique table. The scanner refuses the value
instead of writing it.
+ test {
+ sql """insert into internal.${internal_db}.orders_target
+ select mchid, createtime, orderid, status
+ from ${catalog_name}.${pg_schema}.orders;"""
+ exception "carries more digits than that"
+ }
+
+ test {
+ sql """select createtime from ${catalog_name}.${pg_schema}.orders"""
+ exception "carries more digits than that"
+ }
+
+ // A drifted column whose values all still fit is read normally: the check
is about what
+ // arrives, not about what the cache declares. This is the difference from
rejecting on the
+ // schema, and it is what keeps a source the catalog maps down -- an
Oracle TIMESTAMP(9)
+ // planned as DATETIMEV2(6) -- from failing every scan.
+ onPostgres([
+ "CREATE TABLE ${pg_schema}.whole_seconds (id int, ts timestamp(0))",
+ "INSERT INTO ${pg_schema}.whole_seconds VALUES (1, '2026-06-19
12:23:23')",
+ ])
+ qt_cached_whole_seconds_type """desc
${catalog_name}.${pg_schema}.whole_seconds"""
+ onPostgres(["ALTER TABLE ${pg_schema}.whole_seconds ALTER COLUMN ts TYPE
timestamp(6)"])
+ qt_drifted_but_fits """select ts from
${catalog_name}.${pg_schema}.whole_seconds"""
+
+ // Refreshing reconciles the cache. Every read that failed above has to
succeed now and
+ // return the value the source actually holds.
+ sql """refresh catalog ${catalog_name}"""
+
+ qt_refreshed_datetime_type """desc ${catalog_name}.${pg_schema}.orders"""
+ qt_refreshed_datetime_value """select createtime from
${catalog_name}.${pg_schema}.orders
+ where status = 1"""
+
+ // A source narrower than the plan cannot produce a value the column will
not hold, so it is
+ // left alone -- only the widening direction can be a problem.
+ onPostgres([
+ "CREATE TABLE ${pg_schema}.narrowed (id int, ts timestamp(6))",
+ "INSERT INTO ${pg_schema}.narrowed VALUES (1, '2026-06-19
12:23:23.486067')",
+ ])
+ sql """select ts from ${catalog_name}.${pg_schema}.narrowed"""
+ onPostgres(["ALTER TABLE ${pg_schema}.narrowed ALTER COLUMN ts TYPE
timestamp(0)"])
+ qt_narrowed_source """select ts from
${catalog_name}.${pg_schema}.narrowed"""
+
+ // With the cache reconciled the planner can see the source is wider and
emits the narrowing
+ // cast, so the same insert now succeeds: both source rows round onto one
second-granularity
+ // key and the unique table keeps one of them. Which one is not pinned --
there is no
+ // sequence column and the source query has no ordering -- so assert the
key collapsed and
+ // carries no sub-second digits, not which row won.
+ sql """insert into internal.${internal_db}.orders_target
+ select mchid, createtime, orderid, status
+ from ${catalog_name}.${pg_schema}.orders;"""
+ qt_after_refresh_dedup """select mchid, orderid, createtime,
+ microsecond(cast(createtime as
datetime(6))) as hidden_us
+ from internal.${internal_db}.orders_target"""
+
+ def rows = sql """select count(*) from
internal.${internal_db}.orders_target"""
+ assertEquals(1, (rows[0][0] as Number).intValue())
+
+ // And a wider destination keeps the microseconds the source actually has
-- the reason this
+ // layer refuses rather than rounds.
+ sql """create table internal.${internal_db}.orders_wide (
+ `id` int, `createtime` datetime(6)
+ ) DISTRIBUTED BY HASH(`id`) BUCKETS 1 PROPERTIES ("replication_num"
= "1");"""
+ sql """insert into internal.${internal_db}.orders_wide
+ select status, createtime from ${catalog_name}.${pg_schema}.orders
where status = 1;"""
+ qt_wide_destination """select createtime, microsecond(createtime) as us
+ from internal.${internal_db}.orders_wide"""
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
