From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F00EB26648 for ; Mon, 16 Jul 2018 17:27:14 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id ilVv7w6HnwOQ for ; Mon, 16 Jul 2018 17:27:14 -0400 (EDT) Received: from mail-lj1-f194.google.com (mail-lj1-f194.google.com [209.85.208.194]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 1B58926646 for ; Mon, 16 Jul 2018 17:27:13 -0400 (EDT) Received: by mail-lj1-f194.google.com with SMTP id j19-v6so5807791ljc.7 for ; Mon, 16 Jul 2018 14:27:13 -0700 (PDT) MIME-Version: 1.0 References: <5BB99B27-5F86-4664-AAD5-57A22ECED854@tarantool.org> <93E4DAEA-EF90-479D-9F62-3D1CEB3CBE3F@tarantool.org> <20180628101839.fhnijezdpwviohop@tkn_work_nb> <20180709155006.fwrikbznqk23ger5@tkn_work_nb> <79D03E96-0BD0-418D-9DB2-45318C734628@tarantool.org> <8B8D5501-075D-4BEB-B282-35B0B81CD555@tarantool.org> In-Reply-To: <8B8D5501-075D-4BEB-B282-35B0B81CD555@tarantool.org> From: Nikita Tatunov Date: Tue, 17 Jul 2018 00:27:00 +0300 Message-ID: Subject: [tarantool-patches] Re: [PATCH] sql: xfer optimization issue Content-Type: multipart/alternative; boundary="0000000000001c91690571247ea8" Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-subscribe: List-owner: List-post: List-archive: To: korablev@tarantool.org Cc: tarantool-patches@freelists.org --0000000000001c91690571247ea8 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 =3D 0; #endif /* SQLITE_TEST */ #ifndef SQLITE_OMIT_XFER_OPT @@ -1658,6 +1658,8 @@ xferCompatibleIndex(Index * pDest, Index * pSrc) assert(pDest->pTable !=3D pSrc->pTable); uint32_t nDestCol =3D index_column_count(pDest); uint32_t nSrcCol =3D index_column_count(pSrc); + if ((pDest->idxType !=3D pSrc->idxType)) + return 0; if (nDestCol !=3D nSrcCol) { return 0; /* Different number of columns */ } @@ -1725,9 +1727,9 @@ xferOptimization(Parse * pParse, /* Parser context */ int emptyDestTest =3D 0; /* Address of test for empty pDest */ int emptySrcTest =3D 0; /* Address of test for empty pSrc */ Vdbe *v; /* The VDBE we are building */ - int destHasUniqueIdx =3D 0; /* True if pDest has a UNIQUE index */ int regData, regTupleid; /* Registers holding data and tupleid */ struct session *user_session =3D current_session(); + bool is_err_action_default =3D false; if (pSelect =3D=3D NULL) return 0; /* Must be of the form INSERT INTO ... SELECT ... */ @@ -1744,8 +1746,10 @@ xferOptimization(Parse * pParse, /* Parser context *= / if (onError =3D=3D ON_CONFLICT_ACTION_DEFAULT) { if (pDest->iPKey >=3D 0) onError =3D pDest->keyConf; - if (onError =3D=3D ON_CONFLICT_ACTION_DEFAULT) + if (onError =3D=3D ON_CONFLICT_ACTION_DEFAULT) { onError =3D ON_CONFLICT_ACTION_ABORT; + is_err_action_default =3D true; + } } assert(pSelect->pSrc); /* allocated even if there is no FROM clause */ if (pSelect->pSrc->nSrc !=3D 1) { @@ -1848,9 +1852,6 @@ xferOptimization(Parse * pParse, /* Parser context */ } } for (pDestIdx =3D pDest->pIndex; pDestIdx; pDestIdx =3D pDestIdx->pNext) = { - if (index_is_unique(pDestIdx)) { - destHasUniqueIdx =3D 1; - } for (pSrcIdx =3D pSrc->pIndex; pSrcIdx; pSrcIdx =3D 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 =3D sqlite3GetVdbe(pParse); iSrc =3D pParse->nTab++; iDest =3D pParse->nTab++; regData =3D sqlite3GetTempReg(pParse); regTupleid =3D sqlite3GetTempReg(pParse); sqlite3OpenTable(pParse, iDest, pDest, OP_OpenWrite); - assert(destHasUniqueIdx); - if ((pDest->iPKey < 0 && pDest->pIndex !=3D 0) /* (1) */ - ||destHasUniqueIdx /* (2) */ - || (onError !=3D ON_CONFLICT_ACTION_ABORT - && onError !=3D 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 =3D + space_by_id(SQLITE_PAGENO_TO_SPACEID(pSrc->tnum)); + struct space *dest_space =3D + space_by_id(SQLITE_PAGENO_TO_SPACEID(pDest->tnum)); + struct index *src_idx =3D space_index(src_space, 0); + struct index *dest_idx =3D 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 =3D=3D ON_CONFLICT_ACTION_ABORT && + is_err_action_default =3D=3D false)) { addr1 =3D sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0); VdbeCoverage(v); emptyDestTest =3D sqlite3VdbeAddOp0(v, OP_Goto); sqlite3VdbeJumpHere(v, addr1); +#ifdef SQLITE_TEST + if (dest_idx->vtab->count(dest_idx, ITER_ALL, NULL, 0) =3D=3D 0) + sql_xferOpt_count++; +#endif } - - for (pDestIdx =3D pDest->pIndex; pDestIdx; pDestIdx =3D pDestIdx->pNext) = { - for (pSrcIdx =3D pSrc->pIndex; ALWAYS(pSrcIdx); - pSrcIdx =3D pSrcIdx->pNext) { - if (xferCompatibleIndex(pDestIdx, pSrcIdx)) - break; - } - assert(pSrcIdx); - struct space *src_space =3D - 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 =3D - 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 =3D sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); - VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData); - sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData); - if (pDestIdx->idxType =3D=3D 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 =3D 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 =3D 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; + ]], { + -- + 0 + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.2", + [[ + SELECT * FROM t2; + ]], { + -- + 1, 1, 2, 2, 3, 3 + -- + }) + +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; + ]], { + -- + 0 + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.4", + [[ + SELECT * FROM t2; + ]], { + -- + 1, 1, 2, 2, 3, 3 + -- + }) + +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; + ]], { + -- + 1, "table T2 has 2 columns but 3 values were supplied" + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.6", + [[ + SELECT * FROM t2; + ]], { + -- + + -- + }) + +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; + ]], { + -- + 0 + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.8", + [[ + SELECT * FROM t2; + ]], { + -- + 1, 1, 2, 2, 3, 3 + -- + }) + +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; + ]], { + -- + 1, "Duplicate key exists in unique index 'sqlite_autoindex_T2_1' in space 'T2'" + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.10", + [[ + SELECT * FROM t2; + ]], { + -- + + -- + }) + +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; + ]], { + -- + 0 + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.12", + [[ + SELECT * FROM t2; + ]], { + -- + 1, 1, 2, 2, 3, 2 + -- + }) + +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; + ]], { + -- + 0 + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.14", + [[ + SELECT * FROM t2; + ]], { + -- + 1, 1, 2, 2, 3, 3, 4, 4, 5, 5 + -- + }) + +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; + ]], { + -- + 0 + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.16", + [[ + SELECT * FROM t2; + ]], { + -- + 1, 1, 2, 2, 3, 3, 4, 4, 5, 5 + -- + }) + +-- 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 =3D 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; + ]], { + -- + 1, "Duplicate key exists in unique index 'sqlite_autoindex_T2_1' in space 'T2'" + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.18", + [[ + INSERT INTO t2 VALUES (10, 10); + COMMIT; + SELECT * FROM t2; + ]], { + -- + 2, 2, 3, 4, 4, 4, 10, 10 + -- + }) + +aftr =3D box.sql.debug().sql_xferOpt_count + +test:do_test( + "xfer-optimization-1.19", + function() + if (aftr - bfr =3D=3D 1) then + return {1} + end + if (aftr =3D=3D bfr) then + return {0} + end + return {2} + end, { + -- + 0 + -- + }) + +-- 1.1) insert w/o explicit confl. action & w/ index replace action & empty dest_table +--------------------------------------------------------------------------= ----------------- + +bfr =3D 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; + ]], { + -- + 0 + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.21", + [[ + INSERT INTO t2 VALUES (10, 10); + COMMIT; + SELECT * FROM t2; + ]], { + -- + 1, 1, 3, 3, 5, 5, 10, 10 + -- + }) + +aftr =3D box.sql.debug().sql_xferOpt_count + +test:do_execsql_test( + "xfer-optimization-1.22", + [[ + SELECT * FROM t3; + ]], { + -- + 1 + -- + }) + +test:do_test( + "xfer-optimization-1.23", + function() + if (aftr - bfr =3D=3D 1) then + return {1} + end + if (aftr =3D=3D bfr) then + return {0} + end + return {2} + end, { + -- + 1 + -- + }) + +-- 1.2) insert w/o explicit confl. action & w/ index replace action & non-empty dest_table +--------------------------------------------------------------------------= ----------------- + +bfr =3D 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; + ]], { + -- + 0 + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.25", + [[ + INSERT INTO t2 VALUES (10, 10); + COMMIT; + SELECT * FROM t2; + ]], { + -- + 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 10, 10 + -- + }) + +aftr =3D box.sql.debug().sql_xferOpt_count + +test:do_test( + "xfer-optimization-1.26", + function() + if (aftr - bfr =3D=3D 1) then + return {1} + end + if (aftr =3D=3D bfr) then + return {0} + end + return {2} + end, { + -- + 0 + -- + }) + +-- 2) insert with abort +--------------------------------------------------------------------------= ----------------- + +bfr =3D 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; + ]], { + -- + 1, "Duplicate key exists in unique index 'sqlite_autoindex_T2_1' in space 'T2'" + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.28", + [[ + INSERT INTO t2 VALUES (10, 10); + COMMIT; + SELECT * FROM t2; + ]], { + -- + 2, 2, 3, 4, 4, 4, 10, 10 + -- + }) + +aftr =3D box.sql.debug().sql_xferOpt_count + +test:do_test( + "xfer-optimization-1.29", + function() + if (aftr - bfr =3D=3D 1) then + return {1} + end + if (aftr =3D=3D bfr) then + return {0} + end + return {2} + end, { + -- + 1 + -- + }) + +-- 3.0) insert with rollback (into empty table) +--------------------------------------------------------------------------= ----------------- + +bfr =3D 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; + ]], { + -- + 0 + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.31", + [[ + INSERT INTO t2 VALUES (10, 10); + COMMIT; + SELECT * FROM t2; + ]], { + -- + 1, 1, 3, 3, 5, 5, 10, 10 + -- + }) + +aftr =3D box.sql.debug().sql_xferOpt_count + +test:do_test( + "xfer-optimization-1.32", + function() + if (aftr - bfr =3D=3D 1) then + return {1} + end + if (aftr =3D=3D bfr) then + return {0} + end + return {2} + end, { + -- + 1 + -- + }) + +-- 3.1) insert with rollback (into non-empty table) +--------------------------------------------------------------------------= ----------------- + +bfr =3D 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; + ]], { + -- + 1, "UNIQUE constraint failed: T2.A" + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.34", + [[ + SELECT * FROM t2; + ]], { + -- + 2, 2, 3, 4 + -- + }) + +aftr =3D box.sql.debug().sql_xferOpt_count + +test:do_test( + "xfer-optimization-1.35", + function() + if (aftr - bfr =3D=3D 1) then + return {1} + end + if (aftr =3D=3D bfr) then + return {0} + end + return {2} + end, { + -- + 0 + -- + }) + +-- 4) insert with replace +--------------------------------------------------------------------------= ----------------- + +bfr =3D 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; + ]], { + -- + 0 + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.37", + [[ + INSERT INTO t2 VALUES (10, 10); + COMMIT; + SELECT * FROM t2; + ]], { + -- + 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 10, 10 + -- + }) + +aftr =3D box.sql.debug().sql_xferOpt_count + +test:do_test( + "xfer-optimization-1.38", + function() + if (aftr - bfr =3D=3D 1) then + return {1} + end + if (aftr =3D=3D bfr) then + return {0} + end + return {2} + end, { + -- + 0 + -- + }) + +-- 5) insert with fail +--------------------------------------------------------------------------= ----------------- + +bfr =3D 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; + ]], { + -- + 1, "Duplicate key exists in unique index 'sqlite_autoindex_T2_1' in space 'T2'" + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.40", + [[ + INSERT INTO t2 VALUES (10, 10); + COMMIT; + SELECT * FROM t2; + ]], { + -- + 1, 1, 2, 2, 3, 4, 4, 4, 10, 10 + -- + }) + +aftr =3D box.sql.debug().sql_xferOpt_count + +test:do_test( + "xfer-optimization-1.41", + function() + if (aftr - bfr =3D=3D 1) then + return {1} + end + if (aftr =3D=3D bfr) then + return {0} + end + return {2} + end, { + -- + 0 + -- + }) + +-- 6) insert with ignore +--------------------------------------------------------------------------= ----------------- + +bfr =3D 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; + ]], { + -- + 0 + -- + }) + +test:do_execsql_test( + "xfer-optimization-1.43", + [[ + INSERT INTO t2 VALUES (10, 10); + COMMIT; + SELECT * FROM t2; + ]], { + -- + 1, 1, 2, 2, 3, 4, 4, 4, 5, 5, 10, 10 + -- + }) + +aftr =3D box.sql.debug().sql_xferOpt_count + +test:do_test( + "xfer-optimization-1.44", + function() + if (aftr - bfr =3D=3D 1) then + return {1} + end + if (aftr =3D=3D bfr) then + return {0} + end + return {2} + end, { + -- + 0 + -- + }) + +test:finish_test() =D0=BF=D0=BD, 16 =D0=B8=D1=8E=D0=BB. 2018 =D0=B3. =D0=B2 22:12, n.pettik : > > > 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=E2=80=99t attached it to the letter. --0000000000001c91690571247ea8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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)
=C2=A0 extern= int sql_search_count;
=C2=A0 extern int sql_sort_count;
=C2=A0 extern int sql_found_count;
+ extern int sql_xferOpt_count;
=C2=A0 info_begin(h);
=C2=A0 info_append_int(h, "sql_search_count", s= ql_search_count);
=C2=A0 i= nfo_append_int(h, "sql_sort_count", sql_sort_count);
= =C2=A0 info_append_int(h, "sql_= found_count", sql_found_count);
+ info_append_int(h, "sql_xferOpt_count", sql_xferOpt_= count);
=C2=A0 info_end(h)= ;
=C2=A0}
=C2=A0
diff --git a/src/box/sql/ins= ert.c b/src/box/sql/insert.c
index 2c9188e..9a99bab 100644
<= div>--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -1635,7 +1635,7 @@ sqlite3OpenTableAndIndices(Parse * pParse, /* Parsing context */
=C2=A0 * pu= rposes only - to make sure the transfer optimization really
=C2= =A0 * is happening when it is supposed to.
=C2=A0 */
-i= nt sqlite3_xferopt_count;
+int sql_xferOpt_count =3D 0;
=C2=A0#endif /* SQLITE_TEST */
=C2=A0
=C2=A0#ifndef SQLITE_OMIT_XFER_OPT
@@ -= 1658,6 +1658,8 @@ xferCompatibleIndex(Index * pDest, Index * pSrc)
=C2=A0 assert(pDest->pTable != =3D pSrc->pTable);
=C2=A0 uint32_t nDestCol =3D index_column_count(pDest);
=C2=A0 uint32_t nSrcCol =3D index_column_count(pS= rc);
+ if ((pDest->idxT= ype !=3D pSrc->idxType))
+ return 0;
=C2=A0 if (= nDestCol !=3D nSrcCol) {
=C2=A0 = return 0; /* Different number= of columns */
=C2=A0 }
@@ -1725,9 +1727,9 @@ xferOptimization(Parse * pParse, /* Parser context */
=C2=A0 int emptyDestTest =3D 0; /* Address of test for empty pDest */
=C2=A0<= span style=3D"white-space:pre"> int emptySrcTest =3D 0; /* Address of test for empty pSrc */
=C2=A0 Vdbe *v; /* The VDBE we are building */
- int destHasUniqueIdx =3D 0; /* True if pDest has a UNIQUE index */
=C2= =A0 int regData, regTupleid; /* Registers holding data and tupleid */
=C2=A0 struct session *user_= session =3D current_session();
+ = bool is_err_action_default =3D false;
=C2=A0
=C2= =A0 if (pSelect =3D=3D NULL)
=C2=A0 return 0; /* Must be of the form=C2=A0 INSERT INTO ... SELECT= ... */
@@ -1744,8 +1746,10 @@ xferOptimization(Parse * pParse, /* Parser context */
=C2=A0= if (onError =3D=3D ON_CONFLICT_ACTI= ON_DEFAULT) {
=C2=A0 if (= pDest->iPKey >=3D 0)
=C2=A0= onError =3D pDest->keyConf;
- if (onError =3D=3D ON_CONFLICT_ACTION_DEFAULT)
= + if (onError =3D=3D ON_CONFLICT_AC= TION_DEFAULT) {
=C2=A0 o= nError =3D ON_CONFLICT_ACTION_ABORT;
+ is_err_action_default =3D true;
+ }
=C2=A0 = }
=C2=A0 assert(pSe= lect->pSrc); /* allocated even if= there is no FROM clause */
=C2=A0 if (pSelect->pSrc->nSrc !=3D 1) {
@@ -1848,9 +1852= ,6 @@ xferOptimization(Parse * pParse, /* Parser context */
=C2=A0 <= /span>}
=C2=A0 }
=C2=A0 for (pDestIdx =3D pDest->= pIndex; pDestIdx; pDestIdx =3D pDestIdx->pNext) {
- if (index_is_unique(pDestIdx)) {
-<= span style=3D"white-space:pre"> destHasUniqueIdx =3D 1;
= - }
=C2=A0 for (pSrcIdx =3D pSrc->pIndex; pSrcIdx; pSrcId= x =3D pSrcIdx->pNext) {
=C2=A0= if (xferCompatibleIndex(pDestIdx, pSrcIdx))
=C2=A0 break;
@@ -1888,72 +1889,60= @@ xferOptimization(Parse * pParse, /* Parser context */
=C2=A0 * least a possibility, though it might only work if the destination
=C2=A0 * table (tab1) is ini= tially empty.
=C2=A0 */
-#ifdef SQLITE_TEST
- sqlite3_xferopt_count++;
-#endif
+
=C2= =A0 v =3D sqlite3GetVdbe(pParse);
=C2=A0 iSrc =3D pParse->n= Tab++;
=C2=A0 iDest =3D pP= arse->nTab++;
=C2=A0 re= gData =3D sqlite3GetTempReg(pParse);
=C2=A0 regTupleid =3D sqlite3GetTempReg(pParse);
=C2= =A0 sqlite3OpenTable(pParse, iDest, = pDest, OP_OpenWrite);
- as= sert(destHasUniqueIdx);
- = if ((pDest->iPKey < 0 && pDest->pIndex !=3D 0) /* (1) */
- =C2=A0 =C2=A0 ||destHasUniqueIdx /* (2) */
- = =C2=A0 =C2=A0 || (onError !=3D ON_CONFLICT_ACTION_ABORT
- && onError !=3D ON_CONFLICT_ACTION= _ROLLBACK) /* (3) */
- =C2=A0 =C2=A0 ) {
- /* 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 e= mpty:
- *
- * (1) There is no INTEGER PRIMARY KE= Y but there are indices.
- *
- * (2) The destina= tion has a unique index.=C2=A0 (The xfer optimization
- *=C2=A0 =C2=A0 =C2=A0is unable to test uniq= ueness.)
- *
-= * (3) onError is something other = than ON_CONFLICT_ACTION_ABORT and _ROLLBACK.
- */
+
+ struct space *src_space =3D
+ space_by_id(SQLITE_PAGENO_TO_SPACEID(pSrc->tnum));
+ struct space *dest_space = =3D
+ space_by_id(SQLITE_= PAGENO_TO_SPACEID(pDest->tnum));
+ struct index *src_idx =3D space_index(src_space, 0);
= + struct index *dest_idx =3D space_i= ndex(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 cod= e to make that determination.
+ <= /span> */
+
+ if= (!(onError =3D=3D ON_CONFLICT_ACTION_ABORT &&
+ =C2=A0 =C2=A0 is_err_action_default =3D=3D f= alse)) {
=C2=A0 addr1 =3D= sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0);
=C2=A0 VdbeCoverage(v);
=C2=A0 emptyDestTest =3D sqlite3VdbeAddOp0(v, OP_Goto);
=C2=A0 sqlite3VdbeJumpHere= (v, addr1);
+#ifdef SQLITE_TEST
+ if (dest_idx->vtab->count(dest_idx, ITER_ALL, NULL= , 0) =3D=3D 0)
+ sql_xfe= rOpt_count++;
+#endif
=C2=A0 }
-
- for (pDestIdx =3D pDest->pIndex; pDestIdx; pDestIdx =3D pDestIdx->= pNext) {
- for (pSrcIdx = =3D pSrc->pIndex; ALWAYS(pSrcIdx);
- =C2=A0 =C2=A0 =C2=A0pSrcIdx =3D pSrcIdx->pNext) {
- if (xferCompatibleIndex(pDest= Idx, pSrcIdx))
- break;=
- }
- assert(pSrcIdx);
- struct space *src_space =3D
- space_by_id(SQLITE_PAGENO_TO_SPACEID(pSrcId= x->tnum));
- vdbe_emit= _open_cursor(pParse, iSrc,
- <= /span>=C2=A0 =C2=A0 =C2=A0 SQLITE_PAGENO_TO_INDEXID(pSrcIdx->tnum),
- =C2=A0 =C2=A0 =C2=A0 src_= space);
- VdbeComment((v,= "%s", pSrcIdx->zName));
- struct space *dest_space =3D
- space_by_id(SQLITE_PAGENO_TO_SPACEID(pDestIdx->tnu= m));
- vdbe_emit_open_cur= sor(pParse, iDest,
- = =C2=A0 =C2=A0 =C2=A0 SQLITE_PAGENO_TO_INDEXID(pDestIdx->tnum),
- =C2=A0 =C2=A0 =C2=A0 dest_spac= e);
- VdbeComment((v, &qu= ot;%s", pDestIdx->zName));
- addr1 =3D sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);
-= VdbeCoverage(v);
- sqlite3VdbeAddOp2(v, OP_RowData, iSrc, r= egData);
- sqlite3VdbeAdd= Op2(v, OP_IdxInsert, iDest, regData);
- if (pDestIdx->idxType =3D=3D SQLITE_IDXTYPE_PRIMARYKEY)
- sqlite3VdbeChangeP5(v, = OPFLAG_NCHANGE);
- sqlite= 3VdbeAddOp2(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++;
=C2=A0 }<= /div>
+#endif
+
+ <= /span>vdbe_emit_open_cursor(pParse, iSrc, 0, src_space);
+ VdbeComment((v, "%s", src_idx-&g= t;def->name));
+ vdbe_e= mit_open_cursor(pParse, iDest, 0, dest_space);
+ VdbeComment((v, "%s", dest_idx->def->= ;name));
+ addr1 =3D sqlit= e3VdbeAddOp2(v, OP_Rewind, iSrc, 0);
+ VdbeCoverage(v);
+ <= /span>sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);
+ sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, re= gData);
+ sqlite3VdbeChang= eP5(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, iDe= st, 0);
+
=C2=A0 if (emptySrcTest)
=C2=A0 sqlite3VdbeJumpHere(v, emptySrcTest);
=C2=A0 sqlite3ReleaseTempReg(pParse, regTupleid);
di= ff --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 10075= 5
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 =3D require(&quo= t;sqltester")
+test:plan(44)
+
+local bf= r, aftr
+
+test:do_catchsql_test(
+ "xfer-optimization-1.1",
= + [[
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER UNIQU= E);
+ INSERT INTO t1 VALU= ES (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-optimizatio= n-1.2",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.2>
+ 1, 1, 2, 2, 3, 3
+ -- <xfer-optimization-1.2>
+ })
+
+test:do_catchsq= l_test(
+ "xfer-optim= ization-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);
<= div>+ INSERT INTO t2 SELECT * FROM = t1;
+ ]], {
+ -- <xfer-optimization-1.3>
+ 0
+ -- <xfer-optimization-1.3>
+ })
+
+test:do_execs= ql_test(
+ "xfer-opti= mization-1.4",
+ [[
+ SELECT * FROM t2;
<= div>+ ]], {
+ -- <xfer-optimization-1.4>
+<= span style=3D"white-space:pre"> 1, 1, 2, 2, 3, 3
+ -- <xfer-optimization-1.4>
+ })
+
+test:d= o_catchsql_test(
+ "x= fer-optimization-1.5",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMAR= Y KEY, b INTEGER, c INTEGER);
+ = INSERT INTO t1 VALUES (1, 1, 2), (2, 2, 3), (3, 3, 4);
+ CREATE TABLE t2(a INTEGER PRIMARY KE= Y, b INTEGER);
+ INSERT I= NTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-opti= mization-1.5>
+ 1, &qu= ot;table T2 has 2 columns but 3 values were supplied"
+ -- <xfer-optimization-1.5>
<= div>+ })
+
+test= :do_execsql_test(
+ "= xfer-optimization-1.6",
+ [[
+ SELECT * FROM t= 2;
+ ]], {
+ -- <xfer-optimization-1.6>
=
+
+ -- <xfer-opti= mization-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 PRIMA= RY KEY, b INTEGER);
+ INS= ERT INTO t2 SELECT * FROM t1;
+ <= /span>]], {
+ -- <xfer= -optimization-1.7>
+ 0=
+ -- <xfer-optimizati= on-1.7>
+ })
= +
+test:do_execsql_test(
+ "xfer-optimization-1.8",
+ [[
+ SELECT * FROM t2;
+ ]], = {
+ -- <xfer-optimizat= ion-1.6>
+ 1, 1, 2, 2,= 3, 3
+ -- <xfer-optim= ization-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 IN= TEGER PRIMARY KEY);
+ INS= ERT INTO t2 SELECT * FROM t1;
+ <= /span>]], {
+ -- <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(
+ "xf= er-optimization-1.11",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMAR= Y KEY, b INTEGER);
+ INSE= RT 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_e= xecsql_test(
+ "xfer-= optimization-1.12",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.12>
+ 1, 1, 2, 2, 3, 2
+ -- <xfer-optimization-1.12>
+ })
+
+t= est:do_catchsql_test(
+ &q= uot;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);<= /div>
+ INSERT INTO t2 SELECT *= FROM t1;
+ ]], {
+ -- <xfer-optimization-1.13&g= t;
+ 0
+ -- <xfer-optimization-1.13>
+ })
+
+test:= do_execsql_test(
+ "x= fer-optimization-1.14",
+ [[
+ SELECT * FROM t= 2;
+ ]], {
+ -- <xfer-optimization-1.14>
+ 1, 1, 2, 2, 3, 3, 4, 4, 5, = 5
+ -- <xfer-optimizat= ion-1.14>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.15",
+ [[
+ <= /span>DROP TABLE t1;
+ DR= OP TABLE t2;
+ CREATE TAB= LE 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 I= NTO 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 followin= g tests are supposed to test if xfer-optimization is actually
+--= used in the given cases (if the conflict actually occurs):
+-- <= span style=3D"white-space:pre"> 1.0) insert w/o explicit confl. acti= on & 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 acti= on & 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 wi= th fail
+-- 6) insert wit= h ignore
+
+
+-- 1.0) insert w/o explicit con= fl. action & w/o index replace action
+----------------------= ---------------------------------------------------------------------
=
+
+bfr =3D box.sql.debug().sql_xferOpt_count
+
+test:do_catchsql_test(
+ = "xfer-optimization-1.17",
+ [[
+ D= ROP TABLE t1;
+ DROP TABL= E 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);
<= div>+ INSERT INTO t2 VALUES (2, 2),= (3, 4);
+ BEGIN;
+ INSERT INTO t2 VALUES (4, 4);=
+ INSERT INTO t2 SELECT= * FROM t1;
+ ]], {
<= div>+ -- <xfer-optimization-1.17= >
+ 1, "Duplicate= key exists in unique index 'sqlite_autoindex_T2_1' in space 'T= 2'"
+ -- <xfe= r-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 =3D box.sql.debug().sql_xferOpt_c= ount
+
+test:do_test(
+ "xfer-optimization-1.19",
+ function()
+ if (aftr - bfr =3D=3D 1) then
+ return {1}
+ end
+ if (a= ftr =3D=3D bfr) then
+ r= eturn {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 =3D box.sql.debug().sql_xferOpt_cou= nt
+
+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);
+ <= /span>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 =3D box.= sql.debug().sql_xferOpt_count
+
+test:do_execsql_test(<= /div>
+ "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 =3D=3D 1) then
+ return {1}
+ end
+ if (aftr =3D=3D 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 =3D box.sql.d= ebug().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 IN= TEGER 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:d= o_execsql_test(
+ "xf= er-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-optimiz= ation-1.25>
+ })
<= div>+
+aftr =3D box.sql.debug().sql_xferOpt_count
+
+test:do_test(
+ &q= uot;xfer-optimization-1.26",
+ function()
+ if= (aftr - bfr =3D=3D 1) then
+ <= /span>return {1}
+ end
+ if (aftr =3D=3D bfr) then=
+ return {0}
= + end
+ return {2}
+ end, {
+ -- <= ;xfer-optimization-1.26>
+ 0
+ -- <xfer-opti= mization-1.26>
+ })
+
+-- 2) insert with abort
+--------------------= -----------------------------------------------------------------------
+
+bfr =3D box.sql.debug().sql_xferOpt_count
+
+test:do_catchsql_test(
+ "xfer-optimization-1.27",
+ [[
+ DROP TABLE t1;
+ DROP TA= BLE 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 INT= O t2 SELECT * FROM t1;
+ ]= ], {
+ -- <xfer-optimi= zation-1.27>
+ 1, &quo= t;Duplicate key exists in unique index 'sqlite_autoindex_T2_1' in s= pace '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 =3D box.sql.debug().sq= l_xferOpt_count
+
+test:do_test(
+ "xfer-optimization-1.29",
= + function()
+ if (aftr - bfr =3D=3D 1) then
+ return {1}
+ end
+ = if (aftr =3D=3D bfr) then
+ return {0}
+ e= nd
+ return {2}
+ end, {
+ -- <xfer-optimization-1.29>
+ 1
+ -- <xfer-optimization-1.29>
+ })
+
+-- 3.0) insert with rol= lback (into empty table)
+---------------------------------------= ----------------------------------------------------
+
= +bfr =3D box.sql.debug().sql_xferOpt_count
+
+test:do_c= atchsql_test(
+ "xfer= -optimization-1.30",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+= CREATE TABLE t1(a INTEGER PRIMARY = KEY, b);
+ CREATE TABLE t= 2(a INTEGER PRIMARY KEY, b);
+ <= /span>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&quo= t;,
+ [[
+ 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 =3D box.sql.de= bug().sql_xferOpt_count
+
+test:do_test(
+ "xfer-optimization-1.32",
+ function()
+ if (aftr - bfr =3D=3D 1) then
+ return {1}
+ end
+ if (aftr =3D=3D bfr) then
+ return {0}
+ <= /span>end
+ return {2}
+ end, {
+ -- <xfer-optimization-1.32>
+ 1
+ -- <xfer-optimization-1.32>
+ })
+
+-- 3.1) insert w= ith rollback (into non-empty table)
+----------------------------= ---------------------------------------------------------------
+=
+bfr =3D box.sql.debug().sql_xferOpt_count
+
+test:do_catchsql_test(
+ "xfer-optimization-1.33",
+ [[
+ DROP TA= BLE t1;
+ DROP TABLE t2;<= /div>
+ CREATE TABLE t1(a INTEG= ER PRIMARY KEY, b);
+ CRE= ATE TABLE t2(a INTEGER PRIMARY KEY, b);
+ INSERT INTO t1 VALUES (1, 1), (3, 3), (5, 5);
+<= span style=3D"white-space:pre"> INSERT INTO t2 VALUES (2, 2), (3, 4= );
+ BEGIN;
+ INSERT INTO t2 VALUES (4, 4);
=
+ INSERT OR ROLLBACK INTO t2 = SELECT * FROM t1;
+ ]], {<= /div>
+ -- <xfer-optimizatio= n-1.33>
+ 1, "UNI= QUE constraint failed: T2.A"
+ -- <xfer-optimization-1.33>
+ })
+
+test:do_execsql_test(
<= div>+ "xfer-optimization-1.34&q= uot;,
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.34>
+ 2, 2, 3, 4
+ -- <xfer-optimization-1.34>
+ })
+
+aftr =3D box.sql.debug().s= ql_xferOpt_count
+
+test:do_test(
+ "xfer-optimization-1.35",
+ function()
+ if (aftr - bfr =3D=3D 1) then
+ return {1}
+ end
+ = if (aftr =3D=3D bfr) then
+ return {0}
+ e= nd
+ return {2}
+ end, {
+ -- <xfer-optimization-1.35>
+ 0
+ -- <xfer-optimization-1.35>
+ })
+
+-- 4) insert with repla= ce
+-------------------------------------------------------------= ------------------------------
+
+bfr =3D box.sql.debug= ().sql_xferOpt_count
+
+test:do_catchsql_test(
+ "xfer-optimization-1.36&quo= t;,
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE t2(a INTEGER PRIMARY KE= Y, b);
+ INSERT INTO t1 V= ALUES (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&quo= t;,
+ [[
+ 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 = =3D box.sql.debug().sql_xferOpt_count
+
+test:do_test(<= /div>
+ "xfer-optimization-= 1.38",
+ function()
+ if (aftr - bfr =3D=3D 1)= then
+ return {1}
=
+ end
+ if (aftr =3D=3D bfr) then
+ return {0}
+ end
+ return {2}
+ end, {
+ -- <xfer-optimization-1= .38>
+ 0
+ -- <xfer-optimization-1.38>
+ })
+
+= -- 5) insert with fail
+-----------------------------------------= --------------------------------------------------
+
+b= fr =3D box.sql.debug().sql_xferOpt_count
+
+test:do_cat= chsql_test(
+ "xfer-o= ptimization-1.39",
+ = [[
+ DROP TABLE t1;
=
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KE= Y, 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>
<= div>+ 1, "Duplicate key exists= in unique index 'sqlite_autoindex_T2_1' in space 'T2'"= ;
+ -- <xfer-optimizat= ion-1.39>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.40",
+ [[
+ <= /span>INSERT INTO t2 VALUES (10, 10);
+ COMMIT;
+ = SELECT * FROM t2;
+ ]], {<= /div>
+ -- <xfer-optimizatio= n-1.40>
+ 1, 1, 2, 2, = 3, 4, 4, 4, 10, 10
+ -- &= lt;xfer-optimization-1.40>
+ <= /span>})
+
+aftr =3D box.sql.debug().sql_xferOpt_count<= /div>
+
+test:do_test(
+ "xfer-optimization-1.41",
+ function()
+ if (aftr - bfr =3D=3D 1) then
+ return {1}
+= end
+ if (aftr = =3D=3D bfr) then
+ retur= n {0}
+ end
+ return {2}
+ end, {
+ -- <xfer-optimization-1.41>
+ 0
+ = -- <xfer-optimization-1.41>
+ })
+
+-- 6) insert with ignore
+---= ---------------------------------------------------------------------------= -------------
+
+bfr =3D box.sql.debug().sql_xferOpt_co= unt
+
+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 INT= O t2 VALUES (2, 2), (3, 4);
+ BEGIN;
+ INSERT INT= O t2 VALUES (4, 4);
+ IN= SERT OR IGNORE INTO t2 SELECT * FROM t1;
+ ]], {
+ = -- <xfer-optimization-1.42>
+ 0
+ -- <xfe= r-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 =3D box.sql.debug().s= ql_xferOpt_count
+
+test:do_test(
+ "xfer-optimization-1.44",
+ function()
+ if (aftr - bfr =3D=3D 1) then
+ return {1}
+ end
+ = if (aftr =3D=3D bfr) then
+ return {0}
+ e= nd
+ return {2}
+ end, {
+ -- <xfer-optimization-1.44>
+ 0
+ -- <xfer-optimization-1.44>
+ })
+
+test:finish_test()


=D0=BF=D0= =BD, 16 =D0=B8=D1=8E=D0=BB. 2018 =D0=B3. =D0=B2 22:12, n.pettik <korablev@tarantool.org>:

> Here's diff for the fixed and rebased patch. There're some une= xpected things concerned with the check of emptiness of destination table: = internals can only correctly deal with ABORT conflict action and also my &#= 39;small optimization of optimization' was incorrect.

I see no diff, actually. You haven=E2=80=99t attached it to the letter.
--0000000000001c91690571247ea8--