EmmyMiao87 edited a comment on issue #2848: Support subquery in having clause URL: https://github.com/apache/incubator-doris/issues/2848#issuecomment-582784888 ## Semantic detection 1. check for related subquery ``` correlated predicate in where explain select k1, sum (k2) from all_type_table a group by k1 having k1> (select min (k1) from all_type_table b where a.k1 = b.k1); ERROR 1064 (HY000): errCode = 2, detailMessage = Correlated subquery are not supported in the HAVING clause. ``` ``` correlated predicate in inline view explain select k1, sum (k2) from all_type_table a group by k1 having k1> (select min (k1) from (select * from all_type_table b where a.k1 = b.k1) c); unknown column ``` 2. Does the alias affect the judgment of semantic detection? The column alias of the outer query involved in the subquery is currently unrecognized. ``` alias column in outer query explain select k1 a, sum (k2) b from all_type_table a group by k1 having b> (select min (k1) from all_type_table b where a.a = b.k1); unknown column ``` ``` alias column using in inline view explain select k1 a, sum (k2) b from all_type_table a group by k1 having k1> (select min (k1) from (select * from all_type_table b where a.a = b.k1) c); unknown column ``` ## Query rewrite Scalar query ``` explain select k1, sum (k2) from all_type_table a group by k1 having k1> (select min (k1) from all_type_table b where a.k1 = b.k1); No assert row number node ``` 2. non scalar query ``` explain select k1, sum (k2) from all_type_table a group by k1 having k1> (select k1 from all_type_table b where a.k1 = b.k1); The top node of the rewritten subquery plan contains an assert row number node ``` 3. Non-Related Subqueries ``` Without calculation explain select k1, sum (k2) from all_type_table a group by k1 having k1> (select min (k1) from all_type_table b); supported ``` ``` Arithmetic expressions with subqueries explain select k1, sum (k2) from all_type_table a group by k1 having k1> (95 / 100.0) * (select min (k1) from all_type_table b); supported ``` ``` Subquery results with calculations explain select k1, sum (k2) from all_type_table a group by k1 having k1> (select 0.9 * min (k1) from all_type_table b); supported ``` ``` Subquery with aggregate explain select k1, sum (k2) from all_type_table a group by k1 having k1> (select min (k1) from all_type_table b group by k2); mysql> explain select k1, sum (k2) from all_type_table group by k1 having sum (k2)> (select sum (k1) from all_type_table); -------------------------------------------------- ----------------------------- | Explain String | -------------------------------------------------- ----------------------------- | PLAN FRAGMENT 0 | | OUTPUT EXPRS: <slot 2> | <slot 3> | | PARTITION: RANDOM | | | | RESULT SINK | | | | 4: CROSS JOIN | | | cross join: | | | predicates: <slot 3>> <slot 7> | | | tuple ids: 1 4 | | | | | | ---- 5: EXCHANGE | | | tuple ids: 4 | | | | | 1: AGGREGATE (update finalize) | | | output: sum (`k2`) | | | group by: `k1` | | | tuple ids: 1 | | | | | 0: OlapScanNode | | TABLE: all_type_table | | PREAGGREGATION: OFF. Reason: Aggregate Operator not match: SUM <-> NONE | | partitions = 0/1 | | rollup: null | | tabletRatio = 0/0 | | tabletList = | | cardinality = -1 | | avgRowSize = 0.0 | | numNodes = 0 | | tuple ids: 0 | | | | PLAN FRAGMENT 1 | | OUTPUT EXPRS: | | PARTITION: RANDOM | | | | STREAM DATA SINK | | EXCHANGE ID: 05 | | UNPARTITIONED | | | | 3:AGGREGATE (update finalize) | | | output: sum(`k1`) | | | group by: | | | tuple ids: 4 | | | | | 2:OlapScanNode | | TABLE: all_type_table | | PREAGGREGATION: OFF. Reason: Aggregate Operator not match: SUM <--> null | | partitions=0/1 | | rollup: null | | tabletRatio=0/0 | | tabletList= | | cardinality=-1 | | avgRowSize=0.0 | | numNodes=0 | | tuple ids: 3 | +-------------------------------------------------------------------------------+ 55 rows in set (0.04 sec) ``` ``` The outer query is a complex aggregation type such as avg, count distinct explain select k1, avg (k2) from all_type_table a group by k1 having avg (k2)> (select min (k1) from all_type_table b); ``` ``` Subquery contains grouped columns explain select k1, sum (k2) from all_type_table a group by k1 having sum (k2)> 0.9 * (select avg (k1) from all_type_table b group by k4); Aggregation after grouping, and assert node at the top of the subquery ``` 4. correlated subquery As of now, correlated subqueries cannot resolve associated columns during semantic parsing. The reason why it cannot be parsed is that the associated column should be parsed by the top-level tuple of the outer query, but it cannot be mapped to the correct tuple during parsing.
---------------------------------------------------------------- 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