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.

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

sql: adding a computed column doesn't validate against existing values #88234

Closed
fqazi opened this issue Sep 20, 2022 · 3 comments
Closed

sql: adding a computed column doesn't validate against existing values #88234

fqazi opened this issue Sep 20, 2022 · 3 comments
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)

Comments

@fqazi
Copy link
Collaborator

fqazi commented Sep 20, 2022

Describe the problem

Right now it's possible to add computed columns where the generated values for a subset of columns
can never be properly computed. For example a computed column could be the division between two
columns, in a given table: ALTER TABLE ADD COLUMN c INT AS c1/c2 VIRTUAL. If there is any c2 that is
zero then this computed column would have a division by zero. When the computed column is STORED this
statement will rollback. But, when its virtual this column will be added and any SELECT involving the problematic
column will fail.

The ideal solution here is to have validation for VIRTUAL computed columns, where when a column is
added the existing values in the table are validated for the generated expression. If any of the expressions
can't be generated then the error is surfaced similar to a STORED computed column.

Jira issue: CRDB-19740

@fqazi fqazi added C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-schema-deprecated Use T-sql-foundations instead labels Sep 20, 2022
@blathers-crl blathers-crl bot added this to Triage in SQL Foundations Sep 20, 2022
@ajwerner
Copy link
Contributor

@mgartner I think you'll find this one to be interesting. I think we should just add a validation step.

@ajwerner
Copy link
Contributor

Is this a duplicate of #81698?

@postamar
Copy link
Contributor

It seems so. Let's do this only in the declarative schema changer.

SQL Foundations automation moved this from Triage to Closed Sep 20, 2022
@exalate-issue-sync exalate-issue-sync bot added T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) and removed T-sql-schema-deprecated Use T-sql-foundations instead labels May 10, 2023
@blathers-crl blathers-crl bot added this to Triage in SQL Foundations May 10, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)
Projects
SQL Foundations
  
Done [after migration]
Development

No branches or pull requests

3 participants