PR: cockroachdb/cockroach#97515
Note: Any bugs or findings of interested are documented inline with
NOTE: Delete this section if the feature is not supported by Postgres.
Run the examples from the Postgres Create View Docs on both Postgres and CRDB. Ensure the results match. This serves as a rough check for compatibility with Postgres in basic cases.
CREATE TABLE films (id int PRIMARY KEY, title text, kind text, classification CHAR(1), year int, language text, studio text, runtime float, country_code CHAR(2));
CREATE TABLE user_ratings (film_id int, classification text, country_code CHAR(2), rating float);
CREATE VIEW comedies AS
SELECT *
FROM films
WHERE kind = 'Comedy';
SHOW CREATE VIEW comedies;
table_name | create_statement
-------------+---------------------------------
comedies | CREATE VIEW public.comedies (
| id,
| title,
| kind,
| classification,
| year,
| language,
| studio,
| runtime,
| country_code
| ) AS SELECT
| films.id,
| films.title,
| films.kind,
| films.classification,
| films.year,
| films.language,
| films.studio,
| films.runtime,
| films.country_code
| FROM
| defaultdb.public.films
| WHERE
| kind = 'Comedy'
(1 row)
CREATE VIEW pg_comedies AS
SELECT *
FROM comedies
WHERE classification = 'PG';
SHOW CREATE VIEW pg_comedies;
table_name | create_statement
--------------+------------------------------------
pg_comedies | CREATE VIEW public.pg_comedies (
| id,
| title,
| kind,
| classification,
| year,
| language,
| studio,
| runtime,
| country_code
| ) AS SELECT
| comedies.id,
| comedies.title,
| comedies.kind,
| comedies.classification,
| comedies.year,
| comedies.language,
| comedies.studio,
| comedies.runtime,
| comedies.country_code
| FROM
| defaultdb.public.comedies
| WHERE
| classification = 'PG'
(1 row)
CREATE VIEW comedies2 AS
SELECT f.*,
f.country_code AS country,
(SELECT avg(r.rating)
FROM user_ratings r
WHERE r.film_id = f.id) AS avg_rating
FROM films f
WHERE f.kind = 'Comedy';
table_name | create_statement
-------------+------------------------------------------------------------------------------------------------
comedies2 | CREATE VIEW public.comedies2 (
| id,
| title,
| kind,
| classification,
| year,
| language,
| studio,
| runtime,
| country_code,
| country,
| avg_rating
| ) AS SELECT
| f.id,
| f.title,
| f.kind,
| f.classification,
| f.year,
| f.language,
| f.studio,
| f.runtime,
| f.country_code,
| f.country_code AS country,
| (SELECT avg(r.rating) FROM defaultdb.public.user_ratings AS r WHERE r.film_id = f.id)
| AS avg_rating
| FROM
| defaultdb.public.films AS f
| WHERE
| f.kind = 'Comedy'
(1 row)CREATE OR REPLACE VIEW comedies AS
SELECT *
FROM pg_comedies;
EXPLAIN SELECT * FROM pg_comedies;
ERROR: unexpected EOF
warning: error retrieving the transaction status: connection closed unexpectedly: conn closed
warning: connection lost!
opening new connection: all session settings will be lost
warning: error retrieving the database name: failed to connect to `host=localhost user=root database=`: dial error (dial tcp 127.0.0.1:26257: connect: connection refused)
I230319 21:26:41.260793 1 util/log/flags.go:211 [-] 1 stderr capture started
runtime: goroutine stack exceeds 1000000000-byte limit
runtime: sp=0xc02e3ba388 stack=[0xc02e3ba000, 0xc04e3ba000]
fatal error: stack overflow
runtime stack:
runtime.throw({0x59e7120?, 0x9f342a0?})
GOROOT/src/runtime/panic.go:1047 +0x5d fp=0x7f966a94c278 sp=0x7f966a94c248 pc=0x48f7dd
runtime.newstack()
GOROOT/src/runtime/stack.go:1103 +0x5cc fp=0x7f966a94c430 sp=0x7f966a94c278 pc=0x4a9ccc
runtime.morestack()
GOROOT/src/runtime/asm_amd64.s:570 +0x8b fp=0x7f966a94c438 sp=0x7f966a94c430 pc=0x4c204b
Opened cockroachdb/cockroach#98999 for this.
CREATE TABLE films (id int PRIMARY KEY, title text, kind text, classification CHAR(1), year int, language text, studio text, runtime float, country_code CHAR(2));
CREATE OR REPLACE VIEW comedies AS SELECT ARRAY[films.*]::string FROM films;
ERROR: internal error: relation "comedies" (105): column ID 2 found in depended-on-by references, no such column in this relation
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/catalog/tabledesc/validate.go:693: ValidateSelf()
github.com/cockroachdb/cockroach/pkg/sql/catalog/internal/validate/validate.go:80: func1()
github.com/cockroachdb/cockroach/pkg/sql/catalog/internal/validate/validate.go:198: validateDescriptorsAtLevel()
github.com/cockroachdb/cockroach/pkg/sql/catalog/internal/validate/validate.go:76: Validate()
github.com/cockroachdb/cockroach/pkg/sql/catalog/internal/validate/validate.go:41: Self()
github.com/cockroachdb/cockroach/pkg/sql/catalog/tabledesc/structured.go:539: AllocateIDs()
github.com/cockroachdb/cockroach/pkg/sql/create_view.go:706: addResultColumns()
github.com/cockroachdb/cockroach/pkg/sql/create_view.go:602: replaceViewDesc()
github.com/cockroachdb/cockroach/pkg/sql/create_view.go:199: 1()
github.com/cockroachdb/cockroach/pkg/sql/create_view.go:366: func1()
github.com/cockroachdb/cockroach/pkg/sql/schema_resolver.go:414: runWithOptions()
github.com/cockroachdb/cockroach/pkg/sql/create_view.go:193: startExec()
github.com/cockroachdb/cockroach/pkg/sql/plan.go:524: func2()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:112: func1()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:299: visitInternal()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:79: visit()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:43: walkPlan()
github.com/cockroachdb/cockroach/pkg/sql/plan.go:527: startExec()
github.com/cockroachdb/cockroach/pkg/sql/plan_node_to_row_source.go:172: Start()
github.com/cockroachdb/cockroach/pkg/sql/colexec/columnarizer.go:183: Init()
github.com/cockroachdb/cockroach/pkg/sql/colflow/stats.go:94: init()
github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:92: CatchVectorizedRuntimeError()
github.com/cockroachdb/cockroach/pkg/sql/colflow/stats.go:103: Init()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:246: func1()
github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:92: CatchVectorizedRuntimeError()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:245: init()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:285: Run()
github.com/cockroachdb/cockroach/pkg/sql/colflow/vectorized_flow.go:309: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:866: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1843: PlanAndRun()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1579: func2()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1582: PlanAndRunAll()
HINT: You have encountered an unexpected error.Created cockroachdb/cockroach#99000 for this.
Working cases:
CREATE TABLE strings (a text, b text, c text);
CREATE VIEW stars AS
WITH
stars AS (SELECT array_agg(z.*) AS star_cols FROM (SELECT * FROM (SELECT a FROM strings), (SELECT b FROM strings)) AS z)
SELECT unnest(star_cols) FROM stars;
ERROR: cannot use anonymous record type as table column
CREATE OR REPLACE VIEW stars AS
WITH
stars AS (SELECT array_agg(z.*) AS star_cols FROM (SELECT * FROM strings) AS z)
SELECT unnest(star_cols)::text FROM stars;
SELECT * FROM stars;
CREATE OR REPLACE VIEW stars AS
WITH
stars AS (SELECT array_agg(strings.*) AS star_cols FROM strings)
SELECT unnest(star_cols)::text FROM stars;
CREATE OR REPLACE VIEW stars AS
WITH
stars AS (SELECT array_agg(strings.*) AS star_cols FROM strings)
SELECT unnest(star_cols)::text FROM stars;
SHOW CREATE VIEW stars;
table_name | create_statement
-------------+--------------------------------------------------------------------------------------------
stars | CREATE VIEW public.stars (
| unnest
| ) AS WITH
| stars AS (SELECT array_agg(strings.*) AS star_cols FROM defaultdb.public.strings)
| SELECT
| unnest(star_cols)::STRING
| FROM
| stars
(1 row)
ALTER TABLE strings ADD COLUMN d text;
INSERT INTO strings VALUES('a', 'b', 'c', 'd');
SELECT * FROM stars;
unnest
-------------
(a,b,c,d)
(1 row)
CREATE OR REPLACE VIEW stars AS
WITH
stars AS (SELECT array_agg(z.*) AS star_cols FROM (SELECT * FROM (SELECT * FROM strings), (SELECT * FROM strings)) AS z)
SELECT unnest(star_cols)::text FROM stars;
show create table stars;
table_name | create_statement
-------------+---------------------------------------------------------------------------------
stars | CREATE VIEW public.stars (
| unnest
| ) AS WITH
| stars
| AS (
| SELECT
| array_agg(z.*) AS star_cols
| FROM
| (
| SELECT
| "?subquery1?".a,
| "?subquery1?".b,
| "?subquery1?".c,
| "?subquery1?".d,
| "?subquery2?".a,
| "?subquery2?".b,
| "?subquery2?".c,
| "?subquery2?".d
| FROM
| (
| SELECT
| strings.a, strings.b, strings.c, strings.d
| FROM
| defaultdb.public.strings
| )
| AS "?subquery1?",
| (
| SELECT
| strings.a, strings.b, strings.c, strings.d
| FROM
| defaultdb.public.strings
| )
| AS "?subquery2?"
| )
| AS z
| )
| SELECT
| unnest(star_cols)::STRING
| FROM
| stars
(1 row)
CREATE OR REPLACE VIEW comedies AS
SELECT public.films.*
FROM films
WHERE kind = 'Comedy';
CREATE OR REPLACE VIEW comedies AS
SELECT defaultdb.public.films.*
FROM films
WHERE kind = 'Comedy';
CREATE OR REPLACE FUNCTION passthrough() RETURNS RECORD VOLATILE LANGUAGE SQL AS 'SELECT * FROM comedies';
show create function passthrough;
function_name | create_statement
----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
passthrough | CREATE FUNCTION public.passthrough()
| RETURNS RECORD
| VOLATILE
| NOT LEAKPROOF
| CALLED ON NULL INPUT
| LANGUAGE SQL
| AS $$
| SELECT comedies.id, comedies.title, comedies.kind, comedies.classification, comedies.year, comedies.language, comedies.studio, comedies.runtime, comedies.country_code FROM defaultdb.public.comedies;
| $$
(1 row)
CREATE OR REPLACE VIEW stars AS SELECT * FROM strings UNION ALL SELECT * FROM strings;
CREATE VIEW
Time: 118ms total (execution 118ms / network 0ms)
root@localhost:26257/defaultdb> show create view stars;
table_name | create_statement
-------------+------------------------------------------------------------------------------------------------
stars | CREATE VIEW public.stars (
| a,
| b,
| c,
| d
| ) AS SELECT strings.a, strings.b, strings.c, strings.d FROM defaultdb.public.strings
| UNION ALL SELECT strings.a, strings.b, strings.c, strings.d FROM defaultdb.public.strings
(1 row)
CREATE OR REPLACE VIEW stars AS SELECT * FROM strings WHERE (a,b,c,d) NOT IN (SELECT * FROM strings);
CREATE VIEW
Time: 116ms total (execution 116ms / network 0ms)
root@localhost:26257/defaultdb> show create view stars;
table_name | create_statement
-------------+---------------------------------------------------------------------------------------------------
stars | CREATE VIEW public.stars (
| a,
| b,
| c,
| d
| ) AS SELECT
| strings.a, strings.b, strings.c, strings.d
| FROM
| defaultdb.public.strings
| WHERE
| (a, b, c, d)
| NOT IN (SELECT strings.a, strings.b, strings.c, strings.d FROM defaultdb.public.strings)
(1 row)
CREATE OR REPLACE VIEW stars (a,b,c,d,e,f,g,h) AS SELECT * FROM strings, LATERAL (SELECT * FROM strings a WHERE a.a = strings.a );
show create view stars;
table_name | create_statement
-------------+-------------------------------------------------------------------------------------------------------
stars | CREATE VIEW public.stars (
| a,
| b,
| c,
| d,
| e,
| f,
| g,
| h
| ) AS SELECT
| strings.a,
| strings.b,
| strings.c,
| strings.d,
| "?subquery1?".a,
| "?subquery1?".b,
| "?subquery1?".c,
| "?subquery1?".d
| FROM
| defaultdb.public.strings,
| LATERAL (SELECT a.a, a.b, a.c, a.d FROM defaultdb.public.strings AS a WHERE a.a = strings.a)
| AS "?subquery1?"
(1 row)
CREATE OR REPLACE VIEW stars AS SELECT * FROM strings WHERE EXISTS (SELECT * FROM strings a WHERE a.a = strings.a );
CREATE VIEW
Time: 99ms total (execution 99ms / network 0ms)
root@localhost:26257/defaultdb> show create view stars;
table_name | create_statement
-------------+-----------------------------------------------------------------------------------------------------
stars | CREATE VIEW public.stars (
| a,
| b,
| c,
| d
| ) AS SELECT
| strings.a, strings.b, strings.c, strings.d
| FROM
| defaultdb.public.strings
| WHERE
| EXISTS(SELECT a.a, a.b, a.c, a.d FROM defaultdb.public.strings AS a WHERE a.a = strings.a)
(1 row)
CREATE OR REPLACE VIEW stars AS SELECT * FROM strings WHERE EXISTS (SELECT strings.* FROM strings a WHERE a.a = strings.a );
CREATE VIEW
Time: 114ms total (execution 114ms / network 0ms)
root@localhost:26257/defaultdb> show create view stars;
table_name | create_statement
-------------+-------------------------------------------------------------
stars | CREATE VIEW public.stars (
| a,
| b,
| c,
| d
| ) AS SELECT
| strings.a, strings.b, strings.c, strings.d
| FROM
| defaultdb.public.strings
| WHERE
| EXISTS(
| SELECT
| strings.a, strings.b, strings.c, strings.d
| FROM
| defaultdb.public.strings AS a
| WHERE
| a.a = strings.a
| )
(1 row)
A UDF selecting from a view works though:
CREATE TABLE user_ratings (film_id int, classification text, country_code CHAR(2), rating float);
CREATE VIEW comedies AS
SELECT *
FROM films
WHERE kind = 'Comedy';
CREATE OR REPLACE FUNCTION select_from_udf() RETURNS RECORD VOLATILE LANGUAGE SQL AS 'SELECT * FROM comedies';
SELECT * FROM select_from_udf();A UDF with parameters can select from a view.
CREATE TABLE strings (a text, b text, c text, d text);
INSERT INTO strings VALUES('a', 'b', 'c', 'd');
CREATE OR REPLACE FUNCTION passthrough(e text, f text, g text, h text) RETURNS TEXT[] LANGUAGE SQL AS 'SELECT ARRAY[strings.*,row(e,f,g,h)] FROM strings';
select passthrough('e', 'f', 'g', 'h');
passthrough
-----------------------------
{"(a,b,c,d)","(e,f,g,h)"}
(1 row)CREATE TABLE user_ratings (film_id int, classification text, country_code CHAR(2), rating float);
CREATE VIEW comedies AS
SELECT *
FROM films
WHERE kind = 'Comedy';
CREATE OR REPLACE VIEW stars AS SELECT (SELECT a FROM (SELECT * FROM strings LIMIT 1));
how create view stars
-> ;
table_name | create_statement
-------------+---------------------------------------------------------------------
stars | CREATE VIEW public.stars (
| a
| ) AS SELECT
| (
| SELECT
| a
| FROM
| (
| SELECT
| strings.a, strings.b, strings.c, strings.d
| FROM
| defaultdb.public.strings
| LIMIT
| 1
| )
| AS "?subquery1?"
| )
(1 row)
SELECT * FROM stars;
CREATE OR REPLACE VIEW stars AS SELECT (SELECT a FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM strings LIMIT 1))));
show create view stars
-> ;
table_name | create_statement
-------------+-----------------------------------------------------------------------------------------------------
stars | CREATE VIEW public.stars (
| a
| ) AS SELECT
| (
| SELECT
| a
| FROM
| (
| SELECT
| "?subquery2?".a, "?subquery2?".b, "?subquery2?".c, "?subquery2?".d
| FROM
| (
| SELECT
| "?subquery3?".a, "?subquery3?".b, "?subquery3?".c, "?subquery3?".d
| FROM
| (
| SELECT
| strings.a, strings.b, strings.c, strings.d
| FROM
| defaultdb.public.strings
| LIMIT
| 1
| )
| AS "?subquery3?"
| )
| AS "?subquery2?"
| )
| AS "?subquery1?"
| )
(1 row)
### Coalesce
‼️ The following works on Postgres.
```sql
CREATE TABLE strings (a text, b text, c text, d text);
CREATE OR REPLACE VIEW stars (star_cols) AS SELECT COALESCE(strings.*) AS star_cols FROM strings;
ERROR: cannot use anonymous record type as table column
SQLSTATE: 42P16Created cockroachdb/cockroach#99006 for this.
CREATE OR REPLACE VIEW stars (star_cols) AS SELECT row_to_json(strings.*) AS star_cols FROM strings;
select * from stars;
star_cols
--------------------------------------------
{"a": "a", "b": "b", "c": "c", "d": "d"}