>>
>>obey test313(ddl);
>>
>>create schema cs313s;

--- SQL operation complete.
>>set schema cs313s;

--- SQL operation complete.
>>
>>
>>obey test313(icat_tests);
>>
>>--
>>-- Tests for ASCII function
>>--
>>create volatile table cs313t1 (a1 char(32 BYTES) character set utf8) no partition;

--- SQL operation complete.
>>insert into cs313t1 values('a1a1');

--- 1 row(s) inserted.
>>insert into cs313t1 values('u1u1');

--- 1 row(s) inserted.
>>create volatile table cs313t2 (u1 char(8) character set ucs2) no partition;

--- SQL operation complete.
>>insert into cs313t2 values('a1a1');

--- 1 row(s) inserted.
>>insert into cs313t2 values('u1u1');

--- 1 row(s) inserted.
>>insert into cs313t2 values(_utf8 x'C3B6C3BAC3BB');

--- 1 row(s) inserted.
>>insert into cs313t2 values(_utf8 x'C396C39AC39B');

--- 1 row(s) inserted.
>>insert into cs313t2 values(_utf8 x'E0A0A0E0A0A1E0A0A2');

--- 1 row(s) inserted.
>>--
>>select * from cs313t1 order by 1;

A1                              
--------------------------------

a1a1                            
u1u1                            

--- 2 row(s) selected.
>>select * from cs313t2 order by 1;

U1              
----------------

a1a1            
u1u1            
             
             
???             

--- 5 row(s) selected.
>>select a1, char_length(a1) from cs313t1 order by 1;

A1                                (EXPR)    
--------------------------------  ----------

a1a1                                      32
u1u1                                      32

--- 2 row(s) selected.
>>select u1, char_length(u1) from cs313t2 order by 1;

U1                (EXPR)    
----------------  ----------

a1a1                       8
u1u1                       8
                        8
                        8
???                        8

--- 5 row(s) selected.
>>select CONVERTTOHEX( u1 ) from cs313t2 where u1 = _utf8 x'C3B6C3BAC3BB';

(EXPR)                          
--------------------------------

00F600FA00FB00200020002000200020

--- 1 row(s) selected.
>>select CONVERTTOHEX( u1 ) from cs313t2 where u1 = _utf8 x'E0A0A0E0A0A1E0A0A2';

(EXPR)                          
--------------------------------

08200821082200200020002000200020

--- 1 row(s) selected.
>>select CONVERTTOHEX( TRANSLATE( u1 using UCS2toUTF8 ) ) from cs313t2 where u1 = _utf8 x'C3B6C3BAC3BB';

(EXPR)                                          
------------------------------------------------

C3B6C3BAC3BB2020202020                          

--- 1 row(s) selected.
>>select CONVERTTOHEX( TRANSLATE( u1 using UCS2toUTF8 ) ) from cs313t2 where u1 = _utf8 x'E0A0A0E0A0A1E0A0A2';

(EXPR)                                          
------------------------------------------------

E0A0A0E0A0A1E0A0A22020202020                    

--- 1 row(s) selected.
>>select CONVERTTOHEX( u1 ) from cs313t2 where u1 = UPPER(_utf8 x'C3B6C3BAC3BB');

(EXPR)                          
--------------------------------

00D600DA00DB00200020002000200020

--- 1 row(s) selected.
>>select CONVERTTOHEX( TRANSLATE( u1 using UCS2toUTF8 ) ) from cs313t2 where u1 = UPPER(_utf8 x'C3B6C3BAC3BB');

(EXPR)                                          
------------------------------------------------

C396C39AC39B2020202020                          

--- 1 row(s) selected.
>>select CONVERTTOHEX( TRANSLATE( u1 using UCS2toUTF8 ) ) from cs313t2 where TRANSLATE(u1 using UCS2toUTF8) = _ucs2 x'00D600DA00DB';

(EXPR)                                          
------------------------------------------------

C396C39AC39B2020202020                          

--- 1 row(s) selected.
>>select CONVERTTOHEX( TRANSLATE( u1 using UCS2toUTF8 ) ) from cs313t2 where TRANSLATE(u1 using UCS2toUTF8) = _ucs2 x'00F600FA00FB';

(EXPR)                                          
------------------------------------------------

C3B6C3BAC3BB2020202020                          

--- 1 row(s) selected.
>>select CONVERTTOHEX( u1 ) from cs313t2 where u1 = LOWER(_utf8 x'C396C39AC39B');

(EXPR)                          
--------------------------------

00F600FA00FB00200020002000200020

--- 1 row(s) selected.
>>select CONVERTTOHEX( TRANSLATE( u1 using UCS2toUTF8 ) ) from cs313t2 where u1 = LOWER(_utf8 x'C396C39AC39B');

(EXPR)                                          
------------------------------------------------

C3B6C3BAC3BB2020202020                          

--- 1 row(s) selected.
>>--
>>select ascii('a') from cs313t1 order by 1;

(EXPR)    
----------

        97
        97

--- 2 row(s) selected.
>>select ascii(CHAR(97)) from cs313t1 order by 1;

(EXPR)    
----------

        97
        97

--- 2 row(s) selected.
>>select ascii(CHAR(97, UTF8)) from cs313t1 order by 1;

(EXPR)    
----------

        97
        97

--- 2 row(s) selected.
>>select ascii(CHAR(97, ISO88591)) from cs313t1 order by 1;

(EXPR)    
----------

        97
        97

--- 2 row(s) selected.
>>select ascii( _utf8'a') from cs313t1 order by 1;

(EXPR)    
----------

        97
        97

--- 2 row(s) selected.
>>select ascii(TRANSLATE(u1 using UCS2toUTF8)) from cs313t2 where u1 < _ISO88591 x'8000000000000000' order by 1;

(EXPR)    
----------

        97
       117

--- 2 row(s) selected.
>>select ascii(TRANSLATE(u1 using UCS2toUTF8)) from cs313t2 where u1 <= _utf8 x'7f00000000000000' order by 1;

(EXPR)    
----------

        97
       117

--- 2 row(s) selected.
>>--The following queries should get an error at runtime
>>select ascii(TRANSLATE(u1 using UCS2toUTF8)) from cs313t2 where u1 > _ISO88591 x'7f' order by 1;

*** ERROR[8428] The argument to function ASCII is not valid.

--- 0 row(s) selected.
>>select ascii(TRANSLATE(u1 using UCS2toUTF8)) from cs313t2 where u1 > _utf8 x'7f 00000000000000' order by 1;

*** ERROR[8428] The argument to function ASCII is not valid.

--- 0 row(s) selected.
>>--
>>--The following queries should get an error since ASCII doesn't work on UCS2
>>select ascii(CHAR(97, UCS2)) from cs313t1 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 cs313t1 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 cs313t2 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(_utf8'abcdefghijklmnop' using UTF8ToUCS2) ) from cs313t2 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 cs313t2, cs313t1 order by 1,2;

U1                A1                              
----------------  --------------------------------

a1a1              a1a1                            
a1a1              u1u1                            
u1u1              a1a1                            
u1u1              u1u1                            
               a1a1                            
               u1u1                            
               a1a1                            
               u1u1                            
???               a1a1                            
???               u1u1                            

--- 10 row(s) selected.
>>select * from cs313t2, cs313t1 where 'a' = CHAR(ASCII('a1a1')) order by 1,2;

U1                A1                              
----------------  --------------------------------

a1a1              a1a1                            
a1a1              u1u1                            
u1u1              a1a1                            
u1u1              u1u1                            
               a1a1                            
               u1u1                            
               a1a1                            
               u1u1                            
???               a1a1                            
???               u1u1                            

--- 10 row(s) selected.
>>select * from cs313t2, cs313t1 where 'u' = CHAR(ASCII('u1u1'),UTF8) order by 1,2;

U1                A1                              
----------------  --------------------------------

a1a1              a1a1                            
a1a1              u1u1                            
u1u1              a1a1                            
u1u1              u1u1                            
               a1a1                            
               u1u1                            
               a1a1                            
               u1u1                            
???               a1a1                            
???               u1u1                            

--- 10 row(s) selected.
>>select * from cs313t2, cs313t1 where 'u' = lower(CHAR(ASCII(upper(a1)))) order by 1,2;

U1                A1                              
----------------  --------------------------------

a1a1              u1u1                            
u1u1              u1u1                            
               u1u1                            
               u1u1                            
???               u1u1                            

--- 5 row(s) selected.
>>select * from cs313t2, cs313t1 where CHAR(ASCII('a1a1')) = 'a' order by 1,2;

U1                A1                              
----------------  --------------------------------

a1a1              a1a1                            
a1a1              u1u1                            
u1u1              a1a1                            
u1u1              u1u1                            
               a1a1                            
               u1u1                            
               a1a1                            
               u1u1                            
???               a1a1                            
???               u1u1                            

--- 10 row(s) selected.
>>select * from cs313t2, cs313t1 where CHAR(ASCII('u1u1'),UTF8) = 'u' order by 1,2;

U1                A1                              
----------------  --------------------------------

a1a1              a1a1                            
a1a1              u1u1                            
u1u1              a1a1                            
u1u1              u1u1                            
               a1a1                            
               u1u1                            
               a1a1                            
               u1u1                            
???               a1a1                            
???               u1u1                            

--- 10 row(s) selected.
>>select * from cs313t2, cs313t1 where lower(CHAR(ASCII(upper(a1)))) = 'u' order by 1,2;

U1                A1                              
----------------  --------------------------------

a1a1              u1u1                            
u1u1              u1u1                            
               u1u1                            
               u1u1                            
???               u1u1                            

--- 5 row(s) selected.
>>--
>>select ascii((select 'a1a1' from cs313t1 where a1='u1u1')) from cs313t1 order by 1;

(EXPR)    
----------

        97
        97

--- 2 row(s) selected.
>>select ascii((select 'a1a1' from cs313t2 where u1='a1a1')) from cs313t1 order by 1;

(EXPR)    
----------

        97
        97

--- 2 row(s) selected.
>>--
>>select a1 from cs313t1 where ascii(a1) = ascii((select min(a1) from cs313t1)) order by 1;

A1                              
--------------------------------

a1a1                            

--- 1 row(s) selected.
>>--The following queries should get an error at runtime
>>select a1 from cs313t1 where ascii(a1) = ascii((select max(TRANSLATE(u1 using UCS2toUTF8)) from cs313t2)) order by 1;

*** ERROR[8428] The argument to function ASCII is not valid.

--- 0 row(s) selected.
>>select a1 from cs313t1 where ascii(a1) = ascii((select TRANSLATE(max(u1) using UCS2toUTF8) from cs313t2)) order by 1;

*** ERROR[8428] The argument to function ASCII is not valid.

--- 0 row(s) selected.
>>select a1 from cs313t1 where ascii(a1) = ascii((select max(TRANSLATE(u1 using UCS2toUTF8)) from cs313t2)) order by 1;

*** ERROR[8428] The argument to function ASCII is not valid.

--- 0 row(s) selected.
>>--
>>-- But this should work:
>>select a1 from cs313t1 where ascii(a1) = ascii((select max(TRANSLATE(u1 using UCS2toUTF8)) from cs313t2 where u1 <= _utf8 x'7f00000000000000')) order by 1;

A1                              
--------------------------------

u1u1                            

--- 1 row(s) selected.
>>--
>>select a1 from cs313t1 where ascii(a1) = ascii((select min(a1) from cs313t1)) order by 1;

A1                              
--------------------------------

a1a1                            

--- 1 row(s) selected.
>>--
>>select ascii((select CHAR(97) from cs313t1 where a1='u1u1')) from cs313t1 order by 1;

(EXPR)    
----------

        97
        97

--- 2 row(s) selected.
>>select ascii((select CHAR(97) from cs313t2 where u1='a1a1')) from cs313t1 order by 1;

(EXPR)    
----------

        97
        97

--- 2 row(s) selected.
>>--
>>select a1 from cs313t1 where              97 = ascii((select min('a') from cs313t1 where a1='u1u1')) order by 1;

A1                              
--------------------------------

a1a1                            
u1u1                            

--- 2 row(s) selected.
>>select a1 from cs313t1 where ascii(CHAR(97)) = ascii((select min('a') from cs313t1 where a1='u1u1')) order by 1;

