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: Inconsistent UPSERT behavior when DEFAULT columns are present #33327

Closed
andy-kimball opened this issue Dec 21, 2018 · 4 comments
Closed

sql: Inconsistent UPSERT behavior when DEFAULT columns are present #33327

andy-kimball opened this issue Dec 21, 2018 · 4 comments
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

Example #1: Column 'b' has no default value

root@:26257/defaultdb> create table abc (a int primary key, b int, c int);
CREATE TABLE

root@:26257/defaultdb> insert into abc values (1, 1, 1);
INSERT 1

root@:26257/defaultdb> upsert into abc (a, c) values (1, 2);
INSERT 1

root@:26257/defaultdb> select * from abc;
  a | b | c
+---+---+---+
  1 | 1 | 2

Example #2: Column 'b' has default value

root@:26257/defaultdb> create table abc (a int primary key, b int default(10), c int);
CREATE TABLE

root@:26257/defaultdb> insert into abc values (1, 1, 1);
INSERT 1

root@:26257/defaultdb> upsert into abc (a, c) values (1, 2);
INSERT 1

root@:26257/defaultdb> select * from abc;
  a | b  | c
+---+----+---+
  1 | 10 | 2

Example #3: Column 'b' has NULL default value

root@:26257/defaultdb> create table abc (a int primary key, b int default(null), c int);
CREATE TABLE

root@:26257/defaultdb> insert into abc values (1, 1, 1);
INSERT 1

root@:26257/defaultdb> upsert into abc (a, c) values (1, 2);
INSERT 1

root@:26257/defaultdb> select * from abc;
  a |  b   | c
+---+------+---+
  1 | NULL | 2

EXPECTED: All three results should be the same, namely (1, 1, 2). If a column is not explicitly listed in the upsert, it should not be updated. Our docs (https://www.cockroachlabs.com/docs/stable/upsert.html) imply that only explicitly listed insert columns are translated into update columns. In addition, we should always treat nullable columns as if they were DEFAULT(NULL) - there should never be a semantic difference that depends on whether DEFAULT(NULL) is present or not. As another piece of data, VoltDB always returns (1, 1, 2).

ACTUAL: Each of the three results is different, depending on the DEFAULT value for column 'b'.
@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 21, 2018
@knz knz added the A-sql-mutations Mutation statements: UPDATE/INSERT/UPSERT/DELETE. label Dec 21, 2018
@knz knz added the S-3-erroneous-edge-case Database produces or stores erroneous data without visible error/warning, in rare edge cases. label Dec 21, 2018
@knz
Copy link
Contributor

knz commented Dec 21, 2018

Good find. Thank you for filing.

@andy-kimball
Copy link
Contributor Author

Here's another related case, showing the behavior when insert columns are not explicitly specified:

root@:26257/defaultdb> create table abc (a int primary key, b int, c int);
CREATE TABLE

root@:26257/defaultdb> insert into abc values (1, 1, 1);
INSERT 1

root@:26257/defaultdb> upsert into abc values (1, 2);
INSERT 1

root@:26257/defaultdb> select * from abc;
  a | b |  c
+---+---+------+
  1 | 2 | NULL

So we do insert NULL values in that case, as if the user had specified all columns.

@knz knz unassigned knz and andy-kimball Jan 2, 2019
@andy-kimball
Copy link
Contributor Author

NOTE: This works correctly with the CBO enabled. Recommend we close as "won't fix" for the heuristic planner, since it's been this way for a long time and we're deprecating it.

@jordanlewis
Copy link
Member

Closing as wont fix.

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-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

No branches or pull requests
3 participants
@jordanlewis @knz @andy-kimball