core/sql/regress/compGeneral/TEST006

-- Test: TEST006 (compGeneral)
-- Functionality: Tests to enhance code coverage
-- Expected files: EXPECTED006
--
-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
 
set schema $$TEST_SCHEMA$$;

obey TEST006(clean_up);
sh rm mml.log;
log LOG006 clear;
obey TEST006(create_tables);
obey TEST006(negative_tests);
obey TEST006(positive_tests);
obey TEST006(analyzer_tests);
obey TEST006(defaults_tests);
obey TEST006(misc_tests);
obey TEST006(mode_special_tests);
obey TEST006(olap_functions);
log;
obey TEST006(clean_up);

exit;

?section clean_up
drop view t006t1_v;
drop table t006t1;
drop table t006t2;
drop table t006t3;
drop table t006t4;
drop table t006t5;
drop table t006t6;
drop table t006t7;
drop table t006t8;
drop schema $$TEST_CATALOG$$.TEST_PUBLIC_SCHEMA cascade;
-- used for large scrope rules tests
drop table x1 cascade;
drop table x2 cascade;
drop table x3 cascade;
drop mv mvx cascade;
drop table t006tao;
drop table t006_employees;
drop table t006taoc;
-- Used for builtin function SOUNDEX() tests
drop table t006emp1;
drop table t006emp2;

?section create_tables

create table t006t1 (a varchar (200));

create table t006t2 (a int);

create table t006t3 (a int not null);

create table t006t4 (a char(10) not casespecific, b char(10));

create table t006t5 (a int     not null,
                     b char(3) not null,
                     c date    not null,
                     primary key(a));

create table t006t6 (a char(10) character set ucs2);

create view t006t1_v as select a from t006t1;

create table t006t7 (a float);

create table t006t8 (a time);

-- used for large scope rules
create table x1 (a int not null, b int not null primary key);
create table x2 (c int not null primary key, d int);
create table x3 (e int not null, f int) store by (e);

insert into x1 values (1,1),(2,2);
insert into x2 values (1,1),(2,2),(3,3),(4,4),(5,5);

create mv mvx refresh on request initialize on create as select a,sum(b) s_b
from x1 group by a;

update statistics for table x1 on every column;
update statistics for table x2 on every column;
update statistics for table x3 on every column;

create table t006tao(a int, b int, c int);

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

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

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

create table t006taoc(a char(10), b char(10), c varchar(10));

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

-- Used for builtin function SOUNDEX() tests
create table t006emp1(name varchar(20));

create table t006emp2(name varchar(20) character set UCS2);

insert into t006emp1 values
('Smith'),
('Lynn'),
('Ruoyu'),
('John'),
('Lane')
;

insert into t006emp2 values
('Smith'),
('Lynn'),
('Ruoyu'),
('John'),
('Lane')
;

?section negative_tests

-- Error 3233
prepare xx from update with no rollback t006t4 set a = b;

-- Error 3416
prepare xx from select coalesce(a, NULL) from t006t1;

-- Error 4009
prepare xx from select sum(max(a)) from t006t1;

-- Error 4022
prepare xx from insert into t006t2(a, a) select a, a from t006t3;

-- Error 4034
prepare xx from select * from t006t1 where a = 1234 collate default;

-- Error 4036
prepare xx from select year(a) from t006t2;

-- Error 4039
prepare xx from insert into t006t3 values ('a');

-- Error 4043
prepare xx from select * from t006t1 where to_number(1112) = A;
prepare xx from select tokenstr('a', a) from t006t2;
prepare xx from select queryid_extract(a, b) from t006t5;
prepare xx from select os_userid(a) from t006t2;
prepare xx from select convertfromhex(a) from t006t2;
prepare xx from select char_length(a) from t006t2;
prepare xx from select lower(a) from t006t2;
prepare xx from select octet_length(a) from t006t2;

-- Error 4045
prepare xx from select zeroifnull(a) from t006t1;
prepare xx from select nullifzero(a) from t006t1;
prepare xx from select abs(a) from t006t1;
prepare xx from select user(a) from t006t1;
prepare xx from select ~a from t006t1;
prepare xx from select sin(a) from t006t1;

