[tarantool-patches] [PATCH] sql: statistics removal after dropping an index
N.Tatunov
hollow653 at gmail.com
Tue Apr 3 21:07:13 MSK 2018
Currently dropping an index leads to removal of
all the entries containing the certain index name
in "_sql_statN" tables. Thus far analyze routine was fixed
so it seems that the indexes from the different tables but
with the same names should work more properly.
Closes: #3264
---
Branch: https://github.com/tarantool/tarantool
Issue: https://github.com/tarantool/tarantool/issues/3264
src/box/sql/build.c | 36 ++++++----
test/sql/sql-statN-index-drop.result | 127 +++++++++++++++++++++++++++++++++
test/sql/sql-statN-index-drop.test.lua | 54 ++++++++++++++
3 files changed, 205 insertions(+), 12 deletions(-)
create mode 100644 test/sql/sql-statN-index-drop.result
create mode 100644 test/sql/sql-statN-index-drop.test.lua
diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index 5e3ed0f..baaade2 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -2213,19 +2213,31 @@ sqliteViewResetAll(sqlite3 * db)
static void
sqlite3ClearStatTables(Parse * pParse, /* The parsing context */
const char *zType, /* "idx" or "tbl" */
- const char *zName /* Name of index or table */
+ const char *zName, /* Name of index or table */
+ const char *zTable /* Name of the indexed table*/
)
{
int i;
- for (i = 1; i <= 4; i++) {
- char zTab[24];
- sqlite3_snprintf(sizeof(zTab), zTab, "_sql_stat%d", i);
- if (sqlite3FindTable(pParse->db, zTab)) {
- sqlite3NestedParse(pParse,
- "DELETE FROM \"%s\" WHERE \"%s\"=%Q",
- zTab, zType, zName);
- }
- }
+ if(strcmp(zType, "idx") == 0)
+ for (i = 1; i <= 4; i++) {
+ char zTab[24];
+ sqlite3_snprintf(sizeof(zTab), zTab, "_sql_stat%d", i);
+ if (sqlite3FindTable(pParse->db, zTab)) {
+ sqlite3NestedParse(pParse,
+ "DELETE FROM \"%s\" WHERE (\"%s\"=%Q AND "
+ "\"tbl\"=%Q)",
+ zTab, zType, zName, zTable);
+ }
+ } else
+ for (i = 1; i <= 4; i++) {
+ char zTab[24];
+ sqlite3_snprintf(sizeof(zTab), zTab, "_sql_stat%d", i);
+ if (sqlite3FindTable(pParse->db, zTab)) {
+ sqlite3NestedParse(pParse,
+ "DELETE FROM \"%s\" WHERE \"%s\"=%Q",
+ zTab, zType, zName);
+ }
+ }
}
/*
@@ -2415,7 +2427,7 @@ sqlite3DropTable(Parse * pParse, SrcList * pName, int isView, int noErr)
*/
sqlite3BeginWriteOperation(pParse, 1);
- sqlite3ClearStatTables(pParse, "tbl", pTab->zName);
+ sqlite3ClearStatTables(pParse, "tbl", pTab->zName, NULL);
sqlite3FkDropTable(pParse, pName, pTab);
sqlite3CodeDropTable(pParse, pTab, isView);
@@ -3417,7 +3429,7 @@ sqlite3DropIndex(Parse * pParse, SrcList * pName, Token * pName2, int ifExists)
* But firstly, delete statistics since schema
* changes after DDL.
*/
- sqlite3ClearStatTables(pParse, "idx", pIndex->zName);
+ sqlite3ClearStatTables(pParse, "idx", pIndex->zName, pIndex->pTable->zName);
int record_reg = ++pParse->nMem;
int space_id_reg = ++pParse->nMem;
sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_PAGENO_TO_SPACEID(pIndex->tnum),
diff --git a/test/sql/sql-statN-index-drop.result b/test/sql/sql-statN-index-drop.result
new file mode 100644
index 0000000..707d7e0
--- /dev/null
+++ b/test/sql/sql-statN-index-drop.result
@@ -0,0 +1,127 @@
+test_run = require('test_run').new()
+---
+...
+-- Initializing some things.
+box.sql.execute("CREATE TABLE t1(id PRIMARY KEY, a);")
+---
+...
+box.sql.execute("CREATE TABLE t2(id PRIMARY KEY, a);")
+---
+...
+box.sql.execute("CREATE INDEX i1 ON t1(a);")
+---
+...
+box.sql.execute("CREATE INDEX i1 ON t2(a);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(1, 2);")
+---
+...
+box.sql.execute("INSERT INTO t2 VALUES(1, 2);")
+---
+...
+-- Analyze.
+box.sql.execute("ANALYZE;")
+---
+...
+-- Checking the data.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+---
+- - ['T1', 'I1', '1', '0', '0', !!binary kQI=]
+ - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
+ - ['T2', 'I1', '1', '0', '0', !!binary kQI=]
+ - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
+...
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+---
+- - ['T1', 'I1', '1 1']
+ - ['T1', 'T1', '1 1']
+ - ['T2', 'I1', '1 1']
+ - ['T2', 'T2', '1 1']
+...
+-- Dropping an index
+box.sql.execute("DROP INDEX i1 ON t1;")
+---
+...
+-- Checking the DROP INDEX results.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+---
+- - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
+ - ['T2', 'I1', '1', '0', '0', !!binary kQI=]
+ - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
+...
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+---
+- - ['T1', 'T1', '1 1']
+ - ['T2', 'I1', '1 1']
+ - ['T2', 'T2', '1 1']
+...
+--Cleaning up.
+box.sql.execute("DROP TABLE t1;")
+---
+...
+box.sql.execute("DROP TABLE t2;")
+---
+...
+-- Same test but dropping an INDEX ON t2.
+box.sql.execute("CREATE TABLE t1(id PRIMARY KEY, a);")
+---
+...
+box.sql.execute("CREATE TABLE t2(id PRIMARY KEY, a);")
+---
+...
+box.sql.execute("CREATE INDEX i1 ON t1(a);")
+---
+...
+box.sql.execute("CREATE INDEX i1 ON t2(a);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(1, 2);")
+---
+...
+box.sql.execute("INSERT INTO t2 VALUES(1, 2);")
+---
+...
+-- Analyze.
+box.sql.execute("ANALYZE;")
+---
+...
+-- Checking the data.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+---
+- - ['T1', 'I1', '1', '0', '0', !!binary kQI=]
+ - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
+ - ['T2', 'I1', '1', '0', '0', !!binary kQI=]
+ - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
+...
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+---
+- - ['T1', 'I1', '1 1']
+ - ['T1', 'T1', '1 1']
+ - ['T2', 'I1', '1 1']
+ - ['T2', 'T2', '1 1']
+...
+-- Dropping an index
+box.sql.execute("DROP INDEX i1 ON t2;")
+---
+...
+-- Checking the DROP INDEX results.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+---
+- - ['T1', 'I1', '1', '0', '0', !!binary kQI=]
+ - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
+ - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
+...
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+---
+- - ['T1', 'I1', '1 1']
+ - ['T1', 'T1', '1 1']
+ - ['T2', 'T2', '1 1']
+...
+--Cleaning up.
+box.sql.execute("DROP TABLE t1;")
+---
+...
+box.sql.execute("DROP TABLE t2;")
+---
+...
diff --git a/test/sql/sql-statN-index-drop.test.lua b/test/sql/sql-statN-index-drop.test.lua
new file mode 100644
index 0000000..9b919de
--- /dev/null
+++ b/test/sql/sql-statN-index-drop.test.lua
@@ -0,0 +1,54 @@
+test_run = require('test_run').new()
+
+-- Initializing some things.
+box.sql.execute("CREATE TABLE t1(id PRIMARY KEY, a);")
+box.sql.execute("CREATE TABLE t2(id PRIMARY KEY, a);")
+box.sql.execute("CREATE INDEX i1 ON t1(a);")
+box.sql.execute("CREATE INDEX i1 ON t2(a);")
+box.sql.execute("INSERT INTO t1 VALUES(1, 2);")
+box.sql.execute("INSERT INTO t2 VALUES(1, 2);")
+
+-- Analyze.
+box.sql.execute("ANALYZE;")
+
+-- Checking the data.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+
+-- Dropping an index
+box.sql.execute("DROP INDEX i1 ON t1;")
+
+-- Checking the DROP INDEX results.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+
+--Cleaning up.
+box.sql.execute("DROP TABLE t1;")
+box.sql.execute("DROP TABLE t2;")
+
+-- Same test but dropping an INDEX ON t2.
+
+box.sql.execute("CREATE TABLE t1(id PRIMARY KEY, a);")
+box.sql.execute("CREATE TABLE t2(id PRIMARY KEY, a);")
+box.sql.execute("CREATE INDEX i1 ON t1(a);")
+box.sql.execute("CREATE INDEX i1 ON t2(a);")
+box.sql.execute("INSERT INTO t1 VALUES(1, 2);")
+box.sql.execute("INSERT INTO t2 VALUES(1, 2);")
+
+-- Analyze.
+box.sql.execute("ANALYZE;")
+
+-- Checking the data.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+
+-- Dropping an index
+box.sql.execute("DROP INDEX i1 ON t2;")
+
+-- Checking the DROP INDEX results.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+
+--Cleaning up.
+box.sql.execute("DROP TABLE t1;")
+box.sql.execute("DROP TABLE t2;")
--
2.7.4
More information about the Tarantool-patches
mailing list