Materialize/test/sqllogictest/cockroach/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/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

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_table_keys = true
----
COMPLETE 0

statement ok
SET DATABASE = ""

statement error no database specified
CREATE TABLE a (id INT PRIMARY KEY)

statement error invalid table name: test.""
CREATE TABLE test."" (id INT PRIMARY KEY)

statement ok
CREATE TABLE test.a (id INT PRIMARY KEY)

statement error pgcode 42P07 relation "a" already exists
CREATE TABLE test.a (id INT PRIMARY KEY)

statement ok
SET DATABASE = test

statement error invalid table name: ""
CREATE TABLE "" (id INT PRIMARY KEY)

statement error pgcode 42P07 relation "a" already exists
CREATE TABLE a (id INT PRIMARY KEY)

statement error duplicate column name: "id"
CREATE TABLE b (id INT PRIMARY KEY, id INT)

statement error multiple primary keys for table "b" are not allowed
CREATE TABLE b (id INT PRIMARY KEY, id2 INT PRIMARY KEY)

statement error index \"primary\" contains duplicate column \"a\"
CREATE TABLE dup_primary (a int, primary key (a,a))

statement error index \"dup_unique_a_a_key\" contains duplicate column \"a\"
CREATE TABLE dup_unique (a int, unique (a,a))

statement ok
CREATE TABLE IF NOT EXISTS a (id INT PRIMARY KEY)

statement ok
COMMENT ON TABLE a IS 'a_comment'

query T colnames
SHOW TABLES FROM test
----
table_name
a

statement ok
CREATE TABLE b (id INT PRIMARY KEY)

statement ok
CREATE TABLE c (
  id INT PRIMARY KEY,
  foo INT,
  bar INT,
  INDEX c_foo_idx (foo),
  INDEX (foo),
  INDEX c_foo_bar_idx (foo ASC, bar DESC),
  UNIQUE (bar)
)

query TTBITTBB colnames
SHOW INDEXES ON c
----
table_name  index_name     non_unique  seq_in_index  column_name  direction  storing  implicit
c           primary        false       1             id           ASC        false    false
c           c_foo_idx      true        1             foo          ASC        false    false
c           c_foo_idx      true        2             id           ASC        false    true
c           c_foo_idx1     true        1             foo          ASC        false    false
c           c_foo_idx1     true        2             id           ASC        false    true
c           c_foo_bar_idx  true        1             foo          ASC        false    false
c           c_foo_bar_idx  true        2             bar          DESC       false    false
c           c_foo_bar_idx  true        3             id           ASC        false    true
c           c_bar_key      false       1             bar          ASC        false    false
c           c_bar_key      false       2             id           ASC        false    true

# primary keys can never be null

statement ok
CREATE TABLE d (
  id    INT PRIMARY KEY NULL
)

query TTBTTTB colnames
SHOW COLUMNS FROM d
----
column_name  data_type  is_nullable  column_default  generation_expression  indices    is_hidden
id           INT8       false        NULL            ·                      {primary}  false

statement ok
CREATE TABLE e (
  id    INT NULL PRIMARY KEY
)

query TTBTTTB colnames
SHOW COLUMNS FROM e
----
column_name  data_type  is_nullable  column_default  generation_expression  indices    is_hidden
id           INT8       false        NULL            ·                      {primary}  false

statement ok
CREATE TABLE f (
  a INT,
  b INT,
  c INT,
  PRIMARY KEY (a, b, c)
)

query TTBTTTB colnames
SHOW COLUMNS FROM f
----
column_name  data_type  is_nullable  column_default  generation_expression  indices    is_hidden
a            INT8       false        NULL            ·                      {primary}  false
b            INT8       false        NULL            ·                      {primary}  false
c            INT8       false        NULL            ·                      {primary}  false

query TT
SHOW TABLES FROM test WITH COMMENT
----
a  a_comment
b  ·
c  ·
d  ·
e  ·
f  ·

statement ok
SET DATABASE = ""

query error pgcode 42P01 relation "users" does not exist
SHOW COLUMNS FROM users

query error pgcode 42P01 relation "test.users" does not exist
SHOW COLUMNS FROM test.users

query error pgcode 42P01 relation "users" does not exist
SHOW INDEXES ON users

query error pgcode 42P01 relation "test.users" does not exist
SHOW INDEXES ON test.users

statement ok
CREATE TABLE test.users (
  id        INT PRIMARY KEY,
  name      VARCHAR NOT NULL,
  title     VARCHAR,
  nickname  STRING CHECK (length(nickname) < 10),
  username  STRING(10),
  email     VARCHAR(100) NULL,
  INDEX foo (name),
  CHECK (length(nickname) < length(name)),
  UNIQUE INDEX bar (id, name),
  FAMILY "primary" (id, name),
  FAMILY fam_1_title (title),
  FAMILY fam_2_nickname (nickname),
  FAMILY fam_3_username_email (username, email)
)