A1                              
--------------------------------

a1a1                            
u1u1                            

--- 2 row(s) selected.
>>select a1 from cs313t1 where ascii(CHAR(97)) = ascii((select min(_utf8'a') from cs313t1 where a1='u1u1')) order by 1;

A1                              
--------------------------------

a1a1                            
u1u1                            

--- 2 row(s) selected.
>>select a1 from cs313t1 where              97 = ascii((select min(CHAR(97)) from cs313t1 where a1='u1u1')) order by 1;

A1                              
--------------------------------

a1a1                            
u1u1                            

--- 2 row(s) selected.
>>select a1 from cs313t1 where ascii(CHAR(97)) = ascii((select min(CHAR(97)) from cs313t1 where a1='u1u1')) order by 1;

A1                              
--------------------------------

a1a1                            
u1u1                            

--- 2 row(s) selected.
>>--
>>select a1 from cs313t1 where              97 = ascii((select min('a') from cs313t2 where u1='a1a1')) order by 1;

A1                              
--------------------------------

a1a1                            
u1u1                            

--- 2 row(s) selected.
>>select a1 from cs313t1 where ascii(CHAR(97)) = ascii((select min('a') from cs313t2 where u1='a1a1')) order by 1;

A1                              
--------------------------------

a1a1                            
u1u1                            

--- 2 row(s) selected.
>>select a1 from cs313t1 where ascii(CHAR(97)) = ascii((select min(_utf8'a') from cs313t2 where u1='a1a1')) order by 1;

A1                              
--------------------------------

a1a1                            
u1u1                            

--- 2 row(s) selected.
>>select a1 from cs313t1 where              97 = ascii((select min(CHAR(97)) from cs313t2 where u1='a1a1')) order by 1;

A1                              
--------------------------------

a1a1                            
u1u1                            

--- 2 row(s) selected.
>>select a1 from cs313t1 where ascii(CHAR(97)) = ascii((select min(CHAR(97)) from cs313t2 where u1='a1a1')) order by 1;

A1                              
--------------------------------

a1a1                            
u1u1                            

--- 2 row(s) selected.
>>--
>>-- Tests for basic UNIONs
>>--
>>create volatile table cs313t3 (a1 char(32 BYTES) character set utf8) no partition;

--- SQL operation complete.
>>insert into cs313t3 values('a1a1');

--- 1 row(s) inserted.
>>insert into cs313t3 values('u1u1');

--- 1 row(s) inserted.
>>insert into cs313t3 values(_utf8 x'C3B6C3B7C3B8');

--- 1 row(s) inserted.
>>insert into cs313t3 values(_utf8 x'E0A0A0E0A0A1E0A0A2');

--- 1 row(s) inserted.
>>select * from cs313t3 order by 1;

A1                              
--------------------------------

a1a1                            
u1u1                            
                             
???                             

--- 4 row(s) selected.
>>select a1, char_length(a1) from cs313t3 order by 1;

A1                                (EXPR)    
--------------------------------  ----------

a1a1                                      32
u1u1                                      32
                                       29
???                                       26

--- 4 row(s) selected.
>>create volatile table cs313t4 (u1 char(8) character set ucs2) no partition;

--- SQL operation complete.
>>insert into cs313t4 values('a1a1');

--- 1 row(s) inserted.
>>insert into cs313t4 values('u1u1');

--- 1 row(s) inserted.
>>insert into cs313t4 values(_ucs2 x'00F600F700F8');

--- 1 row(s) inserted.
>>insert into cs313t4 values(_utf8 x'E0A0A0E0A0A1E0A0A2');

--- 1 row(s) inserted.
>>select * from cs313t4 order by 1;

U1              
----------------

a1a1            
u1u1            
             
???             

--- 4 row(s) selected.
>>select u1, char_length(u1) from cs313t4 order by 1;

U1                (EXPR)    
----------------  ----------

a1a1                       8
u1u1                       8
                        8
???                        8

--- 4 row(s) selected.
>>select * from cs313t3 union (select * from cs313t4) order by 1;

A1                                                              
----------------------------------------------------------------

a1a1                                                            
u1u1                                                            
                                                             
???                                                             

--- 4 row(s) selected.
>>select * from cs313t3 union all (select * from cs313t4) order by 1;

A1                                                              
----------------------------------------------------------------

a1a1                                                            
a1a1                                                            
u1u1                                                            
u1u1                                                            
                                                             
                                                             
???                                                             
???                                                             

--- 8 row(s) selected.
>>--
>>-- Tests for basic SQL functions
>>--
>>select * from cs313t3, cs313t4 order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             
u1u1                              a1a1            
u1u1                              u1u1            
u1u1                                           
u1u1                              ???             
                               a1a1            
                               u1u1            
                                            
                               ???             
???                               a1a1            
???                               u1u1            
???                                            
???                               ???             

--- 16 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = case when a1 > 'a1a' then u1 else 'a100' end order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              u1u1            
                                            
???                               ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = case when a1 > 'a1a' then 'a1a1' else 'a100' end order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = coalesce(cast(NULL as varchar(8) character set UTF8), u1, a1) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              u1u1            
                                            
???                               ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = substr('a1a1zzzz',1,4) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = substr(_utf8 x'C3B6'||'a1a1zzzz',2,4) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = substr(_utf8 x'C3B6C3BAC3BB'||'a1a1zzzz',4,4) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = substr(_utf8 x'E0A0A0'||'a1a1zzzz',2,4) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = concat( substr(a1,1,2), 'a1' ) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 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 cs313t3, cs313t4 where a1 = decode(a1,'a1a1','Found', u1, 'u1u1', _utf8 x'C3B6', 'b1b1', 'NotFound') order by 1,2;

A1                                U1              
--------------------------------  ----------------

u1u1                              u1u1            

--- 1 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = decode(a1,'a1a1','Found', u1, 'u1u1', _utf8 x'E0A0A0', 'b1b1', 'NotFound') order by 1,2;

A1                                U1              
--------------------------------  ----------------

u1u1                              u1u1            

--- 1 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = decode(a1,'a1a1','Found', _utf8 x'E0A0A0', 'b1b1', u1, 'u1u1', 'NotFound') order by 1,2;

A1                                U1              
--------------------------------  ----------------

u1u1                              u1u1            

