Materialize/test/sqllogictest/cockroach/rename_column.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_column
#
# 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 ok
CREATE TABLE users (
  uid    INT PRIMARY KEY,
  name  VARCHAR NOT NULL,
  title VARCHAR,
  INDEX foo (name) STORING (title),
  UNIQUE INDEX bar (uid, name)
)

statement ok
INSERT INTO users VALUES (1, 'tom', 'cat'),(2, 'jerry', 'rat')

query ITT colnames,rowsort
SELECT * FROM users
----
uid name  title
1  tom   cat
2  jerry rat

statement error column name "name" already exists
ALTER TABLE users RENAME COLUMN title TO name

statement error empty column name
ALTER TABLE users RENAME COLUMN title TO ""

statement error pgcode 42703 column "ttle" does not exist
ALTER TABLE users RENAME COLUMN ttle TO species

statement error pgcode 42P01 relation "uses" does not exist
ALTER TABLE uses RENAME COLUMN title TO species

statement ok
ALTER TABLE IF EXISTS uses RENAME COLUMN title TO species

statement ok
ALTER TABLE users RENAME COLUMN uid TO id

statement ok
ALTER TABLE users RENAME COLUMN title TO species

query ITT colnames,rowsort
SELECT * FROM users
----
id name  species
1  tom   cat
2  jerry rat

user testuser

statement error user testuser does not have CREATE privilege on relation users
ALTER TABLE users RENAME COLUMN name TO username

user root

statement ok
GRANT CREATE ON TABLE users TO testuser

user testuser

statement ok
ALTER TABLE users RENAME COLUMN name TO username

user root

query ITT colnames,rowsort
SELECT * FROM users
----
id username  species
1  tom       cat
2  jerry     rat

# Renaming a column updates the column names in an index.
query TTBITTBB colnames
SHOW INDEXES ON users
----
table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
users       primary     false       1             id           ASC        false    false
users       foo         true        1             username     ASC        false    false
users       foo         true        2             species      N/A        true     false
users       foo         true        3             id           ASC        false    true
users       bar         false       1             id           ASC        false    false
users       bar         false       2             username     ASC        false    false

statement ok
CREATE VIEW v1 AS SELECT id FROM users WHERE username = 'tom'

statement error cannot rename column "id" because view "v1" depends on it
ALTER TABLE users RENAME COLUMN id TO uid

statement error cannot rename column "username" because view "v1" depends on it
ALTER TABLE users RENAME COLUMN username TO name

# TODO(knz): restore test after cockroach#17269 / cockroach#10083 is fixed.
#statement ok
#ALTER TABLE users RENAME COLUMN species TO title

statement ok
CREATE VIEW v2 AS SELECT id from users

statement ok
DROP VIEW v1

statement error cannot rename column "id" because view "v2" depends on it
ALTER TABLE users RENAME COLUMN id TO uid

# TODO(knz): restore test after cockroach#17269 / cockroach#10083 is fixed.
# statement ok
# ALTER TABLE users RENAME COLUMN username TO name

statement ok
DROP VIEW v2

query T
SELECT column_name FROM [SHOW COLUMNS FROM users]
----
id
username
species

query TTT
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR ALTER TABLE users RENAME COLUMN species TO woo
----
alter table  ·  ·

# Verify that EXPLAIN did not actually rename the column
query T
SELECT column_name FROM [SHOW COLUMNS FROM users]
----
id
username
species

# Check that a column can be added and renamed in the same statement
statement ok
ALTER TABLE users RENAME COLUMN species TO species_old,
                  ADD COLUMN species STRING AS (species_old || ' woo') STORED

query T rowsort
SELECT species FROM users
----
cat woo
rat woo