Annotation of embedaddon/sqlite3/test/tkt1443.test, revision 1.1.1.1
1.1 misho 1: # 2005 September 17
2: #
3: # The author disclaims copyright to this source code. In place of
4: # a legal notice, here is a blessing:
5: #
6: # May you do good and not evil.
7: # May you find forgiveness for yourself and forgive others.
8: # May you share freely, never taking more than you give.
9: #
10: #***********************************************************************
11: # This file implements regression tests for SQLite library.
12: #
13: # This file implements tests to verify that ticket #1433 has been
14: # fixed.
15: #
16: # The problem in ticket #1433 was that the dependencies on the right-hand
17: # side of an IN operator were not being checked correctly. So in an
18: # expression of the form:
19: #
20: # t1.x IN (1,t2.b,3)
21: #
22: # the optimizer was missing the fact that the right-hand side of the IN
23: # depended on table t2. It was checking dependencies based on the
24: # Expr.pRight field rather than Expr.pList and Expr.pSelect.
25: #
26: # Such a bug could be verifed using a less elaborate test case. But
27: # this test case (from the original bug poster) exercises so many different
28: # parts of the system all at once, that it seemed like a good one to
29: # include in the test suite.
30: #
31: # NOTE: Yes, in spite of the name of this file (tkt1443.test) this
32: # test is for ticket #1433 not #1443. I mistyped the name when I was
33: # creating the file and I had already checked in the file by the wrong
34: # name be the time I noticed the error. With CVS it is a really hassle
35: # to change filenames, so I'll just leave it as is. No harm done.
36: #
37: # $Id: tkt1443.test,v 1.4 2006/01/17 09:35:02 danielk1977 Exp $
38:
39: set testdir [file dirname $argv0]
40: source $testdir/tester.tcl
41:
42: ifcapable !subquery||!memorydb {
43: finish_test
44: return
45: }
46:
47: # Construct the sample database.
48: #
49: do_test tkt1443-1.0 {
50: sqlite3 db :memory:
51: execsql {
52: CREATE TABLE Items(
53: itemId integer primary key,
54: item str unique
55: );
56: INSERT INTO "Items" VALUES(0, 'ALL');
57: INSERT INTO "Items" VALUES(1, 'double:source');
58: INSERT INTO "Items" VALUES(2, 'double');
59: INSERT INTO "Items" VALUES(3, 'double:runtime');
60: INSERT INTO "Items" VALUES(4, '.*:runtime');
61:
62: CREATE TABLE Labels(
63: labelId INTEGER PRIMARY KEY,
64: label STR UNIQUE
65: );
66: INSERT INTO "Labels" VALUES(0, 'ALL');
67: INSERT INTO "Labels" VALUES(1, 'localhost@rpl:linux');
68: INSERT INTO "Labels" VALUES(2, 'localhost@rpl:branch');
69:
70: CREATE TABLE LabelMap(
71: itemId INTEGER,
72: labelId INTEGER,
73: branchId integer
74: );
75: INSERT INTO "LabelMap" VALUES(1, 1, 1);
76: INSERT INTO "LabelMap" VALUES(2, 1, 1);
77: INSERT INTO "LabelMap" VALUES(3, 1, 1);
78: INSERT INTO "LabelMap" VALUES(1, 2, 2);
79: INSERT INTO "LabelMap" VALUES(2, 2, 3);
80: INSERT INTO "LabelMap" VALUES(3, 2, 3);
81:
82: CREATE TABLE Users (
83: userId INTEGER PRIMARY KEY,
84: user STRING UNIQUE,
85: salt BINARY,
86: password STRING
87: );
88: INSERT INTO "Users" VALUES(1, 'test', 'æ$d',
89: '43ba0f45014306bd6df529551ffdb3df');
90: INSERT INTO "Users" VALUES(2, 'limited', 'ª>S',
91: 'cf07c8348fdf675cc1f7696b7d45191b');
92: CREATE TABLE UserGroups (
93: userGroupId INTEGER PRIMARY KEY,
94: userGroup STRING UNIQUE
95: );
96: INSERT INTO "UserGroups" VALUES(1, 'test');
97: INSERT INTO "UserGroups" VALUES(2, 'limited');
98:
99: CREATE TABLE UserGroupMembers (
100: userGroupId INTEGER,
101: userId INTEGER
102: );
103: INSERT INTO "UserGroupMembers" VALUES(1, 1);
104: INSERT INTO "UserGroupMembers" VALUES(2, 2);
105:
106: CREATE TABLE Permissions (
107: userGroupId INTEGER,
108: labelId INTEGER NOT NULL,
109: itemId INTEGER NOT NULL,
110: write INTEGER,
111: capped INTEGER,
112: admin INTEGER
113: );
114: INSERT INTO "Permissions" VALUES(1, 0, 0, 1, 0, 1);
115: INSERT INTO "Permissions" VALUES(2, 2, 4, 0, 0, 0);
116: }
117: } {}
118:
119: # Run the query with an index
120: #
121: do_test tkt1443-1.1 {
122: execsql {
123: select distinct
124: Items.Item as trove, UP.pattern as pattern
125: from
126: ( select
127: Permissions.labelId as labelId,
128: PerItems.item as pattern
129: from
130: Users, UserGroupMembers, Permissions
131: left outer join Items as PerItems
132: on Permissions.itemId = PerItems.itemId
133: where
134: Users.user = 'limited'
135: and Users.userId = UserGroupMembers.userId
136: and UserGroupMembers.userGroupId = Permissions.userGroupId
137: ) as UP join LabelMap on ( UP.labelId = 0 or
138: UP.labelId = LabelMap.labelId ),
139: Labels, Items
140: where
141: Labels.label = 'localhost@rpl:branch'
142: and Labels.labelId = LabelMap.labelId
143: and LabelMap.itemId = Items.itemId
144: ORDER BY +trove, +pattern
145: }
146: } {double .*:runtime double:runtime .*:runtime double:source .*:runtime}
147:
148: # Create an index and rerun the query.
149: # Verify that the results are the same
150: #
151: do_test tkt1443-1.2 {
152: execsql {
153: CREATE UNIQUE INDEX PermissionsIdx
154: ON Permissions(userGroupId, labelId, itemId);
155: select distinct
156: Items.Item as trove, UP.pattern as pattern
157: from
158: ( select
159: Permissions.labelId as labelId,
160: PerItems.item as pattern
161: from
162: Users, UserGroupMembers, Permissions
163: left outer join Items as PerItems
164: on Permissions.itemId = PerItems.itemId
165: where
166: Users.user = 'limited'
167: and Users.userId = UserGroupMembers.userId
168: and UserGroupMembers.userGroupId = Permissions.userGroupId
169: ) as UP join LabelMap on ( UP.labelId = 0 or
170: UP.labelId = LabelMap.labelId ),
171: Labels, Items
172: where
173: Labels.label = 'localhost@rpl:branch'
174: and Labels.labelId = LabelMap.labelId
175: and LabelMap.itemId = Items.itemId
176: ORDER BY +trove, +pattern
177: }
178: } {double .*:runtime double:runtime .*:runtime double:source .*:runtime}
179:
180: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to tkt1443.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test