ejbql.sql

derby database source code.good for you.

-- This test EJBQL Absolute function. Resolve 3535
-- Begin of ABS/ABSVAL test.  For all valid types, un-escaped function.


-- Integer has a range of -2147483648 to 2147483647
-- Basic
create table myint( a int );
create table myinteger( a integer );
select abs(a) from myint;
select abs(a) from myinteger;
insert into myint values (null), (+0), (-0), (+1), (-1), (1000), (-1000), (null), (2147483647), (-2147483647);
insert into myinteger values (NULL), (+0), (-0), (+1), (-1), (1000), (-1000), (NULL), (2147483647), (-2147483647);
select a from myint;
select a from myinteger;
select abs(a) from myint;
select abs(a) from myinteger;
select -abs(a) from myint;
select -abs(a) from myinteger;
select abs(abs(-abs(-abs(a)))) from myint;
select abs(abs(-abs(-abs(a)))) from myinteger;
SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYINT;
SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYINTEGER;
insert into myint values (-2147483648);
insert into myinteger values (-2147483648);
select a from myint where a=-2147483648;
select a from myinteger where a=-2147483648;
-- Error
select -a from myint where a=-2147483648;
select -a from myinteger where a=-2147483648;
select abs(-a) from myint where a=-2147483648;
select abs(-a) from myinteger where a=-2147483648;
select abs(a) from myint where a=-2147483648;
select abs(a) from myinteger where a=-2147483648;
select abs(-abs(a)) from myint where a=-2147483648;
select abs(-abs(a)) from myinteger where a=-2147483648;
drop table myint;
drop table myinteger;
-- End of Integer test


-- Smallint has a range of -32768 to 32767
-- Basic
create table mysmallint( a smallint );
select abs(a) from mysmallint;
insert into mysmallint values (null), (+0), (-0), (+1), (-1), (1000), (-1000), (null), (32767), (-32767);
select a from mysmallint;
select abs(a) from mysmallint;
select -abs(a) from mysmallint;
select abs(abs(-abs(-abs(a)))) from mysmallint;
SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYSMALLINT;
insert into mysmallint values (-32768);
select a from mysmallint where a=-32768;
-- Error
select -a from mysmallint where a=-32768;
select abs(-a) from mysmallint where a=-32768;
select abs(a) from mysmallint where a=-32768;
select abs(-abs(a)) from mysmallint where a=-32768;
drop table mysmallint;
-- End of Smallint test


-- Bigint has a range of -9223372036854775808 to 9223372036854775807
-- Basic
create table mybigint( a bigint );
select abs(a) from mybigint;
insert into mybigint values (null), (+0), (-0), (+1), (-1), (1000), (-1000), (null), (9223372036854775807), (-9223372036854775807);
select a from mybigint;
select abs(a) from mybigint;
select -abs(a) from mybigint;
select abs(abs(-abs(-abs(a)))) from mybigint;
SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYBIGINT;
insert into mybigint values (-9223372036854775808);
select a from mybigint where a=-9223372036854775808;
-- Error
select -a from mybigint where a=-9223372036854775808;
select abs(-a) from mybigint where a=-9223372036854775808;
select abs(a) from mybigint where a=-9223372036854775808;
select abs(-abs(a)) from mybigint where a=-9223372036854775808;
drop table mybigint;
-- End of Bigint test


-- REAL has a range of +/-1.175E-37 to +/-3.402E+38 
-- Basic
create table myreal( a real );
select abs(a) from myreal;
insert into myreal values (null), (+0), (-0), (+1), (-1), (null), (100000000), (-100000000),
(3.402E+38), (-3.402E+38),
(1.175E-37), (-1.175E-37);
select a from myreal;
select -a from myreal;
select abs(a) from myreal;
select abs(-a) from myreal;
select -abs(a) from myreal;
select abs(abs(-abs(-abs(a)))) from myreal;
SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYREAL;
select distinct abs(a) from myreal;
---- There is nothing wrong with returning 1.0.  The float overflows and this is just the way it behaves.
-- this used to work on CS, not any more when adopted to DB2 style floats
-- since contant numbers are (parsed as) doubles
select abs(-abs(a)) + 1 from myreal where a=1.175E-37;
-- when casted to a real, it is found
select abs(-abs(a)) + 1 from myreal where a=cast(1.175E-37 as real);
-- Error
insert into myreal values ( 3.402E+38 *2);
insert into myreal values (-3.402E+38 *2);
drop table myreal;
-- End of Real test


