predicatesintoviews.sql

derby database source code.good for you.

-- test the pushing of predicates into unflattened views
-- and derived tables

set isolation to rr;

-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;

-- set up
create table t1(c1 int, c2 int, c3 varchar(2000));

-- simple views
create view sv1 (c1, c2, c3) as select c1, c2, c3 || '$' from t1;
create view sv2 (x1, x2, x3) as select c1, c2, c3  || '$' from t1;
create view sv3 (y1, y2, y3) as select x1, x2, x3  || '$' from sv2;
create view sv4 (z1, z2, z3, z4, z5, z6) as
select a.c1, a.c2, a.c3 || '$', b.c1, b.c2, b.c3 || '$' from t1 a, t1 b;

-- more complex views
create view av1 (c1, c2) as select c1, max(c2) from t1 group by c1;
create view av2 (x1, x2) as select c1, max(c2) from av1 group by c1;
create view av3 (y1, y2, y3, y4) as
select a.c1, b.c1, max(a.c2), max(b.c2) from t1 a, t1 b group by a.c1, b.c1;

-- non-flattenable derived table in a non-flattenable view
create view cv1 (c1, c2) as
select c1, max(c2) from (select c1, c2 + 1 from t1) t(c1, c2) group by c1;

-- populate the tables
insert into t1 values (1, 1, ''), (1, 1, ''), (1, 2, ''), (1, 2, ''),
		      (2, 2, ''), (2, 2, ''), (2, 3, ''), (2, 3, '');


call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 20000;

-- predicate should get pushed into scan
select c1, c2 from sv1 where c1 = 1 order by c1, c2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select c1, c2 from sv1 where c1 = 1  + 1 order by c1, c2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select x1, x2 from sv2 where x1 = 1 order by x1, x2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select y1, y2 from sv3 where y1 = 1 order by y1, y2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select z1, z2, z4, z5 from sv4 where z1 = z4 and z2 = z5
order by z1, z2, z4, z5;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from av1 where c1 = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from av2 where x1 = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from av3;
select y1, y2, y3, y4 + 0 from av3 where y1 = y2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from cv1;
select * from cv1 where c1 = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
create index t1_c1 on t1(c1);
select c1, c2 from sv1 where c1 = 1 order by c1, c2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select x1, x2 from sv2 where x1 = 1 order by x1, x2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select y1, y2 from sv3 where y1 = 1 order by y1, y2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select z1, z2, z4, z5 from sv4 where z1 = z4 and z2 = z5
order by z1, z2, z4, z5;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from av1 where c1 = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from av2 where x1 = 2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select y1, y2, y3, y4 + 0 from av3 where y1 = y2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from cv1 where c1 = 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();


-- cleanup
drop view cv1;
drop view av3;
drop view av2;
drop view av1;
drop view sv4;
drop view sv3;
drop view sv2;
drop view sv1;
drop table t1;

-- DERBY-649: Test for making sure predicates are pushed into unions.
create table test.table1(a integer, b integer, c integer); 
create index test.table1idx on test.table1(b); 

create table test.table2(a integer, b integer, c integer); 
create index test.table2idx on test.table2(b); 
 
create view test.view0 as select all a,b from test.table1 union all select a,b from test.table2; 
create view test.view1(a,b) as select all a,b from test.table1 union all select a,b from test.table2
			union all select 1,1 from test.table1;
 
create view test.view2(c,d) as select all a+1,b+1 from test.table1 union all select a,b from test.table2; 

-- Following Selects using the tables directly would use index
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 
select a from test.table1 where b=25; 
VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 
select a from test.table2 where b=25; 
VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 

-- This select from a view based on the tables would use TableScan before DERBY-649
select a from test.view0 where b=25; 
VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 

-- Can't use index for the following
select a from test.view0 where b=25+a;
VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 

-- Inlist tests
select * from test.view0 where b in (1, 2, 3);
VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 

prepare s as 'select * from test.view0 where b in (?, ?, ?)';

execute s using 'values (1,2,3)';
VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 

-- This select should use index for first two selects, table scan for the third
select a from test.view1 where b=25; 
VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 

