>>
>>create table t001t1 (a int, b char(9), c int, d char(4));

--- SQL operation complete.
>>create table t001tn (a int, b char(9), c int, d char(4) COLLATE SJIS);

*** WARNING[3169] SJIS is not a known collation.

*** ERROR[4069] Column D uses an unsupported collation.

--- SQL operation failed with errors.
>>create table t001t2 (a int not null, b char(9), c int, d char(4), primary key (a));

--- SQL operation complete.
>>create table t001t3 (a int not null, b char(9) not null, c int, d char(4), primary key (a, b));

--- SQL operation complete.
>>
>>#ifMX
>>create table t001ut1 (a int, b nchar(9), c int, d nchar(4));

--- SQL operation complete.
>>create table t001ut2 (a int not null, b nchar(9), c int, d nchar(4), primary key (a));

--- SQL operation complete.
>>create table t001ut3 (a int not null, b nchar(9) not null, c int, d nchar(4), primary key (a, b));

--- SQL operation complete.
>>#ifMX
>>
>>?section dml
>>
>>invoke t001t1;

-- Definition of Trafodion table TRAFODION.SCH.T001T1
-- Definition current  Tue Aug  9 03:54:49 2016

  (
    SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A                                INT DEFAULT NULL
  , B                                CHAR(9) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , C                                INT DEFAULT NULL
  , D                                CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  )

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

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

--- SQL operation failed with errors.
>>invoke t001t2;

-- Definition of Trafodion table TRAFODION.SCH.T001T2
-- Definition current  Tue Aug  9 03:54:50 2016

  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                CHAR(9) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , C                                INT DEFAULT NULL
  , D                                CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  )
  PRIMARY KEY (A ASC)

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

-- Definition of Trafodion table TRAFODION.SCH.T001T3
-- Definition current  Tue Aug  9 03:54:50 2016

  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                CHAR(9) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT NULL
  , D                                CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  )
  PRIMARY KEY (A ASC, B ASC)

--- SQL operation complete.
>>
>>#ifMX
>>invoke $$TEST_SCHEMA$$.t001ut1;

-- Definition of Trafodion table TRAFODION.SCH.T001UT1
-- Definition current  Tue Aug  9 03:54:50 2016

  (
    SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A                                INT DEFAULT NULL
  , B                                CHAR(9) CHARACTER SET UCS2 COLLATE DEFAULT
      DEFAULT NULL
  , C                                INT DEFAULT NULL
  , D                                CHAR(4) CHARACTER SET UCS2 COLLATE DEFAULT
      DEFAULT NULL
  )

--- SQL operation complete.
>>invoke $$TEST_SCHEMA_NAME$$.t001ut2;

-- Definition of Trafodion table TRAFODION.SCH.T001UT2
-- Definition current  Tue Aug  9 03:54:50 2016

  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                CHAR(9) CHARACTER SET UCS2 COLLATE DEFAULT
      DEFAULT NULL
  , C                                INT DEFAULT NULL
  , D                                CHAR(4) CHARACTER SET UCS2 COLLATE DEFAULT
      DEFAULT NULL
  )
  PRIMARY KEY (A ASC)

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

-- Definition of Trafodion table TRAFODION.SCH.T001UT3
-- Definition current  Tue Aug  9 03:54:51 2016

  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                CHAR(9) CHARACTER SET UCS2 COLLATE DEFAULT
      NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT NULL
  , D                                CHAR(4) CHARACTER SET UCS2 COLLATE DEFAULT
      DEFAULT NULL
  )
  PRIMARY KEY (A ASC, B ASC)

--- SQL operation complete.
>>#ifMX
>>
>>-- INSERT queries
>>insert into t001t1 values (10, 'abc', 20, 'xy');

--- 1 row(s) inserted.
>>insert into t001t1(b,d,a,c) values ('defg', 'wx', 10+10, 30);

--- 1 row(s) inserted.
>>insert into t001t2 select * from t001t1;

--- 2 row(s) inserted.
>>insert into t001t3(a,b,c,d) select a,b,c,d from t001t2;

--- 2 row(s) inserted.
>>
>>#ifMX
>>insert into t001ut1 values (10, N'abc', 20, N'xy');

--- 1 row(s) inserted.
>>insert into t001ut1(b,d,a,c) values (N'defg', N'wx', 10+10, 30);

--- 1 row(s) inserted.
>>insert into t001ut2 select * from t001ut1;

