-- Test: TEST002 (Executor)
-- @@@ 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: Union all with joins and aggregates, various joins,
--                subqueries with aggregates, correlated subqueries.
-- Expected files: EXPECTED002, EXPECTED002.MP
-- Table created: t002t1, t002t2, t002t3, t002temp, t002tmp2, t002t5,
--                t002t6, t002t7, t002t8, t002t9, t002t10, t002tab2,
--                t002vt, t002ZZ, t002ZZI, t002FU, t002FUI,
--                t002ut1, t002ut2, t002ut3, t002utemp, t002utmp2
-- View Created: t002v
-- Limitations:
-- To Do: - Table t002vt is not created in ifMX case, but is to be dropped.
--        - Remove DIFF002.KNOWN.NSK when Unicode is supported on MX tables
--          while Unicode may never be supported on MP tables
--          (Done 5/19/02)
-- Revision history:
--     (1/28/02) - Copied from fullstack/TEST002
--     (2/18/02) - Renamed tables ZZ, ZZI, FU, FUI to t002ZZ, t002ZZI,
--                 t002FU, t002FUI
--     (2/19/02) - Merged in fullstack/TEST002U
--     (2/20/02) - Moved most subquery tests to TEST002 (Core)
--     (3/04/02) - Comment off Unicode tests for MP tables
--     (5/19/02) - Removed DIFF002.KNOWN.NSK as unicode is supported.
control query default POS 'OFF';
control query default ATTEMPT_ESP_PARALLELISM 'OFF';

?section ddl

obey TEST002(clnup);

log LOG002 clear;

create table t002t1 (a int, b char(9), c int, d char(4));
create table t002t2 (a int not null, b char(9), c int, d char(4), primary key (a));
create table t002t3 (a int not null, b char(9) not null, c int, d char(4), primary key (a, b));
create table t002temp (a int, b char(9), c int, d char(4));
create table t002tmp2 (a int);

create table t002t5(a int not null, b int);
create table t002t6(c int not null, d int, primary key(c));
create table t002t7 ( col1 int );
create table t002t8 ( col2 int );
create table t002t9 ( col3 int );
create table t002t10 ( col4 int );
create table t002tab2 (char_1 CHAR(1),
                       numeric_1 NUMERIC(4, 0));

-- added for JIRA TRAFODION-2843
Create table D03s
                (
                pk int not null not droppable primary key
                , val01 int
                , val02 int
                );

Create table F01s
                (
                  pk int not null not droppable primary key
                , fk_d01 int not null
                , fk_d02 int not null
                , fk_d03 int not null
                , fk_d04 int not null
                , fk_d05 int not null
                , fk_d06 int not null
                , fk_d07 int not null
                , fk_d08 int not null
                , fk_d09 int not null
                , fk_d10 int not null
                , val01 int
                , val02 int
                , val01_d01 int
                , val02_d01 int
                , val01_d02 int
                , val02_d02 int
                , val01_d03 int
                , val02_d03 int
                ) salt using 8 partitions;

?section Genesis_10_970911_6859
?ifMX
create view t002v(w,x) as values(1,11);	-- should work
?ifMX
?ifMP
create table t002vt (w smallint not null, x smallint not null);
insert into t002vt values (1,11);
create view t002v(w,x) as select * from t002vt;
?ifMP
table t002v;				-- should work
update t002v set w=0;			-- better fail (error 4028)!

#ifMX
create table t002ut1 (a int, b nchar(9), c int, d nchar(4));
create table t002ut2 (a int not null, b nchar(9), c int, d nchar(4), primary key (a));
create table t002ut3 (a int not null, b nchar(9) not null, c int, d nchar(4), primary key (a, b));
create table t002utemp (a int, b nchar(9), c int, d nchar(4));
create table t002utmp2 (a int);
#ifMX

-- To test solution 10-090107-8249
create table t002_oja(a int NOT NULL, PRIMARY key (a)); 
create table t002_ojb(b int NOT NULL, PRIMARY key (b)); 
create table t002_ojc(c int NOT NULL, PRIMARY key (c)); 
create table t002_ojd(e int NOT NULL, PRIMARY key (e)); 
create view t002_vex(x) as 
  select * from t002_ojd where e = 1 ; 
create view t002_valb(p,q) as 
  select a,b from t002_oja left join t002_ojb on a = b ; 
create view t002_valbic(p,q) as 
  select c,V.q from t002_valb V inner join t002_ojc on c = V.p ;

create table t002_oj1 (a int)  ;
create table t002_oj2 (b int)  ;

-- For Genesis Soln 10-100706-1612 
 create table t002_outer_nlj (i int not null, j int not null);
 
 create table t002_outer_lower_hj like t002_outer_nlj;  

 create table t002_inner_lower_hj like t002_outer_nlj;

 create table t002_inner_upper_hj like t002_outer_nlj;
-- the table t002_inner_upper_hj should be empty to cause the upper HJ 
-- to issue a cancel to its left HJ child


?section dml
-- INSERT queries
insert into t002t1 values (10, 'abc', 20, 'xy');
insert into t002t1(b,d,a,c) values ('defg', 'wx', 10+10, 30);
insert into t002t2 select * from t002t1;
insert into t002t3(a,b,c,d) select a,b,c,d from t002t2;
insert into t002t5 values (1, null), (2,0);
insert into t002t6 values (-1,2), (3,4), (5,6), (7, null);
insert into t002t7 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
insert into t002t8 values(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21);
insert into t002t9 values(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31);
insert into t002t10 values(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41);
insert into t002tab2 values('A', 1000), ('B',2000);

#ifMX
insert into t002ut1 values (10, N'abc', 20, N'xy');
insert into t002ut1(b,d,a,c) values (N'defg', N'wx', 10+10, 30);
insert into t002ut2 select * from t002ut1;
insert into t002ut3(a,b,c,d) select a,b,c,d from t002ut2;
#ifMX

