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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
# 2005 September 17
#
# 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 regression tests for SQLite library.
#
# This file implements tests to verify that ticket #1433 has been
# fixed.
#
# The problem in ticket #1433 was that the dependencies on the right-hand
# side of an IN operator were not being checked correctly. So in an
# expression of the form:
#
# t1.x IN (1,t2.b,3)
#
# the optimizer was missing the fact that the right-hand side of the IN
# depended on table t2. It was checking dependencies based on the
# Expr.pRight field rather than Expr.pList and Expr.pSelect.
#
# Such a bug could be verifed using a less elaborate test case. But
# this test case (from the original bug poster) exercises so many different
# parts of the system all at once, that it seemed like a good one to
# include in the test suite.
#
# NOTE: Yes, in spite of the name of this file (tkt1443.test) this
# test is for ticket #1433 not #1443. I mistyped the name when I was
# creating the file and I had already checked in the file by the wrong
# name be the time I noticed the error. With CVS it is a really hassle
# to change filenames, so I'll just leave it as is. No harm done.
#
# $Id: tkt1443.test,v 1.4 2006/01/17 09:35:02 danielk1977 Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable !subquery||!memorydb {
finish_test
return
}
# Construct the sample database.
#
do_test tkt1443-1.0 {
sqlite3 db :memory:
execsql {
CREATE TABLE Items(
itemId integer primary key,
item str unique
);
INSERT INTO "Items" VALUES(0, 'ALL');
INSERT INTO "Items" VALUES(1, 'double:source');
INSERT INTO "Items" VALUES(2, 'double');
INSERT INTO "Items" VALUES(3, 'double:runtime');
INSERT INTO "Items" VALUES(4, '.*:runtime');
CREATE TABLE Labels(
labelId INTEGER PRIMARY KEY,
label STR UNIQUE
);
INSERT INTO "Labels" VALUES(0, 'ALL');
INSERT INTO "Labels" VALUES(1, 'localhost@rpl:linux');
INSERT INTO "Labels" VALUES(2, 'localhost@rpl:branch');
CREATE TABLE LabelMap(
itemId INTEGER,
labelId INTEGER,
branchId integer
);
INSERT INTO "LabelMap" VALUES(1, 1, 1);
INSERT INTO "LabelMap" VALUES(2, 1, 1);
INSERT INTO "LabelMap" VALUES(3, 1, 1);
INSERT INTO "LabelMap" VALUES(1, 2, 2);
INSERT INTO "LabelMap" VALUES(2, 2, 3);
INSERT INTO "LabelMap" VALUES(3, 2, 3);
CREATE TABLE Users (
userId INTEGER PRIMARY KEY,
user STRING UNIQUE,
salt BINARY,
password STRING
);
INSERT INTO "Users" VALUES(1, 'test', 'æ$d',
'43ba0f45014306bd6df529551ffdb3df');
INSERT INTO "Users" VALUES(2, 'limited', 'ª>S',
'cf07c8348fdf675cc1f7696b7d45191b');
CREATE TABLE UserGroups (
userGroupId INTEGER PRIMARY KEY,
userGroup STRING UNIQUE
);
INSERT INTO "UserGroups" VALUES(1, 'test');
INSERT INTO "UserGroups" VALUES(2, 'limited');
CREATE TABLE UserGroupMembers (
userGroupId INTEGER,
userId INTEGER
);
INSERT INTO "UserGroupMembers" VALUES(1, 1);
INSERT INTO "UserGroupMembers" VALUES(2, 2);
CREATE TABLE Permissions (
userGroupId INTEGER,
labelId INTEGER NOT NULL,
itemId INTEGER NOT NULL,
write INTEGER,
capped INTEGER,
admin INTEGER
);
INSERT INTO "Permissions" VALUES(1, 0, 0, 1, 0, 1);
INSERT INTO "Permissions" VALUES(2, 2, 4, 0, 0, 0);
}
} {}
# Run the query with an index
#
do_test tkt1443-1.1 {
execsql {
select distinct
Items.Item as trove, UP.pattern as pattern
from
( select
Permissions.labelId as labelId,
PerItems.item as pattern
from
Users, UserGroupMembers, Permissions
left outer join Items as PerItems
on Permissions.itemId = PerItems.itemId
where
Users.user = 'limited'
and Users.userId = UserGroupMembers.userId
and UserGroupMembers.userGroupId = Permissions.userGroupId
) as UP join LabelMap on ( UP.labelId = 0 or
UP.labelId = LabelMap.labelId ),
Labels, Items
where
Labels.label = 'localhost@rpl:branch'
and Labels.labelId = LabelMap.labelId
and LabelMap.itemId = Items.itemId
ORDER BY +trove, +pattern
}
} {double .*:runtime double:runtime .*:runtime double:source .*:runtime}
# Create an index and rerun the query.
# Verify that the results are the same
#
do_test tkt1443-1.2 {
execsql {
CREATE UNIQUE INDEX PermissionsIdx
ON Permissions(userGroupId, labelId, itemId);
select distinct
Items.Item as trove, UP.pattern as pattern
from
( select
Permissions.labelId as labelId,
PerItems.item as pattern
from
Users, UserGroupMembers, Permissions
left outer join Items as PerItems
on Permissions.itemId = PerItems.itemId
where
Users.user = 'limited'
and Users.userId = UserGroupMembers.userId
and UserGroupMembers.userGroupId = Permissions.userGroupId
) as UP join LabelMap on ( UP.labelId = 0 or
UP.labelId = LabelMap.labelId ),
Labels, Items
where
Labels.label = 'localhost@rpl:branch'
and Labels.labelId = LabelMap.labelId
and LabelMap.itemId = Items.itemId
ORDER BY +trove, +pattern
}
} {double .*:runtime double:runtime .*:runtime double:source .*:runtime}
finish_test
|
