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 178322578F for ; Wed, 16 Jan 2019 09:26:02 -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 J8bjXsr6MRTS for ; Wed, 16 Jan 2019 09:26:02 -0500 (EST) Received: from smtpng2.m.smailru.net (smtpng2.m.smailru.net [94.100.179.3]) (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 257D225725 for ; Wed, 16 Jan 2019 09:26:00 -0500 (EST) Content-Type: text/plain; charset=us-ascii Mime-Version: 1.0 (Mac OS X Mail 12.0 \(3445.100.39\)) Subject: [tarantool-patches] Re: [PATCH 1/8] sql: remove SQLITE_ENABLE_UPDATE_DELETE_LIMIT define From: "n.pettik" In-Reply-To: Date: Wed, 16 Jan 2019 17:25:58 +0300 Content-Transfer-Encoding: quoted-printable Message-Id: <073C008C-361E-453F-B3EF-F90BA6CE5816@tarantool.org> References: <628c1d80ee13227d3e3d520707c3d404346fc94f.1545987214.git.korablev@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: Vladislav Shpilevoy > On 29 Dec 2018, at 19:42, Vladislav Shpilevoy = wrote: >=20 > Hi! Thanks for the patch! >=20 > SQLITE_ENABLE_UPDATE_DELETE_LIMIT still exists > in sql-tap/e_delete.test.lua. >=20 > I found out that in this file all tests from line 111 > to line 495 are about DELETE LIMIT, so we can remove > them as well, can't we? Yep, they are anyway disabled so lets remove them as well. Diff: diff --git a/test/sql-tap/e_delete.test.lua = b/test/sql-tap/e_delete.test.lua index 84a4e0a22..a58dc87c7 100755 --- a/test/sql-tap/e_delete.test.lua +++ b/test/sql-tap/e_delete.test.lua @@ -100,399 +100,5 @@ test:do_delete_tests("e_delete-1.2", { {9, "DELETE FROM t5 WHERE (SELECT max(x) FROM t6) ;SELECT x FROM = t5", {}}, {10, "DELETE FROM t6 WHERE y>'seven' ; SELECT y FROM t6", {"one", = "four", "five"}}, }) --- = #-------------------------------------------------------------------------= --- # Tests for restrictions on DELETE statements that appear within = trigger --- # programs. --- # --- forcedelete test.db2 --- forcedelete test.db3 - --- MUST_WORK_TEST should be rewritten without spaces or deleted -if (0 > 0) then - test:drop_all_tables() - test:do_execsql_test("e_delete-2.0", --- ATTACH 'test.db2' AS aux; --- ATTACH 'test.db3' AS aux2; - [[ - CREATE TABLE temp.t7(a INT primary key, b INT); INSERT INTO = temp.t7 VALUES(1, 2); - CREATE TABLE main.t7(a INT primary key, b INT); INSERT INTO = main.t7 VALUES(3, 4); - CREATE TABLE aux.t7(a INT primary key, b INT); INSERT INTO = aux.t7 VALUES(5, 6); - CREATE TABLE aux2.t7(a INT primary key, b INT); INSERT INTO = aux2.t7 VALUES(7, 8); - CREATE TABLE main.t8(a INT primary key, b INT); INSERT INTO = main.t8 VALUES(1, 2); - CREATE TABLE aux.t8(a INT primary key, b INT); INSERT INTO = aux.t8 VALUES(3, 4); - CREATE TABLE aux2.t8(a INT primary key, b INT); INSERT INTO = aux2.t8 VALUES(5, 6); - CREATE TABLE aux.t9(a INT primary key, b INT); INSERT INTO = aux.t9 VALUES(1, 2); - CREATE TABLE aux2.t9(a INT primary key, b INT); INSERT INTO = aux2.t9 VALUES(3, 4); - CREATE TABLE aux2.t10(a INT primary key, b INT); INSERT INTO = aux2.t10 VALUES(1, 2);]] - , {}) - - -- EVIDENCE-OF: R-09681-58560 The table-name specified as part of a - -- DELETE statement within a trigger body must be unqualified. - -- - -- EVIDENCE-OF: R-12275-20298 In other words, the schema-name. = prefix on - -- the table name is not allowed within triggers. - -- - - test:do_catchsql_test("e_delete-2.1.1",[[ - CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN - DELETE FROM main.t2; - END;]], - {1, "qualified table names are not allowed on INSERT, UPDATE, = and DELETE statements within triggers"}) - test:do_catchsql_test("e_delete-2.1.2",[[ - CREATE TRIGGER tr1 BEFORE UPDATE ON t2 BEGIN - DELETE FROM temp.t7 WHERE a=3Dnew.a; - END;]], - {1, "qualified table names are not allowed on INSERT, UPDATE, = and DELETE statements within triggers"}) - test:do_catchsql_test("e_delete-2.1.3",[[ - CREATE TRIGGER tr1 AFTER UPDATE ON t8 BEGIN - DELETE FROM aux2.t8 WHERE b!=3Da; - END;]], - {1, "qualified table names are not allowed on INSERT, UPDATE, = and DELETE statements within triggers"}) - -- EVIDENCE-OF: R-28818-63526 If the table to which the trigger is - -- attached is not in the temp database, then DELETE statements = within - -- the trigger body must operate on tables within the same database = as - -- it. - --=20 - -- This is tested in two parts. First, check that if a table of = the - -- specified name does not exist, an error is raised. Secondly, = test - -- that if tables with the specified name exist in multiple = databases, - -- the local database table is used. - -- - test:do_delete_tests("e_delete-2.2.1", "-error", " no such table: = %s ", { - {1, [[ - CREATE TRIGGER tr1 AFTER INSERT ON t7 BEGIN - DELETE FROM t9; - END; - INSERT INTO main.t7 VALUES(1, 2);]], {"main.t9"}}, - - {2, [[ - CREATE TRIGGER aux.tr2 BEFORE UPDATE ON t9 BEGIN - DELETE FROM t10; - END; - UPDATE t9 SET a=3D1;]], {"aux.t10"}}, - }) - test:do_execsql_test( - "e_delete-2.2.X", - [[ - DROP TRIGGER main.tr1; - DROP TRIGGER aux.tr2; - ]], { - -- - =20 - -- - }) - - test:do_delete_tests("e_delete-2.2.2", { - {1, [[ - CREATE TRIGGER aux.tr1 AFTER INSERT ON t8 BEGIN - DELETE FROM t9; - END; - INSERT INTO aux.t8 VALUES(1, 2); - - SELECT count(*) FROM aux.t9=20 - UNION ALL - SELECT count(*) FROM aux2.t9;]], {0, 1}}, - {2, [[ - CREATE TRIGGER main.tr1 AFTER INSERT ON t8 BEGIN - DELETE FROM t7; - END; - INSERT INTO main.t8 VALUES(1, 2); - - SELECT count(*) FROM temp.t7=20 - UNION ALL - SELECT count(*) FROM main.t7 - UNION ALL - SELECT count(*) FROM aux.t7 - UNION ALL - SELECT count(*) FROM aux2.t7;]], {1, 0, 1, 1}} - }) - -- EVIDENCE-OF: R-31567-38587 If the table to which the trigger is - -- attached is in the TEMP database, then the unqualified name of = the - -- table being deleted is resolved in the same way as it is for a - -- top-level statement (by searching first the TEMP database, then = the - -- main database, then any other databases in the order they were - -- attached). - -- - test:do_execsql_test( - "e_delete-2.3.0", - [[ - DROP TRIGGER aux.tr1; - DROP TRIGGER main.tr1; - DELETE FROM main.t8 WHERE oid>1; - DELETE FROM aux.t8 WHERE oid>1; - INSERT INTO aux.t9 VALUES(1, 2); - INSERT INTO main.t7 VALUES(3, 4); - ]], { - -- - =20 - -- - }) - - test:do_execsql_test( - "e_delete-2.3.1", - [[ - SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM = main.t7 UNION ALL - SELECT count(*) FROM aux.t7 UNION ALL SELECT count(*) FROM = aux2.t7; - - SELECT count(*) FROM main.t8 UNION ALL SELECT count(*) FROM = aux.t8 =20 - UNION ALL SELECT count(*) FROM aux2.t8; - - SELECT count(*) FROM aux.t9 UNION ALL SELECT count(*) FROM = aux2.t9; - - SELECT count(*) FROM aux2.t10; - ]], { - -- - 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 - -- - }) - - test:do_execsql_test( - "e_delete-2.3.2", - [[ - CREATE TRIGGER temp.tr1 AFTER INSERT ON t7 BEGIN - DELETE FROM t7; - DELETE FROM t8; - DELETE FROM t9; - DELETE FROM t10; - END; - INSERT INTO temp.t7 VALUES('hello', 'world'); - ]], { - -- - =20 - -- - }) - - test:do_execsql_test( - "e_delete-2.3.3", - [[ - SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM = main.t7 UNION ALL - SELECT count(*) FROM aux.t7 UNION ALL SELECT count(*) FROM = aux2.t7; - - SELECT count(*) FROM main.t8 UNION ALL SELECT count(*) FROM = aux.t8 =20 - UNION ALL SELECT count(*) FROM aux2.t8; - - SELECT count(*) FROM aux.t9 UNION ALL SELECT count(*) FROM = aux2.t9; - - SELECT count(*) FROM aux2.t10; - ]], { - -- - 0, 1, 1, 1, 0, 1, 1, 0, 1, 0 - -- - }) - - -- EVIDENCE-OF: R-28691-49464 The INDEXED BY and NOT INDEXED = clauses are - -- not allowed on DELETE statements within triggers. - -- - test:do_execsql_test( - "e_delete-2.4.0", - [[ - CREATE INDEX i8 ON t8(a, b); - ]], { - -- - =20 - -- - }) - - test:do_delete_tests("e_delete-2.4", "-error", [[ - the %s %s clause is not allowed on UPDATE or DELETE statements = within triggers - ]], { - {1, [[ - CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN - DELETE FROM t8 INDEXED BY i8 WHERE a=3D5; - END]], {"INDEXED BY"}}, - {2, [[ - CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN - DELETE FROM t8 NOT INDEXED WHERE a=3D5; - 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=20 - 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 -=