morningman commented on a change in pull request #3150: Support non-correlated
subquery in having clause
URL: https://github.com/apache/incubator-doris/pull/3150#discussion_r396200125
##########
File path: fe/src/main/java/org/apache/doris/analysis/StmtRewriter.java
##########
@@ -97,8 +104,142 @@ private static void rewriteSelectStatement(SelectStmt
stmt, Analyzer analyzer)
}
rewriteWhereClauseSubqueries(stmt, analyzer);
}
+ // Rewrite all subquery in the having clause
+ if (stmt.getHavingPred() != null && stmt.getHavingPred().getSubquery()
!= null) {
+ stmt = rewriteHavingClauseSubqueries(stmt, analyzer);
+ }
stmt.sqlString_ = null;
if (LOG.isDebugEnabled()) LOG.debug("rewritten stmt: " + stmt.toSql());
+ return stmt;
+ }
+
+ /**
+ * Rewrite having subquery.
+ * Step1: rewrite having subquery to where subquery
+ * Step2: rewrite where subquery
+ * <p>
+ * For example:
+ * select cs_item_sk, sum(cs_sales_price) from catalog_sales a group by
cs_item_sk
+ * having sum(cs_sales_price) >
+ * (select min(cs_sales_price) from catalog_sales b where
a.cs_item_sk = b.cs_item_sk);
+ * <p>
+ * Step1: rewrite having subquery to where subquery
+ * Outer query is changed to inline view in rewritten query
+ * Inline view of outer query:
+ * from (select cs_item_sk, sum(cs_sales_price) sum_cs_sales_price
from catalog_sales group by cs_item_sk) a
+ * Rewritten subquery of expr:
+ * where a.sum_cs_sales_price >
+ * (select min(cs_sales_price) from catalog_sales b where
a.cs_item_sk = b.cs_item_sk)
+ * Rewritten query:
+ * select cs_item_sk, a.sum_cs_sales_price from
+ * (select cs_item_sk, sum(cs_sales_price) sum_cs_sales_price from
catalog_sales group by cs_item_sk) a
+ * where a.sum_cs_sales_price >
+ * (select min(cs_sales_price) from catalog_sales b where
a.cs_item_sk = b.cs_item_sk)
+ * <p>
+ * Step2: rewrite where subquery
+ * Inline view of subquery:
+ * from (select b.cs_item_sk, min(cs_sales_price) from catalog_sales b
group by cs_item_sk) c
+ * Rewritten correlated predicate:
+ * where c.cs_item_sk = a.cs_item_sk and a.sum_cs_sales_price >
c.min(cs_sales_price)
Review comment:
How about adding a final stmt? Is that
```
select cs_item_sk, a.sum_cs_sales_price from
(select cs_item_sk, sum(cs_sales_price) sum_cs_sales_price from
catalog_sales group by cs_item_sk) a
join
(select b.cs_item_sk, min(cs_sales_price) from catalog_sales b group by
cs_item_sk) c
where c.cs_item_sk = a.cs_item_sk and a.sum_cs_sales_price >
c.min(cs_sales_price);
```
----------------------------------------------------------------
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:
us...@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