-- Double Precision has a range of +/-2.225E-307 to +/-1.79769E+308 
-- Basic
create table mydoubleprecision( a double precision );
select abs(a) from mydoubleprecision;
insert into mydoubleprecision values (null), (+0), (-0), (+1), (-1), (100000000), (-100000000), (null),
(1.79769E+308), (-1.79769E+308),
(2.225E-307), (-2.225E-307);
select a from mydoubleprecision;
select -a from mydoubleprecision;
select abs(a) from mydoubleprecision;
select abs(-a) from mydoubleprecision;
select -abs(a) from mydoubleprecision;
select abs(abs(-abs(-abs(a)))) from mydoubleprecision;
SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYDOUBLEPRECISION;
select distinct abs(a) from mydoubleprecision;
-- There is nothing wrong with returning 1.0.  The double overflows and this is just the way it behaves.
select abs(-abs(a)) + 1 from mydoubleprecision where a=2.225E-307;
-- Error
insert into mydoubleprecision values ( 1.79769E+308 *2);
insert into mydoubleprecision values (-1.79769E+308 *2);
drop table mydoubleprecision;
-- End of Double Precision test


-- Float has a the range or a REAL or 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 abs(a), abs(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), (-1, -1),
(100000000, 100000000), (-100000000, -100000000), (null, null),
(1.79769E+308, 3.402E+38),
(-1.79769E+308, -3.402E+38),
(2.225E-307, 1.175E-37),
(-2.225E-307, -1.175E-37);
select a, b from myfloat;
select -a, -b from myfloat;
select abs(a), abs(b) from myfloat;
select abs(-a), abs(-b) from myfloat;
select -abs(a), -abs(b) from myfloat;
select abs(abs(-abs(-abs(a)))), abs(abs(-abs(-abs(b)))) from myfloat;
SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))), ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(B)))) FROM MYFLOAT;
select distinct abs(a) from myfloat;
-- -- There is nothing wrong with returning 1.0.  The float overflows and this is just the way it behaves.
-- this used to work in CS, but no more, = on floating point values isn't really useful
select abs(-abs(a)) + 1, abs(-abs(b)) + 1 from myfloat where a=2.225E-307 AND b=1.175E-37;
select abs(-abs(a)) + 1, abs(-abs(b)) + 1 from myfloat where b=3.402E+38;
-- 'real =' works on DB2 and DB2 Cloudscape
select abs(-abs(a)) + 1, abs(-abs(b)) + 1 from myfloat where b=cast(3.402E+38 as real);
select abs(-abs(a)) + 1, abs(-abs(b)) + 1 from myfloat where a=2.225E-307 AND b=cast(1.175E-37 as real);
select abs(-abs(a)) + 1, abs(-abs(b)) + 1 from myfloat where a=2.225E-307;
-- Error
insert into myfloat values ( 1.79769E+308 *2, 3.402E+38 *2);
insert into myfloat values (-1.79769E+308 *2, -3.402E+38 *2);
insert into myfloat values ( 2.225E-307, 3.402E+38 *2);
insert into myfloat values (-2.225E-307, -3.402E+38 *2);
drop table myfloat;
-- End of Float test


-- Decimal is java.math.BigDecimal
-- Basic
create table myDecimal( a decimal(31, 0), b decimal(31,31));
select abs(a) from myDecimal;
insert into myDecimal values (null,0), (+0,0), (-0,0), (+1,0), (-1,0), 
(100000000,.10000000), (-100000000,-.10000000), (null,null), 
(1.0e30, 1.0e-30), 
(-1.0e30, -1.0e-30);
select a from myDecimal;
select -a from myDecimal;
select b from myDecimal;
select -b from myDecimal;
select abs(a) from myDecimal;
select abs(-a) from myDecimal;
select -abs(a) from myDecimal;
select abs(abs(-abs(-abs(a)))) from myDecimal;
SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYDECIMAL;
select distinct abs(a) from myDecimal;
select abs(b) from myDecimal;
select abs(-b) from myDecimal;
select -abs(b) from myDecimal;
select abs(abs(-abs(-abs(b)))) from myDecimal;
SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(B)))) FROM MYDECIMAL;
select distinct abs(b) from myDecimal;
-- There is nothing wrong with returning 1.0.  The decimal overflows and this is just the way it behaves.  Needs to make this compatible with jdk1.1.8(which had a bug).
select abs(-abs(a)) + 1 from myDecimal;
drop table myDecimal;
-- End of Decimal test