--- 1 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = insert('a1', 2, 0, '1a') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = replace(u1, 'u1', 'a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
                                            
???                               ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = replace('u1u1', 'u1', 'a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = replace(_UTF8'u1u1', 'u1', 'a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = replace('u1u1', _UTF8'u1', 'a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = replace('u1u1', 'u1', _UTF8'a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = isnull(a1,'a1a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             
u1u1                              a1a1            
u1u1                              u1u1            
u1u1                                           
u1u1                              ???             
                               a1a1            
                               u1u1            
                                            
                               ???             
???                               a1a1            
???                               u1u1            
???                                            
???                               ???             

--- 16 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = isnull(u1,'a1a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              u1u1            
                                            
???                               ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = isnull('a1a1','a1a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = isnull(CAST(NULL as char(8) character set utf8),'a1a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = LOWER('A1A1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = LEFT('a1a1ZZZZ',4) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = LEFT('a1a1ZZZZ'||_utf8 x'C3B6C3BA',4) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = LEFT('a1a1ZZZZ'||_utf8 x'E0A0A0',4) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = LPAD('a1',4,'a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = LPAD(substr(a1,1,2),4,'a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = NULLIF(a1,u1) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              u1u1            
a1a1                                           
a1a1                              ???             
u1u1                              a1a1            
u1u1                                           
u1u1                              ???             
                               a1a1            
                               u1u1            
                               ???             
???                               a1a1            
???                               u1u1            
???                                            

--- 12 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = NULLIF(a1,'a1b1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             
u1u1                              a1a1            
u1u1                              u1u1            
u1u1                                           
u1u1                              ???             
                               a1a1            
                               u1u1            
                                            
                               ???             
???                               a1a1            
???                               u1u1            
???                                            
???                               ???             

--- 16 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = NVL(a1,'a1b1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             
u1u1                              a1a1            
u1u1                              u1u1            
u1u1                                           
u1u1                              ???             
                               a1a1            
                               u1u1            
                                            
                               ???             
???                               a1a1            
???                               u1u1            
???                                            
???                               ???             

--- 16 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = NVL(a1,'a1a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             
u1u1                              a1a1            
u1u1                              u1u1            
u1u1                                           
u1u1                              ???             
                               a1a1            
                               u1u1            
                                            
                               ???             
???                               a1a1            
???                               u1u1            
???                                            
???                               ???             

--- 16 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = NVL('a1a1','a1b1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = NVL(cast(NULL as char(8) character set utf8),'a1a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = NVL(cast(NULL as char(8)),'a1a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = right(TRIM(u1),4) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              u1u1            
                                            
???                               ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = right('    ' || TRIM(u1),4) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              u1u1            

--- 2 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = right(_UTF8'    ' || TRIM(u1),4) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              u1u1            

--- 2 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = right('    ' || TRIM(a1),4) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             
u1u1                              a1a1            
u1u1                              u1u1            
u1u1                                           
u1u1                              ???             

--- 8 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = right('ZZZZa1a1',4) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = RPAD('a1',4,'a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = RPAD('a1',4,_utf8 x'C3B6') order by 1,2;

--- 0 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = RPAD('a1',4,_utf8 x'C3B6C3BA') order by 1,2;

--- 0 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = RPAD('a1',4,_utf8 x'E0A0A0') order by 1,2;

--- 0 row(s) selected.
>>select * from cs313t3, cs313t4 where trim(a1) = right(RPAD(_utf8 x'C3B6C3BA',8, 'a1'),4) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where trim(a1) = right(RPAD(_utf8 x'E0A0A0',9, 'a1'),4) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = RPAD(substr(a1,1,2),4,'a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = trim(u1) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              u1u1            
                                            
???                               ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = trim('  a1a1  ') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = rtrim(u1) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              u1u1            
                                            
???                               ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = rtrim('a1a1  ') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = ltrim(u1) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              u1u1            
                                            
???                               ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = ltrim('  a1a1') order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
a1a1                                           
a1a1                              ???             

--- 4 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = UPPER('a1a1') order by 1,2;

--- 0 row(s) selected.
>>select * from cs313t3, cs313t4 where a1 = cast('u1u1' as varchar(8) ) order by 1,2;

A1                                U1              
--------------------------------  ----------------

u1u1                              a1a1            
u1u1                              u1u1            
u1u1                                           
u1u1                              ???             

--- 4 row(s) selected.
>>--
>>-- Tests for ASSIGN
>>--
>>create volatile table cs313t10 (a1 char(160 BYTES) character set utf8, u1 char(40) character set ucs2 ) no partition;

--- SQL operation complete.
>>insert into cs313t10 values(CHAR(96), CHAR(96));

--- 1 row(s) inserted.
>>insert into cs313t10 values(SPACE(3), SPACE(3));

--- 1 row(s) inserted.
>>insert into cs313t10 values(SPACE(3, UTF8), SPACE(3, UTF8));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(SPACE(3, UCS2), SPACE(3, UCS2));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(DATEFORMAT(DATE'2009-04-15',USA), DATEFORMAT(DATE'2009-04-15',USA));

--- 1 row(s) inserted.
>>insert into cs313t10 values(DAYNAME(DATE'2009-04-15'), DAYNAME(DATE'2009-04-15'));

--- 1 row(s) inserted.
>>insert into cs313t10 values(MONTHNAME(DATE'2009-04-15'), MONTHNAME(DATE'2009-04-15'));

--- 1 row(s) inserted.
>>insert into cs313t10 values(CURRENT_USER, CURRENT_USER);

--- 1 row(s) inserted.
>>insert into cs313t10 values(SESSION_USER, SESSION_USER);

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(USER, USER);

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(CONVERTTOHEX(_ucs2'abc'), CONVERTTOHEX(_ucs2'abc'));

--- 1 row(s) inserted.
>>insert into cs313t10 values(CONVERTTOHEX(_utf8'abc'), CONVERTTOHEX(_utf8'abc'));

--- 1 row(s) inserted.
>>insert into cs313t10 values(CONVERTTOHEX(_utf8 x'C3B6'), CONVERTTOHEX(_utf8 x'C3B6'));

--- 1 row(s) inserted.
>>insert into cs313t10 values(CONVERTTOHEX(_utf8 x'C3B6'), CONVERTTOHEX(_utf8 x'E0A0A0'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(CONVERTTOHEX(_iso88591'abc'), CONVERTTOHEX(_iso88591'abc'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 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 cs313t10 values(CASE when 1=1 then _utf8'abc' else _utf8'def' end,
+>                          CASE when 1=1 then _utf8'abc' else _utf8'def' end);

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(CAST('abc' as varchar(4)), CAST('abc' as varchar(4)));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(CAST(_utf8'abc' as varchar(4)), CAST(_utf8'abc' as varchar(4)));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(CAST(_utf8 x'C3B6C3BB' as varchar(4)), CAST(_utf8 x'C3B6C3BB' as varchar(4)));

--- 1 row(s) inserted.
>>insert into cs313t10 values(CAST(_utf8 x'E0A0A0' as varchar(4)), CAST(_utf8 x'E0A0A0' as varchar(4)));

--- 1 row(s) inserted.
>>insert into cs313t10 values(CAST(_utf8'abc' as varchar(4)), CAST(_iso88591'abc' as varchar(4)));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(COALESCE('abc','def','xyz'), COALESCE('abc','def','xyz'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(COALESCE(_utf8'abc',_utf8'def',_utf8'xyz'),
+>                          COALESCE(_utf8'abc',_utf8'def',_utf8'xyz'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(COALESCE(_utf8 x'C3B6','def','xyz'), COALESCE(_utf8 x'C3B6','def','xyz'));

--- 1 row(s) inserted.
>>insert into cs313t10 values(COALESCE(_utf8 x'C3B6','def','xyz'), COALESCE(_utf8 x'E0A0A0','def','xyz'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(CONCAT('abc','def'), CONCAT('abc','def'));

--- 1 row(s) inserted.
>>insert into cs313t10 values(CONCAT('abc','def'), CONCAT('abc',_utf8 x'E0A0A0'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(CONCAT(_utf8'abc',_utf8'def'), CONCAT(_utf8'abc',_utf8'def'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(CONCAT(_iso88591'abc',_utf8'def'), CONCAT(_iso88591'abc',_utf8 x'E0A0A0'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(_utf8'abc' || _utf8'def', _utf8'abc' || _utf8'def');

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(_utf8'abc' || _ucs2'def', _utf8'abc' || _ucs2'def');

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(_ucs2'abc' || _utf8'def', _ucs2'abc' || _utf8'def');

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(DECODE('abc','def','EQ','NE'), DECODE('abc','def','EQ','NE'));

--- 1 row(s) inserted.
>>insert into cs313t10 values(DECODE(_utf8'abc',_utf8'def',_utf8'EQ',_utf8'NE'),
+>                          DECODE(_utf8'abc',_utf8'def',_utf8'EQ',_utf8'NE'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(INSERT('abcghi',4,0,'def'), INSERT('abcghi',4,0,'def'));

--- 1 row(s) inserted.
>>insert into cs313t10 values(INSERT(_utf8'abcghi',4,0,_utf8'def'),
+>                          INSERT(_utf8'abcghi',4,0,_utf8'def'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(ISNULL('abc','def'), ISNULL('abc','def'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(ISNULL(_utf8'abc',_utf8'def'), ISNULL(_utf8'abc',_utf8'def'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(LEFT('abcdef',3), LEFT('abcdef',3));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(LEFT(_utf8'abcdef',3), LEFT(_utf8'abcdef',3));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(LOWER('ABC'), LOWER('ABC'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(LOWER(_utf8'ABC'), LOWER(_utf8'ABC'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(LPAD('def',6,'abc'), LPAD('def',6,'abc'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(LPAD(_utf8'def',6,_utf8'abc'), LPAD(_utf8'def',6,_utf8'abc'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(LTRIM('   abc'), LTRIM('   abc'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(LTRIM(_utf8'   abc'), LTRIM(_utf8'   abc'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(NULLIF('abc','def'), NULLIF('abc','def'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(NULLIF(_utf8'abc',_utf8'def'), NULLIF('abc_utf8',_utf8'def'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(NVL('abc','def'), NVL('abc','def'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(NVL(_utf8'abc',_utf8'def'), NVL(_utf8'abc',_utf8'def'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(REPLACE('abcxyzghi','xyz','def'), REPLACE('abcxyzghi','xyz','def'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(REPLACE(_utf8'abcxyzghi',_utf8'xyz',_utf8'def'),
+>                          REPLACE(_utf8'abcxyzghi',_utf8'xyz',_utf8'def'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(RIGHT('defabc',3), RIGHT('defabc',3));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(RIGHT(_utf8'defabc',3), RIGHT(_utf8'defabc',3));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(RPAD('abc',6,'def'), RPAD('abc',6,'def'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(RPAD(_utf8'abc',6,_utf8'def'), RPAD(_utf8'abc',6,_utf8'def'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(RTRIM('abc   '), RTRIM('abc   '));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(RTRIM(_utf8'abc   '), RTRIM(_utf8'abc   '));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(SUBSTR('abcdefghi',1,6), SUBSTR('abcdefghi',1,6));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(SUBSTR(_utf8'abcdefghi',1,6), SUBSTR(_utf8'abcdefghi',1,6));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(TRANSLATE('abcdef' using UCS2toUTF8),
+>                          TRANSLATE('abcdef' using UCS2toUTF8));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(TRANSLATE('abcdef' using UTF8toUCS2),
+>                          TRANSLATE('abcdef' using UTF8toUCS2));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(TRANSLATE(_utf8'abcdef' using UCS2toUTF8),
+>                          TRANSLATE(_utf8'abcdef' using UCS2toUTF8));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(TRANSLATE(_utf8'abcdef' using UTF8toUCS2),
+>                          TRANSLATE(_utf8'abcdef' using UTF8toUCS2));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(TRIM('   abc   '), TRIM('   abc   '));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(TRIM(_utf8'   abc   '), TRIM(_utf8'   abc   '));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t10 values(UPPER('abcdef'), UPPER('abcdef'));

--- 1 row(s) inserted.
>>insert into cs313t10 values(UPPER(_utf8'abcdef'), UPPER(_utf8'abcdef'));

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>select count(*) from cs313t10;

(EXPR)              
--------------------

                  17

--- 1 row(s) selected.
>>--
>>-- Tests for Comparisons
>>--
>>create volatile table cs313t20 (a1 char(80 BYTES) character set utf8, u1 char(20) character set ucs2 ) no partition;

--- SQL operation complete.
>>insert into cs313t20 values(CHAR(97), CHAR(97) );

--- 1 row(s) inserted.
>>insert into cs313t20 values(SPACE(3), SPACE(3) );

--- 1 row(s) inserted.
>>insert into cs313t20 values(DATEFORMAT(DATE'2009-04-15',USA), DATEFORMAT(DATE'2009-04-15',USA) );

--- 1 row(s) inserted.
>>insert into cs313t20 values(DAYNAME(DATE'2009-04-15'), DAYNAME(DATE'2009-04-15') );

--- 1 row(s) inserted.
>>insert into cs313t20 values(MONTHNAME(DATE'2009-04-15'), MONTHNAME(DATE'2009-04-15') );

--- 1 row(s) inserted.
>>insert into cs313t20 values(CONVERTTOHEX(_ucs2'abc'), CONVERTTOHEX(_ucs2'abc') );

--- 1 row(s) inserted.
>>insert into cs313t20 values(CONVERTTOHEX(_utf8'abc'), CONVERTTOHEX(_utf8'abc') );

--- 1 row(s) inserted.
>>insert into cs313t20 values('abc', 'abc') ;

--- 1 row(s) inserted.
>>insert into cs313t20 values('ABC', 'ABC') ;

--- 1 row(s) inserted.
>>insert into cs313t20 values('def', 'def') ;

--- 1 row(s) inserted.
>>insert into cs313t20 values('abcdef', 'abcdef') ;

--- 1 row(s) inserted.
>>insert into cs313t20 values('ABCDEF', 'ABCDEF') ;

--- 1 row(s) inserted.
>>insert into cs313t20 values('abcdefghi', 'abcdefghi') ;

--- 1 row(s) inserted.
>>insert into cs313t20 values('xyz', 'xyz') ;

--- 1 row(s) inserted.
>>insert into cs313t20 values('EQ','EQ');

--- 1 row(s) inserted.
>>insert into cs313t20 values('NE','NE');

--- 1 row(s) inserted.
>>insert into cs313t20 values('abcghi', 'abcghi');

--- 1 row(s) inserted.
>>insert into cs313t20 values('abcdefghi', 'abcdefghi');

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>select * from cs313t20 order by 1;

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                               
abcghi                                                                            abcghi                                  
def                                                                               def                                     
xyz                                                                               xyz                                     

--- 17 row(s) selected.
>>select * from cs313t20 where a1 = CHAR(97) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

a                                                                                 a                                       

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = CHAR(97) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

a                                                                                 a                                       

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = SPACE(3) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

                                                                                                                          

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = SPACE(3) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

                                                                                                                          

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = SPACE(3, UTF8) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

                                                                                                                          

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = SPACE(3, UCS2) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

                                                                                                                          

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = DATEFORMAT(DATE'2009-04-15',USA) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

04/15/2009                                                                        04/15/2009                              

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = DATEFORMAT(DATE'2009-04-15',USA) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

04/15/2009                                                                        04/15/2009                              

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = DAYNAME(DATE'2009-04-15') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

Wednesday                                                                         Wednesday                               

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = DAYNAME(DATE'2009-04-15') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

Wednesday                                                                         Wednesday                               

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = MONTHNAME(DATE'2009-04-15') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

April                                                                             April                                   

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = MONTHNAME(DATE'2009-04-15') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

April                                                                             April                                   

--- 1 row(s) selected.
>>--
>>create volatile table cs313t21 (a1 char(160 BYTES) character set utf8, u1 char(40) character set ucs2 ) no partition;

--- SQL operation complete.
>>insert into cs313t21 values(CURRENT_USER, CURRENT_USER);

--- 1 row(s) inserted.
>>select 'a1 = CURRENT_USER' from cs313t21 where a1 = CURRENT_USER order by 1;

(EXPR)           
-----------------

a1 = CURRENT_USER

--- 1 row(s) selected.
>>select 'u1 = CURRENT_USER' from cs313t21 where u1 = CURRENT_USER order by 1;

(EXPR)           
-----------------

u1 = CURRENT_USER

--- 1 row(s) selected.
>>select 'a1 = SESSION_USER' from cs313t21 where a1 = SESSION_USER order by 1;

(EXPR)           
-----------------

a1 = SESSION_USER

--- 1 row(s) selected.
>>select 'u1 = SESSION_USER' from cs313t21 where u1 = SESSION_USER order by 1;

(EXPR)           
-----------------

u1 = SESSION_USER

--- 1 row(s) selected.
>>select 'a1 = USER' from cs313t21 where a1 = USER order by 1;

(EXPR)   
---------

a1 = USER

--- 1 row(s) selected.
>>select 'u1 = USER' from cs313t21 where u1 = USER order by 1;

(EXPR)   
---------

u1 = USER

--- 1 row(s) selected.
>>--
>>select * from cs313t20 where a1 = CONVERTTOHEX(_ucs2'abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

006100620063                                                                      006100620063                            

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = CONVERTTOHEX(_ucs2'abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

006100620063                                                                      006100620063                            

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = CONVERTTOHEX(_utf8'abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

616263                                                                            616263                                  

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = CONVERTTOHEX(_utf8'abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

616263                                                                            616263                                  

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = CASE when 1=1 then 'abc' else 'def' end order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = CASE when 1=1 then _utf8'abc' else _utf8'def' end order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = CAST('abc' as varchar(4)) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = CAST('abc' as varchar(4)) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = CAST(_utf8'abc' as varchar(4)) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = CAST(_utf8'abc' as varchar(4)) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = COALESCE('abc','def','xyz') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = COALESCE(_utf8'abc',_utf8'def',_utf8'xyz') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = CONCAT('abc','def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = CONCAT(_utf8'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = _utf8'abc' || _utf8'def' order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = _utf8'abc' || _ucs2'def' order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = _utf8'abc' || _ucs2'def' order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = _ucs2'abc' || _utf8'def' order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = _ucs2'abc' || _utf8'def' order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = DECODE('abc','def','EQ','NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = DECODE(_utf8'abc',_utf8'def',_utf8'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = INSERT('abcghi',4,0,'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = INSERT(_utf8'abcghi',4,0,_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = ISNULL('abc','def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = ISNULL(_utf8'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = LEFT('abcdef',3) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = LEFT(_utf8'abcdef',3) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = LOWER('ABC') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = LOWER(_utf8'ABC') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = LPAD('def',6,'abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = LPAD(_utf8'def',6,_utf8'abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = LTRIM('   abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = LTRIM(_utf8'   abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = NULLIF('abc','def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = NULLIF(_utf8'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = NVL('abc','def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = NVL(_utf8'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = REPLACE('abcxyzghi','xyz','def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = REPLACE(_utf8'abcxyzghi',_utf8'xyz',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = RIGHT('defabc',3) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = RIGHT(_utf8'defabc',3) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = RPAD('abc',6,'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = RPAD(_utf8'abc',6,_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = RTRIM('abc   ') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = RTRIM(_utf8'abc   ') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = SUBSTR('abcdefghi',1,6) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = SUBSTR(_utf8'abcdefghi',1,6) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = TRANSLATE('abcdef' using UCS2toUTF8) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = TRANSLATE('abcdef' using UCS2toUTF8) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = TRANSLATE('abcdef' using UTF8toUCS2) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = TRANSLATE('abcdef' using UTF8toUCS2) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = TRANSLATE(_utf8'abcdef' using UCS2toUTF8) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = TRANSLATE(_utf8'abcdef' using UCS2toUTF8) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = TRANSLATE(_utf8'abcdef' using UTF8toUCS2) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = TRANSLATE(_utf8'abcdef' using UTF8toUCS2) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = TRIM('   abc   ') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = TRIM(_utf8'   abc   ') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t20 where a1 = UPPER('abcdef') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

ABCDEF                                                                            ABCDEF                                  

--- 1 row(s) selected.
>>select * from cs313t20 where u1 = UPPER(_utf8'abcdef') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

ABCDEF                                                                            ABCDEF                                  

--- 1 row(s) selected.
>>--
>>-- Additional tests for INSERT statements
>>--
>>insert into cs313t2 values(_utf8 x'E0A0A0E0A0A1E0A0A2E0A0A3E0A0A4E0A0A5E0A0A6E0A0A7');

--- 1 row(s) inserted.
>>insert into cs313t2 values(_utf8 x'E0A0A0E1A0A1E0A0A2E0A0A3E0A0A4E0A0A5E0A0A6E0A0A7'),
+>                          (_utf8 x'E0A0A0E2A0A1E0A0A2E0A0A3E0A0A4E0A0A5E0A0A6E0A0A7');

--- 2 row(s) inserted.
>>--
>>-- NOTE: The 4-character UTF8 values in the following INSERT statement convert to 8 UCS2
>>--       characters because they are in the Surrogate Pair range!
>>--
>>insert into cs313t2 values(_utf8 x'F0A0A0A0F0A0A1A0F0A0A2A0F0A0A3A0'),
+>                          (_utf8 x'F0A0A0A0F0A0A2A0F0A0A2A0F0A0A3A0'),
+>                          (_utf8 x'F0A0A0A0F0A0A3A0F0A0A2A0F0A0A3A0'),
+>                          (_utf8 x'F0A0A0A0F0A0A4A0F0A0A2A0F0A0A3A0');

--- 4 row(s) inserted.
>>select converttohex(u1) from cs313t2 order by u1;

(EXPR)                          
--------------------------------

00610031006100310020002000200020
00750031007500310020002000200020
00D600DA00DB00200020002000200020
00F600FA00FB00200020002000200020
08200821082200200020002000200020
08200821082208230824082508260827
08201821082208230824082508260827
08202821082208230824082508260827
D842DC20D842DC60D842DCA0D842DCE0
D842DC20D842DCA0D842DCA0D842DCE0
D842DC20D842DCE0D842DCA0D842DCE0
D842DC20D842DD20D842DCA0D842DCE0

--- 12 row(s) selected.
>>--
>>-- Tests for Concatenation
>>--
>>create volatile table cs313t30 (a1 char(40 BYTES) character set utf8, u1 char(20) character set ucs2 ) no partition;

--- SQL operation complete.
>>create volatile table cs313t31 (a1 char(160 BYTES) character set utf8, u1 char(40) character set ucs2 ) no partition;

--- SQL operation complete.
>>insert into cs313t30 values('ZA','ZA');

--- 1 row(s) inserted.
>>insert into cs313t30 values('Z','Z');

--- 1 row(s) inserted.
>>insert into cs313t30 values('Z'||DATEFORMAT(DATE'2009-04-15',USA),'Z'||DATEFORMAT(DATE'2009-04-15',USA) );

--- 1 row(s) inserted.
>>insert into cs313t30 values('Z' || DAYNAME(DATE'2009-04-15'), 'Z' || DAYNAME(DATE'2009-04-15') );

--- 1 row(s) inserted.
>>insert into cs313t30 values('Z' || MONTHNAME(DATE'2009-04-15'), 'Z' || MONTHNAME(DATE'2009-04-15') );

--- 1 row(s) inserted.
>>insert into cs313t31 values(CURRENT_USER, CURRENT_USER);

--- 1 row(s) inserted.
>>insert into cs313t31 values('Z'||CURRENT_USER, 'Z'||CURRENT_USER);

--- 1 row(s) inserted.
>>insert into cs313t30 values('Z' || CONVERTTOHEX(_ucs2'abc'), 'Z' || CONVERTTOHEX(_ucs2'abc') );

--- 1 row(s) inserted.
>>insert into cs313t30 values('Z' || CONVERTTOHEX(_utf8'abc'), 'Z' || CONVERTTOHEX(_utf8'abc') );

--- 1 row(s) inserted.
>>insert into cs313t30 values('Za','Za');

--- 1 row(s) inserted.
>>insert into cs313t30 values('Zd','Zd');

--- 1 row(s) inserted.
>>insert into cs313t30 values('Zabc','Zabc');

--- 1 row(s) inserted.
>>insert into cs313t30 values('Zdef','Zdef');

--- 1 row(s) inserted.
>>insert into cs313t30 values('Zxyz','Zxyz');

--- 1 row(s) inserted.
>>insert into cs313t30 values('ZEQ','ZEQ');

--- 1 row(s) inserted.
>>insert into cs313t30 values('ZNE','ZNE');

--- 1 row(s) inserted.
>>insert into cs313t30 values('Zabcdef','Zabcdef');

--- 1 row(s) inserted.
>>insert into cs313t30 values('ZABCDEF','ZABCDEF');

--- 1 row(s) inserted.
>>insert into cs313t30 values('Zabcdefghi','Zabcdefghi');

--- 1 row(s) inserted.
>>select * from cs313t30 order by 1;

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 cs313t30 where a1 = _utf8'Z' || CHAR(97) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Za                                        Za                                      

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || CHAR(97) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Za                                        Za                                      

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || SPACE(3) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Z                                         Z                                       

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || SPACE(3) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Z                                         Z                                       

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || SPACE(3, UTF8) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Z                                         Z                                       

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || SPACE(3, UTF8) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Z                                         Z                                       

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || SPACE(3, UCS2) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Z                                         Z                                       

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || SPACE(3, UCS2) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Z                                         Z                                       

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || DATEFORMAT(DATE'2009-04-15',USA) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Z04/15/2009                               Z04/15/2009                             

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || DATEFORMAT(DATE'2009-04-15',USA) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Z04/15/2009                               Z04/15/2009                             

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || DAYNAME(DATE'2009-04-15') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

ZWednesday                                ZWednesday                              

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || DAYNAME(DATE'2009-04-15') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

ZWednesday                                ZWednesday                              

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || MONTHNAME(DATE'2009-04-15') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

ZApril                                    ZApril                                  

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || MONTHNAME(DATE'2009-04-15') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

ZApril                                    ZApril                                  

--- 1 row(s) selected.
>>select count(*) from cs313t31;

(EXPR)              
--------------------

                   2

--- 1 row(s) selected.
>>select 'a1 = _utf8''Z'' || CURRENT_USER' from cs313t31 where a1 = _utf8'Z' || CURRENT_USER order by 1;

(EXPR)                       
-----------------------------

a1 = _utf8'Z' || CURRENT_USER

--- 1 row(s) selected.
>>select 'a1 = _ucs2''Z'' || CURRENT_USER' from cs313t31 where a1 = _ucs2'Z' || CURRENT_USER order by 1;

(EXPR)                       
-----------------------------

a1 = _ucs2'Z' || CURRENT_USER

--- 1 row(s) selected.
>>select 'a1 = _utf8''Z'' || SESSION_USER' from cs313t31 where a1 = _utf8'Z' || SESSION_USER order by 1;

(EXPR)                       
-----------------------------

a1 = _utf8'Z' || SESSION_USER

--- 1 row(s) selected.
>>select 'a1 = _ucs2''Z'' || SESSION_USER' from cs313t31 where a1 = _ucs2'Z' || SESSION_USER order by 1;

(EXPR)                       
-----------------------------

a1 = _ucs2'Z' || SESSION_USER

--- 1 row(s) selected.
>>select 'a1 = _utf8''Z'' || USER' from cs313t31 where a1 = _utf8'Z' || USER order by 1;

(EXPR)               
---------------------

a1 = _utf8'Z' || USER

--- 1 row(s) selected.
>>select 'a1 = _ucs2''Z'' || USER' from cs313t31 where a1 = _ucs2'Z' || USER order by 1;

(EXPR)               
---------------------

a1 = _ucs2'Z' || USER

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || CONVERTTOHEX(_ucs2'abc') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Z006100620063                             Z006100620063                           

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || CONVERTTOHEX(_ucs2'abc') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Z006100620063                             Z006100620063                           

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || CONVERTTOHEX(_utf8'abc') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Z616263                                   Z616263                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || CONVERTTOHEX(_utf8'abc') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Z616263                                   Z616263                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || CASE when 1=1 then 'abc' else 'def' end order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || CASE when 1=1 then 'abc' else 'def' end order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || CASE when 1=1 then _utf8'abc' else _utf8'def' end order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || CAST('abc' as varchar(4)) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || CAST('abc' as varchar(4)) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || CAST(_utf8'abc' as varchar(4)) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || CAST(_utf8'abc' as varchar(4)) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || COALESCE('abc','def','xyz') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || COALESCE('abc','def','xyz') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || COALESCE(_utf8'abc',_utf8'def',_utf8'xyz') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || CONCAT('abc','def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || CONCAT('abc','def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || CONCAT(_utf8'abc',_utf8'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || (_utf8'abc' || _utf8'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || (_utf8'abc' || _ucs2'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || (_utf8'abc' || _ucs2'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || (_ucs2'abc' || _utf8'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || (_ucs2'abc' || _utf8'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || DECODE('abc','def','EQ','NE') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

ZNE                                       ZNE                                     

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || DECODE('abc','def','EQ','NE') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

ZNE                                       ZNE                                     

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || DECODE(_utf8'abc',_utf8'def',_utf8'EQ',_utf8'NE') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

ZNE                                       ZNE                                     

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || INSERT('abcghi',4,0,'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdefghi                                Zabcdefghi                              

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || INSERT('abcghi',4,0,'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdefghi                                Zabcdefghi                              

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || INSERT(_utf8'abcghi',4,0,_utf8'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdefghi                                Zabcdefghi                              

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || ISNULL('abc','def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || ISNULL('abc','def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || ISNULL(_utf8'abc',_utf8'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || LEFT('abcdef',3) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || LEFT('abcdef',3) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || LEFT(_utf8'abcdef',3) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || LOWER('ABC') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || LOWER('ABC') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || LOWER(_utf8'ABC') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || LPAD('def',6,'abc') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || LPAD('def',6,'abc') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || LPAD(_utf8'def',6,_utf8'abc') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || LTRIM('   abc') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || LTRIM('   abc') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || LTRIM(_utf8'   abc') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || NULLIF('abc','def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || NULLIF('abc','def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || NULLIF(_utf8'abc',_utf8'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || NVL('abc','def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || NVL('abc','def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || NVL(_utf8'abc',_utf8'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || REPLACE('abcxyzghi','xyz','def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdefghi                                Zabcdefghi                              

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || REPLACE('abcxyzghi','xyz','def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdefghi                                Zabcdefghi                              

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || REPLACE(_utf8'abcxyzghi',_utf8'xyz',_utf8'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdefghi                                Zabcdefghi                              

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || RIGHT('defabc',3) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || RIGHT('defabc',3) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || RIGHT(_utf8'defabc',3) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || RPAD('abc',6,'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || RPAD('abc',6,'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || RPAD(_utf8'abc',6,_utf8'def') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || RTRIM('abc   ') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || RTRIM('abc   ') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || RTRIM(_utf8'abc   ') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || SUBSTR('abcdefghi',1,6) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || SUBSTR('abcdefghi',1,6) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || SUBSTR(_utf8'abcdefghi',1,6) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || TRANSLATE('abcdef' using UCS2toUTF8) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || TRANSLATE('abcdef' using UCS2toUTF8) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || TRANSLATE('abcdef' using UTF8toUCS2) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || TRANSLATE('abcdef' using UTF8toUCS2) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || TRANSLATE(_utf8'abcdef' using UCS2toUTF8) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || TRANSLATE(_utf8'abcdef' using UCS2toUTF8) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || TRANSLATE(_utf8'abcdef' using UTF8toUCS2) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || TRANSLATE(_utf8'abcdef' using UTF8toUCS2) order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabcdef                                   Zabcdef                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || TRIM('   abc   ') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || TRIM('   abc   ') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || TRIM(_utf8'   abc   ') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

Zabc                                      Zabc                                    

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _utf8'Z' || UPPER('abcdef') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

ZABCDEF                                   ZABCDEF                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || UPPER('abcdef') order by 1;

A1                                        U1
----------------------------------------  ----------------------------------------

ZABCDEF                                   ZABCDEF                                 

--- 1 row(s) selected.
>>select * from cs313t30 where a1 = _ucs2'Z' || UPPER(_utf8'abcdef') order by 1;

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 volatile table cs313t40 (a1 char(80 BYTES) character set utf8, u1 char(20) character set ucs2 ) no partition;

--- SQL operation complete.
>>insert into cs313t40 values('ZA','ZA');

--- 1 row(s) inserted.
>>insert into cs313t40 values('Z','Z');

--- 1 row(s) inserted.
>>insert into cs313t40 values('Z'||DATEFORMAT(DATE'2009-04-15',USA),'Z'||DATEFORMAT(DATE'2009-04-15',USA) );

--- 1 row(s) inserted.
>>insert into cs313t40 values('Z' || DAYNAME(DATE'2009-04-15'), 'Z' || DAYNAME(DATE'2009-04-15') );

--- 1 row(s) inserted.
>>insert into cs313t40 values('Z' || MONTHNAME(DATE'2009-04-15'), 'Z' || MONTHNAME(DATE'2009-04-15') );

--- 1 row(s) inserted.
>>insert into cs313t40 values('Z' || CONVERTTOHEX(_ucs2'abc'), 'Z' || CONVERTTOHEX(_ucs2'abc') );

--- 1 row(s) inserted.
>>insert into cs313t40 values('Z' || CONVERTTOHEX(_utf8'abc'), 'Z' || CONVERTTOHEX(_utf8'abc') );

--- 1 row(s) inserted.
>>insert into cs313t40 values('Za','Za');

--- 1 row(s) inserted.
>>insert into cs313t40 values('Zd','Zd');

--- 1 row(s) inserted.
>>insert into cs313t40 values('abc','abc');

--- 1 row(s) inserted.
>>insert into cs313t40 values('def','def');

--- 1 row(s) inserted.
>>insert into cs313t40 values('Zabc','Zabc');

--- 1 row(s) inserted.
>>insert into cs313t40 values('Zdef','Zdef');

--- 1 row(s) inserted.
>>insert into cs313t40 values('Zxyz','Zxyz');

--- 1 row(s) inserted.
>>insert into cs313t40 values('EQ','EQ');

--- 1 row(s) inserted.
>>insert into cs313t40 values('ZEQ','ZEQ');

--- 1 row(s) inserted.
>>insert into cs313t40 values('ZNE','ZNE');

--- 1 row(s) inserted.
>>insert into cs313t40 values('NE','NE');

--- 1 row(s) inserted.
>>insert into cs313t40 values('abcdef','abcdef');

--- 1 row(s) inserted.
>>insert into cs313t40 values('Zabcdef','Zabcdef');

--- 1 row(s) inserted.
>>insert into cs313t40 values('ABCDEF','ABCDEF');

--- 1 row(s) inserted.
>>insert into cs313t40 values('ZABCDEF','ZABCDEF');

--- 1 row(s) inserted.
>>insert into cs313t40 values('abcdefghi','abcdefghi');

--- 1 row(s) inserted.
>>insert into cs313t40 values('Zabcdefghi','Zabcdefghi');

--- 1 row(s) inserted.
>>select * from cs313t40 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 cs313t40 where a1 = _utf8'Z' || CHAR(97) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

Za                                                                                Za                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = _utf8'Z' || TRANSLATE( CHAR(97) USING UCS2toUTF8) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

Za                                                                                Za                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = _utf8'Z' || 'a'  order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

Za                                                                                Za                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = _utf8'Z' || CAST('a' as VARCHAR(1)) order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

Za                                                                                Za                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = CASE when 1=1 then _utf8'abc' else _ucs2'def' end order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = CASE when 1=1 then _ucs2'abc' else _utf8'def' end order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = CASE when 1=1 then _ucs2'abc' else _ucs2'def' end order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = CASE when 1=1 then _utf8'abc' else _utf8'def' end order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = CASE when 1=1 then _utf8'abc' else _ucs2'def' end order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = CASE when 1=1 then _ucs2'abc' else _utf8'def' end order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>--
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_utf8'abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>--
>>select * from cs313t40 where a1 = COALESCE(_utf8'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_utf8'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_utf8'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_ucs2'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>--
>>select * from cs313t40 where a1 = COALESCE(_utf8'abc',_utf8'def',_ucs2'ghi') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_utf8'abc',_ucs2'def',_utf8'ghi') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc',_utf8'def',_utf8'ghi') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_utf8'abc',_ucs2'def',_ucs2'ghi') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc',_utf8'def',_ucs2'ghi') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_utf8'ghi') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_ucs2'ghi') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_utf8'abc',_utf8'def',_utf8'ghi') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_utf8'abc',_utf8'def',_ucs2'ghi') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_utf8'abc',_ucs2'def',_utf8'ghi') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_ucs2'abc',_utf8'def',_utf8'ghi') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_utf8'abc',_ucs2'def',_ucs2'ghi') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_ucs2'abc',_utf8'def',_ucs2'ghi') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_ucs2'abc',_ucs2'def',_utf8'ghi') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>--
>>select * from cs313t40 where a1 = COALESCE(_utf8'abc',_utf8'def',_utf8'ghi',_ucs2'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_utf8'abc',_utf8'def',_ucs2'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_utf8'abc',_ucs2'def',_utf8'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc',_utf8'def',_utf8'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_utf8'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc',_utf8'def',_ucs2'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc',_utf8'def',_utf8'ghi',_ucs2'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_utf8'abc',_ucs2'def',_ucs2'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_utf8'abc',_ucs2'def',_utf8'ghi',_ucs2'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_ucs2'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_utf8'ghi',_ucs2'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc',_utf8'def',_ucs2'ghi',_ucs2'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_utf8'abc',_ucs2'def',_ucs2'ghi',_ucs2'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_ucs2'ghi',_ucs2'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_utf8'abc',_utf8'def',_utf8'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_utf8'abc',_utf8'def',_utf8'ghi',_ucs2'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_utf8'abc',_utf8'def',_ucs2'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_utf8'abc',_ucs2'def',_utf8'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_ucs2'abc',_utf8'def',_utf8'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_ucs2'abc',_ucs2'def',_utf8'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_ucs2'abc',_utf8'def',_ucs2'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_ucs2'abc',_utf8'def',_utf8'ghi',_ucs2'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_utf8'abc',_ucs2'def',_ucs2'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_utf8'abc',_ucs2'def',_utf8'ghi',_ucs2'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_ucs2'abc',_ucs2'def',_ucs2'ghi',_utf8'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_ucs2'abc',_ucs2'def',_utf8'ghi',_ucs2'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_ucs2'abc',_utf8'def',_ucs2'ghi',_ucs2'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = COALESCE(_utf8'abc',_ucs2'def',_ucs2'ghi',_ucs2'jkl') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>--
>>select * from cs313t40 where a1 = CONCAT(_utf8'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = CONCAT(_ucs2'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = CONCAT(_ucs2'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = CONCAT(_utf8'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = CONCAT(_utf8'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = CONCAT(_ucs2'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>--
>>select * from cs313t40 where a1 = _utf8'abc' || _ucs2'def' order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = _ucs2'abc' || _utf8'def' order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = _ucs2'abc' || _ucs2'def' order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = _utf8'abc' || _utf8'def' order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = _utf8'abc' || _ucs2'def' order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = _ucs2'abc' || _utf8'def' order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>--
>>select * from cs313t40 where a1 = DECODE(_utf8'abc',_utf8'def',_utf8'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = DECODE(_utf8'abc',_utf8'def',_ucs2'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = DECODE(_utf8'abc',_ucs2'def',_utf8'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = DECODE(_ucs2'abc',_utf8'def',_utf8'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = DECODE(_ucs2'abc',_ucs2'def',_utf8'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = DECODE(_ucs2'abc',_utf8'def',_ucs2'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = DECODE(_ucs2'abc',_utf8'def',_utf8'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = DECODE(_utf8'abc',_ucs2'def',_ucs2'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = DECODE(_utf8'abc',_ucs2'def',_utf8'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = DECODE(_utf8'abc',_utf8'def',_ucs2'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = DECODE(_ucs2'abc',_ucs2'def',_ucs2'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = DECODE(_ucs2'abc',_ucs2'def',_utf8'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = DECODE(_ucs2'abc',_utf8'def',_ucs2'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = DECODE(_utf8'abc',_ucs2'def',_ucs2'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = DECODE(_ucs2'abc',_ucs2'def',_ucs2'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_utf8'abc',_utf8'def',_utf8'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_utf8'abc',_utf8'def',_utf8'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_utf8'abc',_utf8'def',_ucs2'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_utf8'abc',_ucs2'def',_utf8'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_ucs2'abc',_utf8'def',_utf8'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_ucs2'abc',_ucs2'def',_utf8'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_ucs2'abc',_utf8'def',_ucs2'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_ucs2'abc',_utf8'def',_utf8'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_utf8'abc',_ucs2'def',_ucs2'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_utf8'abc',_ucs2'def',_utf8'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_utf8'abc',_utf8'def',_ucs2'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_ucs2'abc',_ucs2'def',_ucs2'EQ',_utf8'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_ucs2'abc',_ucs2'def',_utf8'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_ucs2'abc',_utf8'def',_ucs2'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = DECODE(_utf8'abc',_ucs2'def',_ucs2'EQ',_ucs2'NE') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

NE                                                                                NE                                      

--- 1 row(s) selected.
>>--
>>select * from cs313t40 where a1 = INSERT(_utf8'abcghi',4,0,_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = INSERT(_ucs2'abcghi',4,0,_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = INSERT(_ucs2'abcghi',4,0,_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = INSERT(_utf8'abcghi',4,0,_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = INSERT(_utf8'abcghi',4,0,_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = INSERT(_ucs2'abcghi',4,0,_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>--
>>select * from cs313t40 where a1 = ISNULL(_utf8'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = ISNULL(_ucs2'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = ISNULL(_ucs2'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = ISNULL(_utf8'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = ISNULL(_utf8'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = ISNULL(_ucs2'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>--
>>select * from cs313t40 where a1 = LPAD(_utf8'def',6,_ucs2'abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = LPAD(_ucs2'def',6,_utf8'abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = LPAD(_ucs2'def',6,_ucs2'abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = LPAD(_utf8'def',6,_utf8'abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = LPAD(_utf8'def',6,_ucs2'abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = LPAD(_ucs2'def',6,_utf8'abc') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>--
>>select * from cs313t40 where a1 = NULLIF(_utf8'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = NULLIF(_ucs2'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = NULLIF(_ucs2'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = NULLIF(_utf8'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = NULLIF(_utf8'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = NULLIF(_ucs2'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>--
>>select * from cs313t40 where a1 = NVL(_utf8'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = NVL(_ucs2'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = NVL(_ucs2'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = NVL(_utf8'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = NVL(_utf8'abc',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = NVL(_ucs2'abc',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abc                                                                               abc                                     

--- 1 row(s) selected.
>>--
>>select * from cs313t40 where a1 = REPLACE(_utf8'abcxyzghi',_utf8'xyz',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = REPLACE(_utf8'abcxyzghi',_ucs2'xyz',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = REPLACE(_ucs2'abcxyzghi',_utf8'xyz',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = REPLACE(_ucs2'abcxyzghi',_ucs2'xyz',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = REPLACE(_ucs2'abcxyzghi',_utf8'xyz',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = REPLACE(_utf8'abcxyzghi',_ucs2'xyz',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = REPLACE(_ucs2'abcxyzghi',_ucs2'xyz',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = REPLACE(_utf8'abcxyzghi',_utf8'xyz',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = REPLACE(_utf8'abcxyzghi',_utf8'xyz',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = REPLACE(_utf8'abcxyzghi',_ucs2'xyz',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = REPLACE(_ucs2'abcxyzghi',_utf8'xyz',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = REPLACE(_ucs2'abcxyzghi',_ucs2'xyz',_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = REPLACE(_ucs2'abcxyzghi',_utf8'xyz',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = REPLACE(_utf8'abcxyzghi',_ucs2'xyz',_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdefghi                                                                         abcdefghi                               

--- 1 row(s) selected.
>>--
>>select * from cs313t40 where a1 = RPAD(_utf8'abc',6,_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = RPAD(_ucs2'abc',6,_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where a1 = RPAD(_ucs2'abc',6,_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = RPAD(_utf8'abc',6,_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = RPAD(_utf8'abc',6,_ucs2'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>select * from cs313t40 where u1 = RPAD(_ucs2'abc',6,_utf8'def') order by 1;

A1                                                                                U1
--------------------------------------------------------------------------------  ----------------------------------------

abcdef                                                                            abcdef                                  

--- 1 row(s) selected.
>>--
>>-- Tests for TRANSLATE
>>--
>>create volatile table cs313t50 (a1 char(32 BYTES) character set utf8, u1 char(8) character set ucs2 ) no partition;

--- SQL operation complete.
>>insert into cs313t50 values('abc','abc');

--- 1 row(s) inserted.
>>insert into cs313t50 values('abc','def');

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>select * from cs313t50 order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>
>>--Following should work without errors
>>select * from cs313t50 where a1 = u1 order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where u1 = a1 order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where a1 = TRANSLATE(u1 using UCS2toUTF8) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where u1 = TRANSLATE(a1 using UTF8toUCS2) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where a1 = TRANSLATE(a1 using UTF8toUCS2) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where u1 = TRANSLATE(u1 using UCS2toUTF8) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where a1 = TRANSLATE('abc' using UCS2toUTF8) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where u1 = TRANSLATE(_utf8'abc' using UTF8toUCS2) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where a1 = TRANSLATE(TRANSLATE(u1 using UCS2toUTF8) using UTF8toUCS2) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where u1 = TRANSLATE(TRANSLATE(a1 using UTF8toUCS2) using UCS2toUTF8) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select TRANSLATE(u1 using UCS2toUTF8) from cs313t50 order by 1;

(EXPR)                  
------------------------

abc                     

--- 1 row(s) selected.
>>select TRANSLATE(a1 using UTF8toUCS2) from cs313t50 order by 1;

(EXPR)                                                          
----------------------------------------------------------------

abc                                                             

--- 1 row(s) selected.
>>select TRANSLATE(TRANSLATE(u1 using UCS2toUTF8) using UTF8toUCS2) from cs313t50 order by 1;

(EXPR)                                          
------------------------------------------------

abc                                             

--- 1 row(s) selected.
>>select TRANSLATE(TRANSLATE(a1 using UTF8toUCS2) using UCS2toUTF8) from cs313t50 order by 1;

(EXPR)
------------------------------------------------------------------------------------------------

abc                                                                                             

--- 1 row(s) selected.
>>--
>>--The following should work without errors ... because of Implicit Casting of literals
>>select * from cs313t50 where a1 = TRANSLATE('abc' using UTF8toUCS2) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select TRANSLATE('abc' using UTF8toUCS2) from cs313t50 order by 1;

(EXPR)
------

abc   

--- 1 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 cs313t50 where u1 = TRANSLATE(_utf8'abc' using UCS2toUTF8) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where u1 = TRANSLATE(_ucs2'abc' using UTF8toUCS2) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select TRANSLATE(_utf8'abc' using UCS2toUTF8) from cs313t50 order by 1;

(EXPR)   
---------

abc      

--- 1 row(s) selected.
>>select TRANSLATE(_ucs2'abc' using UTF8toUCS2) from cs313t50 order by 1;

(EXPR)
------

abc   

--- 1 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 cs313t50 where a1 = TRANSLATE(TRANSLATE(u1 using UCS2toUTF8) using UCS2toUTF8) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where u1 = TRANSLATE(TRANSLATE(a1 using UTF8toUCS2) using UTF8toUCS2) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where a1 = TRANSLATE(TRIM(a1) using UCS2toUTF8) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where u1 = TRANSLATE(TRIM(u1) using UTF8toUCS2) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where a1 = TRANSLATE(TRIM(_utf8'abc') using UCS2toUTF8) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where u1 = TRANSLATE(TRIM(_ucs2'abc') using UTF8toUCS2) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t50 where u1 = TRANSLATE(TRIM('abc') using UTF8toUCS2) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>--
>>--
>>--Following should get errors
>>select * from cs313t50 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 cs313t50 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 cs313t50  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 cs313t50  order by 1;

*** ERROR[4106] The character set for the operand of function TRANSLATE must be UCS2.

*** ERROR[8822] The statement was not prepared.

>>--
>>--Following should work without error because a1 is ISO88591 which is subset of UTF8
>>select TRANSLATE(a1 using UTF8toISO88591) from cs313t50  order by 1;

(EXPR)                          
--------------------------------

abc                             

--- 1 row(s) selected.
>>--
>>-- Tests for CAST
>>--
>>create volatile table cs313t60 (a1 char(32 BYTES) character set utf8, u1 char(8) character set ucs2 ) no partition;

--- SQL operation complete.
>>insert into cs313t60 values('abc','abc');

--- 1 row(s) inserted.
>>insert into cs313t60 values('abc','def');

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>select * from cs313t60 order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>
>>select * from cs313t60 where a1 = CAST('abc' as varchar(4)) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t60 where u1 = CAST('abc' as varchar(4)) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t60 where a1 = CAST(_utf8'abc' as varchar(4)) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t60 where u1 = CAST(_utf8'abc' as varchar(4)) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>--
>>select * from cs313t60 where a1 = CAST(a1 as varchar(4)) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t60 where u1 = CAST(u1 as varchar(4)) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t60 where a1 = CAST(u1 as varchar(4)) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t60 where u1 = CAST(a1 as varchar(4)) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>--
>>select * from cs313t60 where a1 = CAST('abc' as varchar(4) character set ucs2) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t60 where a1 = CAST(_utf8'abc' as varchar(4) character set ucs2) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t60 where u1 = CAST(_utf8'abc' as varchar(4) character set utf8) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>--
>>select * from cs313t60 where a1 = CAST(a1 as varchar(4) character set ucs2) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t60 where a1 = CAST(u1 as varchar(4) character set ucs2) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>select * from cs313t60 where u1 = CAST(a1 as varchar(4) character set utf8) order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             

--- 1 row(s) selected.
>>--
>>-- Tests for UNIONs
>>--
>>create volatile table cs313t70 (a1 char(32 BYTES) character set utf8, u1 char(8) character set ucs2 ) no partition;

--- SQL operation complete.
>>insert into cs313t70 values('abc','abc');

--- 1 row(s) inserted.
>>insert into cs313t70 values('abc','def');

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t70 values('def','def');

--- 1 row(s) inserted.
>>insert into cs313t70 values(_ucs2 x'00F600FA00FB',_utf8 x'C3B6C3BAC3BB');

--- 1 row(s) inserted.
>>insert into cs313t70 values(_ucs2 x'082008210822',_utf8 x'E0A0A0E0A0A1E0A0A2');

--- 1 row(s) inserted.
>>--
>>create volatile table cs313t71 (a1 char(8) character set ucs2, u1 char(32 BYTES) character set utf8 ) no partition;

--- SQL operation complete.
>>insert into cs313t71 values('abc','abc');

--- 1 row(s) inserted.
>>insert into cs313t71 values('abc','def');

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into cs313t71 values('def','def');

--- 1 row(s) inserted.
>>insert into cs313t71 values(_ucs2 x'00F600FA00FB',_utf8 x'C3B6C3BAC3BB');

--- 1 row(s) inserted.
>>insert into cs313t71 values(_utf8 x'E0A0A0E0A0A1E0A0A2',_ucs2 x'082008210822');

--- 1 row(s) inserted.
>>--
>>select * from cs313t70 order by a1;

A1                                U1              
--------------------------------  ----------------

abc                               abc             
def                               def             
                                            
???                               ???             

--- 4 row(s) selected.
>>select * from cs313t71 order by a1;

A1                U1                              
----------------  --------------------------------

abc               abc                             
def               def                             
                                            
???               ???                             

--- 4 row(s) selected.
>>select * from cs313t70 union (select * from cs313t71) order by 1,2;

A1                                                                U1
----------------------------------------------------------------  ----------------------------------------------------------------

abc                                                               abc                                                             
def                                                               def                                                             
                                                                                                                            
???                                                               ???                                                             

--- 4 row(s) selected.
>>select * from cs313t70 union all (select * from cs313t71) order by 1,2;

A1                                                                U1
----------------------------------------------------------------  ----------------------------------------------------------------

abc                                                               abc                                                             
abc                                                               abc                                                             
def                                                               def                                                             
def                                                               def                                                             
                                                                                                                            
                                                                                                                            
???                                                               ???                                                             
???                                                               ???                                                             

--- 8 row(s) selected.
>>--
>>select * from cs313t70, cs313t71 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
abc                               abc               def               def                             
abc                               abc                                                           
abc                               abc               ???               ???                             
def                               def               abc               abc                             
def                               def               def               def                             
def                               def                                                           
def                               def               ???               ???                             
                                              abc               abc                             
                                              def               def                             
                                                                                          
                                              ???               ???                             
???                               ???               abc               abc                             
???                               ???               def               def                             
???                               ???                                                           
???                               ???               ???               ???                             

--- 16 row(s) selected.
>>select * from cs313t70, cs313t71 where cs313t70.a1 = cs313t71.u1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>select * from cs313t70, cs313t71 where cs313t70.u1 = cs313t71.a1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>--
>>select * from cs313t70, cs313t71 where cs313t70.a1 = cs313t71.a1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>select * from cs313t70, cs313t71 where cs313t70.u1 = cs313t71.u1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>--
>>select * from cs313t70 union (select * from cs313t70 union (select * from cs313t71)) order by 1,2;

A1                                                                U1
----------------------------------------------------------------  ----------------------------------------------------------------

abc                                                               abc                                                             
def                                                               def                                                             
                                                                                                                            
???                                                               ???                                                             

--- 4 row(s) selected.
>>select * from cs313t70 union (select * from cs313t71 union (select * from cs313t70)) order by 1,2;

A1                                                                U1
----------------------------------------------------------------  ----------------------------------------------------------------

abc                                                               abc                                                             
def                                                               def                                                             
                                                                                                                            
???                                                               ???                                                             

--- 4 row(s) selected.
>>--
>>select * from cs313t70 natural join cs313t71 order by 1,2;

A1                                U1              
--------------------------------  ----------------

abc                               abc             
def                               def             
                                            
???                               ???             

--- 4 row(s) selected.
>>select * from cs313t70 cross join cs313t71  order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
abc                               abc               def               def                             
abc                               abc                                                           
abc                               abc               ???               ???                             
def                               def               abc               abc                             
def                               def               def               def                             
def                               def                                                           
def                               def               ???               ???                             
                                              abc               abc                             
                                              def               def                             
                                                                                          
                                              ???               ???                             
???                               ???               abc               abc                             
???                               ???               def               def                             
???                               ???                                                           
???                               ???               ???               ???                             

--- 16 row(s) selected.
>>select * from cs313t71 cross join cs313t70  order by 1,2,3,4;

A1                U1                                A1                                U1
----------------  --------------------------------  --------------------------------  ----------------

abc               abc                               abc                               abc             
abc               abc                               def                               def             
abc               abc                                                                           
abc               abc                               ???                               ???             
def               def                               abc                               abc             
def               def                               def                               def             
def               def                                                                           
def               def                               ???                               ???             
                                              abc                               abc             
                                              def                               def             
                                                                                          
                                              ???                               ???             
???               ???                               abc                               abc             
???               ???                               def                               def             
???               ???                                                                           
???               ???                               ???                               ???             

--- 16 row(s) selected.
>>--
>>select * from cs313t70 inner join cs313t71 on cs313t70.a1 = cs313t71.u1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>select * from cs313t70 inner join cs313t71 on cs313t70.u1 = cs313t71.u1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>select * from cs313t70 inner join cs313t71 on cs313t70.u1 = cs313t71.a1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>select * from cs313t70 inner join cs313t71 on cs313t70.a1 = cs313t71.a1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>--
>>select * from cs313t70 left outer join cs313t71 on cs313t70.u1 = cs313t71.a1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>select * from cs313t70 left outer join cs313t71 on cs313t70.a1 = cs313t71.u1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>select * from cs313t70 left outer join cs313t71 on cs313t70.u1 = cs313t71.u1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>select * from cs313t70 left outer join cs313t71 on cs313t70.a1 = cs313t71.a1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>--
>>select * from cs313t70 right outer join cs313t71 on cs313t70.u1 = cs313t71.a1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>select * from cs313t70 right outer join cs313t71 on cs313t70.a1 = cs313t71.u1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>select * from cs313t70 right outer join cs313t71 on cs313t70.u1 = cs313t71.u1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>select * from cs313t70 right outer join cs313t71 on cs313t70.a1 = cs313t71.a1 order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
def                               def               def               def                             
                                                                                          
???                               ???               ???               ???                             

--- 4 row(s) selected.
>>--
>>select * from cs313t70, cs313t71 where cs313t70.u1 = (select cs313t71.u1 from cs313t71 where cs313t71.a1 = 'def') order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

def                               def               abc               abc                             
def                               def               def               def                             
def                               def                                                           
def                               def               ???               ???                             

--- 4 row(s) selected.
>>--
>>select * from cs313t70, cs313t71 where cs313t70.u1 > any (select cs313t71.a1 from cs313t71 where cs313t71.u1 = 'def') order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

                                              abc               abc                             
                                              def               def                             
                                                                                          
                                              ???               ???                             
???                               ???               abc               abc                             
???                               ???               def               def                             
???                               ???                                                           
???                               ???               ???               ???                             

--- 8 row(s) selected.
>>select * from cs313t70, cs313t71 where cs313t70.u1 < any (select cs313t71.a1 from cs313t71 where cs313t71.u1 = 'def') order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
abc                               abc               def               def                             
abc                               abc                                                           
abc                               abc               ???               ???                             

--- 4 row(s) selected.
>>select * from cs313t70, cs313t71 where cs313t70.u1 <= any (select cs313t71.a1 from cs313t71 where cs313t71.u1 = 'def') order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
abc                               abc               def               def                             
abc                               abc                                                           
abc                               abc               ???               ???                             
def                               def               abc               abc                             
def                               def               def               def                             
def                               def                                                           
def                               def               ???               ???                             

--- 8 row(s) selected.
>>select * from cs313t70, cs313t71 where cs313t70.u1 >= any (select cs313t71.a1 from cs313t71 where cs313t71.u1 = 'def') order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

def                               def               abc               abc                             
def                               def               def               def                             
def                               def                                                           
def                               def               ???               ???                             
                                              abc               abc                             
                                              def               def                             
                                                                                          
                                              ???               ???                             
???                               ???               abc               abc                             
???                               ???               def               def                             
???                               ???                                                           
???                               ???               ???               ???                             

--- 12 row(s) selected.
>>select * from cs313t70, cs313t71 where cs313t70.u1 > any (select cs313t71.a1 from cs313t71 where cs313t71.u1 = 'abc') order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

def                               def               abc               abc                             
def                               def               def               def                             
def                               def                                                           
def                               def               ???               ???                             
                                              abc               abc                             
                                              def               def                             
                                                                                          
                                              ???               ???                             
???                               ???               abc               abc                             
???                               ???               def               def                             
???                               ???                                                           
???                               ???               ???               ???                             

--- 12 row(s) selected.
>>select * from cs313t70, cs313t71 where cs313t70.u1 < any (select cs313t71.a1 from cs313t71 where cs313t71.u1 = 'abc') order by 1,2,3,4;

--- 0 row(s) selected.
>>select * from cs313t70, cs313t71 where cs313t70.u1 <= any (select cs313t71.a1 from cs313t71 where cs313t71.u1 = 'abc') order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
abc                               abc               def               def                             
abc                               abc                                                           
abc                               abc               ???               ???                             

--- 4 row(s) selected.
>>select * from cs313t70, cs313t71 where cs313t70.u1 >= any (select cs313t71.a1 from cs313t71 where cs313t71.u1 = 'abc') order by 1,2,3,4;

A1                                U1                A1                U1
--------------------------------  ----------------  ----------------  --------------------------------

abc                               abc               abc               abc                             
abc                               abc               def               def                             
abc                               abc                                                           
abc                               abc               ???               ???                             
def                               def               abc               abc                             
def                               def               def               def                             
def                               def                                                           
def                               def               ???               ???                             
                                              abc               abc                             
                                              def               def                             
                                                                                          
                                              ???               ???                             
???                               ???               abc               abc                             
???                               ???               def               def                             
???                               ???                                                           
???                               ???               ???               ???                             

--- 16 row(s) selected.
>>--
>>-- Tests for subqueries
>>--
>>create volatile table cs313t80 (a1 char(32 BYTES) character set utf8) no partition;

--- SQL operation complete.
>>insert into cs313t80 values('a1a1');

--- 1 row(s) inserted.
>>insert into cs313t80 values('u1u1');

--- 1 row(s) inserted.
>>select * from cs313t80 order by 1;

A1                              
--------------------------------

a1a1                            
u1u1                            

--- 2 row(s) selected.
>>create volatile table cs313t81 (u1 char(8) character set ucs2) no partition;

--- SQL operation complete.
>>insert into cs313t81 values('a1a1');

--- 1 row(s) inserted.
>>insert into cs313t81 values('u1u1');

--- 1 row(s) inserted.
>>select * from cs313t81 order by 1;

U1              
----------------

a1a1            
u1u1            

--- 2 row(s) selected.
>>
>>select * from cs313t80, cs313t81 order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
u1u1                              a1a1            
u1u1                              u1u1            

--- 4 row(s) selected.
>>select * from cs313t80, cs313t81 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 cs313t80, cs313t81 where a1 = case when (select min(a1) from cs313t80) > 'a1a' then u1 else 'a100' end order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              u1u1            

--- 2 row(s) selected.
>>select * from cs313t80, cs313t81 where u1 = case when (select min(a1) from cs313t80) > '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 cs313t80, cs313t81 where (select min(a1) from cs313t80) = case when (select min(a1) from cs313t80) > 'a1a' then u1 else 'a100' end order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              a1a1            

--- 2 row(s) selected.
>>select * from cs313t80, cs313t81 where (select min(u1) from cs313t81) = case when (select min(a1) from cs313t80) > 'a1a' then u1 else 'a100' end order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              a1a1            

--- 2 row(s) selected.
>>--
>>select * from cs313t80, cs313t81 where a1 = case when a1 > 'a1a' then (select min(u1) from cs313t81) else 'a100' end order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            

--- 2 row(s) selected.
>>select * from cs313t80, cs313t81 where u1 = case when a1 > 'a1a' then (select min(u1) from cs313t81) else 'a100' end order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              a1a1            

--- 2 row(s) selected.
>>--
>>select * from cs313t80, cs313t81 where a1 = case when (select min(a1) from cs313t80) > 'a1a' then (select min(u1) from cs313t81) else 'a100' end order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            

--- 2 row(s) selected.
>>select * from cs313t80, cs313t81 where u1 = case when (select min(a1) from cs313t80) > 'a1a' then (select min(u1) from cs313t81) else 'a100' end order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              a1a1            

--- 2 row(s) selected.
>>--
>>select * from cs313t80, cs313t81 where a1 = (select min(case when a1 > 'a1a' then u1 else 'a100' end) from cs313t80, cs313t81) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            

--- 2 row(s) selected.
>>select * from cs313t80, cs313t81 where u1 = (select min(case when a1 > 'a1a' then u1 else 'a100' end) from cs313t80, cs313t81) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
u1u1                              a1a1            

--- 2 row(s) selected.
>>--
>>select * from cs313t80, cs313t81 where (select min(a1) from cs313t80) = (select min(case when a1 > 'a1a' then u1 else 'a100' end) from cs313t80, cs313t81) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
u1u1                              a1a1            
u1u1                              u1u1            

--- 4 row(s) selected.
>>select * from cs313t80, cs313t81 where (select min(u1) from cs313t81) = (select min(case when a1 > 'a1a' then u1 else 'a100' end) from cs313t80, cs313t81) order by 1,2;

A1                                U1              
--------------------------------  ----------------

a1a1                              a1a1            
a1a1                              u1u1            
u1u1                              a1a1            
u1u1                              u1u1            

--- 4 row(s) selected.
>>--
>>-- Tests for TRIGGERS
>>--
>>create table cs313t90 (
+>   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 BYTES)  character set UTF8,
+>   ISO_col2   varchar(36 BYTES)  character set UTF8
+>   )no partitions;

--- SQL operation complete.
>>--
>>create TRIGGER TRG1_cs313t90   before UPDATE on cs313t90   REFERENCING NEW as TRG  FOR EACH ROW
+>       set TRG.UCS2_col2 = _UTF8'abc' ;

*** ERROR[3131] The statement just entered is currently not supported.

*** ERROR[8822] The statement was not prepared.

>>--
>>insert into cs313t90 values('Test1','Test1','Test1','Test1'), ('test111','test1111','test111','test1111');

--- 2 row(s) inserted.
>>UPDATE cs313t90 set ISO_col2 = _UTF8'Iso1' where UCS2_col1 = _utf8'Test1';

--- 1 row(s) updated.
>>select * from cs313t90 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 cs313t90 set ISO_col2 = _UTF8'Iso1' where UCS2_col1 = _utf8'Test1';

--- 1 row(s) updated.
>>select * from cs313t90 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 cs313t90 values('Test2','Test2','Test2','Test2'), ('test222','test2222','test222','test2222');

--- 2 row(s) inserted.
>>UPDATE cs313t90 set ISO_col2 = _ucs2'UCS2' where UCS2_col1 = _utf8'Test2';

--- 1 row(s) updated.
>>select * from cs313t90 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 cs313t90 set ISO_col2 = _ucs2'UCS2' where UCS2_col1 = _utf8'Test2';

--- 1 row(s) updated.
>>select * from cs313t90 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 cs313t90 values('Test3','Test3','Test3','Test3'), ('test333','test3333','test333','test3333');

--- 2 row(s) inserted.
>>UPDATE cs313t90 set UCS2_col1 = _UTF8'Iso3' where ISO_col2 = _ucs2'Test3';

--- 1 row(s) updated.
>>select * from cs313t90 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 cs313t90 set UCS2_col1 = _UTF8'Iso3' where ISO_col2 = _ucs2'Test3';

--- 1 row(s) updated.
>>select * from cs313t90 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_cs313t90;

*** ERROR[3131] The statement just entered is currently not supported.

*** ERROR[8822] The statement was not prepared.

>>create TRIGGER TRG1_cs313t90   before UPDATE on cs313t90   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 cs313t90 values('Test4','Test4','Test4','Test4'), ('test444','test4444','test444','test4444');

--- 2 row(s) inserted.
>>UPDATE cs313t90 set ISO_col1 = _UTF8'Iso4' where UCS2_col1 = _utf8'Test4';

--- 1 row(s) updated.
>>select * from cs313t90 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 cs313t90 set ISO_col1 = _UTF8'Iso4' where UCS2_col1 = _utf8'Test4';

--- 1 row(s) updated.
>>select * from cs313t90 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 cs313t90 values('Test5','Test5','Test5','Test5'), ('test555','test5555','test555','test5555');

--- 2 row(s) inserted.
>>UPDATE cs313t90 set ISO_col1 = _ucs2'UCS5' where UCS2_col1 = _utf8'Test5';

--- 1 row(s) updated.
>>select * from cs313t90 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 cs313t90 set ISO_col1 = _ucs2'UCS5' where UCS2_col1 = _utf8'Test5';

--- 1 row(s) updated.
>>select * from cs313t90 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 cs313t90 values('Test6','Test6','Test6','Test6'), ('test666','test6666','test666','test6666');

--- 2 row(s) inserted.
>>UPDATE cs313t90 set UCS2_col1 = _UTF8'Iso6' where UCS2_col2 = _ucs2'Test6';

--- 1 row(s) updated.
>>select * from cs313t90 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 cs313t90 set UCS2_col1 = _UTF8'Iso6' where UCS2_col2 = _ucs2'Test6';

--- 1 row(s) updated.
>>select * from cs313t90 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 cs313t91 (
+>   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 BYTES)  character set UTF8,
+>   ISO_col2   varchar(36 BYTES)  character set UTF8,
+>   PRIMARY KEY (PRKY) ) STORE BY primary key;

--- SQL operation complete.
>>--
>>create TRIGGER TRG1_cs313t91   before UPDATE on cs313t91   REFERENCING NEW as TRG  FOR EACH ROW
+>       set TRG.UCS2_col2 = _UTF8'abc' ;

*** ERROR[3131] The statement just entered is currently not supported.

*** ERROR[8822] The statement was not prepared.

>>--
>>insert into cs313t91 values(1,'Test1','Test1','Test1','Test1'), (2,'test111','test1111','test111','test1111');

--- 2 row(s) inserted.
>>UPDATE cs313t91 set ISO_col2 = _UTF8'Iso1' where UCS2_col1 = _utf8'Test1';

--- 1 row(s) updated.
>>select * from cs313t91 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 cs313t91 set ISO_col2 = _UTF8'Iso1' where UCS2_col1 = _utf8'Test1';

--- 1 row(s) updated.
>>select * from cs313t91 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 cs313t91 values(3,'Test2','Test2','Test2','Test2'), (4,'test222','test2222','test222','test2222');

--- 2 row(s) inserted.
>>UPDATE cs313t91 set ISO_col2 = _ucs2'UCS2' where UCS2_col1 = _utf8'Test2';

--- 1 row(s) updated.
>>select * from cs313t91 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 cs313t91 set ISO_col2 = _ucs2'UCS2' where UCS2_col1 = _utf8'Test2';

--- 1 row(s) updated.
>>select * from cs313t91 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 cs313t91 values(5,'Test3','Test3','Test3','Test3'), (6,'test333','test3333','test333','test3333');

--- 2 row(s) inserted.
>>UPDATE cs313t91 set UCS2_col1 = _UTF8'Iso3' where ISO_col2 = _ucs2'Test3';

--- 1 row(s) updated.
>>select * from cs313t91 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 cs313t91 set UCS2_col1 = _UTF8'Iso3' where ISO_col2 = _ucs2'Test3';

--- 1 row(s) updated.
>>select * from cs313t91 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_cs313t91;

*** ERROR[3131] The statement just entered is currently not supported.

*** ERROR[8822] The statement was not prepared.

>>create TRIGGER TRG1_cs313t91   before UPDATE on cs313t91   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 cs313t91 values(7,'Test4','Test4','Test4','Test4'), (8,'test444','test4444','test444','test4444');

--- 2 row(s) inserted.
>>UPDATE cs313t91 set ISO_col1 = _UTF8'Iso4' where UCS2_col1 = _utf8'Test4';

--- 1 row(s) updated.
>>select * from cs313t91 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 cs313t91 set ISO_col1 = _UTF8'Iso4' where UCS2_col1 = _utf8'Test4';

--- 1 row(s) updated.
>>select * from cs313t91 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 cs313t91 values(9,'Test5','Test5','Test5','Test5'), (10,'test555','test5555','test555','test5555');

--- 2 row(s) inserted.
>>UPDATE cs313t91 set ISO_col1 = _ucs2'UCS5' where UCS2_col1 = _utf8'Test5';

--- 1 row(s) updated.
>>select * from cs313t91 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 cs313t91 set ISO_col1 = _ucs2'UCS5' where UCS2_col1 = _utf8'Test5';

--- 1 row(s) updated.
>>select * from cs313t91 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 cs313t91 values(11,'Test6','Test6','Test6','Test6'), (12,'test666','test6666','test666','test6666');

--- 2 row(s) inserted.
>>UPDATE cs313t91 set UCS2_col1 = _UTF8'Iso6' where UCS2_col2 = _ucs2'Test6';

--- 1 row(s) updated.
>>select * from cs313t91 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 cs313t91 set UCS2_col1 = _UTF8'Iso6' where UCS2_col2 = _ucs2'Test6';

--- 1 row(s) updated.
>>select * from cs313t91 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 volatile table cs313t92 (
+>  bname CHAR(60) CHARACTER SET utf8 NO DEFAULT NOT NULL
+>  )no partitions;

--- SQL operation complete.
>>insert into cs313t92 VALUES(_iso88591'abcdefghi'), ('xyz');

--- 2 row(s) inserted.
>>insert into cs313t92 VALUES(converttohex('abcdefghi')), (converttohex('xyz'));

--- 2 row(s) inserted.
>>insert into cs313t92 VALUES(_utf8 x'C3B6c3baC3BB' || 'xyz'), (_utf8 x'E0A0A0E0A0A1' || 'xyz');

--- 2 row(s) inserted.
>>insert into cs313t92 VALUES(_utf8 x'E0A0A0' || 'xyz'), ('xyz' || _utf8 x'E0A0A0E0A0A1');

--- 2 row(s) inserted.
>>--
>>-- Tests for RPAD and LPAD
>>--
>>select * from cs313t92 order by 1;

BNAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

616263646566676869                                                                                                                                                                                                                              
78797A                                                                                                                                                                                                                                          
abcdefghi                                                                                                                                                                                                                                       
xyz                                                                                                                                                                                                                                             
xyz??                                                                                                                                                                                                                                           
xyz                                                                                                                                                                                                                                          
?xyz                                                                                                                                                                                                                                            
??xyz                                                                                                                                                                                                                                           

--- 8 row(s) selected.
>>insert into cs313t92 select RPAD(_utf8 x'c3b6c3bac3bb' || '456789ABCDEFG',10) from  (values(1)) x(a);

--- 1 row(s) inserted.
>>insert into cs313t92 select LPAD(_UCS2 x'00F6 00FA 00FB' || '456',10) from  (values(1)) x(a);

--- 1 row(s) inserted.
>>
>>select left(bname,40) from cs313t92 order by 1;

(EXPR)
----------------------------------------------------------------------------------------------------------------------------------------------------------------

    456                                                                                                                                                      
616263646566676869                                                                                                                                              
78797A                                                                                                                                                          
abcdefghi                                                                                                                                                       
xyz                                                                                                                                                             
xyz??                                                                                                                                                           
456789A                                                                                                                                                      
xyz                                                                                                                                                          
?xyz                                                                                                                                                            
??xyz                                                                                                                                                           

--- 10 row(s) selected.
>>
>>create table cs313t93 (u1 char(8) character set utf8) no partition;

--- SQL operation complete.
>>--
>>-- Additional tests for INSERT statements
>>--
>>insert into cs313t93 values(_utf8 x'E0A0A0E0A0A1E0A0A2E0A0A3E0A0A4E0A0A5E0A0A6E0A0A7');

--- 1 row(s) inserted.
>>insert into cs313t93 values(_utf8 x'E0A0A0E1A0A1E0A0A2E0A0A3E0A0A4E0A0A5E0A0A6E0A0A7'),
+>                          (_utf8 x'E0A0A0E2A0A1E0A0A2E0A0A3E0A0A4E0A0A5E0A0A6E0A0A7');

--- 2 row(s) inserted.
>>--
>>-- NOTE: The 8-character UTF8 values in the following INSERT statement convert to 16 UCS2
>>--       characters because they are in the Surrogate Pair range!
>>--
>>insert into cs313t93 values(_utf8 x'F0A0A0A0F0A0A1A0F0A0A2A0F0A0A3A0F0A0A5A0F0A0A1A0F0A0A2A0F0A0A3A0'),
+>                          (_utf8 x'F0A0A0A0F0A0A2A0F0A0A2A0F0A0A3A0F0A0A6A0F0A0A1A0F0A0A2A0F0A0A3A0'),
+>                          (_utf8 x'F0A0A0A0F0A0A3A0F0A0A2A0F0A0A3A0F0A0A7A0F0A0A1A0F0A0A2A0F0A0A3A0'),
+>                          (_utf8 x'F0A0A0A0F0A0A4A0F0A0A2A0F0A0A3A0F0A0A8A0F0A0A1A0F0A0A2A0F0A0A3A0');

--- 4 row(s) inserted.
>>select converttohex(u1) from cs313t93 order by 1;

(EXPR)                                                          
----------------------------------------------------------------

E0A0A0E0A0A1E0A0A2E0A0A3E0A0A4E0A0A5E0A0A6E0A0A7                
E0A0A0E1A0A1E0A0A2E0A0A3E0A0A4E0A0A5E0A0A6E0A0A7                
E0A0A0E2A0A1E0A0A2E0A0A3E0A0A4E0A0A5E0A0A6E0A0A7                
F0A0A0A0F0A0A1A0F0A0A2A0F0A0A3A0F0A0A5A0F0A0A1A0F0A0A2A0F0A0A3A0
F0A0A0A0F0A0A2A0F0A0A2A0F0A0A3A0F0A0A6A0F0A0A1A0F0A0A2A0F0A0A3A0
F0A0A0A0F0A0A3A0F0A0A2A0F0A0A3A0F0A0A7A0F0A0A1A0F0A0A2A0F0A0A3A0
F0A0A0A0F0A0A4A0F0A0A2A0F0A0A3A0F0A0A8A0F0A0A1A0F0A0A2A0F0A0A3A0

--- 7 row(s) selected.
>>select converttohex(TRANSLATE(u1 using UTF8toUCS2)) from cs313t93 order by 1;

(EXPR)
--------------------------------------------------------------------------------------------------------------------------------

08200821082208230824082508260827                                                                                                
08201821082208230824082508260827                                                                                                
08202821082208230824082508260827                                                                                                
D842DC20D842DC60D842DCA0D842DCE0D842DD60D842DC60D842DCA0D842DCE0                                                                
D842DC20D842DCA0D842DCA0D842DCE0D842DDA0D842DC60D842DCA0D842DCE0                                                                
D842DC20D842DCE0D842DCA0D842DCE0D842DDE0D842DC60D842DCA0D842DCE0                                                                
D842DC20D842DD20D842DCA0D842DCE0D842DE20D842DC60D842DCA0D842DCE0                                                                

--- 7 row(s) selected.
>>select u1, char_length(u1) from cs313t93 order by 1;

U1                                (EXPR)    
--------------------------------  ----------

????????                                   8
????????                                   8
????????                                   8
????????                                   8
????????                                   8
????????                                   8
????????                                   8

--- 7 row(s) selected.
>>select u1, char_length(TRANSLATE(u1 using UTF8toUCS2)) from cs313t93 order by 1;

U1                                (EXPR)    
--------------------------------  ----------

????????                                   8
????????                                   8
????????                                   8
????????                                  16
????????                                  16
????????                                  16
????????                                  16

--- 7 row(s) selected.
>>
>>obey test313(clnup);
>>
>>drop schema cs313s cascade;

--- SQL operation complete.
>>
>>
>>log;
