Google Git
Sign in
apache/trafodion/master/./core/sql/regress/compGeneral/EXPECTED006.SB
blob: 5f80db518733a750c12b17b306f4d43a669906a5 [file] [log] [blame]
>>obey TEST006(create_tables);
>>
>>create table t006t1 (a varchar (200));
--- SQL operation complete.
>>
>>create table t006t2 (a int);
--- SQL operation complete.
>>
>>create table t006t3 (a int not null);
--- SQL operation complete.
>>
>>create table t006t4 (a char(10) not casespecific, b char(10));
--- SQL operation complete.
>>
>>create table t006t5 (a int not null,
+> b char(3) not null,
+> c date not null,
+> primary key(a));
--- SQL operation complete.
>>
>>create table t006t6 (a char(10) character set ucs2);
--- SQL operation complete.
>>
>>create view t006t1_v as select a from t006t1;
--- SQL operation complete.
>>
>>create table t006t7 (a float);
--- SQL operation complete.
>>
>>create table t006t8 (a time);
--- SQL operation complete.
>>
>>-- used for index hints
>>create table t006t9 (a int not null primary key, b int, c int) salt using 2 partitions ;
--- SQL operation complete.
>>create index t006t9ix1 on t006t9(b) ;
--- SQL operation complete.
>>create index t006t9ix2 on t006t9(c) ;
--- SQL operation complete.
>>create index t006t9ix3 on t006t9(b) salt like table;
--- SQL operation complete.
>>create index t006t9ix4 on t006t9(c) salt like table;
--- SQL operation complete.
>>
>>-- used for large scope rules
>>create table x1 (a int not null, b int not null primary key);
--- SQL operation complete.
>>create table x2 (c int not null primary key, d int);
--- SQL operation complete.
>>create table x3 (e int not null, f int) store by (e);
--- SQL operation complete.
>>
>>insert into x1 values (1,1),(2,2);
--- 2 row(s) inserted.
>>insert into x2 values (1,1),(2,2),(3,3),(4,4),(5,5);
--- 5 row(s) inserted.
>>
>>create mv mvx refresh on request initialize on create as select a,sum(b) s_b
+>from x1 group by a;
*** ERROR[3131] The statement just entered is currently not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>update statistics for table x1 on every column;
--- SQL operation complete.
>>update statistics for table x2 on every column;
--- SQL operation complete.
>>update statistics for table x3 on every column;
--- SQL operation complete.
>>
>>create table t006tao(a int, b int, c int);
--- SQL operation complete.
>>
>>insert into t006tao values
+>(0,NULL,3),
+>(1,NULL,3),
+>(1,2,3),
+>(3,4,5),
+>(3,8,5),
+>(3,10,5),
+>(10,5,8),
+>(10,6,9),
+>(10,7,8),
+>(10,8,9),
+>(10,9,8),
+>(10,10,9),
+>(10,11,8),
+>(10,12,9),
+>(10,13,9),
+>(10,14,9)
+>;
--- 16 row(s) inserted.
>>
>>CREATE TABLE t006_employees
+>(emp_name VARCHAR(10) NOT NULL,
+> dept_name VARCHAR(15) NOT NULL,
+> salary_amt DECIMAL (8,2) NOT NULL,
+> PRIMARY KEY (emp_name, dept_name)
+>);
--- SQL operation complete.
>>
>>INSERT INTO t006_employees
+>VALUES
+>('Aaron','acct',3100.00),
+>('Abaddon','acct',3200.00),
+>('Abbott','acct',3300.00),
+>('Abel','acct',3500.00),
+>('Absalom','acct', 5500.00),
+>('Shannen','ship', 1000.00),
+>('Shannon','ship', 2000.00),
+>('Shaquille','ship', 3400.00),
+>('Sheamus','ship', 4000.00),
+>('Shelah','ship', 3600.00),
+>('Shelby','ship', 4500.00),
+>('Sheldon','ship', 5500.00),
+>('Hamilton','HR',2300.00),
+>('Hamish', 'HR', 1600.00),
+>('Hamlet', 'HR', 1200.00),
+>('Hammond', 'HR', 800.00),
+>('Hamuel', 'HR', 700.00),
+>('Hanael', 'HR', 600.00),
+>('Hanan', 'HR', 1000.00);
--- 19 row(s) inserted.
>>
>>create table t006taoc(a char(10), b char(10), c varchar(10));
--- SQL operation complete.
>>
>>insert into t006taoc values
+>('0',NULL,'3'),
+>('a',NULL,'3'),
+>('a','2','3'),
+>('3bc','4','5'),
+>('3bc','8','5'),
+>('3bc','10','5'),
+>('10','5','8'),
+>('10','6','9'),
+>('10','7','8'),
+>('10','8','9'),
+>('10','9','8'),
+>('10','10','9'),
+>('10','11','8'),
+>('10','12','9'),
+>('10','13','9'),
+>('10','14','9')
+>;
--- 16 row(s) inserted.
>>
>>-- Used for builtin function SOUNDEX() tests
>>create table t006emp1(name varchar(20));
--- SQL operation complete.
>>
>>create table t006emp2(name varchar(20) character set UCS2);
--- SQL operation complete.
>>
>>insert into t006emp1 values
+>('Smith'),
+>('Lynn'),
+>('Ruoyu'),
+>('John'),
+>('Lane')
+>;
--- 5 row(s) inserted.
>>
>>insert into t006emp2 values
+>('Smith'),
+>('Lynn'),
+>('Ruoyu'),
+>('John'),
+>('Lane')
+>;
--- 5 row(s) inserted.
>>
>>obey TEST006(negative_tests);
>>
>>-- Error 3233
>>prepare xx from update with no rollback t006t4 set a = b;
*** ERROR[3233] This type of UPDATE is not allowed when NO ROLLBACK transaction setting is in effect. Suggestion: Set ALLOW_RISKY_UPDATE_WITH_NO_ROLLBACK CQD to ON to allow UPDATE command with right-hand side SET clause consisting of columns.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 3416
>>prepare xx from select coalesce(a, NULL) from t006t1;
*** ERROR[3416] The last operand of function COALESCE must not be NULL.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4009
>>prepare xx from select sum(max(a)) from t006t1;
*** ERROR[4009] An aggregate is not allowed inside an aggregate function.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4022
>>prepare xx from insert into t006t2(a, a) select a, a from t006t3;
*** ERROR[4022] Target column A was specified more than once.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4034
>>prepare xx from select * from t006t1 where a = 1234 collate default;
*** ERROR[4034] The operation (1234 COLLATE DEFAULT) is not allowed.
*** ERROR[4073] The COLLATE clause may appear only after an expression of character data type, not NUMERIC.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4036
>>prepare xx from select year(a) from t006t2;
*** ERROR[4036] The source field of the EXTRACT function must be of DateTime or Interval type.
*** ERROR[4062] The preceding error actually occurred in function YEAR.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4039
>>prepare xx from insert into t006t3 values ('a');
--- SQL command prepared.
>>
>>-- Error 4043
>>prepare xx from select * from t006t1 where to_number(1112) = A;
*** ERROR[4043] The operand of function TO_NUMBER must be character.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select tokenstr('a', a) from t006t2;
*** ERROR[4043] The operand of function TOKENSTR must be character.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select queryid_extract(a, b) from t006t5;
*** ERROR[4043] The operand of function QUERYID_EXTRACT must be character.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select os_userid(a) from t006t2;
*** ERROR[4043] The operand of function OS_USERID must be character.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select convertfromhex(a) from t006t2;
*** ERROR[4043] The operand of function CONVERTFROMHEX must be character.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select char_length(a) from t006t2;
--- SQL command prepared.
>>prepare xx from select lower(a) from t006t2;
*** ERROR[4043] The operand of function LOWER or LCASE must be character.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select octet_length(a) from t006t2;
--- SQL command prepared.
>>
>>-- Error 4045
>>prepare xx from select zeroifnull(a) from t006t1;
*** ERROR[4045] The operand of function ZEROIFNULL must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select nullifzero(a) from t006t1;
*** ERROR[4045] The operand of function NULLIFZERO must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select abs(a) from t006t1;
*** ERROR[4045] The operand of function ABS must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select user(a) from t006t1;
--- SQL command prepared.
>>prepare xx from select ~a from t006t1;
*** ERROR[4045] The operand of function BITNOT must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select sin(a) from t006t1;
*** ERROR[4045] The operand of function SIN must be numeric.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4046
>>prepare xx from select ~(a) from t006t7;
*** ERROR[4046] The operands of function BITNOT must be exact numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select bitextract(a, a, a) from t006t7;
*** ERROR[4046] The operands of function BITEXTRACT must be exact numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select mod(a,a) from t006t1;
*** ERROR[4046] The operands of function MOD must be exact numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select mod(a,a) from t006t7;
*** ERROR[4046] The operands of function MOD must be exact numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select repeat('a', a) from t006t7;
*** ERROR[4046] The operands of function REPEAT must be exact numeric.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4047
>>prepare xx from select round(3.224, 2.3) from t006t1;
*** ERROR[4047] The operands of function ROUND must have a scale of 0.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select round(3.224, 3, 2.3) from t006t1;
*** ERROR[4047] The operands of function ROUND must have a scale of 0.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select round(t006t2.a, 1.1) from t006t1, t006t2;
*** ERROR[4047] The operands of function ROUND must have a scale of 0.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select ~(1.1) from t006t1;
*** ERROR[4047] The operands of function BITNOT must have a scale of 0.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select bitextract(a, a, 1.1) from t006t2;
*** ERROR[4047] The operands of function BITEXTRACT must have a scale of 0.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select mod(a,1.1) from t006t2;
*** ERROR[4047] The operands of function MOD must have a scale of 0.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select repeat(a, 1.1) from t006t1;
*** ERROR[4047] The operands of function REPEAT must have a scale of 0.
*** ERROR[8822] The statement was not prepared.
>>
>>control query default JDBC_PROCESS 'ON';
--- SQL operation complete.
>>prepare xx from select locate(a,a,1.1) from t006t1;
*** ERROR[4047] The operands of function POSITION must have a scale of 0.
*** ERROR[8822] The statement was not prepared.
>>control query default JDBC_PROCESS reset;
--- SQL operation complete.
>>
>>prepare xx from select substring(a,1.1) from t006t1;
*** ERROR[4047] The operands of function SUBSTRING must have a scale of 0.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select substring(a,1,1.1) from t006t1;
*** ERROR[4047] The operands of function SUBSTRING must have a scale of 0.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4051
>>prepare xx from select repeat(a, a) from t006t2;
*** ERROR[4051] The first operand of function REPEAT must be character.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select substring(a,a) from t006t2;
*** ERROR[4051] The first operand of function SUBSTRING must be character.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4052
>>prepare xx from select a^b from t006t5;
*** ERROR[4052] The second operand of function BITXOR must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select 1**a from t006t1;
*** ERROR[4052] The second operand of function '**' must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select substring(a,a) from t006t1;
*** ERROR[4052] The second operand of function SUBSTRING must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select round(t006t2.a, t006t1.a) from t006t1, t006t2;
*** ERROR[4052] The second operand of function ROUND must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select repeat(a, a) from t006t1;
*** ERROR[4052] The second operand of function REPEAT must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select offset(a,a,a) from t006t1;
*** ERROR[4052] The second operand of function OFFSET must be numeric.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4053
>>prepare xx from select bitextract(a, a, b) from t006t5;
*** ERROR[4053] The third operand of function BITEXTRACT must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select substring(a,1,a) from t006t1;
*** ERROR[4053] The third operand of function SUBSTRING must be numeric.
*** ERROR[8822] The statement was not prepared.
>>
>>control query default JDBC_PROCESS 'ON';
--- SQL operation complete.
>>prepare xx from select locate(a,a,a) from t006t1;
*** ERROR[4053] The third operand of function POSITION must be numeric.
*** ERROR[8822] The statement was not prepared.
>>control query default JDBC_PROCESS reset;
--- SQL operation complete.
>>
>>prepare xx from select insert(a, 1, 2.1, 3) from t006t2;
*** ERROR[4053] The third operand of function INSERT must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select round(t006t2.a, t006t2.a, t006t1.a) from t006t1, t006t2;
*** ERROR[4053] The third operand of function ROUND must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select offset(a,1,a) from t006t1;
*** ERROR[4053] The third operand of function OFFSET must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select movingcount(a, 1, a) from t006t1 sequence by a;
*** ERROR[4053] The third operand of function MOVINGCOUNT must be numeric.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4059
>>prepare xx from select round(a) from t006t1;
*** ERROR[4059] The first operand of function ROUND must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select trunc(a) from t006t1;
*** ERROR[4059] The first operand of function TRUNC must be numeric.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select a**a from t006t1;
*** ERROR[4059] The first operand of function '**' must be numeric.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4067
>>prepare xx from select * from table (explain(NULL, 5));
*** ERROR[4067] The operands of function EXPLAIN must be character data types.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4068
>>prepare xx from select convertfromhex(cast(a as char(1))) from t006t1;
*** ERROR[4068] The operand of function CONVERTFROMHEX must contain an even number of characters.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4070
>>prepare xx from select converttimestamp(a) from t006t1;
*** ERROR[4070] The operand of function CONVERTTIMESTAMP must be exact numeric.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4071
>>prepare xx from select * from t006t1 where last_day(A) = A;
*** ERROR[4182] Function LAST_DAY operand 1 must be of type DATE or TIMESTAMP.
*** ERROR[8822] The statement was not prepared.
>>
>>prepare xx from select juliantimestamp(a) from t006t1;
*** ERROR[4071] The first operand of function JULIANTIMESTAMP must be a datetime.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4073
>>prepare xx from select * from t006t1 where a = (select a from t006t3) collate default;
*** ERROR[4034] The operation (SCALAR_AGGR(SCAN TRAFODION.SCH.T006T3) COLLATE DEFAULT) is not allowed.
*** ERROR[4073] The COLLATE clause may appear only after an expression of character data type, not INTEGER.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4078
>>prepare xx from select variance(a, b) over (order by a) from t006t2;
*** ERROR[4078] Function VARIANCE does not accept a weight operand.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4106
>>prepare xx from select code_value(ascii(a)) from t006t6;
*** ERROR[4106] The character set for the operand of function ASCII must be ISO88591.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4122
>>prepare xx from insert into t006t3 values (NULL);
*** ERROR[4122] NULL cannot be assigned to NOT NULL column TRAFODION.SCH.T006T3.A.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4131
>>alter table t006t1 add constraint t006t1_check check (t006t1.A = current_time);
*** ERROR[4131] Current_time, current_date, or current_timestamp is not allowed in a check constraint.
--- SQL operation failed with errors.
>>
>>-- Error 4132
>>alter table t006t1 add constraint t006t1_check check (t006t1.A = current_user);
*** ERROR[4132] Current_user, session_user, or system_user is not allowed in a check constraint.
--- SQL operation failed with errors.
>>
>>-- Error 4185
>>prepare xx from select sum(a) from t006t1 group by 1;
*** ERROR[4185] Select list index is not allowed to be specified in the GROUP BY clause for this query.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4310
>>prepare xx from select * from t006t1 where user(a) = a;
*** ERROR[4310] USER(x) is currently supported only in the outermost SELECT list. For example, it cannot be a part of the subquery.
*** ERROR[4062] The preceding error actually occurred in function USER(TRAFODION.SCH.T006T1.A).
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select * from t006t1 where a = (select user(a) from t006t2);
*** ERROR[4310] USER(x) is currently supported only in the outermost SELECT list. For example, it cannot be a part of the subquery.
*** ERROR[4062] The preceding error actually occurred in function USER(TRAFODION.SCH.T006T2.A).
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4312
>>prepare xx from update HP_SYSTEM_CATALOG.mxcs_schema.ASSOC2DS set assoc_id = 100;
*** ERROR[1002] Catalog HP_SYSTEM_CATALOG does not exist.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4391
>>prepare xx from select variance(a) over (order by ?p) from t006t2;
*** ERROR[4391] Parameters and outer references in the PARTITION BY or ORDER BY clause of a window function are not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 8403
>>prepare xx from select substring(a,1,-1) from t006t1;
*** ERROR[8403] The length argument of function SUBSTRING cannot be less than zero or greater than source string length.
*** ERROR[8822] The statement was not prepared.
>>
>>obey TEST006(positive_tests);
>>
>>-- Non-error coverage cases
>>prepare xx from select queryid_extract(a, b) from t006t4;
--- SQL command prepared.
>>prepare xx from select round(3.224, 3, 2) from t006t1;
--- SQL command prepared.
>>prepare xx from select user(a) from t006t1;
--- SQL command prepared.
>>
>>control query default MODE_SPECIAL_4 'ON';
--- SQL operation complete.
>>prepare xx from select * from t006t2 where to_date(A, '99:99:99:99') = A;
--- SQL command prepared.
>>control query default MODE_SPECIAL_4 reset;
--- SQL operation complete.
>>
>>prepare xx from select current_running from t006t1;
--- SQL command prepared.
>>prepare xx from select position(a in a) from t006t4;
--- SQL command prepared.
>>prepare xx from select replace(a, a, a) from t006t4;
--- SQL command prepared.
>>
>>prepare xx from select date_trunc('YEAR',a) from t006t1;
*** ERROR[4182] Function DATE_TRUNC operand 2 must be of type DATE or TIMESTAMP.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select date_trunc('YEAR',a) from t006t1;
*** ERROR[4182] Function DATE_TRUNC operand 2 must be of type DATE or TIMESTAMP.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select date_trunc('month',a) from t006t1;
*** ERROR[4182] Function DATE_TRUNC operand 2 must be of type DATE or TIMESTAMP.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select date_trunc('century',a) from t006t1;
*** ERROR[4182] Function DATE_TRUNC operand 2 must be of type DATE or TIMESTAMP.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select datediff(year,a, a) from t006t1;
*** ERROR[4182] Function DATEDIFF operand 2 must be of type DATE or TIMESTAMP.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select datediff(month,a, a) from t006t1;
*** ERROR[4182] Function DATEDIFF operand 2 must be of type DATE or TIMESTAMP.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select datediff(day,current_timestamp,a) from t006t1;
*** ERROR[4182] Function DATEDIFF operand 3 must be of type DATE or TIMESTAMP.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select datediff(hour,a, a) from t006t1;
*** ERROR[4182] Function DATEDIFF operand 2 must be of type DATE or TIMESTAMP.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select datediff(minute,a, a) from t006t1;
*** ERROR[4182] Function DATEDIFF operand 2 must be of type DATE or TIMESTAMP.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select datediff(second,a, a) from t006t1;
*** ERROR[4182] Function DATEDIFF operand 2 must be of type DATE or TIMESTAMP.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select datediff(quarter,c, c) from t006t5;
--- SQL command prepared.
>>
>>prepare xx from
+>select
+>decode(CAST (( a + 3) / 4 AS INT ) ,
+>0,0.04,
+>1,0.04,
+>0.06) from t006t3;
--- SQL command prepared.
>>
>>prepare xx from select count(*) from t006t1 where lpad(a,0,'') = 'aa';
--- SQL command prepared.
>>prepare xx from select count(*) from t006t1 where rpad(a,0,'') = 'aa';
--- SQL command prepared.
>>
>>prepare xx from select truncate(a, 2) from t006t3;
--- SQL command prepared.
>>prepare xx from select os_userid(a) from t006t1;
--- SQL command prepared.
>>
>>control query default ODBC_PROCESS 'ON';
--- SQL operation complete.
>>control query default COMP_BOOL_173 'ON';
--- SQL operation complete.
>>prepare xx from insert into t006t3 values (?p);
--- SQL command prepared.
>>control query default ODBC_PROCESS reset;
--- SQL operation complete.
>>control query default COMP_BOOL_173 reset;
--- SQL operation complete.
>>
>>control query default INFER_CHARSET 'ON';
--- SQL operation complete.
>>prepare xx from select replace(a, a, a) from t006t1;
--- SQL command prepared.
>>prepare xx from select * from t006t1 where ?p like a;
--- SQL command prepared.
>>prepare xx from select octet_length(a) from t006t1;
--- SQL command prepared.
>>prepare xx from select locate(a,a) from t006t1;
--- SQL command prepared.
>>prepare xx from select min(a) from t006t1;
--- SQL command prepared.
>>control query default INFER_CHARSET reset;
--- SQL operation complete.
>>
>>prepare xx from select lower(a) from t006t4;
--- SQL command prepared.
>>
>>control query default PUBLIC_SCHEMA_NAME 'test_public_schema';
--- SQL operation complete.
>>create schema $$TEST_CATALOG$$.TEST_PUBLIC_SCHEMA;
--- SQL operation complete.
>>Create table test_public_schema.t_pub1 (a int);
--- SQL operation complete.
>>prepare xx from select * from t_pub1;
--- SQL command prepared.
>>drop schema $$TEST_CATALOG$$.TEST_PUBLIC_SCHEMA cascade;
--- SQL operation complete.
>>control query default PUBLIC_SCHEMA_NAME reset;
--- SQL operation complete.
>>
>>control query default ALLOW_ISOLATION_LEVEL_IN_CREATE_VIEW 'OFF';
--- SQL operation complete.
>>prepare xx from select * from t006t1_v;
--- SQL command prepared.
>>control query default ALLOW_ISOLATION_LEVEL_IN_CREATE_VIEW reset;
--- SQL operation complete.
>>
>>control query default COMP_BOOL_192 'ON';
--- SQL operation complete.
>>prepare xx from select * from t006t2 full outer join t006t3 on t006t2.a = t006t3.a;
--- SQL command prepared.
>>control query default COMP_BOOL_192 reset;
--- SQL operation complete.
>>
>>prepare xx from select * from table (explain(_UCS2'NULL', _UCS2's'));
--- SQL command prepared.
>>
>>control query default COMP_BOOL_203 'ON';
--- SQL operation complete.
>>prepare xx from select rank(a) from t006t1 sequence by a;
--- SQL command prepared.
>>control query default COMP_BOOL_203 reset;
--- SQL operation complete.
>>
>>control query default MULTI_JOIN_CONSIDER_INITIAL_JOIN_ORDER 'ON';
--- SQL operation complete.
>>prepare xx from select * from t006t1, t006t2, t006t3, t006t4, t006t5 where t006t2.a=t006t3.a and t006t1.a = t006t4.a and t006t3.a = t006t5.a and t006t5.b = t006t1.a;
--- SQL command prepared.
>>control query default MULTI_JOIN_CONSIDER_INITIAL_JOIN_ORDER reset;
--- SQL operation complete.
>>
>>control query default HIDE_INDEXES 'KEYINDEXES';
--- SQL operation complete.
>>prepare xx from select * from t006t1;
--- SQL command prepared.
>>control query default HIDE_INDEXES reset;
--- SQL operation complete.
>>
>>prepare xx from select nullifzero(a) from t006t3;
--- SQL command prepared.
>>prepare xx from select converttobits(a) from t006t1;
--- SQL command prepared.
>>prepare xx from select queryid_extract(a, a) from t006t1;
--- SQL command prepared.
>>prepare xx from select tokenstr('a', a) from t006t1;
--- SQL command prepared.
>>
>>prepare xx from select cast(?p as char(3) not null) from t006t1;
--- SQL command prepared.
>>
>>control query default OVERRIDE_SYSKEY 'ON';
--- SQL operation complete.
>>prepare xx from select * from t006t7;
--- SQL command prepared.
>>control query default OVERRIDE_SYSKEY reset;
--- SQL operation complete.
>>
>>control query default COMP_BOOL_108 'OFF';
--- SQL operation complete.
>>prepare xx from select * from t006t2, t006t3 where log(t006t2.a) = log(t006t3.a);
--- SQL command prepared.
>>control query default COMP_BOOL_108 reset;
--- SQL operation complete.
>>
>>prepare xx from select * from t006t8 t5_1, t006t8 t5_2 where hour(t5_1.a) = minute(t5_2.a);
--- SQL command prepared.
>>prepare xx from select * from t006t8 t5_1, t006t8 t5_2 where second(t5_1.a) = minute(t5_2.a);
--- SQL command prepared.
>>
>>control query default MULTI_JOIN_CONSIDER_INITIAL_JOIN_ORDER 'ON';
--- SQL operation complete.
>>prepare xx from select * from t006t1, t006t2, t006t3, t006t4, t006t5 where t006t2.a=t006t3.a and t006t1.a = t006t4.a and t006t3.a = t006t5.a and t006t5.b = t006t1.a;
--- SQL command prepared.
>>control query default MULTI_JOIN_CONSIDER_INITIAL_JOIN_ORDER reset;
--- SQL operation complete.
>>
>>prepare xx from select * from t006t5
+>where c >= current_date - interval '12' day and c < current_date;
--- SQL command prepared.
>>
>>control query default HIST_OPTIMISTIC_CARD_OPTIMIZATION '3';
--- SQL operation complete.
>>prepare xx from select * from t006t5
+>where a > 15;
--- SQL command prepared.
>>control query default HIST_OPTIMISTIC_CARD_OPTIMIZATION reset;
--- SQL operation complete.
>>
>>control query default COMP_BOOL_42 'OFF';
--- SQL operation complete.
>>prepare xx from select * from t006t2, t006t3 where t006t2.a = t006t3.a;
--- SQL command prepared.
>>control query default COMP_BOOL_42 reset;
--- SQL operation complete.
>>
>>control query default COMP_BOOL_8 'ON';
--- SQL operation complete.
>>control query default ASM_PRECOMPUTE 'ON';
--- SQL operation complete.
>>prepare xx from select * from t006t1, t006t2, t006t3, t006t4, t006t5 where t006t2.a=t006t3.a and t006t1.a = t006t4.a and t006t3.a = t006t5.a and t006t5.b = t006t1.a;
--- SQL command prepared.
>>control query default COMP_BOOL_8 reset;
--- SQL operation complete.
>>control query default ASM_PRECOMPUTE reset;
--- SQL operation complete.
>>
>>control query default COMP_BOOL_119 'ON';
--- SQL operation complete.
>>prepare xx from select * from t006t2, t006t3, t006t5 where t006t2.a=t006t3.a and t006t3.a = t006t5.a;
--- SQL command prepared.
>>control query default COMP_BOOL_119 reset;
--- SQL operation complete.
>>
>>control query default COMP_BOOL_25 'ON';
--- SQL operation complete.
>>prepare xx from select * from t006t2, t006t3, t006t5, t006t4
+>where
+>t006t2.a=4 and t006t3.a = t006t5.a
+>or
+>t006t4.b=t006t5.b and t006t3.a = 5
+>;
--- SQL command prepared.
>>control query default COMP_BOOL_25 reset;
--- SQL operation complete.
>>
>>control query default OPTIMIZER_HEURISTIC_3 'ON';
--- SQL operation complete.
>>prepare xx from select * from t006t2, t006t3, t006t5, t006t4
+>where
+>t006t2.a=4 and t006t3.a = t006t5.a
+>or
+>t006t4.b=t006t5.b and t006t3.a = 5
+>;
--- SQL command prepared.
>>control query default OPTIMIZER_HEURISTIC_3 reset;
--- SQL operation complete.
>>
>>control query default COMP_STRING_1 'T5';
--- SQL operation complete.
>>prepare xx from select * from t006t2, t006t3, t006t5 as t5 where t006t2.a=t006t3.a and t006t3.a = t5.a;
--- SQL command prepared.
>>control query default COMP_STRING_1 reset;
--- SQL operation complete.
>>
>>-- coverage for Analyzer and largeScopeRules
>>obey TEST006(analyzer_tests);
>>
>>-- coverage for Analyzer.cpp method QueryAnalysis::newTableAnalysis
>>cqd COMP_BOOL_24 'ON';
--- SQL operation complete.
>>prepare st1 from select a,sum(d) s_d from x1,x2 where b=c group by a;
--- SQL command prepared.
>>cqd COMP_BOOL_24 reset;
--- SQL operation complete.
>>
>>-- coverage for Analyzer.cpp method QueryAnalysis::setHistogramsToDisplay
>>cqd HIST_ROOT_NODE '1,2,3,4';
--- SQL operation complete.
>>prepare st1 from select a,sum(d) s_d from x1,x2 where b=c group by a;
--- SQL command prepared.
>>cqd HIST_ROOT_NODE reset;
--- SQL operation complete.
>>
>>-- coverage for Analyzer.cpp method QueryAnalysis::computeTablesJoinedToLargestTable
>>cqd COMP_BOOL_119 'ON';
--- SQL operation complete.
>>prepare st1 from select a,sum(d) s_d from x1,x2 where b=c group by a;
--- SQL command prepared.
>>cqd COMP_BOOL_119 reset;
--- SQL operation complete.
>>
>>-- coverage for Analyzer.cpp method JBBSubsetAnalysis::isAStarPattern
>>cqd COMP_BOOL_12 'ON';
--- SQL operation complete.
>>prepare st1 from select a,sum(d) s_d from x1,x2 where b=c group by a;
--- SQL command prepared.
>>cqd COMP_BOOL_12 reset;
--- SQL operation complete.
>>
>>-- coverage for Analyzer.cpp method JBB::addJBBC
>>prepare st1 from select a,sum(b) s_b from x1 group by a;
--- SQL command prepared.
>>
>>-- coverage for LargeScopeRules.cpp MJEnumRule::nextSubstitute
>>cqd COMP_INT_79 '2';
--- SQL operation complete.
>>prepare st1 from select a ,b from x1,x2,x3 where b=e;
--- SQL command prepared.
>>cqd COMP_INT_79 reset;
--- SQL operation complete.
>>cqd COMP_INT_51 '2';
--- SQL operation complete.
>>prepare st1 from select a ,b from x1,x2,x3 where b=e;
--- SQL command prepared.
>>cqd COMP_INT_51 reset;
--- SQL operation complete.
>>
>>obey TEST006(defaults_tests);
>>
>>-- The plan to support PUBLISHING_ROLES CQD has been
>>-- cancelled so the test cases are commented out.
>>--cqd PUBLISHING_ROLES 'a.b';
>>--cqd PUBLISHING_ROLES reset;
>>
>>cqd DEFAULT_SCHEMA_NAMETYPE 'user';
--- SQL operation complete.
>>cqd DEFAULT_SCHEMA_NAMETYPE reset;
--- SQL operation complete.
>>
>>obey TEST006(misc_tests);
>>
>>-- Memory monitor infrastructure
>>control query default MEMORY_MONITOR 'ON';
--- SQL operation complete.
>>control query default MEMORY_MONITOR_LOG_INSTANTLY 'on';
--- SQL operation complete.
>>control query default MEMORY_MONITOR_LOGFILE 'mml.log';
--- SQL operation complete.
>>prepare xx from select * from t006t2;
--- SQL command prepared.
>>control query default MEMORY_MONITOR 'OFF';
--- SQL operation complete.
>>
>>obey TEST006(mode_special_tests);
>>-- Error 4026
>>control query default MODE_SPECIAL_1 'ON';
--- SQL operation complete.
>>control query default R2_HALLOWEEN_SUPPORT 'ON';
--- SQL operation complete.
>>prepare s1 from insert into t006t3 select * from t006t3 where a in (select a from t006t3);
*** ERROR[4026] Reading from and inserting into, or updating in, or deleting from the same table, TRAFODION.SCH.T006T3, is not currently supported.
*** ERROR[8822] The statement was not prepared.
>>control query default R2_HALLOWEEN_SUPPORT reset;
--- SQL operation complete.
>>
>>prepare xx from select substring(a for 3) from t006t1;
--- SQL command prepared.
>>prepare xx from select substring(a for 3) from t006t3;
--- SQL command prepared.
>>prepare xx from select substring(c for 3) from t006t5;
--- SQL command prepared.
>>prepare xx from select a from t006t1 group by 1 order by 1;
--- SQL command prepared.
>>prepare xx from select a from t006t1 group by A order by A;
--- SQL command prepared.
>>prepare xx from select * from t006t5 where c = 1231999;
--- SQL command prepared.
>>prepare xx from select * from t006t5 where a = date'12/31/1999';
--- SQL command prepared.
>>prepare xx from select * from t006t5 where cast(a as interval day) = 1;
--- SQL command prepared.
>>prepare xx from select * from t006t5 where 1 = interval '1' day;
--- SQL command prepared.
>>
>>prepare xx from select rows since(a = 11, a) from t006t1 sequence by a;
*** ERROR[4052] The second operand of function ROWS SINCE must be numeric.
*** ERROR[8822] The statement was not prepared.
>>control query default MODE_SPECIAL_1 reset;
--- SQL operation complete.
>>
>>control query default MODE_SPECIAL_4 'ON';
--- SQL operation complete.
>>prepare xx from select * from t006t1 where to_date(A, '99:99:99:99') = A;
*** ERROR[4045] The operand of function TO_DATE must be numeric.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Error 4065
>>prepare xx from select * from t006t1 where to_date(A, '201005') = A;
*** ERROR[4065] The format, "201005", specified in the TO_DATE function is not supported.
*** ERROR[8822] The statement was not prepared.
>>prepare xx from select * from t006t2 where to_date(A, 'YYYYMM') = A;
*** ERROR[4041] Type DATE cannot be compared with type INTEGER.
*** ERROR[8822] The statement was not prepared.
>>control query default MODE_SPECIAL_4 reset;
--- SQL operation complete.
>>
>>
>>obey TEST006(olap_functions);
>>
>>select a, b,
+>lead(b, 2) over(partition by a order by b),
+>max(c) over(partition by a order by b)
+>from t006tao
+>;
A B (EXPR) (EXPR)
----------- ----------- ----------- -----------
0 ? ? 3
1 2 ? 3
1 ? ? 3
3 4 10 5
3 8 ? 5
3 10 ? 5
10 5 7 8
10 6 8 9
10 7 9 9
10 8 10 9
10 9 11 9
10 10 12 9
10 11 13 9
10 12 14 9
10 13 ? 9
10 14 ? 9
--- 16 row(s) selected.
>>
>>select emp_name, dept_name, salary_amt,
+>lead(salary_amt, 3) over (partition by dept_name order by emp_name),
+>lead(salary_amt, 2) over (partition by dept_name order by emp_name)
+>from t006_employees;
EMP_NAME DEPT_NAME SALARY_AMT (EXPR) (EXPR)
---------- --------------- ---------- ---------- ----------
Hamilton HR 2300.00 800.00 1200.00
Hamish HR 1600.00 700.00 800.00
Hamlet HR 1200.00 600.00 700.00
Hammond HR 800.00 1000.00 600.00
Hamuel HR 700.00 ? 1000.00
Hanael HR 600.00 ? ?
Hanan HR 1000.00 ? ?
Aaron acct 3100.00 3500.00 3300.00
Abaddon acct 3200.00 5500.00 3500.00
Abbott acct 3300.00 ? 5500.00
Abel acct 3500.00 ? ?
Absalom acct 5500.00 ? ?
Shannen ship 1000.00 4000.00 3400.00
Shannon ship 2000.00 3600.00 4000.00
Shaquille ship 3400.00 4500.00 3600.00
Sheamus ship 4000.00 5500.00 4500.00
Shelah ship 3600.00 ? 5500.00
Shelby ship 4500.00 ? ?
Sheldon ship 5500.00 ? ?
--- 19 row(s) selected.
>>
>>-- this will destroy the output order of the lead function, and make it
>>-- less obvious on the correctness of the result.
>>select
+>a, b, lead(b, 2)
+>over(partition by a order by b)
+>from t006taoc <<+ cardinality 10e6 >>
+>order by 1, 2, 3
+>;
A B (EXPR)
---------- ---------- ----------
0 ? ?
10 10 12
10 11 13
10 12 14
10 13 5
10 14 6
10 5 7
10 6 8
10 7 9
10 8 ?
10 9 ?
3bc 10 8
3bc 4 ?
3bc 8 ?
a 2 ?
a ? ?
--- 16 row(s) selected.
>>
>>select emp_name, dept_name, salary_amt,
+>lead(salary_amt, 5-3, cast(0 as DECIMAL (8,2))) over (partition by dept_name order by emp_name)
+>from t006_employees;
EMP_NAME DEPT_NAME SALARY_AMT (EXPR)
---------- --------------- ---------- ----------
Hamilton HR 2300.00 1200.00
Hamish HR 1600.00 800.00
Hamlet HR 1200.00 700.00
Hammond HR 800.00 600.00
Hamuel HR 700.00 1000.00
Hanael HR 600.00 .00
Hanan HR 1000.00 .00
Aaron acct 3100.00 3300.00
Abaddon acct 3200.00 3500.00
Abbott acct 3300.00 5500.00
Abel acct 3500.00 .00
Absalom acct 5500.00 .00
Shannen ship 1000.00 3400.00
Shannon ship 2000.00 4000.00
Shaquille ship 3400.00 3600.00
Sheamus ship 4000.00 4500.00
Shelah ship 3600.00 5500.00
Shelby ship 4500.00 .00
Sheldon ship 5500.00 .00
--- 19 row(s) selected.
>>
>>-- LAG() Function Tests --
>>-- default offset of LAG() is 1.
>>select emp_name, dept_name, salary_amt,
+>lag(salary_amt) over (partition by dept_name order by emp_name)
+>from T006_EMPLOYEES;
EMP_NAME DEPT_NAME SALARY_AMT (EXPR)
---------- --------------- ---------- ----------
Hamilton HR 2300.00 ?
Hamish HR 1600.00 2300.00
Hamlet HR 1200.00 1600.00
Hammond HR 800.00 1200.00
Hamuel HR 700.00 800.00
Hanael HR 600.00 700.00
Hanan HR 1000.00 600.00
Aaron acct 3100.00 ?
Abaddon acct 3200.00 3100.00
Abbott acct 3300.00 3200.00
Abel acct 3500.00 3300.00
Absalom acct 5500.00 3500.00
Shannen ship 1000.00 ?
Shannon ship 2000.00 1000.00
Shaquille ship 3400.00 2000.00
Sheamus ship 4000.00 3400.00
Shelah ship 3600.00 4000.00
Shelby ship 4500.00 3600.00
Sheldon ship 5500.00 4500.00
--- 19 row(s) selected.
>>
>>select emp_name, dept_name, salary_amt,
+>lag(salary_amt, 0) over (partition by dept_name order by emp_name)
+>from T006_EMPLOYEES;
EMP_NAME DEPT_NAME SALARY_AMT (EXPR)
---------- --------------- ---------- ----------
Hamilton HR 2300.00 2300.00
Hamish HR 1600.00 1600.00
Hamlet HR 1200.00 1200.00
Hammond HR 800.00 800.00
Hamuel HR 700.00 700.00
Hanael HR 600.00 600.00
Hanan HR 1000.00 1000.00
Aaron acct 3100.00 3100.00
Abaddon acct 3200.00 3200.00
Abbott acct 3300.00 3300.00
Abel acct 3500.00 3500.00
Absalom acct 5500.00 5500.00
Shannen ship 1000.00 1000.00
Shannon ship 2000.00 2000.00
Shaquille ship 3400.00 3400.00
Sheamus ship 4000.00 4000.00
Shelah ship 3600.00 3600.00
Shelby ship 4500.00 4500.00
Sheldon ship 5500.00 5500.00
--- 19 row(s) selected.
>>
>>-- negative offset is flipped
>>select emp_name, dept_name, salary_amt,
+>lag(salary_amt, -2) over (partition by dept_name order by emp_name)
+>from T006_EMPLOYEES;
*** ERROR[8440] The size of the history buffer is too small to execute one or more of the OLAP Windowed Functions in the query.
--- 0 row(s) selected.
>>
>>prepare xx from select emp_name, dept_name, salary_amt,
+>lag(salary_amt, CAST(? as INT)) over (partition by dept_name order by emp_name)
+>from T006_EMPLOYEES;
--- SQL command prepared.
>>execute xx using 2;
EMP_NAME DEPT_NAME SALARY_AMT (EXPR)
---------- --------------- ---------- ----------
Hamilton HR 2300.00 ?
Hamish HR 1600.00 ?
Hamlet HR 1200.00 2300.00
Hammond HR 800.00 1600.00
Hamuel HR 700.00 1200.00
Hanael HR 600.00 800.00
Hanan HR 1000.00 700.00
Aaron acct 3100.00 ?
Abaddon acct 3200.00 ?
Abbott acct 3300.00 3100.00
Abel acct 3500.00 3200.00
Absalom acct 5500.00 3300.00
Shannen ship 1000.00 ?
Shannon ship 2000.00 ?
Shaquille ship 3400.00 1000.00
Sheamus ship 4000.00 2000.00
Shelah ship 3600.00 3400.00
Shelby ship 4500.00 4000.00
Sheldon ship 5500.00 3600.00
--- 19 row(s) selected.
>>
>>select emp_name, dept_name, salary_amt,
+>lag(salary_amt, 3) over (partition by dept_name order by emp_name),
+>lead(salary_amt, 2) over (partition by dept_name order by emp_name)
+>from T006_EMPLOYEES;
EMP_NAME DEPT_NAME SALARY_AMT (EXPR) (EXPR)
---------- --------------- ---------- ---------- ----------
Hamilton HR 2300.00 ? 1200.00
Hamish HR 1600.00 ? 800.00
Hamlet HR 1200.00 ? 700.00
Hammond HR 800.00 2300.00 600.00
Hamuel HR 700.00 1600.00 1000.00
Hanael HR 600.00 1200.00 ?
Hanan HR 1000.00 800.00 ?
Aaron acct 3100.00 ? 3300.00
Abaddon acct 3200.00 ? 3500.00
Abbott acct 3300.00 ? 5500.00
Abel acct 3500.00 3100.00 ?
Absalom acct 5500.00 3200.00 ?
Shannen ship 1000.00 ? 3400.00
Shannon ship 2000.00 ? 4000.00
Shaquille ship 3400.00 ? 3600.00
Sheamus ship 4000.00 1000.00 4500.00
Shelah ship 3600.00 2000.00 5500.00
Shelby ship 4500.00 3400.00 ?
Sheldon ship 5500.00 4000.00 ?
--- 19 row(s) selected.
>>
>>select emp_name, dept_name, salary_amt,
+>lag(salary_amt, 3) over (partition by dept_name order by emp_name),
+>lag(salary_amt, 2) over (partition by dept_name order by emp_name)
+>from T006_EMPLOYEES;
EMP_NAME DEPT_NAME SALARY_AMT (EXPR) (EXPR)
---------- --------------- ---------- ---------- ----------
Hamilton HR 2300.00 ? ?
Hamish HR 1600.00 ? ?
Hamlet HR 1200.00 ? 2300.00
Hammond HR 800.00 2300.00 1600.00
Hamuel HR 700.00 1600.00 1200.00
Hanael HR 600.00 1200.00 800.00
Hanan HR 1000.00 800.00 700.00
Aaron acct 3100.00 ? ?
Abaddon acct 3200.00 ? ?
Abbott acct 3300.00 ? 3100.00
Abel acct 3500.00 3100.00 3200.00
Absalom acct 5500.00 3200.00 3300.00
Shannen ship 1000.00 ? ?
Shannon ship 2000.00 ? ?
Shaquille ship 3400.00 ? 1000.00
Sheamus ship 4000.00 1000.00 2000.00
Shelah ship 3600.00 2000.00 3400.00
Shelby ship 4500.00 3400.00 4000.00
Sheldon ship 5500.00 4000.00 3600.00
--- 19 row(s) selected.
>>
>>select emp_name, dept_name, salary_amt,
+>lag(salary_amt, 5-3, cast(0 as DECIMAL (8,2))) over (partition by dept_name order by emp_name)
+>from t006_employees;
EMP_NAME DEPT_NAME SALARY_AMT (EXPR)
---------- --------------- ---------- ----------
Hamilton HR 2300.00 .00
Hamish HR 1600.00 .00
Hamlet HR 1200.00 2300.00
Hammond HR 800.00 1600.00
Hamuel HR 700.00 1200.00
Hanael HR 600.00 800.00
Hanan HR 1000.00 700.00
Aaron acct 3100.00 .00
Abaddon acct 3200.00 .00
Abbott acct 3300.00 3100.00
Abel acct 3500.00 3200.00
Absalom acct 5500.00 3300.00
Shannen ship 1000.00 .00
Shannon ship 2000.00 .00
Shaquille ship 3400.00 1000.00
Sheamus ship 4000.00 2000.00
Shelah ship 3600.00 3400.00
Shelby ship 4500.00 4000.00
Sheldon ship 5500.00 3600.00
--- 19 row(s) selected.
>>
>>select a, b, c,
+>lag(b, 2) over(partition by a order by b),
+>max(c) over(partition by a order by b)
+>from t006tao;
A B C (EXPR) (EXPR)
----------- ----------- ----------- ----------- -----------
0 ? 3 ? 3
1 2 3 ? 3
1 ? 3 ? 3
3 4 5 ? 5
3 8 5 ? 5
3 10 5 4 5
10 5 8 ? 8
10 6 9 ? 9
10 7 8 5 9
10 8 9 6 9
10 9 8 7 9
10 10 9 8 9
10 11 8 9 9
10 12 9 10 9
10 13 9 11 9
10 14 9 12 9
--- 16 row(s) selected.
>>
>>select emp_name, dept_name, salary_amt,
+>lag(salary_amt, 3) over (partition by dept_name order by emp_name),
+>max(salary_amt) over (partition by dept_name)
+>from T006_EMPLOYEES;
EMP_NAME DEPT_NAME SALARY_AMT (EXPR) (EXPR)
---------- --------------- ---------- ---------- ----------
Hamilton HR 2300.00 ? 2300.00
Hamish HR 1600.00 ? 2300.00
Hamlet HR 1200.00 ? 2300.00
Hammond HR 800.00 2300.00 2300.00
Hamuel HR 700.00 1600.00 2300.00
Hanael HR 600.00 1200.00 2300.00
Hanan HR 1000.00 800.00 2300.00
Aaron acct 3100.00 ? 5500.00
Abaddon acct 3200.00 ? 5500.00
Abbott acct 3300.00 ? 5500.00
Abel acct 3500.00 3100.00 5500.00
Absalom acct 5500.00 3200.00 5500.00
Shannen ship 1000.00 ? 5500.00
Shannon ship 2000.00 ? 5500.00
Shaquille ship 3400.00 ? 5500.00
Sheamus ship 4000.00 1000.00 5500.00
Shelah ship 3600.00 2000.00 5500.00
Shelby ship 4500.00 3400.00 5500.00
Sheldon ship 5500.00 4000.00 5500.00
--- 19 row(s) selected.
>>
>>select a, b, lag(b, 2)
+>over(partition by a order by b)
+>from t006taoc <<+ cardinality 10e6 >>
+>order by 1, 2, 3;
A B (EXPR)
---------- ---------- ----------
0 ? ?
10 10 ?
10 11 ?
10 12 10
10 13 11
10 14 12
10 5 13
10 6 14
10 7 5
10 8 6
10 9 7
3bc 10 ?
3bc 4 ?
3bc 8 10
a 2 ?
a ? ?
--- 16 row(s) selected.
>>
>>select emp_name, dept_name, salary_amt,
+>lag(salary_amt, 3) over (partition by dept_name order by emp_name)
+>from T006_EMPLOYEES <<+ cardinality 10e6 >> order by emp_name;
EMP_NAME DEPT_NAME SALARY_AMT (EXPR)
---------- --------------- ---------- ----------
Aaron acct 3100.00 ?
Abaddon acct 3200.00 ?
Abbott acct 3300.00 ?
Abel acct 3500.00 3100.00
Absalom acct 5500.00 3200.00
Hamilton HR 2300.00 ?
Hamish HR 1600.00 ?
Hamlet HR 1200.00 ?
Hammond HR 800.00 2300.00
Hamuel HR 700.00 1600.00
Hanael HR 600.00 1200.00
Hanan HR 1000.00 800.00
Shannen ship 1000.00 ?
Shannon ship 2000.00 ?
Shaquille ship 3400.00 ?
Sheamus ship 4000.00 1000.00
Shelah ship 3600.00 2000.00
Shelby ship 4500.00 3400.00
Sheldon ship 5500.00 4000.00
--- 19 row(s) selected.
>>
>>select md5('10'), crc32(10), sha('10') from dual;
(EXPR) (EXPR) (EXPR)
-------------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------
d3d9446802a44259755d38e6d163e820 852952723 b1d5781111d84f7b3fe45a0852e59758cd7a87e5
--- 1 row(s) selected.
>>select md5(10) from dual;
*** ERROR[4067] The operands of function MD5 must be character data types.
*** ERROR[8822] The statement was not prepared.
>>select sha1(10) from dual;
*** ERROR[4067] The operands of function SHA must be character data types.
*** ERROR[8822] The statement was not prepared.
>>
>>select SOUNDEX('Jon') from dual;
(EXPR)
------
J500
--- 1 row(s) selected.
>>select SOUNDEX('Roy') from dual;
(EXPR)
------
R000
--- 1 row(s) selected.
>>select SOUNDEX('Lynn') from dual;
(EXPR)
------
L500
--- 1 row(s) selected.
>>
>>select name, SOUNDEX(name) from t006emp1 where SOUNDEX(name)=SOUNDEX('Jon');
NAME (EXPR)
-------------------- ------
John J500
--- 1 row(s) selected.
>>select name, SOUNDEX(name) from t006emp1 where SOUNDEX(name)=SOUNDEX('Roy');
NAME (EXPR)
-------------------- ------
Ruoyu R000
--- 1 row(s) selected.
>>select name, SOUNDEX(name) from t006emp1 where SOUNDEX(name)=SOUNDEX('Lynn');
NAME (EXPR)
-------------------- ------
Lynn L500
Lane L500
--- 2 row(s) selected.
>>
>>select name, SOUNDEX(name) from t006emp2 where SOUNDEX(name)=SOUNDEX('Jon');
NAME (EXPR)
---------------------------------------- ------
John J500
--- 1 row(s) selected.
>>select name, SOUNDEX(name) from t006emp2 where SOUNDEX(name)=SOUNDEX('Roy');
NAME (EXPR)
---------------------------------------- ------
Ruoyu R000
--- 1 row(s) selected.
>>select name, SOUNDEX(name) from t006emp2 where SOUNDEX(name)=SOUNDEX('Lynn');
NAME (EXPR)
---------------------------------------- ------
Lynn L500
Lane L500
--- 2 row(s) selected.
>>select sha2('the original data', 0) from dual;
(EXPR)
----------------------------------------------------------------
38fd175fead1af0a96f46a9ed32d443857c16d0069c19e0cd7239340a1f94334
--- 1 row(s) selected.
>>select sha2('the original data', 128) from dual;
*** ERROR[15002] Internal parser error: The second operand expects 0, 224, 256, 384 or 512.
*** ERROR[8822] The statement was not prepared.
>>select sha2('the original data', 224) from dual;
(EXPR)
--------------------------------------------------------
82e1dae1bc1b2d862d30ddb6e16a717e5de6a143ab8c56c32a310a5b
--- 1 row(s) selected.
>>select sha2('the original data', 256) from dual;
(EXPR)
----------------------------------------------------------------
38fd175fead1af0a96f46a9ed32d443857c16d0069c19e0cd7239340a1f94334
--- 1 row(s) selected.
>>select sha2('the original data', 384) from dual;
(EXPR)
------------------------------------------------------------------------------------------------
bc0050f3ff7a540c74a42664c53a7f9fd485622374a43eb4a62cd84e48f483237bed50f6562f8c6e405454c4dbb0d4a3
--- 1 row(s) selected.
>>select sha2('the original data', 512) from dual;
(EXPR)
--------------------------------------------------------------------------------------------------------------------------------
18091a172d94aae1ae530ced02bde7f022229f446348f5d3b36989c2e6fd59f5252c7a7f8ecb71a74cb724448243ff71651f87aed155aee7340fad1b02567b60
--- 1 row(s) selected.
>>
>>-- Test LOG() for any radix with 2 parameters
>>select log(2,8) from dual;
(EXPR)
-------------------------
3.00000000000000000E+000
--- 1 row(s) selected.
>>select log(5,10) from dual;
(EXPR)
-------------------------
1.43067655807339328E+000
--- 1 row(s) selected.
>>select log(10,100) from dual;
(EXPR)
-------------------------
2.00000000000000000E+000
--- 1 row(s) selected.
>>select log(2) from dual;
(EXPR)
-------------------------
6.93147180559945344E-001
--- 1 row(s) selected.
>>select log(2.71828) from dual;
(EXPR)
-------------------------
9.99999327347282176E-001
--- 1 row(s) selected.
>>
>>-- TEST aes_encrypt/aes_decrypt
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
EC10187492EB7FB319E09AFF8806B1CA4F1382B41D23C00B118B3DDD9A9CFA02
--- 1 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
*** WARNING[8956] IV option ignored
(EXPR)
----------------------------------------------------------------
EC10187492EB7FB319E09AFF8806B1CA4F1382B41D23C00B118B3DDD9A9CFA02
--- 1 row(s) selected.
>>select aes_decrypt(X'EC10187492EB7FB319E09AFF8806B1CA4F1382B41D23C00B118B3DDD9A9CFA02', '1234567812345678') from dual;
(EXPR)
--------------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>select aes_decrypt(X'EC10187492EB7FB319E09AFF8806B1CA4F1382B41D23C00B118B3DDD9A9CFA02', '1234567812345678', '1234567812345678') from dual;
*** WARNING[8956] IV option ignored
(EXPR)
--------------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '1';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
0D9C1230E4B129757607D2E7C10805C9483C239A6A760FD1CECD8FC4D159E76F
--- 1 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
*** WARNING[8956] IV option ignored
(EXPR)
----------------------------------------------------------------
0D9C1230E4B129757607D2E7C10805C9483C239A6A760FD1CECD8FC4D159E76F
--- 1 row(s) selected.
>>select aes_decrypt(X'0D9C1230E4B129757607D2E7C10805C9483C239A6A760FD1CECD8FC4D159E76F', '1234567812345678') from dual;
(EXPR)
--------------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>select aes_decrypt(X'0D9C1230E4B129757607D2E7C10805C9483C239A6A760FD1CECD8FC4D159E76F', '1234567812345678', '1234567812345678') from dual;
*** WARNING[8956] IV option ignored
(EXPR)
--------------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '2';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
EFB4059C8975543222830627F7433861A173BECA31B8902540174253476CA985
--- 1 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
*** WARNING[8956] IV option ignored
(EXPR)
----------------------------------------------------------------
EFB4059C8975543222830627F7433861A173BECA31B8902540174253476CA985
--- 1 row(s) selected.
>>select aes_decrypt(X'EFB4059C8975543222830627F7433861A173BECA31B8902540174253476CA985', '1234567812345678') from dual;
(EXPR)
--------------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>select aes_decrypt(X'EFB4059C8975543222830627F7433861A173BECA31B8902540174253476CA985', '1234567812345678', '1234567812345678') from dual;
*** WARNING[8956] IV option ignored
(EXPR)
--------------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '3';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
12FF81AF2AB65E82DEFAE32D2CF0E7E5C14F90BAA80DF073608CE6ED0C47737F
--- 1 row(s) selected.
>>select aes_decrypt(X'12FF81AF2AB65E82DEFAE32D2CF0E7E5C14F90BAA80DF073608CE6ED0C47737F', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'12FF81AF2AB65E82DEFAE32D2CF0E7E5C14F90BAA80DF073608CE6ED0C47737F', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '4';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
1A1332592C987A79374609C89BB53F89A8F0B0A2B71E06B824894D1B95E1D953
--- 1 row(s) selected.
>>select aes_decrypt(X'1A1332592C987A79374609C89BB53F89A8F0B0A2B71E06B824894D1B95E1D953', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'1A1332592C987A79374609C89BB53F89A8F0B0A2B71E06B824894D1B95E1D953', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '5';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
E22114AB552C8613622ADD34B3FEDCE840A18C84FDEA9D6394F57A14F6DB2497
--- 1 row(s) selected.
>>select aes_decrypt(X'E22114AB552C8613622ADD34B3FEDCE840A18C84FDEA9D6394F57A14F6DB2497', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'E22114AB552C8613622ADD34B3FEDCE840A18C84FDEA9D6394F57A14F6DB2497', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '6';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
2A23CC234BC4175E6AE90793BFBD03E4D7F2921AC6032E45D1C0
--- 1 row(s) selected.
>>select aes_decrypt(X'2A23CC234BC4175E6AE90793BFBD03E4D7F2921AC6032E45D1C0', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'2A23CC234BC4175E6AE90793BFBD03E4D7F2921AC6032E45D1C0', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '7';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
09389E6E99083B18F50D2B4D14106CFCF9F9EEAD004929940C17
--- 1 row(s) selected.
>>select aes_decrypt(X'09389E6E99083B18F50D2B4D14106CFCF9F9EEAD004929940C17', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'09389E6E99083B18F50D2B4D14106CFCF9F9EEAD004929940C17', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '8';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
209F05F9B3E25391258A434F6FA9663F14886E9FF7E6C2CF5EF9
--- 1 row(s) selected.
>>select aes_decrypt(X'209F05F9B3E25391258A434F6FA9663F14886E9FF7E6C2CF5EF9', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'209F05F9B3E25391258A434F6FA9663F14886E9FF7E6C2CF5EF9', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '9';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
0C45C8DB6AE2F87AE36420AE711AC1121576FEECB26BD9B246AB
--- 1 row(s) selected.
>>select aes_decrypt(X'0C45C8DB6AE2F87AE36420AE711AC1121576FEECB26BD9B246AB', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'0C45C8DB6AE2F87AE36420AE711AC1121576FEECB26BD9B246AB', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '10';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
2C39ACEEBF859846261D8B1B712BBC7965F64889293C26CDFE61
--- 1 row(s) selected.
>>select aes_decrypt(X'2C39ACEEBF859846261D8B1B712BBC7965F64889293C26CDFE61', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'2C39ACEEBF859846261D8B1B712BBC7965F64889293C26CDFE61', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '11';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
6A3C1001A693B34DD2619B066413995BDEC5259A73E1E6D2DEBC
--- 1 row(s) selected.
>>select aes_decrypt(X'6A3C1001A693B34DD2619B066413995BDEC5259A73E1E6D2DEBC', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'6A3C1001A693B34DD2619B066413995BDEC5259A73E1E6D2DEBC', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '12';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
0CCE7F3282239C8853A5E704FC8A47901D8036903B6603DF43E5
--- 1 row(s) selected.
>>select aes_decrypt(X'0CCE7F3282239C8853A5E704FC8A47901D8036903B6603DF43E5', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'0CCE7F3282239C8853A5E704FC8A47901D8036903B6603DF43E5', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '13';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
2C6B7A87519F9E95E0F46EDA774152245E8DD142FC6CD57D57E2
--- 1 row(s) selected.
>>select aes_decrypt(X'2C6B7A87519F9E95E0F46EDA774152245E8DD142FC6CD57D57E2', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'2C6B7A87519F9E95E0F46EDA774152245E8DD142FC6CD57D57E2', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '14';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
6A0D74250CB7E54F4690801A7AD9CDB07435157353DAE7BC6203
--- 1 row(s) selected.
>>select aes_decrypt(X'6A0D74250CB7E54F4690801A7AD9CDB07435157353DAE7BC6203', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'6A0D74250CB7E54F4690801A7AD9CDB07435157353DAE7BC6203', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '15';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
0CCE7F3282239C8853A5E704FC8A4790B5D857207B417EFB52D8
--- 1 row(s) selected.
>>select aes_decrypt(X'0CCE7F3282239C8853A5E704FC8A4790B5D857207B417EFB52D8', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'0CCE7F3282239C8853A5E704FC8A4790B5D857207B417EFB52D8', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '16';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
2C6B7A87519F9E95E0F46EDA774152244B6E78427EC604C84868
--- 1 row(s) selected.
>>select aes_decrypt(X'2C6B7A87519F9E95E0F46EDA774152244B6E78427EC604C84868', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'2C6B7A87519F9E95E0F46EDA774152244B6E78427EC604C84868', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>cqd block_encryption_mode '17';
--- SQL operation complete.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_ENCRYPT
--- 0 row(s) selected.
>>select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
(EXPR)
----------------------------------------------------------------
6A0D74250CB7E54F4690801A7AD9CDB053213B2F461EA174E128
--- 1 row(s) selected.
>>select aes_decrypt(X'6A0D74250CB7E54F4690801A7AD9CDB053213B2F461EA174E128', '1234567812345678') from dual;
*** ERROR[8955] Incorrect parameter count in the call to function AES_DECRYPT
--- 0 row(s) selected.
>>select aes_decrypt(X'6A0D74250CB7E54F4690801A7AD9CDB053213B2F461EA174E128', '1234567812345678', '1234567812345678') from dual;
(EXPR)
--------------------------
abcdedfhijklmnopqrstuvwxyz
--- 1 row(s) selected.
>>
>>obey TEST006(optimizer_hints);
>>
>>insert into t006t9 values (1,2,3), (10,20,30);
--- 2 row(s) inserted.
>>prepare expl from
+>select operator, cardinality,
+> cast(substring(description, position('scan_type: ' in description), position('object_type: ' in description) - position('scan_type: ' in description)) as char(100)) scan_type
+>from table(explain(null, 'S'))
+>where operator like '%SCAN%';
--- SQL command prepared.
>>
>>prepare s from
+>select * from t006t9 <<+ index TRAFODION.SCH.T006T9IX1>> where b > 10;
--- SQL command prepared.
>>execute expl;
OPERATOR CARDINALITY SCAN_TYPE
------------------------------ --------------- ----------------------------------------------------------------------------------------------------
TRAFODION_INDEX_SCAN 3.3000000E+001 scan_type: subset scan of index TRAFODION.SCH.T006T9IX1(TRAFODION.SCH.T006T9)
TRAFODION_VSBB_SCAN 1.0000000E+000 scan_type: subset scan of table TRAFODION.SCH.T006T9
--- 2 row(s) selected.
>>execute s;
A B C
----------- ----------- -----------
10 20 30
--- 1 row(s) selected.
>>
>>prepare s from
+>select * from t006t9 <<+ index t006t9ix3>> where b > 10;
--- SQL command prepared.
>>execute expl;
OPERATOR CARDINALITY SCAN_TYPE
------------------------------ --------------- ----------------------------------------------------------------------------------------------------
TRAFODION_INDEX_SCAN 3.3000000E+001 scan_type: subset scan of index TRAFODION.SCH.T006T9IX3(TRAFODION.SCH.T006T9)
TRAFODION_VSBB_SCAN 1.0000000E+000 scan_type: subset scan of table TRAFODION.SCH.T006T9
--- 2 row(s) selected.
>>execute s;
A B C
----------- ----------- -----------
10 20 30
--- 1 row(s) selected.
>>
>>prepare s from
+>select * from t006t9 <<+ index t006t9>> where b > 10;
--- SQL command prepared.
>>execute expl;
OPERATOR CARDINALITY SCAN_TYPE
------------------------------ --------------- ----------------------------------------------------------------------------------------------------
TRAFODION_SCAN 3.3000000E+001 scan_type: subset scan of table TRAFODION.SCH.T006T9
--- 1 row(s) selected.
>>execute s;
A B C
----------- ----------- -----------
10 20 30
--- 1 row(s) selected.
>>
>>prepare s from
+>select a,b from t006t9 <<+ index TRAFODION.SCH.T006T9IX1>> where b > 10;
--- SQL command prepared.
>>execute expl;
OPERATOR CARDINALITY SCAN_TYPE
------------------------------ --------------- ----------------------------------------------------------------------------------------------------
TRAFODION_INDEX_SCAN 3.3000000E+001 scan_type: subset scan of index TRAFODION.SCH.T006T9IX1(TRAFODION.SCH.T006T9)
--- 1 row(s) selected.
>>execute s;
A B
----------- -----------
10 20
--- 1 row(s) selected.
>>
>>prepare s from
+>select a,b from t006t9 <<+ index TRAFODION.SCH.T006T9IX3 | cardinality 2000.0>> where b > 10;
--- SQL command prepared.
>>execute expl;
OPERATOR CARDINALITY SCAN_TYPE
------------------------------ --------------- ----------------------------------------------------------------------------------------------------
TRAFODION_INDEX_SCAN 2.0000000E+003 scan_type: subset scan of index TRAFODION.SCH.T006T9IX3(TRAFODION.SCH.T006T9)
--- 1 row(s) selected.
>>execute s;
A B
----------- -----------
10 20
--- 1 row(s) selected.
>>
>>prepare s from
+>update t006t9 <<+ index TRAFODION.SCH.T006T9IX1>> set c = 5 where b > 10;
--- SQL command prepared.
>>execute expl;
OPERATOR CARDINALITY SCAN_TYPE
------------------------------ --------------- ----------------------------------------------------------------------------------------------------
TRAFODION_INDEX_SCAN 3.3000000E+001 scan_type: subset scan of index TRAFODION.SCH.T006T9IX1(TRAFODION.SCH.T006T9)
TRAFODION_VSBB_SCAN 1.0000000E+000 scan_type: subset scan of table TRAFODION.SCH.T006T9
--- 2 row(s) selected.
>>execute s;
--- 1 row(s) updated.
>>
>>prepare s from
+>update t006t9 <<+ cardinality 2e4 | index sch.T006T9IX1>> as x set c = 5 where b = 10;
--- SQL command prepared.
>>execute expl;
OPERATOR CARDINALITY SCAN_TYPE
------------------------------ --------------- ----------------------------------------------------------------------------------------------------
TRAFODION_INDEX_SCAN 2.0000000E+004 scan_type: subset scan of index TRAFODION.SCH.T006T9IX1(TRAFODION.SCH.T006T9 X)
TRAFODION_VSBB_SCAN 7.1000000E-003 scan_type: subset scan of table TRAFODION.SCH.T006T9 X
--- 2 row(s) selected.
>>execute s;
--- 0 row(s) updated.
>>
>>prepare s from
+>update t006t9 <<+ index TRAFODION.SCH.T006T9IX3 | cardinality 1e7>> set c = 5 where b > 10;
--- SQL command prepared.
>>execute expl;
OPERATOR CARDINALITY SCAN_TYPE
------------------------------ --------------- ----------------------------------------------------------------------------------------------------
TRAFODION_INDEX_SCAN 1.0000000E+007 scan_type: subset scan of index TRAFODION.SCH.T006T9IX3(TRAFODION.SCH.T006T9)
TRAFODION_VSBB_SCAN 1.0000000E+000 scan_type: subset scan of table TRAFODION.SCH.T006T9
--- 2 row(s) selected.
>>execute s;
--- 1 row(s) updated.
>>
>>prepare s from
+>update t006t9 <<+ index TRAFODION.SCH.T006T9IX3>> set c = 5 where b = 10;
--- SQL command prepared.
>>execute expl;
OPERATOR CARDINALITY SCAN_TYPE
------------------------------ --------------- ----------------------------------------------------------------------------------------------------
TRAFODION_INDEX_SCAN 1.0000000E+001 scan_type: subset scan of index TRAFODION.SCH.T006T9IX3(TRAFODION.SCH.T006T9)
TRAFODION_VSBB_SCAN 4.0000000E-001 scan_type: subset scan of table TRAFODION.SCH.T006T9
--- 2 row(s) selected.
>>execute s;
--- 0 row(s) updated.
>>
>>prepare s from
+>delete from t006t9 <<+ index TRAFODION.SCH.T006T9IX2>> where c > 10;
--- SQL command prepared.
>>execute expl;
OPERATOR CARDINALITY SCAN_TYPE
------------------------------ --------------- ----------------------------------------------------------------------------------------------------
TRAFODION_INDEX_SCAN 3.3000000E+001 scan_type: subset scan of index TRAFODION.SCH.T006T9IX2(TRAFODION.SCH.T006T9)
TRAFODION_VSBB_SCAN 1.0000000E+000 scan_type: subset scan of table TRAFODION.SCH.T006T9
--- 2 row(s) selected.
>>execute s;
--- 0 row(s) deleted.
>>
>>prepare s from
+>delete from t006t9 <<+ index TRAFODION.SCH.T006T9IX2 | selectivity 0.03 >> where c = 10;
--- SQL command prepared.
>>execute expl;
OPERATOR CARDINALITY SCAN_TYPE
------------------------------ --------------- ----------------------------------------------------------------------------------------------------
TRAFODION_INDEX_SCAN 3.0000000E+000 scan_type: subset scan of index TRAFODION.SCH.T006T9IX2(TRAFODION.SCH.T006T9)
TRAFODION_VSBB_SCAN 6.6666668E-001 scan_type: subset scan of table TRAFODION.SCH.T006T9
--- 2 row(s) selected.
>>execute s;
--- 0 row(s) deleted.
>>
>>prepare s from
+>delete from t006t9 <<+ index TRAFODION.SCH.T006T9IX4>> where c > 10;
--- SQL command prepared.
>>execute expl;
OPERATOR CARDINALITY SCAN_TYPE
------------------------------ --------------- ----------------------------------------------------------------------------------------------------
TRAFODION_INDEX_SCAN 3.3000000E+001 scan_type: subset scan of index TRAFODION.SCH.T006T9IX4(TRAFODION.SCH.T006T9)
TRAFODION_VSBB_SCAN 1.0000000E+000 scan_type: subset scan of table TRAFODION.SCH.T006T9
--- 2 row(s) selected.
>>execute s;
--- 0 row(s) deleted.
>>
>>prepare s from
+>delete [first 10] from t006t9 <<+ index TRAFODION.SCH.T006T9IX4>> where c = 10;
--- SQL command prepared.
>>execute expl;
OPERATOR CARDINALITY SCAN_TYPE
------------------------------ --------------- ----------------------------------------------------------------------------------------------------
TRAFODION_INDEX_SCAN 1.0000000E+001 scan_type: subset scan of index TRAFODION.SCH.T006T9IX4(TRAFODION.SCH.T006T9)
TRAFODION_VSBB_SCAN 4.0000000E-001 scan_type: subset scan of table TRAFODION.SCH.T006T9
--- 2 row(s) selected.
>>execute s;
--- 0 row(s) deleted.
>>
>>prepare s from
+>delete from t006t9 <<+ index t006t9ix2, t006t9ix4>> where c > 10;
--- SQL command prepared.
>>-- this can pick either index
>>with expl as
+>(select operator, cardinality,
+> cast(substring(description, position('scan_type: ' in description), position('object_type: ' in description) - position('scan_type: ' in description)) as char(100)) scan_type
+> from table(explain(null, 'S'))
+> where operator like '%SCAN%')
+>select count(*) from expl where scan_type like '%T006T9IX2%' or scan_type like '%T006T9IX4%';
(EXPR)
--------------------
1
--- 1 row(s) selected.
>>execute s;
--- 0 row(s) deleted.
>>
>>prepare s from
+>merge into t006t5
+> using (select * from t006t9 <<+index SCH.t006t9ix3 | cardinality 1e7>>) as src
+> on t006t5.a = src.a
+>when matched
+> then update set b='abc'
+>when not matched
+> then insert values (src.a, 'cde', date '2000-01-01');
--- SQL command prepared.
>>execute expl;
OPERATOR CARDINALITY SCAN_TYPE
------------------------------ --------------- ----------------------------------------------------------------------------------------------------
TRAFODION_INDEX_SCAN 1.0000000E+007 scan_type: subset scan of index TRAFODION.SCH.T006T9IX3(TRAFODION.SCH.T006T9)
--- 1 row(s) selected.
>>execute s;
--- 2 row(s) updated.
>>
>>-- negative tests
>>prepare s from
+>select * from t006t9 <<+ index nosuchindex>> where b > 10;
*** WARNING[4371] Only 0 of the 1 indexes in the hint match actual indexes of the table TRAFODION.SCH.T006T9.
--- SQL command prepared.
>>execute s;
A B C
----------- ----------- -----------
10 20 5
--- 1 row(s) selected.
>>
>>cqd index_hint_warnings 'off';
--- SQL operation complete.
>>prepare s from
+>select * from t006t9 <<+ index nosuchindex>> where b > 10;
--- SQL command prepared.
>>execute s;
A B C
----------- ----------- -----------
10 20 5
--- 1 row(s) selected.
>>log;