Got wrong result combine plan cache and stale read

[lcwangchao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

func TestCache(t *testing.T) {
	store, _, clean := testkit.CreateMockStoreAndDomain(t)
	defer clean()

	tk := testkit.NewTestKit(t, store)
	tk.MustExec("use test")
	tk.MustExec("create table t1 (id int primary key, v int)")
	tk.MustExec("insert into t1 values(1, 10)")
	se := tk.Session()

	time.Sleep(time.Millisecond * 100)
	tk.MustExec("set @a=now(6)")
	time.Sleep(time.Millisecond * 100)
	tk.MustExec("update t1 set v=100 where id=1")

	stmtID1, _, _, err := se.PrepareStmt("select * from t1 as of timestamp @a where id=1")
	require.NoError(t, err)

	rs, err := se.ExecutePreparedStmt(context.TODO(), stmtID1, nil)
	require.NoError(t, err)
	tk.ResultSetToResult(rs, fmt.Sprintf("%v", rs)).Check(testkit.Rows("1 10"))

	rs, err = se.ExecutePreparedStmt(context.TODO(), stmtID1, nil)
	require.NoError(t, err)
	tk.ResultSetToResult(rs, fmt.Sprintf("%v", rs)).Check(testkit.Rows("1 10"))

	rs, err = se.ExecutePreparedStmt(context.TODO(), stmtID1, nil)
	require.NoError(t, err)
	tk.ResultSetToResult(rs, fmt.Sprintf("%v", rs)).Check(testkit.Rows("1 10"))
}

2. What did you expect to see? (Required)

Test case should success

3. What did you see instead (Required)

Test case failed

4. What is your TiDB version? (Required)

master

[lcwangchao]

We can use go mysql driver to produce this issue:

import (
	"context"
	"database/sql"
	"testing"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/stretchr/testify/require"
)

func TestPrepareExecute(t *testing.T) {
	ctx := context.TODO()
	db, err := sql.Open("mysql", "root@tcp(127.0.0.1:4001)/test")
	require.NoError(t, err)

	conn, err := db.Conn(ctx)
	require.NoError(t, err)

	_, err = conn.ExecContext(ctx, "drop table if exists t1")
	require.NoError(t, err)
	_, err = conn.ExecContext(ctx, "create table t1 (id int primary key, v int)")
	require.NoError(t, err)
	_, err = conn.ExecContext(ctx, "insert into t1 values(1, 10)")
	require.NoError(t, err)

	time.Sleep(time.Millisecond * 100)
	_, err = conn.ExecContext(ctx, "set @a=now(6)")
	time.Sleep(time.Millisecond * 100)
	_, err = conn.ExecContext(ctx, "update t1 set v=100 where id=1")

	stmt, err := conn.PrepareContext(context.TODO(), "select * from t1 as of timestamp @a where id=1")
	require.NoError(t, err)

	require.Equal(t, []int{1, 10}, executePreparedStmtResult(t, stmt))
	require.Equal(t, []int{1, 10}, executePreparedStmtResult(t, stmt))
	// The third assert will fail
	require.Equal(t, []int{1, 10}, executePreparedStmtResult(t, stmt))
}

func executePreparedStmtResult(t *testing.T, stmt *sql.Stmt) []int {
	var id, v int
	rows, err := stmt.Query()
	require.NoError(t, err)
	require.True(t, rows.Next())
	require.NoError(t, rows.Scan(&id, &v))
	require.NoError(t, rows.Close())
	return []int{id, v}
}

It will fail:

=== RUN   TestPrepareExecute
    staleread_test.go:54: 
        	Error Trace:	staleread_test.go:54
        	Error:      	Not equal: 
        	            	expected: []int{1, 10}
        	            	actual  : []int{1, 100}
        	            	
        	            	Diff:
        	            	--- Expected
        	            	+++ Actual
        	            	@@ -2,3 +2,3 @@
        	            	  (int) 1,
        	            	- (int) 10
        	            	+ (int) 100
        	            	 }
        	Test:       	TestPrepareExecute
--- FAIL: TestPrepareExecute (0.47s)


Expected :[]int{1, 10}
Actual   :[]int{1, 100}