ejbql.sql

derby database source code.good for you.

drop table mytime;

-- bug 5794 - LOCATE built-in function is not db2 udb compatible
create table mytimestamp( a timestamp, b timestamp );
insert into mytimestamp values( timestamp('1997-01-01 03:03:03'), timestamp('1997-01-01 03:03:03' ));
insert into mytimestamp values( timestamp('1997-01-01 03:03:03'), timestamp('1997-01-01 04:03:03' ));
select locate(a, b) from mytimestamp;
drop table mytimestamp;

-- End of ejbql_locate2.sql test


-- This test the EJBQL function, LOCATE. Resolve 3535

-- LOCATE( string1, string2[, start] ) --- string1, searching from the beginning
--   of string2; if start is specified, the search begins from position start.
--   0 is returned if string2 does not contain string1.  Position1 is the first
--   character in string2.

-- Begin of LOCATE test
-- Basic

-- AUTHOR'S NOTE: This test highlights the difference between Oracle8i,
--    IBM DB2, and Cloudscape.

create table foo( a varchar(10), b varchar(20) );
insert into foo values( 'abc', 'abcd' );
insert into foo (a,b) values ( 'ABC', NULL );
insert into foo (a,b) values ( NULL, 'DEF' );
insert into foo (a,b) values ( 'ABC', '') ;
insert into foo (a,b) values ( '', 'DEF' );
insert into foo (a,b) values ( '', '' );
insert into foo (a,b) values ( NULL, NULL );
insert into foo (a,b) values ( 'GHJK', 'GHJ' );
insert into foo (a,b) values ( 'QWE', 'QWERT' );
insert into foo (a,b) values ( 'TYUI', 'RTYUI' );
insert into foo (a,b) values ( 'IOP', 'UIOP[' );
insert into foo (a,b) values ( 'ZXCV', 'ZXCV' );
select * from foo;
select locate(a, b) from foo;
select locate(a, b, 0) from foo;
select locate(a, b, -1) from foo;
select locate(a, b, 1) from foo;
select locate(a, b, 2) from foo;
select locate(a, b, 200) from foo;

drop table foo;
-- End of ejbql_locate3.sql test
-- This test the EJBQL function, LOCATE. Resolve 3535

-- LOCATE( string1, string2[, start] ) --- string1, searching from the beginning
--   of string2 }; if start is specified, the search begins from position start.
--   0 is returned if string2 does not contain string1.  Position1 is the first
--   character in string2.

-- Begin of LOCATE test
-- Basic

create table loc( c varchar(20) default null, a int default null, b int default null);

insert into loc (c) values ('This world is crazy' );
insert into loc (c) values ('nada' );
insert into loc (b) values ( 3 );
select * from loc;
select c, locate( 'crazy', c ) from loc;
autocommit off;

-- Prepare Statements
prepare p1 as 'select locate( ''crazy'', c ) from loc';
execute p1;
-- first arg ?
prepare p2 as 'select locate( ?, c ) from loc';
execute p2 using 'values ( ''crazy'' )';
execute p2 using 'values ( ''hahah'' )';
-- second arg ?
prepare p3 as 'select locate( ''nada'', ? ) from loc';
execute p3 using 'values ( ''nada'' )';
execute p3 using 'values ( ''haha'' )';
-- both first and second arguments ? ?
prepare p4 as 'select locate( ?, ? ) from loc';
execute p4 using 'values ( ''dont'', ''match'' )';
execute p4 using 'values ( ''match'', ''me match me'' )';
-- thrid arg ?
prepare p5 as 'select locate( c, c, ? ) from loc';
execute p5 using 'values ( 1 )';
execute p5 using 'values ( 2 )';
-- all args ? ? ?
prepare p6 as 'select locate( ?, ?, ? ) from loc';
execute p6 using 'values ( ''hello'', ''no match'', 1 )';
execute p6 using 'values ( ''match'', ''me match me'', 2 )';