-- Error 4046
prepare xx from select ~(a) from t006t7;
prepare xx from select bitextract(a, a, a) from t006t7;
prepare xx from select mod(a,a) from t006t1;
prepare xx from select mod(a,a) from t006t7;
prepare xx from select repeat('a', a) from t006t7;

-- Error 4047
prepare xx from select round(3.224, 2.3) from t006t1;
prepare xx from select round(3.224, 3, 2.3) from t006t1;
prepare xx from select round(t006t2.a, 1.1) from t006t1, t006t2;
prepare xx from select ~(1.1) from t006t1;
prepare xx from select bitextract(a, a, 1.1) from t006t2;
prepare xx from select mod(a,1.1) from t006t2;
prepare xx from select repeat(a, 1.1) from t006t1;

control query default JDBC_PROCESS 'ON';
prepare xx from select locate(a,a,1.1) from t006t1;
control query default JDBC_PROCESS reset;

prepare xx from select substring(a,1.1) from t006t1;
prepare xx from select substring(a,1,1.1) from t006t1;

-- Error 4051
prepare xx from select repeat(a, a) from t006t2;
prepare xx from select substring(a,a) from t006t2;

-- Error 4052
prepare xx from select a^b from t006t5;
prepare xx from select 1**a from t006t1;
prepare xx from select substring(a,a) from t006t1;
prepare xx from select round(t006t2.a, t006t1.a) from t006t1, t006t2;
prepare xx from select repeat(a, a) from t006t1;
prepare xx from select offset(a,a,a) from t006t1;

-- Error 4053
prepare xx from select bitextract(a, a, b) from t006t5;
prepare xx from select substring(a,1,a) from t006t1;

control query default JDBC_PROCESS 'ON';
prepare xx from select locate(a,a,a) from t006t1;
control query default JDBC_PROCESS reset;

prepare xx from select insert(a, 1, 2.1, 3) from t006t2;
prepare xx from select round(t006t2.a, t006t2.a, t006t1.a) from t006t1, t006t2;
prepare xx from select offset(a,1,a) from t006t1;
prepare xx from select movingcount(a, 1, a) from t006t1 sequence by a;

-- Error 4059
prepare xx from select round(a) from t006t1;
prepare xx from select trunc(a) from t006t1;
prepare xx from select a**a from t006t1;

-- Error 4067
prepare xx from select * from table (explain(NULL, 5));

-- Error 4068
prepare xx from select convertfromhex(cast(a as char(1))) from t006t1;

-- Error 4070
prepare xx from select converttimestamp(a) from t006t1;

-- Error 4071
prepare xx from select * from t006t1 where last_day(A) = A;

prepare xx from select juliantimestamp(a) from t006t1;

-- Error 4073
prepare xx from select * from t006t1 where a = (select a from t006t3) collate default;

-- Error 4078
prepare xx from select variance(a, b) over (order by a) from t006t2;

-- Error 4106
prepare xx from select code_value(ascii(a)) from t006t6;

-- Error 4122
prepare xx from insert into t006t3 values (NULL);

-- Error 4131
alter table t006t1 add constraint t006t1_check check (t006t1.A = current_time);

-- Error 4132
alter table t006t1 add constraint t006t1_check check (t006t1.A = current_user);

-- Error 4185
prepare xx from select sum(a) from t006t1 group by 1;

-- Error 4310
prepare xx from select * from t006t1 where user(a) = a;
prepare xx from select * from t006t1 where a = (select user(a) from t006t2);

-- Error 4312
prepare xx from update HP_SYSTEM_CATALOG.mxcs_schema.ASSOC2DS set assoc_id = 100;

-- Error 4391
prepare xx from select variance(a) over (order by ?p) from t006t2;

-- Error 8403
prepare xx from select substring(a,1,-1) from t006t1;

?section positive_tests

-- Non-error coverage cases
prepare xx from select queryid_extract(a, b) from t006t4;
prepare xx from select round(3.224, 3, 2) from t006t1;
prepare xx from select user(a) from t006t1;

control query default MODE_SPECIAL_4 'ON';
prepare xx from select * from t006t2 where to_date(A, '99:99:99:99') = A;
control query default MODE_SPECIAL_4 reset;