--- 2 row(s) inserted.
>>insert into t001ut3(a,b,c,d) select a,b,c,d from t001ut2;

--- 2 row(s) inserted.
>>#ifMX
>>
>>-- SELECT queries
>>select * from t001t1;

A            B          C            D   
-----------  ---------  -----------  ----

         10  abc                 20  xy  
         20  defg                30  wx  

--- 2 row(s) selected.
>>select t001t2.* from t001t2;

A            B          C            D   
-----------  ---------  -----------  ----

         10  abc                 20  xy  
         20  defg                30  wx  

--- 2 row(s) selected.
>>select * from t001t3;

A            B          C            D   
-----------  ---------  -----------  ----

         10  abc                 20  xy  
         20  defg                30  wx  

--- 2 row(s) selected.
>>
>>#ifMX
>>select * from t001ut1;

A            B                   C            D       
-----------  ------------------  -----------  --------

         10  abc                          20  xy      
         20  defg                         30  wx      

--- 2 row(s) selected.
>>select t001ut2.* from t001ut2;

A            B                   C            D       
-----------  ------------------  -----------  --------

         10  abc                          20  xy      
         20  defg                         30  wx      

--- 2 row(s) selected.
>>select * from t001ut3;

A            B                   C            D       
-----------  ------------------  -----------  --------

         10  abc                          20  xy      
         20  defg                         30  wx      

--- 2 row(s) selected.
>>#ifMX
>>
>>-- Error case
>>select 0, '0' + 1 from t001t1;

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

     0                     1
     0                     1

--- 2 row(s) selected.
>>-- should get type incompatibility error
>>
>>select cast(b as ansivarchar(8)) from t001t1;

*** ERROR[3178] One or more of the following external (host-language) data types incorrectly appears within the SQL query or operation:  ANSIVARCHAR.

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

>>	-- err 3178
>>create table t001t1_foo(a lsdecimal);

*** ERROR[3178] One or more of the following external (host-language) data types incorrectly appears within the SQL query or operation:  LSDECIMAL.

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

>>		-- err 3178
>>
>>select * from (select * from t001t1) x;

A            B          C            D   
-----------  ---------  -----------  ----

         10  abc                 20  xy  
         20  defg                30  wx  

--- 2 row(s) selected.
>>
>>select a,b from t001t1;

A            B        
-----------  ---------

         10  abc      
         20  defg     

--- 2 row(s) selected.
>>select a,a from t001t1;

A            A          
-----------  -----------

         10           10
         20           20

--- 2 row(s) selected.
>>
>>select a+1,a-1,a*1,a/1 from t001t1;

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

                  11                     9                    10                   10.0
                  21                    19                    20                   20.0

--- 2 row(s) selected.
>>
>>select t001t1.a, t001t1.d from t001t1;

A            D   
-----------  ----

         10  xy  
         20  wx  

--- 2 row(s) selected.
>>
>>select 1 from t001t1;

(EXPR)
------

     1
     1

--- 2 row(s) selected.
>>select 1+1 from t001t1;

(EXPR)
------

     2
     2

--- 2 row(s) selected.
>>
>>select * from t001t1 where 1 = 1;

A            B          C            D   
-----------  ---------  -----------  ----

         10  abc                 20  xy  
         20  defg                30  wx  

--- 2 row(s) selected.
>>select * from t001t1 where a = 10;

A            B          C            D   
-----------  ---------  -----------  ----

         10  abc                 20  xy  

--- 1 row(s) selected.
>>select * from t001t1 where a <> 10;

A            B          C            D   
-----------  ---------  -----------  ----

         20  defg                30  wx  

--- 1 row(s) selected.
>>select * from t001t1 where a = 10 or a = 20;

A            B          C            D   
-----------  ---------  -----------  ----

         10  abc                 20  xy  
         20  defg                30  wx  

--- 2 row(s) selected.
>>select * from t001t1 where a = 10 and a = 20;

--- 0 row(s) selected.
>>
>>-- Error case
>>#ifMX
>>select 0, N'0' + 1 from t001ut1;

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

     0                     1
     0                     1

--- 2 row(s) selected.
>>-- should get type incompatibility error
>>
>>select * from (select * from t001ut1) x;

A            B                   C            D       
-----------  ------------------  -----------  --------

         10  abc                          20  xy      
         20  defg                         30  wx      

