VT12001

VT12001

Error message

unmergable subquery can not be inside complex expression

What it means

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.

Source

Thrown at go/vt/vtgate/planbuilder/operators/subquery.go:214

}

func (sq *SubQuery) GetSelectExprs(ctx *plancontext.PlanningContext) []sqlparser.SelectExpr {
	return sq.Outer.GetSelectExprs(ctx)
}

// GetMergePredicates returns the predicates that we can use to try to merge this subquery with the outer query.
func (sq *SubQuery) GetMergePredicates() []sqlparser.Expr {
	if sq.OuterPredicate != nil {
		return append(sq.Predicates, sq.OuterPredicate)
	}
	return sq.Predicates
}

func (sq *SubQuery) settle(ctx *plancontext.PlanningContext, outer Operator) Operator {
	// We can allow uncorrelated queries even when subquery isn't the top level construct,
	// like if its underneath an Aggregator, because they will be pulled out and run separately.
	if !sq.TopLevel && sq.correlated {
		panic(subqueryNotAtTopErr)
	}
	if sq.correlated && sq.FilterType != opcode.PulloutExists {
		panic(correlatedSubqueryErr)
	}
	if sq.IsArgument {
		if len(sq.GetMergePredicates()) > 0 {
			// this means that we have a correlated subquery on our hands
			panic(correlatedSubqueryErr)
		}
		sq.SubqueryValueName = sq.ArgName
		return outer
	}
	return sq.settleFilter(ctx, outer)
}

var (
	correlatedSubqueryErr = vterrors.VT12001("correlated subquery is only supported for EXISTS")
	subqueryNotAtTopErr   = vterrors.VT12001("unmergable subquery can not be inside complex expression")

View on GitHub (pinned to 01a25a7d17)

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

Example fix

// before
SELECT * FROM t WHERE id IN (SELECT x FROM (SELECT * FROM u WHERE u.id = t.id) d)
// after
SELECT * FROM t WHERE EXISTS (SELECT 1 FROM u WHERE u.id = t.id)

Defensive patterns

Strategy: validation

Validate before calling

// reject queries where a correlated subquery is nested inside another construct
// keep correlated subqueries only at the top-level WHERE clause

Try / catch

err := doQuery(q)
if err != nil && strings.Contains(err.Error(), "VT12001") && strings.Contains(err.Error(), "unmergable subquery") {
    return doQuery(rewriteSubqueryAtTopLevel(q))
}

Prevention

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

When it happens

Trigger: 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.

Common situations: Queries with correlated subqueries nested inside derived tables, joins, or other nested constructs rather than directly in the WHERE clause of the outer query.

Related errors

AI-assisted analysis of vitessio/vitess@01a25a7d17 (2026-09-01). Data as JSON: /api/errors/1ee2faad1617bebb. Report an issue: GitHub.