<div dir="ltr">Hello. I have notices some mistakes.<br>Newer diff:<br><br><div>diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c</div><div>index ae8dafb..b27fc23 100644</div><div>--- a/src/box/sql/insert.c</div><div>+++ b/src/box/sql/insert.c</div><div>@@ -1908,24 +1908,25 @@ xferOptimization(Parse * pParse,<span style="white-space:pre">      </span>/* Parser context */</div><div> <span style="white-space:pre">                                </span>break;</div><div> <span style="white-space:pre">              </span>}</div><div> <span style="white-space:pre">           </span>assert(pSrcIdx);</div><div>-<span style="white-space:pre">             </span>emit_open_cursor(pParse, iSrc, pSrcIdx->tnum);</div><div>-<span style="white-space:pre">            </span>sqlite3VdbeSetP4KeyInfo(pParse, pSrcIdx);</div><div>-<span style="white-space:pre">            </span>VdbeComment((v, "%s", pSrcIdx->zName));</div><div>-<span style="white-space:pre">         </span>emit_open_cursor(pParse, iDest, pDestIdx->tnum);</div><div>-<span style="white-space:pre">          </span>sqlite3VdbeSetP4KeyInfo(pParse, pDestIdx);</div><div>-<span style="white-space:pre">           </span>sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR);</div><div>-<span style="white-space:pre">              </span>VdbeComment((v, "%s", pDestIdx->zName));</div><div>-<span style="white-space:pre">                </span>addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);</div><div>-<span style="white-space:pre">            </span>VdbeCoverage(v);</div><div>-<span style="white-space:pre">             </span>sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);</div><div>-<span style="white-space:pre">             </span>sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData);</div><div>-<span style="white-space:pre">          </span>if (pDestIdx->idxType == SQLITE_IDXTYPE_PRIMARYKEY)</div><div>+<span style="white-space:pre">               </span>if (pDestIdx->idxType == SQLITE_IDXTYPE_PRIMARYKEY) {</div><div>+<span style="white-space:pre">                     </span>emit_open_cursor(pParse, iSrc, pSrcIdx->tnum);</div><div>+<span style="white-space:pre">                    </span>sqlite3VdbeSetP4KeyInfo(pParse, pSrcIdx);</div><div>+<span style="white-space:pre">                    </span>VdbeComment((v, "%s", pSrcIdx->zName));</div><div>+<span style="white-space:pre">                 </span>emit_open_cursor(pParse, iDest, pDestIdx->tnum);</div><div>+<span style="white-space:pre">                  </span>sqlite3VdbeSetP4KeyInfo(pParse, pDestIdx);</div><div>+<span style="white-space:pre">                   </span>sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR);</div><div>+<span style="white-space:pre">                      </span>VdbeComment((v, "%s", pDestIdx->zName));</div><div>+<span style="white-space:pre">                        </span>addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);</div><div>+<span style="white-space:pre">                    </span>VdbeCoverage(v);</div><div>+<span style="white-space:pre">                     </span>sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);</div><div>+<span style="white-space:pre">                     </span>sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData);</div><div> <span style="white-space:pre">                 </span>sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE);</div><div>-<span style="white-space:pre">              </span>sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1 + 1);</div><div>-<span style="white-space:pre">              </span>VdbeCoverage(v);</div><div>-<span style="white-space:pre">             </span>sqlite3VdbeJumpHere(v, addr1);</div><div>-<span style="white-space:pre">               </span>sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);</div><div>-<span style="white-space:pre">             </span>sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);</div><div>+<span style="white-space:pre">                    </span>sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1 + 1);</div><div>+<span style="white-space:pre">                      </span>VdbeCoverage(v);</div><div>+<span style="white-space:pre">                     </span>sqlite3VdbeJumpHere(v, addr1);</div><div>+<span style="white-space:pre">                       </span>sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);</div><div>+<span style="white-space:pre">                     </span>sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);</div><div>+<span style="white-space:pre">            </span>}</div><div> <span style="white-space:pre">   </span>}</div><div> <span style="white-space:pre">   </span>if (emptySrcTest)</div><div> <span style="white-space:pre">           </span>sqlite3VdbeJumpHere(v, emptySrcTest);</div><div>diff --git a/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua</div><div>new file mode 100755</div><div>index 0000000..3b2bcc6</div><div>--- /dev/null</div><div>+++ b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua</div><div>@@ -0,0 +1,52 @@</div><div>+#!/usr/bin/env tarantool</div><div>+test = require("sqltester")</div><div>+test:plan(3)</div><div>+</div><div>+test:do_execsql_test(</div><div>+<span style="white-space:pre">    </span>"xfer-optimization-1.1",</div><div>+<span style="white-space:pre">   </span>[[</div><div>+<span style="white-space:pre">           </span>CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER UNIQUE);</div><div>+<span style="white-space:pre">            </span>INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);</div><div>+<span style="white-space:pre">                </span>CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER UNIQUE);</div><div>+<span style="white-space:pre">            </span>INSERT INTO t2 SELECT * FROM t1;</div><div>+<span style="white-space:pre">             </span>DROP TABLE t1;</div><div>+<span style="white-space:pre">               </span>DROP TABLE t2;</div><div>+<span style="white-space:pre">       </span>]], {</div><div>+<span style="white-space:pre">                </span>-- <xfer-optimization-1.1></div><div>+</div><div>+<span style="white-space:pre">             </span>-- <xfer-optimization-1.1></div><div>+<span style="white-space:pre">     </span>})</div><div>+</div><div>+test:do_execsql_test(</div><div>+<span style="white-space:pre">      </span>"xfer-optimization-1.2",</div><div>+<span style="white-space:pre">   </span>[[</div><div>+<span style="white-space:pre">           </span>CREATE TABLE t1(id INTEGER PRIMARY KEY, b INTEGER);</div><div>+<span style="white-space:pre">          </span>CREATE TABLE t2(id INTEGER PRIMARY KEY, b INTEGER);</div><div>+<span style="white-space:pre">          </span>CREATE INDEX i1 ON t1(b);</div><div>+<span style="white-space:pre">            </span>CREATE INDEX i2 ON t2(b);</div><div>+<span style="white-space:pre">            </span>INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);</div><div>+<span style="white-space:pre">                </span>INSERT INTO t2 SELECT * FROM t1;</div><div>+<span style="white-space:pre">             </span>DROP TABLE t1;</div><div>+<span style="white-space:pre">               </span>DROP TABLE t2;</div><div>+<span style="white-space:pre">       </span>]], {</div><div>+<span style="white-space:pre">                </span>-- <xfer-optimization-1.2></div><div>+</div><div>+<span style="white-space:pre">             </span>-- <xfer-optimization-1.2></div><div>+<span style="white-space:pre">     </span>})</div><div>+</div><div>+test:do_execsql_test(</div><div>+<span style="white-space:pre">      </span>"xfer-optimization-1.3",</div><div>+<span style="white-space:pre">   </span>[[</div><div>+<span style="white-space:pre">           </span>CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);</div><div>+<span style="white-space:pre">           </span>INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);</div><div>+<span style="white-space:pre">                </span>CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER);</div><div>+<span style="white-space:pre">           </span>INSERT INTO t2 SELECT * FROM t1;</div><div>+<span style="white-space:pre">             </span>DROP TABLE t1;</div><div>+<span style="white-space:pre">               </span>DROP TABLE t2;</div><div>+<span style="white-space:pre">       </span>]], {</div><div>+<span style="white-space:pre">                </span>-- <xfer-optimization-1.3></div><div>+</div><div>+<span style="white-space:pre">             </span>-- <xfer-optimization-1.3></div><div>+<span style="white-space:pre">     </span>})</div><div>+</div><div>+test:finish_test()</div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr">ср, 18 апр. 2018 г. в 18:32, N.Tatunov <<a href="mailto:hollow653@gmail.com">hollow653@gmail.com</a>>:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Currently insertion from the table to another one<br>
with the same schema using SELECT works wrong.<br>
The problem lies in xfer optimization which opens cursors for<br>
all of the indexes and inserts data excessively.<br>
The bug was fixed so the data should now insert<br>
correctly.<br>
<br>
Closes #3307<br>
---<br>
<br>
Branch: <a href="https://github.com/tarantool/tarantool/tree/N_Tatunov/gh-3307-xfer-optimization-issue" rel="noreferrer" target="_blank">https://github.com/tarantool/tarantool/tree/N_Tatunov/gh-3307-xfer-optimization-issue</a><br>
Issue: <a href="https://github.com/tarantool/tarantool/issues/3307" rel="noreferrer" target="_blank">https://github.com/tarantool/tarantool/issues/3307</a><br>
<br>
 src/box/sql/insert.c                               | 35 ++++++++--------<br>
 .../sql-tap/gh-3307-xfer-optimization-issue.result |  0<br>
 .../gh-3307-xfer-optimization-issue.test.lua       | 49 ++++++++++++++++++++++<br>
 3 files changed, 67 insertions(+), 17 deletions(-)<br>
 create mode 100644 test/sql-tap/gh-3307-xfer-optimization-issue.result<br>
 create mode 100644 test/sql-tap/gh-3307-xfer-optimization-issue.test.lua<br>