prepare xx from select current_running from t006t1;
prepare xx from select position(a in a) from t006t4;
prepare xx from select replace(a, a, a) from t006t4;

prepare xx from select date_trunc('YEAR',a) from t006t1;
prepare xx from select date_trunc('YEAR',a) from t006t1;
prepare xx from select date_trunc('month',a) from t006t1;
prepare xx from select date_trunc('century',a) from t006t1;
prepare xx from select datediff(year,a, a) from t006t1;
prepare xx from select datediff(month,a, a) from t006t1;
prepare xx from select datediff(hour,a, a) from t006t1;
prepare xx from select datediff(minute,a, a) from t006t1;
prepare xx from select datediff(second,a, a) from t006t1;
prepare xx from select datediff(quarter,c, c) from t006t5;

prepare xx from 
select 
decode(CAST (( a + 3) / 4 AS INT ) ,
0,0.04,
1,0.04,
0.06) from t006t3;

prepare xx from select count(*) from t006t1 where lpad(a,0,'') = 'aa';
prepare xx from select count(*) from t006t1 where rpad(a,0,'') = 'aa';

prepare xx from select truncate(a, 2) from t006t3;
prepare xx from select os_userid(a) from t006t1;

control query default ODBC_PROCESS 'ON';
control query default COMP_BOOL_173 'ON';
prepare xx from insert into t006t3 values (?p);
control query default ODBC_PROCESS reset;
control query default COMP_BOOL_173 reset;

control query default INFER_CHARSET 'ON';
prepare xx from select replace(a, a, a) from t006t1;
prepare xx from select * from t006t1 where ?p like a;
prepare xx from select octet_length(a) from t006t1;
prepare xx from select locate(a,a) from t006t1;
prepare xx from select min(a) from t006t1;
control query default INFER_CHARSET reset;

prepare xx from select lower(a) from t006t4;

control query default PUBLIC_SCHEMA_NAME 'test_public_schema';
create schema $$TEST_CATALOG$$.TEST_PUBLIC_SCHEMA;
Create table test_public_schema.t_pub1 (a int);
prepare xx from select * from t_pub1;
drop schema $$TEST_CATALOG$$.TEST_PUBLIC_SCHEMA cascade;
control query default PUBLIC_SCHEMA_NAME reset;

control query default ALLOW_ISOLATION_LEVEL_IN_CREATE_VIEW 'OFF';
prepare xx from select * from t006t1_v;
control query default ALLOW_ISOLATION_LEVEL_IN_CREATE_VIEW reset;

control query default COMP_BOOL_192 'ON';
prepare xx from select * from t006t2 full outer join t006t3 on t006t2.a = t006t3.a;
control query default COMP_BOOL_192 reset;

prepare xx from select * from table (explain(_UCS2'NULL', _UCS2's'));

control query default COMP_BOOL_203 'ON';
prepare xx from select rank(a) from t006t1 sequence by a;
control query default COMP_BOOL_203 reset;

control query default MULTI_JOIN_CONSIDER_INITIAL_JOIN_ORDER 'ON';
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;
control query default MULTI_JOIN_CONSIDER_INITIAL_JOIN_ORDER reset;

control query default HIDE_INDEXES 'KEYINDEXES';
prepare xx from select * from t006t1;
control query default HIDE_INDEXES reset;

prepare xx from select nullifzero(a) from t006t3;
prepare xx from select converttobits(a) from t006t1;
prepare xx from select queryid_extract(a, a) from t006t1;
prepare xx from select tokenstr('a', a) from t006t1;

prepare xx from select cast(?p as char(3) not null) from t006t1;

control query default OVERRIDE_SYSKEY 'ON';
prepare xx from select * from t006t7;
control query default OVERRIDE_SYSKEY reset;

control query default VALIDATE_RFORK_REDEF_TS 'ON';
prepare xx from select * from t006t7;
control query default VALIDATE_RFORK_REDEF_TS reset;

control query default COMP_BOOL_108 'OFF';
prepare xx from select * from t006t2, t006t3 where log(t006t2.a)  =  log(t006t3.a);
control query default COMP_BOOL_108 reset;

prepare xx from select * from t006t8 t5_1, t006t8 t5_2 where hour(t5_1.a) = minute(t5_2.a);
prepare xx from select * from t006t8 t5_1, t006t8 t5_2 where second(t5_1.a) = minute(t5_2.a);

