From: Hollow111 <hollow653@gmail.com>
To: tarantool-patches@freelists.org
Cc: korablev@tarantool.org
Subject: [tarantool-patches] Re: [PATCH] sql: xfer optimization issue
Date: Wed, 18 Apr 2018 16:33:30 +0000 [thread overview]
Message-ID: <CAEi+_aqB1=iiR91r700CK5DBWahYqZzWcZnrUqzc1wR8dN_guQ@mail.gmail.com> (raw)
In-Reply-To: <1524065531-32467-1-git-send-email-hollow653@gmail.com>
[-- Attachment #1: Type: text/plain, Size: 9487 bytes --]
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@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
>
>
[-- Attachment #2: Type: text/html, Size: 15258 bytes --]
next prev parent reply other threads:[~2018-04-18 16:33 UTC|newest]
Thread overview: 38+ messages / expand[flat|nested] mbox.gz Atom feed top
2018-04-18 15:32 [tarantool-patches] " N.Tatunov
2018-04-18 16:33 ` Hollow111 [this message]
2018-04-19 11:22 ` [tarantool-patches] " n.pettik
2018-04-19 15:36 ` Hollow111
2018-04-20 1:02 ` n.pettik
2018-04-20 15:09 ` Hollow111
2018-04-20 16:09 ` n.pettik
2018-04-20 17:59 ` Hollow111
2018-04-23 23:40 ` n.pettik
2018-04-27 15:45 ` Hollow111
2018-05-03 22:57 ` n.pettik
2018-05-04 12:54 ` Hollow111
2018-06-28 10:18 ` Alexander Turenko
2018-07-09 15:50 ` Alexander Turenko
2018-07-16 12:54 ` Nikita Tatunov
2018-07-16 13:06 ` n.pettik
2018-07-16 13:20 ` Nikita Tatunov
2018-07-16 18:37 ` Nikita Tatunov
2018-07-16 19:12 ` n.pettik
2018-07-16 21:27 ` Nikita Tatunov
2018-07-18 15:13 ` n.pettik
2018-07-18 20:18 ` Nikita Tatunov
2018-07-19 0:20 ` n.pettik
2018-07-19 17:26 ` Nikita Tatunov
2018-07-20 3:20 ` n.pettik
2018-07-20 11:56 ` Nikita Tatunov
2018-07-20 16:43 ` n.pettik
2018-07-20 16:58 ` Nikita Tatunov
2018-07-29 1:12 ` Alexander Turenko
2018-07-29 11:23 ` n.pettik
2018-07-29 15:16 ` Alexander Turenko
2018-07-30 18:33 ` Nikita Tatunov
2018-07-30 22:17 ` Alexander Turenko
2018-07-31 11:48 ` Nikita Tatunov
2018-07-31 13:29 ` Alexander Turenko
2018-07-31 17:04 ` Nikita Tatunov
2018-07-31 17:44 ` Alexander Turenko
2018-08-21 16:43 ` Kirill Yukhin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to='CAEi+_aqB1=iiR91r700CK5DBWahYqZzWcZnrUqzc1wR8dN_guQ@mail.gmail.com' \
--to=hollow653@gmail.com \
--cc=korablev@tarantool.org \
--cc=tarantool-patches@freelists.org \
--subject='[tarantool-patches] Re: [PATCH] sql: xfer optimization issue' \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox