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 85F5421202 for ; Thu, 19 Apr 2018 11:37:00 -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 EU5wLACVoHBI for ; Thu, 19 Apr 2018 11:37:00 -0400 (EDT) Received: from mail-lf0-f53.google.com (mail-lf0-f53.google.com [209.85.215.53]) (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 CA295211F8 for ; Thu, 19 Apr 2018 11:36:59 -0400 (EDT) Received: by mail-lf0-f53.google.com with SMTP id m202-v6so801586lfe.8 for ; Thu, 19 Apr 2018 08:36:59 -0700 (PDT) MIME-Version: 1.0 References: <1524065531-32467-1-git-send-email-hollow653@gmail.com> <08FAE06B-F6D3-49BD-9011-B5770629AA21@tarantool.org> In-Reply-To: <08FAE06B-F6D3-49BD-9011-B5770629AA21@tarantool.org> From: Hollow111 Date: Thu, 19 Apr 2018 15:36:46 +0000 Message-ID: Subject: [tarantool-patches] Re: [PATCH] sql: xfer optimization issue Content-Type: multipart/alternative; boundary="0000000000007edb27056a35576c" 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 --0000000000007edb27056a35576c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Fixes were made: =D1=87=D1=82, 19 =D0=B0=D0=BF=D1=80. 2018 =D0=B3. =D0=B2 14:22, n.pettik : > >The bug was fixed so the data should now insert > >correctly. > > Please, instead of mentioning that you just fixed bug (it is obvious), > provide brief information (without digging in details) how the problem wa= s > solved. > (e.g. 'now only PK is used to handle insertion'). > > Overall, the idea is OK, but implementation could be more elegant. > You don=E2=80=99t need to iterate through all dest/source indexes: > it is possible to get PK using function sqlite3PrimaryKeyIndex(); > Thus, complexity reduces from O(n^2) to O(n), where n - number of indexes= . > > But, there is even better approach: in Tarantool PK always comes with 0 > ordinal > number. So, you can do space lookup by id (there is macros, which convert= s > table->tnum to space id: SQLITE_PAGENO_TO_SPACEID) and fetch real PK > index with O(1) complexity: space_index(space, 0 /* PK */); > It is not mandatory now, only if you are willing to do it. > > Also, as we have discussed, remove pls redundant uniqueness check. > > > } > > if (emptySrcTest) > > sqlite3VdbeJumpHere(v, emptySrcTest); > > 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..3b2bcc6 > > --- /dev/null > > +++ b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua > > @@ -0,0 +1,52 @@ > > +#!/usr/bin/env tarantool > > +test =3D require("sqltester") > > +test:plan(3) > > + > > +test:do_execsql_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; > > + DROP TABLE t1; > > + DROP TABLE t2; > > + ]], { > > + -- > > + > > + -- > > + }) > > do_execsql_test() returns result of last executed query. > In this case, it is =E2=80=98DROP TABLE=E2=80=99, which always (in this p= articular case) > will return nothing (i.e. table will be successfully dropped). > To catch some error, you can use do_catchsql_test() function. > After you check that insertion occurs without errors, you need > to check that all rows have been transferred from one table to another. > So, you just use do_execsql_test() to test 'SELECT * FROM t2;=E2=80=99. > After all, you may drop tables in the beginning of next test, > since it won=E2=80=99t affect result of last executed statement. > > Moreover, I would add more test cases to verify that xfer > optimization in general works: try to rearrange columns/indexes > order, add different ON CONFLICT clauses etc. > > Currently insertion from the table to another one with the same schema using SELECT works wrong. The problem lies in xfer optimization which opens cursors for all of the indexes and inserts data excessively. Now only PRIMARY KEY is used to handle insertion. Moreover analyzing xfer optimization I have noticed that unnecessary check from sqlite3 were used, accordingly excessive code cencerned with it was deleted. Closes #3307 --- Branch: https://github.com/tarantool/tarantool/tree/N_Tatunov/gh-3307-xfer-optimiza= tion-issue Issue: https://github.com/tarantool/tarantool/issues/3307 src/box/sql/insert.c | 64 ++++----- .../gh-3307-xfer-optimization-issue.test.lua | 159 +++++++++++++++++++++ 2 files changed, 184 insertions(+), 39 deletions(-) create mode 100755 test/sql-tap/gh-3307-xfer-optimization-issue.test.lua diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c index ae8dafb..ed134f4 100644 --- a/src/box/sql/insert.c +++ b/src/box/sql/insert.c @@ -1711,6 +1711,7 @@ 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 */ + struct index *src_idx, *dest_idx; /* Source and destination indices */ struct SrcList_item *pItem; /* An element of pSelect->pSrc */ int i; /* Loop counter */ int iSrc, iDest; /* Cursors from source and destination */ @@ -1718,9 +1719,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(); + struct space *space; /* Space pointer for pDest and pSrc */ if (pSelect =3D=3D NULL) return 0; /* Must be of the form INSERT INTO ... SELECT ... */ @@ -1830,9 +1831,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; @@ -1875,52 +1873,40 @@ xferOptimization(Parse * pParse, /* Parser context */ 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. - */ - 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; - } + /* 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 =3D sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0); + VdbeCoverage(v); + emptyDestTest =3D sqlite3VdbeAddOp0(v, OP_Goto); + sqlite3VdbeJumpHere(v, addr1); + + space =3D space_by_id(SQLITE_PAGENO_TO_SPACEID(pDest->tnum)); + dest_idx =3D space_index(space, 0 /* PK */); + space =3D space_by_id(SQLITE_PAGENO_TO_SPACEID(pSrc->tnum)); + src_idx =3D space_index(space, 0 /* PK */); + assert(src_idx); + assert(dest_idx); + pDestIdx =3D sqlite3PrimaryKeyIndex(pDest); + pSrcIdx =3D sqlite3PrimaryKeyIndex(pSrc); + if (xferCompatibleIndex(pDestIdx, pSrcIdx)) { assert(pSrcIdx); emit_open_cursor(pParse, iSrc, pSrcIdx->tnum); sqlite3VdbeSetP4KeyInfo(pParse, pSrcIdx); - VdbeComment((v, "%s", pSrcIdx->zName)); + VdbeComment((v, "%s", src_idx->def->name)); emit_open_cursor(pParse, iDest, pDestIdx->tnum); sqlite3VdbeSetP4KeyInfo(pParse, pDestIdx); sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR); - VdbeComment((v, "%s", pDestIdx->zName)); + 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); - if (pDestIdx->idxType =3D=3D SQLITE_IDXTYPE_PRIMARYKEY) - sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE); + sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE); sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1 + 1); VdbeCoverage(v); sqlite3VdbeJumpHere(v, addr1); 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..1049621 --- /dev/null +++ b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua @@ -0,0 +1,159 @@ +#!/usr/bin/env tarantool +test =3D 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() --=20 2.7.4 --0000000000007edb27056a35576c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Fixes were made:

