| >>sh rm -f LOG001-SECONDARY; |
| >>obey TEST001(compile_libs); |
| >>-------------------------------------------------------------------------- |
| >>log; |
| >> |
| >>obey TEST001(java_compile); |
| >>-------------------------------------------------------------------------- |
| >>-- To compile Java code we invoke a script from regress/tools that uses |
| >>-- environment variables to determine the source and target directories |
| >>-------------------------------------------------------------------------- |
| >>log; |
| ------------------------------------------------------------------------------ |
| -- Compiling Java source files: TEST001_Sessionize.java |
| -- Executing: $javac -d $REGRRUNDIR $REGRTSTDIR/TEST001_Sessionize.java |
| -- $javac returned 0 |
| ------------------------------------------------------------------------------ |
| ------------------------------------------------------------------------------ |
| -- Compiling Java source files: TEST001_Fibonacci.java |
| -- Executing: $javac -d $REGRRUNDIR $REGRTSTDIR/TEST001_Fibonacci.java |
| -- $javac returned 0 |
| ------------------------------------------------------------------------------ |
| ------------------------------------------------------------------------------ |
| -- Archiving Java class files: |
| -- TEST001_Sessionize.class |
| -- TEST001_Sessionize$InternalColumns.class |
| -- TEST001_Fibonacci.class |
| -- Archive will be written to: TEST001.jar |
| -- Executing: $jar cMf TEST001.jar TEST001_Sessionize.class TEST001_Sessionize$InternalColumns.class TEST001_Fibonacci.class |
| -- $jar returned 0 |
| ------------------------------------------------------------------------------ |
| >> |
| >>obey TEST001(create_tables); |
| >>-------------------------------------------------------------------------- |
| >>create table clicks (userid char(32), ts TIME(6), ipAddr char(15)) ; |
| |
| --- SQL operation complete. |
| >>insert into clicks values |
| +> ('super-user',cast(time'09:59:59.50 pm' as TIME(6)),'12.345.567.345'), |
| +> ('super-user',cast(time'11:59:59.50 pm' as TIME(6)),'12.345.567.345'), |
| +> ('super-services',cast(time'11:59:59.50 pm' as TIME(6)),'12.345.567.345'), |
| +> ('super-services',cast(time'11:59:59.55 pm' as TIME(6)),'12.345.567.345'); |
| |
| --- 4 row(s) inserted. |
| >> |
| >>-- for now use VARCHARs for LOBs |
| >>--cqd TRAF_BLOB_AS_VARCHAR 'OFF'; |
| >> |
| >>create table t001_Datatypes ( |
| +>c_char char(15), |
| +>c_char_upshift char(15) upshift, |
| +>c_char_not_casespecific char(15) not casespecific, |
| +>c_char_varying char varying(15), |
| +>c_char_varying_upshift char varying(15) upshift, |
| +>c_char_varying_not_casespecific char varying(15) not casespecific, |
| +>c_varchar varchar(15), |
| +>c_varchar_upshift varchar(15) upshift, |
| +>c_varchar_not_casespecific varchar(15) not casespecific, |
| +>c_nchar nchar(15), |
| +>c_nchar_upshift nchar(15) upshift, |
| +>c_nchar_not_casespecific nchar(15) not casespecific, |
| +>c_nchar_varying nchar varying(15), |
| +>c_nchar_varying_upshift nchar varying(15) upshift, |
| +>c_nchar_varying_not_casespecific nchar varying(15) not casespecific, |
| +>c_numeric numeric(9,2), |
| +>c_numeric_unsigned numeric(9,2) unsigned, |
| +>c_decimal decimal(9,2), |
| +>c_decimal_unsigned decimal(9,2) unsigned, |
| +>c_integer integer, |
| +>c_integer_unsigned integer unsigned, |
| +>c_largeint largeint, |
| +>c_smallint smallint, |
| +>c_smallint_unsigned smallint unsigned, |
| +>c_tinyint tinyint, |
| +>c_tinyint_unsigned tinyint unsigned, |
| +>c_float float(10), |
| +>c_real real, |
| +>c_double_precision double precision, |
| +>c_date date, |
| +>c_time time, |
| +>c_time6 time(6), |
| +>c_timestamp0 timestamp(0), |
| +>c_timestamp timestamp, |
| +>c_timestamp6 timestamp(6), |
| +>c_interval interval year to month, |
| +>c_intervals86 interval second(8,6), |
| +>c_intervald6s interval day(6) to second(6), |
| +>c_blob blob (100), |
| +>c_clob clob (100), |
| +>c_boolean boolean |
| +>); |
| |
| --- SQL operation complete. |
| >> |
| >>insert into t001_Datatypes values ( |
| +>'CHAR_1', |
| +>'char_1', |
| +>'char_1', |
| +>'CHARVAR_1', |
| +>'charvar_1', |
| +>'charvar_1', |
| +>'VARCHAR_1', |
| +>'varchar_1', |
| +>'varchar_1', |
| +>'NCHAR_1', |
| +>'nchar_1', |
| +>'nchar_1', |
| +>'NCHARVAR_1', |
| +>'ncharvar_1', |
| +>'ncharvar_1', |
| +>-1, |
| +>1, |
| +>-1.11, |
| +>1.11, |
| +>-1, |
| +>1, |
| +>-1, |
| +>-1, |
| +>1, |
| +>-1, |
| +>1, |
| +>-1.11, |
| +>-1.11, |
| +>-1.11, |
| +>date '2001-01-01', |
| +>time '01:01:01', |
| +>time '01:01:01.111111', |
| +>timestamp '2001-01-01 01:01:01', |
| +>timestamp '2001-01-01 01:01:01.111111', |
| +>timestamp '2001-01-01 01:01:01.111111', |
| +>interval '01-01' year to month, |
| +>interval '88888888.666666' second(8,6), |
| +>interval '666666 23:59:59.999999' day(6) to second(6), |
| +>-- use these when real LOBs are enabled in this test |
| +>--stringtolob('BLOB_1'), |
| +>--stringtolob('CLOB_1') |
| +>-- for now, use simple chars, see cqd TRAF_BLOB_AS_VARCHAR above |
| +>'BLOB_1', |
| +>'CLOB_1', |
| +>true |
| +>); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>obey TEST001(register_functions); |
| >>-------------------------------------------------------------------------- |
| >> |
| >>create library TEST001 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$; |
| |
| --- SQL operation complete. |
| >> |
| >>-- Sessionize uses a compiler interface to |
| >>-- create result columns that match those of any input table given |
| >>-- and it validates the column name for session id at compile time |
| >>create table_mapping function sessionize_dynamic(user_colname char(10), |
| +> ts_colname char(20), |
| +> timeintval int) |
| +>external name 'SESSIONIZE_DYNAMIC' |
| +>library TEST001; |
| |
| --- SQL operation complete. |
| >> |
| >>-- This shows that we can share the same binaries for multiple |
| >>-- TMUDFs, note that it uses the same external name SESSIONIZE_DYNAMIC |
| >>create table_mapping function sessionize_dynamic_shared(user_colname char(10), |
| +> ts_colname char(10), |
| +> timeintval int) |
| +>external name 'SESSIONIZE_DYNAMIC' |
| +>language cpp |
| +>library TEST001; |
| |
| --- SQL operation complete. |
| >> |
| >>-- create the equivalent Java library and TMUDF |
| >>create library TEST001_Java file $$QUOTE$$ $$REGRRUNDIR$$/TEST001.jar $$QUOTE$$; |
| |
| --- SQL operation complete. |
| >> |
| >>create table_mapping function sessionize_java(user_colname char(10), |
| +> ts_colname char(20), |
| +> timeintval int) |
| +>external name 'TEST001_Sessionize' |
| +>language java |
| +>library TEST001_Java; |
| |
| --- SQL operation complete. |
| >> |
| >>-- negative test case, the entry point SESSIONIZE_ERR does not exist |
| >>create table_mapping function sessionize_err(dummy char(10)) |
| +>returns (session_id largeint) |
| +>external name 'SESSIONIZE_NON_EXISTENT' |
| +>language cpp |
| +>library TEST001; |
| |
| *** ERROR[11246] An error occurred locating function or class 'SESSIONIZE_NON_EXISTENT' in library 'TEST001.dll'. |
| |
| *** ERROR[11248] A call to dlsym returned errors 0 and 0. Details: |
| /mnt2/ansharma/ansharma_bool/incubator-trafodion/core/sqf/rundir/udr/TEST001.dll: undefined symbol: SESSIONIZE_NON_EXISTENT. |
| |
| --- SQL operation failed with errors. |
| >>-- For now this will succeed, since we don't load the library during |
| >>-- DDL time. We will get an error at runtime, though. |
| >> |
| >>-- Testing a TMUDF with no table-valued inputs |
| >>create table_mapping function "Fibonacci"(start_row int, num_rows int) |
| +>returns (ordinal int, fibonacci_number largeint) |
| +>external name 'Fibonacci' |
| +>language cpp |
| +>library TEST001; |
| |
| --- SQL operation complete. |
| >> |
| >>-- same in Java |
| >>create table_mapping function fibonacci_java(start_row int, num_rows int) |
| +>returns (ordinal int, fibonacci_number largeint) |
| +>external name 'TEST001_Fibonacci' |
| +>language java |
| +>library TEST001_Java; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST001(tests); |
| >>-------------------------------------------------------------------------- |
| >>--cqd attempt_esp_parallelism 'off' ; |
| >> |
| >>get table_mapping functions for library TEST001 ; |
| |
| Table_mapping Functions for Library SCH.TEST001 |
| =============================================== |
| |
| SCH.Fibonacci |
| SCH.SESSIONIZE_DYNAMIC |
| SCH.SESSIONIZE_DYNAMIC_SHARED |
| |
| ======================= |
| 3 row(s) returned |
| |
| --- SQL operation complete. |
| >> |
| >>showddl table_mapping function sessionize_dynamic; |
| |
| CREATE TABLE_MAPPING FUNCTION TRAFODION.SCH.SESSIONIZE_DYNAMIC |
| ( |
| IN USER_COLNAME CHAR(10) CHARACTER SET ISO88591 |
| , IN TS_COLNAME CHAR(20) CHARACTER SET ISO88591 |
| , IN TIMEINTVAL INTEGER SIGNED |
| ) |
| EXTERNAL NAME 'SESSIONIZE_DYNAMIC' |
| LIBRARY TRAFODION.SCH.TEST001 |
| LANGUAGE CPP |
| NO SQL |
| SAFE EXECUTION MODE |
| ; |
| |
| --- SQL operation complete. |
| >> |
| >>get table_mapping functions for library TEST001_Java ; |
| |
| Table_mapping Functions for Library SCH.TEST001_JAVA |
| ==================================================== |
| |
| SCH.FIBONACCI_JAVA |
| SCH.SESSIONIZE_JAVA |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- SQL operation complete. |
| >> |
| >>showddl table_mapping function sessionize_java; |
| |
| CREATE TABLE_MAPPING FUNCTION TRAFODION.SCH.SESSIONIZE_JAVA |
| ( |
| IN USER_COLNAME CHAR(10) CHARACTER SET ISO88591 |
| , IN TS_COLNAME CHAR(20) CHARACTER SET ISO88591 |
| , IN TIMEINTVAL INTEGER |
| ) |
| EXTERNAL NAME 'TEST001_Sessionize' |
| LIBRARY TRAFODION.SCH.TEST001_JAVA |
| LANGUAGE JAVA |
| NO SQL |
| SAFE EXECUTION MODE |
| ; |
| |
| --- SQL operation complete. |
| >> |
| >>SELECT cast(CONVERTTIMESTAMP(ts) as TIME(6)), userid, session_id, ipAddr |
| +>FROM UDF(sessionize_dynamic(TABLE(SELECT userid, |
| +> JULIANTIMESTAMP(ts) as TS, |
| +> ipAddr |
| +> FROM clicks |
| +> PARTITION BY 1 ORDER BY 2), |
| +> 'USERID', |
| +> 'TS', |
| +> 60000000)) |
| +>ORDER BY 2, 1, 3; |
| |
| (EXPR) USERID SESSION_ID IPADDR |
| --------------- -------------------------------- -------------------- --------------- |
| |
| 23:59:59.500000 super-services 1 12.345.567.345 |
| 23:59:59.550000 super-services 1 12.345.567.345 |
| 21:59:59.500000 super-user 1 12.345.567.345 |
| 23:59:59.500000 super-user 2 12.345.567.345 |
| |
| --- 4 row(s) selected. |
| >> |
| >>SELECT cast(CONVERTTIMESTAMP(ts) as TIME(6)), userid, session_id, ipAddr |
| +>FROM UDF(sessionize_dynamic(TABLE(SELECT userid, |
| +> JULIANTIMESTAMP(ts) as TS, |
| +> ipAddr |
| +> FROM clicks |
| +> WHERE userid='super-user' |
| +> PARTITION BY 1 ORDER BY 2), |
| +> 'USERID', |
| +> 'TS', |
| +> 60000000)) |
| +>ORDER BY 2, 1, 3; |
| |
| (EXPR) USERID SESSION_ID IPADDR |
| --------------- -------------------------------- -------------------- --------------- |
| |
| 21:59:59.500000 super-user 1 12.345.567.345 |
| 23:59:59.500000 super-user 2 12.345.567.345 |
| |
| --- 2 row(s) selected. |
| >>SELECT cast(CONVERTTIMESTAMP(ts) as TIME(6)), userid, session_id, ipAddr |
| +>FROM UDF(sessionize_dynamic(TABLE(SELECT userid, |
| +> JULIANTIMESTAMP(ts) as TS, |
| +> ipAddr |
| +> FROM clicks |
| +> WHERE userid='super-user' |
| +> PARTITION BY 1 ORDER BY 2), |
| +> 'USERID', |
| +> 'TS', |
| +> 60000000)) |
| +>ORDER BY 2, 1, 3; |
| |
| (EXPR) USERID SESSION_ID IPADDR |
| --------------- -------------------------------- -------------------- --------------- |
| |
| 21:59:59.500000 super-user 1 12.345.567.345 |
| 23:59:59.500000 super-user 2 12.345.567.345 |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- call sessionize_dynamic_shared, sharing the same DLL |
| >>SELECT * |
| +>FROM UDF(sessionize_dynamic_shared(TABLE(SELECT userid, |
| +> JULIANTIMESTAMP(ts) as TS, |
| +> ipAddr |
| +> FROM clicks |
| +> PARTITION BY ipaddr ORDER BY ts), |
| +> 'IPADDR', |
| +> cast('TS' as char(2)), |
| +> 60000000)) XO |
| +>ORDER BY ipaddr, session_id, sequence_no; |
| |
| SESSION_ID SEQUENCE_NO USERID TS IPADDR |
| -------------------- -------------------- -------------------------------- -------------------- --------------- |
| |
| 1 1 super-user 212365360799500000 12.345.567.345 |
| 2 1 super-user 212365367999500000 12.345.567.345 |
| 2 2 super-services 212365367999500000 12.345.567.345 |
| 2 3 super-services 212365367999550000 12.345.567.345 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- uniqueness constraint avoids a groupby |
| >>explain options 'f' |
| +>SELECT distinct ipaddr, session_id, sequence_no |
| +>FROM UDF(sessionize_dynamic(TABLE(SELECT userid, |
| +> JULIANTIMESTAMP(ts) as TS, |
| +> ipAddr |
| +> FROM clicks |
| +> PARTITION BY ipaddr ORDER BY ts), |
| +> 'IPADDR', |
| +> cast('TS' as char(2)), |
| +> 60000000)) XO |
| +>where session_id < 10; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 5 . 6 root 1.79E+001 |
| 4 . 5 esp_exchange 1:4(hash2) 5.00E+001 |
| 3 . 4 tmudf XO 5.00E+001 |
| 2 . 3 sort 1.00E+002 |
| 1 . 2 esp_exchange 4(hash2):1 1.00E+002 |
| . . 1 trafodion_scan CLICKS 1.00E+002 |
| |
| --- SQL operation complete. |
| >> |
| >>control query shape tmudf(sort(scan)); |
| |
| --- SQL operation complete. |
| >>-- predicate on IPADDR is evaluated in child, |
| >>-- predicate on SESSION_ID is evaluated in the UDF |
| >>prepare s from |
| +>SELECT * |
| +>FROM UDF(sessionize_dynamic(TABLE(SELECT userid, |
| +> JULIANTIMESTAMP(ts) as TS, |
| +> ipAddr |
| +> FROM clicks |
| +> PARTITION BY ipaddr ORDER BY ts), |
| +> 'IPADDR', |
| +> cast('TS' as char(2)), |
| +> 60000000)) XO |
| +>where SESSION_ID < 2 and |
| +> IPADDR = '12.345.567.345'; |
| |
| --- SQL command prepared. |
| >>control query shape anything; |
| |
| --- SQL operation complete. |
| >>select count(*) from table(explain(null,'S')) |
| +>where operator = 'TMUDF' |
| +> and description like '% preds_evaluated_by_udf: (SESSION_ID < 2) %'; |
| |
| (EXPR) |
| -------------------- |
| |
| 1 |
| |
| --- 1 row(s) selected. |
| >>execute s; |
| |
| SESSION_ID SEQUENCE_NO USERID TS IPADDR |
| -------------------- -------------------- -------------------------------- -------------------- --------------- |
| |
| 1 1 super-user 212334861599500000 12.345.567.345 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- now test Java UDFs in a similar way |
| >>SELECT cast(CONVERTTIMESTAMP(ts) as TIME(6)), userid, session_id, ipAddr |
| +>FROM UDF(sessionize_java(TABLE(SELECT userid, |
| +> JULIANTIMESTAMP(ts) as TS, |
| +> ipAddr |
| +> FROM clicks |
| +> PARTITION BY 1 ORDER BY 2), |
| +> 'USERID', |
| +> 'TS', |
| +> 60000000)) |
| +>ORDER BY 2, 1, 3; |
| |
| (EXPR) USERID SESSION_ID IPADDR |
| --------------- -------------------------------- -------------------- --------------- |
| |
| 23:59:59.500000 super-services 1 12.345.567.345 |
| 23:59:59.550000 super-services 1 12.345.567.345 |
| 21:59:59.500000 super-user 1 12.345.567.345 |
| 23:59:59.500000 super-user 2 12.345.567.345 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- uniqueness constraint avoids a groupby |
| >>prepare s from |
| +>SELECT distinct ipaddr, session_id, sequence_no |
| +>FROM UDF(sessionize_java(TABLE(SELECT userid, |
| +> JULIANTIMESTAMP(ts) as TS, |
| +> ipAddr |
| +> FROM clicks |
| +> PARTITION BY ipaddr ORDER BY ts), |
| +> 'IPADDR', |
| +> cast('TS' as char(2)), |
| +> 60000000)) XO |
| +>where session_id < 10 |
| +>ORDER BY 2, 1, 3; |
| |
| --- SQL command prepared. |
| >>explain options 'f' s; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 6 . 7 root 1.79E+001 |
| 5 . 6 esp_exchange 1:4(hash2) (m) 5.00E+001 |
| 4 . 5 sort 5.00E+001 |
| 3 . 4 tmudf XO 5.00E+001 |
| 2 . 3 sort 1.00E+002 |
| 1 . 2 esp_exchange 4(hash2):1 1.00E+002 |
| . . 1 trafodion_scan CLICKS 1.00E+002 |
| |
| --- SQL operation complete. |
| >>execute s; |
| |
| IPADDR SESSION_ID SEQUENCE_NO |
| --------------- -------------------- -------------------- |
| |
| 12.345.567.345 1 1 |
| 12.345.567.345 2 1 |
| 12.345.567.345 2 2 |
| 12.345.567.345 2 3 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- predicate on IPADDR is evaluated in child, |
| >>-- predicate on SESSION_ID is evaluated in the UDF |
| >>prepare s from |
| +>SELECT * |
| +>FROM UDF(sessionize_java(TABLE(SELECT userid, |
| +> JULIANTIMESTAMP(ts) as TS, |
| +> ipAddr |
| +> FROM clicks |
| +> PARTITION BY ipaddr ORDER BY ts), |
| +> 'IPADDR', |
| +> cast('TS' as char(2)), |
| +> 60000000)) XO |
| +>where SESSION_ID < 2 and |
| +> IPADDR = '12.345.567.345'; |
| |
| --- SQL command prepared. |
| >>select count(*) from table(explain(null,'S')) |
| +>where operator = 'TMUDF' |
| +> and description like '% preds_evaluated_by_udf: (SESSION_ID < 2) %'; |
| |
| (EXPR) |
| -------------------- |
| |
| 1 |
| |
| --- 1 row(s) selected. |
| >>execute s; |
| |
| SESSION_ID SEQUENCE_NO USERID TS IPADDR |
| -------------------- -------------------- -------------------------------- -------------------- --------------- |
| |
| 1 1 super-user 212373741599500000 12.345.567.345 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- eliminate all columns |
| >>prepare s from |
| +>SELECT COUNT(*) |
| +>FROM UDF(sessionize_java(TABLE(SELECT userid, |
| +> JULIANTIMESTAMP(ts) as TS, |
| +> ipAddr |
| +> FROM clicks |
| +> PARTITION BY ipaddr ORDER BY ts), |
| +> 'IPADDR', |
| +> cast('TS' as char(2)), |
| +> 60000000)) XO; |
| |
| --- SQL command prepared. |
| >>execute s; |
| |
| (EXPR) |
| -------------------- |
| |
| 4 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- will fail until tinyint support is added for spj/procedures |
| >>cqd traf_tinyint_spj_support 'ON'; |
| |
| --- SQL operation complete. |
| >>select * |
| +>from UDF("Fibonacci"(1,10)) XO |
| +>order by 1; |
| |
| ORDINAL FIBONACCI_NUMBER |
| ----------- -------------------- |
| |
| 1 1 |
| 2 1 |
| 3 2 |
| 4 3 |
| 5 5 |
| 6 8 |
| 7 13 |
| 8 21 |
| 9 34 |
| 10 55 |
| |
| --- 10 row(s) selected. |
| >>cqd traf_tinyint_spj_support reset; |
| |
| --- SQL operation complete. |
| >> |
| >>select * |
| +>from UDF("Fibonacci"(1,10)) XO |
| +>order by 1; |
| |
| ORDINAL FIBONACCI_NUMBER |
| ----------- -------------------- |
| |
| 1 1 |
| 2 1 |
| 3 2 |
| 4 3 |
| 5 5 |
| 6 8 |
| 7 13 |
| 8 21 |
| 9 34 |
| 10 55 |
| |
| --- 10 row(s) selected. |
| >> |
| >>select sum(fibonacci_number) |
| +>from UDF("Fibonacci"(50,10)) XO; |
| |
| (EXPR) |
| -------------------- |
| |
| 2484365770887 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select * |
| +>from UDF(fibonacci_java(1,10)) XO |
| +>order by 1; |
| |
| ORDINAL FIBONACCI_NUMBER |
| ----------- -------------------- |
| |
| 1 1 |
| 2 1 |
| 3 2 |
| 4 3 |
| 5 5 |
| 6 8 |
| 7 13 |
| 8 21 |
| 9 34 |
| 10 55 |
| |
| --- 10 row(s) selected. |
| >> |
| >>select sum(fibonacci_number) |
| +>from UDF(fibonacci_java(50,10)) XO; |
| |
| (EXPR) |
| -------------------- |
| |
| 2484365770887 |
| |
| --- 1 row(s) selected. |
| >> |
| >>control query shape join(tmudf, tmudf); |
| |
| --- SQL operation complete. |
| >>select * |
| +>from UDF("Fibonacci"(1,10)) natural join UDF(fibonacci_java(1,10)); |
| |
| ORDINAL FIBONACCI_NUMBER |
| ----------- -------------------- |
| |
| 1 1 |
| 2 1 |
| 3 2 |
| 4 3 |
| 5 5 |
| 6 8 |
| 7 13 |
| 8 21 |
| 9 34 |
| 10 55 |
| |
| --- 10 row(s) selected. |
| >>control query shape off; |
| |
| --- SQL operation complete. |
| >> |
| >>select * |
| +>from UDF("Fibonacci"(1,10)) cpp1 natural join |
| +> UDF(fibonacci_java(1,10)) java1 natural join |
| +> UDF(fibonacci_java(1,10)) java2; |
| |
| ORDINAL FIBONACCI_NUMBER |
| ----------- -------------------- |
| |
| 1 1 |
| 2 1 |
| 3 2 |
| 4 3 |
| 5 5 |
| 6 8 |
| 7 13 |
| 8 21 |
| 9 34 |
| 10 55 |
| |
| --- 10 row(s) selected. |
| >> |
| >>prepare s from |
| +>select * from udf(sessionize_dynamic(table(select * from t001_Datatypes), |
| +> 'C_VARCHAR', 'C_DECIMAL_UNSIGNED', 60)); |
| |
| --- SQL command prepared. |
| >>execute s; |
| |
| SESSION_ID SEQUENCE_NO C_CHAR C_CHAR_UPSHIFT C_CHAR_NOT_CASESPECIFIC C_CHAR_VARYING C_CHAR_VARYING_UPSHIFT C_CHAR_VARYING_NOT_CASESPECIFIC C_VARCHAR C_VARCHAR_UPSHIFT C_VARCHAR_NOT_CASESPECIFIC C_NCHAR C_NCHAR_UPSHIFT C_NCHAR_NOT_CASESPECIFIC C_NCHAR_VARYING C_NCHAR_VARYING_UPSHIFT C_NCHAR_VARYING_NOT_CASESPECIFIC C_NUMERIC C_NUMERIC_UNSIGNED C_DECIMAL C_DECIMAL_UNSIGNED C_INTEGER C_INTEGER_UNSIGNED C_LARGEINT C_SMALLINT C_SMALLINT_UNSIGNED C_TINYINT C_TINYINT_UNSIGNED C_FLOAT C_REAL C_DOUBLE_PRECISION C_DATE C_TIME C_TIME6 C_TIMESTAMP0 C_TIMESTAMP C_TIMESTAMP6 C_INTERVAL C_INTERVALS86 C_INTERVALD6S C_BLOB C_CLOB C_BOOLEAN |
| -------------------- -------------------- --------------- --------------- ----------------------- --------------- ---------------------- ------------------------------- --------------- ----------------- -------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------- ------------ ------------------ ----------- ------------------ ----------- ------------------ -------------------- ---------- ------------------- --------- ------------------ ------------------------- --------------- ------------------------- ---------- -------- --------------- ------------------- -------------------------- -------------------------- ---------- ---------------- ----------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- --------- |
| |
| 1 1 CHAR_1 CHAR_1 char_1 CHARVAR_1 CHARVAR_1 charvar_1 VARCHAR_1 VARCHAR_1 varchar_1 NCHAR_1 NCHAR_1 nchar_1 NCHARVAR_1 NCHARVAR_1 ncharvar_1 -1.00 1.00 -1.11 1.11 -1 1 -1 -1 1 -1 1 -1.11000000000000016E+000 -1.1100000E+000 -1.11000000000000016E+000 2001-01-01 01:01:01 01:01:01.111111 2001-01-01 01:01:01 2001-01-01 01:01:01.111111 2001-01-01 01:01:01.111111 1-01 88888888.666666 666666 23:59:59.999999 BLOB_1 CLOB_1 TRUE |
| |
| --- 1 row(s) selected. |
| >>execute s; |
| |
| SESSION_ID SEQUENCE_NO C_CHAR C_CHAR_UPSHIFT C_CHAR_NOT_CASESPECIFIC C_CHAR_VARYING C_CHAR_VARYING_UPSHIFT C_CHAR_VARYING_NOT_CASESPECIFIC C_VARCHAR C_VARCHAR_UPSHIFT C_VARCHAR_NOT_CASESPECIFIC C_NCHAR C_NCHAR_UPSHIFT C_NCHAR_NOT_CASESPECIFIC C_NCHAR_VARYING C_NCHAR_VARYING_UPSHIFT C_NCHAR_VARYING_NOT_CASESPECIFIC C_NUMERIC C_NUMERIC_UNSIGNED C_DECIMAL C_DECIMAL_UNSIGNED C_INTEGER C_INTEGER_UNSIGNED C_LARGEINT C_SMALLINT C_SMALLINT_UNSIGNED C_TINYINT C_TINYINT_UNSIGNED C_FLOAT C_REAL C_DOUBLE_PRECISION C_DATE C_TIME C_TIME6 C_TIMESTAMP0 C_TIMESTAMP C_TIMESTAMP6 C_INTERVAL C_INTERVALS86 C_INTERVALD6S C_BLOB C_CLOB C_BOOLEAN |
| -------------------- -------------------- --------------- --------------- ----------------------- --------------- ---------------------- ------------------------------- --------------- ----------------- -------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------- ------------ ------------------ ----------- ------------------ ----------- ------------------ -------------------- ---------- ------------------- --------- ------------------ ------------------------- --------------- ------------------------- ---------- -------- --------------- ------------------- -------------------------- -------------------------- ---------- ---------------- ----------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- --------- |
| |
| 1 1 CHAR_1 CHAR_1 char_1 CHARVAR_1 CHARVAR_1 charvar_1 VARCHAR_1 VARCHAR_1 varchar_1 NCHAR_1 NCHAR_1 nchar_1 NCHARVAR_1 NCHARVAR_1 ncharvar_1 -1.00 1.00 -1.11 1.11 -1 1 -1 -1 1 -1 1 -1.11000000000000016E+000 -1.1100000E+000 -1.11000000000000016E+000 2001-01-01 01:01:01 01:01:01.111111 2001-01-01 01:01:01 2001-01-01 01:01:01.111111 2001-01-01 01:01:01.111111 1-01 88888888.666666 666666 23:59:59.999999 BLOB_1 CLOB_1 TRUE |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare s from |
| +>select * from udf(sessionize_java(table(select *, cast(? as int) |
| +> from t001_Datatypes), |
| +> 'C_VARCHAR', 'C_DECIMAL_UNSIGNED', 60)); |
| |
| --- SQL command prepared. |
| >>execute s using 123; |
| |
| SESSION_ID SEQUENCE_NO C_CHAR C_CHAR_UPSHIFT C_CHAR_NOT_CASESPECIFIC C_CHAR_VARYING C_CHAR_VARYING_UPSHIFT C_CHAR_VARYING_NOT_CASESPECIFIC C_VARCHAR C_VARCHAR_UPSHIFT C_VARCHAR_NOT_CASESPECIFIC C_NCHAR C_NCHAR_UPSHIFT C_NCHAR_NOT_CASESPECIFIC C_NCHAR_VARYING C_NCHAR_VARYING_UPSHIFT C_NCHAR_VARYING_NOT_CASESPECIFIC C_NUMERIC C_NUMERIC_UNSIGNED C_DECIMAL C_DECIMAL_UNSIGNED C_INTEGER C_INTEGER_UNSIGNED C_LARGEINT C_SMALLINT C_SMALLINT_UNSIGNED C_TINYINT C_TINYINT_UNSIGNED C_FLOAT C_REAL C_DOUBLE_PRECISION C_DATE C_TIME C_TIME6 C_TIMESTAMP0 C_TIMESTAMP C_TIMESTAMP6 C_INTERVAL C_INTERVALS86 C_INTERVALD6S C_BLOB C_CLOB C_BOOLEAN (EXPR) |
| -------------------- -------------------- --------------- --------------- ----------------------- --------------- ---------------------- ------------------------------- --------------- ----------------- -------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------- ------------ ------------------ ----------- ------------------ ----------- ------------------ -------------------- ---------- ------------------- --------- ------------------ ------------------------- --------------- ------------------------- ---------- -------- --------------- ------------------- -------------------------- -------------------------- ---------- ---------------- ----------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- --------- ----------- |
| |
| 1 1 CHAR_1 CHAR_1 char_1 CHARVAR_1 CHARVAR_1 charvar_1 VARCHAR_1 VARCHAR_1 varchar_1 NCHAR_1 NCHAR_1 nchar_1 NCHARVAR_1 NCHARVAR_1 ncharvar_1 -1.00 1.00 -1.11 1.11 -1 1 -1 -1 1 -1 1 -1.11000000000000016E+000 -1.1100000E+000 -1.11000000000000016E+000 2001-01-01 01:01:01 01:01:01.111111 2001-01-01 01:01:01 2001-01-01 01:01:01.111111 2001-01-01 01:01:01.111111 1-01 88888888.666666 666666 23:59:59.999999 BLOB_1 CLOB_1 TRUE 123 |
| |
| --- 1 row(s) selected. |
| >>execute s using 456; |
| |
| SESSION_ID SEQUENCE_NO C_CHAR C_CHAR_UPSHIFT C_CHAR_NOT_CASESPECIFIC C_CHAR_VARYING C_CHAR_VARYING_UPSHIFT C_CHAR_VARYING_NOT_CASESPECIFIC C_VARCHAR C_VARCHAR_UPSHIFT C_VARCHAR_NOT_CASESPECIFIC C_NCHAR C_NCHAR_UPSHIFT C_NCHAR_NOT_CASESPECIFIC C_NCHAR_VARYING C_NCHAR_VARYING_UPSHIFT C_NCHAR_VARYING_NOT_CASESPECIFIC C_NUMERIC C_NUMERIC_UNSIGNED C_DECIMAL C_DECIMAL_UNSIGNED C_INTEGER C_INTEGER_UNSIGNED C_LARGEINT C_SMALLINT C_SMALLINT_UNSIGNED C_TINYINT C_TINYINT_UNSIGNED C_FLOAT C_REAL C_DOUBLE_PRECISION C_DATE C_TIME C_TIME6 C_TIMESTAMP0 C_TIMESTAMP C_TIMESTAMP6 C_INTERVAL C_INTERVALS86 C_INTERVALD6S C_BLOB C_CLOB C_BOOLEAN (EXPR) |
| -------------------- -------------------- --------------- --------------- ----------------------- --------------- ---------------------- ------------------------------- --------------- ----------------- -------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------- ------------ ------------------ ----------- ------------------ ----------- ------------------ -------------------- ---------- ------------------- --------- ------------------ ------------------------- --------------- ------------------------- ---------- -------- --------------- ------------------- -------------------------- -------------------------- ---------- ---------------- ----------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- --------- ----------- |
| |
| 1 1 CHAR_1 CHAR_1 char_1 CHARVAR_1 CHARVAR_1 charvar_1 VARCHAR_1 VARCHAR_1 varchar_1 NCHAR_1 NCHAR_1 nchar_1 NCHARVAR_1 NCHARVAR_1 ncharvar_1 -1.00 1.00 -1.11 1.11 -1 1 -1 -1 1 -1 1 -1.11000000000000016E+000 -1.1100000E+000 -1.11000000000000016E+000 2001-01-01 01:01:01 01:01:01.111111 2001-01-01 01:01:01 2001-01-01 01:01:01.111111 2001-01-01 01:01:01.111111 1-01 88888888.666666 666666 23:59:59.999999 BLOB_1 CLOB_1 TRUE 456 |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare s from |
| +>select * |
| +>from udf(sessionize_dynamic(table(select * from t001_Datatypes), |
| +> 'C_VARCHAR', 'C_DECIMAL_UNSIGNED', 60)) cpp |
| +> natural join |
| +> udf(sessionize_java(table(select * from t001_Datatypes), |
| +> 'C_VARCHAR', 'C_DECIMAL_UNSIGNED', 60)) java; |
| |
| --- SQL command prepared. |
| >>execute s; |
| |
| SESSION_ID SEQUENCE_NO C_CHAR C_CHAR_UPSHIFT C_CHAR_NOT_CASESPECIFIC C_CHAR_VARYING C_CHAR_VARYING_UPSHIFT C_CHAR_VARYING_NOT_CASESPECIFIC C_VARCHAR C_VARCHAR_UPSHIFT C_VARCHAR_NOT_CASESPECIFIC C_NCHAR C_NCHAR_UPSHIFT C_NCHAR_NOT_CASESPECIFIC C_NCHAR_VARYING C_NCHAR_VARYING_UPSHIFT C_NCHAR_VARYING_NOT_CASESPECIFIC C_NUMERIC C_NUMERIC_UNSIGNED C_DECIMAL C_DECIMAL_UNSIGNED C_INTEGER C_INTEGER_UNSIGNED C_LARGEINT C_SMALLINT C_SMALLINT_UNSIGNED C_TINYINT C_TINYINT_UNSIGNED C_FLOAT C_REAL C_DOUBLE_PRECISION C_DATE C_TIME C_TIME6 C_TIMESTAMP0 C_TIMESTAMP C_TIMESTAMP6 C_INTERVAL C_INTERVALS86 C_INTERVALD6S C_BLOB C_CLOB C_BOOLEAN |
| -------------------- -------------------- --------------- --------------- ----------------------- --------------- ---------------------- ------------------------------- --------------- ----------------- -------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------- ------------ ------------------ ----------- ------------------ ----------- ------------------ -------------------- ---------- ------------------- --------- ------------------ ------------------------- --------------- ------------------------- ---------- -------- --------------- ------------------- -------------------------- -------------------------- ---------- ---------------- ----------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- --------- |
| |
| 1 1 CHAR_1 CHAR_1 char_1 CHARVAR_1 CHARVAR_1 charvar_1 VARCHAR_1 VARCHAR_1 varchar_1 NCHAR_1 NCHAR_1 nchar_1 NCHARVAR_1 NCHARVAR_1 ncharvar_1 -1.00 1.00 -1.11 1.11 -1 1 -1 -1 1 -1 1 -1.11000000000000016E+000 -1.1100000E+000 -1.11000000000000016E+000 2001-01-01 01:01:01 01:01:01.111111 2001-01-01 01:01:01 2001-01-01 01:01:01.111111 2001-01-01 01:01:01.111111 1-01 88888888.666666 666666 23:59:59.999999 BLOB_1 CLOB_1 TRUE |
| |
| --- 1 row(s) selected. |
| >>execute s; |
| |
| SESSION_ID SEQUENCE_NO C_CHAR C_CHAR_UPSHIFT C_CHAR_NOT_CASESPECIFIC C_CHAR_VARYING C_CHAR_VARYING_UPSHIFT C_CHAR_VARYING_NOT_CASESPECIFIC C_VARCHAR C_VARCHAR_UPSHIFT C_VARCHAR_NOT_CASESPECIFIC C_NCHAR C_NCHAR_UPSHIFT C_NCHAR_NOT_CASESPECIFIC C_NCHAR_VARYING C_NCHAR_VARYING_UPSHIFT C_NCHAR_VARYING_NOT_CASESPECIFIC C_NUMERIC C_NUMERIC_UNSIGNED C_DECIMAL C_DECIMAL_UNSIGNED C_INTEGER C_INTEGER_UNSIGNED C_LARGEINT C_SMALLINT C_SMALLINT_UNSIGNED C_TINYINT C_TINYINT_UNSIGNED C_FLOAT C_REAL C_DOUBLE_PRECISION C_DATE C_TIME C_TIME6 C_TIMESTAMP0 C_TIMESTAMP C_TIMESTAMP6 C_INTERVAL C_INTERVALS86 C_INTERVALD6S C_BLOB C_CLOB C_BOOLEAN |
| -------------------- -------------------- --------------- --------------- ----------------------- --------------- ---------------------- ------------------------------- --------------- ----------------- -------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------- ------------ ------------------ ----------- ------------------ ----------- ------------------ -------------------- ---------- ------------------- --------- ------------------ ------------------------- --------------- ------------------------- ---------- -------- --------------- ------------------- -------------------------- -------------------------- ---------- ---------------- ----------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- --------- |
| |
| 1 1 CHAR_1 CHAR_1 char_1 CHARVAR_1 CHARVAR_1 charvar_1 VARCHAR_1 VARCHAR_1 varchar_1 NCHAR_1 NCHAR_1 nchar_1 NCHARVAR_1 NCHARVAR_1 ncharvar_1 -1.00 1.00 -1.11 1.11 -1 1 -1 -1 1 -1 1 -1.11000000000000016E+000 -1.1100000E+000 -1.11000000000000016E+000 2001-01-01 01:01:01 01:01:01.111111 2001-01-01 01:01:01 2001-01-01 01:01:01.111111 2001-01-01 01:01:01.111111 1-01 88888888.666666 666666 23:59:59.999999 BLOB_1 CLOB_1 TRUE |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- negative tests |
| >>select * from udf(sessionize_err('abc')) XOX(a); |
| |
| *** ERROR[1389] Object SESSIONIZE_ERR does not exist in Trafodion. |
| |
| *** ERROR[4450] Function SESSIONIZE_ERR is not a built-in function or registered user-defined function. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>-- expect error 11246 |
| >> |
| >>-- these should be caught by the compiler interface |
| >>select * from udf(sessionize_dynamic(table (select * from clicks), 'TS')) XOX; |
| |
| *** ERROR[11252] Second scalar parameter must be a string constant (SQLSTATE 38003) |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>-- too few input parameters |
| >>select * from udf(sessionize_dynamic(table (select * from clicks), 1,2,3)) XOX; |
| |
| *** ERROR[11252] Column 1 not found (SQLSTATE 38900) |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>-- not a string parameter |
| >>select * from udf(sessionize_dynamic(TABLE(SELECT userid, JULIANTIMESTAMP(ts) as TS |
| +> FROM clicks |
| +> PARTITION BY userid ORDER BY ts), |
| +>TABLE(SELECT userid, JULIANTIMESTAMP(ts) as TS |
| +> FROM clicks |
| +> PARTITION BY userid ORDER BY ts), |
| +>cast('TS' as char(2)), |
| +> 'USERID', |
| +> 60000000)) XOX; |
| |
| *** ERROR[15001] A syntax error occurred at or before: |
| select * from udf(sessionize_dynamic(TABLE(SELECT userid, JULIANTIMESTAMP(ts) a |
| s TS FROM clicks PARTITION |
| BY userid ORDER BY ts), TABLE(SELECT userid, JULIANTIMESTAMP(ts) as TS |
| FROM clicks PARTITION BY userid ORD |
| ER BY ts), cast('TS' as char(2)), 'USERID', |
| 60000000)) XOX; |
| ^ (409 characters from start of SQL statement) |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>-- too many table-valued arguments (syntax error for now) |
| >>SELECT cast(CONVERTTIMESTAMP(ts) as TIME(6)), userid, session_id |
| +>FROM UDF(sessionize_dynamic(TABLE(SELECT userid, JULIANTIMESTAMP(ts) as TS |
| +> FROM clicks |
| +> PARTITION BY userid ORDER BY ts), |
| +> 'NONEXISTENTCOL', |
| +> 'USERID', |
| +> 60000000)) XOX; |
| |
| *** ERROR[11252] Column NONEXISTENTCOL not found (SQLSTATE 38900) |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>-- non-existent column specified in input parameter |
| >>SELECT cast(CONVERTTIMESTAMP(ts) as TIME(6)), userid, session_id |
| +>FROM UDF(sessionize_dynamic(TABLE(SELECT userid, JULIANTIMESTAMP(ts) as TS |
| +> FROM clicks |
| +> PARTITION BY 3 ORDER BY ts), |
| +> 'SESSION_ID', |
| +> 'TS', |
| +> 60000000)) XOX; |
| |
| *** ERROR[11154] Ordinal number 3 used in PARTITION BY clause of a UDF must be an integer ranging from 1 to the number of columns (2 in this case). |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>-- PARTITION BY 3 has an invalid column number |
| >> |
| >> CREATE LIBRARY TRAFODION.SCH.ALTERTEST FILE $$QUOTE$$ $$REGRRUNDIR$$/TEST001.dll $$QUOTE$$; |
| |
| --- SQL operation complete. |
| >> ALTER LIBRARY TRAFODION.SCH.ALTERTEST FILE $$QUOTE$$ $$REGRRUNDIR$$/TEST001.jar $$QUOTE$$; |
| |
| --- SQL operation complete. |
| >> |
| >>log; |
