[patches] [PATCH V2] sql: fix non-working 'INDEXED BY' clause
Bulat Niatshin
niatshin at tarantool.org
Mon Mar 12 14:21:13 MSK 2018
Fix non-working 'INDEXED BY' clause for SELECT statement,
which caused syntax-error during execution.
Closes #2996
---
Branch:
https://github.com/tarantool/tarantool/tree/bn/gh-2996-indexed-by
Issue:
https://github.com/tarantool/tarantool/issues/2996
Changes in V2:
- Functional tests for INDEXED BY clause were implemented.
src/box/sql/parse.c | 2 +-
src/box/sql/parse.y | 2 +-
test/sql-tap/gh-2996-indexed-by.test.lua | 158 +++++++++++++++++++++++++++++++
test/sql/gh2966-indexed-by.result | 21 ++++
test/sql/gh2966-indexed-by.test.lua | 10 ++
5 files changed, 191 insertions(+), 2 deletions(-)
create mode 100755 test/sql-tap/gh-2996-indexed-by.test.lua
create mode 100644 test/sql/gh2966-indexed-by.result
create mode 100644 test/sql/gh2966-indexed-by.test.lua
diff --git a/src/box/sql/parse.c b/src/box/sql/parse.c
index 0019b77dc..3b035c61a 100644
--- a/src/box/sql/parse.c
+++ b/src/box/sql/parse.c
@@ -880,7 +880,7 @@ static const YYCODETYPE yyFallback[] = {
0, /* AS => nothing */
0, /* COMMA => nothing */
0, /* ID => nothing */
- 51, /* INDEXED => ID */
+ 0, /* INDEXED => nothing */
51, /* ABORT => ID */
51, /* ACTION => ID */
51, /* ADD => ID */
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 914fc53b8..f87672ca4 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -205,7 +205,7 @@ columnname(A) ::= nm(A) typetoken(Y). {sqlite3AddColumn(pParse,&A,&Y);}
%fallback ID
ABORT ACTION ADD AFTER AUTOINCREMENT BEFORE CASCADE
CONFLICT DEFERRED END FAIL
- IGNORE INDEXED INITIALLY INSTEAD ISNULL NO NOTNULL MATCH PLAN
+ IGNORE INITIALLY INSTEAD ISNULL NO NOTNULL MATCH PLAN
QUERY KEY OFFSET RAISE RELEASE REPLACE RESTRICT
%ifdef SQLITE_OMIT_COMPOUND_SELECT
INTERSECT
diff --git a/test/sql-tap/gh-2996-indexed-by.test.lua b/test/sql-tap/gh-2996-indexed-by.test.lua
new file mode 100755
index 000000000..8d0b9e0da
--- /dev/null
+++ b/test/sql-tap/gh-2996-indexed-by.test.lua
@@ -0,0 +1,158 @@
+#!/usr/bin/env tarantool
+test = require("sqltester")
+test:plan(12)
+
+-- gh-2996 - INDEXED BY clause wasn't working.
+-- This functional test ensures that execution of that type of
+-- statement is correct.
+
+local function eqp(sql)
+ return "EXPLAIN QUERY PLAN " .. sql
+end
+
+test:execsql [[
+ CREATE TABLE t1(a INT PRIMARY KEY, b);
+ CREATE INDEX t1ix1 ON t1(b);
+ CREATE INDEX t1ix2 on t1(b);
+]]
+
+sample_size = 1000
+local query = "INSERT INTO t1 VALUES "
+
+for i = 1, sample_size do
+ query = query .. "(" .. i .. ", " .. i .. ")"
+ if (i ~= sample_size) then
+ query = query .. ","
+ end
+end
+
+-- Fill our space with data
+test:execsql(query)
+
+-- Make sure that SELECT works correctly when index exists.
+test:do_execsql_test(
+ "indexed-by-1.1",
+ eqp("SELECT b FROM t1 WHERE b <= 5"), {
+ -- <indexed-by-1.1>
+ 0, 0, 0, 'SEARCH TABLE T1 USING COVERING INDEX T1IX2 (B<?)'
+ -- <indexed-by-1.1>
+ })
+
+test:do_execsql_test(
+ "indexed-by-1.2",
+ eqp("SELECT b FROM t1 INDEXED BY t1ix1 WHERE b <= 5"), {
+ -- <indexed-by-1.2>
+ 0, 0, 0, 'SEARCH TABLE T1 USING COVERING INDEX T1IX1 (B<?)'
+ -- <indexed-by-1.2>
+ })
+
+test:execsql [[
+ DROP INDEX t1ix1 ON t1;
+ DROP INDEX t1ix2 ON t1;
+]]
+
+-- Now make sure that when schema was changed (t1ix1 was dropped),
+-- SELECT statement won't work.
+test:do_catchsql_test(
+ "indexed-by-1.3",
+ eqp("SELECT b FROM t1 INDEXED BY t1ix1 WHERE b <= 5"), {
+ -- <indexed-by-1.3>
+ 1, "no such index: T1IX1"
+ -- <indexed-by-1.3>
+ })
+
+test:do_catchsql_test(
+ "indexed-by-1.4",
+ eqp("SELECT b FROM t1 INDEXED BY t1ix2 WHERE b <= 5"), {
+ -- <indexed-by-1.4>
+ 1, "no such index: T1IX2"
+ -- <indexed-by-1.4>
+ })
+
+-- Make sure that DELETE statement works correctly with INDEXED BY.
+test:execsql [[
+ CREATE INDEX t1ix1 ON t1(b);
+ CREATE INDEX t1ix2 on t1(b);
+]]
+
+test:do_execsql_test(
+ "indexed-by-1.5",
+ eqp("DELETE FROM t1 WHERE b <= 5"), {
+ -- <indexed-by-1.5>
+ 0, 0, 0, 'SEARCH TABLE T1 USING COVERING INDEX T1IX2 (B<?)'
+ -- <indexed-by-1.5>
+ })
+
+test:do_execsql_test(
+ "indexed-by-1.6",
+ eqp("DELETE FROM t1 INDEXED BY t1ix1 WHERE b <= 5"), {
+ -- <indexed-by-1.6>
+ 0, 0, 0, 'SEARCH TABLE T1 USING COVERING INDEX T1IX1 (B<?)'
+ -- <indexed-by-1.6>
+ })
+
+test:execsql [[
+ DROP INDEX t1ix1 ON t1;
+ DROP INDEX t1ix2 ON t1;
+]]
+
+test:do_catchsql_test(
+ "indexed-by-1.7",
+ eqp("DELETE FROM t1 INDEXED BY t1ix1 WHERE b <= 5"), {
+ -- <indexed-by-1.7>
+ 1, "no such index: T1IX1"
+ -- <indexed-by-1.7>
+ })
+
+test:do_catchsql_test(
+ "indexed-by-1.8",
+ eqp("DELETE FROM t1 INDEXED BY t1ix2 WHERE b <= 5"), {
+ -- <indexed-by-1.8>
+ 1, "no such index: T1IX2"
+ -- <indexed-by-1.8>
+ })
+
+test:execsql [[
+ CREATE INDEX t1ix1 ON t1(b);
+ CREATE INDEX t1ix2 ON t1(b);
+]]
+
+test:do_execsql_test(
+ "indexed-by-1.9",
+ eqp("UPDATE t1 SET b = 20 WHERE b = 10"), {
+ -- <indexed-by-1.9>
+ 0, 0, 0, 'SEARCH TABLE T1 USING COVERING INDEX T1IX2 (B=?)'
+ -- <indexed-by-1.9>
+ })
+
+test:do_execsql_test(
+ "indexed-by-1.10",
+ eqp("UPDATE t1 INDEXED BY t1ix1 SET b = 20 WHERE b = 10"), {
+ -- <indexed-by-1.10>
+ 0, 0, 0, 'SEARCH TABLE T1 USING COVERING INDEX T1IX1 (B=?)'
+ -- <indexed-by-1.10>
+ })
+
+test:execsql [[
+ DROP INDEX t1ix1 ON t1;
+ DROP INDEX t1ix2 ON t1;
+]]
+
+test:do_catchsql_test(
+ "indexed-by-1.11",
+ eqp("UPDATE t1 INDEXED BY t1ix1 SET b = 20 WHERE b = 10"), {
+ -- <indexed-by-1.11>
+ 1, "no such index: T1IX1"
+ -- <indexed-by-1.11>
+ })
+
+test:do_catchsql_test(
+ "indexed-by-1.12",
+ eqp("UPDATE t1 INDEXED BY t1ix2 SET b = 20 WHERE b = 10"), {
+ -- <indexed-by-1.12>
+ 1, "no such index: T1IX2"
+ -- <indexed-by-1.12>
+ })
+
+test:finish_test()
+
diff --git a/test/sql/gh2966-indexed-by.result b/test/sql/gh2966-indexed-by.result
new file mode 100644
index 000000000..6a39d2f9b
--- /dev/null
+++ b/test/sql/gh2966-indexed-by.result
@@ -0,0 +1,21 @@
+box.cfg{}
+---
+...
+-- Check that 'INDEXED BY' clause works correctly without
+-- raising syntax error, which happened earlier after keyword patch
+-- (ban keywords reserved by standard)
+box.sql.execute("CREATE TABLE a (id PRIMARY KEY, a INTEGER);");
+---
+...
+box.sql.execute("CREATE INDEX i on a(a);");
+---
+...
+box.sql.execute("INSERT INTO a VALUES (1,2), (3,4), (5,6);");
+---
+...
+box.sql.execute("SELECT id FROM a INDEXED BY i");
+---
+- - [1]
+ - [3]
+ - [5]
+...
diff --git a/test/sql/gh2966-indexed-by.test.lua b/test/sql/gh2966-indexed-by.test.lua
new file mode 100644
index 000000000..e67ab18f4
--- /dev/null
+++ b/test/sql/gh2966-indexed-by.test.lua
@@ -0,0 +1,10 @@
+box.cfg{}
+
+-- Check that 'INDEXED BY' clause works correctly without
+-- raising syntax error, which happened earlier after keyword patch
+-- (ban keywords reserved by standard)
+
+box.sql.execute("CREATE TABLE a (id PRIMARY KEY, a INTEGER);");
+box.sql.execute("CREATE INDEX i on a(a);");
+box.sql.execute("INSERT INTO a VALUES (1,2), (3,4), (5,6);");
+box.sql.execute("SELECT id FROM a INDEXED BY i");
--
2.14.1
More information about the Tarantool-patches
mailing list