=D1=87=D1=82, 19 =D0=B0=D0=BF=D1=80. 2018 =D0=B3. =D0=B2 14:22, = n.pettik <korablev@tarantool.o= rg>:
>= The bug was fixed so the data should now insert
>correctly.

Please, instead of mentioning that you just fixed bug (it is obvious),
provide brief information (without digging in details) how the problem was = solved.
(e.g. 'now only PK is used to handle insertion').

Overall, the idea is OK, but implementation could be more elegant.
You don=E2=80=99t need to iterate through all dest/source indexes:
it is possible to get PK using function sqlite3PrimaryKeyIndex();
Thus, complexity reduces from O(n^2) to O(n), where n - number of indexes.<= br>
But, there is even better approach: in Tarantool PK always comes with 0 ord= inal
number. So, you can do space lookup by id (there is macros, which converts<= br> table->tnum to space id: SQLITE_PAGENO_TO_SPACEID) and fetch real PK
index with O(1) complexity: space_index(space, 0 /* PK */);
It is not mandatory now, only if you are willing to do it.

Also, as we have discussed, remove pls redundant uniqueness check.

>=C2=A0 =C2=A0 =C2=A0 =C2=A0}
>=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);
> 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..3b2bcc6
> --- /dev/null
> +++ b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua
> @@ -0,0 +1,52 @@
> +#!/usr/bin/env tarantool
> +test =3D require("sqltester")
> +test:plan(3)
> +
> +test:do_execsql_test(
> +=C2=A0 =C2=A0 =C2=A0"xfer-optimization-1.1",
> +=C2=A0 =C2=A0 =C2=A0[[
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0CREATE TABLE t1(a INT= EGER PRIMARY KEY, b INTEGER UNIQUE);
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0INSERT INTO t1 VALUES= (1, 1), (2, 2), (3, 3);
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0CREATE TABLE t2(a INT= EGER PRIMARY KEY, b INTEGER UNIQUE);
> +=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=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=A0 =C2=A0 =C2=A0 =C2=A0-- <xfer-optimizat= ion-1.1>
> +
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-- <xfer-optimizat= ion-1.1>
> +=C2=A0 =C2=A0 =C2=A0})

do_execsql_test() returns result of last executed query.
In this case, it is =E2=80=98DROP TABLE=E2=80=99, which always (in this par= ticular case)
will return nothing (i.e. table will be successfully dropped).
To catch some error, you can use do_catchsql_test() function.
After you check that insertion occurs without errors, you need
to check that all rows have been transferred from one table to another.
So, you just use do_execsql_test() to test 'SELECT * FROM t2;=E2=80=99.=
After all, you may drop tables in the beginning of next test,
since it won=E2=80=99t affect result of last executed statement.

Moreover, I would add more test cases to verify that xfer
optimization in general works: try to rearrange columns/indexes
order, add different ON CONFLICT clauses etc.


Currently insertion from the table to = another one
with the same schema using SELECT works wrong.
<= div>The problem lies in xfer optimization which opens cursors for
all of the indexes and inserts data excessively.
Now only PRIMAR= Y KEY is used to handle insertion.
Moreover analyzing xfer optimi= zation I have noticed
that unnecessary check from sqlite3 were us= ed,
accordingly excessive code cencerned with it
was de= leted.

Closes #3307
---


=C2=A0src/= box/sql/insert.c=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|=C2=A0 64 ++++-----
=C2=A0.../gh-3307-xfer-optimization-issue.test.lua=C2=A0 =C2=A0 =C2= =A0 =C2=A0| 159 +++++++++++++++++++++
=C2=A02 files changed, 184 = insertions(+), 39 deletions(-)
=C2=A0create mode 100755 test/sql-= tap/gh-3307-xfer-optimization-issue.test.lua

diff = --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index ae8dafb= ..ed134f4 100644
--- a/src/box/sql/insert.c
+++ b/src/b= ox/sql/insert.c
@@ -1711,6 +1711,7 @@ xferOptimization(Parse * pP= arse, /* Parser context */
=C2=A0 ExprList *pEList; /* The result set of the SELECT */
= =C2=A0 Table *pSrc; /* The table in the FROM clause of SELECT */
<= div>=C2=A0 Index *pSrcIdx, *pDestIdx= ; /* Source and destination indices = */
+ struct index *src_idx= , *dest_idx; /* Source and destinati= on indices */
=C2=A0 struc= t SrcList_item *pItem; /* An element= of pSelect->pSrc */
=C2=A0 int i; /* Loop counter */
=C2=A0 int iSrc, iDest; /* Cursors from source and destination */=
@@ -1718,9 +1719,9 @@ xferOptimization(Parse * pParse, /* Parser context */
=C2=A0 int emptyDestTest =3D 0; /* Address of test for empty pDest */
=C2=A0= int emptySrcTest =3D 0; /* Address of test for empty pSrc */
=C2=A0 Vdbe *v; /* The VDBE we are building */
- int destHasUniqueIdx =3D 0; /* True if pDest has a UNIQUE index */
=C2= =A0 int regData, regTupleid; /* Registers holding data and tupleid */
=C2=A0 struct session *user_= session =3D current_session();
+ = struct space *space; /* Space= pointer for pDest and pSrc */
=C2=A0
=C2=A0 if (pSelect =3D=3D NULL)
=C2=A0 return 0; /* Must be of the form=C2=A0 INSERT INTO ... SELECT ... */
<= div>@@ -1830,9 +1831,6 @@ xferOptimization(Parse * pParse, /* Parser context */
=C2=A0 }
=C2=A0 }
=C2=A0 for (pDe= stIdx =3D pDest->pIndex; pDestIdx; pDestIdx =3D pDestIdx->pNext) {
- if (index_is_unique(pDest= Idx)) {
- destHasUniqueI= dx =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,52 +1873,40 @@ 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;
- }
+ /* The xfer optimization is unable to tes= t 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 i= nitially 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);
+
+ sp= ace =3D space_by_id(SQLITE_PAGENO_TO_SPACEID(pDest->tnum));
+<= span style=3D"white-space:pre"> dest_idx =3D space_index(space, 0 /*= PK */);
+ space =3D space= _by_id(SQLITE_PAGENO_TO_SPACEID(pSrc->tnum));
+ src_idx =3D space_index(space, 0 /* PK */);
<= div>+ assert(src_idx);
+ assert(dest_idx);
+ pDestIdx =3D sqlite3PrimaryKeyIndex(pDest);=
+ pSrcIdx =3D sqlite3Prim= aryKeyIndex(pSrc);
+ if (x= ferCompatibleIndex(pDestIdx, pSrcIdx)) {
=C2=A0 assert(pSrcIdx);
=C2=A0 emit_open_cursor(pParse, iSrc, pSrcIdx->tnum);
=C2=A0 sqlite3VdbeSetP4KeyIn= fo(pParse, pSrcIdx);
- Vd= beComment((v, "%s", pSrcIdx->zName));
+ VdbeComment((v, "%s", src_idx->d= ef->name));
=C2=A0 emi= t_open_cursor(pParse, iDest, pDestIdx->tnum);
=C2=A0 sqlite3VdbeSetP4KeyInfo(pParse, pDestIdx);
=C2=A0 sqlite3VdbeChangeP5= (v, OPFLAG_BULKCSR);
- Vd= beComment((v, "%s", pDestIdx->zName));
+ VdbeComment((v, "%s", dest_idx->= def->name));
=C2=A0 ad= dr1 =3D sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);
=C2=A0 VdbeCoverage(v);
=C2=A0 sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regDa= ta);
=C2=A0 sqlite3VdbeAd= dOp2(v, OP_IdxInsert, iDest, regData);
- if (pDestIdx->idxType =3D=3D SQLITE_IDXTYPE_PRIMARYKEY)<= /div>
- sqlite3VdbeChangeP5(v,= OPFLAG_NCHANGE);
+ sqlit= e3VdbeChangeP5(v, OPFLAG_NCHANGE);
=C2=A0 sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1 + 1);
= =C2=A0 VdbeCoverage(v);
= =C2=A0 sqlite3VdbeJumpHere(v, addr1= );
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..1049621
--- /dev/null
+++ b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua
<= div>@@ -0,0 +1,159 @@
+#!/usr/bin/env tarantool
+test = =3D require("sqltester")
+test:plan(12)
+
+test:do_catchsql_test(
+ = "xfer-optimization-1.1",
+ [[
+ CR= EATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER UNIQUE);
+ INSERT INTO t1 VALUES (1, 1), (2, 2), (3, = 3);
+ CREATE TABLE t2(a I= NTEGER PRIMARY KEY, b INTEGER UNIQUE);
+ INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ = -- <xfer-optimization-1.1>
+ 0
+ -- = <xfer-optimization-1.1>
+ <= /span>})
+
+test:do_execsql_test(
+ "xfer-oprimization-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;
+ DROP TABLE t2;
+ CREATE TABLE t1(id INTEGER PRIMARY KEY, b INTEGER);
+<= span style=3D"white-space:pre"> CREATE TABLE t2(id INTEGER PRIMARY = KEY, b INTEGER);
+ CREATE= INDEX i1 ON t1(b);
+ CRE= ATE INDEX i2 ON t2(b);
+ = INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
+ INSERT INTO t2 SELECT * FROM t1;
+ ]], {
+ -- <xfer-optimization-1.3>
+ 0
+ -- <xfer-optimization-1.3>
+ })
+
<= div>+test:do_execsql_test(
+ "xfer-oprimization-1.4",
+ [[
+ SELECT= * FROM t2;
+ ]], {
<= div>+ -- <xfer-optimization-1.4&= gt;
+ 1, 1, 2, 2, 3, 3
+ -- <xfer-optimization-= 1.4>
+ })
+
+test:do_catchsql_test(
+ "xfer-optimization-1.5",
+ [[
+ = DROP TABLE t1;
+ DROP TAB= LE 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 IN= TEGER PRIMARY KEY, b INTEGER);
+ = INSERT INTO t2 SELECT * FROM t1;
+
+ ]], {
+ -- <xfer-optimization-1.5>
+ 1, "table T2 has 2 columns but 3 values were sup= plied"
+ -- <xfer= -optimization-1.5>
+ })=
+
+test:do_ex= ecsql_test(
+ "xfer-o= primization-1.6",
+ [= [
+ SELECT * FROM t2;
+ ]], {
+ -- <xfer-oprimization-1.6>
+=
+ -- <xfer-oprimizati= on-1.6>
+ })
= +
+test:do_catchsql_test= (
+ "xfer-optimizatio= n-1.7",
+ [[
+ 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, 3);
+ <= /span>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",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ -- <= ;xfer-oprimization-1.6>
+ 1, 1, 2, 2, 3, 3
+ --= <xfer-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= 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;
+ ]], {
+ -- <xfer-oprimization-1.10>
+
+ -- <xfe= r-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 INTE= GER PRIMARY KEY, a INTEGER);
+ <= /span>INSERT INTO t2 SELECT * FROM t1;
+
+ ]], {
+ -- <xfer-optimization-1.11>
+ 0
+ -- <xfer-optimization-1.11>
+ })
+
+test:do_execsql_test(
+ "xfer-oprimization-1.12",
+ [[
+ SELECT= * FROM t2;
+ ]], {
<= div>+ -- <xfer-oprimization-1.12= >
+ 1, 1, 2, 2, 3, 2
+ -- <xfer-oprimization= -1.12>
+ })
+=
+test:finish_test()
--=C2=A0
2.7.4=C2=A0
--0000000000007edb27056a35576c--