* [tarantool-patches] [PATCH v1 0/3] sql: do not use OP_Delete+OP_Insert for UPDATES
@ 2018-12-19 15:37 Kirill Shcherbatov
2018-12-19 15:37 ` [tarantool-patches] [PATCH v1 1/3] sql: clean-up vdbe_emit_constraint_checks Kirill Shcherbatov
` (3 more replies)
0 siblings, 4 replies; 10+ messages in thread
From: Kirill Shcherbatov @ 2018-12-19 15:37 UTC (permalink / raw)
To: tarantool-patches, v.shpilevoy; +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.
Branch: http://github.com/tarantool/tarantool/tree/kshch/gh-3850-op-update
Issue: https://github.com/tarantool/tarantool/issues/3850
Kirill Shcherbatov (3):
sql: clean-up vdbe_emit_constraint_checks
sql: fix sql_vdbe_mem_alloc_region result memory
sql: do not use OP_Delete+OP_Insert for UPDATES
src/box/sql/delete.c | 3 +-
src/box/sql/fkey.c | 49 +++---
src/box/sql/insert.c | 49 ++----
src/box/sql/sqliteInt.h | 17 +-
src/box/sql/update.c | 81 +++++++--
src/box/sql/vdbe.c | 106 ++++++++++++
src/box/sql/vdbeInt.h | 6 +
src/box/sql/vdbeaux.c | 3 +-
src/box/sql/vdbemem.c | 36 ++++
src/mpstream.c | 20 +++
src/mpstream.h | 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 | 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 | 10 +-
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/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 +-
44 files changed, 660 insertions(+), 384 deletions(-)
--
2.19.2
^ permalink raw reply [flat|nested] 10+ messages in thread* [tarantool-patches] [PATCH v1 1/3] sql: clean-up vdbe_emit_constraint_checks 2018-12-19 15:37 [tarantool-patches] [PATCH v1 0/3] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov @ 2018-12-19 15:37 ` Kirill Shcherbatov 2018-12-25 17:26 ` [tarantool-patches] " n.pettik 2018-12-19 15:37 ` [tarantool-patches] [PATCH v1 2/3] sql: fix sql_vdbe_mem_alloc_region result memory Kirill Shcherbatov ` (2 subsequent siblings) 3 siblings, 1 reply; 10+ messages in thread From: Kirill Shcherbatov @ 2018-12-19 15:37 UTC (permalink / raw) To: tarantool-patches, v.shpilevoy; +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. Needed for #3850 --- 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] 10+ messages in thread
* [tarantool-patches] Re: [PATCH v1 1/3] sql: clean-up vdbe_emit_constraint_checks 2018-12-19 15:37 ` [tarantool-patches] [PATCH v1 1/3] sql: clean-up vdbe_emit_constraint_checks Kirill Shcherbatov @ 2018-12-25 17:26 ` n.pettik 0 siblings, 0 replies; 10+ messages in thread From: n.pettik @ 2018-12-25 17:26 UTC (permalink / raw) To: tarantool-patches; +Cc: Kirill Shcherbatov > On 19 Dec 2018, at 17:37, Kirill Shcherbatov <kshcherbatov@tarantool.org> wrote: > > Removed vdbe code generation making type checks from > vdbe_emit_constraint_checks as it is useless since strict types > have been introduced. > > Needed for #3850 It doesn’t seem to be needed for #3850. Otherwise explain why it is required for #3850. Patch is OK. ^ permalink raw reply [flat|nested] 10+ messages in thread
* [tarantool-patches] [PATCH v1 2/3] sql: fix sql_vdbe_mem_alloc_region result memory 2018-12-19 15:37 [tarantool-patches] [PATCH v1 0/3] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov 2018-12-19 15:37 ` [tarantool-patches] [PATCH v1 1/3] sql: clean-up vdbe_emit_constraint_checks Kirill Shcherbatov @ 2018-12-19 15:37 ` Kirill Shcherbatov 2018-12-25 17:26 ` [tarantool-patches] " n.pettik 2018-12-19 15:37 ` [tarantool-patches] [PATCH v1 3/3] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov 2018-12-20 20:41 ` [tarantool-patches] Re: [PATCH v1 0/3] " Vladislav Shpilevoy 3 siblings, 1 reply; 10+ messages in thread From: Kirill Shcherbatov @ 2018-12-19 15:37 UTC (permalink / raw) To: tarantool-patches, v.shpilevoy; +Cc: Kirill Shcherbatov The function sql_vdbe_mem_alloc_region that constructing the value of Vdbe Mem object used to change only flags responsible for it's type. It is also required to grind old flags, as their combination may be invalid. In a typical Vdbe scenario, OP_MakeRecord and OP_RowData make memory release with sqlite3VdbeMemRelease and allocation on region with sql_vdbe_mem_alloc_region call. An integrity assert based on sqlite3VdbeCheckMemInvariants would fire here because of contradictory 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] 10+ messages in thread
* [tarantool-patches] Re: [PATCH v1 2/3] sql: fix sql_vdbe_mem_alloc_region result memory 2018-12-19 15:37 ` [tarantool-patches] [PATCH v1 2/3] sql: fix sql_vdbe_mem_alloc_region result memory Kirill Shcherbatov @ 2018-12-25 17:26 ` n.pettik 0 siblings, 0 replies; 10+ messages in thread From: n.pettik @ 2018-12-25 17:26 UTC (permalink / raw) To: tarantool-patches; +Cc: Kirill Shcherbatov > The function sql_vdbe_mem_alloc_region that constructing the > value of Vdbe Mem object used to change only flags responsible > for it's type. > It is also required to grind old flags, as their combination may > be invalid. > In a typical Vdbe scenario, OP_MakeRecord and OP_RowData make > memory release with sqlite3VdbeMemRelease and allocation > on region with sql_vdbe_mem_alloc_region call. An integrity > assert based on sqlite3VdbeCheckMemInvariants would fire here > because of contradictory 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; > } I’ve changed your commit message a bit: 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). Read it and in case it looks OK to you, please apply. LGTM. ^ permalink raw reply [flat|nested] 10+ messages in thread
* [tarantool-patches] [PATCH v1 3/3] sql: do not use OP_Delete+OP_Insert for UPDATES 2018-12-19 15:37 [tarantool-patches] [PATCH v1 0/3] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov 2018-12-19 15:37 ` [tarantool-patches] [PATCH v1 1/3] sql: clean-up vdbe_emit_constraint_checks Kirill Shcherbatov 2018-12-19 15:37 ` [tarantool-patches] [PATCH v1 2/3] sql: fix sql_vdbe_mem_alloc_region result memory Kirill Shcherbatov @ 2018-12-19 15:37 ` Kirill Shcherbatov 2018-12-25 17:26 ` [tarantool-patches] " n.pettik 2018-12-20 20:41 ` [tarantool-patches] Re: [PATCH v1 0/3] " Vladislav Shpilevoy 3 siblings, 1 reply; 10+ messages in thread From: Kirill Shcherbatov @ 2018-12-19 15:37 UTC (permalink / raw) To: tarantool-patches, v.shpilevoy; +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. Closes #3850 --- src/box/sql/delete.c | 3 +- src/box/sql/fkey.c | 49 +++--- src/box/sql/insert.c | 28 +-- src/box/sql/sqliteInt.h | 17 +- src/box/sql/update.c | 81 +++++++-- src/box/sql/vdbe.c | 106 ++++++++++++ src/box/sql/vdbeInt.h | 6 + src/box/sql/vdbemem.c | 36 ++++ src/mpstream.c | 20 +++ src/mpstream.h | 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 | 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 | 10 +- 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/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 +- 43 files changed, 658 insertions(+), 362 deletions(-) diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c index f9c42fdec..723ca534d 100644 --- a/src/box/sql/delete.c +++ b/src/box/sql/delete.c @@ -518,7 +518,8 @@ sql_generate_row_delete(struct Parse *parse, struct Table *table, * constraints attached to other tables) are not * violated by deleting this row. */ - fkey_emit_check(parse, table, first_old_reg, 0, NULL); + fkey_emit_check(parse, table, first_old_reg, 0, NULL, + TK_DELETE); } /* Delete the index and table entries. Skip this step if diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c index 4e3270f0c..44fa1291a 100644 --- a/src/box/sql/fkey.c +++ b/src/box/sql/fkey.c @@ -190,11 +190,12 @@ * @param referenced_idx Id of referenced index. * @param reg_data Address of array containing child table row. * @param incr_count Increment constraint counter by this value. + * @param op operation, one of TK_UPDATE, TK_INSERT, TK_DELETE. */ 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, int op) { assert(incr_count == -1 || incr_count == 1); struct Vdbe *v = sqlite3GetVdbe(parse_context); @@ -221,14 +222,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 +246,29 @@ 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); + if (!(fkey_is_self_referenced(fk_def) && op == TK_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 && @@ -515,7 +522,7 @@ fkey_action_is_set_null(struct Parse *parse_context, const struct fkey *fkey) void fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old, - int reg_new, const int *changed_cols) + int reg_new, const int *changed_cols, int op) { struct sqlite3 *db = parser->db; struct session *user_session = current_session(); @@ -549,7 +556,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, op); } if (reg_new != 0 && !fkey_action_is_set_null(parser, fk)) { /* @@ -568,7 +575,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, op); } } /* diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c index 1b02ea907..de240c409 100644 --- a/src/box/sql/insert.c +++ b/src/box/sql/insert.c @@ -760,10 +760,9 @@ sqlite3Insert(Parse * pParse, /* Parser context */ */ vdbe_emit_constraint_checks(pParse, pTab, regIns + 1, on_error, endOfLoop, 0); - fkey_emit_check(pParse, pTab, 0, regIns, 0); - vdbe_emit_insertion_completion(v, space, regIns + 1, - pTab->def->field_count, - on_error); + fkey_emit_check(pParse, pTab, 0, regIns, 0, TK_INSERT); + vdbe_emit_insertion_completion(v, space, OP_IdxInsert, + regIns + 1, 0, 0, on_error); } /* Update the count of rows that are inserted @@ -1053,8 +1052,9 @@ process_index: ; } void -vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space, - int raw_data_reg, uint32_t tuple_len, +vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space, int op, + int raw_data_reg, int raw_key_reg, + int upd_cols_reg, enum on_conflict_action on_conflict) { assert(v != NULL); @@ -1065,10 +1065,18 @@ vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space, pik_flags |= OPFLAG_OE_FAIL; else if (on_conflict == ON_CONFLICT_ACTION_ROLLBACK) pik_flags |= OPFLAG_OE_ROLLBACK; - sqlite3VdbeAddOp3(v, OP_MakeRecord, raw_data_reg, tuple_len, - raw_data_reg + tuple_len); - sqlite3VdbeAddOp1(v, OP_IdxInsert, raw_data_reg + tuple_len); - sqlite3VdbeChangeP4(v, -1, (char *)space, P4_SPACEPTR); + if (op == OP_IdxInsert) { + uint32_t tuple_raw_reg = raw_data_reg + space->def->field_count; + sqlite3VdbeAddOp3(v, OP_MakeRecord, raw_data_reg, + space->def->field_count, tuple_raw_reg); + sqlite3VdbeAddOp1(v, op, tuple_raw_reg); + sqlite3VdbeChangeP4(v, -1, (char *)space, P4_SPACEPTR); + } else if (op == OP_IdxUpdate) { + sqlite3VdbeAddOp4(v, op, raw_data_reg, raw_key_reg, + upd_cols_reg, (char *)space, P4_SPACEPTR); + } else { + unreachable(); + } sqlite3VdbeChangeP5(v, pik_flags); } diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h index 1ec52b875..f0cc32229 100644 --- a/src/box/sql/sqliteInt.h +++ b/src/box/sql/sqliteInt.h @@ -3865,13 +3865,21 @@ vdbe_emit_constraint_checks(struct Parse *parse_context, * * @param v Virtual database engine. * @param space Pointer to space object. + * @param op VDBE opcode OP_IdxInsert for INSERT or OP_IdxUpdate + * for UPDATE operation. * @param raw_data_reg Register with raw data to insert. - * @param tuple_len Number of registers to hold the tuple. + * @param raw_key_reg (when op == OP_IdxUpdate) Register with key + * for lookup by primary index. + * @param upd_cols_reg (when op == OP_IdxUpdate) Register with + * blob of updated fields numbers. + * Read OP_IdxUpdate opcode comments for + * more details. * @param on_conflict On conflict action. */ void -vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space, - int raw_data_reg, uint32_t tuple_len, +vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space, int op, + int raw_data_reg, int raw_key_reg, + int upd_cols_reg, enum on_conflict_action on_conflict); void @@ -4730,10 +4738,11 @@ void sqlite3WithPush(Parse *, With *, u8); * @param reg_old Register with deleted row. * @param reg_new Register with inserted row. * @param changed_cols Array of updated columns. Can be NULL. + * @param op operation, one of TK_UPDATE, TK_INSERT, TK_DELETE. */ void fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old, - int reg_new, const int *changed_cols); + int reg_new, const int *changed_cols, int op); /** * Emit VDBE code to do CASCADE, SET NULL or SET DEFAULT actions diff --git a/src/box/sql/update.c b/src/box/sql/update.c index 0e2d0fde8..aca8e4adc 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. @@ -430,23 +434,64 @@ sqlite3Update(Parse * pParse, /* The parser context */ vdbe_emit_constraint_checks(pParse, pTab, regNewPk + 1, on_error, labelContinue, aXRef); /* Do FK constraint checks. */ - 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) - fkey_emit_check(pParse, pTab, 0, regNewPk, aXRef); - vdbe_emit_insertion_completion(v, space, regNew, - pTab->def->field_count, - on_error); + if (hasFK) { + fkey_emit_check(pParse, pTab, regOldPk, 0, aXRef, + TK_UPDATE); + } + 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, + TK_UPDATE); + } + if (on_error == ON_CONFLICT_ACTION_REPLACE) { + vdbe_emit_insertion_completion(v, space, OP_IdxInsert, + regNew, 0, 0, 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; + } + + /* Prapare 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); + vdbe_emit_insertion_completion(v, space, OP_IdxUpdate, + regNew, key_reg, + upd_cols_reg, on_error); + } /* * 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 bf6f4cdd1..534d3e885 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -44,11 +44,13 @@ #include "box/fkey.h" #include "box/txn.h" #include "box/session.h" +#include "box/tuple_format.h" #include "sqliteInt.h" #include "vdbeInt.h" #include "tarantoolInt.h" #include "msgpuck/msgpuck.h" +#include "mpstream.h" #include "box/schema.h" #include "box/space.h" @@ -620,6 +622,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(). @@ -4456,6 +4465,103 @@ case OP_IdxInsert: { goto abort_due_to_error; break; } +/* Opcode: OP_IdxUpdate P1 P2 P3 P4 P5 + * Synopsis: key=r[P1] + * + * Make space Update operation. Primary key fields could not be + * modified - use OP_Replace opcode instead. + * This opcode extends IdxInsert/IdxReplace interface to + * transform to OP_Replace in particular case of sql operator + * "UPDATE or REPLACE". + * + * @param P1 The first field of updated tuple. + * @param P2 Index of a register with index key MakeRecord. + * @param P3 Index of a register with upd_fields blob. + * This blob has size sizeof(uint32_t)*upd_fields_cnt. + * It's items are numbers of fields to be replaced with + * new values from P1 blob. 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_IdxUpdate: { + 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 + 1); + 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; + } + + rc = SQLITE_OK; + if (box_update(space->def->id, 0, key_mem->z, key_mem->z + key_mem->n, + ops, ops + ops_size, TUPLE_INDEX_BASE, NULL) != 0) + rc = SQL_TARANTOOL_ERROR; + + if (pOp->p5 & OPFLAG_OE_IGNORE) { + /* + * Ignore any kind of failes 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 50bc35b2b..69898bbcf 100644 --- a/src/box/sql/vdbeInt.h +++ b/src/box/sql/vdbeInt.h @@ -552,4 +552,10 @@ int sqlite3VdbeRecordCompareMsgpack(const void *key1, struct UnpackedRecord *key2); u32 sqlite3VdbeMsgpackGet(const unsigned char *buf, Mem * pMem); +struct mpstream; + +/** Perform encoding memory variable to stream. */ +void +mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var); + #endif /* !defined(SQLITE_VDBEINT_H) */ diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c index 22beba8be..1fee2673d 100644 --- a/src/box/sql/vdbemem.c +++ b/src/box/sql/vdbemem.c @@ -40,6 +40,7 @@ #include "vdbeInt.h" #include "tarantoolInt.h" #include "box/schema.h" +#include "mpstream.h" #ifdef SQLITE_DEBUG /* @@ -1714,3 +1715,38 @@ sqlite3ValueBytes(sqlite3_value * pVal) return 0; return valueBytes(pVal); } + +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_encode_raw(stream, var->z, var->n); + if (var->flags & MEM_Zero) { + for (int i = 0; i < var->u.nZero; i ++) + mpstream_encode_nil(stream); + } + } +} diff --git a/src/mpstream.c b/src/mpstream.c index e4f7950ba..843fc959e 100644 --- a/src/mpstream.c +++ b/src/mpstream.c @@ -175,3 +175,23 @@ mpstream_encode_bool(struct mpstream *stream, bool val) char *pos = mp_encode_bool(data, val); mpstream_advance(stream, pos - data); } + +void +mpstream_encode_raw(struct mpstream *stream, const char *raw, uint32_t len) +{ + char *data = mpstream_reserve(stream, len); + if (data == NULL) + return; + memcpy(data, raw, len); + mpstream_advance(stream, len); +} + +void +mpstream_encode_binl(struct mpstream *stream, uint32_t len) +{ + char *data = mpstream_reserve(stream, mp_sizeof_binl(len)); + if (data == NULL) + return; + mp_encode_binl(data, len); + mpstream_advance(stream, len); +} diff --git a/src/mpstream.h b/src/mpstream.h index e22d05241..600ab5bec 100644 --- a/src/mpstream.h +++ b/src/mpstream.h @@ -133,6 +133,13 @@ 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 data blob raw of length len directly to stream. */ +void +mpstream_encode_raw(struct mpstream *stream, const char *raw, uint32_t len); + #if defined(__cplusplus) } /* extern "C" */ #endif /* defined(__cplusplus) */ 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 8fbbdcfbc..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,23 +182,22 @@ 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> }) -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; - DELETE FROM t6 WHERE a = 100; - SELECT * FROM t6 WHERE a = 100; ]], { -- <fkey3-3.9> + 1, "FOREIGN KEY constraint failed" -- </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 9e8107aef..d26982297 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..f6d5eaf8f 100755 --- a/test/sql-tap/intpkey.test.lua +++ b/test/sql-tap/intpkey.test.lua @@ -145,11 +145,13 @@ test:do_execsql_test( }) -- Try to change the ROWID for the new entry. +-- Direct update of PK is forbidden -- 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> @@ -281,7 +283,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 +338,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/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] 10+ messages in thread
* [tarantool-patches] Re: [PATCH v1 3/3] sql: do not use OP_Delete+OP_Insert for UPDATES 2018-12-19 15:37 ` [tarantool-patches] [PATCH v1 3/3] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov @ 2018-12-25 17:26 ` n.pettik 2018-12-26 8:35 ` Kirill Shcherbatov 0 siblings, 1 reply; 10+ messages in thread From: n.pettik @ 2018-12-25 17:26 UTC (permalink / raw) To: tarantool-patches; +Cc: Kirill Shcherbatov > @@ -253,15 +246,29 @@ 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); Please, don’t change code without any explanations. You should state it within commit message or in source code. > + if (!(fkey_is_self_referenced(fk_def) && op == TK_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 && > @@ -515,7 +522,7 @@ fkey_action_is_set_null(struct Parse *parse_context, const struct fkey *fkey) > > void > fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old, > - int reg_new, const int *changed_cols) > + int reg_new, const int *changed_cols, int op) Why do you need additional argument? reg_new != 0 for insert reg_old != 0 for delete Changed_cols != 0 for update (According to comment to this function) What is more, according to code you need only case when update is processed. > { > struct sqlite3 *db = parser->db; > struct session *user_session = current_session(); > @@ -549,7 +556,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, op); > } > if (reg_new != 0 && !fkey_action_is_set_null(parser, fk)) { > /* > @@ -568,7 +575,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, op); > } > } > /* > > void > -vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space, > - int raw_data_reg, uint32_t tuple_len, > +vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space, int op, > + int raw_data_reg, int raw_key_reg, > + int upd_cols_reg, > enum on_conflict_action on_conflict) > { > assert(v != NULL); > @@ -1065,10 +1065,18 @@ vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space, > pik_flags |= OPFLAG_OE_FAIL; > else if (on_conflict == ON_CONFLICT_ACTION_ROLLBACK) > pik_flags |= OPFLAG_OE_ROLLBACK; > - sqlite3VdbeAddOp3(v, OP_MakeRecord, raw_data_reg, tuple_len, > - raw_data_reg + tuple_len); > - sqlite3VdbeAddOp1(v, OP_IdxInsert, raw_data_reg + tuple_len); > - sqlite3VdbeChangeP4(v, -1, (char *)space, P4_SPACEPTR); > + if (op == OP_IdxInsert) { > + uint32_t tuple_raw_reg = raw_data_reg + space->def->field_count; > + sqlite3VdbeAddOp3(v, OP_MakeRecord, raw_data_reg, > + space->def->field_count, tuple_raw_reg); > + sqlite3VdbeAddOp1(v, op, tuple_raw_reg); > + sqlite3VdbeChangeP4(v, -1, (char *)space, P4_SPACEPTR); > + } else if (op == OP_IdxUpdate) { > + sqlite3VdbeAddOp4(v, op, raw_data_reg, raw_key_reg, > + upd_cols_reg, (char *)space, P4_SPACEPTR); > + } else { > + unreachable(); > + } I’d better introduce separate function (or simply inline) for emitting update operation. Lets keep vdbe_emit_insertion_completion as is. > sqlite3VdbeChangeP5(v, pik_flags); > } > > > void > @@ -4730,10 +4738,11 @@ void sqlite3WithPush(Parse *, With *, u8); > * @param reg_old Register with deleted row. > * @param reg_new Register with inserted row. > * @param changed_cols Array of updated columns. Can be NULL. > + * @param op operation, one of TK_UPDATE, TK_INSERT, TK_DELETE. > */ > void > fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old, > - int reg_new, const int *changed_cols); > + int reg_new, const int *changed_cols, int op); > > /** > * Emit VDBE code to do CASCADE, SET NULL or SET DEFAULT actions > diff --git a/src/box/sql/update.c b/src/box/sql/update.c > index 0e2d0fde8..aca8e4adc 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. > @@ -430,23 +434,64 @@ sqlite3Update(Parse * pParse, /* The parser context */ > vdbe_emit_constraint_checks(pParse, pTab, regNewPk + 1, > on_error, labelContinue, aXRef); > /* Do FK constraint checks. */ > - 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) > - fkey_emit_check(pParse, pTab, 0, regNewPk, aXRef); > - vdbe_emit_insertion_completion(v, space, regNew, > - pTab->def->field_count, > - on_error); > + if (hasFK) { > + fkey_emit_check(pParse, pTab, regOldPk, 0, aXRef, > + TK_UPDATE); > + } > + 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, > + TK_UPDATE); > + } > + if (on_error == ON_CONFLICT_ACTION_REPLACE) { > + vdbe_emit_insertion_completion(v, space, OP_IdxInsert, > + regNew, 0, 0, on_error); > + } else { > + int key_reg; > + if (okOnePass) { > + key_reg = sqlite3GetTempReg(pParse); > + const char *zAff = Nit: zAff -> aff_str. > + 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; > + } > + > + /* Prapare array of changed fields. */ Nit: prepare. > + 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); I remember that there was issue connected with different register allocation policy (during analyze registers were allocated with sqlite3GetTempReg and simple nMem++). Wouldn’t we face the same problem here? > + sqlite3VdbeAddOp4(v, OP_Blob, upd_cols_sz, upd_cols_reg, > + 0, (const char *)upd_cols, P4_DYNAMIC); > + vdbe_emit_insertion_completion(v, space, OP_IdxUpdate, > + regNew, key_reg, > + upd_cols_reg, on_error); > + } > /* > * 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 bf6f4cdd1..534d3e885 100644 > --- a/src/box/sql/vdbe.c > +++ b/src/box/sql/vdbe.c > > +/* Opcode: OP_IdxUpdate P1 P2 P3 P4 P5 > + * Synopsis: key=r[P1] Hmm, during vibe execution I see no ’synopsis’. What is more, I look at opcodes.h and: /* 114 */ "IdxUpdate" OpHelp("”), So, our script didn’t generate auxiliary information for opcode. Open an issue or investigate this case pls. If you fix it, extend synopsis with other args. > + * > + * Make space Update operation. Primary key fields could not be > + * modified - use OP_Replace opcode instead. > + * This opcode extends IdxInsert/IdxReplace interface to > + * transform to OP_Replace in particular case of sql operator > + * "UPDATE or REPLACE". > + * > + * @param P1 The first field of updated tuple. > + * @param P2 Index of a register with index key MakeRecord. > + * @param P3 Index of a register with upd_fields blob. > + * This blob has size sizeof(uint32_t)*upd_fields_cnt. > + * It's items are numbers of fields to be replaced with > + * new values from P1 blob. They must be sorted in > + * ascending order. I would slightly change comment: - * Make space Update operation. Primary key fields could not be - * modified - use OP_Replace opcode instead. - * This opcode extends IdxInsert/IdxReplace interface to - * transform to OP_Replace in particular case of sql operator - * "UPDATE or REPLACE". - * - * @param P1 The first field of updated tuple. - * @param P2 Index of a register with index key MakeRecord. + * Process UPDATE operation. Primary key fields can not be + * modified - in this case OP_IdxReplace is used instead. + * 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 Encoded key to be passed to box_update(). * @param P3 Index of a register with upd_fields blob. - * This blob has size sizeof(uint32_t)*upd_fields_cnt. * It's items are numbers of fields to be replaced with - * new values from P1 blob. They must be sorted in - * ascending order. + * 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_IdxUpdate: { Don’t call it IdxUpdate - it has nothing in common with index. OP_IdxInsert is called this way due to historical reasons. Lets simply name it 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 + 1); > + 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; > + } > + > + rc = SQLITE_OK; assert(rc == SQLITE_OK); If no error takes place, rc must be 0. Otherwise, error must be handled before. > + if (box_update(space->def->id, 0, key_mem->z, key_mem->z + key_mem->n, > + ops, ops + ops_size, TUPLE_INDEX_BASE, NULL) != 0) I see comment to struct request: /** Base field offset for UPDATE/UPSERT, e.g. 0 for C and 1 for Lua. */ int index_base; So why do you pass 1 instead of 0? > + rc = SQL_TARANTOOL_ERROR; > + > + if (pOp->p5 & OPFLAG_OE_IGNORE) { > + /* > + * Ignore any kind of failes and do not raise Nit: fails. > + * 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 50bc35b2b..69898bbcf 100644 > --- a/src/box/sql/vdbeInt.h > +++ b/src/box/sql/vdbeInt.h > @@ -552,4 +552,10 @@ int sqlite3VdbeRecordCompareMsgpack(const void *key1, > struct UnpackedRecord *key2); > u32 sqlite3VdbeMsgpackGet(const unsigned char *buf, Mem * pMem); > > +struct mpstream; > + > +/** Perform encoding memory variable to stream. */ > +void > +mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var); > + > #endif /* !defined(SQLITE_VDBEINT_H) */ > diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c > index 22beba8be..1fee2673d 100644 > --- a/src/box/sql/vdbemem.c > +++ b/src/box/sql/vdbemem.c > @@ -40,6 +40,7 @@ > #include "vdbeInt.h" > #include "tarantoolInt.h" > #include "box/schema.h" > +#include "mpstream.h" > > #ifdef SQLITE_DEBUG > /* > @@ -1714,3 +1715,38 @@ sqlite3ValueBytes(sqlite3_value * pVal) > return 0; > return valueBytes(pVal); > } > + > +void > +mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var) Can we use this routine to encode MsgPack during execution of OP_MakeRecord? Now it uses sqlite3VdbeMsgpackRecordPut() which is almost the same. > diff --git a/src/mpstream.c b/src/mpstream.c > index e4f7950ba..843fc959e 100644 > --- a/src/mpstream.c > +++ b/src/mpstream.c > @@ -175,3 +175,23 @@ mpstream_encode_bool(struct mpstream *stream, bool val) > char *pos = mp_encode_bool(data, val); > mpstream_advance(stream, pos - data); > } > + > +void > +mpstream_encode_raw(struct mpstream *stream, const char *raw, uint32_t len) Hm, it doesn’t encode in fact. Mb better call _copy_raw? > +{ > + char *data = mpstream_reserve(stream, len); > + if (data == NULL) > + return; > + memcpy(data, raw, len); > + mpstream_advance(stream, len); > +} > > #if defined(__cplusplus) > } /* extern "C" */ > #endif /* defined(__cplusplus) */ At this point I didn’t review test changes. ^ permalink raw reply [flat|nested] 10+ messages in thread
* [tarantool-patches] Re: [PATCH v1 3/3] sql: do not use OP_Delete+OP_Insert for UPDATES 2018-12-25 17:26 ` [tarantool-patches] " n.pettik @ 2018-12-26 8:35 ` Kirill Shcherbatov 2018-12-28 14:17 ` n.pettik 0 siblings, 1 reply; 10+ messages in thread From: Kirill Shcherbatov @ 2018-12-26 8:35 UTC (permalink / raw) To: tarantool-patches, Nikita Pettik Hi thank for review! Rebased patch to a new master state. I've also removed "Needed for #3850" mark from the first commit. > Please, don’t change code without any explanations. > You should state it within commit message or in source code. /** * 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)) { > Why do you need additional argument? > reg_new != 0 for insert > reg_old != 0 for delete > Changed_cols != 0 for update > (According to comment to this function) Ok, I've introduced bool is_update = changed_cols != 0; passing as argument in fkey_lookup_parent. > I’d better introduce separate function (or simply inline) for emitting > update operation. Lets keep vdbe_emit_insertion_completion as is. I've reworked this code as separete vdbe_emit_update_completion. I didn't it before because of same prolog an epilog in thouse routines. > Nit: zAff -> aff_str. > Nit: prepare. Tnx, fixed. > I remember that there was issue connected with different register > allocation policy (during analyze registers were allocated with > sqlite3GetTempReg and simple nMem++). Wouldn’t we face > the same problem here? No, thous situation was too specific, manually changing allocation size in a cycle and sqlite3GetTempReg inside. > Hmm, during vibe execution I see no ’synopsis’. > What is more, I look at opcodes.h and: > /* 114 */ "IdxUpdate" OpHelp("”), > > So, our script didn’t generate auxiliary information for opcode. > Open an issue or investigate this case pls. > If you fix it, extend synopsis with other args. I's my fault: not needed to write "Opcode: OP_Update P1 P2 P3 P4 P5", instead of "Opcode: Update P1 P2 P3 P4 P5" > I would slightly change comment: Applied. > Don’t call it IdxUpdate - it has nothing in common with index. > OP_IdxInsert is called this way due to historical reasons. > Lets simply name it OP_Update. Don't mind. /* 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. */ > assert(rc == SQLITE_OK); > > If no error takes place, rc must be 0. > Otherwise, error must be handled before. Ok > So why do you pass 1 instead of 0? It is possible, by it doesn't really matter. I've encoded a msgpack like LUA does. May specify 0 if you like it more: - mpstream_encode_uint(&stream, field_idx + 1); + mpstream_encode_uint(&stream, field_idx); - ops, ops + ops_size, TUPLE_INDEX_BASE, NULL) != 0) + ops, ops + ops_size, 0, NULL) != 0) I don't need TUPLE_INDEX_BASE from tuple_format.h in this case -#include "box/tuple_format.h" > Nit: fails. Ok. > Can we use this routine to encode MsgPack during execution of OP_MakeRecord? > Now it uses sqlite3VdbeMsgpackRecordPut() which is almost the same. Yep, but it requires previous sqlite3VdbeMsgpackRecordLen() but region allocation doesn't. For example, as a part of #3545 task we reworked same code with region. I believe that this is much better and moreover sqlite3VdbeMsgpackRecordLen+sqlite3VdbeMsgpackRecordPut calls should be reworked with mpstream_encode_vdbe_mem where possible (maybe it worth to create such task). > Hm, it doesn’t encode in fact. Mb better call _copy_raw? Don't mind. ====================================================== 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. Closes #3850 --- src/box/sql/fkey.c | 58 ++++--- src/box/sql/update.c | 103 ++++++++--- src/box/sql/vdbe.c | 108 ++++++++++++ src/box/sql/vdbeInt.h | 6 + src/box/sql/vdbemem.c | 36 ++++ src/mpstream.c | 20 +++ src/mpstream.h | 8 + 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 +- 42 files changed, 680 insertions(+), 363 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/src/box/sql/update.c b/src/box/sql/update.c index 0e2d0fde8..b45bbf5d3 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 @@ -65,6 +66,25 @@ sqlite3ColumnDefault(Vdbe *v, struct space_def *def, int i, int ireg) } } +void +vdbe_emit_update_completion(struct Vdbe *v, struct space *space, + int raw_data_reg, int raw_key_reg, int upd_cols_reg, + enum on_conflict_action on_conflict) +{ + assert(v != NULL); + u16 pik_flags = OPFLAG_NCHANGE; + assert(on_conflict != ON_CONFLICT_ACTION_REPLACE); + 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; + sqlite3VdbeAddOp4(v, OP_Update, raw_data_reg, raw_key_reg, + upd_cols_reg, (char *)space, P4_SPACEPTR); + sqlite3VdbeChangeP5(v, pik_flags); +} + /* * Process an UPDATE statement. * @@ -112,6 +132,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 +205,7 @@ sqlite3Update(Parse * pParse, /* The parser context */ goto update_cleanup; } aXRef[j] = i; + upd_cols_cnt++; break; } } @@ -216,7 +239,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 +297,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 +453,63 @@ 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); + vdbe_emit_update_completion(v, space, regNew, key_reg, + upd_cols_reg, on_error); + } /* * 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 e6b413c70..7af86872b 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" @@ -620,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(). @@ -4457,6 +4465,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 50bc35b2b..69898bbcf 100644 --- a/src/box/sql/vdbeInt.h +++ b/src/box/sql/vdbeInt.h @@ -552,4 +552,10 @@ int sqlite3VdbeRecordCompareMsgpack(const void *key1, struct UnpackedRecord *key2); u32 sqlite3VdbeMsgpackGet(const unsigned char *buf, Mem * pMem); +struct mpstream; + +/** Perform encoding memory variable to stream. */ +void +mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var); + #endif /* !defined(SQLITE_VDBEINT_H) */ diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c index 22beba8be..f7776f68b 100644 --- a/src/box/sql/vdbemem.c +++ b/src/box/sql/vdbemem.c @@ -40,6 +40,7 @@ #include "vdbeInt.h" #include "tarantoolInt.h" #include "box/schema.h" +#include "mpstream.h" #ifdef SQLITE_DEBUG /* @@ -1714,3 +1715,38 @@ sqlite3ValueBytes(sqlite3_value * pVal) return 0; return valueBytes(pVal); } + +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_encode_copy_raw(stream, var->z, var->n); + if (var->flags & MEM_Zero) { + for (int i = 0; i < var->u.nZero; i ++) + mpstream_encode_nil(stream); + } + } +} diff --git a/src/mpstream.c b/src/mpstream.c index e4f7950ba..2380db109 100644 --- a/src/mpstream.c +++ b/src/mpstream.c @@ -175,3 +175,23 @@ mpstream_encode_bool(struct mpstream *stream, bool val) char *pos = mp_encode_bool(data, val); mpstream_advance(stream, pos - data); } + +void +mpstream_encode_copy_raw(struct mpstream *stream, const char *raw, uint32_t len) +{ + char *data = mpstream_reserve(stream, len); + if (data == NULL) + return; + memcpy(data, raw, len); + mpstream_advance(stream, len); +} + +void +mpstream_encode_binl(struct mpstream *stream, uint32_t len) +{ + char *data = mpstream_reserve(stream, mp_sizeof_binl(len)); + if (data == NULL) + return; + mp_encode_binl(data, len); + mpstream_advance(stream, len); +} diff --git a/src/mpstream.h b/src/mpstream.h index e22d05241..bf8b62731 100644 --- a/src/mpstream.h +++ b/src/mpstream.h @@ -133,6 +133,14 @@ 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 data blob raw of length len directly to stream. */ +void +mpstream_encode_copy_raw(struct mpstream *stream, const char *raw, + uint32_t len); + #if defined(__cplusplus) } /* extern "C" */ #endif /* defined(__cplusplus) */ 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 8fbbdcfbc..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,23 +182,22 @@ 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> }) -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; - DELETE FROM t6 WHERE a = 100; - SELECT * FROM t6 WHERE a = 100; ]], { -- <fkey3-3.9> + 1, "FOREIGN KEY constraint failed" -- </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] 10+ messages in thread
* [tarantool-patches] Re: [PATCH v1 3/3] sql: do not use OP_Delete+OP_Insert for UPDATES 2018-12-26 8:35 ` Kirill Shcherbatov @ 2018-12-28 14:17 ` n.pettik 0 siblings, 0 replies; 10+ messages in thread From: n.pettik @ 2018-12-28 14:17 UTC (permalink / raw) To: tarantool-patches; +Cc: Kirill Shcherbatov >> Please, don’t change code without any explanations. >> You should state it within commit message or in source code. > /** > * 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)) { So, AFAIU this part can be moved into separate commit which fixes fk+update behaviour. I mean, would it work with old update which consists of insert+delete? >> I’d better introduce separate function (or simply inline) for emitting >> update operation. Lets keep vdbe_emit_insertion_completion as is. > I've reworked this code as separete vdbe_emit_update_completion. I didn't it before because of > same prolog an epilog in thouse routines. Ok, then you can do it like this: 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..281f9da32 100644 --- a/src/box/sql/sqliteInt.h +++ b/src/box/sql/sqliteInt.h @@ -2847,6 +2847,16 @@ struct Parse { #define OPFLAG_SYSTEMSP 0x20 /* OP_Open**: set if space pointer * points to system space. */ + +#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 b45bbf5d3..dbef22fd8 100644 --- a/src/box/sql/update.c +++ b/src/box/sql/update.c @@ -66,25 +66,6 @@ sqlite3ColumnDefault(Vdbe *v, struct space_def *def, int i, int ireg) } } -void -vdbe_emit_update_completion(struct Vdbe *v, struct space *space, - int raw_data_reg, int raw_key_reg, int upd_cols_reg, - enum on_conflict_action on_conflict) -{ - assert(v != NULL); - u16 pik_flags = OPFLAG_NCHANGE; - assert(on_conflict != ON_CONFLICT_ACTION_REPLACE); - 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; - sqlite3VdbeAddOp4(v, OP_Update, raw_data_reg, raw_key_reg, - upd_cols_reg, (char *)space, P4_SPACEPTR); - sqlite3VdbeChangeP5(v, pik_flags); -} - /* * Process an UPDATE statement. * @@ -507,8 +488,12 @@ sqlite3Update(Parse * pParse, /* The parser context */ int upd_cols_reg = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp4(v, OP_Blob, upd_cols_sz, upd_cols_reg, 0, (const char *)upd_cols, P4_DYNAMIC); - vdbe_emit_update_completion(v, space, regNew, key_reg, - upd_cols_reg, on_error); + 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); So I would even prefer simple inlining to static function. >> Can we use this routine to encode MsgPack during execution of OP_MakeRecord? >> Now it uses sqlite3VdbeMsgpackRecordPut() which is almost the same. > Yep, but it requires previous sqlite3VdbeMsgpackRecordLen() but region allocation > doesn’t. So? In some cases it is enough to allocate memory on region (see bIsEphemeral flag in OP_MakeRecord). In the rest situations you can copy it to malloc after all. You may argue that in this case we will gain 2x memory consumption. However, sqlite3VdbeMsgpackRecordLen() uses very rough heuristic to estimate size for msgpack and in some cases it may result in 5-6 times larger memory chunks: https://github.com/tarantool/tarantool/issues/3035 All points considered, I suggest to replace all calls of sqlite3VdbeMsgpackRecordPut() with mpstream as a preparation patch (and close #3035). I guess, it shouldn’t be too complicated to implement. > For example, as a part of #3545 task we reworked same code with region. > I believe that this is much better and moreover > sqlite3VdbeMsgpackRecordLen+sqlite3VdbeMsgpackRecordPut calls should be > reworked with mpstream_encode_vdbe_mem where possible (maybe it worth to > create such task). There are only two places where these functions are called: OP_MakeRecord and encoding msgpack key to pass it to the index iterator. > ====================================================== > > Introduced a new OP_Update opcode making Tarantool native Update > operation. Nit: not making but processing/handling/executing etc. > 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 > ^ permalink raw reply [flat|nested] 10+ messages in thread
* [tarantool-patches] Re: [PATCH v1 0/3] sql: do not use OP_Delete+OP_Insert for UPDATES 2018-12-19 15:37 [tarantool-patches] [PATCH v1 0/3] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov ` (2 preceding siblings ...) 2018-12-19 15:37 ` [tarantool-patches] [PATCH v1 3/3] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov @ 2018-12-20 20:41 ` Vladislav Shpilevoy 3 siblings, 0 replies; 10+ messages in thread From: Vladislav Shpilevoy @ 2018-12-20 20:41 UTC (permalink / raw) To: tarantool-patches, Kirill Shcherbatov, Nikita Pettik Nikita, please, do first review. On 19/12/2018 18:37, 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. > > Branch: http://github.com/tarantool/tarantool/tree/kshch/gh-3850-op-update > Issue: https://github.com/tarantool/tarantool/issues/3850 > > Kirill Shcherbatov (3): > sql: clean-up vdbe_emit_constraint_checks > sql: fix sql_vdbe_mem_alloc_region result memory > sql: do not use OP_Delete+OP_Insert for UPDATES > > src/box/sql/delete.c | 3 +- > src/box/sql/fkey.c | 49 +++--- > src/box/sql/insert.c | 49 ++---- > src/box/sql/sqliteInt.h | 17 +- > src/box/sql/update.c | 81 +++++++-- > src/box/sql/vdbe.c | 106 ++++++++++++ > src/box/sql/vdbeInt.h | 6 + > src/box/sql/vdbeaux.c | 3 +- > src/box/sql/vdbemem.c | 36 ++++ > src/mpstream.c | 20 +++ > src/mpstream.h | 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 | 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 | 10 +- > 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/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 +- > 44 files changed, 660 insertions(+), 384 deletions(-) > ^ permalink raw reply [flat|nested] 10+ messages in thread
end of thread, other threads:[~2018-12-28 14:17 UTC | newest] Thread overview: 10+ messages (download: mbox.gz / follow: Atom feed) -- links below jump to the message on this page -- 2018-12-19 15:37 [tarantool-patches] [PATCH v1 0/3] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov 2018-12-19 15:37 ` [tarantool-patches] [PATCH v1 1/3] sql: clean-up vdbe_emit_constraint_checks Kirill Shcherbatov 2018-12-25 17:26 ` [tarantool-patches] " n.pettik 2018-12-19 15:37 ` [tarantool-patches] [PATCH v1 2/3] sql: fix sql_vdbe_mem_alloc_region result memory Kirill Shcherbatov 2018-12-25 17:26 ` [tarantool-patches] " n.pettik 2018-12-19 15:37 ` [tarantool-patches] [PATCH v1 3/3] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov 2018-12-25 17:26 ` [tarantool-patches] " n.pettik 2018-12-26 8:35 ` Kirill Shcherbatov 2018-12-28 14:17 ` n.pettik 2018-12-20 20:41 ` [tarantool-patches] Re: [PATCH v1 0/3] " Vladislav Shpilevoy
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox