-- @@@ 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 @@@
-- TESTDDL02 (Cardinality)
-- Functionality: Creates random database with multi-column stats.
-- Expected Files: ETESTDDL02.
-- History: Created on 12/03/2008
-- Owner: Renu Varshneya
-----------------------------------------------------------------------------

drop schema cat.mcrand cascade;
create schema cat.mcrand;
cqd pos 'off';

set schema cat.mcrand;
LOG ATESTDDL02 clear;

---------------------------------------------------------------------------
control query default allow_rand_function 'on';

create table x (x largeint not null not droppable, primary key(x));
create table y (y largeint not null not droppable, primary key(y));
create table z (z largeint not null not droppable, primary key(z));

create table ten (x largeint);
create table hundred (x largeint);
create table thousand (x largeint);

create table ten1000 
  (a largeint, 
   b largeint, 
   c largeint, 
   d largeint);

create table hundred1000 
  (a largeint not null not droppable, 
   b largeint not null not droppable, 
   c largeint not null not droppable, 
   d largeint,
   primary key (a,b,c));

insert into ten values(0);
insert into ten values(1);
insert into ten values(2);
insert into ten values(3);
insert into ten values(4);
insert into ten values(5);
insert into ten values(6);
insert into ten values(7);
insert into ten values(8);
insert into ten values(9);

insert into hundred
  (select ten1.x + 10*ten2.x from ten as ten1, ten as ten2);

insert into thousand
  (select ten.x + 10*hundred.x from ten, hundred);

insert into x (select * from hundred);
insert into y (select * from hundred);
insert into z (select * from hundred);

-- 10,000 rows inserted. 
insert into ten1000
  (select thousand.x+1000*ten.x, thousand.x, ten.x, thousand.x*5  from ten, thousand);

update statistics for table ten1000 on every column;
update statistics for table ten1000 on (b,c), (c,d), (a,b,c);

-- 100,000 rows inserted. 
insert into hundred1000
  (select thousand.x+1000*hundred.x,thousand.x, hundred.x, thousand.x*5 from thousand, hundred);

update statistics for table hundred1000 on every column;

update statistics for table hundred1000 on (b,c), (c,d);

update statistics for table hundred1000 create sample random 50 percent;

---------
create table facts
  ( x largeint NO DEFAULT not null, 
    y largeint NO DEFAULT not null,
    z largeint NO DEFAULT not null,
    xpy largeint NO DEFAULT not null,
    xpz largeint NO DEFAULT not null,
    ypz largeint NO DEFAULT not null,
    xpypz largeint,
    xty largeint, xtz largeint, ytz largeint, xtytz largeint,
    x1 largeint, y1 largeint, z1 largeint,
    x2 largeint, y2 largeint, z2 largeint,
    fxy largeint, fxz largeint, fyz largeint,
    fxyz1 largeint, fxyz2 largeint, fxyz3 largeint,
    xy largeint, xz largeint, yz largeint, xyz largeint,
    primary key (x,y,z,xpy,xpz,ypz)) ;-- extent(1024,1024) maxextents 900;


insert into facts
(select 
  x, y, z, 
  x+y, x+z, y+z, x+y+z, 
  x*y, x*y, y*z, x*y*z, 
  x, y, z,
  x*x, y*y, z*z,
  x*x+y*y, x+z*z, y+50*z, 
  x*x+y*y+z*z, x*y+y*z+z*x, x+y*z,
  x+100*y, y+100*z, z+100*x, x+100*y+10000*z
 from x, y, z 
 where z < 10 and y < 30
);

update statistics for table facts on every column;

update statistics for table facts on (x,y,z), (xy,xz), (x,y,xty), (x,y,xpy),
                                     (x,y,xy),(x,y,z,xtytz), (x,y,z,xyz),
                                     (x,y,xpy,xty), (y,xpy), (y, z),
                                     (fxy,fxz), (xz,yz), (xpy,xpz,xpypz);

update statistics for table facts create sample random 50 percent;

----------------
create table randcols
  ( x largeint NO DEFAULT not null, 
    y largeint NO DEFAULT not null,
    z largeint NO DEFAULT not null);

insert into randcols
  select mod(rand(6),100), mod(rand(21),99), mod(rand(38),101)
  from x, y, z 
  where z < 10;
                                     
update statistics for table randcols on (x), (y), (z), (x,y), (x,y,z), (x,z), (y,z);

update statistics for table randcols create sample random 50 percent;

-----------------
create table rc2
  ( x largeint NO DEFAULT not null, 
    y largeint NO DEFAULT not null,
    z largeint NO DEFAULT not null);

insert into rc2
  select mod(rand(3),50), mod(rand(11),90), mod(rand(18),101)
  from ten, x, hundred;

update statistics for table rc2 on (x), (y), (z), (x,y), (x,z), (y,z), (x,y,z);

update statistics for table rc2 create sample random 50 percent;

-----------------
create table mc1
  ( x largeint NO DEFAULT not null, 
    y largeint NO DEFAULT not null,
    z largeint NO DEFAULT not null);


insert into mc1
  select x, x+10*mod(z,10), x+10*mod(y,20)
  from x, y, z 
  where z < 10;


update statistics for table mc1 on every column;
update statistics for table mc1 on (x,y), (x,z), (y,z), (x,y,z);

update statistics for table mc1 create sample random 50 percent;

------------------
create table mc2
  ( x largeint NO DEFAULT not null, 
    y largeint NO DEFAULT not null,
    z largeint NO DEFAULT not null);

insert into mc2
  select x, x+mod(rand(6),10), x+10*mod(rand(21),20)
  from x, y, z 
  where z < 10;
                                     
update statistics for table mc2 on every column;
update statistics for table mc2 on (x,y), (x,z), (y,z), (x,y,z);

update statistics for table mc2 create sample random 50 percent;

exit;