select d from test.view2 where d=25; 
VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 

CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0); 

-- Beetle 4983, customer case, complex query involving views, wrong column remapping
-- after view flattening, NullPointerException, and didn't get predicate pushed down
-- all the way to base table.

autocommit off;

create schema xr;

create table xr.businessentity 
			 ( 	ID    		varchar(48) not null primary key ,
				type		varchar(24) not null,
                           	name    	varchar(128) not null ,
			   	uname		varchar(128) ,
--GENERATED ALWAYS AS (ucase(name)),
			   	description	varchar(256),
                           	createdate   	timestamp  not null,
                           	changedate   	timestamp  not null,
                           	deletedate   	timestamp,
			   	-- for type=BUSINESS this is the delegate owner
			   	-- for type=USER this is their userID
				AuthID	    	varchar(48) not null,  
			       	ownerUserUUID 	varchar(48),
				-- for type=BUSINESS -- in subclass 
			       	businessTypeID  varchar(48)
                         );

create trigger xr.be_uname_i after insert on xr.businessentity
referencing new as n
for each row mode db2sql
update xr.businessentity set uname = upper( n.name ) where name = n.name;

create trigger xr.be_unane_u after update of name, uname on xr.businessentity
referencing new as n
for each row mode db2sql
update xr.businessentity set uname = upper( n.name )
  where name = n.name and uname <> upper( n.name );

create unique index xr.bus1 on xr.businessentity (ownerUserUUID, uname, deletedate);

create table xr.BEMembers(
	beid			varchar(48) not null,
	memberid		varchar(48) not null
	);


create unique index xr.bem1 on xr.BEMembers (beid, memberid);

alter table xr.BEMembers add constraint bem_beid
	foreign key (beid)
	references xr.businessentity(id)
	on delete cascade;

alter table xr.BEMembers add constraint bem_memberid
	foreign key (memberid)
	references xr.businessentity(id)
	on delete cascade;

create table xr.xruser 
			 ( 	businessentityID   	varchar(48) not null primary key ,
				userid			varchar(48) not null, 
				deletedate   		timestamp
                    	);


create unique index xr.user1 on xr.xruser (userID, deletedate);


alter table xr.xruser add constraint u_busent foreign key (businessentityID)
                            references xr.businessentity(ID)
                            on delete cascade;


create table xr.locator 
			(ownerID		varchar(48) not null,
			type	      		varchar(48) not null,
			useTypeID	      	varchar(48) ,
			value			varchar(256),
			street1			varchar(48),
			street2			varchar(48),
			city			varchar(48),
			state			varchar(48),
			country			varchar(48),
			postalcode		varchar(48)	
			);

create unique index xr.loc1 on xr.locator ( ownerID,type,usetypeID ); 

alter table xr.locator add constraint l_busent foreign key (ownerID)
                            references xr.businessentity(ID)
                            on delete cascade;


create table xr.BusinessEntityAssociation
			(ID			varchar(48) not null primary key ,
			sourceID		varchar(48) not null,
			targetID	      	varchar(48) not null,
			ownerID		 	varchar(48) not null,
			assoctypeID		varchar(48) not null,
                  	createdate   		timestamp  not null,
                    	changedate   		timestamp  not null,
			deletedate   		timestamp, 
			description		varchar(256)
			);

alter table xr.BusinessEntityAssociation 
add constraint bea_sourceID foreign key (sourceID)
                            references xr.businessentity(ID)
                            on delete restrict;

alter table xr.BusinessEntityAssociation 
add constraint bea_targetID foreign key (targetID)
                            references xr.businessentity(ID)
                            on delete restrict;

alter table xr.BusinessEntityAssociation 
add constraint bea_ownerID foreign key (ownerID)
                            references xr.businessentity(ID)
                            on delete restrict;


create unique index xr.BEA1 
on xr.BusinessEntityAssociation( sourceid, targetid, ownerID, assoctypeID, deletedate); 