-- UNION ALL
select * from t002t1 union all select * from t002t1;

select * from t002t1 union all
select * from t002t1 union all
select * from t002t2;

select a+1 from t002t1 union all select a from t002t1;

select * from (select * from t002t1 union all select * from t002t1) x;

select * from (values (1,2), (3,4)) x;

#ifMX
select * from t002ut1 union all select * from t002ut1;

select * from t002ut1 union all
select * from t002ut1 union all
select * from t002ut2;

select * from (select * from t002ut1 union all select * from t002ut1) x;
#ifMX

-- INSERT with multiple tuples
insert into t002t3 values (30, 'abc', 20, 'xy'), (30+10, 'defg', 30, 'wx');
select * from t002t3;

select * from t002t1 union all
select * from t002t2 union all
select * from t002t3;

select * from t002t1 union all (select * from t002t2 union all select * from t002t3);

#ifMX
insert into t002ut3 values (30, N'abc', 20, N'xy'), (30+10, N'defg', 30, N'wx');
select * from t002ut3;

select * from t002ut1 union all
select * from t002ut2 union all
select * from t002ut3;

select * from t002ut1 union all (select * from t002ut2 union all select * from t002ut3);
#ifMX

-- INSERT with UNION
delete from t002temp;
insert into t002temp
select * from t002t1 union all
select * from t002t2 union all
select * from t002t3;
select * from t002temp;

delete from t002tmp2;
insert into t002tmp2
select count(*) from
(select * from t002t1 join t002t2 on t002t1.a = t002t2.a
 union all
 select * from t002t1 join t002t2 on t002t1.a = t002t2.a) x;
select * from t002tmp2;

#ifMX
delete from t002utemp;
insert into t002utemp
select * from t002ut1 union all
select * from t002ut2 union all
select * from t002ut3;
select * from t002utemp;

delete from t002utmp2;
insert into t002utmp2
select count(*) from
(select * from t002ut1 join t002ut2 on t002ut1.a = t002ut2.a
 union all
 select * from t002ut1 join t002ut2 on t002ut1.a = t002ut2.a) x;
select * from t002utmp2;
#ifMX

-- AGGREGATEs with UNION
select count(*) from
(select * from t002t1 union all
 select * from t002t2 union all
 select * from t002t3) x;

select count(*) from t002t1 union all select count(*) from t002t2;

select count(*) from (select count(*) from t002t1 union all select count(*) from t002t2) x;

select count(*) from t002t1 union all 
select count(*) from (select count(*) from t002t1 union all select count(*) from t002t2) x;

#ifMX
select count(*) from
(select * from t002ut1 union all
 select * from t002ut2 union all
 select * from t002ut3) x;

select count(*) from t002ut1 union all select count(*) from t002ut2;

select count(*) from (select count(*) from t002ut1 union all select count(*) from t002ut2) x;

select count(*) from t002ut1 union all 
select count(*) from (select count(*) from t002ut1 union all select count(*) from t002ut2) x;
#ifMX

-- JOINs and UNIONs
select * from t002t1 join t002t2 on t002t1.a = t002t2.a
union all
select * from t002t1 join t002t2 on t002t1.a = t002t2.a;

-- All together now
select count(*) from
(select * from t002t1 join t002t2 on t002t1.a = t002t2.a
 union all
 select * from t002t1 join t002t2 on t002t1.a = t002t2.a) x;

select 1 + count(*) from
(select count(*) from (select * from t002t1) x natural join (select * from t002t2) y
 union all
 select count(*) from t002t1 join t002t2 on t002t1.a = t002t2.a) z;

delete from t002temp;


select * from t002temp;

#ifMX
select 1 + count(*) from
(select count(*) from (select * from t002ut1) x natural join (select * from t002ut2) y
 union all
 select count(*) from t002ut1 join t002ut2 on t002ut1.a = t002ut2.a) z;

delete from t002utemp;


select * from t002utemp;
#ifMX


-- UNION
select a from t002t1 union select a from t002t1;
select a from t002t1 union select a from t002t1 union select a from t002t1;
select a from t002t1 union select a from t002t1 union select a from t002t1
     union select a from t002t1;

-- UNION and UNION ALL which returns a relatively large number of rows.
select * from t002t7
UNION
((select *
 from t002t8
 UNION all
 Select *
 from t002t9
)
UNION all
select *
from t002t10)
order by 1;


-- Join a null column (b) with a null (d) or not null column (c).
select * from t002t6 where c is null;
select * from t002t5 left join t002t6 on b < c;
select * from t002t5 inner join t002t6 on b < c;
select * from t002t5 left join t002t6 on c < b;
select * from t002t5 inner join t002t6 on c < b;

-- Same join's but null instantiation works differently.
select * from t002t5 left join t002t6 on b > c;
select * from t002t5 inner join t002t6 on b > c;
select * from t002t5 left join t002t6 on c > b;
select * from t002t5 inner join t002t6 on c > b;

select * from t002t5 left join t002t6 on b+1 <= d;
select * from t002t5 inner join t002t6 on b+1 <= d;

select * from
(select * from (select * from (values (1)) x)y
natural join
(select * from (values (1)) xx)yy) z;

select * from (values (1), (2)) x natural join (select a from t002t1) y;

select a,sum(b) from (select * from (values (1), (2)) x
 natural join (select a from t002t1)z) y(a,b) group by a; 

-- CR 10-010308-1591
select char_1, numeric_1  
from t002tab2          
group by char_1, numeric_1 
having  (char_1, numeric_1) in (  values ('A', 1000), ('F', 3000), (char_1,
2000));

-- Error 15001 expected:
select /*this is here to return a syntax error*/ sum(a),sum(b) from t002t1;

