[patches] [PATCH V2] sql: fix non-working 'INDEXED BY' clause
n.pettik
korablev at tarantool.org
Tue Mar 27 14:17:32 MSK 2018
> 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
You don’t need to invent own wheel: sql-tap already
has test:do_eqp_test() function.
> +
> +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");
> --
Why did you divide tests and put them to different test suits?
> test/sql/gh2966-indexed-by.test.lua
> test/sql-tap/gh-2996-indexed-by.test.lua
Put them both to one file to sql-tap/ .
The rest seems to be OK.
More information about the Tarantool-patches
mailing list