Additional generated identifiers when INSERT is executed by triggers. #4188

ImeevMA · 2019-04-27T17:32:02Z

Tarantool version:
Tarantool 2.2.0-231-g8b350d4

OS version:
Ubuntu16.04

Bug description:
Additional generated identifiers when INSERT is executed by triggers.

Steps to reproduce:

box.execute('CREATE TABLE t1 (i INT PRIMARY KEY);')
box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);')
box.execute('CREATE TRIGGER r AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES (null); END')
box.execute('INSERT INTO t1 VALUES (10), (15), (20);')

Result:
tarantool> box.execute('INSERT INTO t1 VALUES (10), (15), (20);')
---
- autoincrement_ids:
  - 1
  - 2
  - 3
  row_count: 3
...

The text was updated successfully, but these errors were encountered:

Currently, if INSERT is executed by a trigger, the new generated identifiers will be stored in the VDBE. This is wrong, and this patch fixes it. Only identifiers generated during INSERT run by the user will be saved. Closes #4188

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

Currently, if an INSERT was executed by a trigger during the execution of a statement, if there were any generated identifiers, they can be displayed as a result of the statement. This is incorrect, since we are not able to divide the IDs obtained into those that belong to the table mentioned in the statement and those that do not belong to this table. For example: box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TRIGGER r AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES (null); END') box.execute('INSERT INTO t2 VALUES (100);') box.execute('INSERT INTO t1 VALUES (NULL), (NULL), (NULL);') Result should be: --- - autoincrement_ids: - 1 - 2 - 3 row_count: 3 ... Closes #4188

VDBE was added to TXN because the generated identifiers were added to VDBE in the sequence_next() function. Since they are now stored in the VDBE itself, it is not necessary to have it in TXN. Follow-up #4188

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

Currently, if an INSERT was executed by a trigger during the execution of a statement, if there were any generated identifiers, they can be displayed as a result of the statement. This is incorrect, since we are not able to divide the IDs obtained into those that belong to the table mentioned in the statement and those that do not belong to this table. For example: box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TRIGGER r AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES (null); END') box.execute('INSERT INTO t2 VALUES (100);') box.execute('INSERT INTO t1 VALUES (NULL), (NULL), (NULL);') Result should be: --- - autoincrement_ids: - 1 - 2 - 3 row_count: 3 ... Closes #4188

VDBE was added to TXN because the generated identifiers were added to VDBE in the sequence_next() function. Since they are now stored in the VDBE itself, it is not necessary to have it in TXN. Follow-up #4188

Currently, if an INSERT was executed by a trigger during the execution of a statement, if there were any generated identifiers, they can be displayed as a result of the statement. This is incorrect, since we are not able to divide the IDs obtained into those that belong to the table mentioned in the statement and those that do not belong to this table. For example: box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TRIGGER r AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES (null); END') box.execute('INSERT INTO t2 VALUES (100);') box.execute('INSERT INTO t1 VALUES (NULL), (NULL), (NULL);') Result should be: --- - autoincrement_ids: - 1 - 2 - 3 row_count: 3 ... Closes #4188

VDBE was added to TXN because the generated identifiers were added to VDBE in the sequence_next() function. Since they are now stored in the VDBE itself, it is not necessary to have it in TXN. Follow-up #4188

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

Currently, if an INSERT was executed by a trigger during the execution of a statement, if there were any generated identifiers, they can be displayed as a result of the statement. This is incorrect, since we are not able to divide the IDs obtained into those that belong to the table mentioned in the statement and those that do not belong to this table. For example: box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TRIGGER r AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES (null); END') box.execute('INSERT INTO t2 VALUES (100);') box.execute('INSERT INTO t1 VALUES (NULL), (NULL), (NULL);') Result should be: --- - autoincrement_ids: - 1 - 2 - 3 row_count: 3 ... Closes #4188

VDBE was added to TXN because the generated identifiers were added to VDBE in the sequence_next() function. Since they are now stored in the VDBE itself, it is not necessary to have it in TXN. Follow-up #4188

Currently, if we perform something like 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); we generate a new identifier in VDBE, but in any other case we generate it in BOX. That was needed since the CHECK did not work properly. This is not necessary now, because CHECK was moved to BOX due to issue #3691. After this patch all new identifiers will be generated in BOX. Part of #4188

