* [tarantool-patches] [PATCH] sql: implement point where for DELETE stmts
@ 2018-06-01 11:46 Kirill Yukhin
2018-06-01 15:10 ` [tarantool-patches] " Kirill Yukhin
0 siblings, 1 reply; 2+ messages in thread
From: Kirill Yukhin @ 2018-06-01 11:46 UTC (permalink / raw)
To: v.shpilevoy; +Cc: tarantool-patches, Kirill Yukhin
Issue: https://github.com/tarantool/tarantool/issues/3235
Branch: https://github.com/tarantool/tarantool/tree/kyukhin/gh-3235-delete-with-where
Implement support of SQL's DELETE statemets
which a accompanied by point WHERE constraints.
This patch doesn't support any kinds of nested selects
or JOINs.
Part of #3235
---
src/box/field_def.c | 1 +
src/box/sql.c | 4 +-
src/box/sql/build.c | 41 +++++---
src/box/sql/delete.c | 48 ++++++---
src/box/sql/insert.c | 33 +++++++
src/box/sql/sqliteInt.h | 57 ++++++++++-
src/box/sql/where.c | 224 +++++++++++++++++++++++++++++++++++-------
src/box/sql/whereInt.h | 2 +
src/box/sql/wherecode.c | 115 +++++++++++++++++-----
test/sql-tap/delete1.test.lua | 24 +++--
10 files changed, 448 insertions(+), 101 deletions(-)
diff --git a/src/box/field_def.c b/src/box/field_def.c
index 4d39d03..8dbead6 100644
--- a/src/box/field_def.c
+++ b/src/box/field_def.c
@@ -95,6 +95,7 @@ const struct opt_def field_def_reg[] = {
nullable_action, NULL),
OPT_DEF("collation", OPT_UINT32, struct field_def, coll_id),
OPT_DEF("default", OPT_STRPTR, struct field_def, default_value),
+ OPT_DEF("affinity", OPT_UINT32, struct field_def, affinity),
OPT_END,
};
diff --git a/src/box/sql.c b/src/box/sql.c
index 7379cb4..b1d346e 100644
--- a/src/box/sql.c
+++ b/src/box/sql.c
@@ -1453,7 +1453,7 @@ int tarantoolSqlite3MakeTableFormat(Table *pTable, void *buf)
uint32_t cid = def->fields[i].coll_id;
struct field_def *field = &def->fields[i];
const char *default_str = field->default_value;
- int base_len = 4;
+ int base_len = 5;
if (cid != COLL_NONE)
base_len += 1;
if (default_str != NULL)
@@ -1474,6 +1474,8 @@ int tarantoolSqlite3MakeTableFormat(Table *pTable, void *buf)
assert(def->fields[i].is_nullable ==
action_is_nullable(def->fields[i].nullable_action));
p = enc->encode_str(p, t, strlen(t));
+ p = enc->encode_str(p, "affinity", 8);
+ p = enc->encode_uint(p, def->fields[i].affinity);
p = enc->encode_str(p, "is_nullable", 11);
p = enc->encode_bool(p, def->fields[i].is_nullable);
p = enc->encode_str(p, "nullable_action", 15);
diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index 65bba1f..c88ad30 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -1182,9 +1182,14 @@ bool
space_is_view(Table *table) {
assert(table != NULL);
uint32_t space_id = SQLITE_PAGENO_TO_SPACEID(table->tnum);
- struct space *space = space_by_id(space_id);
- assert(space != NULL);
- return space->def->opts.is_view;
+ if (space_id > 0) {
+ struct space *space = space_by_id(space_id);
+ assert(space != NULL);
+ return space->def->opts.is_view;
+ } else {
+ assert(table->def != NULL);
+ return table->def->opts.is_view;
+ }
}
struct ExprList *
@@ -1197,23 +1202,13 @@ space_checks_expr_list(uint32_t space_id)
return space->def->opts.checks;
}
-/**
- * Create cursor which will be positioned to the space/index.
- * It makes space lookup and loads pointer to it into register,
- * which is passes to OP_OpenWrite as an argument.
- *
- * @param parse_context Parse context.
- * @param cursor Number of cursor to be created.
- * @param entity_id Encoded space and index ids.
- * @retval address of last opcode.
- */
int
-emit_open_cursor(Parse *parse_context, int cursor, int entity_id)
+emit_open_cursor(struct Parse *parse_context, int cursor, int entity_id)
{
assert(entity_id > 0);
struct space *space = space_by_id(SQLITE_PAGENO_TO_SPACEID(entity_id));
assert(space != NULL);
- Vdbe *vdbe = parse_context->pVdbe;
+ struct Vdbe *vdbe = parse_context->pVdbe;
int space_ptr_reg = ++parse_context->nMem;
sqlite3VdbeAddOp4(vdbe, OP_LoadPtr, 0, space_ptr_reg, 0, (void*)space,
P4_SPACEPTR);
@@ -1221,6 +1216,22 @@ emit_open_cursor(Parse *parse_context, int cursor, int entity_id)
space_ptr_reg);
}
+int
+sql_emit_open_cursor(struct Parse *parse, int cursor, int index_id, struct space *space)
+{
+ assert(space != NULL);
+ Vdbe *vdbe = parse->pVdbe;
+ int space_ptr_reg = ++parse->nMem;
+ sqlite3VdbeAddOp4(vdbe, OP_LoadPtr, 0, space_ptr_reg, 0, (void*)space,
+ P4_SPACEPTR);
+ struct key_def *def = key_def_dup(space->index[index_id]->def->key_def);
+ if (def == NULL)
+ return 0;
+ return sqlite3VdbeAddOp4(vdbe, OP_OpenWrite, cursor, index_id,
+ space_ptr_reg,
+ (char*)def,
+ P4_KEYDEF);
+}
/*
* Generate code that will increment the schema cookie.
*
diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index 2b59130..de4e0c1 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -184,7 +184,7 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
memset(&nc, 0, sizeof(nc));
nc.pParse = parse;
if (tab_list->a[0].pTab == NULL) {
- struct Table *t = malloc(sizeof(struct Table));
+ struct Table *t = calloc(sizeof(struct Table), 1);
if (t == NULL) {
sqlite3OomFault(parse->db);
goto delete_from_cleanup;
@@ -266,7 +266,12 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
/* Extract the primary key for the current row */
if (!is_view) {
for (int i = 0; i < pk_len; i++) {
- sqlite3ExprCodeGetColumnOfTable(v, table->def,
+ struct space_def *def;
+ if (table != NULL)
+ def = table->def;
+ else
+ def = space->def;
+ sqlite3ExprCodeGetColumnOfTable(v, def,
tab_cursor,
pk_def->parts[i].fieldno,
reg_pk + i);
@@ -339,8 +344,18 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
int space_ptr_reg = ++parse->nMem;
sqlite3VdbeAddOp4(v, OP_LoadPtr, 0, space_ptr_reg, 0,
(void *)space, P4_SPACEPTR);
+
+ int tnum;
+ if (table != NULL) {
+ tnum = table->tnum;
+ }
+ else {
+ /* index id is 0 for PK. */
+ tnum = SQLITE_PAGENO_FROM_SPACEID_AND_INDEXID(space->def->id,
+ 0);
+ }
sqlite3VdbeAddOp3(v, OP_OpenWrite, tab_cursor,
- table->tnum, space_ptr_reg);
+ tnum, space_ptr_reg);
struct key_def *def = key_def_dup(pk_def);
if (def == NULL) {
sqlite3OomFault(parse->db);
@@ -446,7 +461,8 @@ sql_generate_row_delete(struct Parse *parse, struct Table *table,
/* If there are any triggers to fire, allocate a range of registers to
* use for the old.* references in the triggers.
*/
- if (sqlite3FkRequired(table, NULL) || trigger_list != NULL) {
+ if (table != NULL &&
+ (sqlite3FkRequired(table, NULL) || trigger_list != NULL)) {
/* Mask of OLD.* columns in use */
/* TODO: Could use temporary registers here. */
uint32_t mask =
@@ -505,7 +521,7 @@ sql_generate_row_delete(struct Parse *parse, struct Table *table,
* of the DELETE statement is to fire the INSTEAD OF
* triggers).
*/
- if (table->pSelect == NULL) {
+ if (table == NULL || table->pSelect == NULL) {
uint8_t p5 = 0;
sqlite3VdbeAddOp2(v, OP_Delete, cursor,
(need_update_count ? OPFLAG_NCHANGE : 0));
@@ -520,16 +536,20 @@ sql_generate_row_delete(struct Parse *parse, struct Table *table,
sqlite3VdbeChangeP5(v, p5);
}
- /* Do any ON CASCADE, SET NULL or SET DEFAULT operations
- * required to handle rows (possibly in other tables) that
- * refer via a foreign key to the row just deleted.
- */
- sqlite3FkActions(parse, table, 0, first_old_reg, 0);
+ if (table != NULL) {
+ /* Do any ON CASCADE, SET NULL or SET DEFAULT
+ * operations required to handle rows (possibly
+ * in other tables) that refer via a foreign
+ * key to the row just deleted.
+ */
- /* Invoke AFTER DELETE trigger programs. */
- sqlite3CodeRowTrigger(parse, trigger_list,
- TK_DELETE, 0, TRIGGER_AFTER, table, first_old_reg,
- onconf, label);
+ sqlite3FkActions(parse, table, 0, first_old_reg, 0);
+
+ /* Invoke AFTER DELETE trigger programs. */
+ sqlite3CodeRowTrigger(parse, trigger_list,
+ TK_DELETE, 0, TRIGGER_AFTER, table,
+ first_old_reg, onconf, label);
+ }
/* Jump here if the row had already been deleted before
* any BEFORE trigger programs were invoked. Or if a trigger program
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index 3dbf855..8d0ab3b 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -108,6 +108,39 @@ sqlite3IndexAffinityStr(sqlite3 * db, Index * pIdx)
return pIdx->zColAff;
}
+char *
+sql_index_affinity_str(struct sqlite3 * db, struct index_def *def)
+{
+ char *aff;
+ /* The first time a column affinity string for a particular index is
+ * required, it is allocated and populated here. It is then stored as
+ * a member of the Index structure for subsequent use.
+ *
+ * The column affinity string will eventually be deleted by
+ * sqliteDeleteIndex() when the Index structure itself is cleaned
+ * up.
+ */
+ int nColumn = def->key_def->part_count;
+ aff = (char *)sqlite3DbMallocRaw(0, nColumn + 1);
+ if (aff == NULL) {
+ sqlite3OomFault(db);
+ return 0;
+ }
+ int i;
+ struct space *space = space_cache_find(def->space_id);
+ assert(space != NULL);
+
+ for (i = 0; i < nColumn; i++) {
+ uint32_t x = def->key_def->parts[i].fieldno;
+ aff[i] = space->def->fields[x].affinity;
+ if (aff[i] == AFFINITY_UNDEFINED)
+ aff[i] = 'A';
+ }
+ aff[i] = 0;
+
+ return aff;
+}
+
/*
* Compute the affinity string for table pTab, if it has not already been
* computed. As an optimization, omit trailing AFFINITY_BLOB affinities.
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 943fda9..6ea956d 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -2536,6 +2536,8 @@ struct SrcList {
char *zName; /* Name of the table */
char *zAlias; /* The "B" part of a "A AS B" phrase. zName is the "A" */
Table *pTab; /* An SQL table corresponding to zName */
+ /* A temporary hack: need to store eph. space. */
+ struct space *space;
Select *pSelect; /* A SELECT statement used in place of a table name */
int addrFillSub; /* Address of subroutine to manifest a subquery */
int regReturn; /* Register holding return address of addrFillSub */
@@ -3552,8 +3554,36 @@ enum sort_order
sql_index_column_sort_order(Index *idx, uint32_t column);
void sqlite3EndTable(Parse *, Token *, Token *, Select *);
+
+/**
+ * DEPRECATED. All calls to be replaced w/ sql_emit_open_cursor.
+ * Create cursor which will be positioned to the space/index.
+ * It makes space lookup and loads pointer to it into register,
+ * which is passes to OP_OpenWrite as an argument.
+ *
+ * @param parse Parse context.
+ * @param cursor Number of cursor to be created.
+ * @param entity_id Encoded space and index ids.
+ * @retval address of last opcode.
+ */
+int
+emit_open_cursor(struct Parse *parse, int cursor, int entity_id);
+
+/**
+ * Create cursor which will be positioned to the space/index.
+ * It makes space lookup and loads pointer to it into register,
+ * which is passes to OP_OpenWrite as an argument.
+ *
+ * @param parse_context Parse context.
+ * @param cursor Number of cursor to be created.
+ * @param index_id Encoded index id (encoding is void actually, so
+ * pas it as is). In future will be replaced with pointer
+ * to struct index.
+ * @retval address of last opcode.
+ */
int
-emit_open_cursor(Parse *, int, int);
+sql_emit_open_cursor(struct Parse *parse, int cursor, int index_id,
+ struct space *space);
int sqlite3ParseUri(const char *, const char *, unsigned int *,
sqlite3_vfs **, char **, char **);
@@ -4085,6 +4115,31 @@ int sqlite3VarintLen(u64 v);
#define putVarint sqlite3PutVarint
const char *sqlite3IndexAffinityStr(sqlite3 *, Index *);
+
+/**
+ * Return a pointer to the column affinity string associated with index
+ * pIdx. A column affinity string has one character for each column in
+ * the table, according to the affinity of the column:
+ *
+ * Character Column affinity
+ * ------------------------------
+ * 'A' BLOB
+ * 'B' TEXT
+ * 'C' NUMERIC
+ * 'D' INTEGER
+ * 'F' REAL
+ *
+ * Memory for the buffer containing the column index affinity string
+ * is managed along with the rest of the Index structure. It will be
+ * released when sqlite3DeleteIndex() is called.
+ *
+ * @param db Database handle.
+ * @param def index_def where from affinity to be extracted.
+ * @retval Affinity string.
+ */
+char *
+sql_index_affinity_str(struct sqlite3 *db, struct index_def *def);
+
void sqlite3TableAffinity(Vdbe *, Table *, int);
char sqlite3CompareAffinity(Expr * pExpr, char aff2);
int sqlite3IndexAffinityOk(Expr * pExpr, char idx_affinity);
diff --git a/src/box/sql/where.c b/src/box/sql/where.c
index d312587..14cb23d 100644
--- a/src/box/sql/where.c
+++ b/src/box/sql/where.c
@@ -371,7 +371,10 @@ whereScanInit(WhereScan * pScan, /* The WhereScan object being initialized */
pScan->is_column_seen = false;
if (pIdx) {
int j = iColumn;
- iColumn = pIdx->aiColumn[j];
+ if (j >= pIdx->nColumn)
+ iColumn = -1;
+ else
+ iColumn = pIdx->aiColumn[j];
if (iColumn >= 0) {
char affinity =
pIdx->pTable->def->fields[iColumn].affinity;
@@ -390,6 +393,34 @@ whereScanInit(WhereScan * pScan, /* The WhereScan object being initialized */
return whereScanNext(pScan);
}
+static WhereTerm *
+sql_where_scan_init(struct WhereScan *scan, struct WhereClause *clause,
+ int cursor, int column, uint32_t op_mask,
+ struct space_def *space_def, struct key_def *key_def)
+{
+ scan->pOrigWC = scan->pWC = clause;
+ scan->pIdxExpr = NULL;
+ scan->idxaff = 0;
+ scan->coll = NULL;
+ scan->is_column_seen = false;
+ if (key_def != NULL) {
+ int j = column;
+ column = key_def->parts[j].fieldno;
+ if (column >= 0) {
+ scan->idxaff = space_def->fields[column].affinity;
+ scan->coll = key_def->parts[column].coll;
+ scan->is_column_seen = true;
+ }
+ }
+ scan->opMask = op_mask;
+ scan->k = 0;
+ scan->aiCur[0] = cursor;
+ scan->aiColumn[0] = column;
+ scan->nEquiv = 1;
+ scan->iEquiv = 1;
+ return whereScanNext(scan);
+}
+
/*
* Search for a term in the WHERE clause that is of the form "X <op> <expr>"
* where X is a reference to the iColumn of table iCur or of index pIdx
@@ -441,6 +472,36 @@ sqlite3WhereFindTerm(WhereClause * pWC, /* The WHERE clause to be searched */
return pResult;
}
+WhereTerm *
+sql_where_find_term(WhereClause * pWC, /* The WHERE clause to be searched */
+ int iCur, /* Cursor number of LHS */
+ int iColumn, /* Column number of LHS */
+ Bitmask notReady, /* RHS must not overlap with this mask */
+ u32 op, /* Mask of WO_xx values describing operator */
+ struct space_def *space_def,
+ struct key_def *key_def) /* Must be compatible with this index, if not NULL */
+{
+ WhereTerm *pResult = 0;
+ WhereTerm *p;
+ WhereScan scan;
+
+ p = sql_where_scan_init(&scan, pWC, iCur, iColumn, op, space_def,
+ key_def);
+ op &= WO_EQ;
+ while (p) {
+ if ((p->prereqRight & notReady) == 0) {
+ if (p->prereqRight == 0 && (p->eOperator & op) != 0) {
+ testcase(p->eOperator & WO_IS);
+ return p;
+ }
+ if (pResult == 0)
+ pResult = p;
+ }
+ p = whereScanNext(&scan);
+ }
+ return pResult;
+}
+
/*
* This function searches pList for an entry that matches the iCol-th column
* of index pIdx.
@@ -1703,6 +1764,7 @@ whereLoopInit(WhereLoop * p)
p->nLTerm = 0;
p->nLSlot = ArraySize(p->aLTermSpace);
p->wsFlags = 0;
+ p->index_def = NULL;
}
/*
@@ -3366,7 +3428,7 @@ wherePathSatisfiesOrderBy(WhereInfo * pWInfo, /* The WHERE clause */
/* Get the column number in the table (iColumn) and sort order
* (revIdx) for the j-th column of the index.
*/
- if (pIndex) {
+ if (pIndex && j < pIndex->nColumn) {
iColumn = pIndex->aiColumn[j];
revIdx = sql_index_column_sort_order(pIndex,
j);
@@ -4046,7 +4108,7 @@ wherePathSolver(WhereInfo * pWInfo, LogEst nRowEst)
* general-purpose query planner.
*/
static int
-whereShortCut(WhereLoopBuilder * pBuilder)
+sql_where_shortcut(struct WhereLoopBuilder *builder)
{
WhereInfo *pWInfo;
struct SrcList_item *pItem;
@@ -4055,20 +4117,20 @@ whereShortCut(WhereLoopBuilder * pBuilder)
WhereLoop *pLoop;
int iCur;
int j;
- Table *pTab;
- Index *pIdx;
- pWInfo = pBuilder->pWInfo;
+ pWInfo = builder->pWInfo;
if (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)
return 0;
assert(pWInfo->pTabList->nSrc >= 1);
pItem = pWInfo->pTabList->a;
- pTab = pItem->pTab;
+ struct space_def *space_def = pItem->pTab->def;
+ assert(space_def != NULL);
+
if (pItem->fg.isIndexedBy)
return 0;
iCur = pItem->iCursor;
pWC = &pWInfo->sWC;
- pLoop = pBuilder->pNew;
+ pLoop = builder->pNew;
pLoop->wsFlags = 0;
pLoop->nSkip = 0;
pTerm = sqlite3WhereFindTerm(pWC, iCur, -1, 0, WO_EQ, 0);
@@ -4080,34 +4142,74 @@ whereShortCut(WhereLoopBuilder * pBuilder)
/* TUNING: Cost of a PK lookup is 10 */
pLoop->rRun = 33; /* 33==sqlite3LogEst(10) */
} else {
- for (pIdx = pTab->pIndex; pIdx; pIdx = pIdx->pNext) {
+ struct space *space = space_cache_find(space_def->id);
+ if (space != NULL) {
+ for (uint32_t i = 0; i < space->index_count; ++i) {
+ struct index_def *idx_def;
+ idx_def = space->index[i]->def;
+ int opMask;
+ int nIdxCol = idx_def->key_def->part_count;
+ assert(pLoop->aLTermSpace == pLoop->aLTerm);
+ if (!idx_def->opts.is_unique
+ /* || pIdx->pPartIdxWhere != 0 */
+ || nIdxCol > ArraySize(pLoop->aLTermSpace)
+ )
+ continue;
+ opMask = WO_EQ;
+ for (j = 0; j < nIdxCol; j++) {
+ pTerm = sql_where_find_term(pWC, iCur,
+ j, 0,
+ opMask,
+ space_def,
+ idx_def->
+ key_def);
+ if (pTerm == 0)
+ break;
+ testcase(pTerm->eOperator & WO_IS);
+ pLoop->aLTerm[j] = pTerm;
+ }
+ if (j != nIdxCol)
+ continue;
+ pLoop->wsFlags = WHERE_COLUMN_EQ |
+ WHERE_ONEROW | WHERE_INDEXED |
+ WHERE_IDX_ONLY;
+ pLoop->nLTerm = j;
+ pLoop->nEq = j;
+ pLoop->pIndex = NULL;
+ pLoop->index_def = idx_def;
+ /* TUNING: Cost of a unique index lookup is 15 */
+ pLoop->rRun = 39; /* 39==sqlite3LogEst(15) */
+ break;
+ }
+ } else {
+ /* Space is ephemeral. */
+ assert(space_def->id == 0);
int opMask;
- int nIdxCol = index_column_count(pIdx);
+ int nIdxCol = space_def->field_count;
assert(pLoop->aLTermSpace == pLoop->aLTerm);
- if (!index_is_unique(pIdx)
- || pIdx->pPartIdxWhere != 0
- || nIdxCol > ArraySize(pLoop->aLTermSpace)
- )
- continue;
+ if ( nIdxCol > ArraySize(pLoop->aLTermSpace))
+ return 0;
opMask = WO_EQ;
for (j = 0; j < nIdxCol; j++) {
- pTerm =
- sqlite3WhereFindTerm(pWC, iCur, j, 0,
- opMask, pIdx);
- if (pTerm == 0)
+ pTerm = sql_where_find_term(pWC, iCur,
+ j, 0,
+ opMask,
+ space_def,
+ NULL);
+ if (pTerm == NULL)
break;
pLoop->aLTerm[j] = pTerm;
}
if (j != nIdxCol)
- continue;
+ return 0;
pLoop->wsFlags = WHERE_COLUMN_EQ | WHERE_ONEROW |
WHERE_INDEXED | WHERE_IDX_ONLY;
pLoop->nLTerm = j;
pLoop->nEq = j;
- pLoop->pIndex = pIdx;
+ pLoop->pIndex = NULL;
+ pLoop->index_def = NULL;
/* TUNING: Cost of a unique index lookup is 15 */
pLoop->rRun = 39; /* 39==sqlite3LogEst(15) */
- break;
}
}
if (pLoop->wsFlags) {
@@ -4420,7 +4522,7 @@ sqlite3WhereBegin(Parse * pParse, /* The parser context */
}
#endif
- if (nTabList != 1 || whereShortCut(&sWLB) == 0) {
+ if (nTabList != 1 || sql_where_shortcut(&sWLB) == 0) {
rc = whereLoopAddAll(&sWLB);
if (rc)
goto whereBeginError;
@@ -4583,13 +4685,31 @@ sqlite3WhereBegin(Parse * pParse, /* The parser context */
}
if (pLoop->wsFlags & WHERE_INDEXED) {
Index *pIx = pLoop->pIndex;
+ struct index_def *idx_def = pLoop->index_def;
+ struct space *space = space_cache_find(pTabItem->pTab->def->id);
int iIndexCur;
int op = OP_OpenRead;
/* iAuxArg is always set if to a positive value if ONEPASS is possible */
assert(iAuxArg != 0
|| (pWInfo->
wctrlFlags & WHERE_ONEPASS_DESIRED) == 0);
- if (IsPrimaryKeyIndex(pIx)
+ /* Check if index is primary. Either of
+ * points should be true:
+ * 1. struct Index is non-NULL and is
+ * primary
+ * 2. idx_def is non-NULL and it is
+ * primary
+ * 3. (goal of this comment) both pIx and
+ * idx_def are NULL in which case it is
+ * ephemeral table, but not in Tnt sense.
+ * It is something w/ defined space_def
+ * and nothing else. Skip such loops.
+ */
+ if (idx_def == NULL && pIx == NULL) continue;
+ bool is_primary = (pIx != NULL && IsPrimaryKeyIndex(pIx)) ||
+ (idx_def != NULL && (idx_def->iid == 0)) |
+ (idx_def == NULL && pIx == NULL);
+ if (is_primary
&& (wctrlFlags & WHERE_OR_SUBCLAUSE) != 0) {
/* This is one term of an OR-optimization using
* the PRIMARY KEY. No need for a separate index
@@ -4597,13 +4717,31 @@ sqlite3WhereBegin(Parse * pParse, /* The parser context */
iIndexCur = pLevel->iTabCur;
op = 0;
} else if (pWInfo->eOnePass != ONEPASS_OFF) {
- Index *pJ = pTabItem->pTab->pIndex;
- iIndexCur = iAuxArg;
- assert(wctrlFlags & WHERE_ONEPASS_DESIRED);
- while (ALWAYS(pJ) && pJ != pIx) {
- iIndexCur++;
- pJ = pJ->pNext;
+ if (pIx != NULL) {
+ Index *pJ = pTabItem->pTab->pIndex;
+ iIndexCur = iAuxArg;
+ assert(wctrlFlags &
+ WHERE_ONEPASS_DESIRED);
+ while (ALWAYS(pJ) && pJ != pIx) {
+ iIndexCur++;
+ pJ = pJ->pNext;
+ }
+ } else {
+ if (space != NULL) {
+ for(uint32_t i = 0;
+ i < space->index_count;
+ ++i) {
+ if (space->index[i]->def ==
+ idx_def) {
+ iIndexCur = iAuxArg + i;
+ break;
+ }
+ }
+ } else {
+ iIndexCur = iAuxArg;
+ }
}
+ assert(wctrlFlags & WHERE_ONEPASS_DESIRED);
op = OP_OpenWrite;
pWInfo->aiCurOnePass[1] = iIndexCur;
} else if (iAuxArg
@@ -4614,11 +4752,17 @@ sqlite3WhereBegin(Parse * pParse, /* The parser context */
iIndexCur = pParse->nTab++;
}
pLevel->iIdxCur = iIndexCur;
- assert(pIx->pSchema == pTab->pSchema);
assert(iIndexCur >= 0);
if (op) {
- emit_open_cursor(pParse, iIndexCur, pIx->tnum);
- sql_vdbe_set_p4_key_def(pParse, pIx);
+ if (pIx != NULL) {
+ emit_open_cursor(pParse, iIndexCur,
+ pIx->tnum);
+ sql_vdbe_set_p4_key_def(pParse, pIx);
+ } else {
+ sql_emit_open_cursor(pParse, iIndexCur,
+ idx_def->iid,
+ space);
+ }
if ((pLoop->wsFlags & WHERE_CONSTRAINT) != 0
&& (pLoop->
wsFlags & (WHERE_COLUMN_RANGE |
@@ -4627,7 +4771,10 @@ sqlite3WhereBegin(Parse * pParse, /* The parser context */
wctrlFlags & WHERE_ORDERBY_MIN) == 0) {
sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ); /* Hint to COMDB2 */
}
- VdbeComment((v, "%s", pIx->zName));
+ if (pIx != NULL)
+ VdbeComment((v, "%s", pIx->zName));
+ else
+ VdbeComment((v, "%s", idx_def->name));
#ifdef SQLITE_ENABLE_COLUMN_USED_MASK
{
u64 colUsed = 0;
@@ -4809,7 +4956,6 @@ sqlite3WhereEnd(WhereInfo * pWInfo)
for (i = 0, pLevel = pWInfo->a; i < pWInfo->nLevel; i++, pLevel++) {
int k, last;
VdbeOp *pOp;
- Index *pIdx = 0;
struct SrcList_item *pTabItem = &pTabList->a[pLevel->iFrom];
Table *pTab MAYBE_UNUSED = pTabItem->pTab;
assert(pTab != 0);
@@ -4836,12 +4982,15 @@ sqlite3WhereEnd(WhereInfo * pWInfo)
* that reference the table and converts them into opcodes that
* reference the index.
*/
+ Index *pIdx = NULL;
+ struct index_def *def = NULL;
if (pLoop->wsFlags & (WHERE_INDEXED | WHERE_IDX_ONLY)) {
pIdx = pLoop->pIndex;
+ def = pLoop->index_def;
} else if (pLoop->wsFlags & WHERE_MULTI_OR) {
pIdx = pLevel->u.pCovidx;
}
- if (pIdx && !db->mallocFailed) {
+ if ((pIdx != NULL || def != NULL) && !db->mallocFailed) {
last = sqlite3VdbeCurrentAddr(v);
k = pLevel->addrBody;
pOp = sqlite3VdbeGetOp(v, k);
@@ -4850,7 +4999,8 @@ sqlite3WhereEnd(WhereInfo * pWInfo)
continue;
if (pOp->opcode == OP_Column) {
int x = pOp->p2;
- assert(pIdx->pTable == pTab);
+ assert(pIdx == NULL ||
+ pIdx->pTable == pTab);
if (x >= 0) {
pOp->p2 = x;
pOp->p1 = pLevel->iIdxCur;
diff --git a/src/box/sql/whereInt.h b/src/box/sql/whereInt.h
index 1303365..548cbcb 100644
--- a/src/box/sql/whereInt.h
+++ b/src/box/sql/whereInt.h
@@ -141,6 +141,8 @@ struct WhereLoop {
u16 nBtm; /* Size of BTM vector */
u16 nTop; /* Size of TOP vector */
Index *pIndex; /* Index used, or NULL */
+ /** Index definition, if there's no pIndex. */
+ struct index_def *index_def;
u32 wsFlags; /* WHERE_* flags describing the plan */
u16 nLTerm; /* Number of entries in aLTerm[] */
u16 nSkip; /* Number of NULL aLTerm[] entries */
diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c
index bf2a2a2..1ce1db0 100644
--- a/src/box/sql/wherecode.c
+++ b/src/box/sql/wherecode.c
@@ -38,6 +38,7 @@
* that actually generate the bulk of the WHERE loop code. The original where.c
* file retains the code that does query planning and analysis.
*/
+#include "box/schema.h"
#include "sqliteInt.h"
#include "whereInt.h"
@@ -62,6 +63,7 @@ explainIndexColumnName(Index * pIdx, int i)
static void
explainAppendTerm(StrAccum * pStr, /* The text expression being built */
Index * pIdx, /* Index to read column names from */
+ struct index_def *def,
int nTerm, /* Number of terms */
int iTerm, /* Zero-based index of first term. */
int bAnd, /* Non-zero to append " AND " */
@@ -78,9 +80,16 @@ explainAppendTerm(StrAccum * pStr, /* The text expression being built */
for (i = 0; i < nTerm; i++) {
if (i)
sqlite3StrAccumAppend(pStr, ",", 1);
- sqlite3StrAccumAppendAll(pStr,
- explainIndexColumnName(pIdx,
- iTerm + i));
+ const char *name;
+ if (pIdx != NULL) {
+ name = explainIndexColumnName(pIdx, iTerm + i);
+ } else {
+ assert(def != NULL);
+ struct space *space = space_cache_find(def->space_id);
+ assert(space != NULL);
+ name = space->def->fields[i].name;
+ }
+ sqlite3StrAccumAppendAll(pStr, name);
}
if (nTerm > 1)
sqlite3StrAccumAppend(pStr, ")", 1);
@@ -116,16 +125,26 @@ static void
explainIndexRange(StrAccum * pStr, WhereLoop * pLoop)
{
Index *pIndex = pLoop->pIndex;
+ struct index_def *def = pLoop->index_def;
u16 nEq = pLoop->nEq;
u16 nSkip = pLoop->nSkip;
int i, j;
+ assert(pIndex != NULL || def != NULL);
+
if (nEq == 0
&& (pLoop->wsFlags & (WHERE_BTM_LIMIT | WHERE_TOP_LIMIT)) == 0)
return;
sqlite3StrAccumAppend(pStr, " (", 2);
for (i = 0; i < nEq; i++) {
- const char *z = explainIndexColumnName(pIndex, i);
+ const char *z;
+ if (pIndex != NULL) {
+ z = explainIndexColumnName(pIndex, i);
+ } else {
+ struct space *space = space_cache_find(def->space_id);
+ assert(space != NULL);
+ z = space->def->fields[i].name;
+ }
if (i)
sqlite3StrAccumAppend(pStr, " AND ", 5);
sqlite3XPrintf(pStr, i >= nSkip ? "%s=?" : "ANY(%s)", z);
@@ -133,11 +152,11 @@ explainIndexRange(StrAccum * pStr, WhereLoop * pLoop)
j = i;
if (pLoop->wsFlags & WHERE_BTM_LIMIT) {
- explainAppendTerm(pStr, pIndex, pLoop->nBtm, j, i, ">");
+ explainAppendTerm(pStr, pIndex, def, pLoop->nBtm, j, i, ">");
i = 1;
}
if (pLoop->wsFlags & WHERE_TOP_LIMIT) {
- explainAppendTerm(pStr, pIndex, pLoop->nTop, j, i, "<");
+ explainAppendTerm(pStr, pIndex, def, pLoop->nTop, j, i, "<");
}
sqlite3StrAccumAppend(pStr, ")", 1);
}
@@ -199,13 +218,15 @@ sqlite3WhereExplainOneScan(Parse * pParse, /* Parse context */
}
if ((flags & WHERE_IPK) == 0) {
const char *zFmt = 0;
- Index *pIdx;
+ Index *pIdx = pLoop->pIndex;
+ struct index_def *idx_def = pLoop->index_def;
+ if (pIdx == NULL && idx_def == NULL) return 0;
- assert(pLoop->pIndex != 0);
- pIdx = pLoop->pIndex;
+ assert(pIdx != NULL || idx_def != NULL);
assert(!(flags & WHERE_AUTO_INDEX)
|| (flags & WHERE_IDX_ONLY));
- if (IsPrimaryKeyIndex(pIdx)) {
+ if ((pIdx != NULL && IsPrimaryKeyIndex(pIdx)) ||
+ (idx_def != NULL && idx_def->iid == 0)) {
if (isSearch) {
zFmt = "PRIMARY KEY";
}
@@ -220,7 +241,12 @@ sqlite3WhereExplainOneScan(Parse * pParse, /* Parse context */
}
if (zFmt) {
sqlite3StrAccumAppend(&str, " USING ", 7);
- sqlite3XPrintf(&str, zFmt, pIdx->zName);
+ if (pIdx != NULL)
+ sqlite3XPrintf(&str, zFmt, pIdx->zName);
+ else if (idx_def != NULL)
+ sqlite3XPrintf(&str, zFmt, idx_def->name);
+ else
+ sqlite3XPrintf(&str, zFmt, "EPHEMERAL INDEX");
explainIndexRange(&str, pLoop);
}
} else if ((flags & WHERE_IPK) != 0
@@ -675,20 +701,19 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */
u16 nEq; /* The number of == or IN constraints to code */
u16 nSkip; /* Number of left-most columns to skip */
Vdbe *v = pParse->pVdbe; /* The vm under construction */
- Index *pIdx; /* The index being used for this loop */
WhereTerm *pTerm; /* A single constraint term */
WhereLoop *pLoop; /* The WhereLoop object */
int j; /* Loop counter */
int regBase; /* Base register */
int nReg; /* Number of registers to allocate */
- char *zAff; /* Affinity string to return */
/* This module is only called on query plans that use an index. */
pLoop = pLevel->pWLoop;
nEq = pLoop->nEq;
nSkip = pLoop->nSkip;
- pIdx = pLoop->pIndex;
- assert(pIdx != 0);
+ struct Index *pIdx = pLoop->pIndex;
+ struct index_def *idx_def = pLoop->index_def;
+ assert(pIdx != NULL || idx_def != NULL);
/* Figure out how many memory cells we will need then allocate them.
*/
@@ -696,9 +721,13 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */
nReg = pLoop->nEq + nExtraReg;
pParse->nMem += nReg;
- zAff =
- sqlite3DbStrDup(pParse->db,
- sqlite3IndexAffinityStr(pParse->db, pIdx));
+ char *zAff;
+ if (pIdx != NULL) {
+ zAff = sqlite3DbStrDup(pParse->db,
+ sqlite3IndexAffinityStr(pParse->db, pIdx));
+ } else {
+ zAff = sql_index_affinity_str(pParse->db, idx_def);
+ }
assert(zAff != 0 || pParse->db->mallocFailed);
if (nSkip) {
@@ -1214,7 +1243,6 @@ sqlite3WhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about t
int endEq; /* True if range end uses ==, >= or <= */
int start_constraints; /* Start of range is constrained */
int nConstraint; /* Number of constraint terms */
- Index *pIdx; /* The index we will be using */
int iIdxCur; /* The VDBE cursor for the index */
int nExtraReg = 0; /* Number of extra registers needed */
int op; /* Instruction opcode */
@@ -1227,7 +1255,9 @@ sqlite3WhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about t
* to integer type, used for IPK.
*/
- pIdx = pLoop->pIndex;
+ struct Index *pIdx = pLoop->pIndex;
+ struct index_def *idx_def = pLoop->index_def;
+ assert(pIdx != NULL || idx_def != NULL);
iIdxCur = pLevel->iIdxCur;
assert(nEq >= pLoop->nSkip);
@@ -1242,7 +1272,11 @@ sqlite3WhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about t
assert(pWInfo->pOrderBy == 0
|| pWInfo->pOrderBy->nExpr == 1
|| (pWInfo->wctrlFlags & WHERE_ORDERBY_MIN) == 0);
- int nIdxCol = index_column_count(pIdx);
+ int nIdxCol;
+ if (pIdx != NULL)
+ nIdxCol = index_column_count(pIdx);
+ else
+ nIdxCol = idx_def->key_def->part_count;
if ((pWInfo->wctrlFlags & WHERE_ORDERBY_MIN) != 0
&& pWInfo->nOBSat > 0 && (nIdxCol > nEq)) {
j = pIdx->aiColumn[nEq];
@@ -1379,11 +1413,34 @@ sqlite3WhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about t
startEq = 0;
start_constraints = 1;
}
- struct Index *pk = sqlite3PrimaryKeyIndex(pIdx->pTable);
- assert(pk);
- int nPkCol = index_column_count(pk);
- char affinity =
- pIdx->pTable->def->fields[pk->aiColumn[0]].affinity;
+ struct Index *pk = NULL;
+ struct index_def *idx_pk = NULL;
+ char affinity;
+ if (pIdx == NULL) {
+ struct space *space = space_cache_find(idx_def->space_id);
+ assert(space != NULL);
+ idx_pk = space->index[0]->def;
+ int fieldno = idx_pk->key_def->parts[0].fieldno;
+ affinity = space->def->fields[fieldno].affinity;
+ if (affinity == AFFINITY_UNDEFINED) {
+ if (idx_pk->key_def->part_count == 1 &&
+ space->def->fields[fieldno].type ==
+ FIELD_TYPE_INTEGER)
+ affinity = AFFINITY_INTEGER;
+ else
+ affinity = AFFINITY_BLOB;
+ }
+ } else {
+ pk = sqlite3PrimaryKeyIndex(pIdx->pTable);
+ affinity =
+ pIdx->pTable->def->fields[pk->aiColumn[0]].affinity;
+ }
+
+ int nPkCol;
+ if (pk != NULL)
+ nPkCol = index_column_count(pk);
+ else
+ nPkCol = idx_pk->key_def->part_count;
if (nPkCol == 1 && affinity == AFFINITY_INTEGER) {
/* Right now INTEGER PRIMARY KEY is the only option to
* get Tarantool's INTEGER column type. Need special handling
@@ -1392,7 +1449,11 @@ sqlite3WhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about t
*/
int limit = pRangeStart == NULL ? nEq : nEq + 1;
for (int i = 0; i < limit; i++) {
- if (pIdx->aiColumn[i] == pk->aiColumn[0]) {
+ if ((pIdx != NULL && pIdx->aiColumn[i] ==
+ pk->aiColumn[0]) ||
+ (idx_pk != NULL &&
+ idx_def->key_def->parts[i].fieldno ==
+ idx_pk->key_def->parts[0].fieldno)) {
/* Here: we know for sure that table has INTEGER
PRIMARY KEY, single column, and Index we're
trying to use for scan contains this column. */
diff --git a/test/sql-tap/delete1.test.lua b/test/sql-tap/delete1.test.lua
index 810ca8a..d6d4762 100755
--- a/test/sql-tap/delete1.test.lua
+++ b/test/sql-tap/delete1.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(9)
+test:plan(10)
--!./tcltestrunner.lua
-- ["set","testdir",[["file","dirname",["argv0"]]]]
@@ -132,18 +132,30 @@ test:do_test(
})
-- Tests for data dictionary integration.
-s = box.schema.create_space('t')
-i = s:create_index('i', {parts={1, 'unsigned'}})
+format = {}
+format[1] = {name = 'id', type = 'scalar'}
+format[2] = {name = 'f', type = 'scalar'}
+s = box.schema.create_space('t', {format = format})
+i = s:create_index('i', {parts = {1, 'scalar'}})
+
test:do_test(
"delete1-6.0",
function()
- s:replace({1})
- s:replace({2})
- s:replace({3})
+ s:replace({1, 4})
+ s:replace({2, 5})
+ s:replace({3, 6})
return s:count()
end,
3)
+test:do_test(
+ "delete1-6.1.1",
+ function()
+ box.sql.execute([[delete from "t" where "id"=2]])
+ return s:count()
+ end,
+ 2)
+
test:do_test(
"delete1-6.1",
function()
--
2.16.2
^ permalink raw reply [flat|nested] 2+ messages in thread
* [tarantool-patches] Re: [PATCH] sql: implement point where for DELETE stmts
2018-06-01 11:46 [tarantool-patches] [PATCH] sql: implement point where for DELETE stmts Kirill Yukhin
@ 2018-06-01 15:10 ` Kirill Yukhin
0 siblings, 0 replies; 2+ messages in thread
From: Kirill Yukhin @ 2018-06-01 15:10 UTC (permalink / raw)
To: v.shpilevoy; +Cc: tarantool-patches
On 01 июн 14:46, Kirill Yukhin wrote:
> Issue: https://github.com/tarantool/tarantool/issues/3235
> Branch: https://github.com/tarantool/tarantool/tree/kyukhin/gh-3235-delete-with-where
Please, disregard.
--
Kirill
^ permalink raw reply [flat|nested] 2+ messages in thread
end of thread, other threads:[~2018-06-01 15:10 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2018-06-01 11:46 [tarantool-patches] [PATCH] sql: implement point where for DELETE stmts Kirill Yukhin
2018-06-01 15:10 ` [tarantool-patches] " Kirill Yukhin
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox