| -- |
| -- Licensed to the Apache Software Foundation (ASF) under one or more |
| -- contributor license agreements. See the NOTICE file distributed with |
| -- this work for additional information regarding copyright ownership. |
| -- The ASF licenses this file to You under the Apache License, Version 2.0 |
| -- (the "License"); you may not use this file except in compliance with |
| -- the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| -- |
| |
| -- 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 CAST(t.tablename AS VARCHAR(128)) ='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; |
| |
| values absval( 4 ); |
| values absval( -4 ); |
| values absval( 4.4 ); |
| values absval( -4.4 ); |
| |
| values {fn abs( 4 )}; |
| values {fn abs( -4 )}; |
| values {fn abs( 4.4 )}; |
| values {fn abs( -4.4 )}; |
| values {fn abs( -4.44444444444444444444444 )}; |
| autocommit on; |
| |
| drop table myint; |
| -- 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 abs(a) from myStr; |
| select {fn abs(a)} from myStr; |
| drop table myStr; |
| -- End of ABSVAL test |
| |
| -- This test EJBQL function, CONCAT. Resolve 3535 |
| |
| -- Begin of CONCAT test |
| -- Basic |
| values{ fn concat( 'hello', ' world' ) }; |
| VALUES{ FN CONCAT( 'HELLO', ' WORLD' ) }; |
| values{ fn concat( '' , '' )}; |
| values{ fn concat( CHAR(''), CHAR('') ) }; |
| values{ fn concat( 45, 67 )}; |
| values{ fn concat( '45', 67 )}; |
| values{ fn concat( 45, '67' )}; |
| values{ fn concat( CHAR('C'), CHAR('#') ) }; |
| values{ fn concat( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ`1234567890-=\ [];,./ \'' |', |
| 'abcdefghijklmnopqrstuvwxyz~!@#$%^&*()_+|<>?:"{} '''''' ' ) }; |
| create table concat ( a int ); |
| insert into concat values (1); |
| select * from CONCAT; |
| |
| create table myconcat( a varchar(10) default null, b varchar(10) default null, c int); |
| insert into myconcat (c) values( 1 ); |
| insert into myconcat (c) values( 2 ); |
| insert into myconcat (a) values( 'hello' ); |
| insert into myconcat (b) values( 'world' ); |
| insert into myconcat (a,b) values( 'hello', 'world' ); |
| select * from myconcat; |
| select { fn concat( a, b ) } from myconcat; |
| drop table concat; |
| drop table myconcat; |
| -- End of CONCAT 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 |
| -- 2 args |
| values{ fn locate( 'hello', 'hello' ) }; |
| values{ fn locate( 'hello', 'hellohello' ) }; |
| values{ fn locate( 'hello', 'helloworld' ) }; |
| values{ fn locate( 'hello', 'h?hello' ) }; |
| values{ fn locate( 'hello', 'match me, hello now!' ) }; |
| values{ fn locate( '?', '?' ) }; |
| values{ fn locate( '\', '\\') }; |
| values{ fn locate( '/', '//') }; |
| values{ fn locate( '\\', '\') }; |
| values{ fn locate( '//', '/') }; |
| values{ fn locate( '', 'test' ) }; |
| values{ fn locate( '', '' ) }; |
| values{ fn locate( 'test', '' ) }; |
| |
| -- 3 args |
| values{ fn locate( 'hello', 'hello',-1 ) }; |
| values{ fn locate( 'hello', 'hello',-0 ) }; |
| values{ fn locate( 'hello', 'hello', 0 ) }; |
| values{ fn locate( 'hello', 'hello', 1 ) }; |
| values{ fn locate( 'hello', 'hello', 2 ) }; |
| values{ fn locate( 'hello', 'hello', 5 ) }; |
| values{ fn locate( 'hello', 'hello', 9 ) }; |
| |
| values{ fn locate( 'hello', 'hellohello', 0 ) }; |
| values{ fn locate( 'hello', 'hellohello', 1 ) }; |
| values{ fn locate( 'hello', 'hellohello', 2 ) }; |
| values{ fn locate( 'hello', 'hellohello', 5 ) }; |
| values{ fn locate( 'hello', 'hellohello', 6 ) }; |
| values{ fn locate( 'hello', 'hellohello', 7 ) }; |
| |
| values{ fn locate( 'hello', 'h?hello', 1 ) }; |
| values{ fn locate( 'hello', 'h?hello', 2 ) }; |
| values{ fn locate( 'hello', 'h?hello', 3 ) }; |
| values{ fn locate( 'hello', 'h?hello', 4 ) }; |
| |
| values{ fn locate( 'hello', 'match me, hello now!', 7 ) }; |
| values{ fn locate( 'hello', 'match me, hello now!', 15 ) }; |
| |
| values{ fn locate( '?', '?',-1 ) }; |
| values{ fn locate( '?', '?',-0 ) }; |
| values{ fn locate( '?', '?', 0 ) }; |
| values{ fn locate( '?', '?', 1 ) }; |
| values{ fn locate( '?', '?', 2 ) }; |
| |
| values{ fn locate( '\', '\\',0) }; |
| values{ fn locate( '\', '\\',1) }; |
| values{ fn locate( '\', '\\',2) }; |
| values{ fn locate( '\', '\\',3) }; |
| |
| values{ fn locate( '/', '//',0) }; |
| values{ fn locate( '/', '//',1) }; |
| values{ fn locate( '/', '//',2) }; |
| values{ fn locate( '/', '//',3) }; |
| |
| values{ fn locate( '\\', '\',1) }; |
| values{ fn locate( '//', '/',1) }; |
| |
| values{ fn locate( '', 'test',1) }; |
| values{ fn locate( '', 'test',2) }; |
| values{ fn locate( '', 'test',3) }; |
| values{ fn locate( '', 'test',4) }; |
| values{ fn locate( '', 'test',5) }; |
| values{ fn locate( '', '' ,1) }; |
| values{ fn locate( 'test', '',1) }; |
| values{ fn locate( 'test', '',2) }; |
| values{ fn locate( 'test', '',3) }; |
| values{ fn locate( 'test', '',4) }; |
| |
| values{ fn locate( 'hello', 1 ) }; |
| values{ fn locate( 1, 'hello' ) }; |
| values{ fn locate( 'hello', 'hello', 'hello' ) }; |
| values{ fn locate( 'hello', 'hello', 1.99999999999 ) }; |
| values{ fn locate( 1, 'hel1lo' ) }; |
| values{ fn locate( 1, 1 ) }; |
| values{ fn locate( 1, 1, '1' ) }; |
| values{ fn locate( '1', 1, 1 ) }; |
| values{ fn locate( '1', '1', '1' ) }; |
| |
| |
| -- End of EJBQL function test for LOCATE. |
| -- 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 locate( a varchar(20) ); |
| |
| -- create table myChar( a char(10), b char(20), c int default '1' ); |
| create table myChar( a char(10), b char(20), c int ); |
| insert into myChar (a, b) values( '1234567890', 'abcde1234567890fghij' ); |
| insert into myChar (a, b) values( 'abcdefghij', 'abcdefghij1234567890' ); |
| insert into myChar (a, b) values( 'abcdefghij', '1234567890abcdefghij' ); |
| insert into myChar (a, b) values( 'abcdefghij', '1234567890!@#$%^&*()' ); |
| insert into myChar values( '1234567890', 'abcde1234567890fghij', 2 ); |
| insert into myChar values( 'abcdefghij', 'abcdefghij1234567890', 1 ); |
| insert into myChar values( 'abcdefghij', '1234567890abcdefghij', 15 ); |
| insert into myChar (c) values( 0 ); |
| insert into myChar (c) values( 1 ); |
| insert into myChar (c) values( 2 ); |
| insert into myChar (a) values( 'hello' ); |
| insert into myChar (b) values( 'hello' ); |
| insert into myChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 ); |
| select a, b, c from myChar; |
| select locate(a, b) from myChar; |
| select locate(a, b, c) from myChar; |
| |
| drop table myChar; |
| create table myLongVarChar( a long varchar, b long varchar, c int); |
| insert into myLongVarChar (a, b) values( '1234567890', 'abcde1234567890fghij' ); |
| insert into myLongVarChar (a, b) values( 'abcdefghij', 'abcdefghij1234567890' ); |
| insert into myLongVarChar (a, b) values( 'abcdefghij', '1234567890abcdefghij' ); |
| insert into myLongVarChar (a, b) values( 'abcdefghij', '1234567890!@#$%^&*()' ); |
| insert into myLongVarChar (a, b) values( 'abcde', 'abcde' ); |
| insert into myLongVarChar (a, b) values( 'abcde', 'abcd' ); |
| insert into myLongVarChar (a, b) values( '', 'abcde' ); |
| insert into myLongVarChar (a, b) values( 'abcde', null ); |
| insert into myLongVarChar (a, b) values( null, 'abcde' ); |
| insert into myLongVarChar values( '1234567890', 'abcde1234567890fghij', 2 ); |
| insert into myLongVarChar values( 'abcdefghij', 'abcdefghij1234567890', 1 ); |
| insert into myLongVarChar values( 'abcdefghij', '1234567890abcdefghij', 15 ); |
| insert into myLongVarChar values( 'abcde', 'abcde', 1 ); |
| insert into myLongVarChar values( 'abcde', 'abcd', 1 ); |
| insert into myLongVarChar values( '', 'abcde', 2 ); |
| insert into myLongVarChar values( 'abcde', null, 1 ); |
| insert into myLongVarChar values( null, 'abcde', 1 ); |
| insert into myLongVarChar (c) values( 0 ); |
| insert into myLongVarChar (c) values( 1 ); |
| insert into myLongVarChar (c) values( 2 ); |
| insert into myLongVarChar (a) values( 'hello' ); |
| insert into myLongVarChar (b) values( 'hello' ); |
| insert into myLongVarChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 ); |
| select a, b, c from myLongVarChar; |
| select locate(a, b) from myLongVarChar; |
| select locate(a, b, c) from myLongVarChar; |
| drop table myLongVarChar; |
| |
| create table myVarChar( a varchar(10), b varchar(20), c int ); |
| insert into myVarChar (a, b) values( '1234567890', 'abcde1234567890fghij' ); |
| insert into myVarChar (a, b) values( 'abcdefghij', 'abcdefghij1234567890' ); |
| insert into myVarChar (a, b) values( 'abcdefghij', '1234567890abcdefghij' ); |
| insert into myVarChar (a, b) values( 'abcdefghij', '1234567890!@#$%^&*()' ); |
| insert into myVarChar (a, b) values( 'abcde', 'abcde' ); |
| insert into myVarChar (a, b) values( 'abcde', 'abcd' ); |
| insert into myVarChar (a, b) values( '', 'abcde' ); |
| insert into myVarChar (a, b) values( 'abcde', null ); |
| insert into myVarChar (a, b) values( null, 'abcde' ); |
| insert into myVarChar values( '1234567890', 'abcde1234567890fghij', 2 ); |
| insert into myVarChar values( 'abcdefghij', 'abcdefghij1234567890', 1 ); |
| insert into myVarChar values( 'abcdefghij', '1234567890abcdefghij', 15 ); |
| insert into myVarChar values( 'abcde', 'abcde', 1 ); |
| insert into myVarChar values( 'abcde', 'abcd', 1 ); |
| insert into myVarChar values( '', 'abcde', 2 ); |
| insert into myVarChar values( 'abcde', null, 1 ); |
| insert into myVarChar values( null, 'abcde', 1 ); |
| insert into myVarChar (c) values( 0 ); |
| insert into myVarChar (c) values( 1 ); |
| insert into myVarChar (c) values( 2 ); |
| insert into myVarChar (a) values( 'hello' ); |
| insert into myVarChar (b) values( 'hello' ); |
| insert into myVarChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 ); |
| select a, b, c from myVarChar; |
| select locate(a, b) from myVarChar; |
| select locate(a, b, c) from myVarChar; |
| drop table myVarChar; |
| |
| -- Negative cases. To match DB2 behaviour |
| create table t1 (dt date, tm time, ts timestamp); |
| insert into t1 values (current_date, current_time, current_timestamp); |
| select locate (dt, ts) from t1; |
| select locate (tm, ts) from t1; |
| select locate (ts, ts) from t1; |
| drop table t1; |
| |
| values locate('abc', 'dkabc', 1.4); |
| values locate('c', 'abcdedf', cast(1 as decimal(2,0))); |
| |
| -- ========================================================================= |
| -- These test cases for national character types will fail until |
| -- until a future work around is implemented |
| -- ========================================================================= |
| create table mynChar( a nchar(10), b nchar(20), c int ); |
| insert into mynChar values( '1234567890', 'abcde1234567890fghij' ); |
| insert into mynChar values( 'abcdefghij', 'abcdefghij1234567890' ); |
| insert into mynChar values( 'abcdefghij', '1234567890abcdefghij' ); |
| insert into mynChar values( 'abcdefghij', '1234567890!@#$%^&*()' ); |
| insert into mynChar values( '1234567890', 'abcde1234567890fghij', 2 ); |
| insert into mynChar values( 'abcdefghij', 'abcdefghij1234567890', 1 ); |
| insert into mynChar values( 'abcdefghij', '1234567890abcdefghij', 15 ); |
| insert into mynChar (c) values( 0 ); |
| insert into mynChar (c) values( 1 ); |
| insert into mynChar (c) values( 2 ); |
| insert into mynChar (a) values( 'hello' ); |
| insert into mynChar (b) values( 'hello' ); |
| insert into mynChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 ); |
| select a, b, c from mynChar; |
| select locate(a, b) from mynChar; |
| select locate(a, b, c) from mynChar; |
| drop table mynChar; |
| |
| create table myLongnVarChar( a long nvarchar, b long nvarchar, c int ); |
| insert into myLongnVarChar values( '1234567890', 'abcde1234567890fghij' ); |
| insert into myLongnVarChar values( 'abcdefghij', 'abcdefghij1234567890' ); |
| insert into myLongnVarChar values( 'abcdefghij', '1234567890abcdefghij' ); |
| insert into myLongnVarChar values( 'abcdefghij', '1234567890!@#$%^&*()' ); |
| insert into myLongnVarChar values( 'abcde', 'abcde' ); |
| insert into myLongnVarChar values( 'abcde', 'abcd' ); |
| insert into myLongnVarChar values( '', 'abcde' ); |
| insert into myLongnVarChar values( 'abcde', null ); |
| insert into myLongnVarChar values( null, 'abcde' ); |
| insert into myLongnVarChar values( '1234567890', 'abcde1234567890fghij', 2 ); |
| insert into myLongnVarChar values( 'abcdefghij', 'abcdefghij1234567890', 1 ); |
| insert into myLongnVarChar values( 'abcdefghij', '1234567890abcdefghij', 15 ); |
| insert into myLongnVarChar values( 'abcde', 'abcde', 1 ); |
| insert into myLongnVarChar values( 'abcde', 'abcd', 1 ); |
| insert into myLongnVarChar values( '', 'abcde', 2 ); |
| insert into myLongnVarChar values( 'abcde', null, 1 ); |
| insert into myLongnVarChar values( null, 'abcde', 1 ); |
| insert into myLongnVarChar (c) values( 0 ); |
| insert into myLongnVarChar (c) values( 1 ); |
| insert into myLongnVarChar (c) values( 2 ); |
| insert into myLongnVarChar (a) values( 'hello' ); |
| insert into myLongnVarChar (b) values( 'hello' ); |
| insert into myLongnVarChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 ); |
| select a, b, c from myLongnVarChar; |
| select locate(a, b) from myLongnVarChar; |
| select locate(a, b, c) from myLongnVarChar; |
| drop table myLongnVarChar; |
| |
| create table mynVarChar( a nvarchar(10), b nvarchar(20), c int ); |
| insert into mynVarChar values( '1234567890', 'abcde1234567890fghij' ); |
| insert into mynVarChar values( 'abcdefghij', 'abcdefghij1234567890' ); |
| insert into mynVarChar values( 'abcdefghij', '1234567890abcdefghij' ); |
| insert into mynVarChar values( 'abcdefghij', '1234567890!@#$%^&*()' ); |
| insert into mynVarChar values( 'abcde', 'abcde' ); |
| insert into mynVarChar values( 'abcde', 'abcd' ); |
| insert into mynVarChar values( '', 'abcde' ); |
| insert into mynVarChar values( 'abcde', null ); |
| insert into mynVarChar values( null, 'abcde' ); |
| insert into mynVarChar values( '1234567890', 'abcde1234567890fghij', 2 ); |
| insert into mynVarChar values( 'abcdefghij', 'abcdefghij1234567890', 1 ); |
| insert into mynVarChar values( 'abcdefghij', '1234567890abcdefghij', 15 ); |
| insert into mynVarChar values( 'abcde', 'abcde', 1 ); |
| insert into mynVarChar values( 'abcde', 'abcd', 1 ); |
| insert into mynVarChar values( '', 'abcde', 2 ); |
| insert into mynVarChar values( 'abcde', null, 1 ); |
| insert into mynVarChar values( null, 'abcde', 1 ); |
| insert into mynVarChar (c) values( 0 ); |
| insert into mynVarChar (c) values( 1 ); |
| insert into mynVarChar (c) values( 2 ); |
| insert into mynVarChar (a) values( 'hello' ); |
| insert into mynVarChar (b) values( 'hello' ); |
| insert into mynVarChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 ); |
| select a, b, c from mynVarChar; |
| select locate(a, b) from mynVarChar; |
| select locate(a, b, c) from mynVarChar; |
| |
| create table myMixed( a char(10), b long nvarchar, c int ); |
| insert into myMixed values( '1234567890', 'abcde1234567890fghij' ); |
| insert into myMixed values( 'abcdefghij', 'abcdefghij1234567890' ); |
| insert into myMixed values( 'abcdefghij', '1234567890abcdefghij' ); |
| insert into myMixed values( 'abcdefghij', '1234567890!@#$%^&*()' ); |
| insert into myMixed values( '1234567890', 'abcde1234567890fghij', 2 ); |
| insert into myMixed values( 'abcdefghij', 'abcdefghij1234567890', 1 ); |
| insert into myMixed values( 'abcdefghij', '1234567890abcdefghij', 15 ); |
| insert into myMixed (c) values( 0 ); |
| insert into myMixed (c) values( 1 ); |
| insert into myMixed (c) values( 2 ); |
| insert into myMixed (a) values( 'hello' ); |
| insert into myMixed (b) values( 'hello' ); |
| insert into myMixed values( 'abcdefghij', '1234567890!@#$%^&*()', 21 ); |
| select a, b, c from myMixed; |
| select locate(a, b) from myMixed; |
| select locate(a, b, c) from myMixed; |
| drop table myMixed; |
| |
| create table foo( a int ); |
| insert into foo select locate(a, b) from mynVarChar; |
| insert into foo values( {fn locate('hello', 'hello')} ); |
| select * from foo; |
| drop table foo; |
| drop table mynVarChar; |
| -- ========================================================================= |
| |
| -- Other types |
| create table myBigInt( a bigint, b bigint ); |
| insert into myBigInt values( 1234, 1234 ); |
| insert into myBigInt values( 4321, 1234 ); |
| select locate(a, b) from myBigInt; |
| drop table myBigInt; |
| |
| create table myBit( a char for bit data, b char for bit data ); |
| insert into myBit values( X'40', X'40' ); |
| insert into myBit values( X'01', X'40' ); |
| select locate(a, b) from myBit; |
| drop table myBit; |
| |
| -- bug 5794 - LOCATE built-in function is not db2 udb compatible |
| create table myDate( a date, b date ); |
| insert into myDate values( date('1970-01-08'), date('1970-01-08') ); |
| insert into myDate values( date('1979-08-30'), date('1978-07-28') ); |
| select locate(a, b) from myDate; |
| drop table myDate; |
| |
| create table myDecimal( a decimal, b decimal ); |
| insert into myDecimal values( 2.2, 2.2 ); |
| insert into myDecimal values( 12.23, 3423 ); |
| select locate(a, b) from myDecimal; |
| drop table myDecimal; |
| |
| create table myDouble( a double precision, b double precision ); |
| insert into myDouble values( 2.2, 2.2 ); |
| insert into myDouble values( 12.23, 3423 ); |
| select locate(a, b) from myDouble; |
| drop table myDouble; |
| |
| create table myInteger(a integer, b integer ); |
| insert into myInteger values( 2, 2 ); |
| insert into myInteger values( 123, 3423 ); |
| select locate(a, b) from myInteger; |
| drop table myInteger; |
| |
| create table mylongvarbinary( a long varchar for bit data, b long varchar for bit data ); |
| select locate(a, b) from mylongvarbinary; |
| drop table mylongvarbinary; |
| |
| -- bug 5794 - LOCATE built-in function is not db2 udb compatible |
| create table mytime( a time, b time ); |
| insert into mytime values( time('10:00:00'), time('10:00:00') ); |
| insert into mytime values( time('10:00:00'), time('11:00:00') ); |
| select locate(a, b) from mytime; |
| 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 CAST(t.tablename AS VARCHAR(128)) = '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); |
| |
| 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; |