query TTBTTTB colnames
SHOW COLUMNS ON test.users
----
column_name  data_type     is_nullable  column_default  generation_expression  indices            is_hidden
id           INT8          false        NULL            ·                      {primary,foo,bar}  false
name         VARCHAR       false        NULL            ·                      {foo,bar}          false
title        VARCHAR       true         NULL            ·                      {}                 false
nickname     STRING        true         NULL            ·                      {}                 false
username     STRING(10)    true         NULL            ·                      {}                 false
email        VARCHAR(100)  true         NULL            ·                      {}                 false

query TTBITTBB colnames
SHOW INDEXES ON test.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             name         ASC        false    false
users       foo         true        2             id           ASC        false    true
users       bar         false       1             id           ASC        false    false
users       bar         false       2             name         ASC        false    false

statement error precision for type float must be at least 1 bit
CREATE TABLE test.precision (x FLOAT(0))

statement error scale \(2\) must be between 0 and precision \(0\) at or near "\)"
CREATE TABLE test.precision (x DECIMAL(0, 2))

statement error scale \(4\) must be between 0 and precision \(2\) at or near "\)"
CREATE TABLE test.precision (x DECIMAL(2, 4))

query TT
SHOW CREATE TABLE test.users
----
test.public.users  CREATE TABLE users (
                   id INT8 NOT NULL,
                   name VARCHAR NOT NULL,
                   title VARCHAR NULL,
                   nickname STRING NULL,
                   username STRING(10) NULL,
                   email VARCHAR(100) NULL,
                   CONSTRAINT "primary" PRIMARY KEY (id ASC),
                   INDEX foo (name ASC),
                   UNIQUE INDEX bar (id ASC, name ASC),
                   FAMILY "primary" (id, name),
                   FAMILY fam_1_title (title),
                   FAMILY fam_2_nickname (nickname),
                   FAMILY fam_3_username_email (username, email),
                   CONSTRAINT check_nickname_name CHECK (length(nickname) < length(name)),
                   CONSTRAINT check_nickname CHECK (length(nickname) < 10)
)

statement ok
CREATE TABLE test.dupe_generated (
  foo INT CHECK (foo > 1),
  bar INT CHECK (bar > 2),
  CHECK (foo > 2),
  CHECK (foo < 10)
)

query TTTTB colnames
SHOW CONSTRAINTS FROM test.dupe_generated
----
table_name      constraint_name  constraint_type  details           validated
dupe_generated  check_bar        CHECK            CHECK (bar > 2)   true
dupe_generated  check_foo        CHECK            CHECK (foo > 2)   true
dupe_generated  check_foo1       CHECK            CHECK (foo < 10)  true
dupe_generated  check_foo2       CHECK            CHECK (foo > 1)   true

statement ok
CREATE TABLE test.named_constraints (
  id        INT CONSTRAINT pk PRIMARY KEY,
  name      VARCHAR CONSTRAINT nn NOT NULL,
  title     VARCHAR  CONSTRAINT def DEFAULT 'VP of Something',
  nickname  STRING CONSTRAINT ck1 CHECK (length(nickname) < 10),
  username  STRING(10) CONSTRAINT nl NULL,
  email     VARCHAR(100) CONSTRAINT uq UNIQUE,
  INDEX foo (name),
  CONSTRAINT uq2 UNIQUE (username),
  CONSTRAINT ck2 CHECK (length(nickname) < length(name)),
  UNIQUE INDEX bar (id, name),
  FAMILY "primary" (id, name),
  FAMILY fam_1_title (title),
  FAMILY fam_2_nickname (nickname),
  FAMILY fam_3_username_email (username, email)
)

query TT
SHOW CREATE TABLE test.named_constraints
----
test.public.named_constraints  CREATE TABLE named_constraints (
                               id INT8 NOT NULL,
                               name VARCHAR NOT NULL,
                               title VARCHAR NULL DEFAULT 'VP of Something':::STRING,
                               nickname STRING NULL,
                               username STRING(10) NULL,
                               email VARCHAR(100) NULL,
                               CONSTRAINT pk PRIMARY KEY (id ASC),
                               UNIQUE INDEX uq (email ASC),
                               INDEX foo (name ASC),
                               UNIQUE INDEX uq2 (username ASC),
                               UNIQUE INDEX bar (id ASC, name ASC),
                               FAMILY "primary" (id, name),
                               FAMILY fam_1_title (title),
                               FAMILY fam_2_nickname (nickname),
                               FAMILY fam_3_username_email (username, email),
                               CONSTRAINT ck2 CHECK (length(nickname) < length(name)),
                               CONSTRAINT ck1 CHECK (length(nickname) < 10)
)