--- 2 row(s) selected.
>>
>>select a,b from t001ut1;

A            B                 
-----------  ------------------

         10  abc               
         20  defg              

--- 2 row(s) selected.
>>
>>select t001ut1.a, t001ut1.d from t001ut1;

A            D       
-----------  --------

         10  xy      
         20  wx      

--- 2 row(s) selected.
>>
>>select * from t001ut1 where a = 10 and a = 20;

--- 0 row(s) selected.
>>
>>select * from (select * from t001ut1) x , t001ut2;

A            B                   C            D         A            B                   C            D
-----------  ------------------  -----------  --------  -----------  ------------------  -----------  --------

         10  abc                          20  xy                 10  abc                          20  xy      
         10  abc                          20  xy                 20  defg                         30  wx      
         20  defg                         30  wx                 10  abc                          20  xy      
         20  defg                         30  wx                 20  defg                         30  wx      

--- 4 row(s) selected.
>>#ifMX
>>
>>#ifMP
>>set envvar NCHAR_SJIS_DEBUG;
>>			-- allow charset SJIS on MP
>>#ifMP
>>
>>select a from t001t1 where b like _SJIS'sj';

*** ERROR[3010] Character set SJIS is not yet supported.

*** ERROR[15001] A syntax error occurred at or before: 
select a from t001t1 where b like _SJIS'sj';
                                      ^ (39 characters from start of SQL statement)

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

>>select a from t001t1 where b like 'sj'COLLATE SJIS;

*** WARNING[3169] SJIS is not a known collation.

*** ERROR[4041] Type CHAR(9) CHARACTER SET ISO88591 COLLATE DEFAULT cannot be compared with type CHAR(2) CHARACTER SET ISO88591 COLLATE _unknown_.

*** ERROR[4050] The operands of the LIKE predicate must be comparable character data types (that is, of the same character set and collation).

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

>>	-- ok, B gets coerced
>>select a from t001t1 where b COLLATE DEFAULT like 'sj'COLLATE SJIS;

*** WARNING[3169] SJIS is not a known collation.

*** ERROR[4041] Type CHAR(9) CHARACTER SET ISO88591 COLLATE DEFAULT cannot be compared with type CHAR(2) CHARACTER SET ISO88591 COLLATE _unknown_.

*** ERROR[4050] The operands of the LIKE predicate must be comparable character data types (that is, of the same character set and collation).

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

>>select a from t001t1 where b COLLATE DEFAULT like 'jj' escape 's'COLLATE SJIS;

*** WARNING[3169] SJIS is not a known collation.

*** ERROR[4041] Type CHAR(9) CHARACTER SET ISO88591 COLLATE DEFAULT cannot be compared with type CHAR(1) CHARACTER SET ISO88591 COLLATE _unknown_.

*** ERROR[4050] The operands of the LIKE predicate must be comparable character data types (that is, of the same character set and collation).

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

>>
>>select a from t001t1 where trim(1 from 2) = '';

*** ERROR[4133] Both trim character and source have to be CHARACTER typed.

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

>>select a from t001t1 where trim(1 from b) = '';

*** ERROR[4133] Both trim character and source have to be CHARACTER typed.

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

>>select a from t001t1 where trim(b from 2) = '';

*** ERROR[8404] The trim character argument of function TRIM must be one character in length.

--- 0 row(s) selected.
>>
>>-- comment out because of the behavior change to NATIONAL_CHARSET in R2.
>>-- It is a read-only CQD.
>>--control query default NATIONAL_CHARSET 'kanji';
>>--select a from t001t1 where trim(N' ' from b) = '';
>>--control query default NATIONAL_CHARSET 'sql_text';
>>--select a from t001t1 where trim(N' ' from b) = '';	-- ok
>>--control query default NATIONAL_CHARSET reset;
>>select a from t001t1 where trim(N' ' from b) = '';

--- 0 row(s) selected.
>>select a from t001t1 where trim(' 'COLLATE SJIS from b COLLATE DEFAULT) = '';

*** WARNING[3169] SJIS is not a known collation.

*** ERROR[4041] Type CHAR(1) CHARACTER SET ISO88591 COLLATE _unknown_ cannot be compared with type CHAR(9) CHARACTER SET ISO88591 COLLATE DEFAULT.

*** ERROR[4063] The operands of function TRIM, LTRIM, or RTRIM must be comparable character data types (that is, of the same character set and collation).

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

