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 8226D214B1 for ; Fri, 20 Apr 2018 13:59:49 -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 X1c-Q8o6bdNW for ; Fri, 20 Apr 2018 13:59:49 -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 D528B2149F for ; Fri, 20 Apr 2018 13:59:48 -0400 (EDT) Received: by mail-lf0-f66.google.com with SMTP id z130-v6so6334468lff.5 for ; Fri, 20 Apr 2018 10:59:48 -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> In-Reply-To: From: Hollow111 Date: Fri, 20 Apr 2018 17:59:36 +0000 Message-ID: Subject: [tarantool-patches] Re: [PATCH] sql: xfer optimization issue Content-Type: multipart/alternative; boundary="0000000000002001d9056a4b74d9" 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 --0000000000002001d9056a4b74d9 Content-Type: text/plain; charset="UTF-8" Diff for the fixed patch: diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c index ae8dafb..87e1bad 100644 --- a/src/box/sql/insert.c +++ b/src/box/sql/insert.c @@ -1645,6 +1645,9 @@ 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 == 2 && pSrc->idxType != 2) + || (pDest->idxType != 2 && pSrc->idxType == 2)) if (nDestCol != nSrcCol) { return 0; /* Different number of columns */ } @@ -1711,16 +1714,21 @@ xferOptimization(Parse * pParse, /* Parser context */ ExprList *pEList; /* The result set of the SELECT */ Table *pSrc; /* The table in the FROM clause of SELECT */ Index *pSrcIdx, *pDestIdx; /* Source and destination indices */ + /* Source and destination indices */ + struct index *src_idx, *dest_idx; struct SrcList_item *pItem; /* An element of pSelect->pSrc */ int i; /* Loop counter */ int iSrc, iDest; /* Cursors from source and destination */ int addr1; /* Loop addresses */ int emptyDestTest = 0; /* Address of test for empty pDest */ int emptySrcTest = 0; /* Address of test for empty pSrc */ + /* Number of memory cell for cursors */ + int space_ptr_reg; 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(); + /* Space pointer for pDest and pSrc */ + struct space *space; if (pSelect == NULL) return 0; /* Must be of the form INSERT INTO ... SELECT ... */ @@ -1830,9 +1838,6 @@ xferOptimization(Parse * pParse, /* Parser context */ } } for (pDestIdx = pDest->pIndex; pDestIdx; pDestIdx = pDestIdx->pNext) { - if (index_is_unique(pDestIdx)) { - destHasUniqueIdx = 1; - } for (pSrcIdx = pSrc->pIndex; pSrcIdx; pSrcIdx = pSrcIdx->pNext) { if (xferCompatibleIndex(pDestIdx, pSrcIdx)) break; @@ -1875,58 +1880,51 @@ 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. - */ - 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); - } + /* The xfer optimization is unable to test + * uniqueness while we have a unique + * PRIMARY KEY in any existing table. + * This is the reason we can only run it + * if the destination table is initially empty. + * This block generates code to make + * that determination. + */ + addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0); + VdbeCoverage(v); + emptyDestTest = sqlite3VdbeAddOp0(v, OP_Goto); + sqlite3VdbeJumpHere(v, addr1); + + pDestIdx = sqlite3PrimaryKeyIndex(pDest); + pSrcIdx = sqlite3PrimaryKeyIndex(pSrc); + space = space_by_id(SQLITE_PAGENO_TO_SPACEID(pSrc->tnum)); + if((src_idx = space_index(space, 0 /* PK */)) == NULL) + return 0; + space_ptr_reg = ++pParse->nMem; + sqlite3VdbeAddOp4Ptr(v, OP_LoadPtr, 0, space_ptr_reg, 0, + (void *) space); + sqlite3VdbeAddOp3(v, OP_OpenWrite, iSrc, + pSrc->tnum, space_ptr_reg); + VdbeComment((v, "%s", src_idx->def->name)); + space = space_by_id(SQLITE_PAGENO_TO_SPACEID(pDest->tnum)); + if((dest_idx = space_index(space, 0 /* PK */)) == NULL) + return 0; + space_ptr_reg = ++pParse->nMem; + sqlite3VdbeAddOp4Ptr(v, OP_LoadPtr, 0, space_ptr_reg, 0, + (void *) 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..0824c67 --- /dev/null +++ b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua @@ -0,0 +1,155 @@ +#!/usr/bin/env tarantool +test = require("sqltester") +test:plan(12) + +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:finish_test() > --0000000000002001d9056a4b74d9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Diff for the fixed patch:

diff --git a/src/box= /sql/insert.c b/src/box/sql/insert.c
index ae8dafb..87e1bad 10064= 4
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c=
@@ -1645,6 +1645,9 @@ xferCompatibleIndex(Index * pDest, Index *= pSrc)
=C2=A0 assert(pDest= ->pTable !=3D pSrc->pTable);
=C2=A0 uint32_t nDestCol =3D index_column_count(pDest);
= =C2=A0 uint32_t nSrcCol =3D index_co= lumn_count(pSrc);
+ /* One= of them is PK while the other isn't */
+ if ((pDest->idxType =3D=3D 2 && pSrc->idx= Type !=3D 2)
+ =C2=A0 =C2= =A0 || (pDest->idxType !=3D 2 && pSrc->idxType =3D=3D 2))
=C2=A0 if (nDestCol !=3D nSrc= Col) {
=C2=A0 return 0; /* Different number of columns */
=C2=A0 }
@@ -1711,1= 6 +1714,21 @@ xferOptimization(Parse * pParse, /* Parser context */
=C2=A0 ExprList *pEList; /* Th= e result set of the SELECT */
=C2=A0 Table *pSrc; /* The tabl= e in the FROM clause of SELECT */
=C2=A0 Index *pSrcIdx, *pDestIdx; <= /span>/* Source and destination indices */
+ /* Source and destination indices */
+ struct index *src_idx, *dest_idx;
=C2=A0 struct SrcList_item *pItem;<= span style=3D"white-space:pre"> /* An element of pSelect->pSrc */=
=C2=A0 int i; /* Loop counter */
=C2=A0 int iSrc, iDest; /* Cursors from source and destination */
=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 */
+ /* Number of memory cell for cursors */
+ int space_ptr_reg;
=C2=A0 Vdbe *v; /* The VDBE we are building */
- int destHasUniqueIdx =3D 0; /* True if pDest has a UNIQUE index */
=C2=A0 int regData, regTupleid; /* Registers holding data and tupleid */
=C2= =A0 struct session *user_session =3D= current_session();
+ /* S= pace pointer for pDest and pSrc */
+ struct space *space;
=C2=A0
=C2=A0 if (pSelect =3D=3D NULL)
=C2=A0 return 0; /* Must be of the form=C2=A0 INSERT INTO ... SELECT ... */
=
@@ -1830,9 +1838,6 @@ xferOptimization(Parse * pParse, /* Parser context */
=C2=A0 }
=C2=A0 }
=C2=A0 for (pD= estIdx =3D pDest->pIndex; pDestIdx; pDestIdx =3D pDestIdx->pNext) {
- if (index_is_unique(pDes= tIdx)) {
- destHasUnique= Idx =3D 1;
- }
= =C2=A0 for (pSrcIdx =3D pSrc->pI= ndex; pSrcIdx; pSrcIdx =3D pSrcIdx->pNext) {
=C2=A0 if (xferCompatibleIndex(pDestIdx, pSrcIdx))<= /div>
=C2=A0 break;
@@ -1875,58 +1880,51 @@ xferOptimization(Parse * pParse, /* Parser context */
=C2=A0 regData =3D sqlite3GetTempReg(pParse);
= =C2=A0 regTupleid =3D sqlite3GetTemp= Reg(pParse);
=C2=A0 sqlite= 3OpenTable(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_AB= ORT
- && onError = !=3D ON_CONFLICT_ACTION_ROLLBACK) /*= (3) */
- =C2=A0 =C2=A0 ) = {
- /* In some circumstan= ces, we are able to run the xfer optimization
- * only if the destination table is initially empty.=
- * This block generate= s code to make
- * that = determination.
- *
=
- * Conditions under which th= e destination must be empty:
- <= /span> *
- * (1) There i= s no INTEGER PRIMARY KEY but there are indices.
- *
- * (2) The destination has a unique index.=C2=A0 (The xfer optimizatio= n
- *=C2=A0 =C2=A0 =C2= =A0is unable to test uniqueness.)
- *
- * (3) on= Error is something other than ON_CONFLICT_ACTION_ABORT and _ROLLBACK.
=
- */
- addr1 =3D sqlite3VdbeAddOp2(v, OP_Rewind, iDe= st, 0);
- VdbeCoverage(v)= ;
- emptyDestTest =3D sql= ite3VdbeAddOp0(v, OP_Goto);
- sqlite3VdbeJumpHere(v, addr1);
- }
=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);
- }
+ /* The xfer optimization is unable to test
+ * uniqueness while we have a unique
+ * PRIMARY KEY in any existing table.
+ * This is the reason we can= only run it
+ * if the d= estination table is initially empty.
+ * This block generates code to make
+ * that determination.
+ */
+ addr1 =3D sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0);
+ VdbeCoverage(v);
+ emptyDestTest =3D sqlite3VdbeAddOp0(v, OP_Goto= );
+ sqlite3VdbeJumpHere(v= , addr1);
+
+ pD= estIdx =3D sqlite3PrimaryKeyIndex(pDest);
+ pSrcIdx =3D sqlite3PrimaryKeyIndex(pSrc);
+ space =3D space_by_id(SQLITE_PAGENO_TO_= SPACEID(pSrc->tnum));
+ if((src_idx =3D space_index(space, 0 /* PK */)) =3D=3D NULL)
+ return 0;
+ space_ptr_reg =3D ++pParse->nMem;
+ sqlite3VdbeAddOp4Ptr(v, OP_LoadPtr, 0= , space_ptr_reg, 0,
+ = =C2=A0 =C2=A0 =C2=A0(void *) space);
+ sqlite3VdbeAddOp3(v, OP_OpenWrite, iSrc,
+ =C2=A0 pSrc->tnum, space_ptr_reg);
=
+ VdbeComment((v, "%s"= ;, src_idx->def->name));
+ = space =3D space_by_id(SQLITE_PAGENO_TO_SPACEID(pDest->tnum));
+ if((dest_idx =3D space_inde= x(space, 0 /* PK */)) =3D=3D NULL)
+ return 0;
+ sp= ace_ptr_reg =3D ++pParse->nMem;
+ sqlite3VdbeAddOp4Ptr(v, OP_LoadPtr, 0, space_ptr_reg, 0,
+ =C2=A0 =C2=A0 =C2=A0(void *) = space);
+ sqlite3VdbeAddOp= 3(v, OP_OpenWrite, iDest,
+ =C2=A0 pSrc->tnum, space_ptr_reg);
+ sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR);
+ VdbeComment((v, "%s", dest_idx-= >def->name));
+ addr= 1 =3D sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);
+ VdbeCoverage(v);
+ sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);
+ sqlite3VdbeAddOp2(v, OP_IdxInsert= , iDest, regData);
+ sqlit= e3VdbeChangeP5(v, OPFLAG_NCHANGE);
+ sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1 + 1);
+ VdbeCoverage(v);
+ sqlite3VdbeJumpHere(v, addr1);
+ sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0)= ;
+ sqlite3VdbeAddOp2(v, O= P_Close, iDest, 0);
=C2=A0 if (emptySrcTest)
=C2=A0 sqlite3VdbeJumpHere(v, emptySrcTest);
=C2=A0 sqlite3ReleaseTempReg(pParse, regTupleid);
di= ff --git a/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua b/test/sql= -tap/gh-3307-xfer-optimization-issue.test.lua
new file mode 10075= 5
index 0000000..0824c67
--- /dev/null
+++ b/= test/sql-tap/gh-3307-xfer-optimization-issue.test.lua
@@ -0,0 +1,= 155 @@
+#!/usr/bin/env tarantool
+test =3D require(&quo= t;sqltester")
+test:plan(12)
+
+test:do_= catchsql_test(
+ "xfe= r-optimization-1.1",
+ [[
+ CREATE TABLE t1(a = INTEGER PRIMARY KEY, b INTEGER UNIQUE);
+ INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
+<= span style=3D"white-space:pre"> CREATE TABLE t2(a INTEGER PRIMARY K= EY, b INTEGER UNIQUE);
+ = INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <x= fer-optimization-1.1>
+ 0
+ -- <xfer-optimiz= ation-1.1>
+ })
+
+test:do_execsql_test(
+ "xfer-oprimization-1.2",
+ [[
+ SELECT * FROM t2;
+ ]= ], {
+ -- <xfer-oprimi= zation-1.2>
+ 1, 1, 2,= 2, 3, 3
+ -- <xfer-op= rimization-1.2>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.3",
+ [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREAT= E 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 V= ALUES (1, 1), (2, 2), (3, 3);
+ = INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -= - <xfer-optimization-1.3>
+= 0
+ -- <xfer-= optimization-1.3>
+ })<= /div>
+
+test:do_execsql_test(
+ "xfer-oprimization-1.4",
+ [[
+ SELECT * FROM t2;
+ <= /span>]], {
+ -- <xfer= -optimization-1.4>
+ 1= , 1, 2, 2, 3, 3
+ -- <= xfer-optimization-1.4>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.5",
+= [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);
+ INSERT INTO t1 VALUES (1, 1, 2),= (2, 2, 3), (3, 3, 4);
+ = CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER);
+ INSERT INTO t2 SELECT * FROM t1;
+<= span style=3D"white-space:pre"> ]], {
+ -- <xfer-optimization-1.5>
+ 1, "table T2 has 2 columns but 3 value= s were supplied"
+ -= - <xfer-optimization-1.5>
+= })
+
+test:do_execsql_test(
+ "xfer-oprimization-1.6",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ = -- <xfer-oprimization-1.6>
+
+ -- <xfer-oprimization-1.6>
+ })
+
+test:do_catchsql_t= est(
+ "xfer-optimiza= tion-1.7",
+ [[
=
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT= EGER);
+ INSERT INTO t1 V= ALUES (1, 1), (2, 2), (3, 3);
+ = CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER);
+ INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.7>
+ 0
+ -- <xfer-optimization-1.7>
+ })
+
+test:do_execsql_test(
+ "xfer-oprimization-1.= 8",
+ [[
+<= span style=3D"white-space:pre"> SELECT * FROM t2;
+ ]], {
+ -- <xfer-oprimization-1.6>
+ 1, 1, 2, 2, 3, 3
+ -- <xfer-oprimization-1.6>
+ })
+
+test:do_catchsql_te= st(
+ "xfer-optimizat= ion-1.9",
+ [[
<= div>+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTE= GER);
+ INSERT INTO t1 VA= LUES (1, 1), (2, 2), (3, 2);
+ <= /span>CREATE TABLE t2(b INTEGER, a INTEGER PRIMARY KEY);
+ INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.9>
+ 1, "Duplicate key exists in unique= index 'sqlite_autoindex_T2_1' in space 'T2'"
+ -- <xfer-optimization-1.9>= ;
+ })
+
+test:do_execsql_test(
+ "xfer-oprimization-1.10",
+ [[
+ SELECT= * FROM t2;
+ ]], {
<= div>+ -- <xfer-oprimization-1.10= >
+
+ -- <= ;xfer-oprimization-1.10>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.11",
= + [[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
+ INSERT INTO t1 VALUES (1, 1), (2, 2), (3, = 2);
+ CREATE TABLE t2(b I= NTEGER PRIMARY KEY, a INTEGER);
+= INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.11>
+ 0
+ -- <xf= er-optimization-1.11>
+ })
+
+test:do_execsql_test(
+ "xfer-oprimization-1.12",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <= ;xfer-oprimization-1.12>
+ 1, 1, 2, 2, 3, 2
+ -= - <xfer-oprimization-1.12>
+ })
+
+test:finish_test()

<= div class=3D"gmail_quote">

--0000000000002001d9056a4b74d9--