You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by GitBox <gi...@apache.org> on 2020/04/25 07:30:12 UTC
[GitHub] [incubator-doris] wangbo opened a new pull request #3396: (#3395)calculate 'case when expr' when possible
wangbo opened a new pull request #3396:
URL: https://github.com/apache/incubator-doris/pull/3396
(#3395)
calculate 'case when expr' when possible
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wangbo commented on a change in pull request #3396: (#3395)calculate 'case when expr' when possible
Posted by GitBox <gi...@apache.org>.
wangbo commented on a change in pull request #3396:
URL: https://github.com/apache/incubator-doris/pull/3396#discussion_r417717099
##########
File path: fe/src/test/java/org/apache/doris/planner/QueryPlanTest.java
##########
@@ -504,4 +504,106 @@ public void testDateTypeEquality() throws Exception {
Catalog.getCurrentCatalog().getLoadManager().createLoadJobV1FromStmt(loadStmt, EtlJobType.HADOOP,
System.currentTimeMillis());
}
+
+ private SelectStmt getAnalyzedAndRewritedStmt(String sql) throws Exception {
+ SelectStmt selectStmt =
+ (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql, connectContext);
+ selectStmt = (SelectStmt) UtFrameUtils.rewriteStmt(selectStmt, connectContext);
+ selectStmt = (SelectStmt) UtFrameUtils.reAnalyze(selectStmt, connectContext);
+ return selectStmt;
+ }
+
+ @Test
+ public void testConvertCaseWhenToConstant() throws Exception {
Review comment:
👌
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wangbo commented on a change in pull request #3396: (#3395)calculate 'case when expr' when possible
Posted by GitBox <gi...@apache.org>.
wangbo commented on a change in pull request #3396:
URL: https://github.com/apache/incubator-doris/pull/3396#discussion_r417717099
##########
File path: fe/src/test/java/org/apache/doris/planner/QueryPlanTest.java
##########
@@ -504,4 +504,106 @@ public void testDateTypeEquality() throws Exception {
Catalog.getCurrentCatalog().getLoadManager().createLoadJobV1FromStmt(loadStmt, EtlJobType.HADOOP,
System.currentTimeMillis());
}
+
+ private SelectStmt getAnalyzedAndRewritedStmt(String sql) throws Exception {
+ SelectStmt selectStmt =
+ (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql, connectContext);
+ selectStmt = (SelectStmt) UtFrameUtils.rewriteStmt(selectStmt, connectContext);
+ selectStmt = (SelectStmt) UtFrameUtils.reAnalyze(selectStmt, connectContext);
+ return selectStmt;
+ }
+
+ @Test
+ public void testConvertCaseWhenToConstant() throws Exception {
Review comment:
👌
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wangbo commented on a change in pull request #3396: (#3395)calculate 'case when expr' when possible
Posted by GitBox <gi...@apache.org>.
wangbo commented on a change in pull request #3396:
URL: https://github.com/apache/incubator-doris/pull/3396#discussion_r417741395
##########
File path: fe/src/test/java/org/apache/doris/planner/QueryPlanTest.java
##########
@@ -504,4 +504,106 @@ public void testDateTypeEquality() throws Exception {
Catalog.getCurrentCatalog().getLoadManager().createLoadJobV1FromStmt(loadStmt, EtlJobType.HADOOP,
System.currentTimeMillis());
}
+
+ private SelectStmt getAnalyzedAndRewritedStmt(String sql) throws Exception {
Review comment:
```getSQLPlanOrErrorMsg ``` is unable to meet demand.What we want is to check whether the plan changes as our wish,so we need a tools not only retwrite the plan but alse contains the customized check logic.It's obviously that plain sql is directly,but not a common general method,because the print format may change.
Maybe list a todo here?
Anyway,I think the result of ```getSQLPlanOrErrorMsg ``` can't meet demand
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wangbo commented on a change in pull request #3396: (#3395)calculate 'case when expr' when possible
Posted by GitBox <gi...@apache.org>.
wangbo commented on a change in pull request #3396:
URL: https://github.com/apache/incubator-doris/pull/3396#discussion_r417717067
##########
File path: fe/src/test/java/org/apache/doris/planner/QueryPlanTest.java
##########
@@ -504,4 +504,106 @@ public void testDateTypeEquality() throws Exception {
Catalog.getCurrentCatalog().getLoadManager().createLoadJobV1FromStmt(loadStmt, EtlJobType.HADOOP,
System.currentTimeMillis());
}
+
+ private SelectStmt getAnalyzedAndRewritedStmt(String sql) throws Exception {
+ SelectStmt selectStmt =
+ (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql, connectContext);
+ selectStmt = (SelectStmt) UtFrameUtils.rewriteStmt(selectStmt, connectContext);
+ selectStmt = (SelectStmt) UtFrameUtils.reAnalyze(selectStmt, connectContext);
+ return selectStmt;
+ }
+
+ @Test
+ public void testConvertCaseWhenToConstant() throws Exception {
+ // basic test
+ String caseWhenSql = "select "
+ + "case when date_format(now(),'%H%i') < 123 then 1 else 0 end as col "
+ + "from test.test1 "
+ + "where time = case when date_format(now(),'%H%i') < 123 then date_format(date_sub(now(),2),'%Y%m%d') else date_format(date_sub(now(),1),'%Y%m%d') end";
+ SelectStmt selectStmt = getAnalyzedAndRewritedStmt(caseWhenSql);
+ Assert.assertTrue(!selectStmt.toSql().contains("CASE WHEN") && !selectStmt.toSql().contains("case when"));
Review comment:
👌
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wangbo commented on a change in pull request #3396: (#3395)calculate 'case when expr' when possible
Posted by GitBox <gi...@apache.org>.
wangbo commented on a change in pull request #3396:
URL: https://github.com/apache/incubator-doris/pull/3396#discussion_r417717171
##########
File path: fe/src/main/java/org/apache/doris/analysis/CaseExpr.java
##########
@@ -251,4 +257,72 @@ public void analyzeImpl(Analyzer analyzer) throws AnalysisException {
}
return exprs;
}
+
+ // this method just compare literal value and not completely consistent with be,for two cases
+ // 1 not deal float
+ // 2 just compare literal value with same type. for a example sql 'select case when 123 then '1' else '2' end as col'
+ // for be will return '1', because be only regard 0 as false
+ // but for current LiteralExpr.compareLiteral, `123`' won't be regard as true
+ // the case which two values has different type left to be
+ public static Expr computeCaseExpr(CaseExpr expr) {
+ LiteralExpr caseExpr;
+ int startIndex = 0;
+ int size = expr.getChildren().size() - 1;
+ if (expr.hasCaseExpr()) {
+ // just deal literal here
+ // and avoid `float compute` in java,float should be dealt in be
+ Expr caseChildExpr = expr.getChild(0);
+ if (!caseChildExpr.isLiteral()
+ || caseChildExpr instanceof DecimalLiteral || caseChildExpr instanceof FloatLiteral) {
+ return expr;
+ }
+ caseExpr = (LiteralExpr) expr.getChild(0);
+ startIndex++;
+ size--;
+ } else {
+ caseExpr = new BoolLiteral(true);
+ }
+
+ if (expr.hasElseExpr) {
+ size--;
+ }
+
+ //pre return when the `when expr` can't be converted to constants
Review comment:
👌
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wangbo commented on a change in pull request #3396: (#3395)calculate 'case when expr' when possible
Posted by GitBox <gi...@apache.org>.
wangbo commented on a change in pull request #3396:
URL: https://github.com/apache/incubator-doris/pull/3396#discussion_r417730477
##########
File path: fe/src/main/java/org/apache/doris/analysis/CaseExpr.java
##########
@@ -251,4 +257,72 @@ public void analyzeImpl(Analyzer analyzer) throws AnalysisException {
}
return exprs;
}
+
+ // this method just compare literal value and not completely consistent with be,for two cases
+ // 1 not deal float
+ // 2 just compare literal value with same type. for a example sql 'select case when 123 then '1' else '2' end as col'
+ // for be will return '1', because be only regard 0 as false
+ // but for current LiteralExpr.compareLiteral, `123`' won't be regard as true
+ // the case which two values has different type left to be
+ public static Expr computeCaseExpr(CaseExpr expr) {
+ LiteralExpr caseExpr;
+ int startIndex = 0;
Review comment:
yes, be logic is right.I'll fix it
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wangbo commented on pull request #3396: (#3395)calculate 'case when expr' when possible
Posted by GitBox <gi...@apache.org>.
wangbo commented on pull request #3396:
URL: https://github.com/apache/incubator-doris/pull/3396#issuecomment-619387770
@morningman ut added
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] morningman commented on a change in pull request #3396: (#3395)calculate 'case when expr' when possible
Posted by GitBox <gi...@apache.org>.
morningman commented on a change in pull request #3396:
URL: https://github.com/apache/incubator-doris/pull/3396#discussion_r415792585
##########
File path: fe/src/main/java/org/apache/doris/rewrite/FoldConstantsRule.java
##########
@@ -48,6 +50,12 @@
@Override
public Expr apply(Expr expr, Analyzer analyzer) throws AnalysisException {
+ // evaluate case when expr
+ if (expr instanceof CaseExpr && expr.getChild(0) instanceof BoolLiteral) {
Review comment:
This is not right, the CaseExpr has multi child
1. optional case expr
2. optional list<when expr, then expr>
3. optional else expr.
The following SQL will return wrong result after applying your rewrite rule:
```
select case when false then 2 when true then 3 else 0 end;
```
Expect: 3, return 1.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] kangkaisen commented on a change in pull request #3396: (#3395)calculate 'case when expr' when possible
Posted by GitBox <gi...@apache.org>.
kangkaisen commented on a change in pull request #3396:
URL: https://github.com/apache/incubator-doris/pull/3396#discussion_r418495889
##########
File path: fe/src/test/java/org/apache/doris/planner/QueryPlanTest.java
##########
@@ -504,4 +504,106 @@ public void testDateTypeEquality() throws Exception {
Catalog.getCurrentCatalog().getLoadManager().createLoadJobV1FromStmt(loadStmt, EtlJobType.HADOOP,
System.currentTimeMillis());
}
+
+ private SelectStmt getAnalyzedAndRewritedStmt(String sql) throws Exception {
Review comment:
I don't think so.
Directly check final FE sql plan is more accurate:
1. FE send BE is the sql plan
2. Your work do indeed is changing partial plan to constant.
> What we want is to check whether the plan changes as our wish
So you should use `getSQLPlanOrErrorMsg `
> I think the result of getSQLPlanOrErrorMsg can't meet demand
Why couldn't meet you demand? I think which could.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wangbo commented on pull request #3396: (#3395)calculate 'case when expr' when possible
Posted by GitBox <gi...@apache.org>.
wangbo commented on pull request #3396:
URL: https://github.com/apache/incubator-doris/pull/3396#issuecomment-624633156
resolve conflict
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] kangkaisen commented on a change in pull request #3396: (#3395)calculate 'case when expr' when possible
Posted by GitBox <gi...@apache.org>.
kangkaisen commented on a change in pull request #3396:
URL: https://github.com/apache/incubator-doris/pull/3396#discussion_r417402243
##########
File path: fe/src/test/java/org/apache/doris/planner/QueryPlanTest.java
##########
@@ -504,4 +504,106 @@ public void testDateTypeEquality() throws Exception {
Catalog.getCurrentCatalog().getLoadManager().createLoadJobV1FromStmt(loadStmt, EtlJobType.HADOOP,
System.currentTimeMillis());
}
+
+ private SelectStmt getAnalyzedAndRewritedStmt(String sql) throws Exception {
+ SelectStmt selectStmt =
+ (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql, connectContext);
+ selectStmt = (SelectStmt) UtFrameUtils.rewriteStmt(selectStmt, connectContext);
+ selectStmt = (SelectStmt) UtFrameUtils.reAnalyze(selectStmt, connectContext);
+ return selectStmt;
+ }
+
+ @Test
+ public void testConvertCaseWhenToConstant() throws Exception {
+ // basic test
+ String caseWhenSql = "select "
+ + "case when date_format(now(),'%H%i') < 123 then 1 else 0 end as col "
+ + "from test.test1 "
+ + "where time = case when date_format(now(),'%H%i') < 123 then date_format(date_sub(now(),2),'%Y%m%d') else date_format(date_sub(now(),1),'%Y%m%d') end";
+ SelectStmt selectStmt = getAnalyzedAndRewritedStmt(caseWhenSql);
+ Assert.assertTrue(!selectStmt.toSql().contains("CASE WHEN") && !selectStmt.toSql().contains("case when"));
Review comment:
you could use `org.apache.commons.lang3.StringUtils.containsIgnoreCase`
##########
File path: fe/src/test/java/org/apache/doris/planner/QueryPlanTest.java
##########
@@ -504,4 +504,106 @@ public void testDateTypeEquality() throws Exception {
Catalog.getCurrentCatalog().getLoadManager().createLoadJobV1FromStmt(loadStmt, EtlJobType.HADOOP,
System.currentTimeMillis());
}
+
+ private SelectStmt getAnalyzedAndRewritedStmt(String sql) throws Exception {
+ SelectStmt selectStmt =
+ (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql, connectContext);
+ selectStmt = (SelectStmt) UtFrameUtils.rewriteStmt(selectStmt, connectContext);
+ selectStmt = (SelectStmt) UtFrameUtils.reAnalyze(selectStmt, connectContext);
+ return selectStmt;
+ }
+
+ @Test
+ public void testConvertCaseWhenToConstant() throws Exception {
Review comment:
Please move this test to `ConstantExpressTest`
##########
File path: fe/src/main/java/org/apache/doris/analysis/CaseExpr.java
##########
@@ -251,4 +257,72 @@ public void analyzeImpl(Analyzer analyzer) throws AnalysisException {
}
return exprs;
}
+
+ // this method just compare literal value and not completely consistent with be,for two cases
+ // 1 not deal float
+ // 2 just compare literal value with same type. for a example sql 'select case when 123 then '1' else '2' end as col'
+ // for be will return '1', because be only regard 0 as false
+ // but for current LiteralExpr.compareLiteral, `123`' won't be regard as true
+ // the case which two values has different type left to be
+ public static Expr computeCaseExpr(CaseExpr expr) {
+ LiteralExpr caseExpr;
+ int startIndex = 0;
+ int size = expr.getChildren().size() - 1;
+ if (expr.hasCaseExpr()) {
+ // just deal literal here
+ // and avoid `float compute` in java,float should be dealt in be
+ Expr caseChildExpr = expr.getChild(0);
+ if (!caseChildExpr.isLiteral()
+ || caseChildExpr instanceof DecimalLiteral || caseChildExpr instanceof FloatLiteral) {
+ return expr;
+ }
+ caseExpr = (LiteralExpr) expr.getChild(0);
+ startIndex++;
+ size--;
+ } else {
+ caseExpr = new BoolLiteral(true);
+ }
+
+ if (expr.hasElseExpr) {
+ size--;
+ }
+
+ //pre return when the `when expr` can't be converted to constants
Review comment:
```suggestion
// Early return when the `when expr` can't be converted to constants
```
##########
File path: fe/src/test/java/org/apache/doris/planner/QueryPlanTest.java
##########
@@ -504,4 +504,106 @@ public void testDateTypeEquality() throws Exception {
Catalog.getCurrentCatalog().getLoadManager().createLoadJobV1FromStmt(loadStmt, EtlJobType.HADOOP,
System.currentTimeMillis());
}
+
+ private SelectStmt getAnalyzedAndRewritedStmt(String sql) throws Exception {
Review comment:
Don't need to add this method. you could use `getSQLPlanOrErrorMsg` method.
##########
File path: fe/src/main/java/org/apache/doris/analysis/CaseExpr.java
##########
@@ -251,4 +257,72 @@ public void analyzeImpl(Analyzer analyzer) throws AnalysisException {
}
return exprs;
}
+
+ // this method just compare literal value and not completely consistent with be,for two cases
+ // 1 not deal float
+ // 2 just compare literal value with same type. for a example sql 'select case when 123 then '1' else '2' end as col'
+ // for be will return '1', because be only regard 0 as false
+ // but for current LiteralExpr.compareLiteral, `123`' won't be regard as true
+ // the case which two values has different type left to be
+ public static Expr computeCaseExpr(CaseExpr expr) {
+ LiteralExpr caseExpr;
+ int startIndex = 0;
Review comment:
would better rename `startIndex ` and `size `.
The `size` compute logic seems not consistent with BE ？
```
int loop_start = has_case_expr() ? 1 : 0; \
int loop_end = (has_else_expr()) ? num_children - 1 : num_children; \
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] kangkaisen commented on a change in pull request #3396: (#3395)calculate 'case when expr' when possible
Posted by GitBox <gi...@apache.org>.
kangkaisen commented on a change in pull request #3396:
URL: https://github.com/apache/incubator-doris/pull/3396#discussion_r418955391
##########
File path: fe/src/test/java/org/apache/doris/utframe/UtFrameUtils.java
##########
@@ -96,6 +100,34 @@ public static StatementBase parseAndAnalyzeStmt(String originStmt, ConnectContex
return statementBase;
}
+ public static StatementBase rewriteStmt(StatementBase stmt, ConnectContext ctx) throws AnalysisException {
Review comment:
These two methods could also be removed?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wangbo commented on a change in pull request #3396: (#3395)calculate 'case when expr' when possible
Posted by GitBox <gi...@apache.org>.
wangbo commented on a change in pull request #3396:
URL: https://github.com/apache/incubator-doris/pull/3396#discussion_r417758359
##########
File path: fe/src/test/java/org/apache/doris/planner/QueryPlanTest.java
##########
@@ -504,4 +504,106 @@ public void testDateTypeEquality() throws Exception {
Catalog.getCurrentCatalog().getLoadManager().createLoadJobV1FromStmt(loadStmt, EtlJobType.HADOOP,
System.currentTimeMillis());
}
+
+ private SelectStmt getAnalyzedAndRewritedStmt(String sql) throws Exception {
+ SelectStmt selectStmt =
+ (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql, connectContext);
+ selectStmt = (SelectStmt) UtFrameUtils.rewriteStmt(selectStmt, connectContext);
+ selectStmt = (SelectStmt) UtFrameUtils.reAnalyze(selectStmt, connectContext);
+ return selectStmt;
+ }
+
+ @Test
+ public void testConvertCaseWhenToConstant() throws Exception {
Review comment:
```case when expr``` rewrite more complicate than ```convert a function to constants```,it will change the query plan structure and dependency real doris table, so I think put UT in QueryPlanTest is better
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org