# 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