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 a97f582b93 [fix](nereids) use DAYS as default unit for DATE_ADD and
DATE_SUB function (#15559)
a97f582b93 is described below
commit a97f582b93523ab5889320e13d6f2fc8e41ff37a
Author: starocean999 <[email protected]>
AuthorDate: Wed Jan 4 01:55:15 2023 +0800
[fix](nereids) use DAYS as default unit for DATE_ADD and DATE_SUB function
(#15559)
---
.../doris/nereids/parser/LogicalPlanBuilder.java | 14 +-
.../test_conditional_function.out | 201 +++++++++++++++++++++
.../test_conditional_function.groovy | 57 ++++++
3 files changed, 262 insertions(+), 10 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
index f69d1a86ce..061f9ada40 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
@@ -716,11 +716,8 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
Expression timeStamp = (Expression) visit(ctx.timestamp);
Expression amount = (Expression) visit(ctx.unitsAmount);
if (ctx.unit == null) {
- if ("days_add".equalsIgnoreCase(ctx.name.getText())) {
- return new DaysAdd(timeStamp, amount);
- }
- throw new ParseException("Unsupported signature: " + ctx.name
- + " needs time unit (YEAR/MONTH/DAY/HOUR/MINUTE/SECOND)",
ctx);
+ //use "DAY" as unit by default
+ return new DaysAdd(timeStamp, amount);
}
if ("DAY".equalsIgnoreCase(ctx.unit.getText())) {
@@ -753,11 +750,8 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
amount = new Cast(amount, IntegerType.INSTANCE);
}
if (ctx.unit == null) {
- if ("days_sub".equalsIgnoreCase(ctx.name.getText())) {
- return new DaysSub(timeStamp, amount);
- }
- throw new ParseException("Unsupported signature: " + ctx.name
- + " needs time unit (YEAR/MONTH/DAY/HOUR/MINUTE/SECOND)",
ctx);
+ //use "DAY" as unit by default
+ return new DaysSub(timeStamp, amount);
}
if ("DAY".equalsIgnoreCase(ctx.unit.getText())) {
diff --git
a/regression-test/data/query_p0/sql_functions/conditional_functions/test_conditional_function.out
b/regression-test/data/query_p0/sql_functions/conditional_functions/test_conditional_function.out
index f8b54b0509..c657c48dcd 100644
---
a/regression-test/data/query_p0/sql_functions/conditional_functions/test_conditional_function.out
+++
b/regression-test/data/query_p0/sql_functions/conditional_functions/test_conditional_function.out
@@ -196,3 +196,204 @@ true
-- !if_false_else_nullable --
4
+
+-- !sql --
+\N user_id not exist
+\N user_id not exist
+\N user_id not exist
+\N user_id not exist
+1 user_id = 1
+2 user_id = 2
+3 user_id not exist
+4 user_id not exist
+
+-- !sql --
+\N user_id not exist
+\N user_id not exist
+\N user_id not exist
+\N user_id not exist
+1 user_id = 1
+2 user_id = 2
+3 user_id not exist
+4 user_id not exist
+
+-- !sql --
+\N false
+\N false
+\N false
+\N false
+1 true
+2 false
+3 false
+4 false
+
+-- !sql --
+1111
+
+-- !sql --
+1
+
+-- !sql --
+10
+
+-- !sql --
+1
+1
+1
+1
+1
+1
+1
+1
+
+-- !sql --
+1
+1
+1
+1
+1
+2
+3
+4
+
+-- !sql --
+\N
+\N
+\N
+\N
+1
+2
+3
+4
+
+-- !sql --
+\N
+\N
+\N
+\N
+1
+2
+3
+4
+
+-- !sql --
+\N
+
+-- !sql --
+1
+
+-- !sql --
+1
+1
+1
+1
+\N
+1
+1
+1
+
+-- !sql --
+\N
+\N
+\N
+\N
+\N
+2
+3
+4
+
+-- !sql --
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+
+-- !sql --
+\N
+\N
+\N
+\N
+1
+2
+3
+4
+
+-- !sql --
+\N
+
+-- !sql --
+1
+
+-- !sql --
+true
+true
+true
+true
+false
+false
+false
+false
+
+-- !sql --
+false
+false
+false
+false
+true
+true
+true
+true
+
+-- !sql --
+9999-07-31
+
+-- !sql --
+3
+
+-- !sql --
+9999-07
+
+-- !sql --
+\N
+
+-- !sql --
+9999-07
+
+-- !sql --
+1
+2
+3
+4
+99990101
+99990101
+99990101
+99990101
+
+-- !sql --
+1
+2
+3
+4
+999
+999
+999
+999
+
+-- !if_true_then_nullable --
+4
+
+-- !if_true_else_nullable --
+2
+
+-- !if_false_then_nullable --
+2
+
+-- !if_false_else_nullable --
+4
+
+-- !sql --
+9999-08-02 00:00
diff --git
a/regression-test/suites/query_p0/sql_functions/conditional_functions/test_conditional_function.groovy
b/regression-test/suites/query_p0/sql_functions/conditional_functions/test_conditional_function.groovy
index 1146cf4ae8..08863cea7a 100644
---
a/regression-test/suites/query_p0/sql_functions/conditional_functions/test_conditional_function.groovy
+++
b/regression-test/suites/query_p0/sql_functions/conditional_functions/test_conditional_function.groovy
@@ -92,5 +92,62 @@ suite("test_conditional_function") {
qt_if_false_then_nullable """select IF(false, DAYOFWEEK("2022-12-06
17:48:46"), 1) + 1;"""
qt_if_false_else_nullable """select IF(false, 1, DAYOFWEEK("2022-12-06
17:48:46")) + 1;"""
+ sql 'set enable_vectorized_engine=true;'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql 'set enable_nereids_planner=true'
+
+ qt_sql "select user_id, case user_id when 1 then 'user_id = 1' when 2 then
'user_id = 2' else 'user_id not exist' end test_case from ${tbName} order by
user_id;"
+ qt_sql "select user_id, case when user_id = 1 then 'user_id = 1' when
user_id = 2 then 'user_id = 2' else 'user_id not exist' end test_case from
${tbName} order by user_id;"
+
+ qt_sql "select user_id, if(user_id = 1, \"true\", \"false\") test_if from
${tbName} order by user_id;"
+
+ qt_sql "select coalesce(NULL, '1111', '0000');"
+
+ qt_sql "select ifnull(1,0);"
+ qt_sql "select ifnull(null,10);"
+ qt_sql "select ifnull(1,user_id) from ${tbName} order by user_id;"
+ qt_sql "select ifnull(user_id,1) from ${tbName} order by user_id;"
+ qt_sql "select ifnull(null,user_id) from ${tbName} order by user_id;"
+ qt_sql "select ifnull(user_id,null) from ${tbName} order by user_id;"
+
+ qt_sql "select nullif(1,1);"
+ qt_sql "select nullif(1,0);"
+ qt_sql "select nullif(1,user_id) from ${tbName} order by user_id;"
+ qt_sql "select nullif(user_id,1) from ${tbName} order by user_id;"
+ qt_sql "select nullif(null,user_id) from ${tbName} order by user_id;"
+ qt_sql "select nullif(user_id,null) from ${tbName} order by user_id;"
+
+
+ qt_sql "select nullif(1,1);"
+ qt_sql "select nullif(1,0);"
+
+
+ qt_sql "select is_null_pred(user_id) from ${tbName} order by user_id"
+ qt_sql "select is_not_null_pred(user_id) from ${tbName} order by user_id"
+
+ qt_sql """select if(date_format(CONCAT_WS('', '9999-07', '-26'), '%Y-%m')=
DATE_FORMAT( curdate(), '%Y-%m'),
+ curdate(),
+ DATE_FORMAT(DATE_SUB(month_ceil ( CONCAT_WS('', '9999-07',
'-26')), 1), '%Y-%m-%d'));"""
+
+ qt_sql "select ifnull(date_format(CONCAT_WS('', '9999-07', '-00'),
'%Y-%m'),3);"
+
+ qt_sql "select ifnull(date_format(CONCAT_WS('', '9999-07', '-01'),
'%Y-%m'),3);"
+
+ qt_sql "select ifnull(date_format(CONCAT_WS('', '9999-07', '-00'),
'%Y-%m'),date_format(CONCAT_WS('', '9999-07', '-00'), '%Y-%m'));"
+
+ qt_sql "select ifnull(date_format(CONCAT_WS('', '9999-07', '-00'),
'%Y-%m'),date_format(CONCAT_WS('', '9999-07', '-26'), '%Y-%m'));"
+
+ qt_sql "select ifnull( user_id, to_date('9999-01-01')) r from ${tbName}
order by r"
+
+ qt_sql "select ifnull( user_id, 999) r from ${tbName} order by r"
+
+ qt_if_true_then_nullable """select IF(true, DAYOFWEEK("2022-12-06
17:48:46"), 1) + 1;"""
+ qt_if_true_else_nullable """select IF(true, 1, DAYOFWEEK("2022-12-06
17:48:46")) + 1;"""
+
+ qt_if_false_then_nullable """select IF(false, DAYOFWEEK("2022-12-06
17:48:46"), 1) + 1;"""
+ qt_if_false_else_nullable """select IF(false, 1, DAYOFWEEK("2022-12-06
17:48:46")) + 1;"""
+
+ qt_sql "select date_add('9999-08-01 00:00:00',1);"
+
sql "DROP TABLE ${tbName};"
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