<br>
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c<br>
index ae8dafb..b27fc23 100644<br>
--- a/src/box/sql/insert.c<br>
+++ b/src/box/sql/insert.c<br>
@@ -1908,24 +1908,25 @@ xferOptimization(Parse * pParse,        /* Parser context */<br>
                                break;<br>
                }<br>
                assert(pSrcIdx);<br>
-               emit_open_cursor(pParse, iSrc, pSrcIdx->tnum);<br>
-               sqlite3VdbeSetP4KeyInfo(pParse, pSrcIdx);<br>
-               VdbeComment((v, "%s", pSrcIdx->zName));<br>
-               emit_open_cursor(pParse, iDest, pDestIdx->tnum);<br>
-               sqlite3VdbeSetP4KeyInfo(pParse, pDestIdx);<br>
-               sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR);<br>
-               VdbeComment((v, "%s", pDestIdx->zName));<br>
-               addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);<br>
-               VdbeCoverage(v);<br>
-               sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);<br>
-               sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData);<br>
-               if (pDestIdx->idxType == SQLITE_IDXTYPE_PRIMARYKEY)<br>
+               if (pDestIdx->idxType == SQLITE_IDXTYPE_PRIMARYKEY) {<br>
+                       emit_open_cursor(pParse, iSrc, pSrcIdx->tnum);<br>
+                       sqlite3VdbeSetP4KeyInfo(pParse, pSrcIdx);<br>
+                       VdbeComment((v, "%s", pSrcIdx->zName));<br>
+                       emit_open_cursor(pParse, iDest, pDestIdx->tnum);<br>
+                       sqlite3VdbeSetP4KeyInfo(pParse, pDestIdx);<br>
+                       sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR);<br>
+                       VdbeComment((v, "%s", pDestIdx->zName));<br>
+                       addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);<br>
+                       VdbeCoverage(v);<br>
+                       sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);<br>
+                       sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData);<br>
                        sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE);<br>
