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 221692679E for ; Mon, 4 Feb 2019 10:35:03 -0500 (EST) 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 dPqHTjea02Lq for ; Mon, 4 Feb 2019 10:35:03 -0500 (EST) Received: from smtpng1.m.smailru.net (smtpng1.m.smailru.net [94.100.181.251]) (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 9DFCF26745 for ; Mon, 4 Feb 2019 10:35:02 -0500 (EST) From: Ivan Koptelov Subject: [tarantool-patches] [PATCH] sql: remove tests on ORDER BY/LIMIT + DELETE Date: Mon, 4 Feb 2019 18:34:58 +0300 Message-Id: <20190204153458.12559-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, Ivan Koptelov 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)