-- Error 15001 expected:
select a,sum(b) from
(
  (select * from
    (select * from 
	(select a,sum(a) from t002t1 group by a) x(a,b)
	union 
 	select * from (select a,sum(a) from t002t1 group by a) x(a,b)
    ) w(a,b)
    join
    (select a,sum(a) from t002t1 group by a) y(a,b)
  )
  on w.a = y.a
) z(a,b,c,d)
group by a having a > 1 and sum(b) = 20;

-- Legal syntax, expect 1 row (20,20):
select a,sum(b) from
(
  (select * from
    (select * from 
	(select a,sum(a) from t002t1 group by a) x(a,b)
	union 
 	select * from (select a,sum(a) from t002t1 group by a) x(a,b)
    ) w(a,b)
    join
    (select a,sum(a) from t002t1 group by a) y(a,b)
    on w.a = y.a
  )
) z(a,b,c,d)
group by a having a > 1 and sum(b) = 20;

-- Column B not found
select a,sum(b) from
(select a,b from
 (select a,sum(a) from t002t1 group by a) x natural join
 (select a,sum(a) from t002t1 group by a) y) z(a,b,c,d)
		group by a;

-- Number of columns mismatch
select a,sum(b) from
(select * from
 (select a,sum(a) from t002t1 group by a) x natural join
 (select a,sum(a) from t002t1 group by a) y) z(a,b,c,d)
		group by a;

-- This should work
select a,sum(b) from
(select * from
 (select a,sum(a) from t002t1 group by a) x natural join
 (select a,sum(a) from t002t1 group by a) y) z(a,b,c)
		group by a;

-- This should work
#ifMX
select a,sum(b) from
(select * from
 (select a,sum(a) from t002ut1 group by a) x natural join
 (select a,sum(a) from t002ut1 group by a) y) z(a,b,c)
		group by a;
#ifMX


-- This should work because x.* expands to unnamed column sum(a)
select x.* from
 (select a,sum(a) from t002t1 group by a) x natural join
 (select a,sum(a) from t002t1 group by a) y;

-- This should NOT work because x.* expands to zero columns
select x.* from
 (select a from t002t1 group by a) x natural join
 (select a from t002t1 group by a) y;

select *, sum(a) from t002t1;			-- err 4012
select sum(a), * from t002t1;			-- err 4012
select *, sum(a) from t002t1 having 1=1;	-- err 4012
select *, sum(a) from t002t1 group by b;	-- err 4012
select *, sum(a) from t002t1 group by b,c,a,d;	-- ok
select    sum(a) from t002t1;			-- ok
select    sum(a) from t002t1 having 1=1;	-- ok
select    sum(a) from t002t1 group by b;	-- ok
--
--

-- The select list contains a non-grouping non-aggregated column.
SELECT A,MAX(A) FROM T002T1;
-- Ok
SELECT A,MAX(A) FROM T002T1 GROUP BY A;           
-- The column ref A must be a grouping column or be in an aggregate.
SELECT A,MAX(A) FROM T002T1 GROUP BY B;
--
--

-- The select list contains a non-grouping non-aggregated column.
SELECT A,COUNT(*) FROM T002T1;
-- Ok
SELECT A,COUNT(*) FROM T002T1 GROUP BY A;           
-- The column ref A must be a grouping column or be in an aggregate.
SELECT A,COUNT(*) FROM T002T1 GROUP BY B;
--
--

-- The select list contains a non-grouping non-aggregated column.
SELECT (SELECT A FROM T002T2 WHERE OUTT.A > A AND MAX(OUTT.B)>'C') FROM T002T1 OUTT;
-- The column ref OUTT.A must be a grouping column or be in an aggregate.
SELECT (SELECT A FROM T002T2 WHERE OUTT.A > A AND MAX(OUTT.B)>'C') FROM T002T1 OUTT
GROUP BY OUTT.B;
--
--

-- Soln: 10-040609-6809 -- A MapValueId node on right of a merge join where
-- MVI introduces Cast for one of the Join Preds.

#ifdef SEABASE_REGRESS
control query shape 
merge_join(cut,sort(sort_groupby(cut)));
#else
control query shape 
merge_join(cut,
           mvi(sort(sort_groupby(pa(groupby(cut))))));
#endif


select * 
from t002t3 t1, 
     (select count(*) from t002t3 group by a) t2(a)
where t1.a = t2.a;

control query shape cut;

-- Soln: 10-040317-4281 -- A MapValueId node on right of a merge join where
-- one of the projected values is a count that has been replaced with Cast(1).

control query shape merge_join(cut,cut);

select cnt
from (select a from t002t2) t1,
     (select a, count(*) as cnt from t002t2 group by a) t2
where t1.a = t2.a
;

control query shape cut;

-- Three queries to test solution 10-090107-8249
-- all have left outer join that returns nothing from the inner
insert into t002_oja values(2);
insert into t002_ojb values(2); 
insert into t002_ojc values(2); 
insert into t002_ojd values(1); 

control query shape hybrid_hash_join(hybrid_hash_join(hybrid_hash_join(cut,
 cut),cut),cut);

select * from t002_valbic X left join t002_vex Y on X.p = Y.x ;

control query shape cut;

--
insert into t002_oj1 values (1);
insert into t002_oj2 values (2);

control query shape hybrid_hash_join(cut,cut);

select * from t002_oj1
    left join t002_oj2 on b = 2;

control query shape cut;

--
delete from t002_oj1;
delete from t002_oj2;
insert into t002_oj1 values (2) ;
insert into t002_oj2 select * from t002_oj1 where a < 3 ;

control query shape hybrid_hash_join(cut,  
          sort_groupby(hybrid_hash_join(cut,cut)));

select b,
   ( select a from t002_oj1
       right outer join t002_oj2
         on a = b
       where b = 2
   ) from t002_oj2 ;

-- also try w/o pcode
control query default PCODE_OPT_LEVEL 'OFF';

