What causes "multi shard UPDATE with LIMIT" in vitessio/vitess?

Rewrite the DML to select the primary keys first, then update/delete by key without LIMIT

VT12001

VT12001

Error message

multi shard UPDATE with LIMIT

What it means

VT12001 signals an unsupported multi-shard operation. When building the vindex table for a DML with LIMIT (e.g. `UPDATE ... LIMIT n` or `DELETE ... LIMIT n`), the planner requires the table expression to be a simple table name; a non-table expression (subquery/derived table) combined with LIMIT would spread across shards, which is unsupported.

Source

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

	vindexTable := tableInfo.GetVindexTable()
	if tableInfo.GetVindexTable().Type == vindexes.TypeReference && vindexTable.Source != nil {
		sourceTable, _, _, _, _, err := ctx.VSchema.FindTableOrVindex(vindexTable.Source.TableName)
		if err != nil {
			panic(err)
		}
		vindexTable = sourceTable
		refTbl := sqlparser.NewAliasedTableExpr(vindexTable.GetTableName(), "")
		ins.Table.Expr = refTbl.Expr
		// We don't need to process the alias because you cannot define aliases for inserts.
	}

	if !vindexTable.Keyspace.Sharded {
		return vindexTable, &AnyShardRouting{keyspace: vindexTable.Keyspace}
	}

	tblName, ok := table.Alias.Expr.(sqlparser.TableName)
	if !ok {
		panic(vterrors.VT12001("multi shard UPDATE with LIMIT"))
	}

	_, _, _, typ, dest, err := ctx.VSchema.FindTableOrVindex(tblName)
	if err != nil {
		panic(err)
	}
	if dest == nil {
		routing := &ShardedRouting{
			keyspace:    vindexTable.Keyspace,
			RouteOpCode: engine.Scatter,
		}
		return vindexTable, routing
	}

	if typ != topodatapb.TabletType_PRIMARY {
		panic(vterrors.VT09002(dmlType))
	}

View on GitHub (pinned to 01a25a7d17)

Solutions

Rewrite the DML to select the primary keys first, then update/delete by key without LIMIT
Remove the LIMIT clause if it is not essential
Move the statement to an unsharded keyspace or execute it directly against the shard via a targeted workflow

Example fix

// before
UPDATE t SET a = 1 WHERE b = 2 LIMIT 10; -- VT12001
// after
UPDATE t SET a = 1 WHERE id IN (SELECT id FROM t WHERE b = 2 LIMIT 10) -- executed appropriately, or select ids first then update by id

Defensive patterns

Strategy: validation

Validate before calling

// detect DML-with-LIMIT on sharded keyspaces before executing
q := strings.ToLower(statement)
if strings.HasPrefix(q, "update") || strings.HasPrefix(q, "delete") {
    if strings.Contains(q, " limit ") && keyspaceIsSharded {
        // rewrite to key-based DML before sending
    }
}

Try / catch

_, err := conn.Execute("UPDATE t SET a=1 WHERE b=2 LIMIT 10", nil)
if err != nil && strings.Contains(err.Error(), "VT12001") {
    // fall back: select ids first, then update by id without LIMIT
}

Prevention

Avoid UPDATE/DELETE ... LIMIT on sharded keyspaces
Select matching primary keys first, then modify by key
Keep the target table a simple table name for DML (no derived tables)

When it happens

Trigger: Running `UPDATE t ... LIMIT n` (or DELETE with LIMIT) on a sharded keyspace where the FROM/UPDATE target is not a plain TableName — e.g. `UPDATE (SELECT ...) AS x ... LIMIT n`.

Common situations: Applications ported from single-MySQL setups using UPDATE/DELETE with LIMIT on sharded tables; ORM-generated DML with LIMIT clauses.

Related errors

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