{"record":{"id":"bad49acc13fa402d","repo":"vitessio/vitess","slug":"vt12001-bad49a","errorCode":"VT12001","errorMessage":"VT12001: unsupported: subquery in outer join predicate","messagePattern":"VT12001: unsupported: subquery in outer join predicate","errorType":"error_code","errorClass":"VitessError","httpStatus":null,"severity":"error","filePath":"go/vt/vtgate/planbuilder/operators/join.go","lineNumber":108,"sourceCode":"\t\tjoin.Join = sqlparser.LeftJoinType\n\tcase sqlparser.NaturalRightJoinType:\n\t\tlhs, rhs = rhs, lhs\n\t\tjoin.Join = sqlparser.NaturalLeftJoinType\n\t}\n\n\tjoinOp := &Join{\n\t\tbinaryOperator: newBinaryOp(lhs, rhs),\n\t\tJoinType:       join.Join,\n\t}\n\n\t// mark the RHS as outer tables so we know which columns are nullable\n\tctx.OuterTables = ctx.OuterTables.Merge(TableID(rhs))\n\n\t// for outer joins we have to be careful with the predicates we use\n\tvar op Operator\n\tsubq, _, _ := getSubQuery(join.Condition.On)\n\tif subq != nil {\n\t\tpanic(vterrors.VT12001(\"subquery in outer join predicate\"))\n\t}\n\tpredicate := join.Condition.On\n\tsqlparser.RemoveKeyspaceInCol(predicate)\n\tjoinOp.Predicate = predicate\n\top = joinOp\n\n\treturn op\n}\n\nfunc createInnerJoin(ctx *plancontext.PlanningContext, tableExpr *sqlparser.JoinTableExpr, lhs, rhs Operator) Operator {\n\top := createJoin(ctx, lhs, rhs)\n\treturn addJoinPredicates(ctx, tableExpr.Condition.On, op)\n}\n\nfunc addJoinPredicates(\n\tctx *plancontext.PlanningContext,\n\tjoinPredicate sqlparser.Expr,\n\top Operator,","sourceCodeStart":90,"sourceCodeEnd":126,"githubUrl":"https://github.com/vitessio/vitess/blob/01a25a7d176f94613b8d59d799f438380a8760e4/go/vt/vtgate/planbuilder/operators/join.go#L90-L126","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["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)","Rewrite as an INNER JOIN if rows without a match can be filtered out anyway","Pre-compute the subquery result into a derived table and join that instead"],"exampleFix":"// before\nSELECT * FROM a LEFT JOIN b ON a.id = b.id AND b.x IN (SELECT x FROM c);\n// after\nSELECT * FROM a LEFT JOIN b ON a.id = b.id WHERE b.x IS NULL OR b.x IN (SELECT x FROM c);","handlingStrategy":"validation","validationCode":"// Reject subqueries inside ON clauses of outer joins before sending\nif (/LEFT\\s+(OUTER\\s+)?JOIN[\\s\\S]*?\\bON\\b[\\s\\S]*?\\(\\s*(SELECT|EXISTS)\\b/i.test(sql)) {\n  throw new Error('subquery in outer join predicate not supported');\n}","typeGuard":"function onPredicateHasSubquery(joinOnExpr) {\n  return /\\b(select|exists|in\\s*\\(\\s*select)\\b/i.test(joinOnExpr);\n}","tryCatchPattern":"try {\n  await vtgate.execute(sql, args);\n} catch (e) {\n  if (String(e).includes('VT12001') && /outer join/i.test(String(e))) {\n    throw new Error('Move the subquery out of the LEFT JOIN ON clause into WHERE or a derived table');\n  }\n  throw e;\n}","preventionTips":["Keep join ON conditions to simple column equality/comparison predicates","Push subquery filters into WHERE clauses or derived tables","Audit ORM-generated SQL for subqueries embedded in join conditions"],"tags":["outer-join","subquery","unsupported"],"backgroundTag":"subquery-in-outer-join","analyzedSha":"01a25a7d176f94613b8d59d799f438380a8760e4","analyzedAt":"2026-09-01T17:28:30.605Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-08T10:18:20.063Z"}