# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved. # Copyright Materialize, Inc. and contributors. All rights reserved. # # Use of this software is governed by the Business Source License # included in the LICENSE file at the root of this repository. # # As of the Change Date specified in that file, in accordance with # the Business Source License, use of this software will be governed # by the Apache License, Version 2.0. # # This file is derived from the logic test suite in CockroachDB. The # original file was retrieved on June 10, 2019 from: # # https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/rename_table # # The original source code is subject to the terms of the Apache # 2.0 license, a copy of which can be found in the LICENSE file at the # root of this repository. # not supported yet halt mode cockroach statement error pgcode 42P01 relation "foo" does not exist ALTER TABLE foo RENAME TO bar statement ok ALTER TABLE IF EXISTS foo RENAME TO bar statement ok CREATE TABLE kv ( k INT PRIMARY KEY, v INT ) statement ok INSERT INTO kv VALUES (1, 2), (3, 4) query II rowsort SELECT * FROM kv ---- 1 2 3 4 query T SHOW TABLES ---- kv statement ok ALTER TABLE kv RENAME TO new_kv statement error pgcode 42P01 relation "kv" does not exist SELECT * FROM kv query II rowsort SELECT * FROM new_kv ---- 1 2 3 4 query T SHOW TABLES ---- new_kv # check the name in the descriptor, which is used by SHOW GRANTS, is also changed query TTTTT SHOW GRANTS ON TABLE new_kv ---- test public new_kv admin ALL test public new_kv root ALL statement error invalid table name: "" ALTER TABLE "" RENAME TO foo statement error invalid table name: "" ALTER TABLE new_kv RENAME TO "" statement ok ALTER TABLE new_kv RENAME TO new_kv statement ok CREATE TABLE t ( c1 INT PRIMARY KEY, c2 INT ) statement ok INSERT INTO t VALUES (4, 16), (5, 25) statement error pgcode 42P07 relation "new_kv" already exists ALTER TABLE t RENAME TO new_kv user testuser statement error user testuser does not have DROP privilege on relation t ALTER TABLE test.t RENAME TO t2 user root statement ok GRANT DROP ON TABLE test.t TO testuser statement ok create database test2 user testuser statement error user testuser does not have CREATE privilege on database test ALTER TABLE test.t RENAME TO t2 user root statement ok GRANT CREATE ON DATABASE test TO testuser statement ok ALTER TABLE test.t RENAME TO t2 query T SHOW TABLES ---- new_kv t2 user testuser statement error user testuser does not have CREATE privilege on database test2 ALTER TABLE test.t2 RENAME TO test2.t user root statement ok GRANT CREATE ON DATABASE test2 TO testuser statement ok GRANT DROP ON test.new_kv TO testuser user testuser statement ok ALTER TABLE test.new_kv RENAME TO test2.t statement ok ALTER TABLE test.t2 RENAME TO test2.t2 query T SHOW TABLES ---- query T SHOW TABLES FROM test2 ---- t t2 user root query II rowsort SELECT * FROM test2.t ---- 1 2 3 4 query II rowsort SELECT * FROM test2.t2 ---- 4 16 5 25 statement ok CREATE VIEW test2.v1 AS SELECT c1,c2 FROM test2.t2 statement ok ALTER TABLE test2.v1 RENAME TO test2.v2 statement ok ALTER TABLE test2.v2 RENAME TO test2.v1 statement error cannot rename relation "test2.public.t2" because view "v1" depends on it ALTER TABLE test2.t2 RENAME TO test2.t3 # Tests that uncommitted database or table names can be used by statements # in the same transaction. Also tests that if the transaction doesn't commit # the names are discarded and cannot be used by future transactions. statement ok BEGIN statement ok CREATE DATABASE d; CREATE TABLE d.kv (k CHAR PRIMARY KEY, v CHAR); statement ok INSERT INTO d.kv (k,v) VALUES ('a', 'b') statement ok COMMIT statement ok INSERT INTO d.kv (k,v) VALUES ('c', 'd') # A table rename disallows the use of the old name statement ok BEGIN statement ok ALTER TABLE d.kv RENAME TO d.kv2 statement ok INSERT INTO d.kv2 (k,v) VALUES ('e', 'f') statement error pgcode 42P01 relation \"d.kv\" does not exist INSERT INTO d.kv (k,v) VALUES ('g', 'h') statement ok ROLLBACK # A database rename disallows the use of the old name. statement ok BEGIN statement ok ALTER DATABASE d RENAME TO dnew statement ok INSERT INTO dnew.kv (k,v) VALUES ('e', 'f') statement error pgcode 42P01 relation \"d.kv\" does not exist INSERT INTO d.kv (k,v) VALUES ('g', 'h') statement ok ROLLBACK # The reuse of a name is allowed. statement ok BEGIN statement ok ALTER DATABASE d RENAME TO dnew statement ok CREATE DATABASE d statement ok CREATE TABLE d.kv (k CHAR PRIMARY KEY, v CHAR) statement ok INSERT INTO d.kv (k,v) VALUES ('a', 'b') statement ok COMMIT # Check that on a rollback a database name cannot be used. statement ok BEGIN statement ok CREATE DATABASE dd; CREATE TABLE dd.kv (k CHAR PRIMARY KEY, v CHAR) statement ok INSERT INTO dd.kv (k,v) VALUES ('a', 'b') statement ok ROLLBACK statement error pgcode 42P01 relation "dd\.kv" does not exist INSERT INTO dd.kv (k,v) VALUES ('c', 'd') # Check that on a rollback a table name cannot be used. statement ok BEGIN statement ok CREATE TABLE d.kv2 (k CHAR PRIMARY KEY, v CHAR) statement ok INSERT INTO d.kv2 (k,v) VALUES ('a', 'b') statement ok ROLLBACK statement error pgcode 42P01 relation \"d.kv2\" does not exist INSERT INTO d.kv2 (k,v) VALUES ('c', 'd') statement ok USE d query T SHOW TABLES ---- kv query TTT EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR ALTER TABLE kv RENAME TO kv2 ---- rename table · · # Verify that the EXPLAIN above does not actually rename the table (cockroach#30543) query T SHOW TABLES ---- kv