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: add UDFs to sqlsmith #90782

Closed
mgartner opened this issue Oct 27, 2022 · 1 comment · Fixed by #101510
Closed

sql: add UDFs to sqlsmith #90782

mgartner opened this issue Oct 27, 2022 · 1 comment · Fixed by #101510
Assignees
@rharding6373
Labels
A-sql-routine UDFs and Stored Procedures branch-release-23.1 Used to mark GA and release blockers, technical advisories, and bugs for 23.1 C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) GA-blocker T-sql-queries SQL Queries Team

Comments

@mgartner
Copy link
Collaborator

mgartner commented Oct 27, 2022 •
edited
Loading

We should create and execute UDFs in randomized tests like sqlsmith.

Epic: CRDB-20370

Jira issue: CRDB-20941
@mgartner mgartner added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Oct 27, 2022
@blathers-crl blathers-crl bot added the T-sql-queries SQL Queries Team label Oct 27, 2022
@exalate-issue-sync exalate-issue-sync bot added T-sql-queries SQL Queries Team and removed T-sql-queries SQL Queries Team labels Oct 27, 2022
@rharding6373 rharding6373 self-assigned this Mar 7, 2023
@blathers-crl
Copy link

blathers-crl bot commented Mar 16, 2023

Hi @mgartner, please add branch-* labels to identify which branch(es) this release-blocker affects.

🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.

@mgartner mgartner added the branch-release-23.1 Used to mark GA and release blockers, technical advisories, and bugs for 23.1 label Mar 16, 2023
@rharding6373 rharding6373 mentioned this issue Apr 11, 2023
Merged
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Apr 11, 2023
@rharding6373
sqlsmith: add support for creating udfs
b9daecf 
This PR gives sqlsmith the capability to issue `CREATE FUNCTION`
commands. It randomly chooses from available and valid function options.
It also contains up to 10 `SELECT` statements in the UDF body.

This PR also leaves several TODOs, including support for params, return
types other than records, and calling UDFs in queries after they are
created.

Epic: CRDB-20370
Informs: cockroachdb#90782

Release note: None
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Apr 11, 2023
@rharding6373
sqlsmith: add support for creating udfs
6c13a01 
This PR gives sqlsmith the capability to issue `CREATE FUNCTION`
commands. It randomly chooses from available and valid function options.
It also contains up to 10 `SELECT` statements in the UDF body.

This PR also leaves several TODOs, including support for params, return
types other than records, and calling UDFs in queries after they are
created.

Epic: CRDB-20370
Informs: cockroachdb#90782

Release note: None
craig bot pushed a commit that referenced this issue Apr 11, 2023
@dt @cucaroach
@rharding6373 @nvanbenschoten
Merge #100868 #101159 #101205 #101251
247d5b7 
100868: jobs: wrap payload vs progress err differently r=adityamaru a=dt

Release note: none.
Epic: none

101159: copy: fix copyfrom roachtest session var settings r=cucaroach a=cucaroach

Need to use a separate user as root user cannot have session vars set
via ALTER ROLE.

Epic: None
Fixes: #101158
Release note: None


101205: sqlsmith: add support for creating udfs r=rharding6373 a=rharding6373

This PR gives sqlsmith the capability to issue `CREATE FUNCTION` commands. It randomly chooses from available and valid function options. It also contains up to 10 `SELECT` statements in the UDF body.

This PR also leaves several TODOs, including support for params, return types other than records, and calling UDFs in queries after they are created.

Epic: CRDB-20370
Informs: #90782

Release note: None

101251: kv: mark admission epoch lifo verb as safe string r=irfansharif a=nvanbenschoten

Avoids log garbage like this:
```
I230411 02:07:26.905045 79 util/admission/work_queue.go:489 â<8b>® [n59] 1861224 â<80>¹sql-kv-responseâ<80>º: FIFO threshold for tenant 1 â<80>¹isâ<80>º -128
```

Epic: None
Release note: None

Co-authored-by: David Taylor <[email protected]>
Co-authored-by: Tommy Reilly <[email protected]>
Co-authored-by: rharding6373 <[email protected]>
Co-authored-by: Nathan VanBenschoten <[email protected]>
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Apr 11, 2023
@rharding6373
sqlsmith: add params and return types to udfs
bd9a486 
This PR adds up to 9 parameters of any type. It also adds return types
besides records and ensures that the last statement in the UDF body
returns that type.

