Materialize/test/sqllogictest/cockroach/create_as.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/create_as
#
# 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 count 3
CREATE TABLE stock (item, quantity) AS VALUES ('cups', 10), ('plates', 15), ('forks', 30)

statement count 1
CREATE TABLE runningOut AS SELECT * FROM stock WHERE quantity < 12

query TI
SELECT * FROM runningOut
----
cups 10

statement count 3
CREATE TABLE itemColors (color) AS VALUES ('blue'), ('red'), ('green')

statement count 9
CREATE TABLE itemTypes AS (SELECT item, color FROM stock, itemColors)

query TT rowsort
SELECT * FROM itemTypes
----
cups blue
cups red
cups green
plates blue
plates red
plates green
forks blue
forks red
forks green

statement error pq: AS OF SYSTEM TIME must be provided on a top-level statement
CREATE TABLE t AS SELECT * FROM stock AS OF SYSTEM TIME '2016-01-01'

statement error pgcode 42601 CREATE TABLE specifies 3 column names, but data source has 2 columns
CREATE TABLE t2 (col1, col2, col3) AS SELECT * FROM stock

statement error pgcode 42601 CREATE TABLE specifies 1 column name, but data source has 2 columns
CREATE TABLE t2 (col1) AS SELECT * FROM stock

statement count 5
CREATE TABLE unionstock AS SELECT * FROM stock UNION VALUES ('spoons', 25), ('knives', 50)

query TI
SELECT * FROM unionstock ORDER BY quantity
----
cups 10
plates 15
spoons 25
forks 30
knives 50

statement count 0
CREATE TABLE IF NOT EXISTS unionstock AS VALUES ('foo', 'bar')

query TI
SELECT * FROM unionstock ORDER BY quantity LIMIT 1
----
cups 10

statement ok
CREATE DATABASE smtng

statement count 3
CREATE TABLE smtng.something AS SELECT * FROM stock

statement count 0
CREATE TABLE IF NOT EXISTS smtng.something AS SELECT * FROM stock

query TI
SELECT * FROM smtng.something ORDER BY 1 LIMIT 1
----
cups 10

statement error pgcode 42P01 relation "something" does not exist
SELECT * FROM something LIMIT 1

# Check for memory leak (materialize#10466)
statement ok
CREATE TABLE foo (x, y, z) AS SELECT catalog_name, schema_name, sql_path FROM information_schema.schemata

statement error pq: relation "foo" already exists
CREATE TABLE foo (x, y, z) AS SELECT catalog_name, schema_name, sql_path FROM information_schema.schemata

statement error pq: value type tuple cannot be used for table columns
CREATE TABLE foo2 (x) AS (VALUES(ROW()))

statement error pq: nested array unsupported as column type: int\[\]\[\]
CREATE TABLE foo2 (x) AS (VALUES(ARRAY[ARRAY[1]]))

statement error generator functions are not allowed in VALUES
CREATE TABLE foo2 (x) AS (VALUES(generate_series(1,3)))

statement error pq: value type unknown cannot be used for table columns
CREATE TABLE foo2 (x) AS (VALUES(NULL))

# Check nulls are handled properly (database-issues#3988)
query I
CREATE TABLE foo3 (x) AS VALUES (1), (NULL); SELECT * FROM foo3 ORDER BY x
----
NULL
1

# Check that CREATE TABLE AS can use subqueries (materialize#23002)
query B
CREATE TABLE foo4 (x) AS SELECT EXISTS(SELECT * FROM foo3 WHERE x IS NULL); SELECT * FROM foo4
----
true

# Regression test for cockroach#36930.
statement ok
CREATE TABLE bar AS SELECT 1 AS a, 2 AS b, count(*) AS c FROM foo

query III colnames
SELECT * FROM bar
----
a  b  c
1  2  4

statement ok
CREATE TABLE baz (a, b, c) AS SELECT 1, 2, count(*) FROM foo

query III colnames
SELECT * FROM baz
----
a  b  c
1  2  4