-- Test: TEST314
-- Functionality: This tests the handling of non-ASCII, but ISO88591
--                characters and UTF8 handling
-- Expected files:   EXPECTED314
-- Tables created:   cs314t*
-- 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 test314(clnup);

log log314 clear;

obey test314(ddl);

obey test314(ISO_tests);

obey test314(clnup);

log;
exit;

?section ddl

create schema cs314s;
set schema cs314s;

?section ISO_tests

select converttohex(cast(      x'80 90 A0 B0 C0 82 D0 E0 F0 FF' as char(09))) from (values(0))x;
select converttohex(cast(      x'80 90 A0 B0 C0 82 D0 E0 F0 FF' as char(10))) from (values(0))x;
select converttohex(cast(      x'C2 80 C2 81 C2 82 DF BE DF BF' as char(12))) from (values(0))x;
select converttohex(cast(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF' as char(12 BYTES) character set utf8)) from (values(0))x;
select converttohex(cast(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF' as char(10 BYTES) character set utf8)) from (values(0))x;
select converttohex(cast(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF' as char(09 BYTES) character set utf8)) from (values(0))x;
select converttohex(x'C2 80' || _utf8 x'C2 80') from (values(0))x;
select 'BAD' from (values(0))x where x'C2 80'  = _utf8 x'C2 80';
select 'OK ' from (values(0))x where x'C2 80' != _utf8 x'C2 80';
select 'OK ' from (values(0))x where x'80' = _utf8 x'C2 80';
select 'BAD' from (values(0))x where x'90A0B0C0D0E0F0FF' != _utf8 x'C290C2A0C2B0C380C390C3A0C3B0C3BF';
select 'BAD' from (values(0))x where x'C2 80' || _utf8 x'C2 80' != _utf8 x'C382C280C280';
select 'BAD' from (values(0))x where _utf8 x'C2 80' || x'C2 80' != _utf8 x'C280C382C280';

select converttohex(substr(      x'C2 80 C2 81 C2 82 DF BE DF BF',1,3)) from (values(0))x;
select converttohex(substr(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF',1,3)) from (values(0))x;

select converttohex(substr(      x'C2 80 C2 81 C2 82 DF BE DF BF',3,2)) from (values(0))x;
select converttohex(substr(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF',3,2)) from (values(0))x;

select converttohex(insert(      x'C2 80 C2 81 C2 82 DF BE DF BF', 2, 0, 'Z')) from (values(0))x;
select converttohex(insert(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF', 2, 0, 'Z')) from (values(0))x;
select converttohex(insert(      x'C2 80 C2 81 C2 82 DF BE DF BF', 2, 3, 'Z')) from (values(0))x;
select converttohex(insert(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF', 2, 3, 'Z')) from (values(0))x;

select converttohex(replace(      x'C2 80 C2 81 C2 82 DF BE DF BF',       x'C2 82',       x'DC B7')) from (values(0))x;
select converttohex(replace(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF',       x'C2 82',       x'DC B7')) from (values(0))x;
select converttohex(replace(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF', _utf8 x'C2 82',       x'DC B7')) from (values(0))x;
select converttohex(replace(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF', _utf8 x'C2 82', _utf8 x'DC B7')) from (values(0))x;
select converttohex(LOWER(      x'C2 80 C2 81 C2 82 DF BE DF BF')) from (values(0))x;
select converttohex(LOWER(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF')) from (values(0))x;
-- Try certain characters whose LOWER value is an ISO88591 value even though they themselves are not.
select converttohex(LOWER(_ucs2 x'0178')) from (values(0))x;
select converttohex(TRANSLATE(_ucs2 x'0178' using UCS2toUTF8)) from (values(0))x;
select converttohex(LOWER(_utf8 x'C5 B8')) from (values(0))x;
select converttohex(LOWER(_ucs2 x'0130')) from (values(0))x;
select converttohex(TRANSLATE(_ucs2 x'0130' using UCS2toUTF8)) from (values(0))x;
select converttohex(LOWER(_utf8 x'C4 B0')) from (values(0))x;
select converttohex(LOWER(TRANSLATE(_ucs2 x'0178' using UCS2toUTF8))) from (values(0))x;
select converttohex(TRANSLATE(LOWER(_ucs2 x'0178') using UCS2toUTF8)) from (values(0))x;
select converttohex(LOWER(TRANSLATE(_ucs2 x'0130' using UCS2toUTF8))) from (values(0))x;
select converttohex(TRANSLATE(LOWER(_ucs2 x'0130') using UCS2toUTF8)) from (values(0))x;
select converttohex(LOWER(TRANSLATE(_ucs2 x'01300178' using UCS2toUTF8))) from (values(0))x;
select converttohex(TRANSLATE(LOWER(_ucs2 x'01300178') using UCS2toUTF8)) from (values(0))x;
select converttohex(LOWER(TRANSLATE(_ucs2 x'01780130' using UCS2toUTF8))) from (values(0))x;
select converttohex(TRANSLATE(LOWER(_ucs2 x'01780130') using UCS2toUTF8)) from (values(0))x;
select converttohex(UPPER(      x'C2 80 C2 81 C2 82 DF BE DF BF')) from (values(0))x;
select converttohex(UPPER(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF')) from (values(0))x;
-- Try certain characters whose UPPER value is an ISO88591 value even though they themselves are not.
select converttohex(UPPER(_ucs2 x'017F')) from (values(0))x;
select converttohex(TRANSLATE(_ucs2 x'017F' using UCS2toUTF8)) from (values(0))x;
select converttohex(UPPER(_utf8 x'C5 BF')) from (values(0))x;
select converttohex(UPPER(_ucs2 x'0131')) from (values(0))x;
select converttohex(TRANSLATE(_ucs2 x'0131' using UCS2toUTF8)) from (values(0))x;
select converttohex(UPPER(_utf8 x'C4 B1')) from (values(0))x;
select converttohex(UPPER(TRANSLATE(_ucs2 x'017F' using UCS2toUTF8))) from (values(0))x;
select converttohex(TRANSLATE(UPPER(_ucs2 x'017F') using UCS2toUTF8)) from (values(0))x;
select converttohex(TRANSLATE(_ucs2 x'017F' using UCS2toUTF8)) from (values(0))x;
select converttohex(UPPER(TRANSLATE(_ucs2 x'0131' using UCS2toUTF8))) from (values(0))x;
select converttohex(TRANSLATE(UPPER(_ucs2 x'0131') using UCS2toUTF8)) from (values(0))x;
select converttohex(UPPER(TRANSLATE(_ucs2 x'0131017F' using UCS2toUTF8))) from (values(0))x;
select converttohex(TRANSLATE(UPPER(_ucs2 x'0131017F') using UCS2toUTF8)) from (values(0))x;
select converttohex(UPPER(TRANSLATE(_ucs2 x'017F0131' using UCS2toUTF8))) from (values(0))x;
select converttohex(TRANSLATE(UPPER(_ucs2 x'017F0131') using UCS2toUTF8)) from (values(0))x;
-- Invalid UTF-16 surrogate pairs
select converttohex(TRANSLATE(_ucs2 X'D8340041' using UCS2toUTF8)) from (values(0))x;
select converttohex(TRANSLATE(_ucs2 X'DD1E0041' using UCS2toUTF8)) from (values(0))x;
cqd TRANSLATE_ERROR_UNICODE_TO_UNICODE 'off';
select converttohex(TRANSLATE(_ucs2 X'D8340041' using UCS2toUTF8)) from (values(0))x;
select converttohex(TRANSLATE(_ucs2 X'DD1E0041' using UCS2toUTF8)) from (values(0))x;
select converttohex(cast(cast(_ucs2 X'DD1E0041' as char(2) character set ucs2) as char(2) character set UTF8)) from (values(0))x;
cqd TRANSLATE_ERROR_UNICODE_TO_UNICODE reset;
cqd TRANSLATE_ERROR 'off';
select converttohex(TRANSLATE(_ucs2 X'D8340041' using UCS2toUTF8)) from (values(0))x;
select converttohex(TRANSLATE(_ucs2 X'DD1E0041' using UCS2toUTF8)) from (values(0))x;
select converttohex(cast(cast(_ucs2 X'DD1E0041' as char(2) character set ucs2) as char(2) character set UTF8)) from (values(0))x;
--
select converttohex(LEFT(      x'C2 80 C2 81 C2 82 DF BE DF BF',4)) from (values(0))x;
select converttohex(LEFT(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF',4)) from (values(0))x;
select converttohex(RIGHT(      x'C2 80 C2 81 C2 82 DF BE DF BF',4)) from (values(0))x;
select converttohex(RIGHT(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF',4)) from (values(0))x;
select converttohex(LPAD(      x'C2 80 C2 81 C2 82 DF BE DF BF', 12,       x'DC B7')) from (values(0))x;
select converttohex(LPAD(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF', 12,       x'DC B7')) from (values(0))x;
select converttohex(LPAD(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF', 12, _utf8 x'DC B7')) from (values(0))x;
select converttohex(RPAD(      x'C2 80 C2 81 C2 82 DF BE DF BF', 12,       x'DC B7')) from (values(0))x;
select converttohex(RPAD(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF', 12,       x'DC B7')) from (values(0))x;
select converttohex(RPAD(_utf8 x'C2 80 C2 81 C2 82 DF BE DF BF', 12, _utf8 x'DC B7')) from (values(0))x;

select DECODE(x'C2 80', _utf8 x'C2 80', 'BAD', 'C2 80', 'BAD', x'C2'|| x'80', 'GOOD') from (values(0))x;
select DECODE(_utf8 x'C2 80', x'C2 80', 'BAD', _utf8'80', 'BAD', x'80', 'GOOD') from (values(0))x;
--
select converttohex(TRANSLATE(_iso88591 x'F7' using ISO88591toUTF8)) from (values(0))x;

?section clnup

drop schema cs314s cascade;

