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

Check constraints do not work after autoincrement #2981

Closed
pgulutzan opened this issue Dec 5, 2017 · 0 comments
Closed

Check constraints do not work after autoincrement #2981

pgulutzan opened this issue Dec 5, 2017 · 0 comments
Assignees
@TheAviat0r
Labels
sql
Milestone
2.1.0

Comments

@pgulutzan
Copy link
Contributor

I have Ubuntu 14.04. I have Tarantool 1.8 pulled from source today.
I execute these requests:

CREATE TABLE t1 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 INTEGER, CHECK (s1 <> 19));
INSERT INTO t1 VALUES (19, NULL);
INSERT INTO t1 VALUES (18, NULL);
INSERT INTO t1 (s2) VALUES (NULL);
select * from t1;

Result: the first INSERT fails because of the CHECK constraint.
But the third INSERT succeeds, so I see a row where s1 = 19.
@pgulutzan pgulutzan added the sql label Dec 5, 2017
@kostja kostja added this to the 1.8.5 milestone Jan 9, 2018
TheAviat0r added a commit that referenced this issue Feb 13, 2018
@TheAviat0r
sql: fix autoincrement for constraints
6f80f40 
- Compute next autoincrement value before running constraints checks and
  put it to mentioned registers instead of NULL, like it was before.
- Implement new opcode OP_NextValue, which calls sequence_next from
  Tarantool and puts new value to necessary register.
- Implement necessary tests

Closes #2981
TheAviat0r added a commit that referenced this issue Feb 13, 2018
@TheAviat0r
sql: regenerate opcodes after fixing autoinc
02a2ed1 
Regenerate opcodes.c and opcodes.h after fixing autoincrement
in constraints.

For #2981
TheAviat0r added a commit that referenced this issue Feb 13, 2018
@TheAviat0r
sql: fix autoincrement for constraints
cb3c1c2 
- Compute next autoincrement value before running constraints checks and
  put it to mentioned registers instead of NULL, like it was before.
- Implement new opcode OP_NextValue, which calls sequence_next from
  Tarantool and puts new value to necessary register.
- Implement necessary tests

Closes #2981
TheAviat0r added a commit that referenced this issue Feb 13, 2018
@TheAviat0r
sql: regenerate opcodes after fixing autoinc
2bab9bc 
Regenerate opcodes.c and opcodes.h after fixing autoincrement
in constraints.

For #2981
TheAviat0r added a commit that referenced this issue Feb 21, 2018
@TheAviat0r
sql: fix autoincrement for constraints
d2ee628 
- Compute next autoincrement value before running constraints checks and
  put it to mentioned registers instead of NULL, like it was before.
- Implement new opcode OP_NextValue, which calls sequence_next from
  Tarantool and puts new value to necessary register.
- Implement necessary tests

Closes #2981
TheAviat0r added a commit that referenced this issue Feb 21, 2018
@TheAviat0r
sql: regenerate opcodes after fixing autoinc
270f2c2 
Regenerate opcodes.c and opcodes.h after fixing autoincrement
in constraints.

For #2981
TheAviat0r added a commit that referenced this issue Feb 21, 2018
@TheAviat0r
sql: fix autoincrement for constraints
f9c65bf 
- Compute next autoincrement value before running constraints checks and
  put it to mentioned registers instead of NULL, like it was before.
- Implement new opcode OP_NextValue, which calls sequence_next from
  Tarantool and puts new value to necessary register.
- Implement necessary tests
- Generate new opcodes set

Closes #2981
TheAviat0r added a commit that referenced this issue Feb 21, 2018
@TheAviat0r
sql: fix autoincrement for constraints
8369be3 
- Compute next autoincrement value before running
  constraints checks and put it to mentioned registers
  instead of NULL, like it was before.
- Implement new opcode OP_NextAutoincValue, which calls
  sequence_next from Tarantool and puts new value to
  necessary register.
- Implement necessary tests.
- Generate new opcodes set.

Closes #2981
TheAviat0r added a commit that referenced this issue Mar 5, 2018
@TheAviat0r
sql: fix autoincrement for constraints
99d27b3 
Autoincrement now is fully supported by Tarantool facilities. However,
during VDBE program execution there were no information about new value
from sequence, instead of new value there was a NULL. This made CHECK
constraints on autoincrement fields working inappropriately.

In this patch:
- Next autoincrement value is computing before
  running constraints check and is put to
  mentioned registers instead of NULL, like it was before.
- New opcode OP_NextAutoincValue was implemented, which
  calls sequence_next from Tarantool and puts new value
  to necessary register.

