ejbql.sql

derby database source code.good for you.

create table mod(mod int);
insert into mod values(1);
select mod from mod;
select mod(mod,mod) from mod;
drop table mod;

drop table modfn;

-- Using Strings in escape function
create table myStr( a varchar(10) );
insert into myStr values ( '123' );
insert into myStr values ( ' 123' );
insert into myStr values ( ' 12 ' );
insert into myStr values ( '  2 ' );
insert into myStr values ( '1a3' );
select * from myStr;
select sqrt(a) from myStr;
select {fn sqrt(a)} from myStr;
select {fn sqrt( '-12' ) } from myStr;
select {fn sqrt( '-1a2' ) } from myStr;

drop table myreal;
drop table myStr;
-- End of SQRT test

-- CHAR AND VARCHAR 
--
-- create some tables
create table t1 (c15a char(15), c15b char(15), vc15a varchar(15),
	vc15b varchar(15), lvc long varchar);
create table t2 (c20 char(20), c30 char(30), c40 char(40),
				 vc20 varchar(20), vc30 varchar(30), vc40 varchar(40),
				lvc long varchar);

-- populate the tables
insert into t1 (c15a) values(null);
insert into t1 values('1', '2', '3', '4', '5');
insert into t1 values('111111', '222222222222222', 
					  '333333', '444444444444444',
					'555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555');
insert into t1 values('555555    ', '66          ', 
					  '777777    ', '88          ',
					'99999999999999999999999999999999999999999999999999999999999999999999999999999999999                                                                     ');

-- negative tests

-- mixing char and bit (illegal)
values X'11' || 'asdf';
values 'adsf' || X'11';

-- ? parameter on both sides
values ? || ?;

-- simple positive
values 'aaa' || 'bbb';
values X'aaaa' || X'bbbb';

-- non-blank truncation error on char result
insert into t2 (c20) select c15a || c15b from t1 where c15a = '111111';
insert into t2 (vc20) select vc15a || vc15b from t1 where c15a= '111111';
insert into t2 (c20) select lvc || lvc from t1 where c15a = '111111';

maximumdisplaywidth 512;

-- positive tests
-- blank truncation on varchar
insert into t2 (c20) select vc15a || vc15b from t1 where c15a = '555555    ';
select c20 from t2;
delete from t2;

-- no blank truncation on char
insert into t2 (c30) select c15a || c15b from t1 where c15a = '555555    ';
select c30 from t2;
delete from t2;

-- long varchar
insert into t2 (c30) select lvc || lvc from t1 where c15a = '1';
select c30 from t2;
delete from t2;

-- vc || c -> vc
insert into t2 (c30) select vc15a || c15a from t1 where c15a = '555555    ';
select c30 from t2;
delete from t2;

-- c || vc -> vc
insert into t2 (c30) select c15a || vc15a || '9' from t1 where c15a = '555555    ';
select c30 from t2;
delete from t2;

-- vc || c -> lvc
insert into t2 (lvc) select c15a || vc15a from t1 where c15a = '555555    ';
select lvc from t2;
select length(lvc) from t2;
delete from t2;

-- lvc || lvc - > lvc
insert into t2 (lvc) select lvc || lvc from t1;
select lvc from t2;
delete from t2;

-- Parameters can be used in DB2 UDB if one operand is either CHAR(n) or VARCHAR(n), 
-- where n is less than 128, then other is VARCHAR(254 - n). 
-- In all other cases the data type is VARCHAR(254).
autocommit off;
-- ? || c
prepare pc as 'select ? || c15a from t1';

execute pc using 'values (''left'')';

-- c || ?
prepare cp as 'select c15a || ? from t1';

execute cp using 'values (''right'')';

-- ? || v
prepare pv as 'select ? || vc15a from t1';

execute pv using 'values (''left'')';

-- v || ?
prepare vp as 'select vc15a || ? from t1';

execute vp using 'values (''right'')';

-- Parameters cannot be used in DB2 UDB 
-- if one operand is a long varchar [for bit data] data type. 
-- An invalid parameter marker error is thrown in DB2 UDB (SQLSTATE 42610).

-- lvc || ?
prepare lvp as 'select lvc || ? from t1';

