Move bundled copy of sqlite one level deeper to better separate it...

third_party/sqlite/test/tkt1443.test

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