This is an automated email from the ASF dual-hosted git repository.
zfeng pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-seata-go.git
The following commit(s) were added to refs/heads/master by this push:
new c0a7ab09 bugfix: error image when use null value as image query
condition in insert on duplicate #704 (#725) (#884)
c0a7ab09 is described below
commit c0a7ab09e272e190164bd051567378ca61f002cf
Author: lxfeng1997 <[email protected]>
AuthorDate: Sat Nov 8 18:15:14 2025 +0800
bugfix: error image when use null value as image query condition in insert
on duplicate #704 (#725) (#884)
* bugfix: error image when use null value as image query condition in
insert on duplicate #704 (#725)
* bugfix #704
* bugfix #704
* bugfix704-2
* bugfix-test-2
* bugfix-test-2
* pr725 bugfix
---------
Co-authored-by: JayLiu <[email protected]>
Co-authored-by: FengZhang <[email protected]>
* fix: Solve the conflict problem of introducing multiple versions of knadh
(#772)
* fix: Solve the conflict problem of introducing multiple versions of knadh
* fix: fix ci fail
---------
Co-authored-by: JayLiu <[email protected]>
Co-authored-by: FengZhang <[email protected]>
* the ability to automatically run unit tests after creating a pull
request. (#764)
* feat: add unit test workflow
* feat:the ability to automatically run unit tests after creating a pull
request.
* feat:the ability to automatically run unit tests after creating a pull
request.
* feat:the ability to automatically run unit tests after creating a pull
request.
* feat:the ability to automatically run unit tests after creating a pull
request.
* feat:the ability to automatically run unit tests after creating a pull
request.
* feat:the ability to automatically run unit tests after creating a pull
request.
* Optimize/at build lock key performance (#837)
* Refer to buildlockkey2 optimization #829
* Time complexity O(NM)-> O(NK) about buildlockkey and buildlockkey2
Increased readability #829
* update import sort #829
* update Encapsulation into util packages #829
* Support Update join (#761)
* duplicate image row for update join
* update join condition placeholder param error
* update join bugfix
* Open test annotations
* recover update executor
* recover update test
* recover update test
* modified version param
---------
Co-authored-by: JayLiu <[email protected]>
Co-authored-by: FengZhang <[email protected]>
---------
Co-authored-by: jimin <[email protected]>
Co-authored-by: JayLiu <[email protected]>
Co-authored-by: FengZhang <[email protected]>
Co-authored-by: Wiggins <[email protected]>
Co-authored-by: lxfeng1997 <[email protected]>
* merge master
* Improve the test cases
* upload upload-artifact to v4
* chore(ci): update Go version to 1.20.14
---------
Co-authored-by: Aster Zephyr <[email protected]>
Co-authored-by: JayLiu <[email protected]>
Co-authored-by: FengZhang <[email protected]>
Co-authored-by: panlei-coder
<[email protected]>
Co-authored-by: jimin <[email protected]>
Co-authored-by: Wiggins <[email protected]>
---
.github/workflows/unit-test.yml | 79 ++++++++
pkg/datasource/sql/types/image.go | 11 +-
.../undo/builder/basic_undo_log_builder_test.go | 18 ++
...ql_insertonduplicate_update_undo_log_builder.go | 220 ++++++++++++++-------
...sertonduplicate_update_undo_log_builder_test.go | 112 +++++++++--
5 files changed, 349 insertions(+), 91 deletions(-)
diff --git a/.github/workflows/unit-test.yml b/.github/workflows/unit-test.yml
new file mode 100644
index 00000000..91d1bf39
--- /dev/null
+++ b/.github/workflows/unit-test.yml
@@ -0,0 +1,79 @@
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to You under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+name: "Unit Test"
+
+on:
+ push:
+ branches: [ master ]
+ pull_request:
+ branches: [ "*" ]
+ types: [opened, synchronize, reopened]
+
+permissions:
+ contents: read
+
+jobs:
+ unit-test:
+ name: Unit Test
+ runs-on: ubuntu-latest
+ timeout-minutes: 10
+ strategy:
+ matrix:
+ golang:
+ - 1.20.14
+
+ steps:
+ - name: "Set up Go"
+ uses: actions/setup-go@v3
+ with:
+ go-version: ${{ matrix.golang }}
+
+ - name: "Checkout code"
+ uses: actions/checkout@v3
+ with:
+ submodules: true
+
+ - name: "Cache dependencies"
+ uses: actions/cache@v3
+ with:
+ path: ~/go/pkg/mod
+ key: "${{ runner.os }}-go-${{ hashFiles('**/go.sum') }}"
+ restore-keys: |
+ "${{ runner.os }}-go-"
+
+ - name: Shutdown default mysql
+ run: sudo service mysql stop
+
+
+ - name: "Run Unit Tests"
+ run: |
+ echo "=== Starting Unit Tests ==="
+ go test -v ./... -race -coverprofile=coverage.txt -covermode=atomic
-timeout 10m
+ if [ $? -ne 0 ]; then
+ echo "❌ Unit tests failed"
+ exit 1
+ fi
+ echo "✅ Unit tests completed successfully"
+
+ - name: "Archive test results"
+ uses: actions/upload-artifact@v4
+ with:
+ name: test-results
+ path: coverage.txt
+ retention-days: 7
+ overwrite: true
diff --git a/pkg/datasource/sql/types/image.go
b/pkg/datasource/sql/types/image.go
index 3244cef9..62d836a7 100644
--- a/pkg/datasource/sql/types/image.go
+++ b/pkg/datasource/sql/types/image.go
@@ -18,6 +18,7 @@
package types
import (
+ "database/sql/driver"
"encoding/base64"
"encoding/json"
"reflect"
@@ -117,14 +118,16 @@ type RecordImage struct {
// Rows data row
Rows []RowImage `json:"rows"`
// TableMeta table information schema
- TableMeta *TableMeta `json:"-"`
+ TableMeta *TableMeta `json:"-"`
+ PrimaryKeyMap map[string][]driver.Value `json:"primaryKeyMap,omitempty"`
}
func NewEmptyRecordImage(tableMeta *TableMeta, sqlType SQLType) *RecordImage {
return &RecordImage{
- TableName: tableMeta.TableName,
- TableMeta: tableMeta,
- SQLType: sqlType,
+ TableName: tableMeta.TableName,
+ TableMeta: tableMeta,
+ SQLType: sqlType,
+ PrimaryKeyMap: make(map[string][]driver.Value),
}
}
diff --git a/pkg/datasource/sql/undo/builder/basic_undo_log_builder_test.go
b/pkg/datasource/sql/undo/builder/basic_undo_log_builder_test.go
index 8e2f4b12..e4360101 100644
--- a/pkg/datasource/sql/undo/builder/basic_undo_log_builder_test.go
+++ b/pkg/datasource/sql/undo/builder/basic_undo_log_builder_test.go
@@ -117,6 +117,24 @@ func TestBuildLockKey(t *testing.T) {
},
"TEST2_NAME:1_one_11,2_two_22,3_three_33",
},
+ {
+ "Three Primary Keys",
+ types.TableMeta{
+ TableName: "test2_name",
+ Indexs: map[string]types.IndexMeta{
+ "PRIMARY_KEY": {IType:
types.IndexTypePrimaryKey, Columns: columnsThreePk},
+ },
+ },
+ types.RecordImage{
+ TableName: "test2_name",
+ Rows: []types.RowImage{
+
{[]types.ColumnImage{getColumnImage("id", 1), getColumnImage("userId", "one"),
getColumnImage("age", "11")}},
+
{[]types.ColumnImage{getColumnImage("id", 2), getColumnImage("userId", "two"),
getColumnImage("age", "22")}},
+
{[]types.ColumnImage{getColumnImage("id", 3), getColumnImage("userId",
"three"), getColumnImage("age", "33")}},
+ },
+ },
+ "TEST2_NAME:1_one_11,2_two_22,3_three_33",
+ },
{
name: "Single Primary Key",
metaData: types.TableMeta{
diff --git
a/pkg/datasource/sql/undo/builder/mysql_insertonduplicate_update_undo_log_builder.go
b/pkg/datasource/sql/undo/builder/mysql_insertonduplicate_update_undo_log_builder.go
index 56de4a36..e00e2a51 100644
---
a/pkg/datasource/sql/undo/builder/mysql_insertonduplicate_update_undo_log_builder.go
+++
b/pkg/datasource/sql/undo/builder/mysql_insertonduplicate_update_undo_log_builder.go
@@ -97,68 +97,108 @@ func (u *MySQLInsertOnDuplicateUndoLogBuilder)
buildBeforeImageSQL(insertStmt *a
if err := checkDuplicateKeyUpdate(insertStmt, metaData); err != nil {
return "", nil, err
}
- var selectArgs []driver.Value
+ u.BeforeImageSqlPrimaryKeys = make(map[string]bool,
len(metaData.Indexs))
pkIndexMap := u.getPkIndex(insertStmt, metaData)
var pkIndexArray []int
for _, val := range pkIndexMap {
- tmpVal := val
- pkIndexArray = append(pkIndexArray, tmpVal)
+ pkIndexArray = append(pkIndexArray, val)
}
insertRows, err := getInsertRows(insertStmt, pkIndexArray)
if err != nil {
return "", nil, err
}
- insertNum := len(insertRows)
paramMap, err := u.buildImageParameters(insertStmt, args, insertRows)
if err != nil {
return "", nil, err
}
-
- sql := strings.Builder{}
- sql.WriteString("SELECT * FROM " + metaData.TableName + " ")
+ if len(paramMap) == 0 || len(metaData.Indexs) == 0 {
+ return "", nil, nil
+ }
+ hasPK := false
+ for _, index := range metaData.Indexs {
+ if strings.EqualFold("PRIMARY", index.Name) {
+ allPKColumnsHaveValue := true
+ for _, col := range index.Columns {
+ if params, ok := paramMap[col.ColumnName]; !ok
|| len(params) == 0 || params[0] == nil {
+ allPKColumnsHaveValue = false
+ break
+ }
+ }
+ hasPK = allPKColumnsHaveValue
+ break
+ }
+ }
+ if !hasPK {
+ hasValidUniqueIndex := false
+ for _, index := range metaData.Indexs {
+ if !index.NonUnique && !strings.EqualFold("PRIMARY",
index.Name) {
+ if _, _, valid := validateIndexPrefix(index,
paramMap, 0); valid {
+ hasValidUniqueIndex = true
+ break
+ }
+ }
+ }
+ if !hasValidUniqueIndex {
+ return "", nil, nil
+ }
+ }
+ var sql strings.Builder
+ sql.WriteString("SELECT * FROM " + metaData.TableName + " ")
+ var selectArgs []driver.Value
isContainWhere := false
- for i := 0; i < insertNum; i++ {
- finalI := i
- paramAppenderTempList := make([]driver.Value, 0)
+ hasConditions := false
+ for i := 0; i < len(insertRows); i++ {
+ var rowConditions []string
+ var rowArgs []driver.Value
+ usedParams := make(map[string]bool)
+
+ // First try unique indexes
for _, index := range metaData.Indexs {
- //unique index
- if index.NonUnique || isIndexValueNotNull(index,
paramMap, finalI) == false {
+ if index.NonUnique || strings.EqualFold("PRIMARY",
index.Name) {
continue
}
- columnIsNull := true
- uniqueList := make([]string, 0)
- for _, columnMeta := range index.Columns {
- columnName :=
strings.ToLower(columnMeta.ColumnName)
- imageParameters, ok := paramMap[columnName]
- if !ok && columnMeta.ColumnDef != nil {
- if strings.EqualFold("PRIMARY",
index.Name) {
-
u.BeforeImageSqlPrimaryKeys[columnName] = true
- }
- uniqueList = append(uniqueList,
columnName+" = DEFAULT("+columnName+") ")
- columnIsNull = false
- continue
- }
- if strings.EqualFold("PRIMARY", index.Name) {
- u.BeforeImageSqlPrimaryKeys[columnName]
= true
+ if conditions, args, valid :=
validateIndexPrefix(index, paramMap, i); valid {
+ rowConditions = append(rowConditions,
"("+strings.Join(conditions, " and ")+")")
+ rowArgs = append(rowArgs, args...)
+ hasConditions = true
+ for _, colMeta := range index.Columns {
+ usedParams[colMeta.ColumnName] = true
}
- columnIsNull = false
- uniqueList = append(uniqueList, columnName+" =
? ")
- paramAppenderTempList =
append(paramAppenderTempList, imageParameters[finalI])
}
+ }
- if !columnIsNull {
- if isContainWhere {
- sql.WriteString(" OR (" +
strings.Join(uniqueList, " and ") + ") ")
- } else {
- sql.WriteString(" WHERE (" +
strings.Join(uniqueList, " and ") + ") ")
- isContainWhere = true
+ // Then try primary key
+ for _, index := range metaData.Indexs {
+ if !strings.EqualFold("PRIMARY", index.Name) {
+ continue
+ }
+ if conditions, args, valid :=
validateIndexPrefix(index, paramMap, i); valid {
+ rowConditions = append(rowConditions,
"("+strings.Join(conditions, " and ")+")")
+ rowArgs = append(rowArgs, args...)
+ hasConditions = true
+ for _, colMeta := range index.Columns {
+ usedParams[colMeta.ColumnName] = true
}
}
}
- selectArgs = append(selectArgs, paramAppenderTempList...)
+
+ if len(rowConditions) > 0 {
+ if !isContainWhere {
+ sql.WriteString("WHERE ")
+ isContainWhere = true
+ } else {
+ sql.WriteString(" OR ")
+ }
+ sql.WriteString(strings.Join(rowConditions, " OR ") +
" ")
+ selectArgs = append(selectArgs, rowArgs...)
+ }
+ }
+ if !hasConditions {
+ return "", nil, nil
}
- log.Infof("build select sql by insert on duplicate sourceQuery, sql
{}", sql.String())
- return sql.String(), selectArgs, nil
+ sqlStr := sql.String()
+ log.Infof("build select sql by insert on duplicate sourceQuery, sql:
%s", sqlStr)
+ return sqlStr, selectArgs, nil
}
func (u *MySQLInsertOnDuplicateUndoLogBuilder) AfterImage(ctx context.Context,
execCtx *types.ExecContext, beforeImages []*types.RecordImage)
([]*types.RecordImage, error) {
@@ -168,14 +208,14 @@ func (u *MySQLInsertOnDuplicateUndoLogBuilder)
AfterImage(ctx context.Context, e
log.Errorf("build prepare stmt: %+v", err)
return nil, err
}
-
+ defer stmt.Close()
+ tableName :=
execCtx.ParseContext.InsertStmt.Table.TableRefs.Left.(*ast.TableSource).Source.(*ast.TableName).Name.O
+ metaData := execCtx.MetaDataMap[tableName]
rows, err := stmt.Query(selectArgs)
if err != nil {
- log.Errorf("stmt query: %+v", err)
return nil, err
}
- tableName :=
execCtx.ParseContext.InsertStmt.Table.TableRefs.Left.(*ast.TableSource).Source.(*ast.TableName).Name.O
- metaData := execCtx.MetaDataMap[tableName]
+ defer rows.Close()
image, err := u.buildRecordImages(rows, &metaData)
if err != nil {
return nil, err
@@ -185,11 +225,13 @@ func (u *MySQLInsertOnDuplicateUndoLogBuilder)
AfterImage(ctx context.Context, e
func (u *MySQLInsertOnDuplicateUndoLogBuilder) buildAfterImageSQL(ctx
context.Context, beforeImages []*types.RecordImage) (string, []driver.Value) {
selectSQL, selectArgs := u.BeforeSelectSql, u.Args
-
var beforeImage *types.RecordImage
if len(beforeImages) > 0 {
beforeImage = beforeImages[0]
}
+ if beforeImage == nil || len(beforeImage.Rows) == 0 {
+ return selectSQL, selectArgs
+ }
primaryValueMap := make(map[string][]interface{})
for _, row := range beforeImage.Rows {
for _, col := range row.Columns {
@@ -198,25 +240,46 @@ func (u *MySQLInsertOnDuplicateUndoLogBuilder)
buildAfterImageSQL(ctx context.Co
}
}
}
-
var afterImageSql strings.Builder
- var primaryValues []driver.Value
afterImageSql.WriteString(selectSQL)
- for i := 0; i < len(beforeImage.Rows); i++ {
- wherePrimaryList := make([]string, 0)
- for name, value := range primaryValueMap {
- if !u.BeforeImageSqlPrimaryKeys[name] {
- wherePrimaryList = append(wherePrimaryList,
name+" = ? ")
- primaryValues = append(primaryValues, value[i])
+ if len(primaryValueMap) == 0 || len(selectArgs) ==
len(beforeImage.Rows)*len(primaryValueMap) {
+ return selectSQL, selectArgs
+ }
+ var primaryValues []driver.Value
+ usedPrimaryKeys := make(map[string]bool)
+ for name := range primaryValueMap {
+ if !u.BeforeImageSqlPrimaryKeys[name] {
+ usedPrimaryKeys[name] = true
+ for i := 0; i < len(beforeImage.Rows); i++ {
+ if value := primaryValueMap[name][i]; value !=
nil {
+ if dv, ok := value.(driver.Value); ok {
+ primaryValues =
append(primaryValues, dv)
+ } else {
+ primaryValues =
append(primaryValues, value)
+ }
+ }
}
}
- if len(wherePrimaryList) != 0 {
- afterImageSql.WriteString(" OR (" +
strings.Join(wherePrimaryList, " and ") + ") ")
+ }
+ if len(primaryValues) > 0 {
+ afterImageSql.WriteString(" OR (" +
strings.Join(u.buildPrimaryKeyConditions(primaryValueMap, usedPrimaryKeys), "
and ") + ") ")
+ }
+ finalArgs := make([]driver.Value, len(selectArgs)+len(primaryValues))
+ copy(finalArgs, selectArgs)
+ copy(finalArgs[len(selectArgs):], primaryValues)
+ sqlStr := afterImageSql.String()
+ log.Infof("build after select sql by insert on duplicate sourceQuery,
sql %s", sqlStr)
+ return sqlStr, finalArgs
+}
+
+func (u *MySQLInsertOnDuplicateUndoLogBuilder)
buildPrimaryKeyConditions(primaryValueMap map[string][]interface{},
usedPrimaryKeys map[string]bool) []string {
+ var conditions []string
+ for name := range primaryValueMap {
+ if !usedPrimaryKeys[name] {
+ conditions = append(conditions, name+" = ? ")
}
}
- selectArgs = append(selectArgs, primaryValues...)
- log.Infof("build after select sql by insert on duplicate sourceQuery,
sql {}", afterImageSql.String())
- return afterImageSql.String(), selectArgs
+ return conditions
}
func checkDuplicateKeyUpdate(insert *ast.InsertStmt, metaData types.TableMeta)
error {
@@ -243,11 +306,10 @@ func checkDuplicateKeyUpdate(insert *ast.InsertStmt,
metaData types.TableMeta) e
// build sql params
func (u *MySQLInsertOnDuplicateUndoLogBuilder) buildImageParameters(insert
*ast.InsertStmt, args []driver.Value, insertRows [][]interface{})
(map[string][]driver.Value, error) {
- var (
- parameterMap = make(map[string][]driver.Value)
- )
+ parameterMap := make(map[string][]driver.Value)
insertColumns := getInsertColumns(insert)
- var placeHolderIndex = 0
+ placeHolderIndex := 0
+
for _, row := range insertRows {
if len(row) != len(insertColumns) {
log.Errorf("insert row's column size not equal to
insert column size")
@@ -256,13 +318,14 @@ func (u *MySQLInsertOnDuplicateUndoLogBuilder)
buildImageParameters(insert *ast.
for i, col := range insertColumns {
columnName := strings.ToLower(executor.DelEscape(col,
types.DBTypeMySQL))
val := row[i]
- rStr, ok := val.(string)
- if ok && strings.EqualFold(rStr, SqlPlaceholder) {
- objects := args[placeHolderIndex]
- parameterMap[columnName] =
append(parameterMap[col], objects)
+ if str, ok := val.(string); ok &&
strings.EqualFold(str, SqlPlaceholder) {
+ if placeHolderIndex >= len(args) {
+ return nil, fmt.Errorf("not enough
parameters for placeholders")
+ }
+ parameterMap[columnName] =
append(parameterMap[columnName], args[placeHolderIndex])
placeHolderIndex++
} else {
- parameterMap[columnName] =
append(parameterMap[col], val)
+ parameterMap[columnName] =
append(parameterMap[columnName], val)
}
}
}
@@ -296,3 +359,28 @@ func isIndexValueNotNull(indexMeta types.IndexMeta,
imageParameterMap map[string
}
return true
}
+
+func validateIndexPrefix(index types.IndexMeta, paramMap
map[string][]driver.Value, rowIndex int) ([]string, []driver.Value, bool) {
+ var indexConditions []string
+ var indexArgs []driver.Value
+ if len(index.Columns) > 1 {
+ for _, colMeta := range index.Columns {
+ params, ok := paramMap[colMeta.ColumnName]
+ if !ok || len(params) <= rowIndex || params[rowIndex]
== nil {
+ return nil, nil, false
+ }
+ }
+ }
+ for _, colMeta := range index.Columns {
+ columnName := colMeta.ColumnName
+ params, ok := paramMap[columnName]
+ if ok && len(params) > rowIndex && params[rowIndex] != nil {
+ indexConditions = append(indexConditions, columnName+"
= ? ")
+ indexArgs = append(indexArgs, params[rowIndex])
+ }
+ }
+ if len(indexConditions) != len(index.Columns) {
+ return nil, nil, false
+ }
+ return indexConditions, indexArgs, true
+}
diff --git
a/pkg/datasource/sql/undo/builder/mysql_insertonduplicate_update_undo_log_builder_test.go
b/pkg/datasource/sql/undo/builder/mysql_insertonduplicate_update_undo_log_builder_test.go
index 59e673f7..51b00ffe 100644
---
a/pkg/datasource/sql/undo/builder/mysql_insertonduplicate_update_undo_log_builder_test.go
+++
b/pkg/datasource/sql/undo/builder/mysql_insertonduplicate_update_undo_log_builder_test.go
@@ -46,7 +46,7 @@ func TestInsertOnDuplicateBuildBeforeImageSQL(t *testing.T) {
)
columnId := types.ColumnMeta{
ColumnDef: nil,
- ColumnName: "ID",
+ ColumnName: "id",
}
columnName := types.ColumnMeta{
ColumnDef: nil,
@@ -56,12 +56,12 @@ func TestInsertOnDuplicateBuildBeforeImageSQL(t *testing.T)
{
ColumnDef: nil,
ColumnName: "age",
}
- columns["ID"] = columnId
+ columns["id"] = columnId
columns["name"] = columnName
columns["age"] = columnAge
columnMeta1 = append(columnMeta1, columnId)
columnMeta2 = append(columnMeta2, columnName, columnAge)
- index["ID"] = types.IndexMeta{
+ index["id"] = types.IndexMeta{
Name: "PRIMARY",
IType: types.IndexTypePrimaryKey,
Columns: columnMeta1,
@@ -99,33 +99,29 @@ func TestInsertOnDuplicateBuildBeforeImageSQL(t *testing.T)
{
sourceQueryArgs []driver.Value
expectQuery1 string
expectQueryArgs1 []driver.Value
- expectQuery2 string
- expectQueryArgs2 []driver.Value
}{
{
+ name: "normal 1",
execCtx: &types.ExecContext{
Query: "insert into t_user(id, name, age)
values(?,?,?) on duplicate key update name = ?,age = ?",
MetaDataMap:
map[string]types.TableMeta{"t_user": tableMeta1},
},
sourceQueryArgs: []driver.Value{1, "Jack1", 81,
"Link", 18},
- expectQuery1: "SELECT * FROM t_user WHERE (id = ?
) OR (name = ? and age = ? ) ",
- expectQueryArgs1: []driver.Value{1, "Jack1", 81},
- expectQuery2: "SELECT * FROM t_user WHERE (name =
? and age = ? ) OR (id = ? ) ",
- expectQueryArgs2: []driver.Value{"Jack1", 81, 1},
+ expectQuery1: "SELECT * FROM t_user WHERE (name =
? and age = ? ) OR (id = ? ) ",
+ expectQueryArgs1: []driver.Value{"Jack1", 81, 1},
},
{
+ name: "normal 2",
execCtx: &types.ExecContext{
Query: "insert into t_user(id, name, age)
values(1,'Jack1',?) on duplicate key update name = 'Michael',age = ?",
MetaDataMap:
map[string]types.TableMeta{"t_user": tableMeta1},
},
sourceQueryArgs: []driver.Value{81, "Link", 18},
- expectQuery1: "SELECT * FROM t_user WHERE (id = ?
) OR (name = ? and age = ? ) ",
- expectQueryArgs1: []driver.Value{int64(1), "Jack1", 81},
- expectQuery2: "SELECT * FROM t_user WHERE (name =
? and age = ? ) OR (id = ? ) ",
- expectQueryArgs2: []driver.Value{"Jack1", 81, int64(1)},
+ expectQuery1: "SELECT * FROM t_user WHERE (name =
? and age = ? ) OR (id = ? ) ",
+ expectQueryArgs1: []driver.Value{"Jack1", 81, int64(1)},
},
- // multi insert one index
{
+ name: "multi insert one index",
execCtx: &types.ExecContext{
Query: "insert into t_user(id, name, age)
values(?,?,?),(?,?,?) on duplicate key update name = ?,age = ?",
MetaDataMap:
map[string]types.TableMeta{"t_user": tableMeta2},
@@ -135,6 +131,7 @@ func TestInsertOnDuplicateBuildBeforeImageSQL(t *testing.T)
{
expectQueryArgs1: []driver.Value{"Jack1", 81, "Michal",
35},
},
{
+ name: "multi insert one index",
execCtx: &types.ExecContext{
Query: "insert into t_user(id, name, age)
values(?,'Jack1',?),(?,?,35) on duplicate key update name = 'Faker',age = ?",
MetaDataMap:
map[string]types.TableMeta{"t_user": tableMeta2},
@@ -143,6 +140,83 @@ func TestInsertOnDuplicateBuildBeforeImageSQL(t
*testing.T) {
expectQuery1: "SELECT * FROM t_user WHERE (name =
? and age = ? ) OR (name = ? and age = ? ) ",
expectQueryArgs1: []driver.Value{"Jack1", 81, "Michal",
int64(35)},
},
+ // Test case for null unique index
+ {
+ name: "null unique index",
+ execCtx: &types.ExecContext{
+ Query: "insert into t_user(id, name, age)
values(?, ?, ?) on duplicate key update age = ?",
+ MetaDataMap:
map[string]types.TableMeta{"t_user": tableMeta1},
+ },
+ sourceQueryArgs: []driver.Value{1, nil, 2, 5},
+ expectQuery1: "SELECT * FROM t_user WHERE (id = ?
) ",
+ expectQueryArgs1: []driver.Value{1},
+ },
+ // Test case for null primary key
+ {
+ name: "null primary key",
+ execCtx: &types.ExecContext{
+ Query: "insert into t_user(id, name, age)
values(?, ?, ?) on duplicate key update age = ?",
+ MetaDataMap:
map[string]types.TableMeta{"t_user": tableMeta1},
+ },
+ sourceQueryArgs: []driver.Value{nil, "Jack1", 5, 2},
+ expectQuery1: "SELECT * FROM t_user WHERE (name =
? and age = ? ) ",
+ expectQueryArgs1: []driver.Value{"Jack1", 5},
+ },
+ // Test case for null unique index with no primary key
+ {
+ name: "unique index with no primary key",
+ execCtx: &types.ExecContext{
+ Query: "insert into t_user(name, age)
values(?, ?) on duplicate key update age = ?",
+ MetaDataMap:
map[string]types.TableMeta{"t_user": tableMeta2},
+ },
+ sourceQueryArgs: []driver.Value{nil, 2, 5},
+ expectQuery1: "",
+ expectQueryArgs1: nil,
+ },
+ // Test case for null unique index with no primary key
+ {
+ name: "no key",
+ execCtx: &types.ExecContext{
+ Query: "insert into t_user(name)
values(?) on duplicate key update age = ?",
+ MetaDataMap:
map[string]types.TableMeta{"t_user": tableMeta1},
+ },
+ sourceQueryArgs: []driver.Value{"Jack", 5},
+ expectQuery1: "",
+ expectQueryArgs1: nil,
+ },
+ // Test case for composite index with all columns
+ {
+ name: "composite_index_full",
+ execCtx: &types.ExecContext{
+ Query: "insert into t_user(id, name, age)
values(?,?,?) on duplicate key update other = ?",
+ MetaDataMap:
map[string]types.TableMeta{"t_user": tableMeta1},
+ },
+ sourceQueryArgs: []driver.Value{1, "Jack", 25,
"other"},
+ expectQuery1: "SELECT * FROM t_user WHERE (name =
? and age = ? ) OR (id = ? ) ",
+ expectQueryArgs1: []driver.Value{"Jack", 25, 1},
+ },
+ // Test case for composite index with null value
+ {
+ name: "composite_index_with_null",
+ execCtx: &types.ExecContext{
+ Query: "insert into t_user(id, name, age)
values(?,?,?) on duplicate key update other = ?",
+ MetaDataMap:
map[string]types.TableMeta{"t_user": tableMeta1},
+ },
+ sourceQueryArgs: []driver.Value{1, "Jack", nil,
"other"},
+ expectQuery1: "SELECT * FROM t_user WHERE (id = ?
) ",
+ expectQueryArgs1: []driver.Value{1},
+ },
+ // Test case for composite index with leftmost prefix only
+ {
+ name: "composite_index_leftmost_prefix",
+ execCtx: &types.ExecContext{
+ Query: "insert into t_user(id, name)
values(?,?) on duplicate key update other = ?",
+ MetaDataMap:
map[string]types.TableMeta{"t_user": tableMeta1},
+ },
+ sourceQueryArgs: []driver.Value{1, "Jack", "other"},
+ expectQuery1: "SELECT * FROM t_user WHERE (id = ?
) ",
+ expectQueryArgs1: []driver.Value{1},
+ },
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
@@ -151,13 +225,9 @@ func TestInsertOnDuplicateBuildBeforeImageSQL(t
*testing.T) {
tt.execCtx.ParseContext = c
query, args, err :=
builder.buildBeforeImageSQL(tt.execCtx.ParseContext.InsertStmt,
tt.execCtx.MetaDataMap["t_user"], tt.sourceQueryArgs)
assert.Nil(t, err)
- if query == tt.expectQuery1 {
- assert.Equal(t, tt.expectQuery1, query)
- assert.Equal(t, tt.expectQueryArgs1, args)
- } else {
- assert.Equal(t, tt.expectQuery2, query)
- assert.Equal(t, tt.expectQueryArgs2, args)
- }
+
+ assert.Equal(t, tt.expectQuery1, query)
+ assert.Equal(t, tt.expectQueryArgs1, args)
})
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