-- Prepare Statements
prepare p7 as 'select {fn locate( ''crazy'', c )} from loc';
execute p7;
-- first arg ?
prepare p7 as 'select {fn locate( ?, c )} from loc';
execute p7 using 'values ( ''crazy'' )';
execute p7 using 'values ( ''hahah'' )';
-- second arg ?
prepare p8 as 'select {fn locate( ''nada'', ? )} from loc';
execute p8 using 'values ( ''nada'' )';
execute p8 using 'values ( ''haha'' )';
-- both first and second arguments ? ?
prepare p9 as 'select {fn locate( ?, ? )} from loc';
execute p9 using 'values ( ''dont'', ''match'' )';
execute p9 using 'values ( ''match'', ''me match me'' )';
-- thrid arg ?
prepare p10 as 'select {fn locate( c, c, ? )} from loc';
execute p10 using 'values ( 1 )';
execute p10 using 'values ( 2 )';
-- all args ? ? ?
prepare p11 as 'select {fn locate( ?, ?, ? )} from loc';
execute p11 using 'values ( ''hello'', ''no match'', 1 )';
execute p11 using 'values ( ''match'', ''me match me'', 2 )';
autocommit on;

drop table loc;

-- End of LOCATE test
-- This test EJBQL Sqrt function. Resolve 3535
-- Begin of SQRT test.  For all valid types, un-escaped function.


-- Real has a range of +/-1.4E-45 to +/-3.4028235E+38
-- Basic
create table myreal( a real );
select sqrt(a) from myreal;
insert into myreal values (null), (+0), (-0), (+1), (null), (100000000),
(3.402E+38), (1.175E-37);
select a from myreal;
select sqrt(a) from myreal;
select -sqrt(a) from myreal;
select sqrt(sqrt(-sqrt(-sqrt(a)))) from myreal;
SELECT SQRT(SQRT(-SQRT(-SQRT(A)))) FROM MYREAL;
select sqrt(sqrt(sqrt(sqrt(a)))) from myreal;
select distinct sqrt(a) from myreal;
drop table myreal;
-- End of Real test


-- Double Precision has a range of +/-4.9E-324 to +/-1.7976931348623157E+308
-- Basic
create table mydoubleprecision( a double precision );
select sqrt(a) from mydoubleprecision;
insert into mydoubleprecision values (null), (+0), (-0), (+1), (100000000), (null),
(1.79769E+308), (2.225E-307);
select a from mydoubleprecision;
select sqrt(a) from mydoubleprecision;
select -sqrt(a) from mydoubleprecision;
select sqrt(sqrt(-sqrt(-sqrt(a)))) from mydoubleprecision;
SELECT SQRT(SQRT(-SQRT(-SQRT(A)))) FROM MYDOUBLEPRECISION;
select sqrt(sqrt(sqrt(sqrt(a)))) from mydoubleprecision;
select distinct sqrt(a) from mydoubleprecision;
drop table mydoubleprecision;
-- End of Double Precision test


-- Float has a the range or a java.lang.Float or java.lang.Double depending on
-- the precision you specify.  Below a is a double, b is a float
create table myfloat( a float, b float(23) );
select sqrt(a), sqrt(b) from myfloat;
select columnname, columndatatype
from sys.syscolumns c, sys.systables t where c.referenceid = t.tableid and t.tablename='MYFLOAT';
insert into myfloat values (null, null), (+0, +0), (-0, -0), (+1, +1), (100000000, 100000000), (null, null),
(1.79769E+308, 3.402E+38),
(2.225E-307, 1.175E-37);
select a, b from myfloat;
select sqrt(a), sqrt(b) from myfloat;
select -sqrt(a), -sqrt(b) from myfloat;
select sqrt(sqrt(-sqrt(-sqrt(a)))), sqrt(sqrt(-sqrt(-sqrt(b)))) from myfloat;
SELECT SQRT(SQRT(-SQRT(-SQRT(A)))), SQRT(SQRT(-SQRT(-SQRT(B)))) FROM MYFLOAT;
select sqrt(sqrt(sqrt(sqrt(a)))), sqrt(sqrt(sqrt(sqrt(b)))) from myfloat;
select distinct sqrt(a) from myfloat;
select distinct sqrt(b) from myfloat;
drop table myfloat;
-- End of Float test