query TTTTB colnames
SHOW CONSTRAINTS FROM test.named_constraints
----
table_name         constraint_name  constraint_type  details                                  validated
named_constraints  bar              UNIQUE           UNIQUE (id ASC, name ASC)                true
named_constraints  ck1              CHECK            CHECK (length(nickname) < 10)            true
named_constraints  ck2              CHECK            CHECK (length(nickname) < length(name))  true
named_constraints  pk               PRIMARY KEY      PRIMARY KEY (id ASC)                     true
named_constraints  uq               UNIQUE           UNIQUE (email ASC)                       true
named_constraints  uq2              UNIQUE           UNIQUE (username ASC)                    true

statement error duplicate constraint name: "pk"
CREATE TABLE test.dupe_named_constraints (
  id        INT CONSTRAINT pk PRIMARY KEY,
  title     VARCHAR CONSTRAINT one CHECK (1>1),
  name      VARCHAR CONSTRAINT pk UNIQUE
)

statement error duplicate constraint name: "one"
CREATE TABLE test.dupe_named_constraints (
  id        INT CONSTRAINT pk PRIMARY KEY,
  title     VARCHAR CONSTRAINT one CHECK (1>1),
  name      VARCHAR CONSTRAINT one UNIQUE
)

statement error duplicate constraint name: "one"
CREATE TABLE test.dupe_named_constraints (
  id        INT CONSTRAINT pk PRIMARY KEY,
  title     VARCHAR CONSTRAINT one CHECK (1>1),
  name      VARCHAR CONSTRAINT one REFERENCES test.named_constraints (username),
  INDEX (name)
)

statement error duplicate constraint name: "one"
CREATE TABLE test.dupe_named_constraints (
  id        INT CONSTRAINT pk PRIMARY KEY,
  title     VARCHAR CONSTRAINT one CHECK (1>1) CONSTRAINT one CHECK (1<1)
)

statement ok
SET database = test

statement ok
CREATE TABLE alltypes (
  cbigint BIGINT,
  cbigserial BIGSERIAL,
  cblob BLOB,
  cbool BOOL,
  cbit BIT,
  cbit12 BIT(12),
  cvarbit VARBIT,
  cvarbit12 VARBIT(12),
  cbytea BYTEA,
  cbytes BYTES,
  cchar CHAR,
  cchar12 CHAR(12),
  cdate DATE,
  cdec DEC,
  cdec1 DEC(1),
  cdec21 DEC(2,1),
  cdecimal DECIMAL,
  cdecimal1 DECIMAL(1),
  cdecimal21 DECIMAL(2,1),
  cdoubleprecision DOUBLE PRECISION,
  cfloat FLOAT,
  cfloat4 FLOAT4,
  cfloat8 FLOAT8,
  cint INT,
  cint2 INT2,
  cint4 INT4,
  cint64 INT64,
  cint8 INT8,
  cinteger INTEGER,
  cinterval INTERVAL,
  cjson JSONB,
  cnumeric NUMERIC,
  cnumeric1 NUMERIC(1),
  cnumeric21 NUMERIC(2,1),
  cqchar "char",
  creal REAL,
  cserial SERIAL,
  csmallint SMALLINT,
  csmallserial SMALLSERIAL,
  cstring STRING,
  cstring12 STRING(12),
  ctext TEXT,
  ctimestamp TIMESTAMP,
  ctimestampwtz TIMESTAMPTZ,
  cvarchar VARCHAR,
  cvarchar12 VARCHAR(12)
  )

