[tarantool-patches] Re: [PATCH] sql: store statistics in statN as an array of integers
Roman Khabibov
roman.habibov at tarantool.org
Tue Mar 12 04:10:09 MSK 2019
Hi! Thanks for review.
> You can simply do this:
>
> _sql_stat1 = box.space._sql_stat1
Done.
>> +_sql_stat1:insert{'t4', 't4i1', {'nonsense'}}
>> +_sql_stat1:insert{'t4', 't4i2', {432653287412874653284129847632}}
>
> Firstly, it would be better if each test-related thing would
> be placed in scope of test function. So, lets move these
> insertions to test:do_test func (you can check out examples
> in sql-tap/lua-tables.test.lua for instance).
> Secondly, simple insertion to _sql_stat spaces doesn’t trigger
> update of statistics in in-memory structs. In other words, these
> tests check nothing (and without your patch as well). To make
> these tests work, you should force statistics loading with instance
> reload. I am not sure if this possible in tap suite, so probably you
> should move these tests to sql/ suite.
diff --git a/test/sql/sql-statN.test.lua b/test/sql/sql-statN.test.lua
>> +function sum_table(t)
>
> Please, provide brief comment to functions.
+-- Show tuples in the "_sql_stat4" sorted by field "nlt"
+-- where fields "tbl" is "T1" and "idx" is "I1".
+function get_tuple_with_T1_I1_order_by_nlt(order)
+ _sql_stat4 = box.space._sql_stat4
+ t = {}
+ for k, v in _sql_stat4:pairs() do
+ table.insert(t, v)
+ end
+
+ local where = {tbl = "T1", idx = 'I1'}
+ -- Set all tuples in the t to nil if its field "tbl"
+ -- isn't "T1" and field "idx" isn't "I1".
+ for k, v in pairs(where) do
+ local i = 1
+ for _, tuple in pairs(t) do
+ tuple = t[i]
+ if tuple[k] ~= v then
+ t[i] = nil
+ end
+ i = i + 1
+ end
+ end
+
+ --Summarize all numbers in table that contains numbers only.
+ --Needed to compare stat fields.
+ local function sum_table(t)
+ local res = 0
+ for _, v in pairs(t) do
+ res = res + v
+ end
+ return res
+ end
+
+ local order_by = 'nlt'
+ local compare = function() end
+ 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)
+
+ return tostring(t[1])
+end
>> + local res = 0
>> + for k, v in pairs(t) do
>> + res = res + v
>
> If you don’t need one of params, just skip it:
>
> for _, v in pairs(t) do
> …
Done.
>
>> + 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
>
> Always initialise variables, it is best practice.
Hope, I didn’t forget to do it now.
>> + local where = {tbl = "T1", idx = 'I1'}
>> + if where ~= 0 then
>
> This is always true.
Removed.
>> + 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
>
> Can’t read this code. Please, comment it at least.
+ -- Set all tuples in the t to nil if its field "tbl"
+ -- isn't "T1" and field "idx" isn't "I1”.
>> +
>> + 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);
>
> In this particular test(s), you pass the same limit and order by
> args. So I’d rather hardcode them to make function smaller and
> simpler.
Done.
>> ]], {
>> -- <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>
>> })
>>
>>
>>
>>
>> @@ -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.
>> ---
>> +
>
> Extra empty line.
Removed.
>> 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>
>> })
>>
>> +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=], “..
>
> Firstly, it would be nice to see decoded sample; secondly - throw away index/table.
> It would make diff look clearer.
+local function res_4_3()
+ local res = ''
+ for i = 0, 15 do
+ res = res..'[[10, 10, 10], ['..(i * 10)..', '..
+ (i * 10)..', '..(i * 10)..'], ['..i..', '..i..', '..i..'], \''..i..' '..
+ i..' '..i..'\'], '
+ end
+ res = string.sub(res, 0, -3)
+ return res
+end
+
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_with_I1_order_by_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"
+ res_4_3()
-- </4.3>
})
>> + "[\'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()
>
> Wrap this func in test-func call.
Done.
>> +
>> 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)
>
> Why do you need this wrapper at all?
>
>> + for i,t in stat:pairs() do
>> + t = t:transform(3, 3)
>> + print(t)
>
> Debug print.
Removed.
>> + stat:update(t, {{'=', field_num, val}})
>> + end
>> +end
>> +
>> +update_stat_fields(_sql_stat4, 3, {0, 0, 0})
>
> Wrap this func in test-func call.
>
>> +
>> 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’)
>
> Again you break philosophy of tap suite:
> wrap this analyze statement into one of
> testing funcs.
>
>>
>> -- 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’})
>
> Firstly, ‘unordered’ token should be the only member
> of array, it should be placed after original statistics.
> Secondly, without instance reloading all these insertions
> to stat space make no sense.
Moved to the sql suite. Now I add ‘unordered’ to the stat array.
>> 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
>>
>> +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(
>> }
>> )
>>
>> -
>
> Extra diff.
>
>> test:finish_test()
Removed.
commit 6e6393d1aae25dd387db330eb80a412b51f549d6
Author: Roman Khabibov <roman.habibov at 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..168f565a2 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..fde91b7d9 100644
--- a/src/box/lua/upgrade.lua
+++ b/src/box/lua/upgrade.lua
@@ -614,6 +614,25 @@ 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 _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 get_version()
local version = box.space._schema:get{'version'}
if version == nil then
@@ -641,7 +660,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/sql/analyze.c b/src/box/sql/analyze.c
index 8c83288e6..3411d60df 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 an 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,32 @@ 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) {
+ uint32_t size = mp_sizeof_array(p->nKeyCol + 1);
+ size += mp_sizeof_uint(p->nRow);
+ for (int 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 *mp_stat1 = malloc(size);
+ if (mp_stat1 == NULL) {
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);
- assert(p->current.anEq[i]);
+ char *data = mp_stat1;
+ data = mp_encode_array(data, p->nKeyCol + 1);
+ data = mp_encode_uint(data, p->nRow);
+ for (int 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;
+ data = mp_encode_uint(data, val);
+ assert(p->current.anEq[i] > 0);
}
- assert(z[0] == '\0' && z > zRet);
+ assert(data == size + mp_stat1);
- sql_result_text(context, zRet, -1, sql_free);
+ sql_result_blob(context, mp_stat1, size, 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);
@@ -710,24 +715,25 @@ statGet(sql_context * context, int argc, sql_value ** argv)
p->iGet++;
break;
}
- }
+ }
+ size_t size = mp_sizeof_array(p->nCol);
+ for (int i = 0; i < p->nCol; ++i)
+ size += mp_sizeof_uint(aCnt[i]);
- char *zRet = sqlMallocZero(p->nCol * 25);
- if (zRet == 0) {
- sql_result_error_nomem(context);
- } else {
- int i;
- char *z = zRet;
- for (i = 0; i < p->nCol; i++) {
- sql_snprintf(24, z, "%llu ", (u64) aCnt[i]);
- z += sqlStrlen30(z);
+ char *mp_stat4 = malloc(size);
+ if (mp_stat4 == NULL) {
+ sql_result_error_nomem(context);
+ return;
}
- assert(z[0] == '\0' && z > zRet);
- z[-1] = '\0';
- sql_result_text(context, zRet, -1, sql_free);
+ char *data = mp_stat4;
+ data = mp_encode_array(data, p->nCol);
+ for (int i = 0; i < p->nCol; i++)
+ data = mp_encode_uint(data, aCnt[i]);
+ assert(data == mp_stat4 + size);
+ sql_result_blob(context, mp_stat4, size, free);
+ context->pOut->flags |= MEM_Subtype;
+ context->pOut->subtype = SQL_SUBTYPE_MSGPACK;
}
-
-}
#ifndef SQL_DEBUG
UNUSED_PARAMETER(argc);
#endif
@@ -1168,34 +1174,67 @@ struct analysis_index_info {
};
/**
- * 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.
+ * Tokens which may present at array containing statistics.
+ * They can turn on/off certain optimizations and help query
+ * planner. To be extended.
+ */
+enum {
+ UNORDERED_HINT_TK = 0x1,
+ NOSKIPSCAN_HINT_TK = 0x2,
+};
+
+/**
+ * The first argument points to a msgpack array
+ * containing a list of integers. Load the first
+ * stat_size of these into the output arrays.
*
- * @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;
- 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++;
- }
+decode_stat_array(const char *stat_array, int stat_size, tRowcnt *stat_exact,
+ LogEst *stat_log, uint8_t *keywords_mask)
+{
+ const char *z = stat_array;
+ if (z == NULL || mp_typeof(*z) != MP_ARRAY)
+ return;
+ int array_size = mp_decode_array(&z);
+ /*
+ * Number of entries in array should be greater or equal
+ * to given size. If it greater, then it could contain
+ * hint tokens to enable/disable certain optimizations.
+ * But in case it is less, it is likely to be malformed
+ * data, so it makes no sense to continue processing.
+ */
+ if (array_size < stat_size)
+ return;
+ for (int i = 0; i < stat_size; i++) {
+ if (mp_typeof(*z) != MP_UINT)
+ return;
+ 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_mask != NULL) {
+ *keywords_mask = 0;
+ uint32_t keyword_count = array_size - stat_size;
+ while (keyword_count-- > 0) {
+ uint32_t sval_len;
+ if (mp_typeof(*z) != MP_STR)
+ return;
+ const char *sval = mp_decode_str(&z, &sval_len);
+ if (strncmp(sval, "unordered", 9) == 0)
+ *keywords_mask |= UNORDERED_HINT_TK;
+ else if (strncmp(sval, "noskipscan", 10) == 0)
+ *keywords_mask |= NOSKIPSCAN_HINT_TK;
+ }
}
}
@@ -1270,23 +1309,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,
- 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_mask = 0;
+ decode_stat_array(argv[2], column_count, stat->tuple_stat1,
+ stat->tuple_log_est, &keywords_mask);
+ stat->is_unordered = (keywords_mask & UNORDERED_HINT_TK);
+ stat->skip_scan_enabled = ! (keywords_mask & NOSKIPSCAN_HINT_TK);
return 0;
}
@@ -1489,12 +1516,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, NULL, NULL);
+ decode_stat_array((char *)sql_column_text(stmt, 3),
+ column_count, sample->lt, NULL, NULL);
+ decode_stat_array((char *)sql_column_text(stmt, 4),
+ column_count, sample->dlt, NULL, NULL);
/* 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..009fa6488 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{}
---
@@ -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..793d07893 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._sql_stat1
+_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,83 @@ 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>
})
+-- Show tuples in the "_sql_stat4" sorted by field "nlt"
+-- where fields "tbl" is "T1" and "idx" is "I1".
+function get_tuple_with_T1_I1_order_by_nlt(order)
+ _sql_stat4 = box.space._sql_stat4
+ t = {}
+ for k, v in _sql_stat4:pairs() do
+ table.insert(t, v)
+ end
+
+ local where = {tbl = "T1", idx = 'I1'}
+ -- Set all tuples in the t to nil if its field "tbl"
+ -- isn't "T1" and field "idx" isn't "I1".
+ for k, v in pairs(where) do
+ local i = 1
+ for _, tuple in pairs(t) do
+ tuple = t[i]
+ if tuple[k] ~= v then
+ t[i] = nil
+ end
+ i = i + 1
+ end
+ end
+
+ --Summarize all numbers in table that contains numbers only.
+ --Needed to compare stat fields.
+ local function sum_table(t)
+ local res = 0
+ for _, v in pairs(t) do
+ res = res + v
+ end
+ return res
+ end
+
+ local order_by = 'nlt'
+ local compare = function() end
+ 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)
+
+ return tostring(t[1])
+end
+
+box.internal.sql_create_function("get_tuple_with_T1_I1_order_by_nlt", "TEXT", get_tuple_with_T1_I1_order_by_nlt)
+
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_tuple_with_T1_I1_order_by_nlt('asc');
]], {
-- <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_tuple_with_T1_I1_order_by_nlt('desc');
]], {
-- <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..42c8563d0 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(106)
testprefix = "analyze9"
+_sql_stat1 = box.space._sql_stat1
+_sql_stat4 = box.space._sql_stat4
+
--!./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,102 @@ test:do_execsql_test(
-- </4.2>
})
+-- Show tuples in the "_sql_stat4" sorted by field "sample"
+-- where field "idx" is "I1".
+function get_tuples_with_I1_order_by_sample(order, limit)
+ t = {}
+ for k, v in _sql_stat4:pairs() do
+ table.insert(t, v)
+ end
+
+ local count = 1
+ local where = {idx = 'I1'}
+ if where ~= 0 then
+ -- Set all tuples in the t to nil if its field "tbl"
+ -- isn't "T1" and field "idx" isn't "I1".
+ for k, v in pairs(where) do
+ local 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 order_by = 'sample'
+ local compare = function() end
+ 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 = ''
+ local i = 1
+ msgpack = require('msgpack')
+ while i <= limit do
+ t[i] = t[i]:update({{'=', 6, msgpack_decode_sample(t[i]['sample'])}})
+ t[i] = t[i]:transform(1, 2)
+ 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_with_I1_order_by_sample", "TEXT", get_tuples_with_I1_order_by_sample)
+
+local function res_4_3()
+ local res = ''
+ for i = 0, 15 do
+ res = res..'[[10, 10, 10], ['..(i * 10)..', '..
+ (i * 10)..', '..(i * 10)..'], ['..i..', '..i..', '..i..'], \''..i..' '..
+ i..' '..i..'\'], '
+ end
+ res = string.sub(res, 0, -3)
+ return res
+end
+
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_with_I1_order_by_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"
+ res_4_3()
-- </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_with_I1_order_by_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"
+ "[[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>
})
@@ -375,128 +449,6 @@ test:do_execsql_test(
-- </4.9>
})
----------------------------------------------------------------------------
--- This was also crashing (corrupt sql_stat4 table).
-
-test:do_execsql_test(
- 6.1,
- [[
- DROP TABLE IF EXISTS t1;
- CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a TEXT , b INT );
- CREATE INDEX i1 ON t1(a);
- CREATE INDEX i2 ON t1(b);
- INSERT INTO t1 VALUES(null, 1, 1);
- INSERT INTO t1 VALUES(null, 2, 2);
- INSERT INTO t1 VALUES(null, 3, 3);
- 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;
- ]])
-
-test:do_execsql_test(
- 6.2,
- [[
- SELECT * FROM t1 WHERE a = 'abc';
- ]])
-
----------------------------------------------------------------------------
--- The following tests experiment with adding corrupted records to the
--- 'sample' column of the _sql_stat4 table.
---
-local get_pk = function (space, record)
- local pkey = {}
- for _, part in pairs(space.index[0].parts) do
- table.insert(pkey, record[part.fieldno])
- end
- return pkey
-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 record_new = {}
- for i = 1,#record-1 do record_new[i] = record[i] end
- record_new[#record] = ''
- space:insert(record_new)
- return 0
-end
-
-box.internal.sql_create_function("inject_stat_error", "INT", inject_stat_error_func)
-
-test:do_execsql_test(
- 7.1,
- [[
- DROP TABLE IF EXISTS t1;
- CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a INT , b INT );
- CREATE INDEX i1 ON t1(a, b);
- INSERT INTO t1 VALUES(null, 1, 1);
- INSERT INTO t1 VALUES(null, 2, 2);
- INSERT INTO t1 VALUES(null, 3, 3);
- INSERT INTO t1 VALUES(null, 4, 4);
- INSERT INTO t1 VALUES(null, 5, 5);
- ANALYZE;
- SELECT inject_stat_error('_sql_stat4');
- ANALYZE;
- ]])
-
--- Doesn't work due to the fact that in Tarantool rowid has been removed,
--- and tbl, idx and sample have been united into primary key.
--- test:do_execsql_test(
--- 7.2,
--- [[
--- UPDATE _sql_stat4 SET sample = X'FFFF';
--- ANALYZE;
--- SELECT * FROM t1 WHERE a = 1;
--- ]], {
--- -- <7.2>
--- 1, 1
--- -- </7.2>
--- })
-
-test:do_execsql_test(
- 7.3,
- [[
- UPDATE "_sql_stat4" SET "neq" = '0 0 0';
- ANALYZE;
- SELECT * FROM t1 WHERE a = 1;
- ]], {
- -- <7.3>
- 1, 1, 1
- -- </7.3>
- })
-
-test:do_execsql_test(
- 7.4,
- [[
- ANALYZE;
- UPDATE "_sql_stat4" SET "ndlt" = '0 0 0';
- ANALYZE;
- SELECT * FROM t1 WHERE a = 3;
- ]], {
- -- <7.4>
- 3, 3, 3
- -- </7.4>
- })
-
-test:do_execsql_test(
- 7.5,
- [[
- ANALYZE;
- UPDATE "_sql_stat4" SET "nlt" = '0 0 0';
- ANALYZE;
- SELECT * FROM t1 WHERE a = 5;
- ]], {
- -- <7.5>
- 5, 5, 5
- -- </7.5>
- })
-
---------------------------------------------------------------------------
--
test:do_execsql_test(
@@ -1031,119 +983,6 @@ test:do_execsql_test(
})
---------------------------------------------------------------------------
--- Test that nothing untoward happens if the stat4 table contains entries
--- for indexes that do not exist.
--- Or NULL values in any of the other columns except for PK.
---
-test:do_execsql_test(
- 15.1,
- [[
- DROP TABLE IF EXISTS x1;
- CREATE TABLE x1(a INT PRIMARY KEY, b INT , UNIQUE(a, b));
- INSERT INTO x1 VALUES(1, 2);
- INSERT INTO x1 VALUES(3, 4);
- INSERT INTO x1 VALUES(5, 6);
- ANALYZE;
- INSERT INTO "_sql_stat4" VALUES('x1', 'abc', '', '', '', '');
- ]])
-
-test:do_execsql_test(
- 15.2,
- [[
- SELECT * FROM x1;
- ]], {
- -- <15.2>
- 1, 2, 3, 4, 5, 6
- -- </15.2>
- })
-
-test:do_execsql_test(
- 15.3,
- [[
- INSERT INTO "_sql_stat4" VALUES('42', '42', '42', '42', '42', '42');
- ]])
-
-test:do_execsql_test(
- 15.4,
- [[
- SELECT * FROM x1;
- ]], {
- -- <15.4>
- 1, 2, 3, 4, 5, 6
- -- </15.4>
- })
-
-local inject_stat_error_func = function (space_name)
- local space = box.space[space_name]
- local stats = space:select()
- for _, stat in pairs(stats) do
- space:delete(get_pk(space, stat))
- local new_tuple = {"no such tbl"}
- for i=2,#stat do
- table.insert(new_tuple, stat[i])
- end
- space:insert(new_tuple)
- end
- return 0
-end
-
-box.internal.sql_create_function("inject_stat_error", "INT", inject_stat_error_func)
-
-
-test:do_execsql_test(
- 15.7,
- [[
- ANALYZE;
- SELECT inject_stat_error('_sql_stat1');
- ]])
-
-test:do_execsql_test(
- 15.8,
- [[
- SELECT * FROM x1 ;
- ]], {
- -- <15.8>
- 1, 2, 3, 4, 5, 6
- -- </15.8>
- })
-
--- Tarantool: this test seems to be useless. There's no reason
--- for these fields to be nullable.
--- test:do_execsql_test(
--- 15.9,
--- [[
--- ANALYZE;
--- UPDATE "_sql_stat4" SET "neq" = NULL, "nlt" = NULL, "ndlt" = NULL;
--- ]])
-
-test:do_execsql_test(
- 15.10,
- [[
- SELECT * FROM x1;
- ]], {
- -- <15.10>
- 1, 2, 3, 4, 5, 6
- -- </15.10>
- })
-
--- This is just for coverage....
-test:do_execsql_test(
- 15.11,
- [[
- ANALYZE;
- UPDATE "_sql_stat1" SET "stat" = "stat" || ' unordered';
- ]])
-
-test:do_execsql_test(
- 15.12,
- [[
- SELECT * FROM x1;
- ]], {
- -- <15.12>
- 1, 2, 3, 4, 5, 6
- -- </15.12>
- })
----------------------------------------------------------------------------
-- Test that stat4 data may be used with partial indexes.
--
test:do_test(
diff --git a/test/sql-tap/analyzeC.test.lua b/test/sql-tap/analyzeC.test.lua
deleted file mode 100755
index 16d6233de..000000000
--- a/test/sql-tap/analyzeC.test.lua
+++ /dev/null
@@ -1,278 +0,0 @@
-#!/usr/bin/env tarantool
-test = require("sqltester")
-test:plan(20)
-
-testprefix = "analyzeC"
-
-
---!./tcltestrunner.lua
--- 2014-07-22
---
--- The author disclaims copyright to this source code. In place of
--- a legal notice, here is a blessing:
---
--- May you do good and not evil.
--- May you find forgiveness for yourself and forgive others.
--- May you share freely, never taking more than you give.
---
--------------------------------------------------------------------------
---
--- This file contains automated tests used to verify that the text terms
--- at the end of "_sql_stat1".stat are processed correctly.
---
--- (1) "unordered" means that the index cannot be used for ORDER BY
--- or for range queries
---
--- (2) "sz=NNN" sets the relative size of the index entries
---
--- (3) All other fields are silently ignored
---
--- Baseline case. Range queries work OK. Indexes can be used for
--- ORDER BY.
-
-test:do_execsql_test(
- 1.0,
- [[
- DROP TABLE IF EXISTS t1;
- CREATE TABLE t1(a INT PRIMARY KEY, b INT , c INT , d INT );
- INSERT INTO t1(a,b,c,d) VALUES(1,1,2,3),(2,7,8,9),(3,4,5,6),(4,10,11,12),(5,4,8,12),(6,1,11,111);
- CREATE INDEX t1b ON t1(b);
- 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');
- ANALYZE;
- SELECT b,c,d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;
- ]], {
- -- <1.0>
- 4, 5, 6, "#", 7, 8, 9, "#", 4, 8, 12, "#"
- -- </1.0>
- })
-
-test:do_execsql_test(
- 1.1,
- [[
- EXPLAIN QUERY PLAN SELECT b, c, d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;
- ]], {
- -- <1.1>
- 0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX T1B (B>? AND B<?)",
- 0, 0, 0, "USE TEMP B-TREE FOR ORDER BY"
- -- </1.1>
- })
-
-test:do_execsql_test(
- 1.2,
- [[
- SELECT d FROM t1 ORDER BY b;
- ]], {
- -- <1.2>
- 3, 111, 6, 12, 9, 12
- -- </1.2>
- })
-
-test:do_execsql_test(
- 1.3,
- [[
- EXPLAIN QUERY PLAN SELECT d FROM t1 ORDER BY b;
- ]], {
- -- <1.3>
- 0, 0, 0, "SCAN TABLE T1 USING COVERING INDEX T1B"
- -- </1.3>
- })
-
--- Now mark the t1a index as "unordered". Range queries and ORDER BY no
--- longer use the index, but equality queries do.
---
-test:do_execsql_test(
- 2.0,
- [[
- UPDATE "_sql_stat1" SET "stat"='12345 2 unordered' WHERE "idx"='t1b';
- ANALYZE;
- SELECT b, c, d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;
- ]], {
- -- <2.0>
- 4, 5, 6, "#", 7, 8, 9, "#", 4, 8, 12, "#"
- -- </2.0>
- })
-
-test:do_execsql_test(
- 2.1,
- [[
- EXPLAIN QUERY PLAN SELECT b, c, d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;
- ]], {
- -- <2.1>
- 0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX T1B (B>? AND B<?)",
- 0, 0, 0, "USE TEMP B-TREE FOR ORDER BY"
- -- </2.1>
- })
-
-test:do_execsql_test(
- 2.2,
- [[
- SELECT d FROM t1 ORDER BY b;
- ]], {
- -- <2.2>
- 3, 111, 6, 12, 9, 12
- -- </2.2>
- })
-
-test:do_execsql_test(
- 2.3,
- [[
- EXPLAIN QUERY PLAN SELECT d FROM t1 ORDER BY b;
- ]], {
- -- <2.3>
- 0, 0, 0, "SCAN TABLE T1 USING COVERING INDEX T1B"
- -- </2.3>
- })
-
--- Ignore extraneous text parameters in the "_sql_stat1".stat field.
---
-test:do_execsql_test(
- 3.0,
- [[
- UPDATE "_sql_stat1" SET "stat"='12345 2 whatever=5 unordered xyzzy=11' WHERE "idx"='t1b';
- ANALYZE;
- SELECT b, c, d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;
- ]], {
- -- <3.0>
- 4, 5, 6, "#", 7, 8, 9, "#", 4, 8, 12, "#"
- -- </3.0>
- })
-
-test:do_execsql_test(
- 3.1,
- [[
- EXPLAIN QUERY PLAN SELECT b, c, d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;
- ]], {
- -- <3.1>
- 0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX T1B (B>? AND B<?)",
- 0, 0, 0, "USE TEMP B-TREE FOR ORDER BY"
- -- </3.1>
- })
-
-test:do_execsql_test(
- 3.2,
- [[
- SELECT d FROM t1 ORDER BY b;
- ]], {
- -- <3.2>
- 3, 111, 6, 12, 9, 12
- -- </3.2>
- })
-
-test:do_execsql_test(
- 3.3,
- [[
- EXPLAIN QUERY PLAN SELECT d FROM t1 ORDER BY b;
- ]], {
- -- <3.3>
- 0, 0, 0, "SCAN TABLE T1 USING COVERING INDEX T1B"
- -- </3.3>
- })
-
--- The sz=NNN parameter determines which index to scan
---
-test:do_execsql_test(
- 4.0,
- [[
- DROP INDEX t1b ON t1;
- CREATE INDEX t1bc ON t1(b,c);
- CREATE INDEX t1db ON t1(d,b);
- DELETE FROM "_sql_stat1";
- INSERT INTO "_sql_stat1"("tbl","idx","stat") VALUES('t1','t1bc','12345 3 2 sz=10'),('t1','t1db','12345 3 2 sz=20');
- ANALYZE;
- SELECT count(b) FROM t1;
- ]], {
- -- <4.0>
- 6
- -- </4.0>
- })
-
-test:do_execsql_test(
- 4.1,
- [[
- EXPLAIN QUERY PLAN SELECT count(b) FROM t1;
- ]], {
- -- <4.1>
- 0, 0, 0, "SCAN TABLE T1"
- -- </4.1>
- })
-
-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');
- ANALYZE;
- SELECT count(b) FROM t1;
- ]], {
- -- <4.2>
- 6
- -- </4.2>
- })
-
-test:do_execsql_test(
- 4.3,
- [[
- EXPLAIN QUERY PLAN SELECT count(b) FROM t1;
- ]], {
- -- <4.3>
- 0, 0, 0, "SCAN TABLE T1"
- -- </4.3>
- })
-
--- The sz=NNN parameter works even if there is other extraneous text
--- in the sql_stat1.stat column.
---
-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');
- ANALYZE;
- SELECT count(b) FROM t1;
- ]], {
- -- <5.0>
- 6
- -- </5.0>
- })
-
-test:do_execsql_test(
- 5.1,
- [[
- EXPLAIN QUERY PLAN
- SELECT count(b) FROM t1;
- ]], {
- -- <5.1>
- 0, 0, 0, "SCAN TABLE T1"
- -- </5.1>
- })
-
-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');
- ANALYZE;
- SELECT count(b) FROM t1;
- ]], {
- -- <5.2>
- 6
- -- </5.2>
- })
-
-test:do_execsql_test(
- 5.3,
- [[
- EXPLAIN QUERY PLAN SELECT count(b) FROM t1;
- ]], {
- -- <5.3>
- 0, 0, 0, "SCAN TABLE T1"
- -- </5.3>
- })
-
-
-test:finish_test()
diff --git a/test/sql-tap/gh-3350-skip-scan.test.lua b/test/sql-tap/gh-3350-skip-scan.test.lua
index 4cecfe081..29889ab25 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(3)
local function lindex(str, pos)
return str:sub(pos+1, pos+1)
@@ -77,32 +77,5 @@ test:do_execsql_test(
}
)
-test:do_execsql_test(
- "skip-scan-1.4",
- [[
- DROP TABLE IF EXISTS t1;
- CREATE TABLE t1(id INTEGER PRIMARY KEY, a TEXT, b INT, c INT, d INT);
- CREATE INDEX t1abc ON t1(a,b,c);
- DROP TABLE IF EXISTS t2;
- CREATE TABLE t2(id INTEGER PRIMARY KEY);
- INSERT INTO t2 VALUES(1);
- INSERT INTO t1 VALUES(1, 'abc',123,4,5);
- INSERT INTO t1 VALUES(2, 'abc',234,5,6);
- INSERT INTO t1 VALUES(3, 'abc',234,6,7);
- INSERT INTO t1 VALUES(4, 'abc',345,7,8);
- INSERT INTO t1 VALUES(5, 'def',567,8,9);
- INSERT INTO t1 VALUES(6, 'def',345,9,10);
- INSERT INTO t1 VALUES(7, 'bcd',100,6,11);
- ANALYZE;
- DELETE FROM "_sql_stat1";
- DELETE FROM "_sql_stat4";
- INSERT INTO "_sql_stat1" VALUES('T1','T1ABC','10000 5000 2000 10');
- ANALYZE t2;
- SELECT a,b,c,d FROM t1 WHERE b=345;
- ]], {
- "abc", 345, 7, 8, "def", 345, 9, 10
- }
-)
-
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/sql-statN.result b/test/sql/sql-statN.result
new file mode 100644
index 000000000..a656cfc2b
--- /dev/null
+++ b/test/sql/sql-statN.result
@@ -0,0 +1,437 @@
+test_run = require('test_run').new()
+---
+...
+engine = test_run:get_cfg('engine')
+---
+...
+box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+---
+...
+-- Check 'unordered' in "_sql_stat1".
+box.sql.execute("CREATE TABLE x1(a INT PRIMARY KEY, b INT , UNIQUE(a, b))")
+---
+...
+box.sql.execute("INSERT INTO x1 VALUES(1, 2)")
+---
+...
+box.sql.execute("INSERT INTO x1 VALUES(3, 4)")
+---
+...
+box.sql.execute("INSERT INTO x1 VALUES(5, 6)")
+---
+...
+box.sql.execute("ANALYZE")
+---
+...
+box.sql.execute("SELECT * FROM x1")
+---
+- - [1, 2]
+ - [3, 4]
+ - [5, 6]
+...
+box.sql.execute("ANALYZE")
+---
+...
+_sql_stat1 = box.space._sql_stat1
+---
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+function add_to_stat1(txt)
+ for _, tuple in _sql_stat1:pairs() do
+ local temp_table = {}
+ for _, v in pairs(tuple['stat']) do
+ table.insert(temp_table, v)
+ end
+ table.insert(temp_table, txt)
+ _sql_stat1:update(tuple:transform(3, 3), {{'=', 3, temp_table}})
+ end
+end;
+---
+...
+test_run:cmd("setopt delimiter ''");
+---
+- true
+...
+add_to_stat1('unordered')
+---
+...
+test_run:cmd('restart server default');
+box.sql.execute("ANALYZE")
+---
+...
+box.sql.execute("SELECT * FROM x1")
+---
+- - [1, 2]
+ - [3, 4]
+ - [5, 6]
+...
+-- Clean up.
+box.sql.execute("DROP TABLE x1")
+---
+...
+-- Check analyzeC test.
+box.sql.execute("DROP TABLE IF EXISTS t1;")
+---
+...
+box.sql.execute("CREATE TABLE t1(a INT PRIMARY KEY, b INT , c INT , d INT );")
+---
+...
+box.sql.execute("INSERT INTO t1(a,b,c,d) VALUES(1,1,2,3),(2,7,8,9),(3,4,5,6),(4,10,11,12),(5,4,8,12),(6,1,11,111);")
+---
+...
+box.sql.execute("CREATE INDEX t1b ON t1(b);")
+---
+...
+box.sql.execute("CREATE INDEX t1c ON t1(c);")
+---
+...
+box.sql.execute("ANALYZE;")
+---
+...
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+---
+- - ['T1', 'T1', [6, 1]]
+ - ['T1', 'T1B', [6, 2]]
+ - ['T1', 'T1C', [6, 2]]
+...
+_sql_stat1 = box.space._sql_stat1
+---
+...
+_sql_stat1:update({'T1', 'T1B'}, {{'=', 3, {12345, 2}}})
+---
+- ['T1', 'T1B', [12345, 2], 10]
+...
+_sql_stat1:update({'T1', 'T1C'}, {{'=', 3, {12345, 4}}})
+---
+- ['T1', 'T1C', [12345, 4], 11]
+...
+box.sql.execute("SELECT * FROM \"_sql_stat1\"")
+---
+- - ['T1', 'T1', [6, 1]]
+ - ['T1', 'T1B', [12345, 2]]
+ - ['T1', 'T1C', [12345, 4]]
+...
+test_run:cmd('restart server default');
+box.sql.execute("SELECT * FROM \"_sql_stat1\"")
+---
+- - ['T1', 'T1', [6, 1]]
+ - ['T1', 'T1B', [12345, 2]]
+ - ['T1', 'T1C', [12345, 4]]
+...
+_sql_stat1 = box.space._sql_stat1
+---
+...
+box.sql.execute("ANALYZE;")
+---
+...
+box.sql.execute("SELECT b,c,d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;")
+---
+- - [4, 5, 6, '#']
+ - [7, 8, 9, '#']
+ - [4, 8, 12, '#']
+...
+box.sql.execute("EXPLAIN QUERY PLAN SELECT b, c, d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;")
+---
+- - [0, 0, 0, 'SEARCH TABLE T1 USING COVERING INDEX T1B (B>? AND B<?)']
+ - [0, 0, 0, 'USE TEMP B-TREE FOR ORDER BY']
+...
+box.sql.execute("SELECT d FROM t1 ORDER BY b;")
+---
+- - [3]
+ - [111]
+ - [6]
+ - [12]
+ - [9]
+ - [12]
+...
+box.sql.execute("EXPLAIN QUERY PLAN SELECT d FROM t1 ORDER BY b;")
+---
+- - [0, 0, 0, 'SCAN TABLE T1 USING COVERING INDEX T1B']
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+function add_to_stat1(txt)
+ for _, tuple in _sql_stat1:pairs() do
+ local temp_table = {}
+ for _, v in pairs(tuple['stat']) do
+ table.insert(temp_table, v)
+ end
+ table.insert(temp_table, txt)
+ _sql_stat1:update(tuple:transform(3, 3), {{'=', 3, temp_table}})
+ end
+end;
+---
+...
+test_run:cmd("setopt delimiter ''");
+---
+- true
+...
+add_to_stat1('unordered')
+---
+...
+test_run:cmd('restart server default');
+box.sql.execute("ANALYZE;")
+---
+...
+box.sql.execute("SELECT b, c, d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;")
+---
+- - [4, 5, 6, '#']
+ - [7, 8, 9, '#']
+ - [4, 8, 12, '#']
+...
+-- Ignore extraneous text parameters in the "_sql_stat1" stat field.
+_sql_stat1 = box.space._sql_stat1
+---
+...
+_sql_stat1:update({'T1', 'T1'}, {{'=', 3, {'whatever=5', 'unordered', 'xyzzy = 11'}}})
+---
+- ['T1', 'T1', ['whatever=5', 'unordered', 'xyzzy = 11'], 1]
+...
+_sql_stat1:update({'T1', 'T1B'}, {{'=', 3, {'whatever=5', 'unordered', 'xyzzy = 11'}}})
+---
+- ['T1', 'T1B', ['whatever=5', 'unordered', 'xyzzy = 11'], 10]
+...
+_sql_stat1:update({'T1', 'T1C'}, {{'=', 3, {'whatever=5', 'unordered', 'xyzzy = 11'}}})
+---
+- ['T1', 'T1C', ['whatever=5', 'unordered', 'xyzzy = 11'], 11]
+...
+test_run:cmd('restart server default');
+box.sql.execute("ANALYZE;")
+---
+...
+box.sql.execute("SELECT b, c, d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;")
+---
+- - [4, 5, 6, '#']
+ - [7, 8, 9, '#']
+ - [4, 8, 12, '#']
+...
+box.sql.execute("EXPLAIN QUERY PLAN SELECT b, c, d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;")
+---
+- - [0, 0, 0, 'SEARCH TABLE T1 USING COVERING INDEX T1B (B>? AND B<?)']
+ - [0, 0, 0, 'USE TEMP B-TREE FOR ORDER BY']
+...
+box.sql.execute("SELECT d FROM t1 ORDER BY b;")
+---
+- - [3]
+ - [111]
+ - [6]
+ - [12]
+ - [9]
+ - [12]
+...
+box.sql.execute("SELECT d FROM t1 ORDER BY b;")
+---
+- - [3]
+ - [111]
+ - [6]
+ - [12]
+ - [9]
+ - [12]
+...
+-- The sz=NNN parameter determines which index to scan
+box.sql.execute("DROP INDEX t1b ON t1;")
+---
+...
+box.sql.execute("CREATE INDEX t1bc ON t1(b,c);")
+---
+...
+box.sql.execute("CREATE INDEX t1db ON t1(d,b);")
+---
+...
+box.sql.execute("ANALYZE")
+---
+...
+_sql_stat1 = box.space._sql_stat1
+---
+...
+_sql_stat1:update({'T1', 'T1BC'}, {{'=', 3, {12345, 3, 2, 'sz=10'}}})
+---
+- ['T1', 'T1BC', [12345, 3, 2, 'sz=10'], 10]
+...
+_sql_stat1:update({'T1', 'T1DB'}, {{'=', 3, {12345, 3, 2, 'sz=20'}}})
+---
+- ['T1', 'T1DB', [12345, 3, 2, 'sz=20'], 111]
+...
+test_run:cmd('restart server default');
+box.sql.execute("ANALYZE;")
+---
+...
+box.sql.execute("SELECT count(b) FROM t1;")
+---
+- - [6]
+...
+box.sql.execute("EXPLAIN QUERY PLAN SELECT count(b) FROM t1;")
+---
+- - [0, 0, 0, 'SCAN TABLE T1']
+...
+-- The sz=NNN parameter works even if there is other extraneous text
+-- in the sql_stat1.stat column.
+_sql_stat1 = box.space._sql_stat1
+---
+...
+_sql_stat1:update({'T1', 'T1BC'}, {{'=', 3, {12345, 3, 2, 'x=5', 'sz=10', 'y=10'}}})
+---
+- ['T1', 'T1BC', [12345, 3, 2, 'x=5', 'sz=10', 'y=10'], 10]
+...
+_sql_stat1:update({'T1', 'T1DB'}, {{'=', 3, {12345, 3, 2, 'whatever', 'sz=20', 'junk'}}})
+---
+- ['T1', 'T1DB', [12345, 3, 2, 'whatever', 'sz=20', 'junk'], 111]
+...
+test_run:cmd('restart server default');
+box.sql.execute("ANALYZE;")
+---
+...
+box.sql.execute("SELECT count(b) FROM t1;")
+---
+- - [6]
+...
+box.sql.execute("EXPLAIN QUERY PLAN SELECT count(b) FROM t1;")
+---
+- - [0, 0, 0, 'SCAN TABLE T1']
+...
+-- The following tests experiment with adding corrupted records to the
+-- 'sample' column of the _sql_stat4 table.
+box.sql.execute("DROP TABLE IF EXISTS t1;")
+---
+...
+box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a INT , b INT );")
+---
+...
+box.sql.execute("CREATE INDEX i1 ON t1(a, b);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(null, 1, 1);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(null, 2, 2);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(null, 3, 3);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(null, 4, 4);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(null, 5, 5);")
+---
+...
+box.sql.execute("ANALYZE;")
+---
+...
+_sql_stat4 = box.space._sql_stat4
+---
+...
+_sql_stat4:delete{'T1', 'I1', _sql_stat4:select()[1][6]}
+---
+...
+_sql_stat4:insert{'T1', 'I1', {1, 1}, {0, 0}, {0, 0}, ''}
+---
+- ['T1', 'I1', [1, 1], [0, 0], [0, 0], '']
+...
+test_run:cmd('restart server default');
+box.sql.execute("ANALYZE;")
+---
+...
+_sql_stat4 = box.space._sql_stat4
+---
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+function update_stat4_fields(field_num, val)
+ for i,t in _sql_stat4:pairs() do
+ _sql_stat4:update(t:transform(3, 3), {{'=', field_num, val}})
+ end
+end;
+---
+...
+test_run:cmd("setopt delimiter ''")
+update_stat4_fields(3, {0, 0, 0})
+
+test_run:cmd('restart server default');
+box.sql.execute("ANALYZE;")
+---
+...
+box.sql.execute("SELECT * FROM t1 WHERE a = 1;")
+---
+- - [1, 1, 1]
+...
+-- Skip-scan test.
+box.sql.execute("DROP TABLE IF EXISTS t1;")
+---
+...
+box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, a TEXT, b INT, c INT, d INT);")
+---
+...
+box.sql.execute("CREATE INDEX t1abc ON t1(a,b,c);")
+---
+...
+box.sql.execute("DROP TABLE IF EXISTS t2;")
+---
+...
+box.sql.execute("CREATE TABLE t2(id INTEGER PRIMARY KEY);")
+---
+...
+box.sql.execute("INSERT INTO t2 VALUES(1);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(1, 'abc',123,4,5);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(2, 'abc',234,5,6);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(3, 'abc',234,6,7);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(4, 'abc',345,7,8);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(5, 'def',567,8,9);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(6, 'def',345,9,10);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(7, 'bcd',100,6,11);")
+---
+...
+box.sql.execute("ANALYZE;")
+---
+...
+box.sql.execute("DELETE FROM \"_sql_stat1\";")
+---
+...
+box.sql.execute("DELETE FROM \"_sql_stat4\";")
+---
+...
+_sql_stat1 = box.space._sql_stat1
+---
+...
+_sql_stat1:insert{'T1','T1ABC', {10000,5000,2000,10}}
+---
+- ['T1', 'T1ABC', [10000, 5000, 2000, 10]]
+...
+test_run:cmd('restart server default');
+box.sql.execute("ANALYZE t2;")
+---
+...
+box.sql.execute("SELECT a,b,c,d FROM t1 WHERE b=345;")
+---
+- - ['abc', 345, 7, 8]
+ - ['def', 345, 9, 10]
+...
+-- Clean up.
+box.sql.execute("DROP TABLE IF EXISTS t1;")
+---
+...
+box.sql.execute("DROP TABLE IF EXISTS t2;")
+---
+...
+test_run:cmd('restart server default with cleanup=1');
diff --git a/test/sql/sql-statN.test.lua b/test/sql/sql-statN.test.lua
new file mode 100644
index 000000000..b2402aa8f
--- /dev/null
+++ b/test/sql/sql-statN.test.lua
@@ -0,0 +1,193 @@
+test_run = require('test_run').new()
+engine = test_run:get_cfg('engine')
+box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+
+-- Check 'unordered' in "_sql_stat1".
+box.sql.execute("CREATE TABLE x1(a INT PRIMARY KEY, b INT , UNIQUE(a, b))")
+box.sql.execute("INSERT INTO x1 VALUES(1, 2)")
+box.sql.execute("INSERT INTO x1 VALUES(3, 4)")
+box.sql.execute("INSERT INTO x1 VALUES(5, 6)")
+box.sql.execute("ANALYZE")
+box.sql.execute("SELECT * FROM x1")
+box.sql.execute("ANALYZE")
+
+_sql_stat1 = box.space._sql_stat1
+test_run:cmd("setopt delimiter ';'")
+function add_to_stat1(txt)
+ for _, tuple in _sql_stat1:pairs() do
+ local temp_table = {}
+ for _, v in pairs(tuple['stat']) do
+ table.insert(temp_table, v)
+ end
+ table.insert(temp_table, txt)
+ _sql_stat1:update(tuple:transform(3, 3), {{'=', 3, temp_table}})
+ end
+end;
+test_run:cmd("setopt delimiter ''");
+add_to_stat1('unordered')
+
+test_run:cmd('restart server default');
+
+box.sql.execute("ANALYZE")
+box.sql.execute("SELECT * FROM x1")
+
+-- Clean up.
+box.sql.execute("DROP TABLE x1")
+
+-- Check analyzeC test.
+box.sql.execute("DROP TABLE IF EXISTS t1;")
+box.sql.execute("CREATE TABLE t1(a INT PRIMARY KEY, b INT , c INT , d INT );")
+box.sql.execute("INSERT INTO t1(a,b,c,d) VALUES(1,1,2,3),(2,7,8,9),(3,4,5,6),(4,10,11,12),(5,4,8,12),(6,1,11,111);")
+box.sql.execute("CREATE INDEX t1b ON t1(b);")
+box.sql.execute("CREATE INDEX t1c ON t1(c);")
+box.sql.execute("ANALYZE;")
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+
+_sql_stat1 = box.space._sql_stat1
+_sql_stat1:update({'T1', 'T1B'}, {{'=', 3, {12345, 2}}})
+_sql_stat1:update({'T1', 'T1C'}, {{'=', 3, {12345, 4}}})
+
+box.sql.execute("SELECT * FROM \"_sql_stat1\"")
+
+test_run:cmd('restart server default');
+
+box.sql.execute("SELECT * FROM \"_sql_stat1\"")
+
+_sql_stat1 = box.space._sql_stat1
+
+box.sql.execute("ANALYZE;")
+box.sql.execute("SELECT b,c,d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;")
+
+box.sql.execute("EXPLAIN QUERY PLAN SELECT b, c, d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;")
+box.sql.execute("SELECT d FROM t1 ORDER BY b;")
+box.sql.execute("EXPLAIN QUERY PLAN SELECT d FROM t1 ORDER BY b;")
+
+test_run:cmd("setopt delimiter ';'")
+function add_to_stat1(txt)
+ for _, tuple in _sql_stat1:pairs() do
+ local temp_table = {}
+ for _, v in pairs(tuple['stat']) do
+ table.insert(temp_table, v)
+ end
+ table.insert(temp_table, txt)
+ _sql_stat1:update(tuple:transform(3, 3), {{'=', 3, temp_table}})
+ end
+end;
+test_run:cmd("setopt delimiter ''");
+add_to_stat1('unordered')
+
+test_run:cmd('restart server default');
+
+box.sql.execute("ANALYZE;")
+box.sql.execute("SELECT b, c, d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;")
+
+-- Ignore extraneous text parameters in the "_sql_stat1" stat field.
+_sql_stat1 = box.space._sql_stat1
+_sql_stat1:update({'T1', 'T1'}, {{'=', 3, {'whatever=5', 'unordered', 'xyzzy = 11'}}})
+_sql_stat1:update({'T1', 'T1B'}, {{'=', 3, {'whatever=5', 'unordered', 'xyzzy = 11'}}})
+_sql_stat1:update({'T1', 'T1C'}, {{'=', 3, {'whatever=5', 'unordered', 'xyzzy = 11'}}})
+
+test_run:cmd('restart server default');
+
+box.sql.execute("ANALYZE;")
+box.sql.execute("SELECT b, c, d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;")
+box.sql.execute("EXPLAIN QUERY PLAN SELECT b, c, d, '#' FROM t1 WHERE b BETWEEN 3 AND 8 ORDER BY d;")
+box.sql.execute("SELECT d FROM t1 ORDER BY b;")
+box.sql.execute("SELECT d FROM t1 ORDER BY b;")
+
+-- The sz=NNN parameter determines which index to scan
+box.sql.execute("DROP INDEX t1b ON t1;")
+box.sql.execute("CREATE INDEX t1bc ON t1(b,c);")
+box.sql.execute("CREATE INDEX t1db ON t1(d,b);")
+box.sql.execute("ANALYZE")
+
+_sql_stat1 = box.space._sql_stat1
+_sql_stat1:update({'T1', 'T1BC'}, {{'=', 3, {12345, 3, 2, 'sz=10'}}})
+_sql_stat1:update({'T1', 'T1DB'}, {{'=', 3, {12345, 3, 2, 'sz=20'}}})
+
+test_run:cmd('restart server default');
+
+box.sql.execute("ANALYZE;")
+box.sql.execute("SELECT count(b) FROM t1;")
+box.sql.execute("EXPLAIN QUERY PLAN SELECT count(b) FROM t1;")
+
+-- The sz=NNN parameter works even if there is other extraneous text
+-- in the sql_stat1.stat column.
+
+_sql_stat1 = box.space._sql_stat1
+_sql_stat1:update({'T1', 'T1BC'}, {{'=', 3, {12345, 3, 2, 'x=5', 'sz=10', 'y=10'}}})
+_sql_stat1:update({'T1', 'T1DB'}, {{'=', 3, {12345, 3, 2, 'whatever', 'sz=20', 'junk'}}})
+
+test_run:cmd('restart server default');
+
+box.sql.execute("ANALYZE;")
+box.sql.execute("SELECT count(b) FROM t1;")
+
+box.sql.execute("EXPLAIN QUERY PLAN SELECT count(b) FROM t1;")
+
+-- The following tests experiment with adding corrupted records to the
+-- 'sample' column of the _sql_stat4 table.
+box.sql.execute("DROP TABLE IF EXISTS t1;")
+box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a INT , b INT );")
+box.sql.execute("CREATE INDEX i1 ON t1(a, b);")
+box.sql.execute("INSERT INTO t1 VALUES(null, 1, 1);")
+box.sql.execute("INSERT INTO t1 VALUES(null, 2, 2);")
+box.sql.execute("INSERT INTO t1 VALUES(null, 3, 3);")
+box.sql.execute("INSERT INTO t1 VALUES(null, 4, 4);")
+box.sql.execute("INSERT INTO t1 VALUES(null, 5, 5);")
+box.sql.execute("ANALYZE;")
+
+_sql_stat4 = box.space._sql_stat4
+_sql_stat4:delete{'T1', 'I1', _sql_stat4:select()[1][6]}
+_sql_stat4:insert{'T1', 'I1', {1, 1}, {0, 0}, {0, 0}, ''}
+
+test_run:cmd('restart server default');
+
+box.sql.execute("ANALYZE;")
+
+_sql_stat4 = box.space._sql_stat4
+test_run:cmd("setopt delimiter ';'")
+function update_stat4_fields(field_num, val)
+ for i,t in _sql_stat4:pairs() do
+ _sql_stat4:update(t:transform(3, 3), {{'=', field_num, val}})
+ end
+end;
+test_run:cmd("setopt delimiter ''")
+update_stat4_fields(3, {0, 0, 0})
+
+test_run:cmd('restart server default');
+
+box.sql.execute("ANALYZE;")
+box.sql.execute("SELECT * FROM t1 WHERE a = 1;")
+
+-- Skip-scan test.
+box.sql.execute("DROP TABLE IF EXISTS t1;")
+box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, a TEXT, b INT, c INT, d INT);")
+box.sql.execute("CREATE INDEX t1abc ON t1(a,b,c);")
+box.sql.execute("DROP TABLE IF EXISTS t2;")
+box.sql.execute("CREATE TABLE t2(id INTEGER PRIMARY KEY);")
+box.sql.execute("INSERT INTO t2 VALUES(1);")
+box.sql.execute("INSERT INTO t1 VALUES(1, 'abc',123,4,5);")
+box.sql.execute("INSERT INTO t1 VALUES(2, 'abc',234,5,6);")
+box.sql.execute("INSERT INTO t1 VALUES(3, 'abc',234,6,7);")
+box.sql.execute("INSERT INTO t1 VALUES(4, 'abc',345,7,8);")
+box.sql.execute("INSERT INTO t1 VALUES(5, 'def',567,8,9);")
+box.sql.execute("INSERT INTO t1 VALUES(6, 'def',345,9,10);")
+box.sql.execute("INSERT INTO t1 VALUES(7, 'bcd',100,6,11);")
+box.sql.execute("ANALYZE;")
+box.sql.execute("DELETE FROM \"_sql_stat1\";")
+box.sql.execute("DELETE FROM \"_sql_stat4\";")
+
+_sql_stat1 = box.space._sql_stat1
+_sql_stat1:insert{'T1','T1ABC', {10000,5000,2000,10}}
+
+test_run:cmd('restart server default');
+
+box.sql.execute("ANALYZE t2;")
+box.sql.execute("SELECT a,b,c,d FROM t1 WHERE b=345;")
+
+-- Clean up.
+box.sql.execute("DROP TABLE IF EXISTS t1;")
+box.sql.execute("DROP TABLE IF EXISTS t2;")
+test_run:cmd('restart server default with cleanup=1');
+
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})
---
More information about the Tarantool-patches
mailing list