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