[tarantool-patches] [PATCH v1 3/3] sql: create new space for SQL statistics
imeevma at tarantool.org
imeevma at tarantool.org
Thu Mar 21 22:30:08 MSK 2019
This patch creates new space for SQL statistics. This space named
"_sql_stat" and used instead of spaces _sql_stat1 and _sql_stat4.
Number of rows with statistics of any space equivalent to number
of indexes of space. Each row of space _sql_stat contains all SQL
statistics of one index.
Each row of space _sql_stat equivalent to one row of space
_sql_stat1 and from 1 to 24 rows in space _sql_stat4.
Part of #2843
---
src/box/bootstrap.snap | Bin 1831 -> 1876 bytes
src/box/lua/space.cc | 2 +
src/box/lua/upgrade.lua | 27 +-
src/box/schema.cc | 7 +
src/box/schema_def.h | 1 +
src/box/sql/analyze.c | 836 +++++++++++++--------------------
src/box/sql/build.c | 82 +---
src/box/sql/sqlInt.h | 4 +-
test/app-tap/tarantoolctl.test.lua | 2 +-
test/box-py/bootstrap.result | 7 +-
test/box/access_misc.result | 4 +
test/box/access_sysview.result | 2 +-
test/sql-tap/analyze1.test.lua | 159 ++-----
test/sql-tap/analyze3.test.lua | 2 +-
test/sql-tap/analyze4.test.lua | 14 +-
test/sql-tap/analyze5.test.lua | 29 +-
test/sql-tap/analyze9.test.lua | 205 ++++----
test/sql-tap/lua/sqltester.lua | 27 ++
test/sql/sql-statN-index-drop.result | 62 +--
test/sql/sql-statN-index-drop.test.lua | 12 +-
test/wal_off/alter.result | 2 +-
21 files changed, 650 insertions(+), 836 deletions(-)
diff --git a/src/box/bootstrap.snap b/src/box/bootstrap.snap
index 0bb446fb6903ac3ef630c419b909f7db3df0372a..242a0ac13178e9b35b9ca8749747ab266de3467d 100644
GIT binary patch
delta 1872
zcmV-W2e0_24%7~i8Gkc1GA(B|WH>WoI5i4MZgX^DZewLSATwb!W;kXwHZ3?~I5sUb
zI5}Z0IAk&~EjeQ_VKg;iH#B59VhUD6Y;R+0Iv{&}3JTS_3%bn)+yKrICI`f&00000
z04TLD{Qyv%HUO$eR1Zkt7^eU*%rL_YbA<2!LI_whvLg~9B7eUI<E?;Tkh$nlVK*XE
zl9iIaG_BlU<Y^_Nx{lB;M$Z at 9^G!Lu*0h^x+pM&WVw5SR+5px7<^Xfd@;P;L7PaM=
z=*4_nq)Xq}=z1lwPvhNr`SV&bdphe`<YoP;<90 at 02Hd?b`8gCjsd<ihuRqMSNc+&$
zWgfQQ-H_9sb$_+&Cy093h9bB>n$P?Uu9S2)r)u9nzh}MQ`&IAR?9#Yptx!sfoNy!o
zu6;F5m0N(V&e63m3-55Y>Yx7IIn4)mcBlgI;bKONz6W+#i!&<3jkc}MaS0S5J8*8y
zXi?JFpq5P`ZnV`oR$4WP`To|d3Ek=(yU-#J+n&oRUw_;SopZ~}4~hu}&P9m}jurrQ
z{siA=+`>Do-F!P<)@<)SNeU$T2fM!eo3-!`ElE)$*nTda9Bg%tt1e#HelA*)ni at 3e
zw5V#?lF~5P>Kw)Xyh*1;l_lxSi%`qXD7RqWki;>ep7%7iC6!U|q-BR_DM7!UokA%f
z*y<eh{eKLCsoc4K)!mNl`*u$DT#Z?={oi+uXW at 4)d!k?5d2VYo<^a#*7I*Pk%g!i_
zfqq#VRF&V&Dza-4?5vj`tB5rI^p{R-Nl68^I>+#`V!Vf->~>%-Ls#Fgc|bCXkOpzT
zIfI=Yq9X!Z4L~#`W=712*y<dCW<qeM7iW?I at qd!3DSaq;D0NuzyQ07$h9+hvW&?}{
zm<%u&Y9(EuFCZ+GWP!v{I+EGy97h%A3n<HrJ6MaQ&;RbS`9h0?IGNH`=SY;nZs>ZU
zsKMH*>z74xOr(F`u;yjF>YiwPcjp at HtP+)-LOlvwoukkQBQaZ at V~>Y(s>gy(L$t^Q
z)_>)tr)62kc3#Iim(iT`+qum<+}0=&3Eua+U;k%0mO>Onh4z5i>Kw_}_uFZ4cUz-6
zq^-^|==<>QmPT_TY;}&J?A5uLJ4>TC5L$SLHV?Ks#}zu7y4h~kL1JT1M`(<2MM^{{
z%#aZ+BC8OpLEJkns%VwMI8=}>UAk<cK!3J6N78)0zlF6(`?1wI0;rS1IFyh5p~y*E
zp+|>v_?w7sJU)Y4*5RBI!;)4QXWF4L%ey3{I<`7T6`yJqx_HK+5u at v%W61NkZgf*f
zgXdeT)|Jy;@eko$bv2*;T$FYG;z!a6<4ifWI!7EIHDVN0X<W0Pi#0VpV5$nGFn?|a
z2lZ}jb&j5m0Iwh;HC{z%(5Oz289F&MM at CKzdIRI;g^dfF7B)DqvZ8J#ipBN{LajKd
zIHfqDFr6^jm{2q(Xi5ZufT#gggir~LAOHahfFJ<j1qUhUsyq^4z(|b4Fpi-Zh+_~6
zLX86;00toi8bmD^^h%uR_Mv}9w}0T!*M1+ at bEN$~!h#MAi~#@u>lHj`lV%e;d0i`d
zW_ksx8W~<g0Y4%LR^nO2w_Kp#%#5Y#1Hh{ek%Vd}uFbYbZ`^8fax70{K7*^X*)zMP
z&jj<DlTU at nuqJJOCFYc{w)<$~j!}bDupwRdPz*g|AzJcgEuyQ8dn4gnw|_#iIrY$p
ztYfa49W9Hu9nDW&z*b^oK>tE_;ENX!5?w^ByWqi*MAOkHjXDeLD1g||kpB<Z=Z5^>
z0XA*kB$;h>LC_2E#DOffY_0|3D9LPnP98}_1OslUnx6k_fR=r84^q|8;mSS~_#YbQ
z&7A+xIB#ZZ?}D at uw%b_!n1ANUd8z26uF=rFok|$I^Y;LNrJ1rigU-ij6EKA(#cLgn
z<$3EvQ^%`V;aMk36%WAARqXESUr_kBl_Yt at tK08&!veK-gM!koEs(ErvlxF~URgP#
zZWX1M31MGX8=1}(Z|LGl$YRMI2wNx~RHYO{ssQy_0V8%_d_-=iihuXAto2St&!8(k
zsYZ?jWV|PbEQV2o?VTl&a?k!qlq&zDo$&J;z;^OLo$H)a^cqUwG9 at Pz4D;Qz1J2{g
zU8?IoW?$Fy7s1+DH1w;eXfsu#^tv353>z5YKVA5>S|M1!kS)WJ=$8gONXbkiN;>+#
z*Gt4m>QQJ`;E}Ftd4C>tPk6IgB-3^zEz`lA_*E+|O`d^sG7eVT-|fs$&cQ`9plF%-
zPI%r7Iolh at lPrZZr)>&wPkg`<EPbdKFAG{n7}$j!)-g?SR)*f;;fgw~?sd_tO~A{*
zE_q2d3jgb~QYhjQ(c62H$g3!kw4dZoDu-|R6HxuViQS5UA4!CBacK49L>cl?llS%=
zCE?Y8=otVCd~E8FA_*b<<6C7I{txq|{^YE-DFNDY9?3vG?`BuH?NM at pR5<qGkSnWN
Ku*)6Q5UuTj5Q>Wc
delta 1827
zcmV+;2i*A74yO)~8GkZ0H7#c}H)3WoGdVK~Np5p=VQyn(Iv`^*Gd5&pFkvk=W at R@m
zG%zzYEjT!4F)d?aGh{VnH8Wx{HDL-?Lu_wjYdRo%eF_TIx(m9^2Ce|k)s!0pr2qf`
z001bpFZ}>eEj0jYM??=v;25U>Fw8KA5aR at KMG#m>L*QW(A%7yj2IH-OV2p`GQD8SB
zQ<9aEzBH}eU*u^eqq>gJE=JE6+w)C1z1FnIIjXms%tRqmN&(pb)d1!IBbLvpo3p4b
z$3!pY+ag{1#zxmGiG3RH&dZ<IlG)Q)&mu4DR~@%A`ZD0|eaX+E*h$TE%zOP|u0`61
zt}gSi{qBaG_J6FaWj{gG!!{Jb{n32pXK<yYyE#?+{`o!Y{ob#7&t{j#En9_BTGWIi
z32^PJajM(`Y;}&VeOY*ivsM4}@6KsHxU)kQfDaclV)Q+*!&;nCA#Sv7b&gA(2-$&i
zV at 8XTz6P~y3UQ;Y&T-PJLCp8JW=!Z-=h%f7dD!+`R)6{8Ug(@!UVgZAS1>>+ZNbR`
zpw6Eex9|>YH{XtzHQT#Sk^+hT!LINAW-Yu!OH$Mbwx5e92V0%vs*4x4pNp2HrUpzp
zEvj0!q%;h+I!CcTXVPg=Wl1{oBGj at o$}QM8Bymir=RJ*WNo5o`Y1ttflb~PEN}&`G
zY;}(Met!nRRPJ2A>TXB&eLE+6uEwm`{_nfSv+z5YIngifJhwFpbAab at i@W%&WoNWx
zpkLMoRpocHitJhhJL~1gDk6<P{bdtdQc{7f&M~~K81Eq{yB(Oz(AD>A9*~40q(R(o
z&R}PU=!n2pXC at OgC1^+h(Ttc8F%x2|a|D_>aet;42STRiCG#Q1LrjMl4l90F6lh>(
zU^c*LC}k*NC|#(PWP!ebu22$3lA&}|VXJc-#mpBFmKS%h7E7Q1-DUHI771}O6t+4?
zq6~IJ*9%1r)>d7=ERtg){riSBFXL6`q-%V4=Njy+5|x!gJ(#V|k#%|LX<62>o!2p;
z5r0M|bS~@ha4e^K?9^}PHt%pdM2kr9zTf@&Kg)3xqM#|XM`^2bBwycer^VfEjp~rL
zI>(^z!@FA=&55wpIf}AZ=VI<Gjov_L;T_sM*y<d=4;51M-fT#!j<q>*GD1^?BN8)&
zmV|6|j&($f$Qp!d5cf`tDq5v54h5u3mwzr==#Q<=ku;z0Z(%Lcd~9`&0P3VL4%K6S
zC~}fk;L+h6{wAW^j?dthbvUQQu%s2nnRIB(@-9hfj;+p7#iv?@E}n5{#OONc81g)>
z8{HJr;Q7|7W#x2N{6lzGUCn1d7iFEl_>pwNI1`So&Jo8)jTi-08rSUSVogmCn17-|
zDU6%BK|LE=ougMHz$?fIjaLyJ8lBN2M at I(DiID??-n_VRVbj6}$L8i$RuqegTCu%?
zP%2I-PAE<%Og1J|fo-VqTx6&OMi77i1war0(FF%F=!ymsV6Z at pqcDzv7zjfUiXs~a
zKmZIv3N(mXFhZ*abuTo5t}r0bJb#VtMSW;5s(;`EhOL>ZG at V_Dw&p-8+e?Qb5ZH(U
z8w&cNkRT-mlY5E*j$JyRR7_U;;{~ix4OzC?q3X4Ia+*1cr!ninwax4~%hK_LeTB&v
zLjVBRs^4;QWQeYPv}dd-Dk13b>Y^0K&07c!{KzY;+^{zwba*L5k*TZ4R)2A5)rMV5
z1W#@}b)GGRnL+;xjWVv<D$TnPrhAM5C5b{upNYCzXQ?ndZ_JlrcixyU53||%^AMcJ
zXn-!gCygMQX9hfEOg&wHPV7l;!Y8+w%G6)<X<;+(Ard$AAApCZBTbsYJgW!(nR&|J
zKW8&<KWHVG1Y*2nb*s||dVfD!iR^d$j8txkAl2j9AER4<f-G5IreqOrTQ9UL|8iT+
z`jed*i{oi{_|G(#F*%Eg@(e*se+*q_EnS!*7j%53&FC-ksx8Q~^uVod^Bkx>ON?E8
zB&%*d-&__)6H<((ZgQw$7zx{+W69LE*&h?7B7a<sAC~}Z0mJEG=YKq-7ghsTx}5mh
zm2YW>?Z;EnR68??zGSv7f?8y1z%Mw_woZ)_#d6LLT2tbG!rMPqA;^9qOVp6)w)Uk3
zWj-M at 9sOHqC33{?D9#~wr0H7TN8Lc)Ob&^*Dez)Cm?wW<i_>jj;1n9Cw{1vsrpxC{
z>={t>jCkioCWhRtm4EX|*239%3IO;oK41!#Ues%p1qCUL?4u+<m}YW<L(kyhjyf?G
zTJAly-7>J7ywZd0|9Dm~is&PHOHUGW6(y5)oZ3z2 at GO5Ksw8hRS@Hb?aZr+0cGNIK
z43t=2$s?w>#u23tKrzBb1(Hfa^nV<p45fgF?oxj_`)Ep_UK{`xaiyL&vvbKES-e0i
R9JX=Dom4Gm<qp*lt?e81U0eVF
diff --git a/src/box/lua/space.cc b/src/box/lua/space.cc
index 9dfc97b..c2e6596 100644
--- a/src/box/lua/space.cc
+++ b/src/box/lua/space.cc
@@ -556,6 +556,8 @@ box_lua_space_init(struct lua_State *L)
lua_setfield(L, -2, "CLUSTER_ID");
lua_pushnumber(L, BOX_TRIGGER_ID);
lua_setfield(L, -2, "TRIGGER_ID");
+ lua_pushnumber(L, BOX_SQL_STAT_ID);
+ lua_setfield(L, -2, "SQL_STAT_ID");
lua_pushnumber(L, BOX_SQL_STAT1_ID);
lua_setfield(L, -2, "SQL_STAT1_ID");
lua_pushnumber(L, BOX_SQL_STAT4_ID);
diff --git a/src/box/lua/upgrade.lua b/src/box/lua/upgrade.lua
index c72711f..baadb9c 100644
--- a/src/box/lua/upgrade.lua
+++ b/src/box/lua/upgrade.lua
@@ -616,6 +616,30 @@ local function upgrade_to_2_1_0()
upgrade_priv_to_2_1_0()
end
+--------------------------------------------------------------------------------
+-- Tarantool 2.1.2
+--------------------------------------------------------------------------------
+
+local function upgrade_to_2_1_2()
+ local _space = box.space[box.schema.SPACE_ID]
+ local _index = box.space[box.schema.INDEX_ID]
+ local MAP = setmap({})
+
+ log.info("create space _sql_stat")
+ local stat_ft = {{name='space_id', type='unsigned'},
+ {name='index_id', type='unsigned'},
+ {name='stat', type='string'},
+ {name='neq', type='array'},
+ {name='nlt', type='array'},
+ {name='ndlt', type='array'},
+ {name='sample', type='array'}}
+ _space:insert{box.schema.SQL_STAT_ID, ADMIN, '_sql_stat', 'memtx', 0,
+ MAP, stat_ft}
+ log.info("create index primary on _sql_stat")
+ _index:insert{box.schema.SQL_STAT_ID, 0, 'primary', 'tree',
+ {unique = true}, {{0, 'unsigned'}, {1, 'unsigned'}}}
+end
+
local function get_version()
local version = box.space._schema:get{'version'}
if version == nil then
@@ -643,7 +667,8 @@ local function upgrade(options)
{version = mkversion(1, 7, 7), func = upgrade_to_1_7_7, auto = true},
{version = mkversion(1, 10, 0), func = upgrade_to_1_10_0, auto = true},
{version = mkversion(1, 10, 2), func = upgrade_to_1_10_2, auto = true},
- {version = mkversion(2, 1, 0), func = upgrade_to_2_1_0, auto = true}
+ {version = mkversion(2, 1, 0), func = upgrade_to_2_1_0, auto = true},
+ {version = mkversion(2, 1, 2), func = upgrade_to_2_1_2, auto = true},
}
for _, handler in ipairs(handlers) do
diff --git a/src/box/schema.cc b/src/box/schema.cc
index 74d70d8..6f17b36 100644
--- a/src/box/schema.cc
+++ b/src/box/schema.cc
@@ -445,6 +445,13 @@ schema_init()
sc_space_new(BOX_INDEX_ID, "_index", key_parts, 2,
&alter_space_on_replace_index, &on_stmt_begin_index);
+ /* _sql_stat - a statistics on space, seen in SQL. */
+ key_parts[0].fieldno = 0; /* space id */
+ key_parts[0].type = FIELD_TYPE_UNSIGNED;
+ key_parts[1].fieldno = 1; /* index id */
+ key_parts[1].type = FIELD_TYPE_UNSIGNED;
+ sc_space_new(BOX_SQL_STAT_ID, "_sql_stat", key_parts, 2, NULL, NULL);
+
/* _sql_stat1 - a simpler statistics on space, seen in SQL. */
key_parts[0].fieldno = 0; /* space name */
key_parts[0].type = FIELD_TYPE_STRING;
diff --git a/src/box/schema_def.h b/src/box/schema_def.h
index a760ecc..b81f1db 100644
--- a/src/box/schema_def.h
+++ b/src/box/schema_def.h
@@ -107,6 +107,7 @@ enum {
/** Space id of _space_sequence. */
BOX_SPACE_SEQUENCE_ID = 340,
/** Space ids for SQL statictics. */
+ BOX_SQL_STAT_ID = 347,
BOX_SQL_STAT1_ID = 348,
BOX_SQL_STAT4_ID = 349,
/** Space id of _fk_constraint. */
diff --git a/src/box/sql/analyze.c b/src/box/sql/analyze.c
index ffb7335..1d57311 100644
--- a/src/box/sql/analyze.c
+++ b/src/box/sql/analyze.c
@@ -124,22 +124,15 @@
* @param table_name Delete records of this table if specified.
*/
static void
-vdbe_emit_stat_space_open(struct Parse *parse, const char *table_name)
+vdbe_emit_stat_space_open(struct Parse *parse, uint32_t space_id)
{
- const char *stat_names[] = {"_sql_stat1", "_sql_stat4"};
- const uint32_t stat_ids[] = {BOX_SQL_STAT1_ID, BOX_SQL_STAT4_ID};
- struct Vdbe *v = sqlGetVdbe(parse);
- assert(v != NULL);
- assert(sqlVdbeDb(v) == parse->db);
- for (uint i = 0; i < lengthof(stat_names); ++i) {
- const char *space_name = stat_names[i];
- if (table_name != NULL) {
- vdbe_emit_stat_space_clear(parse, space_name, NULL,
- table_name);
- } else {
- sqlVdbeAddOp1(v, OP_Clear, stat_ids[i]);
- }
- }
+ struct Vdbe *vdbe = sqlGetVdbe(parse);
+ assert(vdbe != NULL);
+ assert(sqlVdbeDb(vdbe) == parse->db);
+ if (space_id != BOX_ID_NIL)
+ vdbe_emit_stat_space_clear(parse, space_id, BOX_ID_NIL);
+ else
+ sqlVdbeAddOp1(vdbe, OP_Clear, BOX_SQL_STAT_ID);
}
/*
@@ -781,31 +774,35 @@ callStatGet(Vdbe * v, int regStat4, int iParam, int regOut)
static void
vdbe_emit_analyze_space(struct Parse *parse, struct space *space)
{
- assert(space != NULL);
- struct space *stat1 = space_by_id(BOX_SQL_STAT1_ID);
- assert(stat1 != NULL);
- struct space *stat4 = space_by_id(BOX_SQL_STAT4_ID);
- assert(stat4 != NULL);
-
- /* Register to hold Stat4Accum object. */
- int stat4_reg = ++parse->nMem;
- /* Index of changed index field. */
- int chng_reg = ++parse->nMem;
- /* Key argument passed to stat_push(). */
- int key_reg = ++parse->nMem;
- /* Temporary use register. */
- int tmp_reg = ++parse->nMem;
- /* Register containing table name. */
- int tab_name_reg = ++parse->nMem;
- /* Register containing index name. */
- int idx_name_reg = ++parse->nMem;
- /* Value for the stat column of _sql_stat1. */
- int stat1_reg = ++parse->nMem;
- /* MUST BE LAST (see below). */
- int prev_reg = ++parse->nMem;
/* Do not gather statistics on system tables. */
if (space_is_system(space))
return;
+ assert(space != NULL);
+ struct space *stat = space_by_id(BOX_SQL_STAT_ID);
+ assert(stat != NULL);
+
+ int space_id_reg = ++parse->nMem;
+ int index_id_reg = ++parse->nMem;
+ int stat_reg = ++parse->nMem;
+ int neq_reg = ++parse->nMem;
+ int nlt_reg = ++parse->nMem;
+ int ndlt_reg = ++parse->nMem;
+ int sample_reg = ++parse->nMem;
+
+ int stat_accum_reg = ++parse->nMem;
+ int changed_field_reg = ++parse->nMem;
+ int key_reg = ++parse->nMem;
+ int tmp_reg = ++parse->nMem;
+ int neq_array_reg = ++parse->nMem;
+ parse->nMem += SQL_STAT4_SAMPLES;
+ int nlt_array_reg = parse->nMem;
+ parse->nMem += SQL_STAT4_SAMPLES;
+ int ndlt_array_reg = parse->nMem;
+ parse->nMem += SQL_STAT4_SAMPLES;
+ int sample_array_reg = parse->nMem;
+ parse->nMem += SQL_STAT4_SAMPLES;
+ int prev_reg = parse->nMem;
+
/*
* Open a read-only cursor on the table. Also allocate
* a cursor number to use for scanning indexes.
@@ -813,60 +810,21 @@ vdbe_emit_analyze_space(struct Parse *parse, struct space *space)
int tab_cursor = parse->nTab;
parse->nTab += 2;
assert(space->index_count != 0);
- struct Vdbe *v = sqlGetVdbe(parse);
- assert(v != NULL);
- const char *tab_name = space_name(space);
- sqlVdbeAddOp4(v, OP_IteratorOpen, tab_cursor, 0, 0, (void *) space,
- P4_SPACEPTR);
- sqlVdbeLoadString(v, tab_name_reg, space->def->name);
+ struct index *pk = space_index(space, 0);
+ int pk_part_count = pk->def->key_def->part_count;
+ struct Vdbe *vdbe = sqlGetVdbe(parse);
+ assert(vdbe != NULL);
+ sqlVdbeAddOp4(vdbe, OP_IteratorOpen, tab_cursor, 0, 0, (void *) space,
+ P4_SPACEPTR);
+ sqlVdbeAddOp2(vdbe, OP_Integer, space->def->id, space_id_reg);
for (uint32_t j = 0; j < space->index_count; ++j) {
struct index *idx = space->index[j];
- const char *idx_name;
- /*
- * Primary indexes feature automatically generated
- * names. Thus, for the sake of clarity, use
- * instead more familiar table name.
- */
- if (idx->def->iid == 0)
- idx_name = tab_name;
- else
- idx_name = idx->def->name;
- int part_count = idx->def->key_def->part_count;
- /* Populate the register containing the index name. */
- sqlVdbeLoadString(v, idx_name_reg, idx_name);
- VdbeComment((v, "Analysis for %s.%s", tab_name, idx_name));
- /*
- * Pseudo-code for loop that calls stat_push():
- *
- * Rewind csr
- * if eof(csr) goto end_of_scan;
- * chng_reg = 0
- * goto chng_addr_0;
- *
- * next_row:
- * chng_reg = 0
- * if( idx(0) != prev_reg(0) ) goto chng_addr_0
- * chng_reg = 1
- * if( idx(1) != prev_reg(1) ) goto chng_addr_1
- * ...
- * chng_reg = N
- * goto chng_addr_N
- *
- * chng_addr_0:
- * prev_reg(0) = idx(0)
- * chng_addr_1:
- * prev_reg(1) = idx(1)
- * ...
- *
- * distinct_addr:
- * key_reg = idx(key)
- * stat_push(P, chng_reg, key_reg)
- * Next csr
- * if !eof(csr) goto next_row;
- *
- * end_of_scan:
- */
+ sqlVdbeAddOp2(vdbe, OP_Integer, idx->def->iid,
+ index_id_reg);
+ VdbeComment((vdbe, "Analysis for %s.%s", space_name(space),
+ idx->def->name));
+ int part_count = idx->def->key_def->part_count;
/*
* Make sure there are enough memory cells
* allocated to accommodate the prev_reg array
@@ -874,15 +832,16 @@ vdbe_emit_analyze_space(struct Parse *parse, struct space *space)
* when building a record to insert into
* the sample column of the _sql_stat4 table).
*/
- parse->nMem = MAX(parse->nMem, prev_reg + part_count);
+ parse->nMem = MAX(parse->nMem, prev_reg + part_count +
+ pk_part_count);
/* Open a cursor on the index being analyzed. */
int idx_cursor;
if (j != 0) {
idx_cursor = parse->nTab - 1;
- sqlVdbeAddOp4(v, OP_IteratorOpen, idx_cursor,
- idx->def->iid, 0,
- (void *) space, P4_SPACEPTR);
- VdbeComment((v, "%s", idx->def->name));
+ sqlVdbeAddOp4(vdbe, OP_IteratorOpen, idx_cursor,
+ idx->def->iid, 0, (void *) space,
+ P4_SPACEPTR);
+ VdbeComment((vdbe, "%s", idx->def->name));
} else {
/* We have already opened cursor on PK. */
idx_cursor = tab_cursor;
@@ -900,160 +859,136 @@ vdbe_emit_analyze_space(struct Parse *parse, struct space *space)
*
* The third argument is only used for STAT4
*/
- sqlVdbeAddOp2(v, OP_Count, idx_cursor, stat4_reg + 3);
- sqlVdbeAddOp2(v, OP_Integer, part_count, stat4_reg + 1);
- sqlVdbeAddOp2(v, OP_Integer, part_count, stat4_reg + 2);
- sqlVdbeAddOp4(v, OP_Function0, 0, stat4_reg + 1, stat4_reg,
- (char *)&statInitFuncdef, P4_FUNCDEF);
- sqlVdbeChangeP5(v, 3);
- /*
- * Implementation of the following:
- *
- * Rewind csr
- * if eof(csr) goto end_of_scan;
- * chng_reg = 0
- * goto next_push_0;
- */
- int rewind_addr = sqlVdbeAddOp1(v, OP_Rewind, idx_cursor);
- sqlVdbeAddOp2(v, OP_Integer, 0, chng_reg);
- int distinct_addr = sqlVdbeMakeLabel(v);
+ sqlVdbeAddOp2(vdbe, OP_Count, idx_cursor, stat_accum_reg + 3);
+ sqlVdbeAddOp2(vdbe, OP_Integer, part_count, stat_accum_reg + 1);
+ sqlVdbeAddOp2(vdbe, OP_Integer, part_count, stat_accum_reg + 2);
+ sqlVdbeAddOp4(vdbe, OP_Function0, 0, stat_accum_reg + 1,
+ stat_accum_reg, (char *)&statInitFuncdef,
+ P4_FUNCDEF);
+ sqlVdbeChangeP5(vdbe, 3);
+ int rewind_addr = sqlVdbeAddOp1(vdbe, OP_Rewind, idx_cursor);
+
+
+ sqlVdbeAddOp2(vdbe, OP_Integer, 0, changed_field_reg);
+ int distinct_addr = sqlVdbeMakeLabel(vdbe);
/* Array of jump instruction addresses. */
int *jump_addrs = region_alloc(&parse->region,
sizeof(int) * part_count);
if (jump_addrs == NULL) {
diag_set(OutOfMemory, sizeof(int) * part_count,
- "region", "jump_addrs");
+ "region_alloc", "jump_addrs");
parse->is_aborted = true;
return;
}
+
+ sqlVdbeAddOp0(vdbe, OP_Goto);
+ int next_row_addr = sqlVdbeCurrentAddr(vdbe);
+
/*
- * next_row:
- * chng_reg = 0
- * if( idx(0) != prev_reg(0) ) goto chng_addr_0
- * chng_reg = 1
- * if( idx(1) != prev_reg(1) ) goto chng_addr_1
- * ...
- * chng_reg = N
- * goto distinct_addr
+ * For a single-column UNIQUE index, once we have found a
+ * non-NULL row, we know that all the rest will be
+ * distinct, so skip subsequent distinctness tests.
*/
- sqlVdbeAddOp0(v, OP_Goto);
- int next_row_addr = sqlVdbeCurrentAddr(v);
if (part_count == 1 && idx->def->opts.is_unique) {
- /*
- * For a single-column UNIQUE index, once
- * we have found a non-NULL row, we know
- * that all the rest will be distinct, so
- * skip subsequent distinctness tests.
- */
- sqlVdbeAddOp2(v, OP_NotNull, prev_reg,
- distinct_addr);
+ sqlVdbeAddOp2(vdbe, OP_NotNull, prev_reg,
+ distinct_addr);
}
+
struct key_part *part = idx->def->key_def->parts;
for (int i = 0; i < part_count; ++i, ++part) {
struct coll *coll = part->coll;
- sqlVdbeAddOp2(v, OP_Integer, i, chng_reg);
- sqlVdbeAddOp3(v, OP_Column, idx_cursor,
- part->fieldno, tmp_reg);
- jump_addrs[i] = sqlVdbeAddOp4(v, OP_Ne, tmp_reg, 0,
- prev_reg + i,
- (char *)coll,
- P4_COLLSEQ);
- sqlVdbeChangeP5(v, SQL_NULLEQ);
+ sqlVdbeAddOp2(vdbe, OP_Integer, i, changed_field_reg);
+ sqlVdbeAddOp3(vdbe, OP_Column, idx_cursor,
+ part->fieldno, tmp_reg);
+ jump_addrs[i] = sqlVdbeAddOp4(vdbe, OP_Ne, tmp_reg, 0,
+ prev_reg + i,
+ (char *)coll, P4_COLLSEQ);
+ sqlVdbeChangeP5(vdbe, SQL_NULLEQ);
}
- sqlVdbeAddOp2(v, OP_Integer, part_count, chng_reg);
- sqlVdbeGoto(v, distinct_addr);
- /*
- * chng_addr_0:
- * prev_reg(0) = idx(0)
- * chng_addr_1:
- * prev_reg(1) = idx(1)
- * ...
- */
- sqlVdbeJumpHere(v, next_row_addr - 1);
+ sqlVdbeAddOp2(vdbe, OP_Integer, part_count, changed_field_reg);
+ sqlVdbeGoto(vdbe, distinct_addr);
+
+ sqlVdbeJumpHere(vdbe, next_row_addr - 1);
part = idx->def->key_def->parts;
for (int i = 0; i < part_count; ++i, ++part) {
- sqlVdbeJumpHere(v, jump_addrs[i]);
- sqlVdbeAddOp3(v, OP_Column, idx_cursor,
- part->fieldno, prev_reg + i);
+ sqlVdbeJumpHere(vdbe, jump_addrs[i]);
+ sqlVdbeAddOp3(vdbe, OP_Column, idx_cursor,
+ part->fieldno, prev_reg + i);
}
- sqlVdbeResolveLabel(v, distinct_addr);
- /*
- * chng_addr_N:
- * key_reg = idx(key)
- * stat_push(P, chng_reg, key_reg)
- * Next csr
- * if !eof(csr) goto next_row;
- */
- assert(key_reg == (stat4_reg + 2));
- struct index *pk = space_index(space, 0);
- int pk_part_count = pk->def->key_def->part_count;
- /* Allocate memory for array. */
- parse->nMem = MAX(parse->nMem,
- prev_reg + part_count + pk_part_count);
+ sqlVdbeResolveLabel(vdbe, distinct_addr);
+
int stat_key_reg = prev_reg + part_count;
for (int i = 0; i < pk_part_count; i++) {
uint32_t k = pk->def->key_def->parts[i].fieldno;
assert(k < space->def->field_count);
- sqlVdbeAddOp3(v, OP_Column, idx_cursor, k,
- stat_key_reg + i);
- VdbeComment((v, "%s", space->def->fields[k].name));
+ sqlVdbeAddOp3(vdbe, OP_Column, idx_cursor, k,
+ stat_key_reg + i);
+ VdbeComment((vdbe, "%s", space->def->fields[k].name));
}
- sqlVdbeAddOp3(v, OP_MakeRecord, stat_key_reg,
- pk_part_count, key_reg);
- assert(chng_reg == (stat4_reg + 1));
- sqlVdbeAddOp4(v, OP_Function0, 1, stat4_reg, tmp_reg,
- (char *)&statPushFuncdef, P4_FUNCDEF);
- sqlVdbeChangeP5(v, 3);
- sqlVdbeAddOp2(v, OP_Next, idx_cursor, next_row_addr);
- /* Add the entry to the stat1 table. */
- callStatGet(v, stat4_reg, STAT_GET_STAT1, stat1_reg);
- enum field_type types[4] = { FIELD_TYPE_STRING,
- FIELD_TYPE_STRING,
- FIELD_TYPE_STRING,
- field_type_MAX };
- sqlVdbeAddOp4(v, OP_MakeRecord, tab_name_reg, 4, tmp_reg,
- (char *)types, sizeof(types));
- sqlVdbeAddOp4(v, OP_IdxInsert, tmp_reg, 0, 0,
- (char *)stat1, P4_SPACEPTR);
- /* Add the entries to the stat4 table. */
- int eq_reg = stat1_reg;
- int lt_reg = stat1_reg + 1;
- int dlt_reg = stat1_reg + 2;
- int sample_reg = stat1_reg + 3;
- int col_reg = stat1_reg + 4;
- int sample_key_reg = col_reg + part_count;
- parse->nMem = MAX(parse->nMem, col_reg + part_count);
- int next_addr = sqlVdbeCurrentAddr(v);
- callStatGet(v, stat4_reg, STAT_GET_KEY, sample_key_reg);
- int is_null_addr = sqlVdbeAddOp1(v, OP_IsNull,
- sample_key_reg);
- callStatGet(v, stat4_reg, STAT_GET_NEQ, eq_reg);
- callStatGet(v, stat4_reg, STAT_GET_NLT, lt_reg);
- callStatGet(v, stat4_reg, STAT_GET_NDLT, dlt_reg);
- sqlVdbeAddOp4Int(v, OP_NotFound, tab_cursor, next_addr,
- sample_key_reg, 0);
- /*
- * We know that the sample_key_reg row exists
- * because it was read by the previous loop.
- * Thus the not-found jump of seekOp will never
- * be taken.
- */
- for (int i = 0; i < part_count; i++) {
- uint32_t tabl_col = idx->def->key_def->parts[i].fieldno;
- sqlExprCodeGetColumnOfTable(v, space->def,
- tab_cursor, tabl_col,
- col_reg + i);
+ sqlVdbeAddOp3(vdbe, OP_MakeRecord, stat_key_reg, pk_part_count,
+ key_reg);
+ sqlVdbeAddOp4(vdbe, OP_Function0, 1, stat_accum_reg, tmp_reg,
+ (char *)&statPushFuncdef, P4_FUNCDEF);
+ sqlVdbeChangeP5(vdbe, 3);
+ sqlVdbeAddOp2(vdbe, OP_Next, idx_cursor, next_row_addr);
+
+
+ int sample_key_reg = prev_reg + part_count;
+ callStatGet(vdbe, stat_accum_reg, STAT_GET_STAT1, stat_reg);
+ sqlVdbeAddOp2(vdbe, OP_Integer, 0, tmp_reg);
+ int is_null_addr = sqlVdbeMakeLabel(vdbe);
+ for (int i = 0; i < SQL_STAT4_SAMPLES; ++i) {
+ int next_addr = sqlVdbeCurrentAddr(vdbe);
+ callStatGet(vdbe, stat_accum_reg, STAT_GET_KEY,
+ sample_key_reg);
+ sqlVdbeAddOp2(vdbe, OP_IsNull, sample_key_reg,
+ is_null_addr);
+ callStatGet(vdbe, stat_accum_reg, STAT_GET_NEQ,
+ neq_array_reg + i);
+ callStatGet(vdbe, stat_accum_reg, STAT_GET_NLT,
+ nlt_array_reg + i);
+ callStatGet(vdbe, stat_accum_reg, STAT_GET_NDLT,
+ ndlt_array_reg + i);
+ sqlVdbeAddOp4Int(vdbe, OP_NotFound, tab_cursor,
+ next_addr, sample_key_reg, 0);
+ /*
+ * We know that the sample_key_reg row exists
+ * because it was read by the previous loop.
+ * Thus the not-found jump of seekOp will never
+ * be taken.
+ */
+ for (int i = 0; i < part_count; i++) {
+ uint32_t tabl_col =
+ idx->def->key_def->parts[i].fieldno;
+ sqlExprCodeGetColumnOfTable(vdbe, space->def,
+ tab_cursor,
+ tabl_col,
+ prev_reg + i);
+ }
+ sqlVdbeAddOp3(vdbe, OP_MakeRecord, prev_reg, part_count,
+ sample_array_reg + i);
+ sqlVdbeChangeP5(vdbe, OPFLAG_MAKE_ARRAY);
+ sqlVdbeAddOp2(vdbe, OP_Integer, i + 1, tmp_reg);
}
- sqlVdbeAddOp3(v, OP_MakeRecord, col_reg, part_count,
- sample_reg);
- sqlVdbeAddOp3(v, OP_MakeRecord, tab_name_reg, 6, tmp_reg);
- sqlVdbeAddOp4(v, OP_IdxReplace, tmp_reg, 0, 0,
- (char *)stat4, P4_SPACEPTR);
- /* P1==1 for end-of-loop. */
- sqlVdbeAddOp2(v, OP_Goto, 1, next_addr);
- sqlVdbeJumpHere(v, is_null_addr);
- /* End of analysis. */
- sqlVdbeJumpHere(v, rewind_addr);
+
+ sqlVdbeResolveLabel(vdbe, is_null_addr);
+ sqlVdbeAddOp3(vdbe, OP_MakeRecord, neq_array_reg, tmp_reg,
+ neq_reg);
+ sqlVdbeChangeP5(vdbe, OPFLAG_P2_IS_REG | OPFLAG_MAKE_ARRAY);
+ sqlVdbeAddOp3(vdbe, OP_MakeRecord, nlt_array_reg, tmp_reg,
+ nlt_reg);
+ sqlVdbeChangeP5(vdbe, OPFLAG_P2_IS_REG | OPFLAG_MAKE_ARRAY);
+ sqlVdbeAddOp3(vdbe, OP_MakeRecord, ndlt_array_reg, tmp_reg,
+ ndlt_reg);
+ sqlVdbeChangeP5(vdbe, OPFLAG_P2_IS_REG | OPFLAG_MAKE_ARRAY);
+ sqlVdbeAddOp3(vdbe, OP_MakeRecord, sample_array_reg, tmp_reg,
+ sample_reg);
+ sqlVdbeChangeP5(vdbe, OPFLAG_P2_IS_REG | OPFLAG_MAKE_ARRAY);
+ sqlVdbeAddOp3(vdbe, OP_MakeRecord, space_id_reg, 7, tmp_reg);
+
+ sqlVdbeAddOp4(vdbe, OP_IdxReplace, tmp_reg, 0, 0,
+ (char *)stat, P4_SPACEPTR);
+ sqlVdbeJumpHere(vdbe, rewind_addr);
}
}
@@ -1087,7 +1022,7 @@ static void
sql_analyze_database(struct Parse *parser)
{
sql_set_multi_write(parser, false);
- vdbe_emit_stat_space_open(parser, NULL);
+ vdbe_emit_stat_space_open(parser, BOX_SQL_STAT_ID);
space_foreach(sql_space_foreach_analyze, (void *)parser);
loadAnalysis(parser);
}
@@ -1108,7 +1043,7 @@ vdbe_emit_analyze_table(struct Parse *parse, struct space *space)
* There are two system spaces for statistics: _sql_stat1
* and _sql_stat4.
*/
- vdbe_emit_stat_space_open(parse, space->def->name);
+ vdbe_emit_stat_space_open(parse, space->def->id);
vdbe_emit_analyze_space(parse, space);
loadAnalysis(parse);
}
@@ -1233,33 +1168,8 @@ decode_stat_string(const char *stat_string, int stat_size, tRowcnt *stat_exact,
* @retval 0 on success, -1 otherwise.
*/
static int
-analysis_loader(void *data, int argc, char **argv, char **unused)
+load_stat1(struct index_stat *stat, struct index *index, const char *stat1_str)
{
- assert(argc == 3);
- UNUSED_PARAMETER2(unused, argc);
- if (argv == 0 || argv[0] == 0 || argv[2] == 0)
- return 0;
- struct analysis_index_info *info = (struct analysis_index_info *) data;
- assert(info->stats != NULL);
- struct index_stat *stat = &info->stats[info->index_count++];
- struct space *space = space_by_name(argv[0]);
- if (space == NULL)
- return -1;
- struct index *index;
- uint32_t iid = box_index_id_by_name(space->def->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 (sql_stricmp(argv[0], argv[1]) != 0)
- return -1;
- index = space_index(space, 0);
- }
- assert(index != NULL);
/*
* Additional field is used to describe total
* count of tuples in index. Although now all
@@ -1285,11 +1195,11 @@ analysis_loader(void *data, int argc, char **argv, char **unused)
diag_set(OutOfMemory, stat1_size, "region", "tuple_log_est");
return -1;
}
- decode_stat_string(argv[2], column_count, stat->tuple_stat1,
+ decode_stat_string(stat1_str, column_count, stat->tuple_stat1,
stat->tuple_log_est);
stat->is_unordered = false;
stat->skip_scan_enabled = true;
- char *z = argv[2];
+ const char *z = stat1_str;
/* Position ptr at the end of stat string. */
for (; *z == ' ' || (*z >= '0' && *z <= '9'); ++z);
while (z[0]) {
@@ -1385,47 +1295,46 @@ sample_compare(const void *a, const void *b, void *arg)
* @retval 0 on success, -1 otherwise.
*/
static int
-load_stat_from_space(struct sql *db, const char *sql_select_prepare,
- const char *sql_select_load, struct index_stat *stats)
+load_stat_from_space(struct sql *db, struct index **indexes,
+ struct index_stat *stats)
{
- struct index **indexes = NULL;
- uint32_t index_count = box_index_len(BOX_SQL_STAT4_ID, 0);
- if (index_count > 0) {
- size_t alloc_size = sizeof(struct index *) * index_count;
- indexes = region_alloc(&fiber()->gc, alloc_size);
- if (indexes == NULL) {
- diag_set(OutOfMemory, alloc_size, "region", "indexes");
- return -1;
- }
- }
+ const char *load_query = "SELECT \"space_id\", \"index_id\", "\
+ "\"stat\", \"neq\", \"nlt\", \"ndlt\", "\
+ "\"sample\" FROM \"_sql_stat\";";
sql_stmt *stmt = NULL;
- int rc = sql_prepare(db, sql_select_prepare, -1, &stmt, 0);
- if (rc)
- goto finalize;
+ if (sql_prepare(db, load_query, -1, &stmt, 0) < 0)
+ return -1;
uint32_t current_idx_count = 0;
while (sql_step(stmt) == SQL_ROW) {
- const char *space_name = (char *)sql_column_text(stmt, 0);
- if (space_name == NULL)
+ uint32_t space_id = sql_column_int(stmt, 0);
+ if (space_id == 0)
continue;
- const char *index_name = (char *)sql_column_text(stmt, 1);
- if (index_name == NULL)
+ struct space *space = space_by_id(space_id);
+ if (space == NULL)
continue;
- uint32_t sample_count = sql_column_int(stmt, 2);
- struct space *space = space_by_name(space_name);
- assert(space != NULL);
- struct index *index;
- uint32_t iid = box_index_id_by_name(space->def->id, index_name,
- strlen(index_name));
- if (sql_stricmp(space_name, index_name) == 0 &&
- iid == BOX_ID_NIL)
- index = space_index(space, 0);
- else
- index = space_index(space, iid);
- assert(index != NULL);
+ uint32_t iid = sql_column_int(stmt, 1);
+ struct index *index = space_index(space, iid);
+ if (index == NULL)
+ continue;
+ const char *stat1 = (char *)sql_column_text(stmt, 2);
+ if (stat1 == NULL)
+ continue;
+ const char *neq = (const char *)sql_column_blob(stmt, 3);
+ const char *nlt = (const char *)sql_column_blob(stmt, 4);
+ const char *ndlt = (const char *)sql_column_blob(stmt, 5);
+ const char *sample_key = (const char *)sql_column_blob(stmt, 6);
+
uint32_t column_count = index->def->key_def->part_count;
+
+ uint32_t sample_count = mp_decode_array(&neq);
+ mp_decode_array(&nlt);
+ mp_decode_array(&ndlt);
+ mp_decode_array(&sample_key);
+
struct index_stat *stat = &stats[current_idx_count];
stat->sample_field_count = column_count;
stat->sample_count = 0;
+
/* Space for sample structs. */
size_t alloc_size = sizeof(struct index_sample) * sample_count;
/* Space for eq, lt and dlt stats. */
@@ -1442,8 +1351,7 @@ load_stat_from_space(struct sql *db, const char *sql_select_prepare,
stat->samples = region_alloc(&fiber()->gc, alloc_size);
if (stat->samples == NULL) {
diag_set(OutOfMemory, alloc_size, "region", "samples");
- rc = -1;
- goto finalize;
+ return -1;
}
memset(stat->samples, 0, alloc_size);
/* Marking memory manually. */
@@ -1457,124 +1365,148 @@ load_stat_from_space(struct sql *db, const char *sql_select_prepare,
pSpace += column_count;
stat->samples[i].dlt = pSpace;
pSpace += column_count;
+
}
- assert(((u8 *) pSpace) - alloc_size == (u8 *) (stat->samples));
indexes[current_idx_count] = index;
- assert(current_idx_count < index_count);
- current_idx_count++;
- }
- rc = sql_finalize(stmt);
- if (rc)
- goto finalize;
- rc = sql_prepare(db, sql_select_load, -1, &stmt, 0);
- if (rc)
- goto finalize;
- struct index *prev_index = NULL;
- current_idx_count = 0;
- while (sql_step(stmt) == SQL_ROW) {
- const char *space_name = (char *)sql_column_text(stmt, 0);
- if (space_name == NULL)
- continue;
- const char *index_name = (char *)sql_column_text(stmt, 1);
- if (index_name == NULL)
- continue;
- struct space *space = space_by_name(space_name);
- assert(space != NULL);
- struct index *index;
- uint32_t iid = box_index_id_by_name(space->def->id, index_name,
- strlen(index_name));
- if (iid != BOX_ID_NIL) {
- index = space_index(space, iid);
- } else {
- if (sql_stricmp(space_name, index_name) != 0)
+ if (load_stat1(stat, index, stat1) < 0)
+ return -1;
+ for (uint32_t i = 0; i < sample_count; ++i) {
+ struct index_sample *sample = &stat->samples[i];
+ const char *data;
+ uint32_t data_len;
+ data = mp_decode_str(&neq, &data_len);
+ decode_stat_string(data, column_count, sample->eq, 0);
+ data = mp_decode_str(&nlt, &data_len);
+ decode_stat_string(data, column_count, sample->lt, 0);
+ data = mp_decode_str(&ndlt, &data_len);
+ decode_stat_string(data, column_count, sample->dlt, 0);
+ const char *key = sample_key;
+ mp_next(&sample_key);
+ sample->key_size = sample_key - key;
+ sample->sample_key = region_alloc(&fiber()->gc,
+ sample->key_size);
+ if (sample->sample_key == NULL) {
+ sql_finalize(stmt);
+ diag_set(OutOfMemory, sample->key_size,
+ "region", "sample_key");
return -1;
- index = space_index(space, 0);
- }
- assert(index != NULL);
- uint32_t column_count = index->def->key_def->part_count;
- if (index != prev_index) {
- if (prev_index != NULL) {
- init_avg_eq(prev_index,
- &stats[current_idx_count]);
- current_idx_count++;
}
- prev_index = index;
- }
- struct index_stat *stat = &stats[current_idx_count];
- struct index_sample *sample =
- &stat->samples[stats[current_idx_count].sample_count];
- decode_stat_string((char *)sql_column_text(stmt, 2),
- column_count, sample->eq, 0);
- decode_stat_string((char *)sql_column_text(stmt, 3),
- column_count, sample->lt, 0);
- decode_stat_string((char *)sql_column_text(stmt, 4),
- column_count, sample->dlt, 0);
- /* Take a copy of the sample. */
- sample->key_size = sql_column_bytes(stmt, 5);
- sample->sample_key = region_alloc(&fiber()->gc,
- sample->key_size);
- if (sample->sample_key == NULL) {
- sql_finalize(stmt);
- rc = -1;
- diag_set(OutOfMemory, sample->key_size,
- "region", "sample_key");
- goto finalize;
- }
- if (sample->key_size > 0) {
- memcpy(sample->sample_key,
- sql_column_blob(stmt, 5),
- sample->key_size);
+ if (sample->key_size > 0) {
+ memcpy(sample->sample_key, key,
+ sample->key_size);
+ }
+ stats[current_idx_count].sample_count++;
}
- stats[current_idx_count].sample_count++;
+ init_avg_eq(index, &stats[current_idx_count]);
+ indexes[current_idx_count] = index;
+ current_idx_count++;
}
- rc = sql_finalize(stmt);
- if (rc == SQL_OK && prev_index != NULL)
- init_avg_eq(prev_index, &stats[current_idx_count]);
- assert(current_idx_count <= index_count);
for (uint32_t i = 0; i < current_idx_count; ++i) {
- struct index *index = indexes[i];
- assert(index != NULL);
qsort_arg(stats[i].samples,
stats[i].sample_count,
sizeof(struct index_sample),
- sample_compare, index->def->key_def);
+ sample_compare, indexes[i]->def->key_def);
+ }
+ return current_idx_count;
+}
+
+/**
+ * This function performs copy of statistics.
+ * In contrast to index_stat_dup(), there is no assumption
+ * that source statistics is allocated within chunk. But
+ * destination place is still one big chunk of heap memory.
+ * See also index_stat_sizeof() for understanding memory layout.
+ *
+ * @param dest One chunk of memory where statistics
+ * will be placed.
+ * @param src Statistics to be copied.
+ */
+static void
+stat_copy(struct index_stat *dest, const struct index_stat *src)
+{
+ assert(dest != NULL);
+ assert(src != NULL);
+ dest->sample_count = src->sample_count;
+ dest->sample_field_count = src->sample_field_count;
+ dest->skip_scan_enabled = src->skip_scan_enabled;
+ dest->is_unordered = src->is_unordered;
+ uint32_t array_size = src->sample_field_count * sizeof(uint32_t);
+ uint32_t stat1_offset = sizeof(struct index_stat);
+ char *pos = (char *) dest + stat1_offset;
+ memcpy(pos, src->tuple_stat1, array_size + sizeof(uint32_t));
+ dest->tuple_stat1 = (uint32_t *) pos;
+ pos += array_size + sizeof(uint32_t);
+ memcpy(pos, src->tuple_log_est, array_size + sizeof(uint32_t));
+ dest->tuple_log_est = (log_est_t *) pos;
+ pos += array_size + sizeof(uint32_t);
+ memcpy(pos, src->avg_eq, array_size);
+ dest->avg_eq = (uint32_t *) pos;
+ pos += array_size;
+ dest->samples = (struct index_sample *) pos;
+ pos += dest->sample_count * sizeof(struct index_sample);
+ for (uint32_t i = 0; i < dest->sample_count; ++i) {
+ dest->samples[i].key_size = src->samples[i].key_size;
+ memcpy(pos, src->samples[i].eq, array_size);
+ dest->samples[i].eq = (uint32_t *) pos;
+ pos += array_size;
+ memcpy(pos, src->samples[i].lt, array_size);
+ dest->samples[i].lt = (uint32_t *) pos;
+ pos += array_size;
+ memcpy(pos, src->samples[i].dlt, array_size);
+ dest->samples[i].dlt = (uint32_t *) pos;
+ pos += array_size;
+ memcpy(pos, src->samples[i].sample_key,
+ src->samples[i].key_size);
+ dest->samples[i].sample_key = pos;
+ pos += dest->samples[i].key_size;
}
- finalize:
- return rc;
}
static int
-load_stat_to_index(struct sql *db, const char *sql_select_load,
- struct index_stat **stats)
+load_stat_to_index(struct index **indexes, int index_count,
+ struct index_stat *stats)
{
- assert(stats != NULL && *stats != NULL);
- struct sql_stmt *stmt = NULL;
- if (sql_prepare(db, sql_select_load, -1, &stmt, 0) != 0)
+ /*
+ * Now we have complete statistics for each index
+ * allocated on the region. Time to copy it on the heap.
+ */
+ size_t heap_stats_size = index_count * sizeof(struct index_stat *);
+ struct index_stat **heap_stats = region_alloc(&fiber()->gc,
+ heap_stats_size);
+ if (heap_stats == NULL) {
+ diag_set(OutOfMemory, heap_stats_size, "region_alloc",
+ "heap_stats");
return -1;
- uint32_t current_idx_count = 0;
- while (sql_step(stmt) == SQL_ROW) {
- const char *space_name = (char *)sql_column_text(stmt, 0);
- if (space_name == NULL)
- continue;
- const char *index_name = (char *)sql_column_text(stmt, 1);
- if (index_name == NULL)
- continue;
- struct space *space = space_by_name(space_name);
- assert(space != NULL);
- struct index *index;
- uint32_t iid = box_index_id_by_name(space->def->id, index_name,
- strlen(index_name));
- if (iid != BOX_ID_NIL) {
- index = space_index(space, iid);
- } else {
- if (sql_stricmp(space_name, index_name) != 0)
- return -1;
- index = space_index(space, 0);
+ }
+ /*
+ * We are using 'everything or nothing' policy:
+ * if there is no enough memory for statistics even for
+ * one index, then refresh it for no one.
+ */
+ for (int i = 0; i < index_count; ++i) {
+ size_t size = index_stat_sizeof(stats[i].samples,
+ stats[i].sample_count,
+ stats[i].sample_field_count);
+ heap_stats[i] = malloc(size);
+ if (heap_stats[i] == NULL) {
+ diag_set(OutOfMemory, size, "malloc", "heap_stats");
+ for (int j = 0; j < i; ++j)
+ free(heap_stats[j]);
+ return -1;
}
- assert(index != NULL);
- free(index->def->opts.stat);
- index->def->opts.stat = stats[current_idx_count++];
+ }
+ /*
+ * We can't use stat_dup function since statistics on
+ * region doesn't fit into one memory chunk. Lets
+ * manually copy memory chunks and mark memory.
+ */
+ for (int i = 0; i < index_count; ++i)
+ stat_copy(heap_stats[i], &stats[i]);
+ /* Load stats to index. */
+ for (int i = 0; i < index_count; ++i) {
+ free(indexes[i]->def->opts.stat);
+ indexes[i]->def->opts.stat = heap_stats[i];
}
return 0;
}
@@ -1643,66 +1575,18 @@ index_field_tuple_est(const struct index_def *idx_def, uint32_t field)
return tnt_idx->def->opts.stat->tuple_log_est[field];
}
-/**
- * This function performs copy of statistics.
- * In contrast to index_stat_dup(), there is no assumption
- * that source statistics is allocated within chunk. But
- * destination place is still one big chunk of heap memory.
- * See also index_stat_sizeof() for understanding memory layout.
- *
- * @param dest One chunk of memory where statistics
- * will be placed.
- * @param src Statistics to be copied.
- */
-static void
-stat_copy(struct index_stat *dest, const struct index_stat *src)
-{
- assert(dest != NULL);
- assert(src != NULL);
- dest->sample_count = src->sample_count;
- dest->sample_field_count = src->sample_field_count;
- dest->skip_scan_enabled = src->skip_scan_enabled;
- dest->is_unordered = src->is_unordered;
- uint32_t array_size = src->sample_field_count * sizeof(uint32_t);
- uint32_t stat1_offset = sizeof(struct index_stat);
- char *pos = (char *) dest + stat1_offset;
- memcpy(pos, src->tuple_stat1, array_size + sizeof(uint32_t));
- dest->tuple_stat1 = (uint32_t *) pos;
- pos += array_size + sizeof(uint32_t);
- memcpy(pos, src->tuple_log_est, array_size + sizeof(uint32_t));
- dest->tuple_log_est = (log_est_t *) pos;
- pos += array_size + sizeof(uint32_t);
- memcpy(pos, src->avg_eq, array_size);
- dest->avg_eq = (uint32_t *) pos;
- pos += array_size;
- dest->samples = (struct index_sample *) pos;
- pos += dest->sample_count * sizeof(struct index_sample);
- for (uint32_t i = 0; i < dest->sample_count; ++i) {
- dest->samples[i].key_size = src->samples[i].key_size;
- memcpy(pos, src->samples[i].eq, array_size);
- dest->samples[i].eq = (uint32_t *) pos;
- pos += array_size;
- memcpy(pos, src->samples[i].lt, array_size);
- dest->samples[i].lt = (uint32_t *) pos;
- pos += array_size;
- memcpy(pos, src->samples[i].dlt, array_size);
- dest->samples[i].dlt = (uint32_t *) pos;
- pos += array_size;
- memcpy(pos, src->samples[i].sample_key,
- src->samples[i].key_size);
- dest->samples[i].sample_key = pos;
- pos += dest->samples[i].key_size;
- }
-}
-
int
sql_analysis_load(struct sql *db)
{
- ssize_t index_count = box_index_len(BOX_SQL_STAT1_ID, 0);
+ ssize_t index_count = box_index_len(BOX_SQL_STAT_ID, 0);
if (index_count < 0)
return SQL_TARANTOOL_ERROR;
if (box_txn_begin() != 0)
goto fail;
+ if (index_count == 0) {
+ box_txn_commit();
+ return SQL_OK;
+ }
size_t stats_size = index_count * sizeof(struct index_stat);
struct index_stat *stats = region_alloc(&fiber()->gc, stats_size);
if (stats == NULL) {
@@ -1710,74 +1594,20 @@ sql_analysis_load(struct sql *db)
goto fail;
}
memset(stats, 0, stats_size);
- struct analysis_index_info info;
- info.db = db;
- info.stats = stats;
- info.index_count = 0;
- const char *load_stat1 =
- "SELECT \"tbl\",\"idx\",\"stat\" FROM \"_sql_stat1\"";
- /* Load new statistics out of the _sql_stat1 table. */
- if (sql_exec(db, load_stat1, analysis_loader, &info, 0) != 0)
+
+ size_t indexes_size = index_count * sizeof(struct index *);
+ struct index **indexes = region_alloc(&fiber()->gc, indexes_size);
+ if (stats == NULL) {
+ diag_set(OutOfMemory, indexes_size, "region", "indexes");
goto fail;
- if (info.index_count == 0) {
- box_txn_commit();
- return SQL_OK;
}
- /*
- * This query is used to allocate enough memory for
- * statistics. Result rows are given in a form:
- * <table name>, <index name>, <count of samples>
- */
- const char *init_query = "SELECT \"tbl\",\"idx\",count(*) FROM "
- "\"_sql_stat4\" GROUP BY \"tbl\",\"idx\"";
- /* Query for loading statistics into in-memory structs. */
- const char *load_query = "SELECT \"tbl\",\"idx\",\"neq\",\"nlt\","
- "\"ndlt\",\"sample\" FROM \"_sql_stat4\"";
- /* Load the statistics from the _sql_stat4 table. */
- if (load_stat_from_space(db, init_query, load_query, stats) != 0)
- goto fail;
- /*
- * Now we have complete statistics for each index
- * allocated on the region. Time to copy it on the heap.
- */
- size_t heap_stats_size = info.index_count * sizeof(struct index_stat *);
- struct index_stat **heap_stats = region_alloc(&fiber()->gc,
- heap_stats_size);
- if (heap_stats == NULL) {
- diag_set(OutOfMemory, heap_stats_size, "malloc", "heap_stats");
+ memset(indexes, 0, indexes_size);
+
+ /* Load the statistics from the _sql_stat table. */
+ int index_count_new = load_stat_from_space(db, indexes, stats);
+ if (index_count_new < 0)
goto fail;
- }
- /*
- * We are using 'everything or nothing' policy:
- * if there is no enough memory for statistics even for
- * one index, then refresh it for no one.
- */
- for (uint32_t i = 0; i < info.index_count; ++i) {
- size_t size = index_stat_sizeof(stats[i].samples,
- stats[i].sample_count,
- stats[i].sample_field_count);
- heap_stats[i] = malloc(size);
- if (heap_stats[i] == NULL) {
- diag_set(OutOfMemory, size, "malloc", "heap_stats");
- for (uint32_t j = 0; j < i; ++j)
- free(heap_stats[j]);
- goto fail;
- }
- }
- /*
- * We can't use stat_dup function since statistics on
- * region doesn't fit into one memory chunk. Lets
- * manually copy memory chunks and mark memory.
- */
- for (uint32_t i = 0; i < info.index_count; ++i)
- stat_copy(heap_stats[i], &stats[i]);
- /*
- * Ordered query is needed to be sure that indexes come
- * in the same order as in previous SELECTs.
- */
- const char *order_query = "SELECT \"tbl\",\"idx\" FROM "
- "\"_sql_stat4\" GROUP BY \"tbl\",\"idx\"";
- if (load_stat_to_index(db, order_query, heap_stats) != 0)
+ if (load_stat_to_index(indexes, index_count_new, stats) != 0)
goto fail;
if (box_txn_commit() != 0)
return SQL_TARANTOOL_ERROR;
diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index 0c06555..135a03d 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -1349,75 +1349,26 @@ 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 sql *db = parse->db;
-
- struct Expr *col_name_expr = sqlExpr(db, TK_ID, col_name);
- if (col_name_expr == NULL)
- return NULL;
- struct Expr *col_value_expr = sqlExpr(db, TK_STRING, col_value);
- if (col_value_expr == NULL) {
- sql_expr_delete(db, col_name_expr, false);
- return NULL;
- }
- return sqlPExpr(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)
+vdbe_emit_stat_space_clear(struct Parse *parse, uint32_t space_id,
+ uint32_t index_id)
{
- assert(idx_name != NULL || table_name != NULL);
struct sql *db = parse->db;
assert(!db->mallocFailed);
struct SrcList *src_list = sql_alloc_src_list(db);
if (src_list != NULL)
- src_list->a[0].zName = sqlDbStrDup(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 = sqlExprAnd(db, expr, where);
- }
- if (table_name != NULL) {
- struct Expr *expr = sql_id_eq_str_expr(parse, "tbl", table_name);
- if (expr != NULL)
- where = sqlExprAnd(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);
+ src_list->a[0].zName = sqlDbStrDup(db, "_sql_stat");
+ struct Expr *expr = NULL;
+ struct Expr *col = sqlExpr(db, TK_ID, "space_id");
+ struct Expr *val = sqlExprInteger(db, space_id);
+ if (col != NULL && val != NULL)
+ expr = sqlPExpr(parse, TK_EQ, col, val);
+ if (index_id != BOX_ID_NIL && expr != NULL) {
+ col = sqlExpr(db, TK_ID, "index_id");
+ val = sqlExprInteger(db, index_id);
+ expr = sqlExprAnd(db, sqlPExpr(parse, TK_EQ, col, val), expr);
+ }
+ sql_table_delete_from(parse, src_list, expr);
}
/**
@@ -1638,7 +1589,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);
+ vdbe_emit_stat_space_clear(parse_context, space->def->id, BOX_ID_NIL);
sql_code_drop_table(parse_context, space, is_view);
exit_drop_table:
@@ -2457,7 +2408,8 @@ 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);
+ vdbe_emit_stat_space_clear(parse_context, space->def->id,
+ index->def->iid);
int record_reg = ++parse_context->nMem;
int space_id_reg = ++parse_context->nMem;
sqlVdbeAddOp2(v, OP_Integer, space->def->id, space_id_reg);
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index aed6cfb..a8a96db 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -4939,7 +4939,7 @@ vdbe_emit_halt_with_presence_test(struct Parse *parser, int space_id,
* @param table_name Table name.
*/
void
-vdbe_emit_stat_space_clear(struct Parse *parse, const char *stat_table_name,
- const char *idx_name, const char *table_name);
+vdbe_emit_stat_space_clear(struct Parse *parse, uint32_t space_id,
+ uint32_t index_id);
#endif /* sqlINT_H */
diff --git a/test/app-tap/tarantoolctl.test.lua b/test/app-tap/tarantoolctl.test.lua
index db046e0..62a95c3 100755
--- a/test/app-tap/tarantoolctl.test.lua
+++ b/test/app-tap/tarantoolctl.test.lua
@@ -388,7 +388,7 @@ do
check_ctlcat_xlog(test_i, dir, "--from=3 --to=6 --format=json --show-system --replica 1", "\n", 3)
check_ctlcat_xlog(test_i, dir, "--from=3 --to=6 --format=json --show-system --replica 1 --replica 2", "\n", 3)
check_ctlcat_xlog(test_i, dir, "--from=3 --to=6 --format=json --show-system --replica 2", "\n", 0)
- check_ctlcat_snap(test_i, dir, "--space=280", "---\n", 23)
+ check_ctlcat_snap(test_i, dir, "--space=280", "---\n", 24)
check_ctlcat_snap(test_i, dir, "--space=288", "---\n", 49)
end)
end)
diff --git a/test/box-py/bootstrap.result b/test/box-py/bootstrap.result
index 3e43945..29559a2 100644
--- a/test/box-py/bootstrap.result
+++ b/test/box-py/bootstrap.result
@@ -4,7 +4,7 @@ box.internal.bootstrap()
box.space._schema:select{}
---
- - ['max_id', 511]
- - ['version', 2, 1, 0]
+ - ['version', 2, 1, 2]
...
box.space._cluster:select{}
---
@@ -73,6 +73,10 @@ 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'}]]
+ - [347, 1, '_sql_stat', 'memtx', 0, {}, [{'name': 'space_id', 'type': 'unsigned'},
+ {'name': 'index_id', 'type': 'unsigned'}, {'name': 'stat', 'type': 'string'},
+ {'name': 'neq', 'type': 'array'}, {'name': 'nlt', 'type': 'array'}, {'name': 'ndlt',
+ 'type': 'array'}, {'name': 'sample', 'type': 'array'}]]
- [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',
@@ -133,6 +137,7 @@ 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']]]
+ - [347, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1, 'unsigned']]]
- [348, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'string']]]
- [349, 0, 'primary', 'tree', {'unique': true}, [[0, 'string'], [1, 'string'], [
5, 'scalar']]]
diff --git a/test/box/access_misc.result b/test/box/access_misc.result
index 4ffeb38..adb7b02 100644
--- a/test/box/access_misc.result
+++ b/test/box/access_misc.result
@@ -813,6 +813,10 @@ 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'}]]
+ - [347, 1, '_sql_stat', 'memtx', 0, {}, [{'name': 'space_id', 'type': 'unsigned'},
+ {'name': 'index_id', 'type': 'unsigned'}, {'name': 'stat', 'type': 'string'},
+ {'name': 'neq', 'type': 'array'}, {'name': 'nlt', 'type': 'array'}, {'name': 'ndlt',
+ 'type': 'array'}, {'name': 'sample', 'type': 'array'}]]
- [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',
diff --git a/test/box/access_sysview.result b/test/box/access_sysview.result
index fd8b142..ed44c24 100644
--- a/test/box/access_sysview.result
+++ b/test/box/access_sysview.result
@@ -230,7 +230,7 @@ box.session.su('guest')
...
#box.space._vspace:select{}
---
-- 24
+- 25
...
#box.space._vindex:select{}
---
diff --git a/test/sql-tap/analyze1.test.lua b/test/sql-tap/analyze1.test.lua
index 959ea5e..38a2838 100755
--- a/test/sql-tap/analyze1.test.lua
+++ b/test/sql-tap/analyze1.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(39)
+test:plan(32)
--!./tcltestrunner.lua
-- 2005 July 22
@@ -16,7 +16,7 @@ test:plan(39)
-- This file implements regression tests for sql library.
-- This file implements tests for the ANALYZE command.
--
-
+test.create_stat_view()
-- Basic sanity checks.
--
@@ -157,10 +157,10 @@ test:do_execsql_test(
INSERT INTO t1 VALUES(1, 1,2);
INSERT INTO t1 VALUES(2, 1,3);
ANALYZE t1;
- SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
+ SELECT idx, stat FROM stat_view ORDER BY idx;
]], {
-- <analyze-3.1>
- "T1", "2 1", "T1I1", "2 2", "T1I2", "2 1", "T1I3", "2 2 1"
+ "T1I1","2 2","T1I2","2 1","T1I3","2 2 1","pk_unnamed_T1_1","2 1"
-- </analyze-3.1>
})
@@ -170,10 +170,10 @@ test:do_execsql_test(
INSERT INTO t1 VALUES(3, 1,4);
INSERT INTO t1 VALUES(4, 1,5);
ANALYZE t1;
- SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
+ SELECT idx, stat FROM stat_view ORDER BY idx;
]], {
-- <analyze-3.2>
- "T1", "4 1", "T1I1", "4 4", "T1I2", "4 1", "T1I3", "4 4 1"
+ "T1I1", "4 4", "T1I2", "4 1", "T1I3", "4 4 1", "pk_unnamed_T1_1", "4 1"
-- </analyze-3.2>
})
@@ -182,10 +182,10 @@ test:do_execsql_test(
[[
INSERT INTO t1 (a,b) VALUES(2,5);
ANALYZE;
- SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
+ SELECT idx, stat FROM stat_view ORDER BY idx;
]], {
-- <analyze-3.3>
- "T1","5 1", "T1I1", "5 3", "T1I2", "5 2", "T1I3", "5 3 1"
+ "T1I1", "5 3", "T1I2", "5 2", "T1I3", "5 3 1", "pk_unnamed_T1_1","5 1"
-- </analyze-3.3>
})
@@ -198,10 +198,10 @@ test:do_execsql_test(
CREATE INDEX t2i2 ON t2(b);
CREATE INDEX t2i3 ON t2(a,b);
ANALYZE;
- SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
+ SELECT idx, stat FROM stat_view 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"
+ "T1I1","5 3","T1I2","5 2","T1I3","5 3 1","T2I1","5 3","T2I2","5 2","T2I3","5 3 1", "pk_unnamed_T1_1","5 1", "pk_unnamed_T2_1","5 1"
-- </analyze-3.4>
})
@@ -210,10 +210,10 @@ test:do_execsql_test(
[[
DROP INDEX t2i3 ON t2;;
ANALYZE t1;
- SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
+ SELECT idx, stat FROM stat_view 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"
+ "T1I1","5 3","T1I2","5 2","T1I3","5 3 1","T2I1","5 3","T2I2","5 2", "pk_unnamed_T1_1","5 1","pk_unnamed_T2_1","5 1"
-- </analyze-3.5>
})
@@ -221,10 +221,10 @@ test:do_execsql_test(
"analyze-3.6",
[[
ANALYZE t2;
- SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
+ SELECT idx, stat FROM stat_view 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"
+ "T1I1","5 3","T1I2","5 2","T1I3","5 3 1","T2I1","5 3","T2I2","5 2", "pk_unnamed_T1_1","5 1","pk_unnamed_T2_1","5 1"
-- </analyze-3.6>
})
@@ -233,10 +233,10 @@ test:do_execsql_test(
[[
DROP INDEX t2i2 ON t2;
ANALYZE t2;
- SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
+ SELECT idx, stat FROM stat_view 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"
+ "T1I1","5 3","T1I2","5 2","T1I3","5 3 1","T2I1","5 3", "pk_unnamed_T1_1","5 1","pk_unnamed_T2_1","5 1"
-- </analyze-3.7>
})
@@ -250,7 +250,7 @@ test:do_execsql_test(
CREATE INDEX t3i3 ON t3(d,b,c,a);
DROP TABLE t1;
DROP TABLE t2;
- SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
+ SELECT idx, stat FROM stat_view ORDER BY idx;
]], {
-- <analyze-3.8>
-- </analyze-3.8>
@@ -260,10 +260,10 @@ test:do_execsql_test(
"analyze-3.9",
[[
ANALYZE;
- SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
+ SELECT idx, stat FROM stat_view ORDER BY idx;
]], {
-- <analyze-3.9>
- "T3","5 1","T3I1","5 3","T3I2","5 3 1 1 1","T3I3","5 5 2 1 1"
+ "T3I1","5 3","T3I2","5 3 1 1 1","T3I3","5 5 2 1 1", "pk_unnamed_T3_1","5 1"
-- </analyze-3.9>
})
@@ -277,7 +277,7 @@ test:do_execsql_test(
-- INSERT INTO [silly " name] (a,b,c) VALUES(1, 2, 3);
-- INSERT INTO [silly " name] (a,b,c) VALUES(4, 5, 6);
-- ANALYZE;
--- SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
+-- SELECT idx, stat FROM stat_view ORDER BY idx;
-- ]], {
-- -- <analyze-3.10>
-- "another foolish ' name", "2 1", "foolish ' name", "2 1 1", "t3i1", "5 3", "t3i2", "5 3 1 1 1", "t3i3", "5 5 2 1 1"
@@ -317,10 +317,10 @@ test:do_execsql_test(
CREATE INDEX t4i2 ON t4(y);
INSERT INTO t4 SELECT id,a,b,c FROM t3;
ANALYZE;
- SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx";
+ SELECT idx, stat FROM stat_view 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"
+ "T3I1", "5 3", "T3I2", "5 3 1 1 1", "T3I3", "5 5 2 1 1", "T4I1", "5 3", "T4I2", "5 2", "pk_unnamed_T3_1", "5 1", "pk_unnamed_T4_1", "5 1"
-- </analyze-4.0>
})
@@ -367,7 +367,7 @@ 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 DISTINCT tbl FROM stat_view ORDER BY 1;
]], {
-- <analyze-5.0>
"T3", "T4"
@@ -377,51 +377,29 @@ test:do_execsql_test(
test:do_execsql_test(
"analyze-5.0.1",
[[
- SELECT DISTINCT "idx" FROM "_sql_stat1" ORDER BY 1;
+ SELECT DISTINCT idx FROM stat_view ORDER BY 1;
]], {
-- <analyze-5.0>
- "T3", "T3I1", "T3I2", "T3I3", "T4", "T4I1", "T4I2"
+ "T3I1", "T3I2", "T3I3", "T4I1", "T4I2", "pk_unnamed_T3_1", "pk_unnamed_T4_1"
-- </analyze-5.0>
})
-stat = "_sql_stat4"
-
-test:do_execsql_test(
- "analyze-5.1",
- string.format([[
- SELECT DISTINCT "idx" FROM "%s" ORDER BY 1;
- ]], stat, stat), {
- -- <analyze-5.1>
- "T3", "T3I1", "T3I2", "T3I3", "T4", "T4I1", "T4I2"
- -- </analyze-5.1>
- })
-
-test:do_execsql_test(
- "analyze-5.1.1",
- string.format([[
- SELECT DISTINCT "tbl" FROM "%s" ORDER BY 1;
- ]], stat, stat), {
- -- <analyze-5.1>
- "T3", "T4"
- -- </analyze-5.1>
- })
-
test:do_execsql_test(
"analyze-5.2",
[[
DROP INDEX t3i2 ON t3;
ANALYZE;
- SELECT DISTINCT "idx" FROM "_sql_stat1" ORDER BY 1;
+ SELECT DISTINCT idx FROM stat_view ORDER BY 1;
]], {
-- <analyze-5.2>
- "T3", "T3I1", "T3I3", "T4", "T4I1", "T4I2"
+ "T3I1", "T3I3", "T4I1", "T4I2", "pk_unnamed_T3_1", "pk_unnamed_T4_1"
-- </analyze-5.2>
})
test:do_execsql_test(
"analyze-5.2.1",
[[
- SELECT DISTINCT "tbl" FROM "_sql_stat1" ORDER BY 1;
+ SELECT DISTINCT tbl FROM stat_view ORDER BY 1;
]], {
-- <analyze-5.2>
"T3", "T4"
@@ -429,67 +407,27 @@ test:do_execsql_test(
})
test:do_execsql_test(
- "analyze-5.3",
- string.format([[
- SELECT DISTINCT "idx" FROM "%s" ORDER BY 1;
- ]], stat, stat), {
- -- <analyze-5.3>
- "T3", "T3I1", "T3I3", "T4", "T4I1", "T4I2"
- -- </analyze-5.3>
- })
-
-test:do_execsql_test(
- "analyze-5.3.1",
- string.format([[
- SELECT DISTINCT "tbl" FROM "%s" ORDER BY 1;
- ]], stat, stat), {
- -- <analyze-5.3>
- "T3", "T4"
- -- </analyze-5.3>
- })
-
-test:do_execsql_test(
"analyze-5.4",
[[
DROP TABLE IF EXISTS t3;
ANALYZE;
- SELECT DISTINCT "idx" FROM "_sql_stat1" ORDER BY 1;
+ SELECT DISTINCT idx FROM stat_view ORDER BY 1;
]], {
-- <analyze-5.4>
- "T4", "T4I1", "T4I2"
+ "T4I1", "T4I2", "pk_unnamed_T4_1"
-- </analyze-5.4>
})
test:do_execsql_test(
"analyze-5.4.1",
[[
- SELECT DISTINCT "tbl" FROM "_sql_stat1" ORDER BY 1;
+ SELECT DISTINCT tbl FROM stat_view ORDER BY 1;
]], {
-- <analyze-5.4>
"T4"
-- </analyze-5.4>
})
-test:do_execsql_test(
- "analyze-5.5",
- string.format([[
- SELECT DISTINCT "idx" FROM "%s" ORDER BY 1;
- ]], stat), {
- -- <analyze-5.5>
- "T4", "T4I1", "T4I2"
- -- </analyze-5.5>
- })
-
-test:do_execsql_test(
- "analyze-5.5.1",
- string.format([[
- SELECT DISTINCT "tbl" FROM "%s" ORDER BY 1;
- ]], stat), {
- -- <analyze-5.5>
- "T4"
- -- </analyze-5.5>
- })
-
test:do_test(
"analyze-6.1.1",
function()
@@ -519,7 +457,7 @@ test:do_test(
test:do_execsql_test(
"analyze-6.1.2",
[[
- SELECT * FROM "_sql_stat1" where "tbl"='T1' and "idx"='I1' LIMIT 1;
+ SELECT tbl, idx, stat FROM stat_view where tbl='T1' and idx='I1' LIMIT 1;
]], {
-- <analyze-6.1.2>
"T1", "I1", "221 221 221 221 2"
@@ -529,23 +467,26 @@ test:do_execsql_test(
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;
+ SELECT tbl, idx, neq, nlt, ndlt FROM stat_view where tbl='T1' and idx='I1';
]], {
-- <analyze-6.1.3>
- "T1", "I1", "221 221 221 1", "0 0 0 10", "0 0 0 10"
+ "T1","I1","221 221 221 1","221 221 221 1","221 221 221 1","221 221 221 1",
+ "221 221 221 1","221 221 221 1","221 221 221 1","221 221 221 1",
+ "221 221 221 1","221 221 221 1","221 221 221 1","221 221 221 1",
+ "221 221 221 1","221 221 221 21","221 221 221 1","221 221 221 1",
+ "221 221 221 1","221 221 221 1","221 221 221 1","221 221 221 1",
+ "221 221 221 1","221 221 221 1","221 221 221 1","221 221 221 1",
+ "0 0 0 10","0 0 0 24","0 0 0 25","0 0 0 32","0 0 0 35","0 0 0 44",
+ "0 0 0 49","0 0 0 56","0 0 0 62","0 0 0 66","0 0 0 74","0 0 0 80",
+ "0 0 0 99","0 0 0 101","0 0 0 124","0 0 0 149","0 0 0 157","0 0 0 174",
+ "0 0 0 176","0 0 0 185","0 0 0 192","0 0 0 199","0 0 0 210","0 0 0 219",
+ "0 0 0 10","0 0 0 24","0 0 0 25","0 0 0 32","0 0 0 35","0 0 0 44",
+ "0 0 0 49","0 0 0 56","0 0 0 62","0 0 0 66","0 0 0 74","0 0 0 80",
+ "0 0 0 99","0 0 0 101","0 0 0 104","0 0 0 129","0 0 0 137","0 0 0 154",
+ "0 0 0 156","0 0 0 165","0 0 0 172","0 0 0 179","0 0 0 190","0 0 0 199"
-- </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;
- ]], {
- -- <analyze-6.1.4>
- "T1", "I1", "221 221 221 1", "0 0 0 99", "0 0 0 99"
- -- </analyze-6.1.4>
-})
-
-- This test show that index with 1000 identical index values and
-- 25 distinct ones gives max number of samples.
test:do_test(
@@ -556,12 +497,12 @@ test:do_test(
for i = 0, 999 do test:execsql("INSERT INTO t7 VALUES("..i..", 0) ") end
for i = 1, 24 do test:execsql("INSERT INTO t7 VALUES(".. i + 999 .. ", ".. i ..") ") end
test:execsql("ANALYZE;")
- return test:execsql([[SELECT count(*) FROM "_sql_stat4" WHERE "idx" = 'I7';]])
- end, {
+ return #test:execsql([[SELECT neq FROM stat_view WHERE idx = 'I7';]])
+ end,
-- <analyze-6.1.4>
24
-- </analyze-6.1.4>
-})
+)
-- # This test corrupts the database file so it must be the last test
-- # in the series.
diff --git a/test/sql-tap/analyze3.test.lua b/test/sql-tap/analyze3.test.lua
index 1396287..0b779f6 100755
--- a/test/sql-tap/analyze3.test.lua
+++ b/test/sql-tap/analyze3.test.lua
@@ -93,7 +93,7 @@ test:do_test(
COMMIT;
ANALYZE;
]])
- return test:execsql([[ SELECT count(*)>0 FROM "_sql_stat4"; ]])
+ return test:execsql([[ SELECT count(*)>0 FROM "_sql_stat"; ]])
end, {
diff --git a/test/sql-tap/analyze4.test.lua b/test/sql-tap/analyze4.test.lua
index 7d7498f..1e8cd52 100755
--- a/test/sql-tap/analyze4.test.lua
+++ b/test/sql-tap/analyze4.test.lua
@@ -24,6 +24,8 @@ test:plan(4)
-- ["set","testdir",[["file","dirname",["argv0"]]]]
-- ["source",[["testdir"],"\/tester.tcl"]]
+test.create_stat_view()
+
test:do_test(
"analyze4-1.0",
function()
@@ -54,10 +56,10 @@ test:do_test(
--
test:do_execsql_test(
"analyze4-1.1",
- [[ SELECT "idx", "stat" FROM "_sql_stat1" WHERE "tbl"='T1' ORDER BY "idx"; ]],
+ [[ SELECT idx, stat FROM stat_view WHERE tbl='T1' ORDER BY idx; ]],
{
-- <analyze4-1.1>
- "T1","128 1", "T1A", "128 1", "T1B", "128 128"
+ "T1A", "128 1", "T1B", "128 128", "pk_unnamed_T1_1","128 1"
-- </analyze4-1.1>
})
@@ -73,11 +75,11 @@ test:do_test(
-- 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 stat_view WHERE tbl='T1' ORDER BY idx;
]])
end, {
-- <analyze4-1.2>
- "T1", "128 1", "T1A", "128 1", "T1B", "128 64"
+ "T1A", "128 1", "T1B", "128 64", "pk_unnamed_T1_1","128 1"
-- </analyze4-1.2>
})
@@ -111,11 +113,11 @@ test:do_execsql_test(
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";
+ SELECT idx, stat FROM stat_view WHERE tbl='T1' ORDER BY idx;
]]
, {
-- <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"
+ "T1A", "128 1", "T1B", "128 128", "T1BCD", "128 128 4 2", "T1CBD", "128 4 4 2", "T1CDB", "128 4 2 2", "pk_unnamed_T1_1","128 1"
-- </analyze4-1.3>
})
diff --git a/test/sql-tap/analyze5.test.lua b/test/sql-tap/analyze5.test.lua
index f2583d5..8de7b4e 100755
--- a/test/sql-tap/analyze5.test.lua
+++ b/test/sql-tap/analyze5.test.lua
@@ -21,6 +21,8 @@ json = require("json")
-- with many repeated values and only a few distinct values.
--
+test.create_stat_view()
+
testprefix = "analyze5"
local function eqp(sql)
return test:execsql("EXPLAIN QUERY PLAN"..sql)
@@ -115,13 +117,12 @@ 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([[
+ SELECT sample FROM stat_view where idx = 'T1U' ORDER BY nlt
+ ]])
end, {
-- <analyze5-1.0>
- "alpha", "bravo", "charlie", "delta"
+ "alpha","bravo","charlie","delta"
-- </analyze5-1.0>
})
@@ -141,13 +142,19 @@ test:do_test(
-- -- </analyze5-1.1>
-- })
-test:do_test(
- "analyze5-1.2",
- function()
- return test:execsql([[SELECT "idx", count(*) FROM "_sql_stat4" GROUP BY 1 ORDER BY 1]])
- end, {
+test:do_execsql_test(
+ "analyze5-1.2", [[
+ SELECT idx, sample FROM stat_view;
+ ]], {
-- <analyze5-1.2>
- "T1",24,"T1T",4,"T1U",4,"T1V",1,"T1W",4,"T1X",4,"T1Y",2,"T1Z",4
+ "pk_unnamed_T1_1",112,224,276,336,410,448,499,525,529,530,560,593,594,623,672,701,772,784,798,890,896,925,926,969,
+ "T1T","0.5","1.5","2.5","3.5",
+ "T1U","alpha","bravo","charlie","delta",
+ "T1V","NULL",
+ "T1W","0","1","2","NULL",
+ "T1X","1","2","3","NULL",
+ "T1Y","0","1",
+ "T1Z",0,1,2,3
-- </analyze5-1.2>
})
diff --git a/test/sql-tap/analyze9.test.lua b/test/sql-tap/analyze9.test.lua
index 3969c45..5ba53b8 100755
--- a/test/sql-tap/analyze9.test.lua
+++ b/test/sql-tap/analyze9.test.lua
@@ -1,6 +1,7 @@
#!/usr/bin/env tarantool
+msgpack = require('msgpack')
test = require("sqltester")
-test:plan(121)
+test:plan(120)
testprefix = "analyze9"
@@ -20,6 +21,8 @@ testprefix = "analyze9"
-- functionality is working.
--
+test.create_stat_view()
+
-- SQL Analyze is working correctly only with memtx now.
test:do_execsql_test(
1.0,
@@ -47,21 +50,30 @@ test:do_execsql_test(
})
msgpack_decode_sample = function(txt)
- msgpack = require('msgpack')
- local i = 1
- local decoded_str = ''
- while msgpack.decode(txt)[i] ~= nil do
- if i == 1 then
- decoded_str = msgpack.decode(txt)[i]
- else
- decoded_str = decoded_str.." "..msgpack.decode(txt)[i]
+ local arr = msgpack.decode(txt)
+ if type(arr) ~= 'table' then
+ return tostring(arr)
+ end
+ local result = {}
+ local function flatten(arr)
+ for _, v in ipairs(arr) do
+ if type(v) == "table" then
+ flatten(v)
+ else
+ table.insert(result, v)
+ end
end
- i = i+1
end
- if type(decoded_str) == "number" then
- return tostring(decoded_str)
+ flatten(arr)
+ local str = ''
+ for k, v in pairs(result) do
+ if k == 1 then
+ str = tostring(v)
+ else
+ str = str .. ' ' .. tostring(v)
+ end
end
- return decoded_str
+ return str
end
box.internal.sql_create_function("msgpack_decode_sample", "TEXT", msgpack_decode_sample)
@@ -69,25 +81,30 @@ box.internal.sql_create_function("msgpack_decode_sample", "TEXT", msgpack_decode
test:do_execsql_test(
1.2,
[[
- SELECT "tbl","idx","neq","nlt","ndlt",msgpack_decode_sample("sample") FROM "_sql_stat4" where "idx" = 'I1';
+ SELECT tbl,idx,neq,nlt,ndlt,sample FROM stat_view where idx = 'I1';
]], {
-- <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)"
+ "T1",
+ "I1",
+ "1 1","1 1","1 1","1 1","1 1",
+ "0 0","1 1","2 2","3 3","4 4",
+ "0 0","1 1","2 2","3 3","4 4",
+ "(0)","(0)","(1)","(1)","(2)","(2)","(3)","(3)","(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';
-
+ SELECT tbl,idx,neq,nlt,ndlt,sample FROM stat_view where idx = 'pk_unnamed_T1_1';
]], {
-- <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)'
+ "T1",
+ "pk_unnamed_T1_1",
+ "1","1","1","1","1",
+ "0","1","2","3","4",
+ "0","1","2","3","4",
+ "(0)","(1)","(2)","(3)","(4)"
-- </1.3>
})
@@ -104,10 +121,10 @@ test:do_execsql_test(
INSERT INTO t1 VALUES('text', 12);
CREATE INDEX i1 ON t1(a, b);
ANALYZE;
- SELECT msgpack_decode_sample("sample") FROM "_sql_stat4";
+ SELECT sample FROM stat_view;
]], {
-- <2.1>
- "text 12","some text 14","text","some text"
+ "some text","text","some text",14,"text",12
-- </2.1>
})
@@ -185,12 +202,18 @@ test:do_execsql_test(
-- The first element in the "nEq" list of all samples should therefore be 10.
--
-test:do_execsql_test(
+test:do_test(
"3.3.2",
- [[
- ANALYZE;
- SELECT lrange("neq", 1, 1) FROM "_sql_stat4" WHERE "idx" = 'I2';
- ]], generate_tens_str(24))
+ function()
+ result = test:execsql( [[
+ ANALYZE;
+ SELECT neq FROM stat_view WHERE idx = 'I2';
+ ]])
+ for k,v in pairs(result) do
+ result[k] = lrange(v, 1, 1)
+ end
+ return result;
+ end, generate_tens_str(24))
---------------------------------------------------------------------------
--
@@ -252,7 +275,7 @@ test:do_test(
function()
insert_filler_rows_n(0, 10, 19)
insert_filler_rows_n(20, 1, 100)
- return test:execsql([[
+ return #test:execsql([[
INSERT INTO t1(id, c, b, a) VALUES(null, 200, 1, 'a');
INSERT INTO t1(id, c, b, a) VALUES(null, 200, 1, 'b');
INSERT INTO t1(id, c, b, a) VALUES(null, 200, 1, 'c');
@@ -264,14 +287,14 @@ test:do_test(
INSERT INTO t1(id, c, b, a) VALUES(null, 201, 4, 'h');
ANALYZE;
- SELECT count(*) FROM "_sql_stat4";
+ SELECT neq FROM stat_view;
]])
- end, {
+ end,
-- <4.1>
48
-- </4.1>
- })
+ )
test:do_execsql_test(
4.2,
@@ -286,32 +309,34 @@ test:do_execsql_test(
test:do_execsql_test(
4.3,
[[
- 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;
+ SELECT neq, nlt, ndlt, sample FROM stat_view WHERE idx = 'I1';
]], {
-- <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",
- "10 10 10","50 50 50","5 5 5","5 5 5","10 10 10","60 60 60","6 6 6","6 6 6","10 10 10","70 70 70",
- "7 7 7","7 7 7","10 10 10","80 80 80","8 8 8","8 8 8","10 10 10","90 90 90","9 9 9","9 9 9",
- "10 10 10","100 100 100","10 10 10","10 10 10","10 10 10","110 110 110","11 11 11","11 11 11",
- "10 10 10","120 120 120","12 12 12","12 12 12","10 10 10","130 130 130","13 13 13","13 13 13",
- "10 10 10","140 140 140","14 14 14","14 14 14","10 10 10","150 150 150","15 15 15","15 15 15"
+ -- neq
+ "10 10 10","10 10 10","10 10 10","10 10 10","10 10 10","10 10 10",
+ "10 10 10","10 10 10","10 10 10","10 10 10","10 10 10","10 10 10",
+ "10 10 10","10 10 10","10 10 10","10 10 10","10 10 10","10 10 10",
+ "10 10 10","1 1 1","1 1 1","1 1 1","5 3 1","2 1 1",
+ -- ntl
+ "0 0 0","10 10 10","20 20 20","30 30 30","40 40 40","50 50 50",
+ "60 60 60","70 70 70","80 80 80","90 90 90","100 100 100","110 110 110",
+ "120 120 120","130 130 130","140 140 140","150 150 150",
+ "160 160 160","170 170 170","180 180 180","203 203 203",
+ "237 237 237","271 271 271","290 290 291","295 296 296",
+ -- ndlt
+ "0 0 0","1 1 1","2 2 2","3 3 3","4 4 4","5 5 5","6 6 6","7 7 7","8 8 8",
+ "9 9 9","10 10 10","11 11 11","12 12 12","13 13 13","14 14 14",
+ "15 15 15","16 16 16","17 17 17","18 18 18","32 32 32","66 66 66",
+ "100 100 100","119 119 120","120 122 125",
+ -- sample
+ 0,0,"0",1,1,"1",2,2,"2",3,3,"3",4,4,"4",5,5,"5",6,6,"6",7,7,"7",8,8,"8",
+ 9,9,"9",10,10,"10",11,11,"11",12,12,"12",13,13,"13",14,14,"14",
+ 15,15,"15",16,16,"16",17,17,"17",18,18,"18",33,33,"33",67,67,"67",
+ 101,101,"101",200,1,"b",201,4,"h"
-- </4.3>
})
test:do_execsql_test(
- 4.4,
- [[
- 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;
- ]], {
- -- <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>
- })
-
-test:do_execsql_test(
4.5,
[[
SELECT count(DISTINCT c) FROM t1 WHERE c<201
@@ -331,16 +356,26 @@ test:do_execsql_test(
-- </4.6>
})
--- Check that the perioidic samples are present.
-test:do_execsql_test(
+-- Check that the periodic samples are present.
+test:do_test(
4.7,
- [[
- SELECT count(*) FROM "_sql_stat4" WHERE lrange(msgpack_decode_sample("sample"), 1, 1) IN ('34', '68', '102', '136', '170', '204', '238', '272');
- ]], {
- -- <4.7>
- 8
- -- </4.7>
- })
+ function()
+ periodic = {34, 68, 102, 136, 170, 204, 238, 272}
+ samples = test:execsql("SELECT sample FROM stat_view WHERE idx = 'pk_unnamed_T1_1';")
+ result = {}
+ for _,v in pairs(periodic) do
+ for _,u in pairs(samples) do
+ if (v == u) then
+ table.insert(result, u)
+ end
+ end
+ end
+ return #result;
+ end,
+ -- <4.7>
+ 8
+ -- </4.7>
+ )
-- reset_db()
test:do_test(
@@ -354,24 +389,24 @@ test:do_test(
for i = 0, 9999, 10 do
test:execsql(" INSERT INTO t1 VALUES('x', "..i..") ")
end
- return test:execsql([[
+ return #test:execsql([[
ANALYZE;
- SELECT count(*) FROM "_sql_stat4";
+ SELECT neq FROM stat_view;
]])
- end, {
+ end,
-- <4.8>
25
-- </4.8>
- })
+ )
test:do_execsql_test(
4.9,
[[
- SELECT msgpack_decode_sample("sample") FROM "_sql_stat4";
+ SELECT sample FROM stat_view;
]], {
-- <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>
})
@@ -417,13 +452,13 @@ local get_pk = function (space, record)
end
local inject_stat_error_func = function (space_name)
- local space = box.space[space_name]
- local record = space:select({"T1", "I1", nil}, {limit = 1})[1]
- space:delete(get_pk(space, record))
+ local space_id = box.space[space_name].id
+ local record = box.space._sql_stat:select({space_id, 1})[1]
+ box.space._sql_stat:delete({space_id, 1})
local record_new = {}
for i = 1,#record-1 do record_new[i] = record[i] end
- record_new[#record] = ''
- space:insert(record_new)
+ record_new[#record] = record[#record - 1]
+ box.space._sql_stat:insert(record_new)
return 0
end
@@ -441,7 +476,7 @@ test:do_execsql_test(
INSERT INTO t1 VALUES(null, 4, 4);
INSERT INTO t1 VALUES(null, 5, 5);
ANALYZE;
- SELECT inject_stat_error('_sql_stat4');
+ SELECT inject_stat_error('T1');
ANALYZE;
]])
@@ -1237,12 +1272,12 @@ 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'; ]])
- end, {
+ return #test:execsql([[ SELECT neq FROM stat_view WHERE idx = 'I1'; ]])
+ end,
-- <18.1>
9
-- </18.1>
- })
+ )
---------------------------------------------------------------------------
@@ -1283,11 +1318,17 @@ 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))
- end, {
+ local result = box.sql.execute([[SELECT sample FROM stat_view WHERE idx = 'I1']])[1][1]
+ local counter = 0
+ for k,v in pairs(result) do
+ if v[1] == i then
+ counter = counter + 1;
+ end
+ end
+ return counter
+ end,
1
- })
+ )
end
---------------------------------------------------------------------------
diff --git a/test/sql-tap/lua/sqltester.lua b/test/sql-tap/lua/sqltester.lua
index 8aac64c..24b9c00 100644
--- a/test/sql-tap/lua/sqltester.lua
+++ b/test/sql-tap/lua/sqltester.lua
@@ -348,6 +348,33 @@ local function db(self, cmd, ...)
end
test.db = db
+-- Create a view where tables and indexes names will be shown
+-- instead of their ID.
+local function create_stat_view(name)
+ if not name then
+ name = "stat_view"
+ end
+ box.sql.execute(string.format([[
+ CREATE VIEW
+ %s
+ AS SELECT
+ "_space"."name" AS tbl,
+ "_index"."name" AS idx,
+ "_sql_stat"."stat" as stat,
+ "_sql_stat"."neq" as neq,
+ "_sql_stat"."nlt" as nlt,
+ "_sql_stat"."ndlt" as ndlt,
+ "_sql_stat"."sample" as sample
+ FROM
+ "_sql_stat" LEFT JOIN
+ "_space" ON "_sql_stat"."space_id" = "_space"."id",
+ "_index" ON "_sql_stat"."space_id" = "_index"."id" AND
+ "_sql_stat"."index_id" = "_index"."iid"
+ ;
+ ]], name))
+end
+test.create_stat_view = create_stat_view
+
-- returns first occurance of seed in input or -1
local function lsearch(self, input, seed)
local index = 1
diff --git a/test/sql/sql-statN-index-drop.result b/test/sql/sql-statN-index-drop.result
index 7605951..2e574e9 100644
--- a/test/sql/sql-statN-index-drop.result
+++ b/test/sql/sql-statN-index-drop.result
@@ -31,36 +31,23 @@ box.sql.execute("ANALYZE;")
---
...
-- Checking the data.
-box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+box.sql.execute([[SELECT "index_id", "stat", "neq", "nlt", "ndlt", "sample" FROM "_sql_stat";]])
---
-- - ['T1', 'I1', '1', '0', '0', !!binary kQI=]
- - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
- - ['T2', 'I1', '1', '0', '0', !!binary kQI=]
- - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
-...
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
----
-- - ['T1', 'I1', '1 1']
- - ['T1', 'T1', '1 1']
- - ['T2', 'I1', '1 1']
- - ['T2', 'T2', '1 1']
+- - [0, '1 1', ['1'], ['0'], ['0'], [[1]]]
+ - [1, '1 1', ['1'], ['0'], ['0'], [[2]]]
+ - [0, '1 1', ['1'], ['0'], ['0'], [[1]]]
+ - [1, '1 1', ['1'], ['0'], ['0'], [[2]]]
...
-- Dropping an index.
box.sql.execute("DROP INDEX i1 ON t1;")
---
...
-- Checking the DROP INDEX results.
-box.sql.execute("SELECT * FROM \"_sql_stat4\";")
----
-- - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
- - ['T2', 'I1', '1', '0', '0', !!binary kQI=]
- - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
-...
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+box.sql.execute([[SELECT "index_id", "stat", "neq", "nlt", "ndlt", "sample" FROM "_sql_stat";]])
---
-- - ['T1', 'T1', '1 1']
- - ['T2', 'I1', '1 1']
- - ['T2', 'T2', '1 1']
+- - [0, '1 1', ['1'], ['0'], ['0'], [[1]]]
+ - [0, '1 1', ['1'], ['0'], ['0'], [[1]]]
+ - [1, '1 1', ['1'], ['0'], ['0'], [[2]]]
...
--Cleaning up.
box.sql.execute("DROP TABLE t1;")
@@ -93,36 +80,23 @@ box.sql.execute("ANALYZE;")
---
...
-- Checking the data.
-box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+box.sql.execute([[SELECT "index_id", "stat", "neq", "nlt", "ndlt", "sample" FROM "_sql_stat";]])
---
-- - ['T1', 'I1', '1', '0', '0', !!binary kQI=]
- - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
- - ['T2', 'I1', '1', '0', '0', !!binary kQI=]
- - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
-...
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
----
-- - ['T1', 'I1', '1 1']
- - ['T1', 'T1', '1 1']
- - ['T2', 'I1', '1 1']
- - ['T2', 'T2', '1 1']
+- - [0, '1 1', ['1'], ['0'], ['0'], [[1]]]
+ - [1, '1 1', ['1'], ['0'], ['0'], [[2]]]
+ - [0, '1 1', ['1'], ['0'], ['0'], [[1]]]
+ - [1, '1 1', ['1'], ['0'], ['0'], [[2]]]
...
-- Dropping an index.
box.sql.execute("DROP INDEX i1 ON t2;")
---
...
-- Checking the DROP INDEX results.
-box.sql.execute("SELECT * FROM \"_sql_stat4\";")
----
-- - ['T1', 'I1', '1', '0', '0', !!binary kQI=]
- - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
- - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
-...
-box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+box.sql.execute([[SELECT "index_id", "stat", "neq", "nlt", "ndlt", "sample" FROM "_sql_stat";]])
---
-- - ['T1', 'I1', '1 1']
- - ['T1', 'T1', '1 1']
- - ['T2', 'T2', '1 1']
+- - [0, '1 1', ['1'], ['0'], ['0'], [[1]]]
+ - [1, '1 1', ['1'], ['0'], ['0'], [[2]]]
+ - [0, '1 1', ['1'], ['0'], ['0'], [[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 35f2291..c029518 100644
--- a/test/sql/sql-statN-index-drop.test.lua
+++ b/test/sql/sql-statN-index-drop.test.lua
@@ -14,15 +14,13 @@ 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 "index_id", "stat", "neq", "nlt", "ndlt", "sample" FROM "_sql_stat";]])
-- 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 "index_id", "stat", "neq", "nlt", "ndlt", "sample" FROM "_sql_stat";]])
--Cleaning up.
box.sql.execute("DROP TABLE t1;")
@@ -41,15 +39,13 @@ 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 "index_id", "stat", "neq", "nlt", "ndlt", "sample" FROM "_sql_stat";]])
-- 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 "index_id", "stat", "neq", "nlt", "ndlt", "sample" FROM "_sql_stat";]])
--Cleaning up.
box.sql.execute("DROP TABLE t1;")
diff --git a/test/wal_off/alter.result b/test/wal_off/alter.result
index b4c6a92..d62cc8e 100644
--- a/test/wal_off/alter.result
+++ b/test/wal_off/alter.result
@@ -28,7 +28,7 @@ end;
...
#spaces;
---
-- 65509
+- 65508
...
-- cleanup
for k, v in pairs(spaces) do
--
2.7.4
More information about the Tarantool-patches
mailing list