* [tarantool-patches] [PATCH v1 1/1] sql: hold in stat tables space/index id instead of name
@ 2018-08-23 9:51 imeevma
2018-08-27 16:32 ` [tarantool-patches] " Vladislav Shpilevoy
0 siblings, 1 reply; 4+ messages in thread
From: imeevma @ 2018-08-23 9:51 UTC (permalink / raw)
To: tarantool-patches; +Cc: v.shpilevoy
There is no reason to hold in statistic tables names of spaces and
indexes. This patch allows us to save id of space/index instead of
name.
Closes #3242
---
Branch: https://github.com/tarantool/tarantool/tree/imeevma/gh-3242-hold-ids-in-stat-tables
Issue: https://github.com/tarantool/tarantool/issues/3242
src/box/bootstrap.snap | Bin 1818 -> 1822 bytes
src/box/lua/upgrade.lua | 12 ++--
src/box/schema.cc | 8 +--
src/box/sql.c | 8 +--
src/box/sql/analyze.c | 114 ++++++++++---------------------
src/box/sql/build.c | 87 +++++++-----------------
src/box/sql/sqliteInt.h | 10 +--
test/box-py/bootstrap.result | 17 ++---
test/box/access_misc.result | 11 +--
test/box/alter.result | 6 +-
test/sql-tap/analyze1.test.lua | 100 ++++++++++++++-------------
test/sql-tap/analyze4.test.lua | 79 ++++++++++++----------
test/sql-tap/analyze5.test.lua | 11 +--
test/sql-tap/analyze9.test.lua | 115 ++++++++++++--------------------
test/sql-tap/analyzeC.test.lua | 51 ++++++++------
test/sql-tap/analyzeD.test.lua | 24 +++----
test/sql-tap/gh-3350-skip-scan.test.lua | 15 +++--
test/sql/sql-statN-index-drop.result | 72 ++++++++++----------
test/sql/sql-statN-index-drop.test.lua | 16 ++---
test/sql/upgrade.result | 14 ++--
20 files changed, 346 insertions(+), 424 deletions(-)
diff --git a/src/box/bootstrap.snap b/src/box/bootstrap.snap
index 10f77f641b6308209ba01e6449bb22cc35963a9f..8c154e10b90501d05c4960277b7f069402ea4ef8 100644
GIT binary patch
delta 1818
zcmV+#2j%#h4xSE>8Gkr7I4x&kVKX@~H8u)KZgX^DZewLSAT~5JW;rxsG%YYTG-53@
zFk@paI5{#fEo5dkHaR(EWid8nGYVEiY;R+0Iv{&}3JTS_3%bn)r2x(jT%rM`00000
z04TLD{QywqGyqCSpbtsV7^wgNKU@x@6hF~WSKJoNVsSN=q<^xFSEN_$Ag?xMH!P7U
z$x2DAOWX2`l=9d}DArQb)z0Vo0`Kgm*P0giSJBl%@%WTd3gZCU0PO&EG~illlGrul
zog8T*{zkDz!uo#wGoIjUR_40Afw@W0kREf#qBwpYQH2q3Ej6pWxWb}XYLDmKmm+?h
zFpXy>?2F9ag?}}TrStB*_9U#+f?`G}P<OrS#IA3B+_Tk}TBM{K1-O=)u#H`LleSi8
z>EE5-j^xiHvH-57rV%DNQn;Aa2gfh#(pHB$n0qZXX|;OHeE-Xq<XUQWsYPJ+fUv7p
zucc;=3B>6`s8HbdDH2%1QISp|>O@-MQV3EBhoMTf!hgY#Wq;?T_}OCJOQrP6OHdJ)
zEgqg|!LnlofTckJW~}2+m%Dqo+5_QQYTl5rSb2BvR(T#=OU*Lo-Kq}ED{oTe;96=v
z>&5)ao5Xqjdm^hb_dj-xH_P_QoAl0_Y9QOsOA~`@soCl!mhI=Ich=HNl24c6%d1tJ
z1=muu?0?Ube7X$1v(_jW>Jn^Bo_RI|^q|gOt<oS^^74r4%z=(U4@#wA;96>G{NgyS
z#1HnZ4}WLhxbqVT>+G8CXMNY%Mt<kCCHhOB=N_)g6!`i4;xKM|dA1ru0Pl@Y?Al{k
zV_`8oyDrDi1I5o_PG^?>nv(x8$-P>YM+Hxcs(*t*XGf)@I-xQVtJzcunjHE#?_
zYJ^FINo7ivLX=P?5H=^993dN=8dFW_T51Z_eCTds9f`(6jHbg3hZ&ohnij_`3L9iJ
z$YhYg5OX2MLQH4vh^EGbsMJw~(M+u{6t1Nvj0}wlRr%5<>#}y_e|H*vsYOE_&4g>I
znST?<j|hWg>FE*IUwqKhBd+yz>^k@U@IjB~oc@hT94O}2x1{TgcjtQiydt3orGgT!
zrRK&W{zw<*HBi(upVzaYQ;?X>X+U6}iK{*{b=<ip`f!h^0vUnd@qYa*k7p;jC>2M9
zYiT(k%Ji>Np->@6D&eS7r0*-8NUIa7M1MN1PO32Hgu;-e#yTBbVMz6Kt`OT3L<Pk3
z!^TI~V}?hK*>Np3Ele#st&Xb4+&^80H(O<0Do2+tU9MCd*HUvtpYMNVUEXe7OHBZl
zR#}&_v7apYSu51|vPk~U)l1{Wanrsmu1d02t*k4}(AfpzvsM|`QnLgrY>_Z7?ti>s
z@pY)-<9}c>dWn4T^RL!xiV3$kiNviBcVs^=X`O$`IxCfRr5M*zb0-IkSsg>2Z}#)@
z<_2h7NvT!V)xfCWi)*PF1A79*kOXlN1HyBwEpV{tSrLI?sNqm#kQ$f)0003{0PzI}
zG3ROq5`eHkilZ=)VHgNQ0F2@iXMcbI6odpc0K|d;rr*dQx^*U_Bzr#n<)y&;F_M=;
zFoMp^t#2x^I37}L(bsLx`@$~}AdrPHB>kiRW+f4qKidRnmxUY^#Or2vXTh`~`7^uG
zJ6ca>B1v&(&7#p?H+4WQ<3VdlgeNAA84yzy;2<4_fPn8i<|@Vf1lZ$xo_~{%8Jh?h
zcmh>co3J=8GQgKe23gRDuN29xfzCK@N3hecLk}0}X2D$%J=Myp=#n-&?gY6qk8Ue!
zXMm_bj>ytL{c%K=2I@U*M|4}GfgDeuH`Wv9wgYL13P?H(3&A-_N{GN%xe50j@a=(?
zm`^;gz=k>e@!z)c<&KaHmwyj;gk*e7m#J-py@xR{f567O&;?l|I6aevHfyL~?2Vb?
z0N-}Q$7ns6lSSxTYhA*b*6ZsPDJ`8*v;SjhVB65~SiE?Po|3Fu9S9dz<a|tKExMHn
z+K0c41-@LOu}$gx1Ma;xT50<hfu*cc0f(!zgPM(<(0u_OT?Ib}{C_heRdgR|@y9RO
z2$M9Ow{E|N{#0j@yzR7rqd%%XWO;GT0Wo@Ze-Iim&R}a}Sc{kl3(LnS7s=uCup36)
zr@Oz<7Q(|9>TCgt{H0G}GnqNo$Pt8_(;~$pAH@+4M;2Yn&ZytyH+w%kZ3dJx-72hA
zToD(n1?IAGhHY!h&VQiFIcPBk1lv&Ep`x52x3zmcVcBq#asUA-iEqUOcMs~D!-be5
zY~XCdb)%WlNvjYZny3@$S{uD4*j@&nCa+AJzxg<qj~K+Fy+kL%NiLvDKS=sOu%pz^
zRVR4EKbjD_6e<e_;lJL{`r5^vsT9{^2MtuM-t(+T|J$rHh!bzavR_9+@wH!162>hD
I)ex=i3bKK2F8}}l
delta 1814
zcmV+x2kH2p4w?>-8Gko6FfC^}WiT^1V=`hg3Q2BrbYX5|WjY{XGdMLeGBIK;GG%39
zEi_?cGc95_Ff=VNHDzUCVPj%qVPP~1RzqxWV{1AfdwmKD)w&D1%?6(U&U2<Mm8Adx
z0000ewJ-euP_;Aw`bUTlNzfRn003Wngcn>16PR8tG{9G2A%B*nvW-`ySL`6KHf6VV
z%amlLB-W*Se@Rl?3cz(5$a~6rR6)dCAE{j~8m?KWsS!9-rj!EW0N4QR0HXn2ouqhO
zG~UIq23{P3HZj)s>z}avUb8aSBks#hfP{3I`vt)e+adZQpsSOl@}TO1;HWLnL_dP}
zRmSwCk+CmAdw<u}HI~l1bK8rtUIUUDnXvA9S9x9E!rb1d3oTOOjRJIalCgbVd5gDJ
zXX)Ra<Bnu(hbn-sPSOaI94TDT=zk&3dc08~@1^VNBqyyp%zXdrmZYnb+(L`KY+=`B
zqg|aO4uNx8;goPzs?;T^N;fEy3H6;wClL6ORH~d*DSwq?!jNTu=Oc)3GS7w1X~hL-
z1UI4uhULmm6##Vt1^r?@?rw9m2SQgTi9^C-<=rig@;vD3BuAOWQJt4p-eSr@S10+b
z2lFd$F=sn|BC3$+KkWK$*6o$IXiJJ}Fx$^Z6N9c!vem~s+s{W^(vnLOtjE>M+Gx#!
zu1<37&wrC(J+5p?X%q-~3HBAsOcR3rr?a)u83apSc8JOx==ZZvC<Oyuous}GhM@|?
zv2Wpv%f4^tCG6_#qU~pW*ZC%X=d~sJN1x|5M`H^7e17p5zrE~@!VtK7-}1V4xU#V>
zxGK9ILu^0r^NL7kmVTR($5-UmMgu%4s16F9oqv>0>O{(f%vMn)CW=H?C)vr6phlQP
zm{KNGDMSfGDW~RCgA-J9Lt~;Tb#;<KG#@-0nMa!OAe-qR!$GD-hNi`Di^7H&4KW#F
zFu+`Zu>j-MI+~%fpeb}zVI)K83x%#u@+D@<f~frHll5pj^1pj+y3isaZ)QSQCrJc`
zcz@`A;%If4>ks~Cb(m}6eqH6>zxvPeOr(EbG53MFg_m@l@9tcO*cMsYC-jrh)k*FP
zUM#vUw|$_d`MjnHor1u0UW0v^2C6#E)Nkh&^x<}h0vUbZ?|%I(%d-<AC=^G8u1-=G
zr+ZZ@eD8lwiF7K3P?2!(J(2GJ4M}CXxqrz);am(^sLa#36^2l&(+R0PK~g|WKWcn<
zJ!E)Lm>pf6WPPDUWz|7-m^;?v3RmgOL*>}AWs4PxqpOn~&FA}HS&z6IU7aL=I_b<q
z+1M|RyrdOs;0%htiD*aT!Eo1b234bIqjlz$X6WqtZb_+(u1>N9Yt<5C9Nc-q0)Ka?
z;p2Z|GI~jT@$;|NON!~XI7z%MoIA3gkG0N!WR-Nzyi$y=PI4y)3>pnrI@|2$qm2zL
zR7Ih5=Fz~Q-;1v&fDB0x7cw9`INAaSi=GvfM@4~PsPRx_5E_^P0003{0PzI}Dd&m?
z5`eHkilZ=&ffxuw5R3v7XMg|{gntAYpjt4r-^d`k=SQL>dmg;ae~0m7<UBfHoSn_K
zW}>9xd#qxM5Nd0_7-fOLf-Hg|p+A-mn55wKvv;8EU;I(IcxrT)6(wzmc{T;w?qGdP
zTaN0>nGc)5E7^iUAvGRX<z<nP8QJ3izfE%3nmK&QVXZ0lU%+mxCy?xH=zm0L;DbzK
zBdUr6k|?W07-Xp$x~_fNssjNe_k>Nnjt#DUWdUCxdJ1$^m;2T_PG&hZj}9ZMcJ0s+
z$6(bU-x!;Xoq7mcLzq=5m17&AOY6yY7zyl{10)9wC&0O|Bsrn2+@PwWly^gmxMmNM
zs3B#;9|#+_><z<yiEs}n&40miuWb44r&<cqrb!DT`c-HIcpuL`H;nohiGoY;;nU9W
zV;%@VnAP%2cs#=GmV4zDDJz{(vo>RCT+@$wC=lv6OKH}l%!TV(@N}#u6<JC@$HH^M
zp;+DKXuIt12KGYIthD(ob9r2vkYYpJ*}-RH$GW4N=W;MX!aws;MSmw_eEgXk`N1SY
z&qJ4AlRq^uNg{TN@P#i@A5&R(9n>*Gp$x8Oj4Lg+G29w6yJ1tg<_I|l6c&ky|77<U
zY$1I9Lds&0&|UhJwv}mR4IL#~J}put_$baD;OJFrvqO!6zFFFnZZlw<>EbPAaK))4
z7C?-~nY69poEiC?9)EiVG<UY#L2u5Gv%PRU$zpI8LxBPFia(fw&lmMK%EE&bwsSjS
zrD$U0)2b+krqr2iqm5q4X)gn}$*Wh_|6fkXM-Hz@{-l?15*4WSCy9Tp-j(_zU32|m
zA031OL(7uk=2!kyUv+%KuxwUl2W4GN9eS#ye{j~d<WR!`t`8&9@KvOqBn43p)ex=i
E3T$>{0{{R3
diff --git a/src/box/lua/upgrade.lua b/src/box/lua/upgrade.lua
index 8a30e9f..0d5391e 100644
--- a/src/box/lua/upgrade.lua
+++ b/src/box/lua/upgrade.lua
@@ -482,11 +482,11 @@ local function upgrade_to_2_1_0()
_index:insert{_trigger.id, 1, 'space_id', 'tree', { unique = false },
{{1, 'unsigned'}}}
- local stat1_ft = {{name='tbl', type='string'},
- {name='idx', type='string'},
+ local stat1_ft = {{name='tbl', type='unsigned'},
+ {name='idx', type='unsigned'},
{name='stat', type='string'}}
- local stat4_ft = {{name='tbl', type='string'},
- {name='idx', type='string'},
+ local stat4_ft = {{name='tbl', type='unsigned'},
+ {name='idx', type='unsigned'},
{name='neq', type='string'},
{name='nlt', type='string'},
{name='ndlt', type='string'},
@@ -498,7 +498,7 @@ local function upgrade_to_2_1_0()
log.info("create index primary on _sql_stat1")
_index:insert{box.schema.SQL_STAT1_ID, 0, 'primary', 'tree',
- {unique = true}, {{0, 'string'}, {1, 'string'}}}
+ {unique = true}, {{0, 'unsigned'}, {1, 'unsigned'}}}
log.info("create space _sql_stat4")
_space:insert{box.schema.SQL_STAT4_ID, ADMIN, '_sql_stat4', 'memtx', 0,
@@ -506,7 +506,7 @@ local function upgrade_to_2_1_0()
log.info("create index primary on _sql_stat4")
_index:insert{box.schema.SQL_STAT4_ID, 0, 'primary', 'tree',
- {unique = true}, {{0, 'string'}, {1, 'string'},
+ {unique = true}, {{0, 'unsigned'}, {1, 'unsigned'},
{5, 'scalar'}}}
local fk_constr_ft = {{name='name', type='string'},
diff --git a/src/box/schema.cc b/src/box/schema.cc
index aa4ac32..2c6c280 100644
--- a/src/box/schema.cc
+++ b/src/box/schema.cc
@@ -384,11 +384,11 @@ schema_init()
/* space name */
key_def_set_part(key_def, 0 /* part no */, 0 /* field no */,
- FIELD_TYPE_STRING, ON_CONFLICT_ACTION_DEFAULT, NULL,
+ FIELD_TYPE_UNSIGNED, ON_CONFLICT_ACTION_DEFAULT, NULL,
COLL_NONE, SORT_ORDER_ASC);
/* index name */
key_def_set_part(key_def, 1 /* part no */, 1 /* field no */,
- FIELD_TYPE_STRING, ON_CONFLICT_ACTION_DEFAULT, NULL,
+ FIELD_TYPE_UNSIGNED, ON_CONFLICT_ACTION_DEFAULT, NULL,
COLL_NONE, SORT_ORDER_ASC);
/* _sql_stat1 - a simpler statistics on space, seen in SQL. */
sc_space_new(BOX_SQL_STAT1_ID, "_sql_stat1", key_def, NULL, NULL);
@@ -400,11 +400,11 @@ schema_init()
/* space name */
key_def_set_part(key_def, 0 /* part no */, 0 /* field no */,
- FIELD_TYPE_STRING, ON_CONFLICT_ACTION_DEFAULT, NULL,
+ FIELD_TYPE_UNSIGNED, ON_CONFLICT_ACTION_DEFAULT, NULL,
COLL_NONE, SORT_ORDER_ASC);
/* index name */
key_def_set_part(key_def, 1 /* part no */, 1 /* field no */,
- FIELD_TYPE_STRING, ON_CONFLICT_ACTION_DEFAULT, NULL,
+ FIELD_TYPE_UNSIGNED, ON_CONFLICT_ACTION_DEFAULT, NULL,
COLL_NONE, SORT_ORDER_ASC);
/* sample */
key_def_set_part(key_def, 2 /* part no */, 5 /* field no */,
diff --git a/src/box/sql.c b/src/box/sql.c
index ae12cae..e5411f2 100644
--- a/src/box/sql.c
+++ b/src/box/sql.c
@@ -1148,16 +1148,16 @@ void tarantoolSqlite3LoadSchema(struct init_data *init)
sql_init_callback(init, TARANTOOL_SYS_SQL_STAT1_NAME,
BOX_SQL_STAT1_ID, 0,
"CREATE TABLE \""TARANTOOL_SYS_SQL_STAT1_NAME
- "\"(\"tbl\" text,"
- "\"idx\" text,"
+ "\"(\"tbl\" INT,"
+ "\"idx\" INT,"
"\"stat\" not null,"
"PRIMARY KEY(\"tbl\", \"idx\"))");
sql_init_callback(init, TARANTOOL_SYS_SQL_STAT4_NAME,
BOX_SQL_STAT4_ID, 0,
"CREATE TABLE \""TARANTOOL_SYS_SQL_STAT4_NAME
- "\"(\"tbl\" text,"
- "\"idx\" text,"
+ "\"(\"tbl\" INT,"
+ "\"idx\" INT,"
"\"neq\" text,"
"\"nlt\" text,"
"\"ndlt\" text,"
diff --git a/src/box/sql/analyze.c b/src/box/sql/analyze.c
index 00d96d2..86776ad 100644
--- a/src/box/sql/analyze.c
+++ b/src/box/sql/analyze.c
@@ -124,28 +124,27 @@
* @param parse Parsing context.
* @param stat_cursor Open the _sql_stat1 table on this cursor.
* you should allocate |stat_names| cursors before call.
- * @param table_name Delete records of this index if specified.
+ * @param table_id Delete records of this table if id is not 0.
*/
static void
-vdbe_emit_stat_space_open(struct Parse *parse, int stat_cursor,
- const char *table_name)
+vdbe_emit_stat_space_open(struct Parse *parse, int stat_cursor, int table_id)
{
- const char *stat_names[] = {"_sql_stat1", "_sql_stat4"};
+ const char *stat_names[] = {TARANTOOL_SYS_SQL_STAT1_NAME,
+ TARANTOOL_SYS_SQL_STAT4_NAME};
const uint32_t stat_ids[] = {BOX_SQL_STAT1_ID, BOX_SQL_STAT4_ID};
struct Vdbe *v = sqlite3GetVdbe(parse);
assert(v != NULL);
assert(sqlite3VdbeDb(v) == parse->db);
for (uint i = 0; i < lengthof(stat_names); ++i) {
- const char *space_name = stat_names[i];
/*
* The table already exists, because it is a
* system space.
*/
assert(sqlite3HashFind(&parse->db->pSchema->tblHash,
- space_name) != NULL);
- if (table_name != NULL) {
- vdbe_emit_stat_space_clear(parse, space_name, NULL,
- table_name);
+ stat_names[i]) != NULL);
+ if (table_id != 0) {
+ sql_remove_from_stat(parse, stat_names[i], table_id, 0,
+ false);
} else {
sqlite3VdbeAddOp1(v, OP_Clear, stat_ids[i]);
}
@@ -818,7 +817,7 @@ analyzeOneTable(Parse * pParse, /* Parser context */
iIdxCur = iTab++;
pParse->nTab = MAX(pParse->nTab, iTab);
sqlite3OpenTable(pParse, iTabCur, pTab, OP_OpenRead);
- sqlite3VdbeLoadString(v, regTabname, pTab->def->name);
+ sqlite3VdbeAddOp2(v, OP_Integer, pTab->def->id, regTabname);
for (pIdx = pTab->pIndex; pIdx; pIdx = pIdx->pNext) {
int addrRewind; /* Address of "OP_Rewind iIdxCur" */
@@ -837,8 +836,8 @@ analyzeOneTable(Parse * pParse, /* Parser context */
idx_name = pIdx->def->name;
int part_count = pIdx->def->key_def->part_count;
- /* Populate the register containing the index name. */
- sqlite3VdbeLoadString(v, regIdxname, idx_name);
+ /* Populate the register containing the index id. */
+ sqlite3VdbeAddOp2(v, OP_Integer, pIdx->def->iid, regIdxname);
VdbeComment((v, "Analysis for %s.%s", pTab->def->name,
idx_name));
@@ -1015,9 +1014,10 @@ analyzeOneTable(Parse * pParse, /* Parser context */
/* Add the entry to the stat1 table. */
callStatGet(v, regStat4, STAT_GET_STAT1, regStat1);
- assert("BBB"[0] == AFFINITY_TEXT);
+ assert("DDB"[0] == AFFINITY_INTEGER);
+ assert("BBB"[2] == AFFINITY_TEXT);
sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp,
- "BBB", 0);
+ "DDB", 0);
sqlite3VdbeAddOp2(v, OP_IdxInsert, iStatCur, regTemp);
/* Add the entries to the stat4 table. */
@@ -1086,7 +1086,7 @@ sql_analyze_database(Parse *parser)
sql_set_multi_write(parser, false);
int stat_cursor = parser->nTab;
parser->nTab += 3;
- vdbe_emit_stat_space_open(parser, stat_cursor, NULL);
+ vdbe_emit_stat_space_open(parser, stat_cursor, 0);
int reg = parser->nMem + 1;
int tab_cursor = parser->nTab;
for (struct HashElem *k = sqliteHashFirst(&schema->tblHash); k != NULL;
@@ -1114,7 +1114,7 @@ vdbe_emit_analyze_table(struct Parse *parse, struct Table *table)
sql_set_multi_write(parse, false);
int stat_cursor = parse->nTab;
parse->nTab += 3;
- vdbe_emit_stat_space_open(parse, stat_cursor, table->def->name);
+ vdbe_emit_stat_space_open(parse, stat_cursor, table->def->id);
analyzeOneTable(parse, table, NULL, stat_cursor, parse->nMem + 1,
parse->nTab);
loadAnalysis(parse);
@@ -1246,24 +1246,14 @@ analysis_loader(void *data, int argc, char **argv, char **unused)
struct analysis_index_info *info = (struct analysis_index_info *) data;
assert(info->stats != NULL);
struct index_stat *stat = &info->stats[info->index_count++];
- uint32_t space_id = box_space_id_by_name(argv[0], strlen(argv[0]));
+ uint32_t space_id = atoll(argv[0]);
if (space_id == BOX_ID_NIL)
return -1;
struct space *space = space_by_id(space_id);
assert(space != NULL);
struct index *index;
- uint32_t iid = box_index_id_by_name(space_id, argv[1], strlen(argv[1]));
- /*
- * Convention is if index's name matches with space's
- * one, then it is primary index.
- */
- if (iid != BOX_ID_NIL) {
- index = space_index(space, iid);
- } else {
- if (sqlite3_stricmp(argv[0], argv[1]) != 0)
- return -1;
- index = space_index(space, 0);
- }
+ uint32_t iid = atoll(argv[1]);
+ index = space_index(space, iid);
assert(index != NULL);
/*
* Additional field is used to describe total
@@ -1410,27 +1400,17 @@ load_stat_from_space(struct sqlite3 *db, const char *sql_select_prepare,
goto finalize;
uint32_t current_idx_count = 0;
while (sqlite3_step(stmt) == SQLITE_ROW) {
- const char *space_name = (char *)sqlite3_column_text(stmt, 0);
- if (space_name == NULL)
- continue;
- const char *index_name = (char *)sqlite3_column_text(stmt, 1);
- if (index_name == NULL)
+ if (sqlite3_column_text(stmt, 0) == NULL ||
+ sqlite3_column_text(stmt, 1) == NULL)
continue;
- uint32_t sample_count = sqlite3_column_int(stmt, 2);
- uint32_t space_id = box_space_id_by_name(space_name,
- strlen(space_name));
+ uint32_t space_id = sqlite3_column_int(stmt, 0);
assert(space_id != BOX_ID_NIL);
struct space *space = space_by_id(space_id);
assert(space != NULL);
- struct index *index;
- uint32_t iid = box_index_id_by_name(space_id, index_name,
- strlen(index_name));
- if (sqlite3_stricmp(space_name, index_name) == 0 &&
- iid == BOX_ID_NIL)
- index = space_index(space, 0);
- else
- index = space_index(space, iid);
+ uint32_t iid = sqlite3_column_int(stmt, 1);
+ struct index *index = space_index(space, iid);
assert(index != NULL);
+ uint32_t sample_count = sqlite3_column_int(stmt, 2);
uint32_t column_count = index->def->key_def->part_count;
struct index_stat *stat = &stats[current_idx_count];
stat->sample_field_count = column_count;
@@ -1482,27 +1462,15 @@ load_stat_from_space(struct sqlite3 *db, const char *sql_select_prepare,
struct index *prev_index = NULL;
current_idx_count = 0;
while (sqlite3_step(stmt) == SQLITE_ROW) {
- const char *space_name = (char *)sqlite3_column_text(stmt, 0);
- if (space_name == NULL)
- continue;
- const char *index_name = (char *)sqlite3_column_text(stmt, 1);
- if (index_name == NULL)
+ if (sqlite3_column_text(stmt, 0) == NULL ||
+ sqlite3_column_text(stmt, 1) == NULL)
continue;
- uint32_t space_id = box_space_id_by_name(space_name,
- strlen(space_name));
+ uint32_t space_id = sqlite3_column_int(stmt, 0);
assert(space_id != BOX_ID_NIL);
struct space *space = space_by_id(space_id);
assert(space != NULL);
- struct index *index;
- uint32_t iid = box_index_id_by_name(space_id, index_name,
- strlen(index_name));
- if (iid != BOX_ID_NIL) {
- index = space_index(space, iid);
- } else {
- if (sqlite3_stricmp(space_name, index_name) != 0)
- return -1;
- index = space_index(space, 0);
- }
+ uint32_t iid = sqlite3_column_int(stmt, 1);
+ struct index *index = space_index(space, iid);
assert(index != NULL);
uint32_t column_count = index->def->key_def->part_count;
if (index != prev_index) {
@@ -1566,28 +1534,16 @@ load_stat_to_index(struct sqlite3 *db, const char *sql_select_load,
return -1;
uint32_t current_idx_count = 0;
while (sqlite3_step(stmt) == SQLITE_ROW) {
- const char *space_name = (char *)sqlite3_column_text(stmt, 0);
- if (space_name == NULL)
+ if (sqlite3_column_text(stmt, 0) == NULL ||
+ sqlite3_column_text(stmt, 1) == NULL)
continue;
- const char *index_name = (char *)sqlite3_column_text(stmt, 1);
- if (index_name == NULL)
- continue;
- uint32_t space_id = box_space_id_by_name(space_name,
- strlen(space_name));
+ uint32_t space_id = sqlite3_column_int(stmt, 0);
if (space_id == BOX_ID_NIL)
return -1;
struct space *space = space_by_id(space_id);
assert(space != NULL);
- struct index *index;
- uint32_t iid = box_index_id_by_name(space_id, index_name,
- strlen(index_name));
- if (iid != BOX_ID_NIL) {
- index = space_index(space, iid);
- } else {
- if (sqlite3_stricmp(space_name, index_name) != 0)
- return -1;
- index = space_index(space, 0);
- }
+ uint32_t iid = sqlite3_column_int(stmt, 1);
+ struct index *index = space_index(space, iid);
assert(index != NULL);
free(index->def->opts.stat);
index->def->opts.stat = stats[current_idx_count++];
diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index dddeb12..0d21ff0 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -1962,75 +1962,27 @@ sql_store_select(struct Parse *parse_context, struct Select *select)
parse_context->parsed_ast.select = select_copy;
}
-/**
- * Create expression record "@col_name = '@col_value'".
- *
- * @param parse The parsing context.
- * @param col_name Name of column.
- * @param col_value Name of row.
- * @retval not NULL on success.
- * @retval NULL on failure.
- */
-static struct Expr *
-sql_id_eq_str_expr(struct Parse *parse, const char *col_name,
- const char *col_value)
-{
- struct sqlite3 *db = parse->db;
-
- struct Expr *col_name_expr = sqlite3Expr(db, TK_ID, col_name);
- if (col_name_expr == NULL)
- return NULL;
- struct Expr *col_value_expr = sqlite3Expr(db, TK_STRING, col_value);
- if (col_value_expr == NULL) {
- sql_expr_delete(db, col_name_expr, false);
- return NULL;
- }
- return sqlite3PExpr(parse, TK_EQ, col_name_expr, col_value_expr);
-}
-
void
-vdbe_emit_stat_space_clear(struct Parse *parse, const char *stat_table_name,
- const char *idx_name, const char *table_name)
+sql_remove_from_stat(struct Parse *parse, const char *stat_table_name,
+ int table_id, int index_id, bool is_index_received)
{
- assert(idx_name != NULL || table_name != NULL);
struct sqlite3 *db = parse->db;
assert(!db->mallocFailed);
struct SrcList *src_list = sql_alloc_src_list(db);
if (src_list != NULL)
src_list->a[0].zName = sqlite3DbStrDup(db, stat_table_name);
- struct Expr *where = NULL;
- if (idx_name != NULL) {
- struct Expr *expr = sql_id_eq_str_expr(parse, "idx", idx_name);
- if (expr != NULL)
- where = sqlite3ExprAnd(db, expr, where);
- }
- if (table_name != NULL) {
- struct Expr *expr = sql_id_eq_str_expr(parse, "tbl", table_name);
- if (expr != NULL)
- where = sqlite3ExprAnd(db, expr, where);
- }
- /**
- * On memory allocation error sql_table delete_from
- * releases memory for its own.
- */
- sql_table_delete_from(parse, src_list, where);
-}
-
-/**
- * Remove entries from the _sql_stat1 and _sql_stat4
- * system spaces after a DROP INDEX or DROP TABLE command.
- *
- * @param parse The parsing context.
- * @param table_name The table to be dropped or
- * the table that contains index to be dropped.
- * @param idx_name Index to be dropped.
- */
-static void
-sql_clear_stat_spaces(struct Parse *parse, const char *table_name,
- const char *idx_name)
-{
- vdbe_emit_stat_space_clear(parse, "_sql_stat4", idx_name, table_name);
- vdbe_emit_stat_space_clear(parse, "_sql_stat1", idx_name, table_name);
+ struct Expr *expr = NULL;
+ struct Expr *col = sqlite3Expr(db, TK_ID, "tbl");
+ struct Expr *val = sqlite3ExprInteger(db, table_id);
+ if (col != NULL && col != NULL)
+ expr = sqlite3PExpr(parse, TK_EQ, col, val);
+ if (is_index_received && expr != NULL) {
+ col = sqlite3Expr(db, TK_ID, "idx");
+ val = sqlite3ExprInteger(db, index_id);
+ expr = sqlite3ExprAnd(db, sqlite3PExpr(parse, TK_EQ, col, val),
+ expr);
+ }
+ sql_table_delete_from(parse, src_list, expr);
}
/**
@@ -2251,7 +2203,10 @@ sql_drop_table(struct Parse *parse_context, struct SrcList *table_name_list,
goto exit_drop_table;
}
}
- sql_clear_stat_spaces(parse_context, space_name, NULL);
+ sql_remove_from_stat(parse_context, TARANTOOL_SYS_SQL_STAT1_NAME,
+ space->def->id, 0, false);
+ sql_remove_from_stat(parse_context, TARANTOOL_SYS_SQL_STAT4_NAME,
+ space->def->id, 0, false);
sql_code_drop_table(parse_context, space, is_view);
exit_drop_table:
@@ -3234,7 +3189,11 @@ sql_drop_index(struct Parse *parse_context, struct SrcList *index_name_list,
* But firstly, delete statistics since schema
* changes after DDL.
*/
- sql_clear_stat_spaces(parse_context, table_name, index->def->name);
+ sql_remove_from_stat(parse_context, TARANTOOL_SYS_SQL_STAT1_NAME,
+ space->def->id, index->def->iid, true);
+ sql_remove_from_stat(parse_context, TARANTOOL_SYS_SQL_STAT4_NAME,
+ space->def->id, index->def->iid, true);
+
int record_reg = ++parse_context->nMem;
int space_id_reg = ++parse_context->nMem;
sqlite3VdbeAddOp2(v, OP_Integer, space_id, space_id_reg);
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index b1f2f26..4ac1dae 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -4864,11 +4864,13 @@ vdbe_emit_halt_with_presence_test(struct Parse *parser, int space_id,
*
* @param parse The parsing context.
* @param stat_table_name System stat table name.
- * @param idx_name Index name.
- * @param table_name Table name.
+ * @param table_id Id of table of which analysis will be deleted.
+ * @param index_id Id of index of which analysis will be deleted.
+ * Used only if is_index_received is true.
+ * @param is_index_received True if index_id is given.
*/
void
-vdbe_emit_stat_space_clear(struct Parse *parse, const char *stat_table_name,
- const char *idx_name, const char *table_name);
+sql_remove_from_stat(struct Parse *parse, const char *stat_table_name,
+ int table_id, int index_id, bool is_index_received);
#endif /* SQLITEINT_H */
diff --git a/test/box-py/bootstrap.result b/test/box-py/bootstrap.result
index fb7d7da..a7da8c1 100644
--- a/test/box-py/bootstrap.result
+++ b/test/box-py/bootstrap.result
@@ -71,11 +71,12 @@ box.space._space:select{}
'type': 'unsigned'}]]
- [340, 1, '_space_sequence', 'memtx', 0, {}, [{'name': 'id', 'type': 'unsigned'},
{'name': 'sequence_id', 'type': 'unsigned'}, {'name': 'is_generated', 'type': 'boolean'}]]
- - [348, 1, '_sql_stat1', 'memtx', 0, {}, [{'name': 'tbl', 'type': 'string'}, {'name': 'idx',
- 'type': 'string'}, {'name': 'stat', 'type': 'string'}]]
- - [349, 1, '_sql_stat4', 'memtx', 0, {}, [{'name': 'tbl', 'type': 'string'}, {'name': 'idx',
- 'type': 'string'}, {'name': 'neq', 'type': 'string'}, {'name': 'nlt', 'type': 'string'},
- {'name': 'ndlt', 'type': 'string'}, {'name': 'sample', 'type': 'scalar'}]]
+ - [348, 1, '_sql_stat1', 'memtx', 0, {}, [{'name': 'tbl', 'type': 'unsigned'}, {
+ 'name': 'idx', 'type': 'unsigned'}, {'name': 'stat', 'type': 'string'}]]
+ - [349, 1, '_sql_stat4', 'memtx', 0, {}, [{'name': 'tbl', 'type': 'unsigned'}, {
+ 'name': 'idx', 'type': 'unsigned'}, {'name': 'neq', 'type': 'string'}, {'name': 'nlt',
+ 'type': 'string'}, {'name': 'ndlt', 'type': 'string'}, {'name': 'sample',
+ 'type': 'scalar'}]]
- [356, 1, '_fk_constraint', 'memtx', 0, {}, [{'name': 'name', 'type': 'string'},
{'name': 'child_id', 'type': 'unsigned'}, {'name': 'parent_id', 'type': 'unsigned'},
{'name': 'is_deferred', 'type': 'boolean'}, {'name': 'match', 'type': 'string'},
@@ -131,9 +132,9 @@ box.space._index:select{}
- [330, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned']]]
- [340, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned']]]
- [340, 1, 'sequence', 'tree', {'unique': false}, [[1, 'unsigned']]]
- - [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'string']]]
- - [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'string'], [
- 5, 'scalar']]]
+ - [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1, 'unsigned']]]
+ - [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1, 'unsigned'],
+ [5, 'scalar']]]
- [356, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'unsigned']]]
- [356, 1, 'child_id', 'tree', {'unique': false}, [[1, 'unsigned']]]
...
diff --git a/test/box/access_misc.result b/test/box/access_misc.result
index 3ba33ee..622409c 100644
--- a/test/box/access_misc.result
+++ b/test/box/access_misc.result
@@ -810,11 +810,12 @@ box.space._space:select()
'type': 'unsigned'}]]
- [340, 1, '_space_sequence', 'memtx', 0, {}, [{'name': 'id', 'type': 'unsigned'},
{'name': 'sequence_id', 'type': 'unsigned'}, {'name': 'is_generated', 'type': 'boolean'}]]
- - [348, 1, '_sql_stat1', 'memtx', 0, {}, [{'name': 'tbl', 'type': 'string'}, {'name': 'idx',
- 'type': 'string'}, {'name': 'stat', 'type': 'string'}]]
- - [349, 1, '_sql_stat4', 'memtx', 0, {}, [{'name': 'tbl', 'type': 'string'}, {'name': 'idx',
- 'type': 'string'}, {'name': 'neq', 'type': 'string'}, {'name': 'nlt', 'type': 'string'},
- {'name': 'ndlt', 'type': 'string'}, {'name': 'sample', 'type': 'scalar'}]]
+ - [348, 1, '_sql_stat1', 'memtx', 0, {}, [{'name': 'tbl', 'type': 'unsigned'}, {
+ 'name': 'idx', 'type': 'unsigned'}, {'name': 'stat', 'type': 'string'}]]
+ - [349, 1, '_sql_stat4', 'memtx', 0, {}, [{'name': 'tbl', 'type': 'unsigned'}, {
+ 'name': 'idx', 'type': 'unsigned'}, {'name': 'neq', 'type': 'string'}, {'name': 'nlt',
+ 'type': 'string'}, {'name': 'ndlt', 'type': 'string'}, {'name': 'sample',
+ 'type': 'scalar'}]]
- [356, 1, '_fk_constraint', 'memtx', 0, {}, [{'name': 'name', 'type': 'string'},
{'name': 'child_id', 'type': 'unsigned'}, {'name': 'parent_id', 'type': 'unsigned'},
{'name': 'is_deferred', 'type': 'boolean'}, {'name': 'match', 'type': 'string'},
diff --git a/test/box/alter.result b/test/box/alter.result
index 0d50855..4e14ca4 100644
--- a/test/box/alter.result
+++ b/test/box/alter.result
@@ -228,9 +228,9 @@ _index:select{}
- [330, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned']]]
- [340, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned']]]
- [340, 1, 'sequence', 'tree', {'unique': false}, [[1, 'unsigned']]]
- - [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'string']]]
- - [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'string'], [
- 5, 'scalar']]]
+ - [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1, 'unsigned']]]
+ - [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1, 'unsigned'],
+ [5, 'scalar']]]
- [356, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'unsigned']]]
- [356, 1, 'child_id', 'tree', {'unique': false}, [[1, 'unsigned']]]
...
diff --git a/test/sql-tap/analyze1.test.lua b/test/sql-tap/analyze1.test.lua
index dab7255..27465a4 100755
--- a/test/sql-tap/analyze1.test.lua
+++ b/test/sql-tap/analyze1.test.lua
@@ -160,7 +160,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.1>
- "T1", "2 1", "T1I1", "2 2", "T1I2", "2 1", "T1I3", "2 2 1"
+ 0, "2 1", 1, "2 2", 2, "2 1", 3, "2 2 1"
-- </analyze-3.1>
})
@@ -173,7 +173,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.2>
- "T1", "4 1", "T1I1", "4 4", "T1I2", "4 1", "T1I3", "4 4 1"
+ 0, "4 1", 1, "4 4", 2, "4 1", 3, "4 4 1"
-- </analyze-3.2>
})
@@ -185,7 +185,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.3>
- "T1","5 1", "T1I1", "5 3", "T1I2", "5 2", "T1I3", "5 3 1"
+ 0,"5 1", 1, "5 3", 2, "5 2", 3, "5 3 1"
-- </analyze-3.3>
})
@@ -201,7 +201,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.4>
- "T1","5 1","T1I1","5 3","T1I2","5 2","T1I3","5 3 1","T2","5 1","T2I1","5 3","T2I2","5 2","T2I3","5 3 1"
+ 0,"5 1",0,"5 1",1,"5 3",1,"5 3",2,"5 2",2,"5 2",3,"5 3 1",3,"5 3 1"
-- </analyze-3.4>
})
@@ -213,7 +213,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.5>
- "T1","5 1","T1I1","5 3","T1I2","5 2","T1I3","5 3 1","T2","5 1","T2I1","5 3","T2I2","5 2"
+ 0,"5 1",0,"5 1",1,"5 3",1,"5 3",2,"5 2",2,"5 2",3,"5 3 1"
-- </analyze-3.5>
})
@@ -224,7 +224,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.6>
- "T1","5 1","T1I1","5 3","T1I2","5 2","T1I3","5 3 1","T2","5 1","T2I1","5 3","T2I2","5 2"
+ 0,"5 1",0,"5 1",1,"5 3",1,"5 3",2,"5 2",2,"5 2",3,"5 3 1"
-- </analyze-3.6>
})
@@ -236,7 +236,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.7>
- "T1","5 1","T1I1","5 3","T1I2","5 2","T1I3","5 3 1","T2","5 1","T2I1","5 3"
+ 0,"5 1",0,"5 1",1,"5 3",1,"5 3",2,"5 2",3,"5 3 1"
-- </analyze-3.7>
})
@@ -263,7 +263,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.9>
- "T3","5 1","T3I1","5 3","T3I2","5 3 1 1 1","T3I3","5 5 2 1 1"
+ 0,"5 1",1,"5 3",2,"5 3 1 1 1",3,"5 5 2 1 1"
-- </analyze-3.9>
})
@@ -320,28 +320,30 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-4.0>
- "T3", "5 1", "T3I1", "5 3", "T3I2", "5 3 1 1 1", "T3I3", "5 5 2 1 1", "T4", "5 1", "T4I1", "5 3", "T4I2", "5 2"
+ 0, "5 1", 0, "5 1", 1, "5 3", 1, "5 3", 2, "5 3 1 1 1", 2, "5 2", 3, "5 5 2 1 1"
-- </analyze-4.0>
})
+t4 = box.space.T4
+
test:do_execsql_test(
"analyze-4.1",
- [[
+ string.format([[
DELETE FROM "_sql_stat1";
- INSERT INTO "_sql_stat1" VALUES('t4', 't4i1', 'nonsense');
- INSERT INTO "_sql_stat1" VALUES('t4', 't4i2', '432653287412874653284129847632');
+ INSERT INTO "_sql_stat1" VALUES(%i, %i, 'nonsense');
+ INSERT INTO "_sql_stat1" VALUES(%i, %i, '432653287412874653284129847632');
SELECT * FROM t4 WHERE x = 1234;
- ]], {
+ ]], t4.id, t4.index['T4I1'].id, t4.id, t4.index['T4I2'].id), {
-- <analyze-4.1>
-- </analyze-4.1>
})
test:do_execsql_test(
"analyze-4.2",
- [[
- INSERT INTO "_sql_stat1" VALUES('t4', 'xyzzy', '0 1 2 3');
+ string.format([[
+ INSERT INTO "_sql_stat1" VALUES(%i, 12345, '0 1 2 3');
SELECT * FROM t4 WHERE x = 1234;
- ]], {
+ ]], t4.id), {
-- <analyze-4.2>
-- </analyze-4.2>
})
@@ -367,20 +369,20 @@ test:do_execsql_test(
INSERT INTO t3 (a,b,c,d) SELECT a+64, b+64, c+64, d+64 FROM t3;
INSERT INTO t4 (x,y,z) SELECT a, b, c FROM t3;
ANALYZE;
- SELECT DISTINCT "tbl" FROM "_sql_stat1" ORDER BY 1;
+ SELECT COUNT(DISTINCT "tbl") FROM "_sql_stat1" ORDER BY 1;
]], {
-- <analyze-5.0>
- "T3", "T4"
+ 2
-- </analyze-5.0>
})
test:do_execsql_test(
"analyze-5.0.1",
[[
- SELECT DISTINCT "idx" FROM "_sql_stat1" ORDER BY 1;
+ SELECT "idx" FROM "_sql_stat1" ORDER BY 1;
]], {
-- <analyze-5.0>
- "T3", "T3I1", "T3I2", "T3I3", "T4", "T4I1", "T4I2"
+ 0, 0, 1, 1, 2, 2, 3
-- </analyze-5.0>
})
@@ -392,17 +394,17 @@ test:do_execsql_test(
SELECT DISTINCT "idx" FROM "%s" ORDER BY 1;
]], stat, stat), {
-- <analyze-5.1>
- "T3", "T3I1", "T3I2", "T3I3", "T4", "T4I1", "T4I2"
+ 0, 1, 2, 3
-- </analyze-5.1>
})
test:do_execsql_test(
"analyze-5.1.1",
string.format([[
- SELECT DISTINCT "tbl" FROM "%s" ORDER BY 1;
+ SELECT COUNT(DISTINCT "tbl") FROM "%s" ORDER BY 1;
]], stat, stat), {
-- <analyze-5.1>
- "T3", "T4"
+ 2
-- </analyze-5.1>
})
@@ -411,20 +413,20 @@ test:do_execsql_test(
[[
DROP INDEX t3i2 ON t3;
ANALYZE;
- SELECT DISTINCT "idx" FROM "_sql_stat1" ORDER BY 1;
+ SELECT "idx" FROM "_sql_stat1" ORDER BY 1;
]], {
-- <analyze-5.2>
- "T3", "T3I1", "T3I3", "T4", "T4I1", "T4I2"
+ 0, 0, 1, 1, 2, 3
-- </analyze-5.2>
})
test:do_execsql_test(
"analyze-5.2.1",
[[
- SELECT DISTINCT "tbl" FROM "_sql_stat1" ORDER BY 1;
+ SELECT COUNT(DISTINCT "tbl") FROM "_sql_stat1" ORDER BY 1;
]], {
-- <analyze-5.2>
- "T3", "T4"
+ 2
-- </analyze-5.2>
})
@@ -434,17 +436,17 @@ test:do_execsql_test(
SELECT DISTINCT "idx" FROM "%s" ORDER BY 1;
]], stat, stat), {
-- <analyze-5.3>
- "T3", "T3I1", "T3I3", "T4", "T4I1", "T4I2"
+ 0, 1, 2, 3
-- </analyze-5.3>
})
test:do_execsql_test(
"analyze-5.3.1",
string.format([[
- SELECT DISTINCT "tbl" FROM "%s" ORDER BY 1;
+ SELECT COUNT(DISTINCT "tbl") FROM "%s" ORDER BY 1;
]], stat, stat), {
-- <analyze-5.3>
- "T3", "T4"
+ 2
-- </analyze-5.3>
})
@@ -456,17 +458,17 @@ test:do_execsql_test(
SELECT DISTINCT "idx" FROM "_sql_stat1" ORDER BY 1;
]], {
-- <analyze-5.4>
- "T4", "T4I1", "T4I2"
+ 0, 1, 2
-- </analyze-5.4>
})
test:do_execsql_test(
"analyze-5.4.1",
[[
- SELECT DISTINCT "tbl" FROM "_sql_stat1" ORDER BY 1;
+ SELECT COUNT(DISTINCT "tbl") FROM "_sql_stat1" ORDER BY 1;
]], {
-- <analyze-5.4>
- "T4"
+ 1
-- </analyze-5.4>
})
@@ -476,17 +478,17 @@ test:do_execsql_test(
SELECT DISTINCT "idx" FROM "%s" ORDER BY 1;
]], stat), {
-- <analyze-5.5>
- "T4", "T4I1", "T4I2"
+ 0, 1, 2
-- </analyze-5.5>
})
test:do_execsql_test(
"analyze-5.5.1",
string.format([[
- SELECT DISTINCT "tbl" FROM "%s" ORDER BY 1;
+ SELECT COUNT(DISTINCT "tbl") FROM "%s" ORDER BY 1;
]], stat), {
-- <analyze-5.5>
- "T4"
+ 1
-- </analyze-5.5>
})
@@ -516,33 +518,35 @@ test:do_test(
-- </analyze-6.1.1>
})
+t1 = box.space.T1
+
test:do_execsql_test(
"analyze-6.1.2",
- [[
- SELECT * FROM "_sql_stat1" where "tbl"='T1' and "idx"='I1' LIMIT 1;
- ]], {
+ string.format([[
+ SELECT "idx", "stat" FROM "_sql_stat1" where "tbl"=%i and "idx"=%i LIMIT 1;
+ ]], t1.id, t1.index['I1'].id), {
-- <analyze-6.1.2>
- "T1", "I1", "221 221 221 221 2"
+ 1, "221 221 221 221 2"
-- </analyze-6.1.2>
})
test:do_execsql_test(
"analyze-6.1.3",
- [[
- SELECT "tbl", "idx", "neq", "nlt", "ndlt" FROM "_sql_stat4" where "tbl"='T1' and "idx"='I1' ORDER BY "nlt" LIMIT 1;
- ]], {
+ string.format([[
+ SELECT "idx", "neq", "nlt", "ndlt" FROM "_sql_stat4" where "tbl"=%i and "idx"=%i ORDER BY "nlt" LIMIT 1;
+ ]], t1.id, t1.index['I1'].id), {
-- <analyze-6.1.3>
- "T1", "I1", "221 221 221 1", "0 0 0 10", "0 0 0 10"
+ 1, "221 221 221 1", "0 0 0 10", "0 0 0 10"
-- </analyze-6.1.3>
})
test:do_execsql_test(
"analyze-6.1.4",
- [[
- SELECT "tbl", "idx", "neq", "nlt", "ndlt" FROM "_sql_stat4" where "tbl"='T1' and "idx"='I1' ORDER BY "nlt" DESC LIMIT 1;
- ]], {
+ string.format([[
+ SELECT "idx", "neq", "nlt", "ndlt" FROM "_sql_stat4" where "tbl"=%i and "idx"=%i ORDER BY "nlt" DESC LIMIT 1;
+ ]], t1.id, t1.index['I1'].id), {
-- <analyze-6.1.4>
- "T1", "I1", "221 221 221 1", "0 0 0 99", "0 0 0 99"
+ 1, "221 221 221 1", "0 0 0 99", "0 0 0 99"
-- </analyze-6.1.4>
})
diff --git a/test/sql-tap/analyze4.test.lua b/test/sql-tap/analyze4.test.lua
index c2cc190..dd5533b 100755
--- a/test/sql-tap/analyze4.test.lua
+++ b/test/sql-tap/analyze4.test.lua
@@ -49,15 +49,19 @@ test:do_test(
-- </analyze4-1.0>
})
+t1 = box.space.T1
+
-- Verify that the t1b index shows that it does not narrow down the
-- search any at all.
--
test:do_execsql_test(
"analyze4-1.1",
- [[ SELECT "idx", "stat" FROM "_sql_stat1" WHERE "tbl"='T1' ORDER BY "idx"; ]],
+ string.format([[
+ SELECT "idx", "stat" FROM "_sql_stat1" WHERE "tbl"=%i ORDER BY "idx";
+ ]], t1.id),
{
-- <analyze4-1.1>
- "T1","128 1", "T1A", "128 1", "T1B", "128 128"
+ 0,"128 1", 1, "128 1", 2, "128 128"
-- </analyze4-1.1>
})
@@ -68,16 +72,16 @@ test:do_execsql_test(
test:do_test(
"analyze4-1.2",
function()
- return test:execsql([[
- UPDATE t1 SET b='x' WHERE a%2;
+ return test:execsql(string.format([[
+ UPDATE t1 SET b='x' WHERE a%%2;
-- pragma vdbe_debug=1;
ANALYZE;
-- pragma vdbe_debug=0;
- SELECT "idx", "stat" FROM "_sql_stat1" WHERE "tbl"='T1' ORDER BY "idx";
- ]])
+ SELECT "idx", "stat" FROM "_sql_stat1" WHERE "tbl"=%i ORDER BY "idx";
+ ]], t1.id))
end, {
-- <analyze4-1.2>
- "T1", "128 1", "T1A", "128 1", "T1B", "128 64"
+ 0, "128 1", 1, "128 1", 2, "128 64"
-- </analyze4-1.2>
})
@@ -85,37 +89,42 @@ test:do_test(
-- Create a multi-column indices using t1.b and verify that ANALYZE
-- processes them correctly.
--
+
+test:execsql([[
+ -- Tarantool doesn't suppoort ALTER stmt yet.
+ -- UPDATE t1 SET b=NULL;
+ --ALTER TABLE t1 ADD COLUMN c;
+ --ALTER TABLE t1 ADD COLUMN d;
+ -- So, re-create the table and its contents
+ DROP TABLE t1;
+ CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a,b,c DEFAULT NULL,d DEFAULT NULL);
+ CREATE INDEX t1a ON t1(a);
+ CREATE INDEX t1b ON t1(b);
+ INSERT INTO t1 (a,b) VALUES(1,NULL);
+ INSERT INTO t1 (a,b) SELECT a+1, b FROM t1;
+ INSERT INTO t1 (a,b) SELECT a+2, b FROM t1;
+ INSERT INTO t1 (a,b) SELECT a+4, b FROM t1;
+ INSERT INTO t1 (a,b) SELECT a+8, b FROM t1;
+ INSERT INTO t1 (a,b) SELECT a+16, b FROM t1;
+ INSERT INTO t1 (a,b) SELECT a+32, b FROM t1;
+ INSERT INTO t1 (a,b) SELECT a+64, b FROM t1;
+
+ UPDATE t1 SET c=a/4, d=a/2;
+ CREATE INDEX t1bcd ON t1(b,c,d);
+ CREATE INDEX t1cdb ON t1(c,d,b);
+ CREATE INDEX t1cbd ON t1(c,b,d);
+ ANALYZE;
+]])
+
+t1 = box.space.T1
+
test:do_execsql_test(
"analyze4-1.3",
- [[
- -- Tarantool doesn't suppoort ALTER stmt yet.
- -- UPDATE t1 SET b=NULL;
- --ALTER TABLE t1 ADD COLUMN c;
- --ALTER TABLE t1 ADD COLUMN d;
- -- So, re-create the table and its contents
- DROP TABLE t1;
- CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a,b,c DEFAULT NULL,d DEFAULT NULL);
- CREATE INDEX t1a ON t1(a);
- CREATE INDEX t1b ON t1(b);
- INSERT INTO t1 (a,b) VALUES(1,NULL);
- INSERT INTO t1 (a,b) SELECT a+1, b FROM t1;
- INSERT INTO t1 (a,b) SELECT a+2, b FROM t1;
- INSERT INTO t1 (a,b) SELECT a+4, b FROM t1;
- INSERT INTO t1 (a,b) SELECT a+8, b FROM t1;
- INSERT INTO t1 (a,b) SELECT a+16, b FROM t1;
- INSERT INTO t1 (a,b) SELECT a+32, b FROM t1;
- INSERT INTO t1 (a,b) SELECT a+64, b FROM t1;
-
- UPDATE t1 SET c=a/4, d=a/2;
- CREATE INDEX t1bcd ON t1(b,c,d);
- CREATE INDEX t1cdb ON t1(c,d,b);
- CREATE INDEX t1cbd ON t1(c,b,d);
- ANALYZE;
- SELECT "idx", "stat" FROM "_sql_stat1" WHERE "tbl"='T1' ORDER BY "idx";
- ]]
- , {
+ string.format([[
+ SELECT "idx", "stat" FROM "_sql_stat1" WHERE "tbl"=%i ORDER BY "idx";
+ ]], t1.id), {
-- <analyze4-1.3>
- "T1","128 1", "T1A", "128 1", "T1B", "128 128", "T1BCD", "128 128 4 2", "T1CBD", "128 4 4 2", "T1CDB", "128 4 2 2"
+ 0,"128 1", 1, "128 1", 2, "128 128", 3, "128 128 4 2", 4, "128 4 2 2", 5, "128 4 4 2"
-- </analyze4-1.3>
})
diff --git a/test/sql-tap/analyze5.test.lua b/test/sql-tap/analyze5.test.lua
index d68bd3c..198f397 100755
--- a/test/sql-tap/analyze5.test.lua
+++ b/test/sql-tap/analyze5.test.lua
@@ -114,10 +114,11 @@ test:do_test(
-- DISTINCT idx, sample -- lindex(test_decode(sample),0)
-- WHERE idx='t1u' ORDER BY nlt;
- return test:execsql([[ SELECT DISTINCT msgpack_decode("sample")
- FROM "_sql_stat4"
- WHERE "idx"='T1U'
- ORDER BY "nlt"]])
+ return test:execsql(string.format([[
+ SELECT DISTINCT msgpack_decode("sample")
+ FROM "_sql_stat4"
+ WHERE "idx"= %i
+ ORDER BY "nlt"]], box.space.T1.index['T1U'].id))
end, {
-- <analyze5-1.0>
"alpha", "bravo", "charlie", "delta"
@@ -146,7 +147,7 @@ test:do_test(
return test:execsql([[SELECT "idx", count(*) FROM "_sql_stat4" GROUP BY 1 ORDER BY 1]])
end, {
-- <analyze5-1.2>
- "T1",24,"T1T",4,"T1U",4,"T1V",1,"T1W",4,"T1X",4,"T1Y",2,"T1Z",4
+ 0,24,1,4,2,4,3,1,4,4,5,4,6,2,7,4
-- </analyze5-1.2>
})
diff --git a/test/sql-tap/analyze9.test.lua b/test/sql-tap/analyze9.test.lua
index 1dbfe5d..b0fec3b 100755
--- a/test/sql-tap/analyze9.test.lua
+++ b/test/sql-tap/analyze9.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(121)
+test:plan(118)
testprefix = "analyze9"
@@ -62,29 +62,29 @@ msgpack_decode_sample = function(txt)
end
box.internal.sql_create_function("msgpack_decode_sample", msgpack_decode_sample)
+t1 = box.space.T1
test:do_execsql_test(
1.2,
- [[
- SELECT "tbl","idx","neq","nlt","ndlt",msgpack_decode_sample("sample") FROM "_sql_stat4" where "idx" = 'I1';
- ]], {
+ string.format([[
+ SELECT "idx","neq","nlt","ndlt",msgpack_decode_sample("sample") FROM "_sql_stat4" where "idx" = %i;
+ ]], t1.index['I1'].id), {
-- <1.2>
- "T1", "I1", "1 1", "0 0", "0 0", "(0) (0)", "T1", "I1", "1 1", "1 1", "1 1", "(1) (1)",
- "T1", "I1", "1 1", "2 2", "2 2", "(2) (2)", "T1", "I1", "1 1", "3 3", "3 3", "(3) (3)",
- "T1", "I1", "1 1", "4 4", "4 4", "(4) (4)"
+ 1, "1 1", "0 0", "0 0", "(0) (0)", 1, "1 1", "1 1", "1 1", "(1) (1)",
+ 1, "1 1", "2 2", "2 2", "(2) (2)", 1, "1 1", "3 3", "3 3", "(3) (3)",
+ 1, "1 1", "4 4", "4 4", "(4) (4)"
-- </1.2>
})
test:do_execsql_test(
1.3,
- [[
- SELECT "tbl","idx","neq","nlt","ndlt",msgpack_decode_sample("sample") FROM "_sql_stat4" where "idx" = 'T1';
-
- ]], {
+ string.format([[
+ SELECT "idx","neq","nlt","ndlt",msgpack_decode_sample("sample") FROM "_sql_stat4" where "idx" = %i;
+ ]], t1.index[0].id), {
-- <1.3>
- 'T1', 'T1', '1', '0', '0', '(0)', 'T1', 'T1', '1', '1', '1', '(1)',
- 'T1', 'T1', '1', '2', '2', '(2)', 'T1', 'T1', '1', '3', '3', '(3)',
- 'T1', 'T1', '1', '4', '4', '(4)'
+ 0, '1', '0', '0', '(0)', 0, '1', '1', '1', '(1)',
+ 0, '1', '2', '2', '(2)', 0, '1', '3', '3', '(3)',
+ 0, '1', '4', '4', '(4)'
-- </1.3>
})
@@ -101,10 +101,10 @@ test:do_execsql_test(
INSERT INTO t1 VALUES(22.0, 'some text');
CREATE INDEX i1 ON t1(a, b);
ANALYZE;
- SELECT msgpack_decode_sample("sample") FROM "_sql_stat4";
+ SELECT msgpack_decode_sample("sample") FROM "_sql_stat4" ORDER BY 1;
]], {
-- <2.1>
- "some text 14", "22 some text", "some text", 22
+ 22, "22 some text", "some text", "some text 14"
-- </2.1>
})
@@ -116,6 +116,7 @@ test:do_execsql_test(
CREATE TABLE t2(id INTEGER PRIMARY KEY AUTOINCREMENT, a, b);
CREATE INDEX i2 ON t2(a, b);
]])
+t2 = box.space.T2
test:do_test(
3.2,
@@ -184,10 +185,10 @@ test:do_execsql_test(
--
test:do_execsql_test(
"3.3.2",
- [[
+ string.format([[
ANALYZE;
- SELECT lrange("neq", 1, 1) FROM "_sql_stat4" WHERE "idx" = 'I2';
- ]], generate_tens_str(24))
+ SELECT lrange("neq", 1, 1) FROM "_sql_stat4" WHERE "tbl" = %i and "idx" = %i;
+ ]], t2.id, t2.index['I2'].id) , generate_tens_str(24))
---------------------------------------------------------------------------
--
@@ -232,6 +233,7 @@ test:do_execsql_test(
CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a, b, c);
CREATE INDEX i1 ON t1(c, b, a);
]])
+t1 = box.space.T1
insert_filler_rows_n = function(iStart, nCopy, nVal)
for i = 0, nVal-1 do
@@ -282,10 +284,10 @@ test:do_execsql_test(
test:do_execsql_test(
4.3,
- [[
+ string.format([[
SELECT "neq", lrange("nlt", 1, 3), lrange("ndlt", 1, 3), lrange(msgpack_decode_sample("sample"), 1, 3)
- FROM "_sql_stat4" WHERE "idx" = 'I1' ORDER BY "sample" LIMIT 16;
- ]], {
+ FROM "_sql_stat4" WHERE "tbl" = %i and "idx" = %i ORDER BY "sample" LIMIT 16;
+ ]], t1.id, t1.index['I1'].id), {
-- <4.3>
"10 10 10","0 0 0","0 0 0","0 0 0","10 10 10","10 10 10","1 1 1","1 1 1","10 10 10","20 20 20",
"2 2 2","2 2 2","10 10 10","30 30 30","3 3 3","3 3 3","10 10 10","40 40 40","4 4 4","4 4 4",
@@ -299,10 +301,10 @@ test:do_execsql_test(
test:do_execsql_test(
4.4,
- [[
+ string.format([[
SELECT "neq", lrange("nlt", 1, 3), lrange("ndlt", 1, 3), lrange(msgpack_decode_sample("sample"), 1, 3)
- FROM "_sql_stat4" WHERE "idx" = 'I1' ORDER BY "sample" DESC LIMIT 2;
- ]], {
+ FROM "_sql_stat4" WHERE "tbl" = %i and "idx" = %i ORDER BY "sample" DESC LIMIT 2;
+ ]], t1.id, t1.index['I1'].id), {
-- <4.4>
"2 1 1","295 296 296","120 122 125","201 4 h","5 3 1","290 290 291","119 119 120","200 1 b"
-- </4.4>
@@ -364,11 +366,12 @@ test:do_test(
test:do_execsql_test(
4.9,
[[
- SELECT msgpack_decode_sample("sample") FROM "_sql_stat4";
+ SELECT msgpack_decode_sample("sample") FROM "_sql_stat4" ORDER BY 1;
]], {
-- <4.9>
- "x", 1110, 2230, 2750, 3350, 4090, 4470, 4980, 5240, 5280, 5290, 5590, 5920,
- 5930, 6220, 6710, 7000, 7710, 7830, 7970, 8890, 8950, 9240, 9250, 9680
+ 1110, 2230, 2750, 3350, 4090, 4470, 4980, 5240, 5280, 5290, 5590, 5920,
+ 5930, 6220, 6710, 7000, 7710, 7830, 7970, 8890, 8950, 9240, 9250, 9680,
+ "x"
-- </4.9>
})
@@ -394,6 +397,8 @@ test:do_execsql_test(
INSERT INTO "_sql_stat4" SELECT * FROM x1;
ANALYZE;
]])
+t1 = box.space.T1
+x1 = box.space.X1
test:do_execsql_test(
6.2,
@@ -407,7 +412,7 @@ test:do_execsql_test(
--
test:do_execsql_test(
7.1,
- [[
+ string.format([[
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a, b);
CREATE INDEX i1 ON t1(a, b);
@@ -418,9 +423,9 @@ test:do_execsql_test(
INSERT INTO t1 VALUES(null, 5, 5);
ANALYZE;
UPDATE "_sql_stat4" SET "sample" = '' WHERE "sample" =
- (SELECT "sample" FROM "_sql_stat4" WHERE "tbl" = 't1' AND "idx" = 'i1' LIMIT 1);
+ (SELECT "sample" FROM "_sql_stat4" WHERE "tbl" = %i AND "idx" = %i LIMIT 1);
ANALYZE;
- ]])
+ ]], t1.id, t1.index['I1'].id))
-- Doesn't work due to the fact that in Tarantool rowid has been removed,
-- and tbl, idx and sample have been united into primary key.
@@ -1014,15 +1019,15 @@ test:do_execsql_test(
--
test:do_execsql_test(
15.1,
- [[
+ string.format([[
DROP TABLE IF EXISTS x1;
CREATE TABLE x1(a PRIMARY KEY, b, UNIQUE(a, b));
INSERT INTO x1 VALUES(1, 2);
INSERT INTO x1 VALUES(3, 4);
INSERT INTO x1 VALUES(5, 6);
ANALYZE;
- INSERT INTO "_sql_stat4" VALUES('x1', 'abc', 0, 0, 0, '');
- ]])
+ INSERT INTO "_sql_stat4" VALUES(%i, 12345, 0, 0, 0, '');
+ ]], x1.id))
test:do_execsql_test(
15.2,
@@ -1050,42 +1055,6 @@ test:do_execsql_test(
-- </15.4>
})
-test:do_execsql_test(
- 15.7,
- [[
- ANALYZE;
- UPDATE "_sql_stat1" SET "tbl" = 'no such tbl';
- ]])
-
-test:do_execsql_test(
- 15.8,
- [[
- SELECT * FROM x1 ;
- ]], {
- -- <15.8>
- 1, 2, 3, 4, 5, 6
- -- </15.8>
- })
-
--- Tarantool: this test seems to be useless. There's no reason
--- for these fields to be nullable.
--- test:do_execsql_test(
--- 15.9,
--- [[
--- ANALYZE;
--- UPDATE "_sql_stat4" SET "neq" = NULL, "nlt" = NULL, "ndlt" = NULL;
--- ]])
-
-test:do_execsql_test(
- 15.10,
- [[
- SELECT * FROM x1;
- ]], {
- -- <15.10>
- 1, 2, 3, 4, 5, 6
- -- </15.10>
- })
-
-- This is just for coverage....
test:do_execsql_test(
15.11,
@@ -1135,6 +1104,7 @@ test:do_test(
-- <17.1>
-- </17.1>
})
+t1 = box.space.T1
test:do_execsql_test(
17.2,
@@ -1197,7 +1167,7 @@ test:do_test(
test:execsql(string.format("INSERT INTO t1 VALUES(%s, 0);", i))
end
test:execsql("ANALYZE")
- return test:execsql([[ SELECT count(*) FROM "_sql_stat4" WHERE "idx" = 'I1'; ]])
+ return test:execsql(string.format('SELECT count(*) FROM "_sql_stat4" WHERE "tbl" = %i AND "idx" = %i;', box.space.T1.id, box.space.T1.index['I1'].id))
end, {
-- <18.1>
9
@@ -1239,12 +1209,13 @@ test:do_execsql_test(
-- </20.2>
})
+t1 = box.space.T1
for i = 0, 15 do
test:do_test(
"20.3."..i,
function()
return test:execsql(string.format(
- [[SELECT count(*) FROM "_sql_stat4" WHERE "idx" = 'I1' AND lrange(msgpack_decode_sample("sample"), 1, 1) = '%s']], i))
+ [[SELECT count(*) FROM "_sql_stat4" WHERE "tbl" = %i AND "idx" = %i AND lrange(msgpack_decode_sample("sample"), 1, 1) = '%s']], t1.id, t1.index['I1'].id, i))
end, {
1
})
diff --git a/test/sql-tap/analyzeC.test.lua b/test/sql-tap/analyzeC.test.lua
index a3cea70..1241ee8 100755
--- a/test/sql-tap/analyzeC.test.lua
+++ b/test/sql-tap/analyzeC.test.lua
@@ -30,9 +30,7 @@ testprefix = "analyzeC"
-- Baseline case. Range queries work OK. Indexes can be used for
-- ORDER BY.
-test:do_execsql_test(
- 1.0,
- [[
+test:execsql([[
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a PRIMARY KEY, b, c, d);
INSERT INTO t1(a,b,c,d) VALUES(1,1,2,3),(2,7,8,9),(3,4,5,6),(4,10,11,12),(5,4,8,12),(6,1,11,111);
@@ -40,10 +38,17 @@ test:do_execsql_test(
CREATE INDEX t1c ON t1(c);
ANALYZE;
DELETE FROM "_sql_stat1";
- INSERT INTO "_sql_stat1"("tbl","idx","stat") VALUES('t1','t1b','12345 2'),('t1','t1c','12345 4');
+ ]]
+)
+t1 = box.space.T1
+
+test:do_execsql_test(
+ 1.0,
+ string.format([[
+ INSERT INTO "_sql_stat1"("tbl","idx","stat") VALUES(%i,1,'12345 2'),(%i,2,'12345 4');
ANALYZE;
SELECT b,c,d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;
- ]], {
+ ]], t1.id, t1.id), {
-- <1.0>
4, 5, 6, "#", 7, 8, 9, "#", 4, 8, 12, "#"
-- </1.0>
@@ -173,17 +178,22 @@ test:do_execsql_test(
-- The sz=NNN parameter determines which index to scan
--
-test:do_execsql_test(
- 4.0,
- [[
+
+test:execsql([[
DROP INDEX t1b ON t1;
CREATE INDEX t1bc ON t1(b,c);
CREATE INDEX t1db ON t1(d,b);
DELETE FROM "_sql_stat1";
- INSERT INTO "_sql_stat1"("tbl","idx","stat") VALUES('t1','t1bc','12345 3 2 sz=10'),('t1','t1db','12345 3 2 sz=20');
+ ]]
+)
+
+test:do_execsql_test(
+ 4.0,
+ string.format([[
+ INSERT INTO "_sql_stat1"("tbl","idx","stat") VALUES(%i,%i,'12345 3 2 sz=10'),(%i,%i,'12345 3 2 sz=20');
ANALYZE;
SELECT count(b) FROM t1;
- ]], {
+ ]], t1.id, t1.index['T1BC'].id, t1.id, t1.index['T1DB'].id), {
-- <4.0>
6
-- </4.0>
@@ -199,14 +209,16 @@ test:do_execsql_test(
-- </4.1>
})
+
test:do_execsql_test(
4.2,
+ string.format(
[[
DELETE FROM "_sql_stat1";
- INSERT INTO "_sql_stat1"("tbl","idx","stat") VALUES('t1','t1bc','12345 3 2 sz=20'),('t1','t1db','12345 3 2 sz=10');
+ INSERT INTO "_sql_stat1"("tbl","idx","stat") VALUES(%i,%i,'12345 3 2 sz=20'),(%i,%i,'12345 3 2 sz=10');
ANALYZE;
SELECT count(b) FROM t1;
- ]], {
+ ]], t1.id, t1.index['T1BC'].id, t1.id, t1.index['T1DB'].id), {
-- <4.2>
6
-- </4.2>
@@ -227,14 +239,14 @@ test:do_execsql_test(
--
test:do_execsql_test(
5.0,
- [[
+ string.format([[
DELETE FROM "_sql_stat1";
INSERT INTO "_sql_stat1"("tbl","idx","stat")
- VALUES('t1','t1bc','12345 3 2 x=5 sz=10 y=10'),
- ('t1','t1db','12345 3 2 whatever sz=20 junk');
+ VALUES(%i,%i,'12345 3 2 x=5 sz=10 y=10'),
+ (%i,%i,'12345 3 2 whatever sz=20 junk');
ANALYZE;
SELECT count(b) FROM t1;
- ]], {
+ ]], t1.id, t1.index['T1BC'].id, t1.id, t1.index['T1DB'].id), {
-- <5.0>
6
-- </5.0>
@@ -251,14 +263,15 @@ test:do_execsql_test(
-- </5.1>
})
+
test:do_execsql_test(
5.2,
- [[
+ string.format([[
DELETE FROM "_sql_stat1";
- INSERT INTO "_sql_stat1"("tbl","idx","stat") VALUES('t1','t1db','12345 3 2 x=5 sz=10 y=10'), ('t1','t1bc','12345 3 2 whatever sz=20 junk');
+ INSERT INTO "_sql_stat1"("tbl","idx","stat") VALUES(%i,%i,'12345 3 2 x=5 sz=10 y=10'), (%i,%i,'12345 3 2 whatever sz=20 junk');
ANALYZE;
SELECT count(b) FROM t1;
- ]], {
+ ]], t1.id, t1.index['T1BC'].id, t1.id, t1.index['T1DB'].id), {
-- <5.2>
6
-- </5.2>
diff --git a/test/sql-tap/analyzeD.test.lua b/test/sql-tap/analyzeD.test.lua
index ef6aced..27a19c8 100755
--- a/test/sql-tap/analyzeD.test.lua
+++ b/test/sql-tap/analyzeD.test.lua
@@ -153,9 +153,9 @@ test:do_catchsql_test(
test:do_execsql_test(
"analyzeD-1.10",
- [[
- SELECT * FROM "_sql_stat4" WHERE "tbl" = 'v';
- ]], {
+ string.format([[
+ SELECT * FROM "_sql_stat4" WHERE "tbl" = %i;
+ ]], box.space.V.id), {
-- <analyzeD-1.10>
-- <analyzeD-1.10>
@@ -163,9 +163,9 @@ test:do_execsql_test(
test:do_execsql_test(
"analyzeD-1.11",
- [[
- SELECT * FROM "_sql_stat1" WHERE "tbl" = 'v';
- ]], {
+ string.format([[
+ SELECT * FROM "_sql_stat1" WHERE "tbl" = %i;
+ ]], box.space.V.id), {
-- <analyzeD-1.11>
-- <analyzeD-1.11>
@@ -183,9 +183,9 @@ test:do_catchsql_test(
test:do_execsql_test(
"analyzeD-1.13",
- [[
- SELECT * FROM "_sql_stat4" WHERE "tbl" = 'v';
- ]], {
+ string.format([[
+ SELECT * FROM "_sql_stat4" WHERE "tbl" = %i;
+ ]], box.space.V.id), {
-- <analyzeD-1.13>
-- <analyzeD-1.13>
@@ -193,9 +193,9 @@ test:do_execsql_test(
test:do_execsql_test(
"analyzeD-1.14",
- [[
- SELECT * FROM "_sql_stat1" WHERE "tbl" = 'v';
- ]], {
+ string.format([[
+ SELECT * FROM "_sql_stat1" WHERE "tbl" = %i;
+ ]], box.space.V.id), {
-- <analyzeD-1.14>
-- <analyzeD-1.14>
diff --git a/test/sql-tap/gh-3350-skip-scan.test.lua b/test/sql-tap/gh-3350-skip-scan.test.lua
index 301b04a..917c74c 100755
--- a/test/sql-tap/gh-3350-skip-scan.test.lua
+++ b/test/sql-tap/gh-3350-skip-scan.test.lua
@@ -77,9 +77,7 @@ test:do_execsql_test(
}
)
-test:do_execsql_test(
- "skip-scan-1.4",
- [[
+test:execsql([[
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(id INTEGER PRIMARY KEY, a TEXT, b INT, c INT, d INT);
CREATE INDEX t1abc ON t1(a,b,c);
@@ -96,10 +94,17 @@ test:do_execsql_test(
ANALYZE;
DELETE FROM "_sql_stat1";
DELETE FROM "_sql_stat4";
- INSERT INTO "_sql_stat1" VALUES('T1','T1ABC','10000 5000 2000 10');
+ ]]
+)
+
+test:do_execsql_test(
+ "skip-scan-1.4",
+ string.format(
+ [[
+ INSERT INTO "_sql_stat1" VALUES(%i,%i,'10000 5000 2000 10');
ANALYZE t2;
SELECT a,b,c,d FROM t1 WHERE b=345;
- ]], {
+ ]], box.space.T1.id, box.space.T1.index['T1ABC'].id), {
"abc", 345, 7, 8, "def", 345, 9, 10
}
)
diff --git a/test/sql/sql-statN-index-drop.result b/test/sql/sql-statN-index-drop.result
index a751eca..0e5f2a3 100644
--- a/test/sql/sql-statN-index-drop.result
+++ b/test/sql/sql-statN-index-drop.result
@@ -31,36 +31,36 @@ box.sql.execute("ANALYZE;")
---
...
-- Checking the data.
-box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" 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=]
+- - [0, '1', '0', '0', !!binary kQE=]
+ - [1, '1', '0', '0', !!binary kQI=]
+ - [0, '1', '0', '0', !!binary kQE=]
+ - [1, '1', '0', '0', !!binary kQI=]
...
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+box.sql.execute('SELECT "idx","stat" FROM \"_sql_stat1\";')
---
-- - ['T1', 'I1', '1 1']
- - ['T1', 'T1', '1 1']
- - ['T2', 'I1', '1 1']
- - ['T2', 'T2', '1 1']
+- - [0, '1 1']
+ - [1, '1 1']
+ - [0, '1 1']
+ - [1, '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\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" FROM \"_sql_stat4\";')
---
-- - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
- - ['T2', 'I1', '1', '0', '0', !!binary kQI=]
- - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
+- - [0, '1', '0', '0', !!binary kQE=]
+ - [0, '1', '0', '0', !!binary kQE=]
+ - [1, '1', '0', '0', !!binary kQI=]
...
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+box.sql.execute('SELECT "idx","stat" FROM \"_sql_stat1\";')
---
-- - ['T1', 'T1', '1 1']
- - ['T2', 'I1', '1 1']
- - ['T2', 'T2', '1 1']
+- - [0, '1 1']
+ - [0, '1 1']
+ - [1, '1 1']
...
--Cleaning up.
box.sql.execute("DROP TABLE t1;")
@@ -93,36 +93,36 @@ box.sql.execute("ANALYZE;")
---
...
-- Checking the data.
-box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" 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=]
+- - [0, '1', '0', '0', !!binary kQE=]
+ - [1, '1', '0', '0', !!binary kQI=]
+ - [0, '1', '0', '0', !!binary kQE=]
+ - [1, '1', '0', '0', !!binary kQI=]
...
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+box.sql.execute('SELECT "idx","stat" FROM \"_sql_stat1\";')
---
-- - ['T1', 'I1', '1 1']
- - ['T1', 'T1', '1 1']
- - ['T2', 'I1', '1 1']
- - ['T2', 'T2', '1 1']
+- - [0, '1 1']
+ - [1, '1 1']
+ - [0, '1 1']
+ - [1, '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\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" FROM \"_sql_stat4\";')
---
-- - ['T1', 'I1', '1', '0', '0', !!binary kQI=]
- - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
- - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
+- - [0, '1', '0', '0', !!binary kQE=]
+ - [1, '1', '0', '0', !!binary kQI=]
+ - [0, '1', '0', '0', !!binary kQE=]
...
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+box.sql.execute('SELECT "idx","stat" FROM \"_sql_stat1\";')
---
-- - ['T1', 'I1', '1 1']
- - ['T1', 'T1', '1 1']
- - ['T2', 'T2', '1 1']
+- - [0, '1 1']
+ - [1, '1 1']
+ - [0, '1 1']
...
--Cleaning up.
box.sql.execute("DROP TABLE t1;")
diff --git a/test/sql/sql-statN-index-drop.test.lua b/test/sql/sql-statN-index-drop.test.lua
index fe7e15b..6cc6400 100644
--- a/test/sql/sql-statN-index-drop.test.lua
+++ b/test/sql/sql-statN-index-drop.test.lua
@@ -14,15 +14,15 @@ box.sql.execute("INSERT INTO t2 VALUES(1, 2);")
box.sql.execute("ANALYZE;")
-- Checking the data.
-box.sql.execute("SELECT * FROM \"_sql_stat4\";")
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" FROM \"_sql_stat4\";')
+box.sql.execute('SELECT "idx","stat" 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\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" FROM \"_sql_stat4\";')
+box.sql.execute('SELECT "idx","stat" FROM \"_sql_stat1\";')
--Cleaning up.
box.sql.execute("DROP TABLE t1;")
@@ -41,15 +41,15 @@ box.sql.execute("INSERT INTO t2 VALUES(1, 2);")
box.sql.execute("ANALYZE;")
-- Checking the data.
-box.sql.execute("SELECT * FROM \"_sql_stat4\";")
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" FROM \"_sql_stat4\";')
+box.sql.execute('SELECT "idx","stat" 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\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" FROM \"_sql_stat4\";')
+box.sql.execute('SELECT "idx","stat" FROM \"_sql_stat1\";')
--Cleaning up.
box.sql.execute("DROP TABLE t1;")
diff --git a/test/sql/upgrade.result b/test/sql/upgrade.result
index 5e7d851..6890c5d 100644
--- a/test/sql/upgrade.result
+++ b/test/sql/upgrade.result
@@ -30,13 +30,13 @@ box.space._space.index['name']:get('_trigger')
...
box.space._space.index['name']:get('_sql_stat1')
---
-- [348, 1, '_sql_stat1', 'memtx', 0, {}, [{'name': 'tbl', 'type': 'string'}, {'name': 'idx',
- 'type': 'string'}, {'name': 'stat', 'type': 'string'}]]
+- [348, 1, '_sql_stat1', 'memtx', 0, {}, [{'name': 'tbl', 'type': 'unsigned'}, {'name': 'idx',
+ 'type': 'unsigned'}, {'name': 'stat', 'type': 'string'}]]
...
box.space._space.index['name']:get('_sql_stat4')
---
-- [349, 1, '_sql_stat4', 'memtx', 0, {}, [{'name': 'tbl', 'type': 'string'}, {'name': 'idx',
- 'type': 'string'}, {'name': 'neq', 'type': 'string'}, {'name': 'nlt', 'type': 'string'},
+- [349, 1, '_sql_stat4', 'memtx', 0, {}, [{'name': 'tbl', 'type': 'unsigned'}, {'name': 'idx',
+ 'type': 'unsigned'}, {'name': 'neq', 'type': 'string'}, {'name': 'nlt', 'type': 'string'},
{'name': 'ndlt', 'type': 'string'}, {'name': 'sample', 'type': 'scalar'}]]
...
box.space._index:get({box.space._space.index['name']:get('_trigger').id, 0})
@@ -45,12 +45,12 @@ box.space._index:get({box.space._space.index['name']:get('_trigger').id, 0})
...
box.space._index:get({box.space._space.index['name']:get('_sql_stat1').id, 0})
---
-- [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'string']]]
+- [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1, 'unsigned']]]
...
box.space._index:get({box.space._space.index['name']:get('_sql_stat4').id, 0})
---
-- [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'string'], [5,
- 'scalar']]]
+- [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1, 'unsigned'],
+ [5, 'scalar']]]
...
box.space._schema:format()
---
--
2.7.4
^ permalink raw reply [flat|nested] 4+ messages in thread
* [tarantool-patches] Re: [PATCH v1 1/1] sql: hold in stat tables space/index id instead of name
2018-08-23 9:51 [tarantool-patches] [PATCH v1 1/1] sql: hold in stat tables space/index id instead of name imeevma
@ 2018-08-27 16:32 ` Vladislav Shpilevoy
2018-08-29 11:37 ` Imeev Mergen
0 siblings, 1 reply; 4+ messages in thread
From: Vladislav Shpilevoy @ 2018-08-27 16:32 UTC (permalink / raw)
To: tarantool-patches, imeevma
Hi! Thanks for the patch! See 8 comments below.
On 23/08/2018 06:51, imeevma@tarantool.org wrote:
> There is no reason to hold in statistic tables names of spaces and
> indexes. This patch allows us to save id of space/index instead of
> name.
1. Please, explain why ids are better than names. 'No reason'
is not a reason. Actually the real point of storing ids is to
avoid problems with table and index renaming. Ids are fixed
values on the contrary.
>
> Closes #3242
> ---
> Branch: https://github.com/tarantool/tarantool/tree/imeevma/gh-3242-hold-ids-in-stat-tables
> Issue: https://github.com/tarantool/tarantool/issues/3242
>
> src/box/bootstrap.snap | Bin 1818 -> 1822 bytes
> src/box/lua/upgrade.lua | 12 ++--
> src/box/schema.cc | 8 +--
> src/box/sql.c | 8 +--
> src/box/sql/analyze.c | 114 ++++++++++---------------------
> src/box/sql/build.c | 87 +++++++-----------------
> src/box/sql/sqliteInt.h | 10 +--
> test/box-py/bootstrap.result | 17 ++---
> test/box/access_misc.result | 11 +--
> test/box/alter.result | 6 +-
> test/sql-tap/analyze1.test.lua | 100 ++++++++++++++-------------
> test/sql-tap/analyze4.test.lua | 79 ++++++++++++----------
> test/sql-tap/analyze5.test.lua | 11 +--
> test/sql-tap/analyze9.test.lua | 115 ++++++++++++--------------------
> test/sql-tap/analyzeC.test.lua | 51 ++++++++------
> test/sql-tap/analyzeD.test.lua | 24 +++----
> test/sql-tap/gh-3350-skip-scan.test.lua | 15 +++--
> test/sql/sql-statN-index-drop.result | 72 ++++++++++----------
> test/sql/sql-statN-index-drop.test.lua | 16 ++---
> test/sql/upgrade.result | 14 ++--
> 20 files changed, 346 insertions(+), 424 deletions(-)
>
> diff --git a/src/box/sql/analyze.c b/src/box/sql/analyze.c
> index 00d96d2..86776ad 100644
> --- a/src/box/sql/analyze.c
> +++ b/src/box/sql/analyze.c
> @@ -124,28 +124,27 @@
> * @param parse Parsing context.
> * @param stat_cursor Open the _sql_stat1 table on this cursor.
> * you should allocate |stat_names| cursors before call.
> - * @param table_name Delete records of this index if specified.
> + * @param table_id Delete records of this table if id is not 0.
2. Please, use BOX_ID_NIL constant.
> */
> static void
> -vdbe_emit_stat_space_open(struct Parse *parse, int stat_cursor,
> - const char *table_name)
> +vdbe_emit_stat_space_open(struct Parse *parse, int stat_cursor, int table_id)
3. Space_id has uint32_t type.
> @@ -837,8 +836,8 @@ analyzeOneTable(Parse * pParse, /* Parser context */
> idx_name = pIdx->def->name;
> int part_count = pIdx->def->key_def->part_count;
>
> - /* Populate the register containing the index name. */
> - sqlite3VdbeLoadString(v, regIdxname, idx_name);
> + /* Populate the register containing the index id. */
> + sqlite3VdbeAddOp2(v, OP_Integer, pIdx->def->iid, regIdxname);
4. regIdxName is not correct now.
> VdbeComment((v, "Analysis for %s.%s", pTab->def->name,
> idx_name));
>
> diff --git a/src/box/sql/build.c b/src/box/sql/build.c
> index dddeb12..0d21ff0 100644
> --- a/src/box/sql/build.c
> +++ b/src/box/sql/build.c
> @@ -1962,75 +1962,27 @@ sql_store_select(struct Parse *parse_context, struct Select *select)
> parse_context->parsed_ast.select = select_copy;
> }
>
> -/**
> - * Create expression record "@col_name = '@col_value'".
> - *
> - * @param parse The parsing context.
> - * @param col_name Name of column.
> - * @param col_value Name of row.
> - * @retval not NULL on success.
> - * @retval NULL on failure.
> - */
> -static struct Expr *
> -sql_id_eq_str_expr(struct Parse *parse, const char *col_name,
> - const char *col_value)
> -{
> - struct sqlite3 *db = parse->db;
> -
> - struct Expr *col_name_expr = sqlite3Expr(db, TK_ID, col_name);
> - if (col_name_expr == NULL)
> - return NULL;
> - struct Expr *col_value_expr = sqlite3Expr(db, TK_STRING, col_value);
> - if (col_value_expr == NULL) {
> - sql_expr_delete(db, col_name_expr, false);
> - return NULL;
> - }
> - return sqlite3PExpr(parse, TK_EQ, col_name_expr, col_value_expr);
> -}
> -
> void
> -vdbe_emit_stat_space_clear(struct Parse *parse, const char *stat_table_name,
> - const char *idx_name, const char *table_name)
> +sql_remove_from_stat(struct Parse *parse, const char *stat_table_name,
> + int table_id, int index_id, bool is_index_received)
5. Use index_id == BOX_ID_NIL instead of is_index_received.
> {
> - assert(idx_name != NULL || table_name != NULL);
> struct sqlite3 *db = parse->db;
> assert(!db->mallocFailed);
> struct SrcList *src_list = sql_alloc_src_list(db);
> if (src_list != NULL)
> src_list->a[0].zName = sqlite3DbStrDup(db, stat_table_name);
> - struct Expr *where = NULL;
> - if (idx_name != NULL) {
> - struct Expr *expr = sql_id_eq_str_expr(parse, "idx", idx_name);
> - if (expr != NULL)
> - where = sqlite3ExprAnd(db, expr, where);
> - }
> - if (table_name != NULL) {
> - struct Expr *expr = sql_id_eq_str_expr(parse, "tbl", table_name);
> - if (expr != NULL)
> - where = sqlite3ExprAnd(db, expr, where);
> - }
> - /**
> - * On memory allocation error sql_table delete_from
> - * releases memory for its own.
> - */
> - sql_table_delete_from(parse, src_list, where);
> -}
> @@ -2251,7 +2203,10 @@ sql_drop_table(struct Parse *parse_context, struct SrcList *table_name_list,
> goto exit_drop_table;
> }
> }
> - sql_clear_stat_spaces(parse_context, space_name, NULL);
> + sql_remove_from_stat(parse_context, TARANTOOL_SYS_SQL_STAT1_NAME,
> + space->def->id, 0, false);
> + sql_remove_from_stat(parse_context, TARANTOOL_SYS_SQL_STAT4_NAME,
> + space->def->id, 0, false);
6. The previous was better. Please, encapsulate list of stat tables
inside a function.
> sql_code_drop_table(parse_context, space, is_view);
>
> exit_drop_table:
> diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
> index b1f2f26..4ac1dae 100644
> --- a/src/box/sql/sqliteInt.h
> +++ b/src/box/sql/sqliteInt.h
> @@ -4864,11 +4864,13 @@ vdbe_emit_halt_with_presence_test(struct Parse *parser, int space_id,
> *
> * @param parse The parsing context.
> * @param stat_table_name System stat table name.
> - * @param idx_name Index name.
> - * @param table_name Table name.
> + * @param table_id Id of table of which analysis will be deleted.
> + * @param index_id Id of index of which analysis will be deleted.
> + * Used only if is_index_received is true.
7. Please, align multiple lines by the parameter name. 'Used' should
start at the same position as 'index_id.
> + * @param is_index_received True if index_id is given.
> */
> void
> -vdbe_emit_stat_space_clear(struct Parse *parse, const char *stat_table_name,
> - const char *idx_name, const char *table_name);
> +sql_remove_from_stat(struct Parse *parse, const char *stat_table_name,
> + int table_id, int index_id, bool is_index_received);
>
> #endif /* SQLITEINT_H */
> diff --git a/test/sql-tap/analyzeC.test.lua b/test/sql-tap/analyzeC.test.lua
> index a3cea70..1241ee8 100755
> --- a/test/sql-tap/analyzeC.test.lua
> +++ b/test/sql-tap/analyzeC.test.lua
> @@ -199,14 +209,16 @@ test:do_execsql_test(
> -- </4.1>
> })
>
> +
8. Redundant white space.
> test:do_execsql_test(
> 4.2,
> + string.format(
> [[
> DELETE FROM "_sql_stat1";
> - INSERT INTO "_sql_stat1"("tbl","idx","stat") VALUES('t1','t1bc','12345 3 2 sz=20'),('t1','t1db','12345 3 2 sz=10');
> + INSERT INTO "_sql_stat1"("tbl","idx","stat") VALUES(%i,%i,'12345 3 2 sz=20'),(%i,%i,'12345 3 2 sz=10');
> ANALYZE;
> SELECT count(b) FROM t1;
> - ]], {
> + ]], t1.id, t1.index['T1BC'].id, t1.id, t1.index['T1DB'].id), {
> -- <4.2>
> 6
> -- </4.2>
> @@ -251,14 +263,15 @@ test:do_execsql_test(
> -- </5.1>
> })
>
> +
Here the same.
> test:do_execsql_test(
> 5.2,
> - [[
> + string.format([[
> DELETE FROM "_sql_stat1";
> - INSERT INTO "_sql_stat1"("tbl","idx","stat") VALUES('t1','t1db','12345 3 2 x=5 sz=10 y=10'), ('t1','t1bc','12345 3 2 whatever sz=20 junk');
> + INSERT INTO "_sql_stat1"("tbl","idx","stat") VALUES(%i,%i,'12345 3 2 x=5 sz=10 y=10'), (%i,%i,'12345 3 2 whatever sz=20 junk');
> ANALYZE;
> SELECT count(b) FROM t1;
> - ]], {
> + ]], t1.id, t1.index['T1BC'].id, t1.id, t1.index['T1DB'].id), {
> -- <5.2>
> 6
> -- </5.2>
^ permalink raw reply [flat|nested] 4+ messages in thread
* [tarantool-patches] Re: [PATCH v1 1/1] sql: hold in stat tables space/index id instead of name
2018-08-27 16:32 ` [tarantool-patches] " Vladislav Shpilevoy
@ 2018-08-29 11:37 ` Imeev Mergen
2018-08-29 19:27 ` Vladislav Shpilevoy
0 siblings, 1 reply; 4+ messages in thread
From: Imeev Mergen @ 2018-08-29 11:37 UTC (permalink / raw)
To: Vladislav Shpilevoy, tarantool-patches
Hello! Thanks for review!
On 08/27/2018 07:32 PM, Vladislav Shpilevoy wrote:
> Hi! Thanks for the patch! See 8 comments below.
>
> On 23/08/2018 06:51, imeevma@tarantool.org wrote:
>> There is no reason to hold in statistic tables names of spaces and
>> indexes. This patch allows us to save id of space/index instead of
>> name.
>
> 1. Please, explain why ids are better than names. 'No reason'
> is not a reason. Actually the real point of storing ids is to
> avoid problems with table and index renaming. Ids are fixed
> values on the contrary.
Done.
>
>>
>> Closes #3242
>> ---
>> Branch:
>> https://github.com/tarantool/tarantool/tree/imeevma/gh-3242-hold-ids-in-stat-tables
>> Issue: https://github.com/tarantool/tarantool/issues/3242
>>
>> src/box/bootstrap.snap | Bin 1818 -> 1822 bytes
>> src/box/lua/upgrade.lua | 12 ++--
>> src/box/schema.cc | 8 +--
>> src/box/sql.c | 8 +--
>> src/box/sql/analyze.c | 114
>> ++++++++++---------------------
>> src/box/sql/build.c | 87 +++++++-----------------
>> src/box/sql/sqliteInt.h | 10 +--
>> test/box-py/bootstrap.result | 17 ++---
>> test/box/access_misc.result | 11 +--
>> test/box/alter.result | 6 +-
>> test/sql-tap/analyze1.test.lua | 100
>> ++++++++++++++-------------
>> test/sql-tap/analyze4.test.lua | 79 ++++++++++++----------
>> test/sql-tap/analyze5.test.lua | 11 +--
>> test/sql-tap/analyze9.test.lua | 115
>> ++++++++++++--------------------
>> test/sql-tap/analyzeC.test.lua | 51 ++++++++------
>> test/sql-tap/analyzeD.test.lua | 24 +++----
>> test/sql-tap/gh-3350-skip-scan.test.lua | 15 +++--
>> test/sql/sql-statN-index-drop.result | 72 ++++++++++----------
>> test/sql/sql-statN-index-drop.test.lua | 16 ++---
>> test/sql/upgrade.result | 14 ++--
>> 20 files changed, 346 insertions(+), 424 deletions(-)
>>
>> diff --git a/src/box/sql/analyze.c b/src/box/sql/analyze.c
>> index 00d96d2..86776ad 100644
>> --- a/src/box/sql/analyze.c
>> +++ b/src/box/sql/analyze.c
>> @@ -124,28 +124,27 @@
>> * @param parse Parsing context.
>> * @param stat_cursor Open the _sql_stat1 table on this cursor.
>> * you should allocate |stat_names| cursors before call.
>> - * @param table_name Delete records of this index if specified.
>> + * @param table_id Delete records of this table if id is not 0.
>
> 2. Please, use BOX_ID_NIL constant.
Done.
>
>> */
>> static void
>> -vdbe_emit_stat_space_open(struct Parse *parse, int stat_cursor,
>> - const char *table_name)
>> +vdbe_emit_stat_space_open(struct Parse *parse, int stat_cursor, int
>> table_id)
>
> 3. Space_id has uint32_t type.
Fixed.
>
>> @@ -837,8 +836,8 @@ analyzeOneTable(Parse * pParse, /* Parser
>> context */
>> idx_name = pIdx->def->name;
>> int part_count = pIdx->def->key_def->part_count;
>> - /* Populate the register containing the index name. */
>> - sqlite3VdbeLoadString(v, regIdxname, idx_name);
>> + /* Populate the register containing the index id. */
>> + sqlite3VdbeAddOp2(v, OP_Integer, pIdx->def->iid, regIdxname);
>
> 4. regIdxName is not correct now.
Fixed.
>
>> VdbeComment((v, "Analysis for %s.%s", pTab->def->name,
>> idx_name));
>> diff --git a/src/box/sql/build.c b/src/box/sql/build.c
>> index dddeb12..0d21ff0 100644
>> --- a/src/box/sql/build.c
>> +++ b/src/box/sql/build.c
>> @@ -1962,75 +1962,27 @@ sql_store_select(struct Parse *parse_context,
>> struct Select *select)
>> parse_context->parsed_ast.select = select_copy;
>> }
>> -/**
>> - * Create expression record "@col_name = '@col_value'".
>> - *
>> - * @param parse The parsing context.
>> - * @param col_name Name of column.
>> - * @param col_value Name of row.
>> - * @retval not NULL on success.
>> - * @retval NULL on failure.
>> - */
>> -static struct Expr *
>> -sql_id_eq_str_expr(struct Parse *parse, const char *col_name,
>> - const char *col_value)
>> -{
>> - struct sqlite3 *db = parse->db;
>> -
>> - struct Expr *col_name_expr = sqlite3Expr(db, TK_ID, col_name);
>> - if (col_name_expr == NULL)
>> - return NULL;
>> - struct Expr *col_value_expr = sqlite3Expr(db, TK_STRING,
>> col_value);
>> - if (col_value_expr == NULL) {
>> - sql_expr_delete(db, col_name_expr, false);
>> - return NULL;
>> - }
>> - return sqlite3PExpr(parse, TK_EQ, col_name_expr, col_value_expr);
>> -}
>> -
>> void
>> -vdbe_emit_stat_space_clear(struct Parse *parse, const char
>> *stat_table_name,
>> - const char *idx_name, const char *table_name)
>> +sql_remove_from_stat(struct Parse *parse, const char *stat_table_name,
>> + int table_id, int index_id, bool is_index_received)
>
> 5. Use index_id == BOX_ID_NIL instead of is_index_received.
Done.
>
>> {
>> - assert(idx_name != NULL || table_name != NULL);
>> struct sqlite3 *db = parse->db;
>> assert(!db->mallocFailed);
>> struct SrcList *src_list = sql_alloc_src_list(db);
>> if (src_list != NULL)
>> src_list->a[0].zName = sqlite3DbStrDup(db, stat_table_name);
>> - struct Expr *where = NULL;
>> - if (idx_name != NULL) {
>> - struct Expr *expr = sql_id_eq_str_expr(parse, "idx", idx_name);
>> - if (expr != NULL)
>> - where = sqlite3ExprAnd(db, expr, where);
>> - }
>> - if (table_name != NULL) {
>> - struct Expr *expr = sql_id_eq_str_expr(parse, "tbl",
>> table_name);
>> - if (expr != NULL)
>> - where = sqlite3ExprAnd(db, expr, where);
>> - }
>> - /**
>> - * On memory allocation error sql_table delete_from
>> - * releases memory for its own.
>> - */
>> - sql_table_delete_from(parse, src_list, where);
>> -}
>> @@ -2251,7 +2203,10 @@ sql_drop_table(struct Parse *parse_context,
>> struct SrcList *table_name_list,
>> goto exit_drop_table;
>> }
>> }
>> - sql_clear_stat_spaces(parse_context, space_name, NULL);
>> + sql_remove_from_stat(parse_context, TARANTOOL_SYS_SQL_STAT1_NAME,
>> + space->def->id, 0, false);
>> + sql_remove_from_stat(parse_context, TARANTOOL_SYS_SQL_STAT4_NAME,
>> + space->def->id, 0, false);
>
> 6. The previous was better. Please, encapsulate list of stat tables
> inside a function.
Done.
>
>> sql_code_drop_table(parse_context, space, is_view);
>> exit_drop_table:
>> diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
>> index b1f2f26..4ac1dae 100644
>> --- a/src/box/sql/sqliteInt.h
>> +++ b/src/box/sql/sqliteInt.h
>> @@ -4864,11 +4864,13 @@ vdbe_emit_halt_with_presence_test(struct
>> Parse *parser, int space_id,
>> *
>> * @param parse The parsing context.
>> * @param stat_table_name System stat table name.
>> - * @param idx_name Index name.
>> - * @param table_name Table name.
>> + * @param table_id Id of table of which analysis will be deleted.
>> + * @param index_id Id of index of which analysis will be deleted.
>> + * Used only if is_index_received is true.
>
> 7. Please, align multiple lines by the parameter name. 'Used' should
> start at the same position as 'index_id.
Fixed.
>
>> + * @param is_index_received True if index_id is given.
>> */
>> void
>> -vdbe_emit_stat_space_clear(struct Parse *parse, const char
>> *stat_table_name,
>> - const char *idx_name, const char *table_name);
>> +sql_remove_from_stat(struct Parse *parse, const char *stat_table_name,
>> + int table_id, int index_id, bool is_index_received);
>> #endif /* SQLITEINT_H */
>> diff --git a/test/sql-tap/analyzeC.test.lua
>> b/test/sql-tap/analyzeC.test.lua
>> index a3cea70..1241ee8 100755
>> --- a/test/sql-tap/analyzeC.test.lua
>> +++ b/test/sql-tap/analyzeC.test.lua
>> @@ -199,14 +209,16 @@ test:do_execsql_test(
>> -- </4.1>
>> })
>> +
>
> 8. Redundant white space.
Fixed.
>
>> test:do_execsql_test(
>> 4.2,
>> + string.format(
>> [[
>> DELETE FROM "_sql_stat1";
>> - INSERT INTO "_sql_stat1"("tbl","idx","stat")
>> VALUES('t1','t1bc','12345 3 2 sz=20'),('t1','t1db','12345 3 2 sz=10');
>> + INSERT INTO "_sql_stat1"("tbl","idx","stat")
>> VALUES(%i,%i,'12345 3 2 sz=20'),(%i,%i,'12345 3 2 sz=10');
>> ANALYZE;
>> SELECT count(b) FROM t1;
>> - ]], {
>> + ]], t1.id, t1.index['T1BC'].id, t1.id, t1.index['T1DB'].id), {
>> -- <4.2>
>> 6
>> -- </4.2>
>> @@ -251,14 +263,15 @@ test:do_execsql_test(
>> -- </5.1>
>> })
>> +
>
> Here the same.
Fixed.
>
>> test:do_execsql_test(
>> 5.2,
>> - [[
>> + string.format([[
>> DELETE FROM "_sql_stat1";
>> - INSERT INTO "_sql_stat1"("tbl","idx","stat")
>> VALUES('t1','t1db','12345 3 2 x=5 sz=10 y=10'), ('t1','t1bc','12345 3
>> 2 whatever sz=20 junk');
>> + INSERT INTO "_sql_stat1"("tbl","idx","stat")
>> VALUES(%i,%i,'12345 3 2 x=5 sz=10 y=10'), (%i,%i,'12345 3 2 whatever
>> sz=20 junk');
>> ANALYZE;
>> SELECT count(b) FROM t1;
>> - ]], {
>> + ]], t1.id, t1.index['T1BC'].id, t1.id, t1.index['T1DB'].id), {
>> -- <5.2>
>> 6
>> -- </5.2>
commit ba9b75c336853a01676e758c671584e7ebd39b9b
Author: Mergen Imeev <imeevma@gmail.com>
Date: Mon Aug 20 18:29:23 2018 +0300
sql: hold in stat tables space/index id instead of name
To avoid problems with table and index renaming it is good idea
to save ids of tables and indexes instead of their names. Ids of
tables and indexes are fixed values.
Closes #3242
diff --git a/src/box/bootstrap.snap b/src/box/bootstrap.snap
index 10f77f6..8c154e1 100644
Binary files a/src/box/bootstrap.snap and b/src/box/bootstrap.snap differ
diff --git a/src/box/lua/upgrade.lua b/src/box/lua/upgrade.lua
index 8a30e9f..0d5391e 100644
--- a/src/box/lua/upgrade.lua
+++ b/src/box/lua/upgrade.lua
@@ -482,11 +482,11 @@ local function upgrade_to_2_1_0()
_index:insert{_trigger.id, 1, 'space_id', 'tree', { unique = false },
{{1, 'unsigned'}}}
- local stat1_ft = {{name='tbl', type='string'},
- {name='idx', type='string'},
+ local stat1_ft = {{name='tbl', type='unsigned'},
+ {name='idx', type='unsigned'},
{name='stat', type='string'}}
- local stat4_ft = {{name='tbl', type='string'},
- {name='idx', type='string'},
+ local stat4_ft = {{name='tbl', type='unsigned'},
+ {name='idx', type='unsigned'},
{name='neq', type='string'},
{name='nlt', type='string'},
{name='ndlt', type='string'},
@@ -498,7 +498,7 @@ local function upgrade_to_2_1_0()
log.info("create index primary on _sql_stat1")
_index:insert{box.schema.SQL_STAT1_ID, 0, 'primary', 'tree',
- {unique = true}, {{0, 'string'}, {1, 'string'}}}
+ {unique = true}, {{0, 'unsigned'}, {1, 'unsigned'}}}
log.info("create space _sql_stat4")
_space:insert{box.schema.SQL_STAT4_ID, ADMIN, '_sql_stat4',
'memtx', 0,
@@ -506,7 +506,7 @@ local function upgrade_to_2_1_0()
log.info("create index primary on _sql_stat4")
_index:insert{box.schema.SQL_STAT4_ID, 0, 'primary', 'tree',
- {unique = true}, {{0, 'string'}, {1, 'string'},
+ {unique = true}, {{0, 'unsigned'}, {1, 'unsigned'},
{5, 'scalar'}}}
local fk_constr_ft = {{name='name', type='string'},
diff --git a/src/box/schema.cc b/src/box/schema.cc
index aa4ac32..2c6c280 100644
--- a/src/box/schema.cc
+++ b/src/box/schema.cc
@@ -384,11 +384,11 @@ schema_init()
/* space name */
key_def_set_part(key_def, 0 /* part no */, 0 /* field no */,
- FIELD_TYPE_STRING, ON_CONFLICT_ACTION_DEFAULT, NULL,
+ FIELD_TYPE_UNSIGNED, ON_CONFLICT_ACTION_DEFAULT, NULL,
COLL_NONE, SORT_ORDER_ASC);
/* index name */
key_def_set_part(key_def, 1 /* part no */, 1 /* field no */,
- FIELD_TYPE_STRING, ON_CONFLICT_ACTION_DEFAULT, NULL,
+ FIELD_TYPE_UNSIGNED, ON_CONFLICT_ACTION_DEFAULT, NULL,
COLL_NONE, SORT_ORDER_ASC);
/* _sql_stat1 - a simpler statistics on space, seen in SQL. */
sc_space_new(BOX_SQL_STAT1_ID, "_sql_stat1", key_def, NULL, NULL);
@@ -400,11 +400,11 @@ schema_init()
/* space name */
key_def_set_part(key_def, 0 /* part no */, 0 /* field no */,
- FIELD_TYPE_STRING, ON_CONFLICT_ACTION_DEFAULT, NULL,
+ FIELD_TYPE_UNSIGNED, ON_CONFLICT_ACTION_DEFAULT, NULL,
COLL_NONE, SORT_ORDER_ASC);
/* index name */
key_def_set_part(key_def, 1 /* part no */, 1 /* field no */,
- FIELD_TYPE_STRING, ON_CONFLICT_ACTION_DEFAULT, NULL,
+ FIELD_TYPE_UNSIGNED, ON_CONFLICT_ACTION_DEFAULT, NULL,
COLL_NONE, SORT_ORDER_ASC);
/* sample */
key_def_set_part(key_def, 2 /* part no */, 5 /* field no */,
diff --git a/src/box/sql.c b/src/box/sql.c
index ae12cae..e5411f2 100644
--- a/src/box/sql.c
+++ b/src/box/sql.c
@@ -1148,16 +1148,16 @@ void tarantoolSqlite3LoadSchema(struct init_data
*init)
sql_init_callback(init, TARANTOOL_SYS_SQL_STAT1_NAME,
BOX_SQL_STAT1_ID, 0,
"CREATE TABLE \""TARANTOOL_SYS_SQL_STAT1_NAME
- "\"(\"tbl\" text,"
- "\"idx\" text,"
+ "\"(\"tbl\" INT,"
+ "\"idx\" INT,"
"\"stat\" not null,"
"PRIMARY KEY(\"tbl\", \"idx\"))");
sql_init_callback(init, TARANTOOL_SYS_SQL_STAT4_NAME,
BOX_SQL_STAT4_ID, 0,
"CREATE TABLE \""TARANTOOL_SYS_SQL_STAT4_NAME
- "\"(\"tbl\" text,"
- "\"idx\" text,"
+ "\"(\"tbl\" INT,"
+ "\"idx\" INT,"
"\"neq\" text,"
"\"nlt\" text,"
"\"ndlt\" text,"
diff --git a/src/box/sql/analyze.c b/src/box/sql/analyze.c
index 00d96d2..a7cc173 100644
--- a/src/box/sql/analyze.c
+++ b/src/box/sql/analyze.c
@@ -124,28 +124,29 @@
* @param parse Parsing context.
* @param stat_cursor Open the _sql_stat1 table on this cursor.
* you should allocate |stat_names| cursors before call.
- * @param table_name Delete records of this index if specified.
+ * @param space_id Delete records of this table if id is not
+ * BOX_ID_NIL.
*/
static void
vdbe_emit_stat_space_open(struct Parse *parse, int stat_cursor,
- const char *table_name)
+ uint32_t space_id)
{
- const char *stat_names[] = {"_sql_stat1", "_sql_stat4"};
+ const char *stat_names[] = {TARANTOOL_SYS_SQL_STAT1_NAME,
+ TARANTOOL_SYS_SQL_STAT4_NAME};
const uint32_t stat_ids[] = {BOX_SQL_STAT1_ID, BOX_SQL_STAT4_ID};
struct Vdbe *v = sqlite3GetVdbe(parse);
assert(v != NULL);
assert(sqlite3VdbeDb(v) == parse->db);
for (uint i = 0; i < lengthof(stat_names); ++i) {
- const char *space_name = stat_names[i];
/*
* The table already exists, because it is a
* system space.
*/
assert(sqlite3HashFind(&parse->db->pSchema->tblHash,
- space_name) != NULL);
- if (table_name != NULL) {
- vdbe_emit_stat_space_clear(parse, space_name, NULL,
- table_name);
+ stat_names[i]) != NULL);
+ if (space_id != BOX_ID_NIL) {
+ sql_remove_from_stat(parse, stat_names[i], space_id,
+ BOX_ID_NIL);
} else {
sqlite3VdbeAddOp1(v, OP_Clear, stat_ids[i]);
}
@@ -793,8 +794,8 @@ analyzeOneTable(Parse * pParse, /* Parser context */
int regChng = iMem++; /* Index of changed index field */
int regKey = iMem++; /* Key argument passed to stat_push() */
int regTemp = iMem++; /* Temporary use register */
- int regTabname = iMem++; /* Register containing table name */
- int regIdxname = iMem++; /* Register containing index name */
+ int reg_space_id = iMem++; /* Register containing table id */
+ int reg_index_id = iMem++; /* Register containing index id */
int regStat1 = iMem++; /* Value for the stat column of
_sql_stat1 */
int regPrev = iMem; /* MUST BE LAST (see below) */
@@ -818,7 +819,7 @@ analyzeOneTable(Parse * pParse, /* Parser context */
iIdxCur = iTab++;
pParse->nTab = MAX(pParse->nTab, iTab);
sqlite3OpenTable(pParse, iTabCur, pTab, OP_OpenRead);
- sqlite3VdbeLoadString(v, regTabname, pTab->def->name);
+ sqlite3VdbeAddOp2(v, OP_Integer, pTab->def->id, reg_space_id);
for (pIdx = pTab->pIndex; pIdx; pIdx = pIdx->pNext) {
int addrRewind; /* Address of "OP_Rewind iIdxCur" */
@@ -837,8 +838,8 @@ analyzeOneTable(Parse * pParse, /* Parser context */
idx_name = pIdx->def->name;
int part_count = pIdx->def->key_def->part_count;
- /* Populate the register containing the index name. */
- sqlite3VdbeLoadString(v, regIdxname, idx_name);
+ /* Populate the register containing the index id. */
+ sqlite3VdbeAddOp2(v, OP_Integer, pIdx->def->iid, reg_index_id);
VdbeComment((v, "Analysis for %s.%s", pTab->def->name,
idx_name));
@@ -1015,9 +1016,10 @@ analyzeOneTable(Parse * pParse, /* Parser
context */
/* Add the entry to the stat1 table. */
callStatGet(v, regStat4, STAT_GET_STAT1, regStat1);
- assert("BBB"[0] == AFFINITY_TEXT);
- sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp,
- "BBB", 0);
+ assert("DDB"[0] == AFFINITY_INTEGER);
+ assert("BBB"[2] == AFFINITY_TEXT);
+ sqlite3VdbeAddOp4(v, OP_MakeRecord, reg_space_id, 3, regTemp,
+ "DDB", 0);
sqlite3VdbeAddOp2(v, OP_IdxInsert, iStatCur, regTemp);
/* Add the entries to the stat4 table. */
@@ -1053,7 +1055,7 @@ analyzeOneTable(Parse * pParse, /* Parser
context */
}
sqlite3VdbeAddOp3(v, OP_MakeRecord, regCol, part_count,
regSample);
- sqlite3VdbeAddOp3(v, OP_MakeRecord, regTabname, 6, regTemp);
+ sqlite3VdbeAddOp3(v, OP_MakeRecord, reg_space_id, 6, regTemp);
sqlite3VdbeAddOp2(v, OP_IdxReplace, iStatCur + 1, regTemp);
sqlite3VdbeAddOp2(v, OP_Goto, 1, addrNext); /* P1==1 for
end-of-loop */
sqlite3VdbeJumpHere(v, addrIsNull);
@@ -1086,7 +1088,7 @@ sql_analyze_database(Parse *parser)
sql_set_multi_write(parser, false);
int stat_cursor = parser->nTab;
parser->nTab += 3;
- vdbe_emit_stat_space_open(parser, stat_cursor, NULL);
+ vdbe_emit_stat_space_open(parser, stat_cursor, BOX_ID_NIL);
int reg = parser->nMem + 1;
int tab_cursor = parser->nTab;
for (struct HashElem *k = sqliteHashFirst(&schema->tblHash); k !=
NULL;
@@ -1114,7 +1116,7 @@ vdbe_emit_analyze_table(struct Parse *parse,
struct Table *table)
sql_set_multi_write(parse, false);
int stat_cursor = parse->nTab;
parse->nTab += 3;
- vdbe_emit_stat_space_open(parse, stat_cursor, table->def->name);
+ vdbe_emit_stat_space_open(parse, stat_cursor, table->def->id);
analyzeOneTable(parse, table, NULL, stat_cursor, parse->nMem + 1,
parse->nTab);
loadAnalysis(parse);
@@ -1246,24 +1248,14 @@ analysis_loader(void *data, int argc, char
**argv, char **unused)
struct analysis_index_info *info = (struct analysis_index_info *)
data;
assert(info->stats != NULL);
struct index_stat *stat = &info->stats[info->index_count++];
- uint32_t space_id = box_space_id_by_name(argv[0], strlen(argv[0]));
+ uint32_t space_id = atoll(argv[0]);
if (space_id == BOX_ID_NIL)
return -1;
struct space *space = space_by_id(space_id);
assert(space != NULL);
struct index *index;
- uint32_t iid = box_index_id_by_name(space_id, argv[1],
strlen(argv[1]));
- /*
- * Convention is if index's name matches with space's
- * one, then it is primary index.
- */
- if (iid != BOX_ID_NIL) {
- index = space_index(space, iid);
- } else {
- if (sqlite3_stricmp(argv[0], argv[1]) != 0)
- return -1;
- index = space_index(space, 0);
- }
+ uint32_t iid = atoll(argv[1]);
+ index = space_index(space, iid);
assert(index != NULL);
/*
* Additional field is used to describe total
@@ -1410,27 +1402,17 @@ load_stat_from_space(struct sqlite3 *db, const
char *sql_select_prepare,
goto finalize;
uint32_t current_idx_count = 0;
while (sqlite3_step(stmt) == SQLITE_ROW) {
- const char *space_name = (char *)sqlite3_column_text(stmt, 0);
- if (space_name == NULL)
- continue;
- const char *index_name = (char *)sqlite3_column_text(stmt, 1);
- if (index_name == NULL)
+ if (sqlite3_column_text(stmt, 0) == NULL ||
+ sqlite3_column_text(stmt, 1) == NULL)
continue;
- uint32_t sample_count = sqlite3_column_int(stmt, 2);
- uint32_t space_id = box_space_id_by_name(space_name,
- strlen(space_name));
+ uint32_t space_id = sqlite3_column_int(stmt, 0);
assert(space_id != BOX_ID_NIL);
struct space *space = space_by_id(space_id);
assert(space != NULL);
- struct index *index;
- uint32_t iid = box_index_id_by_name(space_id, index_name,
- strlen(index_name));
- if (sqlite3_stricmp(space_name, index_name) == 0 &&
- iid == BOX_ID_NIL)
- index = space_index(space, 0);
- else
- index = space_index(space, iid);
+ uint32_t iid = sqlite3_column_int(stmt, 1);
+ struct index *index = space_index(space, iid);
assert(index != NULL);
+ uint32_t sample_count = sqlite3_column_int(stmt, 2);
uint32_t column_count = index->def->key_def->part_count;
struct index_stat *stat = &stats[current_idx_count];
stat->sample_field_count = column_count;
@@ -1482,27 +1464,15 @@ load_stat_from_space(struct sqlite3 *db, const
char *sql_select_prepare,
struct index *prev_index = NULL;
current_idx_count = 0;
while (sqlite3_step(stmt) == SQLITE_ROW) {
- const char *space_name = (char *)sqlite3_column_text(stmt, 0);
- if (space_name == NULL)
- continue;
- const char *index_name = (char *)sqlite3_column_text(stmt, 1);
- if (index_name == NULL)
+ if (sqlite3_column_text(stmt, 0) == NULL ||
+ sqlite3_column_text(stmt, 1) == NULL)
continue;
- uint32_t space_id = box_space_id_by_name(space_name,
- strlen(space_name));
+ uint32_t space_id = sqlite3_column_int(stmt, 0);
assert(space_id != BOX_ID_NIL);
struct space *space = space_by_id(space_id);
assert(space != NULL);
- struct index *index;
- uint32_t iid = box_index_id_by_name(space_id, index_name,
- strlen(index_name));
- if (iid != BOX_ID_NIL) {
- index = space_index(space, iid);
- } else {
- if (sqlite3_stricmp(space_name, index_name) != 0)
- return -1;
- index = space_index(space, 0);
- }
+ uint32_t iid = sqlite3_column_int(stmt, 1);
+ struct index *index = space_index(space, iid);
assert(index != NULL);
uint32_t column_count = index->def->key_def->part_count;
if (index != prev_index) {
@@ -1566,28 +1536,16 @@ load_stat_to_index(struct sqlite3 *db, const
char *sql_select_load,
return -1;
uint32_t current_idx_count = 0;
while (sqlite3_step(stmt) == SQLITE_ROW) {
- const char *space_name = (char *)sqlite3_column_text(stmt, 0);
- if (space_name == NULL)
+ if (sqlite3_column_text(stmt, 0) == NULL ||
+ sqlite3_column_text(stmt, 1) == NULL)
continue;
- const char *index_name = (char *)sqlite3_column_text(stmt, 1);
- if (index_name == NULL)
- continue;
- uint32_t space_id = box_space_id_by_name(space_name,
- strlen(space_name));
+ uint32_t space_id = sqlite3_column_int(stmt, 0);
if (space_id == BOX_ID_NIL)
return -1;
struct space *space = space_by_id(space_id);
assert(space != NULL);
- struct index *index;
- uint32_t iid = box_index_id_by_name(space_id, index_name,
- strlen(index_name));
- if (iid != BOX_ID_NIL) {
- index = space_index(space, iid);
- } else {
- if (sqlite3_stricmp(space_name, index_name) != 0)
- return -1;
- index = space_index(space, 0);
- }
+ uint32_t iid = sqlite3_column_int(stmt, 1);
+ struct index *index = space_index(space, iid);
assert(index != NULL);
free(index->def->opts.stat);
index->def->opts.stat = stats[current_idx_count++];
diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index dddeb12..aab67b4 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -1962,75 +1962,45 @@ sql_store_select(struct Parse *parse_context,
struct Select *select)
parse_context->parsed_ast.select = select_copy;
}
-/**
- * Create expression record "@col_name = '@col_value'".
- *
- * @param parse The parsing context.
- * @param col_name Name of column.
- * @param col_value Name of row.
- * @retval not NULL on success.
- * @retval NULL on failure.
- */
-static struct Expr *
-sql_id_eq_str_expr(struct Parse *parse, const char *col_name,
- const char *col_value)
-{
- struct sqlite3 *db = parse->db;
-
- struct Expr *col_name_expr = sqlite3Expr(db, TK_ID, col_name);
- if (col_name_expr == NULL)
- return NULL;
- struct Expr *col_value_expr = sqlite3Expr(db, TK_STRING, col_value);
- if (col_value_expr == NULL) {
- sql_expr_delete(db, col_name_expr, false);
- return NULL;
- }
- return sqlite3PExpr(parse, TK_EQ, col_name_expr, col_value_expr);
-}
-
void
-vdbe_emit_stat_space_clear(struct Parse *parse, const char
*stat_table_name,
- const char *idx_name, const char *table_name)
+sql_remove_from_stat(struct Parse *parse, const char *stat_table_name,
+ uint32_t space_id, uint32_t index_id)
{
- assert(idx_name != NULL || table_name != NULL);
struct sqlite3 *db = parse->db;
assert(!db->mallocFailed);
struct SrcList *src_list = sql_alloc_src_list(db);
if (src_list != NULL)
src_list->a[0].zName = sqlite3DbStrDup(db, stat_table_name);
- struct Expr *where = NULL;
- if (idx_name != NULL) {
- struct Expr *expr = sql_id_eq_str_expr(parse, "idx", idx_name);
- if (expr != NULL)
- where = sqlite3ExprAnd(db, expr, where);
- }
- if (table_name != NULL) {
- struct Expr *expr = sql_id_eq_str_expr(parse, "tbl", table_name);
- if (expr != NULL)
- where = sqlite3ExprAnd(db, expr, where);
- }
- /**
- * On memory allocation error sql_table delete_from
- * releases memory for its own.
- */
- sql_table_delete_from(parse, src_list, where);
+ struct Expr *expr = NULL;
+ struct Expr *col = sqlite3Expr(db, TK_ID, "tbl");
+ struct Expr *val = sqlite3ExprInteger(db, space_id);
+ if (col != NULL && val != NULL)
+ expr = sqlite3PExpr(parse, TK_EQ, col, val);
+ if (index_id != BOX_ID_NIL && expr != NULL) {
+ col = sqlite3Expr(db, TK_ID, "idx");
+ val = sqlite3ExprInteger(db, index_id);
+ expr = sqlite3ExprAnd(db, sqlite3PExpr(parse, TK_EQ, col, val),
+ expr);
+ }
+ sql_table_delete_from(parse, src_list, expr);
}
/**
- * Remove entries from the _sql_stat1 and _sql_stat4
- * system spaces after a DROP INDEX or DROP TABLE command.
+ * Remove entries from the _sql_stat1 and _sql_stat4 system spaces
+ * after a DROP INDEX or DROP TABLE command.
*
- * @param parse The parsing context.
- * @param table_name The table to be dropped or
- * the table that contains index to be dropped.
- * @param idx_name Index to be dropped.
+ * @param parse The parsing context.
+ * @param table_name The table to be dropped or the table that
+ * contains index to be dropped.
+ * @param idx_name Index to be dropped.
*/
static void
-sql_clear_stat_spaces(struct Parse *parse, const char *table_name,
- const char *idx_name)
+sql_clear_stat_spaces(struct Parse *parse, uint32_t space_id, uint32_t
index_id)
{
- vdbe_emit_stat_space_clear(parse, "_sql_stat4", idx_name, table_name);
- vdbe_emit_stat_space_clear(parse, "_sql_stat1", idx_name, table_name);
+ sql_remove_from_stat(parse, TARANTOOL_SYS_SQL_STAT1_NAME, space_id,
+ index_id);
+ sql_remove_from_stat(parse, TARANTOOL_SYS_SQL_STAT4_NAME, space_id,
+ index_id);
}
/**
@@ -2251,7 +2221,7 @@ sql_drop_table(struct Parse *parse_context, struct
SrcList *table_name_list,
goto exit_drop_table;
}
}
- sql_clear_stat_spaces(parse_context, space_name, NULL);
+ sql_clear_stat_spaces(parse_context, space->def->id, BOX_ID_NIL);
sql_code_drop_table(parse_context, space, is_view);
exit_drop_table:
@@ -3230,11 +3200,12 @@ sql_drop_index(struct Parse *parse_context,
struct SrcList *index_name_list,
}
/*
- * Generate code to remove entry from _index space
- * But firstly, delete statistics since schema
- * changes after DDL.
+ * Generate code to remove entry from _index space.
+ * But firstly, delete statistics since schema changes
+ * after DDL.
*/
- sql_clear_stat_spaces(parse_context, table_name, index->def->name);
+ sql_clear_stat_spaces(parse_context, space->def->id, index->def->iid);
+
int record_reg = ++parse_context->nMem;
int space_id_reg = ++parse_context->nMem;
sqlite3VdbeAddOp2(v, OP_Integer, space_id, space_id_reg);
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index b1f2f26..34050ad 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -4864,11 +4864,13 @@ vdbe_emit_halt_with_presence_test(struct Parse
*parser, int space_id,
*
* @param parse The parsing context.
* @param stat_table_name System stat table name.
- * @param idx_name Index name.
- * @param table_name Table name.
+ * @param table_id Id of table of which analysis will be deleted.
+ * @param index_id Id of index of which analysis will be deleted.
+ * If index_id is BOX_ID_NIL than analysis of all indexes
+ * of selected table will be deleted.
*/
void
-vdbe_emit_stat_space_clear(struct Parse *parse, const char
*stat_table_name,
- const char *idx_name, const char *table_name);
+sql_remove_from_stat(struct Parse *parse, const char *stat_table_name,
+ uint32_t table_id, uint32_t index_id);
#endif /* SQLITEINT_H */
diff --git a/test/box-py/bootstrap.result b/test/box-py/bootstrap.result
index fb7d7da..a7da8c1 100644
--- a/test/box-py/bootstrap.result
+++ b/test/box-py/bootstrap.result
@@ -71,11 +71,12 @@ box.space._space:select{}
'type': 'unsigned'}]]
- [340, 1, '_space_sequence', 'memtx', 0, {}, [{'name': 'id',
'type': 'unsigned'},
{'name': 'sequence_id', 'type': 'unsigned'}, {'name':
'is_generated', 'type': 'boolean'}]]
- - [348, 1, '_sql_stat1', 'memtx', 0, {}, [{'name': 'tbl', 'type':
'string'}, {'name': 'idx',
- 'type': 'string'}, {'name': 'stat', 'type': 'string'}]]
- - [349, 1, '_sql_stat4', 'memtx', 0, {}, [{'name': 'tbl', 'type':
'string'}, {'name': 'idx',
- 'type': 'string'}, {'name': 'neq', 'type': 'string'}, {'name':
'nlt', 'type': 'string'},
- {'name': 'ndlt', 'type': 'string'}, {'name': 'sample', 'type':
'scalar'}]]
+ - [348, 1, '_sql_stat1', 'memtx', 0, {}, [{'name': 'tbl', 'type':
'unsigned'}, {
+ 'name': 'idx', 'type': 'unsigned'}, {'name': 'stat', 'type':
'string'}]]
+ - [349, 1, '_sql_stat4', 'memtx', 0, {}, [{'name': 'tbl', 'type':
'unsigned'}, {
+ 'name': 'idx', 'type': 'unsigned'}, {'name': 'neq', 'type':
'string'}, {'name': 'nlt',
+ 'type': 'string'}, {'name': 'ndlt', 'type': 'string'}, {'name':
'sample',
+ 'type': 'scalar'}]]
- [356, 1, '_fk_constraint', 'memtx', 0, {}, [{'name': 'name',
'type': 'string'},
{'name': 'child_id', 'type': 'unsigned'}, {'name': 'parent_id',
'type': 'unsigned'},
{'name': 'is_deferred', 'type': 'boolean'}, {'name': 'match',
'type': 'string'},
@@ -131,9 +132,9 @@ box.space._index:select{}
- [330, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned']]]
- [340, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned']]]
- [340, 1, 'sequence', 'tree', {'unique': false}, [[1, 'unsigned']]]
- - [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1,
'string']]]
- - [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1,
'string'], [
- 5, 'scalar']]]
+ - [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1,
'unsigned']]]
+ - [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1,
'unsigned'],
+ [5, 'scalar']]]
- [356, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1,
'unsigned']]]
- [356, 1, 'child_id', 'tree', {'unique': false}, [[1, 'unsigned']]]
...
diff --git a/test/box/access_misc.result b/test/box/access_misc.result
index 3ba33ee..622409c 100644
--- a/test/box/access_misc.result
+++ b/test/box/access_misc.result
@@ -810,11 +810,12 @@ box.space._space:select()
'type': 'unsigned'}]]
- [340, 1, '_space_sequence', 'memtx', 0, {}, [{'name': 'id',
'type': 'unsigned'},
{'name': 'sequence_id', 'type': 'unsigned'}, {'name':
'is_generated', 'type': 'boolean'}]]
- - [348, 1, '_sql_stat1', 'memtx', 0, {}, [{'name': 'tbl', 'type':
'string'}, {'name': 'idx',
- 'type': 'string'}, {'name': 'stat', 'type': 'string'}]]
- - [349, 1, '_sql_stat4', 'memtx', 0, {}, [{'name': 'tbl', 'type':
'string'}, {'name': 'idx',
- 'type': 'string'}, {'name': 'neq', 'type': 'string'}, {'name':
'nlt', 'type': 'string'},
- {'name': 'ndlt', 'type': 'string'}, {'name': 'sample', 'type':
'scalar'}]]
+ - [348, 1, '_sql_stat1', 'memtx', 0, {}, [{'name': 'tbl', 'type':
'unsigned'}, {
+ 'name': 'idx', 'type': 'unsigned'}, {'name': 'stat', 'type':
'string'}]]
+ - [349, 1, '_sql_stat4', 'memtx', 0, {}, [{'name': 'tbl', 'type':
'unsigned'}, {
+ 'name': 'idx', 'type': 'unsigned'}, {'name': 'neq', 'type':
'string'}, {'name': 'nlt',
+ 'type': 'string'}, {'name': 'ndlt', 'type': 'string'}, {'name':
'sample',
+ 'type': 'scalar'}]]
- [356, 1, '_fk_constraint', 'memtx', 0, {}, [{'name': 'name',
'type': 'string'},
{'name': 'child_id', 'type': 'unsigned'}, {'name': 'parent_id',
'type': 'unsigned'},
{'name': 'is_deferred', 'type': 'boolean'}, {'name': 'match',
'type': 'string'},
diff --git a/test/box/alter.result b/test/box/alter.result
index 0d50855..4e14ca4 100644
--- a/test/box/alter.result
+++ b/test/box/alter.result
@@ -228,9 +228,9 @@ _index:select{}
- [330, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned']]]
- [340, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned']]]
- [340, 1, 'sequence', 'tree', {'unique': false}, [[1, 'unsigned']]]
- - [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1,
'string']]]
- - [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1,
'string'], [
- 5, 'scalar']]]
+ - [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1,
'unsigned']]]
+ - [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1,
'unsigned'],
+ [5, 'scalar']]]
- [356, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1,
'unsigned']]]
- [356, 1, 'child_id', 'tree', {'unique': false}, [[1, 'unsigned']]]
...
diff --git a/test/sql-tap/analyze1.test.lua b/test/sql-tap/analyze1.test.lua
index dab7255..27465a4 100755
--- a/test/sql-tap/analyze1.test.lua
+++ b/test/sql-tap/analyze1.test.lua
@@ -160,7 +160,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.1>
- "T1", "2 1", "T1I1", "2 2", "T1I2", "2 1", "T1I3", "2 2 1"
+ 0, "2 1", 1, "2 2", 2, "2 1", 3, "2 2 1"
-- </analyze-3.1>
})
@@ -173,7 +173,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.2>
- "T1", "4 1", "T1I1", "4 4", "T1I2", "4 1", "T1I3", "4 4 1"
+ 0, "4 1", 1, "4 4", 2, "4 1", 3, "4 4 1"
-- </analyze-3.2>
})
@@ -185,7 +185,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.3>
- "T1","5 1", "T1I1", "5 3", "T1I2", "5 2", "T1I3", "5 3 1"
+ 0,"5 1", 1, "5 3", 2, "5 2", 3, "5 3 1"
-- </analyze-3.3>
})
@@ -201,7 +201,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.4>
- "T1","5 1","T1I1","5 3","T1I2","5 2","T1I3","5 3 1","T2","5
1","T2I1","5 3","T2I2","5 2","T2I3","5 3 1"
+ 0,"5 1",0,"5 1",1,"5 3",1,"5 3",2,"5 2",2,"5 2",3,"5 3 1",3,"5 3 1"
-- </analyze-3.4>
})
@@ -213,7 +213,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.5>
- "T1","5 1","T1I1","5 3","T1I2","5 2","T1I3","5 3 1","T2","5
1","T2I1","5 3","T2I2","5 2"
+ 0,"5 1",0,"5 1",1,"5 3",1,"5 3",2,"5 2",2,"5 2",3,"5 3 1"
-- </analyze-3.5>
})
@@ -224,7 +224,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.6>
- "T1","5 1","T1I1","5 3","T1I2","5 2","T1I3","5 3 1","T2","5
1","T2I1","5 3","T2I2","5 2"
+ 0,"5 1",0,"5 1",1,"5 3",1,"5 3",2,"5 2",2,"5 2",3,"5 3 1"
-- </analyze-3.6>
})
@@ -236,7 +236,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.7>
- "T1","5 1","T1I1","5 3","T1I2","5 2","T1I3","5 3 1","T2","5
1","T2I1","5 3"
+ 0,"5 1",0,"5 1",1,"5 3",1,"5 3",2,"5 2",3,"5 3 1"
-- </analyze-3.7>
})
@@ -263,7 +263,7 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-3.9>
- "T3","5 1","T3I1","5 3","T3I2","5 3 1 1 1","T3I3","5 5 2 1 1"
+ 0,"5 1",1,"5 3",2,"5 3 1 1 1",3,"5 5 2 1 1"
-- </analyze-3.9>
})
@@ -320,28 +320,30 @@ test:do_execsql_test(
SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
]], {
-- <analyze-4.0>
- "T3", "5 1", "T3I1", "5 3", "T3I2", "5 3 1 1 1", "T3I3", "5 5 2
1 1", "T4", "5 1", "T4I1", "5 3", "T4I2", "5 2"
+ 0, "5 1", 0, "5 1", 1, "5 3", 1, "5 3", 2, "5 3 1 1 1", 2, "5
2", 3, "5 5 2 1 1"
-- </analyze-4.0>
})
+t4 = box.space.T4
+
test:do_execsql_test(
"analyze-4.1",
- [[
+ string.format([[
DELETE FROM "_sql_stat1";
- INSERT INTO "_sql_stat1" VALUES('t4', 't4i1', 'nonsense');
- INSERT INTO "_sql_stat1" VALUES('t4', 't4i2',
'432653287412874653284129847632');
+ INSERT INTO "_sql_stat1" VALUES(%i, %i, 'nonsense');
+ INSERT INTO "_sql_stat1" VALUES(%i, %i,
'432653287412874653284129847632');
SELECT * FROM t4 WHERE x = 1234;
- ]], {
+ ]], t4.id, t4.index['T4I1'].id, t4.id, t4.index['T4I2'].id), {
-- <analyze-4.1>
-- </analyze-4.1>
})
test:do_execsql_test(
"analyze-4.2",
- [[
- INSERT INTO "_sql_stat1" VALUES('t4', 'xyzzy', '0 1 2 3');
+ string.format([[
+ INSERT INTO "_sql_stat1" VALUES(%i, 12345, '0 1 2 3');
SELECT * FROM t4 WHERE x = 1234;
- ]], {
+ ]], t4.id), {
-- <analyze-4.2>
-- </analyze-4.2>
})
@@ -367,20 +369,20 @@ test:do_execsql_test(
INSERT INTO t3 (a,b,c,d) SELECT a+64, b+64, c+64, d+64 FROM t3;
INSERT INTO t4 (x,y,z) SELECT a, b, c FROM t3;
ANALYZE;
- SELECT DISTINCT "tbl" FROM "_sql_stat1" ORDER BY 1;
+ SELECT COUNT(DISTINCT "tbl") FROM "_sql_stat1" ORDER BY 1;
]], {
-- <analyze-5.0>
- "T3", "T4"
+ 2
-- </analyze-5.0>
})
test:do_execsql_test(
"analyze-5.0.1",
[[
- SELECT DISTINCT "idx" FROM "_sql_stat1" ORDER BY 1;
+ SELECT "idx" FROM "_sql_stat1" ORDER BY 1;
]], {
-- <analyze-5.0>
- "T3", "T3I1", "T3I2", "T3I3", "T4", "T4I1", "T4I2"
+ 0, 0, 1, 1, 2, 2, 3
-- </analyze-5.0>
})
@@ -392,17 +394,17 @@ test:do_execsql_test(
SELECT DISTINCT "idx" FROM "%s" ORDER BY 1;
]], stat, stat), {
-- <analyze-5.1>
- "T3", "T3I1", "T3I2", "T3I3", "T4", "T4I1", "T4I2"
+ 0, 1, 2, 3
-- </analyze-5.1>
})
test:do_execsql_test(
"analyze-5.1.1",
string.format([[
- SELECT DISTINCT "tbl" FROM "%s" ORDER BY 1;
+ SELECT COUNT(DISTINCT "tbl") FROM "%s" ORDER BY 1;
]], stat, stat), {
-- <analyze-5.1>
- "T3", "T4"
+ 2
-- </analyze-5.1>
})
@@ -411,20 +413,20 @@ test:do_execsql_test(
[[
DROP INDEX t3i2 ON t3;
ANALYZE;
- SELECT DISTINCT "idx" FROM "_sql_stat1" ORDER BY 1;
+ SELECT "idx" FROM "_sql_stat1" ORDER BY 1;
]], {
-- <analyze-5.2>
- "T3", "T3I1", "T3I3", "T4", "T4I1", "T4I2"
+ 0, 0, 1, 1, 2, 3
-- </analyze-5.2>
})
test:do_execsql_test(
"analyze-5.2.1",
[[
- SELECT DISTINCT "tbl" FROM "_sql_stat1" ORDER BY 1;
+ SELECT COUNT(DISTINCT "tbl") FROM "_sql_stat1" ORDER BY 1;
]], {
-- <analyze-5.2>
- "T3", "T4"
+ 2
-- </analyze-5.2>
})
@@ -434,17 +436,17 @@ test:do_execsql_test(
SELECT DISTINCT "idx" FROM "%s" ORDER BY 1;
]], stat, stat), {
-- <analyze-5.3>
- "T3", "T3I1", "T3I3", "T4", "T4I1", "T4I2"
+ 0, 1, 2, 3
-- </analyze-5.3>
})
test:do_execsql_test(
"analyze-5.3.1",
string.format([[
- SELECT DISTINCT "tbl" FROM "%s" ORDER BY 1;
+ SELECT COUNT(DISTINCT "tbl") FROM "%s" ORDER BY 1;
]], stat, stat), {
-- <analyze-5.3>
- "T3", "T4"
+ 2
-- </analyze-5.3>
})
@@ -456,17 +458,17 @@ test:do_execsql_test(
SELECT DISTINCT "idx" FROM "_sql_stat1" ORDER BY 1;
]], {
-- <analyze-5.4>
- "T4", "T4I1", "T4I2"
+ 0, 1, 2
-- </analyze-5.4>
})
test:do_execsql_test(
"analyze-5.4.1",
[[
- SELECT DISTINCT "tbl" FROM "_sql_stat1" ORDER BY 1;
+ SELECT COUNT(DISTINCT "tbl") FROM "_sql_stat1" ORDER BY 1;
]], {
-- <analyze-5.4>
- "T4"
+ 1
-- </analyze-5.4>
})
@@ -476,17 +478,17 @@ test:do_execsql_test(
SELECT DISTINCT "idx" FROM "%s" ORDER BY 1;
]], stat), {
-- <analyze-5.5>
- "T4", "T4I1", "T4I2"
+ 0, 1, 2
-- </analyze-5.5>
})
test:do_execsql_test(
"analyze-5.5.1",
string.format([[
- SELECT DISTINCT "tbl" FROM "%s" ORDER BY 1;
+ SELECT COUNT(DISTINCT "tbl") FROM "%s" ORDER BY 1;
]], stat), {
-- <analyze-5.5>
- "T4"
+ 1
-- </analyze-5.5>
})
@@ -516,33 +518,35 @@ test:do_test(
-- </analyze-6.1.1>
})
+t1 = box.space.T1
+
test:do_execsql_test(
"analyze-6.1.2",
- [[
- SELECT * FROM "_sql_stat1" where "tbl"='T1' and "idx"='I1'
LIMIT 1;
- ]], {
+ string.format([[
+ SELECT "idx", "stat" FROM "_sql_stat1" where "tbl"=%i and
"idx"=%i LIMIT 1;
+ ]], t1.id, t1.index['I1'].id), {
-- <analyze-6.1.2>
- "T1", "I1", "221 221 221 221 2"
+ 1, "221 221 221 221 2"
-- </analyze-6.1.2>
})
test:do_execsql_test(
"analyze-6.1.3",
- [[
- SELECT "tbl", "idx", "neq", "nlt", "ndlt" FROM "_sql_stat4"
where "tbl"='T1' and "idx"='I1' ORDER BY "nlt" LIMIT 1;
- ]], {
+ string.format([[
+ SELECT "idx", "neq", "nlt", "ndlt" FROM "_sql_stat4" where
"tbl"=%i and "idx"=%i ORDER BY "nlt" LIMIT 1;
+ ]], t1.id, t1.index['I1'].id), {
-- <analyze-6.1.3>
- "T1", "I1", "221 221 221 1", "0 0 0 10", "0 0 0 10"
+ 1, "221 221 221 1", "0 0 0 10", "0 0 0 10"
-- </analyze-6.1.3>
})
test:do_execsql_test(
"analyze-6.1.4",
- [[
- SELECT "tbl", "idx", "neq", "nlt", "ndlt" FROM "_sql_stat4"
where "tbl"='T1' and "idx"='I1' ORDER BY "nlt" DESC LIMIT 1;
- ]], {
+ string.format([[
+ SELECT "idx", "neq", "nlt", "ndlt" FROM "_sql_stat4" where
"tbl"=%i and "idx"=%i ORDER BY "nlt" DESC LIMIT 1;
+ ]], t1.id, t1.index['I1'].id), {
-- <analyze-6.1.4>
- "T1", "I1", "221 221 221 1", "0 0 0 99", "0 0 0 99"
+ 1, "221 221 221 1", "0 0 0 99", "0 0 0 99"
-- </analyze-6.1.4>
})
diff --git a/test/sql-tap/analyze4.test.lua b/test/sql-tap/analyze4.test.lua
index c2cc190..dd5533b 100755
--- a/test/sql-tap/analyze4.test.lua
+++ b/test/sql-tap/analyze4.test.lua
@@ -49,15 +49,19 @@ test:do_test(
-- </analyze4-1.0>
})
+t1 = box.space.T1
+
-- Verify that the t1b index shows that it does not narrow down the
-- search any at all.
--
test:do_execsql_test(
"analyze4-1.1",
- [[ SELECT "idx", "stat" FROM "_sql_stat1" WHERE "tbl"='T1' ORDER BY
"idx"; ]],
+ string.format([[
+ SELECT "idx", "stat" FROM "_sql_stat1" WHERE "tbl"=%i ORDER BY
"idx";
+ ]], t1.id),
{
-- <analyze4-1.1>
- "T1","128 1", "T1A", "128 1", "T1B", "128 128"
+ 0,"128 1", 1, "128 1", 2, "128 128"
-- </analyze4-1.1>
})
@@ -68,16 +72,16 @@ test:do_execsql_test(
test:do_test(
"analyze4-1.2",
function()
- return test:execsql([[
- UPDATE t1 SET b='x' WHERE a%2;
+ return test:execsql(string.format([[
+ UPDATE t1 SET b='x' WHERE a%%2;
-- pragma vdbe_debug=1;
ANALYZE;
-- pragma vdbe_debug=0;
- SELECT "idx", "stat" FROM "_sql_stat1" WHERE "tbl"='T1'
ORDER BY "idx";
- ]])
+ SELECT "idx", "stat" FROM "_sql_stat1" WHERE "tbl"=%i ORDER
BY "idx";
+ ]], t1.id))
end, {
-- <analyze4-1.2>
- "T1", "128 1", "T1A", "128 1", "T1B", "128 64"
+ 0, "128 1", 1, "128 1", 2, "128 64"
-- </analyze4-1.2>
})
@@ -85,37 +89,42 @@ test:do_test(
-- Create a multi-column indices using t1.b and verify that ANALYZE
-- processes them correctly.
--
+
+test:execsql([[
+ -- Tarantool doesn't suppoort ALTER stmt yet.
+ -- UPDATE t1 SET b=NULL;
+ --ALTER TABLE t1 ADD COLUMN c;
+ --ALTER TABLE t1 ADD COLUMN d;
+ -- So, re-create the table and its contents
+ DROP TABLE t1;
+ CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a,b,c DEFAULT
NULL,d DEFAULT NULL);
+ CREATE INDEX t1a ON t1(a);
+ CREATE INDEX t1b ON t1(b);
+ INSERT INTO t1 (a,b) VALUES(1,NULL);
+ INSERT INTO t1 (a,b) SELECT a+1, b FROM t1;
+ INSERT INTO t1 (a,b) SELECT a+2, b FROM t1;
+ INSERT INTO t1 (a,b) SELECT a+4, b FROM t1;
+ INSERT INTO t1 (a,b) SELECT a+8, b FROM t1;
+ INSERT INTO t1 (a,b) SELECT a+16, b FROM t1;
+ INSERT INTO t1 (a,b) SELECT a+32, b FROM t1;
+ INSERT INTO t1 (a,b) SELECT a+64, b FROM t1;
+
+ UPDATE t1 SET c=a/4, d=a/2;
+ CREATE INDEX t1bcd ON t1(b,c,d);
+ CREATE INDEX t1cdb ON t1(c,d,b);
+ CREATE INDEX t1cbd ON t1(c,b,d);
+ ANALYZE;
+]])
+
+t1 = box.space.T1
+
test:do_execsql_test(
"analyze4-1.3",
- [[
- -- Tarantool doesn't suppoort ALTER stmt yet.
- -- UPDATE t1 SET b=NULL;
- --ALTER TABLE t1 ADD COLUMN c;
- --ALTER TABLE t1 ADD COLUMN d;
- -- So, re-create the table and its contents
- DROP TABLE t1;
- CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a,b,c
DEFAULT NULL,d DEFAULT NULL);
- CREATE INDEX t1a ON t1(a);
- CREATE INDEX t1b ON t1(b);
- INSERT INTO t1 (a,b) VALUES(1,NULL);
- INSERT INTO t1 (a,b) SELECT a+1, b FROM t1;
- INSERT INTO t1 (a,b) SELECT a+2, b FROM t1;
- INSERT INTO t1 (a,b) SELECT a+4, b FROM t1;
- INSERT INTO t1 (a,b) SELECT a+8, b FROM t1;
- INSERT INTO t1 (a,b) SELECT a+16, b FROM t1;
- INSERT INTO t1 (a,b) SELECT a+32, b FROM t1;
- INSERT INTO t1 (a,b) SELECT a+64, b FROM t1;
-
- UPDATE t1 SET c=a/4, d=a/2;
- CREATE INDEX t1bcd ON t1(b,c,d);
- CREATE INDEX t1cdb ON t1(c,d,b);
- CREATE INDEX t1cbd ON t1(c,b,d);
- ANALYZE;
- SELECT "idx", "stat" FROM "_sql_stat1" WHERE "tbl"='T1'
ORDER BY "idx";
- ]]
- , {
+ string.format([[
+ SELECT "idx", "stat" FROM "_sql_stat1" WHERE "tbl"=%i ORDER
BY "idx";
+ ]], t1.id), {
-- <analyze4-1.3>
- "T1","128 1", "T1A", "128 1", "T1B", "128 128", "T1BCD", "128
128 4 2", "T1CBD", "128 4 4 2", "T1CDB", "128 4 2 2"
+ 0,"128 1", 1, "128 1", 2, "128 128", 3, "128 128 4 2", 4, "128
4 2 2", 5, "128 4 4 2"
-- </analyze4-1.3>
})
diff --git a/test/sql-tap/analyze5.test.lua b/test/sql-tap/analyze5.test.lua
index d68bd3c..198f397 100755
--- a/test/sql-tap/analyze5.test.lua
+++ b/test/sql-tap/analyze5.test.lua
@@ -114,10 +114,11 @@ test:do_test(
-- DISTINCT idx, sample -- lindex(test_decode(sample),0)
-- WHERE idx='t1u' ORDER BY nlt;
- return test:execsql([[ SELECT DISTINCT msgpack_decode("sample")
- FROM "_sql_stat4"
- WHERE "idx"='T1U'
- ORDER BY "nlt"]])
+ return test:execsql(string.format([[
+ SELECT DISTINCT msgpack_decode("sample")
+ FROM "_sql_stat4"
+ WHERE "idx"= %i
+ ORDER BY "nlt"]], box.space.T1.index['T1U'].id))
end, {
-- <analyze5-1.0>
"alpha", "bravo", "charlie", "delta"
@@ -146,7 +147,7 @@ test:do_test(
return test:execsql([[SELECT "idx", count(*) FROM "_sql_stat4"
GROUP BY 1 ORDER BY 1]])
end, {
-- <analyze5-1.2>
- "T1",24,"T1T",4,"T1U",4,"T1V",1,"T1W",4,"T1X",4,"T1Y",2,"T1Z",4
+ 0,24,1,4,2,4,3,1,4,4,5,4,6,2,7,4
-- </analyze5-1.2>
})
diff --git a/test/sql-tap/analyze9.test.lua b/test/sql-tap/analyze9.test.lua
index 1dbfe5d..b0fec3b 100755
--- a/test/sql-tap/analyze9.test.lua
+++ b/test/sql-tap/analyze9.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(121)
+test:plan(118)
testprefix = "analyze9"
@@ -62,29 +62,29 @@ msgpack_decode_sample = function(txt)
end
box.internal.sql_create_function("msgpack_decode_sample",
msgpack_decode_sample)
+t1 = box.space.T1
test:do_execsql_test(
1.2,
- [[
- SELECT
"tbl","idx","neq","nlt","ndlt",msgpack_decode_sample("sample") FROM
"_sql_stat4" where "idx" = 'I1';
- ]], {
+ string.format([[
+ SELECT "idx","neq","nlt","ndlt",msgpack_decode_sample("sample")
FROM "_sql_stat4" where "idx" = %i;
+ ]], t1.index['I1'].id), {
-- <1.2>
- "T1", "I1", "1 1", "0 0", "0 0", "(0) (0)", "T1", "I1", "1 1",
"1 1", "1 1", "(1) (1)",
- "T1", "I1", "1 1", "2 2", "2 2", "(2) (2)", "T1", "I1", "1 1",
"3 3", "3 3", "(3) (3)",
- "T1", "I1", "1 1", "4 4", "4 4", "(4) (4)"
+ 1, "1 1", "0 0", "0 0", "(0) (0)", 1, "1 1", "1 1", "1 1", "(1)
(1)",
+ 1, "1 1", "2 2", "2 2", "(2) (2)", 1, "1 1", "3 3", "3 3", "(3)
(3)",
+ 1, "1 1", "4 4", "4 4", "(4) (4)"
-- </1.2>
})
test:do_execsql_test(
1.3,
- [[
- SELECT
"tbl","idx","neq","nlt","ndlt",msgpack_decode_sample("sample") FROM
"_sql_stat4" where "idx" = 'T1';
-
- ]], {
+ string.format([[
+ SELECT "idx","neq","nlt","ndlt",msgpack_decode_sample("sample")
FROM "_sql_stat4" where "idx" = %i;
+ ]], t1.index[0].id), {
-- <1.3>
- 'T1', 'T1', '1', '0', '0', '(0)', 'T1', 'T1', '1', '1', '1',
'(1)',
- 'T1', 'T1', '1', '2', '2', '(2)', 'T1', 'T1', '1', '3', '3',
'(3)',
- 'T1', 'T1', '1', '4', '4', '(4)'
+ 0, '1', '0', '0', '(0)', 0, '1', '1', '1', '(1)',
+ 0, '1', '2', '2', '(2)', 0, '1', '3', '3', '(3)',
+ 0, '1', '4', '4', '(4)'
-- </1.3>
})
@@ -101,10 +101,10 @@ test:do_execsql_test(
INSERT INTO t1 VALUES(22.0, 'some text');
CREATE INDEX i1 ON t1(a, b);
ANALYZE;
- SELECT msgpack_decode_sample("sample") FROM "_sql_stat4";
+ SELECT msgpack_decode_sample("sample") FROM "_sql_stat4" ORDER
BY 1;
]], {
-- <2.1>
- "some text 14", "22 some text", "some text", 22
+ 22, "22 some text", "some text", "some text 14"
-- </2.1>
})
@@ -116,6 +116,7 @@ test:do_execsql_test(
CREATE TABLE t2(id INTEGER PRIMARY KEY AUTOINCREMENT, a, b);
CREATE INDEX i2 ON t2(a, b);
]])
+t2 = box.space.T2
test:do_test(
3.2,
@@ -184,10 +185,10 @@ test:do_execsql_test(
--
test:do_execsql_test(
"3.3.2",
- [[
+ string.format([[
ANALYZE;
- SELECT lrange("neq", 1, 1) FROM "_sql_stat4" WHERE "idx" = 'I2';
- ]], generate_tens_str(24))
+ SELECT lrange("neq", 1, 1) FROM "_sql_stat4" WHERE "tbl" = %i
and "idx" = %i;
+ ]], t2.id, t2.index['I2'].id) , generate_tens_str(24))
---------------------------------------------------------------------------
--
@@ -232,6 +233,7 @@ test:do_execsql_test(
CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a, b, c);
CREATE INDEX i1 ON t1(c, b, a);
]])
+t1 = box.space.T1
insert_filler_rows_n = function(iStart, nCopy, nVal)
for i = 0, nVal-1 do
@@ -282,10 +284,10 @@ test:do_execsql_test(
test:do_execsql_test(
4.3,
- [[
+ string.format([[
SELECT "neq", lrange("nlt", 1, 3), lrange("ndlt", 1, 3),
lrange(msgpack_decode_sample("sample"), 1, 3)
- FROM "_sql_stat4" WHERE "idx" = 'I1' ORDER BY "sample"
LIMIT 16;
- ]], {
+ FROM "_sql_stat4" WHERE "tbl" = %i and "idx" = %i ORDER BY
"sample" LIMIT 16;
+ ]], t1.id, t1.index['I1'].id), {
-- <4.3>
"10 10 10","0 0 0","0 0 0","0 0 0","10 10 10","10 10 10","1 1
1","1 1 1","10 10 10","20 20 20",
"2 2 2","2 2 2","10 10 10","30 30 30","3 3 3","3 3 3","10 10
10","40 40 40","4 4 4","4 4 4",
@@ -299,10 +301,10 @@ test:do_execsql_test(
test:do_execsql_test(
4.4,
- [[
+ string.format([[
SELECT "neq", lrange("nlt", 1, 3), lrange("ndlt", 1, 3),
lrange(msgpack_decode_sample("sample"), 1, 3)
- FROM "_sql_stat4" WHERE "idx" = 'I1' ORDER BY "sample" DESC
LIMIT 2;
- ]], {
+ FROM "_sql_stat4" WHERE "tbl" = %i and "idx" = %i ORDER BY
"sample" DESC LIMIT 2;
+ ]], t1.id, t1.index['I1'].id), {
-- <4.4>
"2 1 1","295 296 296","120 122 125","201 4 h","5 3 1","290 290
291","119 119 120","200 1 b"
-- </4.4>
@@ -364,11 +366,12 @@ test:do_test(
test:do_execsql_test(
4.9,
[[
- SELECT msgpack_decode_sample("sample") FROM "_sql_stat4";
+ SELECT msgpack_decode_sample("sample") FROM "_sql_stat4" ORDER
BY 1;
]], {
-- <4.9>
- "x", 1110, 2230, 2750, 3350, 4090, 4470, 4980, 5240, 5280,
5290, 5590, 5920,
- 5930, 6220, 6710, 7000, 7710, 7830, 7970, 8890, 8950, 9240,
9250, 9680
+ 1110, 2230, 2750, 3350, 4090, 4470, 4980, 5240, 5280, 5290,
5590, 5920,
+ 5930, 6220, 6710, 7000, 7710, 7830, 7970, 8890, 8950, 9240,
9250, 9680,
+ "x"
-- </4.9>
})
@@ -394,6 +397,8 @@ test:do_execsql_test(
INSERT INTO "_sql_stat4" SELECT * FROM x1;
ANALYZE;
]])
+t1 = box.space.T1
+x1 = box.space.X1
test:do_execsql_test(
6.2,
@@ -407,7 +412,7 @@ test:do_execsql_test(
--
test:do_execsql_test(
7.1,
- [[
+ string.format([[
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a, b);
CREATE INDEX i1 ON t1(a, b);
@@ -418,9 +423,9 @@ test:do_execsql_test(
INSERT INTO t1 VALUES(null, 5, 5);
ANALYZE;
UPDATE "_sql_stat4" SET "sample" = '' WHERE "sample" =
- (SELECT "sample" FROM "_sql_stat4" WHERE "tbl" = 't1' AND
"idx" = 'i1' LIMIT 1);
+ (SELECT "sample" FROM "_sql_stat4" WHERE "tbl" = %i AND
"idx" = %i LIMIT 1);
ANALYZE;
- ]])
+ ]], t1.id, t1.index['I1'].id))
-- Doesn't work due to the fact that in Tarantool rowid has been removed,
-- and tbl, idx and sample have been united into primary key.
@@ -1014,15 +1019,15 @@ test:do_execsql_test(
--
test:do_execsql_test(
15.1,
- [[
+ string.format([[
DROP TABLE IF EXISTS x1;
CREATE TABLE x1(a PRIMARY KEY, b, UNIQUE(a, b));
INSERT INTO x1 VALUES(1, 2);
INSERT INTO x1 VALUES(3, 4);
INSERT INTO x1 VALUES(5, 6);
ANALYZE;
- INSERT INTO "_sql_stat4" VALUES('x1', 'abc', 0, 0, 0, '');
- ]])
+ INSERT INTO "_sql_stat4" VALUES(%i, 12345, 0, 0, 0, '');
+ ]], x1.id))
test:do_execsql_test(
15.2,
@@ -1050,42 +1055,6 @@ test:do_execsql_test(
-- </15.4>
})
-test:do_execsql_test(
- 15.7,
- [[
- ANALYZE;
- UPDATE "_sql_stat1" SET "tbl" = 'no such tbl';
- ]])
-
-test:do_execsql_test(
- 15.8,
- [[
- SELECT * FROM x1 ;
- ]], {
- -- <15.8>
- 1, 2, 3, 4, 5, 6
- -- </15.8>
- })
-
--- Tarantool: this test seems to be useless. There's no reason
--- for these fields to be nullable.
--- test:do_execsql_test(
--- 15.9,
--- [[
--- ANALYZE;
--- UPDATE "_sql_stat4" SET "neq" = NULL, "nlt" = NULL, "ndlt" =
NULL;
--- ]])
-
-test:do_execsql_test(
- 15.10,
- [[
- SELECT * FROM x1;
- ]], {
- -- <15.10>
- 1, 2, 3, 4, 5, 6
- -- </15.10>
- })
-
-- This is just for coverage....
test:do_execsql_test(
15.11,
@@ -1135,6 +1104,7 @@ test:do_test(
-- <17.1>
-- </17.1>
})
+t1 = box.space.T1
test:do_execsql_test(
17.2,
@@ -1197,7 +1167,7 @@ test:do_test(
test:execsql(string.format("INSERT INTO t1 VALUES(%s,
0);", i))
end
test:execsql("ANALYZE")
- return test:execsql([[ SELECT count(*) FROM "_sql_stat4" WHERE
"idx" = 'I1'; ]])
+ return test:execsql(string.format('SELECT count(*) FROM
"_sql_stat4" WHERE "tbl" = %i AND "idx" = %i;', box.space.T1.id,
box.space.T1.index['I1'].id))
end, {
-- <18.1>
9
@@ -1239,12 +1209,13 @@ test:do_execsql_test(
-- </20.2>
})
+t1 = box.space.T1
for i = 0, 15 do
test:do_test(
"20.3."..i,
function()
return test:execsql(string.format(
- [[SELECT count(*) FROM "_sql_stat4" WHERE "idx" = 'I1'
AND lrange(msgpack_decode_sample("sample"), 1, 1) = '%s']], i))
+ [[SELECT count(*) FROM "_sql_stat4" WHERE "tbl" = %i
AND "idx" = %i AND lrange(msgpack_decode_sample("sample"), 1, 1) =
'%s']], t1.id, t1.index['I1'].id, i))
end, {
1
})
diff --git a/test/sql-tap/analyzeC.test.lua b/test/sql-tap/analyzeC.test.lua
index a3cea70..4bfb4f6 100755
--- a/test/sql-tap/analyzeC.test.lua
+++ b/test/sql-tap/analyzeC.test.lua
@@ -30,9 +30,7 @@ testprefix = "analyzeC"
-- Baseline case. Range queries work OK. Indexes can be used for
-- ORDER BY.
-test:do_execsql_test(
- 1.0,
- [[
+test:execsql([[
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a PRIMARY KEY, b, c, d);
INSERT INTO t1(a,b,c,d)
VALUES(1,1,2,3),(2,7,8,9),(3,4,5,6),(4,10,11,12),(5,4,8,12),(6,1,11,111);
@@ -40,10 +38,17 @@ test:do_execsql_test(
CREATE INDEX t1c ON t1(c);
ANALYZE;
DELETE FROM "_sql_stat1";
- INSERT INTO "_sql_stat1"("tbl","idx","stat")
VALUES('t1','t1b','12345 2'),('t1','t1c','12345 4');
+ ]]
+)
+t1 = box.space.T1
+
+test:do_execsql_test(
+ 1.0,
+ string.format([[
+ INSERT INTO "_sql_stat1"("tbl","idx","stat") VALUES(%i,1,'12345
2'),(%i,2,'12345 4');
ANALYZE;
SELECT b,c,d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;
- ]], {
+ ]], t1.id, t1.id), {
-- <1.0>
4, 5, 6, "#", 7, 8, 9, "#", 4, 8, 12, "#"
-- </1.0>
@@ -173,17 +178,22 @@ test:do_execsql_test(
-- The sz=NNN parameter determines which index to scan
--
-test:do_execsql_test(
- 4.0,
- [[
+
+test:execsql([[
DROP INDEX t1b ON t1;
CREATE INDEX t1bc ON t1(b,c);
CREATE INDEX t1db ON t1(d,b);
DELETE FROM "_sql_stat1";
- INSERT INTO "_sql_stat1"("tbl","idx","stat")
VALUES('t1','t1bc','12345 3 2 sz=10'),('t1','t1db','12345 3 2 sz=20');
+ ]]
+)
+
+test:do_execsql_test(
+ 4.0,
+ string.format([[
+ INSERT INTO "_sql_stat1"("tbl","idx","stat")
VALUES(%i,%i,'12345 3 2 sz=10'),(%i,%i,'12345 3 2 sz=20');
ANALYZE;
SELECT count(b) FROM t1;
- ]], {
+ ]], t1.id, t1.index['T1BC'].id, t1.id, t1.index['T1DB'].id), {
-- <4.0>
6
-- </4.0>
@@ -201,12 +211,13 @@ test:do_execsql_test(
test:do_execsql_test(
4.2,
+ string.format(
[[
DELETE FROM "_sql_stat1";
- INSERT INTO "_sql_stat1"("tbl","idx","stat")
VALUES('t1','t1bc','12345 3 2 sz=20'),('t1','t1db','12345 3 2 sz=10');
+ INSERT INTO "_sql_stat1"("tbl","idx","stat")
VALUES(%i,%i,'12345 3 2 sz=20'),(%i,%i,'12345 3 2 sz=10');
ANALYZE;
SELECT count(b) FROM t1;
- ]], {
+ ]], t1.id, t1.index['T1BC'].id, t1.id, t1.index['T1DB'].id), {
-- <4.2>
6
-- </4.2>
@@ -227,14 +238,14 @@ test:do_execsql_test(
--
test:do_execsql_test(
5.0,
- [[
+ string.format([[
DELETE FROM "_sql_stat1";
INSERT INTO "_sql_stat1"("tbl","idx","stat")
- VALUES('t1','t1bc','12345 3 2 x=5 sz=10 y=10'),
- ('t1','t1db','12345 3 2 whatever sz=20 junk');
+ VALUES(%i,%i,'12345 3 2 x=5 sz=10 y=10'),
+ (%i,%i,'12345 3 2 whatever sz=20 junk');
ANALYZE;
SELECT count(b) FROM t1;
- ]], {
+ ]], t1.id, t1.index['T1BC'].id, t1.id, t1.index['T1DB'].id), {
-- <5.0>
6
-- </5.0>
@@ -253,12 +264,12 @@ test:do_execsql_test(
test:do_execsql_test(
5.2,
- [[
+ string.format([[
DELETE FROM "_sql_stat1";
- INSERT INTO "_sql_stat1"("tbl","idx","stat")
VALUES('t1','t1db','12345 3 2 x=5 sz=10 y=10'), ('t1','t1bc','12345 3 2
whatever sz=20 junk');
+ INSERT INTO "_sql_stat1"("tbl","idx","stat")
VALUES(%i,%i,'12345 3 2 x=5 sz=10 y=10'), (%i,%i,'12345 3 2 whatever
sz=20 junk');
ANALYZE;
SELECT count(b) FROM t1;
- ]], {
+ ]], t1.id, t1.index['T1BC'].id, t1.id, t1.index['T1DB'].id), {
-- <5.2>
6
-- </5.2>
diff --git a/test/sql-tap/analyzeD.test.lua b/test/sql-tap/analyzeD.test.lua
index ef6aced..27a19c8 100755
--- a/test/sql-tap/analyzeD.test.lua
+++ b/test/sql-tap/analyzeD.test.lua
@@ -153,9 +153,9 @@ test:do_catchsql_test(
test:do_execsql_test(
"analyzeD-1.10",
- [[
- SELECT * FROM "_sql_stat4" WHERE "tbl" = 'v';
- ]], {
+ string.format([[
+ SELECT * FROM "_sql_stat4" WHERE "tbl" = %i;
+ ]], box.space.V.id), {
-- <analyzeD-1.10>
-- <analyzeD-1.10>
@@ -163,9 +163,9 @@ test:do_execsql_test(
test:do_execsql_test(
"analyzeD-1.11",
- [[
- SELECT * FROM "_sql_stat1" WHERE "tbl" = 'v';
- ]], {
+ string.format([[
+ SELECT * FROM "_sql_stat1" WHERE "tbl" = %i;
+ ]], box.space.V.id), {
-- <analyzeD-1.11>
-- <analyzeD-1.11>
@@ -183,9 +183,9 @@ test:do_catchsql_test(
test:do_execsql_test(
"analyzeD-1.13",
- [[
- SELECT * FROM "_sql_stat4" WHERE "tbl" = 'v';
- ]], {
+ string.format([[
+ SELECT * FROM "_sql_stat4" WHERE "tbl" = %i;
+ ]], box.space.V.id), {
-- <analyzeD-1.13>
-- <analyzeD-1.13>
@@ -193,9 +193,9 @@ test:do_execsql_test(
test:do_execsql_test(
"analyzeD-1.14",
- [[
- SELECT * FROM "_sql_stat1" WHERE "tbl" = 'v';
- ]], {
+ string.format([[
+ SELECT * FROM "_sql_stat1" WHERE "tbl" = %i;
+ ]], box.space.V.id), {
-- <analyzeD-1.14>
-- <analyzeD-1.14>
diff --git a/test/sql-tap/gh-3350-skip-scan.test.lua
b/test/sql-tap/gh-3350-skip-scan.test.lua
index 301b04a..917c74c 100755
--- a/test/sql-tap/gh-3350-skip-scan.test.lua
+++ b/test/sql-tap/gh-3350-skip-scan.test.lua
@@ -77,9 +77,7 @@ test:do_execsql_test(
}
)
-test:do_execsql_test(
- "skip-scan-1.4",
- [[
+test:execsql([[
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(id INTEGER PRIMARY KEY, a TEXT, b INT, c
INT, d INT);
CREATE INDEX t1abc ON t1(a,b,c);
@@ -96,10 +94,17 @@ test:do_execsql_test(
ANALYZE;
DELETE FROM "_sql_stat1";
DELETE FROM "_sql_stat4";
- INSERT INTO "_sql_stat1" VALUES('T1','T1ABC','10000 5000
2000 10');
+ ]]
+)
+
+test:do_execsql_test(
+ "skip-scan-1.4",
+ string.format(
+ [[
+ INSERT INTO "_sql_stat1" VALUES(%i,%i,'10000 5000 2000 10');
ANALYZE t2;
SELECT a,b,c,d FROM t1 WHERE b=345;
- ]], {
+ ]], box.space.T1.id, box.space.T1.index['T1ABC'].id), {
"abc", 345, 7, 8, "def", 345, 9, 10
}
)
diff --git a/test/sql/sql-statN-index-drop.result
b/test/sql/sql-statN-index-drop.result
index a751eca..0e5f2a3 100644
--- a/test/sql/sql-statN-index-drop.result
+++ b/test/sql/sql-statN-index-drop.result
@@ -31,36 +31,36 @@ box.sql.execute("ANALYZE;")
---
...
-- Checking the data.
-box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" 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=]
+- - [0, '1', '0', '0', !!binary kQE=]
+ - [1, '1', '0', '0', !!binary kQI=]
+ - [0, '1', '0', '0', !!binary kQE=]
+ - [1, '1', '0', '0', !!binary kQI=]
...
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+box.sql.execute('SELECT "idx","stat" FROM \"_sql_stat1\";')
---
-- - ['T1', 'I1', '1 1']
- - ['T1', 'T1', '1 1']
- - ['T2', 'I1', '1 1']
- - ['T2', 'T2', '1 1']
+- - [0, '1 1']
+ - [1, '1 1']
+ - [0, '1 1']
+ - [1, '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\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" FROM
\"_sql_stat4\";')
---
-- - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
- - ['T2', 'I1', '1', '0', '0', !!binary kQI=]
- - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
+- - [0, '1', '0', '0', !!binary kQE=]
+ - [0, '1', '0', '0', !!binary kQE=]
+ - [1, '1', '0', '0', !!binary kQI=]
...
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+box.sql.execute('SELECT "idx","stat" FROM \"_sql_stat1\";')
---
-- - ['T1', 'T1', '1 1']
- - ['T2', 'I1', '1 1']
- - ['T2', 'T2', '1 1']
+- - [0, '1 1']
+ - [0, '1 1']
+ - [1, '1 1']
...
--Cleaning up.
box.sql.execute("DROP TABLE t1;")
@@ -93,36 +93,36 @@ box.sql.execute("ANALYZE;")
---
...
-- Checking the data.
-box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" 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=]
+- - [0, '1', '0', '0', !!binary kQE=]
+ - [1, '1', '0', '0', !!binary kQI=]
+ - [0, '1', '0', '0', !!binary kQE=]
+ - [1, '1', '0', '0', !!binary kQI=]
...
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+box.sql.execute('SELECT "idx","stat" FROM \"_sql_stat1\";')
---
-- - ['T1', 'I1', '1 1']
- - ['T1', 'T1', '1 1']
- - ['T2', 'I1', '1 1']
- - ['T2', 'T2', '1 1']
+- - [0, '1 1']
+ - [1, '1 1']
+ - [0, '1 1']
+ - [1, '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\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" FROM
\"_sql_stat4\";')
---
-- - ['T1', 'I1', '1', '0', '0', !!binary kQI=]
- - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
- - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
+- - [0, '1', '0', '0', !!binary kQE=]
+ - [1, '1', '0', '0', !!binary kQI=]
+ - [0, '1', '0', '0', !!binary kQE=]
...
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+box.sql.execute('SELECT "idx","stat" FROM \"_sql_stat1\";')
---
-- - ['T1', 'I1', '1 1']
- - ['T1', 'T1', '1 1']
- - ['T2', 'T2', '1 1']
+- - [0, '1 1']
+ - [1, '1 1']
+ - [0, '1 1']
...
--Cleaning up.
box.sql.execute("DROP TABLE t1;")
diff --git a/test/sql/sql-statN-index-drop.test.lua
b/test/sql/sql-statN-index-drop.test.lua
index fe7e15b..6cc6400 100644
--- a/test/sql/sql-statN-index-drop.test.lua
+++ b/test/sql/sql-statN-index-drop.test.lua
@@ -14,15 +14,15 @@ box.sql.execute("INSERT INTO t2 VALUES(1, 2);")
box.sql.execute("ANALYZE;")
-- Checking the data.
-box.sql.execute("SELECT * FROM \"_sql_stat4\";")
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" FROM
\"_sql_stat4\";')
+box.sql.execute('SELECT "idx","stat" 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\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" FROM
\"_sql_stat4\";')
+box.sql.execute('SELECT "idx","stat" FROM \"_sql_stat1\";')
--Cleaning up.
box.sql.execute("DROP TABLE t1;")
@@ -41,15 +41,15 @@ box.sql.execute("INSERT INTO t2 VALUES(1, 2);")
box.sql.execute("ANALYZE;")
-- Checking the data.
-box.sql.execute("SELECT * FROM \"_sql_stat4\";")
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" FROM
\"_sql_stat4\";')
+box.sql.execute('SELECT "idx","stat" 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\";")
+box.sql.execute('SELECT "idx","neq","nlt","ndlt","sample" FROM
\"_sql_stat4\";')
+box.sql.execute('SELECT "idx","stat" FROM \"_sql_stat1\";')
--Cleaning up.
box.sql.execute("DROP TABLE t1;")
diff --git a/test/sql/upgrade.result b/test/sql/upgrade.result
index 5e7d851..6890c5d 100644
--- a/test/sql/upgrade.result
+++ b/test/sql/upgrade.result
@@ -30,13 +30,13 @@ box.space._space.index['name']:get('_trigger')
...
box.space._space.index['name']:get('_sql_stat1')
---
-- [348, 1, '_sql_stat1', 'memtx', 0, {}, [{'name': 'tbl', 'type':
'string'}, {'name': 'idx',
- 'type': 'string'}, {'name': 'stat', 'type': 'string'}]]
+- [348, 1, '_sql_stat1', 'memtx', 0, {}, [{'name': 'tbl', 'type':
'unsigned'}, {'name': 'idx',
+ 'type': 'unsigned'}, {'name': 'stat', 'type': 'string'}]]
...
box.space._space.index['name']:get('_sql_stat4')
---
-- [349, 1, '_sql_stat4', 'memtx', 0, {}, [{'name': 'tbl', 'type':
'string'}, {'name': 'idx',
- 'type': 'string'}, {'name': 'neq', 'type': 'string'}, {'name':
'nlt', 'type': 'string'},
+- [349, 1, '_sql_stat4', 'memtx', 0, {}, [{'name': 'tbl', 'type':
'unsigned'}, {'name': 'idx',
+ 'type': 'unsigned'}, {'name': 'neq', 'type': 'string'}, {'name':
'nlt', 'type': 'string'},
{'name': 'ndlt', 'type': 'string'}, {'name': 'sample', 'type':
'scalar'}]]
...
box.space._index:get({box.space._space.index['name']:get('_trigger').id, 0})
@@ -45,12 +45,12 @@
box.space._index:get({box.space._space.index['name']:get('_trigger').id, 0})
...
box.space._index:get({box.space._space.index['name']:get('_sql_stat1').id, 0})
---
-- [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1,
'string']]]
+- [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1,
'unsigned']]]
...
box.space._index:get({box.space._space.index['name']:get('_sql_stat4').id, 0})
---
-- [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1,
'string'], [5,
- 'scalar']]]
+- [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1,
'unsigned'],
+ [5, 'scalar']]]
...
box.space._schema:format()
---
^ permalink raw reply [flat|nested] 4+ messages in thread
* [tarantool-patches] Re: [PATCH v1 1/1] sql: hold in stat tables space/index id instead of name
2018-08-29 11:37 ` Imeev Mergen
@ 2018-08-29 19:27 ` Vladislav Shpilevoy
0 siblings, 0 replies; 4+ messages in thread
From: Vladislav Shpilevoy @ 2018-08-29 19:27 UTC (permalink / raw)
To: Imeev Mergen, tarantool-patches
Hi! Thanks for the fixes!
I have force pushed my review fixes. I removed
some leading white spaces and useless assertions.
The diff is below.
Please, fetch, rebase and send to Nikita on the
second review.
diff --git a/src/box/sql/analyze.c b/src/box/sql/analyze.c
index a7cc173cd..3d825acb9 100644
--- a/src/box/sql/analyze.c
+++ b/src/box/sql/analyze.c
@@ -1016,8 +1016,6 @@ analyzeOneTable(Parse * pParse, /* Parser context */
/* Add the entry to the stat1 table. */
callStatGet(v, regStat4, STAT_GET_STAT1, regStat1);
- assert("DDB"[0] == AFFINITY_INTEGER);
- assert("BBB"[2] == AFFINITY_TEXT);
sqlite3VdbeAddOp4(v, OP_MakeRecord, reg_space_id, 3, regTemp,
"DDB", 0);
sqlite3VdbeAddOp2(v, OP_IdxInsert, iStatCur, regTemp);
diff --git a/test/sql-tap/analyze9.test.lua b/test/sql-tap/analyze9.test.lua
index b0fec3b1c..2c6b4b8a0 100755
--- a/test/sql-tap/analyze9.test.lua
+++ b/test/sql-tap/analyze9.test.lua
@@ -70,8 +70,8 @@ test:do_execsql_test(
SELECT "idx","neq","nlt","ndlt",msgpack_decode_sample("sample") FROM "_sql_stat4" where "idx" = %i;
]], t1.index['I1'].id), {
-- <1.2>
- 1, "1 1", "0 0", "0 0", "(0) (0)", 1, "1 1", "1 1", "1 1", "(1) (1)",
- 1, "1 1", "2 2", "2 2", "(2) (2)", 1, "1 1", "3 3", "3 3", "(3) (3)",
+ 1, "1 1", "0 0", "0 0", "(0) (0)", 1, "1 1", "1 1", "1 1", "(1) (1)",
+ 1, "1 1", "2 2", "2 2", "(2) (2)", 1, "1 1", "3 3", "3 3", "(3) (3)",
1, "1 1", "4 4", "4 4", "(4) (4)"
-- </1.2>
})
@@ -82,8 +82,8 @@ test:do_execsql_test(
SELECT "idx","neq","nlt","ndlt",msgpack_decode_sample("sample") FROM "_sql_stat4" where "idx" = %i;
]], t1.index[0].id), {
-- <1.3>
- 0, '1', '0', '0', '(0)', 0, '1', '1', '1', '(1)',
- 0, '1', '2', '2', '(2)', 0, '1', '3', '3', '(3)',
+ 0, '1', '0', '0', '(0)', 0, '1', '1', '1', '(1)',
+ 0, '1', '2', '2', '(2)', 0, '1', '3', '3', '(3)',
0, '1', '4', '4', '(4)'
-- </1.3>
})
^ permalink raw reply [flat|nested] 4+ messages in thread
end of thread, other threads:[~2018-08-29 19:27 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2018-08-23 9:51 [tarantool-patches] [PATCH v1 1/1] sql: hold in stat tables space/index id instead of name imeevma
2018-08-27 16:32 ` [tarantool-patches] " Vladislav Shpilevoy
2018-08-29 11:37 ` Imeev Mergen
2018-08-29 19:27 ` Vladislav Shpilevoy
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox