cockroachdb / cockroachdb/cockroach
sql: UDFs with mutations are not allowed in views
- 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
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