>>
>>select a from t001t1 where '' = replace(1,2,3);

*** ERROR[4064] The operands of function REPLACE must be compatible character data types (that is, of the same character set).

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

>>select a from t001t1 where '' = replace('a','x','z'COLLATE SJIS);

*** WARNING[3169] SJIS is not a known collation.

--- 0 row(s) selected.
>>	-- ok, compatible
>>select a from t001t1 where '' = replace('a'COLLATE DEFAULT,'xx'COLLATE SJIS,'z');

*** WARNING[3169] SJIS is not a known collation.

*** ERROR[4041] Type CHAR(1) CHARACTER SET ISO88591 COLLATE DEFAULT cannot be compared with type CHAR(2) CHARACTER SET ISO88591 COLLATE _unknown_.

*** ERROR[4063] The operands of function REPLACE must be comparable character data types (that is, of the same character set and collation).

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

>>	-- not comparable
>>select a from t001t1 where '' = replace('a',2,'z');

*** ERROR[4041] Type CHAR(1) cannot be compared with type NUMERIC(1).

*** ERROR[4063] The operands of function REPLACE must be comparable character data types (that is, of the same character set and collation).

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

>>	-- not comparable
>>select a from t001t1 where 99 = position(1 in 2);

*** ERROR[4063] The operands of function POSITION or LOCATE must be comparable character data types (that is, of the same character set and collation).

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

>>select a from t001t1 where 99 = position('x' in 2);

*** ERROR[4063] The operands of function POSITION or LOCATE must be comparable character data types (that is, of the same character set and collation).

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

>>select a from t001t1 where 99 = position(1 in 'y');

*** ERROR[4063] The operands of function POSITION or LOCATE must be comparable character data types (that is, of the same character set and collation).

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

>>select a from t001t1 where 99 = position('xx'COLLATE SJIS in 'y'COLLATE DEFAULT);

*** WARNING[3169] SJIS is not a known collation.

*** ERROR[4041] Type CHAR(2) CHARACTER SET ISO88591 COLLATE _unknown_ cannot be compared with type CHAR(1) CHARACTER SET ISO88591 COLLATE DEFAULT.

*** ERROR[4063] The operands of function POSITION or LOCATE must be comparable character data types (that is, of the same character set and collation).

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

>>
>>select b from t001tn UNION     select d from t001tn;

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

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

>>			-- not comparable
>>select b from t001tn UNION ALL select d from t001tn;

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

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

>>		     -- ok, no resulting collating sequence, but no comparison
>>select * from (
+>  select b from t001tn UNION ALL select d from t001tn) u(b) where b='x';

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

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

>>  			-- not comparable
>>select * from (
+>  select b from t001tn UNION ALL select d from t001tn) u(b) where b=(select b from t001tn);

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

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

>>  			-- not comparable
>>select b from t001tn UNION     select d COLLATE SJIS from t001tn;

*** WARNING[3169] SJIS is not a known collation.

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

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

>>			-- ok, coerced
>>select b COLLATE SJIS from t001tn UNION     select d COLLATE SJIS from t001tn;

*** WARNING[3169] SJIS is not a known collation.

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

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

>>			-- ok
>>
>>select a COLLATE SJIS from t001tn;

*** WARNING[3169] SJIS is not a known collation.

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

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

>>select b COLLATE Xyzw from t001tn;

*** WARNING[3169] XYZW is not a known collation.

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

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

>>-- ok, unknown-coll warning, coll not used
>>select b from (select b COLLATE Xyzw from t001tn)x where b>'a';

*** WARNING[3169] XYZW is not a known collation.

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

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

>>
>>
>>-- JOIN queries
>>select t001t1.a, t001t2.a from t001t1 , t001t2 where t001t1.a = t001t2.a;

A            A          
-----------  -----------

         10           10
         20           20

--- 2 row(s) selected.
>>
>>-- Genesis test case added 12/18/96
>>select t001t1.a + t001t2.a from t001t1 , t001t2 where t001t1.a = t001t2.a;

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

                  20
                  40

--- 2 row(s) selected.
>>
>>select t001t1.a, t001t2.a from t001t1 join t001t2 on t001t1.a = t001t2.a and t001t1.a = 10 and t001t2.a = 10;

A            A          
-----------  -----------

         10           10

