File: tkt2640.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 (151 lines) | stat: -rwxr-xr-x 4,615 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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
#!/usr/bin/env tarantool
test = require("sqltester")
test:plan(6)

--!./tcltestrunner.lua
-- 2007 Sep 12
--
-- 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 is to test that ticket #2640 has been fixed.
--
-- $Id: tkt2640.test,v 1.3 2008/08/04 03:51:24 danielk1977 Exp $
--
-- The problem in ticket #2640 was that the query optimizer was 
-- not recognizing all uses of tables within subqueries in the
-- WHERE clause.  If the subquery contained a compound SELECT,
-- then tables that were used by terms of the compound other than
-- the last term would not be recognized as dependencies.
-- So if one of the SELECT statements within a compound made
-- use of a table that occurs later in a join, the query
-- optimizer would not recognize this and would try to evaluate
-- the subquery too early, before that tables value had been
-- established.
-- ["set","testdir",[["file","dirname",["argv0"]]]]
-- ["source",[["testdir"],"\/tester.tcl"]]


test:do_execsql_test(
    "tkt2640-1.1",
    [[
        CREATE TABLE persons(person_id  INT primary key, name TEXT);
        INSERT INTO persons VALUES(1,'fred');
        INSERT INTO persons VALUES(2,'barney');
        INSERT INTO persons VALUES(3,'wilma');
        INSERT INTO persons VALUES(4,'pebbles');
        INSERT INTO persons VALUES(5,'bambam');
        CREATE TABLE directors(id  INT primary key, person_id INT );
        INSERT INTO directors VALUES(1, 5);
        INSERT INTO directors VALUES(2, 3);
        CREATE TABLE writers(person_id  INT primary key);
        INSERT INTO writers VALUES(2);
        INSERT INTO writers VALUES(3);
        INSERT INTO writers VALUES(4);
        SELECT DISTINCT p.name
          FROM persons p, directors d
         WHERE d.person_id=p.person_id
           AND NOT EXISTS (
                 SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id
                 EXCEPT
                 SELECT person_id FROM writers w
               );
    ]], {
        -- <tkt2640-1.1>
        "wilma"
        -- </tkt2640-1.1>
    })

test:do_execsql_test(
    "tkt2640-1.2",
    [[
        SELECT DISTINCT p.name
          FROM persons p CROSS JOIN directors d
         WHERE d.person_id=p.person_id
           AND NOT EXISTS (
                 SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id
                 EXCEPT
                 SELECT person_id FROM writers w
               );
    ]], {
        -- <tkt2640-1.2>
        "wilma"
        -- </tkt2640-1.2>
    })

test:do_execsql_test(
    "tkt2640-1.3",
    [[
        SELECT DISTINCT p.name
          FROM directors d CROSS JOIN persons p
         WHERE d.person_id=p.person_id
           AND NOT EXISTS (
                 SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id
                 EXCEPT
                 SELECT person_id FROM writers w
               );
    ]], {
        -- <tkt2640-1.3>
        "wilma"
        -- </tkt2640-1.3>
    })

test:do_execsql_test(
    "tkt2640-1.4",
    [[
        SELECT DISTINCT p.name
          FROM persons p, directors d
         WHERE d.person_id=p.person_id
           AND NOT EXISTS (
                 SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id
                 EXCEPT
                 SELECT person_id FROM writers w
               );
    ]], {
        -- <tkt2640-1.4>
        "wilma"
        -- </tkt2640-1.4>
    })

test:do_execsql_test(
    "tkt2640-1.5",
    [[
        SELECT DISTINCT p.name
          FROM persons p CROSS JOIN directors d
         WHERE d.person_id=p.person_id
           AND NOT EXISTS (
                 SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id
                 EXCEPT
                 SELECT person_id FROM writers w
               );
    ]], {
        -- <tkt2640-1.5>
        "wilma"
        -- </tkt2640-1.5>
    })

test:do_execsql_test(
    "tkt2640-1.6",
    [[
        SELECT DISTINCT p.name
          FROM directors d CROSS JOIN persons p
         WHERE d.person_id=p.person_id
           AND NOT EXISTS (
                 SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id
                 EXCEPT
                 SELECT person_id FROM writers w
               );
    ]], {
        -- <tkt2640-1.6>
        "wilma"
        -- </tkt2640-1.6>
    })

test:finish_test()