Imported Upstream version 2.0.3

1 files changed, 180 insertions, 0 deletions

diff --git a/test/tkt1443.test b/test/tkt1443.test
new file mode 100644
index 0000000..0f55437
--- /dev/null
+++ b/test/tkt1443.test
@@ -0,0 +1,180 @@
+# 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 {
+  sqlite3 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