Closes #2981
TheAviat0r added a commit that referenced this issue Mar 6, 2018
@TheAviat0r
sql: fix autoincrement for constraints
30d24c6 
Autoincrement now is fully supported by Tarantool facilities. However,
during VDBE program execution there were no information about new value
from sequence, instead of new value there was a NULL. This made CHECK
constraints on autoincrement fields working inappropriately.

In this patch:
- Next autoincrement value is computing before
  running constraints check and is put to
  mentioned registers instead of NULL, like it was before.
- New opcode OP_NextAutoincValue was implemented, which
  calls sequence_next from Tarantool and puts new value
  to necessary register.

Closes #2981
TheAviat0r added a commit that referenced this issue Mar 7, 2018
@TheAviat0r
sql: fix autoincrement for constraints
68dfbd5 
Autoincrement now is fully supported by Tarantool facilities. However,
during VDBE program execution there were no information about new value
from sequence, instead of new value there was a NULL. This made CHECK
constraints on autoincrement fields working inappropriately.

In this patch:
- Next autoincrement value is computing before
  running constraints check and is put to
  mentioned registers instead of NULL, like it was before.
- New opcode OP_NextAutoincValue was implemented, which
  calls sequence_next from Tarantool and puts new value
  to necessary register.

Closes #2981
TheAviat0r added a commit that referenced this issue Mar 12, 2018
@TheAviat0r
sql: fix autoincrement for constraints
ff2a2bf 
Autoincrement now is fully supported by Tarantool facilities. However,
during VDBE program execution there were no information about new value
from sequence, instead of new value there was a NULL. This made CHECK
constraints on autoincrement fields working inappropriately.

In this patch:
- Next autoincrement value is computing before
  running constraints check and is put to
  mentioned registers instead of NULL, like it was before.
- New opcode OP_NextAutoincValue was implemented, which
  calls sequence_next from Tarantool and puts new value
  to necessary register.

Closes #2981
TheAviat0r added a commit that referenced this issue Mar 12, 2018
@TheAviat0r
sql: fix autoincrement for constraints
10e4b2b 
Autoincrement now is fully supported by Tarantool facilities. However,
during VDBE program execution there were no information about new value
from sequence, instead of new value there was a NULL. This made CHECK
constraints on autoincrement fields working inappropriately.

In this patch:
- Next autoincrement value is computing before
  running constraints check and is put to
  mentioned registers instead of NULL, like it was before.
- New opcode OP_NextAutoincValue was implemented, which
  calls sequence_next from Tarantool and puts new value
  to necessary register.

Closes #2981
ImeevMA added a commit that referenced this issue Aug 10, 2018
@ImeevMA
sql: return last_insert_id via IPROTO
a414c5d 
After this patch client will get last_insert_id
as additional metadata when he executes some
queries.

Part of ...

... document
Title: SQL function last_insert_id.
Function last_insert_id returns first autogenerated ID in
last INSERT/REPLACE statement in current session. Return
value of function is undetermined when more than one
INSERT/REPLACE statements executed asynchronously.
Example:
CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, a INTEGER);
INSERT INTO test VALUES (NULL, 1);
SELECT last_insert_id();

Temporary commit.

TODO:
1) Check that PK != NULL and delete unnesessary 'if' if so.
2) Add tests into gh-2981: insert ... (null, null, null).
3) Review fix.
4) Revert secuence back in case of quety failture.
5) Find out purpose of deleted 'if' in vdbe.c.
ImeevMA added a commit that referenced this issue Aug 10, 2018
@ImeevMA
sql: return last_insert_id via IPROTO
af31f16 
After this patch client will get last_insert_id
as additional metadata when he executes some
queries.

Part of ...

... document
Title: SQL function last_insert_id.
Function last_insert_id returns first autogenerated ID in
last INSERT/REPLACE statement in current session. Return
value of function is undetermined when more than one
INSERT/REPLACE statements executed asynchronously.
Example:
CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, a INTEGER);
INSERT INTO test VALUES (NULL, 1);
SELECT last_insert_id();

Temporary commit.

TODO:
1) Check that PK != NULL and delete unnesessary 'if' if so.
2) Add tests into gh-2981: insert ... (null, null, null).
3) Review fix.
4) Revert sequence back in case of query failture.
5) Find out purpose of deleted 'if' in vdbe.c.
ImeevMA added a commit that referenced this issue Aug 17, 2018
@ImeevMA
sql: move autoincrement in vdbe
c87a9ae 
This patch expands changes made in issue #2981. Now NULL
in primary key always replaced by generated value inside
of vdbe. It allows us to get last_insert_id with easy.

