cockroachdb / cockroachdb/cockroach

plpgsql: stored procedures should disallow RETURN with parameter

Open
#114,693 0 comments 0 reactions 0 assignees View on GitHub
A-sql-plpgsql C-bug O-qa P-3 T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

When a PLpgSQL procedure contains a return statement with a non-null value, calling the procedure results in an internal error due to unexpected data type:
```
root@localhost:26257/defaultdb> create procedure foo() language plpgsql as $$ begin return 0; end $$;
CREATE PROCEDURE

Time: 52ms total (execution 28ms / network 24ms)

root@localhost:26257/defaultdb> call foo();
ERROR: internal error: invalid datum type given: INT8, expected VOID
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/rowenc/encoded_datum.go:195: DatumToEncDatum()
github.com/cockroachdb/cockroach/pkg/sql/plan_node_to_row_source.go:228: Next()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:128: next()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:145: nextAdapter()
github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:92: CatchVectorizedRuntimeError()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:150: Next()
github.com/cockroachdb/cockroach/pkg/sql/execinfra/base.go:198: Run()
github.com/cockroachdb/cockroach/pkg/sql/execinfra/processorsbase.go:726: Run()
github.com/cockroachdb/cockroach/pkg/sql/flowinfra/flow.go:579: Run()
github.com/cockroachdb/cockroach/pkg/sql/colflow/vectorized_flow.go:305: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:910: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1957: PlanAndRun()
github.com/cockroachdb/cockroach/pkg/sql/apply_join.go:361: func3()
github.com/cockroachdb/cockroach/pkg/sql/apply_join.go:364: runPlanInsidePlan()
github.com/cockroachdb/cockroach/pkg/sql/routine.go:276: func2()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/scalar.go:1219: func2()
github.com/cockroachdb/cockroach/pkg/sql/routine.go:239: startInternal()
github.com/cockroachdb/cockroach/pkg/sql/routine.go:180: Start()
github.com/cockroachdb/cockroach/pkg/sql/routine.go:92: EvalRoutineExpr()
github.com/cockroachdb/cockroach/pkg/sql/sem/eval/expr.go:629: EvalRoutineExpr()
github.com/cockroachdb/cockroach/bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/sem/tree/eval_expr_generated.go:351: Eval()
github.com/cockroachdb/cockroach/pkg/sql/sem/eval/expr.go:26: Expr()
github.com/cockroachdb/cockroach/pkg/sql/routine.go:42: startExec()
github.com/cockroachdb/cockroach/pkg/sql/plan.go:520: 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:523: startExec()
github.com/cockroachdb/cockroach/pkg/sql/plan_node_to_row_source.go:175: Start()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:120: func1()
github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:92: CatchVectorizedRuntimeError()
```
Postgres doesn't allow a RETURN statement with a parameter at all:
```
postgres=# create procedure foo() language plpgsql as $$ begin return 0; end $$;
ERROR: RETURN cannot have a parameter in a procedure
LINE 1: ...ocedure foo() language plpgsql as $$ begin return 0; end $$;
^
postgres=# create procedure foo() language plpgsql as $$ begin return null; end $$;
ERROR: RETURN cannot have a parameter in a procedure
LINE 1: ...ocedure foo() language plpgsql as $$ begin return null; end ...
^
```

Jira issue: CRDB-33632

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.