Currently, if an INSERT was executed by a trigger during the execution of a statement, if there were any generated identifiers, they can be displayed as a result of the statement. This is incorrect, since we are not able to divide the IDs obtained into those that belong to the table mentioned in the statement and those that do not belong to this table. For example: box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TRIGGER r AFTER INSERT ON t1 FOR EACH ROW '.. 'BEGIN INSERT INTO t2 VALUES (null); END') box.execute('INSERT INTO t2 VALUES (100);') box.execute('INSERT INTO t1 VALUES (NULL), (NULL), (NULL);') Result should be: --- - autoincrement_ids: - 1 - 2 - 3 row_count: 3 ... Closes #4188

VDBE was added to TXN because the generated identifiers were added to VDBE in the sequence_next() function. Since they are now stored in the VDBE itself, it is not necessary to have it in TXN. Follow-up #4188

When the INSERT was executed as an INSERT OR IGNORE, it was possible to get the generated autoincrement identifiers, even if the insert failed. After this patch, only in case of successful insertion, the generated identifiers will be returned. Follow-up #4188

Currently, if an INSERT was executed by a trigger during the execution of a statement, if there were any generated identifiers, they can be displayed as a result of the statement. This is incorrect, since we are not able to divide the IDs obtained into those that belong to the table mentioned in the statement and those that do not belong to this table. This has now been fixed by adding a new opcode and using it to retrieve and save the newly generated identifiers. For example: box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TRIGGER r AFTER INSERT ON t1 FOR EACH ROW '.. 'BEGIN INSERT INTO t2 VALUES (null); END') box.execute('INSERT INTO t2 VALUES (100);') box.execute('INSERT INTO t1 VALUES (NULL), (NULL), (NULL);') Result should be: --- - autoincrement_ids: - 1 - 2 - 3 row_count: 3 ... Closes #4188

VDBE was added to TXN because the generated identifiers were added to VDBE in the sequence_next() function. Since they are now stored in the VDBE itself, it is not necessary to have it in TXN. Follow-up #4188

When the INSERT was executed as an INSERT OR IGNORE, it was possible to get the generated autoincrement identifiers, even if the insert failed. After this patch, only in case of successful insertion, the generated identifiers will be returned. Follow-up #4188

Currently, if an INSERT was executed by a trigger during the execution of a statement, if there were any generated identifiers, they can be displayed as a result of the statement. This is incorrect, since we are not able to divide the IDs obtained into those that belong to the table mentioned in the statement and those that do not belong to this table. This has now been fixed by adding a new opcode and using it to retrieve and save the newly generated identifiers. For example: box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TRIGGER r AFTER INSERT ON t1 FOR EACH ROW '.. 'BEGIN INSERT INTO t2 VALUES (null); END') box.execute('INSERT INTO t2 VALUES (100);') box.execute('INSERT INTO t1 VALUES (NULL), (NULL), (NULL);') Result should be: --- - autoincrement_ids: - 1 - 2 - 3 row_count: 3 ... Closes #4188

VDBE was added to TXN because the generated identifiers were added to VDBE in the sequence_next() function. Since they are now stored in the VDBE itself, it is not necessary to have it in TXN. Follow-up #4188

When the INSERT was executed as an INSERT OR IGNORE, it was possible to get the generated autoincrement identifiers, even if the insert failed. After this patch, only in case of successful insertion, the generated identifiers will be returned. Follow-up #4188

When the INSERT was executed as an INSERT OR IGNORE, it was possible to get the generated autoincrement identifiers, even if the insert failed. For example: CREATE TABLE t (i INT PRIMARY KEY AUTOINCREMENT, a INT check (a > 0)); INSERT OR IGNORE INTO t VALUES (null, 1), (null, -1), (null, 2); should return only two identifiers. After this patch, only in case of successful insertion, the generated identifiers will be returned. Also, row-count shows now number of successfull insertions in case of INSERT OR IGNORE. Follow-up #4188

Currently, if we perform something like 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); we generate a new identifier in VDBE, but in any other case we generate it in BOX. That was needed since the CHECK did not work properly. This is not necessary now, because CHECK was moved to BOX due to issue #3691. After this patch all new identifiers will be generated in BOX. Part of #4188

