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 862B42BFF8 for ; Tue, 3 Apr 2018 17:39:29 -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 08Dx77YUqi3m for ; Tue, 3 Apr 2018 17:39:29 -0400 (EDT) Received: from mail-lf0-f65.google.com (mail-lf0-f65.google.com [209.85.215.65]) (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 0980F2BFF7 for ; Tue, 3 Apr 2018 17:39:28 -0400 (EDT) Received: by mail-lf0-f65.google.com with SMTP id v207-v6so22331821lfa.10 for ; Tue, 03 Apr 2018 14:39:28 -0700 (PDT) From: "N.Tatunov" Subject: [tarantool-patches] [PATCH 2/2] sql: statistics removal after dropping an index Date: Wed, 4 Apr 2018 00:37:16 +0300 Message-Id: <1522791436-8221-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 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/tree/N_Tatunov/gh-3264-stat-table-entries-removal Issue: https://github.com/tarantool/tarantool/issues/3264 src/box/sql/build.c | 41 ++++++----- test/sql/sql-statN-index-drop.result | 127 +++++++++++++++++++++++++++++++++ test/sql/sql-statN-index-drop.test.lua | 54 ++++++++++++++ 3 files changed, 206 insertions(+), 16 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..44d7548 100644 --- a/src/box/sql/build.c +++ b/src/box/sql/build.c @@ -2207,25 +2207,34 @@ sqliteViewResetAll(sqlite3 * db) #endif /* SQLITE_OMIT_VIEW */ /* - * Remove entries from the sqlite_statN tables (for N in (1,2,3)) + * Remove entries from the _sql_statN tables (for N in (1, 4)) * after a DROP INDEX or DROP TABLE command. */ static void -sqlite3ClearStatTables(Parse * pParse, /* The parsing context */ - const char *zType, /* "idx" or "tbl" */ - const char *zName /* Name of index or table */ +sql_clear_stat_tables(Parse * pParse, /* The parsing context */ + const char *zType, /* "idx" or "tbl" */ + const char *table_name, /* Name of the table*/ + const char *idx_name /* Name of the index*/ ) { - 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); - } - } + int i, j; + if(strcmp(zType, "idx") == 0) + for(i = 1, j = 1; i <= 2; i++, j++) { + char zTab[24]; + sqlite3_snprintf(sizeof(zTab), zTab, "_sql_stat%d", i * j); + sqlite3NestedParse(pParse, + "DELETE FROM \"%s\" WHERE (\"idx\"=%Q AND " + "\"tbl\"=%Q)", + zTab, idx_name, table_name); + } + else + for(i = 1, j = 1; i <= 2; i++, j++) { + char zTab[24]; + sqlite3_snprintf(sizeof(zTab), zTab, "_sql_stat%d", i * j); + sqlite3NestedParse(pParse, + "DELETE FROM \"%s\" WHERE \"tbl\"=%Q", + zTab, table_name); + } } /* @@ -2415,7 +2424,7 @@ sqlite3DropTable(Parse * pParse, SrcList * pName, int isView, int noErr) */ sqlite3BeginWriteOperation(pParse, 1); - sqlite3ClearStatTables(pParse, "tbl", pTab->zName); + sql_clear_stat_tables(pParse, "tbl", pTab->zName, NULL); sqlite3FkDropTable(pParse, pName, pTab); sqlite3CodeDropTable(pParse, pTab, isView); @@ -3417,7 +3426,7 @@ sqlite3DropIndex(Parse * pParse, SrcList * pName, Token * pName2, int ifExists) * But firstly, delete statistics since schema * changes after DDL. */ - sqlite3ClearStatTables(pParse, "idx", pIndex->zName); + sql_clear_stat_tables(pParse, "idx", pIndex->pTable->zName, pIndex->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..c7e476f --- /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..bf4a752 --- /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