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 16E82278B1 for ; Wed, 18 Jul 2018 16:18:19 -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 waOEhWl88KfH for ; Wed, 18 Jul 2018 16:18:18 -0400 (EDT) Received: from mail-lf0-f66.google.com (mail-lf0-f66.google.com [209.85.215.66]) (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 30B5A23554 for ; Wed, 18 Jul 2018 16:18:18 -0400 (EDT) Received: by mail-lf0-f66.google.com with SMTP id n96-v6so4378221lfi.1 for ; Wed, 18 Jul 2018 13:18:17 -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> <605B15EF-BD1C-4B03-8A9F-6E6225076812@tarantool.org> In-Reply-To: <605B15EF-BD1C-4B03-8A9F-6E6225076812@tarantool.org> From: Nikita Tatunov Date: Wed, 18 Jul 2018 23:18:04 +0300 Message-ID: Subject: [tarantool-patches] Re: [PATCH] sql: xfer optimization issue Content-Type: multipart/alternative; boundary="00000000000045e49f05714bc3e1" 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 --00000000000045e49f05714bc3e1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hello, Nikita! Here's newer version of the patch. Diff can be found at the end. =D1=81=D1=80, 18 =D0=B8=D1=8E=D0=BB. 2018 =D0=B3. =D0=B2 18:13, n.pettik : > Please, add to commit message results of benchmark to indicate > that this optimisation really matters. > > Added. > > On 17 Jul 2018, at 00:27, Nikita Tatunov wrote: > > > > 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; > > Don=E2=80=99t use camel notation. Lets call it simply =E2=80=99sql_xfer_c= ount=E2=80=99. > > Fixed. > > 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; > > Again: why do you need this flag? Default action is just synonym for ABOR= T, > so why should we care about it? > > It's all about conflict action priorities as I said before. Consider the following example: ``` 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; INSERT INTO t2 VALUES (10, 10); COMMIT; ``` As we understand *_REPLACE should work in this case but onError =3D=3D *_ABORT (as it was converted from the default one). It leads to a situation where an error will occur if xferOptimization is used. > + 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)); > > You don=E2=80=99t need to again proceed lookup: space is found few lines = above. > Moreover, I see those lookups are executed inside =E2=80=98for' loop. Let= s move > them outside it. > Fixed it. > > > + 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. > > + */ > > Multi-line comment should be formatted as following: > > /* > * Comment starts here. > * =E2=80=A6 > */ > Fixed. > > > + > > + 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++; > > Actually, I don=E2=80=99t like this approach. > Look, query may be compiled and saved into cache (even thought it is stil= l > not implemented yet). So it might be executed later and it might be not > empty. > Moreover, we are going to avoid doing space lookups and use only def. > With only def you can=E2=80=99t execute count. > > Personally, I wanted you to defer incrementing sql_xfer_count till > VDBE execution. For instance, you may add special flag and pass it > to OP_RowData indicating that xFer is currently processing. > > > +#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); > > I see few lines above: > > sqlite3OpenTable(pParse, iDest, pDest, OP_OpenWrite); > > So, basically you don=E2=80=99t need to open it again. > Fixed it. > > > + 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 > > +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; > > INSERT OT ROLLBACK outside transaction works the same as ABORT and DEFAUL= T. > So, surround it with transaction and check that it really rollbacks. > > There are basically almost the same tests surrounded by transactions (1.30 - 1.35). > > + ]], { > > + -- > > + 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} > > Why do you repeat this snippet each time? You can declare it as named > function once and use it everywhere. > > > + end, { > > + -- > > + 0 > > + -- > > + }) > > + > > +-- 1.1) insert w/o explicit confl. action & w/ index replace action & > empty dest_table > > Even in tests lets not exceed 80 chars (here and in other places). > diff --git a/src/box/sql.c b/src/box/sql.c index fdce224..656ba17 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_xfer_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_xfer_count", sql_xfer_count); info_end(h); } diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c index 2c9188e..c2df1c2 100644 --- a/src/box/sql/insert.c +++ b/src/box/sql/insert.c @@ -1628,16 +1628,6 @@ sqlite3OpenTableAndIndices(Parse * pParse, /* Parsing context */ return i; } -#ifdef SQLITE_TEST -/* - * The following global variable is incremented whenever the - * transfer optimization is used. This is used for testing - * purposes only - to make sure the transfer optimization really - * is happening when it is supposed to. - */ -int sqlite3_xferopt_count; -#endif /* SQLITE_TEST */ - #ifndef SQLITE_OMIT_XFER_OPT /* * Check to see if index pSrc is compatible as a source of data @@ -1658,6 +1648,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 */ } @@ -1724,10 +1716,9 @@ xferOptimization(Parse * pParse, /* Parser context *= / int addr1; /* Loop addresses */ 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 +1735,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) { @@ -1807,6 +1800,10 @@ xferOptimization(Parse * pParse, /* Parser context *= / /* Both tables must have the same INTEGER PRIMARY KEY. */ if (pDest->iPKey !=3D pSrc->iPKey) return 0; + uint32_t src_space_id =3D SQLITE_PAGENO_TO_SPACEID(pSrc->tnum); + struct space *src_space =3D space_by_id(src_space_id); + uint32_t dest_space_id =3D SQLITE_PAGENO_TO_SPACEID(pDest->tnum); + struct space *dest_space =3D space_by_id(dest_space_id); for (i =3D 0; i < (int)pDest->def->field_count; i++) { enum affinity_type dest_affinity =3D pDest->def->fields[i].affinity; @@ -1826,14 +1823,6 @@ xferOptimization(Parse * pParse, /* Parser context *= / } /* Default values for second and subsequent columns need to match. */ if (i > 0) { - uint32_t src_space_id =3D - SQLITE_PAGENO_TO_SPACEID(pSrc->tnum); - struct space *src_space =3D - space_cache_find(src_space_id); - uint32_t dest_space_id =3D - SQLITE_PAGENO_TO_SPACEID(pDest->tnum); - struct space *dest_space =3D - space_cache_find(dest_space_id); assert(src_space !=3D NULL && dest_space !=3D NULL); char *src_expr_str =3D src_space->def->fields[i].default_value; @@ -1848,9 +1837,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; @@ -1860,10 +1846,8 @@ xferOptimization(Parse * pParse, /* Parser context *= / } } /* Get server checks. */ - ExprList *pCheck_src =3D space_checks_expr_list( - SQLITE_PAGENO_TO_SPACEID(pSrc->tnum)); - ExprList *pCheck_dest =3D space_checks_expr_list( - SQLITE_PAGENO_TO_SPACEID(pDest->tnum)); + ExprList *pCheck_src =3D space_checks_expr_list(src_space_id); + ExprList *pCheck_dest =3D space_checks_expr_list(dest_space_id); if (pCheck_dest !=3D NULL && sqlite3ExprListCompare(pCheck_src, pCheck_dest, -1) !=3D 0) { /* Tables have different CHECK constraints. Ticket #2252 */ @@ -1888,72 +1872,51 @@ 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); + + /* The Vdbe we're building*/ + Vdbe *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. - */ + + vdbe_emit_open_cursor(pParse, iDest, 0, dest_space); + VdbeComment((v, "%s", pDest->def->name)); + + /* + * 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); } - 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); - } + vdbe_emit_open_cursor(pParse, iSrc, 0, src_space); + VdbeComment((v, "%s", pSrc->def->name)); + addr1 =3D sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); + VdbeCoverage(v); + sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData); + +#ifdef SQLITE_TEST + sqlite3VdbeChangeP5(v, OPFLAG_XFER_OPT); +#endif + + 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/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h index 18bf949..4b84695 100644 --- a/src/box/sql/sqliteInt.h +++ b/src/box/sql/sqliteInt.h @@ -3008,6 +3008,10 @@ struct Parse { #define OPFLAG_NOOP_IF_NULL 0x02 /* OP_FCopy: if source register is NULL * then do nothing */ +/* OP_RowData: xferOptimization started processing */ +#ifdef SQLITE_TEST +#define OPFLAG_XFER_OPT 0x01 +#endif /* * Each trigger present in the database schema is stored as an diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index f50e389..5f9bc13 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -77,6 +77,16 @@ int sql_search_count =3D 0; #endif +#ifdef SQLITE_TEST +/* + * The following global variable is incremented whenever the + * transfer optimization is used. This is used for testing + * purposes only - to make sure the transfer optimization really + * is happening when it is supposed to. + */ +int sql_xfer_count =3D 0; +#endif + /* * When this global variable is positive, it gets decremented once before * each instruction in the VDBE. When it reaches zero, the u1.isInterrupted @@ -3976,7 +3986,7 @@ case OP_SorterData: { break; } -/* Opcode: RowData P1 P2 * * * +/* Opcode: RowData P1 P2 * * P5 * Synopsis: r[P2]=3Ddata * * Write into register P2 the complete row content for the row at @@ -3984,6 +3994,8 @@ case OP_SorterData: { * There is no interpretation of the data. * It is just copied onto the P2 register exactly as * it is found in the database file. + * P5 can be used in debug mode to check if xferOptimization has + * actually started processing. * * If cursor P1 is an index, then the content is the key of the row. * If cursor P2 is a table, then the content extracted is the data. @@ -3996,6 +4008,13 @@ case OP_RowData: { BtCursor *pCrsr; u32 n; +#ifdef SQLITE_TEST + if (pOp->p5 =3D=3D 1) { + pOp->p5 =3D 0; + sql_xfer_count++; + } +#endif + pOut =3D &aMem[pOp->p2]; memAboutToChange(p, pOut); 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..34f603f --- /dev/null +++ b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua @@ -0,0 +1,601 @@ +#!/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 + +local function do_xfer_test(test_number, return_code) + test_name =3D string.format("xfer-optimization-1.%d", test_number) + test:do_test( + test_name, + function() + if (aftr - bfr =3D=3D 1) then + return {1} + end + if (aftr =3D=3D bfr) then + return {0} + end + end, { + -- + return_code + -- + }) +end + +-- 1.0) insert w/o explicit confl. action & w/o index replace action +--------------------------------------------------------------------------= ---- + +bfr =3D box.sql.debug().sql_xfer_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_xfer_count + +do_xfer_test(19, 0) + +-- 1.1) insert w/o explicit confl. action & w/ +-- index replace action & empty dest_table +--------------------------------------------------------------------------= ---- + +bfr =3D box.sql.debug().sql_xfer_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_xfer_count + +test:do_execsql_test( + "xfer-optimization-1.22", + [[ + SELECT * FROM t3; + ]], { + -- + 1 + -- + }) + +do_xfer_test(23, 1) + +-- 1.2) insert w/o explicit confl. action & w/ +-- index replace action & non-empty dest_table +--------------------------------------------------------------------------= ---- + +bfr =3D box.sql.debug().sql_xfer_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_xfer_count + +do_xfer_test(26, 0) + +-- 2) insert with abort +--------------------------------------------------------------------------= ---- + +bfr =3D box.sql.debug().sql_xfer_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_xfer_count + +do_xfer_test(29, 1) + +-- 3.0) insert with rollback (into empty table) +--------------------------------------------------------------------------= ---- + +bfr =3D box.sql.debug().sql_xfer_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_xfer_count + +do_xfer_test(32, 1) + +-- 3.1) insert with rollback (into non-empty table) +--------------------------------------------------------------------------= ---- + +bfr =3D box.sql.debug().sql_xfer_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_xfer_count + +do_xfer_test(35, 0) + +-- 4) insert with replace +--------------------------------------------------------------------------= ---- + +bfr =3D box.sql.debug().sql_xfer_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_xfer_count + +do_xfer_test(38, 0) + +-- 5) insert with fail +--------------------------------------------------------------------------= ---- + +bfr =3D box.sql.debug().sql_xfer_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_xfer_count + +do_xfer_test(41, 0) + +-- 6) insert with ignore +--------------------------------------------------------------------------= ---- + +bfr =3D box.sql.debug().sql_xfer_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_xfer_count + +do_xfer_test(44, 0) + +test:finish_test() --00000000000045e49f05714bc3e1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hello, Nikita! Here's newer version of the patch.<= div>Diff can be found at the end.