--- 1 row(s) selected.
>>select t001t1.a, t001t2.a from t001t1 join t001t2 on t001t1.a = 10 or t001t2.a = 10;

A            A          
-----------  -----------

         10           10
         10           20
         20           10

--- 3 row(s) selected.
>>select t001t1.a, t001t2.a from t001t1 join t001t2 on
+>t001t1.a = 10 and (t001t1.a = 10 or t001t1.a = 20)
+>or t001t2.a = 10 and (t001t2.a = 10 or t001t2.a = 20);

A            A          
-----------  -----------

         10           10
         10           20
         20           10

--- 3 row(s) selected.
>>select t001t1.a, t001t2.a from t001t1 join t001t2 on
+>t001t1.a = 10 and (t001t1.a = 10 or t001t1.a = 20)
+>and t001t2.a = 10 and (t001t2.a = 10 or t001t2.a = 20);

A            A          
-----------  -----------

         10           10

--- 1 row(s) selected.
>>
>>select * from (select * from t001t1) x , t001t2;

A            B          C            D     A            B          C            D
-----------  ---------  -----------  ----  -----------  ---------  -----------  ----

         10  abc                 20  xy             10  abc                 20  xy  
         10  abc                 20  xy             20  defg                30  wx  
         20  defg                30  wx             10  abc                 20  xy  
         20  defg                30  wx             20  defg                30  wx  

--- 4 row(s) selected.
>>
>>-- NATURAL JOIN queries
>>select a from t001t1 x natural join t001t1 y;

A          
-----------

         10
         20

--- 2 row(s) selected.
>>select a from t001t1 natural join t001t2;

A          
-----------

         10
         20

--- 2 row(s) selected.
>>select a from t001t1 natural join t001t2 natural join t001t3;

A          
-----------

         10
         20

--- 2 row(s) selected.
>>select x.a, x.b from (select a, b, c from t001t1 natural join t001t2) x join t001t3
+>on x.c = t001t3.c;

A            B        
-----------  ---------

         10  abc      
         20  defg     

--- 2 row(s) selected.
>>
>>#ifMX
>>select a from t001ut1 x natural join t001ut1 y;

A          
-----------

         10
         20

--- 2 row(s) selected.
>>select a from t001ut1 natural join t001ut2;

A          
-----------

         10
         20

--- 2 row(s) selected.
>>select a from t001ut1 natural join t001ut2 natural join t001ut3;

A          
-----------

         10
         20

--- 2 row(s) selected.
>>select x.a, x.b from (select a, b, c from t001ut1 natural join t001ut2) x join t001ut3
+>on x.c = t001ut3.c;

A            B                 
-----------  ------------------

         10  abc               
         20  defg              

--- 2 row(s) selected.
>>
>>select count(*) from (select * from t001ut1) x natural join (select * from t001ut2) y ,
+> (select * from t001ut3) z;

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

                   4

--- 1 row(s) selected.
>>#ifMX
>>
>>-- AGGREGATE queries
>>
>>select count(*), min(a), max(a), sum(a), avg(a) from t001t1;

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

                   2           10           20                    30                    15

--- 1 row(s) selected.
>>select count(*), min(a), max(a), sum(a), avg(a) from t001t1 having count(*) > 0;

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

                   2           10           20                    30                    15

--- 1 row(s) selected.
>>select count(*), min(a), max(a), sum(a), avg(a) from t001t1 having count(*) < 0;

--- 0 row(s) selected.
>>select count(*), sum(a) from t001t1 having count(*) = 2 and sum(a) = 30;

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

                   2                    30

--- 1 row(s) selected.
>>select sum(t001t1.a) from t001t1 , t001t2;

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

                  60

--- 1 row(s) selected.
>>select count(*) from (select * from t001t1) x natural join (select * from t001t2) y ,
+> (select * from t001t3) z;

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

                   4

--- 1 row(s) selected.
>>
>>
>>-- UPDATE queries
>>update t001t1 set a = 10 where a = 10;

--- 1 row(s) updated.
>>select * from t001t1;

A            B          C            D   
-----------  ---------  -----------  ----

         10  abc                 20  xy  
         20  defg                30  wx  

--- 2 row(s) selected.
>>update t001t1 set a = 100, d = 'yx' where b = 'abc';

--- 1 row(s) updated.
>>select * from t001t1;

A            B          C            D   
-----------  ---------  -----------  ----

        100  abc                 20  yx  
         20  defg                30  wx  

