This is an automated email from the ASF dual-hosted git repository.
yiguolei 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 fcfc76f4a9 [Bug](date function) `from_unixtime` return wrong result
(#11410)
fcfc76f4a9 is described below
commit fcfc76f4a9df605ebb45de07ac87c36df57fa3fd
Author: Gabriel <gabrielleeb...@gmail.com>
AuthorDate: Tue Aug 2 14:23:54 2022 +0800
[Bug](date function) `from_unixtime` return wrong result (#11410)
* [Bug](date function) `from_unixtime` return wrong result
---
.../doris/rewrite/RewriteFromUnixTimeRule.java | 4 +-
.../org/apache/doris/planner/QueryPlanTest.java | 51 ----------------------
.../datetime_functions/test_date_function.out | 51 ++++++++++++++++++++++
.../datetime_functions/test_date_function.groovy | 34 +++++++++++++++
4 files changed, 88 insertions(+), 52 deletions(-)
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 a40b33b2a1..c7a9e51c54 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
@@ -105,7 +105,9 @@ public class RewriteFromUnixTimeRule implements
ExprRewriteRule {
return expr;
}
// definition: from_unixtime(int, format)
- if (params.exprs().size() != 1 && params.exprs().size() != 2) {
+ // TODO: from_unixtime(col, format) needs to be processed carefully,
now only rewrite from_unixtime(col)
+ // to avoid wrong results.
+ if (params.exprs().size() != 1) {
return expr;
}
Expr paramSlot = params.exprs().get(0);
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 1d13186779..73a5f8e776 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
@@ -1662,57 +1662,6 @@ public class QueryPlanTest extends TestWithFeService {
String sql = "select * from test1 where from_unixtime(query_time) >
'2021-03-02 10:01:28'";
String explainString = getSQLPlanOrErrorMsg("EXPLAIN " + sql);
Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <=
253402271999, `query_time` > 1614650488"));
-
- //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 = getSQLPlanOrErrorMsg("EXPLAIN " + sql);
- Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <=
253402271999, `query_time` > 1614650488"));
- sql = "select * from test1 where from_unixtime(query_time, '%Y-%m-%d
%H:%i:%s') > '2021-03-02 10:01:28'";
- explainString = getSQLPlanOrErrorMsg("EXPLAIN " + sql);
- 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 = getSQLPlanOrErrorMsg("EXPLAIN " + sql);
- 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 = getSQLPlanOrErrorMsg("EXPLAIN " + sql);
- Assert.assertTrue(explainString.contains("PREDICATES: `query_time` <=
253402271999, `query_time` > 1614614400"));
-
- // format yyyyMMdd or %Y%m%d
- sql = "select * from test1 where from_unixtime(query_time, 'yyyyMMdd')
> '20210302'";
- explainString = getSQLPlanOrErrorMsg("EXPLAIN " + sql);
- 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 = getSQLPlanOrErrorMsg("EXPLAIN " + sql);
- 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'";
- explainString = getSQLPlanOrErrorMsg("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 = getSQLPlanOrErrorMsg("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 = getSQLPlanOrErrorMsg("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 = getSQLPlanOrErrorMsg("EXPLAIN " + sql);
- Assert.assertFalse(explainString.contains("PREDICATES: `query_time` <=
253402271999"));
- //format yyyy
- sql = "select * from test1 where from_unixtime(query_time, 'yyyy') >
'2021'";
- explainString = getSQLPlanOrErrorMsg("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 = getSQLPlanOrErrorMsg("EXPLAIN " + sql);
- Assert.assertFalse(explainString.contains("PREDICATES: `query_time` <=
253402271999"));
}
@Test
diff --git
a/regression-test/data/query/sql_functions/datetime_functions/test_date_function.out
b/regression-test/data/query/sql_functions/datetime_functions/test_date_function.out
index c02183d1c3..df4a4cd085 100644
---
a/regression-test/data/query/sql_functions/datetime_functions/test_date_function.out
+++
b/regression-test/data/query/sql_functions/datetime_functions/test_date_function.out
@@ -290,3 +290,54 @@ February
-- !sql --
200
+-- !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
+
diff --git
a/regression-test/suites/query/sql_functions/datetime_functions/test_date_function.groovy
b/regression-test/suites/query/sql_functions/datetime_functions/test_date_function.groovy
index eada2080eb..70e98134eb 100644
---
a/regression-test/suites/query/sql_functions/datetime_functions/test_date_function.groovy
+++
b/regression-test/suites/query/sql_functions/datetime_functions/test_date_function.groovy
@@ -253,4 +253,38 @@ suite("test_date_function", "query") {
qt_sql """ select count(*) from (select * from numbers("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; """
+
+ sql """ drop table ${tableName} """
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
