Repository: pingcap/tidb
File: pkg/executor/test/loaddatatest/load_data_test.go (Go)

Code before:
	tk.MustExec("use test")
	tk.MustExec("create table load_data_test (a int)")
	err := tk.ExecToErr("load data infile 'remote.csv' into table load_data_test")
	require.ErrorContains(t, err, "[executor:8154]Don't support load data from tidb-server's disk.")
}

func TestFix56408(t *testing.T) {
	store := testkit.CreateMockStore(t)
	tk := testkit.NewTestKit(t, store)
	tk.MustExec("USE test; DROP TABLE IF EXISTS load_data_replace;")
	tk.MustExec("create table a(id int,name varchar(20),addr varchar(100),primary key (id) nonclustered);")
	loadSQL := "LOAD DATA LOCAL INFILE '/tmp/nonexistence.csv' REPLACE INTO TABLE a FIELDS terminated by '|';"
	ctx := tk.Session().(sessionctx.Context)
	tests := []testCase{
		{[]byte("1|aa|beijing\n1|aa|beijing\n1|aa|beijing\n1|aa|beijing\n2|bb|shanghai\n2|bb|shanghai\n2|bb|shanghai\n3|cc|guangzhou\n"),
			[]string{"1 aa beijing", "2 bb shanghai", "3 cc guangzhou"},
			"Records: 8  Deleted: 0  Skipped: 5  Warnings: 0",
		},
	}
	deleteSQL := "DO 1"
	selectSQL := "TABLE a;"
	checkCases(tests, loadSQL, t, tk, ctx, selectSQL, deleteSQL)
	tk.MustExec("ADMIN CHECK TABLE a")
}

func TestLoadDataForSoftDeleteTable(t *testing.T) {
	store := testkit.CreateMockStore(t)
	tk := testkit.NewTestKit(t, store)
	tk.MustExec("USE test; DROP TABLE IF EXISTS softdelete;")
	tk.MustExec("create table softdelete (id int primary key, v int) softdelete retention 7 day")
	ctx := tk.Session().(sessionctx.Context)
	deleteSQL := "delete from softdelete"
	selectSQL := "select * from softdelete"

	loadSQLs := []string{
		"LOAD DATA LOCAL INFILE '/tmp/nonexistence.csv' REPLACE INTO TABLE softdelete fields terminated by ' ' lines terminated by '\n' (id, v)",
		"LOAD DATA LOCAL INFILE '/tmp/nonexistence.csv' IGNORE INTO TABLE softdelete fields terminated by ' ' lines terminated by '\n' (id, v)",
		"LOAD DATA LOCAL INFILE '/tmp/nonexistence.csv' INTO TABLE softdelete fields terminated by ' ' lines terminated by '\n' (id, v)",
	}
	expecteds := [][]string{
		{"1|11", "2|22", "3|33", "4|44", "5|55", "6|66"},
		{"1|1", "2|2", "3|33", "4|44", "5|55", "6|66"},
		{"1|1", "2|2", "3|33", "4|44", "5|55", "6|66"},
	}
	expectedMsgs := []string{
		"Records: 6  Deleted: 4  Skipped: 0  Warnings: 0",
		"Records: 6  Deleted: 4  Skipped: 6  Warnings: 2",
		"Records: 6  Deleted: 4  Skipped: 6  Warnings: 2",
	}

	for i, loadSQL := range loadSQLs {

Review comment (suggestion):
We should also add some cases when `tidb_translate_softdelete_sql` is `OFF` 

Code after:
	tk.MustExec("use test")
	tk.MustExec("create table load_data_test (a int)")
	err := tk.ExecToErr("load data infile 'remote.csv' into table load_data_test")
	require.ErrorContains(t, err, "[executor:8154]Don't support load data from tidb-server's disk.")
}

func TestFix56408(t *testing.T) {
	store := testkit.CreateMockStore(t)
	tk := testkit.NewTestKit(t, store)
	tk.MustExec("USE test; DROP TABLE IF EXISTS load_data_replace;")
	tk.MustExec("create table a(id int,name varchar(20),addr varchar(100),primary key (id) nonclustered);")
	loadSQL := "LOAD DATA LOCAL INFILE '/tmp/nonexistence.csv' REPLACE INTO TABLE a FIELDS terminated by '|';"
	ctx := tk.Session().(sessionctx.Context)
	tests := []testCase{
		{[]byte("1|aa|beijing\n1|aa|beijing\n1|aa|beijing\n1|aa|beijing\n2|bb|shanghai\n2|bb|shanghai\n2|bb|shanghai\n3|cc|guangzhou\n"),
			[]string{"1 aa beijing", "2 bb shanghai", "3 cc guangzhou"},
			"Records: 8  Deleted: 0  Skipped: 5  Warnings: 0",
		},
	}
	deleteSQL := "DO 1"
	selectSQL := "TABLE a;"
	checkCases(tests, loadSQL, t, tk, ctx, selectSQL, deleteSQL)
	tk.MustExec("ADMIN CHECK TABLE a")
}

func TestLoadDataForSoftDeleteTable(t *testing.T) {
	store := testkit.CreateMockStore(t)
	tk := testkit.NewTestKit(t, store)
	testLoadDataForSoftDeleteTable1(t, tk)
	testLoadDataForSoftDeleteTable2(t, tk)
	testLoadDataForSoftDeleteTable3(t, tk)
}

func testLoadDataForSoftDeleteTable1(t *testing.T, tk *testkit.TestKit) {
	tk.MustExec("USE test; DROP TABLE IF EXISTS softdelete;")
	tk.MustExec("create table softdelete (id int primary key, v int) softdelete retention 7 day")
	ctx := tk.Session().(sessionctx.Context)
	deleteSQL := "delete from softdelete"
	selectSQL := "select * from softdelete"
	loadSQLs := []string{
		"LOAD DATA LOCAL INFILE '/tmp/nonexistence.csv' REPLACE INTO TABLE softdelete fields terminated by ' ' lines terminated by '\n'",
		"LOAD DATA LOCAL INFILE '/tmp/nonexistence.csv' IGNORE INTO TABLE softdelete fields terminated by ' ' lines terminated by '\n'",
		"LOAD DATA LOCAL INFILE '/tmp/nonexistence.csv' INTO TABLE softdelete fields terminated by ' ' lines terminated by '\n'",

		// Again with specify columns
		"LOAD DATA LOCAL INFILE '/tmp/nonexistence.csv' REPLACE INTO TABLE softdelete fields terminated by ' ' lines terminated by '\n' (id, v)",
		"LOAD DATA LOCAL INFILE '/tmp/nonexistence.csv' IGNORE INTO TABLE softdelete fields terminated by ' ' lines terminated by '\n' (id, v)",
		"LOAD DATA LOCAL INFILE '/tmp/nonexistence.csv' INTO TABLE softdelete fields terminated by ' ' lines terminated by '\n' (id, v)",
	}
	expecteds := [][]string{
		{"1|11", "2|22", "3|33", "4|44", "5|55", "6|66"},