Materialize/test/sqllogictest/cockroach/rename_table.slt

# 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