[tarantool-patches] Re: [PATCH] sql: xfer optimization issue

[Nikita Tatunov <hollow653@gmail.com>]

[-- Attachment #1: Type: text/plain, Size: 24104 bytes --]

diff --git a/src/box/sql.c b/src/box/sql.c
index fdce224..398b2a6 100644
--- a/src/box/sql.c
+++ b/src/box/sql.c
@@ -1636,10 +1636,12 @@ sql_debug_info(struct info_handler *h)
  extern int sql_search_count;
  extern int sql_sort_count;
  extern int sql_found_count;
+ extern int sql_xferOpt_count;
  info_begin(h);
  info_append_int(h, "sql_search_count", sql_search_count);
  info_append_int(h, "sql_sort_count", sql_sort_count);
  info_append_int(h, "sql_found_count", sql_found_count);
+ info_append_int(h, "sql_xferOpt_count", sql_xferOpt_count);
  info_end(h);
 }

diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index 2c9188e..9a99bab 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -1635,7 +1635,7 @@ sqlite3OpenTableAndIndices(Parse * pParse, /* Parsing
context */
  * purposes only - to make sure the transfer optimization really
  * is happening when it is supposed to.
  */
-int sqlite3_xferopt_count;
+int sql_xferOpt_count = 0;
 #endif /* SQLITE_TEST */

 #ifndef SQLITE_OMIT_XFER_OPT
@@ -1658,6 +1658,8 @@ xferCompatibleIndex(Index * pDest, Index * pSrc)
  assert(pDest->pTable != pSrc->pTable);
  uint32_t nDestCol = index_column_count(pDest);
  uint32_t nSrcCol = index_column_count(pSrc);
+ if ((pDest->idxType != pSrc->idxType))
+ return 0;
  if (nDestCol != nSrcCol) {
  return 0; /* Different number of columns */
  }
@@ -1725,9 +1727,9 @@ xferOptimization(Parse * pParse, /* Parser context */
  int emptyDestTest = 0; /* Address of test for empty pDest */
  int emptySrcTest = 0; /* Address of test for empty pSrc */
  Vdbe *v; /* The VDBE we are building */
- int destHasUniqueIdx = 0; /* True if pDest has a UNIQUE index */
  int regData, regTupleid; /* Registers holding data and tupleid */
  struct session *user_session = current_session();
+ bool is_err_action_default = false;

  if (pSelect == NULL)
  return 0; /* Must be of the form  INSERT INTO ... SELECT ... */
@@ -1744,8 +1746,10 @@ xferOptimization(Parse * pParse, /* Parser context */
  if (onError == ON_CONFLICT_ACTION_DEFAULT) {
  if (pDest->iPKey >= 0)
  onError = pDest->keyConf;
- if (onError == ON_CONFLICT_ACTION_DEFAULT)
+ if (onError == ON_CONFLICT_ACTION_DEFAULT) {
  onError = ON_CONFLICT_ACTION_ABORT;
+ is_err_action_default = true;
+ }
  }
  assert(pSelect->pSrc); /* allocated even if there is no FROM clause */
  if (pSelect->pSrc->nSrc != 1) {
@@ -1848,9 +1852,6 @@ xferOptimization(Parse * pParse, /* Parser context */
  }
  }
  for (pDestIdx = pDest->pIndex; pDestIdx; pDestIdx = pDestIdx->pNext) {
- if (index_is_unique(pDestIdx)) {
- destHasUniqueIdx = 1;
- }
  for (pSrcIdx = pSrc->pIndex; pSrcIdx; pSrcIdx = pSrcIdx->pNext) {
  if (xferCompatibleIndex(pDestIdx, pSrcIdx))
  break;
@@ -1888,72 +1889,60 @@ xferOptimization(Parse * pParse, /* Parser context
*/
  * least a possibility, though it might only work if the destination
  * table (tab1) is initially empty.
  */
-#ifdef SQLITE_TEST
- sqlite3_xferopt_count++;
-#endif
+
  v = sqlite3GetVdbe(pParse);
  iSrc = pParse->nTab++;
  iDest = pParse->nTab++;
  regData = sqlite3GetTempReg(pParse);
  regTupleid = sqlite3GetTempReg(pParse);
  sqlite3OpenTable(pParse, iDest, pDest, OP_OpenWrite);
- assert(destHasUniqueIdx);
- if ((pDest->iPKey < 0 && pDest->pIndex != 0) /* (1) */
-     ||destHasUniqueIdx /* (2) */
-     || (onError != ON_CONFLICT_ACTION_ABORT
- && onError != ON_CONFLICT_ACTION_ROLLBACK) /* (3) */
-     ) {
- /* In some circumstances, we are able to run the xfer optimization
- * only if the destination table is initially empty.
- * This block generates code to make
- * that determination.
- *
- * Conditions under which the destination must be empty:
- *
- * (1) There is no INTEGER PRIMARY KEY but there are indices.
- *
- * (2) The destination has a unique index.  (The xfer optimization
- *     is unable to test uniqueness.)
- *
- * (3) onError is something other than ON_CONFLICT_ACTION_ABORT and
_ROLLBACK.
- */
+
+ struct space *src_space =
+ space_by_id(SQLITE_PAGENO_TO_SPACEID(pSrc->tnum));
+ struct space *dest_space =
+ space_by_id(SQLITE_PAGENO_TO_SPACEID(pDest->tnum));
+ struct index *src_idx = space_index(src_space, 0);
+ struct index *dest_idx = space_index(dest_space, 0);
+
+ /* Xfer optimization is unable to correctly insert data
+ * in case there's a conflict action other than *_ABORT.
+ * This is the reason we want to only run it if the
+ * destination table is initially empty.
+ * That block generates code to make that determination.
+ */
+
+ if (!(onError == ON_CONFLICT_ACTION_ABORT &&
+     is_err_action_default == false)) {
  addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0);
  VdbeCoverage(v);
  emptyDestTest = sqlite3VdbeAddOp0(v, OP_Goto);
  sqlite3VdbeJumpHere(v, addr1);
+#ifdef SQLITE_TEST
+ if (dest_idx->vtab->count(dest_idx, ITER_ALL, NULL, 0) == 0)
+ sql_xferOpt_count++;
+#endif
  }
-
- for (pDestIdx = pDest->pIndex; pDestIdx; pDestIdx = pDestIdx->pNext) {
- for (pSrcIdx = pSrc->pIndex; ALWAYS(pSrcIdx);
-      pSrcIdx = pSrcIdx->pNext) {
- if (xferCompatibleIndex(pDestIdx, pSrcIdx))
- break;
- }
- assert(pSrcIdx);
- struct space *src_space =
- space_by_id(SQLITE_PAGENO_TO_SPACEID(pSrcIdx->tnum));
- vdbe_emit_open_cursor(pParse, iSrc,
-       SQLITE_PAGENO_TO_INDEXID(pSrcIdx->tnum),
-       src_space);
- VdbeComment((v, "%s", pSrcIdx->zName));
- struct space *dest_space =
- space_by_id(SQLITE_PAGENO_TO_SPACEID(pDestIdx->tnum));
- vdbe_emit_open_cursor(pParse, iDest,
-       SQLITE_PAGENO_TO_INDEXID(pDestIdx->tnum),
-       dest_space);
- VdbeComment((v, "%s", pDestIdx->zName));
- addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);
- VdbeCoverage(v);
- sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);
- sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData);
- if (pDestIdx->idxType == SQLITE_IDXTYPE_PRIMARYKEY)
- sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE);
- sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1 + 1);
- VdbeCoverage(v);
- sqlite3VdbeJumpHere(v, addr1);
- sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
- sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
+#ifdef SQLITE_TEST
+ else {
+ sql_xferOpt_count++;
  }
+#endif
+
+ vdbe_emit_open_cursor(pParse, iSrc, 0, src_space);
+ VdbeComment((v, "%s", src_idx->def->name));
+ vdbe_emit_open_cursor(pParse, iDest, 0, dest_space);
+ VdbeComment((v, "%s", dest_idx->def->name));
+ addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);
+ VdbeCoverage(v);
+ sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);
+ sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData);
+ sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE);
+ sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1 + 1);
+ VdbeCoverage(v);
+ sqlite3VdbeJumpHere(v, addr1);
+ sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
+ sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
+
  if (emptySrcTest)
  sqlite3VdbeJumpHere(v, emptySrcTest);
  sqlite3ReleaseTempReg(pParse, regTupleid);
