Repository: matrixorigin/matrixone
Pull request #13932 (2024-01-02 01:23:26)

[Feature]: Vector Index | Phase 4 (kmeans issue, delete from table, lists=0)
## What type of PR is this?

- [ ] API-change
- [ ] BUG
- [ ] Improvement
- [ ] Documentation
- [X] Feature
- [ ] Test and CI
- [ ] Code Refactoring

## Which issue(s) this PR fixes:

issue # https://github.com/matrixorigin/matrixone/issues/10634

## What this PR does / why we need it:
- Fix the Kmeans issue for `zero member cluster`.
- Fix `delete from tbl` issue
- Fail index creation when `lists = 0`
- Fix issue when 2 vector index is created for same column and issued a insert.
- Fix issue when 2 vector index is created for same column and issued a `alter add new column`

## Vector Index Testcases
https://github.com/matrixorigin/docs/pull/264


Patch:
diff --git a/pkg/catalog/secondary_index_utils.go b/pkg/catalog/secondary_index_utils.go
index 9f8af97c0..89630bcdf 100644
--- a/pkg/catalog/secondary_index_utils.go
+++ b/pkg/catalog/secondary_index_utils.go
@@ -157,12 +157,10 @@ func indexParamsToMap(def *tree.Index) (map[string]string, error) {
 	case tree.INDEX_TYPE_BTREE, tree.INDEX_TYPE_INVALID:
 		// do nothing
 	case tree.INDEX_TYPE_IVFFLAT:
-		if def.IndexOption.AlgoParamList < 0 {
-			return nil, moerr.NewInternalErrorNoCtx("invalid list. list must be >= 0")
-		} else if def.IndexOption.AlgoParamList > 0 {
-			res[IndexAlgoParamLists] = strconv.FormatInt(def.IndexOption.AlgoParamList, 10)
+		if def.IndexOption.AlgoParamList <= 0 {
+			return nil, moerr.NewInternalErrorNoCtx("invalid list. list must be > 0")
 		} else {
-			res[IndexAlgoParamLists] = "1" // set lists = 1 as default
+			res[IndexAlgoParamLists] = strconv.FormatInt(def.IndexOption.AlgoParamList, 10)
 		}
 
 		if len(def.IndexOption.AlgoParamVectorOpType) > 0 {
diff --git a/pkg/sql/compile/alter.go b/pkg/sql/compile/alter.go
index 1f52b9a49..0c3c33eb0 100644
--- a/pkg/sql/compile/alter.go
+++ b/pkg/sql/compile/alter.go
@@ -123,7 +123,7 @@ func (s *Scope) AlterTableCopy(c *Compile) error {
 
 	{
 		// 7.b invoke reindex for the new table, if it contains ivf index.
-		multiTableIndexes := make(map[string]map[string]*plan.IndexDef)
+		multiTableIndexes := make(map[string]*MultiTableIndex)
 		newTableRel, err := dbSource.Relation(c.ctx, qry.TableDef.Name, nil)
 		if err != nil {
 			return err
@@ -132,17 +132,19 @@ func (s *Scope) AlterTableCopy(c *Compile) error {
 
 		for _, indexDef := range newTableDef.Indexes {
 			if catalog.IsIvfIndexAlgo(indexDef.IndexAlgo) {
-				indexAlgo := catalog.ToLower(indexDef.IndexAlgo)
-				if _, ok := multiTableIndexes[indexAlgo]; !ok {
-					multiTableIndexes[indexAlgo] = make(map[string]*plan.IndexDef)
+				if _, ok := multiTableIndexes[indexDef.IndexName]; !ok {
+					multiTableIndexes[indexDef.IndexName] = &MultiTableIndex{
+						IndexAlgo: catalog.ToLower(indexDef.IndexAlgo),
+						IndexDefs: make(map[string]*plan.IndexDef),
+					}
 				}
-				multiTableIndexes[indexAlgo][indexDef.IndexAlgoTableType] = indexDef
+				multiTableIndexes[indexDef.IndexName].IndexDefs[catalog.ToLower(indexDef.IndexAlgoTableType)] = indexDef
 			}
 		}
-		for indexAlgoType, indexDefs := range multiTableIndexes {
-			switch catalog.ToLower(indexAlgoType) {
+		for _, multiTableIndex := range multiTableIndexes {
+			switch multiTableIndex.IndexAlgo {
 			case catalog.MoIndexIvfFlatAlgo.ToString():
-				err = s.handleVectorIvfFlatIndex(c, indexDefs, qry.Database, newTableDef, nil)
+				err = s.handleVectorIvfFlatIndex(c, multiTableIndex.IndexDefs, qry.Database, newTableDef, nil)
 			}
 			if err != nil {
 				return err
diff --git a/pkg/sql/compile/ddl.go b/pkg/sql/compile/ddl.go
index eba319dbb..b13c1082d 100644
--- a/pkg/sql/compile/ddl.go
+++ b/pkg/sql/compile/ddl.go
@@ -321,10 +321,10 @@ func (s *Scope) AlterTableInplace(c *Compile) error {
 			indexInfo := act.AddIndex.IndexInfo // IndexInfo is named same as planner's IndexInfo
 			indexTableDef := act.AddIndex.IndexInfo.TableDef
 
-			// IVF -> meta      -> indexDef
-			//     -> centroids -> indexDef
-			//     -> entries   -> indexDef
-			multiTableIndexes := make(map[string]map[string]*plan.IndexDef)
+			// indexName -> meta      -> indexDef
+			//     		 -> centroids -> indexDef
+			//     		 -> entries   -> indexDef
+			multiTableIndexes := make(map[string]*MultiTableIndex)
 			for _, indexDef := range indexTableDef.Indexes {
 
 				for i := range addIndex {
@@ -342,20 +342,23 @@ func (s *Scope) AlterTableInplace(c *Compile) error {
 					err = s.handleRegularSecondaryIndexTable(c, indexDef, qry.Database, tableDef, indexInfo)
 				} else if !indexDef.Unique && catalog.IsIvfIndexAlgo(indexDef.IndexAlgo) {
 					// 3. IVF indexDefs are aggregated and handled later
-					if _, ok := multiTableIndexes[indexDef.IndexAlgo]; !ok {
-						multiTableIndexes[indexDef.IndexAlgo] = make(map[string]*plan.IndexDef)
+					if _, ok := multiTableIndexes[indexDef.IndexName]; !ok {
+						multiTableIndexes[indexDef.IndexName] = &MultiTableIndex{
+							IndexAlgo: catalog.ToLower(indexDef.IndexAlgo),
+							IndexDefs: make(map[string]*plan.IndexDef),
+						}
 					}
-					multiTableIndexes[catalog.ToLower(indexDef.IndexAlgo)][catalog.ToLower(indexDef.IndexAlgoTableType)] = indexDef
+					multiTableIndexes[indexDef.IndexName].IndexDefs[catalog.ToLower(indexDef.IndexAlgoTableType)] = indexDef
+
 				}
 				if err != nil {
 					return err
 				}
 			}
-
-			for indexAlgoType, indexDefs := range multiTableIndexes {
-				switch indexAlgoType { // no need for catalog.ToLower() here
+			for _, multiTableIndex := range multiTableIndexes {
+				switch multiTableIndex.IndexAlgo { // no need for catalog.ToLower() here
 				case catalog.MoIndexIvfFlatAlgo.ToString():
-					err = s.handleVectorIvfFlatIndex(c, indexDefs, qry.Database, tableDef, indexInfo)
+					err = s.handleVectorIvfFlatIndex(c, multiTableIndex.IndexDefs, qry.Database, tableDef, indexInfo)
 				}
 
 				if err != nil {
@@ -399,12 +402,12 @@ func (s *Scope) AlterTableInplace(c *Compile) error {
 				}
 			}
 		case *plan.AlterTable_Action_AlterReindex:
-			// NOTE: We hold lock during alter reindex, as "alter table" takes an exclusive lock in the beginning.
-			// We need to see how to reduce the critical section.
+			// NOTE: We hold lock (with retry) during alter reindex, as "alter table" takes an exclusive lock
+			//in the beginning for pessimistic mode. We need to see how to reduce the critical section.
 			alterKind = addAlterKind(alterKind, api.AlterKind_UpdateConstraint)
 			tableAlterIndex := act.AlterReindex
 			constraintName := tableAlterIndex.IndexName
-			multiTableIndexes := make(map[string]map[string]*plan.IndexDef)
+			multiTableIndexes := make(map[string]*MultiTableIndex)
 
 			for i, indexDef := range tableDef.Indexes {
 				if indexDef.IndexName == constraintName {
@@ -445,22 +448,25 @@ func (s *Scope) AlterTableInplace(c *Compile) error {
 					}
 
 					// 4. Add to multiTableIndexes
-					if _, ok := multiTableIndexes[indexAlgo]; !ok {
-						multiTableIndexes[indexAlgo] = make(map[string]*plan.IndexDef)
+					if _, ok := multiTableIndexes[indexDef.IndexName]; !ok {
+						multiTableIndexes[indexDef.IndexName] = &MultiTableIndex{
+							IndexAlgo: catalog.ToLower(indexDef.IndexAlgo),
+							IndexDefs: make(map[string]*plan.IndexDef),
+						}
 					}
-					multiTableIndexes[indexAlgo][alterIndex.IndexAlgoTableType] = alterIndex
+					multiTableIndexes[indexDef.IndexName].IndexDefs[catalog.ToLower(indexDef.IndexAlgoTableType)] = indexDef
 				}
 			}
 
-			if len(multiTableIndexes) == 0 {
+			if len(multiTableIndexes) != 1 {
 				return moerr.NewInternalError(c.ctx, "invalid index algo type for alter reindex")
 			}
 
 			// update the hidden tables
-			for indexAlgoType, indexDefs := range multiTableIndexes {
-				switch catalog.ToLower(indexAlgoType) {
+			for _, multiTableIndex := range multiTableIndexes {
+				switch multiTableIndex.IndexAlgo {
 				case catalog.MoIndexIvfFlatAlgo.ToString():
-					err = s.handleVectorIvfFlatIndex(c, indexDefs, qry.Database, tableDef, nil)
+					err = s.handleVectorIvfFlatIndex(c, multiTableIndex.IndexDefs, qry.Database, tableDef, nil)
 				}
 
 				if err != nil {
@@ -1112,10 +1118,10 @@ func (s *Scope) CreateIndex(c *Compile) error {
 	indexInfo := qry.GetIndex() // IndexInfo is named same as planner's IndexInfo
 	indexTableDef := indexInfo.GetTableDef()
 
-	// IVF -> meta 		-> indexDef[0]
-	// 	   -> centroids -> indexDef[1]
-	// 	   -> entries 	-> indexDef[2]
-	multiTableIndexes := make(map[string]map[string]*plan.IndexDef)
+	// indexName -> meta      -> indexDef[0]
+	//     		 -> centroids -> indexDef[1]
+	//     		 -> entries   -> indexDef[2]
+	multiTableIndexes := make(map[string]*MultiTableIndex)
 	for _, indexDef := range indexTableDef.Indexes {
 
 		indexAlgo := indexDef.IndexAlgo
@@ -1127,21 +1133,23 @@ func (s *Scope) CreateIndex(c *Compile) error {
 			err = s.handleRegularSecondaryIndexTable(c, indexDef, qry.Database, originalTableDef, indexInfo)
 		} else if !indexDef.Unique && catalog.IsIvfIndexAlgo(indexAlgo) {
 			// 3. IVF indexDefs are aggregated and handled later
-			if _, ok := multiTableIndexes[indexAlgo]; !ok {
-				multiTableIndexes[indexAlgo] = make(map[string]*plan.IndexDef)
+			if _, ok := multiTableIndexes[indexDef.IndexName]; !ok {
+				multiTableIndexes[indexDef.IndexName] = &MultiTableIndex{
+					IndexAlgo: catalog.ToLower(indexDef.IndexAlgo),
+					IndexDefs: make(map[string]*plan.IndexDef),
+				}
 			}
-			indexAlgoTableType := catalog.ToLower(indexDef.IndexAlgoTableType)
-			multiTableIndexes[indexAlgo][indexAlgoTableType] = indexDef
+			multiTableIndexes[indexDef.IndexName].IndexDefs[catalog.ToLower(indexDef.IndexAlgoTableType)] = indexDef
 		}
 		if err != nil {
 			return err
 		}
 	}
 
-	for indexAlgoType, indexDefs := range multiTableIndexes {
-		switch catalog.ToLower(indexAlgoType) {
+	for _, multiTableIndex := range multiTableIndexes {
+		switch multiTableIndex.IndexAlgo {
 		case catalog.MoIndexIvfFlatAlgo.ToString():
-			err = s.handleVectorIvfFlatIndex(c, indexDefs, qry.Database, originalTableDef, indexInfo)
+			err = s.handleVectorIvfFlatIndex(c, multiTableIndex.IndexDefs, qry.Database, originalTableDef, indexInfo)
 		}
 
 		if err != nil {
@@ -1363,7 +1371,8 @@ func makeNewCreateConstraint(oldCt *engine.ConstraintDef, c engine.Constraint) (
 		var indexdef *engine.IndexDef
 		for i, ct := range oldCt.Cts {
 			if indexdef, ok = ct.(*engine.IndexDef); ok {
-				indexdef.Indexes = append(indexdef.Indexes, t.Indexes[0])
+				//TODO: verify if this is correct @ouyuanning & @qingx
+				indexdef.Indexes = append(indexdef.Indexes, t.Indexes...)
 				oldCt.Cts = append(oldCt.Cts[:i], oldCt.Cts[i+1:]...)
 				oldCt.Cts = append(oldCt.Cts, indexdef)
 				break
diff --git a/pkg/sql/compile/types.go b/pkg/sql/compile/types.go
index 6be22b953..b775bec3b 100644
--- a/pkg/sql/compile/types.go
+++ b/pkg/sql/compile/types.go
@@ -305,3 +305,8 @@ type fuzzyCheck struct {
 
 	cnt int
 }
+
+type MultiTableIndex struct {
+	IndexAlgo string
+	IndexDefs map[string]*plan.IndexDef
+}
diff --git a/pkg/sql/plan/build_dml_util.go b/pkg/sql/plan/build_dml_util.go
index 2ef4877b7..dca221e0d 100644
--- a/pkg/sql/plan/build_dml_util.go
+++ b/pkg/sql/plan/build_dml_util.go
@@ -1921,7 +1921,14 @@ func makeDeleteNodeInfo(ctx CompilerContext, objRef *ObjectRef, tableDef *TableD
 	if tableDef.Indexes != nil {
 		for _, indexdef := range tableDef.Indexes {
 			if indexdef.TableExist {
-				delNodeInfo.indexTableNames = append(delNodeInfo.indexTableNames, indexdef.IndexTableName)
+				if catalog.IsRegularIndexAlgo(indexdef.IndexAlgo) {
+					delNodeInfo.indexTableNames = append(delNodeInfo.indexTableNames, indexdef.IndexTableName)
+				} else if catalog.IsIvfIndexAlgo(indexdef.IndexAlgo) {
+					// apply deletes only for entries table.
+					if indexdef.IndexAlgoTableType == catalog.SystemSI_IVFFLAT_TblType_Entries {
+						delNodeInfo.indexTableNames = append(delNodeInfo.indexTableNames, indexdef.IndexTableName)
+					}
+				}
 			}
 		}
 	}
diff --git a/pkg/sql/plan/function/functionAgg/algos/kmeans/elkans/clusterer.go b/pkg/sql/plan/function/functionAgg/algos/kmeans/elkans/clusterer.go
index f67d3b1a6..82d8a41a9 100644
--- a/pkg/sql/plan/function/functionAgg/algos/kmeans/elkans/clusterer.go
+++ b/pkg/sql/plan/function/functionAgg/algos/kmeans/elkans/clusterer.go
@@ -287,18 +287,13 @@ func (km *ElkanClusterer) computeCentroidDistances() {
 // This is the place where most of the "distance computation skipping" happens.
 func (km *ElkanClusterer) assignData() int {
 
-	var distToCurrentCentroid float64 // u(x) in the paper
-	var assignedCentroid int          // c(x) in the paper
 	changes := 0
 
 	for currVector := range km.vectorList {
 
-		distToCurrentCentroid = km.vectorMetas[currVector].upper
-		assignedCentroid = km.assignments[currVector]
-
 		// step 2
 		// u(x) <= s(c(x))
-		if distToCurrentCentroid <= km.minHalfInterCentroidDist[assignedCentroid] {
+		if km.vectorMetas[currVector].upper <= km.minHalfInterCentroidDist[km.assignments[currVector]] {
 			continue
 		}
 
@@ -308,18 +303,30 @@ func (km *ElkanClusterer) assignData() int {
 			// (i) c != c(x) and
 			// (ii) u(x)>l(x, c) and
 			// (iii) u(x)> 0.5 x d(c(x), c)
-			if c != assignedCentroid &&
-				distToCurrentCentroid > km.vectorMetas[currVector].lower[c] &&
-				distToCurrentCentroid > km.halfInterCentroidDistMatrix[assignedCentroid][c] {
+			if c != km.assignments[currVector] &&
+				km.vectorMetas[currVector].upper > km.vectorMetas[currVector].lower[c] &&
+				km.vectorMetas[currVector].upper > km.halfInterCentroidDistMatrix[km.assignments[currVector]][c] {
 
 				//step 3.a - Bounds update
-				// If r(x) then compute d(x, c(x)) and assign r(x)= false. Otherwise, d(x, c(x))=u(x).
-				dxcx := distToCurrentCentroid // d(x, c(x)) in the paper ie distToCentroid
+				// If r(x) then compute d(x, c(x)) and assign r(x)= false.
+				var dxcx float64
 				if km.vectorMetas[currVector].recompute {
-					dxcx = km.distFn(km.vectorList[currVector], km.centroids[assignedCentroid])
-					km.vectorMetas[currVector].upper = dxcx
-					km.vectorMetas[currVector].lower[assignedCentroid] = dxcx
 					km.vectorMetas[currVector].recompute = false
+
+					dxcx = km.distFn(km.vectorList[currVector], km.centroids[km.assignments[currVector]])
+					km.vectorMetas[currVector].upper = dxcx
+					km.vectorMetas[currVector].lower[km.assignments[currVector]] = dxcx
+
+					if km.vectorMetas[currVector].upper <= km.vectorMetas[currVector].lower[c] {
+						continue // Pruned by triangle inequality on lower bound.
+					}
+
+					if km.vectorMetas[currVector].upper <= km.halfInterCentroidDistMatrix[km.assignments[currVector]][c] {
+						continue // Pruned by triangle inequality on cluster distances.
+					}
+
+				} else {
+					dxcx = km.vectorMetas[currVector].upper //  Otherwise, d(x, c(x))=u(x).
 				}
 
 				//step 3.b - Update
@@ -327,15 +334,13 @@ func (km *ElkanClusterer) assignData() int {
 				// Compute d(x, c)
 				// If d(x, c)<d(x, c(x)) then assign c(x)=c.
 				if dxcx > km.vectorMetas[currVector].lower[c] ||
-					dxcx > km.halfInterCentroidDistMatrix[assignedCentroid][c] {
+					dxcx > km.halfInterCentroidDistMatrix[km.assignments[currVector]][c] {
 
 					dxc := km.distFn(km.vectorList[currVector], km.centroids[c]) // d(x,c) in the paper
 					km.vectorMetas[currVector].lower[c] = dxc
 					if dxc < dxcx {
 						km.vectorMetas[currVector].upper = dxc
-
-						assignedCentroid = c
-						km.assignments[currVector] = assignedCentroid
+						km.assignments[currVector] = c
 						changes++
 					}
 				}
@@ -364,10 +369,13 @@ func (km *ElkanClusterer) recalculateCentroids() []*mat.VecDense {
 	// means of the clusters = sum of all the members of the cluster / number of members in the cluster
 	for c := range newCentroids {
 		if membersCount[c] == 0 {
+			// pick a vector randomly from existing vectors as the new centroid
+			//newCentroids[c] = km.vectorList[km.rand.Intn(km.vectorCnt)]
+
 			// if the cluster is empty, reinitialize it to a random vector, since you can't find the mean of an empty set
 			randVector := make([]float64, km.vectorList[0].Len())
 			for l := range randVector {
-				randVector[l] = 10 * (km.rand.Float64() - 0.5)
+				randVector[l] = km.rand.Float64()
 			}
 			newCentroids[c] = mat.NewVecDense(km.vectorList[0].Len(), randVector)
 
diff --git a/pkg/sql/plan/function/functionAgg/cluster_centers.go b/pkg/sql/plan/function/functionAgg/cluster_centers.go
index 9e2d8f100..b44cb5063 100644
--- a/pkg/sql/plan/function/functionAgg/cluster_centers.go
+++ b/pkg/sql/plan/function/functionAgg/cluster_centers.go
@@ -55,15 +55,15 @@ func init() {
 	}
 
 	initTypeStrToEnum = map[string]kmeans.InitType{
-		//"random":         kmeans.Random,
+		"random":         kmeans.Random,
 		"kmeansplusplus": kmeans.KmeansPlusPlus,
 	}
 }
 
 var (
-	AggClusterCentersSupportedParameters = []types.T{
-		types.T_array_float32, types.T_array_float64,
-	}
+	//AggClusterCentersSupportedParameters = []types.T{
+	//	types.T_array_float32, types.T_array_float64,
+	//}
 
 	AggClusterCentersReturnType = func(typs []types.Type) types.Type {
 		return types.T_varchar.ToType()


Test patch:
diff --git a/test/distributed/cases/array/array_index.result b/test/distributed/cases/array/array_index.result
index 402fc12c3..6b4e9e1c9 100644
--- a/test/distributed/cases/array/array_index.result
+++ b/test/distributed/cases/array/array_index.result
@@ -124,7 +124,7 @@ create index idx4 using IVFFLAT on tbl(b,c) lists = 2 op_type 'vector_l2_ops';
 not supported: don't support multi column  IVF vector index
 create index idx5 using IVFFLAT on tbl(b) lists = -1;
 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 51 near " -1;";
-create index idx6 using IVFFLAT on tbl(b) op_type 'vector_l1_ops';
+create index idx6 using IVFFLAT on tbl(b) lists = 1 op_type 'vector_l1_ops';
 internal error: invalid op_type. not of type 'vector_l2_ops'
 drop table if exists tbl;
 create table tbl(a int primary key,b vecf32(3), c vecf32(3));
@@ -133,18 +133,7 @@ insert into tbl values(2, "[1,2,4]","[1,2,4]");
 insert into tbl values(3, "[1,2.4,4]","[1,2.4,4]");
 insert into tbl values(4, "[1,2,5]","[1,2,5]");
 create index idx7 using IVFFLAT on tbl(b);
-show index from tbl;
-Table    Non_unique    Key_name    Seq_in_index    Column_name    Collation    Cardinality    Sub_part    Packed    Null    Index_type    Comment    Index_comment    Index_params    Visible    Expression
-tbl    0    PRIMARY    1    a    A    0    NULL    NULL                        YES    NULL
-tbl    1    idx7    1    b    A    0    NULL    NULL    YES    ivfflat            {"lists":"1","op_type":"vector_l2_ops"}    YES    NULL
-show create table tbl;
-Table    Create Table
-tbl    CREATE TABLE `tbl` (\n`a` INT NOT NULL,\n`b` VECF32(3) DEFAULT NULL,\n`c` VECF32(3) DEFAULT NULL,\nPRIMARY KEY (`a`),\nKEY `idx7` USING ivfflat (`b`) lists = 1  op_type 'vector_l2_ops' \n)
-select name, type, column_name, algo, algo_table_type,algo_params from mo_catalog.mo_indexes where name="idx7";
-name    type    column_name    algo    algo_table_type    algo_params
-idx7    MULTIPLE    b    ivfflat    metadata    {"lists":"1","op_type":"vector_l2_ops"}
-idx7    MULTIPLE    b    ivfflat    centroids    {"lists":"1","op_type":"vector_l2_ops"}
-idx7    MULTIPLE    b    ivfflat    entries    {"lists":"1","op_type":"vector_l2_ops"}
+internal error: invalid list. list must be > 0
 drop table if exists tbl;
 create table tbl(a int primary key, b vecf32(3));
 insert into tbl values(1, "[1,2,3]");
@@ -436,4 +425,59 @@ create table tbl(id int primary key, data vecf32(3));
 create index idx22 using ivfflat on tbl(data) lists=3 op_type "vector_l2_ops";
 insert into tbl values(1, "[1,2,3]");
 insert into tbl values(2, "[1,2,4]");
+drop table if exists tbl;
+create table tbl(id int, embedding vecf32(3));
+insert into tbl values(1, "[1,2,3]");
+insert into tbl values(2, "[1,2,4]");
+insert into tbl values(3, "[1,2.4,4]");
+insert into tbl values(4, "[1,2,5]");
+insert into tbl values(5, "[1,3,5]");
+insert into tbl values(6, "[100,44,50]");
+insert into tbl values(7, "[120,50,70]");
+insert into tbl values(8, "[130,40,90]");
+create index idx23 using ivfflat on tbl(embedding) lists=2 op_type "vector_l2_ops";
+alter table tbl alter reindex idx23 ivfflat lists=2;
+truncate table tbl;
+insert into tbl values(1, "[1,2,3]");
+insert into tbl values(2, "[1,2,4]");
+insert into tbl values(3, "[1,2.4,4]");
+insert into tbl values(4, "[1,2,5]");
+insert into tbl values(5, "[1,3,5]");
+insert into tbl values(6, "[100,44,50]");
+insert into tbl values(7, "[120,50,70]");
+insert into tbl values(8, "[130,40,90]");
+drop table if exists tbl;
+create table tbl(id int, embedding vecf32(3));
+insert into tbl values(1, "[1,2,3]");
+insert into tbl values(2, "[1,2,4]");
+insert into tbl values(3, "[1,2.4,4]");
+insert into tbl values(4, "[1,2,5]");
+insert into tbl values(5, "[1,3,5]");
+insert into tbl values(6, "[100,44,50]");
+insert into tbl values(7, "[120,50,70]");
+insert into tbl values(8, "[130,40,90]");
+create index idx23 using ivfflat on tbl(embedding) lists=2 op_type "vector_l2_ops";
+alter table tbl alter reindex idx23 ivfflat lists=2;
+delete from tbl;
+insert into tbl values(1, "[1,2,3]");
+insert into tbl values(2, "[1,2,4]");
+insert into tbl values(3, "[1,2.4,4]");
+insert into tbl values(4, "[1,2,5]");
+insert into tbl values(5, "[1,3,5]");
+insert into tbl values(6, "[100,44,50]");
+insert into tbl values(7, "[120,50,70]");
+insert into tbl values(8, "[130,40,90]");
+drop table if exists tbl;
+create table tbl(id int primary key, embedding vecf32(3));
+insert into tbl values(1, "[1,2,3]");
+insert into tbl values(2, "[1,2,4]");
+insert into tbl values(3, "[1,2.4,4]");
+insert into tbl values(4, "[1,2,5]");
+insert into tbl values(5, "[1,3,5]");
+insert into tbl values(6, "[100,44,50]");
+insert into tbl values(7, "[120,50,70]");
+insert into tbl values(8, "[130,40,90]");
+create index idx16 using ivfflat on tbl(embedding) lists=2 op_type "vector_l2_ops";
+create index idx17 using ivfflat on tbl(embedding) lists=2 op_type "vector_l2_ops";
+insert into tbl values(9, "[130,40,90]");
 drop database vecdb2;
diff --git a/test/distributed/cases/array/array_index.sql b/test/distributed/cases/array/array_index.sql
index 130eec7b7..737667195 100644
--- a/test/distributed/cases/array/array_index.sql
+++ b/test/distributed/cases/array/array_index.sql
@@ -75,9 +75,9 @@ insert into tbl values(4, "[1,2,5]","[1,2,5]");
 create index idx3 using IVFFLAT on tbl(a) lists = 2 op_type 'vector_l2_ops';
 create index idx4 using IVFFLAT on tbl(b,c) lists = 2 op_type 'vector_l2_ops';
 create index idx5 using IVFFLAT on tbl(b) lists = -1;
-create index idx6 using IVFFLAT on tbl(b) op_type 'vector_l1_ops';
+create index idx6 using IVFFLAT on tbl(b) lists = 1 op_type 'vector_l1_ops';
 
--- 8. [Default] Create IVF index without any params
+-- 8. [Default] Create IVF index without any params -- will fail since we don't have lists argument.
 drop table if exists tbl;
 create table tbl(a int primary key,b vecf32(3), c vecf32(3));
 insert into tbl values(1, "[1,2,3]","[1,2,3]");
@@ -85,9 +85,6 @@ insert into tbl values(2, "[1,2,4]","[1,2,4]");
 insert into tbl values(3, "[1,2.4,4]","[1,2.4,4]");
 insert into tbl values(4, "[1,2,5]","[1,2,5]");
 create index idx7 using IVFFLAT on tbl(b);
-show index from tbl;
-show create table tbl;
-select name, type, column_name, algo, algo_table_type,algo_params from mo_catalog.mo_indexes where name="idx7";
 
 -- 9. duplicate rows
 drop table if exists tbl;
@@ -455,5 +452,86 @@ insert into tbl values(2, "[1,2,4]");
 --|                           0 |                      1 | NULL                |
 --+-----------------------------+------------------------+---------------------+
 
+-- 32. Truncate table (does not remove centroids)
+drop table if exists tbl;
+create table tbl(id int, embedding vecf32(3));
+insert into tbl values(1, "[1,2,3]");
+insert into tbl values(2, "[1,2,4]");
+insert into tbl values(3, "[1,2.4,4]");
+insert into tbl values(4, "[1,2,5]");
+insert into tbl values(5, "[1,3,5]");
+insert into tbl values(6, "[100,44,50]");
+insert into tbl values(7, "[120,50,70]");
+insert into tbl values(8, "[130,40,90]");
+create index idx23 using ivfflat on tbl(embedding) lists=2 op_type "vector_l2_ops";
+alter table tbl alter reindex idx23 ivfflat lists=2;
+truncate table tbl;
+--mysql> select * from `__mo_index_secondary_c18d6262-a56a-11ee-8301-723e89f7b974`;
+--+-----------------------------+------------------------+-----------------------------+
+--| __mo_index_centroid_version | __mo_index_centroid_id | __mo_index_centroid         |
+--+-----------------------------+------------------------+-----------------------------+
+--|                           0 |                      1 | [1, 2.28, 4.2]              |
+--|                           0 |                      2 | [116.666664, 44.666668, 70] |
+--|                           1 |                      1 | [1, 2.28, 4.2]              |
+--|                           1 |                      2 | [116.666664, 44.666668, 70] |
+--+-----------------------------+------------------------+-----------------------------+
+insert into tbl values(1, "[1,2,3]");
+insert into tbl values(2, "[1,2,4]");
+insert into tbl values(3, "[1,2.4,4]");
+insert into tbl values(4, "[1,2,5]");
+insert into tbl values(5, "[1,3,5]");
+insert into tbl values(6, "[100,44,50]");
+insert into tbl values(7, "[120,50,70]");
+insert into tbl values(8, "[130,40,90]");
+
+-- 33. Delete from tbl (without condition)
+drop table if exists tbl;
+create table tbl(id int, embedding vecf32(3));
+insert into tbl values(1, "[1,2,3]");
+insert into tbl values(2, "[1,2,4]");
+insert into tbl values(3, "[1,2.4,4]");
+insert into tbl values(4, "[1,2,5]");
+insert into tbl values(5, "[1,3,5]");
+insert into tbl values(6, "[100,44,50]");
+insert into tbl values(7, "[120,50,70]");
+insert into tbl values(8, "[130,40,90]");
+create index idx23 using ivfflat on tbl(embedding) lists=2 op_type "vector_l2_ops";
+alter table tbl alter reindex idx23 ivfflat lists=2;
+delete from tbl;
+--mysql> select * from `__mo_index_secondary_9aaa720c-a56a-11ee-8301-723e89f7b974`;
+--+-----------------------------+------------------------+-----------------------------+
+--| __mo_index_centroid_version | __mo_index_centroid_id | __mo_index_centroid         |
+--+-----------------------------+------------------------+-----------------------------+
+--|                           0 |                      1 | [1, 2.28, 4.2]              |
+--|                           0 |                      2 | [116.666664, 44.666668, 70] |
+--|                           1 |                      1 | [1, 2.28, 4.2]              |
+--|                           1 |                      2 | [116.666664, 44.666668, 70] |
+--+-----------------------------+------------------------+-----------------------------+
+insert into tbl values(1, "[1,2,3]");
+insert into tbl values(2, "[1,2,4]");
+insert into tbl values(3, "[1,2.4,4]");
+insert into tbl values(4, "[1,2,5]");
+insert into tbl values(5, "[1,3,5]");
+insert into tbl values(6, "[100,44,50]");
+insert into tbl values(7, "[120,50,70]");
+insert into tbl values(8, "[130,40,90]");
+
+
+--- 34. 2 Vector Index on same column
+drop table if exists tbl;
+create table tbl(id int primary key, embedding vecf32(3));
+insert into tbl values(1, "[1,2,3]");
+insert into tbl values(2, "[1,2,4]");
+insert into tbl values(3, "[1,2.4,4]");
+insert into tbl values(4, "[1,2,5]");
+insert into tbl values(5, "[1,3,5]");
+insert into tbl values(6, "[100,44,50]");
+insert into tbl values(7, "[120,50,70]");
+insert into tbl values(8, "[130,40,90]");
+create index idx16 using ivfflat on tbl(embedding) lists=2 op_type "vector_l2_ops";
+create index idx17 using ivfflat on tbl(embedding) lists=2 op_type "vector_l2_ops";
+insert into tbl values(9, "[130,40,90]");
+
+
 -- post
 drop database vecdb2;
\ No newline at end of file