--- 2 row(s) selected.
>>update t001t1 set c = 55;

--- 2 row(s) updated.
>>select * from t001t1;

A            B          C            D   
-----------  ---------  -----------  ----

        100  abc                 55  yx  
         20  defg                55  wx  

--- 2 row(s) selected.
>>
>>update t001t2 set c = c+1, b = 'gfe' where d = 'wx';

--- 1 row(s) updated.
>>select * from t001t2;

A            B          C            D   
-----------  ---------  -----------  ----

         10  abc                 20  xy  
         20  gfe                 31  wx  

--- 2 row(s) selected.
>>
>>
>>#ifMX
>>update t001ut1 set a = 100, d = N'yx' where b = N'abc';

--- 1 row(s) updated.
>>select * from t001ut1;

A            B                   C            D       
-----------  ------------------  -----------  --------

        100  abc                          20  yx      
         20  defg                         30  wx      

--- 2 row(s) selected.
>>
>>update t001ut2 set c = c+1, b = N'gfe' where d = N'wx';

--- 1 row(s) updated.
>>select * from t001ut2;

A            B                   C            D       
-----------  ------------------  -----------  --------

         10  abc                          20  xy      
         20  gfe                          31  wx      

--- 2 row(s) selected.
>>#ifMX
>>
>>
>>-- DELETE queries
>>delete from t001t1 where a = 100;

--- 1 row(s) deleted.
>>select * from t001t1;

A            B          C            D   
-----------  ---------  -----------  ----

         20  defg                55  wx  

--- 1 row(s) selected.
>>
>>delete from t001t3;

--- 2 row(s) deleted.
>>select * from t001t3;

--- 0 row(s) selected.
>>
>>delete from t001t1;

--- 1 row(s) deleted.
>>delete from t001t2;

--- 2 row(s) deleted.
>>
>>#ifMX
>>delete from t001ut3;

--- 2 row(s) deleted.
>>select * from t001ut3;

--- 0 row(s) selected.
>>
>>delete from t001ut1;

--- 2 row(s) deleted.
>>delete from t001ut2;

--- 2 row(s) deleted.
>>#ifMX
>>
>>-- PREPAREd queries
>>prepare s1 from insert into t001t1 values (10, 'abc', 20, 'yz');

--- SQL command prepared.
>>execute s1;

--- 1 row(s) inserted.
>>insert into t001t1 values (30, 'def', 40, 'wx');

--- 1 row(s) inserted.
>>
>>prepare s2 from select * from t001t1;

--- SQL command prepared.
>>execute s2;

A            B          C            D   
-----------  ---------  -----------  ----

         10  abc                 20  yz  
         30  def                 40  wx  

--- 2 row(s) selected.
>>
>>execute s1;

--- 1 row(s) inserted.
>>execute s2;

A            B          C            D   
-----------  ---------  -----------  ----

         10  abc                 20  yz  
         30  def                 40  wx  
         10  abc                 20  yz  

--- 3 row(s) selected.
>>
>>prepare s1 from select * from t001t1 where a = 10;

--- SQL command prepared.
>>execute s1;

A            B          C            D   
-----------  ---------  -----------  ----

         10  abc                 20  yz  
         10  abc                 20  yz  

--- 2 row(s) selected.
>>
>>prepare s2 from select * from t001t1 where a = 30;

--- SQL command prepared.
>>execute s2;

A            B          C            D   
-----------  ---------  -----------  ----

         30  def                 40  wx  

--- 1 row(s) selected.
>>
>>show prepared;
S1
  SELECT * FROM T001T1 WHERE A = 10;

S2
  SELECT * FROM T001T1 WHERE A = 30;

>>
>>#ifMX
>>prepare s1 from insert into t001ut1 values (10, N'abc', 20, N'yz');

--- SQL command prepared.
>>execute s1;

--- 1 row(s) inserted.
>>insert into t001ut1 values (30, N'def', 40, N'wx');

--- 1 row(s) inserted.
>>
>>prepare s2 from select * from t001ut1;

--- SQL command prepared.
>>execute s2;

A            B                   C            D       
-----------  ------------------  -----------  --------

         10  abc                          20  yz      
         30  def                          40  wx      

--- 2 row(s) selected.
>>
>>execute s1;

--- 1 row(s) inserted.
>>execute s2;