-- Numeric java.math.BigDecimal
-- Basic
create table myNumeric( a decimal(31,0), b decimal(31,31 ));
select abs(a) from myNumeric;
insert into myNumeric values (null), (+0), (-0), (+1), (-1),
(100000000), (-100000000), (null),
(1.0e31, ,1.0e-31),
(-1.0e31, -1.0e-31 ), 
select a from myNumeric;
select -a from myNumeric;
select b from myNumeric;
select -b from myNumeric;
select abs(a), abs(b)from myNumeric;
select abs(-a), abs(-b) from myNumeric;
select -abs(a), -abs(b)  from myNumeric;
select abs(abs(-abs(-abs(a)))) from myNumeric;
SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYNUMERIC; 
select distinct abs(a) from myNumeric;
-- There is nothing wrong with returning 1.0.  The numeric overflows and this is just the way it behaves.  Needs to make this compatible with jdk1.1.8(which had a bug).
select abs(-abs(a)) + 1 from myNumeric;
drop table myNumeric;
-- End of Numeric test

-- Test some different statements, just in case
create table foo( a int );
insert into foo values ( abs( 1) );
insert into foo values ( abs(-2) );
insert into foo values (-abs(-3) );
insert into foo values (-abs( 4) );
insert into foo values (          -5  );
insert into foo values (          -6  );
insert into foo values (          -7  );

autocommit off;
prepare p1 as 'select a from foo';
prepare p2 as 'select abs(a) from foo';
prepare p3 as 'insert into foo select a*(-1) from foo';
execute p1;
execute p2;
execute p3;
execute p1;
insert into foo values( abs( 8 ) );
insert into foo values( abs(-9 ) );
insert into foo values(-abs(-10) );
insert into foo values( abs( 11) );
insert into foo values(          -12  );
execute p1;
execute p2;
execute p3;
execute p1;
rollback;
commit;

autocommit on;
insert into foo values( abs( 13) );
insert into foo values( abs(-14) );
insert into foo values(-abs(-15) );
insert into foo values(-abs( 16) );
insert into foo values(          -17  );
execute p1;
execute p2;
execute p3;
execute p1;
select * from foo;
drop table foo;
-- End of ABS/ABSVAL test.  For all valid types.  Un-escaped function.

-- abs is not a reserved word
create table abs( a int );
drop table abs;

-- This test EJBQL Absolute function. Resolve 3535
-- Begin of ABS test.  For escape function.

-- Integer
-- Basic
-- beetle 5805 - support INT[EGER] built-in function
values{fn abs(INT(' 0')               )};
values{fn abs(INT('-0')               )};
values{fn abs(INT(' 1')               )};
values{fn abs(INT('-1')               )};
values{fn abs(INT(' 1000000')         )};
values{fn abs(INT('-1000000')         )};
values{fn abs(INT(' 2147483647')      )};
values{fn abs(INT('-2147483648') + 1  )};
-- Error
values{fn abs(INT('-2147483648')      )};
values{fn abs(INT(' 2147483647') + 1  )};

-- Smallint
-- Basic
-- beetle 5807 - support SMALLINT built-in function
values{fn abs( SMALLINT(' 0')         )};
values{fn abs( SMALLINT('-0')         )};
values{fn abs( SMALLINT(' 1')         )};
values{fn abs( SMALLINT('-1')         )};
values{fn abs( SMALLINT(' 10000')     )};
values{fn abs( SMALLINT('-10000')     )};
values{fn abs( SMALLINT(' 32767')     )};
values{fn abs( SMALLINT('-32768') + 1 )};
values{fn abs(-SMALLINT('-32768')     )};
-- Error
values{fn abs(-SMALLINT(' 32768')     )};
values{fn abs( SMALLINT('-32768')     )};

-- Bigint
-- Basic
-- beetle 5809 - support BIGINT built-in function
values{fn abs( BIGINT(' 0')                       )};
values{fn abs( BIGINT('-0')                       )};
values{fn abs( BIGINT(' 1')                       )};
values{fn abs( BIGINT('-1')                       )};
values{fn abs( BIGINT(' 100000000000')            )};
values{fn abs( BIGINT('-100000000000')            )};
values{fn abs( BIGINT(' 9223372036854775807')     )};
values{fn abs( BIGINT('-9223372036854775808') + 1 )};
-- Error
values{fn abs(-BIGINT('-9223372036854775808')     )};
values{fn abs( BIGINT('-9223372036854775808')     )};

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

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

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


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

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

-- End of ABS test.  For escaped function.
-- This test EJBQL Absolute function. Resolve 3535
-- Begin of ABSVAL test.  For all valid types, un-escaped function.


-- Integer has a range of -2147483648 to 2147483647
-- Basic

create table myint( a int );
select abs(a) from myint;
insert into myint values (null);
select abs(a) from myint;
autocommit off;

-- Prepare Statements, should pass and return 1
prepare p1 as 'select abs(?) from myint';
prepare p1 as 'select 1 from myint where ? <= 4';
execute p1 using 'values absval( 4 )';
execute p1 using 'values absval( -4 )';
execute p1 using 'values absval( 4.4 )';
execute p1 using 'values absval( -4.4 )';