select b,
   ( select a from t002_oj1
       right outer join t002_oj2
         on a = b
       where b = 2
   ) from t002_oj2 ;

control query default PCODE_OPT_LEVEL reset;

control query shape cut;

-- Test Genesis Case 10-090127-0322
-- Cancel Hash Join during Phase1, Reuse on, DelayedLeftRequest.

control query default HJ_TYPE 'ORDERED';
control query shape nested_join(cut,cut);

-- Used to hang
SELECT T1.a FROM t002t1 AS t1  
WHERE EXISTS ( 
    SELECT -74 FROM t002t1 t2 
    UNION all
    SELECT -84 FROM t002t2 t2 
    WHERE NOT ( ( -51 = T1.c )
      OR ( EXISTS (SELECT 1 FROM t002t2 T4, t002t3 t5 
    ) ) ) 
    GROUP BY T2.b
);

-- Used to hang
SELECT T1.a FROM t002t1 AS t1  
WHERE EXISTS ( 
    SELECT -74 FROM t002t1 t2  where t1.c = 30
    UNION all
    SELECT -84 FROM t002t2 t2 
    WHERE NOT ( ( -51 = T1.c )
      OR ( EXISTS (SELECT 1 FROM t002t2 T4, t002t3 t5 
    ) ) ) 
    GROUP BY T2.b
);

control query shape cut;
insert into t002t1 values(30, 'cdef', 40, 'yy');
insert into t002t1 values(40, 'dst', 50, 'zz');
control query shape nested_join(cut,cut);

-- Used to hang
SELECT T1.a FROM t002t1 AS t1  
WHERE EXISTS ( 
    SELECT -74 FROM t002t1 t2  where t1.c = 40
    UNION all
    SELECT -84 FROM t002t2 t2 
    WHERE NOT ( ( -51 = T1.c )
      OR ( EXISTS (SELECT 1 FROM t002t2 T4, t002t3 t5 
    ) ) ) 
    GROUP BY T2.b
);

control query default HJ_TYPE reset;
control query shape cut;
delete from t002t1 where a >= 30;

-- END Test Genesis Case 10-090127-0322


-- Test Genesis Soln 10-100706-1612 
-- Cancel of Hash-Join during phase1, with reuse, causes a hang

-- repeat same values
insert into t002_outer_nlj values (1,1),(1,1),(1,1),(1,1),(1,1); 

insert into t002_outer_lower_hj values (2,2),(2,2),(2,2),(2,2); -- just values

-- t002_inner_upper_hj should be empty to cause the upper HJ to issue a cancel to its left child

-- Make t002_inner_lower_hj big (to keep the lower HJ busy in phase 1, till 
-- cancel comes).   Insert 100,000 rows into t002_inner_lower_hj
#ifdef SEABASE_REGRESS
upsert using load into
#else
insert into
#endif
t002_inner_lower_hj
    select x1 + x2*10 + x3*100 + x4*1000 + x5*10000,
       case when x5 < 5 then 333 else 444 end
    from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) T(x5)
      transpose 0,1,2,3,4,5,6,7,8,9 as x4
      transpose 0,1,2,3,4,5,6,7,8,9 as x3
      transpose 0,1,2,3,4,5,6,7,8,9 as x2
      transpose 0,1,2,3,4,5,6,7,8,9 as x1
      ;

cqd HJ_TYPE 'ORDERED';
cqd NESTED_JOIN_CACHE 'OFF';
cqd JOIN_ORDER_BY_USER 'ON';

control query shape nested_join(cut,ordered_hash_join(
 ordered_hash_join(cut,cut), cut));

-- used to hang
select * from t002_outer_nlj where t002_outer_nlj.i in
 (select t002_inner_lower_hj.i from 
     t002_outer_lower_hj,t002_inner_lower_hj,t002_inner_upper_hj 
        where t002_outer_lower_hj.i=t002_inner_lower_hj.i 
          and t002_inner_lower_hj.j=t002_inner_upper_hj.j);

control query shape cut;
cqd HJ_TYPE reset;
cqd NESTED_JOIN_CACHE reset;
cqd JOIN_ORDER_BY_USER reset;

-- END Test Genesis Soln 10-100706-1612 


-- NULL Instantiation
-- Instantiate a large (> 4K) row.
-- See Genesis Solution: 10-090922-4766

insert into t002t6 values (10,20), (20,30);

control query shape hhj(cut,cut);
select t002t6.c, character_length(T.b), T.a+10
from t002t6
left outer join (
  select b||b||b as b,a from (
  select b||b||b||b as b,a from (
  select b||b||b||b as b,a from (
  select b||b||b||b as b,a from (
  select b||b||b||b as b,a from (
  select b||b||b||b as b,a as a from t002t2
  ) T) T) T) T) T) T
on (t002t6.c = T.a)
;
control query shape nested_join(cut,cut);
select t002t6.c, character_length(T.b), T.a+10
from t002t6
left outer join (
  select b||b||b as b,a from (
  select b||b||b||b as b,a from (
  select b||b||b||b as b,a from (
  select b||b||b||b as b,a from (
  select b||b||b||b as b,a from (
  select b||b||b||b as b,a as a from t002t2
  ) T) T) T) T) T) T
on (t002t6.c = T.a)
;

control query shape merge_join(cut,cut);
cqd PCODE_OPT_LEVEL '0';
select t002t6.c, character_length(T.b), T.a+10
from t002t6
left outer join (
  select b||b||b as b,a from (
  select b||b||b||b as b,a from (
  select b||b||b||b as b,a from (
  select b||b||b||b as b,a from (
  select b||b||b||b as b,a from (
  select b||b||b||b as b,a as a from t002t2
  ) T) T) T) T) T) T
on (t002t6.c = T.a)
;
cqd PCODE_OPT_LEVEL reset;
control query shape cut;