diff --git a/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua
b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua
new file mode 100755
index 0000000..e75fabc
--- /dev/null
+++ b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua
@@ -0,0 +1,706 @@
+#!/usr/bin/env tarantool
+test = require("sqltester")
+test:plan(44)
+
+local bfr, aftr
+
+test:do_catchsql_test(
+ "xfer-optimization-1.1",
+ [[
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER UNIQUE);
+ INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER UNIQUE);
+ INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.1>
+ 0
+ -- <xfer-optimization-1.1>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.2",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.2>
+ 1, 1, 2, 2, 3, 3
+ -- <xfer-optimization-1.2>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.3",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(id INTEGER PRIMARY KEY, b INTEGER);
+ CREATE TABLE t2(id INTEGER PRIMARY KEY, b INTEGER);
+ CREATE INDEX i1 ON t1(b);
+ CREATE INDEX i2 ON t2(b);
+ INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
+ INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.3>
+ 0
+ -- <xfer-optimization-1.3>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.4",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.4>
+ 1, 1, 2, 2, 3, 3
+ -- <xfer-optimization-1.4>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.5",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);
+ INSERT INTO t1 VALUES (1, 1, 2), (2, 2, 3), (3, 3, 4);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER);
+ INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.5>
+ 1, "table T2 has 2 columns but 3 values were supplied"
+ -- <xfer-optimization-1.5>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.6",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.6>
+
+ -- <xfer-optimization-1.6>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.7",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
+ INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER);
+ INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.7>
+ 0
+ -- <xfer-optimization-1.7>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.8",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.6>
+ 1, 1, 2, 2, 3, 3
+ -- <xfer-optimization-1.6>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.9",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
+ INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 2);
+ CREATE TABLE t2(b INTEGER, a INTEGER PRIMARY KEY);
+ INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.9>
+ 1, "Duplicate key exists in unique index 'sqlite_autoindex_T2_1' in space
'T2'"
+ -- <xfer-optimization-1.9>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.10",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.10>
+
+ -- <xfer-optimization-1.10>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.11",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
+ INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 2);
+ CREATE TABLE t2(b INTEGER PRIMARY KEY, a INTEGER);
+ INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.11>
+ 0
+ -- <xfer-optimization-1.11>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.12",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.12>
+ 1, 1, 2, 2, 3, 2
+ -- <xfer-optimization-1.12>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.13",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
+ INSERT INTO t1 VALUES (3, 3), (4, 4), (5, 5);
+ INSERT INTO t2 VALUES (1, 1), (2, 2);
+ INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.13>
+ 0
+ -- <xfer-optimization-1.13>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.14",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.14>
+ 1, 1, 2, 2, 3, 3, 4, 4, 5, 5
+ -- <xfer-optimization-1.14>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.15",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b UNIQUE);
+ INSERT INTO t1 VALUES (2, 2), (3, 3), (5, 5);
+ INSERT INTO t2 VALUES (1, 1), (4, 4);
+ INSERT OR ROLLBACK INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.15>
+ 0
+ -- <xfer-optimization-1.15>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.16",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.16>
+ 1, 1, 2, 2, 3, 3, 4, 4, 5, 5
+ -- <xfer-optimization-1.16>
+ })
+
+-- The following tests are supposed to test if xfer-optimization is
actually
+-- used in the given cases (if the conflict actually occurs):
+-- 1.0) insert w/o explicit confl. action & w/o index replace action
+-- 1.1) insert w/o explicit confl. action & w/ index replace action &
empty dest_table
+-- 1.2) insert w/o explicit confl. action & w/ index replace action &
non-empty dest_table
+-- 2) insert with abort
+-- 3.0) insert with rollback (into empty table)
+-- 3.1) insert with rollback (into non-empty table)
+-- 4) insert with replace
+-- 5) insert with fail
+-- 6) insert with ignore
+
+
+-- 1.0) insert w/o explicit confl. action & w/o index replace action
+-------------------------------------------------------------------------------------------
+
+bfr = box.sql.debug().sql_xferOpt_count
+
+test:do_catchsql_test(
+ "xfer-optimization-1.17",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
+ INSERT INTO t1 VALUES (1, 1), (3, 3), (5, 5);
+ INSERT INTO t2 VALUES (2, 2), (3, 4);
+ BEGIN;
+ INSERT INTO t2 VALUES (4, 4);
+ INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.17>
+ 1, "Duplicate key exists in unique index 'sqlite_autoindex_T2_1' in space
'T2'"
+ -- <xfer-optimization-1.17>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.18",
+ [[
+ INSERT INTO t2 VALUES (10, 10);
+ COMMIT;
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.18>
+ 2, 2, 3, 4, 4, 4, 10, 10
+ -- <xfer-optimization-1.18>
+ })
+
+aftr = box.sql.debug().sql_xferOpt_count
+
+test:do_test(
+ "xfer-optimization-1.19",
+ function()
+ if (aftr - bfr == 1) then
+ return {1}
+ end
+ if (aftr == bfr) then
+ return {0}
+ end
+ return {2}
+ end, {
+ -- <xfer-optimization-1.19>
+ 0
+ -- <xfer-optimization-1.19>
+ })
+
+-- 1.1) insert w/o explicit confl. action & w/ index replace action &
empty dest_table
+-------------------------------------------------------------------------------------------
+
+bfr = box.sql.debug().sql_xferOpt_count
+
+test:do_catchsql_test(
+ "xfer-optimization-1.20",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
+ CREATE TABLE t3(id INT PRIMARY KEY);
+ INSERT INTO t1 VALUES (1, 1), (3, 3), (5, 5);
+ BEGIN;
+ INSERT INTO t3 VALUES (1);
+ INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.20>
+ 0
+ -- <xfer-optimization-1.20>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.21",
+ [[
+ INSERT INTO t2 VALUES (10, 10);
+ COMMIT;
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.21>
+ 1, 1, 3, 3, 5, 5, 10, 10
+ -- <xfer-optimization-1.21>
+ })
+
+aftr = box.sql.debug().sql_xferOpt_count
+
+test:do_execsql_test(
+ "xfer-optimization-1.22",
+ [[
+ SELECT * FROM t3;
+ ]], {
+ -- <xfer-optimization-1.22>
+ 1
+ -- <xfer-optimization-1.22>
+ })
+
+test:do_test(
+ "xfer-optimization-1.23",
+ function()
+ if (aftr - bfr == 1) then
+ return {1}
+ end
+ if (aftr == bfr) then
+ return {0}
+ end
+ return {2}
+ end, {
+ -- <xfer-optimization-1.23>
+ 1
+ -- <xfer-optimization-1.23>
+ })
+
+-- 1.2) insert w/o explicit confl. action & w/ index replace action &
non-empty dest_table
+-------------------------------------------------------------------------------------------
+
+bfr = box.sql.debug().sql_xferOpt_count
+
+test:do_catchsql_test(
+ "xfer-optimization-1.24",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ DROP TABLE t3;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
+ INSERT INTO t1 VALUES (1, 1), (3, 3), (5, 5);
+ INSERT INTO t2 VALUES (2, 2), (3, 4);
+ BEGIN;
+ INSERT INTO t2 VALUES (4, 4);
+ INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.24>
+ 0
+ -- <xfer-optimization-1.24>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.25",
+ [[
+ INSERT INTO t2 VALUES (10, 10);
+ COMMIT;
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.25>
+ 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 10, 10
+ -- <xfer-optimization-1.25>
+ })
+
+aftr = box.sql.debug().sql_xferOpt_count
+
+test:do_test(
+ "xfer-optimization-1.26",
+ function()
+ if (aftr - bfr == 1) then
+ return {1}
+ end
+ if (aftr == bfr) then
+ return {0}
+ end
+ return {2}
+ end, {
+ -- <xfer-optimization-1.26>
+ 0
+ -- <xfer-optimization-1.26>
+ })
+
+-- 2) insert with abort
+-------------------------------------------------------------------------------------------
+
+bfr = box.sql.debug().sql_xferOpt_count
+
+test:do_catchsql_test(
+ "xfer-optimization-1.27",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
+ INSERT INTO t1 VALUES (1, 1), (3, 3), (5, 5);
+ INSERT INTO t2 VALUES (2, 2), (3, 4);
+ BEGIN;
+ INSERT INTO t2 VALUES (4, 4);
+ INSERT OR ABORT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.27>
+ 1, "Duplicate key exists in unique index 'sqlite_autoindex_T2_1' in space
'T2'"
+ -- <xfer-optimization-1.27>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.28",
+ [[
+ INSERT INTO t2 VALUES (10, 10);
+ COMMIT;
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.28>
+ 2, 2, 3, 4, 4, 4, 10, 10
+ -- <xfer-optimization-1.28>
+ })
+
+aftr = box.sql.debug().sql_xferOpt_count
+
+test:do_test(
+ "xfer-optimization-1.29",
+ function()
+ if (aftr - bfr == 1) then
+ return {1}
+ end
+ if (aftr == bfr) then
+ return {0}
+ end
+ return {2}
+ end, {
+ -- <xfer-optimization-1.29>
+ 1
+ -- <xfer-optimization-1.29>
+ })
+
+-- 3.0) insert with rollback (into empty table)
+-------------------------------------------------------------------------------------------
+
+bfr = box.sql.debug().sql_xferOpt_count
+
+test:do_catchsql_test(
+ "xfer-optimization-1.30",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
+ INSERT INTO t1 VALUES (1, 1), (3, 3), (5, 5);
+ BEGIN;
+ INSERT OR ROLLBACK INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.30>
+ 0
+ -- <xfer-optimization-1.30>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.31",
+ [[
+ INSERT INTO t2 VALUES (10, 10);
+ COMMIT;
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.31>
+ 1, 1, 3, 3, 5, 5, 10, 10
+ -- <xfer-optimization-1.31>
+ })
+
+aftr = box.sql.debug().sql_xferOpt_count
+
+test:do_test(
+ "xfer-optimization-1.32",
+ function()
+ if (aftr - bfr == 1) then
+ return {1}
+ end
+ if (aftr == bfr) then
+ return {0}
+ end
+ return {2}
+ end, {
+ -- <xfer-optimization-1.32>
+ 1
+ -- <xfer-optimization-1.32>
+ })
+
+-- 3.1) insert with rollback (into non-empty table)
+-------------------------------------------------------------------------------------------
+
+bfr = box.sql.debug().sql_xferOpt_count
+
+test:do_catchsql_test(
+ "xfer-optimization-1.33",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
+ INSERT INTO t1 VALUES (1, 1), (3, 3), (5, 5);
+ INSERT INTO t2 VALUES (2, 2), (3, 4);
+ BEGIN;
+ INSERT INTO t2 VALUES (4, 4);
+ INSERT OR ROLLBACK INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.33>
+ 1, "UNIQUE constraint failed: T2.A"
+ -- <xfer-optimization-1.33>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.34",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.34>
+ 2, 2, 3, 4
+ -- <xfer-optimization-1.34>
+ })
+
+aftr = box.sql.debug().sql_xferOpt_count
+
+test:do_test(
+ "xfer-optimization-1.35",
+ function()
+ if (aftr - bfr == 1) then
+ return {1}
+ end
+ if (aftr == bfr) then
+ return {0}
+ end
+ return {2}
+ end, {
+ -- <xfer-optimization-1.35>
+ 0
+ -- <xfer-optimization-1.35>
+ })
+
+-- 4) insert with replace
+-------------------------------------------------------------------------------------------
+
+bfr = box.sql.debug().sql_xferOpt_count
+
+test:do_catchsql_test(
+ "xfer-optimization-1.36",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
+ INSERT INTO t1 VALUES (1, 1), (3, 3), (5, 5);
+ INSERT INTO t2 VALUES (2, 2), (3, 4);
+ BEGIN;
+ INSERT INTO t2 VALUES (4, 4);
+ INSERT OR REPLACE INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.36>
+ 0
+ -- <xfer-optimization-1.36>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.37",
+ [[
+ INSERT INTO t2 VALUES (10, 10);
+ COMMIT;
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.37>
+ 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 10, 10
+ -- <xfer-optimization-1.37>
+ })
+
+aftr = box.sql.debug().sql_xferOpt_count
+
+test:do_test(
+ "xfer-optimization-1.38",
+ function()
+ if (aftr - bfr == 1) then
+ return {1}
+ end
+ if (aftr == bfr) then
+ return {0}
+ end
+ return {2}
+ end, {
+ -- <xfer-optimization-1.38>
+ 0
+ -- <xfer-optimization-1.38>
+ })
+
+-- 5) insert with fail
+-------------------------------------------------------------------------------------------
+
+bfr = box.sql.debug().sql_xferOpt_count
+
+test:do_catchsql_test(
+ "xfer-optimization-1.39",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
+ INSERT INTO t1 VALUES (1, 1), (3, 3), (5, 5);
+ INSERT INTO t2 VALUES (2, 2), (3, 4);
+ BEGIN;
+ INSERT INTO t2 VALUES (4, 4);
+ INSERT OR FAIL INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.39>
+ 1, "Duplicate key exists in unique index 'sqlite_autoindex_T2_1' in space
'T2'"
+ -- <xfer-optimization-1.39>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.40",
+ [[
+ INSERT INTO t2 VALUES (10, 10);
+ COMMIT;
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.40>
+ 1, 1, 2, 2, 3, 4, 4, 4, 10, 10
+ -- <xfer-optimization-1.40>
+ })
+
+aftr = box.sql.debug().sql_xferOpt_count
+
+test:do_test(
+ "xfer-optimization-1.41",
+ function()
+ if (aftr - bfr == 1) then
+ return {1}
+ end
+ if (aftr == bfr) then
+ return {0}
+ end
+ return {2}
+ end, {
+ -- <xfer-optimization-1.41>
+ 0
+ -- <xfer-optimization-1.41>
+ })
+
+-- 6) insert with ignore
+-------------------------------------------------------------------------------------------
+
+bfr = box.sql.debug().sql_xferOpt_count
+
+test:do_catchsql_test(
+ "xfer-optimization-1.42",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
+ INSERT INTO t1 VALUES (1, 1), (3, 3), (5, 5);
+ INSERT INTO t2 VALUES (2, 2), (3, 4);
+ BEGIN;
+ INSERT INTO t2 VALUES (4, 4);
+ INSERT OR IGNORE INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.42>
+ 0
+ -- <xfer-optimization-1.42>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.43",
+ [[
+ INSERT INTO t2 VALUES (10, 10);
+ COMMIT;
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.43>
+ 1, 1, 2, 2, 3, 4, 4, 4, 5, 5, 10, 10
+ -- <xfer-optimization-1.43>
+ })
+
+aftr = box.sql.debug().sql_xferOpt_count
+
+test:do_test(
+ "xfer-optimization-1.44",
+ function()
+ if (aftr - bfr == 1) then
+ return {1}
+ end
+ if (aftr == bfr) then
+ return {0}
+ end
+ return {2}
+ end, {
+ -- <xfer-optimization-1.44>
+ 0
+ -- <xfer-optimization-1.44>
+ })
+
+test:finish_test()


пн, 16 июл. 2018 г. в 22:12, n.pettik <korablev@tarantool.org>:

>
> > Here's diff for the fixed and rebased patch. There're some unexpected
> things concerned with the check of emptiness of destination table:
> internals can only correctly deal with ABORT conflict action and also my
> 'small optimization of optimization' was incorrect.
>
> I see no diff, actually. You haven’t attached it to the letter.

[-- Attachment #2: Type: text/html, Size: 60612 bytes --]