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 8760523B83 for ; Fri, 27 Apr 2018 11:45:57 -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 W05sodYcJfvz for ; Fri, 27 Apr 2018 11:45:57 -0400 (EDT) Received: from mail-lf0-f67.google.com (mail-lf0-f67.google.com [209.85.215.67]) (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 BEEBD23B7F for ; Fri, 27 Apr 2018 11:45:56 -0400 (EDT) Received: by mail-lf0-f67.google.com with SMTP id m18-v6so3359866lfb.0 for ; Fri, 27 Apr 2018 08:45:56 -0700 (PDT) MIME-Version: 1.0 References: <1524065531-32467-1-git-send-email-hollow653@gmail.com> <08FAE06B-F6D3-49BD-9011-B5770629AA21@tarantool.org> <5BB99B27-5F86-4664-AAD5-57A22ECED854@tarantool.org> <93E4DAEA-EF90-479D-9F62-3D1CEB3CBE3F@tarantool.org> In-Reply-To: <93E4DAEA-EF90-479D-9F62-3D1CEB3CBE3F@tarantool.org> From: Hollow111 Date: Fri, 27 Apr 2018 15:45:44 +0000 Message-ID: Subject: [tarantool-patches] Re: [PATCH] sql: xfer optimization issue Content-Type: multipart/alternative; boundary="000000000000460a64056ad666e6" 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 --000000000000460a64056ad666e6 Content-Type: text/plain; charset="UTF-8" Hello. I considered your remarks and the following changes were made: diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c index ae8dafb..734ff34 100644 --- a/src/box/sql/insert.c +++ b/src/box/sql/insert.c @@ -1645,6 +1645,12 @@ xferCompatibleIndex(Index * pDest, Index * pSrc) assert(pDest->pTable != pSrc->pTable); uint32_t nDestCol = index_column_count(pDest); uint32_t nSrcCol = index_column_count(pSrc); + /* One of them is PK while the other isn't. */ + if ((pDest->idxType == SQLITE_IDXTYPE_PRIMARYKEY && + pSrc->idxType != SQLITE_IDXTYPE_PRIMARYKEY) || + (pDest->idxType != SQLITE_IDXTYPE_PRIMARYKEY && + pSrc->idxType == SQLITE_IDXTYPE_PRIMARYKEY)) + return 0; if (nDestCol != nSrcCol) { return 0; /* Different number of columns */ } @@ -1718,9 +1724,10 @@ xferOptimization(Parse * pParse, /* Parser context */ int emptyDestTest = 0; /* Address of test for empty pDest */ int emptySrcTest = 0; /* Address of test for empty pSrc */ Vdbe *v; /* The VDBE we are building */ - int destHasUniqueIdx = 0; /* True if pDest has a UNIQUE index */ int regData, regTupleid; /* Registers holding data and tupleid */ struct session *user_session = current_session(); + int dest_has_replace_action = 0; + int confl_action_default = 0; if (pSelect == NULL) return 0; /* Must be of the form INSERT INTO ... SELECT ... */ @@ -1737,8 +1744,10 @@ xferOptimization(Parse * pParse, /* Parser context */ if (onError == ON_CONFLICT_ACTION_DEFAULT) { if (pDest->iPKey >= 0) onError = pDest->keyConf; - if (onError == ON_CONFLICT_ACTION_DEFAULT) + if (onError == ON_CONFLICT_ACTION_DEFAULT) { onError = ON_CONFLICT_ACTION_ABORT; + confl_action_default = 1; + } } assert(pSelect->pSrc); /* allocated even if there is no FROM clause */ if (pSelect->pSrc->nSrc != 1) { @@ -1828,15 +1837,16 @@ xferOptimization(Parse * pParse, /* Parser context */ return 0; /* Default values must be the same for all columns */ } } + if (pDestCol->notNull == ON_CONFLICT_ACTION_REPLACE) + dest_has_replace_action = 1; } for (pDestIdx = pDest->pIndex; pDestIdx; pDestIdx = pDestIdx->pNext) { - if (index_is_unique(pDestIdx)) { - destHasUniqueIdx = 1; - } for (pSrcIdx = pSrc->pIndex; pSrcIdx; pSrcIdx = pSrcIdx->pNext) { if (xferCompatibleIndex(pDestIdx, pSrcIdx)) break; } + if (pDestIdx->onError != ON_CONFLICT_ACTION_REPLACE) + dest_has_replace_action = 1; if (pSrcIdx == 0) { return 0; /* pDestIdx has no corresponding index in pSrc */ } @@ -1875,58 +1885,63 @@ xferOptimization(Parse * pParse, /* Parser context */ regData = sqlite3GetTempReg(pParse); regTupleid = sqlite3GetTempReg(pParse); sqlite3OpenTable(pParse, iDest, pDest, OP_OpenWrite); - assert(destHasUniqueIdx); - if ((pDest->iPKey < 0 && pDest->pIndex != 0) /* (1) */ - ||destHasUniqueIdx /* (2) */ - || (onError != ON_CONFLICT_ACTION_ABORT - && onError != ON_CONFLICT_ACTION_ROLLBACK) /* (3) */ - ) { - /* In some circumstances, we are able to run the xfer optimization - * only if the destination table is initially empty. - * This block generates code to make - * that determination. - * - * Conditions under which the destination must be empty: - * - * (1) There is no INTEGER PRIMARY KEY but there are indices. - * - * (2) The destination has a unique index. (The xfer optimization - * is unable to test uniqueness.) - * - * (3) onError is something other than ON_CONFLICT_ACTION_ABORT and _ROLLBACK. - */ + + /* Xfer optimization is unable to correctly insert + * data in case there's a conflict action + * other than ON_CONFLICT_ACTION_ROLLBACK or there's + * ON_CONFLICT_ACTION_DEFAULT which was transformed into + * ON_CONFLICT_ACTION_ABORT for insertion while we have a + * ON_CONFLICT_ACTION_REPLACE for any of constraints. + * This is the reason we want to only run it + * if the destination table is initially empty. + * That block generates code to make that determination. + */ + + if (onError != ON_CONFLICT_ACTION_ROLLBACK || + (confl_action_default == 1 && + dest_has_replace_action == 1)) { addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0); VdbeCoverage(v); emptyDestTest = sqlite3VdbeAddOp0(v, OP_Goto); sqlite3VdbeJumpHere(v, addr1); } - for (pDestIdx = pDest->pIndex; pDestIdx; pDestIdx = pDestIdx->pNext) { - for (pSrcIdx = pSrc->pIndex; ALWAYS(pSrcIdx); - pSrcIdx = pSrcIdx->pNext) { - if (xferCompatibleIndex(pDestIdx, pSrcIdx)) - break; - } - assert(pSrcIdx); - emit_open_cursor(pParse, iSrc, pSrcIdx->tnum); - sqlite3VdbeSetP4KeyInfo(pParse, pSrcIdx); - VdbeComment((v, "%s", pSrcIdx->zName)); - emit_open_cursor(pParse, iDest, pDestIdx->tnum); - sqlite3VdbeSetP4KeyInfo(pParse, pDestIdx); - sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR); - VdbeComment((v, "%s", pDestIdx->zName)); - addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); - VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData); - sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData); - if (pDestIdx->idxType == SQLITE_IDXTYPE_PRIMARYKEY) - sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE); - sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1 + 1); - VdbeCoverage(v); - sqlite3VdbeJumpHere(v, addr1); - sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0); - sqlite3VdbeAddOp2(v, OP_Close, iDest, 0); - } + int space_ptr_reg = ++pParse->nMem; + struct space *src_space = + space_by_id(SQLITE_PAGENO_TO_SPACEID(pSrc->tnum)); + struct space *dest_space = + space_by_id(SQLITE_PAGENO_TO_SPACEID(pDest->tnum)); + struct index *src_idx = space_index(src_space, 0); + struct index *dest_idx; + + pDestIdx = sqlite3PrimaryKeyIndex(pDest); + pSrcIdx = sqlite3PrimaryKeyIndex(pSrc); + space_ptr_reg = ++pParse->nMem; + sqlite3VdbeAddOp4Ptr(v, OP_LoadPtr, 0, space_ptr_reg, 0, + (void *) src_space); + sqlite3VdbeAddOp3(v, OP_OpenWrite, iSrc, + pSrc->tnum, space_ptr_reg); + VdbeComment((v, "%s", src_idx->def->name)); + + if((dest_idx = space_index(dest_space, 0)) == NULL) + return 0; + space_ptr_reg = ++pParse->nMem; + sqlite3VdbeAddOp4Ptr(v, OP_LoadPtr, 0, space_ptr_reg, 0, + (void *) dest_space); + sqlite3VdbeAddOp3(v, OP_OpenWrite, iDest, + pSrc->tnum, space_ptr_reg); + sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR); + VdbeComment((v, "%s", dest_idx->def->name)); + addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); + VdbeCoverage(v); + sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData); + sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData); + sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE); + sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1 + 1); + VdbeCoverage(v); + sqlite3VdbeJumpHere(v, addr1); + sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0); + sqlite3VdbeAddOp2(v, OP_Close, iDest, 0); if (emptySrcTest) sqlite3VdbeJumpHere(v, emptySrcTest); sqlite3ReleaseTempReg(pParse, regTupleid); diff --git a/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua new file mode 100755 index 0000000..860b4c3 --- /dev/null +++ b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua @@ -0,0 +1,233 @@ +#!/usr/bin/env tarantool +test = require("sqltester") +test:plan(18) + +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-oprimization-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-oprimization-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-oprimization-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-oprimization-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-oprimization-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-oprimization-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 UNIQUE ON CONFLICT REPLACE); + CREATE TABLE t2(a INTEGER PRIMARY KEY, b UNIQUE ON CONFLICT REPLACE); + 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-oprimization-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 ON CONFLICT REPLACE); + CREATE TABLE t2(a INTEGER PRIMARY KEY, b UNIQUE ON CONFLICT REPLACE); + 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-oprimization-1.16", + [[ + SELECT * FROM t2; + ]], { + -- + 1, 1, 2, 2, 3, 3, 4, 4, 5, 5 + -- + }) + +test:do_catchsql_test( + "xfer-optimization-1.17", + [[ + DROP TABLE t1; + DROP TABLE t2; + CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE ON CONFLICT REPLACE); + CREATE TABLE t2(a INTEGER PRIMARY KEY, b UNIQUE ON CONFLICT REPLACE); + INSERT INTO t1 VALUES (1, 2), (3, 3), (5, 5); + INSERT INTO t2 VALUES (1, 1), (4, 4); + INSERT OR REPLACE INTO t2 SELECT * FROM t1; + ]], { + -- + 0 + -- + }) + +test:do_execsql_test( + "xfer-oprimization-1.18", + [[ + SELECT * FROM t2; + ]], { + -- + 1, 2, 3, 3, 4, 4, 5, 5 + -- + }) + +test:finish_test() --000000000000460a64056ad666e6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello. I considered your remarks and the following changes= were made:

diff --git a/src/box/sql/insert.c b/src/box/sql/ins= ert.c
index ae8dafb..734ff34 100644
--- a/src/box/sql/i= nsert.c
+++ b/src/box/sql/insert.c
@@ -1645,6 +1645,12 = @@ xferCompatibleIndex(Index * pDest, Index * pSrc)
=C2=A0 assert(pDest->pTable !=3D pSrc->pTab= le);
=C2=A0 uint32_t nDest= Col =3D index_column_count(pDest);
=C2=A0 uint32_t nSrcCol =3D index_column_count(pSrc);
+<= span style=3D"white-space:pre"> /* One of them is PK while the other= isn't. */
+ if ((pDes= t->idxType =3D=3D SQLITE_IDXTYPE_PRIMARYKEY &&
+ =C2=A0 =C2=A0 =C2=A0pSrc->idxType !=3D= SQLITE_IDXTYPE_PRIMARYKEY) ||
+ = =C2=A0 =C2=A0 (pDest->idxType !=3D SQLITE_IDXTYPE_PRIMARYKEY &= ;&
+ =C2=A0 =C2=A0 =C2= =A0pSrc->idxType =3D=3D SQLITE_IDXTYPE_PRIMARYKEY))
+ return 0;
=C2=A0 if (nDestCol !=3D nSrcCol) {
=C2=A0 return 0;= /* Different number of columns */
=C2=A0 }
@@ -1718,9 +1724,10 @@ xferOptimization(= Parse * pParse, /* Parser context */=
=C2=A0 int emptyDestTest = =3D 0; /* Address of test for empty = pDest */
=C2=A0 int emptyS= rcTest =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 regDa= ta, regTupleid; /* Registers holding= data and tupleid */
=C2=A0 struct session *user_session =3D current_session();
+ int dest_has_replace_action =3D 0;
= + int confl_action_default =3D 0;
=C2=A0
=C2=A0 if (= pSelect =3D=3D NULL)
=C2=A0 return 0; /* Must be of the form= =C2=A0 INSERT INTO ... SELECT ... */
@@ -1737,8 +1744,10 @@ xferO= ptimization(Parse * pParse, /* Parse= r context */
=C2=A0 if (on= Error =3D=3D ON_CONFLICT_ACTION_DEFAULT) {
=C2=A0 if (pDest->iPKey >=3D 0)
=C2=A0 onError =3D pDest->keyConf;
=
- if (onError =3D=3D ON_CONFLI= CT_ACTION_DEFAULT)
+ if (= onError =3D=3D ON_CONFLICT_ACTION_DEFAULT) {
=C2=A0 onError =3D ON_CONFLICT_ACTION_ABORT;
+ confl_action_default =3D 1;
+ }
=C2=A0 }
=C2=A0 assert(pSelect->pSrc); /* allocated even if there is no FROM clause */
=C2=A0 if (pSelect->pSrc->nSrc !=3D 1) {
@@ -1828,15 +1837,16 @@ xferOptimization(Parse * pParse, /* Parser context */
=C2=A0 return 0; /* Default values must be the same for all columns */
= =C2=A0 }
=C2=A0 }
+ if (pDestCol->notNull =3D=3D ON_CONFLICT_ACTION_REPLACE)
+ dest_has_replace_action = =3D 1;
=C2=A0 }
= =C2=A0 for (pDestIdx =3D pDest->p= Index; 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;
=C2=A0 }
+ <= /span>if (pDestIdx->onError !=3D ON_CONFLICT_ACTION_REPLACE)
+= dest_has_replace_action =3D 1;
=C2=A0 if (pSrcIdx =3D=3D 0= ) {
=C2=A0 return 0; /* pDestIdx has no corresponding index = in pSrc */
=C2=A0 }
=
@@ -1875,58 +1885,63 @@ xferOptimization(Parse * pParse, /* Parser context */
=C2=A0 regData =3D sqlite3GetTempReg(pParse);
=C2=A0 regTupleid =3D sqlite3GetT= empReg(pParse);
=C2=A0 sql= ite3OpenTable(pParse, iDest, pDest, OP_OpenWrite);
- assert(destHasUniqueIdx);
- if ((pDest->iPKey < 0 && pDest= ->pIndex !=3D 0) /* (1) */
<= div>- =C2=A0 =C2=A0 ||destHasUniqueI= dx /* (2) */
- =C2=A0 =C2=A0 || (onError !=3D ON_CONFLICT_ACT= ION_ABORT
- && on= Error !=3D ON_CONFLICT_ACTION_ROLLBACK) /* (3) */
- =C2=A0 =C2= =A0 ) {
- /* In some circ= umstances, we are able to run the xfer optimization
- * only if the destination table is initially= empty.
- * This block g= enerates code to make
- = * that determination.
- = *
- * Conditions under w= hich the destination must be empty:
- *
- * (1) = There is no INTEGER PRIMARY KEY but there are indices.
- *
- * (2) The destination has a unique index.=C2=A0 (The xfer opti= mization
- *=C2=A0 =C2= =A0 =C2=A0is unable to test uniqueness.)
- *
- *= (3) onError is something other than ON_CONFLICT_ACTION_ABORT and _ROLLBACK= .
- */
+
=
+ /* Xfer optimization is unabl= e to correctly insert
+ *= data in case there's a conflict action
+ * other than ON_CONFLICT_ACTION_ROLLBACK or there'= s
+ * ON_CONFLICT_ACTION_= DEFAULT which was transformed into
+ * ON_CONFLICT_ACTION_ABORT for insertion while we have a
<= div>+ * ON_CONFLICT_ACTION_REPLACE = for any of constraints.
+ = * This is the reason we want to only run it
+ * if the destination table is initially empty.
<= div>+ * That block generates code t= o make that determination.
+ */
+
+ if (o= nError !=3D ON_CONFLICT_ACTION_ROLLBACK ||
+ =C2=A0 =C2=A0 (confl_action_default =3D=3D 1 &&<= /div>
+ =C2=A0 =C2=A0 dest_has_r= eplace_action =3D=3D 1)) {
=C2=A0= addr1 =3D sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0);
=C2= =A0 VdbeCoverage(v);
=C2= =A0 emptyDestTest =3D sqlite3VdbeAd= dOp0(v, OP_Goto);
=C2=A0 = sqlite3VdbeJumpHere(v, addr1);
=C2=A0 }
=C2=A0
- = for (pDestIdx =3D pDest->pIndex; pDestIdx; pDestIdx =3D pDestIdx-= >pNext) {
- for (pSrcI= dx =3D pSrc->pIndex; ALWAYS(pSrcIdx);
- =C2=A0 =C2=A0 =C2=A0pSrcIdx =3D pSrcIdx->pNext) {
- if (xferCompatibleIndex(pD= estIdx, pSrcIdx))
- bre= ak;
- }
- assert(pSrcIdx);
- emit_open_cursor(pParse, iSrc, pSrcIdx->tn= um);
- sqlite3VdbeSetP4Ke= yInfo(pParse, pSrcIdx);
- VdbeComment((v, "%s", pSrcIdx->zName));
- emit_open_cursor(pParse, iDest, pDestIdx->= ;tnum);
- sqlite3VdbeSetP= 4KeyInfo(pParse, pDestIdx);
- sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR);
- VdbeComment((v, "%s", pDestIdx->zName));=
- addr1 =3D sqlite3VdbeA= ddOp2(v, OP_Rewind, iSrc, 0);
- = VdbeCoverage(v);
- sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);
- sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regDat= a);
- if (pDestIdx->id= xType =3D=3D SQLITE_IDXTYPE_PRIMARYKEY)
- sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE);
- sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr= 1 + 1);
- VdbeCoverage(v)= ;
- sqlite3VdbeJumpHere(v= , addr1);
- sqlite3VdbeAd= dOp2(v, OP_Close, iSrc, 0);
- sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
- }
+ int space_ptr_reg =3D ++pParse->nMem;
+ struct space *src_space =3D
+ space_by_id(SQLITE_PAGENO_TO_SPACEID(pSrc->tnum))= ;
+ struct space *dest_spa= ce =3D
+ space_by_id(SQLI= TE_PAGENO_TO_SPACEID(pDest->tnum));
+ struct index *src_idx =3D space_index(src_space, 0);
+ struct index *dest_idx;
+
+ pDestIdx =3D sqlite= 3PrimaryKeyIndex(pDest);
+ pSrcIdx =3D sqlite3PrimaryKeyIndex(pSrc);
+ space_ptr_reg =3D ++pParse->nMem;
+ sqlite3VdbeAddOp4Ptr(v, OP_LoadPtr, 0, spac= e_ptr_reg, 0,
+ =C2=A0 = =C2=A0 =C2=A0(void *) src_space);
+ sqlite3VdbeAddOp3(v, OP_OpenWrite, iSrc,
+ =C2=A0 pSrc->tnum, space_ptr_reg);
<= div>+ VdbeComment((v, "%s"= , src_idx->def->name));
+
+ if((dest_idx =3D space_index(dest_space, 0)) =3D=3D NULL)=
+ return 0;
+<= span style=3D"white-space:pre"> space_ptr_reg =3D ++pParse->nMem;=
+ sqlite3VdbeAddOp4Ptr(v,= OP_LoadPtr, 0, space_ptr_reg, 0,
+ =C2=A0 =C2=A0 =C2=A0(void *) dest_space);
+ sqlite3VdbeAddOp3(v, OP_OpenWrite, iDest,
+ =C2=A0 pSrc->tnum, spa= ce_ptr_reg);
+ sqlite3Vdbe= ChangeP5(v, OPFLAG_BULKCSR);
+ VdbeComment((v, "%s", dest_idx->def->name));
+ addr1 =3D sqlite3VdbeAddOp2(v, OP= _Rewind, iSrc, 0);
+ VdbeC= overage(v);
+ sqlite3VdbeA= ddOp2(v, OP_RowData, iSrc, regData);
+ sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData);
= + sqlite3VdbeChangeP5(v, OPFLAG_NCHA= NGE);
+ sqlite3VdbeAddOp2(= v, OP_Next, iSrc, addr1 + 1);
+ <= /span>VdbeCoverage(v);
+ s= qlite3VdbeJumpHere(v, addr1);
+ <= /span>sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
+ sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
= =C2=A0 if (emptySrcTest)
= =C2=A0 sqlite3VdbeJumpHere(v, empty= SrcTest);
=C2=A0 sqlite3Re= leaseTempReg(pParse, regTupleid);
diff --git a/test/sql-tap/gh-33= 07-xfer-optimization-issue.test.lua b/test/sql-tap/gh-3307-xfer-optimizatio= n-issue.test.lua
new file mode 100755
index 0000000..86= 0b4c3
--- /dev/null
+++ b/test/sql-tap/gh-3307-xfer-opt= imization-issue.test.lua
@@ -0,0 +1,233 @@
+#!/usr/bin/= env tarantool
+test =3D require("sqltester")
= +test:plan(18)
+
+test:do_catchsql_test(
+ "xfer-optimization-1.1",
+ [[
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTE= GER UNIQUE);
+ INSERT INT= O t1 VALUES (1, 1), (2, 2), (3, 3);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER UNIQUE);
+ INSERT INTO t2 SELECT * FRO= M t1;
+ ]], {
+<= span style=3D"white-space:pre"> -- <xfer-optimization-1.1>
+ 0
+ -- <xfer-optimization-1.1>
+<= span style=3D"white-space:pre"> })
+
+test:do_ex= ecsql_test(
+ "xfer-o= primization-1.2",
+ [= [
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-oprimization-1.2>
+= 1, 1, 2, 2, 3, 3
+ -- <xfer-oprimization-1.2>
+ })
+
+test:= do_catchsql_test(
+ "= xfer-optimization-1.3",
+ [[
+ DROP TABLE t1;<= /div>
+ DROP TABLE t2;
+ CREATE TABLE t1(id INTEGER PRIM= ARY KEY, b INTEGER);
+ CR= EATE 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 SELE= CT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.= 3>
+ 0
+ -- <xfer-optimization-1.3>
=
+ })
+
+tes= t:do_execsql_test(
+ "= ;xfer-oprimization-1.4",
+ <= /span>[[
+ SELECT * FROM = t2;
+ ]], {
+ -- <xfer-optimization-1.4>
+ 1, 1, 2, 2, 3, 3
= + -- <xfer-optimization-1.4><= /div>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.5",
+ [[
+ DROP TAB= LE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGE= R PRIMARY KEY, b INTEGER, c INTEGER);
+ INSERT INTO t1 VALUES (1, 1, 2), (2, 2, 3), (3, 3, 4);
=
+ CREATE TABLE t2(a INTEGER PR= IMARY KEY, b INTEGER);
+ = INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <x= fer-optimization-1.5>
+ 1, "table T2 has 2 columns but 3 values were supplied"
+ -- <xfer-optimization-1.5>= ;
+ })
+
+test:do_execsql_test(
+ "xfer-oprimization-1.6",
+ [[
+ SELECT = * FROM t2;
+ ]], {
+ -- <xfer-oprimization-1.6&g= t;
+
+ -- <x= fer-oprimization-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 INTE= GER PRIMARY KEY, b INTEGER);
+ <= /span>INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ --= <xfer-optimization-1.7>
+ = 0
+ -- <xfer-o= ptimization-1.7>
+ })
+
+test:do_execsql_test(
+ "xfer-oprimization-1.8",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-= oprimization-1.6>
+ 1,= 1, 2, 2, 3, 3
+ -- <x= fer-oprimization-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 INTE= GER, a INTEGER PRIMARY KEY);
+ <= /span>INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ --= <xfer-optimization-1.9>
+ = 1, "Duplicate key exists in unique index 'sqlite_autoindex= _T2_1' in space 'T2'"
+ -- <xfer-optimization-1.9>
+ })
+
+test:do_execsql_test(
+ "xfer-oprimization-1.= 10",
+ [[
+= SELECT * FROM t2;
+ ]], {
+ -- <xfer-oprimization-1.10>
+
+= -- <xfer-oprimization-1.10><= /div>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.11",
+ [[
+ DROP TA= BLE t1;
+ DROP TABLE t2;<= /div>
+ CREATE TABLE t1(a INTEG= ER PRIMARY KEY, b INTEGER);
+ INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 2);
+ CREATE TABLE t2(b INTEGER PRIMARY KEY, a INTE= GER);
+ INSERT INTO t2 SE= LECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-= 1.11>
+ 0
+<= span style=3D"white-space:pre"> -- <xfer-optimization-1.11>
+ })
+
= +test:do_execsql_test(
+ &= quot;xfer-oprimization-1.12",
+ [[
+ SELECT * = FROM t2;
+ ]], {
+ -- <xfer-oprimization-1.12>= ;
+ 1, 1, 2, 2, 3, 2
+ -- <xfer-oprimization-1.= 12>
+ })
+
+test:do_catchsql_test(
+= "xfer-optimization-1.13",
+ [[
+ = DROP TABLE t1;
+ DROP TAB= LE t2;
+ CREATE TABLE t1(= a INTEGER PRIMARY KEY, b UNIQUE ON CONFLICT REPLACE);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b UNI= QUE ON CONFLICT REPLACE);
+ INSERT INTO t1 VALUES (3, 3), (4, 4), (5, 5);
+ INSERT INTO t2 VALUES (1, 1), (2, 2);
+= INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.13>
+ 0
+ -- <xfer-optimization-1.13>
+ })
+
+test:do_execsql_= test(
+ "xfer-oprimiz= ation-1.14",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-optimization-1.14>
+ 1, 1, 2, 2, 3, 3, 4, 4, 5, 5
+ -- <xfer-optimization-1.14>= ;
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.15",
+ [[
+ DROP = TABLE t1;
+ DROP TABLE t2= ;
+ CREATE TABLE t1(a INT= EGER PRIMARY KEY, b UNIQUE ON CONFLICT REPLACE);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b UNIQUE O= N CONFLICT REPLACE);
+ IN= SERT INTO t1 VALUES (2, 2), (3, 3), (5, 5);
+ INSERT INTO t2 VALUES (1, 1), (4, 4);
+ INSERT OR ROLLBACK INTO t2 SELECT * FRO= M t1;
+ ]], {
+<= span style=3D"white-space:pre"> -- <xfer-optimization-1.15>
+ 0
+ -- <xfer-optimization-1.15>
+= })
+
+test:do_e= xecsql_test(
+ "xfer-= oprimization-1.16",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-oprimization-1.16>
+ 1, 1, 2, 2, 3, 3, 4, 4, 5, 5
+ -- <xfer-oprimization-= 1.16>
+ })
+<= /div>
+test:do_catchsql_test(
+ "xfer-optimization-1.17",
+ [[
+ DROP TABLE t1;
+ DROP T= ABLE t2;
+ CREATE TABLE t= 1(a INTEGER PRIMARY KEY, b UNIQUE ON CONFLICT REPLACE);
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b U= NIQUE ON CONFLICT REPLACE);
+ INSERT INTO t1 VALUES (1, 2), (3, 3), (5, 5);
+ INSERT INTO t2 VALUES (1, 1), (4, 4);
+ INSERT OR REPLACE INTO t2 SELE= CT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.= 17>
+ 0
+ -- <xfer-optimization-1.17>
+ })
+
+t= est:do_execsql_test(
+ &qu= ot;xfer-oprimization-1.18",
+ [[
+ SELECT * FR= OM t2;
+ ]], {
+= -- <xfer-oprimization-1.18><= /div>
+ 1, 2, 3, 3, 4, 4, 5, 5<= /div>
+ -- <xfer-oprimizatio= n-1.18>
+ })
= +
+test:finish_test()


--000000000000460a64056ad666e6--