=
=D1=81=D1=80, 18 =D0=B8=D1=8E=D0=BB. 2018 =D0=B3. =D0=B2 1= 8:13, n.pettik <korablev@tarantool.org>:
Please, add to commit message results of benchmark to i= ndicate
that this optimisation really matters.


Added.
=C2=A0
> On 17 Jul 2018, at 00:27, Nikita Tatunov <hollow653@gmail.com> wrote:
>
> 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 =C2=A0 =C2=A0 =C2=A0extern int sql_search_count;
>=C2=A0 =C2=A0 =C2=A0 =C2=A0extern int sql_sort_count;
>=C2=A0 =C2=A0 =C2=A0 =C2=A0extern int sql_found_count;
> +=C2=A0 =C2=A0 =C2=A0extern int sql_xferOpt_count;

Don=E2=80=99t use camel notation. Lets call it simply =E2=80=99sql_xfer_cou= nt=E2=80=99.


Fixed.
=C2=A0
>=C2=A0 =C2=A0 =C2=A0 =C2=A0info_begin(h);
>=C2=A0 =C2=A0 =C2=A0 =C2=A0info_append_int(h, "sql_search_count&qu= ot;, sql_search_count);
>=C2=A0 =C2=A0 =C2=A0 =C2=A0info_append_int(h, "sql_sort_count"= ;, sql_sort_count);
>=C2=A0 =C2=A0 =C2=A0 =C2=A0info_append_int(h, "sql_found_count&quo= t;, sql_found_count);
> +=C2=A0 =C2=A0 =C2=A0info_append_int(h, "sql_xferOpt_count",= sql_xferOpt_count);
>=C2=A0 =C2=A0 =C2=A0 =C2=A0info_end(h);
>=C2=A0 }
>=C2=A0
> 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,=C2=A0= =C2=A0 =C2=A0 /* Parsing context */
>=C2=A0 =C2=A0* purposes only - to make sure the transfer optimization r= eally
>=C2=A0 =C2=A0* is happening when it is supposed to.
>=C2=A0 =C2=A0*/
> -int sqlite3_xferopt_count;
> +int sql_xferOpt_count =3D 0;
>=C2=A0 #endif=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0/* SQLITE_TEST */ >=C2=A0
>=C2=A0 #ifndef SQLITE_OMIT_XFER_OPT
> @@ -1658,6 +1658,8 @@ xferCompatibleIndex(Index * pDest, Index * pSrc)=
>=C2=A0 =C2=A0 =C2=A0 =C2=A0assert(pDest->pTable !=3D pSrc->pTable= );
>=C2=A0 =C2=A0 =C2=A0 =C2=A0uint32_t nDestCol =3D index_column_count(pDe= st);
>=C2=A0 =C2=A0 =C2=A0 =C2=A0uint32_t nSrcCol =3D index_column_count(pSrc= );
> +=C2=A0 =C2=A0 =C2=A0if ((pDest->idxType !=3D pSrc->idxType)) > +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0return 0;
>=C2=A0 =C2=A0 =C2=A0 =C2=A0if (nDestCol !=3D nSrcCol) {
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0return 0;=C2=A0 = =C2=A0 =C2=A0 =C2=A0/* Different number of columns */
>=C2=A0 =C2=A0 =C2=A0 =C2=A0}
> @@ -1725,9 +1727,9 @@ xferOptimization(Parse * pParse,=C2=A0 =C2=A0 = =C2=A0 =C2=A0 /* Parser context */
>=C2=A0 =C2=A0 =C2=A0 =C2=A0int emptyDestTest =3D 0;=C2=A0 /* Address of= test for empty pDest */
>=C2=A0 =C2=A0 =C2=A0 =C2=A0int emptySrcTest =3D 0;=C2=A0 =C2=A0/* Addre= ss of test for empty pSrc */
>=C2=A0 =C2=A0 =C2=A0 =C2=A0Vdbe *v;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 /* The VDBE we are building */
> -=C2=A0 =C2=A0 =C2=A0int destHasUniqueIdx =3D 0;=C2=A0 =C2=A0 =C2=A0 = =C2=A0/* True if pDest has a UNIQUE index */
>=C2=A0 =C2=A0 =C2=A0 =C2=A0int regData, regTupleid;=C2=A0 =C2=A0 =C2=A0= =C2=A0 /* Registers holding data and tupleid */
>=C2=A0 =C2=A0 =C2=A0 =C2=A0struct session *user_session =3D current_ses= sion();
> +=C2=A0 =C2=A0 =C2=A0bool is_err_action_default =3D false;

Again: why do you need this flag? Default action is just synonym for ABORT,=
so why should we care about it?


It's all about conflict action pri= orities as I said before.
Consider the following example:
```<= br>
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);
I= NSERT INTO t2 VALUES (2, 2), (3, 4);
BEGIN;
INS= ERT INTO t2 VALUES (4, 4);
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t2 VALUES (10, 10);
COMMIT;
```
As w= e understand *_REPLACE should work in this case but
onError =3D= =3D *_ABORT (as it was converted from the default one).=C2=A0
It = leads to a situation where an error will occur if xferOptimization is used.=

> +=C2=A0 =C2=A0 =C2=A0struct space *src_space =3D
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0space_by_id(SQLITE_PA= GENO_TO_SPACEID(pSrc->tnum));
> +=C2=A0 =C2=A0 =C2=A0struct space *dest_space =3D
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0space_by_id(SQLITE_PA= GENO_TO_SPACEID(pDest->tnum));

You don=E2=80=99t need to again proceed lookup: space is found few lines ab= ove.
Moreover, I see those lookups are executed inside =E2=80=98for' loop. L= ets move
them outside it.

Fixed it.
= =C2=A0

> +=C2=A0 =C2=A0 =C2=A0struct index *src_idx =3D space_index(src_space, = 0);
> +=C2=A0 =C2=A0 =C2=A0struct index *dest_idx =3D space_index(dest_space= , 0);
> +
> +=C2=A0 =C2=A0 =C2=A0/* Xfer optimization is unable to correctly inser= t data
> +=C2=A0 =C2=A0 =C2=A0 * in case there's a conflict action other th= an *_ABORT.
> +=C2=A0 =C2=A0 =C2=A0 * This is the reason we want to only run it if t= he
> +=C2=A0 =C2=A0 =C2=A0 * destination table is initially empty.
> +=C2=A0 =C2=A0 =C2=A0 * That block generates code to make that determi= nation.
> +=C2=A0 =C2=A0 =C2=A0 */

Multi-line comment should be formatted as following:

/*
=C2=A0* Comment starts here.
=C2=A0* =E2=80=A6
=C2=A0*/

Fixed.
=C2=A0
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">
> +
> +=C2=A0 =C2=A0 =C2=A0if (!(onError =3D=3D ON_CONFLICT_ACTION_ABORT &am= p;&
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0is_err_action_default =3D=3D false)= ) {
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0addr1 =3D sqlite= 3VdbeAddOp2(v, OP_Rewind, iDest, 0);
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0VdbeCoverage(v);=
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0emptyDestTest = =3D sqlite3VdbeAddOp0(v, OP_Goto);
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0sqlite3VdbeJumpH= ere(v, addr1);
> +#ifdef SQLITE_TEST
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (dest_idx->vtab= ->count(dest_idx, ITER_ALL, NULL, 0) =3D=3D 0)
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0sql_xferOpt_count++;

Actually, I don=E2=80=99t like this approach.
Look, query may be compiled and saved into cache (even thought it is still<= br> not implemented yet). So it might be executed later and it might be not emp= ty.
Moreover, we are going to avoid doing space lookups and use only def.
With only def you can=E2=80=99t execute count.

Personally, I wanted you to defer incrementing sql_xfer_count till
VDBE execution. For instance, you may add special flag and pass it
to OP_RowData indicating that xFer is currently processing.

> +#endif
> +=C2=A0 =C2=A0 =C2=A0vdbe_emit_open_cursor(pParse, iSrc, 0, src_space)= ;
> +=C2=A0 =C2=A0 =C2=A0VdbeComment((v, "%s", src_idx->def-&= gt;name));
> +=C2=A0 =C2=A0 =C2=A0vdbe_emit_open_cursor(pParse, iDest, 0, dest_spac= e);

I see few lines above:

sqlite3OpenTable(pParse, iDest, pDest, OP_OpenWrite);

So, basically you don=E2=80=99t need to open it again.

Fixed it.
=C2=A0

> +=C2=A0 =C2=A0 =C2=A0VdbeComment((v, "%s", dest_idx->def-= >name));
> +=C2=A0 =C2=A0 =C2=A0addr1 =3D sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0= );
> +=C2=A0 =C2=A0 =C2=A0VdbeCoverage(v);
> +=C2=A0 =C2=A0 =C2=A0sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);<= br> > +=C2=A0 =C2=A0 =C2=A0sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData= );
> +=C2=A0 =C2=A0 =C2=A0sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE);
> +=C2=A0 =C2=A0 =C2=A0sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1 + 1); > +=C2=A0 =C2=A0 =C2=A0VdbeCoverage(v);
> +=C2=A0 =C2=A0 =C2=A0sqlite3VdbeJumpHere(v, addr1);
> +=C2=A0 =C2=A0 =C2=A0sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
> +=C2=A0 =C2=A0 =C2=A0sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
> +
>=C2=A0 =C2=A0 =C2=A0 =C2=A0if (emptySrcTest)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0sqlite3VdbeJumpH= ere(v, emptySrcTest);
>=C2=A0 =C2=A0 =C2=A0 =C2=A0sqlite3ReleaseTempReg(pParse, regTupleid); > diff --git a/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua b/t= est/sql-tap/gh-3307-xfer-optimization-issue.test.lua
> new file mode 100755
> index 0000000..e75fabc
> --- /dev/null
> +test:do_catchsql_test(
> +=C2=A0 =C2=A0 =C2=A0"xfer-optimization-1.15",
> +=C2=A0 =C2=A0 =C2=A0[[
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0DROP TABLE t1;
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0DROP TABLE t2;
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0CREATE TABLE t1(a INT= EGER PRIMARY KEY, b UNIQUE);
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0CREATE TABLE t2(a INT= EGER PRIMARY KEY, b UNIQUE);
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0INSERT INTO t1 VALUES= (2, 2), (3, 3), (5, 5);
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0INSERT INTO t2 VALUES= (1, 1), (4, 4);
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0INSERT OR ROLLBACK IN= TO t2 SELECT * FROM t1;

INSERT OT ROLLBACK outside transaction works the same as ABORT and DEFAULT.=
So, surround it with transaction and check that it really rollbacks.


There are basically almost the same te= sts surrounded by transactions (1.30 - 1.35).
=C2=A0
> +=C2=A0 =C2=A0 =C2=A0]], {
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-- <xfer-optimizat= ion-1.15>
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-- <xfer-optimizat= ion-1.15>
> +=C2=A0 =C2=A0 =C2=A0})
> +
> +test:do_execsql_test(
> +=C2=A0 =C2=A0 =C2=A0"xfer-optimization-1.16",
> +=C2=A0 =C2=A0 =C2=A0[[
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT * FROM t2;
> +=C2=A0 =C2=A0 =C2=A0]], {
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-- <xfer-optimizat= ion-1.16>
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01, 1, 2, 2, 3, 3, 4, = 4, 5, 5
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-- <xfer-optimizat= ion-1.16>
> +=C2=A0 =C2=A0 =C2=A0})
> +
> +-- The following tests are supposed to test if xfer-optimization is a= ctually
> +-- used in the given cases (if the conflict actually occurs):
> +--=C2=A0 =C2=A01.0) insert w/o explicit confl. action & w/o index= replace action
> +--=C2=A0 =C2=A01.1) insert w/o explicit confl. action & w/ index = replace action & empty dest_table
> +--=C2=A0 =C2=A01.2) insert w/o explicit confl. action & w/ index = replace action & non-empty dest_table
> +--=C2=A0 =C2=A02) insert with abort
> +--=C2=A0 =C2=A03.0) insert with rollback (into empty table)
> +--=C2=A0 =C2=A03.1) insert with rollback (into non-empty table)
> +--=C2=A0 =C2=A04) insert with replace
> +--=C2=A0 =C2=A05) insert with fail
> +--=C2=A0 =C2=A06) insert with ignore
> +
> +
> +-- 1.0) insert w/o explicit confl. action & w/o index replace act= ion
> +---------------------------------------------------------------------= ----------------------
> +
> +bfr =3D box.sql.debug().sql_xferOpt_count
> +
> +test:do_catchsql_test(
> +=C2=A0 =C2=A0 =C2=A0"xfer-optimization-1.17",
> +=C2=A0 =C2=A0 =C2=A0[[
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0DROP TABLE t1;
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0DROP TABLE t2;
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0CREATE TABLE t1(a INT= EGER PRIMARY KEY, b);
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0CREATE TABLE t2(a INT= EGER PRIMARY KEY, b);
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0INSERT INTO t1 VALUES= (1, 1), (3, 3), (5, 5);
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0INSERT INTO t2 VALUES= (2, 2), (3, 4);
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0BEGIN;
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0INSERT INTO t2 VALUES (4, 4);
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0INSERT INTO t2 SELECT * FROM t1;
> +=C2=A0 =C2=A0 =C2=A0]], {
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-- <xfer-optimizat= ion-1.17>
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01, "Duplicate ke= y exists in unique index 'sqlite_autoindex_T2_1' in space 'T2&#= 39;"
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-- <xfer-optimizat= ion-1.17>
> +=C2=A0 =C2=A0 =C2=A0})
> +
> +test:do_execsql_test(
> +=C2=A0 =C2=A0 =C2=A0"xfer-optimization-1.18",
> +=C2=A0 =C2=A0 =C2=A0[[
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0INSERT INTO t2 VALUES (10, 10);
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0COMMIT;
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT * FROM t2;
> +=C2=A0 =C2=A0 =C2=A0]], {
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-- <xfer-optimizat= ion-1.18>
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A02, 2, 3, 4, 4, 4, 10,= 10
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-- <xfer-optimizat= ion-1.18>
> +=C2=A0 =C2=A0 =C2=A0})
> +
> +aftr =3D box.sql.debug().sql_xferOpt_count
> +
> +test:do_test(
> +=C2=A0 =C2=A0 =C2=A0"xfer-optimization-1.19",
> +=C2=A0 =C2=A0 =C2=A0function()
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (aftr - bfr =3D=3D= 1) then
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0return {1}
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0end
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (aftr =3D=3D bfr) = then
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0return {0}
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0end
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0return {2}

Why do you repeat this snippet each time? You can declare it as named
function once and use it everywhere.

> +=C2=A0 =C2=A0 =C2=A0end, {
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-- <xfer-optimizat= ion-1.19>
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-- <xfer-optimizat= ion-1.19>
> +=C2=A0 =C2=A0 =C2=A0})
> +
> +-- 1.1) insert w/o explicit confl. action & w/ index replace acti= on & empty dest_table