-- Test Outer Join with many (all) rows are null instanitated.
-- Test for Hash, Nested and Merge
control query shape groupby(hhj(cut,cut));
select sum(t.x1), count(t002t2.b), sum(t002t2.c)
from (select x1
      from (values(0)) T
      transpose 0,1,2,3,4,5,6,7,8,9 as x5
      transpose 0,1,2,3,4,5,6,7,8,9 as x4
      transpose 0,1,2,3,4,5,6,7,8,9 as x3
      transpose 0,1,2,3,4,5,6,7,8,9 as x2
      transpose 0,1,2,3,4,5,6,7,8,9 as x1) T
left outer join t002t2
on (t.x1 = t002t2.a);

select count(*), count(t.x1), sum(t.x1), count(t002t2.b), sum(t002t2.c)
from (select x1
      from (values(0)) T
      transpose 0,1,2,3,4,5,6,7,8,9 as x5
      transpose 0,1,2,3,4,5,6,7,8,9 as x4
      transpose 0,1,2,3,4,5,6,7,8,9 as x3
      transpose 0,1,2,3,4,5,6,7,8,9 as x2
      transpose 0,1,2,3,4,5,6,7,8,9 as x1) T
full outer join t002t2
on (t.x1 = t002t2.a);

control query shape groupby(nested_join(cut,cut));
select sum(t.x1), count(t002t2.b), sum(t002t2.c)
from (select x1
      from (values(0)) T
      transpose 0,1,2,3,4,5,6,7,8,9 as x5
      transpose 0,1,2,3,4,5,6,7,8,9 as x4
      transpose 0,1,2,3,4,5,6,7,8,9 as x3
      transpose 0,1,2,3,4,5,6,7,8,9 as x2
      transpose 0,1,2,3,4,5,6,7,8,9 as x1) T
left outer join t002t2
on (t.x1 = t002t2.a);

control query shape groupby(merge_join(cut,cut));
select sum(t.x1), count(t002t2.b), sum(t002t2.c)
from (select x1
      from (values(0)) T
      transpose 0,1,2,3,4,5,6,7,8,9 as x5
      transpose 0,1,2,3,4,5,6,7,8,9 as x4
      transpose 0,1,2,3,4,5,6,7,8,9 as x3
      transpose 0,1,2,3,4,5,6,7,8,9 as x2
      transpose 0,1,2,3,4,5,6,7,8,9 as x1) T
left outer join t002t2
on (t.x1 = t002t2.a);

-- Test Outer Join with many (most) rows are null instanitated.
-- Test for Hash, Nested and Merge

control query shape groupby(hhj(cut,cut));
select sum(t.x1), count(t002t2.b), sum(t002t2.c)
from (select x1
      from (values(0)) T
      transpose 0,1,2,3,4,5,6,7,8,9 as x5
      transpose 0,1,2,3,4,5,6,7,8,9 as x4
      transpose 0,1,2,3,4,5,6,7,8,9 as x3
      transpose 0,1,2,3,4,5,6,7,8,9 as x2
      transpose 10,1,20,3,4,5,6,7,8,9 as x1) T
left outer join t002t2
on (t.x1 = t002t2.a);

control query shape groupby(nested_join(cut,cut));
select sum(t.x1), count(t002t2.b), sum(t002t2.c)
from (select x1
      from (values(0)) T
      transpose 0,1,2,3,4,5,6,7,8,9 as x5
      transpose 0,1,2,3,4,5,6,7,8,9 as x4
      transpose 0,1,2,3,4,5,6,7,8,9 as x3
      transpose 0,1,2,3,4,5,6,7,8,9 as x2
      transpose 10,1,20,3,4,5,6,7,8,9 as x1) T
left outer join t002t2
on (t.x1 = t002t2.a);

control query shape groupby(merge_join(cut,cut));
select sum(t.x1), count(t002t2.b), sum(t002t2.c)
from (select x1
      from (values(0)) T
      transpose 0,1,2,3,4,5,6,7,8,9 as x5
      transpose 0,1,2,3,4,5,6,7,8,9 as x4
      transpose 0,1,2,3,4,5,6,7,8,9 as x3
      transpose 0,1,2,3,4,5,6,7,8,9 as x2
      transpose 10,1,20,3,4,5,6,7,8,9 as x1) T
left outer join t002t2
on (t.x1 = t002t2.a);

control query shape cut;


-- Test for multi outer joins.
-- Populate tables with regular data.
--
insert into t002_oja
select x1 + x2*10 + x3*100 + 10
from (values(0)) T
transpose 0,1,2,3,4,5,6,7,8,9 as x3
transpose 0,1,2,3,4,5,6,7,8,9 as x2
transpose 0,1,2,3,4,5,6,7,8,9 as x1;

insert into t002_ojb
select (x1 + x2*10 + x3*100 + 10) * 2
from (values(0)) T
transpose 0,1,2,3,4,5,6,7,8,9 as x3
transpose 0,1,2,3,4,5,6,7,8,9 as x2
transpose 0,1,2,3,4,5,6,7,8,9 as x1;

insert into t002_ojc
select (x1 + x2*10 + x3*100 + 10) * 3
from (values(0)) T
transpose 0,1,2,3,4,5,6,7,8,9 as x3
transpose 0,1,2,3,4,5,6,7,8,9 as x2
transpose 0,1,2,3,4,5,6,7,8,9 as x1;

insert into t002_ojd
select (x1 + x2*10 + x3*100 + 10) * 4
from (values(0)) T
transpose 0,1,2,3,4,5,6,7,8,9 as x3
transpose 0,1,2,3,4,5,6,7,8,9 as x2
transpose 0,1,2,3,4,5,6,7,8,9 as x1;

-- Test with Hash, Nested and Merge.
control query default hash_joins reset;
control query default nested_joins 'off';
control query default merge_joins 'off';
select * from t002_oja A
   left join t002_ojb B
    on A.a = B.b
   left join t002_ojc C
   on (A.a = C.c) 
  left join t002_ojd D
 on A.a = D.e ;