execute lvp using 'values (''right'')';

-- ? || lvc
prepare plv as 'select ? || lvc from t1';

execute plv using 'values (''left'')';
autocommit on;

-- multiple concatenations
insert into t2 (c30, vc30) values ('111  ' || '222  ' || '333   ',
								   '444  ' || '555  ' || '666   ');
select c30, vc30 from t2;
delete from t2;

-- concatenation on a long varchar
create table t3 (c1 long varchar, c2 long varchar);
insert into t3 values ('c1   ', 'c2');
insert into t2 (c30, vc30) select t3.c1 || t3.c2, t3.c2 || t3.c1 from t3;
select c30, vc30 from t2;
delete from t2;

-- drop the tables
drop table t1;
drop table t2;
drop table t3;

------------------------------------------------------------------------------------
-- CHAR (n) FOR BIT DATA AND VARCHAR (n) FOR BIT DATA

-- try some cases zero length cases
values X''|| X'80';
values X'01'|| X'';

-- create some tables


create table t1 (b16a char(2) for bit data, b16b char(2) for bit data, vb16a varchar(2) for bit data, vb16b varchar(2) for bit data, lbv long varchar for bit data);
create table t2 (b20 char(3) for bit data, b60 char(8) for bit data, b80 char(10) for bit data,
				 vb20 varchar(3) for bit data, vb60 varchar(8) for bit data, vb80 varchar(10) for bit data, lbv long varchar for bit data);

-- populate the tables
insert into t1 (b16a) values(null);
insert into t1 values(X'11', X'22', X'33', X'44', X'55');
insert into t1 values(X'1111', X'2222', 
					  X'3333', X'4444',
					  X'5555');
insert into t1 values(X'5555', X'66', 
					  X'7777', X'88',
					  X'9999');

-- negative tests

-- non-blank truncation error on bit result
insert into t2 (b20) select b16a || b16b from t1 where b16a = X'1111';
insert into t2 (vb20) select vb16a || vb16b from t1 where b16a= X'1111';

-- positive tests
-- truncation on bit varying
insert into t2 (b20) select vb16a || vb16b from t1 where b16a = X'5555';
select b20 from t2;
delete from t2;

-- bc || b -> vb
insert into t2 (b80) select vb16a || b16a from t1 where b16a = X'5555';
select b80 from t2;
delete from t2;

-- b || vb -> vb
insert into t2 (b80) select b16a || vb16a || X'99' from t1 where b16a = X'5555';
select b80 from t2;
delete from t2;

-- b || lbv -> lbv
insert into t2 (lbv) select b16a || lbv from t1 where b16a = X'5555';
select lbv from t2;
delete from t2;

-- lbv || b -> lbv
insert into t2 (lbv) select lbv || b16a from t1 where b16a = X'5555';
select lbv from t2;
delete from t2;

-- vb || lbv -> lbv
insert into t2 (lbv) select vb16a || lbv from t1 where b16a = X'5555';
select lbv from t2;
delete from t2;

-- lbv || vb -> lbv
insert into t2 (lbv) select lbv || vb16a from t1 where b16a = X'5555';
select lbv from t2;
delete from t2;

-- Parameters can be used in DB2 UDB 
-- if one operand is either CHAR(n) [for bit data] or VARCHAR(n) [for bit data], 
-- where n is less than 128, then other is VARCHAR(254 - n). 
-- In all other cases the data type is VARCHAR(254).
autocommit off;

-- ? || b
prepare pb as 'select ? || b16a from t1';

execute pb using 'values (X''ABCD'')';

-- b || ?
prepare bp as 'select b16a || ? from t1';

execute bp using 'values (X''ABCD'')';

-- ? || vb
prepare pvb as 'select ? || vb16a from t1';

execute pvb using 'values (X''ABCD'')';

-- vb || ?
prepare vbp as 'select vb16a || ? from t1';

execute vbp using 'values (X''ABCD'')';

-- Parameters cannot be used in DB2 UDB 
-- if one operand is a long varchar [for bit data] data type. 
-- An invalid parameter marker error is thrown in DB2 UDB (SQLSTATE 42610).

