You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2023/01/20 06:11:24 UTC
[doris] branch master updated: [fix](Nereids) fix bugs about date function (#16112)
This is an automated email from the ASF dual-hosted git repository.
morrysnow 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 101bc568d7 [fix](Nereids) fix bugs about date function (#16112)
101bc568d7 is described below
commit 101bc568d770c5eef427d8012b47bdcb095e3b05
Author: 谢健 <ji...@gmail.com>
AuthorDate: Fri Jan 20 14:11:17 2023 +0800
[fix](Nereids) fix bugs about date function (#16112)
1. when casting constant, check the value is whether in the range of targetType
2. change the scale of dateTimeV2 to 6
---
.../rewrite/rules/FoldConstantRuleOnFE.java | 2 +-
.../nereids/trees/expressions/Expression.java | 4 +
.../trees/expressions/literal/ArrayLiteral.java | 9 +
.../nereids/trees/expressions/literal/Literal.java | 42 ++
.../apache/doris/nereids/types/DateTimeV2Type.java | 8 +-
.../apache/doris/nereids/types/LargeIntType.java | 6 +
.../datetime_functions/test_date_function.out | 605 ++++++++++++++++++++
.../datetime_functions/test_date_function.groovy | 624 +++++++++++++++++++++
8 files changed, 1298 insertions(+), 2 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rewrite/rules/FoldConstantRuleOnFE.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rewrite/rules/FoldConstantRuleOnFE.java
index afd9a029ba..e36a21bd05 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rewrite/rules/FoldConstantRuleOnFE.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rewrite/rules/FoldConstantRuleOnFE.java
@@ -258,7 +258,7 @@ public class FoldConstantRuleOnFE extends AbstractExpressionRewriteRule {
return new NullLiteral(cast.getDataType());
}
try {
- Expression castResult = child.castTo(cast.getDataType());
+ Expression castResult = child.checkedCastTo(cast.getDataType());
if (!Objects.equals(castResult, cast) && !Objects.equals(castResult, child)) {
castResult = rewrite(castResult, context);
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Expression.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Expression.java
index 1bcd9a09bc..a45af25e55 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Expression.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Expression.java
@@ -135,6 +135,10 @@ public abstract class Expression extends AbstractTreeNode<Expression> implements
return uncheckedCastTo(targetType);
}
+ public Expression checkedCastTo(DataType targetType) throws AnalysisException {
+ return castTo(targetType);
+ }
+
protected Expression uncheckedCastTo(DataType targetType) throws AnalysisException {
throw new RuntimeException("Do not implement uncheckedCastTo");
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/ArrayLiteral.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/ArrayLiteral.java
index 289dcc5b14..9d13bbc3ea 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/ArrayLiteral.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/ArrayLiteral.java
@@ -68,6 +68,15 @@ public class ArrayLiteral extends Literal {
return super.uncheckedCastTo(targetType);
}
+ @Override
+ public Expression checkedCastTo(DataType targetType) {
+ if (targetType instanceof ArrayType) {
+ return new Array(
+ items.stream().toArray(Expression[]::new)).checkedCastTo(targetType);
+ }
+ return super.checkedCastTo(targetType);
+ }
+
@Override
public String toString() {
String items = this.items.stream()
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/Literal.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/Literal.java
index 812436218d..3c5793e413 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/Literal.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/Literal.java
@@ -25,6 +25,7 @@ import org.apache.doris.nereids.trees.expressions.shape.LeafExpression;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.types.CharType;
import org.apache.doris.nereids.types.DataType;
+import org.apache.doris.nereids.types.LargeIntType;
import org.apache.commons.lang3.StringUtils;
@@ -171,6 +172,47 @@ public abstract class Literal extends Expression implements LeafExpression, Comp
}
}
+ /**
+ * literal expr compare.
+ */
+ @Override
+ public Expression checkedCastTo(DataType targetType) throws AnalysisException {
+ if (getDataType().isNumericType()) {
+ String desc = getStringValue();
+ BigDecimal val = new BigDecimal(desc);
+ BigDecimal maxVal = val;
+ BigDecimal minVal = val;
+ if (targetType.isTinyIntType()) {
+ maxVal = new BigDecimal(Byte.MAX_VALUE);
+ minVal = new BigDecimal(Byte.MIN_VALUE);
+ } else if (targetType.isSmallIntType()) {
+ maxVal = new BigDecimal(Short.MAX_VALUE);
+ minVal = new BigDecimal(Short.MIN_VALUE);
+ } else if (targetType.isIntegerType()) {
+ maxVal = new BigDecimal(Integer.MAX_VALUE);
+ minVal = new BigDecimal(Integer.MIN_VALUE);
+ } else if (targetType.isBigIntType()) {
+ maxVal = new BigDecimal(Long.MAX_VALUE);
+ minVal = new BigDecimal(Long.MIN_VALUE);
+ } else if (targetType.isLargeIntType()) {
+ maxVal = new BigDecimal(LargeIntType.MAX_VALUE);
+ minVal = new BigDecimal(LargeIntType.MIN_VALUE);
+ } else if (targetType.isFloatType()) {
+ maxVal = new BigDecimal(Float.MAX_VALUE);
+ minVal = new BigDecimal(-Float.MAX_VALUE);
+ } else if (targetType.isDoubleType()) {
+ maxVal = new BigDecimal(Double.MAX_VALUE);
+ minVal = new BigDecimal(-Double.MAX_VALUE);
+ }
+
+ if (val.compareTo(maxVal) > 0 || val.compareTo(minVal) < 0) {
+ throw new AnalysisException(
+ String.format("{} can't cast to {}", desc, targetType));
+ }
+ }
+ return uncheckedCastTo(targetType);
+ }
+
@Override
protected Expression uncheckedCastTo(DataType targetType) throws AnalysisException {
String desc = getStringValue();
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/types/DateTimeV2Type.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/types/DateTimeV2Type.java
index c2e72e0cda..8f826c50f0 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/types/DateTimeV2Type.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/types/DateTimeV2Type.java
@@ -20,6 +20,7 @@ package org.apache.doris.nereids.types;
import org.apache.doris.catalog.ScalarType;
import org.apache.doris.catalog.Type;
import org.apache.doris.nereids.exceptions.AnalysisException;
+import org.apache.doris.nereids.types.coercion.AbstractDataType;
import org.apache.doris.nereids.types.coercion.DateLikeType;
import com.google.common.base.Preconditions;
@@ -31,7 +32,7 @@ import java.util.Objects;
*/
public class DateTimeV2Type extends DateLikeType {
public static final int MAX_SCALE = 6;
- public static final DateTimeV2Type SYSTEM_DEFAULT = new DateTimeV2Type(0);
+ public static final DateTimeV2Type SYSTEM_DEFAULT = new DateTimeV2Type(MAX_SCALE);
private static final int WIDTH = 8;
@@ -75,6 +76,11 @@ public class DateTimeV2Type extends DateLikeType {
return Objects.equals(scale, that.scale);
}
+ @Override
+ public boolean acceptsType(AbstractDataType other) {
+ return other instanceof DateTimeV2Type;
+ }
+
@Override
public int width() {
return WIDTH;
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/types/LargeIntType.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/types/LargeIntType.java
index 9c5e1838b8..874fe56460 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/types/LargeIntType.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/types/LargeIntType.java
@@ -21,12 +21,18 @@ import org.apache.doris.catalog.Type;
import org.apache.doris.nereids.types.coercion.AbstractDataType;
import org.apache.doris.nereids.types.coercion.IntegralType;
+import java.math.BigInteger;
+
/**
* LargeInt type in Nereids.
*/
public class LargeIntType extends IntegralType {
public static final LargeIntType INSTANCE = new LargeIntType();
+ public static final BigInteger MAX_VALUE = new BigInteger("170141183460469231731687303715884105727");
+
+ public static final BigInteger MIN_VALUE = new BigInteger("-170141183460469231731687303715884105728");
+
private static final int WIDTH = 16;
private LargeIntType() {
diff --git a/regression-test/data/nereids_p0/datetime_functions/test_date_function.out b/regression-test/data/nereids_p0/datetime_functions/test_date_function.out
new file mode 100644
index 0000000000..e48e583e96
--- /dev/null
+++ b/regression-test/data/nereids_p0/datetime_functions/test_date_function.out
@@ -0,0 +1,605 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !sql --
+2019-07-31T22:21:03
+
+-- !sql --
+2019-07-31T22:21:03
+
+-- !sql --
+2019-08-01T06:21:03
+
+-- !sql --
+2019-08-01T06:21:03
+
+-- !sql --
+\N
+
+-- !sql --
+\N
+
+-- !sql --
+\N
+
+-- !sql --
+\N
+
+-- !sql1 --
+1 2019-08-01T13:21:03 Asia/Shanghai Asia/Shanghai 2019-08-01T13:21:03
+2 2019-08-01T13:21:03 Asia/Singapore Asia/Shanghai 2019-08-01T13:21:03
+3 2019-08-01T13:21:03 Asia/Taipei Asia/Shanghai 2019-08-01T13:21:03
+4 2019-08-02T13:21:03 Australia/Queensland Asia/Shanghai 2019-08-02T11:21:03
+5 2019-08-02T13:21:03 Australia/Lindeman Asia/Shanghai 2019-08-02T11:21:03
+6 2019-08-03T13:21:03 America/Aruba Asia/Shanghai 2019-08-04T01:21:03
+7 2019-08-03T13:21:03 America/Blanc-Sablon Asia/Shanghai 2019-08-04T01:21:03
+8 2019-08-04T13:21:03 America/Dawson Africa/Lusaka 2019-08-04T22:21:03
+9 2019-08-04T13:21:03 America/Creston Africa/Lusaka 2019-08-04T22:21:03
+10 2019-08-05T13:21:03 Asia/Shanghai Asia/Shanghai 2019-08-05T13:21:03
+11 2019-08-05T13:21:03 Asia/Shanghai Asia/Singapore 2019-08-05T13:21:03
+12 2019-08-05T13:21:03 Asia/Shanghai Asia/Taipei 2019-08-05T13:21:03
+13 2019-08-06T13:21:03 Asia/Shanghai Australia/Queensland 2019-08-06T15:21:03
+14 2019-08-06T13:21:03 Asia/Shanghai Australia/Lindeman 2019-08-06T15:21:03
+15 2019-08-07T13:21:03 Asia/Shanghai America/Aruba 2019-08-07T01:21:03
+16 2019-08-07T13:21:03 Asia/Shanghai America/Blanc-Sablon 2019-08-07T01:21:03
+17 2019-08-08T13:21:03 Africa/Lusaka America/Dawson 2019-08-08T04:21:03
+18 2019-08-08T13:21:03 Africa/Lusaka America/Creston 2019-08-08T04:21:03
+
+-- !sql2 --
+2019-08-01T13:21:03 2019-08-01T13:21:03 2019-08-01T13:21:03
+
+-- !sql3 --
+2019-08-02T11:21:03 2019-08-02T11:21:03 2019-08-02T11:21:03
+
+-- !sql4 --
+2019-08-04T22:21:03 2019-08-04T22:21:03 2019-08-04T22:21:03
+
+-- !sql_vec1 --
+1 2019-08-01T13:21:03 Asia/Shanghai Asia/Shanghai 2019-08-01T13:21:03
+2 2019-08-01T13:21:03 Asia/Singapore Asia/Shanghai 2019-08-01T13:21:03
+3 2019-08-01T13:21:03 Asia/Taipei Asia/Shanghai 2019-08-01T13:21:03
+4 2019-08-02T13:21:03 Australia/Queensland Asia/Shanghai 2019-08-02T11:21:03
+5 2019-08-02T13:21:03 Australia/Lindeman Asia/Shanghai 2019-08-02T11:21:03
+6 2019-08-03T13:21:03 America/Aruba Asia/Shanghai 2019-08-04T01:21:03
+7 2019-08-03T13:21:03 America/Blanc-Sablon Asia/Shanghai 2019-08-04T01:21:03
+8 2019-08-04T13:21:03 America/Dawson Africa/Lusaka 2019-08-04T22:21:03
+9 2019-08-04T13:21:03 America/Creston Africa/Lusaka 2019-08-04T22:21:03
+10 2019-08-05T13:21:03 Asia/Shanghai Asia/Shanghai 2019-08-05T13:21:03
+11 2019-08-05T13:21:03 Asia/Shanghai Asia/Singapore 2019-08-05T13:21:03
+12 2019-08-05T13:21:03 Asia/Shanghai Asia/Taipei 2019-08-05T13:21:03
+13 2019-08-06T13:21:03 Asia/Shanghai Australia/Queensland 2019-08-06T15:21:03
+14 2019-08-06T13:21:03 Asia/Shanghai Australia/Lindeman 2019-08-06T15:21:03
+15 2019-08-07T13:21:03 Asia/Shanghai America/Aruba 2019-08-07T01:21:03
+16 2019-08-07T13:21:03 Asia/Shanghai America/Blanc-Sablon 2019-08-07T01:21:03
+17 2019-08-08T13:21:03 Africa/Lusaka America/Dawson 2019-08-08T04:21:03
+18 2019-08-08T13:21:03 Africa/Lusaka America/Creston 2019-08-08T04:21:03
+
+-- !sql_vec2 --
+2019-08-01T13:21:03 2019-08-01T13:21:03 2019-08-01T13:21:03
+
+-- !sql_vec3 --
+2019-08-02T11:21:03 2019-08-02T11:21:03 2019-08-02T11:21:03
+
+-- !sql_vec4 --
+2019-08-04T22:21:03 2019-08-04T22:21:03 2019-08-04T22:21:03
+
+-- !sql --
+2012-11-30T23:59:59
+
+-- !sql --
+2011-01-30T23:59:59
+
+-- !sql --
+2010-12-02T23:59:59
+
+-- !sql --
+2010-12-01T01:59:59
+
+-- !sql --
+2010-12-01T00:01:59
+
+-- !sql --
+2010-12-01T00:00:01
+
+-- !sql --
+22:23:00
+
+-- !sql --
+4th 00 Thu 04 10 Oct 277
+
+-- !sql --
+22 22 10 10:23:00 PM 22:23:00 00 6
+
+-- !sql --
+1998 52
+
+-- !sql --
+01
+
+-- !sql --
+%01
+
+-- !sql --
+2009-10-04
+
+-- !sql --
+2008-11-30T23:59:59
+
+-- !sql --
+2010-09-30T23:59:59
+
+-- !sql --
+2010-11-28T23:59:59
+
+-- !sql --
+2010-11-30T21:59:59
+
+-- !sql --
+2010-11-30T23:57:59
+
+-- !sql --
+2010-11-30T23:59:57
+
+-- !sql --
+1
+
+-- !sql --
+-31
+
+-- !sql --
+16
+
+-- !sql --
+31
+
+-- !sql --
+29
+
+-- !sql --
+Saturday
+
+-- !sql --
+31
+
+-- !sql --
+3
+
+-- !sql --
+3
+
+-- !sql --
+34
+
+-- !sql --
+34
+
+-- !sql --
+2000-07-03
+
+-- !sql --
+0000-01-01
+
+-- !sql --
+2007-12-01 00:30:19
+
+-- !sql --
+2007-12-01 00:30:19
+
+-- !sql --
+2007-12-01
+
+-- !sql --
+2007-12-01 00:30:19
+
+-- !sql --
+\N
+
+-- !sql --
+23
+
+-- !sql --
+0
+
+-- !sql --
+2021-01-01 2021-04-10 2022-02-04
+
+-- !sql --
+59
+
+-- !sql --
+0
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+February
+
+-- !sql --
+February
+
+-- !sql --
+59
+
+-- !sql --
+0
+
+-- !sql --
+2014-12-21T12:34:56
+
+-- !sql --
+2014-12-21T12:34:56
+
+-- !sql --
+2014-12-21T12:34:56
+
+-- !sql --
+2004-10-18
+
+-- !sql --
+2020-09-01T00:00
+
+-- !sql --
+2020-01-01T00:00
+
+-- !sql --
+2020-04-01T00:00
+
+-- !sql --
+2020-02-03T00:00
+
+-- !sql --
+2020-04-09T00:00
+
+-- !sql --
+08:00:00
+
+-- !sql --
+00:00:09
+
+-- !sql --
+\N
+
+-- !sql --
+2020-01-02T00:00
+
+-- !sql --
+2019-02-02T00:00
+
+-- !sql --
+2019-01-09T00:00
+
+-- !sql --
+2019-01-03T00:00
+
+-- !sql --
+2019-01-02T01:00
+
+-- !sql --
+2019-01-02T00:01
+
+-- !sql --
+2019-01-02T00:00:01
+
+-- !sql --
+3
+
+-- !sql --
+-1
+
+-- !sql --
+128885
+
+-- !sql --
+7689600
+
+-- !sql --
+2136
+
+-- !sql --
+89
+
+-- !sql --
+12
+
+-- !sql --
+733321
+
+-- !sql --
+749027
+
+-- !sql --
+1196389819
+
+-- !sql --
+1196389819
+
+-- !sql --
+1196389819
+
+-- !sql --
+0
+
+-- !sql --
+0
+
+-- !sql --
+27
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+8
+
+-- !sql --
+1987
+
+-- !sql --
+2050
+
+-- !sql --
+0000-08-01T13:21:03 0
+2019-08-01T13:21:03 2019
+9999-08-01T13:21:03 9999
+
+-- !sql --
+202052
+
+-- !sql --
+202026
+
+-- !sql --
+198912
+
+-- !sql --
+198912
+
+-- !sql --
+198912
+
+-- !sql --
+198912
+
+-- !sql --
+198912
+
+-- !sql --
+198912
+
+-- !sql --
+198912
+
+-- !sql --
+198912
+
+-- !sql --
+1 2022-08-01
+2 2022-08-01
+4 2022-08-01
+
+-- !sql --
+
+-- !sql --
+3 2022-07-31
+
+-- !sql --
+1 2022-08-01
+2 2022-08-01
+4 2022-08-01
+
+-- !sql --
+1 2022-08-01
+2 2022-08-01
+3 2022-07-31
+4 2022-08-01
+
+-- !sql --
+1 2022-08-01
+2 2022-08-01
+4 2022-08-01
+
+-- !sql --
+2 2022-08-01 00:00:00
+
+-- !sql --
+1 2022-08-01 17:00:31
+4 2022-08-01 00:00:01
+
+-- !sql --
+3 2022-07-31 23:59:59
+
+-- !sql --
+1 2022-08-01 17:00:31
+2 2022-08-01 00:00:00
+4 2022-08-01 00:00:01
+
+-- !sql --
+2 2022-08-01 00:00:00
+3 2022-07-31 23:59:59
+
+-- !sql --
+2 2022-08-01 00:00:00
+
+-- !sql --
+1 2022-08-01 17:00:31
+
+-- !sql --
+true
+
+-- !sql --
+true
+
+-- !sql --
+2022 31 4
+
+-- !sql --
+\N
+
+-- !sql --
+2022-07-12T20:00:45
+
+-- !sql --
+2018-04-02T15:03:28
+
+-- !sql --
+19 19 21 22 23 24 25 26
+
+-- !sql --
+19 19 21 22 23 24 25 26
+
+-- !sql --
+2020-08-01T13:21:03
+
+-- !sql --
+2019-09-01T13:21:03
+
+-- !sql --
+2019-08-08T13:21:03
+
+-- !sql --
+2019-08-02T13:21:03
+
+-- !sql --
+2019-08-01T14:21:03
+
+-- !sql --
+2019-08-01T13:22:03
+
+-- !sql --
+2019-08-01T13:21:04
+
+-- !sql --
+2018-08-01T13:21:03
+
+-- !sql --
+2019-07-01T13:21:03
+
+-- !sql --
+2019-07-25T13:21:03
+
+-- !sql --
+2019-07-31T13:21:03
+
+-- !sql --
+2019-08-01T12:21:03
+
+-- !sql --
+2019-08-01T13:20:03
+
+-- !sql --
+2019-08-01T13:21:02
+
+-- !sql --
+\N
+
+-- !sql --
+\N
+
+-- !sql --
+2020-08-01T13:21:03.111
+
+-- !sql --
+2019-09-01T13:21:03.111
+
+-- !sql --
+2019-08-08T13:21:03.111
+
+-- !sql --
+2019-08-02T13:21:03.111
+
+-- !sql --
+2019-08-01T14:21:03.111
+
+-- !sql --
+2019-08-01T13:22:03.111
+
+-- !sql --
+2019-08-01T13:21:04.111
+
+-- !sql --
+2018-08-01T13:21:03.111
+
+-- !sql --
+2019-07-01T13:21:03.111
+
+-- !sql --
+2019-07-25T13:21:03.111
+
+-- !sql --
+2019-07-31T13:21:03.111
+
+-- !sql --
+2019-08-01T12:21:03.111
+
+-- !sql --
+2019-08-01T13:20:03.111
+
+-- !sql --
+2019-08-01T13:21:02.111
+
+-- !sql --
+2020-08-01T13:21:03.111111
+
+-- !sql --
+2019-09-01T13:21:03.111111
+
+-- !sql --
+2019-08-08T13:21:03.111111
+
+-- !sql --
+2019-08-02T13:21:03.111111
+
+-- !sql --
+2019-08-01T14:21:03.111111
+
+-- !sql --
+2019-08-01T13:22:03.111111
+
+-- !sql --
+2019-08-01T13:21:04.111111
+
+-- !sql --
+2018-08-01T13:21:03.111111
+
+-- !sql --
+2019-07-01T13:21:03.111111
+
+-- !sql --
+2019-07-25T13:21:03.111111
+
+-- !sql --
+2019-07-31T13:21:03.111111
+
+-- !sql --
+2019-08-01T12:21:03.111111
+
+-- !sql --
+2019-08-01T13:20:03.111111
+
+-- !sql --
+2019-08-01T13:21:02.111111
+
+-- !sql --
+\N \N \N \N
+2000-02-29 2000-02-29 2000-02-29 2000-02-29
+2022-01-31 2022-01-31 2022-01-31 2022-01-31
+2022-02-28 2022-02-28 2022-02-28 \N
+
+-- !sql --
+\N \N
+2000-02-29 2000-02-29
+2022-01-31 2022-01-31
+2022-02-28 2022-02-28
+
+-- !sql --
+\N \N \N \N
+1970-01-01 1970-01-01 1970-01-01 1970-01-01
+2000-01-31 2000-01-31 2000-01-31 2000-01-31
+2021-12-27 2021-12-27 2021-12-27 2021-12-27
+2022-02-28 2022-02-28 2022-02-28 2022-02-28
+
diff --git a/regression-test/suites/nereids_p0/datetime_functions/test_date_function.groovy b/regression-test/suites/nereids_p0/datetime_functions/test_date_function.groovy
new file mode 100644
index 0000000000..f973c77916
--- /dev/null
+++ b/regression-test/suites/nereids_p0/datetime_functions/test_date_function.groovy
@@ -0,0 +1,624 @@
+// 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.text.SimpleDateFormat
+
+suite("test_date_function") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def tableName = "test_date_function"
+
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ test_datetime datetime NULL COMMENT ""
+ ) ENGINE=OLAP
+ DUPLICATE KEY(test_datetime)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(test_datetime) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ )
+ """
+ sql """ insert into ${tableName} values ("2019-08-01 13:21:03") """
+ // convert_tz
+ qt_sql """ SELECT convert_tz(test_datetime, 'Asia/Shanghai', 'America/Los_Angeles') result from ${tableName}; """
+ qt_sql """ SELECT convert_tz(test_datetime, '+08:00', 'America/Los_Angeles') result from ${tableName}; """
+
+ qt_sql """ SELECT convert_tz(test_datetime, 'Asia/Shanghai', 'Europe/London') result from ${tableName}; """
+ qt_sql """ SELECT convert_tz(test_datetime, '+08:00', 'Europe/London') result from ${tableName}; """
+
+ qt_sql """ SELECT convert_tz(test_datetime, '+08:00', 'America/London') result from ${tableName}; """
+
+ // some invalid date
+ qt_sql """ SELECT convert_tz('2022-2-29 13:21:03', '+08:00', 'America/London') result; """
+ qt_sql """ SELECT convert_tz('2022-02-29 13:21:03', '+08:00', 'America/London') result; """
+ qt_sql """ SELECT convert_tz('1900-00-00 13:21:03', '+08:00', 'America/London') result; """
+
+ sql """ truncate table ${tableName} """
+
+ def timezoneCachedTableName = "test_convert_tz_with_timezone_cache"
+ sql """ DROP TABLE IF EXISTS ${timezoneCachedTableName} """
+ sql """
+ CREATE TABLE ${timezoneCachedTableName} (
+ id int,
+ test_datetime datetime NULL COMMENT "",
+ origin_tz VARCHAR(255),
+ target_tz VARCHAR(255)
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ )
+ """
+
+ sql """
+ INSERT INTO ${timezoneCachedTableName} VALUES
+ (1, "2019-08-01 13:21:03", "Asia/Shanghai", "Asia/Shanghai"),
+ (2, "2019-08-01 13:21:03", "Asia/Singapore", "Asia/Shanghai"),
+ (3, "2019-08-01 13:21:03", "Asia/Taipei", "Asia/Shanghai"),
+ (4, "2019-08-02 13:21:03", "Australia/Queensland", "Asia/Shanghai"),
+ (5, "2019-08-02 13:21:03", "Australia/Lindeman", "Asia/Shanghai"),
+ (6, "2019-08-03 13:21:03", "America/Aruba", "Asia/Shanghai"),
+ (7, "2019-08-03 13:21:03", "America/Blanc-Sablon", "Asia/Shanghai"),
+ (8, "2019-08-04 13:21:03", "America/Dawson", "Africa/Lusaka"),
+ (9, "2019-08-04 13:21:03", "America/Creston", "Africa/Lusaka"),
+ (10, "2019-08-05 13:21:03", "Asia/Shanghai", "Asia/Shanghai"),
+ (11, "2019-08-05 13:21:03", "Asia/Shanghai", "Asia/Singapore"),
+ (12, "2019-08-05 13:21:03", "Asia/Shanghai", "Asia/Taipei"),
+ (13, "2019-08-06 13:21:03", "Asia/Shanghai", "Australia/Queensland"),
+ (14, "2019-08-06 13:21:03", "Asia/Shanghai", "Australia/Lindeman"),
+ (15, "2019-08-07 13:21:03", "Asia/Shanghai", "America/Aruba"),
+ (16, "2019-08-07 13:21:03", "Asia/Shanghai", "America/Blanc-Sablon"),
+ (17, "2019-08-08 13:21:03", "Africa/Lusaka", "America/Dawson"),
+ (18, "2019-08-08 13:21:03", "Africa/Lusaka", "America/Creston")
+ """
+
+ sql "set parallel_fragment_exec_instance_num = 8"
+
+ qt_sql1 """
+ SELECT
+ `id`, `test_datetime`, `origin_tz`, `target_tz`, convert_tz(`test_datetime`, `origin_tz`, `target_tz`)
+ FROM
+ ${timezoneCachedTableName}
+ ORDER BY `id`
+ """
+ qt_sql2 """
+ SELECT
+ convert_tz(`test_datetime`, `origin_tz`, `target_tz`),
+ convert_tz(`test_datetime`, "Asia/Singapore", `target_tz`),
+ convert_tz(`test_datetime`, `origin_tz`, "Asia/Shanghai")
+ FROM
+ ${timezoneCachedTableName}
+ WHERE
+ id = 2;
+ """
+ qt_sql3 """
+ SELECT
+ convert_tz(`test_datetime`, `origin_tz`, `target_tz`),
+ convert_tz(`test_datetime`, "Australia/Queensland", `target_tz`),
+ convert_tz(`test_datetime`, `origin_tz`, "Asia/Shanghai")
+ FROM
+ ${timezoneCachedTableName}
+ WHERE
+ id = 4;
+ """
+ qt_sql4 """
+ SELECT
+ convert_tz(`test_datetime`, `origin_tz`, `target_tz`),
+ convert_tz(`test_datetime`, "America/Dawson", `target_tz`),
+ convert_tz(`test_datetime`, `origin_tz`, "Africa/Lusaka")
+ FROM
+ ${timezoneCachedTableName}
+ WHERE
+ id = 8;
+ """
+
+ qt_sql_vec1 """
+ SELECT
+ `id`, `test_datetime`, `origin_tz`, `target_tz`, convert_tz(`test_datetime`, `origin_tz`, `target_tz`)
+ FROM
+ ${timezoneCachedTableName}
+ ORDER BY `id`
+ """
+ qt_sql_vec2 """
+ SELECT
+ convert_tz(`test_datetime`, `origin_tz`, `target_tz`),
+ convert_tz(`test_datetime`, "Asia/Singapore", `target_tz`),
+ convert_tz(`test_datetime`, `origin_tz`, "Asia/Shanghai")
+ FROM
+ ${timezoneCachedTableName}
+ WHERE
+ id = 2;
+ """
+ qt_sql_vec3 """
+ SELECT
+ convert_tz(`test_datetime`, `origin_tz`, `target_tz`),
+ convert_tz(`test_datetime`, "Australia/Queensland", `target_tz`),
+ convert_tz(`test_datetime`, `origin_tz`, "Asia/Shanghai")
+ FROM
+ ${timezoneCachedTableName}
+ WHERE
+ id = 4;
+ """
+ qt_sql_vec4 """
+ SELECT
+ convert_tz(`test_datetime`, `origin_tz`, `target_tz`),
+ convert_tz(`test_datetime`, "America/Dawson", `target_tz`),
+ convert_tz(`test_datetime`, `origin_tz`, "Africa/Lusaka")
+ FROM
+ ${timezoneCachedTableName}
+ WHERE
+ id = 8;
+ """
+
+ // curdate,current_date
+ String curdate_str = new SimpleDateFormat("yyyy-MM-dd").format(new Date())
+ def curdate_result = sql """ SELECT CURDATE() """
+ def curdate_date_result = sql """ SELECT CURRENT_DATE() """
+ assertTrue(curdate_str == curdate_result[0][0].toString())
+ assertTrue(curdate_str == curdate_date_result[0][0].toString())
+
+ // DATETIME CURRENT_TIMESTAMP()
+ def current_timestamp_result = """ SELECT current_timestamp() """
+ assertTrue(current_timestamp_result[0].size() == 1)
+
+ // TIME CURTIME()
+ def curtime_result = sql """ SELECT CURTIME() """
+ assertTrue(curtime_result[0].size() == 1)
+
+ sql """ insert into ${tableName} values ("2010-11-30 23:59:59") """
+ // DATE_ADD
+ qt_sql """ select date_add(test_datetime, INTERVAL 2 YEAR) result from ${tableName}; """
+ qt_sql """ select date_add(test_datetime, INTERVAL 2 MONTH) result from ${tableName}; """
+ qt_sql """ select date_add(test_datetime, INTERVAL 2 DAY) result from ${tableName}; """
+ qt_sql """ select date_add(test_datetime, INTERVAL 2 HOUR) result from ${tableName}; """
+ qt_sql """ select date_add(test_datetime, INTERVAL 2 MINUTE) result from ${tableName}; """
+ qt_sql """ select date_add(test_datetime, INTERVAL 2 SECOND) result from ${tableName}; """
+
+ // DATE_FORMAT
+ sql """ truncate table ${tableName} """
+ sql """ insert into ${tableName} values ("2009-10-04 22:23:00") """
+ def resArray = ["Sunday October 2009", "星期日 十月 2009"]
+ def res = sql """ select date_format(test_datetime, '%W %M %Y') from ${tableName}; """
+ assertTrue(resArray.contains(res[0][0]))
+ sql """ truncate table ${tableName} """
+ sql """ insert into ${tableName} values ("2007-10-04 22:23:00") """
+ qt_sql """ select date_format(test_datetime, '%H:%i:%s') from ${tableName};"""
+ sql """ truncate table ${tableName} """
+ sql """ insert into ${tableName} values ("1900-10-04 22:23:00") """
+ qt_sql """ select date_format(test_datetime, '%D %y %a %d %m %b %j') from ${tableName}; """
+ sql """ truncate table ${tableName} """
+ sql """ insert into ${tableName} values ("1997-10-04 22:23:00") """
+ qt_sql """ select date_format(test_datetime, '%H %k %I %r %T %S %w') from ${tableName}; """
+ sql """ truncate table ${tableName} """
+ sql """ insert into ${tableName} values ("1999-01-01 00:00:00") """
+ qt_sql """ select date_format(test_datetime, '%X %V') from ${tableName}; """
+ sql """ truncate table ${tableName} """
+ sql """ insert into ${tableName} values ("2006-06-01") """
+ qt_sql """ select date_format(test_datetime, '%d') from ${tableName}; """
+ qt_sql """ select date_format(test_datetime, '%%%d') from ${tableName}; """
+ sql """ truncate table ${tableName} """
+ sql """ insert into ${tableName} values ("2009-10-04 22:23:00") """
+ qt_sql """ select date_format(test_datetime, 'yyyy-MM-dd') from ${tableName}; """
+ sql """ truncate table ${tableName} """
+
+ sql """ insert into ${tableName} values ("2010-11-30 23:59:59") """
+ // DATE_SUB
+ qt_sql """ select date_sub(test_datetime, INTERVAL 2 YEAR) from ${tableName};"""
+ qt_sql """ select date_sub(test_datetime, INTERVAL 2 MONTH) from ${tableName};"""
+ qt_sql """ select date_sub(test_datetime, INTERVAL 2 DAY) from ${tableName};"""
+ qt_sql """ select date_sub(test_datetime, INTERVAL 2 HOUR) from ${tableName};"""
+ qt_sql """ select date_sub(test_datetime, INTERVAL 2 MINUTE) from ${tableName};"""
+ qt_sql """ select date_sub(test_datetime, INTERVAL 2 SECOND) from ${tableName};"""
+
+
+ // DATEDIFF
+ qt_sql """ select datediff(CAST('2007-12-31 23:59:59' AS DATETIME), CAST('2007-12-30' AS DATETIME)) """
+ qt_sql """ select datediff(CAST('2010-11-30 23:59:59' AS DATETIME), CAST('2010-12-31' AS DATETIME)) """
+ qt_sql """ select datediff('2010-10-31', '2010-10-15') """
+
+ // DAY
+ qt_sql """ select day('1987-01-31') """
+ qt_sql """ select day('2004-02-29') """
+
+ // DAYNAME
+ qt_sql """ select dayname('2007-02-03 00:00:00') """
+
+ // DAYOFMONTH
+ qt_sql """ select dayofmonth('1987-01-31') """
+
+ // DAYOFWEEK
+ qt_sql """ select dayofweek('2019-06-25') """
+ qt_sql """ select dayofweek(cast(20190625 as date)) """
+
+ // DAYOFYEAR
+ qt_sql """ select dayofyear('2007-02-03 10:00:00') """
+ qt_sql """ select dayofyear('2007-02-03') """
+
+ // FROM_DAYS
+ // 通过距离0000-01-01日的天数计算出哪一天
+ qt_sql """ select from_days(730669) """
+ qt_sql """ select from_days(1) """
+
+ // FROM_UNIXTIME
+ qt_sql """ select from_unixtime(1196440219) """
+ qt_sql """ select from_unixtime(1196440219, 'yyyy-MM-dd HH:mm:ss') """
+ qt_sql """ select from_unixtime(1196440219, '%Y-%m-%d') """
+ qt_sql """ select from_unixtime(1196440219, '%Y-%m-%d %H:%i:%s') """
+ qt_sql """ select from_unixtime(253402272000, '%Y-%m-%d %H:%i:%s') """
+
+ // HOUR
+ qt_sql """ select hour('2018-12-31 23:59:59') """
+ qt_sql """ select hour('2018-12-31') """
+
+ // MAKEDATE
+ qt_sql """ select makedate(2021,1), makedate(2021,100), makedate(2021,400) """
+
+ // MINUTE
+ qt_sql """ select minute('2018-12-31 23:59:59') """
+ qt_sql """ select minute('2018-12-31') """
+
+ // MONTH
+ qt_sql """ select month('1987-01-01 23:59:59') """
+ qt_sql """ select month('1987-01-01') """
+
+ // MONTHNAME
+ qt_sql """ select monthname('2008-02-03 00:00:00') """
+ qt_sql """ select monthname('2008-02-03') """
+
+ // NOW
+ def now_result = sql """ select now() """
+ assertTrue(now_result[0].size() == 1)
+
+ // SECOND
+ qt_sql """ select second('2018-12-31 23:59:59') """
+ qt_sql """ select second('2018-12-31 00:00:00') """
+
+ // STR_TO_DATE
+ sql """ truncate table ${tableName} """
+ sql """ insert into ${tableName} values ("2014-12-21 12:34:56") """
+ qt_sql """ select str_to_date(test_datetime, '%Y-%m-%d %H:%i:%s') from ${tableName}; """
+ qt_sql """ select str_to_date("2014-12-21 12:34%3A56", '%Y-%m-%d %H:%i%%3A%s'); """
+ qt_sql """ select str_to_date("2014-12-21 12:34:56.789 PM", '%Y-%m-%d %h:%i:%s.%f %p'); """
+ qt_sql """ select str_to_date('200442 Monday', '%X%V %W') """
+ sql """ truncate table ${tableName} """
+ sql """ insert into ${tableName} values ("2020-09-01") """
+ qt_sql """ select str_to_date(test_datetime, "%Y-%m-%d %H:%i:%s") from ${tableName};"""
+
+ // TIME_ROUND
+ qt_sql """ SELECT YEAR_FLOOR('20200202000000') """
+ qt_sql """ SELECT MONTH_CEIL(CAST('2020-02-02 13:09:20' AS DATETIME), 3) """
+ qt_sql """ SELECT WEEK_CEIL('2020-02-02 13:09:20', '2020-01-06') """
+ qt_sql """ SELECT MONTH_CEIL(CAST('2020-02-02 13:09:20' AS DATETIME), 3, CAST('1970-01-09 00:00:00' AS DATETIME)) """
+
+ // TIMEDIFF
+ qt_sql """ SELECT TIMEDIFF(now(),utc_timestamp()) """
+ qt_sql """ SELECT TIMEDIFF('2019-07-11 16:59:30','2019-07-11 16:59:21') """
+ qt_sql """ SELECT TIMEDIFF('2019-01-01 00:00:00', NULL) """
+
+ // TIMESTAMPADD
+ sql """ truncate table ${tableName} """
+ sql """ insert into ${tableName} values ("2019-01-02") ; """
+ qt_sql """ SELECT TIMESTAMPADD(YEAR,1,test_datetime) from ${tableName}; """
+ qt_sql """ SELECT TIMESTAMPADD(MONTH,1,test_datetime) from ${tableName}; """
+ qt_sql """ SELECT TIMESTAMPADD(WEEK,1,test_datetime) from ${tableName}; """
+ qt_sql """ SELECT TIMESTAMPADD(DAY,1,test_datetime) from ${tableName}; """
+ qt_sql """ SELECT TIMESTAMPADD(HOUR,1,test_datetime) from ${tableName}; """
+ qt_sql """ SELECT TIMESTAMPADD(MINUTE,1,test_datetime) from ${tableName}; """
+ qt_sql """ SELECT TIMESTAMPADD(SECOND,1,test_datetime) from ${tableName}; """
+
+ // TIMESTAMPDIFF
+ qt_sql """ SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') """
+ qt_sql """ SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01') """
+ qt_sql """ SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55') """
+ qt_sql """ SELECT TIMESTAMPDIFF(SECOND,'2003-02-01','2003-05-01') """
+ qt_sql """ SELECT TIMESTAMPDIFF(HOUR,'2003-02-01','2003-05-01') """
+ qt_sql """ SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01') """
+ qt_sql """ SELECT TIMESTAMPDIFF(WEEK,'2003-02-01','2003-05-01') """
+
+ // TO_DAYS
+ qt_sql """ select to_days('2007-10-07') """
+ qt_sql """ select to_days('2050-10-07') """
+
+ // UNIX_TIMESTAMP
+ def unin_timestamp_str = """ select unix_timestamp() """
+ assertTrue(unin_timestamp_str[0].size() == 1)
+ qt_sql """ select unix_timestamp('2007-11-30 10:30:19') """
+ qt_sql """ select unix_timestamp('2007-11-30 10:30-19', '%Y-%m-%d %H:%i-%s') """
+ qt_sql """ select unix_timestamp('2007-11-30 10:30%3A19', '%Y-%m-%d %H:%i%%3A%s') """
+ qt_sql """ select unix_timestamp('1969-01-01 00:00:00') """
+
+ // UTC_TIMESTAMP
+ def utc_timestamp_str = sql """ select utc_timestamp(),utc_timestamp() + 1 """
+ assertTrue(utc_timestamp_str[0].size() == 2)
+ // WEEK
+ qt_sql """ select week('2020-1-1') """
+ qt_sql """ select week('2020-7-1',1) """
+
+ // WEEKDAY
+ qt_sql """ select weekday('2019-06-25'); """
+ qt_sql """ select weekday(cast(20190625 as date)); """
+
+ // WEEKOFYEAR
+ qt_sql """ select weekofyear('2008-02-20 00:00:00') """
+
+ sql """ truncate table ${tableName} """
+ sql """ insert into ${tableName} values ("2019-08-01 13:21:03"), ("9999-08-01 13:21:03"),("0-08-01 13:21:03")"""
+
+ // YEAR
+ qt_sql """ select year('1987-01-01') """
+ qt_sql """ select year('2050-01-01') """
+ qt_sql """ select test_datetime, year(test_datetime) from ${tableName} order by test_datetime """
+
+ // YEARWEEK
+ qt_sql """ select yearweek('2021-1-1') """
+ qt_sql """ select yearweek('2020-7-1') """
+ qt_sql """ select yearweek('1989-03-21', 0) """
+ qt_sql """ select yearweek('1989-03-21', 1) """
+ qt_sql """ select yearweek('1989-03-21', 2) """
+ qt_sql """ select yearweek('1989-03-21', 3) """
+ qt_sql """ select yearweek('1989-03-21', 4) """
+ qt_sql """ select yearweek('1989-03-21', 5) """
+ qt_sql """ select yearweek('1989-03-21', 6) """
+ qt_sql """ select yearweek('1989-03-21', 7) """
+
+ // qt_sql """ select count(*) from (select * from numbers("number" = "200")) tmp1 WHERE 0 <= UNIX_TIMESTAMP(); """
+
+ sql """ drop table ${tableName} """
+
+ tableName = "test_from_unixtime"
+
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ `id` INT NOT NULL COMMENT "用户id",
+ `update_time` INT NOT NULL COMMENT "数据灌入日期时间"
+ ) ENGINE=OLAP
+ UNIQUE KEY(`id`)
+ DISTRIBUTED BY HASH(`id`)
+ PROPERTIES("replication_num" = "1");
+ """
+ sql """ insert into ${tableName} values (1, 1659344431) """
+ sql """ insert into ${tableName} values (2, 1659283200) """
+ sql """ insert into ${tableName} values (3, 1659283199) """
+ sql """ insert into ${tableName} values (4, 1659283201) """
+
+ qt_sql """ SELECT id,FROM_UNIXTIME(update_time,"%Y-%m-%d") FROM ${tableName} WHERE FROM_UNIXTIME(update_time,"%Y-%m-%d") = '2022-08-01' ORDER BY id; """
+ qt_sql """ SELECT id,FROM_UNIXTIME(update_time,"%Y-%m-%d") FROM ${tableName} WHERE FROM_UNIXTIME(update_time,"%Y-%m-%d") > '2022-08-01' ORDER BY id; """
+ qt_sql """ SELECT id,FROM_UNIXTIME(update_time,"%Y-%m-%d") FROM ${tableName} WHERE FROM_UNIXTIME(update_time,"%Y-%m-%d") < '2022-08-01' ORDER BY id; """
+ qt_sql """ SELECT id,FROM_UNIXTIME(update_time,"%Y-%m-%d") FROM ${tableName} WHERE FROM_UNIXTIME(update_time,"%Y-%m-%d") >= '2022-08-01' ORDER BY id; """
+ qt_sql """ SELECT id,FROM_UNIXTIME(update_time,"%Y-%m-%d") FROM ${tableName} WHERE FROM_UNIXTIME(update_time,"%Y-%m-%d") <= '2022-08-01' ORDER BY id; """
+ qt_sql """ SELECT id,FROM_UNIXTIME(update_time,"%Y-%m-%d") FROM ${tableName} WHERE FROM_UNIXTIME(update_time,"%Y-%m-%d") LIKE '2022-08-01' ORDER BY id; """
+
+ qt_sql """ SELECT id,FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s") FROM ${tableName} WHERE FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s") = '2022-08-01 00:00:00' ORDER BY id; """
+ qt_sql """ SELECT id,FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s") FROM ${tableName} WHERE FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s") > '2022-08-01 00:00:00' ORDER BY id; """
+ qt_sql """ SELECT id,FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s") FROM ${tableName} WHERE FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s") < '2022-08-01 00:00:00' ORDER BY id; """
+ qt_sql """ SELECT id,FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s") FROM ${tableName} WHERE FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s") >= '2022-08-01 00:00:00' ORDER BY id; """
+ qt_sql """ SELECT id,FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s") FROM ${tableName} WHERE FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s") <= '2022-08-01 00:00:00' ORDER BY id; """
+ qt_sql """ SELECT id,FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s") FROM ${tableName} WHERE FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s") LIKE '2022-08-01 00:00:00' ORDER BY id; """
+ qt_sql """ SELECT id,FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s") FROM ${tableName} WHERE FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s") = '2022-08-01 17:00:31' ORDER BY id; """
+
+ qt_sql """SELECT CURDATE() = CURRENT_DATE();"""
+ qt_sql """SELECT unix_timestamp(CURDATE()) = unix_timestamp(CURRENT_DATE());"""
+
+ sql """ drop table ${tableName} """
+
+ qt_sql """ select date_format('2022-08-04', '%X %V %w'); """
+ qt_sql """ select STR_TO_DATE('Tue Jul 12 20:00:45 CST 2022', '%a %b %e %H:%i:%s %Y'); """
+ qt_sql """ select STR_TO_DATE('Tue Jul 12 20:00:45 CST 2022', '%a %b %e %T CST %Y'); """
+ qt_sql """ select STR_TO_DATE('2018-4-2 15:3:28','%Y-%m-%d %H:%i:%s'); """
+
+ qt_sql """ select length(cast(now() as string)), length(cast(now(0) as string)), length(cast(now(1) as string)),
+ length(cast(now(2) as string)), length(cast(now(3) as string)), length(cast(now(4) as string)),
+ length(cast(now(5) as string)), length(cast(now(6) as string)); """
+ qt_sql """ select length(cast(current_timestamp() as string)), length(cast(current_timestamp(0) as string)),
+ length(cast(current_timestamp(1) as string)), length(cast(current_timestamp(2) as string)),
+ length(cast(current_timestamp(3) as string)), length(cast(current_timestamp(4) as string)),
+ length(cast(current_timestamp(5) as string)), length(cast(current_timestamp(6) as string)); """
+
+
+ tableName = "test_time_add_sub_function"
+
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ test_time datetime NULL COMMENT "",
+ test_time1 datetimev2(3) NULL COMMENT "",
+ test_time2 datetimev2(6) NULL COMMENT ""
+ ) ENGINE=OLAP
+ DUPLICATE KEY(test_time)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(test_time) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ )
+ """
+ sql """ insert into ${tableName} values ("2019-08-01 13:21:03", "2019-08-01 13:21:03.111", "2019-08-01 13:21:03.111111") """
+ //years_add
+ qt_sql """ select years_add(test_time,1) result from ${tableName}; """
+ //months_add
+ qt_sql """ select months_add(test_time,1) result from ${tableName}; """
+ //weeks_add
+ qt_sql """ select weeks_add(test_time,1) result from ${tableName}; """
+ //days_add
+ qt_sql """ select days_add(test_time,1) result from ${tableName}; """
+ //hours_add
+ qt_sql """ select hours_add(test_time,1) result from ${tableName}; """
+ //minutes_add
+ qt_sql """ select minutes_add(test_time,1) result from ${tableName}; """
+ //seconds_add
+ qt_sql """ select seconds_add(test_time,1) result from ${tableName}; """
+
+ //years_sub
+ qt_sql """ select years_sub(test_time,1) result from ${tableName}; """
+ //months_sub
+ qt_sql """ select months_sub(test_time,1) result from ${tableName}; """
+ //weeks_sub
+ qt_sql """ select weeks_sub(test_time,1) result from ${tableName}; """
+ //days_sub
+ qt_sql """ select days_sub(test_time,1) result from ${tableName}; """
+ //hours_sub
+ qt_sql """ select hours_sub(test_time,1) result from ${tableName}; """
+ //minutes_sub
+ qt_sql """ select minutes_sub(test_time,1) result from ${tableName}; """
+ //seconds_sub
+ qt_sql """ select seconds_sub(test_time,1) result from ${tableName}; """
+
+ qt_sql """ select date_add(NULL, INTERVAL 1 month); """
+ qt_sql """ select date_add(NULL, INTERVAL 1 day); """
+
+ //years_add
+ qt_sql """ select years_add(test_time1,1) result from ${tableName}; """
+ //months_add
+ qt_sql """ select months_add(test_time1,1) result from ${tableName}; """
+ //weeks_add
+ qt_sql """ select weeks_add(test_time1,1) result from ${tableName}; """
+ //days_add
+ qt_sql """ select days_add(test_time1,1) result from ${tableName}; """
+ //hours_add
+ qt_sql """ select hours_add(test_time1,1) result from ${tableName}; """
+ //minutes_add
+ qt_sql """ select minutes_add(test_time1,1) result from ${tableName}; """
+ //seconds_add
+ qt_sql """ select seconds_add(test_time1,1) result from ${tableName}; """
+
+ //years_sub
+ qt_sql """ select years_sub(test_time1,1) result from ${tableName}; """
+ //months_sub
+ qt_sql """ select months_sub(test_time1,1) result from ${tableName}; """
+ //weeks_sub
+ qt_sql """ select weeks_sub(test_time1,1) result from ${tableName}; """
+ //days_sub
+ qt_sql """ select days_sub(test_time1,1) result from ${tableName}; """
+ //hours_sub
+ qt_sql """ select hours_sub(test_time1,1) result from ${tableName}; """
+ //minutes_sub
+ qt_sql """ select minutes_sub(test_time1,1) result from ${tableName}; """
+ //seconds_sub
+ qt_sql """ select seconds_sub(test_time1,1) result from ${tableName}; """
+
+ //years_add
+ qt_sql """ select years_add(test_time2,1) result from ${tableName}; """
+ //months_add
+ qt_sql """ select months_add(test_time2,1) result from ${tableName}; """
+ //weeks_add
+ qt_sql """ select weeks_add(test_time2,1) result from ${tableName}; """
+ //days_add
+ qt_sql """ select days_add(test_time2,1) result from ${tableName}; """
+ //hours_add
+ qt_sql """ select hours_add(test_time2,1) result from ${tableName}; """
+ //minutes_add
+ qt_sql """ select minutes_add(test_time2,1) result from ${tableName}; """
+ //seconds_add
+ qt_sql """ select seconds_add(test_time2,1) result from ${tableName}; """
+
+ //years_sub
+ qt_sql """ select years_sub(test_time2,1) result from ${tableName}; """
+ //months_sub
+ qt_sql """ select months_sub(test_time2,1) result from ${tableName}; """
+ //weeks_sub
+ qt_sql """ select weeks_sub(test_time2,1) result from ${tableName}; """
+ //days_sub
+ qt_sql """ select days_sub(test_time2,1) result from ${tableName}; """
+ //hours_sub
+ qt_sql """ select hours_sub(test_time2,1) result from ${tableName}; """
+ //minutes_sub
+ qt_sql """ select minutes_sub(test_time2,1) result from ${tableName}; """
+ //seconds_sub
+ qt_sql """ select seconds_sub(test_time2,1) result from ${tableName}; """
+
+ // test last_day for vec
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ birth date,
+ birth1 datev2,
+ birth2 datetime,
+ birth3 datetimev2)
+ UNIQUE KEY(birth, birth1, birth2, birth3)
+ DISTRIBUTED BY HASH (birth) BUCKETS 1
+ PROPERTIES( "replication_allocation" = "tag.location.default: 1");
+ """
+ sql """
+ insert into ${tableName} values
+ ('2022-01-01', '2022-01-01', '2022-01-01 00:00:00', '2022-01-01 00:00:00'),
+ ('2000-02-01', '2000-02-01', '2000-02-01 00:00:00', '2000-02-01 00:00:00.123'),
+ ('2022-02-29', '2022-02-29', '2022-02-29 00:00:00', '2022-02-29 00:00:00'),
+ ('2022-02-28', '2022-02-28', '2022-02-28T23:59:59', '2022-02-28T23:59:59');"""
+ qt_sql """
+ select last_day(birth), last_day(birth1),
+ last_day(birth2), last_day(birth3)
+ from ${tableName};
+ """
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ birth date,
+ birth1 datetime)
+ UNIQUE KEY(birth, birth1)
+ DISTRIBUTED BY HASH (birth) BUCKETS 1
+ PROPERTIES( "replication_allocation" = "tag.location.default: 1");
+ """
+ sql """
+ insert into ${tableName} values
+ ('2022-01-01', '2022-01-01 00:00:00'),
+ ('2000-02-01', '2000-02-01 00:00:00'),
+ ('2022-02-29', '2022-02-29 00:00:00'),
+ ('2022-02-28', '2022-02-28 23:59:59');"""
+ qt_sql """
+ select last_day(birth), last_day(birth1) from ${tableName};
+ """
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+
+ // test to_monday
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ birth date,
+ birth1 datev2,
+ birth2 datetime,
+ birth3 datetimev2)
+ UNIQUE KEY(birth, birth1, birth2, birth3)
+ DISTRIBUTED BY HASH (birth) BUCKETS 1
+ PROPERTIES( "replication_allocation" = "tag.location.default: 1");
+ """
+ sql """
+ insert into ${tableName} values
+ ('2022-01-01', '2022-01-01', '2022-01-01 00:00:00', '2022-01-01 00:00:00'),
+ ('2000-02-01', '2000-02-01', '2000-02-01 00:00:00', '2000-02-01 00:00:00.123'),
+ ('2022-02-29', '2022-02-29', '2022-02-29 00:00:00', '2022-02-29 00:00:00'),
+ ('2022-02-28', '2022-02-28', '2022-02-28 23:59:59', '2022-02-28 23:59:59'),
+ ('1970-01-02', '1970-01-02', '1970-01-02 01:02:03', '1970-01-02 02:03:04');"""
+ qt_sql """
+ select to_monday(birth), to_monday(birth1),
+ to_monday(birth2), to_monday(birth3)
+ from ${tableName};
+ """
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org