# 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