A            B                   C            D       
-----------  ------------------  -----------  --------

         10  abc                          20  yz      
         30  def                          40  wx      
         10  abc                          20  yz      

--- 3 row(s) selected.
>>
>>prepare s1 from select * from t001ut1 where a = 10;

--- SQL command prepared.
>>execute s1;

A            B                   C            D       
-----------  ------------------  -----------  --------

         10  abc                          20  yz      
         10  abc                          20  yz      

--- 2 row(s) selected.
>>
>>show prepared;
S2
  SELECT * FROM T001UT1;

S1
  SELECT * FROM T001UT1 WHERE A = 10;

>>#ifMX
>>
>>-- PARAM queries (all moved to TEST001(core))
>>
>>-- ----------------------
>>-- test reverse scanning
>>-- ----------------------
>>create table t001desc
+>  (
+>     a numeric(18) signed    not null,
+>     primary key ( a DESC )
+>  )
+>;

--- SQL operation complete.
>>
>>insert into t001desc values(1), (2), (3), (5), (7), (10);

--- 6 row(s) inserted.
>>select a from t001desc where a < 5 order by a;

A                   
--------------------

                   1
                   2
                   3

--- 3 row(s) selected.
>>
>>------------------------------
>>
>>create table t001asc
+>  (
+>     a numeric(18) signed    not null,
+>     primary key ( a ASC ) 
+>  )
+>;

--- SQL operation complete.
>>
>>insert into t001asc  values(1), (2), (3), (5), (7), (10);

--- 6 row(s) inserted.
>>select a from t001asc where a < 5 order by a DESC;

A                   
--------------------

                   3
                   2
                   1

--- 3 row(s) selected.
>>
>>------------------------------
>>
>>-- This next bit works if Debug, fails if Release;
>>-- and the results (char_length of E) will change after MX-NSK-Rel1,
>>-- when we correctly support KANJI as double-byte instead of single.
>>--
>>-- For now, don't run these tests for MX objects on NSK platform.  It is too 
>>-- difficult to create all the different known results for release, debug, 
>>-- MX format objects, etc. (rsm)
>>
>>-- comment out because of the behavior change to NATIONAL_CHARSET in R2.
>>-- It is a read-only CQD.
>>--#ifNT
>>--  control query default NATIONAL_CHARSET 'kanji';
>>--  set envvar NCHAR_DEBUG;
>>--  create table t001tn2(a int, b char(9), c int, d char(4) COLLATE SJIS,
>>--		       e NCHAR(4));
>>--  create view  t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2;
>>--  reset envvar NCHAR_DEBUG;
>>--  invoke t001vn;
>>--  invoke t001tn2;
>>--  table  t001vn;
>>--  select * from t001tn2 where b=e;	-- not comparable
>>--  create view  t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2;
>>--  select e collate sjis from t001tn2;
>>--  set envvar NCHAR_DEBUG 2;
>>--  create view  t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2;	-- 3179
>>--  select e collate sjis from t001tn2;					-- 3179
>>--  reset envvar NCHAR_DEBUG;
>>--  update t001tn2 set e =      _kanji'km' collate SJIS || e;
>>--  update t001tn2 set e = e || _kanji'km' collate SJISfoo;
>>--  control query default NATIONAL_CHARSET 'reset';
>>--#ifNT
>>--
>>--#ifMP
>>--  control query default NATIONAL_CHARSET 'kanji';
>>--  set envvar NCHAR_DEBUG;
>>--  create table t001tn2(a int, b char(9), c int, d char(4) COLLATE SJIS,
>>--		       e NCHAR(4));
>>--  create view  t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2;
>>--  reset envvar NCHAR_DEBUG;
>>--  invoke t001vn;
>>--  invoke t001tn2;
>>--  table  t001vn;
>>--  select * from t001tn2 where b=e;	-- not comparable
>>--  create view  t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2;
>>--  select e collate sjis from t001tn2;
>>--  set envvar NCHAR_DEBUG 2;
>>--  create view  t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2;	-- 3179
>>--  select e collate sjis from t001tn2;					-- 3179
>>--  reset envvar NCHAR_DEBUG;
>>--  update t001tn2 set e =      _kanji'km' collate SJIS || e;
>>--  update t001tn2 set e = e || _kanji'km' collate SJISfoo;
>>--  control query default NATIONAL_CHARSET 'reset';
>>--#ifMP
>>
>>------------------------------
>>
>>log;
