From: Roman Khabibov <roman.habibov@tarantool.org> To: tarantool-patches@freelists.org Cc: "n. pettik" <korablev@tarantool.org> Subject: [tarantool-patches] Re: [PATCH] sql: store statistics in statN as an array of integers Date: Fri, 1 Mar 2019 13:33:25 +0300 [thread overview] Message-ID: <FF59F166-848D-45D8-AA93-DB91E949C3E0@tarantool.org> (raw) In-Reply-To: <AAE6D883-9629-43F6-AD82-3C475C6FA936@tarantool.org> Hi! Thanks for review. > On Feb 12, 2019, at 2:53 AM, n.pettik <korablev@tarantool.org> wrote: > > >> diff --git a/src/box/sql/analyze.c b/src/box/sql/analyze.c >> index 51c63fa7a..a7aaf665d 100644 >> --- a/src/box/sql/analyze.c >> +++ b/src/box/sql/analyze.c >> @@ -664,27 +664,35 @@ statGet(sqlite3_context * context, int argc, sqlite3_value ** argv) >> * >> * I = (K+D-1)/D >> */ >> - char *z; >> int i; >> >> - char *zRet = sqlite3MallocZero((p->nKeyCol + 1) * 25); >> + size_t size = mp_sizeof_array(p->nKeyCol + 1); >> + size += mp_sizeof_uint(p->nRow); >> + for (i = 0; i < p->nKeyCol; ++i) { >> + uint64_t nDistinct = p->current.anDLt[i] + 1; >> + uint64_t iVal = (p->nRow + nDistinct - 1) / nDistinct; > > Use Tarantool-like names: distinct_count etc Done. > >> + size += mp_sizeof_uint(iVal); >> + } >> + char *zRet = sqlite3MallocZero(size); >> if (zRet == 0) { >> sqlite3_result_error_nomem(context); >> return; >> } >> - >> - sqlite3_snprintf(24, zRet, "%llu", (u64) p->nRow); >> - z = zRet + sqlite3Strlen30(zRet); >> - for (i = 0; i < p->nKeyCol; i++) { >> - u64 nDistinct = p->current.anDLt[i] + 1; >> - u64 iVal = (p->nRow + nDistinct - 1) / nDistinct; >> - sqlite3_snprintf(24, z, " %llu", iVal); >> - z += sqlite3Strlen30(z); >> + char *z = zRet; >> + z = mp_encode_array(z, p->nKeyCol + 1); >> + z = mp_encode_uint(z, p->nRow); >> + for (i = 0; i < p->nKeyCol; ++i) { >> + uint64_t nDistinct = p->current.anDLt[i] + 1; >> + uint64_t iVal = (p->nRow + nDistinct - 1) / nDistinct; >> + z = mp_encode_uint(z, iVal); >> assert(p->current.anEq[i]); >> } >> - assert(z[0] == '\0' && z > zRet); >> + const char *b = zRet; >> + int r = mp_check(&b, z); >> + assert(!r); > > Why not assert(mp_check(&b, z) == 0); ? > >> + assert(zRet != z); >> >> - sqlite3_result_text(context, zRet, -1, sqlite3_free); >> + sqlite3_result_msgpack(context, zRet, size, sqlite3_free); >> } else if (eCall == STAT_GET_KEY) { >> if (p->iGet < 0) { >> samplePushPrevious(p, 0); >> @@ -713,19 +721,28 @@ statGet(sqlite3_context * context, int argc, sqlite3_value ** argv) >> } >> } >> >> - char *zRet = sqlite3MallocZero(p->nCol * 25); >> + int i; >> + >> + size_t size = mp_sizeof_array(p->nCol); >> + for (i = 0; i < p->nCol; ++i) { >> + size += mp_sizeof_uint(aCnt[i]); >> + } >> + >> + char *zRet = sqlite3MallocZero(size); >> if (zRet == 0) { >> sqlite3_result_error_nomem(context); >> } else { >> - int i; >> char *z = zRet; >> + z = mp_encode_array(z, p->nCol); >> for (i = 0; i < p->nCol; i++) { >> - sqlite3_snprintf(24, z, "%llu ", (u64) aCnt[i]); >> - z += sqlite3Strlen30(z); >> + z = mp_encode_uint(z, aCnt[i]); >> } >> - assert(z[0] == '\0' && z > zRet); >> - z[-1] = '\0'; >> - sqlite3_result_text(context, zRet, -1, sqlite3_free); >> + const char *b = zRet; >> + int r = mp_check(&b, z); >> + assert(!r); > > The same: assert(mp_check() == 0); Done. + const char *b = z_ret; + assert(mp_check(&b, z) == 0); + (void) b; + assert(z_ret != z) > A bit broken indentation; put bracer at the next line. > >> + const char *z = stat_array; >> + mp_decode_array(&z); > > mp_decode_array returns number of elements in array. > It is like rude to throw away that information. Yes. Now I use it. >> if (z == NULL) >> - z = ""; >> - for (int i = 0; *z && i < stat_size; i++) { >> - tRowcnt v = 0; >> - int c; >> - while ((c = z[0]) >= '0' && c <= '9') { >> - v = v * 10 + c - '0'; >> - z++; >> - } >> + return; >> + for (int i = 0; i < stat_size; i++) { >> + tRowcnt v = (tRowcnt) mp_decode_uint(&z); >> if (stat_exact != NULL) >> stat_exact[i] = v; >> if (stat_log != NULL) >> stat_log[i] = sqlite3LogEst(v); >> - if (*z == ' ') >> - z++; >> } >> + if (!offset) >> + UNUSED_PARAMETER(offset); >> + else >> + offset = &z; > > I don’t understand why you can’t do this: > > if (offset != NULL) > offset = &z; > >> @@ -1268,22 +1283,23 @@ 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, >> - stat->tuple_log_est); >> + const char *offset; >> + decode_stat_array(argv[2], column_count, stat->tuple_stat1, >> + stat->tuple_log_est, &offset); > > Again indentation is broken a bit. Please, make space and tabs > visible in your code redactor. > >> stat->is_unordered = false; >> stat->skip_scan_enabled = true; >> - char *z = argv[2]; >> - /* Position ptr at the end of stat string. */ >> - for (; *z == ' ' || (*z >= '0' && *z <= '9'); ++z); >> - while (z[0]) { >> - if (sql_strlike_cs("unordered%", z, '[') == 0) >> + const char *z = argv[2]; >> + uint32_t keyword_count = mp_decode_array(&z) - column_count; > > Okay, there is some mess. Lets move this part to decode_stat_array. > There you already decoded that array, know its length and so on. > Instead of using offset output parameter, simply add one bit mask or two > bool output params. Done. +#define KW_UNORDERED 0x01 +#define KW_NOSKIPSCAN 0x02 /** - * The first argument points to a nul-terminated string - * containing a list of space separated integers. Load - * the first stat_size of these into the output arrays. + * The first argument points to a msg_pack array + * containing a list of integers. Load the first + * stat_size of these into the output arrays. + * keywords_info needed for keywords encoding/decoding. * - * @param stat_string String containing array of integers. - * @param stat_size Size of output arrays. + * @param stat_array MP_ARRAY containing array of integers. + * @param stat_size Size of input array (not counting the keywords). * @param[out] stat_exact Decoded array of statistics. * @param[out] stat_log Decoded array of stat logariphms. + * @param[out] keywords_info Bitmask of having keywords in the field. */ static void -decode_stat_string(const char *stat_string, int stat_size, tRowcnt *stat_exact, - LogEst *stat_log) { - const char *z = stat_string; +decode_stat_array(const char *stat_array, int stat_size, tRowcnt *stat_exact, + LogEst *stat_log, uint8_t keywords_info) { + const char *z = stat_array; + uint32_t array_size = mp_decode_array(&z); if (z == NULL) - z = ""; - for (int i = 0; *z && i < stat_size; i++) { - tRowcnt v = 0; - int c; - while ((c = z[0]) >= '0' && c <= '9') { - v = v * 10 + c - '0'; - z++; - } + return; + for (int i = 0; i < stat_size; i++) { + tRowcnt v = (tRowcnt) mp_decode_uint(&z); if (stat_exact != NULL) stat_exact[i] = v; if (stat_log != NULL) stat_log[i] = sqlLogEst(v); - if (*z == ' ') - z++; + } + + /* Keywords processing if needed. */ + if (keywords_info != 0) { + uint32_t keyword_count = array_size - stat_size; + if (keyword_count > 0) { + while (keyword_count) { + const char *sval; + uint32_t sval_len; + sval = mp_decode_str(&z, &sval_len); + if (!sql_stricmp(sval, "unordered")) { + keywords_info |= KW_UNORDERED; + } else if (!sql_stricmp(sval, "noskipscan")) { + keywords_info |= KW_NOSKIPSCAN; + } + keyword_count--; + } + } } } > Ok, I see below some commented tests, so I guess they fail > with segfaults or assertions. Please, dig into the problem. > >> -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; >> - ]], { >> - -- <analyze-6.1.3> >> - "T1", "I1", "221 221 221 1", "0 0 0 10", "0 0 0 10" >> - -- </analyze-6.1.3> >> -}) >> +-- 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; >> +-- ]], { >> +-- -- <analyze-6.1.3> >> +-- "T1", "I1", "221 221 221 1", "0 0 0 10", "0 0 0 10" >> +-- -- </analyze-6.1.3> >> +-- }) The problem wasn’t related to segfaults or assertions. I remade some SQL tests with ORDER BY to Lua style. But its still don’t work in SQL. commit 42d9c098388ee0d650287a7d5cd4c0b616e03c9c Author: Roman Khabibov <roman.habibov@tarantool.org> Date: Mon Jan 21 17:31:52 2019 +0300 sql: store statistics in statN as an array of integers Encode statN stat fields as msgpack array instead of string. Closes #3372 diff --git a/src/box/bootstrap.snap b/src/box/bootstrap.snap index 0bb446fb6..ff047f112 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 70cfb4f2e..ab46142ba 100644 --- a/src/box/lua/upgrade.lua +++ b/src/box/lua/upgrade.lua @@ -614,6 +614,30 @@ local function upgrade_to_2_1_0() upgrade_priv_to_2_1_0() end +-------------------------------------------------------------------------------- +-- Tarantool 2.1.1 +-------------------------------------------------------------------------------- + +local function upgrade_sql_stat_to_2_1_1() + local _sql_stat1 = box.space[box.schema.SQL_STAT1_ID] + local _sql_stat4 = box.space[box.schema.SQL_STAT4_ID] + + local format = _sql_stat1:format() + format[3].type = 'array' + _sql_stat1:format(format) + + + format = _sql_stat4:format() + format[3].type = 'array' + format[4].type = 'array' + format[5].type = 'array' + _sql_stat4:format(format) +end + +local function upgrade_to_2_1_1() + upgrade_sql_stat_to_2_1_1() +end + local function get_version() local version = box.space._schema:get{'version'} if version == nil then @@ -641,7 +665,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, 1), func = upgrade_to_2_1_1, auto = true} } for _, handler in ipairs(handlers) do diff --git a/src/box/sql/analyze.c b/src/box/sql/analyze.c index 8c83288e6..cfc987693 100644 --- a/src/box/sql/analyze.c +++ b/src/box/sql/analyze.c @@ -645,14 +645,14 @@ statGet(sql_context * context, int argc, sql_value ** argv) /* Return the value to store in the "stat" column of the _sql_stat1 * table for this index. * - * The value is a string composed of a list of integers describing + * The value is a msg pack array composed of a list of integers describing * the index. The first integer in the list is the total number of * entries in the index. There is one additional integer in the list * for each indexed column. This additional integer is an estimate of * the number of rows matched by a stabbing query on the index using * a key with the corresponding number of fields. In other words, * if the index is on columns (a,b) and the _sql_stat1 value is - * "100 10 2", then sql estimates that: + * [100, 10, 2], then sql estimates that: * * * the index contains 100 rows, * * "WHERE a=?" matches 10 rows, and @@ -663,27 +663,37 @@ statGet(sql_context * context, int argc, sql_value ** argv) * * I = (K+D-1)/D */ - char *z; int i; - char *zRet = sqlMallocZero((p->nKeyCol + 1) * 25); - if (zRet == 0) { + size_t size = mp_sizeof_array(p->nKeyCol + 1); + size += mp_sizeof_uint(p->nRow); + for (i = 0; i < p->nKeyCol; ++i) { + uint64_t dist_count = p->current.anDLt[i] + 1; + uint64_t val = (p->nRow + dist_count - 1) / dist_count; + size += mp_sizeof_uint(val); + } + char *z_ret = sqlMallocZero(size); + if (z_ret == 0) { sql_result_error_nomem(context); return; } - - sql_snprintf(24, zRet, "%llu", (u64) p->nRow); - z = zRet + sqlStrlen30(zRet); - for (i = 0; i < p->nKeyCol; i++) { - u64 nDistinct = p->current.anDLt[i] + 1; - u64 iVal = (p->nRow + nDistinct - 1) / nDistinct; - sql_snprintf(24, z, " %llu", iVal); - z += sqlStrlen30(z); + char *z = z_ret; + z = mp_encode_array(z, p->nKeyCol + 1); + z = mp_encode_uint(z, p->nRow); + for (i = 0; i < p->nKeyCol; ++i) { + uint64_t dist_count = p->current.anDLt[i] + 1; + uint64_t val = (p->nRow + dist_count - 1) / dist_count; + z = mp_encode_uint(z, val); assert(p->current.anEq[i]); } - assert(z[0] == '\0' && z > zRet); - - sql_result_text(context, zRet, -1, sql_free); + const char *b = z_ret; + assert(mp_check(&b, z) == 0); + (void) b; + assert(z_ret != z); + + sql_result_blob(context, z_ret, size, sql_free); + context->pOut->flags|= MEM_Subtype; + context->pOut->subtype = SQL_SUBTYPE_MSGPACK; } else if (eCall == STAT_GET_KEY) { if (p->iGet < 0) { samplePushPrevious(p, 0); @@ -712,19 +722,30 @@ statGet(sql_context * context, int argc, sql_value ** argv) } } - char *zRet = sqlMallocZero(p->nCol * 25); - if (zRet == 0) { + int i; + + size_t size = mp_sizeof_array(p->nCol); + for (i = 0; i < p->nCol; ++i) { + size += mp_sizeof_uint(aCnt[i]); + } + + char *z_ret = sqlMallocZero(size); + if (z_ret == 0) { sql_result_error_nomem(context); } else { - int i; - char *z = zRet; + char *z = z_ret; + z = mp_encode_array(z, p->nCol); for (i = 0; i < p->nCol; i++) { - sql_snprintf(24, z, "%llu ", (u64) aCnt[i]); - z += sqlStrlen30(z); + z = mp_encode_uint(z, aCnt[i]); } - assert(z[0] == '\0' && z > zRet); - z[-1] = '\0'; - sql_result_text(context, zRet, -1, sql_free); + const char *b = z_ret; + assert(mp_check(&b, z) == 0); + (void) b; + assert(z_ret != z); + + sql_result_blob(context, z_ret, size, sql_free); + context->pOut->flags|= MEM_Subtype; + context->pOut->subtype = SQL_SUBTYPE_MSGPACK; } } @@ -1167,35 +1188,51 @@ struct analysis_index_info { uint32_t index_count; }; +#define KW_UNORDERED 0x01 +#define KW_NOSKIPSCAN 0x02 /** - * The first argument points to a nul-terminated string - * containing a list of space separated integers. Load - * the first stat_size of these into the output arrays. + * The first argument points to a msg_pack array + * containing a list of integers. Load the first + * stat_size of these into the output arrays. + * keywords_info needed for keywords encoding/decoding. * - * @param stat_string String containing array of integers. - * @param stat_size Size of output arrays. + * @param stat_array MP_ARRAY containing array of integers. + * @param stat_size Size of input array (not counting the keywords). * @param[out] stat_exact Decoded array of statistics. * @param[out] stat_log Decoded array of stat logariphms. + * @param[out] keywords_info Bitmask of having keywords in the field. */ static void -decode_stat_string(const char *stat_string, int stat_size, tRowcnt *stat_exact, - LogEst *stat_log) { - const char *z = stat_string; +decode_stat_array(const char *stat_array, int stat_size, tRowcnt *stat_exact, + LogEst *stat_log, uint8_t keywords_info) { + const char *z = stat_array; + uint32_t array_size = mp_decode_array(&z); if (z == NULL) - z = ""; - for (int i = 0; *z && i < stat_size; i++) { - tRowcnt v = 0; - int c; - while ((c = z[0]) >= '0' && c <= '9') { - v = v * 10 + c - '0'; - z++; - } + return; + for (int i = 0; i < stat_size; i++) { + tRowcnt v = (tRowcnt) mp_decode_uint(&z); if (stat_exact != NULL) stat_exact[i] = v; if (stat_log != NULL) stat_log[i] = sqlLogEst(v); - if (*z == ' ') - z++; + } + + /* Keywords processing if needed. */ + if (keywords_info != 0) { + uint32_t keyword_count = array_size - stat_size; + if (keyword_count > 0) { + while (keyword_count) { + const char *sval; + uint32_t sval_len; + sval = mp_decode_str(&z, &sval_len); + if (!sql_stricmp(sval, "unordered")) { + keywords_info |= KW_UNORDERED; + } else if (!sql_stricmp(sval, "noskipscan")) { + keywords_info |= KW_NOSKIPSCAN; + } + keyword_count--; + } + } } } @@ -1270,23 +1307,19 @@ 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, - stat->tuple_log_est); - stat->is_unordered = false; - stat->skip_scan_enabled = true; - char *z = argv[2]; - /* Position ptr at the end of stat string. */ - for (; *z == ' ' || (*z >= '0' && *z <= '9'); ++z); - while (z[0]) { - if (sql_strlike_cs("unordered%", z, '[') == 0) - index->def->opts.stat->is_unordered = true; - else if (sql_strlike_cs("noskipscan%", z, '[') == 0) - index->def->opts.stat->skip_scan_enabled = false; - while (z[0] != 0 && z[0] != ' ') - z++; - while (z[0] == ' ') - z++; - } + uint8_t keywords_info = 0; + decode_stat_array(argv[2], column_count, stat->tuple_stat1, + stat->tuple_log_est, keywords_info); + + if ((keywords_info & KW_UNORDERED) == 0) + stat->is_unordered = false; + else + stat->is_unordered = true; + if ((keywords_info & KW_NOSKIPSCAN) == 0) + stat->skip_scan_enabled = true; + else + stat->skip_scan_enabled = false; + return 0; } @@ -1489,12 +1522,13 @@ load_stat_from_space(struct sql *db, const char *sql_select_prepare, 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); + + decode_stat_array((char *)sql_column_text(stmt, 2), + column_count, sample->eq, 0, 0); + decode_stat_array((char *)sql_column_text(stmt, 3), + column_count, sample->lt, 0, 0); + decode_stat_array((char *)sql_column_text(stmt, 4), + column_count, sample->dlt, 0, 0); /* Take a copy of the sample. */ sample->key_size = sql_column_bytes(stmt, 5); sample->sample_key = region_alloc(&fiber()->gc, diff --git a/test/box-py/bootstrap.result b/test/box-py/bootstrap.result index 3e4394557..b622a529d 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, 1] ... box.space._cluster:select{} --- @@ -74,10 +74,10 @@ box.space._space:select{} - [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'}]] + 'type': 'string'}, {'name': 'stat', 'type': 'array'}]] - [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'}]] + 'type': 'string'}, {'name': 'neq', 'type': 'array'}, {'name': 'nlt', 'type': 'array'}, + {'name': 'ndlt', 'type': 'array'}, {'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/access_misc.result b/test/box/access_misc.result index 4ffeb386a..d17ba71d1 100644 --- a/test/box/access_misc.result +++ b/test/box/access_misc.result @@ -814,10 +814,10 @@ box.space._space:select() - [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'}]] + 'type': 'string'}, {'name': 'stat', 'type': 'array'}]] - [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'}]] + 'type': 'string'}, {'name': 'neq', 'type': 'array'}, {'name': 'nlt', 'type': 'array'}, + {'name': 'ndlt', 'type': 'array'}, {'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/sql-tap/analyze1.test.lua b/test/sql-tap/analyze1.test.lua index cc1259314..e6d512f97 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(38) +test:plan(39) --!./tcltestrunner.lua -- 2005 July 22 @@ -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" + "T1",2,1,"T1I1",2,2,"T1I2",2,1,"T1I3",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" + "T1",4,1,"T1I1",4,4,"T1I2",4,1,"T1I3",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" + "T1",5,1,"T1I1",5,3,"T1I2",5,2,"T1I3",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" + "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 -- </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" + "T1",5,1,"T1I1",5,3,"T1I2",5,2,"T1I3",5,3,1,"T2",5,1,"T2I1",5,3,"T2I2",5,2 -- </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" + "T1",5,1,"T1I1",5,3,"T1I2",5,2,"T1I3",5,3,1,"T2",5,1,"T2I1",5,3,"T2I2",5,2 -- </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" + "T1",5,1,"T1I1",5,3,"T1I2",5,2,"T1I3",5,3,1,"T2",5,1,"T2I1",5,3 -- </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" + "T3",5,1,"T3I1",5,3,"T3I2",5,3,1,1,1,"T3I3",5,5,2,1,1 -- </analyze-3.9> }) @@ -320,26 +320,37 @@ 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" + "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 -- </analyze-4.0> }) test:do_execsql_test( - "analyze-4.1", + "analyze-4.1.1", [[ DELETE FROM "_sql_stat1"; - INSERT INTO "_sql_stat1" VALUES('t4', 't4i1', 'nonsense'); - INSERT INTO "_sql_stat1" VALUES('t4', 't4i2', '432653287412874653284129847632'); + ]], { + -- <analyze-4.1> + -- </analyze-4.1> + }) + +_sql_stat1 = box.space[box.schema.SQL_STAT1_ID] +_sql_stat1:insert{'t4', 't4i1', {'nonsense'}} +_sql_stat1:insert{'t4', 't4i2', {432653287412874653284129847632}} + +test:do_execsql_test( + "analyze-4.1.2", + [[ SELECT * FROM t4 WHERE x = 1234; ]], { -- <analyze-4.1> -- </analyze-4.1> }) +_sql_stat1:insert{'t4', 'xyzzy', {0, 1, 2, 3}} + test:do_execsql_test( "analyze-4.2", [[ - INSERT INTO "_sql_stat1" VALUES('t4', 'xyzzy', '0 1 2 3'); SELECT * FROM t4 WHERE x = 1234; ]], { -- <analyze-4.2> @@ -522,27 +533,98 @@ test:do_execsql_test( SELECT * FROM "_sql_stat1" where "tbl"='T1' and "idx"='I1' LIMIT 1; ]], { -- <analyze-6.1.2> - "T1", "I1", "221 221 221 221 2" + "T1","I1",221,221,221,221,2 -- </analyze-6.1.2> }) +function sum_table(t) + local res = 0 + for k, v in pairs(t) do + res = res + v + end + return res +end + +function get_tuples_where_order_by_limit(order_by, order, limit) + space = box.space[box.schema.SQL_STAT4_ID] + t = {} + for k, v in space:pairs() do + table.insert(t, v) + end + + local i + local count + local where = {tbl = "T1", idx = 'I1'} + if where ~= 0 then + for k, v in pairs(where) do + i = 1 + for key, tuple in pairs(t) do + tuple = t[i] + if tuple[k] ~= v then + t[i] = nil + else + count = i + end + i = i + 1 + end + end + end + + local compare + if order == 'asc' then + compare = function(a, b) + if sum_table(a[order_by]) <= sum_table(b[order_by]) then + return true + end + end + else + compare = function(a, b) + if sum_table(a[order_by]) > sum_table(b[order_by]) then + return true + end + end + end + + table.sort(t, compare) + + if limit == nil then + limit = count + end + + local ret = '' + i = 1 + while i <= limit do + if i == 1 then + ret = tostring(t[i]) + else + ret = ret..' '..tostring(t[i]) + end + i = i + 1 + end + return ret +end + +_sql_stat4 = box.space[box.schema.SQL_STAT4_ID] + +box.internal.sql_create_function("get_tuples_where_order_by_limit", "TEXT", get_tuples_where_order_by_limit) + 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 get_tuples_where_order_by_limit('nlt', 'asc', 1); ]], { -- <analyze-6.1.3> - "T1", "I1", "221 221 221 1", "0 0 0 10", "0 0 0 10" + "[\'T1\', \'I1\', [221, 221, 221, 1], [0, 0, 0, 10], [0, 0, 0, 10], !!binary lKF4oXmhego=]" -- </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; + SELECT get_tuples_where_order_by_limit('nlt', 'desc', 1); ]], { -- <analyze-6.1.4> - "T1", "I1", "221 221 221 1", "0 0 0 99", "0 0 0 99" + "[\'T1\', \'I1\', [221, 221, 221, 1], [0, 0, 0, 219], [0, 0, 0, 199], !!binary lKF4oXmheszH]" -- </analyze-6.1.4> }) diff --git a/test/sql-tap/analyze4.test.lua b/test/sql-tap/analyze4.test.lua index 7d7498faf..864a0c51a 100755 --- a/test/sql-tap/analyze4.test.lua +++ b/test/sql-tap/analyze4.test.lua @@ -57,7 +57,7 @@ test:do_execsql_test( [[ SELECT "idx", "stat" FROM "_sql_stat1" WHERE "tbl"='T1' ORDER BY "idx"; ]], { -- <analyze4-1.1> - "T1","128 1", "T1A", "128 1", "T1B", "128 128" + "T1",128,1,"T1A",128,1,"T1B",128,128 -- </analyze4-1.1> }) @@ -77,7 +77,7 @@ test:do_test( ]]) end, { -- <analyze4-1.2> - "T1", "128 1", "T1A", "128 1", "T1B", "128 64" + "T1",128,1,"T1A",128,1,"T1B",128,64 -- </analyze4-1.2> }) @@ -115,7 +115,7 @@ test:do_execsql_test( ]] , { -- <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" + "T1",128,1,"T1A",128,1,"T1B",128,128,"T1BCD",128,128,4,2,"T1CBD",128,4,4,2,"T1CDB",128,4,2,2 -- </analyze4-1.3> }) diff --git a/test/sql-tap/analyze9.test.lua b/test/sql-tap/analyze9.test.lua index b7033234f..60f4a869c 100755 --- a/test/sql-tap/analyze9.test.lua +++ b/test/sql-tap/analyze9.test.lua @@ -1,9 +1,12 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(121) +test:plan(120) testprefix = "analyze9" +_sql_stat1 = box.space[box.schema.SQL_STAT1_ID] +_sql_stat4 = box.space[box.schema.SQL_STAT4_ID] + --!./tcltestrunner.lua -- 2013 August 3 -- @@ -72,9 +75,9 @@ test:do_execsql_test( SELECT "tbl","idx","neq","nlt","ndlt",msgpack_decode_sample("sample") FROM "_sql_stat4" 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,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.2> }) @@ -85,9 +88,9 @@ test:do_execsql_test( ]], { -- <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","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)" -- </1.3> }) @@ -184,12 +187,12 @@ test:do_execsql_test( ]], generate_tens(100)) -- The first element in the "nEq" list of all samples should therefore be 10. --- + test:do_execsql_test( "3.3.2", [[ ANALYZE; - SELECT lrange("neq", 1, 1) FROM "_sql_stat4" WHERE "idx" = 'I2'; + SELECT lrange(msgpack_decode_sample("neq"), 1, 1) FROM "_sql_stat4" WHERE "idx" = 'I2'; ]], generate_tens_str(24)) --------------------------------------------------------------------------- @@ -283,31 +286,100 @@ test:do_execsql_test( -- </4.2> }) +function get_tuples_where_order_by_limit(order_by, order, limit) + space = box.space[box.schema.SQL_STAT4_ID] + t = {} + for k, v in space:pairs() do + table.insert(t, v) + end + + local i + local count + local where = {idx = 'I1'} + if where ~= 0 then + for k, v in pairs(where) do + i = 1 + for key, tuple in pairs(t) do + tuple = t[i] + if tuple[k] ~= v then + t[i] = nil + else + count = i + end + i = i + 1 + end + end + end + + local compare + if order == 'asc' then + compare = function(a, b) + if a[order_by] <= b[order_by] then + return true + end + end + else + compare = function(a, b) + if a[order_by] > b[order_by] then + return true + end + end + end + + table.sort(t, compare) + + if limit == nil then + limit = count + end + + local ret = '' + i = 1 + while i <= limit do + if i == 1 then + ret = tostring(t[i]) + else + ret = ret..', '..tostring(t[i]) + end + i = i + 1 + end + return ret +end + +box.internal.sql_create_function("get_tuples_where_order_by_limit", "TEXT", get_tuples_where_order_by_limit) + 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 get_tuples_where_order_by_limit('sample', 'asc', 16); ]], { -- <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" + "[\'T1\', \'I1\', [10, 10, 10], [0, 0, 0], [0, 0, 0], !!binary kwAAoTA=], ".. + "[\'T1\', \'I1', [10, 10, 10], [10, 10, 10], [1, 1, 1], !!binary kwEBoTE=], ".. + "[\'T1\', \'I1\', [10, 10, 10], [20, 20, 20], [2, 2, 2], !!binary kwICoTI=], ".. + "[\'T1\', \'I1\', [10, 10, 10], [30, 30, 30], [3, 3, 3], !!binary kwMDoTM=], ".. + "[\'T1\', \'I1\', [10, 10, 10], [40, 40, 40], [4, 4, 4], !!binary kwQEoTQ=], ".. + "[\'T1\', \'I1\', [10, 10, 10], [50, 50, 50], [5, 5, 5], !!binary kwUFoTU=], ".. + "[\'T1\', \'I1\', [10, 10, 10], [60, 60, 60], [6, 6, 6], !!binary kwYGoTY=], ".. + "[\'T1\', \'I1\', [10, 10, 10], [70, 70, 70], [7, 7, 7], !!binary kwcHoTc=], ".. + "[\'T1\', \'I1\', [10, 10, 10], [80, 80, 80], [8, 8, 8], !!binary kwgIoTg=], ".. + "[\'T1\', \'I1\', [10, 10, 10], [90, 90, 90], [9, 9, 9], !!binary kwkJoTk=], ".. + "[\'T1\', \'I1\', [10, 10, 10], [100, 100, 100], [10, 10, 10], !!binary kwoKojEw], ".. + "[\'T1\', \'I1\', [10, 10, 10], [110, 110, 110], [11, 11, 11], !!binary kwsLojEx], ".. + "[\'T1\', \'I1\', [10, 10, 10], [120, 120, 120], [12, 12, 12], !!binary kwwMojEy], ".. + "[\'T1\', \'I1\', [10, 10, 10], [130, 130, 130], [13, 13, 13], !!binary kw0NojEz], ".. + "[\'T1\', \'I1\', [10, 10, 10], [140, 140, 140], [14, 14, 14], !!binary kw4OojE0], ".. + "[\'T1\', \'I1\', [10, 10, 10], [150, 150, 150], [15, 15, 15], !!binary kw8PojE1]" -- </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; + SELECT get_tuples_where_order_by_limit('sample', 'desc', 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" + "['T1', 'I1', [2, 1, 1], [295, 296, 296], [120, 122, 125], !!binary k8zJBKFo], ".. + "['T1', 'I1', [5, 3, 1], [290, 290, 291], [119, 119, 120], !!binary k8zIAaFi]" -- </4.4> }) @@ -391,16 +463,41 @@ test:do_execsql_test( INSERT INTO t1 VALUES(null, 4, 4); INSERT INTO t1 VALUES(null, 5, 5); ANALYZE; - CREATE TABLE x1(tbl TEXT, idx TEXT , neq TEXT, nlt TEXT, ndlt TEXT, sample BLOB, PRIMARY KEY(tbl, idx, sample)); - INSERT INTO x1 SELECT * FROM "_sql_stat4"; - DELETE FROM "_sql_stat4"; - INSERT INTO "_sql_stat4" SELECT * FROM x1; - ANALYZE; ]]) +function copy_tuples(from, to) + for i,t in from:pairs() do + to:insert(t) + end +end + +function prepare_to_6_2() + local format = {} + format[1] = {name='tbl', type='string'} + format[2] = {name='idx', type='string'} + format[3] = {name='neq', type='array'} + format[4] = {name='nlt', type='array'} + format[5] = {name='ndlt', type='array'} + format[6] = {name='sample', type='scalar'} + x1 = box.schema.space.create("x1", {engine = 'memtx', format = format, field_count = 0}) + x1:create_index('primary', {parts = {1, 'string', 2, 'string', 6, 'scalar'}}) + copy_tuples(_sql_stat4, x1) +end + +prepare_to_6_2() + test:do_execsql_test( 6.2, [[ + DELETE FROM "_sql_stat4"; + ]]) + +copy_tuples(x1, _sql_stat4) + +test:do_execsql_test( + 6.3, + [[ + ANALYZE; SELECT * FROM t1 WHERE a = 'abc'; ]]) @@ -459,10 +556,19 @@ test:do_execsql_test( -- -- </7.2> -- }) +function update_stat_fields(stat, field_num, val) + for i,t in stat:pairs() do + t = t:transform(3, 3) + print(t) + stat:update(t, {{'=', field_num, val}}) + end +end + +update_stat_fields(_sql_stat4, 3, {0, 0, 0}) + test:do_execsql_test( 7.3, [[ - UPDATE "_sql_stat4" SET "neq" = '0 0 0'; ANALYZE; SELECT * FROM t1 WHERE a = 1; ]], { @@ -471,11 +577,12 @@ test:do_execsql_test( -- </7.3> }) +box.sql.execute('ANALYZE') +update_stat_fields(_sql_stat4, 5, {0, 0, 0}) + test:do_execsql_test( 7.4, [[ - ANALYZE; - UPDATE "_sql_stat4" SET "ndlt" = '0 0 0'; ANALYZE; SELECT * FROM t1 WHERE a = 3; ]], { @@ -484,11 +591,12 @@ test:do_execsql_test( -- </7.4> }) +box.sql.execute('ANALYZE') +update_stat_fields(_sql_stat4, 4, {0, 0, 0}) + test:do_execsql_test( 7.5, [[ - ANALYZE; - UPDATE "_sql_stat4" SET "nlt" = '0 0 0'; ANALYZE; SELECT * FROM t1 WHERE a = 5; ]], { @@ -1044,9 +1152,10 @@ test:do_execsql_test( INSERT INTO x1 VALUES(3, 4); INSERT INTO x1 VALUES(5, 6); ANALYZE; - INSERT INTO "_sql_stat4" VALUES('x1', 'abc', '', '', '', ''); ]]) +_sql_stat4:insert{'x1', 'abc', {}, {}, {}, ''} + test:do_execsql_test( 15.2, [[ @@ -1057,11 +1166,7 @@ test:do_execsql_test( -- </15.2> }) -test:do_execsql_test( - 15.3, - [[ - INSERT INTO "_sql_stat4" VALUES('42', '42', '42', '42', '42', '42'); - ]]) +_sql_stat4:insert{'42', '42', {42}, {42}, {42}, '42'} test:do_execsql_test( 15.4, @@ -1127,16 +1232,13 @@ test:do_execsql_test( }) -- This is just for coverage.... -test:do_execsql_test( - 15.11, - [[ - ANALYZE; - UPDATE "_sql_stat1" SET "stat" = "stat" || ' unordered'; - ]]) +box.sql.execute('ANALYZE') +update_stat_fields(_sql_stat1, 3, {'unordered'}) test:do_execsql_test( 15.12, [[ + ANALYZE; SELECT * FROM x1; ]], { -- <15.12> diff --git a/test/sql-tap/analyzeC.test.lua b/test/sql-tap/analyzeC.test.lua index 16d6233de..0fd04658b 100755 --- a/test/sql-tap/analyzeC.test.lua +++ b/test/sql-tap/analyzeC.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(20) +test:plan(25) testprefix = "analyzeC" @@ -40,7 +40,18 @@ 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'); + ]], { + -- <1.0> + -- </1.0> + }) + +local _sql_stat1 = box.space[box.schema.SQL_STAT1_ID] +_sql_stat1:insert{'t1','t1b',{12345, 2}} +_sql_stat1:insert{'t1','t1c',{12345, 4}} + +test:do_execsql_test( + 1.0, + [[ ANALYZE; SELECT b,c,d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d; ]], { @@ -180,7 +191,17 @@ test:do_execsql_test( 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'); + ]], { + -- <4.0> + -- </4.0> + }) + +_sql_stat1:insert{'t1','t1bc',{12345, 3, 2, 'sz=10'}} +_sql_stat1:insert{'t1','t1db',{12345, 3, 2, 'sz=20'}} + +test:do_execsql_test( + 4.0, + [[ ANALYZE; SELECT count(b) FROM t1; ]], { @@ -203,7 +224,17 @@ test:do_execsql_test( 4.2, [[ 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'); + ]], { + -- <4.2> + -- </4.2> + }) + +_sql_stat1:insert{'t1','t1bc',{12345, 3, 2, 'sz=20'}} +_sql_stat1:insert{'t1','t1db',{12345, 3, 2, 'sz=10'}} + +test:do_execsql_test( + 4.2, + [[ ANALYZE; SELECT count(b) FROM t1; ]], { @@ -229,9 +260,17 @@ test:do_execsql_test( 5.0, [[ 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'); + ]], { + -- <5.0> + -- </5.0> + }) + +_sql_stat1:insert{'t1','t1bc',{12345, 3, 2, 'x=5', 'sz=10', 'y=10'}} +_sql_stat1:insert{'t1','t1db',{12345, 3, 2, 'whatever', 'sz=20', 'junk'}} + +test:do_execsql_test( + 5.0, + [[ ANALYZE; SELECT count(b) FROM t1; ]], { @@ -255,7 +294,17 @@ test:do_execsql_test( 5.2, [[ 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'); + ]], { + -- <5.2> + -- </5.2> + }) + +_sql_stat1:insert{'t1','t1db',{12345, 3, 2, 'x=5', 'sz=10', 'y=10'}} +_sql_stat1:insert{'t1','t1bc',{12345, 3, 2, 'whatever', 'sz=20', 'junk'}} + +test:do_execsql_test( + 5.2, + [[ ANALYZE; SELECT count(b) FROM t1; ]], { diff --git a/test/sql-tap/gh-3350-skip-scan.test.lua b/test/sql-tap/gh-3350-skip-scan.test.lua index 4cecfe081..eec09a546 100755 --- a/test/sql-tap/gh-3350-skip-scan.test.lua +++ b/test/sql-tap/gh-3350-skip-scan.test.lua @@ -3,7 +3,7 @@ -- gh-3350, gh-2859 test = require("sqltester") -test:plan(4) +test:plan(5) local function lindex(str, pos) return str:sub(pos+1, pos+1) @@ -78,7 +78,7 @@ test:do_execsql_test( ) test:do_execsql_test( - "skip-scan-1.4", + "skip-scan-1.4.1", [[ DROP TABLE IF EXISTS t1; CREATE TABLE t1(id INTEGER PRIMARY KEY, a TEXT, b INT, c INT, d INT); @@ -96,7 +96,16 @@ test:do_execsql_test( ANALYZE; DELETE FROM "_sql_stat1"; DELETE FROM "_sql_stat4"; - INSERT INTO "_sql_stat1" VALUES('T1','T1ABC','10000 5000 2000 10'); + ]], { + } +) + +local _sql_stat1 = box.space[box.schema.SQL_STAT1_ID] +_sql_stat1:insert{'T1','T1ABC', {10000,5000,2000,10}} + +test:do_execsql_test( + "skip-scan-1.4.2", + [[ ANALYZE t2; SELECT a,b,c,d FROM t1 WHERE b=345; ]], { @@ -104,5 +113,4 @@ test:do_execsql_test( } ) - test:finish_test() diff --git a/test/sql/sql-statN-index-drop.result b/test/sql/sql-statN-index-drop.result index 760595188..d71a1e791 100644 --- a/test/sql/sql-statN-index-drop.result +++ b/test/sql/sql-statN-index-drop.result @@ -33,17 +33,17 @@ box.sql.execute("ANALYZE;") -- Checking the data. box.sql.execute("SELECT * FROM \"_sql_stat4\";") --- -- - ['T1', 'I1', '1', '0', '0', !!binary kQI=] - - ['T1', 'T1', '1', '0', '0', !!binary kQE=] - - ['T2', 'I1', '1', '0', '0', !!binary kQI=] - - ['T2', 'T2', '1', '0', '0', !!binary kQE=] +- - ['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'] +- - ['T1', 'I1', [1, 1]] + - ['T1', 'T1', [1, 1]] + - ['T2', 'I1', [1, 1]] + - ['T2', 'T2', [1, 1]] ... -- Dropping an index. box.sql.execute("DROP INDEX i1 ON t1;") @@ -52,15 +52,15 @@ 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=] +- - ['T1', 'T1', [1], [0], [0], !!binary kQE=] + - ['T2', 'I1', [1], [0], [0], !!binary kQI=] + - ['T2', 'T2', [1], [0], [0], !!binary kQE=] ... box.sql.execute("SELECT * FROM \"_sql_stat1\";") --- -- - ['T1', 'T1', '1 1'] - - ['T2', 'I1', '1 1'] - - ['T2', 'T2', '1 1'] +- - ['T1', 'T1', [1, 1]] + - ['T2', 'I1', [1, 1]] + - ['T2', 'T2', [1, 1]] ... --Cleaning up. box.sql.execute("DROP TABLE t1;") @@ -95,17 +95,17 @@ box.sql.execute("ANALYZE;") -- Checking the data. box.sql.execute("SELECT * FROM \"_sql_stat4\";") --- -- - ['T1', 'I1', '1', '0', '0', !!binary kQI=] - - ['T1', 'T1', '1', '0', '0', !!binary kQE=] - - ['T2', 'I1', '1', '0', '0', !!binary kQI=] - - ['T2', 'T2', '1', '0', '0', !!binary kQE=] +- - ['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'] +- - ['T1', 'I1', [1, 1]] + - ['T1', 'T1', [1, 1]] + - ['T2', 'I1', [1, 1]] + - ['T2', 'T2', [1, 1]] ... -- Dropping an index. box.sql.execute("DROP INDEX i1 ON t2;") @@ -114,15 +114,15 @@ 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=] +- - ['T1', 'I1', [1], [0], [0], !!binary kQI=] + - ['T1', 'T1', [1], [0], [0], !!binary kQE=] + - ['T2', 'T2', [1], [0], [0], !!binary kQE=] ... box.sql.execute("SELECT * FROM \"_sql_stat1\";") --- -- - ['T1', 'I1', '1 1'] - - ['T1', 'T1', '1 1'] - - ['T2', 'T2', '1 1'] +- - ['T1', 'I1', [1, 1]] + - ['T1', 'T1', [1, 1]] + - ['T2', 'T2', [1, 1]] ... --Cleaning up. box.sql.execute("DROP TABLE t1;") diff --git a/test/sql/upgrade.result b/test/sql/upgrade.result index 02ab9b42b..2133a0abd 100644 --- a/test/sql/upgrade.result +++ b/test/sql/upgrade.result @@ -31,13 +31,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'}]] + 'type': 'string'}, {'name': 'stat', 'type': 'array'}]] ... 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'}, - {'name': 'ndlt', 'type': 'string'}, {'name': 'sample', 'type': 'scalar'}]] + 'type': 'string'}, {'name': 'neq', 'type': 'array'}, {'name': 'nlt', 'type': 'array'}, + {'name': 'ndlt', 'type': 'array'}, {'name': 'sample', 'type': 'scalar'}]] ... box.space._index:get({box.space._space.index['name']:get('_trigger').id, 0}) ---
next prev parent reply other threads:[~2019-03-01 10:33 UTC|newest] Thread overview: 10+ messages / expand[flat|nested] mbox.gz Atom feed top 2019-01-27 0:28 [tarantool-patches] [PATCH 0/2] " Roman Khabibov 2019-01-27 0:28 ` [tarantool-patches] [PATCH 1/2] sql: add sqlite3 msgpack result function Roman Khabibov 2019-02-11 23:53 ` [tarantool-patches] " n.pettik 2019-01-27 0:28 ` [tarantool-patches] [PATCH 2/2] sql: store statistics in statN as an array of integers Roman Khabibov 2019-02-11 23:53 ` [tarantool-patches] " n.pettik 2019-03-01 10:33 ` Roman Khabibov [this message] 2019-03-05 17:54 ` [tarantool-patches] Re: [PATCH] " n.pettik 2019-03-12 1:10 ` Roman Khabibov 2019-03-22 15:16 ` n.pettik 2019-02-11 23:53 ` [tarantool-patches] Re: [PATCH 0/2] " n.pettik
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --in-reply-to=FF59F166-848D-45D8-AA93-DB91E949C3E0@tarantool.org \ --to=roman.habibov@tarantool.org \ --cc=korablev@tarantool.org \ --cc=tarantool-patches@freelists.org \ --subject='[tarantool-patches] Re: [PATCH] sql: store statistics in statN as an array of integers' \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox