# 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.tclifcapable !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', 'a?>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