-               sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1 + 1);<br>
-               VdbeCoverage(v);<br>
-               sqlite3VdbeJumpHere(v, addr1);<br>
-               sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);<br>
-               sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);<br>
+                       sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1 + 1);<br>
+                       VdbeCoverage(v);<br>
+                       sqlite3VdbeJumpHere(v, addr1);<br>
+                       sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);<br>
+                       sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);<br>
+               }<br>
        }<br>
        if (emptySrcTest)<br>
                sqlite3VdbeJumpHere(v, emptySrcTest);<br>
diff --git a/test/sql-tap/gh-3307-xfer-optimization-issue.result b/test/sql-tap/gh-3307-xfer-optimization-issue.result<br>
new file mode 100644<br>
index 0000000..e69de29<br>
diff --git a/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua<br>
new file mode 100644<br>
index 0000000..e45235e<br>
--- /dev/null<br>
+++ b/test/sql-tap/gh-3307-xfer-optimization-issue.test.lua<br>
@@ -0,0 +1,49 @@<br>
+#!/usr/bin/env tarantool<br>
+test = require("sqltester")<br>
+test:plan(1)<br>
+<br>
+-- gh-3307 - sql: INSERT with SELECT is not working in some cases.<br>
+<br>
+test:do_exesql_test(<br>
+       "xfer-optimization-1.1",<br>
+       [[<br>
+               CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER UNIQUE);<br>
+               INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);<br>
+               CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER UNIQUE);<br>
+               INSERT INTO t2 SELECT * FROM t1;<br>
+               DROP TABLE t1;<br>
+               DROP TABLE t2;<br>
+       ]], {<br>
+               -- <xfer-optimization-1.1><br>
+               -- <xfer-optimization-1.1><br>
+       })<br>
+<br>
+test:do_exesql_test(<br>
+       "xfer-optimization-1.2",<br>
+       [[<br>
+               CREATE TABLE t1(id INTEGER PRIMARY KEY, b INTEGER);<br>
+               CREATE TABLE t2(id INTEGER PRIMARY KEY, b INTEGER);<br>
+               CREATE INDEX i1 ON t1(b);<br>
+               CREATE INDEX i2 ON t2(b);<br>
+               INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);<br>
+               INSERT INTO t2 SELECT * FROM t1;<br>
+               DROP TABLE t1;<br>
+               DROP TABLE t2;<br>
+       ]], {<br>
+               -- <xfer-optimization-1.2><br>
+               -- <xfer-optimization-1.2><br>
+       })<br>
+               <br>
+test:do_exesql_test(<br>
+       "xfer-optimization-1.1",<br>
+       [[<br>
+               CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);<br>
+               INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);<br>
+               CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER);<br>
+               INSERT INTO t2 SELECT * FROM t1;<br>
+               DROP TABLE t1;<br>
+               DROP TABLE t2;<br>
+       ]], {<br>
+               -- <xfer-optimization-1.1><br>
+               -- <xfer-optimization-1.1><br>
+       })<br>
-- <br>
2.7.4<br>
<br>
</blockquote></div>