create table xr.repositoryobjectresource (
	id			varchar(48)	not null primary key,
	type			varchar(48)	not null,
	subtype		varchar(48),
	creatorid		varchar(48)	not null,
	createdate		timestamp	not null,
	currentVersion	varchar(48),
	versionControlled smallint	not null with default 0,
	checkedOut		smallint	not null with default 0,
	checkForLock	smallint	not null with default 0
	);

alter table xr.repositoryobjectresource add constraint ror_creatorid
	foreign key (creatorid)
	references xr.xruser(businessentityid)
	on delete restrict;

create table xr.repositoryobjectversion (
	id			varchar(48)		not null primary key,
	resourceid		varchar(48)		not null,
	name			varchar(128)	not null,
	uname			varchar(128),
--	GENERATED ALWAYS AS (ucase(name)),
	folderid		varchar(48),
	versionName		varchar(128)	not null,
	uri			varchar(255)	not null,
	versionuri		varchar(255)	not null,
	description		varchar(256),
	versionComment	varchar(256),
	ownerid		varchar(48)	not null,
	creatorid		varchar(48)	not null,
	versiondate		timestamp	not null,
	changedate		timestamp	not null,
	deletedate		timestamp, 
	previousversion	varchar(48)
	);
	
create trigger xr.rov_uname_i after insert on xr.repositoryobjectversion
referencing new as n
for each row mode db2sql
update xr.repositoryobjectversion set uname = upper( n.name ) where name = n.name;

create trigger xr.rov_unane_u after update of name, uname on xr.repositoryobjectversion
referencing new as n
for each row mode db2sql
update xr.repositoryobjectversion set uname = upper( n.name )
  where name = n.name and uname <> upper( n.name );
create unique index xr.versionname on xr.repositoryobjectversion (resourceid, versionName);

-- Don't think I want this constraint with versioning.
-- Object could have been deleted in a later version.
-- create unique index xr.versionuri on xr.repositoryobjectversion (versionuri, deletedate);

alter table xr.repositoryobjectversion add constraint rov_previousvers
	foreign key (previousversion)
	references xr.repositoryobjectversion(id)
	on delete set null;

alter table xr.repositoryobjectversion add constraint rov_folderid
	foreign key (folderid)
	references xr.repositoryobjectresource(id)
	on delete restrict;

alter table xr.repositoryobjectversion add constraint rov_ownerid
	foreign key (ownerid)
	references xr.businessentity(id)
	on delete restrict;

alter table xr.repositoryobjectversion add constraint rov_creatorid
	foreign key (creatorid)
	references xr.xruser(businessentityid)
	on delete restrict;

alter table xr.repositoryobjectresource add constraint ror_currentVersion
	foreign key (currentVersion)
	references xr.repositoryobjectversion(id)
	on delete restrict;

create table xr.lock (
	locktoken		varchar(48)	not null,
	resourceid		varchar(48)	not null,
	ownerid		varchar(48) not null,
	exclusive		smallint 	not null,
	timeoutSeconds	bigint	not null,
	expirationDate	timestamp	not null
	);

alter table xr.lock add primary key (locktoken, resourceid);

alter table xr.lock add constraint l_resourceid
	foreign key (resourceid)
	references xr.repositoryobjectresource(id)
	on delete cascade;

alter table xr.lock add constraint l_ownerid
	foreign key (ownerid)
	references xr.xruser(businessentityid)
	on delete cascade;

create table xr.keyword (
	versionid			varchar(48)		not null,
	keyword			varchar(128)	not null
	);

alter table xr.keyword add constraint k_versionid
	foreign key (versionid)
	references xr.repositoryobjectversion(id)
	on delete cascade;

create table xr.slot (
	versionid			varchar(48)		not null,
	name				varchar(128)	not null,
	value				varchar(256)
	);

alter table xr.slot add constraint s_versionid
	foreign key (versionid)
	references xr.repositoryobjectversion(id)
	on delete cascade;
	
create table xr.versionlabel (
	versionid			varchar(48)		not null,
	label				varchar(128)	not null
	);

alter table xr.versionlabel add constraint vl_versionid
	foreign key (versionid)
	references xr.repositoryobjectversion(id)
	on delete cascade;

