>>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

--- 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

--- 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                          212334861599500000  12.345.567.345 

--- 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;
