vitessio/vitess · error

VT12001

VT12001

Error message

DML on reference table with join

What it means

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.

Source

Thrown at go/vt/vtgate/planbuilder/operators/delete.go:326

	if len(order) == 0 {
		return op
	}
	return newOrdering(op, order)
}

func updateQueryGraphWithSource(ctx *plancontext.PlanningContext, input Operator, tblID semantics.TableSet, vTbl *vindexes.BaseTable) *vindexes.BaseTable {
	sourceTable, _, _, _, _, err := ctx.VSchema.FindTableOrVindex(vTbl.Source.TableName)
	if err != nil {
		panic(err)
	}
	vTbl = sourceTable
	TopDown(input, TableID, func(op Operator, lhsTables semantics.TableSet, isRoot bool) (Operator, *ApplyResult) {
		qg, ok := op.(*QueryGraph)
		if !ok {
			return op, NoRewrite
		}
		if len(qg.Tables) > 1 {
			panic(vterrors.VT12001("DML on reference table with join"))
		}
		for _, tbl := range qg.Tables {
			if tbl.ID != tblID {
				continue
			}
			tbl.Alias = sqlparser.NewAliasedTableExpr(sqlparser.NewTableNameWithQualifier(vTbl.Name.String(), vTbl.Keyspace.Name), tbl.Alias.As.String())
			tbl.Table, _ = tbl.Alias.TableName()
		}
		return op, Rewrote("change query table point to source table")
	}, func(operator Operator) VisitRule {
		_, ok := operator.(*QueryGraph)
		return VisitRule(ok)
	})
	return vTbl
}

func createFkCascadeOpForDelete(ctx *plancontext.PlanningContext, parentOp Operator, delStmt *sqlparser.Delete, childFks []vindexes.ChildFKInfo, deletedTbl *vindexes.BaseTable) Operator {
	fkChildren := make([]*FkChild, 0, len(childFks))

View on GitHub (pinned to 01a25a7d17)

Solutions

1. Restructure the DELETE so only the reference table (or the source table directly) is touched — no joins.
2. Compute the matching keys first in a SELECT, then issue a single-table DELETE with an IN list.
3. Delete from the source table instead of the reference table.
4. If reference-table join deletes are essential, track/raise the corresponding Vitess feature request.

Example fix

// before
DELETE r FROM ref_table r JOIN users u ON r.user_id = u.id WHERE u.banned = 1;

// after
DELETE FROM ref_table WHERE user_id IN (SELECT id FROM users WHERE banned = 1);

// reject joined deletes against reference tables before sending to Vitess
if isDelete(sql) && extractPrimaryTarget(sql) is referenceTable && tableCountInFromOrJoin(sql) > 1 {
    return errors.New("DELETE on reference table must not involve joins")
}

func isSingleTableDeleteOnReference(sql string, vs *Vschema) bool {
    target := extractDeleteTarget(sql)
    t := vs.Table(target)
    return t != nil && t.Type == "reference" && joinTableCount(sql) == 1
}

if err := execDelete(sql); err != nil {
    if strings.Contains(err.Error(), "VT12001") {
        return rewriteDeleteWithSubquery(sql) // SELECT keys first, then single-table DELETE
    }
    return err
}

When it happens

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

Common situations: 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.

Related errors

• err
• VT13001
• VT09015
• VT12002
• only integer literals are supported

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