Even in tests lets not exceed 80 chars (here and in other places).

diff --git a/src/box/sql.c b/src/box/sql.c
index fdce224..656ba17 100644
--- a/src/box/sql.c
+++ b/src/box/sql.c
@@ -1636,10 +1636,12 @@ sql_debug_info(struc= t 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_xfer_count;
=C2=A0 info_begin(h);
=C2=A0 info_append_int(h, "sql_search_count", = sql_search_count);
=C2=A0 = info_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_xfer_count", sql_xfer_count)= ;
=C2=A0 info_end(h);
=C2=A0}
=C2=A0
diff --git a/src/box/sql/insert.c = b/src/box/sql/insert.c
index 2c9188e..c2df1c2 100644
--= - a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ = -1628,16 +1628,6 @@ sqlite3OpenTableAndIndices(Parse * pParse, /* Parsing context */
=C2=A0 return i;
=C2=A0}
=C2= =A0
-#ifdef SQLITE_TEST
-/*
- * The following= global variable is incremented whenever the
- * transfer optimiz= ation is used.=C2=A0 This is used for testing
- * purposes only -= to make sure the transfer optimization really
- * is happening w= hen it is supposed to.
- */
-int sqlite3_xferopt_count;=
-#endif /* SQLITE_TEST= */
-
=C2=A0#ifndef SQLITE_OMIT_XFER_OPT
=C2= =A0/*
=C2=A0 * Check to see if index pSrc is compatible as a sour= ce of data
@@ -1658,6 +1648,8 @@ xferCompatibleIndex(Index * pDes= t, Index * pSrc)
=C2=A0 as= sert(pDest->pTable !=3D pSrc->pTable);
=C2=A0 uint32_t nDestCol =3D index_column_count(pDest);<= /div>
=C2=A0 uint32_t nSrcCol = =3D index_column_count(pSrc);
+ <= /span>if ((pDest->idxType !=3D pSrc->idxType))
+ return 0;
=C2=A0 if (nDestCol !=3D nSrcCol) {
=C2=A0 return 0; <= /span>/* Different number of columns */
=C2=A0 }
@@ -1724,10 +1716,9 @@ xferOptimization(Pa= rse * pParse, /* Parser context */
=C2=A0 int addr1; /* Loop addresses */
=C2=A0 int emptyDestTest =3D 0; /* Address of test for empty pDest */
=C2= =A0 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 */
=C2=A0 int regData, regTupleid; /* Registers holding data and tupleid */
=C2=A0 struct session *user_sessio= n =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 +1735,10 @@ xferOptimization(Parse * pParse, /* Parser context */
=C2=A0 if (onError =3D=3D ON_CONFLICT_ACTION_DEF= AULT) {
=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_ACTION_D= EFAULT) {
=C2=A0 onError= =3D ON_CONFLICT_ACTION_ABORT;
+ = is_err_action_default =3D true;
+ }
=C2=A0 }
=C2=A0 assert(pSelect-&= gt;pSrc); /* allocated even if there= is no FROM clause */
=C2=A0 if (pSelect->pSrc->nSrc !=3D 1) {
@@ -1807,6 +1800,10 @@= xferOptimization(Parse * pParse, /*= Parser context */
=C2=A0 = /* Both tables must have the same INTEGER PRIMARY KEY. */
=C2=A0<= span style=3D"white-space:pre"> if (pDest->iPKey !=3D pSrc->iP= Key)
=C2=A0 return 0;
+ uint32_t src_space_id =3D S= QLITE_PAGENO_TO_SPACEID(pSrc->tnum);
+ struct space *src_space =3D space_by_id(src_space_id);
+ uint32_t dest_space_id =3D S= QLITE_PAGENO_TO_SPACEID(pDest->tnum);
+ struct space *dest_space =3D space_by_id(dest_space_id);
=C2=A0 for (i =3D 0; i <= (int)pDest->def->field_count; i++) {
=C2=A0 enum affinity_type dest_affinity =3D
= =C2=A0 pDest->def->fields[i]= .affinity;
@@ -1826,14 +1823,6 @@ xferOptimization(Parse * pParse= , /* Parser context */
=C2= =A0 }
=C2=A0 /* Default values for second and subsequent c= olumns need to match. */
=C2=A0 = if (i > 0) {
- uint32_t src_space_id =3D
- <= /span>SQLITE_PAGENO_TO_SPACEID(pSrc->tnum);
- struct space *src_space =3D
- space_cache_find(src_space_id);
-= uint32_t dest_space_id =3D
<= div>- SQLITE_PAGENO_TO_SPACEID(pD= est->tnum);
- struct = space *dest_space =3D
- space_cache_find(dest_space_id);
=C2=A0 assert(src_space !=3D NULL && dest_space !=3D NULL= );
=C2=A0 char *src_expr= _str =3D
=C2=A0 src_spa= ce->def->fields[i].default_value;
@@ -1848,9 +1837,6 @@ xfe= rOptimization(Parse * pParse, /* Par= ser context */
=C2=A0 }
=C2=A0 }
=C2=A0 for (pDestIdx =3D pDest->pIndex; p= DestIdx; pDestIdx =3D pDestIdx->pNext) {
- if (index_is_unique(pDestIdx)) {
- destHasUniqueIdx =3D 1;
- }
=C2=A0 for (pSrcIdx =3D pSrc->pIndex; pSrcIdx; pSrcIdx =3D pSr= cIdx->pNext) {
=C2=A0 if (xferCompatibleIndex(pDestIdx, pSrcIdx))
=C2=A0 break;
@@ -1860,10 +1846,8 @@ xferOp= timization(Parse * pParse, /* Parser= context */
=C2=A0 }
=C2=A0 }
=C2=A0 /* Get server checks. */
- ExprList *pCheck_src =3D space_checks_e= xpr_list(
- SQLITE_PAGENO= _TO_SPACEID(pSrc->tnum));
- ExprList *pCheck_dest =3D space_checks_expr_list(
- SQLITE_PAGENO_TO_SPACEID(pDest->tnum));<= /div>
+ ExprList *pCheck_src =3D= space_checks_expr_list(src_space_id);
+ ExprList *pCheck_dest =3D space_checks_expr_list(dest_space_= id);
=C2=A0 if (pCheck_des= t !=3D NULL &&
=C2=A0 =C2=A0 =C2=A0 sqlite3ExprListCompare(pCheck_src, pCheck_dest, -1) !=3D = 0) {
=C2=A0 /* Tables hav= e different CHECK constraints.=C2=A0 Ticket #2252 */
@@ -1888,72 = +1872,51 @@ xferOptimization(Parse * pParse, /* Parser context */
=C2=A0 * least a possibility, though it might only work if the destina= tion
=C2=A0 * table (tab1= ) is initially empty.
=C2=A0 */
-#ifdef SQLITE_TEST
- sqlite3_xferopt_count++;
-#endif
- v =3D sqlite3GetVdbe(pParse);
+
+ /* The Vdbe we're bui= lding*/
+ Vdbe *v =3D sqli= te3GetVdbe(pParse);
=C2=A0 iSrc =3D pParse->nTab++;
=C2=A0 iDest =3D pParse->nTab++;
=C2=A0 regData =3D sqlite3GetTempReg(pParse);
=C2=A0= regTupleid =3D sqlite3GetTempReg(pP= arse);
- sqlite3OpenTable(= pParse, iDest, pDest, OP_OpenWrite);
- assert(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_CON= FLICT_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 m= ake
- * that determinati= on.
- *
- * Conditions under which the destinati= on must be empty:
- *
- * (1) There is no INTEGE= R PRIMARY KEY but there are indices.
- *
- * (2)= The destination has a unique index.=C2=A0 (The xfer optimization
- *=C2=A0 =C2=A0 =C2=A0is unable = to test uniqueness.)
- *=
- * (3) onError is some= thing other than ON_CONFLICT_ACTION_ABORT and _ROLLBACK.
- */
+
+ vdbe_emit_open_cursor(pParse, iDest, 0, dest_spac= e);
+ VdbeComment((v, &quo= t;%s", pDest->def->name));
+
+ /*
+ * Xfer optimization is unable to correctly insert data
+ * in case there's a conflict acti= on 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 &&
+ =C2=A0 =C2=A0 is_err_action_default =3D=3D fal= se)) {
=C2=A0 addr1 =3D s= qlite3VdbeAddOp2(v, OP_Rewind, iDest, 0);
=C2=A0 VdbeCoverage(v);
=C2=A0 emptyDestTest =3D sqlite3VdbeAddOp0(v, OP_Goto);
=C2=A0 sqlite3VdbeJumpHere(v= , addr1);
=C2=A0 }
=C2=A0
- for (pDestIdx = =3D pDest->pIndex; pDestIdx; pDestIdx =3D pDestIdx->pNext) {
- for (pSrcIdx =3D pSrc->pInde= x; ALWAYS(pSrcIdx);
- =C2= =A0 =C2=A0 =C2=A0pSrcIdx =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(pPars= e, iSrc,
- =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));
- s= truct space *dest_space =3D
- <= /span>space_by_id(SQLITE_PAGENO_TO_SPACEID(pDestIdx->tnum));
-= vdbe_emit_open_cursor(pParse, iDes= t,
- =C2=A0 =C2=A0 =C2= =A0 SQLITE_PAGENO_TO_INDEXID(pDestIdx->tnum),
- =C2=A0 =C2=A0 =C2=A0 dest_space);
- VdbeComment((v, "%s", pDes= tIdx->zName));
- addr1= =3D sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);
- VdbeCoverage(v);
- sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);
- sqlite3VdbeAddOp2(v, OP_IdxInse= rt, iDest, regData);
- if= (pDestIdx->idxType =3D=3D SQLITE_IDXTYPE_PRIMARYKEY)
- sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE);<= /div>
- sqlite3VdbeAddOp2(v, OP= _Next, iSrc, addr1 + 1);
- VdbeCoverage(v);
- sqli= te3VdbeJumpHere(v, addr1);
- sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
- sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
-= }
+ vdbe_emit_open_cursor(pParse, iSrc, 0, src_space);
<= div>+ VdbeComment((v, "%s"= , pSrc->def->name));
+ addr1 =3D sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);
+ VdbeCoverage(v);
+ sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);
+
+#ifdef SQLITE_TEST
+ sqlite3VdbeChangeP5(v, OPFLAG_XFER_OPT);
+#endif<= /div>
+
+ sqlite3VdbeA= ddOp2(v, OP_IdxInsert, iDest, regData);
+ sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE);
+ sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1 += 1);
+ VdbeCoverage(v);
+ sqlite3VdbeJumpHere(v, add= r1);
+ sqlite3VdbeAddOp2(v= , OP_Close, iSrc, 0);
+ sq= lite3VdbeAddOp2(v, OP_Close, iDest, 0);
+
=C2=A0 if (emptySrcTest)
=C2=A0 sqlite3VdbeJumpHere(v, emptySrcTest);
=C2=A0 sqlite3ReleaseTempReg(p= Parse, regTupleid);
diff --git a/src/box/sql/sqliteInt.h b/src/bo= x/sql/sqliteInt.h
index 18bf949..4b84695 100644
--- a/s= rc/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@= -3008,6 +3008,10 @@ struct Parse {
=C2=A0#define OPFLAG_NOOP_IF_= NULL=C2=A0 0x02 /* OP_FCopy: if sour= ce register is NULL
=C2=A0 * then do nothing
=C2=A0 = */
+/* OP_RowData: xferOptimization started processing = */
+#ifdef SQLITE_TEST
+#define OPFLAG_XFER_OPT=C2=A0 = =C2=A0 =C2=A0 0x01
+#endif
=C2=A0
=C2=A0/*
=C2=A0 * Each trigger present in the database schema is stored as a= n
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
= index f50e389..5f9bc13 100644
--- a/src/box/sql/vdbe.c
= +++ b/src/box/sql/vdbe.c
@@ -77,6 +77,16 @@
=C2=A0int s= ql_search_count =3D 0;
=C2=A0#endif
=C2=A0
+#= ifdef SQLITE_TEST
+/*
+ * The following global variable= is incremented whenever the
+ * transfer optimization is used.= =C2=A0 This is used for testing
+ * purposes only - to make sure = the transfer optimization really
+ * is happening when it is supp= osed to.
+ */
+int sql_xfer_count =3D 0;
+#en= dif
+
=C2=A0/*
=C2=A0 * When this global vari= able is positive, it gets decremented once before
=C2=A0 * each i= nstruction in the VDBE.=C2=A0 When it reaches zero, the u1.isInterrupted
@@ -3976,7 +3986,7 @@ case OP_SorterData: {
=C2=A0 break;
=C2=A0}
=C2=A0<= /div>
-/* Opcode: RowData P1 P2 * * *
+/* Opcode: RowData P1 = P2 * * P5
=C2=A0 * Synopsis: r[P2]=3Ddata
=C2=A0 *
=C2=A0 * Write into register P2 the complete row content for the row = at
@@ -3984,6 +3994,8 @@ case OP_SorterData: {
=C2=A0 *= There is no interpretation of the data.
=C2=A0 * It is just copi= ed onto the P2 register exactly as
=C2=A0 * it is found in the da= tabase file.
+ * P5 can be used in debug mode to check if xferOpt= imization has
+ * actually started processing.
=C2=A0 *=
=C2=A0 * If cursor P1 is an index, then the content is the key o= f the row.
=C2=A0 * If cursor P2 is a table, then the content ext= racted is the data.
@@ -3996,6 +4008,13 @@ case OP_RowData: {
=C2=A0 BtCursor *pCrsr;
=
=C2=A0 u32 n;
=C2=A0<= /div>
+#ifdef SQLITE_TEST
+ <= /span>if (pOp->p5 =3D=3D 1) {
+ pOp->p5 =3D 0;
+ sql_xfer_count++;
+ }=
+#endif
+
=C2=A0 pOut =3D &aMem[pOp->p2];
=C2=A0 memAboutToChange(p, pOut);
=C2=A0
<= div>diff --git a/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua b/te= st/sql-tap/gh-3307-xfer-optimization-issue.test.lua
new file mode= 100755
index 0000000..34f603f
--- /dev/null
= +++ b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua
@@ -0= ,0 +1,601 @@
+#!/usr/bin/env tarantool
+test =3D requir= e("sqltester")
+test:plan(44)
+
+lo= cal bfr, aftr
+
+test:do_catchsql_test(
+ "xfer-optimization-1.1",
+ [[
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER= UNIQUE);
+ INSERT INTO t= 1 VALUES (1, 1), (2, 2), (3, 3);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER UNIQUE);
+ INSERT INTO t2 SELECT * FROM t= 1;
+ ]], {
+ -- <xfer-optimization-1.1>
=
+ 0
+ -- <xfer-optimization-1.1>
+ })
+
+test:do_execsq= l_test(
+ "xfer-optim= ization-1.2",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.2>
+ 1, 1, 2, 2, 3, 3
+ -- <xfer-optimization-1.2>
+= })
+
+test:do_c= atchsql_test(
+ "xfer= -optimization-1.3",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+<= span style=3D"white-space:pre"> 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&g= t;
+ 0
+ -- <xfer-optimization-1.3>
+ })
+
+test:d= o_execsql_test(
+ "xf= er-optimization-1.4",
+ [[
+ SELECT * FROM t2;=
+ ]], {
+ -- <xfer-optimization-1.4>
+ 1, 1, 2, 2, 3, 3
+ -- <xfer-optimization-1.4>
+ })
+
+t= est:do_catchsql_test(
+ &q= uot;xfer-optimization-1.5",
+ [[
+ DROP TABLE = t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER P= RIMARY KEY, b INTEGER, c INTEGER);
+ INSERT INTO t1 VALUES (1, 1, 2), (2, 2, 3), (3, 3, 4);
+ CREATE TABLE t2(a INTEGER PRIMA= RY KEY, b INTEGER);
+ INS= ERT INTO t2 SELECT * FROM t1;
+ <= /span>]], {
+ -- <xfer= -optimization-1.5>
+ 1= , "table T2 has 2 columns but 3 values were supplied"
+= -- <xfer-optimization-1.5>
+ })
+
= +test:do_execsql_test(
+ &= quot;xfer-optimization-1.6",
+ [[
+ SELECT * F= ROM t2;
+ ]], {
= + -- <xfer-optimization-1.6><= /div>
+
+ -- <xfer= -optimization-1.6>
+ })=
+
+test:do_catchsql_test(
+ "xfer-optimization-1.7",
+ [[
+ DROP TABLE t1;
+ <= /span>DROP TABLE t2;
+ CR= EATE 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-optim= ization-1.7>
+ })
=
+
+test:do_execsql_test(
+ "xfer-optimization-1.8",
+ [[
+= SELECT * FROM t2;
+ ]], {
+ -- <xfer-op= timization-1.6>
+ 1, 1= , 2, 2, 3, 3
+ -- <xfe= r-optimization-1.6>
+ }= )
+
+test:do_catchsql_test(
+ "xfer-optimization-1.9",
+ [[
+ DROP TABLE t1;
+ = DROP TABLE t2;
+ C= REATE 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;
+ ]], {
+ -- &l= t;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&= quot;,
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.10>
+
+ -- <xfer-optimization-1.10>
+ })
+
+t= est:do_catchsql_test(
+ &q= uot;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&= gt;
+ 0
+ -- <xfer-optimization-1.11>
<= div>+ })
+
+test= :do_execsql_test(
+ "= xfer-optimization-1.12",
+ <= /span>[[
+ 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 INT= EGER PRIMARY KEY, b);
+ C= REATE 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 SEL= ECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1= .13>
+ 0
+ -- <xfer-optimization-1.13>
+ })
+
+= test:do_execsql_test(
+ &q= uot;xfer-optimization-1.14",
+ [[
+ SELECT * F= ROM t2;
+ ]], {
= + -- <xfer-optimization-1.14>=
+ 1, 1, 2, 2, 3, 3, 4, 4= , 5, 5
+ -- <xfer-opti= mization-1.14>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.15",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREAT= E 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);
+ INS= ERT INTO t2 VALUES (1, 1), (4, 4);
+ INSERT OR ROLLBACK INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.15>
+ 0
+ <= /span>-- <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 follow= ing 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. ac= tion & w/o index replace action
+-- 1.1) insert w/o explicit confl. action & w/ index replac= e action &
+-- empty = dest_table
+-- 1.2) inser= t 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) in= sert with rollback (into non-empty table)
+-- 4) insert with replace
+-- 5) insert with fail
+-- 6) insert with ignore
+
+local fu= nction do_xfer_test(test_number, return_code)
+ test_name =3D string.format("xfer-optimization-1= .%d", test_number)
+ = test:do_test(
+ test_name= ,
+ function()
= + if (aftr - bfr =3D=3D 1) then
+ return {1}
+<= span style=3D"white-space:pre"> end
+ if (aftr =3D=3D bfr) then
+ return {0}
+ end
+ en= d, {
+ -- <test_name&= gt;
+ return_code
<= div>+ -- <test_name>
+ })
+end
+<= /div>
+-- 1.0) insert w/o explicit confl. action & w/o index replac= e action
+-------------------------------------------------------= -----------------------
+
+bfr =3D box.sql.debug().sql_= xfer_count
+
+test:do_catchsql_test(
+ "xfer-optimization-1.17",
<= div>+ [[
+ 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);
+ IN= SERT INTO t2 VALUES (2, 2), (3, 4);
+ BEGIN;
+ IN= SERT INTO t2 VALUES (4, 4);
+ <= /span>INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ --= <xfer-optimization-1.17>
+= 1, "Duplicate key exists in unique index 'sqlite_autoinde= x_T2_1' in space 'T2'"
+ -- <xfer-optimization-1.17>
+ })
+
+test:do_execsql_test= (
+ "xfer-optimizatio= n-1.18",
+ [[
+ INSERT INTO t2 VALUES (10, 10= );
+ COMMIT;
+<= span style=3D"white-space:pre"> 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_xfer_count
+
+do_xfer_test(19, = 0)
+
+-- 1.1) insert w/o explicit confl. action & w= /
+--=C2=A0 =C2=A0 =C2=A0 index replace action & empty dest_t= able
+-----------------------------------------------------------= -------------------
+
+bfr =3D box.sql.debug().sql_xfer= _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 PRIM= ARY 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_tes= t(
+ "xfer-optimizati= on-1.21",
+ [[
<= div>+ INSERT INTO t2 VALUES (10, 1= 0);
+ 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_xfer_count
+
+test:do_execsql_t= est(
+ "xfer-optimiza= tion-1.22",
+ [[
+ SELECT * FROM t3;
+ ]], {
+ -- <xfer-optimization-1.22>
+ 1
+ -- <xfer-optimization-1.22>
+ })
+
+do_xfer_test(23, 1)
+
+-- 1.2) insert w/o explicit confl. action & w/
=
+-- index replace action & non-empty dest_table
+-------= -----------------------------------------------------------------------
+
+bfr =3D box.sql.debug().sql_xfer_count
+
+test:do_catchsql_test(
+ <= /span>"xfer-optimization-1.24",
+ [[
+ DR= OP 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
+ -- <x= fer-optimization-1.24>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.25",
+ [[
+ INSERT INTO t2 VALUES (10, 10);
+ COMMIT;
+ SELECT * FROM t2;
+ ]], {
+ -- <x= fer-optimization-1.25>
+ 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 10, 10
+ -- <xfer-optimization-1.25>
+ })
+
+aftr =3D box.sql.deb= ug().sql_xfer_count
+
+do_xfer_test(26, 0)
+<= /div>
+-- 2) insert with abort
+-----------------------------= -------------------------------------------------
+
+bf= r =3D box.sql.debug().sql_xfer_count
+
+test:do_catchsq= l_test(
+ "xfer-optim= ization-1.27",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b= );
+ CREATE TABLE t2(a IN= TEGER 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;
+ SEL= ECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1= .28>
+ 2, 2, 3, 4, 4, = 4, 10, 10
+ -- <xfer-o= ptimization-1.28>
+ })<= /div>
+
+aftr =3D box.sql.debug().sql_xfer_count
+<= /div>
+do_xfer_test(29, 1)
+
+-- 3.0) insert with r= ollback (into empty table)
+-------------------------------------= -----------------------------------------
+
+bfr =3D bo= x.sql.debug().sql_xfer_count
+
+test:do_catchsql_test(<= /div>
+ "xfer-optimization-= 1.30",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
=
+ CREATE TABLE t2(a INTEGER PR= IMARY KEY, b);
+ INSERT I= NTO 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
+ <= /span>-- <xfer-optimization-1.31>
+ })
+
+aftr =3D box.sql.debug().sql_xfer= _count
+
+do_xfer_test(32, 1)
+
+--= 3.1) insert with rollback (into non-empty table)
+--------------= ----------------------------------------------------------------
= +
+bfr =3D box.sql.debug().sql_xfer_count
+
+= test:do_catchsql_test(
+ &= quot;xfer-optimization-1.33",
+ [[
+ DROP TABL= E t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER= PRIMARY KEY, b);
+ CREAT= E 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 SE= LECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-= 1.33>
+ 1, "UNIQU= E constraint failed: T2.A"
+= -- <xfer-optimization-1.33>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.34&quo= t;,
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.34>
+ 2, 2, 3, 4
+ -- <xfer-optimization-1.34>
+ })
+
+aftr =3D box.sql.debug().sql= _xfer_count
+
+do_xfer_test(35, 0)
+
+-- 4) insert with replace
+-----------------------------------= -------------------------------------------
+
+bfr =3D = box.sql.debug().sql_xfer_count
+
+test:do_catchsql_test= (
+ "xfer-optimizatio= n-1.36",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE t2(a INTEGER= PRIMARY KEY, b);
+ INSER= T 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_tes= t(
+ "xfer-optimizati= on-1.37",
+ [[
<= div>+ INSERT INTO t2 VALUES (10, 1= 0);
+ COMMIT;
+= SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.37>
+ 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 10, 10
+ -- <xfer-optimization-1.37&g= t;
+ })
+
<= div>+aftr =3D box.sql.debug().sql_xfer_count
+
+do_xfer= _test(38, 0)
+
+-- 5) insert with fail
+-----= -------------------------------------------------------------------------
+
+bfr =3D box.sql.debug().sql_xfer_count
+
+test:do_catchsql_test(
+= "xfer-optimization-1.39",
+ [[
+ = DROP TABLE t1;
+ DROP TAB= LE 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;
<= div>+ INSERT INTO t2 VALUES (4, 4)= ;
+ INSERT OR FAIL INTO = t2 SELECT * FROM t1;
+ ]],= {
+ -- <xfer-optimiza= tion-1.39>
+ 1, "= Duplicate key exists in unique index 'sqlite_autoindex_T2_1' in spa= ce 'T2'"
+ -= - <xfer-optimization-1.39>
+ })
+
+test:do_execsql_test(
+ "xfer-optimization-1.40",
<= div>+ [[
+ 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 =3D box.sql.debug(= ).sql_xfer_count
+
+do_xfer_test(41, 0)
+
+-- 6) insert with ignore
+-------------------------------= -----------------------------------------------
+
+bfr = =3D box.sql.debug().sql_xfer_count
+
+test:do_catchsql_= test(
+ "xfer-optimiz= ation-1.42",
+ [[
+ DROP TABLE t1;
+= DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);=
+ CREATE TABLE t2(a INTE= GER PRIMARY KEY, b);
+ IN= SERT 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-optimiz= ation-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
<= div>+ -- <xfer-optimization-1.43= >
+ })
+
+aftr =3D box.sql.debug().sql_xfer_count
+
+do_xf= er_test(44, 0)
+
+test:finish_test()
=C2=A0
--00000000000045e49f05714bc3e1--