create table xr.repositoryentry (
	versionid		varchar(48)		not null primary key,
	versioncontentid	varchar(48),
	mimetype		varchar(48),
	stability		varchar(48),
	status		varchar(48),
	startdate		timestamp,
	expirationdate	timestamp,
	isopaque		smallint		not null with default 0
	);

alter table xr.repositoryentry add constraint re_versionid
	foreign key (versionid)
	references xr.repositoryobjectversion(id)
	on delete cascade;

	
create table xr.repositoryentrycontent (
 	versionid			varchar(48)		not null primary key,
	contentchangedate	timestamp,
	content				long varchar
--blob(1M)
	);

alter table xr.repositoryentry add constraint re_versioncontent
	foreign key (versioncontentid)
	references xr.repositoryentrycontent(versionid)
	on delete set null;


create table xr.objectgroup_content (
	versionid		varchar(48) not null,
	memberid		varchar(48) not null
	);

alter table xr.objectgroup_content add constraint ogc_versionid
	foreign key (versionid)
	references xr.repositoryobjectversion(id)
	on delete cascade;

alter table xr.objectgroup_content add constraint ogc_memberid
	foreign key (memberid)
	references xr.repositoryobjectresource(id)
	on delete cascade;

create table xr.externaldependency_content (
	versionid		varchar(48) not null,
	objectid		varchar(48) not null
	);

alter table xr.externaldependency_content add constraint edc_objectid
	foreign key (objectid)
	references xr.repositoryobjectresource(id)
	on delete cascade;

create table xr.objectassociation (
	id			varchar(48) not null primary key,
	sourceid		varchar(48) not null,
	targetid		varchar(48) not null
	);

alter table xr.objectassociation add constraint oa_id
	foreign key (id)
	references xr.repositoryobjectresource(id)
	on delete cascade;

alter table xr.objectassociation add constraint oa_sourceid
	foreign key (sourceid)
	references xr.repositoryobjectresource(id)
	on delete cascade;

alter table xr.objectassociation add constraint oa_targetid
	foreign key (targetid)
	references xr.repositoryobjectresource(id)
	on delete cascade;

create table xr.classificationscheme (
	id			varchar(48) not null primary key,
	structuretype	varchar(48) not null
	);

alter table xr.classificationscheme add constraint cs_id
	foreign key (id)
	references xr.repositoryobjectresource(id)
	on delete cascade;

create table xr.classification_values (
	versionid		varchar(48) not null,
	valueid		varchar(48) not null,
	value			varchar(128) not null,
	description		varchar(256),
	parentvalueid	varchar(48)
	);

alter table xr.classification_values add primary key (versionid, valueid);

alter table xr.classification_values add constraint cv_versionid
	foreign key (versionid)
	references xr.repositoryobjectversion(id)
	on delete cascade;

alter table xr.classification_values add constraint cv_parentvalueid
	foreign key (versionid, parentvalueid)
	references xr.classification_values(versionid, valueid)
	on delete cascade;

create table xr.classification_value_ancestors (
	versionid		varchar(48) not null,
	valueid		varchar(48) not null,
	ancestorid		varchar(48) not null
	);

alter table xr.classification_value_ancestors add constraint cva_versionid
	foreign key (versionid)
	references xr.repositoryobjectversion(id)
	on delete cascade;

alter table xr.classification_value_ancestors add constraint cva_valueid
	foreign key (versionid, valueid)
	references xr.classification_values(versionid, valueid)
	on delete cascade;

alter table xr.classification_value_ancestors add constraint cva_ancestorid
	foreign key (versionid, ancestorid)
	references xr.classification_values(versionid, valueid)
	on delete cascade;

create table xr.classifications (
	objectversionid	varchar(48) not null,
	valueid		varchar(48) not null
	);

create view  xr.classificationcurrentvalueview (
	valueid,
	value
) as select 
	v.valueid,
	v.value
from  xr.classification_values v, xr.repositoryobjectresource ror
where v.versionid = ror.currentversion;

create view  xr.classificationschemecurrentversionview (
	id,
	versionid,
	name,
	uname,
	versionName,
	uri,
	versionuri,
	folderid,
	ownerid,