Google Git
Sign in
apache/trafodion/master/./core/sql/regress/charsets/EXPECTED310
blob: 58e1468163316c13cbccae56aaa795a76fd3091d [file]
>>
>>obey test310(ddl);
>>
>>create schema cs310s;
--- SQL operation complete.
>>set schema cs310s;
--- SQL operation complete.
>>
>>
>>obey test310(icat_tests);
>>
>>--
>>-- NOTE: CHAR() function is giving non-deterministic results in R2.4
>>-- CONSEQUENTLY, TESTS USING CHAR() FUNCTION ARE COMMENTED OUT.
>>----------------------------------------------------------------------
>>--
>>-- Tests for ASCII function
>>--
>>create table cs310t1 (a1 char(8) character set iso88591) no partition;
--- SQL operation complete.
>>insert into cs310t1 values('a1a1');
--- 1 row(s) inserted.
>>insert into cs310t1 values('u1u1');
--- 1 row(s) inserted.
>>create table cs310t2 (u1 char(8) character set ucs2) no partition;
--- SQL operation complete.
>>insert into cs310t2 values('a1a1');
--- 1 row(s) inserted.
>>insert into cs310t2 values('u1u1');
--- 1 row(s) inserted.
>>--
>>select * from cs310t1 order by 1;
A1
--------
a1a1
u1u1
--- 2 row(s) selected.
>>select ascii('a') from cs310t1 order by 1;
(EXPR)
----------
97
97
--- 2 row(s) selected.
>>select ascii(CHAR(97)) from cs310t1 order by 1;
(EXPR)
----------
97
97
--- 2 row(s) selected.
>> -- SEE NOTE ABOVE
>>select ascii(CHAR(97, ISO88591)) from cs310t1 order by 1;
(EXPR)
----------
97
97
--- 2 row(s) selected.
>> -- SEE NOTE ABOVE
>>-- Following 4 queries should give error since ASCII doesn't work on UCS2
>>select ascii(CHAR(97, UCS2)) from cs310t1 order by 1;
*** ERROR[4106] The character set for the operand of function ASCII must be ISO88591.
*** ERROR[8822] The statement was not prepared.
>>select ascii( _ucs2'a') from cs310t1 order by 1;
*** ERROR[4106] The character set for the operand of function ASCII must be ISO88591.
*** ERROR[8822] The statement was not prepared.
>>select ascii(u1) from cs310t2 order by 1;
*** ERROR[4106] The character set for the operand of function ASCII must be ISO88591.
*** ERROR[8822] The statement was not prepared.
>>select ascii( TRANSLATE(_iso88591'abcdefghijklmnop' using Iso88591ToUCS2) ) from cs310t2 order by 1;
*** ERROR[4106] The character set for the operand of function ASCII must be ISO88591.
*** ERROR[8822] The statement was not prepared.
>>--
>>select * from cs310t2, cs310t1 order by 1,2;
U1 A1
---------------- --------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>>select * from cs310t2, cs310t1 where 'a' = CHAR(ASCII('a1a1')) order by 1,2;
U1 A1
---------------- --------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>> -- SEE NOTE ABOVE
>>select * from cs310t2, cs310t1 where 'u' = CHAR(ASCII('u1u1'),UCS2) order by 1,2;
U1 A1
---------------- --------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>> -- SEE NOTE ABOVE
>>select * from cs310t2, cs310t1 where 'u' = lower(CHAR(ASCII(upper(a1)))) order by 1,2;
U1 A1
---------------- --------
a1a1 u1u1
u1u1 u1u1
--- 2 row(s) selected.
>> -- SEE NOTE ABOVE
>>select * from cs310t2, cs310t1 where CHAR(ASCII('a1a1')) = 'a' order by 1,2;
U1 A1
---------------- --------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>> -- SEE NOTE ABOVE
>>select * from cs310t2, cs310t1 where CHAR(ASCII('u1u1'),UCS2) = 'u' order by 1,2;
U1 A1
---------------- --------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>> -- SEE NOTE ABOVE
>>select * from cs310t2, cs310t1 where lower(CHAR(ASCII(upper(a1)))) = 'u' order by 1,2;
U1 A1
---------------- --------
a1a1 u1u1
u1u1 u1u1
--- 2 row(s) selected.
>> -- SEE NOTE ABOVE
>>--
>>select ascii((select 'a1a1' from cs310t1 where a1='u1u1')) from cs310t1 order by 1;
(EXPR)
----------
97
97
--- 2 row(s) selected.
>>select ascii((select 'a1a1' from cs310t2 where u1='a1a1')) from cs310t1 order by 1;
(EXPR)
----------
97
97
--- 2 row(s) selected.
>>select a1 from cs310t1 where ascii(a1) = ascii((select min(a1) from cs310t1)) order by 1;
A1
--------
a1a1
--- 1 row(s) selected.
>>select a1 from cs310t1 where ascii(a1) = ascii((select TRANSLATE(max(u1) using UCS2toISO88591) from cs310t2)) order by 1;
A1
--------
u1u1
--- 1 row(s) selected.
>>select a1 from cs310t1 where ascii(a1) = ascii((select max(TRANSLATE(u1 using UCS2toISO88591)) from cs310t2)) order by 1;
A1
--------
u1u1
--- 1 row(s) selected.
>>--
>>select ascii((select CHAR(97) from cs310t1 where a1='u1u1')) from cs310t1 order by 1;
(EXPR)
----------
97
97
--- 2 row(s) selected.
>> -- SEE NOTE ABOVE
>>select ascii((select CHAR(97) from cs310t2 where u1='a1a1')) from cs310t1 order by 1;
(EXPR)
----------
97
97
--- 2 row(s) selected.
>> -- SEE NOTE ABOVE
>>--
>>select a1 from cs310t1 where 97 = ascii((select min('a') from cs310t1 where a1='u1u1')) order by 1;
A1
--------
a1a1
u1u1
--- 2 row(s) selected.
>>select a1 from cs310t1 where ascii(CHAR(97)) = ascii((select min('a') from cs310t1 where a1='u1u1')) order by 1;
A1
--------
a1a1
u1u1
--- 2 row(s) selected.
>> -- SEE NOTE ABOVE
>>select a1 from cs310t1 where ascii(CHAR(97)) = ascii((select min(_iso88591'a') from cs310t1 where a1='u1u1')) order by 1;
A1
--------
a1a1
u1u1
--- 2 row(s) selected.
>> -- SEE NOTE ABOVE
>>select a1 from cs310t1 where 97 = ascii((select min(CHAR(97)) from cs310t1 where a1='u1u1')) order by 1;
A1
--------
a1a1
u1u1
--- 2 row(s) selected.
>> -- SEE NOTE ABOVE
>>select a1 from cs310t1 where ascii(CHAR(97)) = ascii((select min(CHAR(97)) from cs310t1 where a1='u1u1')) order by 1;
A1
--------
a1a1
u1u1
--- 2 row(s) selected.
>> -- SEE NOTE ABOVE
>>--
>>select a1 from cs310t1 where 97 = ascii((select min('a') from cs310t2 where u1='a1a1')) order by 1;
A1
--------
a1a1
u1u1
--- 2 row(s) selected.
>>select a1 from cs310t1 where ascii(CHAR(97)) = ascii((select min('a') from cs310t2 where u1='a1a1')) order by 1;
A1
--------
a1a1
u1u1
--- 2 row(s) selected.
>> -- SEE NOTE ABOVE
>>select a1 from cs310t1 where ascii(CHAR(97)) = ascii((select min(_iso88591'a') from cs310t2 where u1='a1a1')) order by 1;
A1
--------
a1a1
u1u1
--- 2 row(s) selected.
>> -- SEE NOTE ABOVE
>>select a1 from cs310t1 where 97 = ascii((select min(CHAR(97)) from cs310t2 where u1='a1a1')) order by 1;
A1
--------
a1a1
u1u1
--- 2 row(s) selected.
>> -- SEE NOTE ABOVE
>>select a1 from cs310t1 where ascii(CHAR(97)) = ascii((select min(CHAR(97)) from cs310t2 where u1='a1a1')) order by 1;
A1
--------
a1a1
u1u1
--- 2 row(s) selected.
>> -- SEE NOTE ABOVE
>>--
>>-- Tests for basic UNIONs
>>--
>>create table cs310t3 (a1 char(8) character set iso88591) no partition;
--- SQL operation complete.
>>insert into cs310t3 values('a1a1');
--- 1 row(s) inserted.
>>insert into cs310t3 values('u1u1');
--- 1 row(s) inserted.
>>select * from cs310t3 order by 1;
A1
--------
a1a1
u1u1
--- 2 row(s) selected.
>>create table cs310t4 (u1 char(8) character set ucs2) no partition;
--- SQL operation complete.
>>insert into cs310t4 values('a1a1');
--- 1 row(s) inserted.
>>insert into cs310t4 values('u1u1');
--- 1 row(s) inserted.
>>select * from cs310t4 order by 1;
U1
----------------
a1a1
u1u1
--- 2 row(s) selected.
>>select * from cs310t3 union (select * from cs310t4) order by 1;
A1
----------------
a1a1
u1u1
--- 2 row(s) selected.
>>select * from cs310t3 union all (select * from cs310t4) order by 1;
A1
----------------
a1a1
a1a1
u1u1
u1u1
--- 4 row(s) selected.
>>--
>>-- Tests for basic SQL functions
>>--
>>select * from cs310t3, cs310t4 order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = case when a1 > 'a1a' then u1 else 'a100' end order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = case when a1 > 'a1a' then 'a1a1' else 'a100' end order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = coalesce(cast(NULL as varchar(8) character set ISO88591), u1, a1) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = substr('a1a1zzzz',1,4) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = concat( substr(a1,1,2), 'a1' ) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = decode(a1,'a1a1','Found', u1, 'u1u1', 'b1b1', 'b1b1', 'NotFound') order by 1,2;
A1 U1
-------- ----------------
u1u1 u1u1
--- 1 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = insert('a1', 2, 0, '1a') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = replace(u1, 'u1', 'a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = replace('u1u1', 'u1', 'a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = replace(_ISO88591'u1u1', 'u1', 'a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = replace('u1u1', _ISO88591'u1', 'a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = replace('u1u1', 'u1', _ISO88591'a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = isnull(a1,'a1a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = isnull(u1,'a1a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = isnull('a1a1','a1a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = isnull(CAST(NULL as char(8) character set iso88591),'a1a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = LOWER('A1A1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = LEFT('a1a1ZZZZ',4) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = LPAD('a1',4,'a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = LPAD(substr(a1,1,2),4,'a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = NULLIF(a1,u1) order by 1,2;
A1 U1
-------- ----------------
a1a1 u1u1
u1u1 a1a1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = NULLIF(a1,'a1b1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = NVL(a1,'a1b1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = NVL(a1,'a1a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = NVL('a1a1','a1b1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = NVL(cast(NULL as char(8) character set iso88591),'a1a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = NVL(cast(NULL as char(8)),'a1a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = right(TRIM(u1),4) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = right(' ' || TRIM(u1),4) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = right(_ISO88591' ' || TRIM(u1),4) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = right(' ' || TRIM(a1),4) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = right('ZZZZa1a1',4) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = RPAD('a1',4,'a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = RPAD(substr(a1,1,2),4,'a1') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = trim(u1) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = trim(' a1a1 ') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = rtrim(u1) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = rtrim(' a1a1 ') order by 1,2;
--- 0 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = ltrim(u1) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = ltrim(' a1a1 ') order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = UPPER('a1a1') order by 1,2;
--- 0 row(s) selected.
>>select * from cs310t3, cs310t4 where a1 = cast('u1u1' as varchar(8) ) order by 1,2;
A1 U1
-------- ----------------
u1u1 a1a1
u1u1 u1u1
--- 2 row(s) selected.
>>--
>>-- Tests for ASSIGN
>>--
>>create table cs310t10 (a1 char(80) character set iso88591, u1 char(80) character set ucs2 ) no partition;
--- SQL operation complete.
>>insert into cs310t10 values(CHAR(96), CHAR(96));
--- 1 row(s) inserted.
>> -- SEE NOTE ABOVE
>>insert into cs310t10 values(SPACE(3), SPACE(3));
--- 1 row(s) inserted.
>>insert into cs310t10 values(SPACE(3, ISO88591), SPACE(3, ISO88591));
--- 1 row(s) inserted.
>>insert into cs310t10 values(SPACE(3, UCS2), SPACE(3, UCS2));
--- 1 row(s) inserted.
>>insert into cs310t10 values(DATEFORMAT(DATE'2009-04-15',USA), DATEFORMAT(DATE'2009-04-15',USA));
--- 1 row(s) inserted.
>>insert into cs310t10 values(DAYNAME(DATE'2009-04-15'), DAYNAME(DATE'2009-04-15'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(MONTHNAME(DATE'2009-04-15'), MONTHNAME(DATE'2009-04-15'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(CONVERTTOHEX(_ucs2'abc'), CONVERTTOHEX(_ucs2'abc'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(CONVERTTOHEX(_iso88591'abc'), CONVERTTOHEX(_iso88591'abc'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(CASE when 1=1 then 'abc' else 'def' end, CASE when 1=1 then 'abc' else 'def' end);
--- 1 row(s) inserted.
>>insert into cs310t10 values(CASE when 1=1 then _iso88591'abc' else _iso88591'def' end,
+> CASE when 1=1 then _iso88591'abc' else _iso88591'def' end);
--- 1 row(s) inserted.
>>insert into cs310t10 values(CAST('abc' as varchar(4)), CAST('abc' as varchar(4)));
--- 1 row(s) inserted.
>>insert into cs310t10 values(CAST(_iso88591'abc' as varchar(4)), CAST(_iso88591'abc' as varchar(4)));
--- 1 row(s) inserted.
>>insert into cs310t10 values(COALESCE('abc','def','xyz'), COALESCE('abc','def','xyz'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(COALESCE(_iso88591'abc',_iso88591'def',_iso88591'xyz'),
+> COALESCE(_iso88591'abc',_iso88591'def',_iso88591'xyz'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(CONCAT('abc','def'), CONCAT('abc','def'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(CONCAT(_iso88591'abc',_iso88591'def'), CONCAT(_iso88591'abc',_iso88591'def'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(_iso88591'abc' || _iso88591'def', _iso88591'abc' || _iso88591'def');
--- 1 row(s) inserted.
>>insert into cs310t10 values(_iso88591'abc' || _ucs2'def', _iso88591'abc' || _ucs2'def');
--- 1 row(s) inserted.
>>insert into cs310t10 values(_ucs2'abc' || _iso88591'def', _ucs2'abc' || _iso88591'def');
--- 1 row(s) inserted.
>>insert into cs310t10 values(DECODE('abc','def','EQ','NE'), DECODE('abc','def','EQ','NE'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(DECODE(_iso88591'abc',_iso88591'def',_iso88591'EQ',_iso88591'NE'),
+> DECODE(_iso88591'abc',_iso88591'def',_iso88591'EQ',_iso88591'NE'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(INSERT('abcghi',4,0,'def'), INSERT('abcghi',4,0,'def'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(INSERT(_iso88591'abcghi',4,0,_iso88591'def'),
+> INSERT(_iso88591'abcghi',4,0,_iso88591'def'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(ISNULL('abc','def'), ISNULL('abc','def'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(ISNULL(_iso88591'abc',_iso88591'def'), ISNULL(_iso88591'abc',_iso88591'def'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(LEFT('abcdef',3), LEFT('abcdef',3));
--- 1 row(s) inserted.
>>insert into cs310t10 values(LEFT(_iso88591'abcdef',3), LEFT(_iso88591'abcdef',3));
--- 1 row(s) inserted.
>>insert into cs310t10 values(LOWER('ABC'), LOWER('ABC'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(LOWER(_iso88591'ABC'), LOWER(_iso88591'ABC'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(LPAD('def',6,'abc'), LPAD('def',6,'abc'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(LPAD(_iso88591'def',6,_iso88591'abc'), LPAD(_iso88591'def',6,_iso88591'abc'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(LTRIM(' abc'), LTRIM(' abc'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(LTRIM(_iso88591' abc'), LTRIM(_iso88591' abc'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(NULLIF('abc','def'), NULLIF('abc','def'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(NULLIF(_iso88591'abc',_iso88591'def'), NULLIF('abc_iso88591',_iso88591'def'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(NVL('abc','def'), NVL('abc','def'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(NVL(_iso88591'abc',_iso88591'def'), NVL(_iso88591'abc',_iso88591'def'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(REPLACE('abcxyzghi','xyz','def'), REPLACE('abcxyzghi','xyz','def'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(REPLACE(_iso88591'abcxyzghi',_iso88591'xyz',_iso88591'def'),
+> REPLACE(_iso88591'abcxyzghi',_iso88591'xyz',_iso88591'def'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(RIGHT('defabc',3), RIGHT('defabc',3));
--- 1 row(s) inserted.
>>insert into cs310t10 values(RIGHT(_iso88591'defabc',3), RIGHT(_iso88591'defabc',3));
--- 1 row(s) inserted.
>>insert into cs310t10 values(RPAD('abc',6,'def'), RPAD('abc',6,'def'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(RPAD(_iso88591'abc',6,_iso88591'def'), RPAD(_iso88591'abc',6,_iso88591'def'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(RTRIM('abc '), RTRIM('abc '));
--- 1 row(s) inserted.
>>insert into cs310t10 values(RTRIM(_iso88591'abc '), RTRIM(_iso88591'abc '));
--- 1 row(s) inserted.
>>insert into cs310t10 values(SUBSTR('abcdefghi',1,6), SUBSTR('abcdefghi',1,6));
--- 1 row(s) inserted.
>>insert into cs310t10 values(SUBSTR(_iso88591'abcdefghi',1,6), SUBSTR(_iso88591'abcdefghi',1,6));
--- 1 row(s) inserted.
>>insert into cs310t10 values(TRANSLATE('abcdef' using UCS2toISO88591),
+> TRANSLATE('abcdef' using UCS2toISO88591));
--- 1 row(s) inserted.
>>insert into cs310t10 values(TRANSLATE('abcdef' using ISO88591toUCS2),
+> TRANSLATE('abcdef' using ISO88591toUCS2));
--- 1 row(s) inserted.
>>insert into cs310t10 values(TRANSLATE(_iso88591'abcdef' using UCS2toISO88591),
+> TRANSLATE(_iso88591'abcdef' using UCS2toISO88591));
--- 1 row(s) inserted.
>>insert into cs310t10 values(TRANSLATE(_iso88591'abcdef' using ISO88591toUCS2),
+> TRANSLATE(_iso88591'abcdef' using ISO88591toUCS2));
--- 1 row(s) inserted.
>>insert into cs310t10 values(TRIM(' abc '), TRIM(' abc '));
--- 1 row(s) inserted.
>>insert into cs310t10 values(TRIM(_iso88591' abc '), TRIM(_iso88591' abc '));
--- 1 row(s) inserted.
>>insert into cs310t10 values(UPPER('abcdef'), UPPER('abcdef'));
--- 1 row(s) inserted.
>>insert into cs310t10 values(UPPER(_iso88591'abcdef'), UPPER(_iso88591'abcdef'));
--- 1 row(s) inserted.
>>select count(*) from cs310t10;
(EXPR)
--------------------
56
--- 1 row(s) selected.
>>--
>>-- Tests for Comparisons
>>--
>>create table cs310t20 (a1 char(20) character set iso88591, u1 char(20) character set ucs2 ) no partition;
--- SQL operation complete.
>>insert into cs310t20 values(CHAR(97), CHAR(97) );
--- 1 row(s) inserted.
>> -- SEE NOTE ABOVE
>>insert into cs310t20 values(SPACE(3), SPACE(3) );
--- 1 row(s) inserted.
>>insert into cs310t20 values(DATEFORMAT(DATE'2009-04-15',USA), DATEFORMAT(DATE'2009-04-15',USA) );
--- 1 row(s) inserted.
>>insert into cs310t20 values(DAYNAME(DATE'2009-04-15'), DAYNAME(DATE'2009-04-15') );
--- 1 row(s) inserted.
>>insert into cs310t20 values(MONTHNAME(DATE'2009-04-15'), MONTHNAME(DATE'2009-04-15') );
--- 1 row(s) inserted.
>>insert into cs310t20 values(CONVERTTOHEX(_ucs2'abc'), CONVERTTOHEX(_ucs2'abc') );
--- 1 row(s) inserted.
>>insert into cs310t20 values(CONVERTTOHEX(_iso88591'abc'), CONVERTTOHEX(_iso88591'abc') );
--- 1 row(s) inserted.
>>insert into cs310t20 values('abc', 'abc') ;
--- 1 row(s) inserted.
>>insert into cs310t20 values('ABC', 'ABC') ;
--- 1 row(s) inserted.
>>insert into cs310t20 values('def', 'def') ;
--- 1 row(s) inserted.
>>insert into cs310t20 values('abcdef', 'abcdef') ;
--- 1 row(s) inserted.
>>insert into cs310t20 values('ABCDEF', 'ABCDEF') ;
--- 1 row(s) inserted.
>>insert into cs310t20 values('abcdefghi', 'abcdefghi') ;
--- 1 row(s) inserted.
>>insert into cs310t20 values('xyz', 'xyz') ;
--- 1 row(s) inserted.
>>insert into cs310t20 values('EQ','EQ');
--- 1 row(s) inserted.
>>insert into cs310t20 values('NE','NE');
--- 1 row(s) inserted.
>>insert into cs310t20 values('abcghi', 'abcghi');
--- 1 row(s) inserted.
>>insert into cs310t20 values('abcdefghi', 'abcdefghi');
--- 1 row(s) inserted.
>>select * from cs310t20 order by 1,2;
A1 U1
-------------------- ----------------------------------------
006100620063 006100620063
04/15/2009 04/15/2009
616263 616263
ABC ABC
ABCDEF ABCDEF
April April
EQ EQ
NE NE
Wednesday Wednesday
a a
abc abc
abcdef abcdef
abcdefghi abcdefghi
abcdefghi abcdefghi
abcghi abcghi
def def
xyz xyz
--- 18 row(s) selected.
>>select * from cs310t20 where a1 = CHAR(97) order by 1,2;
A1 U1
-------------------- ----------------------------------------
a a
--- 1 row(s) selected.
>> -- SEE NOTE ABOVE
>>select * from cs310t20 where u1 = CHAR(97) order by 1,2;
A1 U1
-------------------- ----------------------------------------
a a
--- 1 row(s) selected.
>> -- SEE NOTE ABOVE
>>select * from cs310t20 where a1 = SPACE(3) order by 1,2;
A1 U1
-------------------- ----------------------------------------
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = SPACE(3) order by 1,2;
A1 U1
-------------------- ----------------------------------------
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = SPACE(3, ISO88591) order by 1,2;
A1 U1
-------------------- ----------------------------------------
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = SPACE(3, UCS2) order by 1,2;
A1 U1
-------------------- ----------------------------------------
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = DATEFORMAT(DATE'2009-04-15',USA) order by 1,2;
A1 U1
-------------------- ----------------------------------------
04/15/2009 04/15/2009
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = DATEFORMAT(DATE'2009-04-15',USA) order by 1,2;
A1 U1
-------------------- ----------------------------------------
04/15/2009 04/15/2009
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = DAYNAME(DATE'2009-04-15') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Wednesday Wednesday
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = DAYNAME(DATE'2009-04-15') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Wednesday Wednesday
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = MONTHNAME(DATE'2009-04-15') order by 1,2;
A1 U1
-------------------- ----------------------------------------
April April
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = MONTHNAME(DATE'2009-04-15') order by 1,2;
A1 U1
-------------------- ----------------------------------------
April April
--- 1 row(s) selected.
>>--
>>create table cs310t21 (a1 char(80) character set iso88591, u1 char(80) character set ucs2 ) no partition;
--- SQL operation complete.
>>insert into cs310t21 values(CURRENT_USER, CURRENT_USER);
--- 1 row(s) inserted.
>>select 'a1 = CURRENT_USER' from cs310t21 where a1 = CURRENT_USER order by 1;
(EXPR)
-----------------
a1 = CURRENT_USER
--- 1 row(s) selected.
>>select 'u1 = CURRENT_USER' from cs310t21 where u1 = CURRENT_USER order by 1;
(EXPR)
-----------------
u1 = CURRENT_USER
--- 1 row(s) selected.
>>select 'a1 = SESSION_USER' from cs310t21 where a1 = SESSION_USER order by 1;
(EXPR)
-----------------
a1 = SESSION_USER
--- 1 row(s) selected.
>>select 'u1 = SESSION_USER' from cs310t21 where u1 = SESSION_USER order by 1;
(EXPR)
-----------------
u1 = SESSION_USER
--- 1 row(s) selected.
>>select 'a1 = USER' from cs310t21 where a1 = USER order by 1;
(EXPR)
---------
a1 = USER
--- 1 row(s) selected.
>>select 'u1 = USER' from cs310t21 where u1 = USER order by 1;
(EXPR)
---------
u1 = USER
--- 1 row(s) selected.
>>--
>>select * from cs310t20 order by 1,2;
A1 U1
-------------------- ----------------------------------------
006100620063 006100620063
04/15/2009 04/15/2009
616263 616263
ABC ABC
ABCDEF ABCDEF
April April
EQ EQ
NE NE
Wednesday Wednesday
a a
abc abc
abcdef abcdef
abcdefghi abcdefghi
abcdefghi abcdefghi
abcghi abcghi
def def
xyz xyz
--- 18 row(s) selected.
>>select * from cs310t20 where a1 = CONVERTTOHEX(_ucs2'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
006100620063 006100620063
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = CONVERTTOHEX(_ucs2'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
006100620063 006100620063
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = CONVERTTOHEX(_iso88591'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
616263 616263
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = CONVERTTOHEX(_iso88591'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
616263 616263
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = CASE when 1=1 then 'abc' else 'def' end order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = CASE when 1=1 then _iso88591'abc' else _iso88591'def' end order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = CAST('abc' as varchar(4)) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = CAST('abc' as varchar(4)) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = CAST(_iso88591'abc' as varchar(4)) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = CAST(_iso88591'abc' as varchar(4)) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = COALESCE('abc','def','xyz') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = COALESCE(_iso88591'abc',_iso88591'def',_iso88591'xyz') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = CONCAT('abc','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = CONCAT(_iso88591'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = _iso88591'abc' || _iso88591'def' order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = _iso88591'abc' || _ucs2'def' order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = _iso88591'abc' || _ucs2'def' order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = _ucs2'abc' || _iso88591'def' order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = _ucs2'abc' || _iso88591'def' order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = DECODE('abc','def','EQ','NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = DECODE(_iso88591'abc',_iso88591'def',_iso88591'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = INSERT('abcghi',4,0,'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
abcdefghi abcdefghi
--- 2 row(s) selected.
>>select * from cs310t20 where u1 = INSERT(_iso88591'abcghi',4,0,_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
abcdefghi abcdefghi
--- 2 row(s) selected.
>>select * from cs310t20 where a1 = ISNULL('abc','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = ISNULL(_iso88591'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = LEFT('abcdef',3) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = LEFT(_iso88591'abcdef',3) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = LOWER('ABC') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = LOWER(_iso88591'ABC') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = LPAD('def',6,'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = LPAD(_iso88591'def',6,_iso88591'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = LTRIM(' abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = LTRIM(_iso88591' abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = NULLIF('abc','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = NULLIF(_iso88591'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = NVL('abc','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = NVL(_iso88591'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = REPLACE('abcxyzghi','xyz','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
abcdefghi abcdefghi
--- 2 row(s) selected.
>>select * from cs310t20 where u1 = REPLACE(_iso88591'abcxyzghi',_iso88591'xyz',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
abcdefghi abcdefghi
--- 2 row(s) selected.
>>select * from cs310t20 where a1 = RIGHT('defabc',3) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = RIGHT(_iso88591'defabc',3) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = RPAD('abc',6,'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = RPAD(_iso88591'abc',6,_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = RTRIM('abc ') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = RTRIM(_iso88591'abc ') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = SUBSTR('abcdefghi',1,6) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = SUBSTR(_iso88591'abcdefghi',1,6) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = TRANSLATE('abcdef' using UCS2toISO88591) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = TRANSLATE('abcdef' using UCS2toISO88591) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = TRANSLATE('abcdef' using ISO88591toUCS2) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = TRANSLATE('abcdef' using ISO88591toUCS2) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = TRANSLATE(_iso88591'abcdef' using UCS2toISO88591) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = TRANSLATE(_iso88591'abcdef' using UCS2toISO88591) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = TRANSLATE(_iso88591'abcdef' using ISO88591toUCS2) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = TRANSLATE(_iso88591'abcdef' using ISO88591toUCS2) order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = TRIM(' abc ') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = TRIM(_iso88591' abc ') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t20 where a1 = UPPER('abcdef') order by 1,2;
A1 U1
-------------------- ----------------------------------------
ABCDEF ABCDEF
--- 1 row(s) selected.
>>select * from cs310t20 where u1 = UPPER(_iso88591'abcdef') order by 1,2;
A1 U1
-------------------- ----------------------------------------
ABCDEF ABCDEF
--- 1 row(s) selected.
>>--
>>-- Tests for Concatenation
>>--
>>create table cs310t30 (a1 char(20) character set iso88591, u1 char(20) character set ucs2 ) no partition;
--- SQL operation complete.
>>create table cs310t31 (a1 char(80) character set iso88591, u1 char(80) character set ucs2 ) no partition;
--- SQL operation complete.
>>insert into cs310t30 values('ZA','ZA');
--- 1 row(s) inserted.
>>insert into cs310t30 values('Z','Z');
--- 1 row(s) inserted.
>>insert into cs310t30 values('Z'||DATEFORMAT(DATE'2009-04-15',USA),'Z'||DATEFORMAT(DATE'2009-04-15',USA) );
--- 1 row(s) inserted.
>>insert into cs310t30 values('Z' || DAYNAME(DATE'2009-04-15'), 'Z' || DAYNAME(DATE'2009-04-15') );
--- 1 row(s) inserted.
>>insert into cs310t30 values('Z' || MONTHNAME(DATE'2009-04-15'), 'Z' || MONTHNAME(DATE'2009-04-15') );
--- 1 row(s) inserted.
>>insert into cs310t31 values(CURRENT_USER, CURRENT_USER);
--- 1 row(s) inserted.
>>insert into cs310t31 values('Z'||CURRENT_USER, 'Z'||CURRENT_USER);
--- 1 row(s) inserted.
>>insert into cs310t30 values('Z' || CONVERTTOHEX(_ucs2'abc'), 'Z' || CONVERTTOHEX(_ucs2'abc') );
--- 1 row(s) inserted.
>>insert into cs310t30 values('Z' || CONVERTTOHEX(_iso88591'abc'), 'Z' || CONVERTTOHEX(_iso88591'abc') );
--- 1 row(s) inserted.
>>insert into cs310t30 values('Za','Za');
--- 1 row(s) inserted.
>>insert into cs310t30 values('Zd','Zd');
--- 1 row(s) inserted.
>>insert into cs310t30 values('Zabc','Zabc');
--- 1 row(s) inserted.
>>insert into cs310t30 values('Zdef','Zdef');
--- 1 row(s) inserted.
>>insert into cs310t30 values('Zxyz','Zxyz');
--- 1 row(s) inserted.
>>insert into cs310t30 values('ZEQ','ZEQ');
--- 1 row(s) inserted.
>>insert into cs310t30 values('ZNE','ZNE');
--- 1 row(s) inserted.
>>insert into cs310t30 values('Zabcdef','Zabcdef');
--- 1 row(s) inserted.
>>insert into cs310t30 values('ZABCDEF','ZABCDEF');
--- 1 row(s) inserted.
>>insert into cs310t30 values('Zabcdefghi','Zabcdefghi');
--- 1 row(s) inserted.
>>select * from cs310t30 order by 1,2;
A1 U1
-------------------- ----------------------------------------
Z Z
Z006100620063 Z006100620063
Z04/15/2009 Z04/15/2009
Z616263 Z616263
ZA ZA
ZABCDEF ZABCDEF
ZApril ZApril
ZEQ ZEQ
ZNE ZNE
ZWednesday ZWednesday
Za Za
Zabc Zabc
Zabcdef Zabcdef
Zabcdefghi Zabcdefghi
Zd Zd
Zdef Zdef
Zxyz Zxyz
--- 17 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || CHAR(97) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Za Za
--- 1 row(s) selected.
>> -- SEE NOTE ABOVE
>>select * from cs310t30 where a1 = _ucs2'Z' || CHAR(97) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Za Za
--- 1 row(s) selected.
>> -- SEE NOTE ABOVE
>>select * from cs310t30 where a1 = _iso88591'Z' || SPACE(3) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Z Z
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || SPACE(3) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Z Z
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || SPACE(3, ISO88591) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Z Z
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || SPACE(3, ISO88591) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Z Z
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || SPACE(3, UCS2) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Z Z
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || SPACE(3, UCS2) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Z Z
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || DATEFORMAT(DATE'2009-04-15',USA) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Z04/15/2009 Z04/15/2009
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || DATEFORMAT(DATE'2009-04-15',USA) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Z04/15/2009 Z04/15/2009
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || DAYNAME(DATE'2009-04-15') order by 1,2;
A1 U1
-------------------- ----------------------------------------
ZWednesday ZWednesday
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || DAYNAME(DATE'2009-04-15') order by 1,2;
A1 U1
-------------------- ----------------------------------------
ZWednesday ZWednesday
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || MONTHNAME(DATE'2009-04-15') order by 1,2;
A1 U1
-------------------- ----------------------------------------
ZApril ZApril
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || MONTHNAME(DATE'2009-04-15') order by 1,2;
A1 U1
-------------------- ----------------------------------------
ZApril ZApril
--- 1 row(s) selected.
>>select * from cs310t31 where a1 != CURRENT_USER AND (a1 != ('Z'||CURRENT_USER)) order by 1,2;
--- 0 row(s) selected.
>>select '_iso88591''Z'' || CURRENT_USER' from cs310t31 where a1 = _iso88591'Z' || CURRENT_USER order by 1;
(EXPR)
----------------------------
_iso88591'Z' || CURRENT_USER
--- 1 row(s) selected.
>>select '_ucs2''Z'' || CURRENT_USER' from cs310t31 where a1 = _ucs2'Z' || CURRENT_USER order by 1;
(EXPR)
------------------------
_ucs2'Z' || CURRENT_USER
--- 1 row(s) selected.
>>select '_iso88591''Z'' || SESSION_USER' from cs310t31 where a1 = _iso88591'Z' || SESSION_USER order by 1;
(EXPR)
----------------------------
_iso88591'Z' || SESSION_USER
--- 1 row(s) selected.
>>select '_ucs2''Z'' || SESSION_USER' from cs310t31 where a1 = _ucs2'Z' || SESSION_USER order by 1;
(EXPR)
------------------------
_ucs2'Z' || SESSION_USER
--- 1 row(s) selected.
>>select '_iso88591''Z'' || USER' from cs310t31 where a1 = _iso88591'Z' || USER order by 1;
(EXPR)
--------------------
_iso88591'Z' || USER
--- 1 row(s) selected.
>>select '_ucs2''Z'' || USER' from cs310t31 where a1 = _ucs2'Z' || USER order by 1;
(EXPR)
----------------
_ucs2'Z' || USER
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || CONVERTTOHEX(_ucs2'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Z006100620063 Z006100620063
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || CONVERTTOHEX(_ucs2'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Z006100620063 Z006100620063
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || CONVERTTOHEX(_iso88591'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Z616263 Z616263
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || CONVERTTOHEX(_iso88591'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Z616263 Z616263
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || CASE when 1=1 then 'abc' else 'def' end order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || CASE when 1=1 then 'abc' else 'def' end order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || CASE when 1=1 then _iso88591'abc' else _iso88591'def' end order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || CAST('abc' as varchar(4)) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || CAST('abc' as varchar(4)) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || CAST(_iso88591'abc' as varchar(4)) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || CAST(_iso88591'abc' as varchar(4)) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || COALESCE('abc','def','xyz') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || COALESCE('abc','def','xyz') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || COALESCE(_iso88591'abc',_iso88591'def',_iso88591'xyz') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || CONCAT('abc','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || CONCAT('abc','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || CONCAT(_iso88591'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || (_iso88591'abc' || _iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || (_iso88591'abc' || _ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || (_iso88591'abc' || _ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || (_ucs2'abc' || _iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || (_ucs2'abc' || _iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || DECODE('abc','def','EQ','NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
ZNE ZNE
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || DECODE('abc','def','EQ','NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
ZNE ZNE
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || DECODE(_iso88591'abc',_iso88591'def',_iso88591'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
ZNE ZNE
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || INSERT('abcghi',4,0,'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdefghi Zabcdefghi
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || INSERT('abcghi',4,0,'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdefghi Zabcdefghi
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || INSERT(_iso88591'abcghi',4,0,_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdefghi Zabcdefghi
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || ISNULL('abc','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || ISNULL('abc','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || ISNULL(_iso88591'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || LEFT('abcdef',3) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || LEFT('abcdef',3) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || LEFT(_iso88591'abcdef',3) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || LOWER('ABC') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || LOWER('ABC') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || LOWER(_iso88591'ABC') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || LPAD('def',6,'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || LPAD('def',6,'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || LPAD(_iso88591'def',6,_iso88591'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || LTRIM(' abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || LTRIM(' abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || LTRIM(_iso88591' abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || NULLIF('abc','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || NULLIF('abc','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || NULLIF(_iso88591'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || NVL('abc','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || NVL('abc','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || NVL(_iso88591'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || REPLACE('abcxyzghi','xyz','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdefghi Zabcdefghi
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || REPLACE('abcxyzghi','xyz','def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdefghi Zabcdefghi
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || REPLACE(_iso88591'abcxyzghi',_iso88591'xyz',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdefghi Zabcdefghi
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || RIGHT('defabc',3) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || RIGHT('defabc',3) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || RIGHT(_iso88591'defabc',3) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || RPAD('abc',6,'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || RPAD('abc',6,'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || RPAD(_iso88591'abc',6,_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || RTRIM('abc ') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || RTRIM('abc ') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || RTRIM(_iso88591'abc ') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || SUBSTR('abcdefghi',1,6) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || SUBSTR('abcdefghi',1,6) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || SUBSTR(_iso88591'abcdefghi',1,6) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || TRANSLATE('abcdef' using UCS2toISO88591) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || TRANSLATE('abcdef' using UCS2toISO88591) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || TRANSLATE('abcdef' using ISO88591toUCS2) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || TRANSLATE('abcdef' using ISO88591toUCS2) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || TRANSLATE(_iso88591'abcdef' using UCS2toISO88591) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || TRANSLATE(_iso88591'abcdef' using UCS2toISO88591) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || TRANSLATE(_iso88591'abcdef' using ISO88591toUCS2) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || TRANSLATE(_iso88591'abcdef' using ISO88591toUCS2) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabcdef Zabcdef
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || TRIM(' abc ') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || TRIM(' abc ') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || TRIM(_iso88591' abc ') order by 1,2;
A1 U1
-------------------- ----------------------------------------
Zabc Zabc
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _iso88591'Z' || UPPER('abcdef') order by 1,2;
A1 U1
-------------------- ----------------------------------------
ZABCDEF ZABCDEF
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || UPPER('abcdef') order by 1,2;
A1 U1
-------------------- ----------------------------------------
ZABCDEF ZABCDEF
--- 1 row(s) selected.
>>select * from cs310t30 where a1 = _ucs2'Z' || UPPER(_iso88591'abcdef') order by 1,2;
A1 U1
-------------------- ----------------------------------------
ZABCDEF ZABCDEF
--- 1 row(s) selected.
>>--
>>-- Tests for SQL Functions - that they can accept any combination
>>-- of ISO and UCS2 character sets
>>--
>>create table cs310t40 (a1 char(20) character set iso88591, u1 char(20) character set ucs2 ) no partition;
--- SQL operation complete.
>>insert into cs310t40 values('ZA','ZA');
--- 1 row(s) inserted.
>>insert into cs310t40 values('Z','Z');
--- 1 row(s) inserted.
>>insert into cs310t40 values('Z'||DATEFORMAT(DATE'2009-04-15',USA),'Z'||DATEFORMAT(DATE'2009-04-15',USA) );
--- 1 row(s) inserted.
>>insert into cs310t40 values('Z' || DAYNAME(DATE'2009-04-15'), 'Z' || DAYNAME(DATE'2009-04-15') );
--- 1 row(s) inserted.
>>insert into cs310t40 values('Z' || MONTHNAME(DATE'2009-04-15'), 'Z' || MONTHNAME(DATE'2009-04-15') );
--- 1 row(s) inserted.
>>insert into cs310t40 values('Z' || CONVERTTOHEX(_ucs2'abc'), 'Z' || CONVERTTOHEX(_ucs2'abc') );
--- 1 row(s) inserted.
>>insert into cs310t40 values('Z' || CONVERTTOHEX(_iso88591'abc'), 'Z' || CONVERTTOHEX(_iso88591'abc') );
--- 1 row(s) inserted.
>>insert into cs310t40 values('Za','Za');
--- 1 row(s) inserted.
>>insert into cs310t40 values('Zd','Zd');
--- 1 row(s) inserted.
>>insert into cs310t40 values('abc','abc');
--- 1 row(s) inserted.
>>insert into cs310t40 values('def','def');
--- 1 row(s) inserted.
>>insert into cs310t40 values('Zabc','Zabc');
--- 1 row(s) inserted.
>>insert into cs310t40 values('Zdef','Zdef');
--- 1 row(s) inserted.
>>insert into cs310t40 values('Zxyz','Zxyz');
--- 1 row(s) inserted.
>>insert into cs310t40 values('EQ','EQ');
--- 1 row(s) inserted.
>>insert into cs310t40 values('ZEQ','ZEQ');
--- 1 row(s) inserted.
>>insert into cs310t40 values('ZNE','ZNE');
--- 1 row(s) inserted.
>>insert into cs310t40 values('NE','NE');
--- 1 row(s) inserted.
>>insert into cs310t40 values('abcdef','abcdef');
--- 1 row(s) inserted.
>>insert into cs310t40 values('Zabcdef','Zabcdef');
--- 1 row(s) inserted.
>>insert into cs310t40 values('ABCDEF','ABCDEF');
--- 1 row(s) inserted.
>>insert into cs310t40 values('ZABCDEF','ZABCDEF');
--- 1 row(s) inserted.
>>insert into cs310t40 values('abcdefghi','abcdefghi');
--- 1 row(s) inserted.
>>insert into cs310t40 values('Zabcdefghi','Zabcdefghi');
--- 1 row(s) inserted.
>>select * from cs310t40 order by a1;
A1 U1
-------------------- ----------------------------------------
ABCDEF ABCDEF
EQ EQ
NE NE
Z Z
Z006100620063 Z006100620063
Z04/15/2009 Z04/15/2009
Z616263 Z616263
ZA ZA
ZABCDEF ZABCDEF
ZApril ZApril
ZEQ ZEQ
ZNE ZNE
ZWednesday ZWednesday
Za Za
Zabc Zabc
Zabcdef Zabcdef
Zabcdefghi Zabcdefghi
Zd Zd
Zdef Zdef
Zxyz Zxyz
abc abc
abcdef abcdef
abcdefghi abcdefghi
def def
--- 24 row(s) selected.
>>select * from cs310t40 where a1 = _iso88591'Z' || CHAR(97) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Za Za
--- 1 row(s) selected.
>> -- SEE NOTE ABOVE
>>select * from cs310t40 where a1 = _iso88591'Z' || TRANSLATE( CHAR(97) USING UCS2toISO88591) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Za Za
--- 1 row(s) selected.
>> -- SEE NOTE ABOVE
>>select * from cs310t40 where a1 = _iso88591'Z' || 'a' order by 1,2;
A1 U1
-------------------- ----------------------------------------
Za Za
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = _iso88591'Z' || CAST('a' as VARCHAR(1)) order by 1,2;
A1 U1
-------------------- ----------------------------------------
Za Za
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = CASE when 1=1 then _iso88591'abc' else _ucs2'def' end order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = CASE when 1=1 then _ucs2'abc' else _iso88591'def' end order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = CASE when 1=1 then _ucs2'abc' else _ucs2'def' end order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = CASE when 1=1 then _iso88591'abc' else _iso88591'def' end order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = CASE when 1=1 then _iso88591'abc' else _ucs2'def' end order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = CASE when 1=1 then _ucs2'abc' else _iso88591'def' end order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>--
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_iso88591'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>--
>>select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>--
>>select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_iso88591'def',_ucs2'ghi') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_ucs2'def',_iso88591'ghi') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_iso88591'def',_iso88591'ghi') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_ucs2'def',_ucs2'ghi') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_iso88591'def',_ucs2'ghi') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_iso88591'ghi') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_ucs2'ghi') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_iso88591'def',_iso88591'ghi') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_iso88591'def',_ucs2'ghi') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_ucs2'def',_iso88591'ghi') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_iso88591'def',_iso88591'ghi') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_ucs2'def',_ucs2'ghi') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_iso88591'def',_ucs2'ghi') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_ucs2'def',_iso88591'ghi') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>--
>>select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_iso88591'def',_iso88591'ghi',_ucs2'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_iso88591'def',_ucs2'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_ucs2'def',_iso88591'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_iso88591'def',_iso88591'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_iso88591'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_iso88591'def',_ucs2'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_iso88591'def',_iso88591'ghi',_ucs2'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_ucs2'def',_ucs2'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_ucs2'def',_iso88591'ghi',_ucs2'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_ucs2'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_iso88591'ghi',_ucs2'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_iso88591'def',_ucs2'ghi',_ucs2'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_ucs2'def',_ucs2'ghi',_ucs2'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_ucs2'ghi',_ucs2'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_iso88591'def',_iso88591'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_iso88591'def',_iso88591'ghi',_ucs2'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_iso88591'def',_ucs2'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_ucs2'def',_iso88591'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_iso88591'def',_iso88591'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_ucs2'def',_iso88591'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_iso88591'def',_ucs2'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_iso88591'def',_iso88591'ghi',_ucs2'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_ucs2'def',_ucs2'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_ucs2'def',_iso88591'ghi',_ucs2'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_ucs2'def',_ucs2'ghi',_iso88591'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_ucs2'def',_iso88591'ghi',_ucs2'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_iso88591'def',_ucs2'ghi',_ucs2'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_ucs2'def',_ucs2'ghi',_ucs2'jkl') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>--
>>select * from cs310t40 where a1 = CONCAT(_iso88591'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = CONCAT(_ucs2'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = CONCAT(_ucs2'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = CONCAT(_iso88591'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = CONCAT(_iso88591'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = CONCAT(_ucs2'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>--
>>select * from cs310t40 where a1 = _iso88591'abc' || _ucs2'def' order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = _ucs2'abc' || _iso88591'def' order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = _ucs2'abc' || _ucs2'def' order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = _iso88591'abc' || _iso88591'def' order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = _iso88591'abc' || _ucs2'def' order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = _ucs2'abc' || _iso88591'def' order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>--
>>select * from cs310t40 where a1 = DECODE(_iso88591'abc',_iso88591'def',_iso88591'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = DECODE(_iso88591'abc',_iso88591'def',_ucs2'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = DECODE(_iso88591'abc',_ucs2'def',_iso88591'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = DECODE(_ucs2'abc',_iso88591'def',_iso88591'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = DECODE(_ucs2'abc',_ucs2'def',_iso88591'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = DECODE(_ucs2'abc',_iso88591'def',_ucs2'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = DECODE(_ucs2'abc',_iso88591'def',_iso88591'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = DECODE(_iso88591'abc',_ucs2'def',_ucs2'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = DECODE(_iso88591'abc',_ucs2'def',_iso88591'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = DECODE(_iso88591'abc',_iso88591'def',_ucs2'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = DECODE(_ucs2'abc',_ucs2'def',_ucs2'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = DECODE(_ucs2'abc',_ucs2'def',_iso88591'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = DECODE(_ucs2'abc',_iso88591'def',_ucs2'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = DECODE(_iso88591'abc',_ucs2'def',_ucs2'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = DECODE(_ucs2'abc',_ucs2'def',_ucs2'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_iso88591'abc',_iso88591'def',_iso88591'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_iso88591'abc',_iso88591'def',_iso88591'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_iso88591'abc',_iso88591'def',_ucs2'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_iso88591'abc',_ucs2'def',_iso88591'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_ucs2'abc',_iso88591'def',_iso88591'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_ucs2'abc',_ucs2'def',_iso88591'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_ucs2'abc',_iso88591'def',_ucs2'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_ucs2'abc',_iso88591'def',_iso88591'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_iso88591'abc',_ucs2'def',_ucs2'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_iso88591'abc',_ucs2'def',_iso88591'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_iso88591'abc',_iso88591'def',_ucs2'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_ucs2'abc',_ucs2'def',_ucs2'EQ',_iso88591'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_ucs2'abc',_ucs2'def',_iso88591'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_ucs2'abc',_iso88591'def',_ucs2'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = DECODE(_iso88591'abc',_ucs2'def',_ucs2'EQ',_ucs2'NE') order by 1,2;
A1 U1
-------------------- ----------------------------------------
NE NE
--- 1 row(s) selected.
>>--
>>select * from cs310t40 where a1 = INSERT(_iso88591'abcghi',4,0,_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = INSERT(_ucs2'abcghi',4,0,_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = INSERT(_ucs2'abcghi',4,0,_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = INSERT(_iso88591'abcghi',4,0,_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = INSERT(_iso88591'abcghi',4,0,_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = INSERT(_ucs2'abcghi',4,0,_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>--
>>select * from cs310t40 where a1 = ISNULL(_iso88591'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = ISNULL(_ucs2'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = ISNULL(_ucs2'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = ISNULL(_iso88591'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = ISNULL(_iso88591'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = ISNULL(_ucs2'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>--
>>select * from cs310t40 where a1 = LPAD(_iso88591'def',6,_ucs2'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = LPAD(_ucs2'def',6,_iso88591'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = LPAD(_ucs2'def',6,_ucs2'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = LPAD(_iso88591'def',6,_iso88591'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = LPAD(_iso88591'def',6,_ucs2'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = LPAD(_ucs2'def',6,_iso88591'abc') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>--
>>select * from cs310t40 where a1 = NULLIF(_iso88591'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = NULLIF(_ucs2'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = NULLIF(_ucs2'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = NULLIF(_iso88591'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = NULLIF(_iso88591'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = NULLIF(_ucs2'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>--
>>select * from cs310t40 where a1 = NVL(_iso88591'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = NVL(_ucs2'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = NVL(_ucs2'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = NVL(_iso88591'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = NVL(_iso88591'abc',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = NVL(_ucs2'abc',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abc abc
--- 1 row(s) selected.
>>--
>>select * from cs310t40 where a1 = REPLACE(_iso88591'abcxyzghi',_iso88591'xyz',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = REPLACE(_iso88591'abcxyzghi',_ucs2'xyz',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = REPLACE(_ucs2'abcxyzghi',_iso88591'xyz',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = REPLACE(_ucs2'abcxyzghi',_ucs2'xyz',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = REPLACE(_ucs2'abcxyzghi',_iso88591'xyz',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = REPLACE(_iso88591'abcxyzghi',_ucs2'xyz',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = REPLACE(_ucs2'abcxyzghi',_ucs2'xyz',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = REPLACE(_iso88591'abcxyzghi',_iso88591'xyz',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = REPLACE(_iso88591'abcxyzghi',_iso88591'xyz',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = REPLACE(_iso88591'abcxyzghi',_ucs2'xyz',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = REPLACE(_ucs2'abcxyzghi',_iso88591'xyz',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = REPLACE(_ucs2'abcxyzghi',_ucs2'xyz',_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = REPLACE(_ucs2'abcxyzghi',_iso88591'xyz',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = REPLACE(_iso88591'abcxyzghi',_ucs2'xyz',_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdefghi abcdefghi
--- 1 row(s) selected.
>>--
>>select * from cs310t40 where a1 = RPAD(_iso88591'abc',6,_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = RPAD(_ucs2'abc',6,_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where a1 = RPAD(_ucs2'abc',6,_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = RPAD(_iso88591'abc',6,_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = RPAD(_iso88591'abc',6,_ucs2'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>select * from cs310t40 where u1 = RPAD(_ucs2'abc',6,_iso88591'def') order by 1,2;
A1 U1
-------------------- ----------------------------------------
abcdef abcdef
--- 1 row(s) selected.
>>--
>>-- Tests for TRANSLATE
>>--
>>create table cs310t50 (a1 char(8) character set iso88591, u1 char(8) character set ucs2 ) no partition;
--- SQL operation complete.
>>insert into cs310t50 values('abc','abc');
--- 1 row(s) inserted.
>>insert into cs310t50 values('abc','def');
--- 1 row(s) inserted.
>>select * from cs310t50 order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>--
>>--Following should work without errors
>>select * from cs310t50 where a1 = u1 order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t50 where u1 = a1 order by 1;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t50 where a1 = TRANSLATE(u1 using UCS2toISO88591) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t50 where u1 = TRANSLATE(a1 using ISO88591toUCS2) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t50 where a1 = TRANSLATE(a1 using ISO88591toUCS2) order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t50 where u1 = TRANSLATE(u1 using UCS2toISO88591) order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t50 where a1 = TRANSLATE('abc' using UCS2toISO88591) order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t50 where u1 = TRANSLATE(_iso88591'abc' using ISO88591toUCS2) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t50 where a1 = TRANSLATE(TRANSLATE(u1 using UCS2toISO88591) using ISO88591toUCS2) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t50 where u1 = TRANSLATE(TRANSLATE(a1 using ISO88591toUCS2) using UCS2toISO88591) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select TRANSLATE(u1 using UCS2toISO88591) from cs310t50 order by 1;
(EXPR)
--------
abc
def
--- 2 row(s) selected.
>>select TRANSLATE(a1 using ISO88591toUCS2) from cs310t50 order by 1;
(EXPR)
----------------
abc
abc
--- 2 row(s) selected.
>>select TRANSLATE(TRANSLATE(u1 using UCS2toISO88591) using ISO88591toUCS2) from cs310t50 order by 1;
(EXPR)
----------------
abc
def
--- 2 row(s) selected.
>>select TRANSLATE(TRANSLATE(a1 using ISO88591toUCS2) using UCS2toISO88591) from cs310t50 order by 1;
(EXPR)
--------
abc
abc
--- 2 row(s) selected.
>>--
>>--The following should work without errors ... because of Implicit Casting of literals
>>select * from cs310t50 where a1 = TRANSLATE('abc' using ISO88591toUCS2) order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select TRANSLATE('abc' using ISO88591toUCS2) from cs310t50 order by 1;
(EXPR)
------
abc
abc
--- 2 row(s) selected.
>>--
>>--The following should work without errors ... because of Implicit Casting of literals
>>--HOWEVER, it is possible we should make these give errors.
>>select * from cs310t50 where u1 = TRANSLATE(_iso88591'abc' using UCS2toISO88591) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t50 where u1 = TRANSLATE(_ucs2'abc' using ISO88591toUCS2) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select TRANSLATE(_iso88591'abc' using UCS2toISO88591) from cs310t50 order by 1;
(EXPR)
------
abc
abc
--- 2 row(s) selected.
>>select TRANSLATE(_ucs2'abc' using ISO88591toUCS2) from cs310t50 order by 1;
(EXPR)
------
abc
abc
--- 2 row(s) selected.
>>--
>>--The following should work without errors ... but only because the return value from the
>>--inner Function Invocation gets the Implicit Casting/Translation feature applied to it.
>>select * from cs310t50 where a1 = TRANSLATE(TRANSLATE(u1 using UCS2toISO88591) using UCS2toISO88591) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t50 where u1 = TRANSLATE(TRANSLATE(a1 using ISO88591toUCS2) using ISO88591toUCS2) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t50 where a1 = TRANSLATE(TRIM(a1) using UCS2toISO88591) order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t50 where u1 = TRANSLATE(TRIM(u1) using ISO88591toUCS2) order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t50 where a1 = TRANSLATE(TRIM(_iso88591'abc') using UCS2toISO88591) order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t50 where u1 = TRANSLATE(TRIM(_ucs2'abc') using ISO88591toUCS2) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t50 where u1 = TRANSLATE(TRIM('abc') using ISO88591toUCS2) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>--
>>--
>>--Following should get errors
>>select * from cs310t50 where a1 = TRANSLATE(u1 using ISO88591toUCS2) order by 1,2;
*** ERROR[4106] The character set for the operand of function TRANSLATE must be ISO88591.
*** ERROR[8822] The statement was not prepared.
>>select * from cs310t50 where u1 = TRANSLATE(a1 using UCS2toISO88591) order by 1,2;
*** ERROR[4106] The character set for the operand of function TRANSLATE must be UCS2.
*** ERROR[8822] The statement was not prepared.
>>select TRANSLATE(u1 using ISO88591toUCS2) from cs310t50 order by 1;
*** ERROR[4106] The character set for the operand of function TRANSLATE must be ISO88591.
*** ERROR[8822] The statement was not prepared.
>>select TRANSLATE(a1 using UCS2toISO88591) from cs310t50 order by 1;
*** ERROR[4106] The character set for the operand of function TRANSLATE must be UCS2.
*** ERROR[8822] The statement was not prepared.
>>--
>>-- Tests for CAST
>>--
>>create table cs310t60 (a1 char(8) character set iso88591, u1 char(8) character set ucs2 ) no partition;
--- SQL operation complete.
>>insert into cs310t60 values('abc','abc');
--- 1 row(s) inserted.
>>insert into cs310t60 values('abc','def');
--- 1 row(s) inserted.
>>select * from cs310t60 order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>
>>select * from cs310t60 where a1 = CAST('abc' as varchar(4)) order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t60 where u1 = CAST('abc' as varchar(4)) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t60 where a1 = CAST(_iso88591'abc' as varchar(4)) order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t60 where u1 = CAST(_iso88591'abc' as varchar(4)) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>--
>>select * from cs310t60 where a1 = CAST(a1 as varchar(4)) order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t60 where u1 = CAST(u1 as varchar(4)) order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t60 where a1 = CAST(u1 as varchar(4)) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t60 where u1 = CAST(a1 as varchar(4)) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>--
>>select * from cs310t60 where a1 = CAST('abc' as varchar(4) character set ucs2) order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t60 where a1 = CAST(_iso88591'abc' as varchar(4) character set ucs2) order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t60 where u1 = CAST(_iso88591'abc' as varchar(4) character set iso88591) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>--
>>select * from cs310t60 where a1 = CAST(a1 as varchar(4) character set ucs2) order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t60 where a1 = CAST(u1 as varchar(4) character set ucs2) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>select * from cs310t60 where u1 = CAST(a1 as varchar(4) character set iso88591) order by 1,2;
A1 U1
-------- ----------------
abc abc
--- 1 row(s) selected.
>>--
>>-- Tests for UNIONs
>>--
>>create table cs310t70 (a1 char(8) character set iso88591, u1 char(8) character set ucs2 ) no partition;
--- SQL operation complete.
>>insert into cs310t70 values('abc','abc');
--- 1 row(s) inserted.
>>insert into cs310t70 values('abc','def');
--- 1 row(s) inserted.
>>select * from cs310t70 order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>--
>>create table cs310t71 (a1 char(8) character set ucs2, u1 char(8) character set iso88591 ) no partition;
--- SQL operation complete.
>>insert into cs310t71 values('abc','abc');
--- 1 row(s) inserted.
>>insert into cs310t71 values('abc','def');
--- 1 row(s) inserted.
>>select * from cs310t71 order by 1,2;
A1 U1
---------------- --------
abc abc
abc def
--- 2 row(s) selected.
>>--
>>select * from cs310t70 union (select * from cs310t71) order by 1,2;
A1 U1
---------------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t70 union all (select * from cs310t71) order by 1,2;
A1 U1
---------------- ----------------
abc abc
abc abc
abc def
abc def
--- 4 row(s) selected.
>>--
>>select * from cs310t70, cs310t71 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc abc abc def
abc def abc abc
abc def abc def
--- 4 row(s) selected.
>>select * from cs310t70, cs310t71 where cs310t70.a1 = cs310t71.u1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc def abc abc
--- 2 row(s) selected.
>>select * from cs310t70, cs310t71 where cs310t70.u1 = cs310t71.a1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc abc abc def
--- 2 row(s) selected.
>>--
>>select * from cs310t70, cs310t71 where cs310t70.a1 = cs310t71.a1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc abc abc def
abc def abc abc
abc def abc def
--- 4 row(s) selected.
>>select * from cs310t70, cs310t71 where cs310t70.u1 = cs310t71.u1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc def abc def
--- 2 row(s) selected.
>>--
>>select * from cs310t70 union (select * from cs310t70 union (select * from cs310t71)) order by 1,2;
A1 U1
---------------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t70 union (select * from cs310t71 union (select * from cs310t70)) order by 1,2;
A1 U1
---------------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>--
>>select * from cs310t70 natural join cs310t71 order by 1,2;
A1 U1
-------- ----------------
abc abc
abc def
--- 2 row(s) selected.
>>select * from cs310t70 cross join cs310t71 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc abc abc def
abc def abc abc
abc def abc def
--- 4 row(s) selected.
>>select * from cs310t71 cross join cs310t70 order by 1,2,3,4;
A1 U1 A1 U1
---------------- -------- -------- ----------------
abc abc abc abc
abc abc abc def
abc def abc abc
abc def abc def
--- 4 row(s) selected.
>>--
>>select * from cs310t70 inner join cs310t71 on cs310t70.a1 = cs310t71.u1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc def abc abc
--- 2 row(s) selected.
>>select * from cs310t70 inner join cs310t71 on cs310t70.u1 = cs310t71.u1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc def abc def
--- 2 row(s) selected.
>>select * from cs310t70 inner join cs310t71 on cs310t70.u1 = cs310t71.a1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc abc abc def
--- 2 row(s) selected.
>>select * from cs310t70 inner join cs310t71 on cs310t70.a1 = cs310t71.a1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc abc abc def
abc def abc abc
abc def abc def
--- 4 row(s) selected.
>>--
>>select * from cs310t70 left outer join cs310t71 on cs310t70.u1 = cs310t71.a1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc abc abc def
abc def ? ?
--- 3 row(s) selected.
>>select * from cs310t70 left outer join cs310t71 on cs310t70.a1 = cs310t71.u1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc def abc abc
--- 2 row(s) selected.
>>select * from cs310t70 left outer join cs310t71 on cs310t70.u1 = cs310t71.u1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc def abc def
--- 2 row(s) selected.
>>select * from cs310t70 left outer join cs310t71 on cs310t70.a1 = cs310t71.a1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc abc abc def
abc def abc abc
abc def abc def
--- 4 row(s) selected.
>>--
>>select * from cs310t70 right outer join cs310t71 on cs310t70.u1 = cs310t71.a1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc abc abc def
--- 2 row(s) selected.
>>select * from cs310t70 right outer join cs310t71 on cs310t70.a1 = cs310t71.u1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc def abc abc
? ? abc def
--- 3 row(s) selected.
>>select * from cs310t70 right outer join cs310t71 on cs310t70.u1 = cs310t71.u1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc def abc def
--- 2 row(s) selected.
>>select * from cs310t70 right outer join cs310t71 on cs310t70.a1 = cs310t71.a1 order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc abc abc def
abc def abc abc
abc def abc def
--- 4 row(s) selected.
>>--
>>select * from cs310t70, cs310t71 where cs310t70.u1 = (select cs310t71.u1 from cs310t71 where cs310t71.a1 = 'def') order by 1,2,3,4;
--- 0 row(s) selected.
>>--
>>select * from cs310t70, cs310t71 where cs310t70.u1 > any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'def') order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc def abc abc
abc def abc def
--- 2 row(s) selected.
>>select * from cs310t70, cs310t71 where cs310t70.u1 < any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'def') order by 1,2,3,4;
--- 0 row(s) selected.
>>select * from cs310t70, cs310t71 where cs310t70.u1 <= any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'def') order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc abc abc def
--- 2 row(s) selected.
>>select * from cs310t70, cs310t71 where cs310t70.u1 >= any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'def') order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc abc abc def
abc def abc abc
abc def abc def
--- 4 row(s) selected.
>>select * from cs310t70, cs310t71 where cs310t70.u1 > any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'abc') order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc def abc abc
abc def abc def
--- 2 row(s) selected.
>>select * from cs310t70, cs310t71 where cs310t70.u1 < any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'abc') order by 1,2,3,4;
--- 0 row(s) selected.
>>select * from cs310t70, cs310t71 where cs310t70.u1 <= any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'abc') order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc abc abc def
--- 2 row(s) selected.
>>select * from cs310t70, cs310t71 where cs310t70.u1 >= any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'abc') order by 1,2,3,4;
A1 U1 A1 U1
-------- ---------------- ---------------- --------
abc abc abc abc
abc abc abc def
abc def abc abc
abc def abc def
--- 4 row(s) selected.
>>--
>>-- Tests for subqueries
>>--
>>create table cs310t80 (a1 char(8) character set iso88591) no partition;
--- SQL operation complete.
>>insert into cs310t80 values('a1a1');
--- 1 row(s) inserted.
>>insert into cs310t80 values('u1u1');
--- 1 row(s) inserted.
>>create table cs310t81 (u1 char(8) character set ucs2) no partition;
--- SQL operation complete.
>>insert into cs310t81 values('a1a1');
--- 1 row(s) inserted.
>>insert into cs310t81 values('u1u1');
--- 1 row(s) inserted.
>>
>>select * from cs310t80, cs310t81 order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>>select * from cs310t80, cs310t81 where a1 = case when a1 > 'a1a' then u1 else 'a100' end order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 u1u1
--- 2 row(s) selected.
>>select * from cs310t80, cs310t81 where a1 = case when (select min(a1) from cs310t80) > 'a1a' then u1 else 'a100' end order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 u1u1
--- 2 row(s) selected.
>>select * from cs310t80, cs310t81 where u1 = case when (select min(a1) from cs310t80) > 'a1a' then u1 else 'a100' end order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>>--
>>select * from cs310t80, cs310t81 where (select min(a1) from cs310t80) = case when (select min(a1) from cs310t80) > 'a1a' then u1 else 'a100' end order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 a1a1
--- 2 row(s) selected.
>>select * from cs310t80, cs310t81 where (select min(u1) from cs310t81) = case when (select min(a1) from cs310t80) > 'a1a' then u1 else 'a100' end order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 a1a1
--- 2 row(s) selected.
>>--
>>select * from cs310t80, cs310t81 where a1 = case when a1 > 'a1a' then (select min(u1) from cs310t81) else 'a100' end order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t80, cs310t81 where u1 = case when a1 > 'a1a' then (select min(u1) from cs310t81) else 'a100' end order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 a1a1
--- 2 row(s) selected.
>>--
>>select * from cs310t80, cs310t81 where a1 = case when (select min(a1) from cs310t80) > 'a1a' then (select min(u1) from cs310t81) else 'a100' end order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t80, cs310t81 where u1 = case when (select min(a1) from cs310t80) > 'a1a' then (select min(u1) from cs310t81) else 'a100' end order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 a1a1
--- 2 row(s) selected.
>>--
>>select * from cs310t80, cs310t81 where a1 = (select min(case when a1 > 'a1a' then u1 else 'a100' end) from cs310t80, cs310t81) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
--- 2 row(s) selected.
>>select * from cs310t80, cs310t81 where u1 = (select min(case when a1 > 'a1a' then u1 else 'a100' end) from cs310t80, cs310t81) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
u1u1 a1a1
--- 2 row(s) selected.
>>--
>>select * from cs310t80, cs310t81 where (select min(a1) from cs310t80) = (select min(case when a1 > 'a1a' then u1 else 'a100' end) from cs310t80, cs310t81) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>>select * from cs310t80, cs310t81 where (select min(u1) from cs310t81) = (select min(case when a1 > 'a1a' then u1 else 'a100' end) from cs310t80, cs310t81) order by 1,2;
A1 U1
-------- ----------------
a1a1 a1a1
a1a1 u1u1
u1u1 a1a1
u1u1 u1u1
--- 4 row(s) selected.
>>--
>>-- Tests for TRIGGERS
>>--
>>create table cs310t90 (
+> UCS2_col1 varchar(36) character set UCS2 no default not null,
+> UCS2_col2 varchar(36) character set UCS2 no default not null,
+> ISO_col1 varchar(36) character set ISO88591,
+> ISO_col2 varchar(36) character set ISO88591
+> )no partitions;
--- SQL operation complete.
>>--
>>create TRIGGER TRG1_cs310t90 before UPDATE on cs310t90 REFERENCING NEW as TRG FOR EACH ROW
+> set TRG.UCS2_col2 = _ISO88591'abc' ;
*** ERROR[3131] The statement just entered is currently not supported.
*** ERROR[8822] The statement was not prepared.
>>--
>>insert into cs310t90 values('Test1','Test1','Test1','Test1'), ('test111','test1111','test111','test1111');
--- 2 row(s) inserted.
>>UPDATE cs310t90 set ISO_col2 = _ISO88591'Iso1' where UCS2_col1 = _iso88591'Test1';
--- 1 row(s) updated.
>>select * from cs310t90 order by 1,2,3,4;
UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
Test1 Test1 Test1 Iso1
test111 test1111 test111 test1111
--- 2 row(s) selected.
>>-- Do same thing again.
>>UPDATE cs310t90 set ISO_col2 = _ISO88591'Iso1' where UCS2_col1 = _iso88591'Test1';
--- 1 row(s) updated.
>>select * from cs310t90 order by 1,2,3,4;
UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
Test1 Test1 Test1 Iso1
test111 test1111 test111 test1111
--- 2 row(s) selected.
>>--
>>insert into cs310t90 values('Test2','Test2','Test2','Test2'), ('test222','test2222','test222','test2222');
--- 2 row(s) inserted.
>>UPDATE cs310t90 set ISO_col2 = _ucs2'UCS2' where UCS2_col1 = _iso88591'Test2';
--- 1 row(s) updated.
>>select * from cs310t90 order by 1,2,3,4;
UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
Test1 Test1 Test1 Iso1
Test2 Test2 Test2 UCS2
test111 test1111 test111 test1111
test222 test2222 test222 test2222
--- 4 row(s) selected.
>>-- Do same thing again.
>>UPDATE cs310t90 set ISO_col2 = _ucs2'UCS2' where UCS2_col1 = _iso88591'Test2';
--- 1 row(s) updated.
>>select * from cs310t90 order by 1,2,3,4;
UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
Test1 Test1 Test1 Iso1
Test2 Test2 Test2 UCS2
test111 test1111 test111 test1111
test222 test2222 test222 test2222
--- 4 row(s) selected.
>>--
>>insert into cs310t90 values('Test3','Test3','Test3','Test3'), ('test333','test3333','test333','test3333');
--- 2 row(s) inserted.
>>UPDATE cs310t90 set UCS2_col1 = _ISO88591'Iso3' where ISO_col2 = _ucs2'Test3';
--- 1 row(s) updated.
>>select * from cs310t90 order by 1,2,3,4;
UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
Iso3 Test3 Test3 Test3
Test1 Test1 Test1 Iso1
Test2 Test2 Test2 UCS2
test111 test1111 test111 test1111
test222 test2222 test222 test2222
test333 test3333 test333 test3333
--- 6 row(s) selected.
>>-- Do same thing again.
>>UPDATE cs310t90 set UCS2_col1 = _ISO88591'Iso3' where ISO_col2 = _ucs2'Test3';
--- 1 row(s) updated.
>>select * from cs310t90 order by 1,2,3,4;
UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
Iso3 Test3 Test3 Test3
Test1 Test1 Test1 Iso1
Test2 Test2 Test2 UCS2
test111 test1111 test111 test1111
test222 test2222 test222 test2222
test333 test3333 test333 test3333
--- 6 row(s) selected.
>>--
>>DROP TRIGGER TRG1_cs310t90;
*** ERROR[3131] The statement just entered is currently not supported.
*** ERROR[8822] The statement was not prepared.
>>create TRIGGER TRG1_cs310t90 before UPDATE on cs310t90 REFERENCING NEW as TRG FOR EACH ROW
+> set TRG.ISO_col2 = _ucs2'abc' ;
*** ERROR[3131] The statement just entered is currently not supported.
*** ERROR[8822] The statement was not prepared.
>>--
>>insert into cs310t90 values('Test4','Test4','Test4','Test4'), ('test444','test4444','test444','test4444');
--- 2 row(s) inserted.
>>UPDATE cs310t90 set ISO_col1 = _ISO88591'Iso4' where UCS2_col1 = _iso88591'Test4';
--- 1 row(s) updated.
>>select * from cs310t90 order by 1,2,3,4;
UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
Iso3 Test3 Test3 Test3
Test1 Test1 Test1 Iso1
Test2 Test2 Test2 UCS2
Test4 Test4 Iso4 Test4
test111 test1111 test111 test1111
test222 test2222 test222 test2222
test333 test3333 test333 test3333
test444 test4444 test444 test4444
--- 8 row(s) selected.
>>-- Do same thing again.
>>UPDATE cs310t90 set ISO_col1 = _ISO88591'Iso4' where UCS2_col1 = _iso88591'Test4';
--- 1 row(s) updated.
>>select * from cs310t90 order by 1,2,3,4;
UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
Iso3 Test3 Test3 Test3
Test1 Test1 Test1 Iso1
Test2 Test2 Test2 UCS2
Test4 Test4 Iso4 Test4
test111 test1111 test111 test1111
test222 test2222 test222 test2222
test333 test3333 test333 test3333
test444 test4444 test444 test4444
--- 8 row(s) selected.
>>--
>>insert into cs310t90 values('Test5','Test5','Test5','Test5'), ('test555','test5555','test555','test5555');
--- 2 row(s) inserted.
>>UPDATE cs310t90 set ISO_col1 = _ucs2'UCS5' where UCS2_col1 = _iso88591'Test5';
--- 1 row(s) updated.
>>select * from cs310t90 order by 1,2,3,4;
UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
Iso3 Test3 Test3 Test3
Test1 Test1 Test1 Iso1
Test2 Test2 Test2 UCS2
Test4 Test4 Iso4 Test4
Test5 Test5 UCS5 Test5
test111 test1111 test111 test1111
test222 test2222 test222 test2222
test333 test3333 test333 test3333
test444 test4444 test444 test4444
test555 test5555 test555 test5555
--- 10 row(s) selected.
>>-- Do same thing again.
>>UPDATE cs310t90 set ISO_col1 = _ucs2'UCS5' where UCS2_col1 = _iso88591'Test5';
--- 1 row(s) updated.
>>select * from cs310t90 order by 1,2,3,4;
UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
Iso3 Test3 Test3 Test3
Test1 Test1 Test1 Iso1
Test2 Test2 Test2 UCS2
Test4 Test4 Iso4 Test4
Test5 Test5 UCS5 Test5
test111 test1111 test111 test1111
test222 test2222 test222 test2222
test333 test3333 test333 test3333
test444 test4444 test444 test4444
test555 test5555 test555 test5555
--- 10 row(s) selected.
>>--
>>insert into cs310t90 values('Test6','Test6','Test6','Test6'), ('test666','test6666','test666','test6666');
--- 2 row(s) inserted.
>>UPDATE cs310t90 set UCS2_col1 = _ISO88591'Iso6' where UCS2_col2 = _ucs2'Test6';
--- 1 row(s) updated.
>>select * from cs310t90 order by 1,2,3,4;
UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
Iso3 Test3 Test3 Test3
Iso6 Test6 Test6 Test6
Test1 Test1 Test1 Iso1
Test2 Test2 Test2 UCS2
Test4 Test4 Iso4 Test4
Test5 Test5 UCS5 Test5
test111 test1111 test111 test1111
test222 test2222 test222 test2222
test333 test3333 test333 test3333
test444 test4444 test444 test4444
test555 test5555 test555 test5555
test666 test6666 test666 test6666
--- 12 row(s) selected.
>>-- Do same thing again.
>>UPDATE cs310t90 set UCS2_col1 = _ISO88591'Iso6' where UCS2_col2 = _ucs2'Test6';
--- 1 row(s) updated.
>>select * from cs310t90 order by 1,2,3,4;
UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
Iso3 Test3 Test3 Test3
Iso6 Test6 Test6 Test6
Test1 Test1 Test1 Iso1
Test2 Test2 Test2 UCS2
Test4 Test4 Iso4 Test4
Test5 Test5 UCS5 Test5
test111 test1111 test111 test1111
test222 test2222 test222 test2222
test333 test3333 test333 test3333
test444 test4444 test444 test4444
test555 test5555 test555 test5555
test666 test6666 test666 test6666
--- 12 row(s) selected.
>>--
>>-- Tests for TRIGGERS on tables with primary key.
>>-- NOTE: Tables with a primary key column can take different paths through optimizer.
>>--
>>create table cs310t91 (
+> PRKY int no default not null,
+> UCS2_col1 varchar(36) character set UCS2 no default not null,
+> UCS2_col2 varchar(36) character set UCS2 no default not null,
+> ISO_col1 varchar(36) character set ISO88591,
+> ISO_col2 varchar(36) character set ISO88591,
+> PRIMARY KEY (PRKY) ) STORE BY primary key;
--- SQL operation complete.
>>--
>>create TRIGGER TRG1_cs310t91 before UPDATE on cs310t91 REFERENCING NEW as TRG FOR EACH ROW
+> set TRG.UCS2_col2 = _ISO88591'abc' ;
*** ERROR[3131] The statement just entered is currently not supported.
*** ERROR[8822] The statement was not prepared.
>>--
>>insert into cs310t91 values(1,'Test1','Test1','Test1','Test1'), (2,'test111','test1111','test111','test1111');
--- 2 row(s) inserted.
>>UPDATE cs310t91 set ISO_col2 = _ISO88591'Iso1' where UCS2_col1 = _iso88591'Test1';
--- 1 row(s) updated.
>>select * from cs310t91 order by 1,2,3,4;
PRKY UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
----------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
1 Test1 Test1 Test1 Iso1
2 test111 test1111 test111 test1111
--- 2 row(s) selected.
>>-- Do same thing again.
>>UPDATE cs310t91 set ISO_col2 = _ISO88591'Iso1' where UCS2_col1 = _iso88591'Test1';
--- 1 row(s) updated.
>>select * from cs310t91 order by 1,2,3,4;
PRKY UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
----------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
1 Test1 Test1 Test1 Iso1
2 test111 test1111 test111 test1111
--- 2 row(s) selected.
>>--
>>insert into cs310t91 values(3,'Test2','Test2','Test2','Test2'), (4,'test222','test2222','test222','test2222');
--- 2 row(s) inserted.
>>UPDATE cs310t91 set ISO_col2 = _ucs2'UCS2' where UCS2_col1 = _iso88591'Test2';
--- 1 row(s) updated.
>>select * from cs310t91 order by 1,2,3,4;
PRKY UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
----------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
1 Test1 Test1 Test1 Iso1
2 test111 test1111 test111 test1111
3 Test2 Test2 Test2 UCS2
4 test222 test2222 test222 test2222
--- 4 row(s) selected.
>>-- Do same thing again.
>>UPDATE cs310t91 set ISO_col2 = _ucs2'UCS2' where UCS2_col1 = _iso88591'Test2';
--- 1 row(s) updated.
>>select * from cs310t91 order by 1,2,3,4;
PRKY UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
----------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
1 Test1 Test1 Test1 Iso1
2 test111 test1111 test111 test1111
3 Test2 Test2 Test2 UCS2
4 test222 test2222 test222 test2222
--- 4 row(s) selected.
>>--
>>insert into cs310t91 values(5,'Test3','Test3','Test3','Test3'), (6,'test333','test3333','test333','test3333');
--- 2 row(s) inserted.
>>UPDATE cs310t91 set UCS2_col1 = _ISO88591'Iso3' where ISO_col2 = _ucs2'Test3';
--- 1 row(s) updated.
>>select * from cs310t91 order by 1,2,3,4;
PRKY UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
----------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
1 Test1 Test1 Test1 Iso1
2 test111 test1111 test111 test1111
3 Test2 Test2 Test2 UCS2
4 test222 test2222 test222 test2222
5 Iso3 Test3 Test3 Test3
6 test333 test3333 test333 test3333
--- 6 row(s) selected.
>>-- Do same thing again.
>>UPDATE cs310t91 set UCS2_col1 = _ISO88591'Iso3' where ISO_col2 = _ucs2'Test3';
--- 1 row(s) updated.
>>select * from cs310t91 order by 1,2,3,4;
PRKY UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
----------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
1 Test1 Test1 Test1 Iso1
2 test111 test1111 test111 test1111
3 Test2 Test2 Test2 UCS2
4 test222 test2222 test222 test2222
5 Iso3 Test3 Test3 Test3
6 test333 test3333 test333 test3333
--- 6 row(s) selected.
>>--
>>DROP TRIGGER TRG1_cs310t91;
*** ERROR[3131] The statement just entered is currently not supported.
*** ERROR[8822] The statement was not prepared.
>>create TRIGGER TRG1_cs310t91 before UPDATE on cs310t91 REFERENCING NEW as TRG FOR EACH ROW
+> set TRG.ISO_col2 = _ucs2'abc' ;
*** ERROR[3131] The statement just entered is currently not supported.
*** ERROR[8822] The statement was not prepared.
>>--
>>insert into cs310t91 values(7,'Test4','Test4','Test4','Test4'), (8,'test444','test4444','test444','test4444');
--- 2 row(s) inserted.
>>UPDATE cs310t91 set ISO_col1 = _ISO88591'Iso4' where UCS2_col1 = _iso88591'Test4';
--- 1 row(s) updated.
>>select * from cs310t91 order by 1,2,3,4;
PRKY UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
----------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
1 Test1 Test1 Test1 Iso1
2 test111 test1111 test111 test1111
3 Test2 Test2 Test2 UCS2
4 test222 test2222 test222 test2222
5 Iso3 Test3 Test3 Test3
6 test333 test3333 test333 test3333
7 Test4 Test4 Iso4 Test4
8 test444 test4444 test444 test4444
--- 8 row(s) selected.
>>-- Do same thing again.
>>UPDATE cs310t91 set ISO_col1 = _ISO88591'Iso4' where UCS2_col1 = _iso88591'Test4';
--- 1 row(s) updated.
>>select * from cs310t91 order by 1,2,3,4;
PRKY UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
----------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
1 Test1 Test1 Test1 Iso1
2 test111 test1111 test111 test1111
3 Test2 Test2 Test2 UCS2
4 test222 test2222 test222 test2222
5 Iso3 Test3 Test3 Test3
6 test333 test3333 test333 test3333
7 Test4 Test4 Iso4 Test4
8 test444 test4444 test444 test4444
--- 8 row(s) selected.
>>--
>>insert into cs310t91 values(9,'Test5','Test5','Test5','Test5'), (10,'test555','test5555','test555','test5555');
--- 2 row(s) inserted.
>>UPDATE cs310t91 set ISO_col1 = _ucs2'UCS5' where UCS2_col1 = _iso88591'Test5';
--- 1 row(s) updated.
>>select * from cs310t91 order by 1,2,3,4;
PRKY UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
----------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
1 Test1 Test1 Test1 Iso1
2 test111 test1111 test111 test1111
3 Test2 Test2 Test2 UCS2
4 test222 test2222 test222 test2222
5 Iso3 Test3 Test3 Test3
6 test333 test3333 test333 test3333
7 Test4 Test4 Iso4 Test4
8 test444 test4444 test444 test4444
9 Test5 Test5 UCS5 Test5
10 test555 test5555 test555 test5555
--- 10 row(s) selected.
>>-- Do same thing again.
>>UPDATE cs310t91 set ISO_col1 = _ucs2'UCS5' where UCS2_col1 = _iso88591'Test5';
--- 1 row(s) updated.
>>select * from cs310t91 order by 1,2,3,4;
PRKY UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
----------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
1 Test1 Test1 Test1 Iso1
2 test111 test1111 test111 test1111
3 Test2 Test2 Test2 UCS2
4 test222 test2222 test222 test2222
5 Iso3 Test3 Test3 Test3
6 test333 test3333 test333 test3333
7 Test4 Test4 Iso4 Test4
8 test444 test4444 test444 test4444
9 Test5 Test5 UCS5 Test5
10 test555 test5555 test555 test5555
--- 10 row(s) selected.
>>--
>>insert into cs310t91 values(11,'Test6','Test6','Test6','Test6'), (12,'test666','test6666','test666','test6666');
--- 2 row(s) inserted.
>>UPDATE cs310t91 set UCS2_col1 = _ISO88591'Iso6' where UCS2_col2 = _ucs2'Test6';
--- 1 row(s) updated.
>>select * from cs310t91 order by 1,2,3,4;
PRKY UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
----------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
1 Test1 Test1 Test1 Iso1
2 test111 test1111 test111 test1111
3 Test2 Test2 Test2 UCS2
4 test222 test2222 test222 test2222
5 Iso3 Test3 Test3 Test3
6 test333 test3333 test333 test3333
7 Test4 Test4 Iso4 Test4
8 test444 test4444 test444 test4444
9 Test5 Test5 UCS5 Test5
10 test555 test5555 test555 test5555
11 Iso6 Test6 Test6 Test6
12 test666 test6666 test666 test6666
--- 12 row(s) selected.
>>-- Do same thing again.
>>UPDATE cs310t91 set UCS2_col1 = _ISO88591'Iso6' where UCS2_col2 = _ucs2'Test6';
--- 1 row(s) updated.
>>select * from cs310t91 order by 1,2,3,4;
PRKY UCS2_COL1 UCS2_COL2 ISO_COL1 ISO_COL2
----------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ ------------------------------------
1 Test1 Test1 Test1 Iso1
2 test111 test1111 test111 test1111
3 Test2 Test2 Test2 UCS2
4 test222 test2222 test222 test2222
5 Iso3 Test3 Test3 Test3
6 test333 test3333 test333 test3333
7 Test4 Test4 Iso4 Test4
8 test444 test4444 test444 test4444
9 Test5 Test5 UCS5 Test5
10 test555 test5555 test555 test5555
11 Iso6 Test6 Test6 Test6
12 test666 test6666 test666 test6666
--- 12 row(s) selected.
>>--
>>-- Tests for multi-row VALUES
>>--
>>create table cs310t92 (
+> bname CHAR(66) NO DEFAULT NOT NULL
+> )no partitions;
--- SQL operation complete.
>>insert into cs310t92 VALUES(_iso88591'abcdefghi'), ('xyz');
--- 2 row(s) inserted.
>>insert into cs310t92 VALUES(converttohex('abcdefghi')), (converttohex('xyz'));
--- 2 row(s) inserted.
>>--
>>-- Tests for RPAD and LPAD
>>--
>>insert into cs310t92 select RPAD(_ISO88591 x'c3b6c3bac3bb' || '789ABCDEFG',10) from (values(1)) x(a);
--- 1 row(s) inserted.
>>insert into cs310t92 select LPAD(_UCS2'456' || '789',10) from (values(1)) x(a);
--- 1 row(s) inserted.
>>
>>select * from cs310t92 order by 1;
BNAME
------------------------------------------------------------------
456789
616263646566676869
78797A
abcdefghi
xyz
öúû789A
--- 6 row(s) selected.
>>select left(bname,40) from cs310t92 order by 1;
(EXPR)
----------------------------------------
456789
616263646566676869
78797A
abcdefghi
xyz
öúû789A
--- 6 row(s) selected.
>>---
>>create table cs310t93(a int, c char(10) character set utf8) no partition;
--- SQL operation complete.
>>insert into cs310t93 values (1,'a') ,(2,null);
--- 2 row(s) inserted.
>>-- try mixing character sets
>>insert into cs310t93 values (3, _ucs2 x'4EBA'), (4,'b');
--- 2 row(s) inserted.
>>insert into cs310t93 values (5, 'c'), (6, _ucs2 x'4EBA');
--- 2 row(s) inserted.
>>insert into cs310t93 values (7, 'd'), (8, _utf8 x'E4BABA');
--- 2 row(s) inserted.
>>insert into cs310t93 values (9, _utf8 x'E4BABA'), (10, 'e'), (11, _ucs2 x'4EBB4EBC4EBD4EBE') ;
--- 3 row(s) inserted.
>>insert into cs310t93 values (12, _ucs2 x'4EBB4EBC4EBD4EBE'), (13,'f'), (14,_utf8 x'E4BABAE4BABB');
--- 3 row(s) inserted.
>>insert into cs310t93 values (15, _ucs2 x'4EBB4EBC4EBD4EBE'), (16,_utf8 x'E4BABAE4BABB'),(17,'g');
--- 3 row(s) inserted.
>>--- gbk char followed by null
>>insert into cs310t93 values (18, _ucs2 x'4EBA'), (19,null);
--- 2 row(s) inserted.
>>insert into cs310t93 values (20, _utf8 x'E4BABA'),(21, 'h'),(22,_ucs2 x'4EBB4EBC4EBD4EBE'),(23,null) ;
--- 4 row(s) inserted.
>>--- null followed by gbk char
>>insert into cs310t93 values (24, null), (25, _ucs2 x'4EBA');
--- 2 row(s) inserted.
>>insert into cs310t93 values (26, null), (27, _utf8 x'E4BABA'),(28, 'i'),(29,_ucs2 x'4EBB4EBC4EBD4EBE') ;
--- 4 row(s) inserted.
>>select a, converttohex(c) from cs310t93 order by a;
A (EXPR)
----------- --------------------------------------------------------------------------------
1 61202020202020202020
2 ?
3 E4BABA202020202020202020
4 62202020202020202020
5 63202020202020202020
6 E4BABA202020202020202020
7 64202020202020202020
8 E4BABA202020202020202020
9 E4BABA202020202020202020
10 65202020202020202020
11 E4BABBE4BABCE4BABDE4BABE202020202020
12 E4BABBE4BABCE4BABDE4BABE202020202020
13 66202020202020202020
14 E4BABAE4BABB2020202020202020
15 E4BABBE4BABCE4BABDE4BABE202020202020
16 E4BABAE4BABB2020202020202020
17 67202020202020202020
18 E4BABA202020202020202020
19 ?
20 E4BABA202020202020202020
21 68202020202020202020
22 E4BABBE4BABCE4BABDE4BABE202020202020
23 ?
24 ?
25 E4BABA202020202020202020
26 ?
27 E4BABA202020202020202020
28 69202020202020202020
29 E4BABBE4BABCE4BABDE4BABE202020202020
--- 29 row(s) selected.
>>
>>
>>obey test310(clnup);
>>
>>drop schema cs310s cascade;
--- SQL operation complete.
>>
>>
>>log;