[tarantool-patches] [PATCH] sql: Fixes op-codes' generation for skip-scan

[Ivan Koptelov <ivan.koptelov@tarantool.org>]

From: IvanKoptelov <ivan.koptelov@tarantool.org>

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)