Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: unnamed INOUT parameters are not supported #121251

Open
yuzefovich opened this issue Mar 28, 2024 · 1 comment
Open

sql: unnamed INOUT parameters are not supported #121251

yuzefovich opened this issue Mar 28, 2024 · 1 comment
Labels
A-sql-routine UDFs and Stored Procedures C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) docs-known-limitation docs-todo T-sql-queries SQL Queries Team

Comments

@yuzefovich
Copy link
Member

yuzefovich commented Mar 28, 2024 •
edited

We currently prohibit creation of routines with unnamed INOUT parameters due to difficulties with producing the correct output.

Consider the following logic test:

statement ok
CREATE PROCEDURE p(INOUT INT) AS $$ BEGIN NULL; END; $$ LANGUAGE PLpgSQL;

query I colnames
CALL p(1);
----
column1
1

We currently handle it incorrectly since we have an unnamed INOUT parameter - we give it NULL value. A similar problem arises for UDFs (and for procedures too) when the INOUT parameter has DEFAULT expression.

Jira issue: CRDB-37156
@yuzefovich yuzefovich added C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-queries SQL Queries Team A-sql-routine UDFs and Stored Procedures labels Mar 28, 2024
@yuzefovich
Copy link
Member Author

[triage] for 24.1 we should consider disallowing unnamed INOUT parameters (since IN parameters don't have this problem).

@yuzefovich yuzefovich self-assigned this Apr 2, 2024
@yuzefovich yuzefovich added GA-blocker branch-release-24.1 Used to mark GA and release blockers and technical advisories for 24.1 labels Apr 2, 2024
@yuzefovich yuzefovich mentioned this issue Apr 5, 2024
Merged
craig bot pushed a commit that referenced this issue Apr 6, 2024
@yuzefovich
Merge #121871 #121872
ff52bbf 
121871: roachtest: ignore injected error in sqlsmith in some cases r=yuzefovich a=yuzefovich

We enable vectorized panic injection in `sqlsmith` roachtest, and we generally don't expect it to be propagated all the way as "internal error". However, sqlsmith itself issues some queries (like fetching all DB regions) which might hit this injected error which will result in a panic during `smither.Generate` call - we want to ignore such cases.

Fixes: #121467.

Release note: None

121872: sql: disallow unnamed INOUT parameters r=yuzefovich a=yuzefovich

We currently don't have an easy way to access the input / DEFAULT expression for the unnamed parameter, so we cannot populate the output correctly. For the time being, we'll disable INOUT unnamed parameters. Other parameter classes don't have this problem (unnamed IN parameters can only be referenced in the body via the $i notation, which we don't yet support; unnamed OUT parameters can also change their initial NULL value only via the $i notation).

Informs: #121251.

Epic: None

Release note: None

Co-authored-by: Yahor Yuzefovich <yahor@cockroachlabs.com>
@blathers-crl blathers-crl bot mentioned this issue Apr 6, 2024
Merged
@yuzefovich yuzefovich changed the title sql: incorrect handling of unnamed INOUT parameters in PLpgSQL routines sql: unnamed INOUT parameters are not supported Apr 7, 2024
@yuzefovich yuzefovich added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) docs-todo docs-known-limitation and removed C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. GA-blocker branch-release-24.1 Used to mark GA and release blockers and technical advisories for 24.1 labels Apr 7, 2024
@yuzefovich yuzefovich removed their assignment Apr 7, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
A-sql-routine UDFs and Stored Procedures C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) docs-known-limitation docs-todo T-sql-queries SQL Queries Team
Projects
SQL Queries
Status: Backlog
Milestone
No milestone
Development

No branches or pull requests
1 participant
@yuzefovich