1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/tkt1443.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,180 @@
1.4 +# 2005 September 17
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library.
1.15 +#
1.16 +# This file implements tests to verify that ticket #1433 has been
1.17 +# fixed.
1.18 +#
1.19 +# The problem in ticket #1433 was that the dependencies on the right-hand
1.20 +# side of an IN operator were not being checked correctly. So in an
1.21 +# expression of the form:
1.22 +#
1.23 +# t1.x IN (1,t2.b,3)
1.24 +#
1.25 +# the optimizer was missing the fact that the right-hand side of the IN
1.26 +# depended on table t2. It was checking dependencies based on the
1.27 +# Expr.pRight field rather than Expr.pList and Expr.pSelect.
1.28 +#
1.29 +# Such a bug could be verifed using a less elaborate test case. But
1.30 +# this test case (from the original bug poster) exercises so many different
1.31 +# parts of the system all at once, that it seemed like a good one to
1.32 +# include in the test suite.
1.33 +#
1.34 +# NOTE: Yes, in spite of the name of this file (tkt1443.test) this
1.35 +# test is for ticket #1433 not #1443. I mistyped the name when I was
1.36 +# creating the file and I had already checked in the file by the wrong
1.37 +# name be the time I noticed the error. With CVS it is a really hassle
1.38 +# to change filenames, so I'll just leave it as is. No harm done.
1.39 +#
1.40 +# $Id: tkt1443.test,v 1.4 2006/01/17 09:35:02 danielk1977 Exp $
1.41 +
1.42 +set testdir [file dirname $argv0]
1.43 +source $testdir/tester.tcl
1.44 +
1.45 +ifcapable !subquery||!memorydb {
1.46 + finish_test
1.47 + return
1.48 +}
1.49 +
1.50 +# Construct the sample database.
1.51 +#
1.52 +do_test tkt1443-1.0 {
1.53 + sqlite3 db :memory:
1.54 + execsql {
1.55 + CREATE TABLE Items(
1.56 + itemId integer primary key,
1.57 + item str unique
1.58 + );
1.59 + INSERT INTO "Items" VALUES(0, 'ALL');
1.60 + INSERT INTO "Items" VALUES(1, 'double:source');
1.61 + INSERT INTO "Items" VALUES(2, 'double');
1.62 + INSERT INTO "Items" VALUES(3, 'double:runtime');
1.63 + INSERT INTO "Items" VALUES(4, '.*:runtime');
1.64 +
1.65 + CREATE TABLE Labels(
1.66 + labelId INTEGER PRIMARY KEY,
1.67 + label STR UNIQUE
1.68 + );
1.69 + INSERT INTO "Labels" VALUES(0, 'ALL');
1.70 + INSERT INTO "Labels" VALUES(1, 'localhost@rpl:linux');
1.71 + INSERT INTO "Labels" VALUES(2, 'localhost@rpl:branch');
1.72 +
1.73 + CREATE TABLE LabelMap(
1.74 + itemId INTEGER,
1.75 + labelId INTEGER,
1.76 + branchId integer
1.77 + );
1.78 + INSERT INTO "LabelMap" VALUES(1, 1, 1);
1.79 + INSERT INTO "LabelMap" VALUES(2, 1, 1);
1.80 + INSERT INTO "LabelMap" VALUES(3, 1, 1);
1.81 + INSERT INTO "LabelMap" VALUES(1, 2, 2);
1.82 + INSERT INTO "LabelMap" VALUES(2, 2, 3);
1.83 + INSERT INTO "LabelMap" VALUES(3, 2, 3);
1.84 +
1.85 + CREATE TABLE Users (
1.86 + userId INTEGER PRIMARY KEY,
1.87 + user STRING UNIQUE,
1.88 + salt BINARY,
1.89 + password STRING
1.90 + );
1.91 + INSERT INTO "Users" VALUES(1, 'test', 'æ$d',
1.92 + '43ba0f45014306bd6df529551ffdb3df');
1.93 + INSERT INTO "Users" VALUES(2, 'limited', 'ª>S',
1.94 + 'cf07c8348fdf675cc1f7696b7d45191b');
1.95 + CREATE TABLE UserGroups (
1.96 + userGroupId INTEGER PRIMARY KEY,
1.97 + userGroup STRING UNIQUE
1.98 + );
1.99 + INSERT INTO "UserGroups" VALUES(1, 'test');
1.100 + INSERT INTO "UserGroups" VALUES(2, 'limited');
1.101 +
1.102 + CREATE TABLE UserGroupMembers (
1.103 + userGroupId INTEGER,
1.104 + userId INTEGER
1.105 + );
1.106 + INSERT INTO "UserGroupMembers" VALUES(1, 1);
1.107 + INSERT INTO "UserGroupMembers" VALUES(2, 2);
1.108 +
1.109 + CREATE TABLE Permissions (
1.110 + userGroupId INTEGER,
1.111 + labelId INTEGER NOT NULL,
1.112 + itemId INTEGER NOT NULL,
1.113 + write INTEGER,
1.114 + capped INTEGER,
1.115 + admin INTEGER
1.116 + );
1.117 + INSERT INTO "Permissions" VALUES(1, 0, 0, 1, 0, 1);
1.118 + INSERT INTO "Permissions" VALUES(2, 2, 4, 0, 0, 0);
1.119 + }
1.120 +} {}
1.121 +
1.122 +# Run the query with an index
1.123 +#
1.124 +do_test tkt1443-1.1 {
1.125 + execsql {
1.126 + select distinct
1.127 + Items.Item as trove, UP.pattern as pattern
1.128 + from
1.129 + ( select
1.130 + Permissions.labelId as labelId,
1.131 + PerItems.item as pattern
1.132 + from
1.133 + Users, UserGroupMembers, Permissions
1.134 + left outer join Items as PerItems
1.135 + on Permissions.itemId = PerItems.itemId
1.136 + where
1.137 + Users.user = 'limited'
1.138 + and Users.userId = UserGroupMembers.userId
1.139 + and UserGroupMembers.userGroupId = Permissions.userGroupId
1.140 + ) as UP join LabelMap on ( UP.labelId = 0 or
1.141 + UP.labelId = LabelMap.labelId ),
1.142 + Labels, Items
1.143 + where
1.144 + Labels.label = 'localhost@rpl:branch'
1.145 + and Labels.labelId = LabelMap.labelId
1.146 + and LabelMap.itemId = Items.itemId
1.147 + ORDER BY +trove, +pattern
1.148 + }
1.149 +} {double .*:runtime double:runtime .*:runtime double:source .*:runtime}
1.150 +
1.151 +# Create an index and rerun the query.
1.152 +# Verify that the results are the same
1.153 +#
1.154 +do_test tkt1443-1.2 {
1.155 + execsql {
1.156 + CREATE UNIQUE INDEX PermissionsIdx
1.157 + ON Permissions(userGroupId, labelId, itemId);
1.158 + select distinct
1.159 + Items.Item as trove, UP.pattern as pattern
1.160 + from
1.161 + ( select
1.162 + Permissions.labelId as labelId,
1.163 + PerItems.item as pattern
1.164 + from
1.165 + Users, UserGroupMembers, Permissions
1.166 + left outer join Items as PerItems
1.167 + on Permissions.itemId = PerItems.itemId
1.168 + where
1.169 + Users.user = 'limited'
1.170 + and Users.userId = UserGroupMembers.userId
1.171 + and UserGroupMembers.userGroupId = Permissions.userGroupId
1.172 + ) as UP join LabelMap on ( UP.labelId = 0 or
1.173 + UP.labelId = LabelMap.labelId ),
1.174 + Labels, Items
1.175 + where
1.176 + Labels.label = 'localhost@rpl:branch'
1.177 + and Labels.labelId = LabelMap.labelId
1.178 + and LabelMap.itemId = Items.itemId
1.179 + ORDER BY +trove, +pattern
1.180 + }
1.181 +} {double .*:runtime double:runtime .*:runtime double:source .*:runtime}
1.182 +
1.183 +finish_test