{"record":{"id":"51dc7f00c295a197","repo":"vitessio/vitess","slug":"vt12001-51dc7f","errorCode":"VT12001","errorMessage":"VT12001: unsupported: REPLACE INTO using select statement","messagePattern":"VT12001: unsupported: REPLACE INTO using select statement","errorType":"error_code","errorClass":"VitessError","httpStatus":null,"severity":"error","filePath":"go/vt/vtgate/planbuilder/operators/insert.go","lineNumber":134,"sourceCode":"\n\tdeleteBeforeInsert := false\n\tif ins.Action == sqlparser.ReplaceAct &&\n\t\t(ctx.SemTable.ForeignKeysPresent() || vTbl.Keyspace.Sharded) &&\n\t\t(len(vTbl.PrimaryKey) > 0 || len(vTbl.UniqueKeys) > 0) {\n\t\t// this needs a delete before insert as there can be row clash which needs to be deleted first.\n\t\tins.Action = sqlparser.InsertAct\n\t\tdeleteBeforeInsert = true\n\t}\n\n\tinsOp := checkAndCreateInsertOperator(ctx, ins, vTbl, routing)\n\n\tif !deleteBeforeInsert {\n\t\treturn insOp\n\t}\n\n\trows, isRows := ins.Rows.(sqlparser.Values)\n\tif !isRows {\n\t\tpanic(vterrors.VT12001(\"REPLACE INTO using select statement\"))\n\t}\n\n\tpkCompExpr := pkCompExpression(vTbl, ins, rows)\n\tuniqKeyCompExprs := uniqKeyCompExpressions(vTbl, ins, rows)\n\twhereExpr := getWhereCondExpr(append(uniqKeyCompExprs, pkCompExpr))\n\n\tdelStmt := &sqlparser.Delete{\n\t\tComments:   ins.Comments,\n\t\tTableExprs: sqlparser.TableExprs{sqlparser.Clone(ins.Table)},\n\t\tWhere:      sqlparser.NewWhere(sqlparser.WhereClause, whereExpr),\n\t}\n\tdelOp := createOpFromStmt(ctx, delStmt, false, \"\")\n\treturn &Sequential{Sources: []Operator{delOp, insOp}}\n}\n\nfunc checkAndCreateInsertOperator(ctx *plancontext.PlanningContext, ins *sqlparser.Insert, vTbl *vindexes.BaseTable, routing Routing) Operator {\n\tinsOp := createInsertOperator(ctx, ins, vTbl, routing)\n","sourceCodeStart":116,"sourceCodeEnd":152,"githubUrl":"https://github.com/vitessio/vitess/blob/01a25a7d176f94613b8d59d799f438380a8760e4/go/vt/vtgate/planbuilder/operators/insert.go#L116-L152","documentation":"VT12001: REPLACE INTO that takes its rows from a SELECT statement is not supported when the table requires delete-before-insert handling. createOperatorFromInsert must build a plan that deletes existing rows before inserting, and that construction only works with literal VALUES rows; a select-statement source is rejected.","triggerScenarios":"`REPLACE INTO tbl SELECT ...` (ins.Rows is not sqlparser.Values) on a sharded/managed table where deleteBeforeInsert is true — i.e. the table has primary key or unique key handling requiring the delete-before-insert plan.","commonSituations":"Migrating MySQL applications to Vitess that use REPLACE INTO ... SELECT for data copies; scripts that dedupe rows via REPLACE INTO with a SELECT source on sharded keyspaces.","solutions":["Rewrite as two statements: INSERT INTO ... SELECT, preceded by explicit DELETEs for conflicting keys","Load the SELECT results into the application and issue REPLACE INTO with literal VALUES","Use INSERT ... ON DUPLICATE KEY UPDATE instead of REPLACE if rows should update rather than delete+insert","If the table does not need delete-before-insert behavior, verify table/keyspace configuration (this path is reached only when deleteBeforeInsert is set)"],"exampleFix":"-- before\nREPLACE INTO t SELECT * FROM t2;\n-- after\nINSERT INTO t (a,b) SELECT a,b FROM t2\n  ON DUPLICATE KEY UPDATE b = VALUES(b);","handlingStrategy":"validation","validationCode":"// Pre-check: REPLACE INTO must use VALUES, not SELECT, on sharded tables\nfunc replaceUsesSelect(stmt sqlparser.Statement) bool {\n  ins, ok := stmt.(*sqlparser.Insert)\n  if !ok || ins.Action != sqlparser.ReplaceAct { return false }\n  _, isValues := ins.Rows.(sqlparser.Values)\n  return !isValues\n}","typeGuard":"rows, isValues := ins.Rows.(sqlparser.Values) // isValues must be true for REPLACE INTO plans","tryCatchPattern":"_, err := db.ExecContext(ctx, q)\nif err != nil && strings.Contains(err.Error(), \"VT12001\") && strings.Contains(err.Error(), \"REPLACE INTO\") {\n  // fall back to DELETE + INSERT ... SELECT strategy\n}","preventionTips":["Use INSERT ... ON DUPLICATE KEY UPDATE instead of REPLACE INTO in sharded schemas","Split REPLACE INTO ... SELECT into DELETE + INSERT ... SELECT statements","Audit application SQL for REPLACE usage before sharding","Keep REPLACE INTO only on unsharded single-shard tables"],"tags":["go","insert","replace-into","unsupported-query","sharding"],"backgroundTag":"unsupported-replace-into","analyzedSha":"01a25a7d176f94613b8d59d799f438380a8760e4","analyzedAt":"2026-09-01T17:28:30.605Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-08T10:18:20.063Z"}