query TTBTTTB colnames
SHOW COLUMNS FROM alltypes
----
column_name       data_type     is_nullable  column_default  generation_expression  indices    is_hidden
cbigint           INT8          true         NULL            ·                      {}         false
cbigserial        INT8          false        unique_rowid()  ·                      {}         false
cblob             BYTES         true         NULL            ·                      {}         false
cbool             BOOL          true         NULL            ·                      {}         false
cbit              BIT           true         NULL            ·                      {}         false
cbit12            BIT(12)       true         NULL            ·                      {}         false
cvarbit           VARBIT        true         NULL            ·                      {}         false
cvarbit12         VARBIT(12)    true         NULL            ·                      {}         false
cbytea            BYTES         true         NULL            ·                      {}         false
cbytes            BYTES         true         NULL            ·                      {}         false
cchar             CHAR          true         NULL            ·                      {}         false
cchar12           CHAR(12)      true         NULL            ·                      {}         false
cdate             DATE          true         NULL            ·                      {}         false
cdec              DECIMAL       true         NULL            ·                      {}         false
cdec1             DECIMAL(1)    true         NULL            ·                      {}         false
cdec21            DECIMAL(2,1)  true         NULL            ·                      {}         false
cdecimal          DECIMAL       true         NULL            ·                      {}         false
cdecimal1         DECIMAL(1)    true         NULL            ·                      {}         false
cdecimal21        DECIMAL(2,1)  true         NULL            ·                      {}         false
cdoubleprecision  FLOAT8        true         NULL            ·                      {}         false
cfloat            FLOAT8        true         NULL            ·                      {}         false
cfloat4           FLOAT4        true         NULL            ·                      {}         false
cfloat8           FLOAT8        true         NULL            ·                      {}         false
cint              INT8          true         NULL            ·                      {}         false
cint2             INT2          true         NULL            ·                      {}         false
cint4             INT4          true         NULL            ·                      {}         false
cint64            INT8          true         NULL            ·                      {}         false
cint8             INT8          true         NULL            ·                      {}         false
cinteger          INT8          true         NULL            ·                      {}         false
cinterval         INTERVAL      true         NULL            ·                      {}         false
cjson             JSONB         true         NULL            ·                      {}         false
cnumeric          DECIMAL       true         NULL            ·                      {}         false
cnumeric1         DECIMAL(1)    true         NULL            ·                      {}         false
cnumeric21        DECIMAL(2,1)  true         NULL            ·                      {}         false
cqchar            "char"        true         NULL            ·                      {}         false
creal             FLOAT4        true         NULL            ·                      {}         false
cserial           INT8          false        unique_rowid()  ·                      {}         false
csmallint         INT2          true         NULL            ·                      {}         false
csmallserial      INT8          false        unique_rowid()  ·                      {}         false
cstring           STRING        true         NULL            ·                      {}         false
cstring12         STRING(12)    true         NULL            ·                      {}         false
ctext             STRING        true         NULL            ·                      {}         false
ctimestamp        TIMESTAMP     true         NULL            ·                      {}         false
ctimestampwtz     TIMESTAMPTZ   true         NULL            ·                      {}         false
cvarchar          VARCHAR       true         NULL            ·                      {}         false
cvarchar12        VARCHAR(12)   true         NULL            ·                      {}         false
rowid             INT8          false        unique_rowid()  ·                      {primary}  true

statement ok
CREATE DATABASE IF NOT EXISTS smtng

statement ok
CREATE TABLE IF NOT EXISTS smtng.something (
ID SERIAL PRIMARY KEY
)

statement ok
ALTER TABLE smtng.something ADD COLUMN IF NOT EXISTS OWNER_ID INT

statement ok
ALTER TABLE smtng.something ADD COLUMN IF NOT EXISTS MODEL_ID INT

statement ok
ALTER TABLE smtng.something ADD COLUMN IF NOT EXISTS NAME STRING

statement ok
CREATE DATABASE IF NOT EXISTS smtng

statement ok
CREATE TABLE IF NOT EXISTS smtng.something (
ID SERIAL PRIMARY KEY
)

statement ok
ALTER TABLE smtng.something ADD COLUMN IF NOT EXISTS OWNER_ID INT

statement ok
ALTER TABLE smtng.something ADD COLUMN IF NOT EXISTS MODEL_ID INT

statement ok
ALTER TABLE smtng.something ADD COLUMN IF NOT EXISTS NAME STRING

# Regression test for database-issues#3922
statement ok
CREATE TABLE test.empty ()

statement ok
SELECT * FROM test.empty

# Issue materialize#14308: support tables with DEFAULT NULL columns.
statement ok
CREATE TABLE test.null_default (
  ts timestamp NULL DEFAULT NULL
)

query TT
SHOW CREATE TABLE test.null_default
----
test.public.null_default  CREATE TABLE null_default (
                          ts TIMESTAMP NULL DEFAULT NULL,
                          FAMILY "primary" (ts, rowid)
)

# Issue materialize#13873: don't permit invalid default columns
statement error could not parse "blah" as type decimal
CREATE TABLE test.t1 (a DECIMAL DEFAULT (DECIMAL 'blah'));

statement error could not parse "blah" as type decimal
create table test.t1 (c decimal default if(false, 1, 'blah'::decimal));

statement ok
CREATE DATABASE a; CREATE TABLE a.c(d INT); INSERT INTO a.public.c(d) VALUES (1)

query I
SELECT a.public.c.d FROM a.public.c
----
1