* [tarantool-patches] [PATCH v2 5/5] sql: do not use OP_Delete+OP_Insert for UPDATES
2018-12-29 10:48 [tarantool-patches] [PATCH v2 0/5] sql: do not use OP_Delete+OP_Insert for UPDATES Kirill Shcherbatov
` (3 preceding siblings ...)
2018-12-29 10:49 ` [tarantool-patches] [PATCH v2 4/5] sql: encode tuples with mpstream on Vdbe run Kirill Shcherbatov
@ 2018-12-29 10:49 ` Kirill Shcherbatov
2018-12-29 13:35 ` [tarantool-patches] " n.pettik
2019-01-10 12:30 ` [tarantool-patches] Re: [PATCH v2 0/5] " Kirill Yukhin
5 siblings, 1 reply; 13+ messages in thread
From: Kirill Shcherbatov @ 2018-12-29 10:49 UTC (permalink / raw)
To: tarantool-patches, korablev; +Cc: Kirill Shcherbatov
Introduced a new OP_Update opcode executing Tarantool native
Update operation.
In case of UPDATE or REPLACE we can't use new OP_Update as it
has a complex SQL-specific semantics:
CREATE TABLE tj (s1 INT PRIMARY KEY, s2 INT);
INSERT INTO tj VALUES (1, 3),(2, 4),(3,5);
CREATE UNIQUE INDEX i ON tj (s2);
SELECT * FROM tj;
[1, 3], [2, 4], [3, 5]
UPDATE OR REPLACE tj SET s2 = s2 + 1;
SELECT * FROM tj;
[1, 4], [3, 6]
I.e. [1, 3] tuple is updated as [1, 4] and have replaced tuple
[2, 4]. This logic is implemented as preventive tuples deletion
by all corresponding indexes in SQL.
The other significant change is forbidden primary key update.
It was possible to deal with it the same way like with or
REPLACE specifier but we need an atomic UPDATE step for #3691
ticket to support "or IGNORE/or ABORT/or FAIL" specifiers.
Reworked tests to make testing avoiding primary key UPDATE where
possible.
Closes #3850
---
src/box/sql/insert.c | 7 +-
src/box/sql/sqliteInt.h | 14 ++
src/box/sql/update.c | 88 +++++++---
src/box/sql/vdbe.c | 107 ++++++++++++
src/box/sql/vdbeInt.h | 8 +
src/box/sql/vdbemem.c | 7 +-
test/sql-tap/alter2.test.lua | 16 +-
test/sql-tap/analyze9.test.lua | 43 ++++-
test/sql-tap/bigrow1.test.lua | 12 +-
test/sql-tap/check.test.lua | 14 +-
test/sql-tap/fkey2.test.lua | 161 +++++++++---------
test/sql-tap/fkey3.test.lua | 20 +--
test/sql-tap/fkey4.test.lua | 32 ++--
test/sql-tap/gh2140-trans.test.lua | 26 +--
.../gh2250-trigger-chain-limit.test.lua | 8 +-
test/sql-tap/gh2259-in-stmt-trans.test.lua | 12 +-
test/sql-tap/identifier_case.test.lua | 2 +-
test/sql-tap/index6.test.lua | 5 +-
test/sql-tap/intpkey.test.lua | 23 ++-
test/sql-tap/misc1.test.lua | 20 ++-
test/sql-tap/quote.test.lua | 6 +-
test/sql-tap/table.test.lua | 2 +-
test/sql-tap/tkt-a8a0d2996a.test.lua | 4 +-
test/sql-tap/tkt2767.test.lua | 16 +-
test/sql-tap/tkt2832.test.lua | 10 +-
test/sql-tap/tkt3554.test.lua | 15 +-
test/sql-tap/trigger2.test.lua | 28 +--
test/sql-tap/trigger7.test.lua | 2 +-
test/sql-tap/triggerB.test.lua | 28 +--
test/sql-tap/triggerC.test.lua | 43 ++---
test/sql-tap/triggerD.test.lua | 4 +-
test/sql-tap/update.test.lua | 31 ++--
test/sql-tap/with1.test.lua | 24 +--
test/sql/collation.result | 12 +-
test/sql/collation.test.lua | 12 +-
test/sql/on-conflict.result | 8 +-
test/sql/on-conflict.test.lua | 8 +-
test/sql/row-count.result | 10 +-
test/sql/row-count.test.lua | 10 +-
test/sql/triggers.result | 16 +-
test/sql/triggers.test.lua | 16 +-
41 files changed, 580 insertions(+), 350 deletions(-)
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index 1b02ea907..f147f6a50 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -1059,12 +1059,7 @@ vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space,
{
assert(v != NULL);
u16 pik_flags = OPFLAG_NCHANGE;
- if (on_conflict == ON_CONFLICT_ACTION_IGNORE)
- pik_flags |= OPFLAG_OE_IGNORE;
- else if (on_conflict == ON_CONFLICT_ACTION_FAIL)
- pik_flags |= OPFLAG_OE_FAIL;
- else if (on_conflict == ON_CONFLICT_ACTION_ROLLBACK)
- pik_flags |= OPFLAG_OE_ROLLBACK;
+ SET_CONFLICT_FLAG(pik_flags, on_conflict);
sqlite3VdbeAddOp3(v, OP_MakeRecord, raw_data_reg, tuple_len,
raw_data_reg + tuple_len);
sqlite3VdbeAddOp1(v, OP_IdxInsert, raw_data_reg + tuple_len);
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 4110a5991..de0b6f05b 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -2847,6 +2847,20 @@ struct Parse {
#define OPFLAG_SYSTEMSP 0x20 /* OP_Open**: set if space pointer
* points to system space.
*/
+
+/**
+ * Prepare vdbe P5 flags for OP_{IdxInsert, IdxReplace, Update}
+ * by on_conflict action.
+ */
+#define SET_CONFLICT_FLAG(opflag, on_conflict) do { \
+ if (on_conflict == ON_CONFLICT_ACTION_IGNORE) \
+ opflag |= OPFLAG_OE_IGNORE; \
+ else if (on_conflict == ON_CONFLICT_ACTION_FAIL) \
+ opflag |= OPFLAG_OE_FAIL; \
+ else if (on_conflict == ON_CONFLICT_ACTION_ROLLBACK) \
+ opflag |= OPFLAG_OE_ROLLBACK; \
+} while (0)
+
/* OP_RowData: xferOptimization started processing */
#ifdef SQLITE_TEST
#define OPFLAG_XFER_OPT 0x01
diff --git a/src/box/sql/update.c b/src/box/sql/update.c
index 0e2d0fde8..dbef22fd8 100644
--- a/src/box/sql/update.c
+++ b/src/box/sql/update.c
@@ -36,6 +36,7 @@
#include "sqliteInt.h"
#include "box/session.h"
#include "tarantoolInt.h"
+#include "box/tuple_format.h"
#include "box/schema.h"
void
@@ -112,6 +113,8 @@ sqlite3Update(Parse * pParse, /* The parser context */
int regNew = 0; /* Content of the NEW.* table in triggers */
int regOld = 0; /* Content of OLD.* table in triggers */
int regKey = 0; /* composite PRIMARY KEY value */
+ /* Count of changed rows. Match aXRef items != -1. */
+ int upd_cols_cnt = 0;
db = pParse->db;
if (pParse->nErr || db->mallocFailed) {
@@ -183,6 +186,7 @@ sqlite3Update(Parse * pParse, /* The parser context */
goto update_cleanup;
}
aXRef[j] = i;
+ upd_cols_cnt++;
break;
}
}
@@ -216,7 +220,7 @@ sqlite3Update(Parse * pParse, /* The parser context */
regNewPk = ++pParse->nMem;
}
regNew = pParse->nMem + 1;
- pParse->nMem += def->field_count;
+ pParse->nMem += def->field_count + 1;
/* If we are trying to update a view, realize that view into
* an ephemeral table.
@@ -274,11 +278,11 @@ sqlite3Update(Parse * pParse, /* The parser context */
nKey = pk_part_count;
regKey = iPk;
} else {
- const char *zAff = is_view ? 0 :
- sql_space_index_affinity_str(pParse->db, def,
- pPk->def);
+ const char *aff_str =
+ is_view ? 0 :
+ sql_space_index_affinity_str(pParse->db, def, pPk->def);
sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, pk_part_count,
- regKey, zAff, pk_part_count);
+ regKey, aff_str, pk_part_count);
/*
* Set flag to save memory allocating one by
* malloc.
@@ -430,23 +434,67 @@ sqlite3Update(Parse * pParse, /* The parser context */
vdbe_emit_constraint_checks(pParse, pTab, regNewPk + 1,
on_error, labelContinue, aXRef);
/* Do FK constraint checks. */
- if (hasFK)
+ if (hasFK) {
fkey_emit_check(pParse, pTab, regOldPk, 0, aXRef);
- /*
- * Delete the index entries associated with the
- * current record. It can be already removed by
- * trigger or REPLACE conflict action.
- */
- int addr1 = sqlite3VdbeAddOp4Int(v, OP_NotFound, pk_cursor, 0,
- regKey, nKey);
- assert(regNew == regNewPk + 1);
- sqlite3VdbeAddOp2(v, OP_Delete, pk_cursor, 0);
- sqlite3VdbeJumpHere(v, addr1);
- if (hasFK)
+ }
+ if (on_error == ON_CONFLICT_ACTION_REPLACE) {
+ /*
+ * Delete the index entries associated with the
+ * current record. It can be already removed by
+ * trigger or REPLACE conflict action.
+ */
+ int not_found_lbl =
+ sqlite3VdbeAddOp4Int(v, OP_NotFound, pk_cursor,
+ 0, regKey, nKey);
+ assert(regNew == regNewPk + 1);
+ sqlite3VdbeAddOp2(v, OP_Delete, pk_cursor, 0);
+ sqlite3VdbeJumpHere(v, not_found_lbl);
+ }
+ if (hasFK) {
fkey_emit_check(pParse, pTab, 0, regNewPk, aXRef);
- vdbe_emit_insertion_completion(v, space, regNew,
- pTab->def->field_count,
- on_error);
+ }
+ if (on_error == ON_CONFLICT_ACTION_REPLACE) {
+ vdbe_emit_insertion_completion(v, space, regNew,
+ pTab->def->field_count,
+ on_error);
+
+ } else {
+ int key_reg;
+ if (okOnePass) {
+ key_reg = sqlite3GetTempReg(pParse);
+ const char *zAff =
+ sql_space_index_affinity_str(pParse->db,
+ def,
+ pPk->def);
+ sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk,
+ pk_part_count, key_reg, zAff,
+ pk_part_count);
+ } else {
+ assert(nKey == 0);
+ key_reg = regKey;
+ }
+
+ /* Prepare array of changed fields. */
+ uint32_t upd_cols_sz = upd_cols_cnt * sizeof(uint32_t);
+ uint32_t *upd_cols = sqlite3DbMallocRaw(db, upd_cols_sz);
+ if (upd_cols == NULL)
+ goto update_cleanup;
+ upd_cols_cnt = 0;
+ for (uint32_t i = 0; i < def->field_count; i++) {
+ if (aXRef[i] == -1)
+ continue;
+ upd_cols[upd_cols_cnt++] = i;
+ }
+ int upd_cols_reg = sqlite3GetTempReg(pParse);
+ sqlite3VdbeAddOp4(v, OP_Blob, upd_cols_sz, upd_cols_reg,
+ 0, (const char *)upd_cols, P4_DYNAMIC);
+ u16 pik_flags = OPFLAG_NCHANGE;
+ SET_CONFLICT_FLAG(pik_flags, on_error);
+ sqlite3VdbeAddOp4(v, OP_Update, regNew, key_reg,
+ upd_cols_reg, (char *)space,
+ P4_SPACEPTR);
+ sqlite3VdbeChangeP5(v, pik_flags);
+ }
/*
* Do any ON CASCADE, SET NULL or SET DEFAULT
* operations required to handle rows that refer
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index b8faa8f45..5bbcd58d1 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -621,6 +621,13 @@ vdbe_add_new_autoinc_id(struct Vdbe *vdbe, int64_t id)
return 0;
}
+/** Callback to forward and error from mpstream methods. */
+static inline void
+mpstream_encode_error(void *error_ctx)
+{
+ *(bool *)error_ctx = true;
+}
+
/*
* Execute as much of a VDBE program as we can.
* This is the core of sqlite3_step().
@@ -4464,6 +4471,106 @@ case OP_IdxInsert: {
break;
}
+/* Opcode: Update P1 P2 P3 P4 P5
+ * Synopsis: key=r[P1]
+ *
+ * Process UPDATE operation. Primary key fields can not be
+ * modified.
+ * Under the hood it performs box_update() call.
+ * For the performance sake, it takes whole affected row (P1)
+ * and encodes into msgpack only fields to be updated (P3).
+ *
+ * @param P1 The first field to be updated. Fields are located
+ * in the range of [P1...] in decoded state.
+ * Encoded only fields which numbers are presented
+ * in @P3 array.
+ * @param P2 P2 Encoded key to be passed to box_update().
+ * @param P3 Index of a register with upd_fields blob.
+ * It's items are numbers of fields to be replaced with
+ * new values from P1. They must be sorted in ascending
+ * order.
+ * @param P4 Pointer to the struct space to be updated.
+ * @param P5 Flags. If P5 contains OPFLAG_NCHANGE, then VDBE
+ * accounts the change in a case of successful
+ * insertion in nChange counter. If P5 contains
+ * OPFLAG_OE_IGNORE, then we are processing INSERT OR
+ * INGORE statement. Thus, in case of conflict we don't
+ * raise an error.
+ */
+case OP_Update: {
+ struct Mem *new_tuple = &aMem[pOp->p1];
+ if (pOp->p5 & OPFLAG_NCHANGE)
+ p->nChange++;
+
+ struct space *space = pOp->p4.space;
+ assert(pOp->p4type == P4_SPACEPTR);
+
+ struct Mem *key_mem = &aMem[pOp->p2];
+ assert((key_mem->flags & MEM_Blob) != 0);
+
+ struct Mem *upd_fields_mem = &aMem[pOp->p3];
+ assert((upd_fields_mem->flags & MEM_Blob) != 0);
+ uint32_t *upd_fields = (uint32_t *)upd_fields_mem->z;
+ uint32_t upd_fields_cnt = upd_fields_mem->n / sizeof(uint32_t);
+
+ /* Prepare Tarantool update ops msgpack. */
+ struct region *region = &fiber()->gc;
+ size_t used = region_used(region);
+ bool is_error = false;
+ struct mpstream stream;
+ mpstream_init(&stream, region, region_reserve_cb, region_alloc_cb,
+ mpstream_encode_error, &is_error);
+ mpstream_encode_array(&stream, upd_fields_cnt);
+ for (uint32_t i = 0; i < upd_fields_cnt; i++) {
+ uint32_t field_idx = upd_fields[i];
+ assert(field_idx < space->def->field_count);
+ mpstream_encode_array(&stream, 3);
+ mpstream_encode_strn(&stream, "=", 1);
+ mpstream_encode_uint(&stream, field_idx);
+ mpstream_encode_vdbe_mem(&stream, new_tuple + field_idx);
+ }
+ mpstream_flush(&stream);
+ if (is_error) {
+ diag_set(OutOfMemory, stream.pos - stream.buf,
+ "mpstream_flush", "stream");
+ rc = SQL_TARANTOOL_ERROR;
+ goto abort_due_to_error;
+ }
+ uint32_t ops_size = region_used(region) - used;
+ const char *ops = region_join(region, ops_size);
+ if (ops == NULL) {
+ diag_set(OutOfMemory, ops_size, "region_join", "raw");
+ rc = SQL_TARANTOOL_ERROR;
+ goto abort_due_to_error;
+ }
+
+ assert(rc == SQLITE_OK);
+ if (box_update(space->def->id, 0, key_mem->z, key_mem->z + key_mem->n,
+ ops, ops + ops_size, 0, NULL) != 0)
+ rc = SQL_TARANTOOL_ERROR;
+
+ if (pOp->p5 & OPFLAG_OE_IGNORE) {
+ /*
+ * Ignore any kind of fails and do not raise
+ * error message
+ */
+ rc = SQLITE_OK;
+ /*
+ * If we are in trigger, increment ignore raised
+ * counter.
+ */
+ if (p->pFrame)
+ p->ignoreRaised++;
+ } else if (pOp->p5 & OPFLAG_OE_FAIL) {
+ p->errorAction = ON_CONFLICT_ACTION_FAIL;
+ } else if (pOp->p5 & OPFLAG_OE_ROLLBACK) {
+ p->errorAction = ON_CONFLICT_ACTION_ROLLBACK;
+ }
+ if (rc != 0)
+ goto abort_due_to_error;
+ break;
+}
+
/* Opcode: SInsert P1 P2 P3 * P5
* Synopsis: space id = P1, key = r[P3], on error goto P2
*
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index fcb47455b..66362c465 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -560,6 +560,14 @@ set_encode_error(void *error_ctx)
*(bool *)error_ctx = true;
}
+/**
+ * Perform encoding memory variable to stream.
+ * @param stream Initialized mpstream encoder object.
+ * @param var Vdbe memory variable to encode with stream.
+ */
+void
+mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var);
+
/**
* Perform encoding field_count Vdbe memory fields on region as
* msgpack array.
diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
index 3f171766e..7b21f2aaa 100644
--- a/src/box/sql/vdbemem.c
+++ b/src/box/sql/vdbemem.c
@@ -1717,12 +1717,7 @@ sqlite3ValueBytes(sqlite3_value * pVal)
return valueBytes(pVal);
}
-/**
- * Perform encoding memory variable to stream.
- * @param stream Initialized mpstream encoder object.
- * @param var Vdbe memory variable to encode with stream.
- */
-static void
+void
mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var)
{
assert(memIsValid(var));
diff --git a/test/sql-tap/alter2.test.lua b/test/sql-tap/alter2.test.lua
index d13cfb7a0..e1dd0ff1b 100755
--- a/test/sql-tap/alter2.test.lua
+++ b/test/sql-tap/alter2.test.lua
@@ -204,13 +204,13 @@ test:do_execsql_test(
[[
DROP TABLE child;
DROP TABLE parent;
- CREATE TABLE child (id INT PRIMARY KEY, a INT, b INT);
- CREATE TABLE parent (id INT PRIMARY KEY, c INT, d INT);
- ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES parent ON UPDATE CASCADE MATCH PARTIAL;
- INSERT INTO parent VALUES(1, 2, 3), (3, 4, 5), (6, 7, 8);
- INSERT INTO child VALUES(1, 1, 1), (3, 2, 2);
+ CREATE TABLE child (id INT UNIQUE, a INT, b INT, z INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE parent (id INT UNIQUE, c INT, d INT, z INT PRIMARY KEY AUTOINCREMENT);
+ ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES parent(id) ON UPDATE CASCADE MATCH PARTIAL;
+ INSERT INTO parent(id, c, d) VALUES(1, 2, 3), (3, 4, 5), (6, 7, 8);
+ INSERT INTO child(id, a, b) VALUES(1, 1, 1), (3, 2, 2);
UPDATE parent SET id = 5 WHERE id = 1;
- SELECT * FROM CHILD;
+ SELECT id,a,b FROM CHILD ORDER BY id,a,b;
]], {
-- <alter2-3.2>
3, 2, 2, 5, 1, 1
@@ -220,7 +220,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"alter2-4.1",
[[
- ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY REFERENCES child;
+ ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY REFERENCES child(id);
]], {
-- <alter2-4.1>
1, "near \"REFERENCES\": syntax error"
@@ -230,7 +230,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"alter2-4.2",
[[
- ALTER TABLE child ADD CONSTRAINT fk () FOREIGN KEY REFERENCES child;
+ ALTER TABLE child ADD CONSTRAINT fk () FOREIGN KEY REFERENCES child(id);
]], {
-- <alter2-4.1>
1, "near \"(\": syntax error"
diff --git a/test/sql-tap/analyze9.test.lua b/test/sql-tap/analyze9.test.lua
index 585e91851..df62a1624 100755
--- a/test/sql-tap/analyze9.test.lua
+++ b/test/sql-tap/analyze9.test.lua
@@ -405,6 +405,27 @@ test:do_execsql_test(
-- The following tests experiment with adding corrupted records to the
-- 'sample' column of the _sql_stat4 table.
--
+local get_pk = function (space, record)
+ local pkey = {}
+ for _, part in pairs(space.index[0].parts) do
+ table.insert(pkey, record[part.fieldno])
+ end
+ return pkey
+end
+
+local inject_stat_error_func = function (space_name)
+ local space = box.space[space_name]
+ local record = space:select({"T1", "I1", nil}, {limit = 1})[1]
+ space:delete(get_pk(space, record))
+ local record_new = {}
+ for i = 1,#record-1 do record_new[i] = record[i] end
+ record_new[#record] = ''
+ space:insert(record_new)
+ return 0
+end
+
+box.internal.sql_create_function("inject_stat_error", "INT", inject_stat_error_func)
+
test:do_execsql_test(
7.1,
[[
@@ -417,8 +438,7 @@ test:do_execsql_test(
INSERT INTO t1 VALUES(null, 4, 4);
INSERT INTO t1 VALUES(null, 5, 5);
ANALYZE;
- UPDATE "_sql_stat4" SET "sample" = '' WHERE "sample" =
- (SELECT "sample" FROM "_sql_stat4" WHERE "tbl" = 't1' AND "idx" = 'i1' LIMIT 1);
+ SELECT inject_stat_error('_sql_stat4');
ANALYZE;
]])
@@ -1050,11 +1070,28 @@ test:do_execsql_test(
-- </15.4>
})
+local inject_stat_error_func = function (space_name)
+ local space = box.space[space_name]
+ local stats = space:select()
+ for _, stat in pairs(stats) do
+ space:delete(get_pk(space, stat))
+ local new_tuple = {"no such tbl"}
+ for i=2,#stat do
+ table.insert(new_tuple, stat[i])
+ end
+ space:insert(new_tuple)
+ end
+ return 0
+end
+
+box.internal.sql_create_function("inject_stat_error", "INT", inject_stat_error_func)
+
+
test:do_execsql_test(
15.7,
[[
ANALYZE;
- UPDATE "_sql_stat1" SET "tbl" = 'no such tbl';
+ SELECT inject_stat_error('_sql_stat1');
]])
test:do_execsql_test(
diff --git a/test/sql-tap/bigrow1.test.lua b/test/sql-tap/bigrow1.test.lua
index 43c0d0b67..e69ddf136 100755
--- a/test/sql-tap/bigrow1.test.lua
+++ b/test/sql-tap/bigrow1.test.lua
@@ -40,7 +40,7 @@ test:do_test(
test:do_execsql_test(
"bigrow-1.1",
[[
- CREATE TABLE t1(a text primary key, b text, c text);
+ CREATE TABLE t1(id INT PRIMARY KEY AUTOINCREMENT, a TEXT UNIQUE, b TEXT, c TEXT);
--SELECT name FROM sqlite_master
-- WHERE type='table' OR type='index'
-- ORDER BY name
@@ -55,7 +55,7 @@ test:do_test(
"bigrow-1.2",
function()
big1 = string.sub(bigstr, 1, 65519 + 1)
- local sql = "INSERT INTO t1 VALUES('abc',"
+ local sql = "INSERT INTO t1(a,b,c) VALUES('abc',"
sql = sql .. "'"..big1.."', 'xyz');"
test:execsql(sql)
return test:execsql "SELECT a, c FROM t1"
@@ -80,7 +80,7 @@ test:do_test(
"bigrow-1.4",
function()
big2 = string.sub(bigstr, 1, 65520+1)
- local sql = "INSERT INTO t1 VALUES('abc2',"
+ local sql = "INSERT INTO t1(a,b,c) VALUES('abc2',"
sql = sql .. "'"..big2.."', 'xyz2');"
return test:catchsql(sql)
end, {
@@ -134,7 +134,7 @@ test:do_execsql_test(
test:do_execsql_test(
"bigrow-1.6",
[[
- SELECT * FROM t1
+ SELECT a,b,c FROM t1
]], {
-- <bigrow-1.6>
big1, "abc", "xyz"
@@ -144,8 +144,8 @@ test:do_execsql_test(
test:do_execsql_test(
"bigrow-1.7",
[[
- INSERT INTO t1 VALUES('1','2','3');
- INSERT INTO t1 VALUES('A','B','C');
+ INSERT INTO t1(a,b,c) VALUES('1','2','3');
+ INSERT INTO t1(a,b,c) VALUES('A','B','C');
SELECT b FROM t1 WHERE a=='1';
]], {
-- <bigrow-1.7>
diff --git a/test/sql-tap/check.test.lua b/test/sql-tap/check.test.lua
index c24ae2ff1..1f369fb02 100755
--- a/test/sql-tap/check.test.lua
+++ b/test/sql-tap/check.test.lua
@@ -420,7 +420,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"check-4.1",
[[
- CREATE TABLE t4(x INT primary key, y INT ,
+ CREATE TABLE t4(x INT UNIQUE, y INT, z INT PRIMARY KEY
CHECK (
x+y==11
OR x*y==12
@@ -437,8 +437,8 @@ test:do_execsql_test(
test:do_execsql_test(
"check-4.2",
[[
- INSERT INTO t4 VALUES(1,10);
- SELECT * FROM t4
+ INSERT INTO t4 VALUES(1,10,1);
+ SELECT x,y FROM t4
]], {
-- <check-4.2>
1, 10
@@ -449,7 +449,7 @@ test:do_execsql_test(
"check-4.3",
[[
UPDATE t4 SET x=4, y=3;
- SELECT * FROM t4
+ SELECT x,y FROM t4
]], {
-- <check-4.3>
4, 3
@@ -460,7 +460,7 @@ test:do_execsql_test(
"check-4.4",
[[
UPDATE t4 SET x=12, y=2;
- SELECT * FROM t4
+ SELECT x,y FROM t4
]], {
-- <check-4.4>
12, 2
@@ -471,7 +471,7 @@ test:do_execsql_test(
"check-4.5",
[[
UPDATE t4 SET x=12, y=-22;
- SELECT * FROM t4
+ SELECT x,y FROM t4
]], {
-- <check-4.5>
12, -22
@@ -491,7 +491,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"check-4.7",
[[
- SELECT * FROM t4;
+ SELECT x,y FROM t4;
]], {
-- <check-4.7>
12, -22
diff --git a/test/sql-tap/fkey2.test.lua b/test/sql-tap/fkey2.test.lua
index 61db29f32..03bf025f3 100755
--- a/test/sql-tap/fkey2.test.lua
+++ b/test/sql-tap/fkey2.test.lua
@@ -7,15 +7,14 @@ test:plan(116)
test:do_execsql_test(
"fkey2-1.1",
[[
- CREATE TABLE t1(a INT PRIMARY KEY, b INT );
- CREATE TABLE t2(c INT PRIMARY KEY REFERENCES t1(a), d INT);
+ CREATE TABLE t1(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE t2(c INT UNIQUE REFERENCES t1(a), d INT, id INT PRIMARY KEY AUTOINCREMENT);
- CREATE TABLE t3(a INT PRIMARY KEY, b INT );
- CREATE TABLE t4(c INT PRIMARY KEY REFERENCES t3, d INT );
-
- CREATE TABLE t7(a INT , b INTEGER PRIMARY KEY);
- CREATE TABLE t8(c INT PRIMARY KEY REFERENCES t7, d INT );
+ CREATE TABLE t3(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE t4(c INT UNIQUE REFERENCES t3(a), d INT, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE t7(a INT, b INTEGER UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE t8(c INT UNIQUE REFERENCES t7(b), d INT, id INT PRIMARY KEY AUTOINCREMENT);
]], {
-- <fkey2-1.1>
-- </fkey2-1.1>
@@ -24,7 +23,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"fkey2-1.2",
[[
- INSERT INTO t2 VALUES(1, 3);
+ INSERT INTO t2(c,d) VALUES(1, 3);
]], {
-- <fkey2-1.2>
1, "FOREIGN KEY constraint failed"
@@ -34,7 +33,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.3",
[[
- INSERT INTO t1 VALUES(1, 2);
+ INSERT INTO t1(a, b) VALUES(1, 2);
]], {
-- <fkey2-1.3>
0
@@ -44,7 +43,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.4",
[[
- INSERT INTO t2 VALUES(1, 3);
+ INSERT INTO t2(c,d) VALUES(1, 3);
]], {
-- <fkey2-1.4>
0
@@ -54,7 +53,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.5",
[[
- INSERT INTO t2 VALUES(2, 4);
+ INSERT INTO t2(c,d) VALUES(2, 4);
]], {
-- <fkey2-1.5>
1, "FOREIGN KEY constraint failed"
@@ -64,8 +63,8 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.6",
[[
- INSERT INTO t1 VALUES(3, 5);
- INSERT INTO t2 VALUES(3, 4);
+ INSERT INTO t1(a,b) VALUES(3, 5);
+ INSERT INTO t2(c,d) VALUES(3, 4);
]], {
-- <fkey2-1.6>
0
@@ -85,7 +84,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.8",
[[
- INSERT INTO t1 VALUES(6, 7);
+ INSERT INTO t1(a,b) VALUES(6, 7);
UPDATE t2 SET c = 6 WHERE d = 4;
]], {
-- <fkey2-1.8>
@@ -129,7 +128,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.12",
[[
- INSERT INTO t4 values (1,3);
+ INSERT INTO t4(c,d) values (1,3);
]], {
-- <fkey2-1.12>
1, "FOREIGN KEY constraint failed"
@@ -139,7 +138,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.13",
[[
- INSERT INTO t3 values (1,2);
+ INSERT INTO t3(a,b) values (1,2);
]], {
-- <fkey2-1.13>
0
@@ -149,7 +148,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.14",
[[
- INSERT INTO t4 values (1,3);
+ INSERT INTO t4(c,d) values (1,3);
]], {
-- <fkey2-1.14>
0
@@ -159,7 +158,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.15",
[[
- INSERT INTO t8 values (1,3);
+ INSERT INTO t8(c,d) values (1,3);
]], {
-- <fkey2-1.15>
1, "FOREIGN KEY constraint failed"
@@ -169,7 +168,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.16",
[[
- INSERT INTO t7 values (2,1);
+ INSERT INTO t7(a,b) values (2,1);
]], {
-- <fkey2-1.16>
0
@@ -179,7 +178,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.17",
[[
- INSERT INTO t8 values (1,3);
+ INSERT INTO t8(c,d) values (1,3);
]], {
-- <fkey2-1.17>
0
@@ -189,7 +188,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.18",
[[
- INSERT INTO t8 values (2,4);
+ INSERT INTO t8(c,d) values (2,4);
]], {
-- <fkey2-1.18>
1, "FOREIGN KEY constraint failed"
@@ -199,7 +198,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.19",
[[
- INSERT INTO t8 values (6,4);
+ INSERT INTO t8(c,d) values (6,4);
]], {
-- <fkey2-1.19>
1,"FOREIGN KEY constraint failed"
@@ -269,7 +268,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"fkey2-1.26",
[[
- INSERT INTO t8 VALUES(666, 54644);
+ INSERT INTO t8(c,d) VALUES(666, 54644);
]], {
-- <fkey2-1.26>
1, "FOREIGN KEY constraint failed"
@@ -341,17 +340,19 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-3.1",
[[
- CREATE TABLE ab(a INT PRIMARY KEY, b TEXT);
+ CREATE TABLE ab(id INT PRIMARY KEY AUTOINCREMENT, a INT UNIQUE, b TEXT);
CREATE TABLE cd(
- c INT PRIMARY KEY REFERENCES ab ON UPDATE CASCADE ON DELETE CASCADE,
+ id INT PRIMARY KEY AUTOINCREMENT,
+ c INT UNIQUE REFERENCES ab(a) ON UPDATE CASCADE ON DELETE CASCADE,
d TEXT);
CREATE TABLE ef(
- e INT PRIMARY KEY REFERENCES cd ON UPDATE CASCADE,
+ id INT PRIMARY KEY AUTOINCREMENT,
+ e INT UNIQUE REFERENCES cd(c) ON UPDATE CASCADE,
f TEXT , CHECK (e!=5));
- INSERT INTO ab VALUES(1, 'b');
- INSERT INTO cd VALUES(1, 'd');
- INSERT INTO ef VALUES(1, 'e');
+ INSERT INTO ab(a,b) VALUES(1, 'b');
+ INSERT INTO cd(c,d) VALUES(1, 'd');
+ INSERT INTO ef(e,f) VALUES(1, 'e');
]], {
})
@@ -368,7 +369,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-3.3",
[[
- SELECT * FROM ab;
+ SELECT a,b FROM ab;
]], {
-- <fkey2-3.3>
1, "b"
@@ -388,7 +389,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-3.5",
[[
- SELECT * FROM ef;
+ SELECT e,f FROM ef;
]], {
-- <fkey2-3.5>
1, "e"
@@ -410,7 +411,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-3.7",
[[
- SELECT * FROM ab;
+ SELECT a,b FROM ab;
]], {
-- <fkey2-3.7>
1, "b"
@@ -424,20 +425,21 @@ test:do_execsql_test(
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
- node INT PRIMARY KEY,
- parent INT REFERENCES t1 ON DELETE CASCADE);
+ id INT PRIMARY KEY AUTOINCREMENT,
+ node INT UNIQUE NOT NULL,
+ parent INT REFERENCES t1(node) ON DELETE CASCADE);
CREATE TABLE t2(node INT PRIMARY KEY, parent INT );
CREATE TRIGGER t2t AFTER DELETE ON t2 BEGIN
DELETE FROM t2 WHERE parent = old.node;
END;
- INSERT INTO t1 VALUES(1, NULL);
- INSERT INTO t1 VALUES(2, 1);
- INSERT INTO t1 VALUES(3, 1);
- INSERT INTO t1 VALUES(4, 2);
- INSERT INTO t1 VALUES(5, 2);
- INSERT INTO t1 VALUES(6, 3);
- INSERT INTO t1 VALUES(7, 3);
- INSERT INTO t2 SELECT * FROM t1;
+ INSERT INTO t1(node, parent) VALUES(1, NULL);
+ INSERT INTO t1(node, parent) VALUES(2, 1);
+ INSERT INTO t1(node, parent) VALUES(3, 1);
+ INSERT INTO t1(node, parent) VALUES(4, 2);
+ INSERT INTO t1(node, parent) VALUES(5, 2);
+ INSERT INTO t1(node, parent) VALUES(6, 3);
+ INSERT INTO t1(node, parent) VALUES(7, 3);
+ INSERT INTO t2(node, parent) SELECT node,parent FROM t1;
]], {
-- <fkey2-4.1>
-- </fkey2-4.1>
@@ -525,8 +527,8 @@ test:do_execsql_test(
[[
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
- CREATE TABLE t1(a INT PRIMARY KEY, b INT );
- CREATE TABLE t2(c INTEGER PRIMARY KEY REFERENCES t1, b TEXT);
+ CREATE TABLE t1(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE t2(id INT PRIMARY KEY AUTOINCREMENT, c INTEGER REFERENCES t1(a), b TEXT);
]], {
-- <fkey2-5.1>
-- </fkey2-5.1>
@@ -535,7 +537,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"fkey2-5.2",
[[
- INSERT INTO t2 VALUES(1, 'A');
+ INSERT INTO t2(c,b) VALUES(1, 'A');
]], {
-- <fkey2-5.2>
1, "FOREIGN KEY constraint failed"
@@ -545,9 +547,9 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-5.3",
[[
- INSERT INTO t1 VALUES(1, 2);
- INSERT INTO t1 VALUES(2, 3);
- INSERT INTO t2 VALUES(1, 'A');
+ INSERT INTO t1(a,b) VALUES(1, 2);
+ INSERT INTO t1(a,b) VALUES(2, 3);
+ INSERT INTO t2(c,b) VALUES(1, 'A');
UPDATE t2 SET c = 2;
]], {
-- <fkey2-5.3>
@@ -668,12 +670,11 @@ test:do_execsql_test(
ON DELETE SET NULL);
INSERT INTO pp VALUES(1, 2, 3);
INSERT INTO pp VALUES(4, 5, 6);
- INSERT INTO pp VALUES(7, 8, 9);
+ INSERT INTO pp VALUES(7, 1, 9);
INSERT INTO cc VALUES(6, 'A', 5, 1);
INSERT INTO cc VALUES(6, 'B', 5, 2);
- INSERT INTO cc VALUES(9, 'A', 8, 3);
- INSERT INTO cc VALUES(9, 'B', 8, 4);
- UPDATE pp SET b = 1 WHERE a = 7;
+ INSERT INTO cc VALUES(3, 'A', 2, 3);
+ INSERT INTO cc VALUES(3, 'B', 2, 4);
SELECT * FROM cc;
]], {
-- <fkey2-6.6>
@@ -766,13 +767,13 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-8.1",
[[
- CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);
- CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);
+ CREATE TABLE t1(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE t2(id INT PRIMARY KEY AUTOINCREMENT, c INT UNIQUE, d INT, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);
- INSERT INTO t1 VALUES(10, 100);
- INSERT INTO t2 VALUES(10, 100);
+ INSERT INTO t1(a,b) VALUES(10, 100);
+ INSERT INTO t2(c,d) VALUES(10, 100);
UPDATE t1 SET a = 15;
- SELECT * FROM t2;
+ SELECT c,d FROM t2;
]], {
-- <fkey2-8.1>
15, 100
@@ -788,12 +789,12 @@ test:do_execsql_test(
[[
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1;
- CREATE TABLE t1(a INT , b TEXT PRIMARY KEY);
+ CREATE TABLE t1(a INT, b TEXT UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);
CREATE TABLE t2(
- x TEXT PRIMARY KEY REFERENCES t1 ON UPDATE RESTRICT);
- INSERT INTO t1 VALUES(1, 'one');
- INSERT INTO t1 VALUES(2, 'two');
- INSERT INTO t1 VALUES(3, 'three');
+ x TEXT PRIMARY KEY REFERENCES t1(b) ON UPDATE RESTRICT);
+ INSERT INTO t1(a,b) VALUES(1, 'four');
+ INSERT INTO t1(a,b) VALUES(2, 'two');
+ INSERT INTO t1(a,b) VALUES(3, 'three');
INSERT INTO t2 VALUES('two');
UPDATE t1 SET b = 'four' WHERE b = 'one';
]], {
@@ -916,14 +917,14 @@ test:do_execsql_test(
c10 TEXT , c11 TEXT , c12 TEXT , c13 TEXT , c14 TEXT , c15 TEXT , c16 TEXT , c17 TEXT , c18 TEXT , c19 TEXT ,
c20 TEXT , c21 TEXT , c22 TEXT , c23 TEXT , c24 TEXT , c25 TEXT , c26 TEXT , c27 TEXT , c28 TEXT , c29 TEXT ,
c30 TEXT , c31 TEXT , c32 TEXT , c33 TEXT , c34 TEXT , c35 TEXT , c36 TEXT , c37 TEXT , c38 TEXT , c39 TEXT ,
- PRIMARY KEY(c34, c35));
+ UNIQUE(c34, c35), id INT PRIMARY KEY AUTOINCREMENT);
CREATE TABLE down(
c00 TEXT , c01 TEXT , c02 TEXT , c03 TEXT , c04 TEXT , c05 TEXT , c06 TEXT , c07 TEXT , c08 TEXT , c09 TEXT ,
c10 TEXT , c11 TEXT , c12 TEXT , c13 TEXT , c14 TEXT , c15 TEXT , c16 TEXT , c17 TEXT , c18 TEXT , c19 TEXT ,
c20 TEXT , c21 TEXT , c22 TEXT , c23 TEXT , c24 TEXT , c25 TEXT , c26 TEXT , c27 TEXT , c28 TEXT , c29 TEXT ,
c30 TEXT , c31 TEXT , c32 TEXT , c33 TEXT , c34 TEXT , c35 TEXT , c36 TEXT , c37 TEXT , c38 TEXT , c39 TEXT ,
- PRIMARY KEY(c39, c38),
- FOREIGN KEY(c39, c38) REFERENCES up ON UPDATE CASCADE);
+ UNIQUE(c39, c38),
+ FOREIGN KEY(c39, c38) REFERENCES up(c34,c35) ON UPDATE CASCADE, id INT PRIMARY KEY AUTOINCREMENT);
INSERT INTO up(c34, c35) VALUES('yes', 'no');
INSERT INTO down(c39, c38) VALUES('yes', 'no');
UPDATE up SET c34 = 'possibly';
@@ -1168,8 +1169,8 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-11.1",
[[
- CREATE TABLE self(a INT PRIMARY KEY, b INT REFERENCES self(a));
- INSERT INTO self VALUES(13, 13);
+ CREATE TABLE self(a INT UNIQUE, b INT REFERENCES self(a), id INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO self(a,b) VALUES(13, 13);
UPDATE self SET a = 14, b = 14;
]], {
-- <fkey2-11.1>
@@ -1227,7 +1228,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"fkey2-11.7",
[[
- INSERT INTO self VALUES(20, 21);
+ INSERT INTO self(a,b) VALUES(20, 21);
]], {
-- <fkey2-11.7>
1, "FOREIGN KEY constraint failed"
@@ -1238,8 +1239,8 @@ test:do_execsql_test(
"fkey2-11.8",
[[
DROP TABLE IF EXISTS self;
- CREATE TABLE self(a INT UNIQUE, b INT PRIMARY KEY REFERENCES self(a));
- INSERT INTO self VALUES(13, 13);
+ CREATE TABLE self(a INT UNIQUE, b INT UNIQUE REFERENCES self(a), id INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO self(a,b) VALUES(13, 13);
UPDATE self SET a = 14, b = 14;
]], {
-- <fkey2-11.8>
@@ -1297,7 +1298,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"fkey2-11.14",
[[
- INSERT INTO self VALUES(20, 21);
+ INSERT INTO self(a,b) VALUES(20, 21);
]], {
-- <fkey2-11.14>
1, "FOREIGN KEY constraint failed"
@@ -1310,12 +1311,12 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-12.1",
[[
- CREATE TABLE tdd08(a INT PRIMARY KEY, b INT);
+ CREATE TABLE tdd08(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
CREATE UNIQUE INDEX idd08 ON tdd08(a,b);
- INSERT INTO tdd08 VALUES(200,300);
+ INSERT INTO tdd08(a,b) VALUES(200,300);
- CREATE TABLE tdd08_b(w INT PRIMARY KEY,x INT ,y INT , FOREIGN KEY(x,y) REFERENCES tdd08(a,b));
- INSERT INTO tdd08_b VALUES(100,200,300);
+ CREATE TABLE tdd08_b(w INT UNIQUE, x INT ,y INT , FOREIGN KEY(x,y) REFERENCES tdd08(a,b), id INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO tdd08_b(w,x,y) VALUES(100,200,300);
]], {
-- <fkey2-12.1>
-- </fkey2-12.1>
@@ -1334,7 +1335,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-12.3",
[[
- SELECT * FROM tdd08;
+ SELECT a,b FROM tdd08;
]], {
-- <fkey2-12.3>
200, 300
@@ -1344,7 +1345,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"fkey2-12.4",
[[
- INSERT INTO tdd08_b VALUES(400,500,300);
+ INSERT INTO tdd08_b(w,x,y) VALUES(400,500,300);
]], {
-- <fkey2-12.4>
1, "FOREIGN KEY constraint failed"
@@ -1374,16 +1375,16 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey2-13.1",
[[
- CREATE TABLE tce71(a INT PRIMARY KEY, b INT);
+ CREATE TABLE tce71(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
CREATE UNIQUE INDEX ice71 ON tce71(a,b);
- INSERT INTO tce71 VALUES(100,200);
+ INSERT INTO tce71(a,b) VALUES(100,200);
CREATE TABLE tce72(w INT PRIMARY KEY, x INT , y INT , FOREIGN KEY(x,y) REFERENCES tce71(a,b));
INSERT INTO tce72 VALUES(300,100,200);
UPDATE tce71 set b = 200 where a = 100;
SELECT * FROM tce71, tce72;
]], {
-- <fkey2-13.1>
- 100, 200, 300, 100, 200
+ 100, 200, 1, 300, 100, 200
-- </fkey2-13.1>
})
diff --git a/test/sql-tap/fkey3.test.lua b/test/sql-tap/fkey3.test.lua
index 9bd1aef23..011402da6 100755
--- a/test/sql-tap/fkey3.test.lua
+++ b/test/sql-tap/fkey3.test.lua
@@ -158,12 +158,12 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey3-3.6",
[[
- CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT, c INT, d TEXT, UNIQUE(a, b),
- FOREIGN KEY(c, d) REFERENCES t6(a, b));
- INSERT INTO t6 VALUES(1, 'a', 1, 'a');
- INSERT INTO t6 VALUES(2, 'a', 2, 'a');
- INSERT INTO t6 VALUES(3, 'a', 1, 'a');
- INSERT INTO t6 VALUES(5, 'a', 2, 'a');
+ CREATE TABLE t6(a INT UNIQUE, b TEXT, c INT, d TEXT, UNIQUE(a, b),
+ FOREIGN KEY(c, d) REFERENCES t6(a, b), id INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO t6(a,b,c,d) VALUES(1, 'a', 1, 'a');
+ INSERT INTO t6(a,b,c,d) VALUES(2, 'a', 2, 'a');
+ INSERT INTO t6(a,b,c,d) VALUES(3, 'a', 1, 'a');
+ INSERT INTO t6(a,b,c,d) VALUES(5, 'a', 2, 'a');
]], {
-- <fkey3-3.6>
-- </fkey3-3.6>
@@ -172,7 +172,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"fkey3-3.7",
[[
- INSERT INTO t6 VALUES(4, 'a', 65, 'a');
+ INSERT INTO t6(a,b,c,d) VALUES(4, 'a', 65, 'a');
]], {
-- <fkey3-3.7>
1, "FOREIGN KEY constraint failed"
@@ -182,9 +182,9 @@ test:do_catchsql_test(
test:do_execsql_test(
"fkey3-3.8",
[[
- INSERT INTO t6 VALUES(100, 'one', 100, 'one');
+ INSERT INTO t6(a,b,c,d) VALUES(100, 'one', 100, 'one');
DELETE FROM t6 WHERE a = 100;
- SELECT * FROM t6 WHERE a = 100;
+ SELECT a,b,c,d FROM t6 WHERE a = 100;
]], {
-- <fkey3-3.8>
-- </fkey3-3.8>
@@ -193,7 +193,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"fkey3-3.9",
[[
- INSERT INTO t6 VALUES(100, 'one', 100, 'one');
+ INSERT INTO t6(a,b,c,d) VALUES(100, 'one', 100, 'one');
UPDATE t6 SET c = 1, d = 'a' WHERE a = 100;
]], {
-- <fkey3-3.9>
diff --git a/test/sql-tap/fkey4.test.lua b/test/sql-tap/fkey4.test.lua
index a1b3b1f41..13fd308ee 100755
--- a/test/sql-tap/fkey4.test.lua
+++ b/test/sql-tap/fkey4.test.lua
@@ -8,9 +8,9 @@ test:do_execsql_test(
"fkey8-1.1",
[[
CREATE TABLE p1(a INT PRIMARY KEY);
- CREATE TABLE c1(b INT PRIMARY KEY REFERENCES p1 ON DELETE CASCADE);
+ CREATE TABLE c1(id INT PRIMARY KEY AUTOINCREMENT, b INT UNIQUE REFERENCES p1 ON DELETE CASCADE);
INSERT INTO p1 VALUES (1), (2), (3);
- INSERT INTO c1 VALUES (2);
+ INSERT INTO c1(b) VALUES (2);
DELETE FROM p1 WHERE a = 2;
SELECT * FROM c1;
]], {
@@ -24,9 +24,9 @@ test:do_catchsql_test(
DROP TABLE IF EXISTS c1;
DROP TABLE IF EXISTS p1;
CREATE TABLE p1(a INT PRIMARY KEY);
- CREATE TABLE c1(b INT PRIMARY KEY REFERENCES p1 ON DELETE SET NULL);
+ CREATE TABLE c1(id INT PRIMARY KEY AUTOINCREMENT, b INT UNIQUE NOT NULL REFERENCES p1 ON DELETE SET NULL);
INSERT INTO p1 VALUES (1), (2), (3);
- INSERT INTO c1 VALUES (2);
+ INSERT INTO c1(b) VALUES (2);
DELETE FROM p1 WHERE a = 2;
]], {
-- <fkey8-1.2>
@@ -40,11 +40,11 @@ test:do_execsql_test(
DROP TABLE IF EXISTS c1;
DROP TABLE IF EXISTS p1;
CREATE TABLE p1(a INT PRIMARY KEY);
- CREATE TABLE c1(b INT PRIMARY KEY DEFAULT 3 REFERENCES p1 ON DELETE SET DEFAULT);
+ CREATE TABLE c1(id INT PRIMARY KEY AUTOINCREMENT, b INT UNIQUE DEFAULT 3 REFERENCES p1 ON DELETE SET DEFAULT);
INSERT INTO p1 VALUES (1), (2), (3);
- INSERT INTO c1 VALUES (2);
+ INSERT INTO c1(b) VALUES (2);
DELETE FROM p1 WHERE a = 2;
- SELECT * FROM c1;
+ SELECT b FROM c1;
]], {
-- <fkey8-1.3>
3
@@ -152,10 +152,10 @@ test:do_catchsql_test(
DROP TABLE IF EXISTS cc1;
DROP TABLE IF EXISTS c1;
DROP TABLE IF EXISTS p1;
- CREATE TABLE p1(a INT PRIMARY KEY);
- CREATE TABLE c1(b INT PRIMARY KEY REFERENCES p1 ON UPDATE SET NULL, c INT);
- INSERT INTO p1 VALUES (1), (2), (3);
- INSERT INTO c1 VALUES (2, 1), (3, 2);
+ CREATE TABLE p1(a INT UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE c1(b INT UNIQUE NOT NULL REFERENCES p1(a) ON UPDATE SET NULL, c INT, id INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO p1(a) VALUES (1), (2), (3);
+ INSERT INTO c1(b, c) VALUES (2, 1), (3, 2);
UPDATE OR IGNORE p1 SET a = 4 WHERE a = 2;
]], {
-- <fkey8-1.9>
@@ -168,12 +168,12 @@ test:do_execsql_test(
[[
DROP TABLE IF EXISTS c1;
DROP TABLE IF EXISTS p1;
- CREATE TABLE p1(a INT PRIMARY KEY);
- CREATE TABLE c1(b INT PRIMARY KEY REFERENCES p1 ON UPDATE CASCADE, c INT);
- INSERT INTO p1 VALUES (1), (2), (3);
- INSERT INTO c1 VALUES (2, 1), (3, 2);
+ CREATE TABLE p1(a INT UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);
+ CREATE TABLE c1(b INT UNIQUE NOT NULL REFERENCES p1(a) ON UPDATE CASCADE, c INT, id INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO p1(a) VALUES (1), (2), (3);
+ INSERT INTO c1(b,c) VALUES (2, 1), (3, 2);
UPDATE OR IGNORE p1 SET a = 4 WHERE a = 2;
- SELECT * FROM c1;
+ SELECT b,c FROM c1 ORDER BY b,c;
]], {
-- <fkey8-1.10>
3, 2, 4, 1
diff --git a/test/sql-tap/gh2140-trans.test.lua b/test/sql-tap/gh2140-trans.test.lua
index 3c6f9042f..fe978d1a9 100755
--- a/test/sql-tap/gh2140-trans.test.lua
+++ b/test/sql-tap/gh2140-trans.test.lua
@@ -5,42 +5,42 @@ test:plan(10)
box.sql.execute("DROP TABLE IF EXISTS t1")
box.sql.execute("DROP TABLE IF EXISTS t2")
-box.sql.execute("CREATE TABLE t1 (s1 int primary key, s2 int);")
-box.sql.execute("CREATE TABLE t2 (s1 int primary key, s2 int);")
+box.sql.execute("CREATE TABLE t1 (s0 INT PRIMARY KEY, s1 INT UNIQUE, s2 INT);")
+box.sql.execute("CREATE TABLE t2 (s0 INT PRIMARY KEY, s1 INT UNIQUE, s2 INT);")
-box.sql.execute("INSERT INTO t1 VALUES (1,1);")
-box.sql.execute("INSERT INTO t2 VALUES (1,1);")
+box.sql.execute("INSERT INTO t1 VALUES (1,1,1);")
+box.sql.execute("INSERT INTO t2 VALUES (1,1,1);")
test:do_execsql_test('commit1_check',
[[START TRANSACTION;
- INSERT INTO t1 VALUES (2,2);
+ INSERT INTO t1 VALUES (2,2,2);
COMMIT;
- SELECT * FROM t1]],
+ SELECT s1,s2 FROM t1]],
{1, 1, 2, 2})
test:do_execsql_test('rollback1_check',
[[START TRANSACTION;
- INSERT INTO t1 VALUES (3,3);
+ INSERT INTO t1 VALUES (3,3,3);
ROLLBACK;
- SELECT * FROM t1]],
+ SELECT s1,s2 FROM t1]],
{1, 1, 2, 2})
for _, verb in ipairs({'ROLLBACK', 'ABORT'}) do
box.sql.execute('DELETE FROM t2')
- answer = "Duplicate key exists in unique index 'pk_unnamed_T1_1' in space 'T1'"
+ answer = "Duplicate key exists in unique index 'unique_unnamed_T1_2' in space 'T1'"
test:do_catchsql_test('insert1_'..verb,
[[START TRANSACTION;
- INSERT INTO t2 VALUES (2, 2);
- INSERT OR ]]..verb..[[ INTO t1 VALUES (1,1);
+ INSERT INTO t2 VALUES (20, 2, 2);
+ INSERT OR ]]..verb..[[ INTO t1 VALUES (10,1,1);
]],
{1, answer})
local expect = {}
if verb == 'ABORT' then
box.sql.execute('COMMIT')
- expect = {2, 2}
+ expect = {20, 2, 2}
end
test:do_execsql_test('insert1_'..verb..'_check',
'SELECT * FROM t2', expect)
@@ -48,7 +48,7 @@ for _, verb in ipairs({'ROLLBACK', 'ABORT'}) do
box.sql.execute('DELETE FROM t2')
test:do_catchsql_test('update1_'..verb,
[[START TRANSACTION;
- INSERT INTO t2 VALUES (2, 2);
+ INSERT INTO t2 VALUES (20, 2, 2);
UPDATE OR ]]..verb..[[ t1 SET s1 = 1 WHERE s1 = 2;
]],
{1, answer})
diff --git a/test/sql-tap/gh2250-trigger-chain-limit.test.lua b/test/sql-tap/gh2250-trigger-chain-limit.test.lua
index bfbb26097..e38f286e6 100755
--- a/test/sql-tap/gh2250-trigger-chain-limit.test.lua
+++ b/test/sql-tap/gh2250-trigger-chain-limit.test.lua
@@ -9,20 +9,20 @@ for _, table_count in ipairs({30, 31}) do
drop_string = 'DROP TABLE IF EXISTS t' .. i .. ';'
box.sql.execute(drop_string)
- create_string = 'CREATE TABLE t' .. i .. ' (s1 int primary key, s2 int);'
+ create_string = 'CREATE TABLE t' .. i .. ' (s1 INT UNIQUE, s2 INT, s3 INT PRIMARY KEY);'
box.sql.execute(create_string)
- insert_string = 'INSERT INTO t' .. i .. ' VALUES (0,' .. i .. ');'
+ insert_string = 'INSERT INTO t' .. i .. ' VALUES (0,' .. i .. ', 0);'
box.sql.execute(insert_string)
-- Second table for triggers mixture check
drop_string = 'DROP TABLE IF EXISTS tt' .. i .. ';'
box.sql.execute(drop_string)
- create_string = 'CREATE TABLE tt' .. i .. ' (s1 int primary key, s2 int);'
+ create_string = 'CREATE TABLE tt' .. i .. ' (s1 INT UNIQUE, s2 INT, s3 INT PRIMARY KEY);'
box.sql.execute(create_string)
- insert_string = 'INSERT INTO tt' .. i .. ' VALUES (0,' .. i .. ');'
+ insert_string = 'INSERT INTO tt' .. i .. ' VALUES (0,' .. i .. ', 0);'
box.sql.execute(insert_string)
end
diff --git a/test/sql-tap/gh2259-in-stmt-trans.test.lua b/test/sql-tap/gh2259-in-stmt-trans.test.lua
index d1ced19ec..95e03985f 100755
--- a/test/sql-tap/gh2259-in-stmt-trans.test.lua
+++ b/test/sql-tap/gh2259-in-stmt-trans.test.lua
@@ -5,8 +5,8 @@ test:plan(20)
box.sql.execute("DROP TABLE IF EXISTS t1")
box.sql.execute("DROP TABLE IF EXISTS t2")
-box.sql.execute("CREATE TABLE t1 (s1 int primary key, s2 int);")
-box.sql.execute("CREATE TABLE t2 (s1 int primary key, s2 int);")
+box.sql.execute("CREATE TABLE t1 (s1 INT UNIQUE, s2 INT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE t2 (s1 INT UNIQUE, s2 INT PRIMARY KEY);")
box.sql.execute("INSERT INTO t2 VALUES (1,1);")
box.sql.execute("INSERT INTO t1 VALUES (3,3);")
@@ -18,7 +18,7 @@ for _, prefix in pairs({"BEFORE", "AFTER"}) do
test:do_catchsql_test(prefix..'_insert1',
'INSERT INTO t1 VALUES(1, 2)',
- {1,"Duplicate key exists in unique index 'pk_unnamed_T2_1' in space 'T2'"})
+ {1,"Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
test:do_execsql_test(prefix..'_insert1_check1',
'SELECT * FROM t1',
@@ -34,7 +34,7 @@ for _, prefix in pairs({"BEFORE", "AFTER"}) do
test:do_catchsql_test(prefix..'_update1',
'UPDATE t1 SET s1=1',
- {1,"Duplicate key exists in unique index 'pk_unnamed_T2_1' in space 'T2'"})
+ {1,"Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
test:do_execsql_test(prefix..'_update1_check1',
'SELECT * FROM t1',
@@ -52,7 +52,7 @@ for _, prefix in pairs({"BEFORE", "AFTER"}) do
test:do_catchsql_test(prefix..'delete1',
'DELETE FROM t1;',
- {1, "Duplicate key exists in unique index 'pk_unnamed_T2_1' in space 'T2'"})
+ {1, "Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
-- Nothing should be inserted due to abort
test:do_execsql_test('delete1_check1',
@@ -69,7 +69,7 @@ end
-- Check multi-insert
test:do_catchsql_test('insert2',
'INSERT INTO t1 VALUES (5, 6), (6, 7)',
- {1, "Duplicate key exists in unique index 'pk_unnamed_T2_1' in space 'T2'"})
+ {1, "Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
test:do_execsql_test('insert2_check',
'SELECT * FROM t1;',
{3, 3})
diff --git a/test/sql-tap/identifier_case.test.lua b/test/sql-tap/identifier_case.test.lua
index 1d248f9dc..f26399eb6 100755
--- a/test/sql-tap/identifier_case.test.lua
+++ b/test/sql-tap/identifier_case.test.lua
@@ -116,7 +116,7 @@ test:do_test(
end,
6)
-test:execsql([[create table table1(columnn INT , "columnn" INT primary key)]])
+test:execsql([[create table table1(pk INT PRIMARY KEY AUTOINCREMENT, columnn INT , "columnn" INT UNIQUE)]])
test:execsql([[insert into table1("columnn", "COLUMNN") values(2,1)]])
diff --git a/test/sql-tap/index6.test.lua b/test/sql-tap/index6.test.lua
index 05385efe6..d39da43e8 100755
--- a/test/sql-tap/index6.test.lua
+++ b/test/sql-tap/index6.test.lua
@@ -227,7 +227,8 @@ test:do_execsql_test(
-- </index6-6.0>
})
else
- test:execsql("CREATE TABLE t6(a INT ,b INT , PRIMARY KEY (a,b));")
+ test:execsql("CREATE TABLE t6(id INT PRIMARY KEY AUTOINCREMENT, a INT ,b INT);")
+ test:execsql("CREATE UNIQUE INDEX t6i1 ON t6(a, b);")
test:execsql("INSERT INTO t6(a,b) VALUES(123,456);")
end
@@ -235,7 +236,7 @@ test:do_execsql_test(
"index6-6.1",
[[
UPDATE OR REPLACE t6 SET b=789;
- SELECT * FROM t6;
+ SELECT a,b FROM t6;
]], {
-- <index6-6.1>
123, 789
diff --git a/test/sql-tap/intpkey.test.lua b/test/sql-tap/intpkey.test.lua
index 132d9a37f..45c9f2c6e 100755
--- a/test/sql-tap/intpkey.test.lua
+++ b/test/sql-tap/intpkey.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(39)
+test:plan(40)
--!./tcltestrunner.lua
-- 2001 September 15
@@ -149,7 +149,8 @@ test:do_execsql_test(
test:do_execsql_test(
"intpkey-1.11",
[[
- UPDATE t1 SET a=4 WHERE b='one';
+ DELETE FROM t1 WHERE a = 7;
+ INSERT INTO t1 VALUES(4,'one','two');
SELECT * FROM t1;
]], {
-- <intpkey-1.11>
@@ -239,6 +240,18 @@ test:do_execsql_test(
-- </intpkey-1.16>
})
+-- Direct update of PK is forbidden
+--
+test:do_catchsql_test(
+ "intpkey-1.17",
+ [[
+ CREATE TABLE test(id INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO test VALUES (1);
+ UPDATE test SET id = 2;
+ ]], {
+ 1, "Attempt to modify a tuple field which is part of index 'pk_unnamed_TEST_1' in space 'TEST'"
+ })
+
--### INDICES
-- Check to make sure indices work correctly with integer primary keys
--
@@ -281,7 +294,8 @@ test:do_execsql_test(
test:do_execsql_test(
"intpkey-2.2",
[[
- UPDATE t1 SET a=8 WHERE b=='y';
+ DELETE FROM t1 WHERE b=='y';
+ INSERT INTO t1 VALUES(8,'y','z');
SELECT * FROM t1 WHERE b=='y';
]], {
-- <intpkey-2.2>
@@ -335,7 +349,8 @@ test:do_execsql_test(
"intpkey-2.7",
[[
--UPDATE t1 SET a=-4 WHERE rowid=8;
- UPDATE t1 SET a=-4 WHERE a=8;
+ DELETE FROM t1 WHERE a==8;
+ INSERT INTO t1 VALUES(-4,'y','z');
SELECT * FROM t1 WHERE b>'a';
]], {
-- <intpkey-2.7>
diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua
index 0bf680fe6..a533a7c07 100755
--- a/test/sql-tap/misc1.test.lua
+++ b/test/sql-tap/misc1.test.lua
@@ -237,16 +237,19 @@ test:do_test(
test:do_execsql_test(
"misc1-4.1",
[[
+ CREATE TABLE temp(id INT PRIMARY KEY, a TEXT);
CREATE TABLE t2(a TEXT primary key);
+
START TRANSACTION;
- INSERT INTO t2 VALUES('This is a long string to use up a lot of disk -');
- UPDATE t2 SET a=a||a||a||a;
- INSERT INTO t2 SELECT '1 - ' || a FROM t2;
- INSERT INTO t2 SELECT '2 - ' || a FROM t2;
- INSERT INTO t2 SELECT '3 - ' || a FROM t2;
- INSERT INTO t2 SELECT '4 - ' || a FROM t2;
- INSERT INTO t2 SELECT '5 - ' || a FROM t2;
- INSERT INTO t2 SELECT '6 - ' || a FROM t2;
+ INSERT INTO temp VALUES(0, 'This is a long string to use up a lot of disk -');
+ UPDATE temp SET a=a||a||a||a;
+ INSERT INTO t2 (a) SELECT a FROM temp;
+ INSERT INTO t2 (a) SELECT '1 - ' || a FROM t2;
+ INSERT INTO t2 (a) SELECT '2 - ' || a FROM t2;
+ INSERT INTO t2 (a) SELECT '3 - ' || a FROM t2;
+ INSERT INTO t2 (a) SELECT '4 - ' || a FROM t2;
+ INSERT INTO t2 (a) SELECT '5 - ' || a FROM t2;
+ INSERT INTO t2 (a) SELECT '6 - ' || a FROM t2;
COMMIT;
SELECT count(*) FROM t2;
]], {
@@ -429,6 +432,7 @@ test:execsql([[
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t4;
+ DROP TABLE temp;
]])
-- 64-bit integers are represented exactly.
--
diff --git a/test/sql-tap/quote.test.lua b/test/sql-tap/quote.test.lua
index 3f0bf865d..0a3b89daa 100755
--- a/test/sql-tap/quote.test.lua
+++ b/test/sql-tap/quote.test.lua
@@ -27,7 +27,7 @@ test:do_catchsql_test(
"quote-1.0",
[[
--- CREATE TABLE '@abc' ( '#xyz' int PRIMARY KEY, '!pqr' text );
- CREATE TABLE "abc5_" ( "#xyz" int PRIMARY KEY, "!pqr" text );
+ CREATE TABLE "abc5_" (id INT PRIMARY KEY, "#xyz" INT UNIQUE, "!pqr" TEXT );
]], {
-- <quote-1.0>
0
@@ -39,7 +39,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"quote-1.1",
[[
- INSERT INTO "abc5_" VALUES(5,'hello')
+ INSERT INTO "abc5_" VALUES(1, 5,'hello')
]], {
-- <quote-1.1>
0
@@ -52,7 +52,7 @@ test:do_catchsql_test(
SELECT * FROM "abc5_"
]], {
-- <quote-1.2.1>
- 0, {5, "hello"}
+ 0, {1, 5, "hello"}
-- </quote-1.2.1>
})
diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua
index 71645e2e2..7057f6b0f 100755
--- a/test/sql-tap/table.test.lua
+++ b/test/sql-tap/table.test.lua
@@ -528,7 +528,7 @@ test:do_execsql2_test(
test:do_execsql_test(
"table-7.3",
[[
- CREATE TABLE savepoint_t(release_t int primary key);
+ CREATE TABLE savepoint_t(id INT PRIMARY KEY AUTOINCREMENT, release_t INT UNIQUE);
INSERT INTO savepoint_t(release_t) VALUES(10);
UPDATE savepoint_t SET release_t = 5;
SELECT release_t FROM savepoint_t;
diff --git a/test/sql-tap/tkt-a8a0d2996a.test.lua b/test/sql-tap/tkt-a8a0d2996a.test.lua
index aefc2be0c..cc8827bc2 100755
--- a/test/sql-tap/tkt-a8a0d2996a.test.lua
+++ b/test/sql-tap/tkt-a8a0d2996a.test.lua
@@ -23,8 +23,8 @@ testprefix = "tkt-a8a0d2996a"
test:do_execsql_test(
1.0,
[[
- CREATE TABLE t(x TEXT primary key,y TEXT);
- INSERT INTO t VALUES('1','1');
+ CREATE TABLE t(id INT PRIMARY KEY, x TEXT UNIQUE, y TEXT);
+ INSERT INTO t VALUES(1, '1','1');
SELECT typeof(x), typeof(y) FROM t WHERE 1=x+0 AND y=='1';
]], {
-- <1.0>
diff --git a/test/sql-tap/tkt2767.test.lua b/test/sql-tap/tkt2767.test.lua
index 066c82100..b69b90be1 100755
--- a/test/sql-tap/tkt2767.test.lua
+++ b/test/sql-tap/tkt2767.test.lua
@@ -31,13 +31,13 @@ if (1 > 0)
"tkt2767-1.1",
[[
-- Construct a table with many rows of data
- CREATE TABLE t1(x INT primary key);
- INSERT INTO t1 VALUES(1);
- INSERT INTO t1 VALUES(2);
- INSERT INTO t1 SELECT x+2 FROM t1;
- INSERT INTO t1 SELECT x+4 FROM t1;
- INSERT INTO t1 SELECT x+8 FROM t1;
- INSERT INTO t1 SELECT x+16 FROM t1;
+ CREATE TABLE t1(id INT PRIMARY KEY AUTOINCREMENT, x INT UNIQUE);
+ INSERT INTO t1 VALUES(1, 1);
+ INSERT INTO t1 VALUES(2, 2);
+ INSERT INTO t1 (x) SELECT x+2 FROM t1;
+ INSERT INTO t1 (x) SELECT x+4 FROM t1;
+ INSERT INTO t1 (x) SELECT x+8 FROM t1;
+ INSERT INTO t1 (x) SELECT x+16 FROM t1;
-- BEFORE triggers that invoke raise(ignore). The effect of
-- these triggers should be to make INSERTs, UPDATEs, and DELETEs
@@ -99,7 +99,7 @@ if (1 > 0)
test:do_execsql_test(
"tkt2767-1.4",
[[
- INSERT INTO t1 SELECT x+32 FROM t1;
+ INSERT INTO t1 (x) SELECT x+32 FROM t1;
SELECT count(*), sum(x) FROM t1;
]], {
-- <tkt2767-1.4>
diff --git a/test/sql-tap/tkt2832.test.lua b/test/sql-tap/tkt2832.test.lua
index 108c05cdb..d3f24b586 100755
--- a/test/sql-tap/tkt2832.test.lua
+++ b/test/sql-tap/tkt2832.test.lua
@@ -25,10 +25,10 @@ test:plan(6)
test:do_execsql_test(
"tkt2832-1.1",
[[
- CREATE TABLE t1(a INT PRIMARY KEY);
- INSERT INTO t1 VALUES(2);
- INSERT INTO t1 VALUES(1);
- INSERT INTO t1 VALUES(3);
+ CREATE TABLE t1(id INT PRIMARY KEY AUTOINCREMENT, a INT UNIQUE);
+ INSERT INTO t1(a) VALUES(2);
+ INSERT INTO t1(a) VALUES(1);
+ INSERT INTO t1(a) VALUES(3);
]], {
-- <tkt2832-1.1>
@@ -40,7 +40,7 @@ test:do_execsql_test(
"tkt2832-1.2",
[[
UPDATE OR REPLACE t1 SET a = 1;
- SELECT * FROM t1;
+ SELECT a FROM t1;
]], {
-- <tkt2832-1.2>
1
diff --git a/test/sql-tap/tkt3554.test.lua b/test/sql-tap/tkt3554.test.lua
index ed194107f..0fcf350cb 100755
--- a/test/sql-tap/tkt3554.test.lua
+++ b/test/sql-tap/tkt3554.test.lua
@@ -26,7 +26,8 @@ test:plan(4)
test:do_execsql_test(
"tkt3544-1.1",
[[
- CREATE TABLE test ( obj TEXT, t1 INT , t2 INT , PRIMARY KEY(obj, t1, t2) );
+ CREATE TABLE test (id INT PRIMARY KEY AUTOINCREMENT, obj TEXT, t1 INT , t2 INT);
+ CREATE UNIQUE INDEX testi1 ON test(obj, t1, t2);
CREATE TRIGGER test_insert BEFORE INSERT ON test BEGIN
UPDATE test SET t1 = new.t1
@@ -49,8 +50,8 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3544-1.2",
[[
- INSERT INTO test VALUES('a', 10000, 11000);
- SELECT * FROM test;
+ INSERT INTO test(obj, t1, t2) VALUES('a', 10000, 11000);
+ SELECT obj, t1, t2 FROM test;
]], {
-- <tkt3544-1.2>
"a", 10000, 11000
@@ -61,9 +62,9 @@ test:do_test(
"tkt3544-1.3",
function()
test:execsql [[
- INSERT INTO test VALUES('a', 9000, 10500);
+ INSERT INTO test(obj, t1, t2) VALUES('a', 9000, 10500);
]]
- return test:execsql " SELECT * FROM test "
+ return test:execsql " SELECT obj, t1, t2 FROM test "
end, {
-- <tkt3544-1.3>
"a", 9000, 11000
@@ -74,9 +75,9 @@ test:do_test(
"tkt3544-1.4",
function()
test:execsql [[
- INSERT INTO test VALUES('a', 10000, 12000);
+ INSERT INTO test(obj, t1, t2) VALUES('a', 10000, 12000);
]]
- return test:execsql " SELECT * FROM test "
+ return test:execsql " SELECT obj, t1, t2 FROM test "
end, {
-- <tkt3544-1.4>
"a", 9000, 12000
diff --git a/test/sql-tap/trigger2.test.lua b/test/sql-tap/trigger2.test.lua
index 5d84c312a..b9b6dae74 100755
--- a/test/sql-tap/trigger2.test.lua
+++ b/test/sql-tap/trigger2.test.lua
@@ -61,10 +61,10 @@ test:plan(26)
test:catchsql " pragma recursive_triggers = off "
-- 1.
ii = 0
-tbl_definitions = { "CREATE TABLE tbl (a INTEGER PRIMARY KEY, b INT );",
- "CREATE TABLE tbl (a INT PRIMARY KEY, b INT );",
- "CREATE TABLE tbl (a INT , b INT PRIMARY KEY);",
- "CREATE TABLE tbl (a INT , b INTEGER PRIMARY KEY);" }
+tbl_definitions = { "CREATE TABLE tbl (id INT PRIMARY KEY AUTOINCREMENT, a INTEGER UNIQUE, b INT );",
+ "CREATE TABLE tbl (id INT PRIMARY KEY AUTOINCREMENT, a INT UNIQUE, b INT );",
+ "CREATE TABLE tbl (id INT PRIMARY KEY AUTOINCREMENT, a INT, b INT UNIQUE );",
+ "CREATE TABLE tbl (id INT PRIMARY KEY AUTOINCREMENT, a INT, b INTEGER UNIQUE );"}
-- Tarantool: temporary tables are not supported so far. #2119
-- table.insert(tbl_definitions,"CREATE TEMP TABLE tbl (a, b INTEGER PRIMARY KEY);")
-- table.insert(tbl_definitions,"CREATE TEMP TABLE tbl (a INTEGER PRIMARY KEY, b);")
@@ -81,8 +81,8 @@ for _, tbl_defn in ipairs(tbl_definitions) do
]]
test:execsql(tbl_defn)
test:execsql [[
- INSERT INTO tbl VALUES(1, 2);
- INSERT INTO tbl VALUES(3, 4);
+ INSERT INTO tbl(a, b) VALUES(1, 2);
+ INSERT INTO tbl(a, b) VALUES(3, 4);
]]
test:execsql [[
CREATE TABLE rlog (idx INTEGER PRIMARY KEY, old_a INT , old_b INT , db_sum_a INT , db_sum_b INT , new_a INT , new_b INT );
@@ -140,8 +140,8 @@ for _, tbl_defn in ipairs(tbl_definitions) do
test:execsql [[
DELETE FROM tbl;
DELETE FROM rlog;
- INSERT INTO tbl VALUES (100, 100);
- INSERT INTO tbl VALUES (300, 200);
+ INSERT INTO tbl(a, b) VALUES (100, 100);
+ INSERT INTO tbl(a, b) VALUES (300, 200);
CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
BEGIN
INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
@@ -203,7 +203,7 @@ for _, tbl_defn in ipairs(tbl_definitions) do
INSERT INTO other_tbl VALUES(1, 2);
INSERT INTO other_tbl VALUES(3, 4);
-- INSERT INTO tbl SELECT * FROM other_tbl;
- INSERT INTO tbl VALUES(5, 6);
+ INSERT INTO tbl(a,b) VALUES(5, 6);
DROP TABLE other_tbl;
SELECT * FROM rlog;
@@ -355,8 +355,8 @@ table.insert(when_triggers,"t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM t
test:execsql [[
CREATE TABLE tbl (a INT , b INT PRIMARY KEY, c INT , d INT );
- CREATE TABLE log (a INT PRIMARY KEY);
- INSERT INTO log VALUES (0);
+ CREATE TABLE log (id INT PRIMARY KEY AUTOINCREMENT, a INT);
+ INSERT INTO log VALUES (0, 0);
]]
for _, trig in ipairs(when_triggers) do
test:execsql("CREATE TRIGGER "..trig.." BEGIN UPDATE log set a = a + 1; END;")
@@ -368,17 +368,17 @@ test:do_test(
local r = {}
table.insert(r, test:execsql([[
INSERT INTO tbl VALUES(0, 1, 0, 0); -- 1 (ifcapable subquery)
- SELECT * FROM log;
+ SELECT a FROM log;
UPDATE log SET a = 0;]])[1])
table.insert(r, test:execsql([[
INSERT INTO tbl VALUES(0, 2, 0, 0); -- 0
- SELECT * FROM log;
+ SELECT a FROM log;
UPDATE log SET a = 0;]])[1])
table.insert(r, test:execsql([[
INSERT INTO tbl VALUES(200, 3, 0, 0); -- 1
- SELECT * FROM log;
+ SELECT a FROM log;
UPDATE log SET a = 0;]])[1])
return r
diff --git a/test/sql-tap/trigger7.test.lua b/test/sql-tap/trigger7.test.lua
index 21ff2af4d..21b015024 100755
--- a/test/sql-tap/trigger7.test.lua
+++ b/test/sql-tap/trigger7.test.lua
@@ -40,7 +40,7 @@ test:do_test(
"trigger7-2.1",
function()
test:execsql [[
- CREATE TABLE t1(x INT PRIMARY KEY, y INT);
+ CREATE TABLE t1(x INT UNIQUE, y INT, z INT PRIMARY KEY AUTOINCREMENT);
CREATE TRIGGER r1 AFTER UPDATE OF x ON t1 BEGIN
SELECT '___update_t1.x___';
END;
diff --git a/test/sql-tap/triggerB.test.lua b/test/sql-tap/triggerB.test.lua
index 455c3cb3e..414a4d098 100755
--- a/test/sql-tap/triggerB.test.lua
+++ b/test/sql-tap/triggerB.test.lua
@@ -25,9 +25,9 @@ test:plan(201)
test:do_execsql_test(
"triggerB-1.1",
[[
- CREATE TABLE x(x INTEGER PRIMARY KEY, y INT NOT NULL);
- INSERT INTO x VALUES(1, 1);
- INSERT INTO x VALUES(2, 1);
+ CREATE TABLE x(id INTEGER PRIMARY KEY, x INTEGER UNIQUE, y INT NOT NULL);
+ INSERT INTO x VALUES(1, 1, 1);
+ INSERT INTO x VALUES(2, 2, 1);
CREATE VIEW vx AS SELECT x, y, 0 AS yy FROM x;
CREATE TRIGGER tx INSTEAD OF UPDATE OF y ON vx
BEGIN
@@ -57,7 +57,7 @@ test:do_catchsql_test(
CREATE TRIGGER ty AFTER INSERT ON x BEGIN
SELECT wen.x; -- Unrecognized name
END;
- INSERT INTO x VALUES(1,2);
+ INSERT INTO x VALUES(3,1,2);
]], {
-- <triggerB-2.1>
1, "no such column: WEN.X"
@@ -81,8 +81,8 @@ test:do_test(
"triggerB-2.3",
function()
test:execsql [[
- CREATE TABLE t2(a INTEGER PRIMARY KEY, b INT );
- INSERT INTO t2 VALUES(1,2);
+ CREATE TABLE t2(id INTEGER PRIMARY KEY, a INTEGER UNIQUE, b INT );
+ INSERT INTO t2 VALUES(1, 1,2);
CREATE TABLE changes(x INT PRIMARY KEY,y INT );
CREATE TRIGGER r1t2 AFTER UPDATE ON t2 BEGIN
INSERT INTO changes VALUES(new.a, new.b);
@@ -128,17 +128,17 @@ test:do_test(
function()
test:execsql [[
CREATE TABLE t3(
- c0 TEXT PRIMARY KEY, c1 TEXT , c2 TEXT , c3 TEXT , c4 TEXT , c5 TEXT , c6 TEXT , c7 TEXT , c8 TEXT , c9 TEXT ,
- c10 TEXT , c11 TEXT , c12 TEXT , c13 TEXT , c14 TEXT , c15 TEXT , c16 TEXT , c17 TEXT , c18 TEXT , c19 TEXT ,
- c20 TEXT , c21 TEXT , c22 TEXT , c23 TEXT , c24 TEXT , c25 TEXT , c26 TEXT , c27 TEXT , c28 TEXT , c29 TEXT ,
- c30 TEXT , c31 TEXT , c32 TEXT , c33 TEXT , c34 TEXT , c35 TEXT , c36 TEXT , c37 TEXT , c38 TEXT , c39 TEXT ,
- c40 TEXT , c41 TEXT , c42 TEXT , c43 TEXT , c44 TEXT , c45 TEXT , c46 TEXT , c47 TEXT , c48 TEXT , c49 TEXT ,
- c50 TEXT , c51 TEXT , c52 TEXT , c53 TEXT , c54 TEXT , c55 TEXT , c56 TEXT , c57 TEXT , c58 TEXT , c59 TEXT ,
- c60 TEXT , c61 TEXT , c62 TEXT , c63 TEXT , c64 TEXT , c65 TEXT
+ id INT PRIMARY KEY, c0 TEXT UNIQUE, c1 TEXT , c2 TEXT , c3 TEXT , c4 TEXT , c5 TEXT , c6 TEXT , c7 TEXT ,
+ c8 TEXT , c9 TEXT , c10 TEXT , c11 TEXT , c12 TEXT , c13 TEXT , c14 TEXT , c15 TEXT , c16 TEXT , c17 TEXT ,
+ c18 TEXT , c19 TEXT , c20 TEXT , c21 TEXT , c22 TEXT , c23 TEXT , c24 TEXT , c25 TEXT , c26 TEXT , c27 TEXT ,
+ c28 TEXT , c29 TEXT , c30 TEXT , c31 TEXT , c32 TEXT , c33 TEXT , c34 TEXT , c35 TEXT , c36 TEXT , c37 TEXT ,
+ c38 TEXT , c39 TEXT , c40 TEXT , c41 TEXT , c42 TEXT , c43 TEXT , c44 TEXT , c45 TEXT , c46 TEXT , c47 TEXT ,
+ c48 TEXT , c49 TEXT , c50 TEXT , c51 TEXT , c52 TEXT , c53 TEXT , c54 TEXT , c55 TEXT , c56 TEXT , c57 TEXT ,
+ c58 TEXT , c59 TEXT , c60 TEXT , c61 TEXT , c62 TEXT , c63 TEXT , c64 TEXT , c65 TEXT
);
CREATE TABLE t3_changes(colnum INT PRIMARY KEY, oldval TEXT , newval TEXT );
INSERT INTO t3 VALUES(
- 'a0', 'a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9',
+ 0, 'a0', 'a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9',
'a10','a11','a12','a13','a14','a15','a16','a17','a18','a19',
'a20','a21','a22','a23','a24','a25','a26','a27','a28','a29',
'a30','a31','a32','a33','a34','a35','a36','a37','a38','a39',
diff --git a/test/sql-tap/triggerC.test.lua b/test/sql-tap/triggerC.test.lua
index 59a6d2367..f94c5bdbf 100755
--- a/test/sql-tap/triggerC.test.lua
+++ b/test/sql-tap/triggerC.test.lua
@@ -52,7 +52,7 @@ test:execsql " PRAGMA recursive_triggers = on "
test:do_execsql_test(
"triggerC-1.1",
[[
- CREATE TABLE t1(a TEXT PRIMARY KEY, b TEXT, c TEXT);
+ CREATE TABLE t1(id INT PRIMARY KEY AUTOINCREMENT, a TEXT UNIQUE, b TEXT, c TEXT);
CREATE TABLE log(t TEXT PRIMARY KEY, a1 TEXT, b1 TEXT, c1 TEXT, a2 TEXT, b2 TEXT, c2 TEXT);
CREATE TRIGGER trig1 BEFORE INSERT ON t1 BEGIN
INSERT INTO log VALUES('before', NULL, NULL, NULL, new.a, new.b, new.c);
@@ -82,7 +82,7 @@ test:do_execsql_test(
test:do_execsql_test(
"triggerC-1.2",
[[
- INSERT INTO t1 VALUES('A', 'B', 'C');
+ INSERT INTO t1 VALUES(1, 'A', 'B', 'C');
SELECT * FROM log ORDER BY t DESC;
]], {
-- <triggerC-1.2>
@@ -96,7 +96,7 @@ test:do_execsql_test(
SELECT * FROM t1
]], {
-- <triggerC-1.3>
- "A", "B", "C"
+ 1, "A", "B", "C"
-- </triggerC-1.3>
})
@@ -118,7 +118,7 @@ test:do_execsql_test(
SELECT * FROM t1
]], {
-- <triggerC-1.5>
- "a", "B", "C"
+ 1, "a", "B", "C"
-- </triggerC-1.5>
})
@@ -182,7 +182,7 @@ test:do_execsql_test(
test:do_execsql_test(
"triggerC-1.11",
[[
- CREATE TABLE t5 (a INT primary key, b INT, c INT);
+ CREATE TABLE t5 (a INT UNIQUE, b INT PRIMARY KEY, c INT);
INSERT INTO t5 values (1, 2, 3);
CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN
UPDATE OR IGNORE t5 SET a = new.a, c = 10;
@@ -206,7 +206,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"triggerC-1.13",
[[
- CREATE TABLE t6(a INTEGER PRIMARY KEY, b INT);
+ CREATE TABLE t6(a INT UNIQUE, b INT PRIMARY KEY);
INSERT INTO t6 VALUES(1, 2);
create trigger r1 after update on t6 for each row begin
SELECT 1;
@@ -222,10 +222,11 @@ test:do_execsql_test(
"triggerC-1.14",
[[
DROP TABLE IF EXISTS t1;
- CREATE TABLE cnt(n INT PRIMARY KEY);
- INSERT INTO cnt VALUES(0);
- CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT UNIQUE, c INT, d INT, e INT);
+ CREATE TABLE cnt(id INT PRIMARY KEY, n INT UNIQUE);
+ INSERT INTO cnt VALUES(0, 0);
+ CREATE TABLE t1(a INT UNIQUE, b INT UNIQUE, c INT, d INT, e INT PRIMARY KEY);
CREATE INDEX t1cd ON t1(c,d);
+ CREATE UNIQUE INDEX t1a ON t1(a);
CREATE TRIGGER t1r1 AFTER UPDATE ON t1 BEGIN UPDATE cnt SET n=n+1; END;
INSERT INTO t1 VALUES(1,2,3,4,5);
INSERT INTO t1 VALUES(6,7,8,9,10);
@@ -241,7 +242,7 @@ test:do_catchsql_test(
UPDATE OR ROLLBACK t1 SET a=100;
]], {
-- <triggerC-1.15>
- 1, "Duplicate key exists in unique index 'pk_unnamed_T1_1' in space 'T1'"
+ 1, "Duplicate key exists in unique index 'unique_unnamed_T1_1' in space 'T1'"
-- </triggerC-1.15>
})
@@ -712,11 +713,12 @@ test:do_test(
"triggerC-10.1",
function()
test:execsql [[
- CREATE TABLE t10(a TEXT PRIMARY KEY, updatecnt INT DEFAULT 0);
+ CREATE TABLE t10(id INT PRIMARY KEY, a TEXT, updatecnt INT DEFAULT 0);
+ CREATE UNIQUE INDEX t10i1 ON t10(a);
CREATE TRIGGER t10_bu BEFORE UPDATE OF a ON t10 BEGIN
UPDATE t10 SET updatecnt = updatecnt+1 WHERE a = old.a;
END;
- INSERT INTO t10 VALUES('hello', 0);
+ INSERT INTO t10 VALUES(0, 'hello', 0);
]]
-- Before the problem was fixed, table t10 would contain the tuple
-- (world, 0) after running the following script (because the value
@@ -728,7 +730,7 @@ test:do_test(
]]
end, {
-- <triggerC-10.1>
- "world", 1
+ 0, "world", 1
-- </triggerC-10.1>
})
@@ -739,7 +741,7 @@ test:do_execsql_test(
SELECT * FROM t10;
]], {
-- <triggerC-10.2>
- "tcl", 5
+ 0, "tcl", 5
-- </triggerC-10.2>
})
@@ -748,10 +750,11 @@ test:do_test(
function()
test:execsql [[
CREATE TABLE t11(
- c1 INT PRIMARY KEY, c2 INT, c3 INT, c4 INT, c5 INT, c6 INT, c7 INT, c8 INT, c9 INT, c10 INT,
- c11 INT, c12 INT, c13 INT, c14 INT, c15 INT, c16 INT, c17 INT, c18 INT, c19 INT, c20 INT,
- c21 INT, c22 INT, c23 INT, c24 INT, c25 INT, c26 INT, c27 INT, c28 INT, c29 INT, c30 INT,
- c31 INT, c32 INT, c33 INT, c34 INT, c35 INT, c36 INT, c37 INT, c38 INT, c39 INT, c40 INT
+ c0 INT PRIMARY KEY, c1 INT UNIQUE, c2 INT, c3 INT, c4 INT, c5 INT, c6 INT, c7 INT,
+ c8 INT, c9 INT, c10 INT, c11 INT, c12 INT, c13 INT, c14 INT, c15 INT, c16 INT,
+ c17 INT, c18 INT, c19 INT, c20 INT, c21 INT, c22 INT, c23 INT, c24 INT, c25 INT,
+ c26 INT, c27 INT, c28 INT, c29 INT, c30 INT, c31 INT, c32 INT, c33 INT, c34 INT,
+ c35 INT, c36 INT, c37 INT, c38 INT, c39 INT, c40 INT
);
CREATE TRIGGER t11_bu BEFORE UPDATE OF c1 ON t11 BEGIN
@@ -759,7 +762,7 @@ test:do_test(
END;
INSERT INTO t11 VALUES(
- 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
+ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
31, 32, 33, 34, 35, 36, 37, 38, 39, 40
@@ -775,7 +778,7 @@ test:do_test(
]]
end, {
-- <triggerC-10.3>
- 5, 2, 3, 35, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 32, 33, 22, 34, 35, 36, 37, 38, 39, 40
+ 0, 5, 2, 3, 35, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 32, 33, 22, 34, 35, 36, 37, 38, 39, 40
-- </triggerC-10.3>
})
diff --git a/test/sql-tap/triggerD.test.lua b/test/sql-tap/triggerD.test.lua
index f4d1c29a8..37b33ae2d 100755
--- a/test/sql-tap/triggerD.test.lua
+++ b/test/sql-tap/triggerD.test.lua
@@ -36,7 +36,7 @@ test:do_test(
"triggerD-1.1",
function()
return test:execsql [[
- CREATE TABLE t1(rowid INT PRIMARY KEY, oid INT, _rowid_ INT, x INT);
+ CREATE TABLE t1(id INT PRIMARY KEY, rowid INT UNIQUE, oid INT, _rowid_ INT, x INT);
CREATE TABLE log(a TEXT PRIMARY KEY,b INT,c INT,d INT,e INT);
CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
@@ -69,7 +69,7 @@ test:do_test(
"triggerD-1.2",
function()
return test:execsql [[
- INSERT INTO t1 VALUES(100,200,300,400);
+ INSERT INTO t1 VALUES(0, 100,200,300,400);
SELECT * FROM log
]]
end, {
diff --git a/test/sql-tap/update.test.lua b/test/sql-tap/update.test.lua
index d0fc66ebb..82168932e 100755
--- a/test/sql-tap/update.test.lua
+++ b/test/sql-tap/update.test.lua
@@ -888,15 +888,16 @@ test:do_catchsql_test("update-9.4", [[
test:do_execsql_test("update-10.1", [[
DROP TABLE test1;
CREATE TABLE t1(
- a integer primary key,
+ a integer UNIQUE,
b INT UNIQUE,
c INT , d INT ,
e INT , f INT ,
- UNIQUE(c,d)
+ UNIQUE(c,d),
+ id INT PRIMARY KEY AUTOINCREMENT
);
- INSERT INTO t1 VALUES(1,2,3,4,5,6);
- INSERT INTO t1 VALUES(2,3,4,4,6,7);
- SELECT * FROM t1
+ INSERT INTO t1(a,b,c,d,e,f) VALUES(1,2,3,4,5,6);
+ INSERT INTO t1(a,b,c,d,e,f) VALUES(2,3,4,4,6,7);
+ SELECT a,b,c,d,e,f FROM t1
]], {
-- <update-10.1>
1, 2, 3, 4, 5, 6, 2, 3, 4, 4, 6, 7
@@ -905,7 +906,7 @@ test:do_execsql_test("update-10.1", [[
test:do_catchsql_test("update-10.2", [[
UPDATE t1 SET a=1, e=9 WHERE f=6;
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.2>
0, {1, 2, 3, 4, 9, 6, 2, 3, 4, 4, 6, 7}
@@ -914,15 +915,15 @@ test:do_catchsql_test("update-10.2", [[
test:do_catchsql_test("update-10.3", [[
UPDATE t1 SET a=1, e=10 WHERE f=7;
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.3>
- 1, "Duplicate key exists in unique index 'pk_unnamed_T1_1' in space 'T1'"
+ 1, "Duplicate key exists in unique index 'unique_unnamed_T1_1' in space 'T1'"
-- </update-10.3>
})
test:do_catchsql_test("update-10.4", [[
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.4>
0, {1, 2, 3, 4, 9, 6, 2, 3, 4, 4, 6, 7}
@@ -931,7 +932,7 @@ test:do_catchsql_test("update-10.4", [[
test:do_catchsql_test("update-10.5", [[
UPDATE t1 SET b=2, e=11 WHERE f=6;
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.5>
0, {1, 2, 3, 4, 11, 6, 2, 3, 4, 4, 6, 7}
@@ -940,7 +941,7 @@ test:do_catchsql_test("update-10.5", [[
test:do_catchsql_test("update-10.6", [[
UPDATE t1 SET b=2, e=12 WHERE f=7;
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.6>
1, "Duplicate key exists in unique index 'unique_unnamed_T1_2' in space 'T1'"
@@ -948,7 +949,7 @@ test:do_catchsql_test("update-10.6", [[
})
test:do_catchsql_test("update-10.7", [[
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.7>
0, {1, 2, 3, 4, 11, 6, 2, 3, 4, 4, 6, 7}
@@ -957,7 +958,7 @@ test:do_catchsql_test("update-10.7", [[
test:do_catchsql_test("update-10.8", [[
UPDATE t1 SET c=3, d=4, e=13 WHERE f=6;
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.8>
0, {1, 2, 3, 4, 13, 6, 2, 3, 4, 4, 6, 7}
@@ -966,7 +967,7 @@ test:do_catchsql_test("update-10.8", [[
test:do_catchsql_test("update-10.9", [[
UPDATE t1 SET c=3, d=4, e=14 WHERE f=7;
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.9>
1, "Duplicate key exists in unique index 'unique_unnamed_T1_3' in space 'T1'"
@@ -974,7 +975,7 @@ test:do_catchsql_test("update-10.9", [[
})
test:do_catchsql_test("update-10.10", [[
- SELECT * FROM t1;
+ SELECT a,b,c,d,e,f FROM t1;
]], {
-- <update-10.10>
0, {1, 2, 3, 4, 13, 6, 2, 3, 4, 4, 6, 7}
diff --git a/test/sql-tap/with1.test.lua b/test/sql-tap/with1.test.lua
index 28b3a858b..97585c13b 100755
--- a/test/sql-tap/with1.test.lua
+++ b/test/sql-tap/with1.test.lua
@@ -25,7 +25,7 @@ testprefix = "with1"
-- end
test:do_execsql_test(1.0, [[
- CREATE TABLE t1(x INTEGER PRIMARY KEY, y INTEGER);
+ CREATE TABLE t1(x INTEGER UNIQUE, y INTEGER, z INTEGER PRIMARY KEY);
WITH x(a) AS ( SELECT * FROM t1) SELECT 10
]], {
-- <1.0>
@@ -42,7 +42,7 @@ test:do_execsql_test(1.1, [[
})
test:do_execsql_test(1.2, [[
- WITH x(a) AS ( SELECT * FROM t1) INSERT INTO t1 VALUES(1,2);
+ WITH x(a) AS ( SELECT * FROM t1) INSERT INTO t1 VALUES(1,1,2);
]], {
-- <1.2>
@@ -185,15 +185,15 @@ test:do_catchsql_test(3.6, [[
---------------------------------------------------------------------------
test:do_execsql_test(4.1, [[
DROP TABLE IF EXISTS t1;
- CREATE TABLE t1(x INT PRIMARY KEY);
- INSERT INTO t1 VALUES(1);
- INSERT INTO t1 VALUES(2);
- INSERT INTO t1 VALUES(3);
- INSERT INTO t1 VALUES(4);
+ CREATE TABLE t1(x INT UNIQUE, z INT PRIMARY KEY AUTOINCREMENT);
+ INSERT INTO t1(x) VALUES(1);
+ INSERT INTO t1(x) VALUES(2);
+ INSERT INTO t1(x) VALUES(3);
+ INSERT INTO t1(x) VALUES(4);
WITH dset AS ( SELECT 2 UNION ALL SELECT 4 )
DELETE FROM t1 WHERE x IN dset;
- SELECT * FROM t1;
+ SELECT (x) FROM t1;
]], {
-- <4.1>
1, 3
@@ -202,18 +202,18 @@ test:do_execsql_test(4.1, [[
test:do_execsql_test(4.2, [[
WITH iset AS ( SELECT 2 UNION ALL SELECT 4 )
- INSERT INTO t1 SELECT * FROM iset;
- SELECT * FROM t1;
+ INSERT INTO t1(x) SELECT * FROM iset;
+ SELECT x FROM t1;
]], {
-- <4.2>
- 1, 2, 3, 4
+ 1, 3, 2, 4
-- </4.2>
})
test:do_execsql_test(4.3, [[
WITH uset(a, b) AS ( SELECT 2, 8 UNION ALL SELECT 4, 9 )
UPDATE t1 SET x = COALESCE( (SELECT b FROM uset WHERE a=x), x );
- SELECT * FROM t1;
+ SELECT x FROM t1;
]], {
-- <4.3>
1, 3, 8, 9
diff --git a/test/sql/collation.result b/test/sql/collation.result
index c69510fe7..5721ef854 100644
--- a/test/sql/collation.result
+++ b/test/sql/collation.result
@@ -263,13 +263,13 @@ box.schema.user.drop('tmp')
...
-- gh-3644 Foreign key update fails with "unicode_ci".
-- Check that foreign key update doesn't fail with "unicode_ci".
-box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) COLLATE "unicode_ci" PRIMARY KEY);')
+box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) COLLATE "unicode_ci" UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);')
---
...
box.sql.execute('CREATE TABLE t1 (s1 INT PRIMARY KEY, s0 CHAR(5) COLLATE "unicode_ci" REFERENCES t0(s1));')
---
...
-box.sql.execute("INSERT INTO t0 VALUES ('a');")
+box.sql.execute("INSERT INTO t0(s1) VALUES ('a');")
---
...
box.sql.execute("INSERT INTO t1 VALUES (1,'a');")
@@ -279,7 +279,7 @@ box.sql.execute("INSERT INTO t1 VALUES (1,'a');")
box.sql.execute("UPDATE t0 SET s1 = 'A';")
---
...
-box.sql.execute("SELECT * FROM t0;")
+box.sql.execute("SELECT s1 FROM t0;")
---
- - ['A']
...
@@ -294,13 +294,13 @@ box.sql.execute("DROP TABLE t0;")
---
...
-- Check that foreign key update fails with default collation.
-box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) PRIMARY KEY);')
+box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);')
---
...
box.sql.execute('CREATE TABLE t1 (s1 INT PRIMARY KEY, s0 CHAR(5) REFERENCES t0(s1));')
---
...
-box.sql.execute("INSERT INTO t0 VALUES ('a');")
+box.sql.execute("INSERT INTO t0(s1) VALUES ('a');")
---
...
box.sql.execute("INSERT INTO t1 VALUES (1,'a');")
@@ -315,7 +315,7 @@ box.sql.execute("SELECT * FROM t1;")
---
- - [1, 'a']
...
-box.sql.execute("SELECT * FROM t0;")
+box.sql.execute("SELECT s1 FROM t0;")
---
- - ['a']
...
diff --git a/test/sql/collation.test.lua b/test/sql/collation.test.lua
index 4ad2d5e50..4c649a444 100644
--- a/test/sql/collation.test.lua
+++ b/test/sql/collation.test.lua
@@ -105,24 +105,24 @@ box.schema.user.drop('tmp')
-- gh-3644 Foreign key update fails with "unicode_ci".
-- Check that foreign key update doesn't fail with "unicode_ci".
-box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) COLLATE "unicode_ci" PRIMARY KEY);')
+box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) COLLATE "unicode_ci" UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);')
box.sql.execute('CREATE TABLE t1 (s1 INT PRIMARY KEY, s0 CHAR(5) COLLATE "unicode_ci" REFERENCES t0(s1));')
-box.sql.execute("INSERT INTO t0 VALUES ('a');")
+box.sql.execute("INSERT INTO t0(s1) VALUES ('a');")
box.sql.execute("INSERT INTO t1 VALUES (1,'a');")
-- Should't fail.
box.sql.execute("UPDATE t0 SET s1 = 'A';")
-box.sql.execute("SELECT * FROM t0;")
+box.sql.execute("SELECT s1 FROM t0;")
box.sql.execute("SELECT * FROM t1;")
box.sql.execute("DROP TABLE t1;")
box.sql.execute("DROP TABLE t0;")
-- Check that foreign key update fails with default collation.
-box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) PRIMARY KEY);')
+box.sql.execute('CREATE TABLE t0 (s1 CHAR(5) UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);')
box.sql.execute('CREATE TABLE t1 (s1 INT PRIMARY KEY, s0 CHAR(5) REFERENCES t0(s1));')
-box.sql.execute("INSERT INTO t0 VALUES ('a');")
+box.sql.execute("INSERT INTO t0(s1) VALUES ('a');")
box.sql.execute("INSERT INTO t1 VALUES (1,'a');")
-- Should fail.
box.sql.execute("UPDATE t0 SET s1 = 'A';")
box.sql.execute("SELECT * FROM t1;")
-box.sql.execute("SELECT * FROM t0;")
+box.sql.execute("SELECT s1 FROM t0;")
box.sql.execute("DROP TABLE t1;")
box.sql.execute("DROP TABLE t0;")
diff --git a/test/sql/on-conflict.result b/test/sql/on-conflict.result
index 731f03c66..97ec77526 100644
--- a/test/sql/on-conflict.result
+++ b/test/sql/on-conflict.result
@@ -86,10 +86,10 @@ box.sql.execute("DROP TABLE a;")
...
-- gh-3566: UPDATE OR IGNORE causes deletion of old entry.
--
-box.sql.execute("CREATE TABLE tj (s1 INT PRIMARY KEY, s2 INT);")
+box.sql.execute("CREATE TABLE tj (s0 INT PRIMARY KEY, s1 INT UNIQUE, s2 INT);")
---
...
-box.sql.execute("INSERT INTO tj VALUES (1, 2), (2, 3);")
+box.sql.execute("INSERT INTO tj VALUES (1, 1, 2), (2, 2, 3);")
---
...
box.sql.execute("CREATE UNIQUE INDEX i ON tj (s2);")
@@ -98,7 +98,7 @@ box.sql.execute("CREATE UNIQUE INDEX i ON tj (s2);")
box.sql.execute("UPDATE OR IGNORE tj SET s1 = s1 + 1;")
---
...
-box.sql.execute("SELECT * FROM tj;")
+box.sql.execute("SELECT s1, s2 FROM tj;")
---
- - [1, 2]
- [3, 3]
@@ -106,7 +106,7 @@ box.sql.execute("SELECT * FROM tj;")
box.sql.execute("UPDATE OR IGNORE tj SET s2 = s2 + 1;")
---
...
-box.sql.execute("SELECT * FROM tj;")
+box.sql.execute("SELECT s1, s2 FROM tj;")
---
- - [1, 2]
- [3, 4]
diff --git a/test/sql/on-conflict.test.lua b/test/sql/on-conflict.test.lua
index aa58b854b..9486942b9 100644
--- a/test/sql/on-conflict.test.lua
+++ b/test/sql/on-conflict.test.lua
@@ -35,13 +35,13 @@ box.sql.execute("DROP TABLE a;")
-- gh-3566: UPDATE OR IGNORE causes deletion of old entry.
--
-box.sql.execute("CREATE TABLE tj (s1 INT PRIMARY KEY, s2 INT);")
-box.sql.execute("INSERT INTO tj VALUES (1, 2), (2, 3);")
+box.sql.execute("CREATE TABLE tj (s0 INT PRIMARY KEY, s1 INT UNIQUE, s2 INT);")
+box.sql.execute("INSERT INTO tj VALUES (1, 1, 2), (2, 2, 3);")
box.sql.execute("CREATE UNIQUE INDEX i ON tj (s2);")
box.sql.execute("UPDATE OR IGNORE tj SET s1 = s1 + 1;")
-box.sql.execute("SELECT * FROM tj;")
+box.sql.execute("SELECT s1, s2 FROM tj;")
box.sql.execute("UPDATE OR IGNORE tj SET s2 = s2 + 1;")
-box.sql.execute("SELECT * FROM tj;")
+box.sql.execute("SELECT s1, s2 FROM tj;")
-- gh-3565: INSERT OR REPLACE causes assertion fault.
--
diff --git a/test/sql/row-count.result b/test/sql/row-count.result
index d4c86ac2b..d6248eb0f 100644
--- a/test/sql/row-count.result
+++ b/test/sql/row-count.result
@@ -27,10 +27,10 @@ box.sql.execute("SELECT ROW_COUNT();")
---
- - [1]
...
-box.sql.execute("CREATE TABLE t3 (i1 INT PRIMARY KEY, i2 INT);")
+box.sql.execute("CREATE TABLE t3 (i1 INT UNIQUE, i2 INT, i3 INT PRIMARY KEY);")
---
...
-box.sql.execute("INSERT INTO t3 VALUES (0, 0);")
+box.sql.execute("INSERT INTO t3 VALUES (0, 0, 0);")
---
...
box.sql.execute("SELECT ROW_COUNT();")
@@ -80,7 +80,7 @@ box.sql.execute("SELECT ROW_COUNT();")
---
- - [4]
...
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
---
...
box.sql.execute("TRUNCATE TABLE t3;")
@@ -90,7 +90,7 @@ box.sql.execute("SELECT ROW_COUNT();")
---
- - [0]
...
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
---
...
box.sql.execute("UPDATE t3 SET i2 = 666;")
@@ -110,7 +110,7 @@ box.sql.execute("SELECT ROW_COUNT();")
---
- - [3]
...
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
---
...
box.sql.execute("DELETE FROM t3")
diff --git a/test/sql/row-count.test.lua b/test/sql/row-count.test.lua
index 45a39d19a..f10807fff 100644
--- a/test/sql/row-count.test.lua
+++ b/test/sql/row-count.test.lua
@@ -9,8 +9,8 @@ box.sql.execute("SELECT ROW_COUNT();")
box.sql.execute("SELECT ROW_COUNT();")
box.sql.execute("CREATE TABLE t2 (s1 CHAR(10) PRIMARY KEY, s2 CHAR(10) REFERENCES t1 ON DELETE CASCADE);")
box.sql.execute("SELECT ROW_COUNT();")
-box.sql.execute("CREATE TABLE t3 (i1 INT PRIMARY KEY, i2 INT);")
-box.sql.execute("INSERT INTO t3 VALUES (0, 0);")
+box.sql.execute("CREATE TABLE t3 (i1 INT UNIQUE, i2 INT, i3 INT PRIMARY KEY);")
+box.sql.execute("INSERT INTO t3 VALUES (0, 0, 0);")
box.sql.execute("SELECT ROW_COUNT();")
box.sql.execute("CREATE TRIGGER x AFTER DELETE ON t1 FOR EACH ROW BEGIN UPDATE t3 SET i1 = i1 + ROW_COUNT(); END;")
box.sql.execute("SELECT ROW_COUNT();")
@@ -25,10 +25,10 @@ box.sql.execute("REPLACE INTO t2 VALUES('a', 'c');")
box.sql.execute("SELECT ROW_COUNT();")
box.sql.execute("DELETE FROM t1;")
box.sql.execute("SELECT ROW_COUNT();")
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
box.sql.execute("TRUNCATE TABLE t3;")
box.sql.execute("SELECT ROW_COUNT();")
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
box.sql.execute("UPDATE t3 SET i2 = 666;")
box.sql.execute("SELECT ROW_COUNT();")
-- gh-3816: DELETE optimization returns valid number of
@@ -36,7 +36,7 @@ box.sql.execute("SELECT ROW_COUNT();")
--
box.sql.execute("DELETE FROM t3 WHERE 0 = 0;")
box.sql.execute("SELECT ROW_COUNT();")
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
box.sql.execute("DELETE FROM t3")
box.sql.execute("SELECT ROW_COUNT();")
-- But triggers still should't be accounted.
diff --git a/test/sql/triggers.result b/test/sql/triggers.result
index 45d231f72..bbfff3302 100644
--- a/test/sql/triggers.result
+++ b/test/sql/triggers.result
@@ -253,7 +253,7 @@ box.sql.execute("DROP TABLE T1;")
box.sql.execute("PRAGMA sql_default_engine ('vinyl');")
---
...
-box.sql.execute("CREATE TABLE m (s1 TEXT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE m (s0 INT PRIMARY KEY, s1 TEXT UNIQUE);")
---
...
box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;")
@@ -262,13 +262,13 @@ box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE
box.sql.execute("PRAGMA sql_default_engine('memtx');")
---
...
-box.sql.execute("CREATE TABLE n (s1 TEXT PRIMARY KEY, s2 REAL);")
+box.sql.execute("CREATE TABLE n (s0 INT PRIMARY KEY, s1 TEXT UNIQUE, s2 REAL);")
---
...
-box.sql.execute("INSERT INTO m VALUES ('0');")
+box.sql.execute("INSERT INTO m VALUES (0, '0');")
---
...
-box.sql.execute("INSERT INTO n VALUES ('',null);")
+box.sql.execute("INSERT INTO n VALUES (0, '',null);")
---
...
box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';")
@@ -289,7 +289,7 @@ box.sql.execute("DROP TABLE n;")
box.sql.execute("PRAGMA sql_default_engine ('memtx');")
---
...
-box.sql.execute("CREATE TABLE m (s1 TEXT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE m (s0 INT PRIMARY KEY, s1 TEXT UNIQUE);")
---
...
box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;")
@@ -298,13 +298,13 @@ box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE
box.sql.execute("PRAGMA sql_default_engine('vinyl');")
---
...
-box.sql.execute("CREATE TABLE n (s1 TEXT PRIMARY KEY, s2 REAL);")
+box.sql.execute("CREATE TABLE n (s0 INT PRIMARY KEY, s1 TEXT UNIQUE, s2 REAL);")
---
...
-box.sql.execute("INSERT INTO m VALUES ('0');")
+box.sql.execute("INSERT INTO m VALUES (0, '0');")
---
...
-box.sql.execute("INSERT INTO n VALUES ('',null);")
+box.sql.execute("INSERT INTO n VALUES (0, '',null);")
---
...
box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';")
diff --git a/test/sql/triggers.test.lua b/test/sql/triggers.test.lua
index a322b2f02..eb3f41ef5 100644
--- a/test/sql/triggers.test.lua
+++ b/test/sql/triggers.test.lua
@@ -101,12 +101,12 @@ box.sql.execute("DROP TABLE T1;")
--
-- Case 1: Src 'vinyl' table; Dst 'memtx' table
box.sql.execute("PRAGMA sql_default_engine ('vinyl');")
-box.sql.execute("CREATE TABLE m (s1 TEXT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE m (s0 INT PRIMARY KEY, s1 TEXT UNIQUE);")
box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;")
box.sql.execute("PRAGMA sql_default_engine('memtx');")
-box.sql.execute("CREATE TABLE n (s1 TEXT PRIMARY KEY, s2 REAL);")
-box.sql.execute("INSERT INTO m VALUES ('0');")
-box.sql.execute("INSERT INTO n VALUES ('',null);")
+box.sql.execute("CREATE TABLE n (s0 INT PRIMARY KEY, s1 TEXT UNIQUE, s2 REAL);")
+box.sql.execute("INSERT INTO m VALUES (0, '0');")
+box.sql.execute("INSERT INTO n VALUES (0, '',null);")
box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';")
-- ANALYZE operates with _sql_stat{1,4} tables should work
@@ -117,12 +117,12 @@ box.sql.execute("DROP TABLE n;")
-- Case 2: Src 'memtx' table; Dst 'vinyl' table
box.sql.execute("PRAGMA sql_default_engine ('memtx');")
-box.sql.execute("CREATE TABLE m (s1 TEXT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE m (s0 INT PRIMARY KEY, s1 TEXT UNIQUE);")
box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;")
box.sql.execute("PRAGMA sql_default_engine('vinyl');")
-box.sql.execute("CREATE TABLE n (s1 TEXT PRIMARY KEY, s2 REAL);")
-box.sql.execute("INSERT INTO m VALUES ('0');")
-box.sql.execute("INSERT INTO n VALUES ('',null);")
+box.sql.execute("CREATE TABLE n (s0 INT PRIMARY KEY, s1 TEXT UNIQUE, s2 REAL);")
+box.sql.execute("INSERT INTO m VALUES (0, '0');")
+box.sql.execute("INSERT INTO n VALUES (0, '',null);")
box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';")
-- ANALYZE operates with _sql_stat{1,4} tables should work
--
2.19.2
^ permalink raw reply [flat|nested] 13+ messages in thread