core/sql/regress/charsets/EXPECTED002

>>
>>--HEADING can only be character_string_literal without Phase I work
>>create table t001 (c char(10) HEADING 'MYHEADING');

--- SQL operation complete.
>>create table t002 (c char(10) HEADING _ISO88591'MYISOHEADING');

--- SQL operation complete.
>>create table t003 (c char(10) default 'asdf');

--- SQL operation complete.
>>
>>--default can be UCS2
>>create table t004 (c char(10) character set UCS2 default _UCS2'adf');

--- SQL operation complete.
>>
>>create table t005 (c char(10) check (c > 'aaaa'));

--- SQL operation complete.
>>create table t006 (c char(10), check (c > 'aaaa'));

--- SQL operation complete.
>>create table t007 (c char(10), constraint myc1 check (c > 'qwew'));

--- SQL operation complete.
>>
>>--UNIQUE constraint can be specified on UCS2 columns
>>create table t008 (c char(10) character set UCS2 NOT NULL, 
+>                   d char(10) character set UCS2 NOT NULL, 
+>                   primary key(c), 
+>                   constraint myc5 unique(c,d));

--- SQL operation complete.
>>
>>--Referential Integrity on UCS2 colums
>>create table t009 (x char(10) character set UCS2, y int NOT NULL NOT DROPPABLE,
+>		   primary key(y),
+>                   foreign key(x) references $$TEST_SCHEMA$$.t008(c));

--- SQL operation complete.
>>
>>
>>create table t010 (c char(10) character set UCS2);

--- SQL operation complete.
>>
>>--view can be defined on a UCS2 column
>>create view t010v2 as select c from t010;

--- SQL operation complete.
>>
>>--index can be created on a UCS2 column
>>create table t011(c char(10) character set UCS2, d char(10) character set UCS2);

--- SQL operation complete.
>>create index t011i1 on t011(c,d);

--- SQL operation complete.
>>
>>--the COLLATION clause can be specified on the UCS2 or ISO88591 column
>>--and DEFAULT/BINARY is the only allowed collation
>>create table t012 (a char(10) character set ISO88591 COLLATE default,
+>                   b char(10) character set UCS2 COLLATE default);

--- SQL operation complete.
>>
>>
>>create table nt001 (c char(10) HEADING _UCS2'MYUCS2HEADING');

--- SQL operation complete.
>>create table nt002 (c char(10) character set UCS2 default _UCS2'default');

--- SQL operation complete.
>>
>>--UCS2 strings are NOW allowed in a constraint
>>create table nt003 (c char(10) character set UCS2 check (c > _UCS2'aaaa'));

--- SQL operation complete.
>>
>>--UCS2 strings are NOW allowed in a constraint
>>create table nt004 (c char(10) character set UCS2, check (c = _UCS2'aaaa'));

--- SQL operation complete.
>>
>>--UCS2 strings are NOW allowed in a constraint
>>create table nt005 (c char(10) character set UCS2, constraint myc2 check (c >
+>_UCS2'asdf'));

--- SQL operation complete.
>>
>>--no UCS2 strings in a view text
>>create view nt006v1 as select * from t013 where c > _UCS2'aaaa';

*** ERROR[4082] Object TRAFODION.SCH.T013 does not exist or is inaccessible.

--- SQL operation failed with errors.
>>
>>--no UCS2 strings in a trigger text
>>create table nt007 (d char(10) character set UCS2, i int);

--- SQL operation complete.
>>
>>--ok
>>create table nt008 (d char(10) character set UCS2, i int);

--- SQL operation complete.
>>create trigger t013t1 after insert on nt008
+>	update nt008 set i = 7 where i = 9;

*** ERROR[3131] The statement just entered is currently not supported.

*** ERROR[8822] The statement was not prepared.

>>--not ok
>>create trigger t013t2 after insert on nt008
+>	update nt007 set d = _UCS2'qwer' where d = _UCS2'aaaa';

*** ERROR[3131] The statement just entered is currently not supported.

*** ERROR[8822] The statement was not prepared.

>>
>>--no UCS2 strings in a FIRST KEY clause
>>create table nt009 (c char(10) character set UCS2)
+>	partition (add first key (_UCS2'aaa') location  $DATA);

*** ERROR[1240] The character set for a PARTITION KEY column must be ISO88591.

*** ERROR[8822] The statement was not prepared.

>>--INFER_CHARSET works for default values in CREATE TABLE DDL
>>control query default infer_charset 'on';

--- SQL operation complete.
>>create table t013(a char(10) character set ucs2 default 'aa');

--- SQL operation complete.
>>create table t013a(a char(10));

--- SQL operation complete.
>>invoke t013;

-- Definition of Trafodion table TRAFODION.SCH.T013
-- Definition current  Fri Aug  5 18:17:57 2016

  (
    SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A                                CHAR(10) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT _UCS2'aa'
  )

--- SQL operation complete.
>>
>>-- test INFER_CHARSET fixes:
>>-- test fix to genesis case 10-060317-2228
>>select *,case when a is null then 'A' else 'B' end as test_fld from t013;

--- 0 row(s) selected.
>>-- select used to get error 4035
>>
>>-- test fix to genesis case 10-060315-0580
>>select * from (values('A'),('B')) as t(c);

C
-

A
B

--- 2 row(s) selected.
>> -- used to get error 4035
>>
>>-- test fix to case 10-081022-6724 
>>prepare xx from insert into t013 select 'a' from t013a;

--- SQL command prepared.
>>prepare xx from insert into t013 select 'a' from t013a group by 'a';

--- SQL command prepared.
>>prepare xx from insert into t013 select 'a'||'b' from t013a;

--- SQL command prepared.
>>
>>control query default infer_charset 'off';

--- SQL operation complete.
>>
>>?section dml
>>
>>?section clnup
>>drop table t001;

--- SQL operation complete.
>>drop table t002;

--- SQL operation complete.
>>drop table t003;

--- SQL operation complete.
>>drop table t004;

--- SQL operation complete.
>>drop table t005;

--- SQL operation complete.
>>drop table t006;

--- SQL operation complete.
>>drop table t007;

--- SQL operation complete.
>>drop table t008 cascade;

--- SQL operation complete.
>>drop table t009;

--- SQL operation complete.
>>drop table t010 cascade;

--- SQL operation complete.
>>drop table t011;

--- SQL operation complete.
>>drop table t012;

--- SQL operation complete.
>>drop table t013;

--- SQL operation complete.
>>drop table t013a;

--- SQL operation complete.
>>drop table nt001;

--- SQL operation complete.
>>drop table nt002;

--- SQL operation complete.
>>drop table nt003;

--- SQL operation complete.
>>drop table nt004;

--- SQL operation complete.
>>drop table nt005;

--- SQL operation complete.
>>drop table nt007 cascade;

--- SQL operation complete.
>>drop table nt008 cascade;

--- SQL operation complete.
>>
>>log;