This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.2
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.2 by this push:
new 05966bc0a3f branch-4.2: [fix](fe) Preserve TIMESTAMPTZ precision in
legacy comparisons #69774 (#69798)
05966bc0a3f is described below
commit 05966bc0a3f1a4f7567ce6a96076b6f4050afbcd
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Fri Oct 30 09:36:59 2026 +0800
branch-4.2: [fix](fe) Preserve TIMESTAMPTZ precision in legacy comparisons
#69774 (#69798)
Cherry-picked from #69774
Co-authored-by: TengJianPing <[email protected]>
---
.../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 8362c16ea79..81c9708a818 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
@@ -2133,6 +2133,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 ce6377e8f3f..5b097d362dc 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
@@ -115,6 +115,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]
