vitessio/vitess · error · VitessError

VT12001

VT12001

Error message

VT12001: unsupported: subquery in outer join predicate

What it means

VT12001 is raised by createLeftOuterJoin in go/vt/vtgate/planbuilder/operators/join.go:108 when the ON predicate of a LEFT (outer) join contains a subquery. Subqueries in outer join predicates cannot be safely planned/pushed down by Vitess, so such joins are unsupported and rejected during planning.

Source

Thrown at go/vt/vtgate/planbuilder/operators/join.go:108

		join.Join = sqlparser.LeftJoinType
	case sqlparser.NaturalRightJoinType:
		lhs, rhs = rhs, lhs
		join.Join = sqlparser.NaturalLeftJoinType
	}

	joinOp := &Join{
		binaryOperator: newBinaryOp(lhs, rhs),
		JoinType:       join.Join,
	}

	// mark the RHS as outer tables so we know which columns are nullable
	ctx.OuterTables = ctx.OuterTables.Merge(TableID(rhs))

	// for outer joins we have to be careful with the predicates we use
	var op Operator
	subq, _, _ := getSubQuery(join.Condition.On)
	if subq != nil {
		panic(vterrors.VT12001("subquery in outer join predicate"))
	}
	predicate := join.Condition.On
	sqlparser.RemoveKeyspaceInCol(predicate)
	joinOp.Predicate = predicate
	op = joinOp

	return op
}

func createInnerJoin(ctx *plancontext.PlanningContext, tableExpr *sqlparser.JoinTableExpr, lhs, rhs Operator) Operator {
	op := createJoin(ctx, lhs, rhs)
	return addJoinPredicates(ctx, tableExpr.Condition.On, op)
}

func addJoinPredicates(
	ctx *plancontext.PlanningContext,
	joinPredicate sqlparser.Expr,
	op Operator,

View on GitHub (pinned to 01a25a7d17)

Solutions

1. Move the subquery condition from the ON clause to the WHERE clause (for LEFT JOIN, apply it to the right table's columns on the outer-filtered result)
2. Rewrite as an INNER JOIN if rows without a match can be filtered out anyway
3. Pre-compute the subquery result into a derived table and join that instead

Example fix

// before
SELECT * FROM a LEFT JOIN b ON a.id = b.id AND b.x IN (SELECT x FROM c);
// after
SELECT * FROM a LEFT JOIN b ON a.id = b.id WHERE b.x IS NULL OR b.x IN (SELECT x FROM c);

// Reject subqueries inside ON clauses of outer joins before sending
if (/LEFT\s+(OUTER\s+)?JOIN[\s\S]*?\bON\b[\s\S]*?\(\s*(SELECT|EXISTS)\b/i.test(sql)) {
  throw new Error('subquery in outer join predicate not supported');
}

function onPredicateHasSubquery(joinOnExpr) {
  return /\b(select|exists|in\s*\(\s*select)\b/i.test(joinOnExpr);
}

try {
  await vtgate.execute(sql, args);
} catch (e) {
  if (String(e).includes('VT12001') && /outer join/i.test(String(e))) {
    throw new Error('Move the subquery out of the LEFT JOIN ON clause into WHERE or a derived table');
  }
  throw e;
}

When it happens

Trigger: SELECT ... FROM a LEFT JOIN b ON a.id = b.id AND b.x IN (SELECT ...) — any subquery (getSubQuery returns non-nil) inside the ON condition of an outer join.

Common situations: ORMs that inline EXISTS/IN subqueries into join conditions; queries converted from correlated-update patterns; hand-written filtering logic that belongs in WHERE moved into ON.

Related errors

• unsupported subquery: %v
• Not implemented, post_header_length!=8
• Not implemented, post_header_length==6
• VT13001
• VT09015

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