It also disables CTEs while generating the statements for the UDF body,
since CTEs are not currently supported.

Epic: CRDB-20370
Informs: cockroachdb#90782

Release note: None
@rharding6373 rharding6373 mentioned this issue Apr 11, 2023
Merged
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Apr 12, 2023
@rharding6373
sqlsmith: add params and return types to udfs
07c2164 
This PR adds up to 9 parameters of any type. It also adds return types
besides records and ensures that the last statement in the UDF body
returns that type.

It also disables CTEs while generating the statements for the UDF body,
since CTEs are not currently supported.

Epic: CRDB-20370
Informs: cockroachdb#90782

Release note: None
@rharding6373 rharding6373 added the A-sql-routine UDFs and Stored Procedures label Apr 12, 2023
craig bot pushed a commit that referenced this issue Apr 12, 2023
@rharding6373 @knz
Merge #101266 #101303
e11cba5 
101266: sqlsmith: add params and return types to udfs r=rharding6373 a=rharding6373

This PR adds up to 9 parameters of any type. It also adds return types besides records and ensures that the last statement in the UDF body returns that type.

It also disables CTEs while generating the statements for the UDF body, since CTEs are not currently supported.

Epic: CRDB-20370
Informs: #90782

Release note: None

101303: multitenant: display the dropping state as "dropping" not "drop" r=ecwall a=knz

Requested by `@ecwall` [here](#100613 (review)).
Needed for #100613.

This specific data state for secondary tenants indicate that the tenant keyspace is queued for asynchronous deletion by a GC jobs; however, it may not have been deleted yet.

Therefore, the visual representation of that state is best named "dropping" instead of "drop".

Release note: None
Epic: CRDB-23559

Co-authored-by: rharding6373 <[email protected]>
Co-authored-by: Raphael 'kena' Poss <[email protected]>
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Apr 13, 2023
@rharding6373
sqlsmith: call udfs in sqlsmith queries
cac2b72 
This PR adds UDFs created by sqlsmith to the list of available functions
that can then be called in other queries generated by sqlsmith.

Epic: CRDB-20370
Informs: cockroachdb#90782

Release note: None
@rharding6373 rharding6373 mentioned this issue Apr 13, 2023
Merged
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Apr 13, 2023
@rharding6373
sqlsmith: allow udf params to be referenced inside udfs
b632c26 
Randomly generated UDFs may now reference their parameters inside the
UDF body statements.

Epic: CRDB-20370
Fixes: cockroachdb#90782

Release note: None
@rharding6373 rharding6373 mentioned this issue Apr 13, 2023
Merged
craig bot pushed a commit that referenced this issue Apr 14, 2023
@rharding6373
Merge #101402
ee9831d 
101402: sqlsmith: call udfs in sqlsmith queries r=rharding6373 a=rharding6373

This PR adds UDFs created by sqlsmith to the list of available functions that can then be called in other queries generated by sqlsmith.

Epic: CRDB-20370
Informs: #90782

Release note: None

Co-authored-by: rharding6373 <[email protected]>
craig bot pushed a commit that referenced this issue Apr 14, 2023
@rharding6373
Merge #101510
8e6cdbd 
101510: sqlsmith: allow udf params to be referenced inside udfs r=rharding6373 a=rharding6373

Randomly generated UDFs may now reference their parameters inside the
UDF body statements.
    
Epic: CRDB-20370
Fixes: #90782
    
Release note: None

Co-authored-by: rharding6373 <[email protected]>
@craig craig bot closed this as completed in 490defa Apr 14, 2023
@mgartner mgartner moved this to Done in SQL Queries Jul 24, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@rharding6373 rharding6373

Labels
A-sql-routine UDFs and Stored Procedures branch-release-23.1 Used to mark GA and release blockers, technical advisories, and bugs for 23.1 C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) GA-blocker T-sql-queries SQL Queries Team
Projects
SQL Queries
Archived in project
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sqlsmith: allow udf params to be referenced inside udfs rharding6373/cockroach
2 participants
@mgartner @rharding6373