# 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 { sqlite4 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