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 8C44F2A2A6 for ; Wed, 18 Apr 2018 11:32:33 -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 wD8yDiKVk2lu for ; Wed, 18 Apr 2018 11:32:33 -0400 (EDT) Received: from mail-lf0-f68.google.com (mail-lf0-f68.google.com [209.85.215.68]) (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 2A09C2A275 for ; Wed, 18 Apr 2018 11:32:32 -0400 (EDT) Received: by mail-lf0-f68.google.com with SMTP id m202-v6so3288525lfe.8 for ; Wed, 18 Apr 2018 08:32:32 -0700 (PDT) From: "N.Tatunov" Subject: [tarantool-patches] [PATCH] sql: xfer optimization issue Date: Wed, 18 Apr 2018 18:32:11 +0300 Message-Id: <1524065531-32467-1-git-send-email-hollow653@gmail.com> 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: tarantool-patches@freelists.org Cc: korablev@tarantool.org, "N.Tatunov" 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; + ]], { + -- + -- + }) + +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; + ]], { + -- + -- + }) + +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; + ]], { + -- + -- + }) -- 2.7.4