AryaWu/sqlite

1# 2005 September 172#3# The author disclaims copyright to this source code.  In place of4# 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 been14# fixed.  15#16# The problem in ticket #1433 was that the dependencies on the right-hand17# side of an IN operator were not being checked correctly.  So in an18# 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 IN23# depended on table t2.  It was checking dependencies based on the24# Expr.pRight field rather than Expr.pList and Expr.pSelect.  25#26# Such a bug could be verifed using a less elaborate test case.  But27# this test case (from the original bug poster) exercises so many different28# parts of the system all at once, that it seemed like a good one to29# include in the test suite. 30#31# NOTE:  Yes, in spite of the name of this file (tkt1443.test) this32# test is for ticket #1433 not #1443.  I mistyped the name when I was33# creating the file and I had already checked in the file by the wrong34# name be the time I noticed the error.  With CVS it is a really hassle35# 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 39set testdir [file dirname $argv0]40source $testdir/tester.tcl41 42ifcapable !subquery||!memorydb {43  finish_test44  return45}46 47# Construct the sample database.48#49do_test tkt1443-1.0 {50  sqlite3 db :memory:51  execsql {52    CREATE TABLE Items(53    	itemId integer primary key,54    	 item str unique55    );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 UNIQUE65    );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 integer74    );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 STRING87    );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 UNIQUE95    );96    INSERT INTO "UserGroups" VALUES(1, 'test');97    INSERT INTO "UserGroups" VALUES(2, 'limited');98    99    CREATE TABLE UserGroupMembers (100    	userGroupId INTEGER,101    	userId INTEGER102    );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 INTEGER113    );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 index120#121do_test tkt1443-1.1 {122  execsql {123    select distinct124        Items.Item as trove, UP.pattern as pattern125    from126       ( select127           Permissions.labelId as labelId,128           PerItems.item as pattern129         from130           Users, UserGroupMembers, Permissions131           left outer join Items as PerItems132                 on Permissions.itemId = PerItems.itemId133         where134               Users.user = 'limited'135           and Users.userId = UserGroupMembers.userId136           and UserGroupMembers.userGroupId = Permissions.userGroupId137       ) as UP join LabelMap on ( UP.labelId = 0 or138                                  UP.labelId = LabelMap.labelId ),139       Labels, Items140    where141        Labels.label = 'localhost@rpl:branch'142    and Labels.labelId = LabelMap.labelId143    and LabelMap.itemId = Items.itemId144    ORDER BY +trove, +pattern145  }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 same150#151do_test tkt1443-1.2 {152  execsql {153    CREATE UNIQUE INDEX PermissionsIdx154         ON Permissions(userGroupId, labelId, itemId);155    select distinct156        Items.Item as trove, UP.pattern as pattern157    from158       ( select159           Permissions.labelId as labelId,160           PerItems.item as pattern161         from162           Users, UserGroupMembers, Permissions163           left outer join Items as PerItems164                 on Permissions.itemId = PerItems.itemId165         where166               Users.user = 'limited'167           and Users.userId = UserGroupMembers.userId168           and UserGroupMembers.userGroupId = Permissions.userGroupId169       ) as UP join LabelMap on ( UP.labelId = 0 or170                                  UP.labelId = LabelMap.labelId ),171       Labels, Items172    where173        Labels.label = 'localhost@rpl:branch'174    and Labels.labelId = LabelMap.labelId175    and LabelMap.itemId = Items.itemId176    ORDER BY +trove, +pattern177  }178} {double .*:runtime double:runtime .*:runtime double:source .*:runtime}179 180finish_test181