Google Git
Sign in
apache / derby / 08cc0f89fe077234078430bf9b93ebc9dfd70ac5 / . / java / testing / org / apache / derbyTesting / functionTests / master / ejbql.out
blob: 8eb49b95252b0269086bc7590d2a3c47a0b441b1 [file] [log] [blame]
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.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn abs( REAL(-0) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn abs( REAL( 1) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn abs( REAL(-1) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn abs( REAL( 1000000.001) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn abs( REAL(-1000000.001) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn abs( REAL( 3.402E+38) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn abs( REAL(-3.402E+38) + 1 )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> -- Error
values{fn abs( REAL( 3.402E+38 * 2) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 2, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn abs(-REAL( NaN) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn abs( REAL( 1.40129846432481707e-45) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn abs( REAL( 3.40282346638528860e+38) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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) )};
1
------------------------
4.9E-324
ij> values{fn abs( DOUBLE( 1.7976931348623157E308) )};
1
------------------------
1.7976931348623157E308
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.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{ fn abs(DEC(-0) )};
ERROR 42X01: Syntax error: Encountered "DEC" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{ fn abs(DEC( 1) )};
ERROR 42X01: Syntax error: Encountered "DEC" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{ fn abs(DEC(-1) )};
ERROR 42X01: Syntax error: Encountered "DEC" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{ fn abs(DEC( 1000000000000) )};
ERROR 42X01: Syntax error: Encountered "DEC" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{ fn abs(DEC(-1000000000000) )};
ERROR 42X01: Syntax error: Encountered "DEC" at line 1, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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.3407796239501852E154
-4.716990566028302E-154
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.3407796239501852E154 |-1.844451138023282E19
-4.716990566028302E-154 |-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.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> insert into foo values ( sqrt(FLOAT( 2)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> insert into foo values (-sqrt(FLOAT( 3)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> insert into foo values (-sqrt(FLOAT( 4)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> insert into foo values ( (FLOAT(-5)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> -- this insert should fail
insert into foo values ( sqrt(FLOAT(-3)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 2, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> insert into foo values (-sqrt(FLOAT( 7)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> insert into foo values (-sqrt(FLOAT( 8)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> insert into foo values ( sqrt(FLOAT( 9)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> insert into foo values ( (FLOAT(10)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> insert into foo values (-sqrt(FLOAT( 12)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> insert into foo values (-sqrt(FLOAT( 13)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> insert into foo values (-sqrt(FLOAT( 14)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> insert into foo values ( (FLOAT( 15)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> -- these 2 inserts should fail
insert into foo values (-sqrt(FLOAT(-12)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 2, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> insert into foo values ( sqrt(FLOAT(-13)));
ERROR 42X01: Syntax error: Encountered "FLOAT" at line 1, column 31.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn sqrt( REAL(-0) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn sqrt( REAL( 1) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn sqrt( REAL(-1) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn sqrt( REAL( 1000000.001) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn sqrt( REAL(-1000000.001) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn sqrt( REAL( 3.402E+38) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn sqrt( REAL(-3.402E+38) + 1 )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> -- Error
values{fn sqrt( REAL( 3.402E+38 * 2) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 2, column 17.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn sqrt(-REAL( NaN) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn sqrt( REAL( 1.40129846432481707e-45) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn sqrt( REAL( 3.40282346638528860e+38) )};
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 17.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values{fn -sqrt( DOUBLE(1.79769E+308) )};
ERROR 42X01: Syntax error: Encountered "-" at line 1, column 11.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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) )};
1
------------------------
2.2227587494850775E-162
ij> values{fn sqrt( DOUBLE( 1.7976931348623157E308) )};
1
------------------------
1.3407807929942596E154
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.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> execute p1 using 'values -sqrt(REAL( 20))';
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 14.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> execute p1 using 'values sqrt(REAL( 20))';
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 14.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> -- this should fail
execute p1 using 'values sqrt(REAL(-20))';
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 14.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> execute p2 using 'values {fn -sqrt(REAL( 20))}';
ERROR 42X01: Syntax error: Encountered "-" at line 1, column 12.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> execute p2 using 'values {fn sqrt (REAL( 20))}';
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 18.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> -- this should fail
execute p2 using 'values {fn sqrt(REAL(-20))}';
ERROR 42X01: Syntax error: Encountered "REAL" at line 1, column 18.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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>