[patches] [sql 7/9] sql: replaced all usages of OP_OpenEphemeral
Nikita Pettik
korablev at tarantool.org
Tue Jan 23 23:19:18 MSK 2018
- Autoindexes are temporary disabled since they rely on ephemeral tables
with ROWID. WITHOUT ROWID (in SQLite meaning) tables are known to be unable
to support autoindex optimization.
- ORDER BY optimization is also temporary disabled: transient ephemeral table
contains all columns from SELECT statement plus columns from
ORDER BY statement without excluding their intersection.
- Replaced all generated occurrences of OP_OpenEphemeral including those
which used ROWID with OP_OpenTEphemeral opcode.
- Fixed tests which rely on mnemonic of opcode creating epehemral table.
Part of #2680
Signed-off-by: Nikita Pettik <korablev at tarantool.org>
---
src/box/sql/CMakeLists.txt | 1 +
src/box/sql/opcodes.c | 4 +-
src/box/sql/opcodes.h | 4 +-
src/box/sql/select.c | 101 ++++++++++++++++++++++++++++-------------
src/box/sql/where.c | 7 ++-
test/sql-tap/distinct.test.lua | 2 +-
6 files changed, 79 insertions(+), 40 deletions(-)
diff --git a/src/box/sql/CMakeLists.txt b/src/box/sql/CMakeLists.txt
index be2f5ce5e..6dae48af7 100644
--- a/src/box/sql/CMakeLists.txt
+++ b/src/box/sql/CMakeLists.txt
@@ -19,6 +19,7 @@ add_definitions(-DTHREADSAFE=0)
add_definitions(-DSQLITE_DEFAULT_FOREIGN_KEYS=1)
add_definitions(-DSQLITE_ENABLE_SELECTTRACE=1)
add_definitions(-DSQLITE_ENABLE_WHERETRACE=1)
+add_definitions(-DSQLITE_OMIT_AUTOMATIC_INDEX)
set(TEST_DEFINITIONS
SQLITE_DEBUG=1
diff --git a/src/box/sql/opcodes.c b/src/box/sql/opcodes.c
index b91784db3..e021a986b 100644
--- a/src/box/sql/opcodes.c
+++ b/src/box/sql/opcodes.c
@@ -126,8 +126,8 @@ const char *sqlite3OpcodeName(int i){
/* 112 */ "Sequence" OpHelp("r[P2]=cursor[P1].ctr++"),
/* 113 */ "NextId" OpHelp("r[P3]=get_max(space_index[P1]{Column[P2]})"),
/* 114 */ "NextIdEphemeral" OpHelp("r[P3]=get_max(space_index[P1]{Column[P2]})"),
- /* 115 */ "FCopy" OpHelp("reg[P2 at cur_frame]= reg[P1 at root_frame(OPFLAG_SAME_FRAME)]"),
- /* 116 */ "Real" OpHelp("r[P2]=P4"),
+ /* 115 */ "Real" OpHelp("r[P2]=P4"),
+ /* 116 */ "FCopy" OpHelp("reg[P2 at cur_frame]= reg[P1 at root_frame(OPFLAG_SAME_FRAME)]"),
/* 117 */ "NewRowid" OpHelp("r[P2]=rowid"),
/* 118 */ "Insert" OpHelp("intkey=r[P3] data=r[P2]"),
/* 119 */ "InsertInt" OpHelp("intkey=P3 data=r[P2]"),
diff --git a/src/box/sql/opcodes.h b/src/box/sql/opcodes.h
index 7c331c788..a0883b097 100644
--- a/src/box/sql/opcodes.h
+++ b/src/box/sql/opcodes.h
@@ -115,8 +115,8 @@
#define OP_Sequence 112 /* synopsis: r[P2]=cursor[P1].ctr++ */
#define OP_NextId 113 /* synopsis: r[P3]=get_max(space_index[P1]{Column[P2]}) */
#define OP_NextIdEphemeral 114 /* synopsis: r[P3]=get_max(space_index[P1]{Column[P2]}) */
-#define OP_FCopy 115 /* synopsis: reg[P2 at cur_frame]= reg[P1 at root_frame(OPFLAG_SAME_FRAME)] */
-#define OP_Real 116 /* same as TK_FLOAT, synopsis: r[P2]=P4 */
+#define OP_Real 115 /* same as TK_FLOAT, synopsis: r[P2]=P4 */
+#define OP_FCopy 116 /* synopsis: reg[P2 at cur_frame]= reg[P1 at root_frame(OPFLAG_SAME_FRAME)] */
#define OP_NewRowid 117 /* synopsis: r[P2]=rowid */
#define OP_Insert 118 /* synopsis: intkey=r[P3] data=r[P2] */
#define OP_InsertInt 119 /* synopsis: intkey=P3 data=r[P2] */
diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 5c47ab020..c3c9b6b3e 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -847,7 +847,15 @@ selectInnerLoop(Parse * pParse, /* The parser context */
* saving space and CPU cycles.
*/
ecelFlags |= (SQLITE_ECEL_OMITREF | SQLITE_ECEL_REF);
- for (i = pSort->nOBSat; i < pSort->pOrderBy->nExpr; i++) {
+ /* This optimization is temporary disabled. It seems
+ * that it was possible to create table with n columns and
+ * insert tuple with m columns, where m < n. Contrary, Tarantool
+ * doesn't allow to alter the number of fields in tuple
+ * to be inserted. This may be uncommented by delaying the
+ * creation of table until insertion of first tuple,
+ * so that the number of fields in tuple can be precisely calculated.
+ */
+ /*for (i = pSort->nOBSat; i < pSort->pOrderBy->nExpr; i++) {
int j;
if ((j =
pSort->pOrderBy->a[i].u.x.iOrderByCol) >
@@ -855,7 +863,7 @@ selectInnerLoop(Parse * pParse, /* The parser context */
pEList->a[j - 1].u.x.iOrderByCol =
(u16) (i + 1 - pSort->nOBSat);
}
- }
+ }*/
regOrig = 0;
assert(eDest == SRT_Set || eDest == SRT_Mem
|| eDest == SRT_Coroutine
@@ -990,7 +998,7 @@ selectInnerLoop(Parse * pParse, /* The parser context */
* current row to the index and proceed with writing it to the
* output table as well.
*/
- int addr = sqlite3VdbeCurrentAddr(v) + 4;
+ int addr = sqlite3VdbeCurrentAddr(v) + 6;
sqlite3VdbeAddOp4Int(v, OP_Found, iParm + 1,
addr, r1, 0);
VdbeCoverage(v);
@@ -1004,11 +1012,22 @@ selectInnerLoop(Parse * pParse, /* The parser context */
r1 + nPrefixReg, regResult, 1,
nPrefixReg);
} else {
- int r2 = sqlite3GetTempReg(pParse);
- sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, r2);
- sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, r2);
- sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
- sqlite3ReleaseTempReg(pParse, r2);
+ int regRec = sqlite3GetTempReg(pParse);
+ /* Last column is required for ID. */
+ int regCopy = sqlite3GetTempRange(pParse, nResultCol + 1);
+ sqlite3VdbeAddOp3(v, OP_NextIdEphemeral, iParm,
+ 0, regCopy + nResultCol);
+ /* Positioning ID column to be last in inserted tuple.
+ * NextId -> regCopy + n + 1
+ * Copy [regResult, regResult + n] -> [regCopy, regCopy + n]
+ * MakeRecord -> [regCopy, regCopy + n + 1] -> regRec
+ * IdxInsert -> regRec
+ */
+ sqlite3VdbeAddOp3(v, OP_Copy, regResult, regCopy, nResultCol - 1);
+ sqlite3VdbeAddOp3(v, OP_MakeRecord, regCopy, nResultCol + 1, regRec);
+ sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, regRec);
+ sqlite3ReleaseTempReg(pParse, regRec);
+ sqlite3ReleaseTempRange(pParse, regCopy, nResultCol + 1);
}
sqlite3ReleaseTempRange(pParse, r1, nPrefixReg + 1);
break;
@@ -1183,6 +1202,7 @@ sqlite3KeyInfoAlloc(sqlite3 * db, int N, int X)
p->nXField = (u16) X;
p->db = db;
p->nRef = 1;
+ p->aColl[0] = NULL;
memset(&p[1], 0, nExtra);
} else {
sqlite3OomFault(db);
@@ -1468,10 +1488,12 @@ generateSortTail(Parse * pParse, /* Parsing context */
switch (eDest) {
case SRT_Table:
case SRT_EphemTab: {
- sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, regRowid);
- sqlite3VdbeAddOp3(v, OP_Insert, iParm, regRow,
- regRowid);
- sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
+ int regCopy = sqlite3GetTempRange(pParse, nColumn);
+ sqlite3VdbeAddOp3(v, OP_NextIdEphemeral, iParm, 0, regRowid);
+ sqlite3VdbeAddOp3(v, OP_Copy, regRow, regCopy, nSortData - 1);
+ sqlite3VdbeAddOp3(v, OP_MakeRecord, regCopy, nColumn + 1, regRow);
+ sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, regRow);
+ sqlite3ReleaseTempReg(pParse, regCopy);
break;
}
#ifndef SQLITE_OMIT_SUBQUERY
@@ -2337,15 +2359,19 @@ generateWithRecursiveQuery(Parse * pParse, /* Parsing context */
sqlite3VdbeAddOp4(v, OP_OpenTEphemeral, iQueue,
pOrderBy->nExpr + 2, 0, (char *)pKeyInfo,
P4_KEYINFO);
+ VdbeComment((v, "Orderby table"));
destQueue.pOrderBy = pOrderBy;
} else {
- sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iQueue, nCol);
+ KeyInfo *pKeyInfo = sqlite3KeyInfoAlloc(pParse->db, nCol + 1, 0);
+ sqlite3VdbeAddOp4(v, OP_OpenTEphemeral, iQueue, nCol + 1, 0,
+ (char*)pKeyInfo, P4_KEYINFO);
+ VdbeComment((v, "Queue table"));
}
- VdbeComment((v, "Queue table"));
if (iDistinct) {
p->addrOpenEphm[0] =
- sqlite3VdbeAddOp2(v, OP_OpenTEphemeral, iDistinct, 0);
+ sqlite3VdbeAddOp2(v, OP_OpenTEphemeral, iDistinct, 1);
p->selFlags |= SF_UsesEphemeral;
+ VdbeComment((v, "Distinct table"));
}
/* Detach the ORDER BY clause from the compound SELECT */
@@ -2540,8 +2566,11 @@ multiSelect(Parse * pParse, /* Parsing context */
*/
if (dest.eDest == SRT_EphemTab) {
assert(p->pEList);
- sqlite3VdbeAddOp2(v, OP_OpenEphemeral, dest.iSDParm,
- p->pEList->nExpr);
+ int nCols = p->pEList->nExpr;
+ KeyInfo *pKeyInfo = sqlite3KeyInfoAlloc(pParse->db, nCols + 1, 0);
+ sqlite3VdbeAddOp4(v, OP_OpenTEphemeral, dest.iSDParm, nCols + 1,
+ 0, (char*)pKeyInfo, P4_KEYINFO);
+ VdbeComment((v, "Destination temp"));
dest.eDest = SRT_Table;
}
@@ -5237,8 +5266,8 @@ resetAccumulator(Parse * pParse, AggInfo * pAggInfo)
KeyInfo *pKeyInfo =
keyInfoFromExprList(pParse, pE->x.pList, 0,
0);
- sqlite3VdbeAddOp4(v, OP_OpenEphemeral,
- pFunc->iDistinct, 0, 0,
+ sqlite3VdbeAddOp4(v, OP_OpenTEphemeral,
+ pFunc->iDistinct, 1, 0,
(char *)pKeyInfo, P4_KEYINFO);
}
}
@@ -5730,14 +5759,19 @@ sqlite3Select(Parse * pParse, /* The parser context */
if (sSort.pOrderBy) {
KeyInfo *pKeyInfo;
pKeyInfo =
- keyInfoFromExprList(pParse, sSort.pOrderBy, 0,
- pEList->nExpr);
+ keyInfoFromExprList(pParse, sSort.pOrderBy, 0, pEList->nExpr);
sSort.iECursor = pParse->nTab++;
+ /* Number of columns in transient table equals to number of columns in
+ * SELECT statement plus number of columns in ORDER BY statement
+ * and plus one column for ID.
+ */
+ int nCols = pEList->nExpr + sSort.pOrderBy->nExpr + 1;
sSort.addrSortIndex =
- sqlite3VdbeAddOp4(v, OP_OpenEphemeral,
+ sqlite3VdbeAddOp4(v, OP_OpenTEphemeral,
sSort.iECursor,
- sSort.pOrderBy->nExpr + 1 + pEList->nExpr,
+ nCols,
0, (char *)pKeyInfo, P4_KEYINFO);
+ VdbeComment((v, "Sort table"));
} else {
sSort.addrSortIndex = -1;
}
@@ -5745,8 +5779,12 @@ sqlite3Select(Parse * pParse, /* The parser context */
/* If the output is destined for a temporary table, open that table.
*/
if (pDest->eDest == SRT_EphemTab) {
- sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iSDParm,
- pEList->nExpr);
+ KeyInfo *pKeyInfo = sqlite3KeyInfoAlloc(pParse->db,
+ pEList->nExpr + 1, 0);
+ sqlite3VdbeAddOp4(v, OP_OpenTEphemeral, pDest->iSDParm,
+ pEList->nExpr + 1, 0, (char*)pKeyInfo, P4_KEYINFO);
+
+ VdbeComment((v, "Output table"));
}
/* Set the limiter.
@@ -5765,13 +5803,14 @@ sqlite3Select(Parse * pParse, /* The parser context */
*/
if (p->selFlags & SF_Distinct) {
sDistinct.tabTnct = pParse->nTab++;
- sDistinct.addrTnct = sqlite3VdbeAddOp4(v, OP_OpenEphemeral,
- sDistinct.tabTnct, 0, 0,
- (char *)
- keyInfoFromExprList
- (pParse, p->pEList, 0,
- 0), P4_KEYINFO);
+ KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, p->pEList, 0, 0);
+ sDistinct.addrTnct = sqlite3VdbeAddOp4(v, OP_OpenTEphemeral,
+ sDistinct.tabTnct,
+ pKeyInfo->nField,
+ 0, (char *)pKeyInfo,
+ P4_KEYINFO);
sqlite3VdbeChangeP5(v, BTREE_UNORDERED);
+ VdbeComment((v, "Distinct table"));
sDistinct.eTnctType = WHERE_DISTINCT_UNORDERED;
} else {
sDistinct.eTnctType = WHERE_DISTINCT_NOOP;
diff --git a/src/box/sql/where.c b/src/box/sql/where.c
index 5979ff933..d6a39187c 100644
--- a/src/box/sql/where.c
+++ b/src/box/sql/where.c
@@ -2783,10 +2783,8 @@ whereLoopAddBtree(WhereLoopBuilder * pBuilder, /* WHERE clause information */
int iSortIdx = 1; /* Index number */
int b; /* A boolean value */
LogEst rSize; /* number of rows in the table */
- LogEst rLogSize; /* Logarithm of the number of rows in the table */
WhereClause *pWC; /* The parsed WHERE clause */
Table *pTab; /* Table being queried */
- struct session *user_session = current_session();
pNew = pBuilder->pNew;
pWInfo = pBuilder->pWInfo;
@@ -2826,11 +2824,12 @@ whereLoopAddBtree(WhereLoopBuilder * pBuilder, /* WHERE clause information */
}
pProbe = &sPk;
}
- rSize = pTab->nRowLogEst;
- rLogSize = estLog(rSize);
#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
/* Automatic indexes */
+ LogEst rSize = pTab->nRowLogEst;
+ LogEst rLogSize = estLog(rSize);
+ struct session *user_session = current_session();
if (!pBuilder->pOrSet /* Not part of an OR optimization */
&& (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE) == 0 && (user_session->sql_flags & SQLITE_AutoIndex) != 0 && pSrc->pIBIndex == 0 /* Has no INDEXED BY clause */
&& !pSrc->fg.notIndexed /* Has no NOT INDEXED clause */
diff --git a/test/sql-tap/distinct.test.lua b/test/sql-tap/distinct.test.lua
index e932394d3..203afab5d 100755
--- a/test/sql-tap/distinct.test.lua
+++ b/test/sql-tap/distinct.test.lua
@@ -67,7 +67,7 @@ local function do_temptables_test(tn, sql, temptables)
for _, val in ipairs(r) do
local opcode = val[2]
local p5 = val[7]
- if opcode == "OpenEphemeral" or opcode == "SorterOpen" then
+ if opcode == "OpenTEphemeral" or opcode == "SorterOpen" then
if p5 ~= "08" and p5 ~= "00" then
error()--p5 = $p5)
end
--
2.15.1
More information about the Tarantool-patches
mailing list