control query default MULTI_JOIN_CONSIDER_INITIAL_JOIN_ORDER 'ON';
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;
control query default MULTI_JOIN_CONSIDER_INITIAL_JOIN_ORDER reset;

prepare xx from select * from t006t5
where c >= current_date - interval '12' day and c < current_date;

control query default HIST_OPTIMISTIC_CARD_OPTIMIZATION '3';
prepare xx from select * from t006t5
where a > 15;
control query default HIST_OPTIMISTIC_CARD_OPTIMIZATION reset;

control query default COMP_BOOL_42 'OFF';
prepare xx from select * from t006t2, t006t3 where t006t2.a = t006t3.a;
control query default COMP_BOOL_42 reset;

control query default COMP_BOOL_8 'ON';
control query default ASM_PRECOMPUTE 'ON';
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;
control query default COMP_BOOL_8 reset;
control query default ASM_PRECOMPUTE reset;

control query default COMP_BOOL_119 'ON';
prepare xx from select * from t006t2, t006t3, t006t5 where t006t2.a=t006t3.a and t006t3.a = t006t5.a;
control query default COMP_BOOL_119 reset;

control query default COMP_BOOL_25 'ON';
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 
;
control query default COMP_BOOL_25 reset;

control query default OPTIMIZER_HEURISTIC_3 'ON';
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 
;
control query default OPTIMIZER_HEURISTIC_3 reset;

control query default COMP_STRING_1 'T5';
prepare xx from select * from t006t2, t006t3, t006t5 as t5 where t006t2.a=t006t3.a and t006t3.a = t5.a;
control query default COMP_STRING_1 reset;

-- coverage for Analyzer and largeScopeRules
?section analyzer_tests

-- coverage for Analyzer.cpp method QueryAnalysis::newTableAnalysis
cqd COMP_BOOL_24 'ON';
prepare st1 from select a,sum(d) s_d from x1,x2 where b=c group by a;
cqd COMP_BOOL_24 reset;

-- coverage for Analyzer.cpp method QueryAnalysis::setHistogramsToDisplay
cqd HIST_ROOT_NODE '1,2,3,4';
prepare st1 from select a,sum(d) s_d from x1,x2 where b=c group by a;
cqd HIST_ROOT_NODE reset;

-- coverage for Analyzer.cpp method QueryAnalysis::computeTablesJoinedToLargestTable
cqd COMP_BOOL_119 'ON';
prepare st1 from select a,sum(d) s_d from x1,x2 where b=c group by a;
cqd COMP_BOOL_119 reset;

-- coverage for Analyzer.cpp method JBBSubsetAnalysis::isAStarPattern
cqd COMP_BOOL_12 'ON';
prepare st1 from select a,sum(d) s_d from x1,x2 where b=c group by a;
cqd COMP_BOOL_12 reset;

-- coverage for Analyzer.cpp method JBB::addJBBC
prepare st1 from select a,sum(b) s_b from x1 group by a;

-- coverage for LargeScopeRules.cpp MJEnumRule::nextSubstitute
cqd COMP_INT_79 '2';
prepare st1 from select a ,b from x1,x2,x3 where b=e;
cqd COMP_INT_79 reset;
cqd COMP_INT_51 '2';
prepare st1 from select a ,b from x1,x2,x3 where b=e;
cqd COMP_INT_51 reset;

?section 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 EID_TRACE_STR 'abc';
cqd EID_TRACE_STR reset;

cqd POS_ABSOLUTE_MAX_TABLE_SIZE '123,456,789';
cqd POS_ABSOLUTE_MAX_TABLE_SIZE reset;

cqd DEFAULT_SCHEMA_NAMETYPE 'user';
cqd DEFAULT_SCHEMA_NAMETYPE reset;

?section misc_tests

-- Memory monitor infrastructure
control query default MEMORY_MONITOR 'ON';
control query default MEMORY_MONITOR_LOG_INSTANTLY 'on';
control query default MEMORY_MONITOR_LOGFILE 'mml.log';
prepare xx from select * from t006t2;
control query default MEMORY_MONITOR 'OFF';

