Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit ad814d2

Browse filesBrowse files
yujun777yujun777
authored
[fix](nereids) fix compare with date like literal (#45382)
when compare with date like literal, the literal may be wrong cut. for example: if a is datetimev1, then: `a = '2020-01-01 00:00:00.12'` should opt as `FALSE`, but it opt as `a = '2020-01-01 00:00:00`; `a >= '2020-01-01 00:00:00.12'` should opt as `a >= '2020-01-01 00:00:01`, but it opt as `a >= '2020-01-01 00:00:00`; if a is date / datev2, then: `a = '2020-01-01 00:00:12'` should opt as `FALSE`, but it don't opt it;
1 parent c58a1b7 commit ad814d2
Copy full SHA for ad814d2

File tree

4 files changed

+145
-52
lines changed
Filter options

4 files changed

+145
-52
lines changed

‎fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SimplifyComparisonPredicate.java

Copy file name to clipboardExpand all lines: fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SimplifyComparisonPredicate.java
+44-48Lines changed: 44 additions & 48 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,7 @@
4646
import org.apache.doris.nereids.trees.expressions.literal.NumericLiteral;
4747
import org.apache.doris.nereids.trees.expressions.literal.SmallIntLiteral;
4848
import org.apache.doris.nereids.trees.expressions.literal.TinyIntLiteral;
49+
import org.apache.doris.nereids.types.DataType;
4950
import org.apache.doris.nereids.types.DateTimeType;
5051
import org.apache.doris.nereids.types.DateTimeV2Type;
5152
import org.apache.doris.nereids.types.DateType;
@@ -71,12 +72,6 @@
7172
public class SimplifyComparisonPredicate extends AbstractExpressionRewriteRule implements ExpressionPatternRuleFactory {
7273
public static SimplifyComparisonPredicate INSTANCE = new SimplifyComparisonPredicate();
7374

74-
enum AdjustType {
75-
LOWER,
76-
UPPER,
77-
NONE
78-
}
79-
8075
@Override
8176
public List<ExpressionPatternMatcher<? extends Expression>> buildRules() {
8277
return ImmutableList.of(
@@ -119,18 +114,23 @@ public static Expression simplify(ComparisonPredicate cp) {
119114
return result;
120115
}
121116

122-
private static Expression processComparisonPredicateDateTimeV2Literal(
117+
private static Expression processDateTimeLikeComparisonPredicateDateTimeV2Literal(
123118
ComparisonPredicate comparisonPredicate, Expression left, DateTimeV2Literal right) {
124-
DateTimeV2Type leftType = (DateTimeV2Type) left.getDataType();
119+
DataType leftType = left.getDataType();
120+
int toScale = 0;
121+
if (leftType instanceof DateTimeType) {
122+
toScale = 0;
123+
} else if (leftType instanceof DateTimeV2Type) {
124+
toScale = ((DateTimeV2Type) leftType).getScale();
125+
} else {
126+
return comparisonPredicate;
127+
}
125128
DateTimeV2Type rightType = right.getDataType();
126-
if (leftType.getScale() < rightType.getScale()) {
127-
int toScale = leftType.getScale();
129+
if (toScale < rightType.getScale()) {
128130
if (comparisonPredicate instanceof EqualTo) {
129131
long originValue = right.getMicroSecond();
130132
right = right.roundCeiling(toScale);
131-
if (right.getMicroSecond() == originValue) {
132-
return comparisonPredicate.withChildren(left, right);
133-
} else {
133+
if (right.getMicroSecond() != originValue) {
134134
// TODO: the ideal way is to return an If expr like:
135135
// return new If(new IsNull(left), new NullLiteral(BooleanType.INSTANCE),
136136
// BooleanLiteral.of(false));
@@ -142,50 +142,55 @@ private static Expression processComparisonPredicateDateTimeV2Literal(
142142
} else if (comparisonPredicate instanceof NullSafeEqual) {
143143
long originValue = right.getMicroSecond();
144144
right = right.roundCeiling(toScale);
145-
if (right.getMicroSecond() == originValue) {
146-
return comparisonPredicate.withChildren(left, right);
147-
} else {
145+
if (right.getMicroSecond() != originValue) {
148146
return BooleanLiteral.of(false);
149147
}
150148
} else if (comparisonPredicate instanceof GreaterThan
151149
|| comparisonPredicate instanceof LessThanEqual) {
152-
return comparisonPredicate.withChildren(left, right.roundFloor(toScale));
150+
right = right.roundFloor(toScale);
153151
} else if (comparisonPredicate instanceof LessThan
154152
|| comparisonPredicate instanceof GreaterThanEqual) {
155-
return comparisonPredicate.withChildren(left, right.roundCeiling(toScale));
153+
right = right.roundCeiling(toScale);
154+
} else {
155+
return comparisonPredicate;
156+
}
157+
Expression newRight = leftType instanceof DateTimeType ? migrateToDateTime(right) : right;
158+
return comparisonPredicate.withChildren(left, newRight);
159+
} else {
160+
if (leftType instanceof DateTimeType) {
161+
return comparisonPredicate.withChildren(left, migrateToDateTime(right));
162+
} else {
163+
return comparisonPredicate;
156164
}
157165
}
158-
return comparisonPredicate;
159166
}
160167

161168
private static Expression processDateLikeTypeCoercion(ComparisonPredicate cp, Expression left, Expression right) {
162169
if (left instanceof Cast && right instanceof DateLiteral) {
163170
Cast cast = (Cast) left;
164-
if (cast.child().getDataType() instanceof DateTimeType) {
171+
if (cast.child().getDataType() instanceof DateTimeType
172+
|| cast.child().getDataType() instanceof DateTimeV2Type) {
165173
if (right instanceof DateTimeV2Literal) {
166-
left = cast.child();
167-
right = migrateToDateTime((DateTimeV2Literal) right);
168-
}
169-
}
170-
if (cast.child().getDataType() instanceof DateTimeV2Type) {
171-
if (right instanceof DateTimeV2Literal) {
172-
left = cast.child();
173-
return processComparisonPredicateDateTimeV2Literal(cp, left, (DateTimeV2Literal) right);
174+
return processDateTimeLikeComparisonPredicateDateTimeV2Literal(
175+
cp, cast.child(), (DateTimeV2Literal) right);
174176
}
175177
}
178+
176179
// datetime to datev2
177180
if (cast.child().getDataType() instanceof DateType || cast.child().getDataType() instanceof DateV2Type) {
178181
if (right instanceof DateTimeLiteral) {
179-
if (cannotAdjust((DateTimeLiteral) right, cp)) {
180-
return cp;
181-
}
182-
AdjustType type = AdjustType.NONE;
183-
if (cp instanceof GreaterThanEqual || cp instanceof LessThan) {
184-
type = AdjustType.UPPER;
185-
} else if (cp instanceof GreaterThan || cp instanceof LessThanEqual) {
186-
type = AdjustType.LOWER;
182+
DateTimeLiteral dateTimeLiteral = (DateTimeLiteral) right;
183+
right = migrateToDateV2(dateTimeLiteral);
184+
if (dateTimeLiteral.getHour() != 0 || dateTimeLiteral.getMinute() != 0
185+
|| dateTimeLiteral.getSecond() != 0) {
186+
if (cp instanceof EqualTo) {
187+
return ExpressionUtils.falseOrNull(cast.child());
188+
} else if (cp instanceof NullSafeEqual) {
189+
return BooleanLiteral.FALSE;
190+
} else if (cp instanceof GreaterThanEqual || cp instanceof LessThan) {
191+
right = ((DateV2Literal) right).plusDays(1);
192+
}
187193
}
188-
right = migrateToDateV2((DateTimeLiteral) right, type);
189194
if (cast.child().getDataType() instanceof DateV2Type) {
190195
left = cast.child();
191196
}
@@ -416,17 +421,8 @@ private static Expression migrateToDateTime(DateTimeV2Literal l) {
416421
return new DateTimeLiteral(l.getYear(), l.getMonth(), l.getDay(), l.getHour(), l.getMinute(), l.getSecond());
417422
}
418423

419-
private static boolean cannotAdjust(DateTimeLiteral l, ComparisonPredicate cp) {
420-
return cp instanceof EqualTo && (l.getHour() != 0 || l.getMinute() != 0 || l.getSecond() != 0);
421-
}
422-
423-
private static Expression migrateToDateV2(DateTimeLiteral l, AdjustType type) {
424-
DateV2Literal d = new DateV2Literal(l.getYear(), l.getMonth(), l.getDay());
425-
if (type == AdjustType.UPPER && (l.getHour() != 0 || l.getMinute() != 0 || l.getSecond() != 0)) {
426-
return d.plusDays(1);
427-
} else {
428-
return d;
429-
}
424+
private static Expression migrateToDateV2(DateTimeLiteral l) {
425+
return new DateV2Literal(l.getYear(), l.getMonth(), l.getDay());
430426
}
431427

432428
private static Expression migrateToDate(DateV2Literal l) {

‎fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/rules/SimplifyComparisonPredicateTest.java

Copy file name to clipboardExpand all lines: fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/rules/SimplifyComparisonPredicateTest.java
+99-2Lines changed: 99 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,10 @@
4848
import org.apache.doris.nereids.types.BigIntType;
4949
import org.apache.doris.nereids.types.BooleanType;
5050
import org.apache.doris.nereids.types.DataType;
51+
import org.apache.doris.nereids.types.DateTimeType;
5152
import org.apache.doris.nereids.types.DateTimeV2Type;
53+
import org.apache.doris.nereids.types.DateType;
54+
import org.apache.doris.nereids.types.DateV2Type;
5255
import org.apache.doris.nereids.types.DecimalV3Type;
5356
import org.apache.doris.nereids.types.DoubleType;
5457
import org.apache.doris.nereids.types.IntegerType;
@@ -95,11 +98,11 @@ void testSimplifyComparisonPredicateRule() {
9598
new LessThan(dv2, dv2PlusOne));
9699
assertRewrite(
97100
new EqualTo(new Cast(dv2, DateTimeV2Type.SYSTEM_DEFAULT), dtv2),
98-
new EqualTo(new Cast(dv2, DateTimeV2Type.SYSTEM_DEFAULT), dtv2));
101+
BooleanLiteral.FALSE);
99102

100103
assertRewrite(
101104
new EqualTo(new Cast(d, DateTimeV2Type.SYSTEM_DEFAULT), dtv2),
102-
new EqualTo(new Cast(d, DateTimeV2Type.SYSTEM_DEFAULT), dtv2));
105+
BooleanLiteral.FALSE);
103106

104107
// test hour, minute and second all zero
105108
Expression dtv2AtZeroClock = new DateTimeV2Literal(1, 1, 1, 0, 0, 0, 0);
@@ -140,6 +143,100 @@ void testDateTimeV2CmpDateTimeV2() {
140143
expression = new GreaterThan(left, right);
141144
rewrittenExpression = executor.rewrite(typeCoercion(expression), context);
142145
Assertions.assertEquals(dt.getDataType(), rewrittenExpression.child(0).getDataType());
146+
147+
Expression date = new SlotReference("a", DateV2Type.INSTANCE);
148+
Expression datev1 = new SlotReference("a", DateType.INSTANCE);
149+
Expression datetime0 = new SlotReference("a", DateTimeV2Type.of(0));
150+
Expression datetime2 = new SlotReference("a", DateTimeV2Type.of(2));
151+
Expression datetimev1 = new SlotReference("a", DateTimeType.INSTANCE);
152+
153+
// date
154+
// cast (date as datetimev1) cmp datetimev1
155+
assertRewrite(new EqualTo(new Cast(date, DateTimeType.INSTANCE), new DateTimeLiteral("2020-01-01 00:00:00")),
156+
new EqualTo(date, new DateV2Literal("2020-01-01")));
157+
assertRewrite(new EqualTo(new Cast(date, DateTimeType.INSTANCE), new DateTimeLiteral("2020-01-01 00:00:01")),
158+
ExpressionUtils.falseOrNull(date));
159+
assertRewrite(new NullSafeEqual(new Cast(date, DateTimeType.INSTANCE), new DateTimeLiteral("2020-01-01 00:00:01")),
160+
BooleanLiteral.FALSE);
161+
assertRewrite(new GreaterThan(new Cast(date, DateTimeType.INSTANCE), new DateTimeLiteral("2020-01-01 00:00:01")),
162+
new GreaterThan(date, new DateV2Literal("2020-01-01")));
163+
assertRewrite(new GreaterThanEqual(new Cast(date, DateTimeType.INSTANCE), new DateTimeLiteral("2020-01-01 00:00:01")),
164+
new GreaterThanEqual(date, new DateV2Literal("2020-01-02")));
165+
assertRewrite(new LessThan(new Cast(date, DateTimeType.INSTANCE), new DateTimeLiteral("2020-01-01 00:00:01")),
166+
new LessThan(date, new DateV2Literal("2020-01-02")));
167+
assertRewrite(new LessThanEqual(new Cast(date, DateTimeType.INSTANCE), new DateTimeLiteral("2020-01-01 00:00:01")),
168+
new LessThanEqual(date, new DateV2Literal("2020-01-01")));
169+
// cast (date as datev1) = datev1-literal
170+
// assertRewrite(new EqualTo(new Cast(date, DateType.INSTANCE), new DateLiteral("2020-01-01")),
171+
// new EqualTo(date, new DateV2Literal("2020-01-01")));
172+
// assertRewrite(new GreaterThan(new Cast(date, DateType.INSTANCE), new DateLiteral("2020-01-01")),
173+
// new GreaterThan(date, new DateV2Literal("2020-01-01")));
174+
175+
// cast (datev1 as datetimev1) cmp datetimev1
176+
assertRewrite(new EqualTo(new Cast(datev1, DateTimeType.INSTANCE), new DateTimeLiteral("2020-01-01 00:00:00")),
177+
new EqualTo(datev1, new DateLiteral("2020-01-01")));
178+
assertRewrite(new EqualTo(new Cast(datev1, DateTimeType.INSTANCE), new DateTimeLiteral("2020-01-01 00:00:01")),
179+
ExpressionUtils.falseOrNull(datev1));
180+
assertRewrite(new NullSafeEqual(new Cast(datev1, DateTimeType.INSTANCE), new DateTimeLiteral("2020-01-01 00:00:01")),
181+
BooleanLiteral.FALSE);
182+
assertRewrite(new GreaterThan(new Cast(datev1, DateTimeType.INSTANCE), new DateTimeLiteral("2020-01-01 00:00:01")),
183+
new GreaterThan(datev1, new DateLiteral("2020-01-01")));
184+
assertRewrite(new GreaterThanEqual(new Cast(datev1, DateTimeType.INSTANCE), new DateTimeLiteral("2020-01-01 00:00:01")),
185+
new GreaterThanEqual(datev1, new DateLiteral("2020-01-02")));
186+
assertRewrite(new LessThan(new Cast(datev1, DateTimeType.INSTANCE), new DateTimeLiteral("2020-01-01 00:00:01")),
187+
new LessThan(datev1, new DateLiteral("2020-01-02")));
188+
assertRewrite(new LessThanEqual(new Cast(datev1, DateTimeType.INSTANCE), new DateTimeLiteral("2020-01-01 00:00:01")),
189+
new LessThanEqual(datev1, new DateLiteral("2020-01-01")));
190+
assertRewrite(new EqualTo(new Cast(datev1, DateV2Type.INSTANCE), new DateV2Literal("2020-01-01")),
191+
new EqualTo(datev1, new DateLiteral("2020-01-01")));
192+
assertRewrite(new GreaterThan(new Cast(datev1, DateV2Type.INSTANCE), new DateV2Literal("2020-01-01")),
193+
new GreaterThan(datev1, new DateLiteral("2020-01-01")));
194+
195+
// cast (datetimev1 as datetime) cmp datetime
196+
assertRewrite(new EqualTo(new Cast(datetimev1, DateTimeV2Type.of(0)), new DateTimeV2Literal("2020-01-01 00:00:00")),
197+
new EqualTo(datetimev1, new DateTimeLiteral("2020-01-01 00:00:00")));
198+
assertRewrite(new GreaterThan(new Cast(datetimev1, DateTimeV2Type.of(0)), new DateTimeV2Literal("2020-01-01 00:00:00")),
199+
new GreaterThan(datetimev1, new DateTimeLiteral("2020-01-01 00:00:00")));
200+
assertRewrite(new EqualTo(new Cast(datetimev1, DateTimeV2Type.of(2)), new DateTimeV2Literal("2020-01-01 00:00:00.12")),
201+
ExpressionUtils.falseOrNull(datetimev1));
202+
assertRewrite(new NullSafeEqual(new Cast(datetimev1, DateTimeV2Type.of(2)), new DateTimeV2Literal("2020-01-01 00:00:00.12")),
203+
BooleanLiteral.FALSE);
204+
assertRewrite(new GreaterThan(new Cast(datetimev1, DateTimeV2Type.of(2)), new DateTimeV2Literal("2020-01-01 00:00:00.12")),
205+
new GreaterThan(datetimev1, new DateTimeLiteral("2020-01-01 00:00:00")));
206+
assertRewrite(new GreaterThanEqual(new Cast(datetimev1, DateTimeV2Type.of(2)), new DateTimeV2Literal("2020-01-01 00:00:00.12")),
207+
new GreaterThanEqual(datetimev1, new DateTimeLiteral("2020-01-01 00:00:01")));
208+
assertRewrite(new LessThan(new Cast(datetimev1, DateTimeV2Type.of(2)), new DateTimeV2Literal("2020-01-01 00:00:00.12")),
209+
new LessThan(datetimev1, new DateTimeLiteral("2020-01-01 00:00:01")));
210+
assertRewrite(new LessThanEqual(new Cast(datetimev1, DateTimeV2Type.of(2)), new DateTimeV2Literal("2020-01-01 00:00:00.12")),
211+
new LessThanEqual(datetimev1, new DateTimeLiteral("2020-01-01 00:00:00")));
212+
213+
// cast (datetime0 as datetime) cmp datetime
214+
assertRewrite(new EqualTo(new Cast(datetime0, DateTimeV2Type.of(2)), new DateTimeV2Literal("2020-01-01 00:00:00.12")),
215+
ExpressionUtils.falseOrNull(datetime0));
216+
assertRewrite(new NullSafeEqual(new Cast(datetime0, DateTimeV2Type.of(2)), new DateTimeV2Literal("2020-01-01 00:00:00.12")),
217+
BooleanLiteral.FALSE);
218+
assertRewrite(new GreaterThan(new Cast(datetime0, DateTimeV2Type.of(2)), new DateTimeV2Literal("2020-01-01 00:00:00.12")),
219+
new GreaterThan(datetime0, new DateTimeV2Literal("2020-01-01 00:00:00")));
220+
assertRewrite(new GreaterThanEqual(new Cast(datetime0, DateTimeV2Type.of(2)), new DateTimeV2Literal("2020-01-01 00:00:00.12")),
221+
new GreaterThanEqual(datetime0, new DateTimeV2Literal("2020-01-01 00:00:01")));
222+
assertRewrite(new LessThan(new Cast(datetime0, DateTimeV2Type.of(2)), new DateTimeV2Literal("2020-01-01 00:00:00.12")),
223+
new LessThan(datetime0, new DateTimeV2Literal("2020-01-01 00:00:01")));
224+
assertRewrite(new LessThanEqual(new Cast(datetime0, DateTimeV2Type.of(2)), new DateTimeV2Literal("2020-01-01 00:00:00.12")),
225+
new LessThanEqual(datetime0, new DateTimeV2Literal("2020-01-01 00:00:00")));
226+
227+
// cast (datetime2 as datetime) cmp datetime
228+
assertRewrite(new EqualTo(new Cast(datetime2, DateTimeV2Type.of(3)), new DateTimeV2Literal("2020-01-01 00:00:00.123")),
229+
ExpressionUtils.falseOrNull(datetime2));
230+
assertRewrite(new NullSafeEqual(new Cast(datetime2, DateTimeV2Type.of(3)), new DateTimeV2Literal("2020-01-01 00:00:00.123")),
231+
BooleanLiteral.FALSE);
232+
assertRewrite(new GreaterThan(new Cast(datetime2, DateTimeV2Type.of(3)), new DateTimeV2Literal("2020-01-01 00:00:00.123")),
233+
new GreaterThan(datetime2, new DateTimeV2Literal("2020-01-01 00:00:00.12")));
234+
assertRewrite(new GreaterThanEqual(new Cast(datetime2, DateTimeV2Type.of(3)), new DateTimeV2Literal("2020-01-01 00:00:00.123")),
235+
new GreaterThanEqual(datetime2, new DateTimeV2Literal("2020-01-01 00:00:00.13")));
236+
assertRewrite(new LessThan(new Cast(datetime2, DateTimeV2Type.of(3)), new DateTimeV2Literal("2020-01-01 00:00:00.123")),
237+
new LessThan(datetime2, new DateTimeV2Literal("2020-01-01 00:00:00.13")));
238+
assertRewrite(new LessThanEqual(new Cast(datetime2, DateTimeV2Type.of(3)), new DateTimeV2Literal("2020-01-01 00:00:00.123")),
239+
new LessThanEqual(datetime2, new DateTimeV2Literal("2020-01-01 00:00:00.12")));
143240
}
144241

145242
@Test

‎regression-test/data/nereids_rules_p0/infer_predicate/pull_up_predicate_literal.out

Copy file name to clipboardExpand all lines: regression-test/data/nereids_rules_p0/infer_predicate/pull_up_predicate_literal.out
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1528,7 +1528,7 @@ PhysicalResultSink
15281528
----------PhysicalProject
15291529
------------PhysicalStorageLayerAggregate[test_pull_up_predicate_literal]
15301530
------PhysicalProject
1531-
--------filter((cast(d_date as DATETIMEV2(0)) = '2024-08-02 10:10:00'))
1531+
--------filter((t2.d_date = '2024-08-02'))
15321532
----------PhysicalOlapScan[test_types]
15331533

15341534
-- !const_value_and_join_column_type170 --

‎regression-test/suites/nereids_rules_p0/infer_predicate/pull_up_predicate_literal.groovy

Copy file name to clipboardExpand all lines: regression-test/suites/nereids_rules_p0/infer_predicate/pull_up_predicate_literal.groovy
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -997,7 +997,7 @@ select c1 from (select
997997
qt_const_value_and_join_column_type169 """
998998
explain shape plan
999999
select c1 from (select
1000-
'2024-08-02 10:10:00.123332' as c1 from test_pull_up_predicate_literal limit 10) t inner join test_types t2 on d_date=t.c1"""
1000+
'2024-08-02 00:00:00.000000' as c1 from test_pull_up_predicate_literal limit 10) t inner join test_types t2 on d_date=t.c1"""
10011001

10021002
qt_const_value_and_join_column_type170 """
10031003
explain shape plan

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.