ij> -- -- 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 ); 0 rows inserted/updated/deleted ij> create table myinteger( a integer ); 0 rows inserted/updated/deleted ij> select abs(a) from myint; 1 ----------- ij> select abs(a) from myinteger; 1 ----------- ij> insert into myint values (null), (+0), (-0), (+1), (-1), (1000), (-1000), (null), (2147483647), (-2147483647); 10 rows inserted/updated/deleted ij> insert into myinteger values (NULL), (+0), (-0), (+1), (-1), (1000), (-1000), (NULL), (2147483647), (-2147483647); 10 rows inserted/updated/deleted ij> select a from myint; A ----------- NULL 0 0 1 -1 1000 -1000 NULL 2147483647 -2147483647 ij> select a from myinteger; A ----------- NULL 0 0 1 -1 1000 -1000 NULL 2147483647 -2147483647 ij> select abs(a) from myint; 1 ----------- NULL 0 0 1 1 1000 1000 NULL 2147483647 2147483647 ij> select abs(a) from myinteger; 1 ----------- NULL 0 0 1 1 1000 1000 NULL 2147483647 2147483647 ij> select -abs(a) from myint; 1 ----------- NULL 0 0 -1 -1 -1000 -1000 NULL -2147483647 -2147483647 ij> select -abs(a) from myinteger; 1 ----------- NULL 0 0 -1 -1 -1000 -1000 NULL -2147483647 -2147483647 ij> select abs(abs(-abs(-abs(a)))) from myint; 1 ----------- NULL 0 0 1 1 1000 1000 NULL 2147483647 2147483647 ij> select abs(abs(-abs(-abs(a)))) from myinteger; 1 ----------- NULL 0 0 1 1 1000 1000 NULL 2147483647 2147483647 ij> SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYINT; 1 ----------- NULL 0 0 1 1 1000 1000 NULL 2147483647 2147483647 ij> SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYINTEGER; 1 ----------- NULL 0 0 1 1 1000 1000 NULL 2147483647 2147483647 ij> insert into myint values (-2147483648); 1 row inserted/updated/deleted ij> insert into myinteger values (-2147483648); 1 row inserted/updated/deleted ij> select a from myint where a=-2147483648; A ----------- -2147483648 ij> select a from myinteger where a=-2147483648; A ----------- -2147483648 ij> -- Error select -a from myint where a=-2147483648; 1 ----------- ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> select -a from myinteger where a=-2147483648; 1 ----------- ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> select abs(-a) from myint where a=-2147483648; 1 ----------- ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> select abs(-a) from myinteger where a=-2147483648; 1 ----------- ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> select abs(a) from myint where a=-2147483648; 1 ----------- ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> select abs(a) from myinteger where a=-2147483648; 1 ----------- ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> select abs(-abs(a)) from myint where a=-2147483648; 1 ----------- ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> select abs(-abs(a)) from myinteger where a=-2147483648; 1 ----------- ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> drop table myint; 0 rows inserted/updated/deleted ij> drop table myinteger; 0 rows inserted/updated/deleted ij> -- End of Integer test -- Smallint has a range of -32768 to 32767 -- Basic create table mysmallint( a smallint ); 0 rows inserted/updated/deleted ij> select abs(a) from mysmallint; 1 ------ ij> insert into mysmallint values (null), (+0), (-0), (+1), (-1), (1000), (-1000), (null), (32767), (-32767); 10 rows inserted/updated/deleted ij> select a from mysmallint; A ------ NULL 0 0 1 -1 1000 -1000 NULL 32767 -32767 ij> select abs(a) from mysmallint; 1 ------ NULL 0 0 1 1 1000 1000 NULL 32767 32767 ij> select -abs(a) from mysmallint; 1 ------ NULL 0 0 -1 -1 -1000 -1000 NULL -32767 -32767 ij> select abs(abs(-abs(-abs(a)))) from mysmallint; 1 ------ NULL 0 0 1 1 1000 1000 NULL 32767 32767 ij> SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYSMALLINT; 1 ------ NULL 0 0 1 1 1000 1000 NULL 32767 32767 ij> insert into mysmallint values (-32768); 1 row inserted/updated/deleted ij> select a from mysmallint where a=-32768; A ------ -32768 ij> -- Error select -a from mysmallint where a=-32768; 1 ------ ERROR 22003: The resulting value is outside the range for the data type SMALLINT. ij> select abs(-a) from mysmallint where a=-32768; 1 ------ ERROR 22003: The resulting value is outside the range for the data type SMALLINT. ij> select abs(a) from mysmallint where a=-32768; 1 ------ ERROR 22003: The resulting value is outside the range for the data type SMALLINT. ij> select abs(-abs(a)) from mysmallint where a=-32768; 1 ------ ERROR 22003: The resulting value is outside the range for the data type SMALLINT. ij> drop table mysmallint; 0 rows inserted/updated/deleted ij> -- End of Smallint test -- Bigint has a range of -9223372036854775808 to 9223372036854775807 -- Basic create table mybigint( a bigint ); 0 rows inserted/updated/deleted ij> select abs(a) from mybigint; 1 -------------------- ij> insert into mybigint values (null), (+0), (-0), (+1), (-1), (1000), (-1000), (null), (9223372036854775807), (-9223372036854775807); 10 rows inserted/updated/deleted ij> select a from mybigint; A -------------------- NULL 0 0 1 -1 1000 -1000 NULL 9223372036854775807 -9223372036854775807 ij> select abs(a) from mybigint; 1 -------------------- NULL 0 0 1 1 1000 1000 NULL 9223372036854775807 9223372036854775807 ij> select -abs(a) from mybigint; 1 -------------------- NULL 0 0 -1 -1 -1000 -1000 NULL -9223372036854775807 -9223372036854775807 ij> select abs(abs(-abs(-abs(a)))) from mybigint; 1 -------------------- NULL 0 0 1 1 1000 1000 NULL 9223372036854775807 9223372036854775807 ij> SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYBIGINT; 1 -------------------- NULL 0 0 1 1 1000 1000 NULL 9223372036854775807 9223372036854775807 ij> insert into mybigint values (-9223372036854775808); 1 row inserted/updated/deleted ij> select a from mybigint where a=-9223372036854775808; A -------------------- -9223372036854775808 ij> -- Error select -a from mybigint where a=-9223372036854775808; 1 -------------------- ERROR 22003: The resulting value is outside the range for the data type BIGINT. ij> select abs(-a) from mybigint where a=-9223372036854775808; 1 -------------------- ERROR 22003: The resulting value is outside the range for the data type BIGINT. ij> select abs(a) from mybigint where a=-9223372036854775808; 1 -------------------- ERROR 22003: The resulting value is outside the range for the data type BIGINT. ij> select abs(-abs(a)) from mybigint where a=-9223372036854775808; 1 -------------------- ERROR 22003: The resulting value is outside the range for the data type BIGINT. ij> drop table mybigint; 0 rows inserted/updated/deleted ij> -- End of Bigint test -- REAL has a range of +/-1.175E-37 to +/-3.402E+38 -- Basic create table myreal( a real ); 0 rows inserted/updated/deleted ij> select abs(a) from myreal; 1 ------------- ij> insert into myreal values (null), (+0), (-0), (+1), (-1), (null), (100000000), (-100000000), (3.402E+38), (-3.402E+38), (1.175E-37), (-1.175E-37); 12 rows inserted/updated/deleted ij> select a from myreal; A ------------- NULL 0.0 0.0 1.0 -1.0 NULL 1.0E8 -1.0E8 3.402E38 -3.402E38 1.175E-37 -1.175E-37 ij> select -a from myreal; 1 ------------- NULL 0.0 0.0 -1.0 1.0 NULL -1.0E8 1.0E8 -3.402E38 3.402E38 -1.175E-37 1.175E-37 ij> select abs(a) from myreal; 1 ------------- NULL 0.0 0.0 1.0 1.0 NULL 1.0E8 1.0E8 3.402E38 3.402E38 1.175E-37 1.175E-37 ij> select abs(-a) from myreal; 1 ------------- NULL 0.0 0.0 1.0 1.0 NULL 1.0E8 1.0E8 3.402E38 3.402E38 1.175E-37 1.175E-37 ij> select -abs(a) from myreal; 1 ------------- NULL 0.0 0.0 -1.0 -1.0 NULL -1.0E8 -1.0E8 -3.402E38 -3.402E38 -1.175E-37 -1.175E-37 ij> select abs(abs(-abs(-abs(a)))) from myreal; 1 ------------- NULL 0.0 0.0 1.0 1.0 NULL 1.0E8 1.0E8 3.402E38 3.402E38 1.175E-37 1.175E-37 ij> SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYREAL; 1 ------------- NULL 0.0 0.0 1.0 1.0 NULL 1.0E8 1.0E8 3.402E38 3.402E38 1.175E-37 1.175E-37 ij> select distinct abs(a) from myreal; 1 ------------- 0.0 1.175E-37 1.0 1.0E8 3.402E38 NULL ij> ---- 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; 1 ------------- ij> -- when casted to a real, it is found select abs(-abs(a)) + 1 from myreal where a=cast(1.175E-37 as real); 1 ------------- 1.0 ij> -- Error insert into myreal values ( 3.402E+38 *2); ERROR 22003: The resulting value is outside the range for the data type REAL. ij> insert into myreal values (-3.402E+38 *2); ERROR 22003: The resulting value is outside the range for the data type REAL. ij> drop table myreal; 0 rows inserted/updated/deleted ij> -- End of Real test -- Double Precision has a range of +/-2.225E-307 to +/-1.79769E+308 -- Basic create table mydoubleprecision( a double precision ); 0 rows inserted/updated/deleted ij> select abs(a) from mydoubleprecision; 1 ---------------------- ij> insert into mydoubleprecision values (null), (+0), (-0), (+1), (-1), (100000000), (-100000000), (null), (1.79769E+308), (-1.79769E+308), (2.225E-307), (-2.225E-307); 12 rows inserted/updated/deleted ij> select a from mydoubleprecision; A ---------------------- NULL 0.0 0.0 1.0 -1.0 1.0E8 -1.0E8 NULL 1.79769E308 -1.79769E308 2.225E-307 -2.225E-307 ij> select -a from mydoubleprecision; 1 ---------------------- NULL 0.0 0.0 -1.0 1.0 -1.0E8 1.0E8 NULL -1.79769E308 1.79769E308 -2.225E-307 2.225E-307 ij> select abs(a) from mydoubleprecision; 1 ---------------------- NULL 0.0 0.0 1.0 1.0 1.0E8 1.0E8 NULL 1.79769E308 1.79769E308 2.225E-307 2.225E-307 ij> select abs(-a) from mydoubleprecision; 1 ---------------------- NULL 0.0 0.0 1.0 1.0 1.0E8 1.0E8 NULL 1.79769E308 1.79769E308 2.225E-307 2.225E-307 ij> select -abs(a) from mydoubleprecision; 1 ---------------------- NULL 0.0 0.0 -1.0 -1.0 -1.0E8 -1.0E8 NULL -1.79769E308 -1.79769E308 -2.225E-307 -2.225E-307 ij> select abs(abs(-abs(-abs(a)))) from mydoubleprecision; 1 ---------------------- NULL 0.0 0.0 1.0 1.0 1.0E8 1.0E8 NULL 1.79769E308 1.79769E308 2.225E-307 2.225E-307 ij> SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYDOUBLEPRECISION; 1 ---------------------- NULL 0.0 0.0 1.0 1.0 1.0E8 1.0E8 NULL 1.79769E308 1.79769E308 2.225E-307 2.225E-307 ij> select distinct abs(a) from mydoubleprecision; 1 ---------------------- 0.0 2.225E-307 1.0 1.0E8 1.79769E308 NULL ij> -- 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; 1 ---------------------- 1.0 ij> -- Error insert into mydoubleprecision values ( 1.79769E+308 *2); ERROR 22003: The resulting value is outside the range for the data type DOUBLE. ij> insert into mydoubleprecision values (-1.79769E+308 *2); ERROR 22003: The resulting value is outside the range for the data type DOUBLE. ij> drop table mydoubleprecision; 0 rows inserted/updated/deleted ij> -- 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) ); 0 rows inserted/updated/deleted ij> select abs(a), abs(b) from myfloat; 1 |2 ------------------------------------ ij> select columnname, columndatatype from sys.syscolumns c, sys.systables t where c.referenceid = t.tableid and CAST(t.tablename AS VARCHAR(128)) ='MYFLOAT'; COLUMNNAME |COLUMNDATATYPE ------------------------------------------------------------------------------------------------------------------------------------------------ A |DOUBLE B |REAL ij> 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); 12 rows inserted/updated/deleted ij> select a, b from myfloat; A |B ------------------------------------ NULL |NULL 0.0 |0.0 0.0 |0.0 1.0 |1.0 -1.0 |-1.0 1.0E8 |1.0E8 -1.0E8 |-1.0E8 NULL |NULL 1.79769E308 |3.402E38 -1.79769E308 |-3.402E38 2.225E-307 |1.175E-37 -2.225E-307 |-1.175E-37 ij> select -a, -b from myfloat; 1 |2 ------------------------------------ NULL |NULL 0.0 |0.0 0.0 |0.0 -1.0 |-1.0 1.0 |1.0 -1.0E8 |-1.0E8 1.0E8 |1.0E8 NULL |NULL -1.79769E308 |-3.402E38 1.79769E308 |3.402E38 -2.225E-307 |-1.175E-37 2.225E-307 |1.175E-37 ij> select abs(a), abs(b) from myfloat; 1 |2 ------------------------------------ NULL |NULL 0.0 |0.0 0.0 |0.0 1.0 |1.0 1.0 |1.0 1.0E8 |1.0E8 1.0E8 |1.0E8 NULL |NULL 1.79769E308 |3.402E38 1.79769E308 |3.402E38 2.225E-307 |1.175E-37 2.225E-307 |1.175E-37 ij> select abs(-a), abs(-b) from myfloat; 1 |2 ------------------------------------ NULL |NULL 0.0 |0.0 0.0 |0.0 1.0 |1.0 1.0 |1.0 1.0E8 |1.0E8 1.0E8 |1.0E8 NULL |NULL 1.79769E308 |3.402E38 1.79769E308 |3.402E38 2.225E-307 |1.175E-37 2.225E-307 |1.175E-37 ij> select -abs(a), -abs(b) from myfloat; 1 |2 ------------------------------------ NULL |NULL 0.0 |0.0 0.0 |0.0 -1.0 |-1.0 -1.0 |-1.0 -1.0E8 |-1.0E8 -1.0E8 |-1.0E8 NULL |NULL -1.79769E308 |-3.402E38 -1.79769E308 |-3.402E38 -2.225E-307 |-1.175E-37 -2.225E-307 |-1.175E-37 ij> select abs(abs(-abs(-abs(a)))), abs(abs(-abs(-abs(b)))) from myfloat; 1 |2 ------------------------------------ NULL |NULL 0.0 |0.0 0.0 |0.0 1.0 |1.0 1.0 |1.0 1.0E8 |1.0E8 1.0E8 |1.0E8 NULL |NULL 1.79769E308 |3.402E38 1.79769E308 |3.402E38 2.225E-307 |1.175E-37 2.225E-307 |1.175E-37 ij> SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))), ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(B)))) FROM MYFLOAT; 1 |2 ------------------------------------ NULL |NULL 0.0 |0.0 0.0 |0.0 1.0 |1.0 1.0 |1.0 1.0E8 |1.0E8 1.0E8 |1.0E8 NULL |NULL 1.79769E308 |3.402E38 1.79769E308 |3.402E38 2.225E-307 |1.175E-37 2.225E-307 |1.175E-37 ij> select distinct abs(a) from myfloat; 1 ---------------------- 0.0 2.225E-307 1.0 1.0E8 1.79769E308 NULL ij> -- -- 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; 1 |2 ------------------------------------ ij> select abs(-abs(a)) + 1, abs(-abs(b)) + 1 from myfloat where b=3.402E+38; 1 |2 ------------------------------------ ij> -- '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); 1 |2 ------------------------------------ 1.79769E308 |3.402E38 ij> select abs(-abs(a)) + 1, abs(-abs(b)) + 1 from myfloat where a=2.225E-307 AND b=cast(1.175E-37 as real); 1 |2 ------------------------------------ 1.0 |1.0 ij> select abs(-abs(a)) + 1, abs(-abs(b)) + 1 from myfloat where a=2.225E-307; 1 |2 ------------------------------------ 1.0 |1.0 ij> -- Error insert into myfloat values ( 1.79769E+308 *2, 3.402E+38 *2); ERROR 22003: The resulting value is outside the range for the data type DOUBLE. ij> insert into myfloat values (-1.79769E+308 *2, -3.402E+38 *2); ERROR 22003: The resulting value is outside the range for the data type DOUBLE. ij> insert into myfloat values ( 2.225E-307, 3.402E+38 *2); ERROR 22003: The resulting value is outside the range for the data type REAL. ij> insert into myfloat values (-2.225E-307, -3.402E+38 *2); ERROR 22003: The resulting value is outside the range for the data type REAL. ij> drop table myfloat; 0 rows inserted/updated/deleted ij> -- End of Float test -- Decimal is java.math.BigDecimal -- Basic create table myDecimal( a decimal(31, 0), b decimal(31,31)); 0 rows inserted/updated/deleted ij> select abs(a) from myDecimal; 1 -------------------------------- ij> 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); 10 rows inserted/updated/deleted ij> select a from myDecimal; A -------------------------------- NULL 0 0 1 -1 100000000 -100000000 NULL 1000000000000000000000000000000 -1000000000000000000000000000000 ij> select -a from myDecimal; 1 -------------------------------- NULL 0 0 -1 1 -100000000 100000000 NULL -1000000000000000000000000000000 1000000000000000000000000000000 ij> select b from myDecimal; B ---------------------------------- 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.1000000000000000000000000000000 -0.1000000000000000000000000000000 NULL 0.0000000000000000000000000000010 -0.0000000000000000000000000000010 ij> select -b from myDecimal; 1 ---------------------------------- 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 -0.1000000000000000000000000000000 0.1000000000000000000000000000000 NULL -0.0000000000000000000000000000010 0.0000000000000000000000000000010 ij> select abs(a) from myDecimal; 1 -------------------------------- NULL 0 0 1 1 100000000 100000000 NULL 1000000000000000000000000000000 1000000000000000000000000000000 ij> select abs(-a) from myDecimal; 1 -------------------------------- NULL 0 0 1 1 100000000 100000000 NULL 1000000000000000000000000000000 1000000000000000000000000000000 ij> select -abs(a) from myDecimal; 1 -------------------------------- NULL 0 0 -1 -1 -100000000 -100000000 NULL -1000000000000000000000000000000 -1000000000000000000000000000000 ij> select abs(abs(-abs(-abs(a)))) from myDecimal; 1 -------------------------------- NULL 0 0 1 1 100000000 100000000 NULL 1000000000000000000000000000000 1000000000000000000000000000000 ij> SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYDECIMAL; 1 -------------------------------- NULL 0 0 1 1 100000000 100000000 NULL 1000000000000000000000000000000 1000000000000000000000000000000 ij> select distinct abs(a) from myDecimal; 1 -------------------------------- 0 1 100000000 1000000000000000000000000000000 NULL ij> select abs(b) from myDecimal; 1 ---------------------------------- 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.1000000000000000000000000000000 0.1000000000000000000000000000000 NULL 0.0000000000000000000000000000010 0.0000000000000000000000000000010 ij> select abs(-b) from myDecimal; 1 ---------------------------------- 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.1000000000000000000000000000000 0.1000000000000000000000000000000 NULL 0.0000000000000000000000000000010 0.0000000000000000000000000000010 ij> select -abs(b) from myDecimal; 1 ---------------------------------- 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 -0.1000000000000000000000000000000 -0.1000000000000000000000000000000 NULL -0.0000000000000000000000000000010 -0.0000000000000000000000000000010 ij> select abs(abs(-abs(-abs(b)))) from myDecimal; 1 ---------------------------------- 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.1000000000000000000000000000000 0.1000000000000000000000000000000 NULL 0.0000000000000000000000000000010 0.0000000000000000000000000000010 ij> SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(B)))) FROM MYDECIMAL; 1 ---------------------------------- 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.0000000000000000000000000000000 0.1000000000000000000000000000000 0.1000000000000000000000000000000 NULL 0.0000000000000000000000000000010 0.0000000000000000000000000000010 ij> select distinct abs(b) from myDecimal; 1 ---------------------------------- 0.0000000000000000000000000000000 0.0000000000000000000000000000010 0.1000000000000000000000000000000 NULL ij> -- 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; 1 -------------------------------- NULL 1 1 2 2 100000001 100000001 NULL 1000000000000000000000000000001 1000000000000000000000000000001 ij> drop table myDecimal; 0 rows inserted/updated/deleted ij> -- End of Decimal test -- Numeric java.math.BigDecimal -- Basic create table myNumeric( a decimal(31,0), b decimal(31,31 )); 0 rows inserted/updated/deleted ij> select abs(a) from myNumeric; 1 -------------------------------- ij> 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; ERROR 42X80: VALUES clause must contain at least one element. Empty elements are not allowed. ij> select -a from myNumeric; 1 -------------------------------- ij> select b from myNumeric; B ---------------------------------- ij> select -b from myNumeric; 1 ---------------------------------- ij> select abs(a), abs(b)from myNumeric; 1 |2 ------------------------------------------------------------------- ij> select abs(-a), abs(-b) from myNumeric; 1 |2 ------------------------------------------------------------------- ij> select -abs(a), -abs(b) from myNumeric; 1 |2 ------------------------------------------------------------------- ij> select abs(abs(-abs(-abs(a)))) from myNumeric; 1 -------------------------------- ij> SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYNUMERIC; 1 -------------------------------- ij> select distinct abs(a) from myNumeric; 1 -------------------------------- ij> -- 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; 1 -------------------------------- ij> drop table myNumeric; 0 rows inserted/updated/deleted ij> -- End of Numeric test -- Test some different statements, just in case create table foo( a int ); 0 rows inserted/updated/deleted ij> insert into foo values ( abs( 1) ); 1 row inserted/updated/deleted ij> insert into foo values ( abs(-2) ); 1 row inserted/updated/deleted ij> insert into foo values (-abs(-3) ); 1 row inserted/updated/deleted ij> insert into foo values (-abs( 4) ); 1 row inserted/updated/deleted ij> insert into foo values ( -5 ); 1 row inserted/updated/deleted ij> insert into foo values ( -6 ); 1 row inserted/updated/deleted ij> insert into foo values ( -7 ); 1 row inserted/updated/deleted ij> autocommit off; ij> prepare p1 as 'select a from foo'; ij> prepare p2 as 'select abs(a) from foo'; ij> prepare p3 as 'insert into foo select a*(-1) from foo'; ij> execute p1; A ----------- 1 2 -3 -4 -5 -6 -7 ij> execute p2; 1 ----------- 1 2 3 4 5 6 7 ij> execute p3; 7 rows inserted/updated/deleted ij> execute p1; A ----------- 1 2 -3 -4 -5 -6 -7 -1 -2 3 4 5 6 7 ij> insert into foo values( abs( 8 ) ); 1 row inserted/updated/deleted ij> insert into foo values( abs(-9 ) ); 1 row inserted/updated/deleted ij> insert into foo values(-abs(-10) ); 1 row inserted/updated/deleted ij> insert into foo values( abs( 11) ); 1 row inserted/updated/deleted ij> insert into foo values( -12 ); 1 row inserted/updated/deleted ij> execute p1; A ----------- 1 2 -3 -4 -5 -6 -7 -1 -2 3 4 5 6 7 8 9 -10 11 -12 ij> execute p2; 1 ----------- 1 2 3 4 5 6 7 1 2 3 4 5 6 7 8 9 10 11 12 ij> execute p3; 19 rows inserted/updated/deleted ij> execute p1; A ----------- 1 2 -3 -4 -5 -6 -7 -1 -2 3 4 5 6 7 8 9 -10 11 -12 -1 -2 3 4 5 6 7 1 2 -3 -4 -5 -6 -7 -8 -9 10 -11 12 ij> rollback; ij> commit; ij> autocommit on; ij> insert into foo values( abs( 13) ); 1 row inserted/updated/deleted ij> insert into foo values( abs(-14) ); 1 row inserted/updated/deleted ij> insert into foo values(-abs(-15) ); 1 row inserted/updated/deleted ij> insert into foo values(-abs( 16) ); 1 row inserted/updated/deleted ij> insert into foo values( -17 ); 1 row inserted/updated/deleted ij> execute p1; A ----------- 1 2 -3 -4 -5 -6 -7 13 14 -15 -16 -17 ij> execute p2; 1 ----------- 1 2 3 4 5 6 7 13 14 15 16 17 ij> execute p3; 12 rows inserted/updated/deleted ij> execute p1; A ----------- 1 2 -3 -4 -5 -6 -7 13 14 -15 -16 -17 -1 -2 3 4 5 6 7 -13 -14 15 16 17 ij> select * from foo; A ----------- 1 2 -3 -4 -5 -6 -7 13 14 -15 -16 -17 -1 -2 3 4 5 6 7 -13 -14 15 16 17 ij> drop table foo; 0 rows inserted/updated/deleted ij> -- End of ABS/ABSVAL test. For all valid types. Un-escaped function. -- abs is not a reserved word create table abs( a int ); 0 rows inserted/updated/deleted ij> drop table abs; 0 rows inserted/updated/deleted ij> -- 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') )}; 1 ----------- 0 ij> values{fn abs(INT('-0') )}; 1 ----------- 0 ij> values{fn abs(INT(' 1') )}; 1 ----------- 1 ij> values{fn abs(INT('-1') )}; 1 ----------- 1 ij> values{fn abs(INT(' 1000000') )}; 1 ----------- 1000000 ij> values{fn abs(INT('-1000000') )}; 1 ----------- 1000000 ij> values{fn abs(INT(' 2147483647') )}; 1 ----------- 2147483647 ij> values{fn abs(INT('-2147483648') + 1 )}; 1 ----------- 2147483647 ij> -- Error values{fn abs(INT('-2147483648') )}; 1 ----------- ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> values{fn abs(INT(' 2147483647') + 1 )}; 1 ----------- ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> -- Smallint -- Basic -- beetle 5807 - support SMALLINT built-in function values{fn abs( SMALLINT(' 0') )}; 1 ------ 0 ij> values{fn abs( SMALLINT('-0') )}; 1 ------ 0 ij> values{fn abs( SMALLINT(' 1') )}; 1 ------ 1 ij> values{fn abs( SMALLINT('-1') )}; 1 ------ 1 ij> values{fn abs( SMALLINT(' 10000') )}; 1 ------ 10000 ij> values{fn abs( SMALLINT('-10000') )}; 1 ------ 10000 ij> values{fn abs( SMALLINT(' 32767') )}; 1 ------ 32767 ij> values{fn abs( SMALLINT('-32768') + 1 )}; 1 ----------- 32767 ij> values{fn abs(-SMALLINT('-32768') )}; 1 ------ ERROR 22003: The resulting value is outside the range for the data type SMALLINT. ij> -- Error values{fn abs(-SMALLINT(' 32768') )}; ERROR 22003: The resulting value is outside the range for the data type SHORT. ij> values{fn abs( SMALLINT('-32768') )}; 1 ------ ERROR 22003: The resulting value is outside the range for the data type SMALLINT. ij> -- Bigint -- Basic -- beetle 5809 - support BIGINT built-in function values{fn abs( BIGINT(' 0') )}; 1 -------------------- 0 ij> values{fn abs( BIGINT('-0') )}; 1 -------------------- 0 ij> values{fn abs( BIGINT(' 1') )}; 1 -------------------- 1 ij> values{fn abs( BIGINT('-1') )}; 1 -------------------- 1 ij> values{fn abs( BIGINT(' 100000000000') )}; 1 -------------------- 100000000000 ij> values{fn abs( BIGINT('-100000000000') )}; 1 -------------------- 100000000000 ij> values{fn abs( BIGINT(' 9223372036854775807') )}; 1 -------------------- 9223372036854775807 ij> values{fn abs( BIGINT('-9223372036854775808') + 1 )}; 1 -------------------- 9223372036854775807 ij> -- Error values{fn abs(-BIGINT('-9223372036854775808') )}; 1 -------------------- ERROR 22003: The resulting value is outside the range for the data type BIGINT. ij> values{fn abs( BIGINT('-9223372036854775808') )}; 1 -------------------- ERROR 22003: The resulting value is outside the range for the data type BIGINT. ij> -- Real -- Basic -- beetle 5806 - support REAL built-in function values{fn abs( REAL( 0) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 4, column 16. ij> values{fn abs( REAL(-0) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16. ij> values{fn abs( REAL( 1) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16. ij> values{fn abs( REAL(-1) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16. ij> values{fn abs( REAL( 1000000.001) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16. ij> values{fn abs( REAL(-1000000.001) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16. ij> values{fn abs( REAL( 3.402E+38) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16. ij> values{fn abs( REAL(-3.402E+38) + 1 )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16. ij> -- Error values{fn abs( REAL( 3.402E+38 * 2) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 2, column 16. ij> values{fn abs(-REAL( NaN) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16. ij> values{fn abs( REAL( 1.40129846432481707e-45) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16. ij> values{fn abs( REAL( 3.40282346638528860e+38) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16. ij> -- Double Precision/Double -- Basic -- beetle 5803 - support DOUBLE_[PRECISION] built-in function values{fn abs( DOUBLE( 0) )}; 1 ---------------------- 0.0 ij> values{fn abs( DOUBLE(-0) )}; 1 ---------------------- 0.0 ij> values{fn abs( DOUBLE( 1) )}; 1 ---------------------- 1.0 ij> values{fn abs( DOUBLE(-1) )}; 1 ---------------------- 1.0 ij> values{fn abs( DOUBLE( 1000000.001) )}; 1 ---------------------- 1000000.001 ij> values{fn abs( DOUBLE(-1000000.001) )}; 1 ---------------------- 1000000.001 ij> values{fn abs( DOUBLE(-1.79769E+308) )}; 1 ---------------------- 1.79769E308 ij> values{fn abs( DOUBLE( 1.79769E+308) + 1 )}; 1 ---------------------- 1.79769E308 ij> values{fn abs( DOUBLE( 2.225E-307 + 1) )}; 1 ---------------------- 1.0 ij> -- Error values{fn abs( DOUBLE( 1.79769E+308 * 2) )}; 1 ---------------------- ERROR 22003: The resulting value is outside the range for the data type DOUBLE. ij> values{fn abs(-DOUBLE( NaN) )}; ERROR 42X04: Column 'NAN' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'NAN' is not a column in the target table. ij> values{fn abs( DOUBLE( 4.9E-324) )}; ERROR 22003: The resulting value is outside the range for the data type DOUBLE. ij> values{fn abs( DOUBLE( 1.7976931348623157E308) )}; ERROR 22003: The resulting value is outside the range for the data type DOUBLE. ij> -- Decimal/Numeric -- Basic -- beetle 5802 - support DEC[IMAL] built-in function values{ fn abs(DEC( 0) )}; ERROR 42X01: Syntax error: Encountered "DEC" at line 4, column 16. ij> values{ fn abs(DEC(-0) )}; ERROR 42X01: Syntax error: Encountered "DEC" at line 1, column 16. ij> values{ fn abs(DEC( 1) )}; ERROR 42X01: Syntax error: Encountered "DEC" at line 1, column 16. ij> values{ fn abs(DEC(-1) )}; ERROR 42X01: Syntax error: Encountered "DEC" at line 1, column 16. ij> values{ fn abs(DEC( 1000000000000) )}; ERROR 42X01: Syntax error: Encountered "DEC" at line 1, column 16. ij> values{ fn abs(DEC(-1000000000000) )}; ERROR 42X01: Syntax error: Encountered "DEC" at line 1, column 16. ij> -- More generic test values{ fn abs( 0-1-.1 ) }; 1 --------------- 1.1 ij> values{ fn abs( -0-1.000000001 ) }; 1 ----------------------- 1.000000001 ij> VALUES{ FN ABS( 100-200-300 ) }; 1 ----------- 400 ij> -- Error values{ fn abs('null') }; ERROR 42X25: The 'ABS/ABSVAL' function is not allowed on the 'CHAR' type. ij> -- 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 ); 0 rows inserted/updated/deleted ij> select abs(a) from myint; 1 ----------- ij> insert into myint values (null); 1 row inserted/updated/deleted ij> select abs(a) from myint; 1 ----------- NULL ij> autocommit off; ij> values absval( 4 ); 1 ----------- 4 ij> values absval( -4 ); 1 ----------- 4 ij> values absval( 4.4 ); 1 ---- 4.4 ij> values absval( -4.4 ); 1 ---- 4.4 ij> values {fn abs( 4 )}; 1 ----------- 4 ij> values {fn abs( -4 )}; 1 ----------- 4 ij> values {fn abs( 4.4 )}; 1 ---- 4.4 ij> values {fn abs( -4.4 )}; 1 ---- 4.4 ij> values {fn abs( -4.44444444444444444444444 )}; 1 -------------------------- 4.44444444444444444444444 ij> autocommit on; ij> drop table myint; 0 rows inserted/updated/deleted ij> -- Using Strings in escape function create table myStr( a varchar(10) ); 0 rows inserted/updated/deleted ij> insert into myStr values ( '123' ); 1 row inserted/updated/deleted ij> insert into myStr values ( '-123' ); 1 row inserted/updated/deleted ij> insert into myStr values ( '-12 ' ); 1 row inserted/updated/deleted ij> insert into myStr values ( ' -2 ' ); 1 row inserted/updated/deleted ij> insert into myStr values ( '1a3' ); 1 row inserted/updated/deleted ij> select * from myStr; A ---------- 123 -123 -12 -2 1a3 ij> select abs(a) from myStr; ERROR 42X25: The 'ABS/ABSVAL' function is not allowed on the 'VARCHAR' type. ij> select {fn abs(a)} from myStr; ERROR 42X25: The 'ABS/ABSVAL' function is not allowed on the 'VARCHAR' type. ij> drop table myStr; 0 rows inserted/updated/deleted ij> -- End of ABSVAL test -- This test EJBQL function, CONCAT. Resolve 3535 -- Begin of CONCAT test -- Basic values{ fn concat( 'hello', ' world' ) }; 1 ----------- hello world ij> VALUES{ FN CONCAT( 'HELLO', ' WORLD' ) }; 1 ----------- HELLO WORLD ij> values{ fn concat( '' , '' )}; 1 --------------- ij> values{ fn concat( CHAR(''), CHAR('') ) }; 1 --------------- ij> values{ fn concat( 45, 67 )}; ERROR 42846: Cannot convert types 'INTEGER' to 'VARCHAR'. ij> values{ fn concat( '45', 67 )}; ERROR 42846: Cannot convert types 'INTEGER' to 'VARCHAR'. ij> values{ fn concat( 45, '67' )}; ERROR 42846: Cannot convert types 'INTEGER' to 'VARCHAR'. ij> values{ fn concat( CHAR('C'), CHAR('#') ) }; 1 -- C# ij> values{ fn concat( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ`1234567890-=\ [];,./ \'' |', 'abcdefghijklmnopqrstuvwxyz~!@#$%^&*()_+|<>?:"{} '''''' ' ) }; 1 -------------------------------------------------------------------------------------------------------------------- ABCDEFGHIJKLMNOPQRSTUVWXYZ`1234567890-=\ [];,./ \' |abcdefghijklmnopqrstuvwxyz~!@#$%^&*()_+|<>?:"{} ''' ij> create table concat ( a int ); 0 rows inserted/updated/deleted ij> insert into concat values (1); 1 row inserted/updated/deleted ij> select * from CONCAT; A ----------- 1 ij> create table myconcat( a varchar(10) default null, b varchar(10) default null, c int); 0 rows inserted/updated/deleted ij> insert into myconcat (c) values( 1 ); 1 row inserted/updated/deleted ij> insert into myconcat (c) values( 2 ); 1 row inserted/updated/deleted ij> insert into myconcat (a) values( 'hello' ); 1 row inserted/updated/deleted ij> insert into myconcat (b) values( 'world' ); 1 row inserted/updated/deleted ij> insert into myconcat (a,b) values( 'hello', 'world' ); 1 row inserted/updated/deleted ij> select * from myconcat; A |B |C --------------------------------- NULL |NULL |1 NULL |NULL |2 hello |NULL |NULL NULL |world |NULL hello |world |NULL ij> select { fn concat( a, b ) } from myconcat; 1 -------------------- NULL NULL NULL NULL helloworld ij> drop table concat; 0 rows inserted/updated/deleted ij> drop table myconcat; 0 rows inserted/updated/deleted ij> -- 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' ) }; 1 ----------- 1 ij> values{ fn locate( 'hello', 'hellohello' ) }; 1 ----------- 1 ij> values{ fn locate( 'hello', 'helloworld' ) }; 1 ----------- 1 ij> values{ fn locate( 'hello', 'h?hello' ) }; 1 ----------- 3 ij> values{ fn locate( 'hello', 'match me, hello now!' ) }; 1 ----------- 11 ij> values{ fn locate( '?', '?' ) }; 1 ----------- 1 ij> values{ fn locate( '\', '\\') }; 1 ----------- 1 ij> values{ fn locate( '/', '//') }; 1 ----------- 1 ij> values{ fn locate( '\\', '\') }; 1 ----------- 0 ij> values{ fn locate( '//', '/') }; 1 ----------- 0 ij> values{ fn locate( '', 'test' ) }; 1 ----------- 1 ij> values{ fn locate( '', '' ) }; 1 ----------- 1 ij> values{ fn locate( 'test', '' ) }; 1 ----------- 0 ij> -- 3 args values{ fn locate( 'hello', 'hello',-1 ) }; 1 ----------- ERROR 22014: The start position for LOCATE is invalid; it must be a positive integer. The index to start the search from is '-1'. The string to search for is 'hello'. The string to search from is 'hello'. ij> values{ fn locate( 'hello', 'hello',-0 ) }; 1 ----------- ERROR 22014: The start position for LOCATE is invalid; it must be a positive integer. The index to start the search from is '0'. The string to search for is 'hello'. The string to search from is 'hello'. ij> values{ fn locate( 'hello', 'hello', 0 ) }; 1 ----------- ERROR 22014: The start position for LOCATE is invalid; it must be a positive integer. The index to start the search from is '0'. The string to search for is 'hello'. The string to search from is 'hello'. ij> values{ fn locate( 'hello', 'hello', 1 ) }; 1 ----------- 1 ij> values{ fn locate( 'hello', 'hello', 2 ) }; 1 ----------- 0 ij> values{ fn locate( 'hello', 'hello', 5 ) }; 1 ----------- 0 ij> values{ fn locate( 'hello', 'hello', 9 ) }; 1 ----------- 0 ij> values{ fn locate( 'hello', 'hellohello', 0 ) }; 1 ----------- ERROR 22014: The start position for LOCATE is invalid; it must be a positive integer. The index to start the search from is '0'. The string to search for is 'hello'. The string to search from is 'hellohello'. ij> values{ fn locate( 'hello', 'hellohello', 1 ) }; 1 ----------- 1 ij> values{ fn locate( 'hello', 'hellohello', 2 ) }; 1 ----------- 6 ij> values{ fn locate( 'hello', 'hellohello', 5 ) }; 1 ----------- 6 ij> values{ fn locate( 'hello', 'hellohello', 6 ) }; 1 ----------- 6 ij> values{ fn locate( 'hello', 'hellohello', 7 ) }; 1 ----------- 0 ij> values{ fn locate( 'hello', 'h?hello', 1 ) }; 1 ----------- 3 ij> values{ fn locate( 'hello', 'h?hello', 2 ) }; 1 ----------- 3 ij> values{ fn locate( 'hello', 'h?hello', 3 ) }; 1 ----------- 3 ij> values{ fn locate( 'hello', 'h?hello', 4 ) }; 1 ----------- 0 ij> values{ fn locate( 'hello', 'match me, hello now!', 7 ) }; 1 ----------- 11 ij> values{ fn locate( 'hello', 'match me, hello now!', 15 ) }; 1 ----------- 0 ij> values{ fn locate( '?', '?',-1 ) }; 1 ----------- ERROR 22014: The start position for LOCATE is invalid; it must be a positive integer. The index to start the search from is '-1'. The string to search for is '?'. The string to search from is '?'. ij> values{ fn locate( '?', '?',-0 ) }; 1 ----------- ERROR 22014: The start position for LOCATE is invalid; it must be a positive integer. The index to start the search from is '0'. The string to search for is '?'. The string to search from is '?'. ij> values{ fn locate( '?', '?', 0 ) }; 1 ----------- ERROR 22014: The start position for LOCATE is invalid; it must be a positive integer. The index to start the search from is '0'. The string to search for is '?'. The string to search from is '?'. ij> values{ fn locate( '?', '?', 1 ) }; 1 ----------- 1 ij> values{ fn locate( '?', '?', 2 ) }; 1 ----------- 0 ij> values{ fn locate( '\', '\\',0) }; 1 ----------- ERROR 22014: The start position for LOCATE is invalid; it must be a positive integer. The index to start the search from is '0'. The string to search for is '\'. The string to search from is '\\'. ij> values{ fn locate( '\', '\\',1) }; 1 ----------- 1 ij> values{ fn locate( '\', '\\',2) }; 1 ----------- 2 ij> values{ fn locate( '\', '\\',3) }; 1 ----------- 0 ij> values{ fn locate( '/', '//',0) }; 1 ----------- ERROR 22014: The start position for LOCATE is invalid; it must be a positive integer. The index to start the search from is '0'. The string to search for is '/'. The string to search from is '//'. ij> values{ fn locate( '/', '//',1) }; 1 ----------- 1 ij> values{ fn locate( '/', '//',2) }; 1 ----------- 2 ij> values{ fn locate( '/', '//',3) }; 1 ----------- 0 ij> values{ fn locate( '\\', '\',1) }; 1 ----------- 0 ij> values{ fn locate( '//', '/',1) }; 1 ----------- 0 ij> values{ fn locate( '', 'test',1) }; 1 ----------- 1 ij> values{ fn locate( '', 'test',2) }; 1 ----------- 2 ij> values{ fn locate( '', 'test',3) }; 1 ----------- 3 ij> values{ fn locate( '', 'test',4) }; 1 ----------- 4 ij> values{ fn locate( '', 'test',5) }; 1 ----------- 5 ij> values{ fn locate( '', '' ,1) }; 1 ----------- 1 ij> values{ fn locate( 'test', '',1) }; 1 ----------- 0 ij> values{ fn locate( 'test', '',2) }; 1 ----------- 0 ij> values{ fn locate( 'test', '',3) }; 1 ----------- 0 ij> values{ fn locate( 'test', '',4) }; 1 ----------- 0 ij> values{ fn locate( 'hello', 1 ) }; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> values{ fn locate( 1, 'hello' ) }; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> values{ fn locate( 'hello', 'hello', 'hello' ) }; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> values{ fn locate( 'hello', 'hello', 1.99999999999 ) }; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> values{ fn locate( 1, 'hel1lo' ) }; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> values{ fn locate( 1, 1 ) }; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> values{ fn locate( 1, 1, '1' ) }; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> values{ fn locate( '1', 1, 1 ) }; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> values{ fn locate( '1', '1', '1' ) }; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> -- 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) ); 0 rows inserted/updated/deleted ij> -- create table myChar( a char(10), b char(20), c int default '1' ); create table myChar( a char(10), b char(20), c int ); 0 rows inserted/updated/deleted ij> insert into myChar (a, b) values( '1234567890', 'abcde1234567890fghij' ); 1 row inserted/updated/deleted ij> insert into myChar (a, b) values( 'abcdefghij', 'abcdefghij1234567890' ); 1 row inserted/updated/deleted ij> insert into myChar (a, b) values( 'abcdefghij', '1234567890abcdefghij' ); 1 row inserted/updated/deleted ij> insert into myChar (a, b) values( 'abcdefghij', '1234567890!@#$%^&*()' ); 1 row inserted/updated/deleted ij> insert into myChar values( '1234567890', 'abcde1234567890fghij', 2 ); 1 row inserted/updated/deleted ij> insert into myChar values( 'abcdefghij', 'abcdefghij1234567890', 1 ); 1 row inserted/updated/deleted ij> insert into myChar values( 'abcdefghij', '1234567890abcdefghij', 15 ); 1 row inserted/updated/deleted ij> insert into myChar (c) values( 0 ); 1 row inserted/updated/deleted ij> insert into myChar (c) values( 1 ); 1 row inserted/updated/deleted ij> insert into myChar (c) values( 2 ); 1 row inserted/updated/deleted ij> insert into myChar (a) values( 'hello' ); 1 row inserted/updated/deleted ij> insert into myChar (b) values( 'hello' ); 1 row inserted/updated/deleted ij> insert into myChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 ); 1 row inserted/updated/deleted ij> select a, b, c from myChar; A |B |C ------------------------------------------- 1234567890|abcde1234567890fghij|NULL abcdefghij|abcdefghij1234567890|NULL abcdefghij|1234567890abcdefghij|NULL abcdefghij|1234567890!@#$%^&*()|NULL 1234567890|abcde1234567890fghij|2 abcdefghij|abcdefghij1234567890|1 abcdefghij|1234567890abcdefghij|15 NULL |NULL |0 NULL |NULL |1 NULL |NULL |2 hello |NULL |NULL NULL |hello |NULL abcdefghij|1234567890!@#$%^&*()|21 ij> select locate(a, b) from myChar; 1 ----------- 6 1 11 0 6 1 11 NULL NULL NULL NULL NULL 0 ij> select locate(a, b, c) from myChar; 1 ----------- 6 1 11 0 6 1 0 NULL NULL NULL NULL NULL 0 ij> drop table myChar; 0 rows inserted/updated/deleted ij> create table myLongVarChar( a long varchar, b long varchar, c int); 0 rows inserted/updated/deleted ij> insert into myLongVarChar (a, b) values( '1234567890', 'abcde1234567890fghij' ); 1 row inserted/updated/deleted ij> insert into myLongVarChar (a, b) values( 'abcdefghij', 'abcdefghij1234567890' ); 1 row inserted/updated/deleted ij> insert into myLongVarChar (a, b) values( 'abcdefghij', '1234567890abcdefghij' ); 1 row inserted/updated/deleted ij> insert into myLongVarChar (a, b) values( 'abcdefghij', '1234567890!@#$%^&*()' ); 1 row inserted/updated/deleted ij> insert into myLongVarChar (a, b) values( 'abcde', 'abcde' ); 1 row inserted/updated/deleted ij> insert into myLongVarChar (a, b) values( 'abcde', 'abcd' ); 1 row inserted/updated/deleted ij> insert into myLongVarChar (a, b) values( '', 'abcde' ); 1 row inserted/updated/deleted ij> insert into myLongVarChar (a, b) values( 'abcde', null ); 1 row inserted/updated/deleted ij> insert into myLongVarChar (a, b) values( null, 'abcde' ); 1 row inserted/updated/deleted ij> insert into myLongVarChar values( '1234567890', 'abcde1234567890fghij', 2 ); 1 row inserted/updated/deleted ij> insert into myLongVarChar values( 'abcdefghij', 'abcdefghij1234567890', 1 ); 1 row inserted/updated/deleted ij> insert into myLongVarChar values( 'abcdefghij', '1234567890abcdefghij', 15 ); 1 row inserted/updated/deleted ij> insert into myLongVarChar values( 'abcde', 'abcde', 1 ); 1 row inserted/updated/deleted ij> insert into myLongVarChar values( 'abcde', 'abcd', 1 ); 1 row inserted/updated/deleted ij> insert into myLongVarChar values( '', 'abcde', 2 ); 1 row inserted/updated/deleted ij> insert into myLongVarChar values( 'abcde', null, 1 ); 1 row inserted/updated/deleted ij> insert into myLongVarChar values( null, 'abcde', 1 ); 1 row inserted/updated/deleted ij> insert into myLongVarChar (c) values( 0 ); 1 row inserted/updated/deleted ij> insert into myLongVarChar (c) values( 1 ); 1 row inserted/updated/deleted ij> insert into myLongVarChar (c) values( 2 ); 1 row inserted/updated/deleted ij> insert into myLongVarChar (a) values( 'hello' ); 1 row inserted/updated/deleted ij> insert into myLongVarChar (b) values( 'hello' ); 1 row inserted/updated/deleted ij> insert into myLongVarChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 ); 1 row inserted/updated/deleted ij> select a, b, c from myLongVarChar; A |B |C ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1234567890 |abcde1234567890fghij |NULL abcdefghij |abcdefghij1234567890 |NULL abcdefghij |1234567890abcdefghij |NULL abcdefghij |1234567890!@#$%^&*() |NULL abcde |abcde |NULL abcde |abcd |NULL |abcde |NULL abcde |NULL |NULL NULL |abcde |NULL 1234567890 |abcde1234567890fghij |2 abcdefghij |abcdefghij1234567890 |1 abcdefghij |1234567890abcdefghij |15 abcde |abcde |1 abcde |abcd |1 |abcde |2 abcde |NULL |1 NULL |abcde |1 NULL |NULL |0 NULL |NULL |1 NULL |NULL |2 hello |NULL |NULL NULL |hello |NULL abcdefghij |1234567890!@#$%^&*() |21 ij> select locate(a, b) from myLongVarChar; 1 ----------- 6 1 11 0 1 0 1 NULL NULL 6 1 11 1 0 1 NULL NULL NULL NULL NULL NULL NULL 0 ij> select locate(a, b, c) from myLongVarChar; 1 ----------- 6 1 11 0 1 0 1 NULL NULL 6 1 0 1 0 2 NULL NULL NULL NULL NULL NULL NULL 0 ij> drop table myLongVarChar; 0 rows inserted/updated/deleted ij> create table myVarChar( a varchar(10), b varchar(20), c int ); 0 rows inserted/updated/deleted ij> insert into myVarChar (a, b) values( '1234567890', 'abcde1234567890fghij' ); 1 row inserted/updated/deleted ij> insert into myVarChar (a, b) values( 'abcdefghij', 'abcdefghij1234567890' ); 1 row inserted/updated/deleted ij> insert into myVarChar (a, b) values( 'abcdefghij', '1234567890abcdefghij' ); 1 row inserted/updated/deleted ij> insert into myVarChar (a, b) values( 'abcdefghij', '1234567890!@#$%^&*()' ); 1 row inserted/updated/deleted ij> insert into myVarChar (a, b) values( 'abcde', 'abcde' ); 1 row inserted/updated/deleted ij> insert into myVarChar (a, b) values( 'abcde', 'abcd' ); 1 row inserted/updated/deleted ij> insert into myVarChar (a, b) values( '', 'abcde' ); 1 row inserted/updated/deleted ij> insert into myVarChar (a, b) values( 'abcde', null ); 1 row inserted/updated/deleted ij> insert into myVarChar (a, b) values( null, 'abcde' ); 1 row inserted/updated/deleted ij> insert into myVarChar values( '1234567890', 'abcde1234567890fghij', 2 ); 1 row inserted/updated/deleted ij> insert into myVarChar values( 'abcdefghij', 'abcdefghij1234567890', 1 ); 1 row inserted/updated/deleted ij> insert into myVarChar values( 'abcdefghij', '1234567890abcdefghij', 15 ); 1 row inserted/updated/deleted ij> insert into myVarChar values( 'abcde', 'abcde', 1 ); 1 row inserted/updated/deleted ij> insert into myVarChar values( 'abcde', 'abcd', 1 ); 1 row inserted/updated/deleted ij> insert into myVarChar values( '', 'abcde', 2 ); 1 row inserted/updated/deleted ij> insert into myVarChar values( 'abcde', null, 1 ); 1 row inserted/updated/deleted ij> insert into myVarChar values( null, 'abcde', 1 ); 1 row inserted/updated/deleted ij> insert into myVarChar (c) values( 0 ); 1 row inserted/updated/deleted ij> insert into myVarChar (c) values( 1 ); 1 row inserted/updated/deleted ij> insert into myVarChar (c) values( 2 ); 1 row inserted/updated/deleted ij> insert into myVarChar (a) values( 'hello' ); 1 row inserted/updated/deleted ij> insert into myVarChar (b) values( 'hello' ); 1 row inserted/updated/deleted ij> insert into myVarChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 ); 1 row inserted/updated/deleted ij> select a, b, c from myVarChar; A |B |C ------------------------------------------- 1234567890|abcde1234567890fghij|NULL abcdefghij|abcdefghij1234567890|NULL abcdefghij|1234567890abcdefghij|NULL abcdefghij|1234567890!@#$%^&*()|NULL abcde |abcde |NULL abcde |abcd |NULL |abcde |NULL abcde |NULL |NULL NULL |abcde |NULL 1234567890|abcde1234567890fghij|2 abcdefghij|abcdefghij1234567890|1 abcdefghij|1234567890abcdefghij|15 abcde |abcde |1 abcde |abcd |1 |abcde |2 abcde |NULL |1 NULL |abcde |1 NULL |NULL |0 NULL |NULL |1 NULL |NULL |2 hello |NULL |NULL NULL |hello |NULL abcdefghij|1234567890!@#$%^&*()|21 ij> select locate(a, b) from myVarChar; 1 ----------- 6 1 11 0 1 0 1 NULL NULL 6 1 11 1 0 1 NULL NULL NULL NULL NULL NULL NULL 0 ij> select locate(a, b, c) from myVarChar; 1 ----------- 6 1 11 0 1 0 1 NULL NULL 6 1 0 1 0 2 NULL NULL NULL NULL NULL NULL NULL 0 ij> drop table myVarChar; 0 rows inserted/updated/deleted ij> -- Negative cases. To match DB2 behaviour create table t1 (dt date, tm time, ts timestamp); 0 rows inserted/updated/deleted ij> insert into t1 values (current_date, current_time, current_timestamp); 1 row inserted/updated/deleted ij> select locate (dt, ts) from t1; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> select locate (tm, ts) from t1; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> select locate (ts, ts) from t1; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> drop table t1; 0 rows inserted/updated/deleted ij> values locate('abc', 'dkabc', 1.4); ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> values locate('c', 'abcdedf', cast(1 as decimal(2,0))); ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> -- ========================================================================= -- 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 ); ERROR 0A000: Feature not implemented: NATIONAL CHAR. ij> insert into mynChar values( '1234567890', 'abcde1234567890fghij' ); ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> insert into mynChar values( 'abcdefghij', 'abcdefghij1234567890' ); ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> insert into mynChar values( 'abcdefghij', '1234567890abcdefghij' ); ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> insert into mynChar values( 'abcdefghij', '1234567890!@#$%^&*()' ); ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> insert into mynChar values( '1234567890', 'abcde1234567890fghij', 2 ); ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> insert into mynChar values( 'abcdefghij', 'abcdefghij1234567890', 1 ); ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> insert into mynChar values( 'abcdefghij', '1234567890abcdefghij', 15 ); ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> insert into mynChar (c) values( 0 ); ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> insert into mynChar (c) values( 1 ); ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> insert into mynChar (c) values( 2 ); ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> insert into mynChar (a) values( 'hello' ); ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> insert into mynChar (b) values( 'hello' ); ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> insert into mynChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 ); ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> select a, b, c from mynChar; ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> select locate(a, b) from mynChar; ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> select locate(a, b, c) from mynChar; ERROR 42X05: Table/View 'MYNCHAR' does not exist. ij> drop table mynChar; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'MYNCHAR' because it does not exist. ij> create table myLongnVarChar( a long nvarchar, b long nvarchar, c int ); ERROR 0A000: Feature not implemented: LONG NVARCHAR. ij> insert into myLongnVarChar values( '1234567890', 'abcde1234567890fghij' ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( 'abcdefghij', 'abcdefghij1234567890' ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( 'abcdefghij', '1234567890abcdefghij' ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( 'abcdefghij', '1234567890!@#$%^&*()' ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( 'abcde', 'abcde' ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( 'abcde', 'abcd' ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( '', 'abcde' ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( 'abcde', null ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( null, 'abcde' ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( '1234567890', 'abcde1234567890fghij', 2 ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( 'abcdefghij', 'abcdefghij1234567890', 1 ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( 'abcdefghij', '1234567890abcdefghij', 15 ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( 'abcde', 'abcde', 1 ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( 'abcde', 'abcd', 1 ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( '', 'abcde', 2 ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( 'abcde', null, 1 ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( null, 'abcde', 1 ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar (c) values( 0 ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar (c) values( 1 ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar (c) values( 2 ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar (a) values( 'hello' ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar (b) values( 'hello' ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> insert into myLongnVarChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 ); ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> select a, b, c from myLongnVarChar; ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> select locate(a, b) from myLongnVarChar; ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> select locate(a, b, c) from myLongnVarChar; ERROR 42X05: Table/View 'MYLONGNVARCHAR' does not exist. ij> drop table myLongnVarChar; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'MYLONGNVARCHAR' because it does not exist. ij> create table mynVarChar( a nvarchar(10), b nvarchar(20), c int ); ERROR 0A000: Feature not implemented: NATIONAL CHAR VARYING. ij> insert into mynVarChar values( '1234567890', 'abcde1234567890fghij' ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( 'abcdefghij', 'abcdefghij1234567890' ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( 'abcdefghij', '1234567890abcdefghij' ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( 'abcdefghij', '1234567890!@#$%^&*()' ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( 'abcde', 'abcde' ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( 'abcde', 'abcd' ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( '', 'abcde' ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( 'abcde', null ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( null, 'abcde' ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( '1234567890', 'abcde1234567890fghij', 2 ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( 'abcdefghij', 'abcdefghij1234567890', 1 ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( 'abcdefghij', '1234567890abcdefghij', 15 ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( 'abcde', 'abcde', 1 ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( 'abcde', 'abcd', 1 ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( '', 'abcde', 2 ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( 'abcde', null, 1 ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( null, 'abcde', 1 ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar (c) values( 0 ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar (c) values( 1 ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar (c) values( 2 ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar (a) values( 'hello' ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar (b) values( 'hello' ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into mynVarChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 ); ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> select a, b, c from mynVarChar; ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> select locate(a, b) from mynVarChar; ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> select locate(a, b, c) from mynVarChar; ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> create table myMixed( a char(10), b long nvarchar, c int ); ERROR 0A000: Feature not implemented: LONG NVARCHAR. ij> insert into myMixed values( '1234567890', 'abcde1234567890fghij' ); ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> insert into myMixed values( 'abcdefghij', 'abcdefghij1234567890' ); ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> insert into myMixed values( 'abcdefghij', '1234567890abcdefghij' ); ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> insert into myMixed values( 'abcdefghij', '1234567890!@#$%^&*()' ); ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> insert into myMixed values( '1234567890', 'abcde1234567890fghij', 2 ); ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> insert into myMixed values( 'abcdefghij', 'abcdefghij1234567890', 1 ); ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> insert into myMixed values( 'abcdefghij', '1234567890abcdefghij', 15 ); ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> insert into myMixed (c) values( 0 ); ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> insert into myMixed (c) values( 1 ); ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> insert into myMixed (c) values( 2 ); ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> insert into myMixed (a) values( 'hello' ); ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> insert into myMixed (b) values( 'hello' ); ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> insert into myMixed values( 'abcdefghij', '1234567890!@#$%^&*()', 21 ); ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> select a, b, c from myMixed; ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> select locate(a, b) from myMixed; ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> select locate(a, b, c) from myMixed; ERROR 42X05: Table/View 'MYMIXED' does not exist. ij> drop table myMixed; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'MYMIXED' because it does not exist. ij> create table foo( a int ); 0 rows inserted/updated/deleted ij> insert into foo select locate(a, b) from mynVarChar; ERROR 42X05: Table/View 'MYNVARCHAR' does not exist. ij> insert into foo values( {fn locate('hello', 'hello')} ); 1 row inserted/updated/deleted ij> select * from foo; A ----------- 1 ij> drop table foo; 0 rows inserted/updated/deleted ij> drop table mynVarChar; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'MYNVARCHAR' because it does not exist. ij> -- ========================================================================= -- Other types create table myBigInt( a bigint, b bigint ); 0 rows inserted/updated/deleted ij> insert into myBigInt values( 1234, 1234 ); 1 row inserted/updated/deleted ij> insert into myBigInt values( 4321, 1234 ); 1 row inserted/updated/deleted ij> select locate(a, b) from myBigInt; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> drop table myBigInt; 0 rows inserted/updated/deleted ij> create table myBit( a char for bit data, b char for bit data ); 0 rows inserted/updated/deleted ij> insert into myBit values( X'40', X'40' ); 1 row inserted/updated/deleted ij> insert into myBit values( X'01', X'40' ); 1 row inserted/updated/deleted ij> select locate(a, b) from myBit; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> drop table myBit; 0 rows inserted/updated/deleted ij> -- bug 5794 - LOCATE built-in function is not db2 udb compatible create table myDate( a date, b date ); 0 rows inserted/updated/deleted ij> insert into myDate values( date('1970-01-08'), date('1970-01-08') ); 1 row inserted/updated/deleted ij> insert into myDate values( date('1979-08-30'), date('1978-07-28') ); 1 row inserted/updated/deleted ij> select locate(a, b) from myDate; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> drop table myDate; 0 rows inserted/updated/deleted ij> create table myDecimal( a decimal, b decimal ); 0 rows inserted/updated/deleted ij> insert into myDecimal values( 2.2, 2.2 ); 1 row inserted/updated/deleted ij> insert into myDecimal values( 12.23, 3423 ); 1 row inserted/updated/deleted ij> select locate(a, b) from myDecimal; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> drop table myDecimal; 0 rows inserted/updated/deleted ij> create table myDouble( a double precision, b double precision ); 0 rows inserted/updated/deleted ij> insert into myDouble values( 2.2, 2.2 ); 1 row inserted/updated/deleted ij> insert into myDouble values( 12.23, 3423 ); 1 row inserted/updated/deleted ij> select locate(a, b) from myDouble; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> drop table myDouble; 0 rows inserted/updated/deleted ij> create table myInteger(a integer, b integer ); 0 rows inserted/updated/deleted ij> insert into myInteger values( 2, 2 ); 1 row inserted/updated/deleted ij> insert into myInteger values( 123, 3423 ); 1 row inserted/updated/deleted ij> select locate(a, b) from myInteger; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> drop table myInteger; 0 rows inserted/updated/deleted ij> create table mylongvarbinary( a long varchar for bit data, b long varchar for bit data ); 0 rows inserted/updated/deleted ij> select locate(a, b) from mylongvarbinary; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> drop table mylongvarbinary; 0 rows inserted/updated/deleted ij> -- bug 5794 - LOCATE built-in function is not db2 udb compatible create table mytime( a time, b time ); 0 rows inserted/updated/deleted ij> insert into mytime values( time('10:00:00'), time('10:00:00') ); 1 row inserted/updated/deleted ij> insert into mytime values( time('10:00:00'), time('11:00:00') ); 1 row inserted/updated/deleted ij> select locate(a, b) from mytime; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> drop table mytime; 0 rows inserted/updated/deleted ij> -- bug 5794 - LOCATE built-in function is not db2 udb compatible create table mytimestamp( a timestamp, b timestamp ); 0 rows inserted/updated/deleted ij> insert into mytimestamp values( timestamp('1997-01-01 03:03:03'), timestamp('1997-01-01 03:03:03' )); 1 row inserted/updated/deleted ij> insert into mytimestamp values( timestamp('1997-01-01 03:03:03'), timestamp('1997-01-01 04:03:03' )); 1 row inserted/updated/deleted ij> select locate(a, b) from mytimestamp; ERROR 42884: No authorized routine named 'LOCATE' of type 'FUNCTION' having compatible arguments was found. ij> drop table mytimestamp; 0 rows inserted/updated/deleted ij> -- 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) ); 0 rows inserted/updated/deleted ij> insert into foo values( 'abc', 'abcd' ); 1 row inserted/updated/deleted ij> insert into foo (a,b) values ( 'ABC', NULL ); 1 row inserted/updated/deleted ij> insert into foo (a,b) values ( NULL, 'DEF' ); 1 row inserted/updated/deleted ij> insert into foo (a,b) values ( 'ABC', '') ; 1 row inserted/updated/deleted ij> insert into foo (a,b) values ( '', 'DEF' ); 1 row inserted/updated/deleted ij> insert into foo (a,b) values ( '', '' ); 1 row inserted/updated/deleted ij> insert into foo (a,b) values ( NULL, NULL ); 1 row inserted/updated/deleted ij> insert into foo (a,b) values ( 'GHJK', 'GHJ' ); 1 row inserted/updated/deleted ij> insert into foo (a,b) values ( 'QWE', 'QWERT' ); 1 row inserted/updated/deleted ij> insert into foo (a,b) values ( 'TYUI', 'RTYUI' ); 1 row inserted/updated/deleted ij> insert into foo (a,b) values ( 'IOP', 'UIOP[' ); 1 row inserted/updated/deleted ij> insert into foo (a,b) values ( 'ZXCV', 'ZXCV' ); 1 row inserted/updated/deleted ij> select * from foo; A |B ------------------------------- abc |abcd ABC |NULL NULL |DEF ABC | |DEF | NULL |NULL GHJK |GHJ QWE |QWERT TYUI |RTYUI IOP |UIOP[ ZXCV |ZXCV ij> select locate(a, b) from foo; 1 ----------- 1 NULL NULL 0 1 1 NULL 0 1 2 2 1 ij> select locate(a, b, 0) from foo; 1 ----------- ERROR 22014: The start position for LOCATE is invalid; it must be a positive integer. The index to start the search from is '0'. The string to search for is 'abc'. The string to search from is 'abcd'. ij> select locate(a, b, -1) from foo; 1 ----------- ERROR 22014: The start position for LOCATE is invalid; it must be a positive integer. The index to start the search from is '-1'. The string to search for is 'abc'. The string to search from is 'abcd'. ij> select locate(a, b, 1) from foo; 1 ----------- 1 NULL NULL 0 1 1 NULL 0 1 2 2 1 ij> select locate(a, b, 2) from foo; 1 ----------- 0 NULL NULL 0 2 2 NULL 0 0 2 2 0 ij> select locate(a, b, 200) from foo; 1 ----------- 0 NULL NULL 0 200 200 NULL 0 0 0 0 0 ij> drop table foo; 0 rows inserted/updated/deleted ij> -- 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); 0 rows inserted/updated/deleted ij> insert into loc (c) values ('This world is crazy' ); 1 row inserted/updated/deleted ij> insert into loc (c) values ('nada' ); 1 row inserted/updated/deleted ij> insert into loc (b) values ( 3 ); 1 row inserted/updated/deleted ij> select * from loc; C |A |B -------------------------------------------- This world is crazy |NULL |NULL nada |NULL |NULL NULL |NULL |3 ij> select c, locate( 'crazy', c ) from loc; C |2 -------------------------------- This world is crazy |15 nada |0 NULL |NULL ij> autocommit off; ij> -- Prepare Statements prepare p1 as 'select locate( ''crazy'', c ) from loc'; ij> execute p1; 1 ----------- 15 0 NULL ij> -- first arg ? prepare p2 as 'select locate( ?, c ) from loc'; ij> execute p2 using 'values ( ''crazy'' )'; 1 ----------- 15 0 NULL ij> execute p2 using 'values ( ''hahah'' )'; 1 ----------- 0 0 NULL ij> -- second arg ? prepare p3 as 'select locate( ''nada'', ? ) from loc'; ij> execute p3 using 'values ( ''nada'' )'; 1 ----------- 1 1 1 ij> execute p3 using 'values ( ''haha'' )'; 1 ----------- 0 0 0 ij> -- both first and second arguments ? ? prepare p4 as 'select locate( ?, ? ) from loc'; ij> execute p4 using 'values ( ''dont'', ''match'' )'; 1 ----------- 0 0 0 ij> execute p4 using 'values ( ''match'', ''me match me'' )'; 1 ----------- 4 4 4 ij> -- thrid arg ? prepare p5 as 'select locate( c, c, ? ) from loc'; ij> execute p5 using 'values ( 1 )'; 1 ----------- 1 1 NULL ij> execute p5 using 'values ( 2 )'; 1 ----------- 0 0 NULL ij> -- all args ? ? ? prepare p6 as 'select locate( ?, ?, ? ) from loc'; ij> execute p6 using 'values ( ''hello'', ''no match'', 1 )'; 1 ----------- 0 0 0 ij> execute p6 using 'values ( ''match'', ''me match me'', 2 )'; 1 ----------- 4 4 4 ij> -- Prepare Statements prepare p7 as 'select {fn locate( ''crazy'', c )} from loc'; ij> execute p7; 1 ----------- 15 0 NULL ij> -- first arg ? prepare p7 as 'select {fn locate( ?, c )} from loc'; ij> execute p7 using 'values ( ''crazy'' )'; 1 ----------- 15 0 NULL ij> execute p7 using 'values ( ''hahah'' )'; 1 ----------- 0 0 NULL ij> -- second arg ? prepare p8 as 'select {fn locate( ''nada'', ? )} from loc'; ij> execute p8 using 'values ( ''nada'' )'; 1 ----------- 1 1 1 ij> execute p8 using 'values ( ''haha'' )'; 1 ----------- 0 0 0 ij> -- both first and second arguments ? ? prepare p9 as 'select {fn locate( ?, ? )} from loc'; ij> execute p9 using 'values ( ''dont'', ''match'' )'; 1 ----------- 0 0 0 ij> execute p9 using 'values ( ''match'', ''me match me'' )'; 1 ----------- 4 4 4 ij> -- thrid arg ? prepare p10 as 'select {fn locate( c, c, ? )} from loc'; ij> execute p10 using 'values ( 1 )'; 1 ----------- 1 1 NULL ij> execute p10 using 'values ( 2 )'; 1 ----------- 0 0 NULL ij> -- all args ? ? ? prepare p11 as 'select {fn locate( ?, ?, ? )} from loc'; ij> execute p11 using 'values ( ''hello'', ''no match'', 1 )'; 1 ----------- 0 0 0 ij> execute p11 using 'values ( ''match'', ''me match me'', 2 )'; 1 ----------- 4 4 4 ij> autocommit on; ij> drop table loc; 0 rows inserted/updated/deleted ij> -- 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 ); 0 rows inserted/updated/deleted ij> select sqrt(a) from myreal; 1 ---------------------- ij> insert into myreal values (null), (+0), (-0), (+1), (null), (100000000), (3.402E+38), (1.175E-37); 8 rows inserted/updated/deleted ij> select a from myreal; A ------------- NULL 0.0 0.0 1.0 NULL 1.0E8 3.402E38 1.175E-37 ij> select sqrt(a) from myreal; 1 ---------------------- NULL 0.0 0.0 1.0 NULL 10000.0 1.844451138023282E19 3.427827260414494E-19 ij> select -sqrt(a) from myreal; 1 ---------------------- NULL 0.0 0.0 -1.0 NULL -10000.0 -1.844451138023282E19 -3.427827260414494E-19 ij> select sqrt(sqrt(-sqrt(-sqrt(a)))) from myreal; 1 ---------------------- NULL 0.0 0.0 ERROR 22013: Attempt to take the square root of a negative number, '-1.0'. ij> SELECT SQRT(SQRT(-SQRT(-SQRT(A)))) FROM MYREAL; 1 ---------------------- NULL 0.0 0.0 ERROR 22013: Attempt to take the square root of a negative number, '-1.0'. ij> select sqrt(sqrt(sqrt(sqrt(a)))) from myreal; 1 ---------------------- NULL 0.0 0.0 1.0 NULL 3.1622776601683795 255.99612668910464 0.004919006155611499 ij> select distinct sqrt(a) from myreal; 1 ---------------------- 0.0 3.427827260414494E-19 1.0 10000.0 1.844451138023282E19 NULL ij> drop table myreal; 0 rows inserted/updated/deleted ij> -- End of Real test -- Double Precision has a range of +/-4.9E-324 to +/-1.7976931348623157E+308 -- Basic create table mydoubleprecision( a double precision ); 0 rows inserted/updated/deleted ij> select sqrt(a) from mydoubleprecision; 1 ---------------------- ij> insert into mydoubleprecision values (null), (+0), (-0), (+1), (100000000), (null), (1.79769E+308), (2.225E-307); 8 rows inserted/updated/deleted ij> select a from mydoubleprecision; A ---------------------- NULL 0.0 0.0 1.0 1.0E8 NULL 1.79769E308 2.225E-307 ij> select sqrt(a) from mydoubleprecision; 1 ---------------------- NULL 0.0 0.0 1.0 10000.0 NULL 1.3407796239501852E154 4.716990566028302E-154 ij> select -sqrt(a) from mydoubleprecision; 1 ---------------------- NULL 0.0 0.0 -1.0 -10000.0 NULL -1.3407796239501852E1& -4.716990566028302E-1& ij> select sqrt(sqrt(-sqrt(-sqrt(a)))) from mydoubleprecision; 1 ---------------------- NULL 0.0 0.0 ERROR 22013: Attempt to take the square root of a negative number, '-1.0'. ij> SELECT SQRT(SQRT(-SQRT(-SQRT(A)))) FROM MYDOUBLEPRECISION; 1 ---------------------- NULL 0.0 0.0 ERROR 22013: Attempt to take the square root of a negative number, '-1.0'. ij> select sqrt(sqrt(sqrt(sqrt(a)))) from mydoubleprecision; 1 ---------------------- NULL 0.0 0.0 1.0 3.1622776601683795 NULL 1.8446742063214512E19 6.826657681281494E-20 ij> select distinct sqrt(a) from mydoubleprecision; 1 ---------------------- 0.0 4.716990566028302E-154 1.0 10000.0 1.3407796239501852E154 NULL ij> drop table mydoubleprecision; 0 rows inserted/updated/deleted ij> -- 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) ); 0 rows inserted/updated/deleted ij> select sqrt(a), sqrt(b) from myfloat; 1 |2 --------------------------------------------- ij> select columnname, columndatatype from sys.syscolumns c, sys.systables t where c.referenceid = t.tableid and CAST(t.tablename AS VARCHAR(128)) = 'MYFLOAT'; COLUMNNAME |COLUMNDATATYPE ------------------------------------------------------------------------------------------------------------------------------------------------ A |DOUBLE B |REAL ij> 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); 8 rows inserted/updated/deleted ij> select a, b from myfloat; A |B ------------------------------------ NULL |NULL 0.0 |0.0 0.0 |0.0 1.0 |1.0 1.0E8 |1.0E8 NULL |NULL 1.79769E308 |3.402E38 2.225E-307 |1.175E-37 ij> select sqrt(a), sqrt(b) from myfloat; 1 |2 --------------------------------------------- NULL |NULL 0.0 |0.0 0.0 |0.0 1.0 |1.0 10000.0 |10000.0 NULL |NULL 1.3407796239501852E154|1.844451138023282E19 4.716990566028302E-154|3.427827260414494E-19 ij> select -sqrt(a), -sqrt(b) from myfloat; 1 |2 --------------------------------------------- NULL |NULL 0.0 |0.0 0.0 |0.0 -1.0 |-1.0 -10000.0 |-10000.0 NULL |NULL -1.3407796239501852E1&|-1.844451138023282E19 -4.716990566028302E-1&|-3.427827260414494E-19 ij> select sqrt(sqrt(-sqrt(-sqrt(a)))), sqrt(sqrt(-sqrt(-sqrt(b)))) from myfloat; 1 |2 --------------------------------------------- NULL |NULL 0.0 |0.0 0.0 |0.0 ERROR 22013: Attempt to take the square root of a negative number, '-1.0'. ij> SELECT SQRT(SQRT(-SQRT(-SQRT(A)))), SQRT(SQRT(-SQRT(-SQRT(B)))) FROM MYFLOAT; 1 |2 --------------------------------------------- NULL |NULL 0.0 |0.0 0.0 |0.0 ERROR 22013: Attempt to take the square root of a negative number, '-1.0'. ij> select sqrt(sqrt(sqrt(sqrt(a)))), sqrt(sqrt(sqrt(sqrt(b)))) from myfloat; 1 |2 --------------------------------------------- NULL |NULL 0.0 |0.0 0.0 |0.0 1.0 |1.0 3.1622776601683795 |3.1622776601683795 NULL |NULL 1.8446742063214512E19 |255.99612668910464 6.826657681281494E-20 |0.004919006155611499 ij> select distinct sqrt(a) from myfloat; 1 ---------------------- 0.0 4.716990566028302E-154 1.0 10000.0 1.3407796239501852E154 NULL ij> select distinct sqrt(b) from myfloat; 1 ---------------------- 0.0 3.427827260414494E-19 1.0 10000.0 1.844451138023282E19 NULL ij> drop table myfloat; 0 rows inserted/updated/deleted ij> -- End of Float test -- Test some different statements, just in case -- beetle 5804 - support FLOAT built-in function create table foo( a float ); 0 rows inserted/updated/deleted ij> insert into foo values ( sqrt(FLOAT( 1))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> insert into foo values ( sqrt(FLOAT( 2))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> insert into foo values (-sqrt(FLOAT( 3))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> insert into foo values (-sqrt(FLOAT( 4))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> insert into foo values ( (FLOAT(-5))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> -- this insert should fail insert into foo values ( sqrt(FLOAT(-3))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 2, column 31. ij> autocommit off; ij> prepare p1 as 'select a from foo'; ij> prepare p2 as 'insert into foo select a*(-1) from foo'; ij> execute p1; A ---------------------- ij> execute p2; 0 rows inserted/updated/deleted ij> execute p1; A ---------------------- ij> insert into foo values ( sqrt(FLOAT( 6))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> insert into foo values (-sqrt(FLOAT( 7))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> insert into foo values (-sqrt(FLOAT( 8))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> insert into foo values ( sqrt(FLOAT( 9))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> insert into foo values ( (FLOAT(10))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> -- few negative tests -- insert should fail insert into foo values ( sqrt(FLOAT(-7))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 3, column 31. ij> prepare p3 as 'select sqrt(a) from foo'; ij> -- executing p3 should fail execute p3; 1 ---------------------- ij> -- these should pass execute p1; A ---------------------- ij> execute p2; 0 rows inserted/updated/deleted ij> execute p1; A ---------------------- ij> rollback; ij> commit; ij> autocommit on; ij> insert into foo values ( sqrt(FLOAT( 11))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> insert into foo values (-sqrt(FLOAT( 12))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> insert into foo values (-sqrt(FLOAT( 13))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> insert into foo values (-sqrt(FLOAT( 14))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> insert into foo values ( (FLOAT( 15))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> -- these 2 inserts should fail insert into foo values (-sqrt(FLOAT(-12))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 2, column 31. ij> insert into foo values ( sqrt(FLOAT(-13))); ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31. ij> -- these should pass autocommit off; ij> execute p1; A ---------------------- ij> execute p3; 1 ---------------------- ij> execute p1; A ---------------------- ij> -- executing p2 should fail execute p2; 0 rows inserted/updated/deleted ij> autocommit on; ij> select * from foo; A ---------------------- ij> drop table foo; 0 rows inserted/updated/deleted ij> -- 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 ); 0 rows inserted/updated/deleted ij> create table myinteger( a Integer ); 0 rows inserted/updated/deleted ij> select sqrt(a) from myint; 1 ---------------------- ij> select sqrt(a) from myinteger; 1 ---------------------- ij> drop table myint; 0 rows inserted/updated/deleted ij> drop table myinteger; 0 rows inserted/updated/deleted ij> create table mysmallint( a smallint ); 0 rows inserted/updated/deleted ij> select sqrt(a) from mysmallint; 1 ---------------------- ij> drop table mysmallint; 0 rows inserted/updated/deleted ij> create table mybigint( a bigint ); 0 rows inserted/updated/deleted ij> select sqrt(a) from mybigint; 1 ---------------------- ij> drop table mybigint; 0 rows inserted/updated/deleted ij> create table mydecimal( a decimal ); 0 rows inserted/updated/deleted ij> select sqrt(a) from mydecimal; 1 ---------------------- ij> drop table mydecimal; 0 rows inserted/updated/deleted ij> -- For escape function. -- Integer -- Basic values{ fn sqrt(INT('0'))}; 1 ---------------------- 0.0 ij> -- Smallint -- Basic -- beetle 5805 - support INT[EGER] built-in function values{ fn sqrt(SMALLINT('0'))}; 1 ---------------------- 0.0 ij> -- Bigint -- Basic -- beetle 5809 - support BIGINT built-in function values{ fn sqrt(BIGINT('0'))}; 1 ---------------------- 0.0 ij> -- Real -- Basic -- beetle 5806 - support REAL built-in function values{fn sqrt( REAL( 0) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 4, column 17. ij> values{fn sqrt( REAL(-0) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17. ij> values{fn sqrt( REAL( 1) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17. ij> values{fn sqrt( REAL(-1) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17. ij> values{fn sqrt( REAL( 1000000.001) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17. ij> values{fn sqrt( REAL(-1000000.001) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17. ij> values{fn sqrt( REAL( 3.402E+38) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17. ij> values{fn sqrt( REAL(-3.402E+38) + 1 )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17. ij> -- Error values{fn sqrt( REAL( 3.402E+38 * 2) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 2, column 17. ij> values{fn sqrt(-REAL( NaN) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17. ij> values{fn sqrt( REAL( 1.40129846432481707e-45) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17. ij> values{fn sqrt( REAL( 3.40282346638528860e+38) )}; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17. ij> -- Double Precision/Double -- Basic -- beetle 5803 - support DOUBLE_[PRECISION] built-in function values{fn sqrt( DOUBLE( 0) )}; 1 ---------------------- 0.0 ij> values{fn sqrt( DOUBLE(-0) )}; 1 ---------------------- 0.0 ij> values{fn sqrt( DOUBLE( 1) )}; 1 ---------------------- 1.0 ij> values{fn -sqrt( DOUBLE(1) )}; ERROR 42X01: Syntax error: Encountered "-" at line 1, column 11. ij> values{fn sqrt( DOUBLE( 1000000.001) )}; 1 ---------------------- 1000.0000005 ij> values{fn -sqrt( DOUBLE(1000000.001) )}; ERROR 42X01: Syntax error: Encountered "-" at line 1, column 11. ij> values{fn -sqrt( DOUBLE(1.79769E+308) )}; ERROR 42X01: Syntax error: Encountered "-" at line 1, column 11. ij> values{fn sqrt( DOUBLE( 1.79769E+308) + 1 )}; 1 ---------------------- 1.3407796239501852E154 ij> values{fn sqrt( DOUBLE( 2.225E-307 + 1) )}; 1 ---------------------- 1.0 ij> -- Error values{fn sqrt( DOUBLE(-1) )}; 1 ---------------------- ERROR 22013: Attempt to take the square root of a negative number, '-1.0'. ij> values{fn sqrt( DOUBLE(-1000000.001) )}; 1 ---------------------- ERROR 22013: Attempt to take the square root of a negative number, '-1000000.001'. ij> values{fn sqrt( DOUBLE(-1.79769E+308) )}; 1 ---------------------- ERROR 22013: Attempt to take the square root of a negative number, '-1.79769E308'. ij> values{fn sqrt( DOUBLE( 1.79769E+308 * 2) )}; 1 ---------------------- ERROR 22003: The resulting value is outside the range for the data type DOUBLE. ij> values{fn sqrt(-DOUBLE( NaN) )}; ERROR 42X04: Column 'NAN' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'NAN' is not a column in the target table. ij> values{fn sqrt( DOUBLE( 4.9E-324) )}; ERROR 22003: The resulting value is outside the range for the data type DOUBLE. ij> values{fn sqrt( DOUBLE( 1.7976931348623157E308) )}; ERROR 22003: The resulting value is outside the range for the data type DOUBLE. ij> -- Decimal/Numeric -- Basic -- beetle 5802 - support DEC[IMAL] built-in function values{ fn sqrt(DEC('0'))}; ERROR 42X01: Syntax error: Encountered "DEC" at line 4, column 17. ij> -- More generic test values{ fn sqrt( 0+1+.1 ) }; 1 ---------------------- 1.0488088481701516 ij> values{ fn sqrt( +0+1.000000001 ) }; 1 ---------------------- 1.0000000005 ij> VALUES{ FN sqrt( 100+200+300 ) }; 1 ---------------------- 24.49489742783178 ij> values{ fn sqrt( 0-1-.1 ) }; 1 ---------------------- ERROR 22013: Attempt to take the square root of a negative number, '-1.1'. ij> values{ fn sqrt( -0-1.000000001 ) }; 1 ---------------------- ERROR 22013: Attempt to take the square root of a negative number, '-1.000000001'. ij> VALUES{ FN sqrt( 100-200-300 ) }; 1 ---------------------- ERROR 22013: Attempt to take the square root of a negative number, '-400.0'. ij> -- Error values{ fn sqrt('null') }; ERROR 42X25: The 'SQRT' function is not allowed on the 'CHAR' type. ij> -- sqrt as a keyword create table sqrt( a int ); 0 rows inserted/updated/deleted ij> -- 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 ); 0 rows inserted/updated/deleted ij> select sqrt(a) from myreal; 1 ---------------------- ij> insert into myreal values ( 3.402E+38 ); 1 row inserted/updated/deleted ij> select a from myreal; A ------------- 3.402E38 ij> -- Prepare Statements, should pass -- beetle 5806 - support REAL built-in function autocommit off; ij> prepare p1 as 'select a from myreal where ? <> 1'; ij> execute p1 using 'values sqrt(REAL( 0 ))'; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 14. ij> execute p1 using 'values -sqrt(REAL( 20))'; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 14. ij> execute p1 using 'values sqrt(REAL( 20))'; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 14. ij> -- this should fail execute p1 using 'values sqrt(REAL(-20))'; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 14. ij> -- Prepare Statements, should pass -- beetle 5806 - support REAL built-in function prepare p2 as 'select a from myreal where ? <> 1'; ij> execute p2 using 'values {fn sqrt (REAL( 0 ))}'; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 18. ij> execute p2 using 'values {fn -sqrt(REAL( 20))}'; ERROR 42X01: Syntax error: Encountered "-" at line 1, column 12. ij> execute p2 using 'values {fn sqrt (REAL( 20))}'; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 18. ij> -- this should fail execute p2 using 'values {fn sqrt(REAL(-20))}'; ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 18. ij> autocommit on; ij> -- mod function create table modfn(s smallint, i int, b bigint, c char(10), d decimal(6,3), r real, dbl double); 0 rows inserted/updated/deleted ij> insert into modfn values(0, 0, 0, '0', 0.0, 0.0, 0.0); 1 row inserted/updated/deleted ij> insert into modfn values(5, 5, 5, '5', 5.0, 5.0, 5.0); 1 row inserted/updated/deleted ij> insert into modfn values(null, null, null, null, null, null, null); 1 row inserted/updated/deleted ij> select { fn mod(s, 3) } from modfn; 1 ----------- 0 2 NULL ij> select { fn mod(i, 3) } from modfn; 1 ----------- 0 2 NULL ij> select { fn mod(b, 3) } from modfn; 1 -------------------- 0 2 NULL ij> select { fn mod(c, 3) } from modfn; 1 ----------- 0 2 NULL ij> select { fn mod(d, 3) } from modfn; ERROR 42Y95: The 'mod' operator with a left operand type of 'DECIMAL' and a right operand type of 'INTEGER' is not supported. ij> select { fn mod(r, 3) } from modfn; ERROR 42Y95: The 'mod' operator with a left operand type of 'REAL' and a right operand type of 'INTEGER' is not supported. ij> select { fn mod(dbl, 3) } from modfn; ERROR 42Y95: The 'mod' operator with a left operand type of 'DOUBLE' and a right operand type of 'INTEGER' is not supported. ij> select { fn mod(67, t) } from modfn where s <> 0; ERROR 42X04: Column 'T' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T' is not a column in the target table. ij> select { fn mod(67, s) } from modfn where s <> 0; 1 ----------- 2 ij> select { fn mod(67, i) } from modfn where s <> 0; 1 ----------- 2 ij> select { fn mod(67, b) } from modfn where s <> 0; 1 -------------------- 2 ij> select { fn mod(67, c) } from modfn where s <> 0; 1 ----------- 2 ij> select { fn mod(67, d) } from modfn where s <> 0; ERROR 42Y95: The 'mod' operator with a left operand type of 'INTEGER' and a right operand type of 'DECIMAL' is not supported. ij> select { fn mod(67, r) } from modfn where s <> 0; ERROR 42Y95: The 'mod' operator with a left operand type of 'INTEGER' and a right operand type of 'REAL' is not supported. ij> select { fn mod(67, dbl) } from modfn where s <> 0; ERROR 42Y95: The 'mod' operator with a left operand type of 'INTEGER' and a right operand type of 'DOUBLE' is not supported. ij> select { fn mod(s, s) } from modfn where s = 0; 1 ------ ERROR 22012: Attempt to divide by zero. ij> select { fn mod(i, i) } from modfn where s = 0; 1 ----------- ERROR 22012: Attempt to divide by zero. ij> select { fn mod(i, b) } from modfn where s = 0; 1 -------------------- ERROR 22012: Attempt to divide by zero. ij> select { fn mod(s, s) } from modfn where s is null; 1 ------ NULL ij> select { fn mod(i, i) } from modfn where s is null; 1 ----------- NULL ij> select { fn mod(i, b) } from modfn where s is null; 1 -------------------- NULL ij> select { fn mod(67, i) } from modfn where i <> 0; 1 ----------- 2 ij> select { fn mod(67, b) } from modfn where b <> 0; 1 -------------------- 2 ij> -- this query should fail because of incompatible arguments select { fn mod('rrrr', b) } from modfn where b <> 0; 1 -------------------- ERROR 22018: Invalid character string format for type BIGINT. ij> values { fn mod(23, 9)}; 1 ----------- 5 ij> values mod(23, 9); 1 ----------- 5 ij> create table mod(mod int); 0 rows inserted/updated/deleted ij> insert into mod values(1); 1 row inserted/updated/deleted ij> select mod from mod; MOD ----------- 1 ij> select mod(mod,mod) from mod; 1 ----------- 0 ij> drop table mod; 0 rows inserted/updated/deleted ij> drop table modfn; 0 rows inserted/updated/deleted ij> -- Using Strings in escape function create table myStr( a varchar(10) ); 0 rows inserted/updated/deleted ij> insert into myStr values ( '123' ); 1 row inserted/updated/deleted ij> insert into myStr values ( ' 123' ); 1 row inserted/updated/deleted ij> insert into myStr values ( ' 12 ' ); 1 row inserted/updated/deleted ij> insert into myStr values ( ' 2 ' ); 1 row inserted/updated/deleted ij> insert into myStr values ( '1a3' ); 1 row inserted/updated/deleted ij> select * from myStr; A ---------- 123 123 12 2 1a3 ij> select sqrt(a) from myStr; ERROR 42X25: The 'SQRT' function is not allowed on the 'VARCHAR' type. ij> select {fn sqrt(a)} from myStr; ERROR 42X25: The 'SQRT' function is not allowed on the 'VARCHAR' type. ij> select {fn sqrt( '-12' ) } from myStr; ERROR 42X25: The 'SQRT' function is not allowed on the 'CHAR' type. ij> select {fn sqrt( '-1a2' ) } from myStr; ERROR 42X25: The 'SQRT' function is not allowed on the 'CHAR' type. ij> drop table myreal; 0 rows inserted/updated/deleted ij> drop table myStr; 0 rows inserted/updated/deleted ij> -- 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); 0 rows inserted/updated/deleted ij> create table t2 (c20 char(20), c30 char(30), c40 char(40), vc20 varchar(20), vc30 varchar(30), vc40 varchar(40), lvc long varchar); 0 rows inserted/updated/deleted ij> -- populate the tables insert into t1 (c15a) values(null); 1 row inserted/updated/deleted ij> insert into t1 values('1', '2', '3', '4', '5'); 1 row inserted/updated/deleted ij> insert into t1 values('111111', '222222222222222', '333333', '444444444444444', '555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555'); 1 row inserted/updated/deleted ij> insert into t1 values('555555 ', '66 ', '777777 ', '88 ', '99999999999999999999999999999999999999999999999999999999999999999999999999999999999 '); 1 row inserted/updated/deleted ij> -- negative tests -- mixing char and bit (illegal) values X'11' || 'asdf'; ERROR 42884: No authorized routine named '||' of type 'FUNCTION' having compatible arguments was found. ij> values 'adsf' || X'11'; ERROR 42884: No authorized routine named '||' of type 'FUNCTION' having compatible arguments was found. ij> -- ? parameter on both sides values ? || ?; ERROR 42X35: It is not allowed for both operands of '||' to be ? parameters. ij> -- simple positive values 'aaa' || 'bbb'; 1 ------ aaabbb ij> values X'aaaa' || X'bbbb'; 1 -------- aaaabbbb ij> -- non-blank truncation error on char result insert into t2 (c20) select c15a || c15b from t1 where c15a = '111111'; ERROR 22001: A truncation error was encountered trying to shrink CHAR '111111 222222222222222' to length 20. ij> insert into t2 (vc20) select vc15a || vc15b from t1 where c15a= '111111'; ERROR 22001: A truncation error was encountered trying to shrink VARCHAR '333333444444444444444' to length 20. ij> insert into t2 (c20) select lvc || lvc from t1 where c15a = '111111'; ERROR 22001: A truncation error was encountered trying to shrink CHAR '555555555555555555555555555555555555555555555555555555555555&' to length 20. ij> maximumdisplaywidth 512; ij> -- positive tests -- blank truncation on varchar insert into t2 (c20) select vc15a || vc15b from t1 where c15a = '555555 '; 1 row inserted/updated/deleted ij> select c20 from t2; C20 -------------------- 777777 88 ij> delete from t2; 1 row inserted/updated/deleted ij> -- no blank truncation on char insert into t2 (c30) select c15a || c15b from t1 where c15a = '555555 '; 1 row inserted/updated/deleted ij> select c30 from t2; C30 ------------------------------ 555555 66 ij> delete from t2; 1 row inserted/updated/deleted ij> -- long varchar insert into t2 (c30) select lvc || lvc from t1 where c15a = '1'; 1 row inserted/updated/deleted ij> select c30 from t2; C30 ------------------------------ 55 ij> delete from t2; 1 row inserted/updated/deleted ij> -- vc || c -> vc insert into t2 (c30) select vc15a || c15a from t1 where c15a = '555555 '; 1 row inserted/updated/deleted ij> select c30 from t2; C30 ------------------------------ 777777 555555 ij> delete from t2; 1 row inserted/updated/deleted ij> -- c || vc -> vc insert into t2 (c30) select c15a || vc15a || '9' from t1 where c15a = '555555 '; 1 row inserted/updated/deleted ij> select c30 from t2; C30 ------------------------------ 555555 777777 9 ij> delete from t2; 1 row inserted/updated/deleted ij> -- vc || c -> lvc insert into t2 (lvc) select c15a || vc15a from t1 where c15a = '555555 '; 1 row inserted/updated/deleted ij> select lvc from t2; LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 555555 777777 ij> select length(lvc) from t2; 1 ----------- 25 ij> delete from t2; 1 row inserted/updated/deleted ij> -- lvc || lvc - > lvc insert into t2 (lvc) select lvc || lvc from t1; 4 rows inserted/updated/deleted ij> select lvc from t2; LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL 55 555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555 99999999999999999999999999999999999999999999999999999999999999999999999999999999999 99999999999999999999999999999999999999999999999999999999999999999999999999999999999 ij> delete from t2; 4 rows inserted/updated/deleted ij> -- 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; ij> -- ? || c prepare pc as 'select ? || c15a from t1'; ij> execute pc using 'values (''left'')'; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL left1 left111111 left555555 ij> -- c || ? prepare cp as 'select c15a || ? from t1'; ij> execute cp using 'values (''right'')'; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL 1 right 111111 right 555555 right ij> -- ? || v prepare pv as 'select ? || vc15a from t1'; ij> execute pv using 'values (''left'')'; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL left3 left333333 left777777 ij> -- v || ? prepare vp as 'select vc15a || ? from t1'; ij> execute vp using 'values (''right'')'; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL 3right 333333right 777777 right ij> -- 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'; ij> execute lvp using 'values (''right'')'; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL 5right 555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555right 99999999999999999999999999999999999999999999999999999999999999999999999999999999999 right ij> -- ? || lvc prepare plv as 'select ? || lvc from t1'; ij> execute plv using 'values (''left'')'; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL left5 left555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555 left99999999999999999999999999999999999999999999999999999999999999999999999999999999999 ij> autocommit on; ij> -- multiple concatenations insert into t2 (c30, vc30) values ('111 ' || '222 ' || '333 ', '444 ' || '555 ' || '666 '); 1 row inserted/updated/deleted ij> select c30, vc30 from t2; C30 |VC30 ------------------------------------------------------------- 111 222 333 |444 555 666 ij> delete from t2; 1 row inserted/updated/deleted ij> -- concatenation on a long varchar create table t3 (c1 long varchar, c2 long varchar); 0 rows inserted/updated/deleted ij> insert into t3 values ('c1 ', 'c2'); 1 row inserted/updated/deleted ij> insert into t2 (c30, vc30) select t3.c1 || t3.c2, t3.c2 || t3.c1 from t3; 1 row inserted/updated/deleted ij> select c30, vc30 from t2; C30 |VC30 ------------------------------------------------------------- c1 c2 |c2c1 ij> delete from t2; 1 row inserted/updated/deleted ij> -- drop the tables drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> drop table t3; 0 rows inserted/updated/deleted ij> ------------------------------------------------------------------------------------ -- CHAR (n) FOR BIT DATA AND VARCHAR (n) FOR BIT DATA -- try some cases zero length cases values X''|| X'80'; 1 -- 80 ij> values X'01'|| X''; 1 -- 01 ij> -- 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); 0 rows inserted/updated/deleted ij> 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); 0 rows inserted/updated/deleted ij> -- populate the tables insert into t1 (b16a) values(null); 1 row inserted/updated/deleted ij> insert into t1 values(X'11', X'22', X'33', X'44', X'55'); 1 row inserted/updated/deleted ij> insert into t1 values(X'1111', X'2222', X'3333', X'4444', X'5555'); 1 row inserted/updated/deleted ij> insert into t1 values(X'5555', X'66', X'7777', X'88', X'9999'); 1 row inserted/updated/deleted ij> -- negative tests -- non-blank truncation error on bit result insert into t2 (b20) select b16a || b16b from t1 where b16a = X'1111'; ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '11112222' to length 3. ij> insert into t2 (vb20) select vb16a || vb16b from t1 where b16a= X'1111'; ERROR 22001: A truncation error was encountered trying to shrink VARCHAR () FOR BIT DATA '33334444' to length 3. ij> -- positive tests -- truncation on bit varying insert into t2 (b20) select vb16a || vb16b from t1 where b16a = X'5555'; 1 row inserted/updated/deleted ij> select b20 from t2; B20 ------ 777788 ij> delete from t2; 1 row inserted/updated/deleted ij> -- bc || b -> vb insert into t2 (b80) select vb16a || b16a from t1 where b16a = X'5555'; 1 row inserted/updated/deleted ij> select b80 from t2; B80 -------------------- 77775555202020202020 ij> delete from t2; 1 row inserted/updated/deleted ij> -- b || vb -> vb insert into t2 (b80) select b16a || vb16a || X'99' from t1 where b16a = X'5555'; 1 row inserted/updated/deleted ij> select b80 from t2; B80 -------------------- 55557777992020202020 ij> delete from t2; 1 row inserted/updated/deleted ij> -- b || lbv -> lbv insert into t2 (lbv) select b16a || lbv from t1 where b16a = X'5555'; 1 row inserted/updated/deleted ij> select lbv from t2; LBV -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 55559999 ij> delete from t2; 1 row inserted/updated/deleted ij> -- lbv || b -> lbv insert into t2 (lbv) select lbv || b16a from t1 where b16a = X'5555'; 1 row inserted/updated/deleted ij> select lbv from t2; LBV -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 99995555 ij> delete from t2; 1 row inserted/updated/deleted ij> -- vb || lbv -> lbv insert into t2 (lbv) select vb16a || lbv from t1 where b16a = X'5555'; 1 row inserted/updated/deleted ij> select lbv from t2; LBV -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 77779999 ij> delete from t2; 1 row inserted/updated/deleted ij> -- lbv || vb -> lbv insert into t2 (lbv) select lbv || vb16a from t1 where b16a = X'5555'; 1 row inserted/updated/deleted ij> select lbv from t2; LBV -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 99997777 ij> delete from t2; 1 row inserted/updated/deleted ij> -- 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; ij> -- ? || b prepare pb as 'select ? || b16a from t1'; ij> execute pb using 'values (X''ABCD'')'; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL abcd1120 abcd1111 abcd5555 ij> -- b || ? prepare bp as 'select b16a || ? from t1'; ij> execute bp using 'values (X''ABCD'')'; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL 1120abcd 1111abcd 5555abcd ij> -- ? || vb prepare pvb as 'select ? || vb16a from t1'; ij> execute pvb using 'values (X''ABCD'')'; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL abcd33 abcd3333 abcd7777 ij> -- vb || ? prepare vbp as 'select vb16a || ? from t1'; ij> execute vbp using 'values (X''ABCD'')'; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL 33abcd 3333abcd 7777abcd ij> -- 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'; ij> execute plbv using 'values (X''ABCD'')'; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL abcd55 abcd5555 abcd9999 ij> -- lbv || ? prepare lbvp as 'select lbv || ? from t1'; ij> execute lbvp using 'values (X''ABCD'')'; 1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL 55abcd 5555abcd 9999abcd ij> autocommit on; ij> -- 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'); 1 row inserted/updated/deleted ij> select b80, vb80, lbv from t2; B80 |VB80 |LBV -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- e0a02020202020202020|1011e0 |1234a020 ij> delete from t2; 1 row inserted/updated/deleted ij> -- concatenation on a byte create table t3 (b1 char(1) for bit data, b2 char(1) for bit data); 0 rows inserted/updated/deleted ij> insert into t3 values (X'11', X'22'); 1 row inserted/updated/deleted ij> insert into t2 (b80, vb80) select t3.b1 || t3.b2, t3.b2 || t3.b1 from t3; 1 row inserted/updated/deleted ij> select b80, vb80 from t2; B80 |VB80 ----------------------------------------- 11222020202020202020|2211 ij> delete from t2; 1 row inserted/updated/deleted ij> -- clean up the prepared statements remove pc; ij> remove cp; ij> remove vp; ij> remove pv; ij> remove pb; ij> remove bp; ij> remove pvb; ij> remove vbp; ij> remove lvp; ij> remove plv; ij> remove plbv; ij> remove lbvp; ij> -- drop the tables drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> drop table t3; 0 rows inserted/updated/deleted ij> -- reset maximumdisplaywidth maximumdisplaywidth 128; ij> -- -- -- 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); 0 rows inserted/updated/deleted ij> insert into t values('hello','world', 'nice day, huh?'); 1 row inserted/updated/deleted ij> insert into t values('goodbye','planet', 'see you later'); 1 row inserted/updated/deleted ij> insert into t values('aloha','orb', 'hang loose'); 1 row inserted/updated/deleted ij> -- subquery on left side select * from t where (select max(c) from t) like '%'; C |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- hello |world |nice day, huh? goodbye |planet |see you later aloha |orb |hang loose ij> select * from t where c like 'h%'; C |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- hello |world |nice day, huh? ij> select * from t where v like '%or%'; C |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- hello |world |nice day, huh? aloha |orb |hang loose ij> select * from t where lvc like '%y%'; C |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- hello |world |nice day, huh? goodbye |planet |see you later ij> -- these four should all have the same results: select * from t where not v like '%or%'; C |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- goodbye |planet |see you later ij> select * from t where not (v like '%or%'); C |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- goodbye |planet |see you later ij> select * from t where 1=0 or not v like '%or%'; C |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- goodbye |planet |see you later ij> select * from t where not (1=0 or not v not like '%or%'); C |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- goodbye |planet |see you later ij> -- these two should have the same results: select * from t where c like '%lo%' or v like '%o%'; C |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- hello |world |nice day, huh? aloha |orb |hang loose ij> select * from t where v like '%o%' or c like '%lo%'; C |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- hello |world |nice day, huh? aloha |orb |hang loose ij> -- these three should have the same results: select * from t where c like '%lo%' and 0=0; C |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- hello |world |nice day, huh? aloha |orb |hang loose ij> select * from t where c like '%lo%' and 1=1; C |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- hello |world |nice day, huh? aloha |orb |hang loose ij> select * from t where 1=1 and c like '%lo%'; C |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- hello |world |nice day, huh? aloha |orb |hang loose ij> -- we can at least show the parameters compile... autocommit off; ij> prepare s as 'select * from t where v like ?'; ij> execute s; ERROR 07000: At least one parameter to the current statement is uninitialized. ij> prepare s as 'select * from t where ? like ?'; ij> execute s; ERROR 07000: At least one parameter to the current statement is uninitialized. ij> prepare s as 'select * from t where c like ?'; ij> execute s; ERROR 07000: At least one parameter to the current statement is uninitialized. ij> prepare s as 'select * from t where lvc like ?'; ij> execute s; ERROR 07000: At least one parameter to the current statement is uninitialized. ij> prepare s as 'select * from t where lvc like ?'; ij> execute s; ERROR 07000: At least one parameter to the current statement is uninitialized. ij> autocommit on; ij> create table n (i int, r real, d date, u char(10)); 0 rows inserted/updated/deleted ij> -- these should succeed insert into n values (1, 1.1, date('1111-11-11'), '%'); 1 row inserted/updated/deleted ij> insert into n values (2, 2.2, date('2222-2-2'), 'haha'); 1 row inserted/updated/deleted ij> select * from n where u like 'haha______'; I |R |D |U ----------------------------------------------- 2 |2.2 |2222-02-02|haha ij> -- now, with an index create table m (i int, r real, d date, u varchar(10)); 0 rows inserted/updated/deleted ij> insert into m select * from n; 2 rows inserted/updated/deleted ij> select * from m where u like 'haha'; I |R |D |U ----------------------------------------------- ij> select * from m where u like 'haha______'; I |R |D |U ----------------------------------------------- 2 |2.2 |2222-02-02|haha ij> create index i1 on m(u); 0 rows inserted/updated/deleted ij> select * from m where u like 'haha'; I |R |D |U ----------------------------------------------- ij> select * from m where u like 'haha______'; I |R |D |U ----------------------------------------------- 2 |2.2 |2222-02-02|haha ij> -- tests for column like constant optimization create table u (c char(10), vc varchar(10)); 0 rows inserted/updated/deleted ij> insert into u values ('hello', 'hello'); 1 row inserted/updated/deleted ij> select * from u where c like 'hello'; C |VC --------------------- ij> select * from u where vc like 'hello'; C |VC --------------------- hello |hello ij> select * from u where c like 'hello '; C |VC --------------------- hello |hello ij> select * from u where vc like 'hello '; C |VC --------------------- ij> -- cleanup drop table t; 0 rows inserted/updated/deleted ij> drop table n; 0 rows inserted/updated/deleted ij> drop table m; 0 rows inserted/updated/deleted ij> drop table u; 0 rows inserted/updated/deleted ij> -- testing JDBC escaped length function -- JDBC length is defined as the number of characters in a string without trailing blanks. values {FN LENGTH('hello ') }; 1 ----------- 5 ij> values {FN LENGTH(rtrim('hello ')) }; 1 ----------- 5 ij> -- defect 5749. rtrim() over substr() used to raise ASSERT failure. create table t1 (c1 char(10)); 0 rows inserted/updated/deleted ij> insert into t1 values ('testing'); 1 row inserted/updated/deleted ij> select rtrim(substr(' asdf', 1, 3)) from t1; 1 ---- as ij>