[tarantool-patches] Re: [PATCH] sql: xfer optimization issue
Hollow111
hollow653 at gmail.com
Wed Apr 18 19:33:30 MSK 2018
Hello. I have notices some mistakes.
Newer diff:
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.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 = 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;
+ ]], {
+ -- <xfer-optimization-1.1>
+
+ -- <xfer-optimization-1.1>
+ })
+
+test:do_execsql_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_execsql_test(
+ "xfer-optimization-1.3",
+ [[
+ 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.3>
+
+ -- <xfer-optimization-1.3>
+ })
+
+test:finish_test()
ср, 18 апр. 2018 г. в 18:32, N.Tatunov <hollow653 at gmail.com>:
> 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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.tarantool.org/pipermail/tarantool-patches/attachments/20180418/4f75d005/attachment.html>
More information about the Tarantool-patches
mailing list