sql: the rename of a table to a different database and schema causes unexpected behaviour #55710
Description
Describe the problem
When the source table belongs to a user defined schema, moving it to another schema in a different database seems to succeed. However, unexpected errors occur when running SHOW TABLES in the destination database. The table does not seem to exist in the source or destination schema when trying to SELECT * from the table.
When the table belongs to the public schema of the source database, the table gets put in the public schema of the destination database even if another destination schema as specified.
To Reproduce
Unexpected error:
root@:26257/defaultdb> create database otherdb;
CREATE DATABASE
Time: 81ms total (execution 81ms / network 0ms)
root@:26257/defaultdb> use otherdb;
SET
Time: 24ms total (execution 24ms / network 0ms)
root@:26257/otherdb> create schema otherschema;
CREATE SCHEMA
Time: 215ms total (execution 88ms / network 127ms)
root@:26257/otherdb> use defaultdb;
SET
Time: 0ms total (execution 0ms / network 0ms)
root@:26257/defaultdb> create schema defaultschema;
CREATE SCHEMA
Time: 207ms total (execution 65ms / network 142ms)
root@:26257/defaultdb> create table defaultschema.bar();
CREATE TABLE
Time: 92ms total (execution 91ms / network 0ms)
root@:26257/defaultdb> alter table defaultschema.bar rename to otherdb.otherschema.bar;
NOTICE: renaming tables with a qualification is deprecated
HINT: use ALTER TABLE defaultschema.bar RENAME TO bar instead
RENAME TABLE
Time: 249ms total (execution 71ms / network 178ms)
root@:26257/defaultdb> use otherdb;
SET
Time: 0ms total (execution 0ms / network 0ms)
root@:26257/otherdb> show tables;
ERROR: internal error: schema id 57 not found
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/information_schema.go:1918: forEachTableDescWithTableLookupInternal()
github.com/cockroachdb/cockroach/pkg/sql/information_schema.go:1825: forEachTableDescWithTableLookup()
github.com/cockroachdb/cockroach/pkg/sql/pg_catalog.go:1072: func1()
github.com/cockroachdb/cockroach/pkg/sql/virtual_schema.go:492: 1()
github.com/cockroachdb/cockroach/pkg/sql/virtual_table.go:115: func2()
runtime/asm_amd64.s:1357: goexit()
HINT: You have encountered an unexpected error.
Please check the public issue tracker to check whether this problem is
already tracked. If you cannot find it there, please report the error
with details by creating a new issue.
If you would rather not post publicly, please contact us directly
using the support form.
We appreciate your feedback.
root@:26257/otherdb> use defaultdb;
SET
Time: 0ms total (execution 0ms / network 0ms)
root@:26257/defaultdb> select * from defaultschema.bar;
ERROR: relation "defaultschema.bar" does not exist
SQLSTATE: 42P01
root@:26257/defaultdb> use otherdb;
SET
Time: 0ms total (execution 0ms / network 0ms)
root@:26257/otherdb> select * from otherschema.bar;
ERROR: relation "otherschema.bar" does not exist
SQLSTATE: 42P01
Table gets moved to the public schema instead of the specified one:
root@:26257/defaultdb> create table foo();
CREATE TABLE
Time: 87ms total (execution 87ms / network 0ms)
root@:26257/defaultdb> create database otherdb;
CREATE DATABASE
Time: 81ms total (execution 81ms / network 0ms)
root@:26257/defaultdb> use otherdb;
SET
Time: 24ms total (execution 24ms / network 0ms)
root@:26257/otherdb> create schema otherschema;
CREATE SCHEMA
Time: 215ms total (execution 88ms / network 127ms)
root@:26257/otherdb> use defaultdb;
SET
Time: 0ms total (execution 0ms / network 0ms)
root@:26257/defaultdb> alter table foo rename to otherdb.otherschema.foo;
NOTICE: renaming tables with a qualification is deprecated
HINT: use ALTER TABLE foo RENAME TO foo instead
RENAME TABLE
Time: 223ms total (execution 55ms / network 168ms)
root@:26257/defaultdb> use otherdb;
SET
Time: 0ms total (execution 0ms / network 0ms)
root@:26257/otherdb> select * from information_schema.tables where table_name = 'foo';
table_catalog | table_schema | table_name | table_type | is_insertable_into | version
----------------+--------------+------------+------------+--------------------+----------
otherdb | public | foo | BASE TABLE | YES | 3
(1 row)
Time: 6ms total (execution 5ms / network 0ms)
Expected behavior
The table should successfully move to the correct schema in the destination database.
Environment:
- CockroachDB version [e.g. 2.0.2]