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: Incorrect results when deleting rows from table with index column being dropped #33361

Closed
andy-kimball opened this issue Dec 26, 2018 · 1 comment · Fixed by #33438
Closed

sql: Incorrect results when deleting rows from table with index column being dropped #33361

andy-kimball opened this issue Dec 26, 2018 · 1 comment · Fixed by #33438
Assignees
@vivekmenezes
Labels
A-sql-mutations Mutation statements: UPDATE/INSERT/UPSERT/DELETE. C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. S-3-erroneous-edge-case Database produces or stores erroneous data without visible error/warning, in rare edge cases.

Comments

@andy-kimball
Copy link
Contributor

The bug occurs when a column being dropped is part of an index. In that case, it is incorrectly projected by the RETURNING clause.

root@:26257/defaultdb> DROP TABLE t29494; CREATE TABLE t29494(x INT PRIMARY KEY, y INT UNIQUE); INSERT INTO t29494 VALUES (1, 2);
INSERT 1

root@:26257/defaultdb> BEGIN; ALTER TABLE t29494 DROP COLUMN y;
ALTER TABLE

root@:26257/defaultdb  OPEN> DELETE FROM t29494 RETURNING *;
  x | y
+---+---+
  1 | 2

EXPECTED: Since column y was dropped, it should not be part of the results.

ACTUAL: Column y is projected in the result because it is part of an index. If there is no index, then the correct results are returned.
@andy-kimball andy-kimball added the C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. label Dec 26, 2018
@andy-kimball andy-kimball self-assigned this Dec 26, 2018
@andy-kimball
Copy link
Contributor Author

CC: @vivekmenezes, since this is a schema change related issue.

@knz knz added A-sql-mutations Mutation statements: UPDATE/INSERT/UPSERT/DELETE. S-3-erroneous-edge-case Database produces or stores erroneous data without visible error/warning, in rare edge cases. labels Dec 27, 2018
@knz knz unassigned knz and andy-kimball Jan 2, 2019
@vivekmenezes vivekmenezes self-assigned this Jan 2, 2019
vivekmenezes added a commit to vivekmenezes/cockroach that referenced this issue Jan 2, 2019
@vivekmenezes
sql: stop returning dropped unique columns in DELETE RETURNING
0023c78 
this change is a followup to fix cockroachdb#32188

unique columns have an index on them. When such a column is dropped,
a DELETE statement will read a dropped column to correctly drop
the index on it, but this dropped column is exposed through the
RETURNING clause. This change stops exposing the column via RETURNING.

fixes cockroachdb#33361

Release note (sql change): fixed problem with returning dropped
unique columns in DELETE RETURNING statement.
@vivekmenezes vivekmenezes mentioned this issue Jan 2, 2019
Merged
craig bot pushed a commit that referenced this issue Jan 3, 2019
@vivekmenezes
Merge #33438
edda421 
33438: sql: stop returning dropped unique columns in DELETE RETURNING r=vivekmenezes a=vivekmenezes

this change is a followup to fix #32188

unique columns have an index on them. When such a column is dropped,
a DELETE statement will read a dropped column to correctly drop
the index on it, but this dropped column is exposed through the
RETURNING clause. This change stops exposing the column via RETURNING.

fixes #33361

Release note (sql change): fixed problem with returning dropped
unique columns in DELETE RETURNING statement.

Co-authored-by: Vivek Menezes <vivek@cockroachlabs.com>
@craig craig bot closed this as completed in #33438 Jan 3, 2019
vivekmenezes added a commit to vivekmenezes/cockroach that referenced this issue Jan 7, 2019
@vivekmenezes
sql: stop returning dropped unique columns in DELETE RETURNING
a943ec2 
this change is a followup to fix cockroachdb#32188

unique columns have an index on them. When such a column is dropped,
a DELETE statement will read a dropped column to correctly drop
the index on it, but this dropped column is exposed through the
RETURNING clause. This change stops exposing the column via RETURNING.

fixes cockroachdb#33361

Release note (sql change): fixed problem with returning dropped
unique columns in DELETE RETURNING statement.
@vivekmenezes vivekmenezes mentioned this issue Jan 7, 2019
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@vivekmenezes vivekmenezes

Labels
A-sql-mutations Mutation statements: UPDATE/INSERT/UPSERT/DELETE. C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. S-3-erroneous-edge-case Database produces or stores erroneous data without visible error/warning, in rare edge cases.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: stop returning dropped unique columns in DELETE RETURNING vivekmenezes/cockroach
3 participants
@knz @vivekmenezes @andy-kimball