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