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/26 02:54:07 UTC
[GitHub] [incubator-doris] EmmyMiao87 edited a comment on issue #2848:
Support subquery in having clause
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:
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