core/sql/regress/charsets/TEST004

-- Test: TEST004
-- Functionality: This tests char, code_value/ascii, upper/lower, l/rtrim, 
--                space, replace string functions.
-- Expected files:   EXPECTED004
-- Tables created:   tab_iso91 tab_int tab_ucs2
-- 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 @@@

?section ddl

obey TEST004(clnup);

control query default query_cache '0';

log LOG004 clear;

create table tab_iso91 (c char(10) character set ISO88591);
create table tab_ucs2 (d char(10) character set UCS2);
create table tab_int( i int);

?section dml

--positive

insert into tab_int values(ASCII('asdf'));
insert into tab_int values(ASCII(x'00'));
insert into tab_int values(code_value('zxcv'));
insert into tab_int values(code_value('zxcv'));
insert into tab_int values(code_value(_ucs2 x'1234 5678'));
select * from tab_int;

insert into tab_iso91 values(CHAR(43));
insert into tab_iso91 values(CHAR(65, ISO88591));
select * from tab_iso91;

insert into tab_ucs2 values(CHAR(1041, UCS2)); -- cyrillic capital letter BE
insert into tab_ucs2 values(CHAR(9617, UCS2)); -- light shade symbol
select code_value(d) from tab_ucs2;

select UPPER(_UCS2'ALL IN CAPS') from (values(1))x;
select UPPER(_UCS2'all in small case') from (values(1))x;
select lower(_UCS2'ALL IN CAPS') from (values(1))x;
select lower(_UCS2'all in small case') from (values(1))x;

-- title cases.
--0x01F1 "DZ" capital dz is U+01F2 "Dz" capital d with small z. 


-- should get 0x01f1 = 497
select code_value(UPPER(_UCS2 x'01f2')) from (values(1))x;

-- should get 0x01f3 = 499
select code_value(lower(_UCS2 x'01f2')) from (values(1))x;

--00e0 and 00f6
select converttohex(upper(_ucs2 x'00e0 00f6')) from (values(1))x;
select converttohex(lower(_ucs2 x'00c0 00d6')) from (values(1))x;
select converttohex(code_value(_ucs2 x'00c0 00d6')) from (values(1))x;

--0130
select converttohex(lower(_UCS2 x'0130')) from (values(1))x;
select converttohex(upper(_UCS2 x'0130')) from (values(1))x;

--0131
select converttohex(lower(_UCS2 x'0131')) from (values(1))x;
select converttohex(upper(_UCS2 x'0131')) from (values(1))x;

-- 1e9a -> 0041 02be
-- 00df -> 0053 0053
select char_length(upper(_UCS2 X'1e9a 00DF')) from (values(1))x;
select upper(repeat(_UCS2 X'00DF', 10)) from (values(1))x;

--both have the same upper/lower case
select code_value(upper(_ucs2 x'0000')) from (values(1))x;
select code_value(lower(_ucs2 x'FFFD')) from (values(1))x;

--l/r trims
select char_length(ltrim(_ucs2'  abc  ')) from (values(1))x;
select rtrim(_ucs2'  abc  ') from (values(1))x;

--space
select * from (values(1))x where char_length(space(30, iso88591)) = 30;
select * from (values(1))x where char_length(space(30, ucs2)) = 30;
select space(10) from (values(1))x;
select space(10, ucs2) from (values(1))x;

--replace
select replace(_ucs2'1abcdeab', _ucs2'abcdeab', _ucs2 x'0058') from (values(1))x;
select replace(_ucs2'1abcdeab', _ucs2'abcdeab', _ucs2 x'') from (values(1))x;
select replace(_ucs2'abc', _ucs2'z', _ucs2 x'') from (values(1))x;

--UCS2 column can be used in where clause
select d from tab_ucs2 where d = _ucs2'abc';

--negative

--charset mismatch
select ascii(_ucs2'asdf') from (values(1)) x;
insert into tab_ucs2 values(CHAR(43));

--illegal chars
select char(-1) from (values(1)) x;
select char(257) from (values(1)) x;
select char(-10, ucs2) from (values(1)) x;
select char(65535, ucs2) from (values(1)) x;

-- overflow 

--ok as 4096 bytes or 2048 UCS2 chars is the upper limit. 682x 3 = 2046 
select char_length(upper(repeat(_ucs2 x'0390', 682))) from (values(1))x;

--should see an overflow error (683x3=2049)
select char_length(upper(repeat(_ucs2 x'0390', 683))) from (values(1))x;

--disabled old func names
select unicode_value(_ucs2 x'0390', ucs2) from (values(1))x;
select unicode_char(0390, ucs2) from (values(1))x;

--replace(), zero length pattern
select replace(_ucs2'abc', _ucs2'', _ucs2 x'') from (values(1))x;

-- ANSI char matching rules are preserved (i.e., relaxation only done on UCS2 hostvars)
select _ucs2'abc' || '' from (values(1))x;

select left(repeat(_ucs2 x'00c0 00d6', 25000), 10) from (values(1))x;
select _iso88591 x'$#' from (values(1))x;
select _ucs2 x'0a$#' from (values(1))x;

--large length values should not cause core-dump (case 10-040218-2346)
select space(2147483647,ucs2) from (values (1)) as x;
select rpad(N'test',2147483647,N'T') from (values(1)) as x;
select lpad(N'test',2147483647,N'T') from (values(1)) as x;
select substring(_ucs2'character set test' from 1 for 2147483647) from (values(1)) as x;

log;


?section clnup
drop table tab_iso91;
drop table tab_int;
drop table tab_ucs2;