| -- -*- mode: sql; coding: utf-8 -*- |
| -- Test: TEST310 |
| -- Functionality: This tests Implicit Casting and Translation feature |
| -- Expected files: EXPECTED310 |
| -- Tables created: cs310t* |
| -- Limitations: None |
| -- |
| -- @@@ START COPYRIGHT @@@ |
| -- |
| -- Licensed to the Apache Software Foundation (ASF) under one |
| -- or more contributor license agreements. See the NOTICE file |
| -- distributed with this work for additional information |
| -- regarding copyright ownership. The ASF licenses this file |
| -- to you under the Apache License, Version 2.0 (the |
| -- "License"); you may not use this file except in compliance |
| -- with the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, |
| -- software distributed under the License is distributed on an |
| -- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| -- KIND, either express or implied. See the License for the |
| -- specific language governing permissions and limitations |
| -- under the License. |
| -- |
| -- @@@ END COPYRIGHT @@@ |
| |
| --set terminal_charset utf8; -- NOT on SeaQuest |
| --set session default iso_mapping 'UTF8'; -- NOT on SeaQuest |
| |
| obey test310(clnup); |
| |
| log log310 clear; |
| |
| obey test310(ddl); |
| |
| obey test310(icat_tests); |
| |
| obey test310(clnup); |
| |
| log; |
| exit; |
| |
| ?section ddl |
| |
| create schema cs310s; |
| set schema cs310s; |
| |
| ?section icat_tests |
| |
| -- |
| -- NOTE: CHAR() function is giving non-deterministic results in R2.4 |
| -- CONSEQUENTLY, TESTS USING CHAR() FUNCTION ARE COMMENTED OUT. |
| ---------------------------------------------------------------------- |
| -- |
| -- Tests for ASCII function |
| -- |
| create table cs310t1 (a1 char(8) character set iso88591) no partition; |
| insert into cs310t1 values('a1a1'); |
| insert into cs310t1 values('u1u1'); |
| create table cs310t2 (u1 char(8) character set ucs2) no partition; |
| insert into cs310t2 values('a1a1'); |
| insert into cs310t2 values('u1u1'); |
| -- |
| select * from cs310t1 order by 1; |
| select ascii('a') from cs310t1 order by 1; |
| select ascii(CHAR(97)) from cs310t1 order by 1; -- SEE NOTE ABOVE |
| select ascii(CHAR(97, ISO88591)) from cs310t1 order by 1; -- SEE NOTE ABOVE |
| -- Following 4 queries should give error since ASCII doesn't work on UCS2 |
| select ascii(CHAR(97, UCS2)) from cs310t1 order by 1; |
| select ascii( _ucs2'a') from cs310t1 order by 1; |
| select ascii(u1) from cs310t2 order by 1; |
| select ascii( TRANSLATE(_iso88591'abcdefghijklmnop' using Iso88591ToUCS2) ) from cs310t2 order by 1; |
| -- |
| select * from cs310t2, cs310t1 order by 1,2; |
| select * from cs310t2, cs310t1 where 'a' = CHAR(ASCII('a1a1')) order by 1,2; -- SEE NOTE ABOVE |
| select * from cs310t2, cs310t1 where 'u' = CHAR(ASCII('u1u1'),UCS2) order by 1,2; -- SEE NOTE ABOVE |
| select * from cs310t2, cs310t1 where 'u' = lower(CHAR(ASCII(upper(a1)))) order by 1,2; -- SEE NOTE ABOVE |
| select * from cs310t2, cs310t1 where CHAR(ASCII('a1a1')) = 'a' order by 1,2; -- SEE NOTE ABOVE |
| select * from cs310t2, cs310t1 where CHAR(ASCII('u1u1'),UCS2) = 'u' order by 1,2; -- SEE NOTE ABOVE |
| select * from cs310t2, cs310t1 where lower(CHAR(ASCII(upper(a1)))) = 'u' order by 1,2; -- SEE NOTE ABOVE |
| -- |
| select ascii((select 'a1a1' from cs310t1 where a1='u1u1')) from cs310t1 order by 1; |
| select ascii((select 'a1a1' from cs310t2 where u1='a1a1')) from cs310t1 order by 1; |
| select a1 from cs310t1 where ascii(a1) = ascii((select min(a1) from cs310t1)) order by 1; |
| select a1 from cs310t1 where ascii(a1) = ascii((select TRANSLATE(max(u1) using UCS2toISO88591) from cs310t2)) order by 1; |
| select a1 from cs310t1 where ascii(a1) = ascii((select max(TRANSLATE(u1 using UCS2toISO88591)) from cs310t2)) order by 1; |
| -- |
| select ascii((select CHAR(97) from cs310t1 where a1='u1u1')) from cs310t1 order by 1; -- SEE NOTE ABOVE |
| select ascii((select CHAR(97) from cs310t2 where u1='a1a1')) from cs310t1 order by 1; -- SEE NOTE ABOVE |
| -- |
| select a1 from cs310t1 where 97 = ascii((select min('a') from cs310t1 where a1='u1u1')) order by 1; |
| select a1 from cs310t1 where ascii(CHAR(97)) = ascii((select min('a') from cs310t1 where a1='u1u1')) order by 1; -- SEE NOTE ABOVE |
| select a1 from cs310t1 where ascii(CHAR(97)) = ascii((select min(_iso88591'a') from cs310t1 where a1='u1u1')) order by 1; -- SEE NOTE ABOVE |
| select a1 from cs310t1 where 97 = ascii((select min(CHAR(97)) from cs310t1 where a1='u1u1')) order by 1; -- SEE NOTE ABOVE |
| select a1 from cs310t1 where ascii(CHAR(97)) = ascii((select min(CHAR(97)) from cs310t1 where a1='u1u1')) order by 1; -- SEE NOTE ABOVE |
| -- |
| select a1 from cs310t1 where 97 = ascii((select min('a') from cs310t2 where u1='a1a1')) order by 1; |
| select a1 from cs310t1 where ascii(CHAR(97)) = ascii((select min('a') from cs310t2 where u1='a1a1')) order by 1; -- SEE NOTE ABOVE |
| select a1 from cs310t1 where ascii(CHAR(97)) = ascii((select min(_iso88591'a') from cs310t2 where u1='a1a1')) order by 1; -- SEE NOTE ABOVE |
| select a1 from cs310t1 where 97 = ascii((select min(CHAR(97)) from cs310t2 where u1='a1a1')) order by 1; -- SEE NOTE ABOVE |
| select a1 from cs310t1 where ascii(CHAR(97)) = ascii((select min(CHAR(97)) from cs310t2 where u1='a1a1')) order by 1; -- SEE NOTE ABOVE |
| -- |
| -- Tests for basic UNIONs |
| -- |
| create table cs310t3 (a1 char(8) character set iso88591) no partition; |
| insert into cs310t3 values('a1a1'); |
| insert into cs310t3 values('u1u1'); |
| select * from cs310t3 order by 1; |
| create table cs310t4 (u1 char(8) character set ucs2) no partition; |
| insert into cs310t4 values('a1a1'); |
| insert into cs310t4 values('u1u1'); |
| select * from cs310t4 order by 1; |
| select * from cs310t3 union (select * from cs310t4) order by 1; |
| select * from cs310t3 union all (select * from cs310t4) order by 1; |
| -- |
| -- Tests for basic SQL functions |
| -- |
| select * from cs310t3, cs310t4 order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = case when a1 > 'a1a' then u1 else 'a100' end order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = case when a1 > 'a1a' then 'a1a1' else 'a100' end order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = coalesce(cast(NULL as varchar(8) character set ISO88591), u1, a1) order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = substr('a1a1zzzz',1,4) order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = concat( substr(a1,1,2), 'a1' ) order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = decode(a1,'a1a1','Found', u1, 'u1u1', 'b1b1', 'b1b1', 'NotFound') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = insert('a1', 2, 0, '1a') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = replace(u1, 'u1', 'a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = replace('u1u1', 'u1', 'a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = replace(_ISO88591'u1u1', 'u1', 'a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = replace('u1u1', _ISO88591'u1', 'a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = replace('u1u1', 'u1', _ISO88591'a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = isnull(a1,'a1a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = isnull(u1,'a1a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = isnull('a1a1','a1a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = isnull(CAST(NULL as char(8) character set iso88591),'a1a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = LOWER('A1A1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = LEFT('a1a1ZZZZ',4) order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = LPAD('a1',4,'a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = LPAD(substr(a1,1,2),4,'a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = NULLIF(a1,u1) order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = NULLIF(a1,'a1b1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = NVL(a1,'a1b1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = NVL(a1,'a1a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = NVL('a1a1','a1b1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = NVL(cast(NULL as char(8) character set iso88591),'a1a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = NVL(cast(NULL as char(8)),'a1a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = right(TRIM(u1),4) order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = right(' ' || TRIM(u1),4) order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = right(_ISO88591' ' || TRIM(u1),4) order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = right(' ' || TRIM(a1),4) order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = right('ZZZZa1a1',4) order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = RPAD('a1',4,'a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = RPAD(substr(a1,1,2),4,'a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = trim(u1) order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = trim(' a1a1 ') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = rtrim(u1) order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = rtrim(' a1a1 ') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = ltrim(u1) order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = ltrim(' a1a1 ') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = UPPER('a1a1') order by 1,2; |
| select * from cs310t3, cs310t4 where a1 = cast('u1u1' as varchar(8) ) order by 1,2; |
| -- |
| -- Tests for ASSIGN |
| -- |
| create table cs310t10 (a1 char(80) character set iso88591, u1 char(80) character set ucs2 ) no partition; |
| insert into cs310t10 values(CHAR(96), CHAR(96)); -- SEE NOTE ABOVE |
| insert into cs310t10 values(SPACE(3), SPACE(3)); |
| insert into cs310t10 values(SPACE(3, ISO88591), SPACE(3, ISO88591)); |
| insert into cs310t10 values(SPACE(3, UCS2), SPACE(3, UCS2)); |
| insert into cs310t10 values(DATEFORMAT(DATE'2009-04-15',USA), DATEFORMAT(DATE'2009-04-15',USA)); |
| insert into cs310t10 values(DAYNAME(DATE'2009-04-15'), DAYNAME(DATE'2009-04-15')); |
| insert into cs310t10 values(MONTHNAME(DATE'2009-04-15'), MONTHNAME(DATE'2009-04-15')); |
| insert into cs310t10 values(CONVERTTOHEX(_ucs2'abc'), CONVERTTOHEX(_ucs2'abc')); |
| insert into cs310t10 values(CONVERTTOHEX(_iso88591'abc'), CONVERTTOHEX(_iso88591'abc')); |
| insert into cs310t10 values(CASE when 1=1 then 'abc' else 'def' end, CASE when 1=1 then 'abc' else 'def' end); |
| insert into cs310t10 values(CASE when 1=1 then _iso88591'abc' else _iso88591'def' end, |
| CASE when 1=1 then _iso88591'abc' else _iso88591'def' end); |
| insert into cs310t10 values(CAST('abc' as varchar(4)), CAST('abc' as varchar(4))); |
| insert into cs310t10 values(CAST(_iso88591'abc' as varchar(4)), CAST(_iso88591'abc' as varchar(4))); |
| insert into cs310t10 values(COALESCE('abc','def','xyz'), COALESCE('abc','def','xyz')); |
| insert into cs310t10 values(COALESCE(_iso88591'abc',_iso88591'def',_iso88591'xyz'), |
| COALESCE(_iso88591'abc',_iso88591'def',_iso88591'xyz')); |
| insert into cs310t10 values(CONCAT('abc','def'), CONCAT('abc','def')); |
| insert into cs310t10 values(CONCAT(_iso88591'abc',_iso88591'def'), CONCAT(_iso88591'abc',_iso88591'def')); |
| insert into cs310t10 values(_iso88591'abc' || _iso88591'def', _iso88591'abc' || _iso88591'def'); |
| insert into cs310t10 values(_iso88591'abc' || _ucs2'def', _iso88591'abc' || _ucs2'def'); |
| insert into cs310t10 values(_ucs2'abc' || _iso88591'def', _ucs2'abc' || _iso88591'def'); |
| insert into cs310t10 values(DECODE('abc','def','EQ','NE'), DECODE('abc','def','EQ','NE')); |
| insert into cs310t10 values(DECODE(_iso88591'abc',_iso88591'def',_iso88591'EQ',_iso88591'NE'), |
| DECODE(_iso88591'abc',_iso88591'def',_iso88591'EQ',_iso88591'NE')); |
| insert into cs310t10 values(INSERT('abcghi',4,0,'def'), INSERT('abcghi',4,0,'def')); |
| insert into cs310t10 values(INSERT(_iso88591'abcghi',4,0,_iso88591'def'), |
| INSERT(_iso88591'abcghi',4,0,_iso88591'def')); |
| insert into cs310t10 values(ISNULL('abc','def'), ISNULL('abc','def')); |
| insert into cs310t10 values(ISNULL(_iso88591'abc',_iso88591'def'), ISNULL(_iso88591'abc',_iso88591'def')); |
| insert into cs310t10 values(LEFT('abcdef',3), LEFT('abcdef',3)); |
| insert into cs310t10 values(LEFT(_iso88591'abcdef',3), LEFT(_iso88591'abcdef',3)); |
| insert into cs310t10 values(LOWER('ABC'), LOWER('ABC')); |
| insert into cs310t10 values(LOWER(_iso88591'ABC'), LOWER(_iso88591'ABC')); |
| insert into cs310t10 values(LPAD('def',6,'abc'), LPAD('def',6,'abc')); |
| insert into cs310t10 values(LPAD(_iso88591'def',6,_iso88591'abc'), LPAD(_iso88591'def',6,_iso88591'abc')); |
| insert into cs310t10 values(LTRIM(' abc'), LTRIM(' abc')); |
| insert into cs310t10 values(LTRIM(_iso88591' abc'), LTRIM(_iso88591' abc')); |
| insert into cs310t10 values(NULLIF('abc','def'), NULLIF('abc','def')); |
| insert into cs310t10 values(NULLIF(_iso88591'abc',_iso88591'def'), NULLIF('abc_iso88591',_iso88591'def')); |
| insert into cs310t10 values(NVL('abc','def'), NVL('abc','def')); |
| insert into cs310t10 values(NVL(_iso88591'abc',_iso88591'def'), NVL(_iso88591'abc',_iso88591'def')); |
| insert into cs310t10 values(REPLACE('abcxyzghi','xyz','def'), REPLACE('abcxyzghi','xyz','def')); |
| insert into cs310t10 values(REPLACE(_iso88591'abcxyzghi',_iso88591'xyz',_iso88591'def'), |
| REPLACE(_iso88591'abcxyzghi',_iso88591'xyz',_iso88591'def')); |
| insert into cs310t10 values(RIGHT('defabc',3), RIGHT('defabc',3)); |
| insert into cs310t10 values(RIGHT(_iso88591'defabc',3), RIGHT(_iso88591'defabc',3)); |
| insert into cs310t10 values(RPAD('abc',6,'def'), RPAD('abc',6,'def')); |
| insert into cs310t10 values(RPAD(_iso88591'abc',6,_iso88591'def'), RPAD(_iso88591'abc',6,_iso88591'def')); |
| insert into cs310t10 values(RTRIM('abc '), RTRIM('abc ')); |
| insert into cs310t10 values(RTRIM(_iso88591'abc '), RTRIM(_iso88591'abc ')); |
| insert into cs310t10 values(SUBSTR('abcdefghi',1,6), SUBSTR('abcdefghi',1,6)); |
| insert into cs310t10 values(SUBSTR(_iso88591'abcdefghi',1,6), SUBSTR(_iso88591'abcdefghi',1,6)); |
| insert into cs310t10 values(TRANSLATE('abcdef' using UCS2toISO88591), |
| TRANSLATE('abcdef' using UCS2toISO88591)); |
| insert into cs310t10 values(TRANSLATE('abcdef' using ISO88591toUCS2), |
| TRANSLATE('abcdef' using ISO88591toUCS2)); |
| insert into cs310t10 values(TRANSLATE(_iso88591'abcdef' using UCS2toISO88591), |
| TRANSLATE(_iso88591'abcdef' using UCS2toISO88591)); |
| insert into cs310t10 values(TRANSLATE(_iso88591'abcdef' using ISO88591toUCS2), |
| TRANSLATE(_iso88591'abcdef' using ISO88591toUCS2)); |
| insert into cs310t10 values(TRIM(' abc '), TRIM(' abc ')); |
| insert into cs310t10 values(TRIM(_iso88591' abc '), TRIM(_iso88591' abc ')); |
| insert into cs310t10 values(UPPER('abcdef'), UPPER('abcdef')); |
| insert into cs310t10 values(UPPER(_iso88591'abcdef'), UPPER(_iso88591'abcdef')); |
| select count(*) from cs310t10; |
| -- |
| -- Tests for Comparisons |
| -- |
| create table cs310t20 (a1 char(20) character set iso88591, u1 char(20) character set ucs2 ) no partition; |
| insert into cs310t20 values(CHAR(97), CHAR(97) ); -- SEE NOTE ABOVE |
| insert into cs310t20 values(SPACE(3), SPACE(3) ); |
| insert into cs310t20 values(DATEFORMAT(DATE'2009-04-15',USA), DATEFORMAT(DATE'2009-04-15',USA) ); |
| insert into cs310t20 values(DAYNAME(DATE'2009-04-15'), DAYNAME(DATE'2009-04-15') ); |
| insert into cs310t20 values(MONTHNAME(DATE'2009-04-15'), MONTHNAME(DATE'2009-04-15') ); |
| insert into cs310t20 values(CONVERTTOHEX(_ucs2'abc'), CONVERTTOHEX(_ucs2'abc') ); |
| insert into cs310t20 values(CONVERTTOHEX(_iso88591'abc'), CONVERTTOHEX(_iso88591'abc') ); |
| insert into cs310t20 values('abc', 'abc') ; |
| insert into cs310t20 values('ABC', 'ABC') ; |
| insert into cs310t20 values('def', 'def') ; |
| insert into cs310t20 values('abcdef', 'abcdef') ; |
| insert into cs310t20 values('ABCDEF', 'ABCDEF') ; |
| insert into cs310t20 values('abcdefghi', 'abcdefghi') ; |
| insert into cs310t20 values('xyz', 'xyz') ; |
| insert into cs310t20 values('EQ','EQ'); |
| insert into cs310t20 values('NE','NE'); |
| insert into cs310t20 values('abcghi', 'abcghi'); |
| insert into cs310t20 values('abcdefghi', 'abcdefghi'); |
| select * from cs310t20 order by 1,2; |
| select * from cs310t20 where a1 = CHAR(97) order by 1,2; -- SEE NOTE ABOVE |
| select * from cs310t20 where u1 = CHAR(97) order by 1,2; -- SEE NOTE ABOVE |
| select * from cs310t20 where a1 = SPACE(3) order by 1,2; |
| select * from cs310t20 where u1 = SPACE(3) order by 1,2; |
| select * from cs310t20 where u1 = SPACE(3, ISO88591) order by 1,2; |
| select * from cs310t20 where a1 = SPACE(3, UCS2) order by 1,2; |
| select * from cs310t20 where a1 = DATEFORMAT(DATE'2009-04-15',USA) order by 1,2; |
| select * from cs310t20 where u1 = DATEFORMAT(DATE'2009-04-15',USA) order by 1,2; |
| select * from cs310t20 where a1 = DAYNAME(DATE'2009-04-15') order by 1,2; |
| select * from cs310t20 where u1 = DAYNAME(DATE'2009-04-15') order by 1,2; |
| select * from cs310t20 where a1 = MONTHNAME(DATE'2009-04-15') order by 1,2; |
| select * from cs310t20 where u1 = MONTHNAME(DATE'2009-04-15') order by 1,2; |
| -- |
| create table cs310t21 (a1 char(80) character set iso88591, u1 char(80) character set ucs2 ) no partition; |
| insert into cs310t21 values(CURRENT_USER, CURRENT_USER); |
| select 'a1 = CURRENT_USER' from cs310t21 where a1 = CURRENT_USER order by 1; |
| select 'u1 = CURRENT_USER' from cs310t21 where u1 = CURRENT_USER order by 1; |
| select 'a1 = SESSION_USER' from cs310t21 where a1 = SESSION_USER order by 1; |
| select 'u1 = SESSION_USER' from cs310t21 where u1 = SESSION_USER order by 1; |
| select 'a1 = USER' from cs310t21 where a1 = USER order by 1; |
| select 'u1 = USER' from cs310t21 where u1 = USER order by 1; |
| -- |
| select * from cs310t20 order by 1,2; |
| select * from cs310t20 where a1 = CONVERTTOHEX(_ucs2'abc') order by 1,2; |
| select * from cs310t20 where u1 = CONVERTTOHEX(_ucs2'abc') order by 1,2; |
| select * from cs310t20 where a1 = CONVERTTOHEX(_iso88591'abc') order by 1,2; |
| select * from cs310t20 where u1 = CONVERTTOHEX(_iso88591'abc') order by 1,2; |
| select * from cs310t20 where a1 = CASE when 1=1 then 'abc' else 'def' end order by 1,2; |
| select * from cs310t20 where u1 = CASE when 1=1 then _iso88591'abc' else _iso88591'def' end order by 1,2; |
| select * from cs310t20 where a1 = CAST('abc' as varchar(4)) order by 1,2; |
| select * from cs310t20 where u1 = CAST('abc' as varchar(4)) order by 1,2; |
| select * from cs310t20 where a1 = CAST(_iso88591'abc' as varchar(4)) order by 1,2; |
| select * from cs310t20 where u1 = CAST(_iso88591'abc' as varchar(4)) order by 1,2; |
| select * from cs310t20 where a1 = COALESCE('abc','def','xyz') order by 1,2; |
| select * from cs310t20 where u1 = COALESCE(_iso88591'abc',_iso88591'def',_iso88591'xyz') order by 1,2; |
| select * from cs310t20 where a1 = CONCAT('abc','def') order by 1,2; |
| select * from cs310t20 where u1 = CONCAT(_iso88591'abc',_iso88591'def') order by 1,2; |
| select * from cs310t20 where u1 = _iso88591'abc' || _iso88591'def' order by 1,2; |
| select * from cs310t20 where a1 = _iso88591'abc' || _ucs2'def' order by 1,2; |
| select * from cs310t20 where u1 = _iso88591'abc' || _ucs2'def' order by 1,2; |
| select * from cs310t20 where a1 = _ucs2'abc' || _iso88591'def' order by 1,2; |
| select * from cs310t20 where u1 = _ucs2'abc' || _iso88591'def' order by 1,2; |
| select * from cs310t20 where a1 = DECODE('abc','def','EQ','NE') order by 1,2; |
| select * from cs310t20 where u1 = DECODE(_iso88591'abc',_iso88591'def',_iso88591'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t20 where a1 = INSERT('abcghi',4,0,'def') order by 1,2; |
| select * from cs310t20 where u1 = INSERT(_iso88591'abcghi',4,0,_iso88591'def') order by 1,2; |
| select * from cs310t20 where a1 = ISNULL('abc','def') order by 1,2; |
| select * from cs310t20 where u1 = ISNULL(_iso88591'abc',_iso88591'def') order by 1,2; |
| select * from cs310t20 where a1 = LEFT('abcdef',3) order by 1,2; |
| select * from cs310t20 where u1 = LEFT(_iso88591'abcdef',3) order by 1,2; |
| select * from cs310t20 where a1 = LOWER('ABC') order by 1,2; |
| select * from cs310t20 where u1 = LOWER(_iso88591'ABC') order by 1,2; |
| select * from cs310t20 where a1 = LPAD('def',6,'abc') order by 1,2; |
| select * from cs310t20 where u1 = LPAD(_iso88591'def',6,_iso88591'abc') order by 1,2; |
| select * from cs310t20 where a1 = LTRIM(' abc') order by 1,2; |
| select * from cs310t20 where u1 = LTRIM(_iso88591' abc') order by 1,2; |
| select * from cs310t20 where a1 = NULLIF('abc','def') order by 1,2; |
| select * from cs310t20 where u1 = NULLIF(_iso88591'abc',_iso88591'def') order by 1,2; |
| select * from cs310t20 where a1 = NVL('abc','def') order by 1,2; |
| select * from cs310t20 where u1 = NVL(_iso88591'abc',_iso88591'def') order by 1,2; |
| select * from cs310t20 where a1 = REPLACE('abcxyzghi','xyz','def') order by 1,2; |
| select * from cs310t20 where u1 = REPLACE(_iso88591'abcxyzghi',_iso88591'xyz',_iso88591'def') order by 1,2; |
| select * from cs310t20 where a1 = RIGHT('defabc',3) order by 1,2; |
| select * from cs310t20 where u1 = RIGHT(_iso88591'defabc',3) order by 1,2; |
| select * from cs310t20 where a1 = RPAD('abc',6,'def') order by 1,2; |
| select * from cs310t20 where u1 = RPAD(_iso88591'abc',6,_iso88591'def') order by 1,2; |
| select * from cs310t20 where a1 = RTRIM('abc ') order by 1,2; |
| select * from cs310t20 where u1 = RTRIM(_iso88591'abc ') order by 1,2; |
| select * from cs310t20 where a1 = SUBSTR('abcdefghi',1,6) order by 1,2; |
| select * from cs310t20 where u1 = SUBSTR(_iso88591'abcdefghi',1,6) order by 1,2; |
| select * from cs310t20 where a1 = TRANSLATE('abcdef' using UCS2toISO88591) order by 1,2; |
| select * from cs310t20 where u1 = TRANSLATE('abcdef' using UCS2toISO88591) order by 1,2; |
| select * from cs310t20 where a1 = TRANSLATE('abcdef' using ISO88591toUCS2) order by 1,2; |
| select * from cs310t20 where u1 = TRANSLATE('abcdef' using ISO88591toUCS2) order by 1,2; |
| select * from cs310t20 where a1 = TRANSLATE(_iso88591'abcdef' using UCS2toISO88591) order by 1,2; |
| select * from cs310t20 where u1 = TRANSLATE(_iso88591'abcdef' using UCS2toISO88591) order by 1,2; |
| select * from cs310t20 where a1 = TRANSLATE(_iso88591'abcdef' using ISO88591toUCS2) order by 1,2; |
| select * from cs310t20 where u1 = TRANSLATE(_iso88591'abcdef' using ISO88591toUCS2) order by 1,2; |
| select * from cs310t20 where a1 = TRIM(' abc ') order by 1,2; |
| select * from cs310t20 where u1 = TRIM(_iso88591' abc ') order by 1,2; |
| select * from cs310t20 where a1 = UPPER('abcdef') order by 1,2; |
| select * from cs310t20 where u1 = UPPER(_iso88591'abcdef') order by 1,2; |
| -- |
| -- Tests for Concatenation |
| -- |
| create table cs310t30 (a1 char(20) character set iso88591, u1 char(20) character set ucs2 ) no partition; |
| create table cs310t31 (a1 char(80) character set iso88591, u1 char(80) character set ucs2 ) no partition; |
| insert into cs310t30 values('ZA','ZA'); |
| insert into cs310t30 values('Z','Z'); |
| insert into cs310t30 values('Z'||DATEFORMAT(DATE'2009-04-15',USA),'Z'||DATEFORMAT(DATE'2009-04-15',USA) ); |
| insert into cs310t30 values('Z' || DAYNAME(DATE'2009-04-15'), 'Z' || DAYNAME(DATE'2009-04-15') ); |
| insert into cs310t30 values('Z' || MONTHNAME(DATE'2009-04-15'), 'Z' || MONTHNAME(DATE'2009-04-15') ); |
| insert into cs310t31 values(CURRENT_USER, CURRENT_USER); |
| insert into cs310t31 values('Z'||CURRENT_USER, 'Z'||CURRENT_USER); |
| insert into cs310t30 values('Z' || CONVERTTOHEX(_ucs2'abc'), 'Z' || CONVERTTOHEX(_ucs2'abc') ); |
| insert into cs310t30 values('Z' || CONVERTTOHEX(_iso88591'abc'), 'Z' || CONVERTTOHEX(_iso88591'abc') ); |
| insert into cs310t30 values('Za','Za'); |
| insert into cs310t30 values('Zd','Zd'); |
| insert into cs310t30 values('Zabc','Zabc'); |
| insert into cs310t30 values('Zdef','Zdef'); |
| insert into cs310t30 values('Zxyz','Zxyz'); |
| insert into cs310t30 values('ZEQ','ZEQ'); |
| insert into cs310t30 values('ZNE','ZNE'); |
| insert into cs310t30 values('Zabcdef','Zabcdef'); |
| insert into cs310t30 values('ZABCDEF','ZABCDEF'); |
| insert into cs310t30 values('Zabcdefghi','Zabcdefghi'); |
| select * from cs310t30 order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || CHAR(97) order by 1,2; -- SEE NOTE ABOVE |
| select * from cs310t30 where a1 = _ucs2'Z' || CHAR(97) order by 1,2; -- SEE NOTE ABOVE |
| select * from cs310t30 where a1 = _iso88591'Z' || SPACE(3) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || SPACE(3) order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || SPACE(3, ISO88591) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || SPACE(3, ISO88591) order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || SPACE(3, UCS2) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || SPACE(3, UCS2) order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || DATEFORMAT(DATE'2009-04-15',USA) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || DATEFORMAT(DATE'2009-04-15',USA) order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || DAYNAME(DATE'2009-04-15') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || DAYNAME(DATE'2009-04-15') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || MONTHNAME(DATE'2009-04-15') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || MONTHNAME(DATE'2009-04-15') order by 1,2; |
| select * from cs310t31 where a1 != CURRENT_USER AND (a1 != ('Z'||CURRENT_USER)) order by 1,2; |
| select '_iso88591''Z'' || CURRENT_USER' from cs310t31 where a1 = _iso88591'Z' || CURRENT_USER order by 1; |
| select '_ucs2''Z'' || CURRENT_USER' from cs310t31 where a1 = _ucs2'Z' || CURRENT_USER order by 1; |
| select '_iso88591''Z'' || SESSION_USER' from cs310t31 where a1 = _iso88591'Z' || SESSION_USER order by 1; |
| select '_ucs2''Z'' || SESSION_USER' from cs310t31 where a1 = _ucs2'Z' || SESSION_USER order by 1; |
| select '_iso88591''Z'' || USER' from cs310t31 where a1 = _iso88591'Z' || USER order by 1; |
| select '_ucs2''Z'' || USER' from cs310t31 where a1 = _ucs2'Z' || USER order by 1; |
| select * from cs310t30 where a1 = _iso88591'Z' || CONVERTTOHEX(_ucs2'abc') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || CONVERTTOHEX(_ucs2'abc') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || CONVERTTOHEX(_iso88591'abc') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || CONVERTTOHEX(_iso88591'abc') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || CASE when 1=1 then 'abc' else 'def' end order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || CASE when 1=1 then 'abc' else 'def' end order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || CASE when 1=1 then _iso88591'abc' else _iso88591'def' end order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || CAST('abc' as varchar(4)) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || CAST('abc' as varchar(4)) order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || CAST(_iso88591'abc' as varchar(4)) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || CAST(_iso88591'abc' as varchar(4)) order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || COALESCE('abc','def','xyz') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || COALESCE('abc','def','xyz') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || COALESCE(_iso88591'abc',_iso88591'def',_iso88591'xyz') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || CONCAT('abc','def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || CONCAT('abc','def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || CONCAT(_iso88591'abc',_iso88591'def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || (_iso88591'abc' || _iso88591'def') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || (_iso88591'abc' || _ucs2'def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || (_iso88591'abc' || _ucs2'def') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || (_ucs2'abc' || _iso88591'def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || (_ucs2'abc' || _iso88591'def') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || DECODE('abc','def','EQ','NE') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || DECODE('abc','def','EQ','NE') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || DECODE(_iso88591'abc',_iso88591'def',_iso88591'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || INSERT('abcghi',4,0,'def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || INSERT('abcghi',4,0,'def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || INSERT(_iso88591'abcghi',4,0,_iso88591'def') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || ISNULL('abc','def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || ISNULL('abc','def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || ISNULL(_iso88591'abc',_iso88591'def') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || LEFT('abcdef',3) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || LEFT('abcdef',3) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || LEFT(_iso88591'abcdef',3) order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || LOWER('ABC') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || LOWER('ABC') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || LOWER(_iso88591'ABC') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || LPAD('def',6,'abc') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || LPAD('def',6,'abc') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || LPAD(_iso88591'def',6,_iso88591'abc') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || LTRIM(' abc') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || LTRIM(' abc') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || LTRIM(_iso88591' abc') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || NULLIF('abc','def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || NULLIF('abc','def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || NULLIF(_iso88591'abc',_iso88591'def') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || NVL('abc','def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || NVL('abc','def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || NVL(_iso88591'abc',_iso88591'def') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || REPLACE('abcxyzghi','xyz','def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || REPLACE('abcxyzghi','xyz','def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || REPLACE(_iso88591'abcxyzghi',_iso88591'xyz',_iso88591'def') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || RIGHT('defabc',3) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || RIGHT('defabc',3) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || RIGHT(_iso88591'defabc',3) order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || RPAD('abc',6,'def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || RPAD('abc',6,'def') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || RPAD(_iso88591'abc',6,_iso88591'def') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || RTRIM('abc ') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || RTRIM('abc ') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || RTRIM(_iso88591'abc ') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || SUBSTR('abcdefghi',1,6) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || SUBSTR('abcdefghi',1,6) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || SUBSTR(_iso88591'abcdefghi',1,6) order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || TRANSLATE('abcdef' using UCS2toISO88591) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || TRANSLATE('abcdef' using UCS2toISO88591) order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || TRANSLATE('abcdef' using ISO88591toUCS2) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || TRANSLATE('abcdef' using ISO88591toUCS2) order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || TRANSLATE(_iso88591'abcdef' using UCS2toISO88591) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || TRANSLATE(_iso88591'abcdef' using UCS2toISO88591) order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || TRANSLATE(_iso88591'abcdef' using ISO88591toUCS2) order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || TRANSLATE(_iso88591'abcdef' using ISO88591toUCS2) order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || TRIM(' abc ') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || TRIM(' abc ') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || TRIM(_iso88591' abc ') order by 1,2; |
| select * from cs310t30 where a1 = _iso88591'Z' || UPPER('abcdef') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || UPPER('abcdef') order by 1,2; |
| select * from cs310t30 where a1 = _ucs2'Z' || UPPER(_iso88591'abcdef') order by 1,2; |
| -- |
| -- Tests for SQL Functions - that they can accept any combination |
| -- of ISO and UCS2 character sets |
| -- |
| create table cs310t40 (a1 char(20) character set iso88591, u1 char(20) character set ucs2 ) no partition; |
| insert into cs310t40 values('ZA','ZA'); |
| insert into cs310t40 values('Z','Z'); |
| insert into cs310t40 values('Z'||DATEFORMAT(DATE'2009-04-15',USA),'Z'||DATEFORMAT(DATE'2009-04-15',USA) ); |
| insert into cs310t40 values('Z' || DAYNAME(DATE'2009-04-15'), 'Z' || DAYNAME(DATE'2009-04-15') ); |
| insert into cs310t40 values('Z' || MONTHNAME(DATE'2009-04-15'), 'Z' || MONTHNAME(DATE'2009-04-15') ); |
| insert into cs310t40 values('Z' || CONVERTTOHEX(_ucs2'abc'), 'Z' || CONVERTTOHEX(_ucs2'abc') ); |
| insert into cs310t40 values('Z' || CONVERTTOHEX(_iso88591'abc'), 'Z' || CONVERTTOHEX(_iso88591'abc') ); |
| insert into cs310t40 values('Za','Za'); |
| insert into cs310t40 values('Zd','Zd'); |
| insert into cs310t40 values('abc','abc'); |
| insert into cs310t40 values('def','def'); |
| insert into cs310t40 values('Zabc','Zabc'); |
| insert into cs310t40 values('Zdef','Zdef'); |
| insert into cs310t40 values('Zxyz','Zxyz'); |
| insert into cs310t40 values('EQ','EQ'); |
| insert into cs310t40 values('ZEQ','ZEQ'); |
| insert into cs310t40 values('ZNE','ZNE'); |
| insert into cs310t40 values('NE','NE'); |
| insert into cs310t40 values('abcdef','abcdef'); |
| insert into cs310t40 values('Zabcdef','Zabcdef'); |
| insert into cs310t40 values('ABCDEF','ABCDEF'); |
| insert into cs310t40 values('ZABCDEF','ZABCDEF'); |
| insert into cs310t40 values('abcdefghi','abcdefghi'); |
| insert into cs310t40 values('Zabcdefghi','Zabcdefghi'); |
| select * from cs310t40 order by a1; |
| select * from cs310t40 where a1 = _iso88591'Z' || CHAR(97) order by 1,2; -- SEE NOTE ABOVE |
| select * from cs310t40 where a1 = _iso88591'Z' || TRANSLATE( CHAR(97) USING UCS2toISO88591) order by 1,2; -- SEE NOTE ABOVE |
| select * from cs310t40 where a1 = _iso88591'Z' || 'a' order by 1,2; |
| select * from cs310t40 where a1 = _iso88591'Z' || CAST('a' as VARCHAR(1)) order by 1,2; |
| select * from cs310t40 where a1 = CASE when 1=1 then _iso88591'abc' else _ucs2'def' end order by 1,2; |
| select * from cs310t40 where a1 = CASE when 1=1 then _ucs2'abc' else _iso88591'def' end order by 1,2; |
| select * from cs310t40 where a1 = CASE when 1=1 then _ucs2'abc' else _ucs2'def' end order by 1,2; |
| select * from cs310t40 where u1 = CASE when 1=1 then _iso88591'abc' else _iso88591'def' end order by 1,2; |
| select * from cs310t40 where u1 = CASE when 1=1 then _iso88591'abc' else _ucs2'def' end order by 1,2; |
| select * from cs310t40 where u1 = CASE when 1=1 then _ucs2'abc' else _iso88591'def' end order by 1,2; |
| -- |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_iso88591'abc') order by 1,2; |
| -- |
| select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_iso88591'def') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_iso88591'def') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_iso88591'def') order by 1,2; |
| -- |
| select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_iso88591'def',_ucs2'ghi') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_ucs2'def',_iso88591'ghi') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_iso88591'def',_iso88591'ghi') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_ucs2'def',_ucs2'ghi') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_iso88591'def',_ucs2'ghi') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_iso88591'ghi') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_ucs2'ghi') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_iso88591'def',_iso88591'ghi') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_iso88591'def',_ucs2'ghi') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_ucs2'def',_iso88591'ghi') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_iso88591'def',_iso88591'ghi') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_ucs2'def',_ucs2'ghi') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_iso88591'def',_ucs2'ghi') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_ucs2'def',_iso88591'ghi') order by 1,2; |
| -- |
| select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_iso88591'def',_iso88591'ghi',_ucs2'jkl') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_iso88591'def',_ucs2'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_ucs2'def',_iso88591'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_iso88591'def',_iso88591'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_iso88591'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_iso88591'def',_ucs2'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_iso88591'def',_iso88591'ghi',_ucs2'jkl') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_ucs2'def',_ucs2'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_ucs2'def',_iso88591'ghi',_ucs2'jkl') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_ucs2'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_iso88591'ghi',_ucs2'jkl') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_iso88591'def',_ucs2'ghi',_ucs2'jkl') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_iso88591'abc',_ucs2'def',_ucs2'ghi',_ucs2'jkl') order by 1,2; |
| select * from cs310t40 where a1 = COALESCE(_ucs2'abc',_ucs2'def',_ucs2'ghi',_ucs2'jkl') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_iso88591'def',_iso88591'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_iso88591'def',_iso88591'ghi',_ucs2'jkl') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_iso88591'def',_ucs2'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_ucs2'def',_iso88591'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_iso88591'def',_iso88591'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_ucs2'def',_iso88591'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_iso88591'def',_ucs2'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_iso88591'def',_iso88591'ghi',_ucs2'jkl') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_ucs2'def',_ucs2'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_ucs2'def',_iso88591'ghi',_ucs2'jkl') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_ucs2'def',_ucs2'ghi',_iso88591'jkl') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_ucs2'def',_iso88591'ghi',_ucs2'jkl') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_ucs2'abc',_iso88591'def',_ucs2'ghi',_ucs2'jkl') order by 1,2; |
| select * from cs310t40 where u1 = COALESCE(_iso88591'abc',_ucs2'def',_ucs2'ghi',_ucs2'jkl') order by 1,2; |
| -- |
| select * from cs310t40 where a1 = CONCAT(_iso88591'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where a1 = CONCAT(_ucs2'abc',_iso88591'def') order by 1,2; |
| select * from cs310t40 where a1 = CONCAT(_ucs2'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = CONCAT(_iso88591'abc',_iso88591'def') order by 1,2; |
| select * from cs310t40 where u1 = CONCAT(_iso88591'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = CONCAT(_ucs2'abc',_iso88591'def') order by 1,2; |
| -- |
| select * from cs310t40 where a1 = _iso88591'abc' || _ucs2'def' order by 1,2; |
| select * from cs310t40 where a1 = _ucs2'abc' || _iso88591'def' order by 1,2; |
| select * from cs310t40 where a1 = _ucs2'abc' || _ucs2'def' order by 1,2; |
| select * from cs310t40 where u1 = _iso88591'abc' || _iso88591'def' order by 1,2; |
| select * from cs310t40 where u1 = _iso88591'abc' || _ucs2'def' order by 1,2; |
| select * from cs310t40 where u1 = _ucs2'abc' || _iso88591'def' order by 1,2; |
| -- |
| select * from cs310t40 where a1 = DECODE(_iso88591'abc',_iso88591'def',_iso88591'EQ',_ucs2'NE') order by 1,2; |
| select * from cs310t40 where a1 = DECODE(_iso88591'abc',_iso88591'def',_ucs2'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where a1 = DECODE(_iso88591'abc',_ucs2'def',_iso88591'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where a1 = DECODE(_ucs2'abc',_iso88591'def',_iso88591'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where a1 = DECODE(_ucs2'abc',_ucs2'def',_iso88591'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where a1 = DECODE(_ucs2'abc',_iso88591'def',_ucs2'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where a1 = DECODE(_ucs2'abc',_iso88591'def',_iso88591'EQ',_ucs2'NE') order by 1,2; |
| select * from cs310t40 where a1 = DECODE(_iso88591'abc',_ucs2'def',_ucs2'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where a1 = DECODE(_iso88591'abc',_ucs2'def',_iso88591'EQ',_ucs2'NE') order by 1,2; |
| select * from cs310t40 where a1 = DECODE(_iso88591'abc',_iso88591'def',_ucs2'EQ',_ucs2'NE') order by 1,2; |
| select * from cs310t40 where a1 = DECODE(_ucs2'abc',_ucs2'def',_ucs2'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where a1 = DECODE(_ucs2'abc',_ucs2'def',_iso88591'EQ',_ucs2'NE') order by 1,2; |
| select * from cs310t40 where a1 = DECODE(_ucs2'abc',_iso88591'def',_ucs2'EQ',_ucs2'NE') order by 1,2; |
| select * from cs310t40 where a1 = DECODE(_iso88591'abc',_ucs2'def',_ucs2'EQ',_ucs2'NE') order by 1,2; |
| select * from cs310t40 where a1 = DECODE(_ucs2'abc',_ucs2'def',_ucs2'EQ',_ucs2'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_iso88591'abc',_iso88591'def',_iso88591'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_iso88591'abc',_iso88591'def',_iso88591'EQ',_ucs2'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_iso88591'abc',_iso88591'def',_ucs2'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_iso88591'abc',_ucs2'def',_iso88591'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_ucs2'abc',_iso88591'def',_iso88591'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_ucs2'abc',_ucs2'def',_iso88591'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_ucs2'abc',_iso88591'def',_ucs2'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_ucs2'abc',_iso88591'def',_iso88591'EQ',_ucs2'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_iso88591'abc',_ucs2'def',_ucs2'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_iso88591'abc',_ucs2'def',_iso88591'EQ',_ucs2'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_iso88591'abc',_iso88591'def',_ucs2'EQ',_ucs2'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_ucs2'abc',_ucs2'def',_ucs2'EQ',_iso88591'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_ucs2'abc',_ucs2'def',_iso88591'EQ',_ucs2'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_ucs2'abc',_iso88591'def',_ucs2'EQ',_ucs2'NE') order by 1,2; |
| select * from cs310t40 where u1 = DECODE(_iso88591'abc',_ucs2'def',_ucs2'EQ',_ucs2'NE') order by 1,2; |
| -- |
| select * from cs310t40 where a1 = INSERT(_iso88591'abcghi',4,0,_ucs2'def') order by 1,2; |
| select * from cs310t40 where a1 = INSERT(_ucs2'abcghi',4,0,_iso88591'def') order by 1,2; |
| select * from cs310t40 where a1 = INSERT(_ucs2'abcghi',4,0,_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = INSERT(_iso88591'abcghi',4,0,_iso88591'def') order by 1,2; |
| select * from cs310t40 where u1 = INSERT(_iso88591'abcghi',4,0,_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = INSERT(_ucs2'abcghi',4,0,_iso88591'def') order by 1,2; |
| -- |
| select * from cs310t40 where a1 = ISNULL(_iso88591'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where a1 = ISNULL(_ucs2'abc',_iso88591'def') order by 1,2; |
| select * from cs310t40 where a1 = ISNULL(_ucs2'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = ISNULL(_iso88591'abc',_iso88591'def') order by 1,2; |
| select * from cs310t40 where u1 = ISNULL(_iso88591'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = ISNULL(_ucs2'abc',_iso88591'def') order by 1,2; |
| -- |
| select * from cs310t40 where a1 = LPAD(_iso88591'def',6,_ucs2'abc') order by 1,2; |
| select * from cs310t40 where a1 = LPAD(_ucs2'def',6,_iso88591'abc') order by 1,2; |
| select * from cs310t40 where a1 = LPAD(_ucs2'def',6,_ucs2'abc') order by 1,2; |
| select * from cs310t40 where u1 = LPAD(_iso88591'def',6,_iso88591'abc') order by 1,2; |
| select * from cs310t40 where u1 = LPAD(_iso88591'def',6,_ucs2'abc') order by 1,2; |
| select * from cs310t40 where u1 = LPAD(_ucs2'def',6,_iso88591'abc') order by 1,2; |
| -- |
| select * from cs310t40 where a1 = NULLIF(_iso88591'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where a1 = NULLIF(_ucs2'abc',_iso88591'def') order by 1,2; |
| select * from cs310t40 where a1 = NULLIF(_ucs2'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = NULLIF(_iso88591'abc',_iso88591'def') order by 1,2; |
| select * from cs310t40 where u1 = NULLIF(_iso88591'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = NULLIF(_ucs2'abc',_iso88591'def') order by 1,2; |
| -- |
| select * from cs310t40 where a1 = NVL(_iso88591'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where a1 = NVL(_ucs2'abc',_iso88591'def') order by 1,2; |
| select * from cs310t40 where a1 = NVL(_ucs2'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = NVL(_iso88591'abc',_iso88591'def') order by 1,2; |
| select * from cs310t40 where u1 = NVL(_iso88591'abc',_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = NVL(_ucs2'abc',_iso88591'def') order by 1,2; |
| -- |
| select * from cs310t40 where a1 = REPLACE(_iso88591'abcxyzghi',_iso88591'xyz',_ucs2'def') order by 1,2; |
| select * from cs310t40 where a1 = REPLACE(_iso88591'abcxyzghi',_ucs2'xyz',_iso88591'def') order by 1,2; |
| select * from cs310t40 where a1 = REPLACE(_ucs2'abcxyzghi',_iso88591'xyz',_iso88591'def') order by 1,2; |
| select * from cs310t40 where a1 = REPLACE(_ucs2'abcxyzghi',_ucs2'xyz',_iso88591'def') order by 1,2; |
| select * from cs310t40 where a1 = REPLACE(_ucs2'abcxyzghi',_iso88591'xyz',_ucs2'def') order by 1,2; |
| select * from cs310t40 where a1 = REPLACE(_iso88591'abcxyzghi',_ucs2'xyz',_ucs2'def') order by 1,2; |
| select * from cs310t40 where a1 = REPLACE(_ucs2'abcxyzghi',_ucs2'xyz',_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = REPLACE(_iso88591'abcxyzghi',_iso88591'xyz',_iso88591'def') order by 1,2; |
| select * from cs310t40 where u1 = REPLACE(_iso88591'abcxyzghi',_iso88591'xyz',_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = REPLACE(_iso88591'abcxyzghi',_ucs2'xyz',_iso88591'def') order by 1,2; |
| select * from cs310t40 where u1 = REPLACE(_ucs2'abcxyzghi',_iso88591'xyz',_iso88591'def') order by 1,2; |
| select * from cs310t40 where u1 = REPLACE(_ucs2'abcxyzghi',_ucs2'xyz',_iso88591'def') order by 1,2; |
| select * from cs310t40 where u1 = REPLACE(_ucs2'abcxyzghi',_iso88591'xyz',_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = REPLACE(_iso88591'abcxyzghi',_ucs2'xyz',_ucs2'def') order by 1,2; |
| -- |
| select * from cs310t40 where a1 = RPAD(_iso88591'abc',6,_ucs2'def') order by 1,2; |
| select * from cs310t40 where a1 = RPAD(_ucs2'abc',6,_iso88591'def') order by 1,2; |
| select * from cs310t40 where a1 = RPAD(_ucs2'abc',6,_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = RPAD(_iso88591'abc',6,_iso88591'def') order by 1,2; |
| select * from cs310t40 where u1 = RPAD(_iso88591'abc',6,_ucs2'def') order by 1,2; |
| select * from cs310t40 where u1 = RPAD(_ucs2'abc',6,_iso88591'def') order by 1,2; |
| -- |
| -- Tests for TRANSLATE |
| -- |
| create table cs310t50 (a1 char(8) character set iso88591, u1 char(8) character set ucs2 ) no partition; |
| insert into cs310t50 values('abc','abc'); |
| insert into cs310t50 values('abc','def'); |
| select * from cs310t50 order by 1,2; |
| -- |
| --Following should work without errors |
| select * from cs310t50 where a1 = u1 order by 1,2; |
| select * from cs310t50 where u1 = a1 order by 1; |
| select * from cs310t50 where a1 = TRANSLATE(u1 using UCS2toISO88591) order by 1,2; |
| select * from cs310t50 where u1 = TRANSLATE(a1 using ISO88591toUCS2) order by 1,2; |
| select * from cs310t50 where a1 = TRANSLATE(a1 using ISO88591toUCS2) order by 1,2; |
| select * from cs310t50 where u1 = TRANSLATE(u1 using UCS2toISO88591) order by 1,2; |
| select * from cs310t50 where a1 = TRANSLATE('abc' using UCS2toISO88591) order by 1,2; |
| select * from cs310t50 where u1 = TRANSLATE(_iso88591'abc' using ISO88591toUCS2) order by 1,2; |
| select * from cs310t50 where a1 = TRANSLATE(TRANSLATE(u1 using UCS2toISO88591) using ISO88591toUCS2) order by 1,2; |
| select * from cs310t50 where u1 = TRANSLATE(TRANSLATE(a1 using ISO88591toUCS2) using UCS2toISO88591) order by 1,2; |
| select TRANSLATE(u1 using UCS2toISO88591) from cs310t50 order by 1; |
| select TRANSLATE(a1 using ISO88591toUCS2) from cs310t50 order by 1; |
| select TRANSLATE(TRANSLATE(u1 using UCS2toISO88591) using ISO88591toUCS2) from cs310t50 order by 1; |
| select TRANSLATE(TRANSLATE(a1 using ISO88591toUCS2) using UCS2toISO88591) from cs310t50 order by 1; |
| -- |
| --The following should work without errors ... because of Implicit Casting of literals |
| select * from cs310t50 where a1 = TRANSLATE('abc' using ISO88591toUCS2) order by 1,2; |
| select TRANSLATE('abc' using ISO88591toUCS2) from cs310t50 order by 1; |
| -- |
| --The following should work without errors ... because of Implicit Casting of literals |
| --HOWEVER, it is possible we should make these give errors. |
| select * from cs310t50 where u1 = TRANSLATE(_iso88591'abc' using UCS2toISO88591) order by 1,2; |
| select * from cs310t50 where u1 = TRANSLATE(_ucs2'abc' using ISO88591toUCS2) order by 1,2; |
| select TRANSLATE(_iso88591'abc' using UCS2toISO88591) from cs310t50 order by 1; |
| select TRANSLATE(_ucs2'abc' using ISO88591toUCS2) from cs310t50 order by 1; |
| -- |
| --The following should work without errors ... but only because the return value from the |
| --inner Function Invocation gets the Implicit Casting/Translation feature applied to it. |
| select * from cs310t50 where a1 = TRANSLATE(TRANSLATE(u1 using UCS2toISO88591) using UCS2toISO88591) order by 1,2; |
| select * from cs310t50 where u1 = TRANSLATE(TRANSLATE(a1 using ISO88591toUCS2) using ISO88591toUCS2) order by 1,2; |
| select * from cs310t50 where a1 = TRANSLATE(TRIM(a1) using UCS2toISO88591) order by 1,2; |
| select * from cs310t50 where u1 = TRANSLATE(TRIM(u1) using ISO88591toUCS2) order by 1,2; |
| select * from cs310t50 where a1 = TRANSLATE(TRIM(_iso88591'abc') using UCS2toISO88591) order by 1,2; |
| select * from cs310t50 where u1 = TRANSLATE(TRIM(_ucs2'abc') using ISO88591toUCS2) order by 1,2; |
| select * from cs310t50 where u1 = TRANSLATE(TRIM('abc') using ISO88591toUCS2) order by 1,2; |
| -- |
| -- |
| --Following should get errors |
| select * from cs310t50 where a1 = TRANSLATE(u1 using ISO88591toUCS2) order by 1,2; |
| select * from cs310t50 where u1 = TRANSLATE(a1 using UCS2toISO88591) order by 1,2; |
| select TRANSLATE(u1 using ISO88591toUCS2) from cs310t50 order by 1; |
| select TRANSLATE(a1 using UCS2toISO88591) from cs310t50 order by 1; |
| -- |
| -- Tests for CAST |
| -- |
| create table cs310t60 (a1 char(8) character set iso88591, u1 char(8) character set ucs2 ) no partition; |
| insert into cs310t60 values('abc','abc'); |
| insert into cs310t60 values('abc','def'); |
| select * from cs310t60 order by 1,2; |
| |
| select * from cs310t60 where a1 = CAST('abc' as varchar(4)) order by 1,2; |
| select * from cs310t60 where u1 = CAST('abc' as varchar(4)) order by 1,2; |
| select * from cs310t60 where a1 = CAST(_iso88591'abc' as varchar(4)) order by 1,2; |
| select * from cs310t60 where u1 = CAST(_iso88591'abc' as varchar(4)) order by 1,2; |
| -- |
| select * from cs310t60 where a1 = CAST(a1 as varchar(4)) order by 1,2; |
| select * from cs310t60 where u1 = CAST(u1 as varchar(4)) order by 1,2; |
| select * from cs310t60 where a1 = CAST(u1 as varchar(4)) order by 1,2; |
| select * from cs310t60 where u1 = CAST(a1 as varchar(4)) order by 1,2; |
| -- |
| select * from cs310t60 where a1 = CAST('abc' as varchar(4) character set ucs2) order by 1,2; |
| select * from cs310t60 where a1 = CAST(_iso88591'abc' as varchar(4) character set ucs2) order by 1,2; |
| select * from cs310t60 where u1 = CAST(_iso88591'abc' as varchar(4) character set iso88591) order by 1,2; |
| -- |
| select * from cs310t60 where a1 = CAST(a1 as varchar(4) character set ucs2) order by 1,2; |
| select * from cs310t60 where a1 = CAST(u1 as varchar(4) character set ucs2) order by 1,2; |
| select * from cs310t60 where u1 = CAST(a1 as varchar(4) character set iso88591) order by 1,2; |
| -- |
| -- Tests for UNIONs |
| -- |
| create table cs310t70 (a1 char(8) character set iso88591, u1 char(8) character set ucs2 ) no partition; |
| insert into cs310t70 values('abc','abc'); |
| insert into cs310t70 values('abc','def'); |
| select * from cs310t70 order by 1,2; |
| -- |
| create table cs310t71 (a1 char(8) character set ucs2, u1 char(8) character set iso88591 ) no partition; |
| insert into cs310t71 values('abc','abc'); |
| insert into cs310t71 values('abc','def'); |
| select * from cs310t71 order by 1,2; |
| -- |
| select * from cs310t70 union (select * from cs310t71) order by 1,2; |
| select * from cs310t70 union all (select * from cs310t71) order by 1,2; |
| -- |
| select * from cs310t70, cs310t71 order by 1,2,3,4; |
| select * from cs310t70, cs310t71 where cs310t70.a1 = cs310t71.u1 order by 1,2,3,4; |
| select * from cs310t70, cs310t71 where cs310t70.u1 = cs310t71.a1 order by 1,2,3,4; |
| -- |
| select * from cs310t70, cs310t71 where cs310t70.a1 = cs310t71.a1 order by 1,2,3,4; |
| select * from cs310t70, cs310t71 where cs310t70.u1 = cs310t71.u1 order by 1,2,3,4; |
| -- |
| select * from cs310t70 union (select * from cs310t70 union (select * from cs310t71)) order by 1,2; |
| select * from cs310t70 union (select * from cs310t71 union (select * from cs310t70)) order by 1,2; |
| -- |
| select * from cs310t70 natural join cs310t71 order by 1,2; |
| select * from cs310t70 cross join cs310t71 order by 1,2,3,4; |
| select * from cs310t71 cross join cs310t70 order by 1,2,3,4; |
| -- |
| select * from cs310t70 inner join cs310t71 on cs310t70.a1 = cs310t71.u1 order by 1,2,3,4; |
| select * from cs310t70 inner join cs310t71 on cs310t70.u1 = cs310t71.u1 order by 1,2,3,4; |
| select * from cs310t70 inner join cs310t71 on cs310t70.u1 = cs310t71.a1 order by 1,2,3,4; |
| select * from cs310t70 inner join cs310t71 on cs310t70.a1 = cs310t71.a1 order by 1,2,3,4; |
| -- |
| select * from cs310t70 left outer join cs310t71 on cs310t70.u1 = cs310t71.a1 order by 1,2,3,4; |
| select * from cs310t70 left outer join cs310t71 on cs310t70.a1 = cs310t71.u1 order by 1,2,3,4; |
| select * from cs310t70 left outer join cs310t71 on cs310t70.u1 = cs310t71.u1 order by 1,2,3,4; |
| select * from cs310t70 left outer join cs310t71 on cs310t70.a1 = cs310t71.a1 order by 1,2,3,4; |
| -- |
| select * from cs310t70 right outer join cs310t71 on cs310t70.u1 = cs310t71.a1 order by 1,2,3,4; |
| select * from cs310t70 right outer join cs310t71 on cs310t70.a1 = cs310t71.u1 order by 1,2,3,4; |
| select * from cs310t70 right outer join cs310t71 on cs310t70.u1 = cs310t71.u1 order by 1,2,3,4; |
| select * from cs310t70 right outer join cs310t71 on cs310t70.a1 = cs310t71.a1 order by 1,2,3,4; |
| -- |
| select * from cs310t70, cs310t71 where cs310t70.u1 = (select cs310t71.u1 from cs310t71 where cs310t71.a1 = 'def') order by 1,2,3,4; |
| -- |
| select * from cs310t70, cs310t71 where cs310t70.u1 > any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'def') order by 1,2,3,4; |
| select * from cs310t70, cs310t71 where cs310t70.u1 < any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'def') order by 1,2,3,4; |
| select * from cs310t70, cs310t71 where cs310t70.u1 <= any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'def') order by 1,2,3,4; |
| select * from cs310t70, cs310t71 where cs310t70.u1 >= any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'def') order by 1,2,3,4; |
| select * from cs310t70, cs310t71 where cs310t70.u1 > any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'abc') order by 1,2,3,4; |
| select * from cs310t70, cs310t71 where cs310t70.u1 < any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'abc') order by 1,2,3,4; |
| select * from cs310t70, cs310t71 where cs310t70.u1 <= any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'abc') order by 1,2,3,4; |
| select * from cs310t70, cs310t71 where cs310t70.u1 >= any (select cs310t71.a1 from cs310t71 where cs310t71.u1 = 'abc') order by 1,2,3,4; |
| -- |
| -- Tests for subqueries |
| -- |
| create table cs310t80 (a1 char(8) character set iso88591) no partition; |
| insert into cs310t80 values('a1a1'); |
| insert into cs310t80 values('u1u1'); |
| create table cs310t81 (u1 char(8) character set ucs2) no partition; |
| insert into cs310t81 values('a1a1'); |
| insert into cs310t81 values('u1u1'); |
| |
| select * from cs310t80, cs310t81 order by 1,2; |
| select * from cs310t80, cs310t81 where a1 = case when a1 > 'a1a' then u1 else 'a100' end order by 1,2; |
| select * from cs310t80, cs310t81 where a1 = case when (select min(a1) from cs310t80) > 'a1a' then u1 else 'a100' end order by 1,2; |
| select * from cs310t80, cs310t81 where u1 = case when (select min(a1) from cs310t80) > 'a1a' then u1 else 'a100' end order by 1,2; |
| -- |
| select * from cs310t80, cs310t81 where (select min(a1) from cs310t80) = case when (select min(a1) from cs310t80) > 'a1a' then u1 else 'a100' end order by 1,2; |
| select * from cs310t80, cs310t81 where (select min(u1) from cs310t81) = case when (select min(a1) from cs310t80) > 'a1a' then u1 else 'a100' end order by 1,2; |
| -- |
| select * from cs310t80, cs310t81 where a1 = case when a1 > 'a1a' then (select min(u1) from cs310t81) else 'a100' end order by 1,2; |
| select * from cs310t80, cs310t81 where u1 = case when a1 > 'a1a' then (select min(u1) from cs310t81) else 'a100' end order by 1,2; |
| -- |
| select * from cs310t80, cs310t81 where a1 = case when (select min(a1) from cs310t80) > 'a1a' then (select min(u1) from cs310t81) else 'a100' end order by 1,2; |
| select * from cs310t80, cs310t81 where u1 = case when (select min(a1) from cs310t80) > 'a1a' then (select min(u1) from cs310t81) else 'a100' end order by 1,2; |
| -- |
| select * from cs310t80, cs310t81 where a1 = (select min(case when a1 > 'a1a' then u1 else 'a100' end) from cs310t80, cs310t81) order by 1,2; |
| select * from cs310t80, cs310t81 where u1 = (select min(case when a1 > 'a1a' then u1 else 'a100' end) from cs310t80, cs310t81) order by 1,2; |
| -- |
| select * from cs310t80, cs310t81 where (select min(a1) from cs310t80) = (select min(case when a1 > 'a1a' then u1 else 'a100' end) from cs310t80, cs310t81) order by 1,2; |
| select * from cs310t80, cs310t81 where (select min(u1) from cs310t81) = (select min(case when a1 > 'a1a' then u1 else 'a100' end) from cs310t80, cs310t81) order by 1,2; |
| -- |
| -- Tests for TRIGGERS |
| -- |
| create table cs310t90 ( |
| UCS2_col1 varchar(36) character set UCS2 no default not null, |
| UCS2_col2 varchar(36) character set UCS2 no default not null, |
| ISO_col1 varchar(36) character set ISO88591, |
| ISO_col2 varchar(36) character set ISO88591 |
| )no partitions; |
| -- |
| create TRIGGER TRG1_cs310t90 before UPDATE on cs310t90 REFERENCING NEW as TRG FOR EACH ROW |
| set TRG.UCS2_col2 = _ISO88591'abc' ; |
| -- |
| insert into cs310t90 values('Test1','Test1','Test1','Test1'), ('test111','test1111','test111','test1111'); |
| UPDATE cs310t90 set ISO_col2 = _ISO88591'Iso1' where UCS2_col1 = _iso88591'Test1'; |
| select * from cs310t90 order by 1,2,3,4; |
| -- Do same thing again. |
| UPDATE cs310t90 set ISO_col2 = _ISO88591'Iso1' where UCS2_col1 = _iso88591'Test1'; |
| select * from cs310t90 order by 1,2,3,4; |
| -- |
| insert into cs310t90 values('Test2','Test2','Test2','Test2'), ('test222','test2222','test222','test2222'); |
| UPDATE cs310t90 set ISO_col2 = _ucs2'UCS2' where UCS2_col1 = _iso88591'Test2'; |
| select * from cs310t90 order by 1,2,3,4; |
| -- Do same thing again. |
| UPDATE cs310t90 set ISO_col2 = _ucs2'UCS2' where UCS2_col1 = _iso88591'Test2'; |
| select * from cs310t90 order by 1,2,3,4; |
| -- |
| insert into cs310t90 values('Test3','Test3','Test3','Test3'), ('test333','test3333','test333','test3333'); |
| UPDATE cs310t90 set UCS2_col1 = _ISO88591'Iso3' where ISO_col2 = _ucs2'Test3'; |
| select * from cs310t90 order by 1,2,3,4; |
| -- Do same thing again. |
| UPDATE cs310t90 set UCS2_col1 = _ISO88591'Iso3' where ISO_col2 = _ucs2'Test3'; |
| select * from cs310t90 order by 1,2,3,4; |
| -- |
| DROP TRIGGER TRG1_cs310t90; |
| create TRIGGER TRG1_cs310t90 before UPDATE on cs310t90 REFERENCING NEW as TRG FOR EACH ROW |
| set TRG.ISO_col2 = _ucs2'abc' ; |
| -- |
| insert into cs310t90 values('Test4','Test4','Test4','Test4'), ('test444','test4444','test444','test4444'); |
| UPDATE cs310t90 set ISO_col1 = _ISO88591'Iso4' where UCS2_col1 = _iso88591'Test4'; |
| select * from cs310t90 order by 1,2,3,4; |
| -- Do same thing again. |
| UPDATE cs310t90 set ISO_col1 = _ISO88591'Iso4' where UCS2_col1 = _iso88591'Test4'; |
| select * from cs310t90 order by 1,2,3,4; |
| -- |
| insert into cs310t90 values('Test5','Test5','Test5','Test5'), ('test555','test5555','test555','test5555'); |
| UPDATE cs310t90 set ISO_col1 = _ucs2'UCS5' where UCS2_col1 = _iso88591'Test5'; |
| select * from cs310t90 order by 1,2,3,4; |
| -- Do same thing again. |
| UPDATE cs310t90 set ISO_col1 = _ucs2'UCS5' where UCS2_col1 = _iso88591'Test5'; |
| select * from cs310t90 order by 1,2,3,4; |
| -- |
| insert into cs310t90 values('Test6','Test6','Test6','Test6'), ('test666','test6666','test666','test6666'); |
| UPDATE cs310t90 set UCS2_col1 = _ISO88591'Iso6' where UCS2_col2 = _ucs2'Test6'; |
| select * from cs310t90 order by 1,2,3,4; |
| -- Do same thing again. |
| UPDATE cs310t90 set UCS2_col1 = _ISO88591'Iso6' where UCS2_col2 = _ucs2'Test6'; |
| select * from cs310t90 order by 1,2,3,4; |
| -- |
| -- Tests for TRIGGERS on tables with primary key. |
| -- NOTE: Tables with a primary key column can take different paths through optimizer. |
| -- |
| create table cs310t91 ( |
| PRKY int no default not null, |
| UCS2_col1 varchar(36) character set UCS2 no default not null, |
| UCS2_col2 varchar(36) character set UCS2 no default not null, |
| ISO_col1 varchar(36) character set ISO88591, |
| ISO_col2 varchar(36) character set ISO88591, |
| PRIMARY KEY (PRKY) ) STORE BY primary key; |
| -- |
| create TRIGGER TRG1_cs310t91 before UPDATE on cs310t91 REFERENCING NEW as TRG FOR EACH ROW |
| set TRG.UCS2_col2 = _ISO88591'abc' ; |
| -- |
| insert into cs310t91 values(1,'Test1','Test1','Test1','Test1'), (2,'test111','test1111','test111','test1111'); |
| UPDATE cs310t91 set ISO_col2 = _ISO88591'Iso1' where UCS2_col1 = _iso88591'Test1'; |
| select * from cs310t91 order by 1,2,3,4; |
| -- Do same thing again. |
| UPDATE cs310t91 set ISO_col2 = _ISO88591'Iso1' where UCS2_col1 = _iso88591'Test1'; |
| select * from cs310t91 order by 1,2,3,4; |
| -- |
| insert into cs310t91 values(3,'Test2','Test2','Test2','Test2'), (4,'test222','test2222','test222','test2222'); |
| UPDATE cs310t91 set ISO_col2 = _ucs2'UCS2' where UCS2_col1 = _iso88591'Test2'; |
| select * from cs310t91 order by 1,2,3,4; |
| -- Do same thing again. |
| UPDATE cs310t91 set ISO_col2 = _ucs2'UCS2' where UCS2_col1 = _iso88591'Test2'; |
| select * from cs310t91 order by 1,2,3,4; |
| -- |
| insert into cs310t91 values(5,'Test3','Test3','Test3','Test3'), (6,'test333','test3333','test333','test3333'); |
| UPDATE cs310t91 set UCS2_col1 = _ISO88591'Iso3' where ISO_col2 = _ucs2'Test3'; |
| select * from cs310t91 order by 1,2,3,4; |
| -- Do same thing again. |
| UPDATE cs310t91 set UCS2_col1 = _ISO88591'Iso3' where ISO_col2 = _ucs2'Test3'; |
| select * from cs310t91 order by 1,2,3,4; |
| -- |
| DROP TRIGGER TRG1_cs310t91; |
| create TRIGGER TRG1_cs310t91 before UPDATE on cs310t91 REFERENCING NEW as TRG FOR EACH ROW |
| set TRG.ISO_col2 = _ucs2'abc' ; |
| -- |
| insert into cs310t91 values(7,'Test4','Test4','Test4','Test4'), (8,'test444','test4444','test444','test4444'); |
| UPDATE cs310t91 set ISO_col1 = _ISO88591'Iso4' where UCS2_col1 = _iso88591'Test4'; |
| select * from cs310t91 order by 1,2,3,4; |
| -- Do same thing again. |
| UPDATE cs310t91 set ISO_col1 = _ISO88591'Iso4' where UCS2_col1 = _iso88591'Test4'; |
| select * from cs310t91 order by 1,2,3,4; |
| -- |
| insert into cs310t91 values(9,'Test5','Test5','Test5','Test5'), (10,'test555','test5555','test555','test5555'); |
| UPDATE cs310t91 set ISO_col1 = _ucs2'UCS5' where UCS2_col1 = _iso88591'Test5'; |
| select * from cs310t91 order by 1,2,3,4; |
| -- Do same thing again. |
| UPDATE cs310t91 set ISO_col1 = _ucs2'UCS5' where UCS2_col1 = _iso88591'Test5'; |
| select * from cs310t91 order by 1,2,3,4; |
| -- |
| insert into cs310t91 values(11,'Test6','Test6','Test6','Test6'), (12,'test666','test6666','test666','test6666'); |
| UPDATE cs310t91 set UCS2_col1 = _ISO88591'Iso6' where UCS2_col2 = _ucs2'Test6'; |
| select * from cs310t91 order by 1,2,3,4; |
| -- Do same thing again. |
| UPDATE cs310t91 set UCS2_col1 = _ISO88591'Iso6' where UCS2_col2 = _ucs2'Test6'; |
| select * from cs310t91 order by 1,2,3,4; |
| -- |
| -- Tests for multi-row VALUES |
| -- |
| create table cs310t92 ( |
| bname CHAR(66) NO DEFAULT NOT NULL |
| )no partitions; |
| insert into cs310t92 VALUES(_iso88591'abcdefghi'), ('xyz'); |
| insert into cs310t92 VALUES(converttohex('abcdefghi')), (converttohex('xyz')); |
| -- |
| -- Tests for RPAD and LPAD |
| -- |
| insert into cs310t92 select RPAD(_ISO88591 x'c3b6c3bac3bb' || '789ABCDEFG',10) from (values(1)) x(a); |
| insert into cs310t92 select LPAD(_UCS2'456' || '789',10) from (values(1)) x(a); |
| |
| select * from cs310t92 order by 1; |
| select left(bname,40) from cs310t92 order by 1; |
| --- |
| create table cs310t93(a int, c char(10) character set utf8) no partition; |
| insert into cs310t93 values (1,'a') ,(2,null); |
| -- try mixing character sets |
| insert into cs310t93 values (3, _ucs2 x'4EBA'), (4,'b'); |
| insert into cs310t93 values (5, 'c'), (6, _ucs2 x'4EBA'); |
| insert into cs310t93 values (7, 'd'), (8, _utf8 x'E4BABA'); |
| insert into cs310t93 values (9, _utf8 x'E4BABA'), (10, 'e'), (11, _ucs2 x'4EBB4EBC4EBD4EBE') ; |
| insert into cs310t93 values (12, _ucs2 x'4EBB4EBC4EBD4EBE'), (13,'f'), (14,_utf8 x'E4BABAE4BABB'); |
| insert into cs310t93 values (15, _ucs2 x'4EBB4EBC4EBD4EBE'), (16,_utf8 x'E4BABAE4BABB'),(17,'g'); |
| --- gbk char followed by null |
| insert into cs310t93 values (18, _ucs2 x'4EBA'), (19,null); |
| insert into cs310t93 values (20, _utf8 x'E4BABA'),(21, 'h'),(22,_ucs2 x'4EBB4EBC4EBD4EBE'),(23,null) ; |
| --- null followed by gbk char |
| insert into cs310t93 values (24, null), (25, _ucs2 x'4EBA'); |
| insert into cs310t93 values (26, null), (27, _utf8 x'E4BABA'),(28, 'i'),(29,_ucs2 x'4EBB4EBC4EBD4EBE') ; |
| select a, converttohex(c) from cs310t93 order by a; |
| |
| ?section clnup |
| |
| drop schema cs310s cascade; |
| |