Repository: vitessio/vitess
Pull request #8701 (2021-08-27 16:23:47)

Fixing error on deletes from owning table for a lookup being populated by CreateLookupVindex after a SwitchWrites
## Description

After creating two keyspaces and a lookup Vindex using `CreateLookupVindex `, every delete query that uses the same lookup Vindex starts failing. Below are: a sample query using the column `c2` (which is our lookup Vindex); and the error that we got after executing the query.

```
mysql> delete from table where c2 = 1;
ERROR 1105 (HY000): cannot map vindex to unique keyspace id: DestinationKeyRange(-)
```

The reason behind this error is the following: the engine tries to execute a `delete equal` however, the lookup Vindex used to resolve the shard is in `write_only` mode, meaning we cannot resolve to which shard this query needs to be routed. The default behavior, when this happened, was to return a slice of `DestinationKeyRange` keyspace IDs, which is not a single shard, thus failing for `delete equal`. This pull request changes this behavior by transforming `delete unique` into `delete scatter` when the query is made using a lookup Vindex on `write_only` mode.

## Related Issue(s)

## Checklist
- [ ] Should this PR be backported?
- [x] Tests were added or are not required
- [x] Documentation was added or is not required



Patch:
diff --git a/go/vt/vtgate/planbuilder/dml.go b/go/vt/vtgate/planbuilder/dml.go
index 8a85dd39f8..f6e9323a92 100644
--- a/go/vt/vtgate/planbuilder/dml.go
+++ b/go/vt/vtgate/planbuilder/dml.go
@@ -51,6 +51,10 @@ func getDMLRouting(where *sqlparser.Where, table *vindexes.Table) (engine.DMLOpc
 			opcode := engine.Equal
 			if pv.IsList() {
 				opcode = engine.In
+			} else if lu, isLu := single.(vindexes.LookupBackfill); isLu && lu.IsBackfilling() {
+				// Checking if the Vindex is currently backfilling or not, if it isn't we can read from the vindex table
+				// and we will be able to do a delete equal. Otherwise, we continue to look for next best vindex.
+				continue
 			}
 			return opcode, ksidVindex, ksidCol, single, []sqltypes.PlanValue{pv}, nil
 		}
diff --git a/go/vt/vtgate/vindexes/consistent_lookup.go b/go/vt/vtgate/vindexes/consistent_lookup.go
index 82c3889755..d06acece44 100644
--- a/go/vt/vtgate/vindexes/consistent_lookup.go
+++ b/go/vt/vtgate/vindexes/consistent_lookup.go
@@ -384,3 +384,8 @@ func (lu *clCommon) addWhere(buf *bytes.Buffer, cols []string) {
 		buf.WriteString(column + " = :" + lu.lkp.FromColumns[colIdx])
 	}
 }
