Renaming a table inside a transaction and using it doesn't work #39664
Description
This happens only if that multi-statement query is inside a transaction (which is how diesel runs migrations).
Here is the minimal reproduction:
mariadb --host=127.0.0.1 --port=4000 --user=root --database test --delimiter ';;;' --execute "$(cat << EOF
-- First migration creates the table foo
BEGIN;
;;;
CREATE TABLE foo(pk CHAR(1) PRIMARY KEY);
;;;
COMMIT;
-- Second migration renames foo, creates a new foo,
-- and selects into the new foo from the old foo
;;;
BEGIN;
;;;
ALTER TABLE foo RENAME TO bar;
CREATE TABLE foo(pk CHAR(1) PRIMARY KEY);
INSERT INTO foo (pk) SELECT pk FROM bar;
;;;
COMMIT;
;;;
EOF
)"
Tested against a new instance run by tiup:
*************************** 1. row ***************************
tidb_version(): Release Version: v6.4.0
Edition: Community
Git Commit Hash: cf36a9ce2fe1039db3cf3444d51930b887df18a1
Git Branch: heads/refs/tags/v6.4.0
UTC Build Time: 2022-11-13 05:25:30
GoVersion: go1.19.2
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv
Originally posted by @Pythoner6 in #24194 (comment)