cockroachdb / cockroachdb/cockroach

sql: UDFs with mutations are not allowed in views

Open
#151,686 0 comments 0 reactions 0 assignees View on GitHub
A-sql-routine C-enhancement docs-known-limitation O-qa T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

A view cannot reference a UDF if the UDF makes has a mutation statement (`INSERT`, `UPDATE`, `UPSERT`, `DELETE`). For example:

```sql
CREATE TABLE t (k INT PRIMARY KEY);
-- CREATE TABLE

CREATE FUNCTION f(i INT) RETURNS VOID LANGUAGE SQL AS $$
INSERT INTO t VALUES (i);
$$;
-- CREATE FUNCTION

CREATE VIEW v AS SELECT f(1);
-- ERROR: INSERT cannot be used inside a view definition
-- SQLSTATE: 42601
```

Jira issue: CRDB-53418

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the SQL example with a mutating UDF referenced by a view, then trace where view definitions reject mutation statements. Add regression coverage for the demonstrated behavior and verify the resulting SQL error or accepted definition matches the intended resolution.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.