| 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. |
| -- |
| -- Test various functions |
| |
| create table alltypes( |
| id int not null primary key, |
| smallIntCol smallint, |
| intCol int, |
| bigIntCol bigint, |
| floatCol float, |
| float1Col float(1), |
| float26Col float(26), |
| realCol real, |
| doubleCol double, |
| decimalCol decimal, |
| decimal10Col decimal(10), |
| decimal11Col decimal(11), |
| numeric10d2Col numeric(10,2), |
| charCol char, |
| char32Col char(32), |
| charForBitCol char(16) for bit data, |
| varcharCol varchar(64), |
| varcharForBitCol varchar(64) for bit data, |
| longVarcharCol long varchar, |
| blobCol blob(10k), |
| clobCol clob(10k), |
| dateCol date, |
| timeCol time, |
| timestampCol timestamp); |
| 0 rows inserted/updated/deleted |
| ij> insert into allTypes(id) values(1),(2); |
| 2 rows inserted/updated/deleted |
| ij> update allTypes set smallIntCol = 2 where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set intCol = 2 where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set bigIntCol = 3 where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set floatCol = 4.1 where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set float1Col = 5 where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set float26Col = 6.1234567890123456 where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set realCol = 7.2 where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set doubleCol = 8.2 where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set decimalCol = 9 where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set decimal10Col = 1234 where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set decimal11Col = 1234 where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set numeric10d2Col = 11.12 where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set charCol = 'a' where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set char32Col = 'abc' where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set charForBitCol = X'ABCD' where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set varcharCol = 'abcde' where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set varcharForBitCol = X'ABCDEF' where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set longVarcharCol = 'abcdefg' where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set blobCol = cast( X'0031' as blob(10k)) where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set clobCol = 'clob data' where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set dateCol = date( '2004-3-13') where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set timeCol = time( '16:07:21') where id = 1; |
| 1 row inserted/updated/deleted |
| ij> update allTypes set timestampCol = timestamp( '2004-3-14 17:08:22.123456') where id = 1; |
| 1 row inserted/updated/deleted |
| ij> select id, length(smallIntCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |2 |
| 2 |NULL |
| ij> select id, length(intCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |4 |
| 2 |NULL |
| ij> select id, length(bigIntCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |8 |
| 2 |NULL |
| ij> select id, length(floatCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |8 |
| 2 |NULL |
| ij> select id, length(float1Col) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |4 |
| 2 |NULL |
| ij> select id, length(float26Col) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |8 |
| 2 |NULL |
| ij> select id, length(realCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |4 |
| 2 |NULL |
| ij> select id, length(doubleCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |8 |
| 2 |NULL |
| ij> select id, length(decimalCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |3 |
| 2 |NULL |
| ij> select id, length(decimal10Col) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |6 |
| 2 |NULL |
| ij> select id, length(decimal11Col) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |6 |
| 2 |NULL |
| ij> select id, length(numeric10d2Col) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |6 |
| 2 |NULL |
| ij> select id, length(charCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |1 |
| 2 |NULL |
| ij> select id, length(char32Col) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |32 |
| 2 |NULL |
| ij> select id, length(charForBitCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |16 |
| 2 |NULL |
| ij> select id, length(varcharCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |5 |
| 2 |NULL |
| ij> select id, length(varcharForBitCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |3 |
| 2 |NULL |
| ij> select id, length(longVarcharCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |7 |
| 2 |NULL |
| ij> select id, length(blobCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |2 |
| 2 |NULL |
| ij> select id, length(clobCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |9 |
| 2 |NULL |
| ij> select id, length(dateCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |4 |
| 2 |NULL |
| ij> select id, length(timeCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |3 |
| 2 |NULL |
| ij> select id, length(timestampCol) from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |10 |
| 2 |NULL |
| ij> -- try length of constants |
| values( length( 1), length( 720176), length( 12345678901)); |
| 1 |2 |3 |
| ----------------------------------- |
| 4 |4 |8 |
| ij> values( length( 2.2E-1)); |
| 1 |
| ----------- |
| 8 |
| ij> values( length( 1.), length( 12.3), length( 123.4), length( 123.45)); |
| 1 |2 |3 |4 |
| ----------------------------------------------- |
| 1 |2 |3 |3 |
| ij> values( length( '1'), length( '12')); |
| 1 |2 |
| ----------------------- |
| 1 |2 |
| ij> values( length( X'00'), length( X'FF'), length( X'FFFF')); |
| 1 |2 |3 |
| ----------------------------------- |
| 1 |1 |2 |
| ij> values( length( date('0001-1-1')), length( time('0:00:00')), length( timestamp( '0001-1-1 0:00:00'))); |
| 1 |2 |3 |
| ----------------------------------- |
| 4 |3 |10 |
| ij> -- try a length in the where clause |
| select id from allTypes where length(smallIntCol) > 5 order by id; |
| ID |
| ----------- |
| ij> select id from allTypes where length(intCol) > 5 order by id; |
| ID |
| ----------- |
| ij> select id from allTypes where length(bigIntCol) > 5 order by id; |
| ID |
| ----------- |
| 1 |
| ij> select id from allTypes where length(floatCol) > 5 order by id; |
| ID |
| ----------- |
| 1 |
| ij> select id from allTypes where length(float1Col) > 5 order by id; |
| ID |
| ----------- |
| ij> select id from allTypes where length(float26Col) > 5 order by id; |
| ID |
| ----------- |
| 1 |
| ij> select id from allTypes where length(realCol) > 5 order by id; |
| ID |
| ----------- |
| ij> select id from allTypes where length(doubleCol) > 5 order by id; |
| ID |
| ----------- |
| 1 |
| ij> select id from allTypes where length(decimalCol) > 5 order by id; |
| ID |
| ----------- |
| ij> select id from allTypes where length(decimal10Col) > 5 order by id; |
| ID |
| ----------- |
| 1 |
| ij> select id from allTypes where length(decimal11Col) > 5 order by id; |
| ID |
| ----------- |
| 1 |
| ij> select id from allTypes where length(numeric10d2Col) > 5 order by id; |
| ID |
| ----------- |
| 1 |
| ij> select id from allTypes where length(charCol) > 5 order by id; |
| ID |
| ----------- |
| ij> select id from allTypes where length(char32Col) > 5 order by id; |
| ID |
| ----------- |
| 1 |
| ij> select id from allTypes where length(charForBitCol) > 5 order by id; |
| ID |
| ----------- |
| 1 |
| ij> select id from allTypes where length(varcharCol) > 5 order by id; |
| ID |
| ----------- |
| ij> select id from allTypes where length(varcharForBitCol) > 5 order by id; |
| ID |
| ----------- |
| ij> select id from allTypes where length(longVarcharCol) > 5 order by id; |
| ID |
| ----------- |
| 1 |
| ij> select id from allTypes where length(blobCol) > 5 order by id; |
| ID |
| ----------- |
| ij> select id from allTypes where length(clobCol) > 5 order by id; |
| ID |
| ----------- |
| 1 |
| ij> select id from allTypes where length(dateCol) > 5 order by id; |
| ID |
| ----------- |
| ij> select id from allTypes where length(timeCol) > 5 order by id; |
| ID |
| ----------- |
| ij> select id from allTypes where length(timestampCol) > 5 order by id; |
| ID |
| ----------- |
| 1 |
| ij> -- try an expression |
| select id, length( charCol || 'abc') from allTypes order by id; |
| ID |2 |
| ----------------------- |
| 1 |4 |
| 2 |NULL |
| ij> -- bug 5761 & 5627 |
| -- JDBC escape length function has the following behavior |
| -- LENGTH (RTRIM (xxxx)) |
| values {FN LENGTH('xxxx ')}; |
| 1 |
| ----------- |
| 4 |
| ij> values {FN LENGTH(' xxxx ')}; |
| 1 |
| ----------- |
| 5 |
| ij> values {FN LENGTH(' xxxx ')}; |
| 1 |
| ----------- |
| 6 |
| ij> values {FN LENGTH(' xxxx ')}; |
| 1 |
| ----------- |
| 7 |
| ij> CREATE FUNCTION COUNT_ROWS(P1 VARCHAR(128), P2 VARCHAR(128)) RETURNS INT |
| READS SQL DATA |
| EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.countRows' |
| LANGUAGE JAVA PARAMETER STYLE JAVA; |
| 0 rows inserted/updated/deleted |
| ij> CREATE FUNCTION FN_ABS(P1 INT) RETURNS INT |
| NO SQL |
| RETURNS NULL ON NULL INPUT |
| EXTERNAL NAME 'java.lang.Math.abs' |
| LANGUAGE JAVA PARAMETER STYLE JAVA; |
| 0 rows inserted/updated/deleted |
| ij> select FN_ABS(i) FROM SV_TAB; |
| ERROR 42X05: Table/View 'SV_TAB' does not exist. |
| ij> select COUNT_ROWS(CURRENT SCHEMA, 'SV_TAB') from SV_TAB; |
| ERROR 42X05: Table/View 'SV_TAB' does not exist. |
| ij> select FN_ABS(i), COUNT_ROWS(CURRENT SCHEMA, 'SV_TAB') from SV_TAB; |
| ERROR 42X05: Table/View 'SV_TAB' does not exist. |
| ij> DROP FUNCTION SV_RNNI; |
| ERROR 42Y55: 'DROP FUNCTION' cannot be performed on 'SV_RNNI' because it does not exist. |
| ij> DROP FUNCTION SV_CNI; |
| ERROR 42Y55: 'DROP FUNCTION' cannot be performed on 'SV_CNI' because it does not exist. |
| ij> DROP FUNCTION SV_DEF; |
| ERROR 42Y55: 'DROP FUNCTION' cannot be performed on 'SV_DEF' because it does not exist. |
| ij> DROP FUNCTION MAX_RNNI; |
| ERROR 42Y55: 'DROP FUNCTION' cannot be performed on 'MAX_RNNI' because it does not exist. |
| ij> DROP FUNCTION MAX_CNI; |
| ERROR 42Y55: 'DROP FUNCTION' cannot be performed on 'MAX_CNI' because it does not exist. |
| ij> DROP FUNCTION MAX_DEF; |
| ERROR 42Y55: 'DROP FUNCTION' cannot be performed on 'MAX_DEF' because it does not exist. |
| ij> DROP FUNCTION FN_ABS; |
| 0 rows inserted/updated/deleted |
| ij> DROP FUNCTION COUNT_ROWS; |
| 0 rows inserted/updated/deleted |
| ij> DROP TABLE SV_TAB; |
| ERROR 42Y55: 'DROP TABLE' cannot be performed on 'SV_TAB' because it does not exist. |
| ij> -- function definition without parameter names are valid |
| CREATE FUNCTION NONAME1(INT, INT) RETURNS INT EXTERNAL NAME 'java.lang.Math.max' LANGUAGE JAVA PARAMETER STYLE JAVA; |
| 0 rows inserted/updated/deleted |
| ij> CREATE FUNCTION NONAME2(P1 INT, INT) RETURNS INT EXTERNAL NAME 'java.lang.Math.max' LANGUAGE JAVA PARAMETER STYLE JAVA; |
| 0 rows inserted/updated/deleted |
| ij> VALUES NONAME1(99, -45); |
| 1 |
| ----------- |
| 99 |
| ij> VALUES NONAME2(99, -45); |
| 1 |
| ----------- |
| 99 |
| ij> DROP FUNCTION NONAME1; |
| 0 rows inserted/updated/deleted |
| ij> DROP FUNCTION NONAME2; |
| 0 rows inserted/updated/deleted |
| ij> -- check MODIFIES SQL DATA not allowed with FUNCTION |
| CREATE FUNCTION COUNT_ROWS(P1 VARCHAR(128), P2 VARCHAR(128)) RETURNS INT |
| MODIFIES SQL DATA |
| EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.countRows' |
| LANGUAGE JAVA PARAMETER STYLE JAVA; |
| ERROR 42X01: Syntax error: MODIFIES SQL DATA. |
| 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> CREATE FUNCTION SIGNATURE_BUG_DERBY_258_D(P_VAL INT, P_RADIX INT) RETURNS VARCHAR(20) |
| LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL |
| EXTERNAL NAME 'java.lang.Integer.toString(int, int)'; |
| 0 rows inserted/updated/deleted |
| ij> CREATE FUNCTION SIGNATURE_BUG_DERBY_258_NS(P_VAL INT, P_RADIX INT) RETURNS VARCHAR(20) |
| LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL |
| EXTERNAL NAME 'java.lang.Integer.toString'; |
| 0 rows inserted/updated/deleted |
| ij> CREATE FUNCTION SIGNATURE_BUG_DERBY_258_E() RETURNS VARCHAR(20) |
| LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL |
| EXTERNAL NAME 'java.lang.Integer.toXXString()'; |
| 0 rows inserted/updated/deleted |
| ij> -- these are ok |
| VALUES SIGNATURE_BUG_DERBY_258_NS(2356, 16); |
| 1 |
| -------------------- |
| 934 |
| ij> VALUES SIGNATURE_BUG_DERBY_258_NS(2356, 10); |
| 1 |
| -------------------- |
| 2356 |
| ij> VALUES SIGNATURE_BUG_DERBY_258_NS(2356, 2); |
| 1 |
| -------------------- |
| 100100110100 |
| ij> -- Must resolve as above |
| VALUES SIGNATURE_BUG_DERBY_258_D(2356, 16); |
| 1 |
| -------------------- |
| 934 |
| ij> -- no method to resolve to (with specified signature) |
| VALUES SIGNATURE_BUG_DERBY_258_E(); |
| ERROR 42X50: No method was found that matched the method call java.lang.Integer.toXXString(), tried all combinations of object and primitive types and any possible type conversion for any parameters the method call may have. The method might exist but it is not public and/or static, or the parameter types are not method invocation convertible. |
| ij> DROP FUNCTION SIGNATURE_BUG_DERBY_258_D; |
| 0 rows inserted/updated/deleted |
| ij> DROP FUNCTION SIGNATURE_BUG_DERBY_258_E; |
| 0 rows inserted/updated/deleted |
| ij> DROP FUNCTION SIGNATURE_BUG_DERBY_258_NS; |
| 0 rows inserted/updated/deleted |
| ij> -- SYSFUN functions (unqualifed functions are automatically resolved |
| -- to the in-memory SYSFUN functions if the function does not exist |
| -- in the current schema. |
| |
| -- SYSFUN math functions |
| create table SYSFUN_MATH_TEST (d double); |
| 0 rows inserted/updated/deleted |
| ij> insert into SYSFUN_MATH_TEST values null; |
| 1 row inserted/updated/deleted |
| ij> insert into SYSFUN_MATH_TEST values 0.67; |
| 1 row inserted/updated/deleted |
| ij> insert into SYSFUN_MATH_TEST values 1.34; |
| 1 row inserted/updated/deleted |
| ij> select cast (SYSFUN.ACOS(d) as DECIMAL(6,3)) AS SYSFUN_ACOS FROM SYSFUN_MATH_TEST; |
| SYSFUN_& |
| -------- |
| NULL |
| 0.836 |
| ERROR 22003: The resulting value is outside the range for the data type DOUBLE. |
| ij> CREATE VIEW VSMT AS SELECT SIN(d) sd, PI() pi FROM SYSFUN_MATH_TEST; |
| 0 rows inserted/updated/deleted |
| ij> select cast (sd as DECIMAL(6,3)), cast (pi as DECIMAL(6,3)) from VSMT; |
| 1 |2 |
| ----------------- |
| NULL |3.141 |
| 0.620 |3.141 |
| 0.973 |3.141 |
| ij> drop view VSMT; |
| 0 rows inserted/updated/deleted |
| ij> drop table SYSFUN_MATH_TEST; |
| 0 rows inserted/updated/deleted |
| ij> drop function SYSFUN.ACOS; |
| ERROR 42X62: 'DROP FUNCTION' is not allowed in the 'SYSFUN' schema. |
| ij> |