?section mode_special_tests
-- Error 4026
control query default MODE_SPECIAL_1 'ON';
control query default R2_HALLOWEEN_SUPPORT 'ON';
prepare s1 from insert into t006t3 select * from t006t3 where a in (select a from t006t3);
control query default R2_HALLOWEEN_SUPPORT reset;

prepare xx from select substring(a for 3) from t006t1;
prepare xx from select substring(a for 3) from t006t3;
prepare xx from select substring(c for 3) from t006t5;
prepare xx from select a from t006t1 group by 1 order by 1;
prepare xx from select a from t006t1 group by A order by A;
prepare xx from select * from t006t5 where c = 1231999;
prepare xx from select * from t006t5 where a = date'12/31/1999';
prepare xx from select * from t006t5 where cast(a as interval day) = 1;
prepare xx from select * from t006t5 where 1 = interval '1' day;

prepare xx from select rows since(a = 11, a) from t006t1 sequence by a;
control query default MODE_SPECIAL_1 reset;

control query default MODE_SPECIAL_4 'ON';
prepare xx from select * from t006t1 where to_date(A, '99:99:99:99') = A;

-- Error 4065
prepare xx from select * from t006t1 where to_date(A, '201005') = A;
prepare xx from select * from t006t2 where to_date(A, 'YYYYMM') = A;
control query default MODE_SPECIAL_4 reset;


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

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;

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

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;

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

select emp_name, dept_name, salary_amt, 
lag(salary_amt, 0) over (partition by dept_name order by emp_name)
from T006_EMPLOYEES;

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

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;
execute xx using 2;

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;

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;

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;

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;

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;

select a, b, lag(b, 2) 
over(partition by a order by b)
from t006taoc <<+ cardinality 10e6 >>
order by 1, 2, 3;

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;

select md5('10'), crc32(10), sha('10') from dual;
select md5(10) from dual;
select sha1(10) from dual;

select SOUNDEX('Jon') from dual;
select SOUNDEX('Roy') from dual;
select SOUNDEX('Lynn') from dual;

select name, SOUNDEX(name) from t006emp1 where SOUNDEX(name)=SOUNDEX('Jon');
select name, SOUNDEX(name) from t006emp1 where SOUNDEX(name)=SOUNDEX('Roy');
select name, SOUNDEX(name) from t006emp1 where SOUNDEX(name)=SOUNDEX('Lynn');

select name, SOUNDEX(name) from t006emp2 where SOUNDEX(name)=SOUNDEX('Jon');
select name, SOUNDEX(name) from t006emp2 where SOUNDEX(name)=SOUNDEX('Roy');
select name, SOUNDEX(name) from t006emp2 where SOUNDEX(name)=SOUNDEX('Lynn');
select sha2('the original data', 0) from dual;
select sha2('the original data', 128) from dual;
select sha2('the original data', 224) from dual;
select sha2('the original data', 256) from dual;
select sha2('the original data', 384) from dual;
select sha2('the original data', 512) from dual;

-- Test LOG() for any radix with 2 parameters
select log(2,8) from dual;
select log(5,10) from dual;
select log(10,100) from dual;
select log(2) from dual;
select log(2.71828) from dual;

