Repository: matrixorigin/matrixone
Pull request #9345 (2023-05-09 10:56:02)

Don't allow a quantified subquery's left operand to be a subquery
## What type of PR is this?

- [ ] API-change
- [x] BUG
- [ ] Improvement
- [ ] Documentation
- [ ] Feature
- [ ] Test and CI
- [ ] Code Refactoring

## Which issue(s) this PR fixes:

issue #9340

## What this PR does / why we need it:

aunjgr: @mergify queue
mergify[bot]: > queue

#### 🟠 The pull request is the 1st in the queue to be merged

<details>

#9345 is queued for merge.

**Required conditions of queue** `default` **for merge:**



**The following pull requests are queued:**

| | Pull request | Queue/Priority | Speculative checks | Queued
| ---: | :--- | :--- | :--- | :--- |
| 1 | Don't allow a quantified subquery's left operand to be a subquery ([#9345](/matrixorigin/matrixone/pull/9345)) | default/medium |  | 2023-05-09 13:05 UTC|

---

More informations about Mergify merge queue can be found in the [documentation](https://docs.mergify.com/actions/queue.html).

<details>
<summary>Mergify commands</summary>

<br />

You can also trigger Mergify actions by commenting on this pull request:

- `@Mergifyio refresh` will re-evaluate the queue rules

Additionally, on Mergify [dashboard](https://dashboard.mergify.com) you can:

- look at your merge queues
- generate the Mergify configuration with the config editor.

Finally, you can contact us on https://mergify.com
</details>

</details>


<!---
DO NOT EDIT
-*- Mergify Payload -*-
{"command": "queue", "conclusion": null}
-*- Mergify Payload End -*-
-->

Patch:
diff --git a/pkg/sql/plan/flatten_subquery.go b/pkg/sql/plan/flatten_subquery.go
index f9f403ead..80bf5b917 100644
--- a/pkg/sql/plan/flatten_subquery.go
+++ b/pkg/sql/plan/flatten_subquery.go
@@ -71,6 +71,10 @@ func (builder *QueryBuilder) flattenSubqueries(nodeID int32, expr *plan.Expr, ct
 }
 
 func (builder *QueryBuilder) flattenSubquery(nodeID int32, subquery *plan.SubqueryRef, ctx *BindContext) (int32, *plan.Expr, error) {
+	if subquery.Child != nil && hasSubquery(subquery.Child) {
+		return 0, nil, moerr.NewNotSupported(builder.GetContext(), "a quantified subquery's left operand can't contain subquery")
+	}
+
 	subID := subquery.NodeId
 	subCtx := builder.ctxByNode[subID]
 
diff --git a/pkg/sql/plan/utils.go b/pkg/sql/plan/utils.go
index dae8c3911..74f10b48e 100644
--- a/pkg/sql/plan/utils.go
+++ b/pkg/sql/plan/utils.go
@@ -71,11 +71,46 @@ func hasCorrCol(expr *plan.Expr) bool {
 		return true
 
 	case *plan.Expr_F:
-		ret := false
 		for _, arg := range exprImpl.F.Args {
-			ret = ret || hasCorrCol(arg)
+			if hasCorrCol(arg) {
+				return true
+			}
 		}
-		return ret
+		return false
+
+	case *plan.Expr_List:
+		for _, arg := range exprImpl.List.List {
+			if hasCorrCol(arg) {
+				return true
+			}
+		}
+		return false
+
+	default:
+		return false
+	}
+}
+
+func hasSubquery(expr *plan.Expr) bool {
+	switch exprImpl := expr.Expr.(type) {
+	case *plan.Expr_Sub:
+		return true
+
+	case *plan.Expr_F:
+		for _, arg := range exprImpl.F.Args {
+			if hasSubquery(arg) {
+				return true
+			}
+		}
+		return false
+
+	case *plan.Expr_List:
+		for _, arg := range exprImpl.List.List {
+			if hasSubquery(arg) {
+				return true
+			}
+		}
+		return false
 
 	default:
 		return false


Test patch:
diff --git a/test/distributed/cases/subquery/subquery-with-in.result b/test/distributed/cases/subquery/subquery-with-in.result
index 9d64b7639..ff68554d9 100755
--- a/test/distributed/cases/subquery/subquery-with-in.result
+++ b/test/distributed/cases/subquery/subquery-with-in.result
@@ -824,8 +824,7 @@ WHERE a IN (SELECT 4 FROM t1 AS x2)
 )
 ) AS result
 FROM t1 AS x3;
-result
-false
+not supported: a quantified subquery's left operand can't contain subquery
 SELECT
 (
 (36, (SELECT 1 IN (SELECT 1 FROM t1 AS x1)))
@@ -836,8 +835,7 @@ WHERE a IN (SELECT 4 FROM t1 AS x2)
 )
 ) AS result
 FROM t1 AS x3;
-result
-false
+not supported: a quantified subquery's left operand can't contain subquery
 DROP TABLE IF EXISTS t1;
 drop table if exists t2;
 CREATE TABLE t1 (