1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
|
#!/usr/bin/env tarantool
test = require("sqltester")
test:plan(18)
--!./tcltestrunner.lua
-- 2010 September 21
--
-- The author disclaims copyright to this source code. In place of
-- a legal notice, here is a blessing:
--
-- May you do good and not evil.
-- May you find forgiveness for yourself and forgive others.
-- May you share freely, never taking more than you give.
--
-------------------------------------------------------------------------
--
-- This file implements tests to verify that the "testable statements" in
-- the lang_delete.html document are correct.
--
-- ["set","testdir",[["file","dirname",["argv0"]]]]
-- ["source",[["testdir"],"\/tester.tcl"]]
test.do_delete_tests = test.do_select_tests
test:do_execsql_test(
"e_delete-0.0",
[[
CREATE TABLE t1(a INT PRIMARY KEY, b INT );
CREATE INDEX i1 ON t1(a);
]], {
-- <e_delete-0.0>
-- </e_delete-0.0>
})
-- -- syntax diagram delete-stmt
-- -- syntax diagram qualified-table-name
--
test:do_delete_tests("e_delete-0.1", {
{1, "DELETE FROM t1", {}},
{2, "DELETE FROM t1 INDEXED BY i1", {}},
{3, "DELETE FROM t1 NOT INDEXED", {}},
{7, "DELETE FROM t1 WHERE a>2", {}},
{8, "DELETE FROM t1 INDEXED BY i1 WHERE a>2", {}},
{9, "DELETE FROM t1 NOT INDEXED WHERE a>2", {}},
})
-- EVIDENCE-OF: R-20205-17349 If the WHERE clause is not present, all
-- records in the table are deleted.
--
--drop_all_tables
test:execsql "DROP TABLE t1;"
test:do_test(
"e_delete-1.0",
function()
local tables = {
"t1", "t2", "t3", "t4", "t5", "t6"
}
for _, t in ipairs(tables) do
local sql = 'CREATE TABLE '..t..'(x INT PRIMARY KEY, y TEXT);'
test:execsql(sql)
end
for _, t in ipairs(tables) do
local sql = [[
INSERT INTO TABLE_NAME VALUES(1, 'one');
INSERT INTO TABLE_NAME VALUES(2, 'two');
INSERT INTO TABLE_NAME VALUES(3, 'three');
INSERT INTO TABLE_NAME VALUES(4, 'four');
INSERT INTO TABLE_NAME VALUES(5, 'five');
]]
sql = string.gsub(sql, "TABLE_NAME", t)
test:execsql(sql)
end
return
end, {
-- <e_delete-1.0>
-- </e_delete-1.0>
})
test:do_delete_tests("e_delete-1.1", {
{1, "DELETE FROM t1 ; SELECT * FROM t1", {}},
})
-- EVIDENCE-OF: R-26300-50198 If a WHERE clause is supplied, then only
-- those rows for which the WHERE clause boolean expression is true are
-- deleted.
--
-- EVIDENCE-OF: R-23360-48280 Rows for which the expression is false or
-- NULL are retained.
--
test:do_delete_tests("e_delete-1.2", {
{1, "DELETE FROM t3 WHERE true ; SELECT x FROM t3", {}},
{3, "DELETE FROM t4 WHERE false ; SELECT x FROM t4", {1, 2, 3, 4, 5}},
{4, "DELETE FROM t4 WHERE NULL ; SELECT x FROM t4", {1, 2, 3, 4, 5}},
{5, "DELETE FROM t4 WHERE y!='two'; SELECT x FROM t4", {2}},
{6, "DELETE FROM t4 WHERE y='two' ; SELECT x FROM t4", {}},
{7, "DELETE FROM t5 WHERE x=(SELECT max(x) FROM t5);SELECT x FROM t5", {1, 2, 3, 4}},
{8, "DELETE FROM t5 WHERE (SELECT max(x) FROM t4) ;SELECT x FROM t5", {1, 2, 3, 4}},
{9, "DELETE FROM t5 WHERE (SELECT max(x) FROM t6) != 0 ;SELECT x FROM t5", {}},
{10, "DELETE FROM t6 WHERE y>'seven' ; SELECT y FROM t6", {"one", "four", "five"}},
})
test:finish_test()
|