-- Test some different statements, just in case
-- beetle 5804 - support FLOAT built-in function
create table foo( a float );
insert into foo values ( sqrt(FLOAT( 1)));
insert into foo values ( sqrt(FLOAT( 2)));
insert into foo values (-sqrt(FLOAT( 3)));
insert into foo values (-sqrt(FLOAT( 4)));
insert into foo values (     (FLOAT(-5)));
-- this insert should fail
insert into foo values ( sqrt(FLOAT(-3)));

autocommit off;
prepare p1 as 'select a from foo';
prepare p2 as 'insert into foo select a*(-1) from foo';
execute p1;
execute p2;
execute p1;
insert into foo values ( sqrt(FLOAT( 6)));
insert into foo values (-sqrt(FLOAT( 7)));
insert into foo values (-sqrt(FLOAT( 8)));
insert into foo values ( sqrt(FLOAT( 9)));
insert into foo values (     (FLOAT(10)));
-- few negative tests
-- insert should fail
insert into foo values ( sqrt(FLOAT(-7)));
prepare p3 as 'select sqrt(a) from foo';
-- executing p3 should fail
execute p3;

-- these should pass
execute p1;
execute p2;
execute p1;
rollback;
commit;

autocommit on;
insert into foo values ( sqrt(FLOAT( 11)));
insert into foo values (-sqrt(FLOAT( 12)));
insert into foo values (-sqrt(FLOAT( 13)));
insert into foo values (-sqrt(FLOAT( 14)));
insert into foo values (     (FLOAT( 15)));
-- these 2 inserts should fail
insert into foo values (-sqrt(FLOAT(-12)));
insert into foo values ( sqrt(FLOAT(-13)));

-- these should pass
autocommit off;
execute p1;
execute p3;
execute p1;
-- executing p2 should fail
execute p2;

autocommit on;
select * from foo;
drop table foo;
-- End of SQRT test.  For all valid types.  Un-escaped function.
-- This test EJBQL Sqrt function. Resolve 3535
-- Begin of SQRT test.


-- Integer, Smallint, Bigint, Decimal
create table myint( a int );
create table myinteger( a Integer );
select sqrt(a) from myint;
select sqrt(a) from myinteger;
drop table myint;
drop table myinteger;

create table mysmallint( a smallint );
select sqrt(a) from mysmallint;
drop table mysmallint;

create table mybigint( a bigint );
select sqrt(a) from mybigint;
drop table mybigint;

create table mydecimal( a decimal );
select sqrt(a) from mydecimal;
drop table mydecimal;


-- For escape function.


-- Integer
-- Basic
values{ fn sqrt(INT('0'))};

-- Smallint
-- Basic
-- beetle 5805 - support INT[EGER] built-in function
values{ fn sqrt(SMALLINT('0'))};

-- Bigint
-- Basic
-- beetle 5809 - support BIGINT built-in function
values{ fn sqrt(BIGINT('0'))};

-- Real
-- Basic
-- beetle 5806 - support REAL built-in function
values{fn sqrt( REAL( 0)                       )};
values{fn sqrt( REAL(-0)                       )};
values{fn sqrt( REAL( 1)                       )};
values{fn sqrt( REAL(-1)                       )};
values{fn sqrt( REAL( 1000000.001)             )};
values{fn sqrt( REAL(-1000000.001)             )};
values{fn sqrt( REAL( 3.402E+38)               )};
values{fn sqrt( REAL(-3.402E+38) + 1           )};
-- Error
values{fn sqrt( REAL( 3.402E+38 * 2)           )};
values{fn sqrt(-REAL( NaN)                     )};
values{fn sqrt( REAL( 1.40129846432481707e-45) )};
values{fn sqrt( REAL( 3.40282346638528860e+38) )};

-- Double Precision/Double
-- Basic
-- beetle 5803 - support DOUBLE_[PRECISION] built-in function
values{fn  sqrt( DOUBLE( 0)                      )};
values{fn  sqrt( DOUBLE(-0)                      )};
values{fn  sqrt( DOUBLE( 1)                      )};
values{fn -sqrt( DOUBLE(1)                       )};
values{fn  sqrt( DOUBLE( 1000000.001)            )};
values{fn -sqrt( DOUBLE(1000000.001)             )};
values{fn -sqrt( DOUBLE(1.79769E+308)            )};
values{fn  sqrt( DOUBLE( 1.79769E+308) + 1       )};
values{fn  sqrt( DOUBLE( 2.225E-307 + 1)         )};
-- Error
values{fn  sqrt( DOUBLE(-1)                      )};
values{fn  sqrt( DOUBLE(-1000000.001)            )};
values{fn  sqrt( DOUBLE(-1.79769E+308)           )};
values{fn  sqrt( DOUBLE( 1.79769E+308 * 2)       )};
values{fn  sqrt(-DOUBLE( NaN)                    )};
values{fn  sqrt( DOUBLE( 4.9E-324)               )};
values{fn  sqrt( DOUBLE( 1.7976931348623157E308) )};

