From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4067D26FF7 for ; Mon, 11 Mar 2019 21:10:14 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id apOOrTf7cJKu for ; Mon, 11 Mar 2019 21:10:14 -0400 (EDT) Received: from smtp46.i.mail.ru (smtp46.i.mail.ru [94.100.177.106]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 15E9F26F5E for ; Mon, 11 Mar 2019 21:10:12 -0400 (EDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 12.2 \(3445.102.3\)) Subject: [tarantool-patches] Re: [PATCH] sql: store statistics in statN as an array of integers From: Roman Khabibov In-Reply-To: <443EE5E9-93B1-4F19-9C71-C28F97A754CB@tarantool.org> Date: Tue, 12 Mar 2019 04:10:09 +0300 Content-Transfer-Encoding: quoted-printable Message-Id: References: <443EE5E9-93B1-4F19-9C71-C28F97A754CB@tarantool.org> Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: tarantool-patches@freelists.org Cc: "n. pettik" Hi! Thanks for review. > You can simply do this: >=20 > _sql_stat1 =3D box.space._sql_stat1 Done. >> +_sql_stat1:insert{'t4', 't4i1', {'nonsense'}} >> +_sql_stat1:insert{'t4', 't4i2', {432653287412874653284129847632}} >=20 > 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=E2=80=99t 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) >=20 > 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 =3D box.space._sql_stat4 + t =3D {} + for k, v in _sql_stat4:pairs() do + table.insert(t, v) + end + + local where =3D {tbl =3D "T1", idx =3D '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 =3D 1 + for _, tuple in pairs(t) do + tuple =3D t[i] + if tuple[k] ~=3D v then + t[i] =3D nil + end + i =3D 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 =3D 0 + for _, v in pairs(t) do + res =3D res + v + end + return res + end + + local order_by =3D 'nlt' + local compare =3D function() end + if order =3D=3D 'asc' then + compare =3D function(a, b) + if sum_table(a[order_by]) <=3D sum_table(b[order_by]) then + return true + end + end + else + compare =3D 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 =3D 0 >> + for k, v in pairs(t) do >> + res =3D res + v >=20 > If you don=E2=80=99t need one of params, just skip it: >=20 > for _, v in pairs(t) do > =E2=80=A6 Done. >=20 >> + end >> + return res >> +end >> + >> +function get_tuples_where_order_by_limit(order_by, order, limit) >> + space =3D box.space[box.schema.SQL_STAT4_ID] >> + t =3D {} >> + for k, v in space:pairs() do >> + table.insert(t, v) >> + end >> + >> + local i >> + local count >=20 > Always initialise variables, it is best practice. Hope, I didn=E2=80=99t forget to do it now. >> + local where =3D {tbl =3D "T1", idx =3D 'I1'} >> + if where ~=3D 0 then >=20 > This is always true. Removed. >> + for k, v in pairs(where) do >> + i =3D 1 >> + for key, tuple in pairs(t) do >> + tuple =3D t[i] >> + if tuple[k] ~=3D v then >> + t[i] =3D nil >> + else >> + count =3D i >> + end >> + i =3D i + 1 >> + end >> + end >> + end >=20 > Can=E2=80=99t 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=E2=80=9D. >> + >> + local compare >> + if order =3D=3D 'asc' then >> + compare =3D function(a, b) >> + if sum_table(a[order_by]) <=3D sum_table(b[order_by]) = then >> + return true >> + end >> + end >> + else >> + compare =3D 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 =3D=3D nil then >> + limit =3D count >> + end >> + >> + local ret =3D '' >> + i =3D 1 >> + while i <=3D limit do >> + if i =3D=3D 1 then >> + ret =3D tostring(t[i]) >> + else >> + ret =3D ret..' '..tostring(t[i]) >> + end >> + i =3D i + 1 >> + end >> + return ret >> +end >> + >> +_sql_stat4 =3D 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"=3D'T1' and "idx"=3D'I1' ORDER BY "nlt" LIMIT 1; >> + SELECT get_tuples_where_order_by_limit('nlt', 'asc', 1); >=20 > In this particular test(s), you pass the same limit and order by > args. So I=E2=80=99d rather hardcode them to make function smaller and > simpler.=20 Done. >> ]], { >> -- >> - "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=3D]" >> -- >> }) >>=20 >>=20 >>=20 >>=20 >> @@ -184,12 +187,12 @@ test:do_execsql_test( >> ]], generate_tens(100)) >>=20 >> -- The first element in the "nEq" list of all samples should = therefore be 10. >> --- =20 >> + =20 >=20 > Extra empty line. Removed. >> test:do_execsql_test( >> "3.3.2", >> [[ >> ANALYZE; >> - SELECT lrange("neq", 1, 1) FROM "_sql_stat4" WHERE "idx" =3D = 'I2'; >> + SELECT lrange(msgpack_decode_sample("neq"), 1, 1) FROM = "_sql_stat4" WHERE "idx" =3D 'I2'; >> ]], generate_tens_str(24)) >>=20 >> = --------------------------------------------------------------------------= - >> @@ -283,31 +286,100 @@ test:do_execsql_test( >> -- >> }) >>=20 >> +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)=20 >> - FROM "_sql_stat4" WHERE "idx" =3D '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=3D], =E2=80=9C.. >=20 > 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 =3D '' + for i =3D 0, 15 do + res =3D res..'[[10, 10, 10], ['..(i * 10)..', '.. + (i * 10)..', '..(i * 10)..'], ['..i..', '..i..', '..i..'], = \''..i..' '.. + i..' '..i..'\'], ' + end + res =3D 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)=20 - FROM "_sql_stat4" WHERE "idx" =3D '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() -- }) >> + "[\'T1\', \'I1', [10, 10, 10], [10, 10, 10], [1, 1, 1], = !!binary kwEBoTE=3D], ".. >> + "[\'T1\', \'I1\', [10, 10, 10], [20, 20, 20], [2, 2, 2], = !!binary kwICoTI=3D], ".. >> + "[\'T1\', \'I1\', [10, 10, 10], [30, 30, 30], [3, 3, 3], = !!binary kwMDoTM=3D], ".. >> + "[\'T1\', \'I1\', [10, 10, 10], [40, 40, 40], [4, 4, 4], = !!binary kwQEoTQ=3D], ".. >> + "[\'T1\', \'I1\', [10, 10, 10], [50, 50, 50], [5, 5, 5], = !!binary kwUFoTU=3D], ".. >> + "[\'T1\', \'I1\', [10, 10, 10], [60, 60, 60], [6, 6, 6], = !!binary kwYGoTY=3D], ".. >> + "[\'T1\', \'I1\', [10, 10, 10], [70, 70, 70], [7, 7, 7], = !!binary kwcHoTc=3D], ".. >> + "[\'T1\', \'I1\', [10, 10, 10], [80, 80, 80], [8, 8, 8], = !!binary kwgIoTg=3D], ".. >> + "[\'T1\', \'I1\', [10, 10, 10], [90, 90, 90], [9, 9, 9], = !!binary kwkJoTk=3D], ".. >> + "[\'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]" >> -- >> }) >>=20 >> test:do_execsql_test( >> 4.4, >> [[ >> - SELECT "neq", lrange("nlt", 1, 3), lrange("ndlt", 1, 3), = lrange(msgpack_decode_sample("sample"), 1, 3)=20 >> - FROM "_sql_stat4" WHERE "idx" =3D '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]" >> -- >> }) >>=20 >> @@ -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; >> ]]) >>=20 >> +function copy_tuples(from, to) >> + for i,t in from:pairs() do >> + to:insert(t) >> + end >> +end >> + >> +function prepare_to_6_2() >> + local format =3D {} >> + format[1] =3D {name=3D'tbl', type=3D'string'} >> + format[2] =3D {name=3D'idx', type=3D'string'} >> + format[3] =3D {name=3D'neq', type=3D'array'} >> + format[4] =3D {name=3D'nlt', type=3D'array'} >> + format[5] =3D {name=3D'ndlt', type=3D'array'} >> + format[6] =3D {name=3D'sample', type=3D'scalar'} >> + x1 =3D box.schema.space.create("x1", {engine =3D 'memtx', format = =3D format, field_count =3D 0}) >> + x1:create_index('primary', {parts =3D {1, 'string', 2, 'string', = 6, 'scalar'}}) >> + copy_tuples(_sql_stat4, x1) >> +end >> + >> +prepare_to_6_2() >=20 > 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 =3D 'abc'; >> ]]) >>=20 >> @@ -459,10 +556,19 @@ test:do_execsql_test( >> -- -- >> -- }) >>=20 >> +function update_stat_fields(stat, field_num, val) >=20 > Why do you need this wrapper at all? >=20 >> + for i,t in stat:pairs() do >> + t =3D t:transform(3, 3) >> + print(t) >=20 > Debug print. Removed. >> + stat:update(t, {{'=3D', field_num, val}}) >> + end >> +end >> + >> +update_stat_fields(_sql_stat4, 3, {0, 0, 0}) >=20 > Wrap this func in test-func call. >=20 >> + >> test:do_execsql_test( >> 7.3, >> [[ >> - UPDATE "_sql_stat4" SET "neq" =3D '0 0 0'; >> ANALYZE; >> SELECT * FROM t1 WHERE a =3D 1; >> ]], { >> @@ -471,11 +577,12 @@ test:do_execsql_test( >> -- >> }) >>=20 >> +box.sql.execute('ANALYZE=E2=80=99) >=20 > Again you break philosophy of tap suite: > wrap this analyze statement into one of > testing funcs. >=20 >>=20 >> -- This is just for coverage.... >> -test:do_execsql_test( >> - 15.11, >> - [[ >> - ANALYZE; >> - UPDATE "_sql_stat1" SET "stat" =3D "stat" || ' unordered'; >> - ]]) >> +box.sql.execute('ANALYZE') >> +update_stat_fields(_sql_stat1, 3, {'unordered=E2=80=99}) >=20 > Firstly, =E2=80=98unordered=E2=80=99 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 =E2=80=98unordered=E2=80=99 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 >>=20 >> +local _sql_stat1 =3D 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=3D345; >> ]], { >> @@ -104,5 +113,4 @@ test:do_execsql_test( >> } >> ) >>=20 >> - >=20 > Extra diff. >=20 >> test:finish_test() Removed. commit 6e6393d1aae25dd387db330eb80a412b51f549d6 Author: Roman Khabibov Date: Mon Jan 21 17:31:52 2019 +0300 sql: store statistics in statN as an array of integers =20 Encode statN stat fields as msgpack array instead of string. =20 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 =20 = +-------------------------------------------------------------------------= ------- +-- Tarantool 2.1.2 = +-------------------------------------------------------------------------= ------- + +local function upgrade_to_2_1_2() + local _sql_stat1 =3D box.space[box.schema.SQL_STAT1_ID] + local _sql_stat4 =3D box.space[box.schema.SQL_STAT4_ID] + + local format =3D _sql_stat1:format() + format[3].type =3D 'array' + _sql_stat1:format(format) + + format =3D _sql_stat4:format() + format[3].type =3D 'array' + format[4].type =3D 'array' + format[5].type =3D 'array' + _sql_stat4:format(format) +end + local function get_version() local version =3D box.space._schema:get{'version'} if version =3D=3D nil then @@ -641,7 +660,8 @@ local function upgrade(options) {version =3D mkversion(1, 7, 7), func =3D upgrade_to_1_7_7, = auto =3D true}, {version =3D mkversion(1, 10, 0), func =3D upgrade_to_1_10_0, = auto =3D true}, {version =3D mkversion(1, 10, 2), func =3D upgrade_to_1_10_2, = auto =3D true}, - {version =3D mkversion(2, 1, 0), func =3D upgrade_to_2_1_0, = auto =3D true} + {version =3D mkversion(2, 1, 0), func =3D upgrade_to_2_1_0, = auto =3D true}, + {version =3D mkversion(2, 1, 2), func =3D upgrade_to_2_1_2, = auto =3D true} } =20 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=3D?" matches 10 rows, and @@ -663,27 +663,32 @@ statGet(sql_context * context, int argc, sql_value = ** argv) * * I =3D (K+D-1)/D */ - char *z; - int i; - - char *zRet =3D sqlMallocZero((p->nKeyCol + 1) * 25); - if (zRet =3D=3D 0) { + uint32_t size =3D mp_sizeof_array(p->nKeyCol + 1); + size +=3D mp_sizeof_uint(p->nRow); + for (int i =3D 0; i < p->nKeyCol; ++i) { + uint64_t dist_count =3D p->current.anDLt[i] + 1; + uint64_t val =3D (p->nRow + dist_count - 1) / = dist_count; + size +=3D mp_sizeof_uint(val); + } + char *mp_stat1 =3D malloc(size); + if (mp_stat1 =3D=3D NULL) { sql_result_error_nomem(context); return; } - - sql_snprintf(24, zRet, "%llu", (u64) p->nRow); - z =3D zRet + sqlStrlen30(zRet); - for (i =3D 0; i < p->nKeyCol; i++) { - u64 nDistinct =3D p->current.anDLt[i] + 1; - u64 iVal =3D (p->nRow + nDistinct - 1) / = nDistinct; - sql_snprintf(24, z, " %llu", iVal); - z +=3D sqlStrlen30(z); - assert(p->current.anEq[i]); + char *data =3D mp_stat1; + data =3D mp_encode_array(data, p->nKeyCol + 1); + data =3D mp_encode_uint(data, p->nRow); + for (int i =3D 0; i < p->nKeyCol; ++i) { + uint64_t dist_count =3D p->current.anDLt[i] + 1; + uint64_t val =3D (p->nRow + dist_count - 1) / = dist_count; + data =3D mp_encode_uint(data, val); + assert(p->current.anEq[i] > 0); } - assert(z[0] =3D=3D '\0' && z > zRet); + assert(data =3D=3D size + mp_stat1); =20 - sql_result_text(context, zRet, -1, sql_free); + sql_result_blob(context, mp_stat1, size, free); + context->pOut->flags |=3D MEM_Subtype; + context->pOut->subtype =3D SQL_SUBTYPE_MSGPACK; } else if (eCall =3D=3D 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 =3D mp_sizeof_array(p->nCol); + for (int i =3D 0; i < p->nCol; ++i) + size +=3D mp_sizeof_uint(aCnt[i]); =20 - char *zRet =3D sqlMallocZero(p->nCol * 25); - if (zRet =3D=3D 0) { - sql_result_error_nomem(context); - } else { - int i; - char *z =3D zRet; - for (i =3D 0; i < p->nCol; i++) { - sql_snprintf(24, z, "%llu ", (u64) aCnt[i]); - z +=3D sqlStrlen30(z); + char *mp_stat4 =3D malloc(size); + if (mp_stat4 =3D=3D NULL) { + sql_result_error_nomem(context); + return; } - assert(z[0] =3D=3D '\0' && z > zRet); - z[-1] =3D '\0'; - sql_result_text(context, zRet, -1, sql_free); + char *data =3D mp_stat4; + data =3D mp_encode_array(data, p->nCol); + for (int i =3D 0; i < p->nCol; i++) + data =3D mp_encode_uint(data, aCnt[i]); + assert(data =3D=3D mp_stat4 + size); + sql_result_blob(context, mp_stat4, size, free); + context->pOut->flags |=3D MEM_Subtype; + context->pOut->subtype =3D SQL_SUBTYPE_MSGPACK; } - -} #ifndef SQL_DEBUG UNUSED_PARAMETER(argc); #endif @@ -1168,34 +1174,67 @@ struct analysis_index_info { }; =20 /** - * 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 =3D 0x1, + NOSKIPSCAN_HINT_TK =3D 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 =3D stat_string; - if (z =3D=3D NULL) - z =3D ""; - for (int i =3D 0; *z && i < stat_size; i++) { - tRowcnt v =3D 0; - int c; - while ((c =3D z[0]) >=3D '0' && c <=3D '9') { - v =3D 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 =3D stat_array; + if (z =3D=3D NULL || mp_typeof(*z) !=3D MP_ARRAY) + return; + int array_size =3D 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 =3D 0; i < stat_size; i++) { + if (mp_typeof(*z) !=3D MP_UINT) + return; + tRowcnt v =3D (tRowcnt) mp_decode_uint(&z); if (stat_exact !=3D NULL) stat_exact[i] =3D v; if (stat_log !=3D NULL) stat_log[i] =3D sqlLogEst(v); - if (*z =3D=3D ' ') - z++; + } + + /* Keywords processing if needed. */ + if (keywords_mask !=3D NULL) { + *keywords_mask =3D 0; + uint32_t keyword_count =3D array_size - stat_size; + while (keyword_count-- > 0) { + uint32_t sval_len; + if (mp_typeof(*z) !=3D MP_STR) + return; + const char *sval =3D mp_decode_str(&z, = &sval_len); + if (strncmp(sval, "unordered", 9) =3D=3D 0) + *keywords_mask |=3D UNORDERED_HINT_TK; + else if (strncmp(sval, "noskipscan", 10) =3D=3D = 0) + *keywords_mask |=3D NOSKIPSCAN_HINT_TK; + } } } =20 @@ -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 =3D false; - stat->skip_scan_enabled =3D true; - char *z =3D argv[2]; - /* Position ptr at the end of stat string. */ - for (; *z =3D=3D ' ' || (*z >=3D '0' && *z <=3D '9'); ++z); - while (z[0]) { - if (sql_strlike_cs("unordered%", z, '[') =3D=3D 0) - index->def->opts.stat->is_unordered =3D true; - else if (sql_strlike_cs("noskipscan%", z, '[') =3D=3D 0) - index->def->opts.stat->skip_scan_enabled =3D = false; - while (z[0] !=3D 0 && z[0] !=3D ' ') - z++; - while (z[0] =3D=3D ' ') - z++; - } + uint8_t keywords_mask =3D 0; + decode_stat_array(argv[2], column_count, stat->tuple_stat1, + stat->tuple_log_est, &keywords_mask); + stat->is_unordered =3D (keywords_mask & UNORDERED_HINT_TK); + stat->skip_scan_enabled =3D ! (keywords_mask & = NOSKIPSCAN_HINT_TK); return 0; } =20 @@ -1489,12 +1516,13 @@ load_stat_from_space(struct sql *db, const char = *sql_select_prepare, struct index_stat *stat =3D &stats[current_idx_count]; struct index_sample *sample =3D = &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 =3D sql_column_bytes(stmt, 5); sample->sample_key =3D 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 =3D require("sqltester") -test:plan(38) +test:plan(39) =20 --!./tcltestrunner.lua -- 2005 July 22 @@ -160,7 +160,7 @@ test:do_execsql_test( SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx"; ]], { -- - "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 -- }) =20 @@ -173,7 +173,7 @@ test:do_execsql_test( SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx"; ]], { -- - "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 -- }) =20 @@ -185,7 +185,7 @@ test:do_execsql_test( SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx"; ]], { -- - "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 -- }) =20 @@ -201,7 +201,7 @@ test:do_execsql_test( SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx"; ]], { -- - "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 -- }) =20 @@ -213,7 +213,7 @@ test:do_execsql_test( SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx"; ]], { -- - "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= -- }) =20 @@ -224,7 +224,7 @@ test:do_execsql_test( SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx"; ]], { -- - "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= -- }) =20 @@ -236,7 +236,7 @@ test:do_execsql_test( SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx"; ]], { -- - "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 -- }) =20 @@ -263,7 +263,7 @@ test:do_execsql_test( SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx"; ]], { -- - "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 -- }) =20 @@ -320,26 +320,37 @@ test:do_execsql_test( SELECT "idx", "stat" FROM "_sql_stat1" ORDER BY "idx"; ]], { -- - "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 -- }) =20 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'); + ]], { + -- + -- + }) + +_sql_stat1 =3D 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 =3D 1234; ]], { -- -- }) =20 +_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 =3D 1234; ]], { -- @@ -522,27 +533,83 @@ test:do_execsql_test( SELECT * FROM "_sql_stat1" where "tbl"=3D'T1' and = "idx"=3D'I1' LIMIT 1; ]], { -- - "T1", "I1", "221 221 221 221 2" + "T1","I1",221,221,221,221,2 -- }) =20 +-- 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 =3D box.space._sql_stat4 + t =3D {} + for k, v in _sql_stat4:pairs() do + table.insert(t, v) + end + + local where =3D {tbl =3D "T1", idx =3D '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 =3D 1 + for _, tuple in pairs(t) do + tuple =3D t[i] + if tuple[k] ~=3D v then + t[i] =3D nil + end + i =3D 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 =3D 0 + for _, v in pairs(t) do + res =3D res + v + end + return res + end + + local order_by =3D 'nlt' + local compare =3D function() end + if order =3D=3D 'asc' then + compare =3D function(a, b) + if sum_table(a[order_by]) <=3D sum_table(b[order_by]) then + return true + end + end + else + compare =3D 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"=3D'T1' and "idx"=3D'I1' ORDER BY "nlt" LIMIT 1; + SELECT get_tuple_with_T1_I1_order_by_nlt('asc'); ]], { -- - "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=3D]" -- }) =20 test:do_execsql_test( "analyze-6.1.4", [[ - SELECT "tbl", "idx", "neq", "nlt", "ndlt" FROM "_sql_stat4" = where "tbl"=3D'T1' and "idx"=3D'I1' ORDER BY "nlt" DESC LIMIT 1; + SELECT get_tuple_with_T1_I1_order_by_nlt('desc'); ]], { -- - "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]" -- }) =20 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"=3D'T1' ORDER = BY "idx"; ]], { -- - "T1","128 1", "T1A", "128 1", "T1B", "128 128" + "T1",128,1,"T1A",128,1,"T1B",128,128 -- }) =20 @@ -77,7 +77,7 @@ test:do_test( ]]) end, { -- - "T1", "128 1", "T1A", "128 1", "T1B", "128 64" + "T1",128,1,"T1A",128,1,"T1B",128,64 -- }) =20 @@ -115,7 +115,7 @@ test:do_execsql_test( ]] , { -- - "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 -- }) =20 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 =3D require("sqltester") -test:plan(121) +test:plan(106) =20 testprefix =3D "analyze9" =20 +_sql_stat1 =3D box.space._sql_stat1 +_sql_stat4 =3D 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" =3D 'I1'; ]], { -- <1.2> - "T1", "I1", "1 1", "0 0", "0 0", "(0) (0)", "T1", "I1", "1 1", = "1 1", "1 1", "(1) (1)",=20 - "T1", "I1", "1 1", "2 2", "2 2", "(2) (2)", "T1", "I1", "1 1", = "3 3", "3 3", "(3) (3)",=20 - "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)" -- }) =20 @@ -85,9 +88,9 @@ test:do_execsql_test( =20 ]], { -- <1.3> - 'T1', 'T1', '1', '0', '0', '(0)', 'T1', 'T1', '1', '1', '1', = '(1)',=20 - 'T1', 'T1', '1', '2', '2', '(2)', 'T1', 'T1', '1', '3', '3', = '(3)',=20 - '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)" -- }) =20 @@ -184,12 +187,12 @@ test:do_execsql_test( ]], generate_tens(100)) =20 -- The first element in the "nEq" list of all samples should therefore = be 10. --- =20 + =20 test:do_execsql_test( "3.3.2", [[ ANALYZE; - SELECT lrange("neq", 1, 1) FROM "_sql_stat4" WHERE "idx" =3D = 'I2'; + SELECT lrange(msgpack_decode_sample("neq"), 1, 1) FROM = "_sql_stat4" WHERE "idx" =3D 'I2'; ]], generate_tens_str(24)) =20 = --------------------------------------------------------------------------= - @@ -283,31 +286,102 @@ test:do_execsql_test( -- }) =20 +-- 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 =3D {} + for k, v in _sql_stat4:pairs() do + table.insert(t, v) + end + + local count =3D 1 + local where =3D {idx =3D 'I1'} + if where ~=3D 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 =3D 1 + for key, tuple in pairs(t) do + tuple =3D t[i] + if tuple[k] ~=3D v then + t[i] =3D nil + else + count =3D i + end + i =3D i + 1 + end + end + end + + local order_by =3D 'sample' + local compare =3D function() end + if order =3D=3D 'asc' then + compare =3D function(a, b) + if a[order_by] <=3D b[order_by] then + return true + end + end + else + compare =3D function(a, b) + if a[order_by] > b[order_by] then + return true + end + end + end + + table.sort(t, compare) + + if limit =3D=3D nil then + limit =3D count + end + + local ret =3D '' + local i =3D 1 + msgpack =3D require('msgpack') + while i <=3D limit do + t[i] =3D t[i]:update({{'=3D', 6, = msgpack_decode_sample(t[i]['sample'])}}) + t[i] =3D t[i]:transform(1, 2) + if i =3D=3D 1 then + ret =3D tostring(t[i]) + else + ret =3D ret..', '..tostring(t[i]) + end + i =3D 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 =3D '' + for i =3D 0, 15 do + res =3D res..'[[10, 10, 10], ['..(i * 10)..', '.. + (i * 10)..', '..(i * 10)..'], ['..i..', '..i..', '..i..'], = \''..i..' '.. + i..' '..i..'\'], ' + end + res =3D 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)=20 - FROM "_sql_stat4" WHERE "idx" =3D '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() -- }) =20 test:do_execsql_test( 4.4, [[ - SELECT "neq", lrange("nlt", 1, 3), lrange("ndlt", 1, 3), = lrange(msgpack_decode_sample("sample"), 1, 3)=20 - FROM "_sql_stat4" WHERE "idx" =3D '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']" -- }) =20 @@ -375,128 +449,6 @@ test:do_execsql_test( -- }) =20 = --------------------------------------------------------------------------= -- --- 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 =3D 'abc'; - ]]) - = --------------------------------------------------------------------------= -- --- The following tests experiment with adding corrupted records to the --- 'sample' column of the _sql_stat4 table. --- -local get_pk =3D function (space, record) - local pkey =3D {} - for _, part in pairs(space.index[0].parts) do - table.insert(pkey, record[part.fieldno]) - end - return pkey -end - -local inject_stat_error_func =3D function (space_name) - local space =3D box.space[space_name] - local record =3D space:select({"T1", "I1", nil}, {limit =3D 1})[1] - space:delete(get_pk(space, record)) - local record_new =3D {} - for i =3D 1,#record-1 do record_new[i] =3D record[i] end - record_new[#record] =3D '' - 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 =3D X'FFFF'; --- ANALYZE; --- SELECT * FROM t1 WHERE a =3D 1; --- ]], { --- -- <7.2> --- 1, 1 --- -- --- }) - -test:do_execsql_test( - 7.3, - [[ - UPDATE "_sql_stat4" SET "neq" =3D '0 0 0'; - ANALYZE; - SELECT * FROM t1 WHERE a =3D 1; - ]], { - -- <7.3> - 1, 1, 1 - -- - }) - -test:do_execsql_test( - 7.4, - [[ - ANALYZE; - UPDATE "_sql_stat4" SET "ndlt" =3D '0 0 0'; - ANALYZE; - SELECT * FROM t1 WHERE a =3D 3; - ]], { - -- <7.4> - 3, 3, 3 - -- - }) - -test:do_execsql_test( - 7.5, - [[ - ANALYZE; - UPDATE "_sql_stat4" SET "nlt" =3D '0 0 0'; - ANALYZE; - SELECT * FROM t1 WHERE a =3D 5; - ]], { - -- <7.5> - 5, 5, 5 - -- - }) - = --------------------------------------------------------------------------= - -- test:do_execsql_test( @@ -1031,119 +983,6 @@ test:do_execsql_test( }) =20 = --------------------------------------------------------------------------= - --- 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;=20 - ]], { - -- <15.2> - 1, 2, 3, 4, 5, 6 - -- - }) - -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 - -- - }) - -local inject_stat_error_func =3D function (space_name) - local space =3D box.space[space_name] - local stats =3D space:select() - for _, stat in pairs(stats) do - space:delete(get_pk(space, stat)) - local new_tuple =3D {"no such tbl"} - for i=3D2,#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 - -- - }) - --- 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" =3D NULL, "nlt" =3D NULL, = "ndlt" =3D NULL; --- ]]) - -test:do_execsql_test( - 15.10, - [[ - SELECT * FROM x1; - ]], { - -- <15.10> - 1, 2, 3, 4, 5, 6 - -- - }) - --- This is just for coverage.... -test:do_execsql_test( - 15.11, - [[ - ANALYZE; - UPDATE "_sql_stat1" SET "stat" =3D "stat" || ' unordered'; - ]]) - -test:do_execsql_test( - 15.12, - [[ - SELECT * FROM x1; - ]], { - -- <15.12> - 1, 2, 3, 4, 5, 6 - -- - }) = --------------------------------------------------------------------------= -- -- 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 =3D require("sqltester") -test:plan(20) - -testprefix =3D "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=3DNNN" 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, "#" - -- - }) - -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 - }) - -test:do_execsql_test( - 1.2, - [[ - SELECT d FROM t1 ORDER BY b; - ]], { - -- <1.2> - 3, 111, 6, 12, 9, 12 - -- - }) - -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" - -- - }) - --- 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"=3D'12345 2 unordered' WHERE = "idx"=3D'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, "#" - -- - }) - -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 - }) - -test:do_execsql_test( - 2.2, - [[ - SELECT d FROM t1 ORDER BY b; - ]], { - -- <2.2> - 3, 111, 6, 12, 9, 12 - -- - }) - -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" - -- - }) - --- Ignore extraneous text parameters in the "_sql_stat1".stat field. --- -test:do_execsql_test( - 3.0, - [[ - UPDATE "_sql_stat1" SET "stat"=3D'12345 2 whatever=3D5 = unordered xyzzy=3D11' WHERE "idx"=3D'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, "#" - -- - }) - -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 - }) - -test:do_execsql_test( - 3.2, - [[ - SELECT d FROM t1 ORDER BY b; - ]], { - -- <3.2> - 3, 111, 6, 12, 9, 12 - -- - }) - -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" - -- - }) - --- The sz=3DNNN 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=3D10'),('t1','t1db','12345 3 2 sz=3D20');= - ANALYZE; - SELECT count(b) FROM t1; - ]], { - -- <4.0> - 6 - -- - }) - -test:do_execsql_test( - 4.1, - [[ - EXPLAIN QUERY PLAN SELECT count(b) FROM t1; - ]], { - -- <4.1> - 0, 0, 0, "SCAN TABLE T1" - -- - }) - -test:do_execsql_test( - 4.2, - [[ - DELETE FROM "_sql_stat1"; - INSERT INTO "_sql_stat1"("tbl","idx","stat") = VALUES('t1','t1bc','12345 3 2 sz=3D20'),('t1','t1db','12345 3 2 sz=3D10');= - ANALYZE; - SELECT count(b) FROM t1; - ]], { - -- <4.2> - 6 - -- - }) - -test:do_execsql_test( - 4.3, - [[ - EXPLAIN QUERY PLAN SELECT count(b) FROM t1; - ]], { - -- <4.3> - 0, 0, 0, "SCAN TABLE T1" - -- - }) - --- The sz=3DNNN 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=3D5 sz=3D10 y=3D10'), - ('t1','t1db','12345 3 2 whatever sz=3D20 junk'); - ANALYZE; - SELECT count(b) FROM t1; - ]], { - -- <5.0> - 6 - -- - }) - -test:do_execsql_test( - 5.1, - [[ - EXPLAIN QUERY PLAN - SELECT count(b) FROM t1; - ]], { - -- <5.1> - 0, 0, 0, "SCAN TABLE T1" - -- - }) - -test:do_execsql_test( - 5.2, - [[ - DELETE FROM "_sql_stat1"; - INSERT INTO "_sql_stat1"("tbl","idx","stat") = VALUES('t1','t1db','12345 3 2 x=3D5 sz=3D10 y=3D10'), = ('t1','t1bc','12345 3 2 whatever sz=3D20 junk'); - ANALYZE; - SELECT count(b) FROM t1; - ]], { - -- <5.2> - 6 - -- - }) - -test:do_execsql_test( - 5.3, - [[ - EXPLAIN QUERY PLAN SELECT count(b) FROM t1; - ]], { - -- <5.3> - 0, 0, 0, "SCAN TABLE T1" - -- - }) - - -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 =20 test =3D require("sqltester") -test:plan(4) +test:plan(3) =20 local function lindex(str, pos) return str:sub(pos+1, pos+1) @@ -77,32 +77,5 @@ test:do_execsql_test( } ) =20 -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=3D345; - ]], { - "abc", 345, 7, 8, "def", 345, 9, 10 - } -) - =20 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=3D] - - ['T1', 'T1', '1', '0', '0', !!binary kQE=3D] - - ['T2', 'I1', '1', '0', '0', !!binary kQI=3D] - - ['T2', 'T2', '1', '0', '0', !!binary kQE=3D] +- - ['T1', 'I1', [1], [0], [0], !!binary kQI=3D] + - ['T1', 'T1', [1], [0], [0], !!binary kQE=3D] + - ['T2', 'I1', [1], [0], [0], !!binary kQI=3D] + - ['T2', 'T2', [1], [0], [0], !!binary kQE=3D] ... 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=3D] - - ['T2', 'I1', '1', '0', '0', !!binary kQI=3D] - - ['T2', 'T2', '1', '0', '0', !!binary kQE=3D] +- - ['T1', 'T1', [1], [0], [0], !!binary kQE=3D] + - ['T2', 'I1', [1], [0], [0], !!binary kQI=3D] + - ['T2', 'T2', [1], [0], [0], !!binary kQE=3D] ... 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=3D] - - ['T1', 'T1', '1', '0', '0', !!binary kQE=3D] - - ['T2', 'I1', '1', '0', '0', !!binary kQI=3D] - - ['T2', 'T2', '1', '0', '0', !!binary kQE=3D] +- - ['T1', 'I1', [1], [0], [0], !!binary kQI=3D] + - ['T1', 'T1', [1], [0], [0], !!binary kQE=3D] + - ['T2', 'I1', [1], [0], [0], !!binary kQI=3D] + - ['T2', 'T2', [1], [0], [0], !!binary kQE=3D] ... 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=3D] - - ['T1', 'T1', '1', '0', '0', !!binary kQE=3D] - - ['T2', 'T2', '1', '0', '0', !!binary kQE=3D] +- - ['T1', 'I1', [1], [0], [0], !!binary kQI=3D] + - ['T1', 'T1', [1], [0], [0], !!binary kQE=3D] + - ['T2', 'T2', [1], [0], [0], !!binary kQE=3D] ... 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 =3D require('test_run').new() +--- +... +engine =3D test_run:get_cfg('engine') +--- +... +box.sql.execute('pragma sql_default_engine=3D\''..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 =3D 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 =3D {} + 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), {{'=3D', 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 =3D box.space._sql_stat1 +--- +... +_sql_stat1:update({'T1', 'T1B'}, {{'=3D', 3, {12345, 2}}}) +--- +- ['T1', 'T1B', [12345, 2], 10] +... +_sql_stat1:update({'T1', 'T1C'}, {{'=3D', 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 =3D 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? AND B