core/sql/regress/charsets/EXPECTED012

>>
>>create table tlongvarchar0(a long varchar(10) character set ucs2);

--- SQL operation complete.
>>create table tlongwvarchar0(a longwvarchar(50));

--- SQL operation complete.
>>create table tlongwvarchar1(
+>                            a longwvarchar default _ucs2'good day', 
+>                            b longwvarchar default _ucs2'today', 
+>                            c int, d real)
+>#ifNSK
+>attribute blocksize 4096
+>#ifNSK
+>;

--- SQL operation complete.
>>
>>
>>showddl tlongvarchar0;

CREATE TABLE TRAFODION.SCH.TLONGVARCHAR0
  (
    A                                VARCHAR(10) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>showddl tlongwvarchar0;

CREATE TABLE TRAFODION.SCH.TLONGWVARCHAR0
  (
    A                                VARCHAR(50) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  )
 ATTRIBUTES ALIGNED FORMAT
;

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

-- Definition of Trafodion table TRAFODION.SCH.TLONGWVARCHAR1
-- Definition current  Tue Sep 20 12:53:27 2016

  (
    SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A                                VARCHAR(2000) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT _UCS2'good day'
  , B                                VARCHAR(2000) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT _UCS2'today'
  , C                                INT DEFAULT NULL
  , D                                REAL DEFAULT NULL
  )

--- SQL operation complete.
>>
>>insert into tlongvarchar0 values(N'4356'), (_ucs2'today');

--- 2 row(s) inserted.
>>insert into tlongwvarchar0 values(N'4356'), (_ucs2'tomorrow');

--- 2 row(s) inserted.
>>insert into tlongwvarchar1 values(N'abc', N'4356', 1, 2), (_ucs2'today', _ucs2'tomorrow', 2, 3);

--- 2 row(s) inserted.
>>insert into tlongwvarchar1(c, d) values(1, 2);

--- 1 row(s) inserted.
>>
>>select cast(_ucs2'abcd' as longwvarchar(20)) from (values(1))x;

(EXPR)                                  
----------------------------------------

abcd                                    

--- 1 row(s) selected.
>>select char_length(cast(_ucs2'abcd' as longwvarchar(20))) from (values(1))x;

(EXPR)    
----------

         4

--- 1 row(s) selected.
>>select octet_length(cast(_ucs2'abcd' as longwvarchar(20))) from (values(1))x;

(EXPR)    
----------

         8

--- 1 row(s) selected.
>>select char_length(cast(_ucs2'abcd' as longwvarchar)) from (values(1))x;

(EXPR)    
----------

         4

--- 1 row(s) selected.
>>
>>select a || _ucs2'_suffix' from tlongvarchar0;

(EXPR)                            
----------------------------------

4356_suffix                       
today_suffix                      

--- 2 row(s) selected.
>>select a || _ucs2'_suffix' from tlongwvarchar0;

(EXPR)
------------------------------------------------------------------------------------------------------------------

4356_suffix                                                                                                       
tomorrow_suffix                                                                                                   

--- 2 row(s) selected.
>>select a || _ucs2'_suffix' from tlongwvarchar1;

(EXPR)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

abc_suffix                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
today_suffix                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
good day_suffix                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

--- 3 row(s) selected.
>>
>>update tlongwvarchar0 set a = _ucs2'the day after tomorrow';

--- 2 row(s) updated.
>>update tlongwvarchar1 set a = _ucs2'the day after tomorrow';

--- 3 row(s) updated.
>>delete from tlongwvarchar0;

--- 2 row(s) deleted.
>>
>>-- test min/max values
>>control query default Min_LONG_VARCHAR_DEFAULT_SIZE '2';

--- SQL operation complete.
>>control query default Min_LONG_VARCHAR_DEFAULT_SIZE '3';

--- SQL operation complete.
>>control query default Min_LONG_WVARCHAR_DEFAULT_SIZE '4';

--- SQL operation complete.
>>control query default Min_LONG_WVARCHAR_DEFAULT_SIZE '9';

--- SQL operation complete.
>>
>>control query default MAX_LONG_VARCHAR_DEFAULT_SIZE '11';

--- SQL operation complete.
>>control query default MAX_LONG_VARCHAR_DEFAULT_SIZE '10';

--- SQL operation complete.
>>control query default MAX_LONG_WVARCHAR_DEFAULT_SIZE '111';

--- SQL operation complete.
>>control query default MAX_LONG_WVARCHAR_DEFAULT_SIZE '100';

--- SQL operation complete.
>>
>>-- A valid range must be maintained (i.e., MIN <= MAX). The following 
>>-- four CQD should fail
>>control query default MIN_LONG_VARCHAR_DEFAULT_SIZE '12';

*** ERROR[2029] The new min value is greater than the current max value 10.

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

>>control query default MIN_LONG_WVARCHAR_DEFAULT_SIZE '101';

*** ERROR[2029] The new min value is greater than the current max value 100.

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

>>control query default MAX_LONG_VARCHAR_DEFAULT_SIZE '2';

*** ERROR[2030] The new max value is less than the current min value 3.

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

>>control query default MAX_LONG_WVARCHAR_DEFAULT_SIZE '6';

*** ERROR[2030] The new max value is less than the current min value 9.

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

>>
>>drop table tlongvarchar0;

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

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

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