[tarantool-patches] [PATCH] sql: remove tests on ORDER BY/LIMIT + DELETE
Ivan Koptelov
ivan.koptelov at tarantool.org
Mon Feb 4 18:34:58 MSK 2019
Removes obsolete tests on ORDER BY/LIMIT + DELETE. This
functionality was not supported for some time, but a few tests
were still existing.
Closes #2172
---
Branch https://github.com/tarantool/tarantool/compare/sudobobo/gh-2172-rm-order-by-and-limit-from-delete
Issue https://github.com/tarantool/tarantool/issues/2172
test/sql-tap/e_delete.test.lua | 188 -----------------------------------------
1 file changed, 188 deletions(-)
diff --git a/test/sql-tap/e_delete.test.lua b/test/sql-tap/e_delete.test.lua
index 84a4e0a22..0a81eb3bd 100755
--- a/test/sql-tap/e_delete.test.lua
+++ b/test/sql-tap/e_delete.test.lua
@@ -305,194 +305,6 @@ if (0 > 0) then
DELETE FROM t8 NOT INDEXED WHERE a=5;
END;]], {"NOT INDEXED"}},
})
- -- EVIDENCE-OF: R-64942-06615 The LIMIT and ORDER BY clauses (described
- -- below) are unsupported for DELETE statements within triggers.
- --
- test:do_delete_tests("e_delete-2.5", "-error", [[ near "%s": syntax error ]], {
- {1, [[
- CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN
- DELETE FROM t8 LIMIT 10;
- END;]], {"LIMIT"}},
- {2, [[
- CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN
- DELETE FROM t8 ORDER BY a LIMIT 5;
- END;]], {"ORDER"}}
- })
- -- EVIDENCE-OF: R-40026-10531 If SQLite is compiled with the
- -- SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax
- -- of the DELETE statement is extended by the addition of optional ORDER
- -- BY and LIMIT clauses:
- --
- -- -- syntax diagram delete-stmt-limited
- --
- test:do_delete_tests("e_delete-3.1", {
- {1, "DELETE FROM t1 LIMIT 5", {}},
- {2, "DELETE FROM t1 LIMIT 5-1 OFFSET 2+2", {}},
- {3, "DELETE FROM t1 LIMIT 2+2, 16/4", {}},
- {4, "DELETE FROM t1 ORDER BY x LIMIT 5", {}},
- {5, "DELETE FROM t1 ORDER BY x LIMIT 5-1 OFFSET 2+2", {}},
- {6, "DELETE FROM t1 ORDER BY x LIMIT 2+2, 16/4", {}},
- {7, "DELETE FROM t1 WHERE x>2 LIMIT 5", {}},
- {8, "DELETE FROM t1 WHERE x>2 LIMIT 5-1 OFFSET 2+2", {}},
- {9, "DELETE FROM t1 WHERE x>2 LIMIT 2+2, 16/4", {}},
- {10, "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 5", {}},
- {11, "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 5-1 OFFSET 2+2", {}},
- {12, "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 2+2, 16/4", {}},
- })
-end
-
-
-
--- MUST_WORK_TEST delete limit syntax
-if (0 > 0) then
- test.drop_all_tables()
- local function rebuild_t1()
- test:catchsql " DROP TABLE t1 "
- test:execsql [[
- CREATE TABLE t1(a INT PRIMARY KEY, b INT );
- INSERT INTO t1 VALUES(1, 'one');
- INSERT INTO t1 VALUES(2, 'two');
- INSERT INTO t1 VALUES(3, 'three');
- INSERT INTO t1 VALUES(4, 'four');
- INSERT INTO t1 VALUES(5, 'five');
- ]]
- end
- -- EVIDENCE-OF: R-44062-08550 If a DELETE statement has a LIMIT clause,
- -- the maximum number of rows that will be deleted is found by evaluating
- -- the accompanying expression and casting it to an integer value.
- --
- rebuild_t1()
- --test:do_delete_tests("e_delete-3.2", "-repair", "rebuild_t1", "-query", "SELECT a FROM t1", {
- test:do_delete_tests("e_delete-3.2", {
- {1, "DELETE FROM t1 LIMIT 3", {4, 5}},
- {2, "DELETE FROM t1 LIMIT 1+1", {3, 4, 5}},
- {3, "DELETE FROM t1 LIMIT '4'", {5}},
- {4, "DELETE FROM t1 LIMIT '1.0'", {2, 3, 4, 5}},
- })
- -- EVIDENCE-OF: R-02661-56399 If the result of the evaluating the LIMIT
- -- clause cannot be losslessly converted to an integer value, it is an
- -- error.
- --
- test:do_delete_tests("e_delete-3.3", "-error", " datatype mismatch ", {
- {1, "DELETE FROM t1 LIMIT 'abc'", {}},
- {2, "DELETE FROM t1 LIMIT NULL", {}},
- {3, "DELETE FROM t1 LIMIT X'ABCD'", {}},
- {4, "DELETE FROM t1 LIMIT 1.2", {}},
-})
- -- EVIDENCE-OF: R-00598-03741 A negative LIMIT value is interpreted as
- -- "no limit".
- --
- test:do_delete_tests("e_delete-3.4", "-repair", "rebuild_t1", "-query", [[
- SELECT a FROM t1
-]], {
- {1, "DELETE FROM t1 LIMIT -1", {}},
- {2, "DELETE FROM t1 LIMIT 2-4", {}},
- {3, "DELETE FROM t1 LIMIT -4.0", {}},
- {4, "DELETE FROM t1 LIMIT 5*-1", {}},
-})
- -- EVIDENCE-OF: R-26377-49195 If the DELETE statement also has an OFFSET
- -- clause, then it is similarly evaluated and cast to an integer value.
- -- Again, it is an error if the value cannot be losslessly converted to
- -- an integer.
- --
- test:do_delete_tests("e_delete-3.5", "-error", " datatype mismatch ", {
- {1, "DELETE FROM t1 LIMIT 1 OFFSET 'abc'", {}},
- {2, "DELETE FROM t1 LIMIT 1 OFFSET NULL", {}},
- {3, "DELETE FROM t1 LIMIT 1 OFFSET X'ABCD'", {}},
- {4, "DELETE FROM t1 LIMIT 1 OFFSET 1.2", {}},
- {5, "DELETE FROM t1 LIMIT 'abc', 1", {}},
- {6, "DELETE FROM t1 LIMIT NULL, 1", {}},
- {7, "DELETE FROM t1 LIMIT X'ABCD', 1", {}},
- {8, "DELETE FROM t1 LIMIT 1.2, 1", {}},
-})
- -- EVIDENCE-OF: R-64004-53814 If there is no OFFSET clause, or the
- -- calculated integer value is negative, the effective OFFSET value is
- -- zero.
- --
- test:do_delete_tests("e_delete-3.6", "-repair", "rebuild_t1", "-query", [[
- SELECT a FROM t1
-]], {
-{"1a", "DELETE FROM t1 LIMIT 3 OFFSET 0", {4, 5}},
-{"1b", "DELETE FROM t1 LIMIT 3", {4, 5}},
-{"1c", "DELETE FROM t1 LIMIT 3 OFFSET -1", {4, 5}},
-{"2a", "DELETE FROM t1 LIMIT 1+1 OFFSET 0", {3, 4, 5}},
-{"2b", "DELETE FROM t1 LIMIT 1+1", {3, 4, 5}},
-{"2c", "DELETE FROM t1 LIMIT 1+1 OFFSET 2-5", {3, 4, 5}},
-{"3a", "DELETE FROM t1 LIMIT '4' OFFSET 0", {5}},
-{"3b", "DELETE FROM t1 LIMIT '4'", {5}},
-{"3c", "DELETE FROM t1 LIMIT '4' OFFSET -1.0", {5}},
-{"4a", "DELETE FROM t1 LIMIT '1.0' OFFSET 0", {2, 3, 4, 5}},
-{"4b", "DELETE FROM t1 LIMIT '1.0'", {2, 3, 4, 5}},
-{"4c", "DELETE FROM t1 LIMIT '1.0' OFFSET -11", {2, 3, 4, 5}},
-})
- -- EVIDENCE-OF: R-48141-52334 If the DELETE statement has an ORDER BY
- -- clause, then all rows that would be deleted in the absence of the
- -- LIMIT clause are sorted according to the ORDER BY. The first M rows,
- -- where M is the value found by evaluating the OFFSET clause expression,
- -- are skipped, and the following N, where N is the value of the LIMIT
- -- expression, are deleted.
- --
- --test:do_delete_tests("e_delete-3.7", "-repair", "rebuild_t1", "-query", "SELECT a FROM t1", {
- test:do_delete_tests("e_delete-3.7",{
- {1, "DELETE FROM t1 ORDER BY b LIMIT 2", {1, 2, 3}},
- {2, "DELETE FROM t1 ORDER BY length(b), a LIMIT 3", {3, 5}},
- {3, "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 0", {1, 2, 3, 4}},
- {4, "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 1", {1, 2, 3, 5}},
- {5, "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 2", {1, 2, 4, 5}},
-})
- -- EVIDENCE-OF: R-64535-08414 If there are less than N rows remaining
- -- after taking the OFFSET clause into account, or if the LIMIT clause
- -- evaluated to a negative value, then all remaining rows are deleted.
- --
- test:do_delete_tests("e_delete-3.8", "-repair", "rebuild_t1", "-query", [[
- SELECT a FROM t1
-]], {
- {1, "DELETE FROM t1 ORDER BY a ASC LIMIT 10", {}},
- {2, "DELETE FROM t1 ORDER BY a ASC LIMIT -1", {}},
- {3, "DELETE FROM t1 ORDER BY a ASC LIMIT 4 OFFSET 2", {1, 2}},
-})
- -- EVIDENCE-OF: R-37284-06965 If the DELETE statement has no ORDER BY
- -- clause, then all rows that would be deleted in the absence of the
- -- LIMIT clause are assembled in an arbitrary order before applying the
- -- LIMIT and OFFSET clauses to determine the subset that are actually
- -- deleted.
- --
- -- In practice, the "arbitrary order" is rowid order.
- --
- test:do_delete_tests("e_delete-3.9", "-repair", "rebuild_t1", "-query", [[
- SELECT a FROM t1
-]], {
- {1, "DELETE FROM t1 LIMIT 2", {3, 4, 5}},
- {2, "DELETE FROM t1 LIMIT 3", {4, 5}},
- {3, "DELETE FROM t1 LIMIT 1 OFFSET 0", {2, 3, 4, 5}},
- {4, "DELETE FROM t1 LIMIT 1 OFFSET 1", {1, 3, 4, 5}},
- {5, "DELETE FROM t1 LIMIT 1 OFFSET 2", {1, 2, 4, 5}},
-})
- -- EVIDENCE-OF: R-07548-13422 The ORDER BY clause on a DELETE statement
- -- is used only to determine which rows fall within the LIMIT. The order
- -- in which rows are deleted is arbitrary and is not influenced by the
- -- ORDER BY clause.
- --
- -- In practice, rows are always deleted in rowid order.
- --
- test:do_delete_tests("e_delete-3.10", "-repair", [[
- rebuild_t1
- catchsql { DROP TABLE t1log }
- execsql {
- CREATE TABLE t1log(x INT );
- CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN
- INSERT INTO t1log VALUES(old.a);
- END;
- }
-]], "-query", [[
- SELECT x FROM t1log
-]], {
- {1, "DELETE FROM t1 ORDER BY a DESC LIMIT 2", {4, 5}},
- {2, "DELETE FROM t1 ORDER BY a DESC LIMIT -1", {1, 2, 3, 4, 5}},
- {3, "DELETE FROM t1 ORDER BY a ASC LIMIT 2", {1, 2}},
- {4, "DELETE FROM t1 ORDER BY a ASC LIMIT -1", {1, 2, 3, 4, 5}},
-})
end
-
test:finish_test()
--
2.14.3 (Apple Git-98)
More information about the Tarantool-patches
mailing list