This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-doris.git
The following commit(s) were added to refs/heads/master by this push:
new 22a0011 [fix](planner) Convert format in RewriteFromUnixTimeRule
(#8235)
22a0011 is described below
commit 22a0011403b53287a44421afab606c980e205a57
Author: Lijia Liu <[email protected]>
AuthorDate: Sat Mar 5 15:26:37 2022 +0800
[fix](planner) Convert format in RewriteFromUnixTimeRule (#8235)
SQL to reproduce:
```
SELECT * FROM table WHERE where FROM_UNIXTIME(d_datekey,'%Y-%m-%d
%H:%i:%s') != '1970-08-20 00:11:43';
org.apache.doris.common.AnalysisException: errCode = 2, detailMessage =
Unexpected exception: Illegal pattern character 'i'
at org.apache.doris.qe.StmtExecutor.analyze(StmtExecutor.java:584)
~[palo-fe.jar:3.4.0]
at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:345)
~[palo-fe.jar:3.4.0]
at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:318)
~[palo-fe.jar:3.4.0]
at
org.apache.doris.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:221)
~[palo-fe.jar:3.4.0]
at
org.apache.doris.qe.ConnectProcessor.dispatch(ConnectProcessor.java:361)
~[palo-fe.jar:3.4.0]
at
org.apache.doris.qe.ConnectProcessor.processOnce(ConnectProcessor.java:562)
~[palo-fe.jar:3.4.0]
at
org.apache.doris.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:50)
~[palo-fe.jar:3.4.0]
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
[?:?]
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
[?:?]
at java.lang.Thread.run(Thread.java:835) [?:?]
```
Describe the overview of changes.
Just support:
yyyy-MM-dd HH:mm:ss
yyyy-MM-dd
yyyyMMdd
---
.../date-time-functions/date_format.md | 8 +++
.../date-time-functions/from_unixtime.md | 11 +---
.../date-time-functions/date_format.md | 8 +++
.../date-time-functions/from_unixtime.md | 11 +---
.../doris/rewrite/RewriteFromUnixTimeRule.java | 69 ++++++++++++++++++----
.../org/apache/doris/planner/QueryPlanTest.java | 54 ++++++++++++-----
6 files changed, 113 insertions(+), 48 deletions(-)
diff --git
a/docs/en/sql-reference/sql-functions/date-time-functions/date_format.md
b/docs/en/sql-reference/sql-functions/date-time-functions/date_format.md
index 29a618d..8bede2c 100644
--- a/docs/en/sql-reference/sql-functions/date-time-functions/date_format.md
+++ b/docs/en/sql-reference/sql-functions/date-time-functions/date_format.md
@@ -102,6 +102,14 @@ The formats available are:
%% | Represent %
+Also support 3 formats:
+
+yyyyMMdd
+
+yyyy-MM-dd
+
+yyyy-MM-dd HH:mm:ss
+
## example
```
diff --git
a/docs/en/sql-reference/sql-functions/date-time-functions/from_unixtime.md
b/docs/en/sql-reference/sql-functions/date-time-functions/from_unixtime.md
index 8f444ed..bb080a9 100644
--- a/docs/en/sql-reference/sql-functions/date-time-functions/from_unixtime.md
+++ b/docs/en/sql-reference/sql-functions/date-time-functions/from_unixtime.md
@@ -34,16 +34,7 @@ Convert the UNIX timestamp to the corresponding time format
of bits, and the for
Input is an integer and return is a string type
-Currently, `string_format` supports following formats:
-
- %Y: Year. eg. 2014, 1900
- %m: Month. eg. 12, 09
- %d: Day. eg. 11, 01
- %H: Hour. eg. 23, 01, 12
- %i: Minute. eg. 05, 11
- %s: Second. eg. 59, 01
-
-Default is `%Y-%m-%d %H:%i:%s`
+Support `date_format`'s format, and default is `%Y-%m-%d %H:%i:%s`
Other `string_format` is illegal and will returns NULL.
diff --git
a/docs/zh-CN/sql-reference/sql-functions/date-time-functions/date_format.md
b/docs/zh-CN/sql-reference/sql-functions/date-time-functions/date_format.md
index b15929e..e2621d4 100644
--- a/docs/zh-CN/sql-reference/sql-functions/date-time-functions/date_format.md
+++ b/docs/zh-CN/sql-reference/sql-functions/date-time-functions/date_format.md
@@ -102,6 +102,14 @@ date 参数是合法的日期。format 规定日期/时间的输出格式。
%% | 用于表示 %
+还可以使用三种特殊格式：
+
+yyyyMMdd
+
+yyyy-MM-dd
+
+yyyy-MM-dd HH:mm:ss
+
## example
```
diff --git
a/docs/zh-CN/sql-reference/sql-functions/date-time-functions/from_unixtime.md
b/docs/zh-CN/sql-reference/sql-functions/date-time-functions/from_unixtime.md
index 8f4dbd9..29a37b7 100644
---
a/docs/zh-CN/sql-reference/sql-functions/date-time-functions/from_unixtime.md
+++
b/docs/zh-CN/sql-reference/sql-functions/date-time-functions/from_unixtime.md
@@ -33,19 +33,10 @@ under the License.
将 unix 时间戳转化为对应的 time 格式，返回的格式由 `string_format` 指定
-默认为 yyyy-MM-dd HH:mm:ss ,也支持date_format中的format格式
+支持date_format中的format格式，默认为 %Y-%m-%d %H:%i:%s
传入的是整形，返回的是字符串类型
-目前 `string_format` 支持格式：
-
- %Y：年。例：2014，1900
- %m：月。例：12，09
- %d：日。例：11，01
- %H：时。例：23，01，12
- %i：分。例：05，11
- %s：秒。例：59，01
-
其余 `string_format` 格式是非法的，返回NULL
如果给定的时间戳小于 0 或大于 253402271999，则返回 NULL。即时间戳范围是：
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/rewrite/RewriteFromUnixTimeRule.java
b/fe/fe-core/src/main/java/org/apache/doris/rewrite/RewriteFromUnixTimeRule.java
index 3179516..0377fde 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/rewrite/RewriteFromUnixTimeRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/rewrite/RewriteFromUnixTimeRule.java
@@ -29,9 +29,14 @@ import org.apache.doris.analysis.SlotRef;
import org.apache.doris.catalog.Type;
import org.apache.doris.common.AnalysisException;
-import java.text.ParseException;
-import java.text.SimpleDateFormat;
-import java.util.Date;
+import com.google.common.collect.ImmutableMap;
+
+import java.time.LocalDate;
+import java.time.LocalDateTime;
+import java.time.OffsetDateTime;
+import java.time.format.DateTimeFormatter;
+import java.time.format.DateTimeParseException;
+import java.util.function.Function;
/*
* rewrite sql: select * from table where from_unixtime(query_time) >
'2021-03-02 12:12:23'
@@ -41,7 +46,42 @@ import java.util.Date;
* this rewrite can improve the query performance, because from_unixtime is
cpu-exhausted
* */
public class RewriteFromUnixTimeRule implements ExprRewriteRule {
+
public static RewriteFromUnixTimeRule INSTANCE = new
RewriteFromUnixTimeRule();
+ // In BE, will convert format in timestamp function.
+ // yyyyMMdd -> %Y%m%d
+ // yyyy-MM-dd -> %Y-%m-%d
+ // yyyy-MM-dd HH:mm:ss -> %Y-%m-%d %H:%i:%s
+ // Here, we just support these three format.
+ private final ImmutableMap<String, String> beSupportFormatMap;
+ private final ImmutableMap<String, Function<String, Long>>
parseMillisFunctionMap;
+ public RewriteFromUnixTimeRule() {
+ beSupportFormatMap = ImmutableMap.<String, String>builder()
+ .put("%Y%m%d", "yyyyMMdd")
+ .put("%Y-%m-%d", "yyyy-MM-dd")
+ .put("%Y-%m-%d %H:%i:%s", "yyyy-MM-dd HH:mm:ss")
+ .build();
+ parseMillisFunctionMap = ImmutableMap.<String, Function<String,
Long>>builder()
+ .put("yyyyMMdd", (str) -> LocalDate.parse(str,
DateTimeFormatter.ofPattern("yyyyMMdd")).atStartOfDay().toEpochSecond(OffsetDateTime.now().getOffset()))
+ .put("yyyy-MM-dd", (str) -> LocalDate.parse(str,
DateTimeFormatter.ofPattern("yyyy-MM-dd"))
+
.atStartOfDay().toEpochSecond(OffsetDateTime.now().getOffset()))
+ .put("yyyy-MM-dd HH:mm:ss", (str) -> LocalDateTime.parse(str,
DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
+ .toEpochSecond(OffsetDateTime.now().getOffset()))
+ .build();
+ }
+
+ Function<String, Long> getParseSecondsFunction(String formatStr) {
+ final String patternStr;
+ if (beSupportFormatMap.containsValue(formatStr)) {
+ patternStr = formatStr;
+ } else if (beSupportFormatMap.containsKey(formatStr)) {
+ patternStr = beSupportFormatMap.get(formatStr);
+ } else {
+ return null;
+ }
+
+ return parseMillisFunctionMap.get(patternStr);
+ }
@Override
public Expr apply(Expr expr, Analyzer analyzer, ExprRewriter.ClauseType
clauseType) throws AnalysisException {
@@ -78,30 +118,35 @@ public class RewriteFromUnixTimeRule implements
ExprRewriteRule {
return expr;
}
LiteralExpr le = (LiteralExpr) right;
- SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
- // default format is "yyyy-MM-dd HH:mm:ss"
+ final String formatStr;
+ // default format is "yyyy-MM-dd HH:mm:ss" (%Y-%m-%d %H:%i:%s)
if (params.exprs().size() == 1) {
- format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ formatStr = "yyyy-MM-dd HH:mm:ss";
} else {
LiteralExpr fm = (LiteralExpr) params.exprs().get(1);
- format = new SimpleDateFormat(fm.getStringValue());
+ formatStr = fm.getStringValue();
}
+ Function<String, Long> parseSecondsFunction =
getParseSecondsFunction(formatStr);
+ if (null == parseSecondsFunction) {
+ return expr;
+ }
+
try {
- Date date = format.parse(le.getStringValue());
+ Expr literalExpr =
LiteralExpr.create(String.valueOf(parseSecondsFunction.apply(le.getStringValue())),
Type.BIGINT);
// it must adds low bound 0, because when a field contains
negative data like -100, it will be queried as a result
if (bp.getOp() == BinaryPredicate.Operator.LT || bp.getOp() ==
BinaryPredicate.Operator.LE) {
- BinaryPredicate r = new BinaryPredicate(bp.getOp(), sr,
LiteralExpr.create(String.valueOf(date.getTime() / 1000), Type.BIGINT));
+ BinaryPredicate r = new BinaryPredicate(bp.getOp(), sr,
literalExpr);
BinaryPredicate l = new
BinaryPredicate(BinaryPredicate.Operator.GE, sr, LiteralExpr.create("0",
Type.BIGINT));
return new CompoundPredicate(CompoundPredicate.Operator.AND,
r, l);
} else if (bp.getOp() == BinaryPredicate.Operator.GT || bp.getOp()
== BinaryPredicate.Operator.GE) {
// also it must adds upper bound 253402271999, because
from_unixtime support time range is [1970-01-01 00:00:00 ~ 9999-12-31 23:59:59]
- BinaryPredicate l = new BinaryPredicate(bp.getOp(), sr,
LiteralExpr.create(String.valueOf(date.getTime() / 1000), Type.BIGINT));
+ BinaryPredicate l = new BinaryPredicate(bp.getOp(), sr,
literalExpr);
BinaryPredicate r = new
BinaryPredicate(BinaryPredicate.Operator.LE, sr,
LiteralExpr.create("253402271999", Type.BIGINT));
return new CompoundPredicate(CompoundPredicate.Operator.AND,
r, l);
} else {
- return new BinaryPredicate(bp.getOp(), sr,
LiteralExpr.create(String.valueOf(date.getTime() / 1000), Type.BIGINT));
+ return new BinaryPredicate(bp.getOp(), sr, literalExpr);
}
- } catch (ParseException e) {
+ } catch (DateTimeParseException e) {
return expr;
}
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/planner/QueryPlanTest.java
b/fe/fe-core/src/test/java/org/apache/doris/planner/QueryPlanTest.java
index 7c2466a..bfaec00 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/planner/QueryPlanTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/planner/QueryPlanTest.java
@@ -1658,35 +1658,57 @@ public class QueryPlanTest {
String sql = "select * from test1 where from_unixtime(query_time) >
'2021-03-02 10:01:28'";
String explainString =
UtFrameUtils.getSQLPlanOrErrorMsg(connectContext, "EXPLAIN " + sql);
Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <=
253402271999, `query_time` > 1614650488"));
- //format yyyy-MM-dd HH:mm:ss
+
+ //format yyyy-MM-dd HH:mm:ss or %Y-%m-%d %H:%i:%s
sql = "select * from test1 where from_unixtime(query_time, 'yyyy-MM-dd
HH:mm:ss') > '2021-03-02 10:01:28'";
explainString = UtFrameUtils.getSQLPlanOrErrorMsg(connectContext,
"EXPLAIN " + sql);
Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <=
253402271999, `query_time` > 1614650488"));
- //format yyyy-MM-dd HH:mm
- sql = "select * from test1 where from_unixtime(query_time, 'yyyy-MM-dd
HH:mm') > '2021-03-02 10:01:28'";
+ sql = "select * from test1 where from_unixtime(query_time, '%Y-%m-%d
%H:%i:%s') > '2021-03-02 10:01:28'";
explainString = UtFrameUtils.getSQLPlanOrErrorMsg(connectContext,
"EXPLAIN " + sql);
- Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <=
253402271999, `query_time` > 1614650460"));
- //format yyyy-MM-dd HH
- sql = "select * from test1 where from_unixtime(query_time, 'yyyy-MM-dd
HH') > '2021-03-02 10:01:28'";
+ Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <=
253402271999, `query_time` > 1614650488"));
+
+ //format yyyy-MM-dd or %Y-%m-%d
+ sql = "select * from test1 where from_unixtime(query_time,
'yyyy-MM-dd') > '2021-03-02'";
explainString = UtFrameUtils.getSQLPlanOrErrorMsg(connectContext,
"EXPLAIN " + sql);
- Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <=
253402271999, `query_time` > 1614650400"));
- //format yyyy-MM-dd
- sql = "select * from test1 where from_unixtime(query_time,
'yyyy-MM-dd') > '2021-03-02 10:01:28'";
+ Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <=
253402271999, `query_time` > 1614614400"));
+ sql = "select * from test1 where from_unixtime(query_time, '%Y-%m-%d')
> '2021-03-02'";
explainString = UtFrameUtils.getSQLPlanOrErrorMsg(connectContext,
"EXPLAIN " + sql);
Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <=
253402271999, `query_time` > 1614614400"));
- //format yyyy-MM
- sql = "select * from test1 where from_unixtime(query_time, 'yyyy-MM')
> '2021-03-02 10:01:28'";
+
+ // format yyyyMMdd or %Y%m%d
+ sql = "select * from test1 where from_unixtime(query_time, 'yyyyMMdd')
> '20210302'";
explainString = UtFrameUtils.getSQLPlanOrErrorMsg(connectContext,
"EXPLAIN " + sql);
- Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <=
253402271999, `query_time` > 1614528000"));
- //format yyyy
- sql = "select * from test1 where from_unixtime(query_time, 'yyyy') >
'2021-03-02 10:01:28'";
+ Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <=
253402271999, `query_time` > 1614614400"));
+ sql = "select * from test1 where from_unixtime(query_time, '%Y%m%d') >
'20210302'";
explainString = UtFrameUtils.getSQLPlanOrErrorMsg(connectContext,
"EXPLAIN " + sql);
- Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <=
253402271999, `query_time` > 1609430400"));
+ Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <=
253402271999, `query_time` > 1614614400"));
//format less than
- sql = "select * from test1 where from_unixtime(query_time,
'yyyy-MM-dd') < '2021-03-02 10:01:28'";
+ sql = "select * from test1 where from_unixtime(query_time,
'yyyy-MM-dd') < '2021-03-02'";
explainString = UtFrameUtils.getSQLPlanOrErrorMsg(connectContext,
"EXPLAIN " + sql);
Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <
1614614400, `query_time` >= 0"));
+
+ // Do not support other format
+ //format yyyy-MM-dd HH:mm
+ sql = "select * from test1 where from_unixtime(query_time, 'yyyy-MM-dd
HH:mm') > '2021-03-02 10:01'";
+ explainString = UtFrameUtils.getSQLPlanOrErrorMsg(connectContext,
"EXPLAIN " + sql);
+ Assert.assertFalse(explainString.contains("PREDICATES: `query_time` <=
253402271999"));
+ //format yyyy-MM-dd HH
+ sql = "select * from test1 where from_unixtime(query_time, 'yyyy-MM-dd
HH') > '2021-03-02 10'";
+ explainString = UtFrameUtils.getSQLPlanOrErrorMsg(connectContext,
"EXPLAIN " + sql);
+ Assert.assertFalse(explainString.contains("PREDICATES: `query_time` <=
253402271999"));
+ //format yyyy-MM
+ sql = "select * from test1 where from_unixtime(query_time, 'yyyy-MM')
> '2021-03'";
+ explainString = UtFrameUtils.getSQLPlanOrErrorMsg(connectContext,
"EXPLAIN " + sql);
+ Assert.assertFalse(explainString.contains("PREDICATES: `query_time` <=
253402271999"));
+ //format yyyy
+ sql = "select * from test1 where from_unixtime(query_time, 'yyyy') >
'2021'";
+ explainString = UtFrameUtils.getSQLPlanOrErrorMsg(connectContext,
"EXPLAIN " + sql);
+ Assert.assertFalse(explainString.contains("PREDICATES: `query_time` <=
253402271999"));
+ // parse error
+ sql = "select * from test1 where from_unixtime(query_time, 'yyyyMMdd')
> '2021-03-02 10:01:28'";
+ explainString = UtFrameUtils.getSQLPlanOrErrorMsg(connectContext,
"EXPLAIN " + sql);
+ Assert.assertFalse(explainString.contains("PREDICATES: `query_time` <=
253402271999"));
}
@Test
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
