File: e_delete.test.lua

package info (click to toggle)
tarantool 2.6.0-1.4
  • links: PTS, VCS
  • area: main
  • in suites: sid, trixie
  • size: 85,412 kB
  • sloc: ansic: 513,775; cpp: 69,493; sh: 25,650; python: 19,190; perl: 14,973; makefile: 4,178; yacc: 1,329; sql: 1,074; pascal: 620; ruby: 190; awk: 18; lisp: 7
file content (103 lines) | stat: -rwxr-xr-x 3,442 bytes parent folder | download | duplicates (3)
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()