-- @@@ 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 @@@
>>
>>---------------------------------------------------------------------------
>>
>>Create table D01
+>(
+>  pk int not null not droppable primary key
+>, val01 int
+>, val02 int
+>);

--- SQL operation complete.
>>
>>
>>Create table D02
+>(
+>  pk int not null not droppable primary key
+>, val01 int
+>, val02 int
+>);

--- SQL operation complete.
>>
>>Create table D03
+>(
+>  pk int not null not droppable primary key
+>, val01 int
+>, val02 int
+>);

--- SQL operation complete.
>>
>>Create table D04 like D01;

--- SQL operation complete.
>>
>>Create table D05 like D02;

--- SQL operation complete.
>>
>>Create table F01
+>(
+>  pk int not null not droppable 
+>, fk_d01 int not null -- foreign key references D01(pk)
+>, fk_d02 int not null -- foreign key references D02(pk)
+>, fk_d03 int not null -- foreign key references D03(pk)
+>, fk_d04 int not null -- foreign key references D04(pk)
+>, fk_d05 int not null -- foreign key references D05(pk)
+>, val01 int
+>, val02 int
+>, val01_d01 int
+>, val02_d01 int
+>, val01_d02 int
+>, val02_d02 int
+>, val01_d03 int
+>, val02_d03 int
+>);

--- SQL operation complete.
>>
>>
>>Create table F02 like F01 with partitions;

--- SQL operation complete.
>>
>>-- D01 (10 rows): 10-3-6
>>
>>insert into D01
+>select c1, mod(c1,3), mod(c1,6)
+>from (values(1)) T
+>transpose 0,1,2,3,4,5,6,7,8,9 as c1
+>;

--- 10 row(s) inserted.
>>-- D02 (100 rows): 100-5-10
>>
>>insert into D02
+>select c1+c2*10, mod(c1+c2*10,5), c1
+>from (values(1)) T
+>transpose 0,1,2,3,4,5,6,7,8,9 as c1
+>transpose 0,1,2,3,4,5,6,7,8,9 as c2
+>;

--- 100 row(s) inserted.
>>-- D03 (1,000 rows): 1000-10-100
>>
>>insert into D03
+>select c1+c2*10+c3*100, c1, c1+c2*10
+>from (values(1)) T
+>transpose 0,1,2,3,4,5,6,7,8,9 as c1
+>transpose 0,1,2,3,4,5,6,7,8,9 as c2
+>transpose 0,1,2,3,4,5,6,7,8,9 as c3
+>;

--- 1000 row(s) inserted.
>>
>>insert into F01
+>select c1+c2*10+c3*100+c4*200
+>      ,c1                       -- fk_d01(10)
+>      ,c1+c2*10                 -- fk_d02(100)
+>      ,c1+c2*10+c3*100          -- fk_d03(1,000)
+>      ,c1                       -- fk_d04(10)
+>      ,c1+c2*10                 -- fk_d05(100)
+>      ,c1+c2*10                 -- val01 (100)
+>      ,mod(c1+c2*100+c3*100,200)-- val02 (200)
+>      ,mod(c1,3)                -- val01_d01(3)
+>      ,mod(c1,6)                -- val02_d01(6)
+>      ,mod(c1+c2*10,5)          -- val01_d02(5)
+>      ,c1                       -- val02_d02(10)
+>      ,c1                       -- val01_d03(10)
+>      ,c1+c2*10                 -- val02_d03(100)
+>from (values(1)) T
+>transpose 0,1,2,3,4,5 as c1
+>transpose 0,1,2,3,4,5 as c2
+>transpose 0,1,2,3,4,5 as c3
+>transpose 0,1,2,3,4,5 as c4
+>transpose 0,1,2,3,4,5 as c5
+>transpose 0,1,2,3,4,5 as c6
+>;

--- 46656 row(s) inserted.
>>
>>insert into D04 select * from D01;

--- 10 row(s) inserted.
>>
>>
>>insert into D05 select * from D02;

--- 100 row(s) inserted.
>>
>>insert into F02 select * from F01;

--- 46656 row(s) inserted.
>>
>>update statistics for table D01 on every column;

--- SQL operation complete.
>>
>>update statistics for table D02 on every column;

--- SQL operation complete.
>>
>>update statistics for table D03 on every column;

--- SQL operation complete.
>>
>>update statistics for table D04 on every column;

--- SQL operation complete.
>>
>>update statistics for table D05 on every column;

--- SQL operation complete.
>>
>>update statistics for table F01 on every key;

--- SQL operation complete.
>>
>>update statistics for table F02 on every key;

--- SQL operation complete.
>>
>>update statistics for table F01 on every column;

--- SQL operation complete.
>>
>>update statistics for table F02 on every column;

--- SQL operation complete.
>>
>>update statistics for table F01 on (FK_D01, FK_D02, FK_D03), (FK_D02, FK_D03), 
+>(FK_D01, FK_D02), (VAL01_D03, VAL02_D03), (FK_D04, FK_D05), (VAL01, VAL02);

*** WARNING[9202] UPDATE STATISTICS has located previously generated histograms that are not being regenerated. This may affect the plans that will be generated. Missing columns lists are (SYSKEY),(VAL02_D02),(VAL01_D02),(VAL02_D01),(VAL01_D01),(PK).

--- SQL operation completed with warnings.
>>
>>update statistics for table F02 on (FK_D01, FK_D02, FK_D03), (FK_D02, FK_D03), 
+>(FK_D01, FK_D02), (VAL01_D03, VAL02_D03), (FK_D04, FK_D05), (VAL01, VAL02);

*** WARNING[9202] UPDATE STATISTICS has located previously generated histograms that are not being regenerated. This may affect the plans that will be generated. Missing columns lists are (SYSKEY),(VAL02_D02),(VAL01_D02),(VAL02_D01),(VAL01_D01),(PK).

--- SQL operation completed with warnings.
>>
>>update statistics for table D01 on (VAL01, VAL02);

*** WARNING[9202] UPDATE STATISTICS has located previously generated histograms that are not being regenerated. This may affect the plans that will be generated. Missing columns lists are (PK).

--- SQL operation completed with warnings.
>>
>>update statistics for table D02 on (VAL01, VAL02);

*** WARNING[9202] UPDATE STATISTICS has located previously generated histograms that are not being regenerated. This may affect the plans that will be generated. Missing columns lists are (PK).

--- SQL operation completed with warnings.
>>
>>update statistics for table D03 on (VAL01, VAL02);

*** WARNING[9202] UPDATE STATISTICS has located previously generated histograms that are not being regenerated. This may affect the plans that will be generated. Missing columns lists are (PK).

--- SQL operation completed with warnings.
>>
>>update statistics for table D04 on (VAL01, VAL02);

*** WARNING[9202] UPDATE STATISTICS has located previously generated histograms that are not being regenerated. This may affect the plans that will be generated. Missing columns lists are (PK).

--- SQL operation completed with warnings.
>>
>>update statistics for table D05 on (VAL01, VAL02);

*** WARNING[9202] UPDATE STATISTICS has located previously generated histograms that are not being regenerated. This may affect the plans that will be generated. Missing columns lists are (PK).

--- SQL operation completed with warnings.
>>
>>log;
