This is an automated email from the ASF dual-hosted git repository.
jacktengg 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 a7142a61168 [fix](fe) Preserve TIMESTAMPTZ precision in legacy
comparisons (#69774)
a7142a61168 is described below
commit a7142a611689eabeb7d03f841d6b0e2339313c1a
Author: TengJianPing <[email protected]>
AuthorDate: Thu Oct 29 10:30:59 2026 +0800
[fix](fe) Preserve TIMESTAMPTZ precision in legacy comparisons (#69774)
### What problem does this PR solve?
Issue Number: N/A
Related PR: N/A
Problem Summary: With enable_new_type_coercion_behavior disabled,
comparing TIMESTAMPTZ values with different scales falls through to the
legacy DATETIME comparison type. String literals are parsed at their own
fractional precision, so deleting from a TIMESTAMPTZ(6) unique-key table
with an unscaled timestamp literal fails with a TIMESTAMPTZ-to-DATETIME
cast error, even if no row matches. Reuse the existing TIMESTAMPTZ
common-type rule in legacy comparisons to preserve the higher precision
and UTC representation. Add coverage for every scale pair, string
literals, both coercion modes, timezone offsets, boundary years, and
microsecond-sensitive DELETE and IN predicates.
### Release note
Fix TIMESTAMPTZ comparisons and DELETE predicates involving different
fractional precisions when enable_new_type_coercion_behavior is
disabled.
### Check List (For Author)
- Test: Unit Test / Regression test / Manual test
- run-fe-ut.sh: TypeCoercionUtilsTest and TypeCoercionMatrixTest, 75
tests passed.
- build.sh --fe: build and Checkstyle passed.
- run-regression-test.sh: test_timestamptz_delete_scale_coercion and
test_timestamptz_delete_uniq_key passed.
- Generated regression expectations using run-regression-test.sh -genOut
and verified identical results in both modes.
- Reproduced the reported SQL error before the fix and verified it
succeeds after the fix.
- Behavior changed: Yes, legacy comparisons retain TIMESTAMPTZ precision
instead of failing a DATETIME cast.
- Does this need documentation: No
### What problem does this PR solve?
Issue Number: close #xxx
Related PR: #xxx
Problem Summary:
### Release note
None
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [ ] Regression test
- [ ] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
- Behavior changed:
- [ ] No.
- [ ] Yes. <!-- Explain the behavior change -->
- Does this need documentation?
- [ ] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR
should merge into -->
---
.../doris/nereids/util/TypeCoercionUtils.java | 5 +
.../doris/nereids/util/TypeCoercionUtilsTest.java | 56 +++++++++
.../test_timestamptz_delete_scale_coercion.out | 127 +++++++++++++++++++++
.../test_timestamptz_delete_scale_coercion.groovy | 111 ++++++++++++++++++
4 files changed, 299 insertions(+)
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 1e69664571f..f44f6b3e745 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
@@ -2080,6 +2080,11 @@ public class TypeCoercionUtils {
return
Optional.of(DecimalV2Type.widerDecimalV2Type((DecimalV2Type) leftType,
(DecimalV2Type) rightType));
}
+ // Keep TIMESTAMPTZ precision promotion consistent with the new
coercion rules.
+ if (leftType instanceof TimeStampTzType && rightType instanceof
TimeStampTzType) {
+ return findWiderTypeForTwo(leftType, rightType, false, false);
+ }
+
// date
if (canCompareDate(leftType, rightType)) {
if (leftType.isDateTimeV2Type() && rightType.isDateTimeV2Type()) {
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 779ed4d9c7c..7d8641ee2e7 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
@@ -118,6 +118,62 @@ import java.util.List;
import java.util.Optional;
public class TypeCoercionUtilsTest {
+ @Test
+ public void testTimestampTzComparisonPreservesScaleInBothModes() {
+ boolean oldBehavior = GlobalVariable.enableNewTypeCoercionBehavior;
+ try {
+ for (boolean newBehavior : ImmutableList.of(false, true)) {
+ GlobalVariable.enableNewTypeCoercionBehavior = newBehavior;
+ for (int leftScale = 0; leftScale <=
TimeStampTzType.MAX_SCALE; leftScale++) {
+ for (int rightScale = 0; rightScale <=
TimeStampTzType.MAX_SCALE; rightScale++) {
+ SlotReference left = new SlotReference("left",
TimeStampTzType.of(leftScale));
+ SlotReference right = new SlotReference("right",
TimeStampTzType.of(rightScale));
+ TimeStampTzType commonType =
TimeStampTzType.of(Math.max(leftScale, rightScale));
+ for (ComparisonPredicate comparison : ImmutableList.of(
+ new EqualTo(left, right), new
NullSafeEqual(left, right),
+ new LessThan(left, right), new
LessThanEqual(left, right),
+ new GreaterThan(left, right), new
GreaterThanEqual(left, right))) {
+ Expression normalized =
TypeCoercionUtils.processComparisonPredicate(comparison);
+ Assertions.assertEquals(commonType,
normalized.child(0).getDataType());
+ Assertions.assertEquals(commonType,
normalized.child(1).getDataType());
+ }
+ Expression in = TypeCoercionUtils.processInPredicate(
+ new InPredicate(left,
ImmutableList.of(right)));
+ Assertions.assertEquals(commonType,
in.child(0).getDataType());
+ Assertions.assertEquals(commonType,
in.child(1).getDataType());
+ }
+ }
+ }
+ } finally {
+ GlobalVariable.enableNewTypeCoercionBehavior = oldBehavior;
+ }
+ }
+
+ @Test
+ public void testTimestampTzStringComparisonPreservesScaleInBothModes() {
+ boolean oldBehavior = GlobalVariable.enableNewTypeCoercionBehavior;
+ try {
+ for (boolean newBehavior : ImmutableList.of(false, true)) {
+ GlobalVariable.enableNewTypeCoercionBehavior = newBehavior;
+ SlotReference timestampTz = new SlotReference("ts_tz",
TimeStampTzType.MAX);
+ for (String value : ImmutableList.of(
+ "0000-01-01 00:00:00 +00:00", "0000-01-01
00:00:00.000001 +00:00",
+ "2023-08-08 20:20:20.123 +00:00", "2023-08-09 04:20:20
+08:00",
+ "9999-12-31 23:59:59 +00:00", "9999-12-31
23:59:59.999999 +00:00")) {
+ EqualTo comparison = new EqualTo(timestampTz, new
StringLiteral(value));
+ for (ComparisonPredicate ordered :
ImmutableList.of(comparison, comparison.commute())) {
+ Expression normalized =
TypeCoercionUtils.processComparisonPredicate(ordered);
+ Assertions.assertEquals(TimeStampTzType.MAX,
normalized.child(0).getDataType());
+ Assertions.assertEquals(TimeStampTzType.MAX,
normalized.child(1).getDataType());
+ Assertions.assertSame(timestampTz,
normalized.child(ordered.left() == timestampTz ? 0 : 1));
+ }
+ }
+ }
+ } finally {
+ GlobalVariable.enableNewTypeCoercionBehavior = oldBehavior;
+ }
+ }
+
@Test
public void testReplaceDateTimeV2WithMaxKeepsTimestampNsIndependent() {
Assertions.assertEquals(DateTimeV2Type.MAX,
diff --git
a/regression-test/data/datatype_p0/timestamptz/test_timestamptz_delete_scale_coercion.out
b/regression-test/data/datatype_p0/timestamptz/test_timestamptz_delete_scale_coercion.out
new file mode 100644
index 00000000000..0960b5e146e
--- /dev/null
+++
b/regression-test/data/datatype_p0/timestamptz/test_timestamptz_delete_scale_coercion.out
@@ -0,0 +1,127 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !delete_min_true --
+\N -1
+0000-01-01 00:00:00.000001+00:00 0
+0000-01-01 00:00:00.123456+00:00 0
+0000-01-01 00:00:00.999999+00:00 10
+2023-08-08 20:20:20.000000+00:00 8
+2023-08-08 20:20:20.000001+00:00 8
+2023-08-08 20:20:20.123456+00:00 8
+2023-08-08 20:20:20.999999+00:00 8
+9999-12-31 23:59:59.000000+00:00 9998
+9999-12-31 23:59:59.000001+00:00 9998
+9999-12-31 23:59:59.123456+00:00 9998
+9999-12-31 23:59:59.999999+00:00 9999
+
+-- !delete_offset_true --
+\N -1
+0000-01-01 00:00:00.000001+00:00 0
+0000-01-01 00:00:00.123456+00:00 0
+0000-01-01 00:00:00.999999+00:00 10
+2023-08-08 20:20:20.000001+00:00 8
+2023-08-08 20:20:20.123456+00:00 8
+2023-08-08 20:20:20.999999+00:00 8
+9999-12-31 23:59:59.000000+00:00 9998
+9999-12-31 23:59:59.000001+00:00 9998
+9999-12-31 23:59:59.123456+00:00 9998
+9999-12-31 23:59:59.999999+00:00 9999
+
+-- !delete_max_true --
+\N -1
+0000-01-01 00:00:00.000001+00:00 0
+0000-01-01 00:00:00.123456+00:00 0
+0000-01-01 00:00:00.999999+00:00 10
+2023-08-08 20:20:20.000001+00:00 8
+2023-08-08 20:20:20.123456+00:00 8
+2023-08-08 20:20:20.999999+00:00 8
+9999-12-31 23:59:59.000001+00:00 9998
+9999-12-31 23:59:59.123456+00:00 9998
+9999-12-31 23:59:59.999999+00:00 9999
+
+-- !delete_in_true --
+\N -1
+0000-01-01 00:00:00.123456+00:00 0
+0000-01-01 00:00:00.999999+00:00 10
+2023-08-08 20:20:20.000001+00:00 8
+2023-08-08 20:20:20.999999+00:00 8
+9999-12-31 23:59:59.000001+00:00 9998
+9999-12-31 23:59:59.123456+00:00 9998
+
+-- !delete_less_true --
+\N -1
+0000-01-01 00:00:00.999999+00:00 10
+2023-08-08 20:20:20.000001+00:00 8
+2023-08-08 20:20:20.999999+00:00 8
+9999-12-31 23:59:59.000001+00:00 9998
+9999-12-31 23:59:59.123456+00:00 9998
+
+-- !delete_greater_equal_true --
+\N -1
+0000-01-01 00:00:00.999999+00:00 10
+2023-08-08 20:20:20.000001+00:00 8
+2023-08-08 20:20:20.999999+00:00 8
+9999-12-31 23:59:59.000001+00:00 9998
+
+-- !delete_min_false --
+\N -1
+0000-01-01 00:00:00.000001+00:00 0
+0000-01-01 00:00:00.123456+00:00 0
+0000-01-01 00:00:00.999999+00:00 10
+2023-08-08 20:20:20.000000+00:00 8
+2023-08-08 20:20:20.000001+00:00 8
+2023-08-08 20:20:20.123456+00:00 8
+2023-08-08 20:20:20.999999+00:00 8
+9999-12-31 23:59:59.000000+00:00 9998
+9999-12-31 23:59:59.000001+00:00 9998
+9999-12-31 23:59:59.123456+00:00 9998
+9999-12-31 23:59:59.999999+00:00 9999
+
+-- !delete_offset_false --
+\N -1
+0000-01-01 00:00:00.000001+00:00 0
+0000-01-01 00:00:00.123456+00:00 0
+0000-01-01 00:00:00.999999+00:00 10
+2023-08-08 20:20:20.000001+00:00 8
+2023-08-08 20:20:20.123456+00:00 8
+2023-08-08 20:20:20.999999+00:00 8
+9999-12-31 23:59:59.000000+00:00 9998
+9999-12-31 23:59:59.000001+00:00 9998
+9999-12-31 23:59:59.123456+00:00 9998
+9999-12-31 23:59:59.999999+00:00 9999
+
+-- !delete_max_false --
+\N -1
+0000-01-01 00:00:00.000001+00:00 0
+0000-01-01 00:00:00.123456+00:00 0
+0000-01-01 00:00:00.999999+00:00 10
+2023-08-08 20:20:20.000001+00:00 8
+2023-08-08 20:20:20.123456+00:00 8
+2023-08-08 20:20:20.999999+00:00 8
+9999-12-31 23:59:59.000001+00:00 9998
+9999-12-31 23:59:59.123456+00:00 9998
+9999-12-31 23:59:59.999999+00:00 9999
+
+-- !delete_in_false --
+\N -1
+0000-01-01 00:00:00.123456+00:00 0
+0000-01-01 00:00:00.999999+00:00 10
+2023-08-08 20:20:20.000001+00:00 8
+2023-08-08 20:20:20.999999+00:00 8
+9999-12-31 23:59:59.000001+00:00 9998
+9999-12-31 23:59:59.123456+00:00 9998
+
+-- !delete_less_false --
+\N -1
+0000-01-01 00:00:00.999999+00:00 10
+2023-08-08 20:20:20.000001+00:00 8
+2023-08-08 20:20:20.999999+00:00 8
+9999-12-31 23:59:59.000001+00:00 9998
+9999-12-31 23:59:59.123456+00:00 9998
+
+-- !delete_greater_equal_false --
+\N -1
+0000-01-01 00:00:00.999999+00:00 10
+2023-08-08 20:20:20.000001+00:00 8
+2023-08-08 20:20:20.999999+00:00 8
+9999-12-31 23:59:59.000001+00:00 9998
+
diff --git
a/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_delete_scale_coercion.groovy
b/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_delete_scale_coercion.groovy
new file mode 100644
index 00000000000..9705759449c
--- /dev/null
+++
b/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_delete_scale_coercion.groovy
@@ -0,0 +1,111 @@
+// 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.
+
+suite("test_timestamptz_delete_scale_coercion", "nonConcurrent") {
+ sql "set time_zone = '+00:00'"
+ sql "drop table if exists timestamptz_delete_scale_coercion"
+ sql """
+ create table timestamptz_delete_scale_coercion (
+ ts_tz timestamptz(6),
+ value int
+ ) unique key(ts_tz)
+ distributed by hash(ts_tz) buckets 16
+ properties("replication_num" = "1")
+ """
+
+ for (boolean mode : [true, false]) {
+ setGlobalVarTemporary([enable_new_type_coercion_behavior: mode], {
+ sql "truncate table timestamptz_delete_scale_coercion"
+ sql """
+ insert into timestamptz_delete_scale_coercion values
+ (null, -1),
+ ('0000-01-01 00:00:00 +00:00', 0),
+ ('0000-01-01 00:00:00.000000 +00:00', 0),
+ ('0000-01-01 00:00:00.000001 +00:00', 0),
+ ('0000-01-01 00:00:00.123456 +00:00', 0),
+ ('0000-01-01 00:00:00.999999 +00:00', 10),
+ ('2023-08-08 20:20:20 +00:00', 8),
+ ('2023-08-08 20:20:20.000000 +00:00', 8),
+ ('2023-08-08 20:20:20.000001 +00:00', 8),
+ ('2023-08-08 20:20:20.123456 +00:00', 8),
+ ('2023-08-08 20:20:20.999999 +00:00', 8),
+ ('9999-12-31 23:59:59 +00:00', 9998),
+ ('9999-12-31 23:59:59.000000 +00:00', 9998),
+ ('9999-12-31 23:59:59.000001 +00:00', 9998),
+ ('9999-12-31 23:59:59.123456 +00:00', 9998),
+ ('9999-12-31 23:59:59.999999 +00:00', 9999)
+ """
+
+ // A scale-0 literal must not demote the scale-6 key to DATETIME.
+ sql """
+ delete from timestamptz_delete_scale_coercion
+ where ts_tz = '0000-01-01 00:00:00 +00:00'
+ """
+ // Repeating the DELETE must also work when there is no matching
row.
+ sql """
+ delete from timestamptz_delete_scale_coercion
+ where ts_tz = '0000-01-01 00:00:00 +00:00'
+ """
+ "qt_delete_min_${mode}" """
+ select * from timestamptz_delete_scale_coercion order by 1, 2
+ """
+
+ sql """
+ delete from timestamptz_delete_scale_coercion
+ where ts_tz = '2023-08-09 04:20:20 +08:00'
+ """
+ "qt_delete_offset_${mode}" """
+ select * from timestamptz_delete_scale_coercion order by 1, 2
+ """
+
+ sql """
+ delete from timestamptz_delete_scale_coercion
+ where ts_tz = '9999-12-31 23:59:59 +00:00'
+ """
+ "qt_delete_max_${mode}" """
+ select * from timestamptz_delete_scale_coercion order by 1, 2
+ """
+
+ sql """
+ delete from timestamptz_delete_scale_coercion
+ where ts_tz in ('0000-01-01 00:00:00 +00:00',
+ '0000-01-01 00:00:00.000001 +00:00',
+ '2023-08-08 20:20:20.123456 +00:00',
+ '9999-12-31 23:59:59.999999 +00:00')
+ """
+ "qt_delete_in_${mode}" """
+ select * from timestamptz_delete_scale_coercion order by 1, 2
+ """
+
+ sql """
+ delete from timestamptz_delete_scale_coercion
+ where ts_tz < '0000-01-01 00:00:00.999 +00:00'
+ """
+ "qt_delete_less_${mode}" """
+ select * from timestamptz_delete_scale_coercion order by 1, 2
+ """
+
+ sql """
+ delete from timestamptz_delete_scale_coercion
+ where ts_tz >= '9999-12-31 23:59:59.123 +00:00'
+ """
+ "qt_delete_greater_equal_${mode}" """
+ select * from timestamptz_delete_scale_coercion order by 1, 2
+ """
+ })
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