-- Decimal/Numeric
-- Basic
-- beetle 5802 - support DEC[IMAL] built-in function
values{ fn sqrt(DEC('0'))};

-- More generic test
values{ fn sqrt( 0+1+.1 ) };
values{ fn sqrt( +0+1.000000001 ) };
VALUES{ FN sqrt( 100+200+300 ) };
values{ fn sqrt( 0-1-.1 ) };
values{ fn sqrt( -0-1.000000001 ) };
VALUES{ FN sqrt( 100-200-300 ) };

-- Error
values{ fn sqrt('null') };

-- sqrt as a keyword
create table sqrt( a int );

-- End of SQRT test.
-- This test EJBQL Sqrt function. Resolve 3535
-- Begin of SQRT test.  For all valid types, un-escaped function.

create table myreal( a real );
select sqrt(a) from myreal;
insert into myreal values ( 3.402E+38 );
select a from myreal;

-- Prepare Statements, should pass
-- beetle 5806 - support REAL built-in function
autocommit off;
prepare p1 as 'select a from myreal where ? <> 1';
execute p1 using 'values  sqrt(REAL( 0 ))';
execute p1 using 'values -sqrt(REAL( 20))';
execute p1 using 'values  sqrt(REAL( 20))';
-- this should fail
execute p1 using 'values  sqrt(REAL(-20))';

-- Prepare Statements, should pass
-- beetle 5806 - support REAL built-in function
prepare p2 as 'select a from myreal where ? <> 1';
execute p2 using 'values {fn sqrt (REAL( 0 ))}';
execute p2 using 'values {fn -sqrt(REAL( 20))}';
execute p2 using 'values {fn sqrt (REAL( 20))}';
-- this should fail
execute p2 using 'values {fn  sqrt(REAL(-20))}';
autocommit on;

-- mod function
create table modfn(s smallint, i int, b bigint, c char(10), d decimal(6,3), r real, dbl double);
insert into modfn values(0, 0, 0, '0', 0.0, 0.0, 0.0);
insert into modfn values(5, 5, 5, '5', 5.0, 5.0, 5.0);
insert into modfn values(null, null, null, null, null, null, null);
select { fn mod(s, 3) } from modfn;
select { fn mod(i, 3) } from modfn;
select { fn mod(b, 3) } from modfn;
select { fn mod(c, 3) } from modfn;
select { fn mod(d, 3) } from modfn;
select { fn mod(r, 3) } from modfn;
select { fn mod(dbl, 3) } from modfn;
select { fn mod(67, t) } from modfn where s <> 0;
select { fn mod(67, s) } from modfn where s <> 0;
select { fn mod(67, i) } from modfn where s <> 0;
select { fn mod(67, b) } from modfn where s <> 0;
select { fn mod(67, c) } from modfn where s <> 0;
select { fn mod(67, d) } from modfn where s <> 0;
select { fn mod(67, r) } from modfn where s <> 0;
select { fn mod(67, dbl) } from modfn where s <> 0;
select { fn mod(s, s) } from modfn where s = 0;
select { fn mod(i, i) } from modfn where s = 0;
select { fn mod(i, b) } from modfn where s = 0;
select { fn mod(s, s) } from modfn where s is null;
select { fn mod(i, i) } from modfn where s is null;
select { fn mod(i, b) } from modfn where s is null;

select { fn mod(67, i) } from modfn where i <> 0;
select { fn mod(67, b) } from modfn where b <> 0;
-- this query should fail because of incompatible arguments
select { fn mod('rrrr', b) } from modfn  where b <> 0;

values { fn mod(23, 9)};
values mod(23, 9);