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.
The bug was fixed so the data should now insert
correctly.

Closes #3307
---

Branch: https://github.com/tarantool/tarantool/tree/N_Tatunov/gh-3307-xfer-optimization-issue
Issue: https://github.com/tarantool/tarantool/issues/3307

 src/box/sql/insert.c                               | 35 ++++++++--------
 .../sql-tap/gh-3307-xfer-optimization-issue.result |  0
 .../gh-3307-xfer-optimization-issue.test.lua       | 49 ++++++++++++++++++++++
 3 files changed, 67 insertions(+), 17 deletions(-)
 create mode 100644 test/sql-tap/gh-3307-xfer-optimization-issue.result
 create mode 100644 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..b27fc23 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -1908,24 +1908,25 @@ xferOptimization(Parse * pParse,        /* Parser context */
                                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)
+               if (pDestIdx->idxType == SQLITE_IDXTYPE_PRIMARYKEY) {
+                       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);
                        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);
+                       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);
diff --git a/test/sql-tap/gh-3307-xfer-optimization-issue.result b/test/sql-tap/gh-3307-xfer-optimization-issue.result
new file mode 100644
index 0000000..e69de29
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 100644
index 0000000..e45235e
--- /dev/null
+++ b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua
@@ -0,0 +1,49 @@
+#!/usr/bin/env tarantool
+test = require("sqltester")
+test:plan(1)
+
+-- gh-3307 - sql: INSERT with SELECT is not working in some cases.
+
+test:do_exesql_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;
+       ]], {
+               -- <xfer-optimization-1.1>
+               -- <xfer-optimization-1.1>
+       })
+
+test:do_exesql_test(
+       "xfer-optimization-1.2",
+       [[
+               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;
+               DROP TABLE t1;
+               DROP TABLE t2;
+       ]], {
+               -- <xfer-optimization-1.2>
+               -- <xfer-optimization-1.2>
+       })
+               
+test:do_exesql_test(
+       "xfer-optimization-1.1",
+       [[
+               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;
+               DROP TABLE t1;
+               DROP TABLE t2;
+       ]], {
+               -- <xfer-optimization-1.1>
+               -- <xfer-optimization-1.1>
+       })
--
2.7.4