+
+// IsBackfilling implements the LookupBackfill interface
+func (lu *ConsistentLookupUnique) IsBackfilling() bool {
+	return lu.writeOnly
+}
diff --git a/go/vt/vtgate/vindexes/lookup.go b/go/vt/vtgate/vindexes/lookup.go
index 589f594270..d06881e8a1 100644
--- a/go/vt/vtgate/vindexes/lookup.go
+++ b/go/vt/vtgate/vindexes/lookup.go
@@ -286,3 +286,8 @@ func (lu *LookupUnique) Delete(vcursor VCursor, rowsColValues [][]sqltypes.Value
 func (lu *LookupUnique) MarshalJSON() ([]byte, error) {
 	return json.Marshal(lu.lkp)
 }
+
+// IsBackfilling implements the LookupBackfill interface
+func (lu *LookupUnique) IsBackfilling() bool {
+	return lu.writeOnly
+}
diff --git a/go/vt/vtgate/vindexes/lookup_hash.go b/go/vt/vtgate/vindexes/lookup_hash.go
index 2f76db4df7..df34f84112 100644
--- a/go/vt/vtgate/vindexes/lookup_hash.go
+++ b/go/vt/vtgate/vindexes/lookup_hash.go
@@ -344,3 +344,8 @@ func (lhu *LookupHashUnique) Update(vcursor VCursor, oldValues []sqltypes.Value,
 func (lhu *LookupHashUnique) MarshalJSON() ([]byte, error) {
 	return json.Marshal(lhu.lkp)
 }
+
+// IsBackfilling implements the LookupBackfill interface
+func (lhu *LookupHashUnique) IsBackfilling() bool {
+	return lhu.writeOnly
+}
diff --git a/go/vt/vtgate/vindexes/lookup_unicodeloosemd5_hash.go b/go/vt/vtgate/vindexes/lookup_unicodeloosemd5_hash.go
index d3a2c8b00c..66a8c34379 100644
--- a/go/vt/vtgate/vindexes/lookup_unicodeloosemd5_hash.go
+++ b/go/vt/vtgate/vindexes/lookup_unicodeloosemd5_hash.go
@@ -381,6 +381,11 @@ func (lhu *LookupUnicodeLooseMD5HashUnique) MarshalJSON() ([]byte, error) {
 	return json.Marshal(lhu.lkp)
 }
 
+// IsBackfilling implements the LookupBackfill interface
+func (lhu *LookupUnicodeLooseMD5HashUnique) IsBackfilling() bool {
+	return lhu.writeOnly
+}
+
 func unicodeHashValue(value sqltypes.Value) (sqltypes.Value, error) {
 	hash, err := unicodeHash(vMD5Hash, value)
 	if err != nil {
diff --git a/go/vt/vtgate/vindexes/vindex.go b/go/vt/vtgate/vindexes/vindex.go
index 6152d75bbe..45564f3b2b 100644
--- a/go/vt/vtgate/vindexes/vindex.go
+++ b/go/vt/vtgate/vindexes/vindex.go
@@ -128,6 +128,11 @@ type Lookup interface {
 	Update(vc VCursor, oldValues []sqltypes.Value, ksid []byte, newValues []sqltypes.Value) error
 }
 
+// LookupBackfill interfaces all lookup vindexes that can backfill rows, such as LookupUnique.
+type LookupBackfill interface {
+	IsBackfilling() bool
+}
+
 // WantOwnerInfo defines the interface that a vindex must
 // satisfy to request info about the owner table. This information can
 // be used to query the owner's table for the owning row's presence.


Test patch:
diff --git a/go/vt/vtgate/executor_dml_test.go b/go/vt/vtgate/executor_dml_test.go
index 13350aafda..d9a19cb994 100644
--- a/go/vt/vtgate/executor_dml_test.go
+++ b/go/vt/vtgate/executor_dml_test.go
@@ -493,6 +493,315 @@ func TestDeleteScatter(t *testing.T) {
 	}
 }
 
+func TestUpdateEqualWithWriteOnlyLookupUniqueVindex(t *testing.T) {
+	res := []*sqltypes.Result{sqltypes.MakeTestResult(
+		sqltypes.MakeTestFields("id|wo_lu_col|lu_col|t2_lu_vdx", "int64|int64|int64|int64"),
+		"1|2|1|0",
+	)}
+	executor, sbc1, sbc2, sbcLookup := createCustomExecutorSetValues(executorVSchema, res)
+
+	_, err := executorExec(executor, "update t2_wo_lookup set lu_col = 5 where wo_lu_col = 2", nil)
+	require.NoError(t, err)
+	wantQueries := []*querypb.BoundQuery{
+		{
+			Sql:           "select id, wo_lu_col, lu_col, lu_col = 5 from t2_wo_lookup where wo_lu_col = 2 for update",
+			BindVariables: map[string]*querypb.BindVariable{},
+		}, {
+			Sql:           "update t2_wo_lookup set lu_col = 5 where wo_lu_col = 2",
+			BindVariables: map[string]*querypb.BindVariable{},
+		}}
+
+	utils.MustMatch(t, wantQueries, sbc1.Queries)
+	utils.MustMatch(t, wantQueries, sbc2.Queries)
+
+	bq1 := &querypb.BoundQuery{
+		Sql: "delete from lu_idx where lu_col = :lu_col and keyspace_id = :keyspace_id",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id": sqltypes.Uint64BindVariable(1),
+			"lu_col":      sqltypes.Int64BindVariable(1),
+		},
+	}
+	bq2 := &querypb.BoundQuery{
+		Sql: "insert into lu_idx(lu_col, keyspace_id) values (:lu_col_0, :keyspace_id_0)",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id_0": sqltypes.Uint64BindVariable(1),
+			"lu_col_0":      sqltypes.Int64BindVariable(5),
+		},
+	}
+	lookWant := []*querypb.BoundQuery{bq1, bq2, bq1, bq2, bq1, bq2, bq1, bq2, bq1, bq2, bq1, bq2, bq1, bq2, bq1, bq2}
+	utils.MustMatch(t, lookWant, sbcLookup.Queries)
+}
+
+func TestUpdateEqualWithMultipleLookupVindex(t *testing.T) {
+	executor, sbc1, sbc2, sbcLookup := createCustomExecutorSetValues(executorVSchema, nil)
+
+	sbcLookup.SetResults([]*sqltypes.Result{sqltypes.MakeTestResult(
+		sqltypes.MakeTestFields("lu_col|keyspace_id", "int64|varbinary"),
+		"1|1",
+	)})
+
+	sbc1.SetResults([]*sqltypes.Result{sqltypes.MakeTestResult(
+		sqltypes.MakeTestFields("id|wo_lu_col|lu_col|t2_lu_vdx", "int64|int64|int64|int64"),
+		"1|2|1|0",
+	)})
+
+	_, err := executorExec(executor, "update t2_wo_lookup set lu_col = 5 where wo_lu_col = 2 and lu_col = 1", nil)
+	require.NoError(t, err)
+	wantQueries := []*querypb.BoundQuery{
+		{
+			Sql:           "select id, wo_lu_col, lu_col, lu_col = 5 from t2_wo_lookup where wo_lu_col = 2 and lu_col = 1 for update",
+			BindVariables: map[string]*querypb.BindVariable{},
+		}, {
+			Sql:           "update t2_wo_lookup set lu_col = 5 where wo_lu_col = 2 and lu_col = 1",
+			BindVariables: map[string]*querypb.BindVariable{},
+		}}
+
+	vars, _ := sqltypes.BuildBindVariable([]interface{}{
+		sqltypes.NewInt64(1),
+	})
+	lookWant := []*querypb.BoundQuery{{
+		Sql: "select lu_col, keyspace_id from lu_idx where lu_col in ::lu_col for update",
+		BindVariables: map[string]*querypb.BindVariable{
+			"lu_col": vars,
+		},
+	}, {
+		Sql: "delete from lu_idx where lu_col = :lu_col and keyspace_id = :keyspace_id",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id": sqltypes.Uint64BindVariable(1),
+			"lu_col":      sqltypes.Int64BindVariable(1),
+		},
+	}, {
+		Sql: "insert into lu_idx(lu_col, keyspace_id) values (:lu_col_0, :keyspace_id_0)",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id_0": sqltypes.Uint64BindVariable(1),
+			"lu_col_0":      sqltypes.Int64BindVariable(5),
+		},
+	}}
+	utils.MustMatch(t, lookWant, sbcLookup.Queries)
+	utils.MustMatch(t, wantQueries, sbc1.Queries)
+	assert.Nil(t, sbc2.Queries)
+}
+
+func TestUpdateUseHigherCostVindexIfBackfilling(t *testing.T) {
+	executor, sbc1, sbc2, sbcLookup := createCustomExecutorSetValues(executorVSchema, nil)
+
+	sbcLookup.SetResults([]*sqltypes.Result{sqltypes.MakeTestResult(
+		sqltypes.MakeTestFields("lu_col|keyspace_id", "int64|varbinary"),
+		"1|1",
+		"2|1",
+	)})
+
+	sbc1.SetResults([]*sqltypes.Result{sqltypes.MakeTestResult(
+		sqltypes.MakeTestFields("id|wo_lu_col|lu_col|t2_lu_vdx", "int64|int64|int64|int64"),
+		"1|2|1|0",
+		"1|2|2|0",
+	)})
+
+	_, err := executorExec(executor, "update t2_wo_lookup set lu_col = 5 where wo_lu_col = 2 and lu_col in (1, 2)", nil)
+	require.NoError(t, err)
+	wantQueries := []*querypb.BoundQuery{
+		{
+			Sql:           "select id, wo_lu_col, lu_col, lu_col = 5 from t2_wo_lookup where wo_lu_col = 2 and lu_col in (1, 2) for update",
+			BindVariables: map[string]*querypb.BindVariable{},
+		}, {
+			Sql:           "update t2_wo_lookup set lu_col = 5 where wo_lu_col = 2 and lu_col in (1, 2)",
+			BindVariables: map[string]*querypb.BindVariable{},
+		}}
+
+	vars, _ := sqltypes.BuildBindVariable([]interface{}{
+		sqltypes.NewInt64(1),
+		sqltypes.NewInt64(2),
+	})
+	lookWant := []*querypb.BoundQuery{{
+		Sql: "select lu_col, keyspace_id from lu_idx where lu_col in ::lu_col for update",
+		BindVariables: map[string]*querypb.BindVariable{
+			"lu_col": vars,
+		},
+	}, {
+		Sql: "delete from lu_idx where lu_col = :lu_col and keyspace_id = :keyspace_id",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id": sqltypes.Uint64BindVariable(1),
+			"lu_col":      sqltypes.Int64BindVariable(1),
+		},
+	}, {
+		Sql: "insert into lu_idx(lu_col, keyspace_id) values (:lu_col_0, :keyspace_id_0)",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id_0": sqltypes.Uint64BindVariable(1),
+			"lu_col_0":      sqltypes.Int64BindVariable(5),
+		},
+	}, {
+		Sql: "delete from lu_idx where lu_col = :lu_col and keyspace_id = :keyspace_id",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id": sqltypes.Uint64BindVariable(1),
+			"lu_col":      sqltypes.Int64BindVariable(2),
+		},
+	}, {
+		Sql: "insert into lu_idx(lu_col, keyspace_id) values (:lu_col_0, :keyspace_id_0)",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id_0": sqltypes.Uint64BindVariable(1),
+			"lu_col_0":      sqltypes.Int64BindVariable(5),
+		},
+	}}
+	utils.MustMatch(t, lookWant, sbcLookup.Queries)
+	utils.MustMatch(t, wantQueries, sbc1.Queries)
+	assert.Nil(t, sbc2.Queries)
+}
+
+func TestDeleteEqualWithWriteOnlyLookupUniqueVindex(t *testing.T) {
+	res := []*sqltypes.Result{sqltypes.MakeTestResult(
+		sqltypes.MakeTestFields("id|wo_lu_col|lu_col", "int64|int64|int64"),
+		"1|1|1",
+	)}
+	executor, sbc1, sbc2, sbcLookup := createCustomExecutorSetValues(executorVSchema, res)
+
+	_, err := executorExec(executor, "delete from t2_wo_lookup where wo_lu_col = 1", nil)
+	require.NoError(t, err)
+	wantQueries := []*querypb.BoundQuery{
+		{
+			Sql:           "select id, wo_lu_col, lu_col from t2_wo_lookup where wo_lu_col = 1 for update",
+			BindVariables: map[string]*querypb.BindVariable{},
+		}, {
+			Sql:           "delete from t2_wo_lookup where wo_lu_col = 1",
+			BindVariables: map[string]*querypb.BindVariable{},
+		}}
+
+	bq1 := &querypb.BoundQuery{
+		Sql: "delete from wo_lu_idx where wo_lu_col = :wo_lu_col and keyspace_id = :keyspace_id",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id": {Type: querypb.Type_VARBINARY, Value: []byte("\x16k@\xb4J\xbaK\xd6")},
+			"wo_lu_col":   sqltypes.Int64BindVariable(1),
+		},
+	}
+	bq2 := &querypb.BoundQuery{
+		Sql: "delete from lu_idx where lu_col = :lu_col and keyspace_id = :keyspace_id",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id": sqltypes.Uint64BindVariable(1),
+			"lu_col":      sqltypes.Int64BindVariable(1),
+		},
+	}
+	lookWant := []*querypb.BoundQuery{bq1, bq2, bq1, bq2, bq1, bq2, bq1, bq2, bq1, bq2, bq1, bq2, bq1, bq2, bq1, bq2}
+	utils.MustMatch(t, lookWant, sbcLookup.Queries)
+	utils.MustMatch(t, wantQueries, sbc1.Queries)
+	utils.MustMatch(t, wantQueries, sbc2.Queries)
+}
+
+func TestDeleteEqualWithMultipleLookupVindex(t *testing.T) {
+	executor, sbc1, sbc2, sbcLookup := createCustomExecutorSetValues(executorVSchema, nil)
+
+	sbcLookup.SetResults([]*sqltypes.Result{sqltypes.MakeTestResult(
+		sqltypes.MakeTestFields("lu_col|keyspace_id", "int64|varbinary"),
+		"1|1",
+	)})
+
+	sbc1.SetResults([]*sqltypes.Result{sqltypes.MakeTestResult(
+		sqltypes.MakeTestFields("id|wo_lu_col|lu_col", "int64|int64|int64"),
+		"1|1|1",
+	)})
+
+	_, err := executorExec(executor, "delete from t2_wo_lookup where wo_lu_col = 1 and lu_col = 1", nil)
+	require.NoError(t, err)
+	wantQueries := []*querypb.BoundQuery{
+		{
+			Sql:           "select id, wo_lu_col, lu_col from t2_wo_lookup where wo_lu_col = 1 and lu_col = 1 for update",
+			BindVariables: map[string]*querypb.BindVariable{},
+		}, {
+			Sql:           "delete from t2_wo_lookup where wo_lu_col = 1 and lu_col = 1",
+			BindVariables: map[string]*querypb.BindVariable{},
+		}}
+
+	vars, _ := sqltypes.BuildBindVariable([]interface{}{
+		sqltypes.NewInt64(1),
+	})
+	lookWant := []*querypb.BoundQuery{{
+		Sql: "select lu_col, keyspace_id from lu_idx where lu_col in ::lu_col for update",
+		BindVariables: map[string]*querypb.BindVariable{
+			"lu_col": vars,
+		},
+	}, {
+		Sql: "delete from wo_lu_idx where wo_lu_col = :wo_lu_col and keyspace_id = :keyspace_id",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id": {Type: querypb.Type_VARBINARY, Value: []byte("\x16k@\xb4J\xbaK\xd6")},
+			"wo_lu_col":   sqltypes.Int64BindVariable(1),
+		},
+	}, {
+		Sql: "delete from lu_idx where lu_col = :lu_col and keyspace_id = :keyspace_id",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id": sqltypes.Uint64BindVariable(1),
+			"lu_col":      {Type: querypb.Type_INT64, Value: []byte("1")},
+		},
+	}}
+	utils.MustMatch(t, lookWant, sbcLookup.Queries)
+
+	utils.MustMatch(t, wantQueries, sbc1.Queries)
+	assert.Nil(t, sbc2.Queries)
+}
+
+func TestDeleteUseHigherCostVindexIfBackfilling(t *testing.T) {
+	executor, sbc1, sbc2, sbcLookup := createCustomExecutorSetValues(executorVSchema, nil)
+
+	sbcLookup.SetResults([]*sqltypes.Result{sqltypes.MakeTestResult(
+		sqltypes.MakeTestFields("lu_col|keyspace_id", "int64|varbinary"),
+		"1|1",
+		"2|1",
+	)})
+
+	sbc1.SetResults([]*sqltypes.Result{sqltypes.MakeTestResult(
+		sqltypes.MakeTestFields("id|wo_lu_col|lu_col", "int64|int64|int64"),
+		"1|1|1",
+		"1|1|2",
+	)})
+
+	_, err := executorExec(executor, "delete from t2_wo_lookup where wo_lu_col = 1 and lu_col in (1, 2)", nil)
+	require.NoError(t, err)
+	wantQueries := []*querypb.BoundQuery{
+		{
+			Sql:           "select id, wo_lu_col, lu_col from t2_wo_lookup where wo_lu_col = 1 and lu_col in (1, 2) for update",
+			BindVariables: map[string]*querypb.BindVariable{},
+		}, {
+			Sql:           "delete from t2_wo_lookup where wo_lu_col = 1 and lu_col in (1, 2)",
+			BindVariables: map[string]*querypb.BindVariable{},
+		}}
+
+	vars, _ := sqltypes.BuildBindVariable([]interface{}{
+		sqltypes.NewInt64(1),
+		sqltypes.NewInt64(2),
+	})
+	lookWant := []*querypb.BoundQuery{{
+		Sql: "select lu_col, keyspace_id from lu_idx where lu_col in ::lu_col for update",
+		BindVariables: map[string]*querypb.BindVariable{
+			"lu_col": vars,
+		},
+	}, {
+		Sql: "delete from wo_lu_idx where wo_lu_col = :wo_lu_col and keyspace_id = :keyspace_id",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id": {Type: querypb.Type_VARBINARY, Value: []byte("\x16k@\xb4J\xbaK\xd6")},
+			"wo_lu_col":   sqltypes.Int64BindVariable(1),
+		},
+	}, {
+		Sql: "delete from lu_idx where lu_col = :lu_col and keyspace_id = :keyspace_id",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id": sqltypes.Uint64BindVariable(1),
+			"lu_col":      sqltypes.Int64BindVariable(1),
+		},
+	}, {
+		Sql: "delete from wo_lu_idx where wo_lu_col = :wo_lu_col and keyspace_id = :keyspace_id",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id": {Type: querypb.Type_VARBINARY, Value: []byte("\x16k@\xb4J\xbaK\xd6")},
+			"wo_lu_col":   sqltypes.Int64BindVariable(1),
+		},
+	}, {
+		Sql: "delete from lu_idx where lu_col = :lu_col and keyspace_id = :keyspace_id",
+		BindVariables: map[string]*querypb.BindVariable{
+			"keyspace_id": sqltypes.Uint64BindVariable(1),
+			"lu_col":      sqltypes.Int64BindVariable(2),
+		},
+	}}
+	utils.MustMatch(t, lookWant, sbcLookup.Queries)
+
+	utils.MustMatch(t, wantQueries, sbc1.Queries)
+	assert.Nil(t, sbc2.Queries)
+}
+
 func TestDeleteByDestination(t *testing.T) {
 	executor, sbc1, sbc2, _ := createLegacyExecutorEnv()
 	// This query is not supported in v3, so we know for sure is taking the DeleteByDestination route
diff --git a/go/vt/vtgate/executor_framework_test.go b/go/vt/vtgate/executor_framework_test.go
index 550d40ed29..ced654d46a 100644
--- a/go/vt/vtgate/executor_framework_test.go
+++ b/go/vt/vtgate/executor_framework_test.go
@@ -111,7 +111,26 @@ var executorVSchema = `
         		"from": "unq_col",
         		"to": "keyspace_id"
       		},
-      	"owner": "t1"
+      		"owner": "t1"
+    	},
+		"t2_wo_lu_vdx": {
+      		"type": "lookup_unique",
+      		"params": {
+        		"table": "TestUnsharded.wo_lu_idx",
+        		"from": "wo_lu_col",
+        		"to": "keyspace_id",
+				"write_only": "true"
+      		},
+      		"owner": "t2_wo_lookup"
+    	},
+		"t2_lu_vdx": {
+      		"type": "lookup_hash_unique",
+      		"params": {
+        		"table": "TestUnsharded.lu_idx",
+        		"from": "lu_col",
+        		"to": "keyspace_id"
+      		},
+      		"owner": "t2_wo_lookup"
     	}
 	},
 	"tables": {
@@ -268,7 +287,23 @@ var executorVSchema = `
 				  	"name": "hash_index"
 				}
 			]
