* [tarantool-patches] [PATCH v2 0/5] sql: do not use OP_Delete+OP_Insert for UPDATES
@ 2018-12-29 10:48 Kirill Shcherbatov
2018-12-29 10:48 ` [tarantool-patches] [PATCH v2 1/5] sql: clean-up vdbe_emit_constraint_checks Kirill Shcherbatov
` (5 more replies)
0 siblings, 6 replies; 13+ messages in thread
From: Kirill Shcherbatov @ 2018-12-29 10:48 UTC (permalink / raw)
To: tarantool-patches, korablev; +Cc: Kirill Shcherbatov
Introduced a new OP_Update opcode making Tarantool native Update
operation.
In case of UPDATE or REPLACE we can't use new OP_Update as it
has a complex SQL-specific semantics:
CREATE TABLE tj (s1 INT PRIMARY KEY, s2 INT);
INSERT INTO tj VALUES (1, 3),(2, 4),(3,5);
CREATE UNIQUE INDEX i ON tj (s2);
SELECT * FROM tj;
[1, 3], [2, 4], [3, 5]
UPDATE OR REPLACE tj SET s2 = s2 + 1;
SELECT * FROM tj;
[1, 4], [3, 6]
I.e. [1, 3] tuple is updated as [1, 4] and have replaced tuple
[2, 4]. This logic is implemented as preventive tuples deletion
by all corresponding indexes in SQL.
The other significant change is forbidden primary key update.
It was possible to deal with it the same way like with or
REPLACE specifier but we need an atomic UPDATE step for #3691
ticket to support "or IGNORE/or ABORT/or FAIL" specifiers.
Reworked tests to make testing avoiding primary key UPDATE where
possible.
Fixed bug in VDBE - sometimes temporal tuples in memory allocated
with sql_vdbe_mem_alloc_region were stored in Mem variable having
invalid flags set.
Changes in version 2:
- Reworked part of code to close tikets #3035, #3918: reused
a new routine mpstream_encode_vdbe_mem to encode tuples on
region everywhere on Vdbe execution: got rid of routines
sqlite3VdbeMsgpackRecordLen and sqlite3VdbeMsgpackRecordPut
that became useless.
- Fixed few spell mistakes in comments and commit messages
- Minor code changes: better mpstream methods names, changed
OP_IdxUpdate to OP_Update name.
Branch: http://github.com/tarantool/tarantool/tree/kshch/gh-3850-op-update
Issue: https://github.com/tarantool/tarantool/issues/3850
Kirill Shcherbatov (5):
sql: clean-up vdbe_emit_constraint_checks
sql: fix sql_vdbe_mem_alloc_region result memory
sql: fix fkey exception for self-referenced table
sql: encode tuples with mpstream on Vdbe run
sql: do not use OP_Delete+OP_Insert for UPDATES
src/box/sql.c | 27 ++-
src/box/sql/fkey.c | 58 ++++---
src/box/sql/insert.c | 28 +--
src/box/sql/sqliteInt.h | 14 ++
src/box/sql/update.c | 88 +++++++---
src/box/sql/vdbe.c | 138 +++++++++++++--
src/box/sql/vdbeInt.h | 34 +++-
src/box/sql/vdbeaux.c | 74 +-------
src/box/sql/vdbemem.c | 65 +++++++
src/mpstream.c | 30 ++++
src/mpstream.h | 11 ++
test/sql-tap/alter2.test.lua | 16 +-
test/sql-tap/analyze9.test.lua | 43 ++++-
test/sql-tap/bigrow1.test.lua | 12 +-
test/sql-tap/check.test.lua | 14 +-
test/sql-tap/fkey2.test.lua | 161 +++++++++---------
test/sql-tap/fkey3.test.lua | 25 ++-
test/sql-tap/fkey4.test.lua | 32 ++--
test/sql-tap/gh2140-trans.test.lua | 26 +--
.../gh2250-trigger-chain-limit.test.lua | 8 +-
test/sql-tap/gh2259-in-stmt-trans.test.lua | 12 +-
test/sql-tap/identifier_case.test.lua | 2 +-
test/sql-tap/index6.test.lua | 5 +-
test/sql-tap/intpkey.test.lua | 23 ++-
test/sql-tap/misc1.test.lua | 20 ++-
test/sql-tap/quote.test.lua | 6 +-
test/sql-tap/table.test.lua | 2 +-
test/sql-tap/tkt-a8a0d2996a.test.lua | 4 +-
test/sql-tap/tkt2767.test.lua | 16 +-
test/sql-tap/tkt2832.test.lua | 10 +-
test/sql-tap/tkt3554.test.lua | 15 +-
test/sql-tap/trigger2.test.lua | 28 +--
test/sql-tap/trigger7.test.lua | 2 +-
test/sql-tap/triggerB.test.lua | 28 +--
test/sql-tap/triggerC.test.lua | 43 ++---
test/sql-tap/triggerD.test.lua | 4 +-
test/sql-tap/update.test.lua | 31 ++--
test/sql-tap/with1.test.lua | 24 +--
test/sql/collation.result | 12 +-
test/sql/collation.test.lua | 12 +-
test/sql/on-conflict.result | 8 +-
test/sql/on-conflict.test.lua | 8 +-
test/sql/row-count.result | 10 +-
test/sql/row-count.test.lua | 10 +-
test/sql/triggers.result | 16 +-
test/sql/triggers.test.lua | 16 +-
46 files changed, 781 insertions(+), 490 deletions(-)
--
2.19.2
^ permalink raw reply [flat|nested] 13+ messages in thread
* [tarantool-patches] [PATCH v2 1/5] sql: clean-up vdbe_emit_constraint_checks
2018-12-29 10:48 [tarantool-patches] [PATCH v2 0/5] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov
@ 2018-12-29 10:48 ` Kirill Shcherbatov
2018-12-29 10:48 ` [tarantool-patches] [PATCH v2 2/5] sql: fix sql_vdbe_mem_alloc_region result memory Kirill Shcherbatov
` (4 subsequent siblings)
5 siblings, 0 replies; 13+ messages in thread
From: Kirill Shcherbatov @ 2018-12-29 10:48 UTC (permalink / raw)
To: tarantool-patches, korablev; +Cc: Kirill Shcherbatov
Removed vdbe code generation making type checks from
vdbe_emit_constraint_checks as it is useless since strict types
have been introduced.
---
src/box/sql/insert.c | 21 ---------------------
1 file changed, 21 deletions(-)
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index 6b76bb6da..1b02ea907 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -965,27 +965,6 @@ vdbe_emit_constraint_checks(struct Parse *parse_context, struct Table *tab,
}
}
sql_emit_table_affinity(v, tab->def, new_tuple_reg);
- /*
- * If PK is marked as INTEGER, use it as strict type,
- * not as affinity. Emit code for type checking.
- * FIXME: should be removed after introducing
- * strict typing.
- */
- struct index *pk = space_index(tab->space, 0);
- uint32_t part_count = pk->def->key_def->part_count;
- if (part_count == 1) {
- uint32_t fieldno = pk->def->key_def->parts[0].fieldno;
- int reg_pk = new_tuple_reg + fieldno;
- if (def->fields[fieldno].affinity == AFFINITY_INTEGER) {
- int skip_if_null = sqlite3VdbeMakeLabel(v);
- if (autoinc_fieldno != UINT32_MAX) {
- sqlite3VdbeAddOp2(v, OP_IsNull, reg_pk,
- skip_if_null);
- }
- sqlite3VdbeAddOp2(v, OP_MustBeInt, reg_pk, 0);
- sqlite3VdbeResolveLabel(v, skip_if_null);
- }
- }
/*
* Other actions except for REPLACE and UPDATE OR IGNORE
* can be handled by setting appropriate flag in OP_Halt.
--
2.19.2
^ permalink raw reply [flat|nested] 13+ messages in thread
* [tarantool-patches] [PATCH v2 2/5] sql: fix sql_vdbe_mem_alloc_region result memory
2018-12-29 10:48 [tarantool-patches] [PATCH v2 0/5] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov
2018-12-29 10:48 ` [tarantool-patches] [PATCH v2 1/5] sql: clean-up vdbe_emit_constraint_checks Kirill Shcherbatov
@ 2018-12-29 10:48 ` Kirill Shcherbatov
2018-12-29 10:49 ` [tarantool-patches] [PATCH v2 3/5] sql: fix fkey exception for self-referenced table Kirill Shcherbatov
` (3 subsequent siblings)
5 siblings, 0 replies; 13+ messages in thread
From: Kirill Shcherbatov @ 2018-12-29 10:48 UTC (permalink / raw)
To: tarantool-patches, korablev; +Cc: Kirill Shcherbatov
Function sql_vdbe_mem_alloc_region() that constructs the value
of Vdbe Mem object used to change only type related flags.
However, it is also required to erase other flags (for instance
flags related to allocation policy: static, dynamic etc), since
their combination may be invalid.
In a typical Vdbe scenario, OP_MakeRecord and OP_RowData release
memory with sqlite3VdbeMemRelease() and allocate on region with
sql_vdbe_mem_alloc_region(). An integrity assert based on
sqlite3VdbeCheckMemInvariants() would fire here due to
incompatible combination of flags:
MEM_Static | (MEM_Blob | MEM_Ephem).
Needed for #3850
---
src/box/sql/vdbeaux.c | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)
diff --git a/src/box/sql/vdbeaux.c b/src/box/sql/vdbeaux.c
index fc805e3aa..d477662a4 100644
--- a/src/box/sql/vdbeaux.c
+++ b/src/box/sql/vdbeaux.c
@@ -3231,7 +3231,8 @@ sql_vdbe_mem_alloc_region(Mem *vdbe_mem, uint32_t size)
vdbe_mem->z = region_alloc(&fiber()->gc, size);
if (vdbe_mem->z == NULL)
return SQLITE_NOMEM;
- MemSetTypeFlag(vdbe_mem, MEM_Blob | MEM_Ephem);
+ vdbe_mem->flags = MEM_Ephem | MEM_Blob;
+ assert(sqlite3VdbeCheckMemInvariants(vdbe_mem));
return SQLITE_OK;
}
--
2.19.2
^ permalink raw reply [flat|nested] 13+ messages in thread
* [tarantool-patches] [PATCH v2 3/5] sql: fix fkey exception for self-referenced table
2018-12-29 10:48 [tarantool-patches] [PATCH v2 0/5] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov
2018-12-29 10:48 ` [tarantool-patches] [PATCH v2 1/5] sql: clean-up vdbe_emit_constraint_checks Kirill Shcherbatov
2018-12-29 10:48 ` [tarantool-patches] [PATCH v2 2/5] sql: fix sql_vdbe_mem_alloc_region result memory Kirill Shcherbatov
@ 2018-12-29 10:49 ` Kirill Shcherbatov
2018-12-29 13:26 ` [tarantool-patches] " n.pettik
2018-12-29 10:49 ` [tarantool-patches] [PATCH v2 4/5] sql: encode tuples with mpstream on Vdbe run Kirill Shcherbatov
` (2 subsequent siblings)
5 siblings, 1 reply; 13+ messages in thread
From: Kirill Shcherbatov @ 2018-12-29 10:49 UTC (permalink / raw)
To: tarantool-patches, korablev; +Cc: Kirill Shcherbatov
UPDATE operation doesn't fail when fkey self-reference condition
unsatisfied, when table has other records.
To do not raise error where it is not necessary Vdbe makes
lookup in parent table with OP_Found. This branch is not valid
for self-referenced table since its looking for a tuple affected
by UPDATE operation and since the foreign key has already
detected a conflict it mast be raised.
Example:
CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT, c INT, d TEXT, UNIQUE(a, b),
FOREIGN KEY(c, d) REFERENCES t6(a, b));
INSERT INTO t6 VALUES(1, 'a', 1, 'a');
INSERT INTO t6 VALUES(100, 'one', 100, 'one');
UPDATE t6 SET c = 1, d = 'a' WHERE a = 100;
-- fk conflict must be raised here
Needed for #3850
Closes #3918
---
src/box/sql/fkey.c | 58 +++++++++++++++++++++++--------------
test/sql-tap/fkey3.test.lua | 5 ++--
2 files changed, 38 insertions(+), 25 deletions(-)
diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c
index 4e3270f0c..6242275c6 100644
--- a/src/box/sql/fkey.c
+++ b/src/box/sql/fkey.c
@@ -194,7 +194,7 @@
static void
fkey_lookup_parent(struct Parse *parse_context, struct space *parent,
struct fkey_def *fk_def, uint32_t referenced_idx,
- int reg_data, int incr_count)
+ int reg_data, int incr_count, bool is_update)
{
assert(incr_count == -1 || incr_count == 1);
struct Vdbe *v = sqlite3GetVdbe(parse_context);
@@ -221,14 +221,6 @@ fkey_lookup_parent(struct Parse *parse_context, struct space *parent,
sqlite3VdbeAddOp2(v, OP_IsNull, reg, ok_label);
}
uint32_t field_count = fk_def->field_count;
- int temp_regs = sqlite3GetTempRange(parse_context, field_count);
- int rec_reg = sqlite3GetTempReg(parse_context);
- vdbe_emit_open_cursor(parse_context, cursor, referenced_idx, parent);
- link = fk_def->links;
- for (uint32_t i = 0; i < field_count; ++i, ++link) {
- sqlite3VdbeAddOp2(v, OP_Copy, link->child_field + 1 + reg_data,
- temp_regs + i);
- }
/*
* If the parent table is the same as the child table, and
* we are about to increment the constraint-counter (i.e.
@@ -253,15 +245,36 @@ fkey_lookup_parent(struct Parse *parse_context, struct space *parent,
}
sqlite3VdbeGoto(v, ok_label);
}
- struct index *idx = space_index(parent, referenced_idx);
- assert(idx != NULL);
- sqlite3VdbeAddOp4(v, OP_MakeRecord, temp_regs, field_count, rec_reg,
- sql_space_index_affinity_str(parse_context->db,
- parent->def, idx->def),
- P4_DYNAMIC);
- sqlite3VdbeAddOp4Int(v, OP_Found, cursor, ok_label, rec_reg, 0);
- sqlite3ReleaseTempReg(parse_context, rec_reg);
- sqlite3ReleaseTempRange(parse_context, temp_regs, field_count);
+ /**
+ * Make a lookup in a parent table with OP_Found.
+ * We mustn't make it for a self-referenced table since
+ * it's tuple will be modified by the update operation.
+ * And since the foreign key has already detected a
+ * conflict, fk counter must be increased.
+ */
+ if (!(fkey_is_self_referenced(fk_def) && is_update)) {
+ int temp_regs = sqlite3GetTempRange(parse_context, field_count);
+ int rec_reg = sqlite3GetTempReg(parse_context);
+ vdbe_emit_open_cursor(parse_context, cursor, referenced_idx,
+ parent);
+ link = fk_def->links;
+ for (uint32_t i = 0; i < field_count; ++i, ++link) {
+ sqlite3VdbeAddOp2(v, OP_Copy,
+ link->child_field + 1 + reg_data,
+ temp_regs + i);
+ }
+ struct index *idx = space_index(parent, referenced_idx);
+ assert(idx != NULL);
+ sqlite3VdbeAddOp4(v, OP_MakeRecord, temp_regs, field_count,
+ rec_reg,
+ sql_space_index_affinity_str(parse_context->db,
+ parent->def,
+ idx->def),
+ P4_DYNAMIC);
+ sqlite3VdbeAddOp4Int(v, OP_Found, cursor, ok_label, rec_reg, 0);
+ sqlite3ReleaseTempReg(parse_context, rec_reg);
+ sqlite3ReleaseTempRange(parse_context, temp_regs, field_count);
+ }
struct session *session = current_session();
if (!fk_def->is_deferred &&
(session->sql_flags & SQLITE_DeferFKs) == 0 &&
@@ -517,6 +530,7 @@ void
fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old,
int reg_new, const int *changed_cols)
{
+ bool is_update = changed_cols != NULL;
struct sqlite3 *db = parser->db;
struct session *user_session = current_session();
@@ -534,7 +548,7 @@ fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old,
struct fkey *fk;
rlist_foreach_entry(fk, &space->child_fkey, child_link) {
struct fkey_def *fk_def = fk->def;
- if (changed_cols != NULL && !fkey_is_self_referenced(fk_def) &&
+ if (is_update && !fkey_is_self_referenced(fk_def) &&
!fkey_is_modified(fk_def, FIELD_LINK_CHILD, changed_cols))
continue;
parser->nTab++;
@@ -549,7 +563,7 @@ fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old,
* foreign key constraint violation.
*/
fkey_lookup_parent(parser, parent, fk_def, fk->index_id,
- reg_old, -1);
+ reg_old, -1, is_update);
}
if (reg_new != 0 && !fkey_action_is_set_null(parser, fk)) {
/*
@@ -568,7 +582,7 @@ fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old,
* cause an FK violation.
*/
fkey_lookup_parent(parser, parent, fk_def, fk->index_id,
- reg_new, +1);
+ reg_new, +1, is_update);
}
}
/*
@@ -577,7 +591,7 @@ fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old,
*/
rlist_foreach_entry(fk, &space->parent_fkey, parent_link) {
struct fkey_def *fk_def = fk->def;
- if (changed_cols != NULL &&
+ if (is_update &&
!fkey_is_modified(fk_def, FIELD_LINK_PARENT, changed_cols))
continue;
if (!fk_def->is_deferred &&
diff --git a/test/sql-tap/fkey3.test.lua b/test/sql-tap/fkey3.test.lua
index 8fbbdcfbc..9bd1aef23 100755
--- a/test/sql-tap/fkey3.test.lua
+++ b/test/sql-tap/fkey3.test.lua
@@ -190,15 +190,14 @@ test:do_execsql_test(
-- </fkey3-3.8>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"fkey3-3.9",
[[
INSERT INTO t6 VALUES(100, 'one', 100, 'one');
UPDATE t6 SET c = 1, d = 'a' WHERE a = 100;
- DELETE FROM t6 WHERE a = 100;
- SELECT * FROM t6 WHERE a = 100;
]], {
-- <fkey3-3.9>
+ 1, "FOREIGN KEY constraint failed"
-- </fkey3-3.9>
})
--
2.19.2
^ permalink raw reply [flat|nested] 13+ messages in thread
* [tarantool-patches] [PATCH v2 4/5] sql: encode tuples with mpstream on Vdbe run
2018-12-29 10:48 [tarantool-patches] [PATCH v2 0/5] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov
` (2 preceding siblings ...)
2018-12-29 10:49 ` [tarantool-patches] [PATCH v2 3/5] sql: fix fkey exception for self-referenced table Kirill Shcherbatov
@ 2018-12-29 10:49 ` Kirill Shcherbatov
2018-12-29 13:26 ` [tarantool-patches] " n.pettik
2018-12-29 10:49 ` [tarantool-patches] [PATCH v2 5/5] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov
2019-01-10 12:30 ` [tarantool-patches] Re: [PATCH v2 0/5] " Kirill Yukhin
5 siblings, 1 reply; 13+ messages in thread
From: Kirill Shcherbatov @ 2018-12-29 10:49 UTC (permalink / raw)
To: tarantool-patches, korablev; +Cc: Kirill Shcherbatov
Introduced new sql_vdbe_mem_encode_tuple and
mpstream_encode_vdbe_mem routines to perform Vdbe memory to
msgpack encoding on region without previous size estimation call.
Got rid off sqlite3VdbeMsgpackRecordLen and
sqlite3VdbeMsgpackRecordPut functions that became useless. This
approach also resolves problem with invalid size estimation #3035
because it is not required anymore.
Needed for #3850
Closes #3035
---
src/box/sql.c | 27 ++++++++--------
src/box/sql/vdbe.c | 31 +++++++++++--------
src/box/sql/vdbeInt.h | 26 ++++++++++++++--
src/box/sql/vdbeaux.c | 71 -------------------------------------------
src/box/sql/vdbemem.c | 70 ++++++++++++++++++++++++++++++++++++++++++
src/mpstream.c | 30 ++++++++++++++++++
src/mpstream.h | 11 +++++++
7 files changed, 167 insertions(+), 99 deletions(-)
diff --git a/src/box/sql.c b/src/box/sql.c
index 8c7607d84..081a038f1 100644
--- a/src/box/sql.c
+++ b/src/box/sql.c
@@ -34,6 +34,7 @@
#include "sql/sqliteInt.h"
#include "sql/tarantoolInt.h"
#include "sql/vdbeInt.h"
+#include "mpstream.h"
#include "index.h"
#include <info.h>
@@ -268,15 +269,20 @@ int tarantoolSqlite3Previous(BtCursor *pCur, int *pRes)
int tarantoolSqlite3MovetoUnpacked(BtCursor *pCur, UnpackedRecord *pIdxKey,
int *pRes)
{
- int rc, res_success;
- size_t ks;
-
- ks = sqlite3VdbeMsgpackRecordLen(pIdxKey->aMem, pIdxKey->nField);
- if (key_alloc(pCur, ks) != 0)
+ struct region *region = &fiber()->gc;
+ size_t used = region_used(region);
+ uint32_t tuple_size;
+ const char *tuple =
+ sql_vdbe_mem_encode_tuple(pIdxKey->aMem, pIdxKey->nField,
+ &tuple_size, region);
+ if (tuple == NULL)
return SQL_TARANTOOL_ERROR;
- sqlite3VdbeMsgpackRecordPut((u8 *)pCur->key, pIdxKey->aMem,
- pIdxKey->nField);
+ if (key_alloc(pCur, tuple_size) != 0)
+ return SQL_TARANTOOL_ERROR;
+ memcpy(pCur->key, tuple, tuple_size);
+ region_truncate(region, used);
+ int rc, res_success;
switch (pIdxKey->opcode) {
default:
/* "Unexpected opcode" */
@@ -697,13 +703,6 @@ rename_fail:
return SQL_TARANTOOL_ERROR;
}
-/** Callback to forward and error from mpstream methods. */
-static void
-set_encode_error(void *error_ctx)
-{
- *(bool *)error_ctx = true;
-}
-
int
sql_rename_table(uint32_t space_id, const char *new_name)
{
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index e6b413c70..b8faa8f45 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -49,6 +49,7 @@
#include "tarantoolInt.h"
#include "msgpuck/msgpuck.h"
+#include "mpstream.h"
#include "box/schema.h"
#include "box/space.h"
@@ -2848,7 +2849,6 @@ case OP_Affinity: {
*/
case OP_MakeRecord: {
Mem *pRec; /* The new record */
- i64 nByte; /* Data space required for this record */
Mem *pData0; /* First field to be combined into the record */
Mem MAYBE_UNUSED *pLast; /* Last field of the record */
int nField; /* Number of fields in the record */
@@ -2894,14 +2894,17 @@ case OP_MakeRecord: {
}while( zAffinity[0]);
}
- /* Loop through the elements that will make up the record to figure
- * out how much space is required for the new record.
- */
- nByte = sqlite3VdbeMsgpackRecordLen(pData0, nField);
-
- if (nByte>db->aLimit[SQLITE_LIMIT_LENGTH]) {
- goto too_big;
+ struct region *region = &fiber()->gc;
+ size_t used = region_used(region);
+ uint32_t tuple_size;
+ char *tuple =
+ sql_vdbe_mem_encode_tuple(pData0, nField, &tuple_size, region);
+ if (tuple == NULL) {
+ rc = SQL_TARANTOOL_ERROR;
+ goto abort_due_to_error;
}
+ if ((int64_t)tuple_size > db->aLimit[SQLITE_LIMIT_LENGTH])
+ goto too_big;
/* In case of ephemeral space, it is possible to save some memory
* allocating one by ordinary malloc: instead of cutting pieces
@@ -2915,21 +2918,25 @@ case OP_MakeRecord: {
* routine.
*/
if (bIsEphemeral) {
- rc = sqlite3VdbeMemClearAndResize(pOut, nByte);
+ rc = sqlite3VdbeMemClearAndResize(pOut, tuple_size);
pOut->flags = MEM_Blob;
+ pOut->n = tuple_size;
+ memcpy(pOut->z, tuple, tuple_size);
+ region_truncate(region, used);
} else {
/* Allocate memory on the region for the tuple
* to be passed to Tarantool. Before that, make
* sure previously allocated memory has gone.
*/
sqlite3VdbeMemRelease(pOut);
- rc = sql_vdbe_mem_alloc_region(pOut, nByte);
+ pOut->flags = MEM_Blob | MEM_Ephem;
+ pOut->n = tuple_size;
+ pOut->z = tuple;
}
if (rc)
goto no_mem;
- /* Write the record */
+ assert(sqlite3VdbeCheckMemInvariants(pOut));
assert(pOp->p3>0 && pOp->p3<=(p->nMem+1 - p->nCursor));
- pOut->n = sqlite3VdbeMsgpackRecordPut((u8 *)pOut->z, pData0, nField);
REGISTER_TRACE(pOp->p3, pOut);
UPDATE_MAX_BLOBSIZE(pOut);
break;
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index 50bc35b2b..fcb47455b 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -526,8 +526,6 @@ int sqlite3VdbeMemExpandBlob(Mem *);
#define ExpandBlob(P) SQLITE_OK
#endif
-i64 sqlite3VdbeMsgpackRecordLen(Mem * pMem, u32 n);
-u32 sqlite3VdbeMsgpackRecordPut(u8 * pBuf, Mem * pMem, u32 n);
/**
* Perform comparison of two keys: one is packed and one is not.
*
@@ -552,4 +550,28 @@ int sqlite3VdbeRecordCompareMsgpack(const void *key1,
struct UnpackedRecord *key2);
u32 sqlite3VdbeMsgpackGet(const unsigned char *buf, Mem * pMem);
+struct mpstream;
+struct region;
+
+/** Callback to forward and error from mpstream methods. */
+static inline void
+set_encode_error(void *error_ctx)
+{
+ *(bool *)error_ctx = true;
+}
+
+/**
+ * Perform encoding field_count Vdbe memory fields on region as
+ * msgpack array.
+ * @param fields The first Vdbe memory field to encode.
+ * @param field_count Count of fields to encode.
+ * @param[out] tuple_size Size of encoded tuple.
+ * @param region Region to use.
+ * @retval NULL on error, diag message is set.
+ * @retval no NULL tuple pointer on success.
+ */
+char *
+sql_vdbe_mem_encode_tuple(struct Mem *fields, uint32_t field_count,
+ uint32_t *tuple_size, struct region *region);
+
#endif /* !defined(SQLITE_VDBEINT_H) */
diff --git a/src/box/sql/vdbeaux.c b/src/box/sql/vdbeaux.c
index d477662a4..6ef8c308e 100644
--- a/src/box/sql/vdbeaux.c
+++ b/src/box/sql/vdbeaux.c
@@ -3532,77 +3532,6 @@ sqlite3VdbeSetVarmask(Vdbe * v, int iVar)
}
}
-i64
-sqlite3VdbeMsgpackRecordLen(Mem * pRec, u32 n)
-{
- i64 nByte = 5; /* largest array header */
- Mem *pEnd = pRec + n;
- assert(n != 0);
- do {
- assert(memIsValid(pRec));
- if (pRec->flags & (MEM_Null | MEM_Bool)) {
- nByte += 1;
- } else if (pRec->flags & (MEM_Int | MEM_Real)) {
- nByte += 9;
- } else {
- nByte += 5 + (u32) pRec->n;
- if (pRec->flags & MEM_Zero) {
- nByte += pRec->u.nZero;
- }
- }
- } while ((++pRec) != pEnd);
- return nByte;
-}
-
-u32
-sqlite3VdbeMsgpackRecordPut(u8 * pBuf, Mem * pRec, u32 n)
-{
- char *zNewRecord = mp_encode_array((char *)pBuf, n);
- Mem *pEnd = pRec + n;
- assert(n != 0);
- do {
- assert(memIsValid(pRec));
- if (pRec->flags & MEM_Null) {
- zNewRecord = mp_encode_nil(zNewRecord);
- } else if (pRec->flags & MEM_Real) {
- zNewRecord = mp_encode_double(zNewRecord, pRec->u.r);
- } else if (pRec->flags & MEM_Int) {
- if (pRec->u.i >= 0) {
- zNewRecord =
- mp_encode_uint(zNewRecord, pRec->u.i);
- } else {
- zNewRecord =
- mp_encode_int(zNewRecord, pRec->u.i);
- }
- } else if (pRec->flags & MEM_Str) {
- zNewRecord =
- mp_encode_str(zNewRecord, pRec->z, pRec->n);
- } else if (pRec->flags & MEM_Bool) {
- zNewRecord =
- mp_encode_bool(zNewRecord, pRec->u.b);
- } else {
- /* Emit BIN header iff the BLOB doesn't store MsgPack content */
- if ((pRec->flags & MEM_Subtype) == 0
- || pRec->subtype != SQL_SUBTYPE_MSGPACK) {
- zNewRecord =
- mp_encode_binl(zNewRecord,
- pRec->n +
- ((pRec->
- flags & MEM_Zero) ? pRec->
- u.nZero : 0)
- );
- }
- memcpy(zNewRecord, pRec->z, pRec->n);
- zNewRecord += pRec->n;
- if (pRec->flags & MEM_Zero) {
- memset(zNewRecord, 0, pRec->u.nZero);
- zNewRecord += pRec->u.nZero;
- }
- }
- } while ((++pRec) != pEnd);
- return (u32) (zNewRecord - (char *)pBuf);
-}
-
int
sqlite3VdbeCompareMsgpack(const char **key1,
struct UnpackedRecord *unpacked, int key2_idx)
diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
index 22beba8be..3f171766e 100644
--- a/src/box/sql/vdbemem.c
+++ b/src/box/sql/vdbemem.c
@@ -40,6 +40,8 @@
#include "vdbeInt.h"
#include "tarantoolInt.h"
#include "box/schema.h"
+#include "box/tuple.h"
+#include "mpstream.h"
#ifdef SQLITE_DEBUG
/*
@@ -1714,3 +1716,71 @@ sqlite3ValueBytes(sqlite3_value * pVal)
return 0;
return valueBytes(pVal);
}
+
+/**
+ * Perform encoding memory variable to stream.
+ * @param stream Initialized mpstream encoder object.
+ * @param var Vdbe memory variable to encode with stream.
+ */
+static void
+mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var)
+{
+ assert(memIsValid(var));
+ if (var->flags & MEM_Null) {
+ mpstream_encode_nil(stream);
+ } else if (var->flags & MEM_Real) {
+ mpstream_encode_double(stream, var->u.r);
+ } else if (var->flags & MEM_Int) {
+ if (var->u.i >= 0)
+ mpstream_encode_uint(stream, var->u.i);
+ else
+ mpstream_encode_int(stream, var->u.i);
+ } else if (var->flags & MEM_Str) {
+ mpstream_encode_strn(stream, var->z, var->n);
+ } else if (var->flags & MEM_Bool) {
+ mpstream_encode_bool(stream, var->u.b);
+ } else {
+ /*
+ * Emit BIN header iff the BLOB doesn't store
+ * MsgPack content.
+ */
+ if ((var->flags & MEM_Subtype) == 0 ||
+ var->subtype != SQL_SUBTYPE_MSGPACK) {
+ uint32_t binl = var->n +
+ ((var->flags & MEM_Zero) ?
+ var->u.nZero : 0);
+ mpstream_encode_binl(stream, binl);
+ }
+ mpstream_memcpy(stream, var->z, var->n);
+ if (var->flags & MEM_Zero)
+ mpstream_memset(stream, 0, var->u.nZero);
+ }
+}
+
+char *
+sql_vdbe_mem_encode_tuple(struct Mem *fields, uint32_t field_count,
+ uint32_t *tuple_size, struct region *region)
+{
+ size_t used = region_used(region);
+ bool is_error = false;
+ struct mpstream stream;
+ mpstream_init(&stream, region, region_reserve_cb, region_alloc_cb,
+ set_encode_error, &is_error);
+ mpstream_encode_array(&stream, field_count);
+ for (struct Mem *field = fields; field < fields + field_count; field++)
+ mpstream_encode_vdbe_mem(&stream, field);
+ mpstream_flush(&stream);
+ if (is_error) {
+ diag_set(OutOfMemory, stream.pos - stream.buf,
+ "mpstream_flush", "stream");
+ return NULL;
+ }
+ *tuple_size = region_used(region) - used;
+ char *tuple = region_join(region, *tuple_size);
+ if (tuple == NULL) {
+ diag_set(OutOfMemory, *tuple_size, "region_join", "tuple");
+ return NULL;
+ }
+ mp_tuple_assert(tuple, tuple + *tuple_size);
+ return tuple;
+}
diff --git a/src/mpstream.c b/src/mpstream.c
index e4f7950ba..8b7276ab1 100644
--- a/src/mpstream.c
+++ b/src/mpstream.c
@@ -175,3 +175,33 @@ mpstream_encode_bool(struct mpstream *stream, bool val)
char *pos = mp_encode_bool(data, val);
mpstream_advance(stream, pos - data);
}
+
+void
+mpstream_encode_binl(struct mpstream *stream, uint32_t len)
+{
+ char *data = mpstream_reserve(stream, mp_sizeof_binl(len));
+ if (data == NULL)
+ return;
+ char *pos = mp_encode_binl(data, len);
+ mpstream_advance(stream, pos - data);
+}
+
+void
+mpstream_memcpy(struct mpstream *stream, const void *src, uint32_t n)
+{
+ char *data = mpstream_reserve(stream, n);
+ if (data == NULL)
+ return;
+ memcpy(data, src, n);
+ mpstream_advance(stream, n);
+}
+
+void
+mpstream_memset(struct mpstream *stream, int c, uint32_t n)
+{
+ char *data = mpstream_reserve(stream, n);
+ if (data == NULL)
+ return;
+ memset(data, c, n);
+ mpstream_advance(stream, n);
+}
diff --git a/src/mpstream.h b/src/mpstream.h
index e22d05241..8f86e0d09 100644
--- a/src/mpstream.h
+++ b/src/mpstream.h
@@ -133,6 +133,17 @@ mpstream_encode_nil(struct mpstream *stream);
void
mpstream_encode_bool(struct mpstream *stream, bool val);
+void
+mpstream_encode_binl(struct mpstream *stream, uint32_t len);
+
+/** Copies n bytes from memory area src to stream. */
+void
+mpstream_memcpy(struct mpstream *stream, const void *src, uint32_t n);
+
+/** Fills n stream bytes with the constant byte c. */
+void
+mpstream_memset(struct mpstream *stream, int c, uint32_t n);
+
#if defined(__cplusplus)
} /* extern "C" */
#endif /* defined(__cplusplus) */
--
2.19.2
^ permalink raw reply [flat|nested] 13+ messages in thread
* [tarantool-patches] [PATCH v2 5/5] sql: do not use OP_Delete+OP_Insert for UPDATES
2018-12-29 10:48 [tarantool-patches] [PATCH v2 0/5] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov
` (3 preceding siblings ...)
2018-12-29 10:49 ` [tarantool-patches] [PATCH v2 4/5] sql: encode tuples with mpstream on Vdbe run Kirill Shcherbatov
@ 2018-12-29 10:49 ` Kirill Shcherbatov
2018-12-29 13:35 ` [tarantool-patches] " n.pettik
2019-01-10 12:30 ` [tarantool-patches] Re: [PATCH v2 0/5] " Kirill Yukhin
5 siblings, 1 reply; 13+ messages in thread
From: Kirill Shcherbatov @ 2018-12-29 10:49 UTC (permalink / raw)
To: tarantool-patches, korablev; +Cc: Kirill Shcherbatov
Introduced a new OP_Update opcode executing Tarantool native
Update operation.
In case of UPDATE or REPLACE we can't use new OP_Update as it
has a complex SQL-specific semantics:
CREATE TABLE tj (s1 INT PRIMARY KEY, s2 INT);
INSERT INTO tj VALUES (1, 3),(2, 4),(3,5);
CREATE UNIQUE INDEX i ON tj (s2);
SELECT * FROM tj;
[1, 3], [2, 4], [3, 5]
UPDATE OR REPLACE tj SET s2 = s2 + 1;
SELECT * FROM tj;
[1, 4], [3, 6]
I.e. [1, 3] tuple is updated as [1, 4] and have replaced tuple
[2, 4]. This logic is implemented as preventive tuples deletion
by all corresponding indexes in SQL.
The other significant change is forbidden primary key update.
It was possible to deal with it the same way like with or
REPLACE specifier but we need an atomic UPDATE step for #3691
ticket to support "or IGNORE/or ABORT/or FAIL" specifiers.
Reworked tests to make testing avoiding primary key UPDATE where
possible.
Closes #3850
---
src/box/sql/insert.c | 7 +-
src/box/sql/sqliteInt.h | 14 ++
src/box/sql/update.c | 88 +++++++---
src/box/sql/vdbe.c | 107 ++++++++++++
src/box/sql/vdbeInt.h | 8 +
src/box/sql/vdbemem.c | 7 +-
test/sql-tap/alter2.test.lua | 16 +-
test/sql-tap/analyze9.test.lua | 43 ++++-
test/sql-tap/bigrow1.test.lua | 12 +-
test/sql-tap/check.test.lua | 14 +-
test/sql-tap/fkey2.test.lua | 161 +++++++++---------
test/sql-tap/fkey3.test.lua | 20 +--
test/sql-tap/fkey4.test.lua | 32 ++--
test/sql-tap/gh2140-trans.test.lua | 26 +--
.../gh2250-trigger-chain-limit.test.lua | 8 +-
test/sql-tap/gh2259-in-stmt-trans.test.lua | 12 +-
test/sql-tap/identifier_case.test.lua | 2 +-
test/sql-tap/index6.test.lua | 5 +-
test/sql-tap/intpkey.test.lua | 23 ++-
test/sql-tap/misc1.test.lua | 20 ++-
test/sql-tap/quote.test.lua | 6 +-
test/sql-tap/table.test.lua | 2 +-
test/sql-tap/tkt-a8a0d2996a.test.lua | 4 +-
test/sql-tap/tkt2767.test.lua | 16 +-
test/sql-tap/tkt2832.test.lua | 10 +-
test/sql-tap/tkt3554.test.lua | 15 +-
test/sql-tap/trigger2.test.lua | 28 +--
test/sql-tap/trigger7.test.lua | 2 +-
test/sql-tap/triggerB.test.lua | 28 +--
test/sql-tap/triggerC.test.lua | 43 ++---
test/sql-tap/triggerD.test.lua | 4 +-
test/sql-tap/update.test.lua | 31 ++--
test/sql-tap/with1.test.lua | 24 +--
test/sql/collation.result | 12 +-
test/sql/collation.test.lua | 12 +-
test/sql/on-conflict.result | 8 +-
test/sql/on-conflict.test.lua | 8 +-
test/sql/row-count.result | 10 +-
test/sql/row-count.test.lua | 10 +-
test/sql/triggers.result | 16 +-
test/sql/triggers.test.lua | 16 +-
41 files changed, 580 insertions(+), 350 deletions(-)
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index 1b02ea907..f147f6a50 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -1059,12 +1059,7 @@ vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space,
{
assert(v != NULL);
u16 pik_flags = OPFLAG_NCHANGE;
- if (on_conflict == ON_CONFLICT_ACTION_IGNORE)
- pik_flags |= OPFLAG_OE_IGNORE;
- else if (on_conflict == ON_CONFLICT_ACTION_FAIL)
- pik_flags |= OPFLAG_OE_FAIL;
- else if (on_conflict == ON_CONFLICT_ACTION_ROLLBACK)
- pik_flags |= OPFLAG_OE_ROLLBACK;
+ SET_CONFLICT_FLAG(pik_flags, on_conflict);
sqlite3VdbeAddOp3(v, OP_MakeRecord, raw_data_reg, tuple_len,
raw_data_reg + tuple_len);
sqlite3VdbeAddOp1(v, OP_IdxInsert, raw_data_reg + tuple_len);
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 4110a5991..de0b6f05b 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -2847,6 +2847,20 @@ struct Parse {
#define OPFLAG_SYSTEMSP 0x20 /* OP_Open**: set if space pointer
* points to system space.
*/
+
+/**
+ * Prepare vdbe P5 flags for OP_{IdxInsert, IdxReplace, Update}
+ * by on_conflict action.
+ */
+#define SET_CONFLICT_FLAG(opflag, on_conflict) do { \
+ if (on_conflict == ON_CONFLICT_ACTION_IGNORE) \
+ opflag |= OPFLAG_OE_IGNORE; \
+ else if (on_conflict == ON_CONFLICT_ACTION_FAIL) \
+ opflag |= OPFLAG_OE_FAIL; \
+ else if (on_conflict == ON_CONFLICT_ACTION_ROLLBACK) \
+ opflag |= OPFLAG_OE_ROLLBACK; \
+} while (0)
+
/* OP_RowData: xferOptimization started processing */
#ifdef SQLITE_TEST
#define OPFLAG_XFER_OPT 0x01
diff --git a/src/box/sql/update.c b/src/box/sql/update.c
index 0e2d0fde8..dbef22fd8 100644
--- a/src/box/sql/update.c
+++ b/src/box/sql/update.c
@@ -36,6 +36,7 @@
#include "sqliteInt.h"
#include "box/session.h"
#include "tarantoolInt.h"
+#include "box/tuple_format.h"
#include "box/schema.h"
void
@@ -112,6 +113,8 @@ sqlite3Update(Parse * pParse, /* The parser context */
int regNew = 0; /* Content of the NEW.* table in triggers */
int regOld = 0; /* Content of OLD.* table in triggers */
int regKey = 0; /* composite PRIMARY KEY value */
+ /* Count of changed rows. Match aXRef items != -1. */
+ int upd_cols_cnt = 0;
db = pParse->db;
if (pParse->nErr || db->mallocFailed) {
@@ -183,6 +186,7 @@ sqlite3Update(Parse * pParse, /* The parser context */
goto update_cleanup;
}
aXRef[j] = i;
+ upd_cols_cnt++;
break;
}
}
@@ -216,7 +220,7 @@ sqlite3Update(Parse * pParse, /* The parser context */
regNewPk = ++pParse->nMem;
}
regNew = pParse->nMem + 1;
- pParse->nMem += def->field_count;
+ pParse->nMem += def->field_count + 1;
/* If we are trying to update a view, realize that view into
* an ephemeral table.
@@ -274,11 +278,11 @@ sqlite3Update(Parse * pParse, /* The parser context */
nKey = pk_part_count;
regKey = iPk;
} else {
- const char *zAff = is_view ? 0 :
- sql_space_index_affinity_str(pParse->db, def,
- pPk->def);
+ const char *aff_str =
+ is_view ? 0 :
+ sql_space_index_affinity_str(pParse->db, def, pPk->def);
sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, pk_part_count,
- regKey, zAff, pk_part_count);
+ regKey, aff_str, pk_part_count);
/*
* Set flag to save memory allocating one by
* malloc.
@@ -430,23 +434,67 @@ sqlite3Update(Parse * pParse, /* The parser context */
vdbe_emit_constraint_checks(pParse, pTab, regNewPk + 1,
on_error, labelContinue, aXRef);
/* Do FK constraint checks. */
- if (hasFK)
+ if (hasFK) {
fkey_emit_check(pParse, pTab, regOldPk, 0, aXRef);
- /*
- * Delete the index entries associated with the
- * current record. It can be already removed by
- * trigger or REPLACE conflict action.
- */
- int addr1 = sqlite3VdbeAddOp4Int(v, OP_NotFound, pk_cursor, 0,
- regKey, nKey);
- assert(regNew == regNewPk + 1);
- sqlite3VdbeAddOp2(v, OP_Delete, pk_cursor, 0);
- sqlite3VdbeJumpHere(v, addr1);
- if (hasFK)
+ }
+ if (on_error == ON_CONFLICT_ACTION_REPLACE) {
+ /*
+ * Delete the index entries associated with the
+ * current record. It can be already removed by
+ * trigger or REPLACE conflict action.
+ */
+ int not_found_lbl =
+ sqlite3VdbeAddOp4Int(v, OP_NotFound, pk_cursor,
+ 0, regKey, nKey);
+ assert(regNew == regNewPk + 1);
+ sqlite3VdbeAddOp2(v, OP_Delete, pk_cursor, 0);
+ sqlite3VdbeJumpHere(v, not_found_lbl);
+ }
+ if (hasFK) {
fkey_emit_check(pParse, pTab, 0, regNewPk, aXRef);
- vdbe_emit_insertion_completion(v, space, regNew,
- pTab->def->field_count,
- on_error);
+ }
+ if (on_error == ON_CONFLICT_ACTION_REPLACE) {
+ vdbe_emit_insertion_completion(v, space, regNew,
+ pTab->def->field_count,
+ on_error);
+
+ } else {
+ int key_reg;
+ if (okOnePass) {
+ key_reg = sqlite3GetTempReg(pParse);
+ const char *zAff =
+ sql_space_index_affinity_str(pParse->db,
+ def,
+ pPk->def);
+ sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk,
+ pk_part_count, key_reg, zAff,
+ pk_part_count);
+ } else {
+ assert(nKey == 0);
+ key_reg = regKey;
+ }
+
+ /* Prepare array of changed fields. */
+ uint32_t upd_cols_sz = upd_cols_cnt * sizeof(uint32_t);
+ uint32_t *upd_cols = sqlite3DbMallocRaw(db, upd_cols_sz);
+ if (upd_cols == NULL)
+ goto update_cleanup;
+ upd_cols_cnt = 0;
+ for (uint32_t i = 0; i < def->field_count; i++) {
+ if (aXRef[i] == -1)
+ continue;
+ upd_cols[upd_cols_cnt++] = i;
+ }
+ int upd_cols_reg = sqlite3GetTempReg(pParse);
+ sqlite3VdbeAddOp4(v, OP_Blob, upd_cols_sz, upd_cols_reg,
+ 0, (const char *)upd_cols, P4_DYNAMIC);
+ u16 pik_flags = OPFLAG_NCHANGE;
+ SET_CONFLICT_FLAG(pik_flags, on_error);
+ sqlite3VdbeAddOp4(v, OP_Update, regNew, key_reg,
+ upd_cols_reg, (char *)space,
+ P4_SPACEPTR);
+ sqlite3VdbeChangeP5(v, pik_flags);
+ }
/*
* Do any ON CASCADE, SET NULL or SET DEFAULT
* operations required to handle rows that refer
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index b8faa8f45..5bbcd58d1 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -621,6 +621,13 @@ vdbe_add_new_autoinc_id(struct Vdbe *vdbe, int64_t id)
return 0;
}
+/** Callback to forward and error from mpstream methods. */
+static inline void
+mpstream_encode_error(void *error_ctx)
+{
+ *(bool *)error_ctx = true;
+}
+
/*
* Execute as much of a VDBE program as we can.
* This is the core of sqlite3_step().
@@ -4464,6 +4471,106 @@ case OP_IdxInsert: {
break;
}
+/* Opcode: Update P1 P2 P3 P4 P5
+ * Synopsis: key=r[P1]
+ *
+ * Process UPDATE operation. Primary key fields can not be
+ * modified.
+ * Under the hood it performs box_update() call.
+ * For the performance sake, it takes whole affected row (P1)
+ * and encodes into msgpack only fields to be updated (P3).
+ *
+ * @param P1 The first field to be updated. Fields are located
+ * in the range of [P1...] in decoded state.
+ * Encoded only fields which numbers are presented
+ * in @P3 array.
+ * @param P2 P2 Encoded key to be passed to box_update().
+ * @param P3 Index of a register with upd_fields blob.
+ * It's items are numbers of fields to be replaced with
+ * new values from P1. They must be sorted in ascending
+ * order.
+ * @param P4 Pointer to the struct space to be updated.
+ * @param P5 Flags. If P5 contains OPFLAG_NCHANGE, then VDBE
+ * accounts the change in a case of successful
+ * insertion in nChange counter. If P5 contains
+ * OPFLAG_OE_IGNORE, then we are processing INSERT OR
+ * INGORE statement. Thus, in case of conflict we don't
+ * raise an error.
+ */
+case OP_Update: {
+ struct Mem *new_tuple = &aMem[pOp->p1];
+ if (pOp->p5 & OPFLAG_NCHANGE)
+ p->nChange++;
+
+ struct space *space = pOp->p4.space;
+ assert(pOp->p4type == P4_SPACEPTR);
+
+ struct Mem *key_mem = &aMem[pOp->p2];
+ assert((key_mem->flags & MEM_Blob) != 0);
+
+ struct Mem *upd_fields_mem = &aMem[pOp->p3];
+ assert((upd_fields_mem->flags & MEM_Blob) != 0);
+ uint32_t *upd_fields = (uint32_t *)upd_fields_mem->z;
+ uint32_t upd_fields_cnt = upd_fields_mem->n / sizeof(uint32_t);
+
+ /* Prepare Tarantool update ops msgpack. */
+ struct region *region = &fiber()->gc;
+ size_t used = region_used(region);
+ bool is_error = false;
+ struct mpstream stream;
+ mpstream_init(&stream, region, region_reserve_cb, region_alloc_cb,
+ mpstream_encode_error, &is_error);
+ mpstream_encode_array(&stream, upd_fields_cnt);
+ for (uint32_t i = 0; i < upd_fields_cnt; i++) {
+ uint32_t field_idx = upd_fields[i];
+ assert(field_idx < space->def->field_count);
+ mpstream_encode_array(&stream, 3);
+ mpstream_encode_strn(&stream, "=", 1);
+ mpstream_encode_uint(&stream, field_idx);
+ mpstream_encode_vdbe_mem(&stream, new_tuple + field_idx);
+ }
+ mpstream_flush(&stream);
+ if (is_error) {
+ diag_set(OutOfMemory, stream.pos - stream.buf,
+ "mpstream_flush", "stream");
+ rc = SQL_TARANTOOL_ERROR;
+ goto abort_due_to_error;
+ }
+ uint32_t ops_size = region_used(region) - used;
+ const char *ops = region_join(region, ops_size);
+ if (ops == NULL) {
+ diag_set(OutOfMemory, ops_size, "region_join", "raw");
+ rc = SQL_TARANTOOL_ERROR;
+ goto abort_due_to_error;
+ }
+
+ assert(rc == SQLITE_OK);
+ if (box_update(space->def->id, 0, key_mem->z, key_mem->z + key_mem->n,
+ ops, ops + ops_size, 0, NULL) != 0)
+ rc = SQL_TARANTOOL_ERROR;
+
+ if (pOp->p5 & OPFLAG_OE_IGNORE) {
+ /*
+ * Ignore any kind of fails and do not raise
+ * error message
+ */
+ rc = SQLITE_OK;
+ /*
+ * If we are in trigger, increment ignore raised
+ * counter.
+ */
+ if (p->pFrame)
+ p->ignoreRaised++;
+ } else if (pOp->p5 & OPFLAG_OE_FAIL) {
+ p->errorAction = ON_CONFLICT_ACTION_FAIL;
+ } else if (pOp->p5 & OPFLAG_OE_ROLLBACK) {
+ p->errorAction = ON_CONFLICT_ACTION_ROLLBACK;
+ }
+ if (rc != 0)
+ goto abort_due_to_error;
+ break;
+}
+
/* Opcode: SInsert P1 P2 P3 * P5
* Synopsis: space id = P1, key = r[P3], on error goto P2
*
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index fcb47455b..66362c465 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -560,6 +560,14 @@ set_encode_error(void *error_ctx)
*(bool *)error_ctx = true;
}
+/**
+ * Perform encoding memory variable to stream.
+ * @param stream Initialized mpstream encoder object.
+ * @param var Vdbe memory variable to encode with stream.
+ */
+void
+mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var);
+
/**
* Perform encoding field_count Vdbe memory fields on region as
* msgpack array.
diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
index 3f171766e..7b21f2aaa 100644
--- a/src/box/sql/vdbemem.c
+++ b/src/box/sql/vdbemem.c
@@ -1717,12 +1717,7 @@ sqlite3ValueBytes(sqlite3_value * pVal)
return valueBytes(pVal);
}
-/**
- * Perform encoding memory variable to stream.
- * @param stream Initialized mpstream encoder object.
- * @param var Vdbe memory variable to encode with stream.
- */
-static void
+void
mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var)
{
assert(memIsValid(var));
diff --git a/test/sql-tap/alter2.test.lua b/test/sql-tap/alter2.test.lua
index d13cfb7a0..e1dd0ff1b 100755
--- a/test/sql-tap/alter2.test.lua
+++ b/test/sql-tap/alter2.test.lua
@@ -204,13 +204,13 @@ test:do_execsql_test(
[[
DROP TABLE child;
DROP TABLE parent;
- CREATE TABLE child (id INT PRIMARY KEY, a INT, b INT);
- CREATE TABLE parent (id INT PRIMARY KEY, c INT, d INT);
- ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES parent ON UPDATE CASCADE MATCH PARTIAL;
- INSERT INTO parent VALUES(1, 2, 3), (3, 4, 5), (6, 7, 8);
- INSERT INTO child VALUES(1, 1, 1), (3, 2, 2);
+ CREATE TABLE child (id INT UNIQUE, a INT, b INT, z INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE parent (id INT UNIQUE, c INT, d INT, z INT PRIMARY KEY AUTOINCREMENT);
+ ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES parent(id) ON UPDATE CASCADE MATCH PARTIAL;
+ INSERT INTO parent(id, c, d) VALUES(1, 2, 3), (3, 4, 5), (6, 7, 8);
+ INSERT INTO child(id, a, b) VALUES(1, 1, 1), (3, 2, 2);
UPDATE parent SET id = 5 WHERE id = 1;
- SELECT * FROM CHILD;
+ SELECT id,a,b FROM CHILD ORDER BY id,a,b;
]], {
-- <alter2-3.2>
3, 2, 2, 5, 1, 1
@@ -220,7 +220,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"alter2-4.1",
[[
- ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY REFERENCES child;
+ ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY REFERENCES child(id);
]], {
-- <alter2-4.1>
1, "near \"REFERENCES\": syntax error"
@@ -230,7 +230,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"alter2-4.2",
[[
- ALTER TABLE child ADD CONSTRAINT fk () FOREIGN KEY REFERENCES child;
+ ALTER TABLE child ADD CONSTRAINT fk () FOREIGN KEY REFERENCES child(id);
]], {
-- <alter2-4.1>
1, "near \"(\": syntax error"
diff --git a/test/sql-tap/analyze9.test.lua b/test/sql-tap/analyze9.test.lua
index 585e91851..df62a1624 100755
--- a/test/sql-tap/analyze9.test.lua
+++ b/test/sql-tap/analyze9.test.lua
@@ -405,6 +405,27 @@ test:do_execsql_test(
-- The following tests experiment with adding corrupted records to the
-- 'sample' column of the _sql_stat4 table.
--
+local get_pk = function (space, record)
+ local pkey = {}
+ for _, part in pairs(space.index[0].parts) do
+ table.insert(pkey, record[part.fieldno])
+ end
+ return pkey
+end
+
+local inject_stat_error_func = function (space_name)
+ local space = box.space[space_name]
+ local record = space:select({"T1", "I1", nil}, {limit = 1})[1]
+ space:delete(get_pk(space, record))
+ local record_new = {}
+ for i = 1,#record-1 do record_new[i] = record[i] end
+ record_new[#record] = ''
+ space:insert(record_new)
+ return 0
+end
+
+box.internal.sql_create_function("inject_stat_error", "INT", inject_stat_error_func)
+
test:do_execsql_test(
7.1,
[[
@@ -417,8 +438,7 @@ test:do_execsql_test(
INSERT INTO t1 VALUES(null, 4, 4);
INSERT INTO t1 VALUES(null, 5, 5);
ANALYZE;
- UPDATE "_sql_stat4" SET "sample" = '' WHERE "sample" =
- (SELECT "sample" FROM "_sql_stat4" WHERE "tbl" = 't1' AND "idx" = 'i1' LIMIT 1);
+ SELECT inject_stat_error('_sql_stat4');
ANALYZE;
]])
@@ -1050,11 +1070,28 @@ test:do_execsql_test(
-- </15.4>
})
+local inject_stat_error_func = function (space_name)
+ local space = box.space[space_name]
+ local stats = space:select()
+ for _, stat in pairs(stats) do
+ space:delete(get_pk(space, stat))
+ local new_tuple = {"no such tbl"}
+ for i=2,#stat do
+ table.insert(new_tuple, stat[i])
+ end
+ space:insert(new_tuple)
+ end
+ return 0
+end
+
+box.internal.sql_create_function("inject_stat_error", "INT", inject_stat_error_func)
+
+
test:do_execsql_test(
15.7,
[[
ANALYZE;
- UPDATE "_sql_stat1" SET "tbl" = 'no such tbl';
+ SELECT inject_stat_error('_sql_stat1');
]])
test:do_execsql_test(
diff --git a/test/sql-tap/bigrow1.test.lua b/test/sql-tap/bigrow1.test.lua
index 43c0d0b67..e69ddf136 100755
--- a/test/sql-tap/bigrow1.test.lua
+++ b/test/sql-tap/bigrow1.test.lua
@@ -40,7 +40,7 @@ test:do_test(
test:do_execsql_test(
"bigrow-1.1",
[[
- CREATE TABLE t1(a text primary key, b text, c text);
+ CREATE TABLE t1(id INT PRIMARY KEY AUTOINCREMENT, a TEXT UNIQUE, b TEXT, c TEXT);
--SELECT name FROM sqlite_master
-- WHERE type='table' OR type='index'
-- ORDER BY name
@@ -55,7 +55,7 @@ test:do_test(
"bigrow-1.2",
function()
big1 = string.sub(bigstr, 1, 65519 + 1)
- local sql = "INSERT INTO t1 VALUES('abc',"
+ local sql = "INSERT INTO t1(a,b,c) VALUES('abc',"
sql = sql .. "'"..big1.."', 'xyz');"
test:execsql(sql)
return test:execsql "SELECT a, c FROM t1"
@@ -80,7 +80,7 @@ test:do_test(
"bigrow-1.4",
function()
big2 = string.sub(bigstr, 1, 65520+1)
- local sql = "INSERT INTO t1 VALUES('abc2',"
+ local sql = "INSERT INTO t1(a,b,c) VALUES('abc2',"
sql = sql .. "'"..big2.."', 'xyz2');"
return test:catchsql(sql)
end, {
@@ -134,7 +134,7 @@ test:do_execsql_test(
test:do_execsql_test(
"bigrow-1.6",
[[
- SELECT * FROM t1
+ SELECT a,b,c FROM t1
]], {
-- <bigrow-1.6>
big1, "abc", "xyz"
@@ -144,8 +144,8 @@ test:do_execsql_test(
test:do_execsql_test(
"bigrow-1.7",
[[
- INSERT INTO t1 VALUES('1','2','3');
- INSERT INTO t1 VALUES('A','B','C');
+ INSERT INTO t1(a,b,c) VALUES('1','2','3');
+ INSERT INTO t1(a,b,c) VALUES('A','B','C');
SELECT b FROM t1 WHERE a=='1';
]], {
-- <bigrow-1.7>
diff --git a/test/sql-tap/check.test.lua b/test/sql-tap/check.test.lua
index c24ae2ff1..1f369fb02 100755
--- a/test/sql-tap/check.test.lua
+++ b/test/sql-tap/check.test.lua
@@ -420,7 +420,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"check-4.1",
[[
- CREATE TABLE t4(x INT primary key, y INT ,
+ CREATE TABLE t4(x INT UNIQUE, y INT, z INT PRIMARY KEY
CHECK (
x+y==11
OR x*y==12
@@ -437,8 +437,8 @@ test:do_execsql_test(
test:do_execsql_test(
"check-4.2",
[[
- INSERT INTO t4 VALUES(1,10);
- SELECT * FROM t4
+ INSERT INTO t4 VALUES(1,10,1);
+ SELECT x,y FROM t4
]], {
-- <check-4.2>
1, 10
@@ -449,7 +449,7 @@ test:do_execsql_test(
"check-4.3",
[[
UPDATE t4 SET x=4, y=3;
- SELECT * FROM t4
+ SELECT x,y FROM t4
]], {
-- <check-4.3>
4, 3
@@ -460,7 +460,7 @@ test:do_execsql_test(
"check-4.4",
[[
UPDATE t4 SET x=12, y=2;
- SELECT * FROM t4
+ SELECT x,y FROM t4
]], {
-- <check-4.4>
12, 2
@@ -471,7 +471,7 @@ test:do_execsql_test(
"check-4.5",
[[
UPDATE t4 SET x=12, y=-22;
- SELECT * FROM t4
+ SELECT x,y FROM t4
]], {
-- <check-4.5>
12, -22
@@ -491,7 +491,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"check-4.7",
[[
- SELECT * FROM t4;
+ SELECT x,y FROM t4;
]], {
-- <check-4.7>
12, -22
diff --git a/test/sql-tap/fkey2.test.lua b/test/sql-tap/fkey2.test.lua
index 61db29f32..03bf025f3 100755
--- a/test/sql-tap/fkey2.test.lua
+++ b/test/sql-tap/fkey2.test.lua
@@ -7,15 +7,14 @@ test:plan(116)
test:do_execsql_test(
"fkey2-1.1",
[[
- CREATE TABLE t1(a INT PRIMARY KEY, b INT );
- CREATE TABLE t2(c INT PRIMARY KEY REFERENCES t1(a), d INT);
+ CREATE TABLE t1(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE t2(c INT UNIQUE REFERENCES t1(a), d INT, id INT PRIMARY KEY AUTOINCREMENT);
- CREATE TABLE t3(a INT PRIMARY KEY, b INT );
- CREATE TABLE t4(c INT PRIMARY KEY REFERENCES t3, d INT );
-
- CREATE TABLE t7(a INT , b INTEGER PRIMARY KEY);
- CREATE TABLE t8(c INT PRIMARY KEY REFERENCES t7, d INT );
+ CREATE TABLE t3(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE t4(c INT UNIQUE REFERENCES t3(a), d INT, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE t7(a INT, b INTEGER UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE t8(c INT UNIQUE REFERENCES t7(b), d INT, id INT PRIMARY KEY AUTOINCREMENT);
]], {
-- <fkey2-1.1>
-- </fkey2-1.1>
@@ -24,7 +23,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"fkey2-1.2",
[[
- INSERT INTO t2 VALUES(1, 3);
+ INSERT INTO t2(c,d) VALUES(1, 3);
]], {
-- <fkey2-1.2>
1, "FOREIGN KEY constraint failed"
@@ -34,7 +33,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.3",
[[
- INSERT INTO t1 VALUES(1, 2);
+ INSERT INTO t1(a, b) VALUES(1, 2);
]], {
-- <fkey2-1.3>
0
@@ -44,7 +43,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.4",
[[
- INSERT INTO t2 VALUES(1, 3);
+ INSERT INTO t2(c,d) VALUES(1, 3);
]], {
-- <fkey2-1.4>
0
@@ -54,7 +53,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.5",
[[
- INSERT INTO t2 VALUES(2, 4);
+ INSERT INTO t2(c,d) VALUES(2, 4);
]], {
-- <fkey2-1.5>
1, "FOREIGN KEY constraint failed"
@@ -64,8 +63,8 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.6",
[[
- INSERT INTO t1 VALUES(3, 5);
- INSERT INTO t2 VALUES(3, 4);
+ INSERT INTO t1(a,b) VALUES(3, 5);
+ INSERT INTO t2(c,d) VALUES(3, 4);
]], {
-- <fkey2-1.6>
0
@@ -85,7 +84,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.8",
[[
- INSERT INTO t1 VALUES(6, 7);
+ INSERT INTO t1(a,b) VALUES(6, 7);
UPDATE t2 SET c = 6 WHERE d = 4;
]], {
-- <fkey2-1.8>
@@ -129,7 +128,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.12",
[[
- INSERT INTO t4 values (1,3);
+ INSERT INTO t4(c,d) values (1,3);
]], {
-- <fkey2-1.12>
1, "FOREIGN KEY constraint failed"
@@ -139,7 +138,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.13",
[[
- INSERT INTO t3 values (1,2);
+ INSERT INTO t3(a,b) values (1,2);
]], {
-- <fkey2-1.13>
0
@@ -149,7 +148,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.14",
[[
- INSERT INTO t4 values (1,3);
+ INSERT INTO t4(c,d) values (1,3);
]], {
-- <fkey2-1.14>
0
@@ -159,7 +158,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.15",
[[
- INSERT INTO t8 values (1,3);
+ INSERT INTO t8(c,d) values (1,3);
]], {
-- <fkey2-1.15>
1, "FOREIGN KEY constraint failed"
@@ -169,7 +168,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.16",
[[
- INSERT INTO t7 values (2,1);
+ INSERT INTO t7(a,b) values (2,1);
]], {
-- <fkey2-1.16>
0
@@ -179,7 +178,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.17",
[[
- INSERT INTO t8 values (1,3);
+ INSERT INTO t8(c,d) values (1,3);
]], {
-- <fkey2-1.17>
0
@@ -189,7 +188,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.18",
[[
- INSERT INTO t8 values (2,4);
+ INSERT INTO t8(c,d) values (2,4);
]], {
-- <fkey2-1.18>
1, "FOREIGN KEY constraint failed"
@@ -199,7 +198,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.19",
[[
- INSERT INTO t8 values (6,4);
+ INSERT INTO t8(c,d) values (6,4);
]], {
-- <fkey2-1.19>
1,"FOREIGN KEY constraint failed"
@@ -269,7 +268,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.26",
[[
- INSERT INTO t8 VALUES(666, 54644);
+ INSERT INTO t8(c,d) VALUES(666, 54644);
]], {
-- <fkey2-1.26>
1, "FOREIGN KEY constraint failed"
@@ -341,17 +340,19 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-3.1",
[[
- CREATE TABLE ab(a INT PRIMARY KEY, b TEXT);
+ CREATE TABLE ab(id INT PRIMARY KEY AUTOINCREMENT, a INT UNIQUE, b TEXT);
CREATE TABLE cd(
- c INT PRIMARY KEY REFERENCES ab ON UPDATE CASCADE ON DELETE CASCADE,
+ id INT PRIMARY KEY AUTOINCREMENT,
+ c INT UNIQUE REFERENCES ab(a) ON UPDATE CASCADE ON DELETE CASCADE,
d TEXT);
CREATE TABLE ef(
- e INT PRIMARY KEY REFERENCES cd ON UPDATE CASCADE,
+ id INT PRIMARY KEY AUTOINCREMENT,
+ e INT UNIQUE REFERENCES cd(c) ON UPDATE CASCADE,
f TEXT , CHECK (e!=5));
- INSERT INTO ab VALUES(1, 'b');
- INSERT INTO cd VALUES(1, 'd');
- INSERT INTO ef VALUES(1, 'e');
+ INSERT INTO ab(a,b) VALUES(1, 'b');
+ INSERT INTO cd(c,d) VALUES(1, 'd');
+ INSERT INTO ef(e,f) VALUES(1, 'e');
]], {
})
@@ -368,7 +369,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-3.3",
[[
- SELECT * FROM ab;
+ SELECT a,b FROM ab;
]], {
-- <fkey2-3.3>
1, "b"
@@ -388,7 +389,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-3.5",
[[
- SELECT * FROM ef;
+ SELECT e,f FROM ef;
]], {
-- <fkey2-3.5>
1, "e"
@@ -410,7 +411,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-3.7",
[[
- SELECT * FROM ab;
+ SELECT a,b FROM ab;
]], {
-- <fkey2-3.7>
1, "b"
@@ -424,20 +425,21 @@ test:do_execsql_test(
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
- node INT PRIMARY KEY,
- parent INT REFERENCES t1 ON DELETE CASCADE);
+ id INT PRIMARY KEY AUTOINCREMENT,
+ node INT UNIQUE NOT NULL,
+ parent INT REFERENCES t1(node) ON DELETE CASCADE);
CREATE TABLE t2(node INT PRIMARY KEY, parent INT );
CREATE TRIGGER t2t AFTER DELETE ON t2 BEGIN
DELETE FROM t2 WHERE parent = old.node;
END;
- INSERT INTO t1 VALUES(1, NULL);
- INSERT INTO t1 VALUES(2, 1);
- INSERT INTO t1 VALUES(3, 1);
- INSERT INTO t1 VALUES(4, 2);
- INSERT INTO t1 VALUES(5, 2);
- INSERT INTO t1 VALUES(6, 3);
- INSERT INTO t1 VALUES(7, 3);
- INSERT INTO t2 SELECT * FROM t1;
+ INSERT INTO t1(node, parent) VALUES(1, NULL);
+ INSERT INTO t1(node, parent) VALUES(2, 1);
+ INSERT INTO t1(node, parent) VALUES(3, 1);
+ INSERT INTO t1(node, parent) VALUES(4, 2);
+ INSERT INTO t1(node, parent) VALUES(5, 2);
+ INSERT INTO t1(node, parent) VALUES(6, 3);
+ INSERT INTO t1(node, parent) VALUES(7, 3);
+ INSERT INTO t2(node, parent) SELECT node,parent FROM t1;
]], {
-- <fkey2-4.1>
-- </fkey2-4.1>
@@ -525,8 +527,8 @@ test:do_execsql_test(
[[
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
- CREATE TABLE t1(a INT PRIMARY KEY, b INT );
- CREATE TABLE t2(c INTEGER PRIMARY KEY REFERENCES t1, b TEXT);
+ CREATE TABLE t1(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE t2(id INT PRIMARY KEY AUTOINCREMENT, c INTEGER REFERENCES t1(a), b TEXT);
]], {
-- <fkey2-5.1>
-- </fkey2-5.1>
@@ -535,7 +537,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"fkey2-5.2",
[[
- INSERT INTO t2 VALUES(1, 'A');
+ INSERT INTO t2(c,b) VALUES(1, 'A');
]], {
-- <fkey2-5.2>
1, "FOREIGN KEY constraint failed"
@@ -545,9 +547,9 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-5.3",
[[
- INSERT INTO t1 VALUES(1, 2);
- INSERT INTO t1 VALUES(2, 3);
- INSERT INTO t2 VALUES(1, 'A');
+ INSERT INTO t1(a,b) VALUES(1, 2);
+ INSERT INTO t1(a,b) VALUES(2, 3);
+ INSERT INTO t2(c,b) VALUES(1, 'A');
UPDATE t2 SET c = 2;
]], {
-- <fkey2-5.3>
@@ -668,12 +670,11 @@ test:do_execsql_test(
ON DELETE SET NULL);
INSERT INTO pp VALUES(1, 2, 3);
INSERT INTO pp VALUES(4, 5, 6);
- INSERT INTO pp VALUES(7, 8, 9);
+ INSERT INTO pp VALUES(7, 1, 9);
INSERT INTO cc VALUES(6, 'A', 5, 1);
INSERT INTO cc VALUES(6, 'B', 5, 2);
- INSERT INTO cc VALUES(9, 'A', 8, 3);
- INSERT INTO cc VALUES(9, 'B', 8, 4);
- UPDATE pp SET b = 1 WHERE a = 7;
+ INSERT INTO cc VALUES(3, 'A', 2, 3);
+ INSERT INTO cc VALUES(3, 'B', 2, 4);
SELECT * FROM cc;
]], {
-- <fkey2-6.6>
@@ -766,13 +767,13 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-8.1",
[[
- CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);
- CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);
+ CREATE TABLE t1(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE t2(id INT PRIMARY KEY AUTOINCREMENT, c INT UNIQUE, d INT, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);
- INSERT INTO t1 VALUES(10, 100);
- INSERT INTO t2 VALUES(10, 100);
+ INSERT INTO t1(a,b) VALUES(10, 100);
+ INSERT INTO t2(c,d) VALUES(10, 100);
UPDATE t1 SET a = 15;
- SELECT * FROM t2;
+ SELECT c,d FROM t2;
]], {
-- <fkey2-8.1>
15, 100
@@ -788,12 +789,12 @@ test:do_execsql_test(
[[
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1;
- CREATE TABLE t1(a INT , b TEXT PRIMARY KEY);
+ CREATE TABLE t1(a INT, b TEXT UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);
CREATE TABLE t2(
- x TEXT PRIMARY KEY REFERENCES t1 ON UPDATE RESTRICT);
- INSERT INTO t1 VALUES(1, 'one');
- INSERT INTO t1 VALUES(2, 'two');
- INSERT INTO t1 VALUES(3, 'three');
+ x TEXT PRIMARY KEY REFERENCES t1(b) ON UPDATE RESTRICT);
+ INSERT INTO t1(a,b) VALUES(1, 'four');
+ INSERT INTO t1(a,b) VALUES(2, 'two');
+ INSERT INTO t1(a,b) VALUES(3, 'three');
INSERT INTO t2 VALUES('two');
UPDATE t1 SET b = 'four' WHERE b = 'one';
]], {
@@ -916,14 +917,14 @@ test:do_execsql_test(
c10 TEXT , c11 TEXT , c12 TEXT , c13 TEXT , c14 TEXT , c15 TEXT , c16 TEXT , c17 TEXT , c18 TEXT , c19 TEXT ,
c20 TEXT , c21 TEXT , c22 TEXT , c23 TEXT , c24 TEXT , c25 TEXT , c26 TEXT , c27 TEXT , c28 TEXT , c29 TEXT ,
c30 TEXT , c31 TEXT , c32 TEXT , c33 TEXT , c34 TEXT , c35 TEXT , c36 TEXT , c37 TEXT , c38 TEXT , c39 TEXT ,
- PRIMARY KEY(c34, c35));
+ UNIQUE(c34, c35), id INT PRIMARY KEY AUTOINCREMENT);
CREATE TABLE down(
c00 TEXT , c01 TEXT , c02 TEXT , c03 TEXT , c04 TEXT , c05 TEXT , c06 TEXT , c07 TEXT , c08 TEXT , c09 TEXT ,
c10 TEXT , c11 TEXT , c12 TEXT , c13 TEXT , c14 TEXT , c15 TEXT , c16 TEXT , c17 TEXT , c18 TEXT , c19 TEXT ,
c20 TEXT , c21 TEXT , c22 TEXT , c23 TEXT , c24 TEXT , c25 TEXT , c26 TEXT , c27 TEXT , c28 TEXT , c29 TEXT ,
c30 TEXT , c31 TEXT , c32 TEXT , c33 TEXT , c34 TEXT , c35 TEXT , c36 TEXT , c37 TEXT , c38 TEXT , c39 TEXT ,
- PRIMARY KEY(c39, c38),
- FOREIGN KEY(c39, c38) REFERENCES up ON UPDATE CASCADE);
+ UNIQUE(c39, c38),
+ FOREIGN KEY(c39, c38) REFERENCES up(c34,c35) ON UPDATE CASCADE, id INT PRIMARY KEY AUTOINCREMENT);
INSERT INTO up(c34, c35) VALUES('yes', 'no');
INSERT INTO down(c39, c38) VALUES('yes', 'no');
UPDATE up SET c34 = 'possibly';
@@ -1168,8 +1169,8 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-11.1",
[[
- CREATE TABLE self(a INT PRIMARY KEY, b INT REFERENCES self(a));
- INSERT INTO self VALUES(13, 13);
+ CREATE TABLE self(a INT UNIQUE, b INT REFERENCES self(a), id INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO self(a,b) VALUES(13, 13);
UPDATE self SET a = 14, b = 14;
]], {
-- <fkey2-11.1>
@@ -1227,7 +1228,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"fkey2-11.7",
[[
- INSERT INTO self VALUES(20, 21);
+ INSERT INTO self(a,b) VALUES(20, 21);
]], {
-- <fkey2-11.7>
1, "FOREIGN KEY constraint failed"
@@ -1238,8 +1239,8 @@ test:do_execsql_test(
"fkey2-11.8",
[[
DROP TABLE IF EXISTS self;
- CREATE TABLE self(a INT UNIQUE, b INT PRIMARY KEY REFERENCES self(a));
- INSERT INTO self VALUES(13, 13);
+ CREATE TABLE self(a INT UNIQUE, b INT UNIQUE REFERENCES self(a), id INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO self(a,b) VALUES(13, 13);
UPDATE self SET a = 14, b = 14;
]], {
-- <fkey2-11.8>
@@ -1297,7 +1298,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"fkey2-11.14",
[[
- INSERT INTO self VALUES(20, 21);
+ INSERT INTO self(a,b) VALUES(20, 21);
]], {
-- <fkey2-11.14>
1, "FOREIGN KEY constraint failed"
@@ -1310,12 +1311,12 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-12.1",
[[
- CREATE TABLE tdd08(a INT PRIMARY KEY, b INT);
+ CREATE TABLE tdd08(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
CREATE UNIQUE INDEX idd08 ON tdd08(a,b);
- INSERT INTO tdd08 VALUES(200,300);
+ INSERT INTO tdd08(a,b) VALUES(200,300);
- CREATE TABLE tdd08_b(w INT PRIMARY KEY,x INT ,y INT , FOREIGN KEY(x,y) REFERENCES tdd08(a,b));
- INSERT INTO tdd08_b VALUES(100,200,300);
+ CREATE TABLE tdd08_b(w INT UNIQUE, x INT ,y INT , FOREIGN KEY(x,y) REFERENCES tdd08(a,b), id INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO tdd08_b(w,x,y) VALUES(100,200,300);
]], {
-- <fkey2-12.1>
-- </fkey2-12.1>
@@ -1334,7 +1335,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-12.3",
[[
- SELECT * FROM tdd08;
+ SELECT a,b FROM tdd08;
]], {
-- <fkey2-12.3>
200, 300
@@ -1344,7 +1345,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"fkey2-12.4",
[[
- INSERT INTO tdd08_b VALUES(400,500,300);
+ INSERT INTO tdd08_b(w,x,y) VALUES(400,500,300);
]], {
-- <fkey2-12.4>
1, "FOREIGN KEY constraint failed"
@@ -1374,16 +1375,16 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-13.1",
[[
- CREATE TABLE tce71(a INT PRIMARY KEY, b INT);
+ CREATE TABLE tce71(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
CREATE UNIQUE INDEX ice71 ON tce71(a,b);
- INSERT INTO tce71 VALUES(100,200);
+ INSERT INTO tce71(a,b) VALUES(100,200);
CREATE TABLE tce72(w INT PRIMARY KEY, x INT , y INT , FOREIGN KEY(x,y) REFERENCES tce71(a,b));
INSERT INTO tce72 VALUES(300,100,200);
UPDATE tce71 set b = 200 where a = 100;
SELECT * FROM tce71, tce72;
]], {
-- <fkey2-13.1>
- 100, 200, 300, 100, 200
+ 100, 200, 1, 300, 100, 200
-- </fkey2-13.1>
})
diff --git a/test/sql-tap/fkey3.test.lua b/test/sql-tap/fkey3.test.lua
index 9bd1aef23..011402da6 100755
--- a/test/sql-tap/fkey3.test.lua
+++ b/test/sql-tap/fkey3.test.lua
@@ -158,12 +158,12 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey3-3.6",
[[
- CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT, c INT, d TEXT, UNIQUE(a, b),
- FOREIGN KEY(c, d) REFERENCES t6(a, b));
- INSERT INTO t6 VALUES(1, 'a', 1, 'a');
- INSERT INTO t6 VALUES(2, 'a', 2, 'a');
- INSERT INTO t6 VALUES(3, 'a', 1, 'a');
- INSERT INTO t6 VALUES(5, 'a', 2, 'a');
+ CREATE TABLE t6(a INT UNIQUE, b TEXT, c INT, d TEXT, UNIQUE(a, b),
+ FOREIGN KEY(c, d) REFERENCES t6(a, b), id INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO t6(a,b,c,d) VALUES(1, 'a', 1, 'a');
+ INSERT INTO t6(a,b,c,d) VALUES(2, 'a', 2, 'a');
+ INSERT INTO t6(a,b,c,d) VALUES(3, 'a', 1, 'a');
+ INSERT INTO t6(a,b,c,d) VALUES(5, 'a', 2, 'a');
]], {
-- <fkey3-3.6>
-- </fkey3-3.6>
@@ -172,7 +172,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"fkey3-3.7",
[[
- INSERT INTO t6 VALUES(4, 'a', 65, 'a');
+ INSERT INTO t6(a,b,c,d) VALUES(4, 'a', 65, 'a');
]], {
-- <fkey3-3.7>
1, "FOREIGN KEY constraint failed"
@@ -182,9 +182,9 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey3-3.8",
[[
- INSERT INTO t6 VALUES(100, 'one', 100, 'one');
+ INSERT INTO t6(a,b,c,d) VALUES(100, 'one', 100, 'one');
DELETE FROM t6 WHERE a = 100;
- SELECT * FROM t6 WHERE a = 100;
+ SELECT a,b,c,d FROM t6 WHERE a = 100;
]], {
-- <fkey3-3.8>
-- </fkey3-3.8>
@@ -193,7 +193,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"fkey3-3.9",
[[
- INSERT INTO t6 VALUES(100, 'one', 100, 'one');
+ INSERT INTO t6(a,b,c,d) VALUES(100, 'one', 100, 'one');
UPDATE t6 SET c = 1, d = 'a' WHERE a = 100;
]], {
-- <fkey3-3.9>
diff --git a/test/sql-tap/fkey4.test.lua b/test/sql-tap/fkey4.test.lua
index a1b3b1f41..13fd308ee 100755
--- a/test/sql-tap/fkey4.test.lua
+++ b/test/sql-tap/fkey4.test.lua
@@ -8,9 +8,9 @@ test:do_execsql_test(
"fkey8-1.1",
[[
CREATE TABLE p1(a INT PRIMARY KEY);
- CREATE TABLE c1(b INT PRIMARY KEY REFERENCES p1 ON DELETE CASCADE);
+ CREATE TABLE c1(id INT PRIMARY KEY AUTOINCREMENT, b INT UNIQUE REFERENCES p1 ON DELETE CASCADE);
INSERT INTO p1 VALUES (1), (2), (3);
- INSERT INTO c1 VALUES (2);
+ INSERT INTO c1(b) VALUES (2);
DELETE FROM p1 WHERE a = 2;
SELECT * FROM c1;
]], {
@@ -24,9 +24,9 @@ test:do_catchsql_test(
DROP TABLE IF EXISTS c1;
DROP TABLE IF EXISTS p1;
CREATE TABLE p1(a INT PRIMARY KEY);
- CREATE TABLE c1(b INT PRIMARY KEY REFERENCES p1 ON DELETE SET NULL);
+ CREATE TABLE c1(id INT PRIMARY KEY AUTOINCREMENT, b INT UNIQUE NOT NULL REFERENCES p1 ON DELETE SET NULL);
INSERT INTO p1 VALUES (1), (2), (3);
- INSERT INTO c1 VALUES (2);
+ INSERT INTO c1(b) VALUES (2);
DELETE FROM p1 WHERE a = 2;
]], {
-- <fkey8-1.2>
@@ -40,11 +40,11 @@ test:do_execsql_test(
DROP TABLE IF EXISTS c1;
DROP TABLE IF EXISTS p1;
CREATE TABLE p1(a INT PRIMARY KEY);
- CREATE TABLE c1(b INT PRIMARY KEY DEFAULT 3 REFERENCES p1 ON DELETE SET DEFAULT);
+ CREATE TABLE c1(id INT PRIMARY KEY AUTOINCREMENT, b INT UNIQUE DEFAULT 3 REFERENCES p1 ON DELETE SET DEFAULT);
INSERT INTO p1 VALUES (1), (2), (3);
- INSERT INTO c1 VALUES (2);
+ INSERT INTO c1(b) VALUES (2);
DELETE FROM p1 WHERE a = 2;
- SELECT * FROM c1;
+ SELECT b FROM c1;
]], {
-- <fkey8-1.3>
3
@@ -152,10 +152,10 @@ test:do_catchsql_test(
DROP TABLE IF EXISTS cc1;
DROP TABLE IF EXISTS c1;
DROP TABLE IF EXISTS p1;
- CREATE TABLE p1(a INT PRIMARY KEY);
- CREATE TABLE c1(b INT PRIMARY KEY REFERENCES p1 ON UPDATE SET NULL, c INT);
- INSERT INTO p1 VALUES (1), (2), (3);
- INSERT INTO c1 VALUES (2, 1), (3, 2);
+ CREATE TABLE p1(a INT UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE c1(b INT UNIQUE NOT NULL REFERENCES p1(a) ON UPDATE SET NULL, c INT, id INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO p1(a) VALUES (1), (2), (3);
+ INSERT INTO c1(b, c) VALUES (2, 1), (3, 2);
UPDATE OR IGNORE p1 SET a = 4 WHERE a = 2;
]], {
-- <fkey8-1.9>
@@ -168,12 +168,12 @@ test:do_execsql_test(
[[
DROP TABLE IF EXISTS c1;
DROP TABLE IF EXISTS p1;
- CREATE TABLE p1(a INT PRIMARY KEY);
- CREATE TABLE c1(b INT PRIMARY KEY REFERENCES p1 ON UPDATE CASCADE, c INT);
- INSERT INTO p1 VALUES (1), (2), (3);
- INSERT INTO c1 VALUES (2, 1), (3, 2);
+ CREATE TABLE p1(a INT UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE c1(b INT UNIQUE NOT NULL REFERENCES p1(a) ON UPDATE CASCADE, c INT, id INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO p1(a) VALUES (1), (2), (3);
+ INSERT INTO c1(b,c) VALUES (2, 1), (3, 2);
UPDATE OR IGNORE p1 SET a = 4 WHERE a = 2;
- SELECT * FROM c1;
+ SELECT b,c FROM c1 ORDER BY b,c;
]], {
-- <fkey8-1.10>
3, 2, 4, 1
diff --git a/test/sql-tap/gh2140-trans.test.lua b/test/sql-tap/gh2140-trans.test.lua
index 3c6f9042f..fe978d1a9 100755
--- a/test/sql-tap/gh2140-trans.test.lua
+++ b/test/sql-tap/gh2140-trans.test.lua
@@ -5,42 +5,42 @@ test:plan(10)
box.sql.execute("DROP TABLE IF EXISTS t1")
box.sql.execute("DROP TABLE IF EXISTS t2")
-box.sql.execute("CREATE TABLE t1 (s1 int primary key, s2 int);")
-box.sql.execute("CREATE TABLE t2 (s1 int primary key, s2 int);")
+box.sql.execute("CREATE TABLE t1 (s0 INT PRIMARY KEY, s1 INT UNIQUE, s2 INT);")
+box.sql.execute("CREATE TABLE t2 (s0 INT PRIMARY KEY, s1 INT UNIQUE, s2 INT);")
-box.sql.execute("INSERT INTO t1 VALUES (1,1);")
-box.sql.execute("INSERT INTO t2 VALUES (1,1);")
+box.sql.execute("INSERT INTO t1 VALUES (1,1,1);")
+box.sql.execute("INSERT INTO t2 VALUES (1,1,1);")
test:do_execsql_test('commit1_check',
[[START TRANSACTION;
- INSERT INTO t1 VALUES (2,2);
+ INSERT INTO t1 VALUES (2,2,2);
COMMIT;
- SELECT * FROM t1]],
+ SELECT s1,s2 FROM t1]],
{1, 1, 2, 2})
test:do_execsql_test('rollback1_check',
[[START TRANSACTION;
- INSERT INTO t1 VALUES (3,3);
+ INSERT INTO t1 VALUES (3,3,3);
ROLLBACK;
- SELECT * FROM t1]],
+ SELECT s1,s2 FROM t1]],
{1, 1, 2, 2})
for _, verb in ipairs({'ROLLBACK', 'ABORT'}) do
box.sql.execute('DELETE FROM t2')
- answer = "Duplicate key exists in unique index 'pk_unnamed_T1_1' in space 'T1'"
+ answer = "Duplicate key exists in unique index 'unique_unnamed_T1_2' in space 'T1'"
test:do_catchsql_test('insert1_'..verb,
[[START TRANSACTION;
- INSERT INTO t2 VALUES (2, 2);
- INSERT OR ]]..verb..[[ INTO t1 VALUES (1,1);
+ INSERT INTO t2 VALUES (20, 2, 2);
+ INSERT OR ]]..verb..[[ INTO t1 VALUES (10,1,1);
]],
{1, answer})
local expect = {}
if verb == 'ABORT' then
box.sql.execute('COMMIT')
- expect = {2, 2}
+ expect = {20, 2, 2}
end
test:do_execsql_test('insert1_'..verb..'_check',
'SELECT * FROM t2', expect)
@@ -48,7 +48,7 @@ for _, verb in ipairs({'ROLLBACK', 'ABORT'}) do
box.sql.execute('DELETE FROM t2')
test:do_catchsql_test('update1_'..verb,
[[START TRANSACTION;
- INSERT INTO t2 VALUES (2, 2);
+ INSERT INTO t2 VALUES (20, 2, 2);
UPDATE OR ]]..verb..[[ t1 SET s1 = 1 WHERE s1 = 2;
]],
{1, answer})
diff --git a/test/sql-tap/gh2250-trigger-chain-limit.test.lua b/test/sql-tap/gh2250-trigger-chain-limit.test.lua
index bfbb26097..e38f286e6 100755
--- a/test/sql-tap/gh2250-trigger-chain-limit.test.lua
+++ b/test/sql-tap/gh2250-trigger-chain-limit.test.lua
@@ -9,20 +9,20 @@ for _, table_count in ipairs({30, 31}) do
drop_string = 'DROP TABLE IF EXISTS t' .. i .. ';'
box.sql.execute(drop_string)
- create_string = 'CREATE TABLE t' .. i .. ' (s1 int primary key, s2 int);'
+ create_string = 'CREATE TABLE t' .. i .. ' (s1 INT UNIQUE, s2 INT, s3 INT PRIMARY KEY);'
box.sql.execute(create_string)
- insert_string = 'INSERT INTO t' .. i .. ' VALUES (0,' .. i .. ');'
+ insert_string = 'INSERT INTO t' .. i .. ' VALUES (0,' .. i .. ', 0);'
box.sql.execute(insert_string)
-- Second table for triggers mixture check
drop_string = 'DROP TABLE IF EXISTS tt' .. i .. ';'
box.sql.execute(drop_string)
- create_string = 'CREATE TABLE tt' .. i .. ' (s1 int primary key, s2 int);'
+ create_string = 'CREATE TABLE tt' .. i .. ' (s1 INT UNIQUE, s2 INT, s3 INT PRIMARY KEY);'
box.sql.execute(create_string)
- insert_string = 'INSERT INTO tt' .. i .. ' VALUES (0,' .. i .. ');'
+ insert_string = 'INSERT INTO tt' .. i .. ' VALUES (0,' .. i .. ', 0);'
box.sql.execute(insert_string)
end
diff --git a/test/sql-tap/gh2259-in-stmt-trans.test.lua b/test/sql-tap/gh2259-in-stmt-trans.test.lua
index d1ced19ec..95e03985f 100755
--- a/test/sql-tap/gh2259-in-stmt-trans.test.lua
+++ b/test/sql-tap/gh2259-in-stmt-trans.test.lua
@@ -5,8 +5,8 @@ test:plan(20)
box.sql.execute("DROP TABLE IF EXISTS t1")
box.sql.execute("DROP TABLE IF EXISTS t2")
-box.sql.execute("CREATE TABLE t1 (s1 int primary key, s2 int);")
-box.sql.execute("CREATE TABLE t2 (s1 int primary key, s2 int);")
+box.sql.execute("CREATE TABLE t1 (s1 INT UNIQUE, s2 INT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE t2 (s1 INT UNIQUE, s2 INT PRIMARY KEY);")
box.sql.execute("INSERT INTO t2 VALUES (1,1);")
box.sql.execute("INSERT INTO t1 VALUES (3,3);")
@@ -18,7 +18,7 @@ for _, prefix in pairs({"BEFORE", "AFTER"}) do
test:do_catchsql_test(prefix..'_insert1',
'INSERT INTO t1 VALUES(1, 2)',
- {1,"Duplicate key exists in unique index 'pk_unnamed_T2_1' in space 'T2'"})
+ {1,"Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
test:do_execsql_test(prefix..'_insert1_check1',
'SELECT * FROM t1',
@@ -34,7 +34,7 @@ for _, prefix in pairs({"BEFORE", "AFTER"}) do
test:do_catchsql_test(prefix..'_update1',
'UPDATE t1 SET s1=1',
- {1,"Duplicate key exists in unique index 'pk_unnamed_T2_1' in space 'T2'"})
+ {1,"Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
test:do_execsql_test(prefix..'_update1_check1',
'SELECT * FROM t1',
@@ -52,7 +52,7 @@ for _, prefix in pairs({"BEFORE", "AFTER"}) do
test:do_catchsql_test(prefix..'delete1',
'DELETE FROM t1;',
- {1, "Duplicate key exists in unique index 'pk_unnamed_T2_1' in space 'T2'"})
+ {1, "Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
-- Nothing should be inserted due to abort
test:do_execsql_test('delete1_check1',
@@ -69,7 +69,7 @@ end
-- Check multi-insert
test:do_catchsql_test('insert2',
'INSERT INTO t1 VALUES (5, 6), (6, 7)',
- {1, "Duplicate key exists in unique index 'pk_unnamed_T2_1' in space 'T2'"})
+ {1, "Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
test:do_execsql_test('insert2_check',
'SELECT * FROM t1;',
{3, 3})
diff --git a/test/sql-tap/identifier_case.test.lua b/test/sql-tap/identifier_case.test.lua
index 1d248f9dc..f26399eb6 100755
--- a/test/sql-tap/identifier_case.test.lua
+++ b/test/sql-tap/identifier_case.test.lua
@@ -116,7 +116,7 @@ test:do_test(
end,
6)
-test:execsql([[create table table1(columnn INT , "columnn" INT primary key)]])
+test:execsql([[create table table1(pk INT PRIMARY KEY AUTOINCREMENT, columnn INT , "columnn" INT UNIQUE)]])
test:execsql([[insert into table1("columnn", "COLUMNN") values(2,1)]])
diff --git a/test/sql-tap/index6.test.lua b/test/sql-tap/index6.test.lua
index 05385efe6..d39da43e8 100755
--- a/test/sql-tap/index6.test.lua
+++ b/test/sql-tap/index6.test.lua
@@ -227,7 +227,8 @@ test:do_execsql_test(
-- </index6-6.0>
})
else
- test:execsql("CREATE TABLE t6(a INT ,b INT , PRIMARY KEY (a,b));")
+ test:execsql("CREATE TABLE t6(id INT PRIMARY KEY AUTOINCREMENT, a INT ,b INT);")
+ test:execsql("CREATE UNIQUE INDEX t6i1 ON t6(a, b);")
test:execsql("INSERT INTO t6(a,b) VALUES(123,456);")
end
@@ -235,7 +236,7 @@ test:do_execsql_test(
"index6-6.1",
[[
UPDATE OR REPLACE t6 SET b=789;
- SELECT * FROM t6;
+ SELECT a,b FROM t6;
]], {
-- <index6-6.1>
123, 789
diff --git a/test/sql-tap/intpkey.test.lua b/test/sql-tap/intpkey.test.lua
index 132d9a37f..45c9f2c6e 100755
--- a/test/sql-tap/intpkey.test.lua
+++ b/test/sql-tap/intpkey.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(39)
+test:plan(40)
--!./tcltestrunner.lua
-- 2001 September 15
@@ -149,7 +149,8 @@ test:do_execsql_test(
test:do_execsql_test(
"intpkey-1.11",
[[
- UPDATE t1 SET a=4 WHERE b='one';
+ DELETE FROM t1 WHERE a = 7;
+ INSERT INTO t1 VALUES(4,'one','two');
SELECT * FROM t1;
]], {
-- <intpkey-1.11>
@@ -239,6 +240,18 @@ test:do_execsql_test(
-- </intpkey-1.16>
})
+-- Direct update of PK is forbidden
+--
+test:do_catchsql_test(
+ "intpkey-1.17",
+ [[
+ CREATE TABLE test(id INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO test VALUES (1);
+ UPDATE test SET id = 2;
+ ]], {
+ 1, "Attempt to modify a tuple field which is part of index 'pk_unnamed_TEST_1' in space 'TEST'"
+ })
+
--### INDICES
-- Check to make sure indices work correctly with integer primary keys
--
@@ -281,7 +294,8 @@ test:do_execsql_test(
test:do_execsql_test(
"intpkey-2.2",
[[
- UPDATE t1 SET a=8 WHERE b=='y';
+ DELETE FROM t1 WHERE b=='y';
+ INSERT INTO t1 VALUES(8,'y','z');
SELECT * FROM t1 WHERE b=='y';
]], {
-- <intpkey-2.2>
@@ -335,7 +349,8 @@ test:do_execsql_test(
"intpkey-2.7",
[[
--UPDATE t1 SET a=-4 WHERE rowid=8;
- UPDATE t1 SET a=-4 WHERE a=8;
+ DELETE FROM t1 WHERE a==8;
+ INSERT INTO t1 VALUES(-4,'y','z');
SELECT * FROM t1 WHERE b>'a';
]], {
-- <intpkey-2.7>
diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua
index 0bf680fe6..a533a7c07 100755
--- a/test/sql-tap/misc1.test.lua
+++ b/test/sql-tap/misc1.test.lua
@@ -237,16 +237,19 @@ test:do_test(
test:do_execsql_test(
"misc1-4.1",
[[
+ CREATE TABLE temp(id INT PRIMARY KEY, a TEXT);
CREATE TABLE t2(a TEXT primary key);
+
START TRANSACTION;
- INSERT INTO t2 VALUES('This is a long string to use up a lot of disk -');
- UPDATE t2 SET a=a||a||a||a;
- INSERT INTO t2 SELECT '1 - ' || a FROM t2;
- INSERT INTO t2 SELECT '2 - ' || a FROM t2;
- INSERT INTO t2 SELECT '3 - ' || a FROM t2;
- INSERT INTO t2 SELECT '4 - ' || a FROM t2;
- INSERT INTO t2 SELECT '5 - ' || a FROM t2;
- INSERT INTO t2 SELECT '6 - ' || a FROM t2;
+ INSERT INTO temp VALUES(0, 'This is a long string to use up a lot of disk -');
+ UPDATE temp SET a=a||a||a||a;
+ INSERT INTO t2 (a) SELECT a FROM temp;
+ INSERT INTO t2 (a) SELECT '1 - ' || a FROM t2;
+ INSERT INTO t2 (a) SELECT '2 - ' || a FROM t2;
+ INSERT INTO t2 (a) SELECT '3 - ' || a FROM t2;
+ INSERT INTO t2 (a) SELECT '4 - ' || a FROM t2;
+ INSERT INTO t2 (a) SELECT '5 - ' || a FROM t2;
+ INSERT INTO t2 (a) SELECT '6 - ' || a FROM t2;
COMMIT;
SELECT count(*) FROM t2;
]], {
@@ -429,6 +432,7 @@ test:execsql([[
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t4;
+ DROP TABLE temp;
]])
-- 64-bit integers are represented exactly.
--
diff --git a/test/sql-tap/quote.test.lua b/test/sql-tap/quote.test.lua
index 3f0bf865d..0a3b89daa 100755
--- a/test/sql-tap/quote.test.lua
+++ b/test/sql-tap/quote.test.lua
@@ -27,7 +27,7 @@ test:do_catchsql_test(
"quote-1.0",
[[
--- CREATE TABLE '@abc' ( '#xyz' int PRIMARY KEY, '!pqr' text );
- CREATE TABLE "abc5_" ( "#xyz" int PRIMARY KEY, "!pqr" text );
+ CREATE TABLE "abc5_" (id INT PRIMARY KEY, "#xyz" INT UNIQUE, "!pqr" TEXT );
]], {
-- <quote-1.0>
0
@@ -39,7 +39,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"quote-1.1",
[[
- INSERT INTO "abc5_" VALUES(5,'hello')
+ INSERT INTO "abc5_" VALUES(1, 5,'hello')
]], {
-- <quote-1.1>
0
@@ -52,7 +52,7 @@ test:do_catchsql_test(
SELECT * FROM "abc5_"
]], {
-- <quote-1.2.1>
- 0, {5, "hello"}
+ 0, {1, 5, "hello"}
-- </quote-1.2.1>
})
diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua
index 71645e2e2..7057f6b0f 100755
--- a/test/sql-tap/table.test.lua
+++ b/test/sql-tap/table.test.lua
@@ -528,7 +528,7 @@ test:do_execsql2_test(
test:do_execsql_test(
"table-7.3",
[[
- CREATE TABLE savepoint_t(release_t int primary key);
+ CREATE TABLE savepoint_t(id INT PRIMARY KEY AUTOINCREMENT, release_t INT UNIQUE);
INSERT INTO savepoint_t(release_t) VALUES(10);
UPDATE savepoint_t SET release_t = 5;
SELECT release_t FROM savepoint_t;
diff --git a/test/sql-tap/tkt-a8a0d2996a.test.lua b/test/sql-tap/tkt-a8a0d2996a.test.lua
index aefc2be0c..cc8827bc2 100755
--- a/test/sql-tap/tkt-a8a0d2996a.test.lua
+++ b/test/sql-tap/tkt-a8a0d2996a.test.lua
@@ -23,8 +23,8 @@ testprefix = "tkt-a8a0d2996a"
test:do_execsql_test(
1.0,
[[
- CREATE TABLE t(x TEXT primary key,y TEXT);
- INSERT INTO t VALUES('1','1');
+ CREATE TABLE t(id INT PRIMARY KEY, x TEXT UNIQUE, y TEXT);
+ INSERT INTO t VALUES(1, '1','1');
SELECT typeof(x), typeof(y) FROM t WHERE 1=x+0 AND y=='1';
]], {
-- <1.0>
diff --git a/test/sql-tap/tkt2767.test.lua b/test/sql-tap/tkt2767.test.lua
index 066c82100..b69b90be1 100755
--- a/test/sql-tap/tkt2767.test.lua
+++ b/test/sql-tap/tkt2767.test.lua
@@ -31,13 +31,13 @@ if (1 > 0)
"tkt2767-1.1",
[[
-- Construct a table with many rows of data
- CREATE TABLE t1(x INT primary key);
- INSERT INTO t1 VALUES(1);
- INSERT INTO t1 VALUES(2);
- INSERT INTO t1 SELECT x+2 FROM t1;
- INSERT INTO t1 SELECT x+4 FROM t1;
- INSERT INTO t1 SELECT x+8 FROM t1;
- INSERT INTO t1 SELECT x+16 FROM t1;
+ CREATE TABLE t1(id INT PRIMARY KEY AUTOINCREMENT, x INT UNIQUE);
+ INSERT INTO t1 VALUES(1, 1);
+ INSERT INTO t1 VALUES(2, 2);
+ INSERT INTO t1 (x) SELECT x+2 FROM t1;
+ INSERT INTO t1 (x) SELECT x+4 FROM t1;
+ INSERT INTO t1 (x) SELECT x+8 FROM t1;
+ INSERT INTO t1 (x) SELECT x+16 FROM t1;
-- BEFORE triggers that invoke raise(ignore). The effect of
-- these triggers should be to make INSERTs, UPDATEs, and DELETEs
@@ -99,7 +99,7 @@ if (1 > 0)
test:do_execsql_test(
"tkt2767-1.4",
[[
- INSERT INTO t1 SELECT x+32 FROM t1;
+ INSERT INTO t1 (x) SELECT x+32 FROM t1;
SELECT count(*), sum(x) FROM t1;
]], {
-- <tkt2767-1.4>
diff --git a/test/sql-tap/tkt2832.test.lua b/test/sql-tap/tkt2832.test.lua
index 108c05cdb..d3f24b586 100755
--- a/test/sql-tap/tkt2832.test.lua
+++ b/test/sql-tap/tkt2832.test.lua
@@ -25,10 +25,10 @@ test:plan(6)
test:do_execsql_test(
"tkt2832-1.1",
[[
- CREATE TABLE t1(a INT PRIMARY KEY);
- INSERT INTO t1 VALUES(2);
- INSERT INTO t1 VALUES(1);
- INSERT INTO t1 VALUES(3);
+ CREATE TABLE t1(id INT PRIMARY KEY AUTOINCREMENT, a INT UNIQUE);
+ INSERT INTO t1(a) VALUES(2);
+ INSERT INTO t1(a) VALUES(1);
+ INSERT INTO t1(a) VALUES(3);
]], {
-- <tkt2832-1.1>
@@ -40,7 +40,7 @@ test:do_execsql_test(
"tkt2832-1.2",
[[
UPDATE OR REPLACE t1 SET a = 1;
- SELECT * FROM t1;
+ SELECT a FROM t1;
]], {
-- <tkt2832-1.2>
1
diff --git a/test/sql-tap/tkt3554.test.lua b/test/sql-tap/tkt3554.test.lua
index ed194107f..0fcf350cb 100755
--- a/test/sql-tap/tkt3554.test.lua
+++ b/test/sql-tap/tkt3554.test.lua
@@ -26,7 +26,8 @@ test:plan(4)
test:do_execsql_test(
"tkt3544-1.1",
[[
- CREATE TABLE test ( obj TEXT, t1 INT , t2 INT , PRIMARY KEY(obj, t1, t2) );
+ CREATE TABLE test (id INT PRIMARY KEY AUTOINCREMENT, obj TEXT, t1 INT , t2 INT);
+ CREATE UNIQUE INDEX testi1 ON test(obj, t1, t2);
CREATE TRIGGER test_insert BEFORE INSERT ON test BEGIN
UPDATE test SET t1 = new.t1
@@ -49,8 +50,8 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3544-1.2",
[[
- INSERT INTO test VALUES('a', 10000, 11000);
- SELECT * FROM test;
+ INSERT INTO test(obj, t1, t2) VALUES('a', 10000, 11000);
+ SELECT obj, t1, t2 FROM test;
]], {
-- <tkt3544-1.2>
"a", 10000, 11000
@@ -61,9 +62,9 @@ test:do_test(
"tkt3544-1.3",
function()
test:execsql [[
- INSERT INTO test VALUES('a', 9000, 10500);
+ INSERT INTO test(obj, t1, t2) VALUES('a', 9000, 10500);
]]
- return test:execsql " SELECT * FROM test "
+ return test:execsql " SELECT obj, t1, t2 FROM test "
end, {
-- <tkt3544-1.3>
"a", 9000, 11000
@@ -74,9 +75,9 @@ test:do_test(
"tkt3544-1.4",
function()
test:execsql [[
- INSERT INTO test VALUES('a', 10000, 12000);
+ INSERT INTO test(obj, t1, t2) VALUES('a', 10000, 12000);
]]
- return test:execsql " SELECT * FROM test "
+ return test:execsql " SELECT obj, t1, t2 FROM test "
end, {
-- <tkt3544-1.4>
"a", 9000, 12000
diff --git a/test/sql-tap/trigger2.test.lua b/test/sql-tap/trigger2.test.lua
index 5d84c312a..b9b6dae74 100755
--- a/test/sql-tap/trigger2.test.lua
+++ b/test/sql-tap/trigger2.test.lua
@@ -61,10 +61,10 @@ test:plan(26)
test:catchsql " pragma recursive_triggers = off "
-- 1.
ii = 0
-tbl_definitions = { "CREATE TABLE tbl (a INTEGER PRIMARY KEY, b INT );",
- "CREATE TABLE tbl (a INT PRIMARY KEY, b INT );",
- "CREATE TABLE tbl (a INT , b INT PRIMARY KEY);",
- "CREATE TABLE tbl (a INT , b INTEGER PRIMARY KEY);" }
+tbl_definitions = { "CREATE TABLE tbl (id INT PRIMARY KEY AUTOINCREMENT, a INTEGER UNIQUE, b INT );",
+ "CREATE TABLE tbl (id INT PRIMARY KEY AUTOINCREMENT, a INT UNIQUE, b INT );",
+ "CREATE TABLE tbl (id INT PRIMARY KEY AUTOINCREMENT, a INT, b INT UNIQUE );",
+ "CREATE TABLE tbl (id INT PRIMARY KEY AUTOINCREMENT, a INT, b INTEGER UNIQUE );"}
-- Tarantool: temporary tables are not supported so far. #2119
-- table.insert(tbl_definitions,"CREATE TEMP TABLE tbl (a, b INTEGER PRIMARY KEY);")
-- table.insert(tbl_definitions,"CREATE TEMP TABLE tbl (a INTEGER PRIMARY KEY, b);")
@@ -81,8 +81,8 @@ for _, tbl_defn in ipairs(tbl_definitions) do
]]
test:execsql(tbl_defn)
test:execsql [[
- INSERT INTO tbl VALUES(1, 2);
- INSERT INTO tbl VALUES(3, 4);
+ INSERT INTO tbl(a, b) VALUES(1, 2);
+ INSERT INTO tbl(a, b) VALUES(3, 4);
]]
test:execsql [[
CREATE TABLE rlog (idx INTEGER PRIMARY KEY, old_a INT , old_b INT , db_sum_a INT , db_sum_b INT , new_a INT , new_b INT );
@@ -140,8 +140,8 @@ for _, tbl_defn in ipairs(tbl_definitions) do
test:execsql [[
DELETE FROM tbl;
DELETE FROM rlog;
- INSERT INTO tbl VALUES (100, 100);
- INSERT INTO tbl VALUES (300, 200);
+ INSERT INTO tbl(a, b) VALUES (100, 100);
+ INSERT INTO tbl(a, b) VALUES (300, 200);
CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
BEGIN
INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
@@ -203,7 +203,7 @@ for _, tbl_defn in ipairs(tbl_definitions) do
INSERT INTO other_tbl VALUES(1, 2);
INSERT INTO other_tbl VALUES(3, 4);
-- INSERT INTO tbl SELECT * FROM other_tbl;
- INSERT INTO tbl VALUES(5, 6);
+ INSERT INTO tbl(a,b) VALUES(5, 6);
DROP TABLE other_tbl;
SELECT * FROM rlog;
@@ -355,8 +355,8 @@ table.insert(when_triggers,"t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM t
test:execsql [[
CREATE TABLE tbl (a INT , b INT PRIMARY KEY, c INT , d INT );
- CREATE TABLE log (a INT PRIMARY KEY);
- INSERT INTO log VALUES (0);
+ CREATE TABLE log (id INT PRIMARY KEY AUTOINCREMENT, a INT);
+ INSERT INTO log VALUES (0, 0);
]]
for _, trig in ipairs(when_triggers) do
test:execsql("CREATE TRIGGER "..trig.." BEGIN UPDATE log set a = a + 1; END;")
@@ -368,17 +368,17 @@ test:do_test(
local r = {}
table.insert(r, test:execsql([[
INSERT INTO tbl VALUES(0, 1, 0, 0); -- 1 (ifcapable subquery)
- SELECT * FROM log;
+ SELECT a FROM log;
UPDATE log SET a = 0;]])[1])
table.insert(r, test:execsql([[
INSERT INTO tbl VALUES(0, 2, 0, 0); -- 0
- SELECT * FROM log;
+ SELECT a FROM log;
UPDATE log SET a = 0;]])[1])
table.insert(r, test:execsql([[
INSERT INTO tbl VALUES(200, 3, 0, 0); -- 1
- SELECT * FROM log;
+ SELECT a FROM log;
UPDATE log SET a = 0;]])[1])
return r
diff --git a/test/sql-tap/trigger7.test.lua b/test/sql-tap/trigger7.test.lua
index 21ff2af4d..21b015024 100755
--- a/test/sql-tap/trigger7.test.lua
+++ b/test/sql-tap/trigger7.test.lua
@@ -40,7 +40,7 @@ test:do_test(
"trigger7-2.1",
function()
test:execsql [[
- CREATE TABLE t1(x INT PRIMARY KEY, y INT);
+ CREATE TABLE t1(x INT UNIQUE, y INT, z INT PRIMARY KEY AUTOINCREMENT);
CREATE TRIGGER r1 AFTER UPDATE OF x ON t1 BEGIN
SELECT '___update_t1.x___';
END;
diff --git a/test/sql-tap/triggerB.test.lua b/test/sql-tap/triggerB.test.lua
index 455c3cb3e..414a4d098 100755
--- a/test/sql-tap/triggerB.test.lua
+++ b/test/sql-tap/triggerB.test.lua
@@ -25,9 +25,9 @@ test:plan(201)
test:do_execsql_test(
"triggerB-1.1",
[[
- CREATE TABLE x(x INTEGER PRIMARY KEY, y INT NOT NULL);
- INSERT INTO x VALUES(1, 1);
- INSERT INTO x VALUES(2, 1);
+ CREATE TABLE x(id INTEGER PRIMARY KEY, x INTEGER UNIQUE, y INT NOT NULL);
+ INSERT INTO x VALUES(1, 1, 1);
+ INSERT INTO x VALUES(2, 2, 1);
CREATE VIEW vx AS SELECT x, y, 0 AS yy FROM x;
CREATE TRIGGER tx INSTEAD OF UPDATE OF y ON vx
BEGIN
@@ -57,7 +57,7 @@ test:do_catchsql_test(
CREATE TRIGGER ty AFTER INSERT ON x BEGIN
SELECT wen.x; -- Unrecognized name
END;
- INSERT INTO x VALUES(1,2);
+ INSERT INTO x VALUES(3,1,2);
]], {
-- <triggerB-2.1>
1, "no such column: WEN.X"
@@ -81,8 +81,8 @@ test:do_test(
"triggerB-2.3",
function()
test:execsql [[
- CREATE TABLE t2(a INTEGER PRIMARY KEY, b INT );
- INSERT INTO t2 VALUES(1,2);
+ CREATE TABLE t2(id INTEGER PRIMARY KEY, a INTEGER UNIQUE, b INT );
+ INSERT INTO t2 VALUES(1, 1,2);
CREATE TABLE changes(x INT PRIMARY KEY,y INT );
CREATE TRIGGER r1t2 AFTER UPDATE ON t2 BEGIN
INSERT INTO changes VALUES(new.a, new.b);
@@ -128,17 +128,17 @@ test:do_test(
function()
test:execsql [[
CREATE TABLE t3(
- c0 TEXT PRIMARY KEY, c1 TEXT , c2 TEXT , c3 TEXT , c4 TEXT , c5 TEXT , c6 TEXT , c7 TEXT , c8 TEXT , c9 TEXT ,
- c10 TEXT , c11 TEXT , c12 TEXT , c13 TEXT , c14 TEXT , c15 TEXT , c16 TEXT , c17 TEXT , c18 TEXT , c19 TEXT ,
- c20 TEXT , c21 TEXT , c22 TEXT , c23 TEXT , c24 TEXT , c25 TEXT , c26 TEXT , c27 TEXT , c28 TEXT , c29 TEXT ,
- c30 TEXT , c31 TEXT , c32 TEXT , c33 TEXT , c34 TEXT , c35 TEXT , c36 TEXT , c37 TEXT , c38 TEXT , c39 TEXT ,
- c40 TEXT , c41 TEXT , c42 TEXT , c43 TEXT , c44 TEXT , c45 TEXT , c46 TEXT , c47 TEXT , c48 TEXT , c49 TEXT ,
- c50 TEXT , c51 TEXT , c52 TEXT , c53 TEXT , c54 TEXT , c55 TEXT , c56 TEXT , c57 TEXT , c58 TEXT , c59 TEXT ,
- c60 TEXT , c61 TEXT , c62 TEXT , c63 TEXT , c64 TEXT , c65 TEXT
+ id INT PRIMARY KEY, c0 TEXT UNIQUE, c1 TEXT , c2 TEXT , c3 TEXT , c4 TEXT , c5 TEXT , c6 TEXT , c7 TEXT ,
+ c8 TEXT , c9 TEXT , c10 TEXT , c11 TEXT , c12 TEXT , c13 TEXT , c14 TEXT , c15 TEXT , c16 TEXT , c17 TEXT ,
+ c18 TEXT , c19 TEXT , c20 TEXT , c21 TEXT , c22 TEXT , c23 TEXT , c24 TEXT , c25 TEXT , c26 TEXT , c27 TEXT ,
+ c28 TEXT , c29 TEXT , c30 TEXT , c31 TEXT , c32 TEXT , c33 TEXT , c34 TEXT , c35 TEXT , c36 TEXT , c37 TEXT ,
+ c38 TEXT , c39 TEXT , c40 TEXT , c41 TEXT , c42 TEXT , c43 TEXT , c44 TEXT , c45 TEXT , c46 TEXT , c47 TEXT ,
+ c48 TEXT , c49 TEXT , c50 TEXT , c51 TEXT , c52 TEXT , c53 TEXT , c54 TEXT , c55 TEXT , c56 TEXT , c57 TEXT ,
+ c58 TEXT , c59 TEXT , c60 TEXT , c61 TEXT , c62 TEXT , c63 TEXT , c64 TEXT , c65 TEXT
);
CREATE TABLE t3_changes(colnum INT PRIMARY KEY, oldval TEXT , newval TEXT );
INSERT INTO t3 VALUES(
- 'a0', 'a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9',
+ 0, 'a0', 'a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9',
'a10','a11','a12','a13','a14','a15','a16','a17','a18','a19',
'a20','a21','a22','a23','a24','a25','a26','a27','a28','a29',
'a30','a31','a32','a33','a34','a35','a36','a37','a38','a39',
diff --git a/test/sql-tap/triggerC.test.lua b/test/sql-tap/triggerC.test.lua
index 59a6d2367..f94c5bdbf 100755
--- a/test/sql-tap/triggerC.test.lua
+++ b/test/sql-tap/triggerC.test.lua
@@ -52,7 +52,7 @@ test:execsql " PRAGMA recursive_triggers = on "
test:do_execsql_test(
"triggerC-1.1",
[[
- CREATE TABLE t1(a TEXT PRIMARY KEY, b TEXT, c TEXT);
+ CREATE TABLE t1(id INT PRIMARY KEY AUTOINCREMENT, a TEXT UNIQUE, b TEXT, c TEXT);
CREATE TABLE log(t TEXT PRIMARY KEY, a1 TEXT, b1 TEXT, c1 TEXT, a2 TEXT, b2 TEXT, c2 TEXT);
CREATE TRIGGER trig1 BEFORE INSERT ON t1 BEGIN
INSERT INTO log VALUES('before', NULL, NULL, NULL, new.a, new.b, new.c);
@@ -82,7 +82,7 @@ test:do_execsql_test(
test:do_execsql_test(
"triggerC-1.2",
[[
- INSERT INTO t1 VALUES('A', 'B', 'C');
+ INSERT INTO t1 VALUES(1, 'A', 'B', 'C');
SELECT * FROM log ORDER BY t DESC;
]], {
-- <triggerC-1.2>
@@ -96,7 +96,7 @@ test:do_execsql_test(
SELECT * FROM t1
]], {
-- <triggerC-1.3>
- "A", "B", "C"
+ 1, "A", "B", "C"
-- </triggerC-1.3>
})
@@ -118,7 +118,7 @@ test:do_execsql_test(
SELECT * FROM t1
]], {
-- <triggerC-1.5>
- "a", "B", "C"
+ 1, "a", "B", "C"
-- </triggerC-1.5>
})
@@ -182,7 +182,7 @@ test:do_execsql_test(
test:do_execsql_test(
"triggerC-1.11",
[[
- CREATE TABLE t5 (a INT primary key, b INT, c INT);
+ CREATE TABLE t5 (a INT UNIQUE, b INT PRIMARY KEY, c INT);
INSERT INTO t5 values (1, 2, 3);
CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN
UPDATE OR IGNORE t5 SET a = new.a, c = 10;
@@ -206,7 +206,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"triggerC-1.13",
[[
- CREATE TABLE t6(a INTEGER PRIMARY KEY, b INT);
+ CREATE TABLE t6(a INT UNIQUE, b INT PRIMARY KEY);
INSERT INTO t6 VALUES(1, 2);
create trigger r1 after update on t6 for each row begin
SELECT 1;
@@ -222,10 +222,11 @@ test:do_execsql_test(
"triggerC-1.14",
[[
DROP TABLE IF EXISTS t1;
- CREATE TABLE cnt(n INT PRIMARY KEY);
- INSERT INTO cnt VALUES(0);
- CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT UNIQUE, c INT, d INT, e INT);
+ CREATE TABLE cnt(id INT PRIMARY KEY, n INT UNIQUE);
+ INSERT INTO cnt VALUES(0, 0);
+ CREATE TABLE t1(a INT UNIQUE, b INT UNIQUE, c INT, d INT, e INT PRIMARY KEY);
CREATE INDEX t1cd ON t1(c,d);
+ CREATE UNIQUE INDEX t1a ON t1(a);
CREATE TRIGGER t1r1 AFTER UPDATE ON t1 BEGIN UPDATE cnt SET n=n+1; END;
INSERT INTO t1 VALUES(1,2,3,4,5);
INSERT INTO t1 VALUES(6,7,8,9,10);
@@ -241,7 +242,7 @@ test:do_catchsql_test(
UPDATE OR ROLLBACK t1 SET a=100;
]], {
-- <triggerC-1.15>
- 1, "Duplicate key exists in unique index 'pk_unnamed_T1_1' in space 'T1'"
+ 1, "Duplicate key exists in unique index 'unique_unnamed_T1_1' in space 'T1'"
-- </triggerC-1.15>
})
@@ -712,11 +713,12 @@ test:do_test(
"triggerC-10.1",
function()
test:execsql [[
- CREATE TABLE t10(a TEXT PRIMARY KEY, updatecnt INT DEFAULT 0);
+ CREATE TABLE t10(id INT PRIMARY KEY, a TEXT, updatecnt INT DEFAULT 0);
+ CREATE UNIQUE INDEX t10i1 ON t10(a);
CREATE TRIGGER t10_bu BEFORE UPDATE OF a ON t10 BEGIN
UPDATE t10 SET updatecnt = updatecnt+1 WHERE a = old.a;
END;
- INSERT INTO t10 VALUES('hello', 0);
+ INSERT INTO t10 VALUES(0, 'hello', 0);
]]
-- Before the problem was fixed, table t10 would contain the tuple
-- (world, 0) after running the following script (because the value
@@ -728,7 +730,7 @@ test:do_test(
]]
end, {
-- <triggerC-10.1>
- "world", 1
+ 0, "world", 1
-- </triggerC-10.1>
})
@@ -739,7 +741,7 @@ test:do_execsql_test(
SELECT * FROM t10;
]], {
-- <triggerC-10.2>
- "tcl", 5
+ 0, "tcl", 5
-- </triggerC-10.2>
})
@@ -748,10 +750,11 @@ test:do_test(
function()
test:execsql [[
CREATE TABLE t11(
- c1 INT PRIMARY KEY, c2 INT, c3 INT, c4 INT, c5 INT, c6 INT, c7 INT, c8 INT, c9 INT, c10 INT,
- c11 INT, c12 INT, c13 INT, c14 INT, c15 INT, c16 INT, c17 INT, c18 INT, c19 INT, c20 INT,
- c21 INT, c22 INT, c23 INT, c24 INT, c25 INT, c26 INT, c27 INT, c28 INT, c29 INT, c30 INT,
- c31 INT, c32 INT, c33 INT, c34 INT, c35 INT, c36 INT, c37 INT, c38 INT, c39 INT, c40 INT
+ c0 INT PRIMARY KEY, c1 INT UNIQUE, c2 INT, c3 INT, c4 INT, c5 INT, c6 INT, c7 INT,
+ c8 INT, c9 INT, c10 INT, c11 INT, c12 INT, c13 INT, c14 INT, c15 INT, c16 INT,
+ c17 INT, c18 INT, c19 INT, c20 INT, c21 INT, c22 INT, c23 INT, c24 INT, c25 INT,
+ c26 INT, c27 INT, c28 INT, c29 INT, c30 INT, c31 INT, c32 INT, c33 INT, c34 INT,
+ c35 INT, c36 INT, c37 INT, c38 INT, c39 INT, c40 INT
);
CREATE TRIGGER t11_bu BEFORE UPDATE OF c1 ON t11 BEGIN
@@ -759,7 +762,7 @@ test:do_test(
END;
INSERT INTO t11 VALUES(
- 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
+ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
31, 32, 33, 34, 35, 36, 37, 38, 39, 40
@@ -775,7 +778,7 @@ test:do_test(
]]
end, {
-- <triggerC-10.3>
- 5, 2, 3, 35, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 32, 33, 22, 34, 35, 36, 37, 38, 39, 40
+ 0, 5, 2, 3, 35, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 32, 33, 22, 34, 35, 36, 37, 38, 39, 40
-- </triggerC-10.3>
})
diff --git a/test/sql-tap/triggerD.test.lua b/test/sql-tap/triggerD.test.lua
index f4d1c29a8..37b33ae2d 100755
--- a/test/sql-tap/triggerD.test.lua
+++ b/test/sql-tap/triggerD.test.lua
@@ -36,7 +36,7 @@ test:do_test(
"triggerD-1.1",
function()
return test:execsql [[
- CREATE TABLE t1(rowid INT PRIMARY KEY, oid INT, _rowid_ INT, x INT);
+ CREATE TABLE t1(id INT PRIMARY KEY, rowid INT UNIQUE, oid INT, _rowid_ INT, x INT);
CREATE TABLE log(a TEXT PRIMARY KEY,b INT,c INT,d INT,e INT);
CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
@@ -69,7 +69,7 @@ test:do_test(
"triggerD-1.2",
function()
return test:execsql [[
- INSERT INTO t1 VALUES(100,200,300,400);
+ INSERT INTO t1 VALUES(0, 100,200,300,400);
SELECT * FROM log
]]
end, {
diff --git a/test/sql-tap/update.test.lua b/test/sql-tap/update.test.lua
index d0fc66ebb..82168932e 100755
--- a/test/sql-tap/update.test.lua
+++ b/test/sql-tap/update.test.lua
@@ -888,15 +888,16 @@ test:do_catchsql_test("update-9.4", [[
test:do_execsql_test("update-10.1", [[
DROP TABLE test1;
CREATE TABLE t1(
- a integer primary key,
+ a integer UNIQUE,
b INT UNIQUE,
c INT , d INT ,
e INT , f INT ,
- UNIQUE(c,d)
+ UNIQUE(c,d),
+ id INT PRIMARY KEY AUTOINCREMENT
);
- INSERT INTO t1 VALUES(1,2,3,4,5,6);
- INSERT INTO t1 VALUES(2,3,4,4,6,7);
- SELECT * FROM t1
+ INSERT INTO t1(a,b,c,d,e,f) VALUES(1,2,3,4,5,6);
+ INSERT INTO t1(a,b,c,d,e,f) VALUES(2,3,4,4,6,7);
+ SELECT a,b,c,d,e,f FROM t1
]], {
-- <update-10.1>
1, 2, 3, 4, 5, 6, 2, 3, 4, 4, 6, 7
@@ -905,7 +906,7 @@ test:do_execsql_test("update-10.1", [[
test:do_catchsql_test("update-10.2", [[
UPDATE t1 SET a=1, e=9 WHERE f=6;
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.2>
0, {1, 2, 3, 4, 9, 6, 2, 3, 4, 4, 6, 7}
@@ -914,15 +915,15 @@ test:do_catchsql_test("update-10.2", [[
test:do_catchsql_test("update-10.3", [[
UPDATE t1 SET a=1, e=10 WHERE f=7;
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.3>
- 1, "Duplicate key exists in unique index 'pk_unnamed_T1_1' in space 'T1'"
+ 1, "Duplicate key exists in unique index 'unique_unnamed_T1_1' in space 'T1'"
-- </update-10.3>
})
test:do_catchsql_test("update-10.4", [[
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.4>
0, {1, 2, 3, 4, 9, 6, 2, 3, 4, 4, 6, 7}
@@ -931,7 +932,7 @@ test:do_catchsql_test("update-10.4", [[
test:do_catchsql_test("update-10.5", [[
UPDATE t1 SET b=2, e=11 WHERE f=6;
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.5>
0, {1, 2, 3, 4, 11, 6, 2, 3, 4, 4, 6, 7}
@@ -940,7 +941,7 @@ test:do_catchsql_test("update-10.5", [[
test:do_catchsql_test("update-10.6", [[
UPDATE t1 SET b=2, e=12 WHERE f=7;
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.6>
1, "Duplicate key exists in unique index 'unique_unnamed_T1_2' in space 'T1'"
@@ -948,7 +949,7 @@ test:do_catchsql_test("update-10.6", [[
})
test:do_catchsql_test("update-10.7", [[
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.7>
0, {1, 2, 3, 4, 11, 6, 2, 3, 4, 4, 6, 7}
@@ -957,7 +958,7 @@ test:do_catchsql_test("update-10.7", [[
test:do_catchsql_test("update-10.8", [[
UPDATE t1 SET c=3, d=4, e=13 WHERE f=6;
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.8>
0, {1, 2, 3, 4, 13, 6, 2, 3, 4, 4, 6, 7}
@@ -966,7 +967,7 @@ test:do_catchsql_test("update-10.8", [[
test:do_catchsql_test("update-10.9", [[
UPDATE t1 SET c=3, d=4, e=14 WHERE f=7;
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.9>
1, "Duplicate key exists in unique index 'unique_unnamed_T1_3' in space 'T1'"
@@ -974,7 +975,7 @@ test:do_catchsql_test("update-10.9", [[
})
test:do_catchsql_test("update-10.10", [[
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.10>
0, {1, 2, 3, 4, 13, 6, 2, 3, 4, 4, 6, 7}
diff --git a/test/sql-tap/with1.test.lua b/test/sql-tap/with1.test.lua
index 28b3a858b..97585c13b 100755
--- a/test/sql-tap/with1.test.lua
+++ b/test/sql-tap/with1.test.lua
@@ -25,7 +25,7 @@ testprefix = "with1"
-- end
test:do_execsql_test(1.0, [[
- CREATE TABLE t1(x INTEGER PRIMARY KEY, y INTEGER);
+ CREATE TABLE t1(x INTEGER UNIQUE, y INTEGER, z INTEGER PRIMARY KEY);
WITH x(a) AS ( SELECT * FROM t1) SELECT 10
]], {
-- <1.0>
@@ -42,7 +42,7 @@ test:do_execsql_test(1.1, [[
})
test:do_execsql_test(1.2, [[
- WITH x(a) AS ( SELECT * FROM t1) INSERT INTO t1 VALUES(1,2);
+ WITH x(a) AS ( SELECT * FROM t1) INSERT INTO t1 VALUES(1,1,2);
]], {
-- <1.2>
@@ -185,15 +185,15 @@ test:do_catchsql_test(3.6, [[
---------------------------------------------------------------------------
test:do_execsql_test(4.1, [[
DROP TABLE IF EXISTS t1;
- CREATE TABLE t1(x INT PRIMARY KEY);
- INSERT INTO t1 VALUES(1);
- INSERT INTO t1 VALUES(2);
- INSERT INTO t1 VALUES(3);
- INSERT INTO t1 VALUES(4);
+ CREATE TABLE t1(x INT UNIQUE, z INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO t1(x) VALUES(1);
+ INSERT INTO t1(x) VALUES(2);
+ INSERT INTO t1(x) VALUES(3);
+ INSERT INTO t1(x) VALUES(4);
WITH dset AS ( SELECT 2 UNION ALL SELECT 4 )
DELETE FROM t1 WHERE x IN dset;
- SELECT * FROM t1;
+ SELECT (x) FROM t1;
]], {
-- <4.1>
1, 3
@@ -202,18 +202,18 @@ test:do_execsql_test(4.1, [[
test:do_execsql_test(4.2, [[
WITH iset AS ( SELECT 2 UNION ALL SELECT 4 )
- INSERT INTO t1 SELECT * FROM iset;
- SELECT * FROM t1;
+ INSERT INTO t1(x) SELECT * FROM iset;
+ SELECT x FROM t1;
]], {
-- <4.2>
- 1, 2, 3, 4
+ 1, 3, 2, 4
-- </4.2>
})
test:do_execsql_test(4.3, [[
WITH uset(a, b) AS ( SELECT 2, 8 UNION ALL SELECT 4, 9 )
UPDATE t1 SET x = COALESCE( (SELECT b FROM uset WHERE a=x), x );
- SELECT * FROM t1;
+ SELECT x FROM t1;
]], {
-- <4.3>
1, 3, 8, 9
diff --git a/test/sql/collation.result b/test/sql/collation.result
index c69510fe7..5721ef854 100644
--- a/test/sql/collation.result
+++ b/test/sql/collation.result
@@ -263,13 +263,13 @@ box.schema.user.drop('tmp')
...
-- gh-3644 Foreign key update fails with "unicode_ci".
-- Check that foreign key update doesn't fail with "unicode_ci".
-box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) COLLATE "unicode_ci" PRIMARY KEY);')
+box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) COLLATE "unicode_ci" UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);')
---
...
box.sql.execute('CREATE TABLE t1 (s1 INT PRIMARY KEY, s0 CHAR(5) COLLATE "unicode_ci" REFERENCES t0(s1));')
---
...
-box.sql.execute("INSERT INTO t0 VALUES ('a');")
+box.sql.execute("INSERT INTO t0(s1) VALUES ('a');")
---
...
box.sql.execute("INSERT INTO t1 VALUES (1,'a');")
@@ -279,7 +279,7 @@ box.sql.execute("INSERT INTO t1 VALUES (1,'a');")
box.sql.execute("UPDATE t0 SET s1 = 'A';")
---
...
-box.sql.execute("SELECT * FROM t0;")
+box.sql.execute("SELECT s1 FROM t0;")
---
- - ['A']
...
@@ -294,13 +294,13 @@ box.sql.execute("DROP TABLE t0;")
---
...
-- Check that foreign key update fails with default collation.
-box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) PRIMARY KEY);')
+box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);')
---
...
box.sql.execute('CREATE TABLE t1 (s1 INT PRIMARY KEY, s0 CHAR(5) REFERENCES t0(s1));')
---
...
-box.sql.execute("INSERT INTO t0 VALUES ('a');")
+box.sql.execute("INSERT INTO t0(s1) VALUES ('a');")
---
...
box.sql.execute("INSERT INTO t1 VALUES (1,'a');")
@@ -315,7 +315,7 @@ box.sql.execute("SELECT * FROM t1;")
---
- - [1, 'a']
...
-box.sql.execute("SELECT * FROM t0;")
+box.sql.execute("SELECT s1 FROM t0;")
---
- - ['a']
...
diff --git a/test/sql/collation.test.lua b/test/sql/collation.test.lua
index 4ad2d5e50..4c649a444 100644
--- a/test/sql/collation.test.lua
+++ b/test/sql/collation.test.lua
@@ -105,24 +105,24 @@ box.schema.user.drop('tmp')
-- gh-3644 Foreign key update fails with "unicode_ci".
-- Check that foreign key update doesn't fail with "unicode_ci".
-box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) COLLATE "unicode_ci" PRIMARY KEY);')
+box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) COLLATE "unicode_ci" UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);')
box.sql.execute('CREATE TABLE t1 (s1 INT PRIMARY KEY, s0 CHAR(5) COLLATE "unicode_ci" REFERENCES t0(s1));')
-box.sql.execute("INSERT INTO t0 VALUES ('a');")
+box.sql.execute("INSERT INTO t0(s1) VALUES ('a');")
box.sql.execute("INSERT INTO t1 VALUES (1,'a');")
-- Should't fail.
box.sql.execute("UPDATE t0 SET s1 = 'A';")
-box.sql.execute("SELECT * FROM t0;")
+box.sql.execute("SELECT s1 FROM t0;")
box.sql.execute("SELECT * FROM t1;")
box.sql.execute("DROP TABLE t1;")
box.sql.execute("DROP TABLE t0;")
-- Check that foreign key update fails with default collation.
-box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) PRIMARY KEY);')
+box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);')
box.sql.execute('CREATE TABLE t1 (s1 INT PRIMARY KEY, s0 CHAR(5) REFERENCES t0(s1));')
-box.sql.execute("INSERT INTO t0 VALUES ('a');")
+box.sql.execute("INSERT INTO t0(s1) VALUES ('a');")
box.sql.execute("INSERT INTO t1 VALUES (1,'a');")
-- Should fail.
box.sql.execute("UPDATE t0 SET s1 = 'A';")
box.sql.execute("SELECT * FROM t1;")
-box.sql.execute("SELECT * FROM t0;")
+box.sql.execute("SELECT s1 FROM t0;")
box.sql.execute("DROP TABLE t1;")
box.sql.execute("DROP TABLE t0;")
diff --git a/test/sql/on-conflict.result b/test/sql/on-conflict.result
index 731f03c66..97ec77526 100644
--- a/test/sql/on-conflict.result
+++ b/test/sql/on-conflict.result
@@ -86,10 +86,10 @@ box.sql.execute("DROP TABLE a;")
...
-- gh-3566: UPDATE OR IGNORE causes deletion of old entry.
--
-box.sql.execute("CREATE TABLE tj (s1 INT PRIMARY KEY, s2 INT);")
+box.sql.execute("CREATE TABLE tj (s0 INT PRIMARY KEY, s1 INT UNIQUE, s2 INT);")
---
...
-box.sql.execute("INSERT INTO tj VALUES (1, 2), (2, 3);")
+box.sql.execute("INSERT INTO tj VALUES (1, 1, 2), (2, 2, 3);")
---
...
box.sql.execute("CREATE UNIQUE INDEX i ON tj (s2);")
@@ -98,7 +98,7 @@ box.sql.execute("CREATE UNIQUE INDEX i ON tj (s2);")
box.sql.execute("UPDATE OR IGNORE tj SET s1 = s1 + 1;")
---
...
-box.sql.execute("SELECT * FROM tj;")
+box.sql.execute("SELECT s1, s2 FROM tj;")
---
- - [1, 2]
- [3, 3]
@@ -106,7 +106,7 @@ box.sql.execute("SELECT * FROM tj;")
box.sql.execute("UPDATE OR IGNORE tj SET s2 = s2 + 1;")
---
...
-box.sql.execute("SELECT * FROM tj;")
+box.sql.execute("SELECT s1, s2 FROM tj;")
---
- - [1, 2]
- [3, 4]
diff --git a/test/sql/on-conflict.test.lua b/test/sql/on-conflict.test.lua
index aa58b854b..9486942b9 100644
--- a/test/sql/on-conflict.test.lua
+++ b/test/sql/on-conflict.test.lua
@@ -35,13 +35,13 @@ box.sql.execute("DROP TABLE a;")
-- gh-3566: UPDATE OR IGNORE causes deletion of old entry.
--
-box.sql.execute("CREATE TABLE tj (s1 INT PRIMARY KEY, s2 INT);")
-box.sql.execute("INSERT INTO tj VALUES (1, 2), (2, 3);")
+box.sql.execute("CREATE TABLE tj (s0 INT PRIMARY KEY, s1 INT UNIQUE, s2 INT);")
+box.sql.execute("INSERT INTO tj VALUES (1, 1, 2), (2, 2, 3);")
box.sql.execute("CREATE UNIQUE INDEX i ON tj (s2);")
box.sql.execute("UPDATE OR IGNORE tj SET s1 = s1 + 1;")
-box.sql.execute("SELECT * FROM tj;")
+box.sql.execute("SELECT s1, s2 FROM tj;")
box.sql.execute("UPDATE OR IGNORE tj SET s2 = s2 + 1;")
-box.sql.execute("SELECT * FROM tj;")
+box.sql.execute("SELECT s1, s2 FROM tj;")
-- gh-3565: INSERT OR REPLACE causes assertion fault.
--
diff --git a/test/sql/row-count.result b/test/sql/row-count.result
index d4c86ac2b..d6248eb0f 100644
--- a/test/sql/row-count.result
+++ b/test/sql/row-count.result
@@ -27,10 +27,10 @@ box.sql.execute("SELECT ROW_COUNT();")
---
- - [1]
...
-box.sql.execute("CREATE TABLE t3 (i1 INT PRIMARY KEY, i2 INT);")
+box.sql.execute("CREATE TABLE t3 (i1 INT UNIQUE, i2 INT, i3 INT PRIMARY KEY);")
---
...
-box.sql.execute("INSERT INTO t3 VALUES (0, 0);")
+box.sql.execute("INSERT INTO t3 VALUES (0, 0, 0);")
---
...
box.sql.execute("SELECT ROW_COUNT();")
@@ -80,7 +80,7 @@ box.sql.execute("SELECT ROW_COUNT();")
---
- - [4]
...
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
---
...
box.sql.execute("TRUNCATE TABLE t3;")
@@ -90,7 +90,7 @@ box.sql.execute("SELECT ROW_COUNT();")
---
- - [0]
...
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
---
...
box.sql.execute("UPDATE t3 SET i2 = 666;")
@@ -110,7 +110,7 @@ box.sql.execute("SELECT ROW_COUNT();")
---
- - [3]
...
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
---
...
box.sql.execute("DELETE FROM t3")
diff --git a/test/sql/row-count.test.lua b/test/sql/row-count.test.lua
index 45a39d19a..f10807fff 100644
--- a/test/sql/row-count.test.lua
+++ b/test/sql/row-count.test.lua
@@ -9,8 +9,8 @@ box.sql.execute("SELECT ROW_COUNT();")
box.sql.execute("SELECT ROW_COUNT();")
box.sql.execute("CREATE TABLE t2 (s1 CHAR(10) PRIMARY KEY, s2 CHAR(10) REFERENCES t1 ON DELETE CASCADE);")
box.sql.execute("SELECT ROW_COUNT();")
-box.sql.execute("CREATE TABLE t3 (i1 INT PRIMARY KEY, i2 INT);")
-box.sql.execute("INSERT INTO t3 VALUES (0, 0);")
+box.sql.execute("CREATE TABLE t3 (i1 INT UNIQUE, i2 INT, i3 INT PRIMARY KEY);")
+box.sql.execute("INSERT INTO t3 VALUES (0, 0, 0);")
box.sql.execute("SELECT ROW_COUNT();")
box.sql.execute("CREATE TRIGGER x AFTER DELETE ON t1 FOR EACH ROW BEGIN UPDATE t3 SET i1 = i1 + ROW_COUNT(); END;")
box.sql.execute("SELECT ROW_COUNT();")
@@ -25,10 +25,10 @@ box.sql.execute("REPLACE INTO t2 VALUES('a', 'c');")
box.sql.execute("SELECT ROW_COUNT();")
box.sql.execute("DELETE FROM t1;")
box.sql.execute("SELECT ROW_COUNT();")
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
box.sql.execute("TRUNCATE TABLE t3;")
box.sql.execute("SELECT ROW_COUNT();")
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
box.sql.execute("UPDATE t3 SET i2 = 666;")
box.sql.execute("SELECT ROW_COUNT();")
-- gh-3816: DELETE optimization returns valid number of
@@ -36,7 +36,7 @@ box.sql.execute("SELECT ROW_COUNT();")
--
box.sql.execute("DELETE FROM t3 WHERE 0 = 0;")
box.sql.execute("SELECT ROW_COUNT();")
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
box.sql.execute("DELETE FROM t3")
box.sql.execute("SELECT ROW_COUNT();")
-- But triggers still should't be accounted.
diff --git a/test/sql/triggers.result b/test/sql/triggers.result
index 45d231f72..bbfff3302 100644
--- a/test/sql/triggers.result
+++ b/test/sql/triggers.result
@@ -253,7 +253,7 @@ box.sql.execute("DROP TABLE T1;")
box.sql.execute("PRAGMA sql_default_engine ('vinyl');")
---
...
-box.sql.execute("CREATE TABLE m (s1 TEXT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE m (s0 INT PRIMARY KEY, s1 TEXT UNIQUE);")
---
...
box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;")
@@ -262,13 +262,13 @@ box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE
box.sql.execute("PRAGMA sql_default_engine('memtx');")
---
...
-box.sql.execute("CREATE TABLE n (s1 TEXT PRIMARY KEY, s2 REAL);")
+box.sql.execute("CREATE TABLE n (s0 INT PRIMARY KEY, s1 TEXT UNIQUE, s2 REAL);")
---
...
-box.sql.execute("INSERT INTO m VALUES ('0');")
+box.sql.execute("INSERT INTO m VALUES (0, '0');")
---
...
-box.sql.execute("INSERT INTO n VALUES ('',null);")
+box.sql.execute("INSERT INTO n VALUES (0, '',null);")
---
...
box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';")
@@ -289,7 +289,7 @@ box.sql.execute("DROP TABLE n;")
box.sql.execute("PRAGMA sql_default_engine ('memtx');")
---
...
-box.sql.execute("CREATE TABLE m (s1 TEXT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE m (s0 INT PRIMARY KEY, s1 TEXT UNIQUE);")
---
...
box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;")
@@ -298,13 +298,13 @@ box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE
box.sql.execute("PRAGMA sql_default_engine('vinyl');")
---
...
-box.sql.execute("CREATE TABLE n (s1 TEXT PRIMARY KEY, s2 REAL);")
+box.sql.execute("CREATE TABLE n (s0 INT PRIMARY KEY, s1 TEXT UNIQUE, s2 REAL);")
---
...
-box.sql.execute("INSERT INTO m VALUES ('0');")
+box.sql.execute("INSERT INTO m VALUES (0, '0');")
---
...
-box.sql.execute("INSERT INTO n VALUES ('',null);")
+box.sql.execute("INSERT INTO n VALUES (0, '',null);")
---
...
box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';")
diff --git a/test/sql/triggers.test.lua b/test/sql/triggers.test.lua
index a322b2f02..eb3f41ef5 100644
--- a/test/sql/triggers.test.lua
+++ b/test/sql/triggers.test.lua
@@ -101,12 +101,12 @@ box.sql.execute("DROP TABLE T1;")
--
-- Case 1: Src 'vinyl' table; Dst 'memtx' table
box.sql.execute("PRAGMA sql_default_engine ('vinyl');")
-box.sql.execute("CREATE TABLE m (s1 TEXT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE m (s0 INT PRIMARY KEY, s1 TEXT UNIQUE);")
box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;")
box.sql.execute("PRAGMA sql_default_engine('memtx');")
-box.sql.execute("CREATE TABLE n (s1 TEXT PRIMARY KEY, s2 REAL);")
-box.sql.execute("INSERT INTO m VALUES ('0');")
-box.sql.execute("INSERT INTO n VALUES ('',null);")
+box.sql.execute("CREATE TABLE n (s0 INT PRIMARY KEY, s1 TEXT UNIQUE, s2 REAL);")
+box.sql.execute("INSERT INTO m VALUES (0, '0');")
+box.sql.execute("INSERT INTO n VALUES (0, '',null);")
box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';")
-- ANALYZE operates with _sql_stat{1,4} tables should work
@@ -117,12 +117,12 @@ box.sql.execute("DROP TABLE n;")
-- Case 2: Src 'memtx' table; Dst 'vinyl' table
box.sql.execute("PRAGMA sql_default_engine ('memtx');")
-box.sql.execute("CREATE TABLE m (s1 TEXT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE m (s0 INT PRIMARY KEY, s1 TEXT UNIQUE);")
box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;")
box.sql.execute("PRAGMA sql_default_engine('vinyl');")
-box.sql.execute("CREATE TABLE n (s1 TEXT PRIMARY KEY, s2 REAL);")
-box.sql.execute("INSERT INTO m VALUES ('0');")
-box.sql.execute("INSERT INTO n VALUES ('',null);")
+box.sql.execute("CREATE TABLE n (s0 INT PRIMARY KEY, s1 TEXT UNIQUE, s2 REAL);")
+box.sql.execute("INSERT INTO m VALUES (0, '0');")
+box.sql.execute("INSERT INTO n VALUES (0, '',null);")
box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';")
-- ANALYZE operates with _sql_stat{1,4} tables should work
--
2.19.2
^ permalink raw reply [flat|nested] 13+ messages in thread
* [tarantool-patches] Re: [PATCH v2 3/5] sql: fix fkey exception for self-referenced table
2018-12-29 10:49 ` [tarantool-patches] [PATCH v2 3/5] sql: fix fkey exception for self-referenced table Kirill Shcherbatov
@ 2018-12-29 13:26 ` n.pettik
0 siblings, 0 replies; 13+ messages in thread
From: n.pettik @ 2018-12-29 13:26 UTC (permalink / raw)
To: tarantool-patches; +Cc: Kirill Shcherbatov
> UPDATE operation doesn't fail when fkey self-reference condition
> unsatisfied, when table has other records.
Nit: ... and table has other records.
> To do not raise error where it is not necessary Vdbe makes
It is russian-like collocation, we can’t ‘make’ lookup.
"… Vdbe inspects parent table emitting OP_Found" (or smth like this)
> lookup in parent table with OP_Found. This branch is not valid
> for self-referenced table since its looking for a tuple affected
> by UPDATE operation and since the foreign key has already
> detected a conflict it mast be raised.
Nit: must.
Sorry for being so picky.
>
> Example:
> CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT, c INT, d TEXT, UNIQUE(a, b),
> FOREIGN KEY(c, d) REFERENCES t6(a, b));
> INSERT INTO t6 VALUES(1, 'a', 1, 'a');
> INSERT INTO t6 VALUES(100, 'one', 100, 'one');
> UPDATE t6 SET c = 1, d = 'a' WHERE a = 100;
> -- fk conflict must be raised here
>
> Needed for #3850
> Closes #3918
> —
Patch itself is OK.
Anyway, I am still interested why SQLite demonstrates
this behaviour. I believe they know about it and don't
fix it so far.
^ permalink raw reply [flat|nested] 13+ messages in thread
* [tarantool-patches] Re: [PATCH v2 4/5] sql: encode tuples with mpstream on Vdbe run
2018-12-29 10:49 ` [tarantool-patches] [PATCH v2 4/5] sql: encode tuples with mpstream on Vdbe run Kirill Shcherbatov
@ 2018-12-29 13:26 ` n.pettik
2018-12-29 15:28 ` Kirill Shcherbatov
0 siblings, 1 reply; 13+ messages in thread
From: n.pettik @ 2018-12-29 13:26 UTC (permalink / raw)
To: tarantool-patches; +Cc: Kirill Shcherbatov
> On 29 Dec 2018, at 12:49, Kirill Shcherbatov <kshcherbatov@tarantool.org> wrote:
>
> Introduced new sql_vdbe_mem_encode_tuple and
> mpstream_encode_vdbe_mem routines to perform Vdbe memory to
> msgpack encoding on region without previous size estimation call.
> Got rid off sqlite3VdbeMsgpackRecordLen and
Nit: got rid of.
> +/**
> + * Perform encoding field_count Vdbe memory fields on region as
> + * msgpack array.
> + * @param fields The first Vdbe memory field to encode.
> + * @param field_count Count of fields to encode.
> + * @param[out] tuple_size Size of encoded tuple.
> + * @param region Region to use.
> + * @retval NULL on error, diag message is set.
> + * @retval no NULL tuple pointer on success.
Nit: not NULL … or simply “Pointer to valid tuple."
> @@ -1714,3 +1716,71 @@ sqlite3ValueBytes(sqlite3_value * pVal)
> return 0;
> return valueBytes(pVal);
> }
> +
> +/**
> + * Perform encoding memory variable to stream.
> + * @param stream Initialized mpstream encoder object.
> + * @param var Vdbe memory variable to encode with stream.
> + */
> +static void
> +mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var)
> +{
> + assert(memIsValid(var));
> + if (var->flags & MEM_Null) {
> + mpstream_encode_nil(stream);
> + } else if (var->flags & MEM_Real) {
> + mpstream_encode_double(stream, var->u.r);
> + } else if (var->flags & MEM_Int) {
> + if (var->u.i >= 0)
> + mpstream_encode_uint(stream, var->u.i);
> + else
> + mpstream_encode_int(stream, var->u.i);
> + } else if (var->flags & MEM_Str) {
> + mpstream_encode_strn(stream, var->z, var->n);
> + } else if (var->flags & MEM_Bool) {
> + mpstream_encode_bool(stream, var->u.b);
> + } else {
> + /*
> + * Emit BIN header iff the BLOB doesn't store
> + * MsgPack content.
> + */
> + if ((var->flags & MEM_Subtype) == 0 ||
> + var->subtype != SQL_SUBTYPE_MSGPACK) {
> + uint32_t binl = var->n +
> + ((var->flags & MEM_Zero) ?
> + var->u.nZero : 0);
> + mpstream_encode_binl(stream, binl);
> + }
> + mpstream_memcpy(stream, var->z, var->n);
> + if (var->flags & MEM_Zero)
> + mpstream_memset(stream, 0, var->u.nZero);
> + }
Lets replace these if-else's with one switch.
The rest is OK.
^ permalink raw reply [flat|nested] 13+ messages in thread
* [tarantool-patches] Re: [PATCH v2 5/5] sql: do not use OP_Delete+OP_Insert for UPDATES
2018-12-29 10:49 ` [tarantool-patches] [PATCH v2 5/5] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov
@ 2018-12-29 13:35 ` n.pettik
2018-12-29 15:31 ` Kirill Shcherbatov
0 siblings, 1 reply; 13+ messages in thread
From: n.pettik @ 2018-12-29 13:35 UTC (permalink / raw)
To: tarantool-patches; +Cc: Kirill Shcherbatov
>
> +/** Callback to forward and error from mpstream methods. */
> +static inline void
> +mpstream_encode_error(void *error_ctx)
> +{
> + *(bool *)error_ctx = true;
> +}
Is this artefact after previous patch?
> +/**
> + * Perform encoding memory variable to stream.
> + * @param stream Initialized mpstream encoder object.
> + * @param var Vdbe memory variable to encode with stream.
> + */
> +void
> +mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var);
> +
The same question.
The rest is Ok now.
I didn’t carefully check test fixes tho, only looked through, since they
consist of monotonic changes, but I believe you did it :)
^ permalink raw reply [flat|nested] 13+ messages in thread
* [tarantool-patches] Re: [PATCH v2 4/5] sql: encode tuples with mpstream on Vdbe run
2018-12-29 13:26 ` [tarantool-patches] " n.pettik
@ 2018-12-29 15:28 ` Kirill Shcherbatov
2019-01-09 12:29 ` n.pettik
0 siblings, 1 reply; 13+ messages in thread
From: Kirill Shcherbatov @ 2018-12-29 15:28 UTC (permalink / raw)
To: tarantool-patches, Nikita Pettik
Hi! Thank you for review.
> Nit: ... and table has other records.
> It is russian-like collocation, we can’t ‘make’ lookup.
> "… Vdbe inspects parent table emitting OP_Found" (or smth like this)
> Nit: must.
> Nit: got rid of.
> Nit: not NULL … or simply “Pointer to valid tuple."
Fixed. Here and in previous patch.
> Lets replace these if-else's with one switch.
Unfortunately, it is not possible now, as we may have a flag combination
(like OP_String+OP_Blob). You may take a look into Server chat for more
details for this particular case, but it is not the one.
Maybe we may improve it in future(when affinity would be reworked?) but
not now and not as a part of this patch, I believe.
> The rest is OK
^ permalink raw reply [flat|nested] 13+ messages in thread
* [tarantool-patches] Re: [PATCH v2 5/5] sql: do not use OP_Delete+OP_Insert for UPDATES
2018-12-29 13:35 ` [tarantool-patches] " n.pettik
@ 2018-12-29 15:31 ` Kirill Shcherbatov
0 siblings, 0 replies; 13+ messages in thread
From: Kirill Shcherbatov @ 2018-12-29 15:31 UTC (permalink / raw)
To: tarantool-patches, Nikita Pettik
>> +/** Callback to forward and error from mpstream methods. */
>> +static inline void
>> +mpstream_encode_error(void *error_ctx)
>> +{
>> + *(bool *)error_ctx = true;
>> +}
>
> Is this artefact after previous patch?
Ugum, it was already fixed on branch.
>> +void
>> +mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var);
>> +
> The same question.
Here I did it consciously. This routine was just a helper in previous patch so may be
static that likelly doesn't require function call by pointer. In this patch it became
reusable so I have to define it's signature in header.
But I've already moved this change to previous path to decrease changes. It doesn't
really matter.
> The rest is Ok now.
> I didn’t carefully check test fixes tho, only looked through, since they
> consist of monotonic changes, but I believe you did it :)
^ permalink raw reply [flat|nested] 13+ messages in thread
* [tarantool-patches] Re: [PATCH v2 4/5] sql: encode tuples with mpstream on Vdbe run
2018-12-29 15:28 ` Kirill Shcherbatov
@ 2019-01-09 12:29 ` n.pettik
0 siblings, 0 replies; 13+ messages in thread
From: n.pettik @ 2019-01-09 12:29 UTC (permalink / raw)
To: tarantool-patches; +Cc: Kirill Shcherbatov
>> Lets replace these if-else's with one switch.
> Unfortunately, it is not possible now, as we may have a flag combination
> (like OP_String+OP_Blob). You may take a look into Server chat for more
> details for this particular case, but it is not the one.
> Maybe we may improve it in future(when affinity would be reworked?) but
> not now and not as a part of this patch, I believe.
It looks like an issue. I am going to open an issue then and fix it.
Thx for investigation.
Whole patchset LGTM.
^ permalink raw reply [flat|nested] 13+ messages in thread
* [tarantool-patches] Re: [PATCH v2 0/5] sql: do not use OP_Delete+OP_Insert for UPDATES
2018-12-29 10:48 [tarantool-patches] [PATCH v2 0/5] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov
` (4 preceding siblings ...)
2018-12-29 10:49 ` [tarantool-patches] [PATCH v2 5/5] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov
@ 2019-01-10 12:30 ` Kirill Yukhin
5 siblings, 0 replies; 13+ messages in thread
From: Kirill Yukhin @ 2019-01-10 12:30 UTC (permalink / raw)
To: tarantool-patches; +Cc: korablev, Kirill Shcherbatov
Hello,
On 29 Dec 13:48, Kirill Shcherbatov wrote:
> Introduced a new OP_Update opcode making Tarantool native Update
> operation.
> In case of UPDATE or REPLACE we can't use new OP_Update as it
> has a complex SQL-specific semantics:
>
> CREATE TABLE tj (s1 INT PRIMARY KEY, s2 INT);
> INSERT INTO tj VALUES (1, 3),(2, 4),(3,5);
> CREATE UNIQUE INDEX i ON tj (s2);
> SELECT * FROM tj;
> [1, 3], [2, 4], [3, 5]
> UPDATE OR REPLACE tj SET s2 = s2 + 1;
> SELECT * FROM tj;
> [1, 4], [3, 6]
>
> I.e. [1, 3] tuple is updated as [1, 4] and have replaced tuple
> [2, 4]. This logic is implemented as preventive tuples deletion
> by all corresponding indexes in SQL.
>
> The other significant change is forbidden primary key update.
> It was possible to deal with it the same way like with or
> REPLACE specifier but we need an atomic UPDATE step for #3691
> ticket to support "or IGNORE/or ABORT/or FAIL" specifiers.
> Reworked tests to make testing avoiding primary key UPDATE where
> possible.
>
> Fixed bug in VDBE - sometimes temporal tuples in memory allocated
> with sql_vdbe_mem_alloc_region were stored in Mem variable having
> invalid flags set.
>
> Changes in version 2:
> - Reworked part of code to close tikets #3035, #3918: reused
> a new routine mpstream_encode_vdbe_mem to encode tuples on
> region everywhere on Vdbe execution: got rid of routines
> sqlite3VdbeMsgpackRecordLen and sqlite3VdbeMsgpackRecordPut
> that became useless.
> - Fixed few spell mistakes in comments and commit messages
> - Minor code changes: better mpstream methods names, changed
> OP_IdxUpdate to OP_Update name.
>
> Branch: http://github.com/tarantool/tarantool/tree/kshch/gh-3850-op-update
> Issue: https://github.com/tarantool/tarantool/issues/3850
I've checked your patch set into 2.1 branch.
--
Regards, Kirill Yukhin
^ permalink raw reply [flat|nested] 13+ messages in thread
end of thread, other threads:[~2019-01-10 12:30 UTC | newest]
Thread overview: 13+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2018-12-29 10:48 [tarantool-patches] [PATCH v2 0/5] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov
2018-12-29 10:48 ` [tarantool-patches] [PATCH v2 1/5] sql: clean-up vdbe_emit_constraint_checks Kirill Shcherbatov
2018-12-29 10:48 ` [tarantool-patches] [PATCH v2 2/5] sql: fix sql_vdbe_mem_alloc_region result memory Kirill Shcherbatov
2018-12-29 10:49 ` [tarantool-patches] [PATCH v2 3/5] sql: fix fkey exception for self-referenced table Kirill Shcherbatov
2018-12-29 13:26 ` [tarantool-patches] " n.pettik
2018-12-29 10:49 ` [tarantool-patches] [PATCH v2 4/5] sql: encode tuples with mpstream on Vdbe run Kirill Shcherbatov
2018-12-29 13:26 ` [tarantool-patches] " n.pettik
2018-12-29 15:28 ` Kirill Shcherbatov
2019-01-09 12:29 ` n.pettik
2018-12-29 10:49 ` [tarantool-patches] [PATCH v2 5/5] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov
2018-12-29 13:35 ` [tarantool-patches] " n.pettik
2018-12-29 15:31 ` Kirill Shcherbatov
2019-01-10 12:30 ` [tarantool-patches] Re: [PATCH v2 0/5] " Kirill Yukhin
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox