# 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