-- Test: TEST061 (CORE)
-- @@@ 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 @@@
--
-- Functionality: Tests several Genesis cases related to Parser/Binder, Catman
--                and some Unicode features for NT
-- Expected files: EXPECTED061
-- Limitations: This test is run for MX only.
-- Revision history:
--     (01/23/03): This test is created from earlier TEST061 of core. 
--                 The earlier test061 created 63 tables. That has been 
--                 cleaned up. The tests that were testing only parsing/binding
--                 features were moved into another test (TEST061 in COMPGENERAL
--                 test suite). Some tables are now being reused.


-- This test consists of those parts of TEST061 that should go into core
#ifNT
control query default risk_premium_serial '1.0';
#ifNT
set schema $$TEST_SCHEMA$$;

obey TEST061(clnupmxonly);

log LOG061 clear;

obey TEST061(mxonly);

obey TEST061(clnupmxonly);
log;
exit;

?section mxonly

-- test genesis case 10-980520-2421 and genesis case 10-980527-5980
create table t2421
(a pic 9v, b picture v9, c pic s99(09)v9(08), d pic s9v9, e pic sv9, f pic s9v,
 h pic 99v9);
invoke t2421;
insert into t2421 values(0,0.1,2,3,0.4,5,1.23);
select * from t2421;


-- picture variant (from Ken Luu's catman/test101)
create table  t101t2 ( c01 picture XX
                     , c02 picture X(2)
                     , c12 pic XXXXXXXXXXXXXXXXXXXXX
                     , c13 pic X(21)
                     );
invoke        t101t2;
showddl       t101t2;
insert into   t101t2 values ('ab',  '4', 'abcdefghijklmnopqrstu', '1'),
                            ('21',  'A', '123456789012345678901', 'B');
select * from t101t2;


-- the following (taken from catman/test104) broke as we were trying to fix
-- the NCHAR default value bug; reproduced here to avoid breaking it again.
create table  t104t2 (c char(15) not null, d int not null,
                      primary key (c, d))
  range partition (add first key ('Mickey''s', 2) location $DATA,
                   add first key ('sad"story', 10) location $DATA1);
invoke t104t2;
showddl t104t2;


#ifNT
-- test genesis case: 10-980515-9563
-- It tests the result of natural joins 

create table BTlocal5
(char2_2             NChar(2)               not null,
 varchar3_4          NChar Varying(8)            not null,
 sbinneg15_nuniq     Largeint);

create table BTlocal5c
(char2_2             NChar(2)               not null,
 varchar3_4          NChar Varying(8)            not null,
 sbinneg15_nuniq     Largeint);

Insert Into BTlocal5 Values ( N'AA',  N'BA',  -4344);
Insert Into BTlocal5 Values ( N'AA', N'BAA',  -4344);
Insert Into BTlocal5 Values ( N'BA', N'AA',   -3552);
Insert Into BTlocal5 Values ( N'BA', N'CA',   -2389);
Insert Into BTlocal5 Values ( N'DA', N'CA',   -2789);
Insert Into BTlocal5 Values ( N'EA', N'DA',   -1950);

Insert Into BTlocal5c select * from BTlocal5;

create view  VNlocal5 ( n1, c2, c4 ) as
select max(BTlocal5.sbinneg15_nuniq) , min(BTlocal5.char2_2)
     , max(BTlocal5c.varchar3_4)
from BTlocal5
left  join BTlocal5c
on BTlocal5.char2_2   = BTlocal5c.varchar3_4
group by BTlocal5.sbinneg15_nuniq, BTlocal5.char2_2, BTlocal5c.varchar3_4;

select * from VNlocal5 order by 1,2,3;

select n1,c2,c4 from VNlocal5 natural join VNlocal5 t order by n1,c2;
--end


-- test genesis cases: 10-980508-4495, 10-980901-0430 and 10-980512-6420

create table t4495 (a int, b nchar varying(15));
insert into t4495 values (100, N'abcdefgh');
insert into t4495 values (200, N'defgh');
insert into t4495 values (300, N'abc');

create view vut4495 (inta, varchar20)
   as select * from t4495 where a > 200
   union select * from t4495 where a< 150;

-- These give an error 1127 - The specified table is not a base table.

create index  t061i1 on vut4495(a);

create table t061t33(t061t3_c int references vut4495);
create table t061t4(t061t4_c int references vut4495(a));
create table t061t5(t061t5_c int, foreign key(t061t5_c) references vut4495);
create table t061t6(t061t6_c int, foreign key(t061t6_c) references vut4495(a));

select * from vut4495;

-- used to get an error: "operands of the LIKE predicate must be character"

select varchar20 from vut4495 where varchar20 like N'%a%';

-- test genesis case: 10-980508-4452
-- comment out because view text can not contain ucs2 string literals.
--create view vut4495b as select * from t4495 where b < N'bbb';
--select * from vut4495b;
--showddl vut4495b;

set param ?p_param _ucs2' bcDEFg8i ';
select substring((b || ?p_param ) from 2) as From2 from t4495;

-- the original bug was an assertion failure here

-- this is OK

select substring((b || N' bcDEFg8i ') from 2) as From2 from t4495;


-- not OK: opds with different char sets are illegal
         select substring((b || ' bcDEFg8i ') from 2) as From2 from t4495;

-- set param is a sqlci command and it does not know about N'strings'
set param ?DoYouKnowTheWayToSanJose N'otAchance';

#ifNT

-- test genesis case: 10-980603-2116
-- It is used to test to select data from a view created with 
-- sum(distinct...) function
 
CREATE TABLE t2116t1 (
  pic_x_7                PIC X(7)          not null
, binary_signed          numeric (4, 0) signed not null
, binary_64_s            numeric (18,3) SIGNED not null
, PRIMARY KEY (binary_signed) );

CREATE TABLE t2116t3 (
  pic_x_7        PIC X(7)            not null
, binary_64_s    numeric (18,2) SIGNED      not null );

CREATE VIEW t2116v1 (col_1)
AS SELECT sum (distinct t2116t1.binary_signed)
   FROM t2116t1, t2116t3
   WHERE t2116t1.binary_64_s > t2116t3.binary_64_s AND
       ( t2116t1.pic_x_7 <> t2116t3.pic_x_7 );

insert into t2116t1 values ('walter',50,200);
insert into t2116t1 values ('9',8000,2000);
insert into t2116t1 values ('michael',-5000,2000);
insert into t2116t1 values ('jimmy',3000,2000);
insert into t2116t3 values ('A',200);
insert into t2116t3 values ('7',1200);
insert into t2116t3 values ('5',1000);
insert into t2116t3 values ('michael',1500);
insert into t2116t3 values ('7',2000);
insert into t2116t3 values ('B',3000);
insert into t2116t3 values ('michael',4000);

select * from t2116v1;

?section clnupmxonly
drop table t2421;
drop table t101t2;
drop table t104t2;
drop view  VNlocal5;
drop table BTlocal5;
drop table BTlocal5c;
drop view vut4495;
--drop view vut4495b;
drop table t4495;
drop view t2116v1;
drop table t2116t3;
drop table t2116t1;

