{"record":{"id":"1ee2faad1617bebb","repo":"vitessio/vitess","slug":"vt12001-1ee2fa","errorCode":"VT12001","errorMessage":"unmergable subquery can not be inside complex expression","messagePattern":"unmergable subquery can not be inside complex expression","errorType":"error_code","errorClass":null,"httpStatus":null,"severity":"error","filePath":"go/vt/vtgate/planbuilder/operators/subquery.go","lineNumber":214,"sourceCode":"}\n\nfunc (sq *SubQuery) GetSelectExprs(ctx *plancontext.PlanningContext) []sqlparser.SelectExpr {\n\treturn sq.Outer.GetSelectExprs(ctx)\n}\n\n// GetMergePredicates returns the predicates that we can use to try to merge this subquery with the outer query.\nfunc (sq *SubQuery) GetMergePredicates() []sqlparser.Expr {\n\tif sq.OuterPredicate != nil {\n\t\treturn append(sq.Predicates, sq.OuterPredicate)\n\t}\n\treturn sq.Predicates\n}\n\nfunc (sq *SubQuery) settle(ctx *plancontext.PlanningContext, outer Operator) Operator {\n\t// We can allow uncorrelated queries even when subquery isn't the top level construct,\n\t// like if its underneath an Aggregator, because they will be pulled out and run separately.\n\tif !sq.TopLevel && sq.correlated {\n\t\tpanic(subqueryNotAtTopErr)\n\t}\n\tif sq.correlated && sq.FilterType != opcode.PulloutExists {\n\t\tpanic(correlatedSubqueryErr)\n\t}\n\tif sq.IsArgument {\n\t\tif len(sq.GetMergePredicates()) > 0 {\n\t\t\t// this means that we have a correlated subquery on our hands\n\t\t\tpanic(correlatedSubqueryErr)\n\t\t}\n\t\tsq.SubqueryValueName = sq.ArgName\n\t\treturn outer\n\t}\n\treturn sq.settleFilter(ctx, outer)\n}\n\nvar (\n\tcorrelatedSubqueryErr = vterrors.VT12001(\"correlated subquery is only supported for EXISTS\")\n\tsubqueryNotAtTopErr   = vterrors.VT12001(\"unmergable subquery can not be inside complex expression\")","sourceCodeStart":196,"sourceCodeEnd":232,"githubUrl":"https://github.com/vitessio/vitess/blob/01a25a7d176f94613b8d59d799f438380a8760e4/go/vt/vtgate/planbuilder/operators/subquery.go#L196-L232","documentation":"VT12001 raised by SubQuery.settle when an unmergable, correlated subquery is not at the top level of the operator tree. Vitess can only execute correlated subqueries in limited positions; if the subquery is nested inside another construct and cannot be merged into the outer route, planning panics with this unsupported-shape error.","triggerScenarios":"A correlated subquery (non-EXISTS filter type) appears inside a non-top-level operator (e.g. under a join/aggregate in a way that cannot be pulled out), so sq.TopLevel is false while sq.correlated is true.","commonSituations":"Queries with correlated subqueries nested inside derived tables, joins, or other nested constructs rather than directly in the WHERE clause of the outer query.","solutions":["Rewrite the query so the correlated subquery appears directly in the outer query's WHERE clause","Convert the correlated subquery to a JOIN against a derived table","Use an EXISTS form of the subquery, which is the supported correlated pattern","Split the query: fetch the subquery result separately and use it as a literal or join input"],"exampleFix":"// before\nSELECT * FROM t WHERE id IN (SELECT x FROM (SELECT * FROM u WHERE u.id = t.id) d)\n// after\nSELECT * FROM t WHERE EXISTS (SELECT 1 FROM u WHERE u.id = t.id)","handlingStrategy":"validation","validationCode":"// reject queries where a correlated subquery is nested inside another construct\n// keep correlated subqueries only at the top-level WHERE clause","typeGuard":null,"tryCatchPattern":"err := doQuery(q)\nif err != nil && strings.Contains(err.Error(), \"VT12001\") && strings.Contains(err.Error(), \"unmergable subquery\") {\n    return doQuery(rewriteSubqueryAtTopLevel(q))\n}","preventionTips":["Keep correlated subqueries directly in the outer WHERE clause","Prefer EXISTS over nested/derived-table-wrapped correlated subqueries","Rewrite correlated subqueries as joins when nesting is needed","Run a SQL compatibility test suite against vtgate before deploying new query shapes"],"tags":["subquery","correlated-subquery","planner","unsupported-query"],"backgroundTag":"unsupported-correlated-subquery","analyzedSha":"01a25a7d176f94613b8d59d799f438380a8760e4","analyzedAt":"2026-09-01T17:28:30.605Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-08T10:18:20.063Z"}