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/03/17 12:29:01 UTC
[GitHub] [incubator-doris] yangzhg opened a new pull request #3135: support
subquery in case when statement
yangzhg opened a new pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135
implement subquery support for sub query in case when statement like
```
SELECT CASE
WHEN (
SELECT COUNT(*) / 2
FROM t
) > k4 THEN (
SELECT AVG(k4)
FROM t
)
ELSE (
SELECT SUM(k4)
FROM t
)
END AS kk4
FROM t;
```
this statement will be rewrite to
```
SELECT CASE
WHEN t1.a > k4 THEN t2.a
ELSE t3.a
END AS kk4
FROM t, (
SELECT COUNT(*) / 2 AS a
FROM t
) t1, (
SELECT AVG(k4) AS a
FROM t
) t2, (
SELECT SUM(k4) AS a
FROM t
) t3;
```
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
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
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
morningman commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r393727675
##########
File path: fe/src/main/java/org/apache/doris/analysis/SelectStmt.java
##########
@@ -1244,6 +1213,46 @@ public void rewriteExprs(ExprRewriter rewriter) throws AnalysisException {
orderByElem.setExpr(rewriter.rewrite(orderByElem.getExpr(), analyzer));
}
}
+ if (subqueryInCase) {
+ for (SelectListItem item : selectList.getItems()) {
+ if (!(item.getExpr() instanceof CaseExpr)) {
+ continue;
+ }
+ if (!item.getExpr().contains(Predicates.instanceOf(Subquery.class))) {
+ continue;
+ }
+ item.setExpr(rewriteSubquery(item.getExpr()));
+ }
+ }
+ }
+
+
+ private Expr rewriteSubquery(Expr expr) throws AnalysisException {
Review comment:
Add comments for this method.
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
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
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
morningman commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r393733325
##########
File path: fe/src/main/java/org/apache/doris/analysis/SelectStmt.java
##########
@@ -1244,6 +1213,46 @@ public void rewriteExprs(ExprRewriter rewriter) throws AnalysisException {
orderByElem.setExpr(rewriter.rewrite(orderByElem.getExpr(), analyzer));
}
}
+ if (subqueryInCase) {
+ for (SelectListItem item : selectList.getItems()) {
+ if (!(item.getExpr() instanceof CaseExpr)) {
+ continue;
+ }
+ if (!item.getExpr().contains(Predicates.instanceOf(Subquery.class))) {
+ continue;
+ }
+ item.setExpr(rewriteSubquery(item.getExpr()));
+ }
+ }
+ }
+
+
+ private Expr rewriteSubquery(Expr expr) throws AnalysisException {
+ if (expr instanceof Subquery) {
+ if (!(((Subquery) expr).getStatement() instanceof SelectStmt) ) {
+ throw new AnalysisException("only support select subquery in case statement.");
+ }
+ SelectStmt subquery = (SelectStmt)((Subquery) expr).getStatement();
+ if (subquery.getSelectList().getItems().size() != 1) {
Review comment:
How do you make sure the subquery is like `select sum(k1) from tbl`, not `select k1 from tbl`?
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on a change in pull request
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r395417179
##########
File path: fe/src/main/java/org/apache/doris/analysis/StmtRewriter.java
##########
@@ -953,5 +957,81 @@ private static Expr createJoinConjunct(Expr exprWithSubquery, InlineViewRef inli
smap.put(subquery, subquerySubstitute);
return exprWithSubquery.substitute(smap, analyzer, false);
}
+
+ /** rewrite subquery in case when to an inline view
+ * subquery in case when statement like
+ *
+ * SELECT CASE
+ * WHEN (
+ * SELECT COUNT(*) / 2
+ * FROM t
+ * ) > k4 THEN (
+ * SELECT AVG(k4)
+ * FROM t
+ * )
+ * ELSE (
+ * SELECT SUM(k4)
+ * FROM t
+ * )
+ * END AS kk4
+ * FROM t;
+ * this statement will be rewrite to
+ *
+ * SELECT CASE
+ * WHEN t1.a > k4 THEN t2.a
+ * ELSE t3.a
+ * END AS kk4
+ * FROM t, (
+ * SELECT COUNT(*) / 2 AS a
+ * FROM t
+ * ) t1, (
+ * SELECT AVG(k4) AS a
+ * FROM t
+ * ) t2, (
+ * SELECT SUM(k4) AS a
+ * FROM t
+ * ) t3;
+ */
+ private static void rewriteCaseWhenSubqueries(SelectStmt stmt, Analyzer analyzer) throws AnalysisException {
+ for (SelectListItem item: stmt.getSelectList().getItems()) {
+ if (!(item.getExpr() instanceof CaseExpr)) {
+ continue;
+ }
+ if (!item.getExpr().contains(Predicates.instanceOf(Subquery.class))) {
+ continue;
+ }
+ item.setExpr(rewriteCaseWhenSubquery(item.getExpr(), stmt, analyzer));
+ }
+
+ }
+
+ private static Expr rewriteCaseWhenSubquery(Expr expr, SelectStmt stmt, Analyzer analyzer)
+ throws AnalysisException {
+ if (expr instanceof Subquery) {
+ if (!(((Subquery) expr).getStatement() instanceof SelectStmt)) {
+ throw new AnalysisException("only support select subquery in case statement.");
+ }
+ SelectStmt subquery = (SelectStmt) ((Subquery) expr).getStatement();
+ if (subquery.getSelectList().getItems().size() != 1) {
+ throw new AnalysisException("only support scala select subquery in case statement.");
Review comment:
```suggestion
throw new AnalysisException("Only support one result column of subquery in case statement.");
```
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on a change in pull request
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r394779004
##########
File path: fe/src/main/java/org/apache/doris/analysis/SelectStmt.java
##########
@@ -1246,6 +1228,68 @@ public void rewriteExprs(ExprRewriter rewriter) throws AnalysisException {
}
}
+ /** rewrite subquery in case when to an inline view
+ * subquery in case when statement like
+ *
+ * SELECT CASE
+ * WHEN (
+ * SELECT COUNT(*) / 2
+ * FROM t
+ * ) > k4 THEN (
+ * SELECT AVG(k4)
+ * FROM t
+ * )
+ * ELSE (
+ * SELECT SUM(k4)
+ * FROM t
+ * )
+ * END AS kk4
+ * FROM t;
+ * this statement will be rewrite to
+ *
+ * SELECT CASE
+ * WHEN t1.a > k4 THEN t2.a
+ * ELSE t3.a
+ * END AS kk4
+ * FROM t, (
+ * SELECT COUNT(*) / 2 AS a
+ * FROM t
+ * ) t1, (
+ * SELECT AVG(k4) AS a
+ * FROM t
+ * ) t2, (
+ * SELECT SUM(k4) AS a
+ * FROM t
+ * ) t3;
+ */
+ private Expr rewriteSubquery(Expr expr) throws AnalysisException {
Review comment:
The entire query should be reanalyzed if there is subquery is rewritten. So maybe this method should be move to StmtRewriter.
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on a change in pull request
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r394779125
##########
File path: fe/src/main/java/org/apache/doris/analysis/SelectStmt.java
##########
@@ -1246,6 +1228,68 @@ public void rewriteExprs(ExprRewriter rewriter) throws AnalysisException {
}
}
+ /** rewrite subquery in case when to an inline view
+ * subquery in case when statement like
+ *
+ * SELECT CASE
+ * WHEN (
+ * SELECT COUNT(*) / 2
+ * FROM t
+ * ) > k4 THEN (
+ * SELECT AVG(k4)
+ * FROM t
+ * )
+ * ELSE (
+ * SELECT SUM(k4)
+ * FROM t
+ * )
+ * END AS kk4
+ * FROM t;
+ * this statement will be rewrite to
+ *
+ * SELECT CASE
+ * WHEN t1.a > k4 THEN t2.a
+ * ELSE t3.a
+ * END AS kk4
+ * FROM t, (
+ * SELECT COUNT(*) / 2 AS a
+ * FROM t
+ * ) t1, (
+ * SELECT AVG(k4) AS a
+ * FROM t
+ * ) t2, (
+ * SELECT SUM(k4) AS a
+ * FROM t
+ * ) t3;
+ */
+ private Expr rewriteSubquery(Expr expr) throws AnalysisException {
+ if (expr instanceof Subquery) {
+ if (!(((Subquery) expr).getStatement() instanceof SelectStmt)) {
+ throw new AnalysisException("only support select subquery in case statement.");
+ }
+ SelectStmt subquery = (SelectStmt) ((Subquery) expr).getStatement();
+ if (subquery.getSelectList().getItems().size() != 1) {
+ throw new AnalysisException("only support scala select subquery in case statement.");
+ }
+ String alias = getTableAliasGenerator().getNextAlias();
+ String colAlias = getColumnAliasGenerator().getNextAlias();
+ InlineViewRef inlineViewRef = new InlineViewRef(alias, subquery, Arrays.asList(colAlias));
Review comment:
The subquery should be reset before it is reanalyzed.
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
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
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
morningman commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r394858704
##########
File path: fe/src/main/java/org/apache/doris/analysis/SelectStmt.java
##########
@@ -17,6 +17,15 @@
package org.apache.doris.analysis;
+import com.google.common.base.Preconditions;
Review comment:
Reorder your import order in your IDE:
org.apache.doris
com
org
java
javax
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] yangzhg commented on a change in pull request
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
yangzhg commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r394817526
##########
File path: fe/src/main/java/org/apache/doris/analysis/SelectStmt.java
##########
@@ -341,7 +345,11 @@ public void analyze(Analyzer analyzer) throws AnalysisException, UserException {
// of expr child and depth limits (toColumn() label may call toSql()).
item.getExpr().analyze(analyzer);
if (item.getExpr().contains(Predicates.instanceOf(Subquery.class))) {
- throw new AnalysisException("Subquery is not supported in the select list.");
+ if (item.getExpr() instanceof CaseExpr) {
Review comment:
this is not allowed by in predicate or other predicate
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] yangzhg commented on a change in pull request
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
yangzhg commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r395421920
##########
File path: fe/src/main/java/org/apache/doris/analysis/StmtRewriter.java
##########
@@ -953,5 +957,81 @@ private static Expr createJoinConjunct(Expr exprWithSubquery, InlineViewRef inli
smap.put(subquery, subquerySubstitute);
return exprWithSubquery.substitute(smap, analyzer, false);
}
+
+ /** rewrite subquery in case when to an inline view
+ * subquery in case when statement like
+ *
+ * SELECT CASE
+ * WHEN (
+ * SELECT COUNT(*) / 2
+ * FROM t
+ * ) > k4 THEN (
+ * SELECT AVG(k4)
+ * FROM t
+ * )
+ * ELSE (
+ * SELECT SUM(k4)
+ * FROM t
+ * )
+ * END AS kk4
+ * FROM t;
+ * this statement will be rewrite to
+ *
+ * SELECT CASE
+ * WHEN t1.a > k4 THEN t2.a
+ * ELSE t3.a
+ * END AS kk4
+ * FROM t, (
+ * SELECT COUNT(*) / 2 AS a
+ * FROM t
+ * ) t1, (
+ * SELECT AVG(k4) AS a
+ * FROM t
+ * ) t2, (
+ * SELECT SUM(k4) AS a
+ * FROM t
+ * ) t3;
+ */
+ private static void rewriteCaseWhenSubqueries(SelectStmt stmt, Analyzer analyzer) throws AnalysisException {
+ for (SelectListItem item: stmt.getSelectList().getItems()) {
+ if (!(item.getExpr() instanceof CaseExpr)) {
+ continue;
+ }
+ if (!item.getExpr().contains(Predicates.instanceOf(Subquery.class))) {
+ continue;
+ }
+ item.setExpr(rewriteCaseWhenSubquery(item.getExpr(), stmt, analyzer));
+ }
+
+ }
+
+ private static Expr rewriteCaseWhenSubquery(Expr expr, SelectStmt stmt, Analyzer analyzer)
+ throws AnalysisException {
+ if (expr instanceof Subquery) {
+ if (!(((Subquery) expr).getStatement() instanceof SelectStmt)) {
+ throw new AnalysisException("only support select subquery in case statement.");
+ }
+ SelectStmt subquery = (SelectStmt) ((Subquery) expr).getStatement();
+ if (subquery.getSelectList().getItems().size() != 1) {
+ throw new AnalysisException("only support scala select subquery in case statement.");
+ }
+ subquery.reset();
+ String alias = stmt.getTableAliasGenerator().getNextAlias();
+ String colAlias = stmt.getColumnAliasGenerator().getNextAlias();
+ InlineViewRef inlineViewRef = new InlineViewRef(alias, subquery, Arrays.asList(colAlias));
+ try {
+ inlineViewRef.analyze(analyzer);
+ } catch (UserException e) {
+ throw new AnalysisException(e.getMessage());
+ }
+ stmt.fromClause_.add(inlineViewRef);
+ expr = new SlotRef(new TableName(null, alias), colAlias);
Review comment:
first parameter is database name
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] yangzhg commented on a change in pull request
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
yangzhg commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r394086481
##########
File path: fe/src/main/java/org/apache/doris/analysis/SelectStmt.java
##########
@@ -1244,6 +1213,46 @@ public void rewriteExprs(ExprRewriter rewriter) throws AnalysisException {
orderByElem.setExpr(rewriter.rewrite(orderByElem.getExpr(), analyzer));
}
}
+ if (subqueryInCase) {
+ for (SelectListItem item : selectList.getItems()) {
+ if (!(item.getExpr() instanceof CaseExpr)) {
+ continue;
+ }
+ if (!item.getExpr().contains(Predicates.instanceOf(Subquery.class))) {
+ continue;
+ }
+ item.setExpr(rewriteSubquery(item.getExpr()));
+ }
+ }
+ }
+
+
+ private Expr rewriteSubquery(Expr expr) throws AnalysisException {
+ if (expr instanceof Subquery) {
+ if (!(((Subquery) expr).getStatement() instanceof SelectStmt) ) {
+ throw new AnalysisException("only support select subquery in case statement.");
+ }
+ SelectStmt subquery = (SelectStmt)((Subquery) expr).getStatement();
+ if (subquery.getSelectList().getItems().size() != 1) {
Review comment:
this expr is validate by the predicate of case when clause
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on a change in pull request
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r395417350
##########
File path: fe/src/main/java/org/apache/doris/analysis/StmtRewriter.java
##########
@@ -953,5 +957,81 @@ private static Expr createJoinConjunct(Expr exprWithSubquery, InlineViewRef inli
smap.put(subquery, subquerySubstitute);
return exprWithSubquery.substitute(smap, analyzer, false);
}
+
+ /** rewrite subquery in case when to an inline view
+ * subquery in case when statement like
+ *
+ * SELECT CASE
+ * WHEN (
+ * SELECT COUNT(*) / 2
+ * FROM t
+ * ) > k4 THEN (
+ * SELECT AVG(k4)
+ * FROM t
+ * )
+ * ELSE (
+ * SELECT SUM(k4)
+ * FROM t
+ * )
+ * END AS kk4
+ * FROM t;
+ * this statement will be rewrite to
+ *
+ * SELECT CASE
+ * WHEN t1.a > k4 THEN t2.a
+ * ELSE t3.a
+ * END AS kk4
+ * FROM t, (
+ * SELECT COUNT(*) / 2 AS a
+ * FROM t
+ * ) t1, (
+ * SELECT AVG(k4) AS a
+ * FROM t
+ * ) t2, (
+ * SELECT SUM(k4) AS a
+ * FROM t
+ * ) t3;
+ */
+ private static void rewriteCaseWhenSubqueries(SelectStmt stmt, Analyzer analyzer) throws AnalysisException {
+ for (SelectListItem item: stmt.getSelectList().getItems()) {
+ if (!(item.getExpr() instanceof CaseExpr)) {
+ continue;
+ }
+ if (!item.getExpr().contains(Predicates.instanceOf(Subquery.class))) {
+ continue;
+ }
+ item.setExpr(rewriteCaseWhenSubquery(item.getExpr(), stmt, analyzer));
+ }
+
+ }
+
+ private static Expr rewriteCaseWhenSubquery(Expr expr, SelectStmt stmt, Analyzer analyzer)
+ throws AnalysisException {
+ if (expr instanceof Subquery) {
+ if (!(((Subquery) expr).getStatement() instanceof SelectStmt)) {
+ throw new AnalysisException("only support select subquery in case statement.");
+ }
+ SelectStmt subquery = (SelectStmt) ((Subquery) expr).getStatement();
+ if (subquery.getSelectList().getItems().size() != 1) {
Review comment:
The correlated subquery should be forbidden.
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wutiangan commented on a change in pull request
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
wutiangan commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r396373763
##########
File path: fe/src/main/java/org/apache/doris/analysis/SelectList.java
##########
@@ -83,7 +84,18 @@ public void reset() {
public void rewriteExprs(ExprRewriter rewriter, Analyzer analyzer)
throws AnalysisException {
for (SelectListItem item : items) {
- if (item.isStar()) continue;
+ if (item.isStar()) {
+ continue;
+ }
+ // when select list contain case when, for now subquery is supported in case-when
+ // (or more subquery is supported in future) so subquery all need to rewrite
Review comment:
The meaning of these two lines is not clear。
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
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
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
morningman commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r393729725
##########
File path: fe/src/main/java/org/apache/doris/analysis/SelectStmt.java
##########
@@ -1244,6 +1213,46 @@ public void rewriteExprs(ExprRewriter rewriter) throws AnalysisException {
orderByElem.setExpr(rewriter.rewrite(orderByElem.getExpr(), analyzer));
}
}
+ if (subqueryInCase) {
+ for (SelectListItem item : selectList.getItems()) {
+ if (!(item.getExpr() instanceof CaseExpr)) {
+ continue;
+ }
+ if (!item.getExpr().contains(Predicates.instanceOf(Subquery.class))) {
+ continue;
+ }
+ item.setExpr(rewriteSubquery(item.getExpr()));
+ }
+ }
+ }
+
+
+ private Expr rewriteSubquery(Expr expr) throws AnalysisException {
+ if (expr instanceof Subquery) {
+ if (!(((Subquery) expr).getStatement() instanceof SelectStmt) ) {
+ throw new AnalysisException("only support select subquery in case statement.");
+ }
+ SelectStmt subquery = (SelectStmt)((Subquery) expr).getStatement();
+ if (subquery.getSelectList().getItems().size() != 1) {
+ throw new AnalysisException("only support scala select subquery in case statement.");
+ }
+ String alias = getTableAliasGenerator().getNextAlias();
+ String colAlias = getColumnAliasGenerator().getNextAlias();
+ InlineViewRef inlineViewRef = new InlineViewRef(alias, subquery, Arrays.asList(colAlias));
+ try {
+ inlineViewRef.analyze(analyzer);
+ } catch (UserException e) {
+ throw new AnalysisException(e.getMessage());
+ }
+ fromClause_.add(inlineViewRef);
+ expr = new SlotRef(new TableName(null, alias), colAlias);
Review comment:
```suggestion
expr = new SlotRef(new TableName(null, alias), colAlias);
```
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
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
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
morningman commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r393726480
##########
File path: fe/src/main/java/org/apache/doris/analysis/SelectStmt.java
##########
@@ -102,6 +103,8 @@
// Set in analyze().
protected String sqlString_;
+ private boolean subqueryInCase = false;
Review comment:
Add comment for this member~
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on a change in pull request
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r395417753
##########
File path: fe/src/main/java/org/apache/doris/analysis/StmtRewriter.java
##########
@@ -953,5 +957,81 @@ private static Expr createJoinConjunct(Expr exprWithSubquery, InlineViewRef inli
smap.put(subquery, subquerySubstitute);
return exprWithSubquery.substitute(smap, analyzer, false);
}
+
+ /** rewrite subquery in case when to an inline view
+ * subquery in case when statement like
+ *
+ * SELECT CASE
+ * WHEN (
+ * SELECT COUNT(*) / 2
+ * FROM t
+ * ) > k4 THEN (
+ * SELECT AVG(k4)
+ * FROM t
+ * )
+ * ELSE (
+ * SELECT SUM(k4)
+ * FROM t
+ * )
+ * END AS kk4
+ * FROM t;
+ * this statement will be rewrite to
+ *
+ * SELECT CASE
+ * WHEN t1.a > k4 THEN t2.a
+ * ELSE t3.a
+ * END AS kk4
+ * FROM t, (
+ * SELECT COUNT(*) / 2 AS a
+ * FROM t
+ * ) t1, (
+ * SELECT AVG(k4) AS a
+ * FROM t
+ * ) t2, (
+ * SELECT SUM(k4) AS a
+ * FROM t
+ * ) t3;
+ */
+ private static void rewriteCaseWhenSubqueries(SelectStmt stmt, Analyzer analyzer) throws AnalysisException {
+ for (SelectListItem item: stmt.getSelectList().getItems()) {
+ if (!(item.getExpr() instanceof CaseExpr)) {
+ continue;
+ }
+ if (!item.getExpr().contains(Predicates.instanceOf(Subquery.class))) {
+ continue;
+ }
+ item.setExpr(rewriteCaseWhenSubquery(item.getExpr(), stmt, analyzer));
+ }
+
+ }
+
+ private static Expr rewriteCaseWhenSubquery(Expr expr, SelectStmt stmt, Analyzer analyzer)
+ throws AnalysisException {
+ if (expr instanceof Subquery) {
+ if (!(((Subquery) expr).getStatement() instanceof SelectStmt)) {
+ throw new AnalysisException("only support select subquery in case statement.");
+ }
+ SelectStmt subquery = (SelectStmt) ((Subquery) expr).getStatement();
+ if (subquery.getSelectList().getItems().size() != 1) {
+ throw new AnalysisException("only support scala select subquery in case statement.");
+ }
+ subquery.reset();
+ String alias = stmt.getTableAliasGenerator().getNextAlias();
+ String colAlias = stmt.getColumnAliasGenerator().getNextAlias();
+ InlineViewRef inlineViewRef = new InlineViewRef(alias, subquery, Arrays.asList(colAlias));
+ try {
+ inlineViewRef.analyze(analyzer);
+ } catch (UserException e) {
+ throw new AnalysisException(e.getMessage());
+ }
+ stmt.fromClause_.add(inlineViewRef);
+ expr = new SlotRef(new TableName(null, alias), colAlias);
Review comment:
The table name could be fetch from inline view ref
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] morningman merged pull request #3135: support
subquery in case when statement
Posted by GitBox <gi...@apache.org>.
morningman merged pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on a change in pull request
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r394779204
##########
File path: fe/src/main/java/org/apache/doris/analysis/SelectStmt.java
##########
@@ -341,7 +345,11 @@ public void analyze(Analyzer analyzer) throws AnalysisException, UserException {
// of expr child and depth limits (toColumn() label may call toSql()).
item.getExpr().analyze(analyzer);
if (item.getExpr().contains(Predicates.instanceOf(Subquery.class))) {
- throw new AnalysisException("Subquery is not supported in the select list.");
+ if (item.getExpr() instanceof CaseExpr) {
Review comment:
How about the case expr in the where predicate?
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on a change in pull request
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r395416717
##########
File path: fe/src/main/java/org/apache/doris/analysis/StmtRewriter.java
##########
@@ -953,5 +957,81 @@ private static Expr createJoinConjunct(Expr exprWithSubquery, InlineViewRef inli
smap.put(subquery, subquerySubstitute);
return exprWithSubquery.substitute(smap, analyzer, false);
}
+
+ /** rewrite subquery in case when to an inline view
+ * subquery in case when statement like
+ *
+ * SELECT CASE
+ * WHEN (
+ * SELECT COUNT(*) / 2
+ * FROM t
+ * ) > k4 THEN (
+ * SELECT AVG(k4)
+ * FROM t
+ * )
+ * ELSE (
+ * SELECT SUM(k4)
+ * FROM t
+ * )
+ * END AS kk4
+ * FROM t;
+ * this statement will be rewrite to
+ *
+ * SELECT CASE
+ * WHEN t1.a > k4 THEN t2.a
+ * ELSE t3.a
+ * END AS kk4
+ * FROM t, (
+ * SELECT COUNT(*) / 2 AS a
+ * FROM t
+ * ) t1, (
+ * SELECT AVG(k4) AS a
+ * FROM t
+ * ) t2, (
+ * SELECT SUM(k4) AS a
+ * FROM t
+ * ) t3;
+ */
+ private static void rewriteCaseWhenSubqueries(SelectStmt stmt, Analyzer analyzer) throws AnalysisException {
+ for (SelectListItem item: stmt.getSelectList().getItems()) {
+ if (!(item.getExpr() instanceof CaseExpr)) {
+ continue;
+ }
+ if (!item.getExpr().contains(Predicates.instanceOf(Subquery.class))) {
+ continue;
+ }
+ item.setExpr(rewriteCaseWhenSubquery(item.getExpr(), stmt, analyzer));
+ }
+
+ }
+
+ private static Expr rewriteCaseWhenSubquery(Expr expr, SelectStmt stmt, Analyzer analyzer)
+ throws AnalysisException {
+ if (expr instanceof Subquery) {
+ if (!(((Subquery) expr).getStatement() instanceof SelectStmt)) {
+ throw new AnalysisException("only support select subquery in case statement.");
+ }
+ SelectStmt subquery = (SelectStmt) ((Subquery) expr).getStatement();
+ if (subquery.getSelectList().getItems().size() != 1) {
Review comment:
If the item of select list is '*'
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] yangzhg commented on a change in pull request
#3135: support subquery in case when statement
Posted by GitBox <gi...@apache.org>.
yangzhg commented on a change in pull request #3135: support subquery in case when statement
URL: https://github.com/apache/incubator-doris/pull/3135#discussion_r396310757
##########
File path: fe/src/main/java/org/apache/doris/analysis/StmtRewriter.java
##########
@@ -953,5 +957,81 @@ private static Expr createJoinConjunct(Expr exprWithSubquery, InlineViewRef inli
smap.put(subquery, subquerySubstitute);
return exprWithSubquery.substitute(smap, analyzer, false);
}
+
+ /** rewrite subquery in case when to an inline view
+ * subquery in case when statement like
+ *
+ * SELECT CASE
+ * WHEN (
+ * SELECT COUNT(*) / 2
+ * FROM t
+ * ) > k4 THEN (
+ * SELECT AVG(k4)
+ * FROM t
+ * )
+ * ELSE (
+ * SELECT SUM(k4)
+ * FROM t
+ * )
+ * END AS kk4
+ * FROM t;
+ * this statement will be rewrite to
+ *
+ * SELECT CASE
+ * WHEN t1.a > k4 THEN t2.a
+ * ELSE t3.a
+ * END AS kk4
+ * FROM t, (
+ * SELECT COUNT(*) / 2 AS a
+ * FROM t
+ * ) t1, (
+ * SELECT AVG(k4) AS a
+ * FROM t
+ * ) t2, (
+ * SELECT SUM(k4) AS a
+ * FROM t
+ * ) t3;
+ */
+ private static void rewriteCaseWhenSubqueries(SelectStmt stmt, Analyzer analyzer) throws AnalysisException {
+ for (SelectListItem item: stmt.getSelectList().getItems()) {
+ if (!(item.getExpr() instanceof CaseExpr)) {
+ continue;
+ }
+ if (!item.getExpr().contains(Predicates.instanceOf(Subquery.class))) {
+ continue;
+ }
+ item.setExpr(rewriteCaseWhenSubquery(item.getExpr(), stmt, analyzer));
+ }
+
+ }
+
+ private static Expr rewriteCaseWhenSubquery(Expr expr, SelectStmt stmt, Analyzer analyzer)
+ throws AnalysisException {
+ if (expr instanceof Subquery) {
+ if (!(((Subquery) expr).getStatement() instanceof SelectStmt)) {
+ throw new AnalysisException("only support select subquery in case statement.");
+ }
+ SelectStmt subquery = (SelectStmt) ((Subquery) expr).getStatement();
+ if (subquery.getSelectList().getItems().size() != 1) {
Review comment:
correlated subquery is supported
----------------------------------------------------------------
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
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org