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 181