Currently, if an INSERT was executed by a trigger during the execution of a statement, if there were any generated identifiers, they can be displayed as a result of the statement. This is incorrect, since we are not able to divide the IDs obtained into those that belong to the table mentioned in the statement and those that do not belong to this table. This has now been fixed by adding a new opcode and using it to retrieve and save the newly generated identifiers. For example: box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TRIGGER r AFTER INSERT ON t1 FOR EACH ROW '.. 'BEGIN INSERT INTO t2 VALUES (null); END') box.execute('INSERT INTO t2 VALUES (100);') box.execute('INSERT INTO t1 VALUES (NULL), (NULL), (NULL);') Result should be: --- - autoincrement_ids: - 1 - 2 - 3 row_count: 3 ... Closes #4188

Currently, if we perform something like 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); we generate a new identifier in VDBE, but in any other case we generate it in BOX. That was needed since the CHECK did not work properly. This is not necessary now, because CHECK was moved to BOX due to issue #3691. After this patch all new identifiers will be generated in BOX. Part of #4188

Currently, if an INSERT is executed inside SQL trigger and it results in generated autoincrement identifiers, ones will be displayed as a result of the statement. This is wrong, since we are not able to divide IDs obtained into those that belong to the table mentioned in the statement and those that do not belong to this table. This has been fixed by adding a new argument to OP_IdxInsert. In case the argument is not 0, recently generated identifier is retrieved and saved into the list, which is held in VDBE itself. Note that from now we don't save autoincremented value to VDBE right in sequence_next() - this operation is moved to OP_IdxInsert. So that, VDBE can be removed from struct txn. For example: box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TRIGGER r AFTER INSERT ON t1 FOR EACH ROW '.. 'BEGIN INSERT INTO t2 VALUES (null); END') box.execute('INSERT INTO t2 VALUES (100);') box.execute('INSERT INTO t1 VALUES (NULL), (NULL), (NULL);') Result should be: --- - autoincrement_ids: - 1 - 2 - 3 row_count: 3 ... Closes #4188

VDBE was added to TXN because the generated identifiers were added to VDBE in the sequence_next() function. Since they are now stored in the VDBE itself, it is not necessary to have it in TXN. Follow-up #4188

If INSERT statement is executed with IGNORE error action (i.e. INSERT OR IGNORE ...), it will return number of rows inserted. For example: CREATE TABLE t (i INT PRIMARY KEY, a INT check (a > 0)); INSERT OR IGNORE INTO t VALUES (1, 1), (2, -1), (3, 2); Should return: --- - row_count: 2 ... However it was three before this patch. So, let's account number of successful insertions in case of INSERT or REPLACE. Follow-up #4188

Currently, if we perform something like 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); we generate a new identifier in VDBE, but in any other case we generate it in BOX. That was needed since the CHECK did not work properly. This is not necessary now, because CHECK was moved to BOX due to issue #3691. After this patch all new identifiers will be generated in BOX. Part of #4188

Currently, if an INSERT is executed inside SQL trigger and it results in generated autoincrement identifiers, ones will be displayed as a result of the statement. This is wrong, since we are not able to divide IDs obtained into those that belong to the table mentioned in the statement and those that do not belong to this table. This has been fixed by adding a new argument to OP_IdxInsert. In case the argument is not 0, recently generated identifier is retrieved and saved into the list, which is held in VDBE itself. Note that from now we don't save autoincremented value to VDBE right in sequence_next() - this operation is moved to OP_IdxInsert. So that, VDBE can be removed from struct txn. For example: box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);') box.execute('CREATE TRIGGER r AFTER INSERT ON t1 FOR EACH ROW '.. 'BEGIN INSERT INTO t2 VALUES (null); END') box.execute('INSERT INTO t2 VALUES (100);') box.execute('INSERT INTO t1 VALUES (NULL), (NULL), (NULL);') Result should be: --- - autoincrement_ids: - 1 - 2 - 3 row_count: 3 ... Closes #4188

VDBE was added to TXN because the generated identifiers were added to VDBE in the sequence_next() function. Since they are now stored in the VDBE itself, it is not necessary to have it in TXN. Follow-up #4188

If INSERT statement is executed with IGNORE error action (i.e. INSERT OR IGNORE ...), it will return number of rows inserted. For example: CREATE TABLE t (i INT PRIMARY KEY, a INT check (a > 0)); INSERT OR IGNORE INTO t VALUES (1, 1), (2, -1), (3, 2); Should return: --- - row_count: 2 ... However it was three before this patch. So, let's account number of successful insertions in case of INSERT OR IGNORE. Follow-up #4188