-- TEST aes_encrypt/aes_decrypt
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'EC10187492EB7FB319E09AFF8806B1CA4F1382B41D23C00B118B3DDD9A9CFA02', '1234567812345678') from dual;
select aes_decrypt(X'EC10187492EB7FB319E09AFF8806B1CA4F1382B41D23C00B118B3DDD9A9CFA02', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '1';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'0D9C1230E4B129757607D2E7C10805C9483C239A6A760FD1CECD8FC4D159E76F', '1234567812345678') from dual;
select aes_decrypt(X'0D9C1230E4B129757607D2E7C10805C9483C239A6A760FD1CECD8FC4D159E76F', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '2';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'EFB4059C8975543222830627F7433861A173BECA31B8902540174253476CA985', '1234567812345678') from dual;
select aes_decrypt(X'EFB4059C8975543222830627F7433861A173BECA31B8902540174253476CA985', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '3';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'12FF81AF2AB65E82DEFAE32D2CF0E7E5C14F90BAA80DF073608CE6ED0C47737F', '1234567812345678') from dual;
select aes_decrypt(X'12FF81AF2AB65E82DEFAE32D2CF0E7E5C14F90BAA80DF073608CE6ED0C47737F', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '4';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'1A1332592C987A79374609C89BB53F89A8F0B0A2B71E06B824894D1B95E1D953', '1234567812345678') from dual;
select aes_decrypt(X'1A1332592C987A79374609C89BB53F89A8F0B0A2B71E06B824894D1B95E1D953', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '5';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'E22114AB552C8613622ADD34B3FEDCE840A18C84FDEA9D6394F57A14F6DB2497', '1234567812345678') from dual;
select aes_decrypt(X'E22114AB552C8613622ADD34B3FEDCE840A18C84FDEA9D6394F57A14F6DB2497', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '6';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'2A23CC234BC4175E6AE90793BFBD03E4D7F2921AC6032E45D1C0', '1234567812345678') from dual;
select aes_decrypt(X'2A23CC234BC4175E6AE90793BFBD03E4D7F2921AC6032E45D1C0', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '7';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'09389E6E99083B18F50D2B4D14106CFCF9F9EEAD004929940C17', '1234567812345678') from dual;
select aes_decrypt(X'09389E6E99083B18F50D2B4D14106CFCF9F9EEAD004929940C17', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '8';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'209F05F9B3E25391258A434F6FA9663F14886E9FF7E6C2CF5EF9', '1234567812345678') from dual;
select aes_decrypt(X'209F05F9B3E25391258A434F6FA9663F14886E9FF7E6C2CF5EF9', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '9';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'0C45C8DB6AE2F87AE36420AE711AC1121576FEECB26BD9B246AB', '1234567812345678') from dual;
select aes_decrypt(X'0C45C8DB6AE2F87AE36420AE711AC1121576FEECB26BD9B246AB', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '10';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'2C39ACEEBF859846261D8B1B712BBC7965F64889293C26CDFE61', '1234567812345678') from dual;
select aes_decrypt(X'2C39ACEEBF859846261D8B1B712BBC7965F64889293C26CDFE61', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '11';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'6A3C1001A693B34DD2619B066413995BDEC5259A73E1E6D2DEBC', '1234567812345678') from dual;
select aes_decrypt(X'6A3C1001A693B34DD2619B066413995BDEC5259A73E1E6D2DEBC', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '12';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'0CCE7F3282239C8853A5E704FC8A47901D8036903B6603DF43E5', '1234567812345678') from dual;
select aes_decrypt(X'0CCE7F3282239C8853A5E704FC8A47901D8036903B6603DF43E5', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '13';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'2C6B7A87519F9E95E0F46EDA774152245E8DD142FC6CD57D57E2', '1234567812345678') from dual;
select aes_decrypt(X'2C6B7A87519F9E95E0F46EDA774152245E8DD142FC6CD57D57E2', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '14';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'6A0D74250CB7E54F4690801A7AD9CDB07435157353DAE7BC6203', '1234567812345678') from dual;
select aes_decrypt(X'6A0D74250CB7E54F4690801A7AD9CDB07435157353DAE7BC6203', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '15';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'0CCE7F3282239C8853A5E704FC8A4790B5D857207B417EFB52D8', '1234567812345678') from dual;
select aes_decrypt(X'0CCE7F3282239C8853A5E704FC8A4790B5D857207B417EFB52D8', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '16';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'2C6B7A87519F9E95E0F46EDA774152244B6E78427EC604C84868', '1234567812345678') from dual;
select aes_decrypt(X'2C6B7A87519F9E95E0F46EDA774152244B6E78427EC604C84868', '1234567812345678', '1234567812345678') from dual;

cqd block_encryption_mode '17';
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678')) from dual;
select converttohex(aes_encrypt('abcdedfhijklmnopqrstuvwxyz', '1234567812345678', '1234567812345678')) from dual;
select aes_decrypt(X'6A0D74250CB7E54F4690801A7AD9CDB053213B2F461EA174E128', '1234567812345678') from dual;
select aes_decrypt(X'6A0D74250CB7E54F4690801A7AD9CDB053213B2F461EA174E128', '1234567812345678', '1234567812345678') from dual;