{"record":{"id":"e283dc439c63156c","repo":"vitessio/vitess","slug":"vt12001-e283dc","errorCode":"VT12001","errorMessage":"DML on reference table with join","messagePattern":"DML on reference table with join","errorType":"error_code","errorClass":null,"httpStatus":null,"severity":"error","filePath":"go/vt/vtgate/planbuilder/operators/delete.go","lineNumber":326,"sourceCode":"\tif len(order) == 0 {\n\t\treturn op\n\t}\n\treturn newOrdering(op, order)\n}\n\nfunc updateQueryGraphWithSource(ctx *plancontext.PlanningContext, input Operator, tblID semantics.TableSet, vTbl *vindexes.BaseTable) *vindexes.BaseTable {\n\tsourceTable, _, _, _, _, err := ctx.VSchema.FindTableOrVindex(vTbl.Source.TableName)\n\tif err != nil {\n\t\tpanic(err)\n\t}\n\tvTbl = sourceTable\n\tTopDown(input, TableID, func(op Operator, lhsTables semantics.TableSet, isRoot bool) (Operator, *ApplyResult) {\n\t\tqg, ok := op.(*QueryGraph)\n\t\tif !ok {\n\t\t\treturn op, NoRewrite\n\t\t}\n\t\tif len(qg.Tables) > 1 {\n\t\t\tpanic(vterrors.VT12001(\"DML on reference table with join\"))\n\t\t}\n\t\tfor _, tbl := range qg.Tables {\n\t\t\tif tbl.ID != tblID {\n\t\t\t\tcontinue\n\t\t\t}\n\t\t\ttbl.Alias = sqlparser.NewAliasedTableExpr(sqlparser.NewTableNameWithQualifier(vTbl.Name.String(), vTbl.Keyspace.Name), tbl.Alias.As.String())\n\t\t\ttbl.Table, _ = tbl.Alias.TableName()\n\t\t}\n\t\treturn op, Rewrote(\"change query table point to source table\")\n\t}, func(operator Operator) VisitRule {\n\t\t_, ok := operator.(*QueryGraph)\n\t\treturn VisitRule(ok)\n\t})\n\treturn vTbl\n}\n\nfunc createFkCascadeOpForDelete(ctx *plancontext.PlanningContext, parentOp Operator, delStmt *sqlparser.Delete, childFks []vindexes.ChildFKInfo, deletedTbl *vindexes.BaseTable) Operator {\n\tfkChildren := make([]*FkChild, 0, len(childFks))","sourceCodeStart":308,"sourceCodeEnd":344,"githubUrl":"https://github.com/vitessio/vitess/blob/01a25a7d176f94613b8d59d799f438380a8760e4/go/vt/vtgate/planbuilder/operators/delete.go#L308-L344","documentation":"VT12001 (unsupported feature) is raised when a DELETE on a reference table is planned against a query graph containing more than one table, i.e. the delete involves a join. Reference-table deletes are rewritten to the source table and only single-table deletes are supported; a joined delete would require multi-table semantics the reference redirect cannot express.","triggerScenarios":"DELETE that references (joins) other tables in WHERE, where the primary target is a `REFERENCE`-type table with a source — e.g. `DELETE r FROM ref_table r JOIN other t ON ...` or a WHERE subquery causing a multi-table query graph.","commonSituations":"Application queries deleting from a materialized reference table joined with a lookup table; ORMs generating joined deletes; reference-table source redirect colliding with existing joins in the delete.","solutions":["Restructure the DELETE so only the reference table (or the source table directly) is touched — no joins.","Compute the matching keys first in a SELECT, then issue a single-table DELETE with an IN list.","Delete from the source table instead of the reference table.","If reference-table join deletes are essential, track/raise the corresponding Vitess feature request."],"exampleFix":"// before\nDELETE r FROM ref_table r JOIN users u ON r.user_id = u.id WHERE u.banned = 1;\n\n// after\nDELETE FROM ref_table WHERE user_id IN (SELECT id FROM users WHERE banned = 1);","handlingStrategy":"validation","validationCode":"// reject joined deletes against reference tables before sending to Vitess\nif isDelete(sql) && extractPrimaryTarget(sql) is referenceTable && tableCountInFromOrJoin(sql) > 1 {\n    return errors.New(\"DELETE on reference table must not involve joins\")\n}","typeGuard":"func isSingleTableDeleteOnReference(sql string, vs *Vschema) bool {\n    target := extractDeleteTarget(sql)\n    t := vs.Table(target)\n    return t != nil && t.Type == \"reference\" && joinTableCount(sql) == 1\n}","tryCatchPattern":"if err := execDelete(sql); err != nil {\n    if strings.Contains(err.Error(), \"VT12001\") {\n        return rewriteDeleteWithSubquery(sql) // SELECT keys first, then single-table DELETE\n    }\n    return err\n}","preventionTips":["Never write JOIN-based DELETEs against reference tables","Use `DELETE ... WHERE id IN (SELECT ...)` instead of joins","Keep reference-table consumers read-mostly; route deletes to source tables","Add query-lint rules flagging multi-table DML in CI"],"tags":["vitess","planbuilder","delete","vt12001","reference-table","join"],"backgroundTag":"unsupported-dml-on-reference-table","analyzedSha":"01a25a7d176f94613b8d59d799f438380a8760e4","analyzedAt":"2026-09-01T17:28:30.605Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-08T10:18:20.063Z"}