-- ? || lbv
prepare plbv as 'select ? || lbv from t1';

execute plbv using 'values (X''ABCD'')';

-- lbv || ?
prepare lbvp as 'select lbv || ? from t1';

execute lbvp using 'values (X''ABCD'')';
autocommit on;

-- multiple concatenations
insert into t2 (b80, vb80, lbv) values (X'e0' || X'A0' || X'20',
								   X'10' || X'11' || X'e0',
								   X'1234' || X'A0' || X'20');
select b80, vb80, lbv from t2;
delete from t2;

-- concatenation on a byte
create table t3 (b1 char(1) for bit data, b2 char(1) for bit data);
insert into t3 values (X'11', X'22');
insert into t2 (b80, vb80) select t3.b1 || t3.b2, t3.b2 || t3.b1 from t3;
select b80, vb80 from t2;
delete from t2;

-- clean up the prepared statements
remove pc;
remove cp;
remove vp;
remove pv;
remove pb;
remove bp;
remove pvb;
remove vbp;
remove lvp;
remove plv;
remove plbv;
remove lbvp;

-- drop the tables
drop table t1;
drop table t2;
drop table t3;
-- reset maximumdisplaywidth
maximumdisplaywidth 128;
--
--
-- the like tests are all run through the unit test
-- mechanism that is fired off with this test's
-- properties file.  that test tests all the %, _ combinations
-- to exhaustion.
--
-- we show that the language level support works, here, which is:
-- the syntax
-- char and varchar columns
-- not can be applied and pushed around with it
-- parameters (would need to be .java to show completely...)
-- not other types of columns
--

create table t (c char(20), v varchar(20), lvc long varchar);
insert into t values('hello','world', 'nice day, huh?');
insert into t values('goodbye','planet', 'see you later');
insert into t values('aloha','orb', 'hang loose');

-- subquery on left side
select * from t where (select max(c) from t) like '%';

select * from t where c like 'h%';
select * from t where v like '%or%';
select * from t where lvc like '%y%';

-- these four should all have the same results:
select * from t where not v like '%or%';
select * from t where not (v like '%or%');
select * from t where 1=0 or not v like '%or%';
select * from t where not (1=0 or not v not like '%or%');

-- these two should have the same results:
select * from t where c like '%lo%' or v like '%o%';
select * from t where v like '%o%' or c like '%lo%';

-- these three should have the same results:
select * from t where c like '%lo%' and 0=0;
select * from t where c like '%lo%' and 1=1;
select * from t where 1=1 and c like '%lo%';

-- we can at least show the parameters compile...
autocommit off;
prepare s as 'select * from t where v like ?';
execute s;
prepare s as 'select * from t where ? like ?';
execute s;
prepare s as 'select * from t where c like ?';
execute s;
prepare s as 'select * from t where lvc like ?';
execute s;
prepare s as 'select * from t where lvc like ?';
execute s;
autocommit on;

create table n (i int, r real, d date, u char(10));

-- these should succeed
insert into n values (1, 1.1, date('1111-11-11'), '%');
insert into n values (2, 2.2, date('2222-2-2'), 'haha');
select * from n where u like 'haha______';

-- now, with an index
create table m (i int, r real, d date, u varchar(10));
insert into m select * from n;
select * from m where u like 'haha';
select * from m where u like 'haha______';
create index i1 on m(u);
select * from m where u like 'haha';
select * from m where u like 'haha______';

-- tests for column like constant optimization
create table u (c char(10), vc varchar(10));
insert into u values ('hello', 'hello');
select * from u where c like 'hello';
select * from u where vc like 'hello';
select * from u where c like 'hello     ';
select * from u where vc like 'hello     ';

-- cleanup
drop table t;
drop table n;
drop table m;
drop table u;

-- testing JDBC escaped length function
-- JDBC length is defined as the number of characters in a string without trailing blanks.
values {FN LENGTH('hello     ') };
values {FN LENGTH(rtrim('hello     ')) };

-- defect 5749. rtrim() over substr() used to raise ASSERT failure.
create table t1 (c1 char(10));
insert into t1 values ('testing');
select rtrim(substr(' asdf', 1, 3)) from t1;