-- With an AfterJoinPred
select * from t002_oja A
   left join t002_ojb B
    on A.a = B.b
   left join t002_ojc C
   on (A.a = C.c) 
  left join t002_ojd D
 on A.a = D.e 
where A.a < 100 and B.b is null and C.c is null and D.e is null;

-- Full Outer, With an AfterJoinPred
select * from (select * from t002_oja where a < 100) A
   full outer join (select * from t002_ojb where b < 100) B
    on A.a = B.b
    full outer join (select * from t002_ojc where c < 100) C
   on (A.a = C.c) 
  full outer join (select * from t002_ojd where e < 100) D
 on A.a = D.e 
where A.a is null or (B.b is null and C.c is null and D.e is null);

-- FOJ with beforepred, at least one outer row w/o a match
select count(*) from (
select T1.*
from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1
      full outer join
     (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2
      on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < T1.c)
) C
;

-- FOJ with afterjoinpred, at least one outer row w/o a match
select count(*) from (
select T1.*
from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1
      full outer join
     (select *, cast('Hello' as char(512)) b from t002_oja) T2
      on (T1.a-10 = T2.a and T1.b = T2.b)
where T1.c < 5) C
;

-- FOJ with afterjoinpred and beforepred, at least one outer row w/o a match
select count(*) from (
select T1.*
from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1
      full outer join
     (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2
      on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < 10)
where T1.c < 5) C
;

-- FOJ with constant projected from outer side.
Select  T1.a, T1.c
from (
   Select 79 c, a
   from t002_oja
   where a < 20
 ) T1
full join (
   Select *
   from t002_oja
   where a < 20
 ) T2
on (T1.a = T2.a)
;

-- A simple FOJ with empty outer clusters
Select *
from (
   Select a - 3 a
   from t002_oja
   where a < 20 and a > 9
 ) T1
full join (
   Select a - 10 a
   from t002_oja
   where a < 20 and a > 9
 ) T2
on (T1.a = T2.a)
;

-- LOJ with beforepred, at least one outer row w/o a match
select count(T2.c)
from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1
      left outer join
     (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2
      on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < T1.c)
;

