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 E5684235F9 for ; Fri, 27 Apr 2018 06:06:10 -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 Yle58ZKnW3PN for ; Fri, 27 Apr 2018 06:06:10 -0400 (EDT) Received: from smtp57.i.mail.ru (smtp57.i.mail.ru [217.69.128.37]) (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 7B7CC235F7 for ; Fri, 27 Apr 2018 06:06:10 -0400 (EDT) From: Ivan Koptelov Subject: [tarantool-patches] [PATCH] sql: Fixes op-codes' generation for skip-scan Date: Fri, 27 Apr 2018 13:06:13 +0300 Message-Id: <20180427100613.94160-1-ivan.koptelov@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: korablev@tarantool.org, IvanKoptelov From: IvanKoptelov Currently we have skip-scan optimization working the way described here: (https://sqlite.org/optoverview.html#skipscan) To understand the problem solved consider the example with some skip-scan query and a table created like CREATE TABLE t(a, b, c, PRIMARY KEY(c, b, a)); Before the patch: op-codes realization of skip-scan relied on the following work of 'Column' op-code: If P1 is a number of cursor pointing to the table then P2 is number of column in 'native' order (0 for a, 1 for b, etc) If P1 is a number of cursor pointing to the index then P2 in number of column in 'index' order (0 for c, 1 for b, etc) But currently our 'Column' op-code always consider P2 to be column number in 'native order'. With the patch: P2 is always set in 'Column' as a column number in 'native' order. Closes #3350; Closes #2859 --- src/box/sql/wherecode.c | 2 +- test/sql-tap/skip-scan.test.lua | 102 ++++++++++++++++++++++++++++++++++++++++ 2 files changed, 103 insertions(+), 1 deletion(-) create mode 100755 test/sql-tap/skip-scan.test.lua diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c index 6aec4ae79..a2620386c 100644 --- a/src/box/sql/wherecode.c +++ b/src/box/sql/wherecode.c @@ -721,7 +721,7 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */ VdbeCoverageIf(v, bRev != 0); sqlite3VdbeJumpHere(v, j); for (j = 0; j < nSkip; j++) { - sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, j, + sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, pIdx->aiColumn[j], regBase + j); testcase(pIdx->aiColumn[j] == XN_EXPR); VdbeComment((v, "%s", explainIndexColumnName(pIdx, j))); diff --git a/test/sql-tap/skip-scan.test.lua b/test/sql-tap/skip-scan.test.lua new file mode 100755 index 000000000..0c6edbf34 --- /dev/null +++ b/test/sql-tap/skip-scan.test.lua @@ -0,0 +1,102 @@ +#!/usr/bin/env tarantool + +test = require("sqltester") +test:plan(4) + +local function lindex(str, pos) + return str:sub(pos+1, pos+1) +end + +local function int_to_char(i) + local res = '' + local char = 'abcdefghij' + local divs = {1000, 100, 10, 1} + for _, div in ipairs(divs) do + res = res .. lindex(char, math.floor(i/div) % 10) + end + return res +end + +box.internal.sql_create_function("lindex", lindex) +box.internal.sql_create_function("int_to_char", int_to_char) + +test:do_execsql_test( + "skip-scan-1.1", + [[ + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a COLLATE "unicode_ci", b, c, d, e, f, PRIMARY KEY(c, b, a)); + WITH data(a, b, c, d, e, f) AS + (SELECT int_to_char(0), 'xyz', 'zyx', '*', 0, 0 UNION ALL + SELECT int_to_char(f+1), b, c, d, (e+1) % 2, f+1 FROM data WHERE f<1024) + INSERT INTO t1 SELECT a, b, c, d, e, f FROM data; + ANALYZE; + SELECT COUNT(*) FROM t1 WHERE a < 'aaad'; + DROP TABLE t1; + ]], { + 3 + }) + +test:do_execsql_test( + "skip-scan-1.2", + [[ + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(a COLLATE "unicode_ci", b, c, d, e, f, PRIMARY KEY(e, f)); + WITH data(a, b, c, d, e, f) AS + (SELECT int_to_char(0), 'xyz', 'zyx', '*', 0, 0 UNION ALL + SELECT int_to_char(f+1), b, c, d, (e+1) % 2, f+1 FROM data WHERE f<1024) + INSERT INTO t2 SELECT a, b, c, d, e, f FROM data; + ANALYZE; + SELECT COUNT(*) FROM t2 WHERE f < 500; + DROP TABLE t2; + ]], { + 500 + } +) + +test:do_execsql_test( + "skip-scan-1.3", + [[ + DROP TABLE IF EXISTS t3; + CREATE TABLE t3(a COLLATE "unicode_ci", b, c, d, e, f, PRIMARY KEY(a)); + CREATE INDEX i31 ON t3(e, f); + WITH data(a, b, c, d, e, f) AS + (SELECT int_to_char(0), 'xyz', 'zyx', '*', 0, 0 UNION ALL + SELECT int_to_char(f+1), b, c, d, (e+1) % 2, f+1 FROM data WHERE f<1024) + INSERT INTO t3 SELECT a, b, c, d, e, f FROM data; + ANALYZE; + SELECT COUNT(*) FROM t3 WHERE f < 500; + DROP INDEX i31 on t3; + DROP TABLE t3; + ]], { + 500 + } +) + +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 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() -- 2.14.3 (Apple Git-98)