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