Part of #2618
ImeevMA added a commit that referenced this issue Aug 22, 2018
@ImeevMA
sql: move autoincrement in vdbe
c66035a 
This patch expands changes made in issue #2981. Now NULL
in primary key always replaced by generated value inside
of vdbe. It allows us to get last_insert_id with easy.

Part of ...
ImeevMA added a commit that referenced this issue Aug 22, 2018
@ImeevMA
sql: move autoincrement in vdbe
2ab414e 
This patch expands changes made in issue #2981. Now NULL
in primary key always replaced by generated value inside
of vdbe. It allows us to get last_insert_id with easy.

Part of #2618
ImeevMA added a commit that referenced this issue Sep 13, 2018
@ImeevMA
sql: move autoincrement in vdbe
6f641eb 
This patch expands changes made in issue #2981. Now NULL
in primary key always replaced by generated value inside
of vdbe. It allows us to get last_insert_id with easy.

Part of ...
ImeevMA added a commit that referenced this issue Sep 26, 2018
@ImeevMA
sql: move autoincrement in vdbe
d602717 
This patch expands changes made in issue #2981. Now NULL in
primary key always replaced by generated value during VDBE
execution. It allows us to get last_insert_id with ease.

Part of #2618
Closes #3670
ImeevMA added a commit that referenced this issue Sep 26, 2018
@ImeevMA
sql: move autoincrement in vdbe
e60c48d 
This patch expands changes made in issue #2981. Now NULL in
primary key always replaced by generated value during VDBE
execution. It allows us to get all generated ids with ease.

Part of #2618
Closes #3670
ImeevMA added a commit that referenced this issue Sep 26, 2018
@ImeevMA
sql: move autoincrement in vdbe
45a5c01 
This patch expands changes made in issue #2981. Now NULL in
primary key always replaced by generated value during VDBE
execution. It allows us to get all generated ids with ease.

Part of #2618
Closes #3670
ImeevMA added a commit that referenced this issue May 28, 2019
@ImeevMA
sql: do not show triggered autogenerated ids
5e41a5a 
At the moment this issue is blocked by 3691, as one test will be
broken till 3691 is done.

Test gh-2981-check-autoinc.test.lua was disabled and should be
enabled!

Closes ...4188
ImeevMA added a commit that referenced this issue Jul 3, 2019
@ImeevMA
sql: remove unnecessary AUTOINCREMENT ID generation
7f19b20 
Currently, if we perform something like
CREATE TABLE t (i INT PRIMARY KEY AUTOINCREMENT);
INSERT INTO t(a) VALUES (NULL);

we generate a new identifier in a special way. This was necessary
for the cases described in issue #2981. This is not necessary now,
because CHECK was moved to BOX due to issue #3691. This patch
removes the mentioned special way.

Part of #4188
ImeevMA added a commit that referenced this issue Jul 3, 2019
@ImeevMA
sql: remove unnecessary AUTOINCREMENT ID generation
519f25c 
Currently, if we perform something like
CREATE TABLE t (i INT PRIMARY KEY AUTOINCREMENT);
INSERT INTO t(a) VALUES (NULL);

we generate a new identifier in a special way. This was necessary
for the cases described in issue #2981. This is not necessary now,
because CHECK was moved to BOX due to issue #3691. This patch
removes the mentioned special way.

Part of #4188
ImeevMA added a commit that referenced this issue Jul 4, 2019
@ImeevMA
sql: remove unnecessary AUTOINCREMENT ID generation
96975db 
Currently, if we perform something like
CREATE TABLE t (i INT PRIMARY KEY AUTOINCREMENT);
INSERT INTO t(a) VALUES (NULL);

we generate a new identifier in a special way. This was necessary
for the cases described in issue #2981. Here it is:
CREATE TABLE t1 (
        s1 INTEGER PRIMARY KEY AUTOINCREMENT,
        s2 INTEGER,
        CHECK (s1 <> 19)
);
INSERT INTO t1 VALUES (18, NULL);
INSERT INTO t1 (s2) VALUES (NULL);

In this case, the CHECK did not work properly. This is not
necessary now, because CHECK was moved to BOX due to issue #3691.
This patch removes the mentioned special way.

Part of #4188
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@TheAviat0r TheAviat0r

Labels
sql
Projects
None yet
Milestone
2.1.0
Development

No branches or pull requests
3 participants
@kostja @pgulutzan @TheAviat0r