-- LOJ with afterjoinpred, at least one outer row w/o a match
select count(*)
from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1
      left outer join
     (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2
      on (T1.a-10 = T2.a and T1.b = T2.b)
where T2.c < 5 or T2.c is null
;

-- LOJ with afterjoinpred and beforepred, at least one outer row w/o a match
select count(*)
from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1
      left outer join
     (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2
      on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < T1.c)
where T2.c < 5 or T2.c is null
;

-- Test Hash with forced overflow.
control query default EXE_TEST_HASH_FORCE_OVERFLOW_EVERY '2';
select * from t002_oja A
   left join t002_ojb B
    on A.a = B.b
   left join t002_ojc C
   on (A.a = C.c) 
  left join t002_ojd D
 on A.a = D.e ;

-- With an AfterJoinPred
select * from t002_oja A
   left join t002_ojb B
    on A.a = B.b
   left join t002_ojc C
   on (A.a = C.c) 
  left join t002_ojd D
 on A.a = D.e 
where A.a < 100 and B.b is null and C.c is null and D.e is null;

-- Full Outer, With an AfterJoinPred
select * from (select * from t002_oja where a < 100) A
   full outer join (select * from t002_ojb where b < 100) B
    on A.a = B.b
    full outer join (select * from t002_ojc where c < 100) C
   on (A.a = C.c) 
  full outer join (select * from t002_ojd where e < 100) D
 on A.a = D.e 
where A.a is null or (B.b is null and C.c is null and D.e is null);

-- FOJ with beforepred, at least one outer row w/o a match and forced overflow.
select count(*) from (
select T1.*
from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1
      full outer join
     (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2
      on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < T1.c)
) C
;

-- FOJ with afterjoinpred, at least one outer row w/o a match and forced overflow.
select count(*) from (
select T1.*
from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1
      full outer join
     (select *, cast('Hello' as char(512)) b from t002_oja) T2
      on (T1.a-10 = T2.a and T1.b = T2.b)
where T1.c < 5) C
;

-- FOJ with afterjoinpred and beforepred, at least one outer row w/o a match and forced overflow.
select count(*) from (
select T1.*
from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1
      full outer join
     (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2
      on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < 10)
where T1.c < 5) C
;

-- FOJ with constant projected from outer side.
Select  T1.a, T1.c
from (
   Select 79 c, a
   from t002_oja
   where a < 20
 ) T1
full join (
   Select *
   from t002_oja
   where a < 20
 ) T2
on (T1.a = T2.a)
;

-- A simple FOJ with empty outer clusters
Select *
from (
   Select a - 3 a
   from t002_oja
   where a < 20 and a > 9
 ) T1
full join (
   Select a - 10 a
   from t002_oja
   where a < 20 and a > 9
 ) T2
on (T1.a = T2.a)
;

-- LOJ with beforepred, at least one outer row w/o a match and forced overflow.
select count(T2.c)
from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1
      left outer join
     (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2
      on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < T1.c)
;

-- LOJ with afterjoinpred, at least one outer row w/o a match and forced overflow.
select count(*)
from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1
      left outer join
     (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2
      on (T1.a-10 = T2.a and T1.b = T2.b)
where T2.c < 5 or T2.c is null
;

-- LOJ with afterjoinpred and beforepred, at least one outer row w/o a match and forced overflow.
select count(*)
from (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja where a < 100) T1
      left outer join
     (select *, cast('Hello' as char(512)) b, a/10 as c from t002_oja) T2
      on (T1.a-10 = T2.a and T1.b = T2.b and T2.c < T1.c)
where T2.c < 5 or T2.c is null
;

control query default EXE_TEST_HASH_FORCE_OVERFLOW_EVERY reset;


control query default hash_joins 'off';
control query default nested_joins reset;
control query default merge_joins 'off';
-- Does not find the nested_join plan with default opt level.
control query default optimization_level '5';
select * from t002_oja A
   left join t002_ojb B
    on A.a = B.b
   left join t002_ojc C
   on (A.a = C.c) 
  left join t002_ojd D
 on A.a = D.e ;

-- With an AfterJoinPred
select * from t002_oja A
   left join t002_ojb B
    on A.a = B.b
   left join t002_ojc C
   on (A.a = C.c) 
  left join t002_ojd D
 on A.a = D.e 
where A.a < 100 and B.b is null and C.c is null and D.e is null;
control query default optimization_level reset;

control query default hash_joins 'off';
control query default nested_joins 'off';
control query default merge_joins reset;
select * from t002_oja A
   left join t002_ojb B
    on A.a = B.b
   left join t002_ojc C
   on (A.a = C.c) 
  left join t002_ojd D
 on A.a = D.e ;

-- With an AfterJoinPred
select * from t002_oja A
   left join t002_ojb B
    on A.a = B.b
   left join t002_ojc C
   on (A.a = C.c) 
  left join t002_ojd D
 on A.a = D.e 
where A.a < 100 and B.b is null and C.c is null and D.e is null;

control query default hash_joins reset;
control query default nested_joins reset;
control query default merge_joins reset;

drop table if exists student;
create table student( student_name char(10), test_score int );
insert into student values('s1', 88);
insert into student values('s1', 79);
insert into student values('s2', 88);
insert into student values('s3', 90);
insert into student values('s1', 88);
insert into student values('s1', 60);
insert into student values('s1', 100);
insert into student values('s1', 50);
insert into student values('s1', 101);
insert into student values('s1', 40);
insert into student values('s3', 40);
insert into student values('s3', 40);
insert into student values('s3', 100);
insert into student values('s2', 88);
insert into student values('s2', 188);
insert into student values('s2', 18);
insert into student values('s2', 58);
insert into student values('学生三',79);

SELECT student_name,
          GROUP_CONCAT(DISTINCT test_score
                    ORDER BY test_score SEPARATOR '-')
          FROM student
          GROUP BY student_name order by student_name;

SELECT student_name,
          GROUP_CONCAT(test_score
                    ORDER BY test_score SEPARATOR '-')
          FROM student
          GROUP BY student_name order by student_name;

SELECT GROUP_CONCAT(student_name) from student;

cqd ATTEMPT_ESP_PARALLELISM 'ON';
control query shape exchange(cut);
prepare s1 from SELECT student_name,
          GROUP_CONCAT(DISTINCT test_score
                    ORDER BY test_score desc SEPARATOR '-')
          FROM student
          GROUP BY student_name order by student_name;
explain options 'f' s1;
execute s1;
control query shape cut;
cqd ATTEMPT_ESP_PARALLELISM 'OFF';
drop table student;
drop table if exists regexp_test;
create table regexp_test (c1 char(32) );
insert into regexp_test values( '123' );
insert into regexp_test values( '123a' );
insert into regexp_test values( 'english' );
insert into regexp_test values( 'dev@trafodion.org' );
insert into regexp_test values( '127.0.0.1' );
insert into regexp_test values( '127.0.0.300' );
insert into regexp_test values('中文测试');

-- only number
select * from regexp_test where c1 regexp '^[0-9]*\s*$';
select * from regexp_test where c1 regexp '^[[:digit:]]*\s*$';
-- only english
select * from regexp_test where c1 regexp '^.[A-Za-z]+\s*$';
-- valid email address
select * from regexp_test where c1 regexp '\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*\s*';
-- valid ip address
select * from regexp_test where c1 regexp '^(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9])\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9]|0)\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9]|0)\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[0-9])\s*$';
-- utf-8 code
select * from regexp_test where c1 regexp '(中文测试)';
select * from regexp_test where c1 regexp '[^\';
drop table regexp_test;

--create table have 1K rows
create table T002T1K (uniq int not null,
 c1K int, c100 int,
 c10 int, c1 int, c0 int  )
 STORE BY (uniq)
  ATTRIBUTES ALIGNED FORMAT
  SALT USING 8 PARTITIONS
  ;

upsert using load into T002T1K select
0 + (1000 * x10) + (100 * x1) + (10 * x1) + (1 * x01),
0 + (100 * x10) + (10 * x1) + (1 * x01),
0 + (10 * x1) + (1 * x01),
0 + (1 * x01),
0,
X01
from (values(0)) t
transpose 0,1,2,3,4,5,6,7,8,9 as x10
transpose 0,1,2,3,4,5,6,7,8,9 as x1
transpose 0,1,2,3,4,5,6,7,8,9 as X01;

create table t002timert (c0 int, c1 int, c2 largeint);
create table t002tmp1 (c1 int);
insert into t002tmp1 values(1),(2),(3);

insert into t002timert select 1, sleep(5) , unix_timestamp() from t002tmp1;
insert into t002timert select 2, sleep(5) , unix_timestamp() from t002tmp1;
select 'sleeptimetest002', di from (select ( max(c2) - min(c2)) as di from t002timert ) where di between 5 and 9;
select distinct(count(uuid())) from T002T1K;
select distinct(count(sys_guid())) from T002T1K;
drop table t002timert;
drop table t002tmp1;
------------------------------------------------------------------------
-- added for JIRA TRAFODION-2843

insert into D03s
                select c1+c2*10+c3*100, c1, c1+c2*10
                from (values(1)) T
                transpose 0,1 as c1
                transpose 0,1 as c2
                transpose 0,1 as c3;

insert with no rollback into F01s
                select c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000
                      ,c1
                      ,c1+c2*10
                      ,c1+c2*10+c3*100
                      ,c1
                      ,c1+c2*10
                      ,c1+c2*10+c3*100
                      ,c1
                      ,c1+c2*10
                      ,c1+c2*10+c3*100
                      ,c1
                      ,c1+c2*10
                      ,mod(c1+c2*100+c3*100,200)
                      ,mod(c1,3)
                      ,mod(c1,6)
                      ,mod(c1+c2*10,5)
                      ,c1
                      ,c1
                      ,c1+c2*10
                from (values(1)) T
                transpose 0,1 as c1
                transpose 0,1 as c2
                transpose 0,1 as c3
                transpose 0,1 as c4
                transpose 0 as c5
                transpose 0 as c6
                ;

prepare x1 from
                        select F01s.val01, TD03.val01
                        From F01s 
                        full outer join
                          (select D03s.val01,count(D03s.pk)
                          from D03s
                          group by D03s.val01) as TD03(val01,pk) 
                        on (TD03.pk=F01s.fk_d03
                          AND TD03.pk>0 );

execute x1;

prepare x1v from
                        select F01s.val01, F01s.fk_d03, TD03.pk, TD03.val01
                        From F01s 
                        full outer join
                          (select D03s.val01,count(D03s.pk)
                          from D03s
                          group by D03s.val01) as TD03(val01,pk) 
                        on (TD03.pk=F01s.fk_d03);

execute x1v;

obey TEST002(BR0198_MULTI);
obey TEST002(BR0198_EMPTY);

log;
obey TEST002(clnup);
exit;


?section clnup
-- CLEANUP database
drop table t002t1;
drop table t002t2;
drop table t002t3;
drop table t002temp;
drop table t002tmp2;
drop view  t002v;
drop table t002vt;
drop table t002t5;
drop table t002t6;
drop table t002t7;
drop table t002t8;
drop table t002t9;
drop table t002t10;
drop table t002tab2;
drop table t002t1k;
-- * drop table t002ZZ;
-- * drop table t002ZZI;
-- * drop table t002FU;
-- * drop table t002FUI;

#ifMX
drop table t002ut1;
drop table t002ut2;
drop table t002ut3;
drop table t002utemp;
drop table t002utmp2;
#ifMX

-- To test solution 10-090107-8249
drop view t002_valbic;
drop view t002_valb;
drop view t002_vex;

drop table t002_oja; 
drop table t002_ojb; 
drop table t002_ojc; 
drop table t002_ojd; 

drop table t002_oj1  ;
drop table t002_oj2  ;

-- For Genesis Soln 10-100706-1612 
drop table t002_outer_nlj ;
drop table t002_outer_lower_hj ;
drop table t002_inner_lower_hj ;
drop table t002_inner_upper_hj ;

drop table F01s;
drop table D03s;

?section clnup_end


--------------------------------
?section BR0198_MULTI
select count(*) from T002T1;	-- must be >1 for this test to be valid
obey TEST002(BR0198_BASIC);
--------------------------------

--------------------------------
?section BR0198_EMPTY
delete from T002T1;
select count(*) from T002T2;	-- must be >1 for one case below to be valid
obey TEST002(BR0198_BASIC);
--------------------------------

?section BR0198_BASIC

SELECT 54 FROM T002T1 T
HAVING ( EXISTS (
   SELECT STDDEV ( T.A ) FROM T002T1 ));	-- 0 rows EMPTY, 1 MULTI

SELECT 54 FROM T002T1 T
HAVING ( EXISTS (
   SELECT SUM    ( T.A ) FROM T002T1 ));	-- 0 rows EMPTY, 1 MULTI

SELECT 54 FROM T002T1 T
HAVING ( EXISTS (
   SELECT COUNT  ( T.A ) FROM T002T1 ));	-- 0 rows EMPTY, 1 MULTI

SELECT 54 FROM T002T1 T
HAVING ( EXISTS (
   SELECT STDDEV ( T002T1.A ) FROM T002T1 ));	-- 0 rows EMPTY, 1 MULTI

SELECT 54 FROM T002T1 T
HAVING ( EXISTS (
   SELECT SUM ( T002T1.A ) FROM T002T1 ));	-- 0 rows EMPTY, 1 MULTI

SELECT 54 FROM T002T2 T
HAVING ( EXISTS (
   SELECT SUM ( T002T1.A ) FROM T002T1 ));	-- 1 row ALWAYS

SELECT 54 FROM (values(1)) T
HAVING ( EXISTS (
   SELECT SUM ( T002T1.A ) FROM T002T1 ));	-- 1 row ALWAYS

SELECT 54 FROM (values(1)) T
HAVING ( EXISTS (
   SELECT 27 FROM T002T1 ));			-- 0 rows EMPTY, 1 MULTI

  SELECT foo FROM (values(54)) T(foo)
  WHERE  ( EXISTS (
     SELECT 27 FROM T002T1 ));			-- 0 rows EMPTY, 1 MULTI

  SELECT 54 FROM (values(1)) T
  WHERE
    (SELECT 27 FROM (values(2))u)=27;		-- 1 row ALWAYS

  SELECT 54 FROM (values(1)) TbadCorr
  WHERE
    (SELECT 27 FROM TbadCorr) = 27;	-- ok: cat.sch.tbadCorr not found

  SELECT 54 FROM T002T2 T
  WHERE  ( EXISTS (
     SELECT 27 FROM T002T1 ));			-- 0 rows EMPTY, >0 MULTI

SELECT 54 FROM T002T1 T
HAVING (SELECT SUM ( T.A ) FROM T002T1 )>0;	-- 0 rows EMPTY, err 8401 MULTI

SELECT 54 FROM T002T1 T
HAVING (SELECT SUM ( T002T1.A ) FROM T002T1 )>0;-- 0 rows EMPTY, 1 MULTI

SELECT 54 FROM T002T1 T
HAVING (SELECT A FROM T002T1 )>0;		-- 0 rows EMPTY, err 8401 MULTI

?section BR0198_end