-		}
+		},
+		"t2_wo_lookup": {
+      		"column_vindexes": [
+				{
+				  	"column": "id",
+				  	"name": "hash_index"
+				},
+				{
+				  	"column": "wo_lu_col",
+				  	"name": "t2_wo_lu_vdx"
+				},
+				{
+				  	"column": "lu_col",
+				  	"name": "t2_lu_vdx"
+				}
+            ]
+    	}
 	}
 }
 `
@@ -300,6 +335,8 @@ var unshardedVSchema = `
 				"sequence": "user_seq"
 			}
 		},
+		"wo_lu_idx": {},
+		"lu_idx": {},
 		"simple": {}
 	}
 }
@@ -461,6 +498,31 @@ func createCustomExecutor(vschema string) (executor *Executor, sbc1, sbc2, sbclo
 	return executor, sbc1, sbc2, sbclookup
 }
 
+func createCustomExecutorSetValues(vschema string, values []*sqltypes.Result) (executor *Executor, sbc1, sbc2, sbclookup *sandboxconn.SandboxConn) {
+	cell := "aa"
+	hc := discovery.NewFakeLegacyHealthCheck()
+	s := createSandbox("TestExecutor")
+	s.VSchema = vschema
+	serv := newSandboxForCells([]string{cell})
+	resolver := newTestLegacyResolver(hc, serv, cell)
+	shards := []string{"-20", "20-40", "40-60", "60-80", "80-a0", "a0-c0", "c0-e0", "e0-"}
+	sbcs := []*sandboxconn.SandboxConn{}
+	for _, shard := range shards {
+		sbc := hc.AddTestTablet(cell, shard, 1, "TestExecutor", shard, topodatapb.TabletType_PRIMARY, true, 1, nil)
+		if values != nil {
+			sbc.SetResults(values)
+		}
+		sbcs = append(sbcs, sbc)
+	}
+
+	createSandbox(KsTestUnsharded)
+	sbclookup = hc.AddTestTablet(cell, "0", 1, KsTestUnsharded, "0", topodatapb.TabletType_PRIMARY, true, 1, nil)
+	getSandbox(KsTestUnsharded).VSchema = unshardedVSchema
+
+	executor = NewExecutor(context.Background(), serv, cell, resolver, false, false, testBufferSize, cache.DefaultConfig, nil, false)
+	return executor, sbcs[0], sbcs[1], sbclookup
+}
+
 func executorExecSession(executor *Executor, sql string, bv map[string]*querypb.BindVariable, session *vtgatepb.Session) (*sqltypes.Result, error) {
 	return executor.Execute(
 		context.Background(),
diff --git a/go/vt/vtgate/executor_test.go b/go/vt/vtgate/executor_test.go
index c7eaa253cc..76b6d05bb2 100644
--- a/go/vt/vtgate/executor_test.go
+++ b/go/vt/vtgate/executor_test.go
@@ -752,6 +752,8 @@ func TestExecutorShow(t *testing.T) {
 			buildVarCharRow("TestExecutor", "name_lastname_keyspace_id_map", "lookup", "from=name,lastname; table=name_lastname_keyspace_id_map; to=keyspace_id", "user2"),
 			buildVarCharRow("TestExecutor", "name_user_map", "lookup_hash", "from=name; table=name_user_map; to=user_id", "user"),
 			buildVarCharRow("TestExecutor", "t1_lkp_vdx", "consistent_lookup_unique", "from=unq_col; table=t1_lkp_idx; to=keyspace_id", "t1"),
+			buildVarCharRow("TestExecutor", "t2_lu_vdx", "lookup_hash_unique", "from=lu_col; table=TestUnsharded.lu_idx; to=keyspace_id", "t2_wo_lookup"),
+			buildVarCharRow("TestExecutor", "t2_wo_lu_vdx", "lookup_unique", "from=wo_lu_col; table=TestUnsharded.wo_lu_idx; to=keyspace_id; write_only=true", "t2_wo_lookup"),
 		},
 	}
 	utils.MustMatch(t, wantqr, qr, query)
@@ -882,6 +884,7 @@ func TestExecutorShow(t *testing.T) {
 		Rows: [][]sqltypes.Value{
 			buildVarCharRow("dual"),
 			buildVarCharRow("ins_lookup"),
+			buildVarCharRow("lu_idx"),
 			buildVarCharRow("main1"),
 			buildVarCharRow("music_user_map"),
 			buildVarCharRow("name_lastname_keyspace_id_map"),
@@ -889,6 +892,7 @@ func TestExecutorShow(t *testing.T) {
 			buildVarCharRow("simple"),
 			buildVarCharRow("user_msgs"),
 			buildVarCharRow("user_seq"),
+			buildVarCharRow("wo_lu_idx"),
 		},
 	}
 	utils.MustMatch(t, wantqr, qr, query)
@@ -1344,12 +1348,12 @@ func TestExecutorAddDropVschemaTableDDL(t *testing.T) {
 	stmt := "alter vschema add table test_table"
 	_, err := executor.Execute(ctx, "TestExecute", session, stmt, nil)
 	require.NoError(t, err)
-	_ = waitForVschemaTables(t, ks, append(vschemaTables, "test_table"), executor)
+	_ = waitForVschemaTables(t, ks, append([]string{"test_table"}, vschemaTables...), executor)
 
 	stmt = "alter vschema add table test_table2"
 	_, err = executor.Execute(ctx, "TestExecute", session, stmt, nil)
 	require.NoError(t, err)
-	_ = waitForVschemaTables(t, ks, append(vschemaTables, []string{"test_table", "test_table2"}...), executor)
+	_ = waitForVschemaTables(t, ks, append([]string{"test_table", "test_table2"}, vschemaTables...), executor)
 
 	// Should fail adding a table on a sharded keyspace
 	session = NewSafeSession(&vtgatepb.Session{TargetString: "TestExecutor"})
diff --git a/go/vt/vtgate/executor_vschema_ddl_test.go b/go/vt/vtgate/executor_vschema_ddl_test.go
index 1ce5a87a62..697f8ecdf7 100644
--- a/go/vt/vtgate/executor_vschema_ddl_test.go
+++ b/go/vt/vtgate/executor_vschema_ddl_test.go
@@ -297,12 +297,12 @@ func TestPlanExecutorAddDropVschemaTableDDL(t *testing.T) {
 	stmt := "alter vschema add table test_table"
 	_, err := executor.Execute(context.Background(), "TestExecute", session, stmt, nil)
 	require.NoError(t, err)
-	_ = waitForVschemaTables(t, ks, append(vschemaTables, "test_table"), executor)
+	_ = waitForVschemaTables(t, ks, append([]string{"test_table"}, vschemaTables...), executor)
 
 	stmt = "alter vschema add table test_table2"
 	_, err = executor.Execute(context.Background(), "TestExecute", session, stmt, nil)
 	require.NoError(t, err)
-	_ = waitForVschemaTables(t, ks, append(vschemaTables, []string{"test_table", "test_table2"}...), executor)
+	_ = waitForVschemaTables(t, ks, append([]string{"test_table", "test_table2"}, vschemaTables...), executor)
 
 	// Should fail adding a table on a sharded keyspace
 	session = NewSafeSession(&vtgatepb.Session{TargetString: "TestExecutor"})
diff --git a/go/vt/vtgate/planbuilder/testdata/dml_cases.txt b/go/vt/vtgate/planbuilder/testdata/dml_cases.txt
index 1c5d6d9bf9..51b2309c7c 100644
--- a/go/vt/vtgate/planbuilder/testdata/dml_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/dml_cases.txt
@@ -2446,3 +2446,166 @@ Gen4 plan same as above
   }
 }
 Gen4 plan same as above
+
+# Delete on backfilling unique lookup vindex should be a scatter
+"delete from zlookup_unique.t1 where c2 = 20"
+{
+  "QueryType": "DELETE",
+  "Original": "delete from zlookup_unique.t1 where c2 = 20",
+  "Instructions": {
+    "OperatorType": "Delete",
+    "Variant": "Scatter",
+    "Keyspace": {
+      "Name": "zlookup_unique",
+      "Sharded": true
+    },
+    "TargetTabletType": "PRIMARY",
+    "KsidVindex": "xxhash",
+    "MultiShardAutocommit": false,
+    "OwnedVindexQuery": "select c1, c2, c3 from t1 where c2 = 20 for update",
+    "Query": "delete from t1 where c2 = 20",
+    "Table": "t1"
+  }
+}
+Gen4 plan same as above
+
+# Update on backfilling unique lookup vindex should be a scatter
+"update zlookup_unique.t1 set c2 = 1 where c2 = 20"
+{
+  "QueryType": "UPDATE",
+  "Original": "update zlookup_unique.t1 set c2 = 1 where c2 = 20",
+  "Instructions": {
+    "OperatorType": "Update",
+    "Variant": "Scatter",
+    "Keyspace": {
+      "Name": "zlookup_unique",
+      "Sharded": true
+    },
+    "TargetTabletType": "PRIMARY",
+    "ChangedVindexValues": [
+      "lookup_t1:3"
+    ],
+    "KsidVindex": "xxhash",
+    "MultiShardAutocommit": false,
+    "OwnedVindexQuery": "select c1, c2, c3, c2 = 1 from t1 where c2 = 20 for update",
+    "Query": "update t1 set c2 = 1 where c2 = 20",
+    "Table": "t1"
+  }
+}
+Gen4 plan same as above
+
+# Delete on backfilling and non-backfilling unique lookup vindexes should be a delete equal
+"delete from zlookup_unique.t1 where c2 = 10 and c3 = 20"
+{
+  "QueryType": "DELETE",
+  "Original": "delete from zlookup_unique.t1 where c2 = 10 and c3 = 20",
+  "Instructions": {
+    "OperatorType": "Delete",
+    "Variant": "Equal",
+    "Keyspace": {
+      "Name": "zlookup_unique",
+      "Sharded": true
+    },
+    "TargetTabletType": "PRIMARY",
+    "KsidVindex": "xxhash",
+    "MultiShardAutocommit": false,
+    "OwnedVindexQuery": "select c1, c2, c3 from t1 where c2 = 10 and c3 = 20 for update",
+    "Query": "delete from t1 where c2 = 10 and c3 = 20",
+    "Table": "t1",
+    "Values": [
+      20
+    ],
+    "Vindex": "lookup_t1_2"
+  }
+}
+Gen4 plan same as above
+
+# Update on backfilling and non-backfilling unique lookup vindexes should be an equal
+"update zlookup_unique.t1 set c2 = 1 where c2 = 10 and c3 = 20"
+{
+  "QueryType": "UPDATE",
+  "Original": "update zlookup_unique.t1 set c2 = 1 where c2 = 10 and c3 = 20",
+  "Instructions": {
+    "OperatorType": "Update",
+    "Variant": "Equal",
+    "Keyspace": {
+      "Name": "zlookup_unique",
+      "Sharded": true
+    },
+    "TargetTabletType": "PRIMARY",
+    "ChangedVindexValues": [
+      "lookup_t1:3"
+    ],
+    "KsidVindex": "xxhash",
+    "MultiShardAutocommit": false,
+    "OwnedVindexQuery": "select c1, c2, c3, c2 = 1 from t1 where c2 = 10 and c3 = 20 for update",
+    "Query": "update t1 set c2 = 1 where c2 = 10 and c3 = 20",
+    "Table": "t1",
+    "Values": [
+      20
+    ],
+    "Vindex": "lookup_t1_2"
+  }
+}
+Gen4 plan same as above
+
+# Delete EQUAL and IN on backfilling and non-backfilling unique lookup vindexes should be a delete IN
+"delete from zlookup_unique.t1 where c2 = 10 and c3 in (20, 21)"
+{
+  "QueryType": "DELETE",
+  "Original": "delete from zlookup_unique.t1 where c2 = 10 and c3 in (20, 21)",
+  "Instructions": {
+    "OperatorType": "Delete",
+    "Variant": "In",
+    "Keyspace": {
+      "Name": "zlookup_unique",
+      "Sharded": true
+    },
+    "TargetTabletType": "PRIMARY",
+    "KsidVindex": "xxhash",
+    "MultiShardAutocommit": false,
+    "OwnedVindexQuery": "select c1, c2, c3 from t1 where c2 = 10 and c3 in (20, 21) for update",
+    "Query": "delete from t1 where c2 = 10 and c3 in (20, 21)",
+    "Table": "t1",
+    "Values": [
+      [
+        20,
+        21
+      ]
+    ],
+    "Vindex": "lookup_t1_2"
+  }
+}
+Gen4 plan same as above
+
+# Update EQUAL and IN on backfilling and non-backfilling unique lookup vindexes should be an update IN
+"update zlookup_unique.t1 set c2 = 1 where c2 = 10 and c3 in (20, 21)"
+{
+  "QueryType": "UPDATE",
+  "Original": "update zlookup_unique.t1 set c2 = 1 where c2 = 10 and c3 in (20, 21)",
+  "Instructions": {
+    "OperatorType": "Update",
+    "Variant": "In",
+    "Keyspace": {
+      "Name": "zlookup_unique",
+      "Sharded": true
+    },
+    "TargetTabletType": "PRIMARY",
+    "ChangedVindexValues": [
+      "lookup_t1:3"
+    ],
+    "KsidVindex": "xxhash",
+    "MultiShardAutocommit": false,
+    "OwnedVindexQuery": "select c1, c2, c3, c2 = 1 from t1 where c2 = 10 and c3 in (20, 21) for update",
+    "Query": "update t1 set c2 = 1 where c2 = 10 and c3 in (20, 21)",
+    "Table": "t1",
+    "Values": [
+      [
+        20,
+        21
+      ]
+    ],
+    "Vindex": "lookup_t1_2"
+  }
+}
+Gen4 plan same as above
diff --git a/go/vt/vtgate/planbuilder/testdata/schema_test.json b/go/vt/vtgate/planbuilder/testdata/schema_test.json
index c97210835d..356fa36e97 100644
--- a/go/vt/vtgate/planbuilder/testdata/schema_test.json
+++ b/go/vt/vtgate/planbuilder/testdata/schema_test.json
@@ -292,6 +292,54 @@
       "tables": {
       }
     },
+    "zlookup_unique": {
+      "sharded": true,
+      "vindexes": {
+        "hash": {
+          "type": "hash"
+        },
+        "xxhash": {
+          "type": "xxhash"
+        },
+        "lookup_t1": {
+          "type": "lookup_unique",
+          "params": {
+            "from": "c2",
+            "table": "targetkeyspace.lookup_t1_backing",
+            "to": "keyspace_id",
+            "write_only": "true"
+          },
+          "owner": "t1"
+        },
+        "lookup_t1_2": {
+          "type": "lookup_unique",
+          "params": {
+            "from": "c3",
+            "table": "targetkeyspace.lookup_t1_2_backing",
+            "to": "keyspace_id"
+          },
+          "owner": "t1"
+        }
+      },
+      "tables": {
+        "t1": {
+          "columnVindexes": [
+            {
+              "column": "c1",
+              "name": "xxhash"
+            },
+            {
+              "column": "c2",
+              "name": "lookup_t1"
+            },
+            {
+              "column": "c3",
+              "name": "lookup_t1_2"
+            }
+          ]
+        }
+      }
+    },
     "main": {
       "tables": {
         "unsharded": {