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

mode cockroach

# NOTE: Keep this table at the beginning of the file to ensure that its numeric
#       reference is 53 (the numeric reference of the first table). If the
#       numbering scheme in cockroach changes, this test will break.
statement ok
CREATE TABLE t (a INT PRIMARY KEY, b INT)

statement ok
INSERT INTO t VALUES (1, 99), (2, 98), (3, 97)

statement ok
CREATE VIEW v1 AS SELECT a, b FROM t

statement error pgcode 42P07 relation "v1" already exists
CREATE VIEW v1 AS SELECT a, b FROM t

statement error pgcode 42P07 relation "t" already exists
CREATE VIEW t AS SELECT a, b FROM t

statement ok
CREATE VIEW v2 (x, y) AS SELECT a, b FROM t

statement error pgcode 42601 CREATE VIEW specifies 1 column name, but data source has 2 columns
CREATE VIEW v3 (x) AS SELECT a, b FROM t

statement error pgcode 42601 CREATE VIEW specifies 3 column names, but data source has 2 columns
CREATE VIEW v4 (x, y, z) AS SELECT a, b FROM t

statement error pgcode 42P01 relation "dne" does not exist
CREATE VIEW v5 AS SELECT a, b FROM dne

statement ok
CREATE VIEW v6 (x, y) AS SELECT a, b FROM v1

statement ok
CREATE VIEW v7 (x, y) AS SELECT a, b FROM v1 ORDER BY a DESC LIMIT 2

query II colnames,rowsort
SELECT * FROM v1
----
a b
1 99
2 98
3 97

query II colnames,rowsort
SELECT * FROM v2
----
x y
1 99
2 98
3 97

query II colnames,rowsort
SELECT * FROM v6
----
x y
1 99
2 98
3 97

query II colnames
SELECT * FROM v7
----
x y
3 97
2 98

query II colnames
SELECT * FROM v7 ORDER BY x LIMIT 1
----
x y
2 98

query II
SELECT * FROM v2 ORDER BY x DESC LIMIT 1
----
3 97

query I rowsort
SELECT x FROM v2
----
1
2
3

query I rowsort
SELECT y FROM v2
----
99
98
97

query I
SELECT x FROM v7
----
3
2

query I
SELECT x FROM v7 ORDER BY x LIMIT 1
----
2

query I
SELECT y FROM v7
----
97
98

query I
SELECT y FROM v7 ORDER BY x LIMIT 1
----
98

query IIII rowsort
SELECT * FROM v1 AS v1 INNER JOIN v2 AS v2 ON v1.a = v2.x
----
1 99 1 99
2 98 2 98
3 97 3 97

statement error pgcode 42809 "v1" is not a table
DROP TABLE v1

statement error pgcode 42809 "t" is not a view
DROP VIEW t

# v7 fails
# statement ok
# DROP VIEW v7

statement ok
DROP VIEW v6

statement ok
DROP VIEW v2

statement ok
DROP VIEW v1

statement error pgcode 42P01 relation "v1" does not exist
DROP VIEW v1

statement ok
create view s1 AS SELECT count(*) FROM t

statement ok
create view s2 AS SELECT a FROM t WHERE a IN (SELECT count(*) FROM t)

statement ok
create view s3 AS SELECT a, count(*) FROM t GROUP BY a

statement ok
create view s4 AS SELECT a, count(*) FROM t GROUP BY a HAVING a > (SELECT count(*) FROM t)

# s4 fails
# statement ok
# DROP VIEW s4

statement ok
DROP VIEW s3

# s2 fails
# statement ok
# DROP VIEW s2

statement ok
DROP VIEW s1

statement ok
DROP TABLE t

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

statement error pq: relation "foo" already exists
CREATE VIEW foo AS SELECT catalog_name, schema_name, sql_path FROM information_schema.schemata

# Ensure views work with dates/timestamps (materialize#12420)
statement ok
CREATE TABLE t (d DATE, t TIMESTAMP)

statement ok
CREATE VIEW dt AS SELECT d, t FROM t WHERE d > DATE '1988-11-12' AND t < TIMESTAMP '2017-01-01'

statement ok
SELECT * FROM dt

statement ok
CREATE VIEW dt2 AS SELECT d, t FROM t WHERE d > d + INTERVAL '10h'

statement ok
SELECT * FROM dt2

# Ensure that creating a view doesn't leak any session-level settings that
# could affect subsequent AS OF SYSTEM TIME queries (materialize#13547).
statement ok
CREATE VIEW v AS SELECT d, t FROM t

statement error pq: AS OF SYSTEM TIME must be provided on a top-level statement
CREATE TABLE t2 AS SELECT d, t FROM t AS OF SYSTEM TIME '2017-02-13 21:30:00'

statement ok
DROP TABLE t CASCADE

# not supported yet
# statement ok
# CREATE TABLE t (a INT[])
#
# statement ok
# INSERT INTO t VALUES (array[1,2,3])
#
# statement ok
# CREATE VIEW b AS SELECT a[1] FROM t
#
# query I
# SELECT * FROM b
# ----
# 1
#
# statement ok
# DROP TABLE t CASCADE

statement ok
CREATE VIEW arr(a) AS SELECT ARRAY[3]

query TI
SELECT *, a[1] FROM arr
----
{3}  3

# Regression for materialize#15951

statement ok
CREATE TABLE t15951 (a int, b int)

statement ok
CREATE VIEW Caps15951 AS SELECT a, b FROM t15951

statement ok
INSERT INTO t15951 VALUES (1, 1), (1, 2), (1, 3), (2, 2), (2, 3), (3, 3)

query R
SELECT sum (Caps15951. a) FROM Caps15951 GROUP BY b ORDER BY b
----
1
3
6

query R
SELECT sum ("caps15951". a) FROM "caps15951" GROUP BY b ORDER BY b
----
1
3
6

statement ok
CREATE VIEW "QuotedCaps15951" AS SELECT a, b FROM t15951

query R
SELECT sum ("QuotedCaps15951". a) FROM "QuotedCaps15951" GROUP BY b ORDER BY b
----
1
3
6

# Regression tests for database-issues#7154

statement ok
CREATE VIEW w AS WITH a AS (SELECT 1 AS x) SELECT x FROM a

query T
SELECT create_statement FROM [SHOW CREATE w]
----
CREATE VIEW w (x) AS WITH a AS (SELECT 1 AS x) SELECT x FROM a

statement ok
CREATE VIEW w2 AS WITH t AS (SELECT x FROM w) SELECT x FROM t

query T
SELECT create_statement FROM [SHOW CREATE w2]
----
CREATE VIEW w2 (x) AS WITH t AS (SELECT x FROM test.public.w) SELECT x FROM t

statement ok
CREATE VIEW w3 AS (WITH t AS (SELECT x FROM w) SELECT x FROM t)

query T
SELECT create_statement FROM [SHOW CREATE w3]
----
CREATE VIEW w3 (x) AS (WITH t AS (SELECT x FROM test.public.w) SELECT x FROM t)

# Test CRUD privilege in view.

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

statement ok
CREATE VIEW crud_view AS SELECT a, b FROM [INSERT INTO t (a, b) VALUES (100, 100) RETURNING a, b]

statement ok
GRANT SELECT ON crud_view TO testuser

user testuser

query error user testuser does not have INSERT privilege on relation t
SELECT * FROM crud_view

user root