-- @@@ 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 @@@
----------------------------------------------------
-- TEST014
-- Self joins.
-- Author: Yuval Sherman, Barry Fritchman
-- Owner: MV Query Rewrite team
----------------------------------------------------

obey TEST014(clean_up);
obey TEST014(clean_up_files);
log LOG014 clear;
obey TEST014(set_up);

obey TEST014(Simple_SJ2);
obey TEST014(Medium_SJ22);
obey TEST014(Complex_SJ23);

obey TEST014(set_up_tpcds);
obey TEST014(SSJ2_SRES);
obey TEST014(SSJ2_SRNG);
obey TEST014(SSJ2_CYCLE);
obey TEST014(SSJ2_RNGKEYS);
obey TEST014(SSJ2_RESKEYS);
obey TEST014(SSJ2_ARES);
obey TEST014(SSJ2_ARNG);
obey TEST014(ASJ2_RNG1);
obey TEST014(ASJ2_RNG2);
obey TEST014(ASJ2_RNG3);
obey TEST014(ASJ2_RNG4);
obey TEST014(ASJ2_RES1);
obey TEST014(ASJ2_RES2);
obey TEST014(ASJ2_RES3);
obey TEST014(ASJ2_RNG_RES);
obey TEST014(ASJ2_2_2);
obey TEST014(ASJ8_2);

obey TEST014(clean_up);

exit;

?section clean_up
----------------------------------------------------
------------------ clean up section ----------------
----------------------------------------------------
drop schema S014 cascade;
drop schema S014_TPCDS cascade;

?section clean_up_files
----------------------------------------------------
------------------ clean up files section ----------
----------------------------------------------------
sh rm T014*;

?section set_up
----------------------------------------------------
-------------------- set up section ----------------
----------------------------------------------------

obey GetMvDesc6;

control query default MVQR_REWRITE_LEVEL '3';
control query default MVQR_REWRITE_ENABLED_OPTION 'ON';
control query default QUERY_CACHE '0';

-- This is used to make sure the MV is used in the plan.
prepare checkPlan from
select operator, tname
from table(explain(NULL, 'QUERYSTMT'))
where operator like '%_SCAN%'
order by tname;

create schema S014;
set schema S014;
set pattern $$MVSchemaName$$ S014;

create table CI_DTL_F
(
  CI_D_KY                      LARGEINT NO DEFAULT  NOT NULL
 ,APP_ACTL_SNST_GMT_DT_KY	     LARGEINT NO DEFAULT  NOT NULL
 ,APP_KY                       LARGEINT NO DEFAULT  NOT NULL
 ,APP_PLN_SNST_GMT_DT_KY	     LARGEINT NO DEFAULT  NOT NULL
 ,CI_CRTCLTY_KY                LARGEINT NO DEFAULT  NOT NULL
 ,CI_STAT_KY                   LARGEINT NO DEFAULT  NOT NULL
 ,DVC_TYPE_KY                  LARGEINT NO DEFAULT  NOT NULL
 ,IT_ASSET_OWN_AG_ORG_HIER_KY  LARGEINT NO DEFAULT  NOT NULL
 ,LEG_CO_KY                    LARGEINT NO DEFAULT  NOT NULL
 ,primary key (CI_D_KY)
);

insert into CI_DTL_F values
  (10,26,16,22,16,15,29,10,24),
  (11,30,13,18,25,13,14,30,23),
  (12,17,28,19,29,15,13,27,15),
  (13,15,18,11,16,11,11,14,12),
  (14,16,24,19,14,26,10,11,22),
  (15,27,14,24,11,22,23,30,16),
  (16,28,16,30,22,11,18,27,29),
  (17,18,14,24,19,15,20,24,18),
  (18,29,19,20,27,10,28,28,24),
  (19,22,27,26,26,21,17,10,27),
  (20,24,11,30,21,11,22,17,19),
  (21,13,28,26,14,17,15,12,16),
  (22,11,18,22,29,15,29,26,15),
  (23,24,12,15,14,16,15,15,25),
  (24,24,29,22,29,24,30,21,23),
  (25,18,27,29,10,28,26,27,24),
  (26,15,10,19,10,21,13,22,18),
  (27,17,24,29,26,14,28,10,23),
  (28,27,17,29,23,16,24,26,24),
  (29,16,10,20,30,11,25,10,29),
  (30,20,28,11,19,22,30,11,14),
  (31,23,20,30,26,11,13,11,11),
  (32,26,28,27,27,19,23,25,13),
  (33,16,26,13,27,14,15,18,30),
  (34,23,25,25,28,15,29,16,20),
  (35,26,17,15,18,27,15,27,20),
  (36,10,13,13,19,19,10,25,19),
  (37,15,28,18,23,21,10,11,15),
  (38,12,13,22,14,26,18,23,26),
  (39,18,16,12,22,17,26,21,22),
  (40,17,20,26,21,12,25,10,23),
  (41,12,24,29,13,26,29,10,23),
  (42,21,25,17,18,20,29,27,18),
  (43,20,14,20,13,29,15,11,15),
  (44,28,25,24,27,21,25,11,29),
  (45,18,26,22,20,11,25,22,16),
  (46,23,15,12,26,11,22,11,23),
  (47,26,15,25,14,11,14,18,17),
  (48,20,18,12,15,16,23,28,21),
  (49,22,23,29,26,13,10,11,16);

create table ASGN_GRP_ORG_HIER_D
(
  ASGN_GRP_ORG_HIER_KY    LARGEINT NO DEFAULT  NOT NULL
 ,ASGN_GRP_ORG_LVL_1_NM   CHAR(20) CHARACTER SET UCS2  DEFAULT NULL
 ,ASGN_GRP_ORG_LVL_2_NM   CHAR(20) CHARACTER SET UCS2  DEFAULT NULL
 ,ASGN_GRP_ORG_LVL_3_NM   CHAR(20) CHARACTER SET UCS2  DEFAULT NULL
 ,ASGN_GRP_ORG_LVL_4_NM   CHAR(20) CHARACTER SET UCS2  DEFAULT NULL
 ,primary key (ASGN_GRP_ORG_HIER_KY)
);

insert into ASGN_GRP_ORG_HIER_D values
  (10,_UCS2'Level 1 26',_UCS2'hp.com it',_UCS2'Level 3 22',_UCS2'Level 4 16'),
  (11,_UCS2'Level 1 30',_UCS2'hp.com it',_UCS2'Level 3 18',_UCS2'Level 4 25'),
  (12,_UCS2'Level 1 17',_UCS2'hp.com it',_UCS2'Level 3 19',_UCS2'Level 4 29'),
  (13,_UCS2'Level 1 15',_UCS2'hp.com it',_UCS2'Level 3 11',_UCS2'Level 4 16'),
  (14,_UCS2'Level 1 16',_UCS2'hp.com it',_UCS2'Level 3 19',_UCS2'Level 4 14'),
  (15,_UCS2'Level 1 27',_UCS2'hp.com it',_UCS2'Level 3 24',_UCS2'Level 4 11'),
  (16,_UCS2'Level 1 28',_UCS2'hp.com it',_UCS2'Level 3 30',_UCS2'Level 4 22'),
  (17,_UCS2'Level 1 18',_UCS2'hp.com it',_UCS2'Level 3 24',_UCS2'Level 4 19'),
  (18,_UCS2'Level 1 29',_UCS2'hp.com it',_UCS2'Level 3 20',_UCS2'Level 4 27'),
  (19,_UCS2'Level 1 22',_UCS2'hp.com it',_UCS2'Level 3 26',_UCS2'Level 4 26'),
  (20,_UCS2'Level 1 24',_UCS2'Lvl 2 11 ',_UCS2'Level 3 30',_UCS2'Level 4 21'),
  (21,_UCS2'Level 1 13',_UCS2'hp.com it',_UCS2'Level 3 26',_UCS2'Level 4 14'),
  (22,_UCS2'Level 1 11',_UCS2'Lvl 2 18 ',_UCS2'Level 3 22',_UCS2'Level 4 29'),
  (23,_UCS2'Level 1 24',_UCS2'hp.com it',_UCS2'Level 3 15',_UCS2'Level 4 14'),
  (24,_UCS2'Level 1 24',_UCS2'Lvl 2 29 ',_UCS2'Level 3 22',_UCS2'Level 4 29'),
  (25,_UCS2'Level 1 18',_UCS2'hp.com it',_UCS2'Level 3 29',_UCS2'Level 4 10'),
  (26,_UCS2'Level 1 15',_UCS2'Lvl 2 10 ',_UCS2'Level 3 19',_UCS2'Level 4 10'),
  (27,_UCS2'Level 1 17',_UCS2'hp.com it',_UCS2'Level 3 29',_UCS2'Level 4 26'),
  (28,_UCS2'Level 1 27',_UCS2'Lvl 2 17 ',_UCS2'Level 3 29',_UCS2'Level 4 23'),
  (29,_UCS2'Level 1 16',_UCS2'hp.com it',_UCS2'Level 3 20',_UCS2'Level 4 30');

create table APP_D
(
  APP_KY              LARGEINT NO DEFAULT  NOT NULL
 ,APP_CI_LGCL_NM      CHAR(9)  CHARACTER SET UCS2  DEFAULT NULL
 ,APP_PRTFL_ID        CHAR(12) CHARACTER SET ISO88591  DEFAULT NULL 
 ,APP_TYPE_DN         CHAR(10) CHARACTER SET UCS2  DEFAULT NULL
 ,LGCL_DEL_FG         CHAR(1)  CHARACTER SET ISO88591  NO DEFAULT NOT NULL
 ,SMPLFCTN_APRCH_DN   CHAR(10) CHARACTER SET UCS2  DEFAULT NULL
 ,primary key (APP_KY)
);

insert into APP_D values
  (10,'lgcl 26','Prtfl 16','TypeDn 22','y','AprchDn 16'),
  (11,'lgcl 30','Prtfl 13','TypeDn 18','y','AprchDn 25'),
  (12,'lgcl 17','Prtfl 28','TypeDn 19','y','AprchDn 29'),
  (13,'lgcl 15','Prtfl 18','TypeDn 11','y','AprchDn 16'),
  (14,'lgcl 16','Prtfl 24','TypeDn 19','y','AprchDn 14'),
  (15,'lgcl 27','Prtfl 14','TypeDn 24','n','AprchDn 11'),
  (16,'lgcl 28','Prtfl 16','TypeDn 30','n','AprchDn 22'),
  (17,'lgcl 18','Prtfl 14','TypeDn 24','n','AprchDn 19'),
  (18,'lgcl 29','Prtfl 19','TypeDn 20','n','AprchDn 27'),
  (19,'lgcl 22','Prtfl 27','TypeDn 26','n','AprchDn 26'),
  (20,'lgcl 24','Prtfl 11','TypeDn 30','n','AprchDn 21'),
  (21,'lgcl 13','Prtfl 28','TypeDn 26','n','AprchDn 14'),
  (22,'lgcl 11','Prtfl 18','TypeDn 22','n','AprchDn 29'),
  (23,'lgcl 24','Prtfl 12','TypeDn 15','n','AprchDn 14'),
  (24,'lgcl 24','Prtfl 29','TypeDn 22','n','AprchDn 29'),
  (25,'lgcl 18','Prtfl 27','TypeDn 29','n','AprchDn 10'),
  (26,'lgcl 15','Prtfl 10','TypeDn 19','n','AprchDn 10'),
  (27,'lgcl 17','Prtfl 24','TypeDn 29','n','AprchDn 26'),
  (28,'lgcl 27','Prtfl 17','TypeDn 29','n','AprchDn 23'),
  (29,'lgcl 16','Prtfl 10','TypeDn 20','n','AprchDn 30');

create table CI_D
(
  CI_D_KY             LARGEINT NO DEFAULT  NOT NULL
 ,CI_ALIAS_NM         CHAR(9) CHARACTER SET UCS2  DEFAULT NULL
 ,CI_NM               CHAR(9) CHARACTER SET UCS2  DEFAULT NULL
 ,LGCL_DEL_FG         CHAR(1) CHARACTER SET ISO88591  NO DEFAULT NOT NULL
 ,SRC_SYS_UPD_DT      DATE DEFAULT NULL
 ,primary key (CI_D_KY)
);


insert into CI_D values
  (10,'Alias 30','Name 27','y',date'2010-04-12'),
  (11,'Alias 10','Name 23','y',date'2010-01-18'),
  (12,'Alias 11','Name 21','y',date'2010-04-15'),
  (13,'Alias 10','Name 14','y',date'2010-03-14'),
  (14,'Alias 11','Name 12','y',date'2010-05-24'),
  (15,'Alias 17','Name 22','n',date'2010-01-23'),
  (16,'Alias 22','Name 12','n',date'2010-04-25'),
  (17,'Alias 10','Name 28','n',date'2010-03-15'),
  (18,'Alias 23','Name 26','n',date'2010-04-30'),
  (19,'Alias 11','Name 20','n',date'2010-10-11'),
  (20,'Alias 15','Name 15','n',date'2010-07-14'),
  (21,'Alias 26','Name 27','n',date'2010-03-12'),
  (22,'Alias 12','Name 11','n',date'2010-08-14'),
  (23,'Alias 20','Name 21','n',date'2010-09-13'),
  (24,'Alias 13','Name 10','n',date'2010-09-20'),
  (25,'Alias 19','Name 23','n',date'2010-05-11'),
  (26,'Alias 26','Name 14','n',date'2010-09-18'),
  (27,'Alias 16','Name 11','n',date'2010-09-14'),
  (28,'Alias 15','Name 30','n',date'2010-01-24'),
  (29,'Alias 12','Name 27','n',date'2010-01-23'),
  (30,'Alias 30','Name 27','y',date'2010-04-12'),
  (31,'Alias 10','Name 23','y',date'2010-01-18'),
  (32,'Alias 11','Name 21','y',date'2010-04-15'),
  (33,'Alias 10','Name 14','y',date'2010-03-14'),
  (34,'Alias 11','Name 12','y',date'2010-05-24'),
  (35,'Alias 17','Name 22','n',date'2010-01-23'),
  (36,'Alias 22','Name 12','n',date'2010-04-25'),
  (37,'Alias 10','Name 28','n',date'2010-03-15'),
  (38,'Alias 23','Name 26','n',date'2010-04-30'),
  (39,'Alias 11','Name 20','n',date'2010-10-11'),
  (40,'Alias 15','Name 15','n',date'2010-07-14'),
  (41,'Alias 26','Name 27','n',date'2010-03-12'),
  (42,'Alias 12','Name 11','n',date'2010-08-14'),
  (43,'Alias 20','Name 21','n',date'2010-09-13'),
  (44,'Alias 13','Name 10','n',date'2010-09-20'),
  (45,'Alias 19','Name 23','n',date'2010-05-11'),
  (46,'Alias 26','Name 14','n',date'2010-09-18'),
  (47,'Alias 16','Name 11','n',date'2010-09-14'),
  (48,'Alias 15','Name 30','n',date'2010-01-24'),
  (49,'Alias 12','Name 27','n',date'2010-01-23');

create table DT_DAY_D
(
  DT_DAY_KY           LARGEINT NO DEFAULT  NOT NULL
 ,CLDR_DT             DATE DEFAULT NULL
 ,primary key (DT_DAY_KY)
);

insert into DT_DAY_D values
  (10,date'2010-09-18'),
  (11,date'2010-01-13'),
  (12,date'2010-01-25'),
  (13,date'2010-02-27'),
  (14,date'2010-04-19'),
  (15,date'2010-09-27'),
  (16,date'2010-06-13'),
  (17,date'2010-03-30'),
  (18,date'2010-02-20'),
  (19,date'2010-04-12'),
  (20,date'2010-01-16'),
  (21,date'2010-04-27'),
  (22,date'2010-09-21'),
  (23,date'2010-07-12'),
  (24,date'2010-06-30'),
  (25,date'2010-08-17'),
  (26,date'2010-07-23'),
  (27,date'2010-07-31'),
  (28,date'2010-04-22'),
  (29,date'2010-04-29');

create table HPSC_MSTR_L_CD_D
(
  HPSC_MSTR_L_KY      LARGEINT NO DEFAULT  NOT NULL
 ,MSTR_L_COL_ID       CHAR(12) CHARACTER SET ISO88591  DEFAULT NULL
 ,DSPLY_DN            CHAR(12) CHARACTER SET UCS2  DEFAULT NULL
 ,primary key (HPSC_MSTR_L_KY)
);

insert into HPSC_MSTR_L_CD_D values
  (10,'Col ID 27','Dis 22'),
  (11,'Col ID 28','Dis 30'),
  (12,'Col ID 17','Dis 21'),
  (13,'Col ID 28','Dis 22'),
  (14,'Col ID 27','Dis 25'),
  (15,'Col ID 16','Dis 24'),
  (16,'Col ID 14','Dis 30'),
  (17,'Col ID 30','Dis 14'),
  (18,'Col ID 27','Dis 16'),
  (19,'Col ID 17','Dis 22'),
  (20,'Col ID 22','Dis 24'),
  (21,'Col ID 29','Dis 11'),
  (22,'Col ID 26','Dis 15'),
  (23,'Col ID 21','Dis 27'),
  (24,'Col ID 26','Dis 28'),
  (25,'Col ID 27','Dis 20'),
  (26,'Col ID 30','Dis 30'),
  (27,'Col ID 29','Dis 18'),
  (28,'Col ID 28','Dis 30'),
  (29,'Col ID 22','Dis 25');

create table DVC_TYPE_D
(
  DVC_TYPE_KY			LARGEINT NO DEFAULT  NOT NULL
 ,DVC_TYPE_DN			CHAR(12) CHARACTER SET UCS2  DEFAULT NULL
 ,primary key (DVC_TYPE_KY)
);

insert into DVC_TYPE_D values
  (10,'application'),
  (11,'application'),
  (12,'application'),
  (13,'application'),
  (14,'application'),
  (15,'application'),
  (16,'application'),
  (17,'application'),
  (18,'application'),
  (19,'application'),
  (20,'application'),
  (21,'application'),
  (22,'application'),
  (23,'application'),
  (24,'application'),
  (25,'application'),
  (26,'non-app    '),
  (27,'non-app    '),
  (28,'non-app    '),
  (29,'non-app    ');

--============================================================================
?section set_up_tpcds
create schema S014_TPCDS;
set schema S014_TPCDS;
set pattern $$MVSchemaName$$ S014_TPCDS;
-- Creating TPCDS tables (output not logged).
log;
obey tpcds.ddl;

log LOG014;

insert into item (I_ITEM_SK, I_ITEM_ID, I_REC_START_DATE, I_REC_END_DATE, I_ITEM_DESC, I_CURRENT_PRICE, I_WHOLESALE_COST, I_BRAND_ID, I_BRAND, I_CLASS_ID, I_CLASS, I_CATEGORY_ID, I_CATEGORY, I_MANUFACT_ID, I_MANUFACT, I_SIZE, I_FORMULATION, I_COLOR, I_UNITS, I_CONTAINER, I_MANAGER_ID, I_PRODUCT_NAME) values
  (  1, 'AAAAAAAABAAAAAAA', date'1997-10-27', date'2010-10-26', 'Years open after a employers. ',  1.92,  1.22,  8005002, 'scholarnameless #2   ',  5, 'fitness          ',  8, 'Sports      ', 439, 'n stpriese     ', 'N/A         ', '87172175465083red525', 'violet     ', 'Oz     ', 'Unknown', 26, 'ought      ' ),
  (  2, 'AAAAAAAACAAAAAAA', date'1997-10-27', date'2010-10-26', 'Bad, separate matters recognis',  5.58,  2.28,  6002003, 'importocorp #3       ',  2, 'diamonds         ',  6, 'Jewelry     ', 105, 'antibarought   ', 'N/A         ', '33magenta66717330227', 'sienna     ', 'Cup    ', 'Unknown',  4, 'able       ' ),
  (  3, 'AAAAAAAACAAAAAAA', date'2000-10-27', date'2010-10-26', 'Wings could live now; public, ',  6.57,  2.82,  6014004, 'edu packbrand #4     ',  2, 'estate           ',  6, 'Jewelry     ', 105, 'antibarought   ', 'N/A         ', '8850744776navy753652', 'rosy       ', 'Dram   ', 'Unknown', 19, 'pri        ' ),
  (  4, 'AAAAAAAAEAAAAAAA', date'1997-10-27', date'1999-10-27', 'European years could not bring',   .51,   .23,  7012005, 'importonameless #5   ', 12, 'paint            ',  7, 'Home        ', 146, 'callyeseought  ', 'N/A         ', '58953675575honeydew0', 'peru       ', 'Case   ', 'Unknown', 52, 'ese        ' ),
  (  5, 'AAAAAAAAEAAAAAAA', date'1999-10-28', date'2001-10-26', 'European years could not bring', 69.42,   .23,  7012005, 'amalgamalg #2        ',  1, 'dresses          ',  1, 'Women       ',   2, 'able           ', 'large       ', '4595peru691582387846', 'turquoise  ', 'Cup    ', 'Unknown', 55, 'anti       ' ),
  (  6, 'AAAAAAAAEAAAAAAA', date'2001-10-27', date'2010-10-26', 'European years could not bring', 95.89, 68.08,  9008007, 'namelessmaxi #7      ',  1, 'romance          ',  9, 'Books       ', 114, 'eseoughtought  ', 'N/A         ', '1white18427753054173', 'puff       ', 'Tbl    ', 'Unknown', 27, 'cally      ' ),
  (  7, 'AAAAAAAAHAAAAAAA', date'1997-10-27', date'2010-10-26', 'Wives ensure supposed forces. ',   .89,   .63,  4001002, 'amalgedu pack #2     ',  1, 'womens           ',  4, 'Shoes       ',  59, 'n stanti       ', 'extra large ', '22052946slate4626263', 'lawn       ', 'Dram   ', 'Unknown', 32, 'ation      ' ),
  (  8, 'AAAAAAAAIAAAAAAA', date'1997-10-27', date'2000-10-26', 'J                             ',  1.95,   .99,  8003009, 'exportinameless #9   ',  3, 'basketball       ',  8, 'Sports      ', 297, 'ationn stable  ', 'N/A         ', '4422sandy39551140183', 'turquoise  ', 'Carton ', 'Unknown', 26, 'eing       ' ),
  (  9, 'AAAAAAAAIAAAAAAA', date'2000-10-27', date'2010-10-26', 'J                             ',  4.21,  3.19,  8006010, 'corpnameless #10     ',  6, 'football         ',  8, 'Sports      ', 297, 'ationn stable  ', 'N/A         ', '4422sandy39551140183', 'tan        ', 'Ton    ', 'Unknown', 45, 'n st       ' ),
  ( 10, 'AAAAAAAAKAAAAAAA', date'1997-10-27', date'1999-10-27', 'Never agricultural women can m', 88.15, 61.70,  5003001, 'exportischolar #1    ',  3, 'pop              ',  5, 'Music       ',   2, 'able           ', 'N/A         ', '69turquoise605332481', 'blush      ', 'Oz     ', 'Unknown', 15, 'bpadught   ' ),
  ( 11, 'AAAAAAAAKAAAAAAA', date'1999-10-28', date'2001-10-26', 'Never agricultural women can m',   .53, 61.70,  7004002, 'edu packbrand #2     ',  3, 'curtains/drapes  ',  7, 'Home        ', 289, 'n steingable   ', 'N/A         ', '39466074081955357960', 'firebrick  ', 'Box    ', 'Unknown', 45, 'opadtought ' ),
  ( 12, 'AAAAAAAAKAAAAAAA', date'2001-10-27', date'2010-10-26', 'Never agricultural women can m',  2.65, 61.70, 10004013, 'edu packunivamalg #13',  3, 'audio            ', 10, 'Electronics ', 686, 'callyeingcally ', 'N/A         ', '39466074081955357960', 'seashell   ', 'Gram   ', 'Unknown', 23, 'apadought  ' ),
  ( 13, 'AAAAAAAANAAAAAAA', date'1997-10-27', date'2010-10-26', 'Enviro                        ',  8.21,  6.97,  6006006, 'corpcorp #6          ',  6, 'rings            ',  6, 'Jewelry     ', 888, 'eingeingeing   ', 'N/A         ', '2886thistle928000408', 'peach      ', 'Dram   ', 'Unknown', 91, 'ppadught   ' ),
  ( 14, 'AAAAAAAAOAAAAAAA', date'1997-10-27', date'2000-10-26', 'True, modern agencies must not',  3.42,  2.66,  1003001, 'exportiamalg #1      ',  3, 'maternity        ',  1, 'Women       ', 305, 'antibarpri     ', 'petite      ', '86092099sienna066303', 'snow       ', 'Pallet ', 'Unknown', 35, 'epadught   ' ),
  ( 15, 'AAAAAAAAOAAAAAAA', date'2000-10-27', date'2010-10-26', 'True, modern agencies must not', 87.34, 48.03,  4003002, 'exportiedu pack #2   ',  3, 'kids             ',  4, 'Shoes       ',  19, 'antibarpri     ', 'medium      ', '860920wheatnna066303', 'royal      ', 'Gram   ', 'Unknown', 16, 'apadought  ' ),
  ( 16, 'AAAAAAAAABAAAAAA', date'1997-10-27', date'1999-10-27', 'Communist, current hills rule ',  9.94,  4.97,  5004001, 'edu packscholar #1   ',  4, 'classical        ',  5, 'Music       ', 164, 'esecallyought  ', 'N/A         ', '78919smoke0226911516', 'spring     ', 'Box    ', 'Unknown', 36, 'cpadyought ' ),
  ( 17, 'AAAAAAAAABAAAAAA', date'1999-10-28', date'2001-10-26', 'Communist, current hills rule ',  2.06,  4.97,  5004001, 'amalgcorp #2         ',  1, 'birdal           ',  6, 'Jewelry     ', 164, 'esecallyought  ', 'N/A         ', '48719089115665642944', 'navy       ', 'Gross  ', 'Unknown', 46, 'apadnought ' ),
  ( 18, 'AAAAAAAAABAAAAAA', date'2001-10-27', date'2010-10-26', 'Small, other women used to lea',  7.12,  3.13,  9009007, 'maximaxi #7          ',  9, 'science          ',  9, 'Books       ', 284, 'eseeingable    ', 'N/A         ', '9194sky7507798825801', 'purple     ', 'Each   ', 'Unknown', 62, 'epadought  ' ),
  ( 19, 'AAAAAAAADBAAAAAA', date'1997-10-27', date'2010-10-26', 'Active benefits visit funny, m',   .29,   .08,  2003002, 'exportiimporto #2    ',  3, 'pants            ',  2, 'Men         ', 509, 'n stbaranti    ', 'medium      ', '693wheat931156684397', 'peru       ', 'Bundle ', 'Unknown',  4, 'npadought  ' ),
  ( 20, 'AAAAAAAAEBAAAAAA', date'1997-10-27', date'2000-10-26', 'Powerful windows could find al',  2.17,  1.54, 10007004, 'brandunivamalg #4    ',  7, 'personal         ', 10, 'Electronics ', 520, 'barableanti    ', 'N/A         ', '6933731518spring6469', 'turquoise  ', 'Pallet ', 'Unknown', 11, 'bpadble    ' ),
  ( 21, 'AAAAAAAAEBAAAAAA', date'2000-10-27', date'2010-10-26', 'Features can take marginal sha',  4.97,  1.54, 10007004, 'amalgimporto #2      ',  7, 'accessories      ',  2, 'Men         ', 167, 'ationcallyought', 'medium      ', '00823951290896599376', 'yellow     ', 'N/A    ', 'Unknown', 55, 'opadtable  ' ),
  ( 22, 'AAAAAAAAGBAAAAAA', date'1997-10-27', date'1999-10-27', 'Whole systems dry never proble',  4.71,  4.09,  1003001, 'exportiamalg #1      ',  3, 'maternity        ',  1, 'Women       ', 448, 'eingeseese     ', 'medium      ', '86348835seashell0884', 'grey       ', 'Dram   ', 'Unknown', 31, 'apadable   ' ),
  ( 23, 'AAAAAAAAGBAAAAAA', date'1999-10-28', date'2001-10-26', 'Whole systems dry never proble',  7.94,  5.47, 10007007, 'brandunivamalg #7    ',  3, 'personal         ', 10, 'Electronics ', 512, 'ableoughtanti  ', 'N/A         ', '24632694906526902559', 'steel      ', 'Case   ', 'Unknown', 52, 'ppadble    ' ),
  ( 24, 'AAAAAAAAGBAAAAAA', date'2001-10-27', date'2010-10-26', 'Whole systems dry never proble',  1.30,   .59, 10007007, 'edu packexporti #1   ',  4, 'school-uniforms  ',  3, 'Children    ', 310, 'ableoughtanti  ', 'extra large ', 'indian01855229403429', 'grey       ', 'Pallet ', 'Unknown', 35, 'epadble    ' ),
  ( 25, 'AAAAAAAAJBAAAAAA', date'1997-10-27', date'2010-10-26', 'Clearly small efforts may jump',  5.94,  3.92, 10002009, 'importounivamalg #9  ',  2, 'camcorders       ', 10, 'Electronics ', 467, 'ationcallyese  ', 'N/A         ', '8987447306lime185036', 'saddle     ', 'Tbl    ', 'Unknown', 39, 'apadable   ' ),
  ( 26, 'AAAAAAAAKBAAAAAA', date'1997-10-27', date'2000-10-26', 'Properly royal risks indicate ', 94.36, 52.84,  3002001, 'importoexporti #1    ',  2, 'infants          ',  3, 'Children    ', 204, 'esebarable     ', 'medium      ', '318550561orange32094', 'powder     ', 'Ton    ', 'Unknown', 31, 'cpadyable  ' ),
  ( 27, 'AAAAAAAAKBAAAAAA', date'2000-10-27', date'2010-10-26', 'Mentally social numbers may no',  3.22, 52.84,  3003002, 'exportiexporti #2    ',  3, 'toddlers         ',  3, 'Children    ', 331, 'esebarable     ', 'small       ', '80431023919544474414', 'honeydew   ', 'Dozen  ', 'Unknown', 19, 'apadnable  ' ),
  ( 28, 'AAAAAAAAMBAAAAAA', date'1997-10-27', date'1999-10-27', 'Samples shall arrive however b',   .86,   .74,  5002001, 'importoscholar #1    ',  2, 'country          ',  5, 'Music       ',  75, 'antiation      ', 'N/A         ', '5254709411322olive00', 'lace       ', 'Box    ', 'Unknown', 31, 'epadable   ' ),
  ( 29, 'AAAAAAAAMBAAAAAA', date'1999-10-28', date'2001-10-26', 'Samples shall arrive however b',  3.61,   .74,  5002001, 'maxibrand #10        ',  9, 'mattresses       ',  7, 'Home        ',  75, 'antiation      ', 'N/A         ', '24084416593613711980', 'rose       ', 'Carton ', 'Unknown',  7, 'npadable   ' ),
  ( 30, 'AAAAAAAAMBAAAAAA', date'2001-10-27', date'2010-10-26', 'American wo                   ', 70.19, 58.25,  5002001, 'amalgscholar #1      ',  1, 'rock             ',  5, 'Music       ',  66, 'callycally     ', 'N/A         ', '01710violet244361654', 'royal      ', 'Case   ', 'Unknown', 45, 'bpadri     ' ),
  ( 31, 'AAAAAAAAPBAAAAAA', date'1997-10-27', date'2010-10-26', 'Particular stores might not ta',  9.88,  2.96,  2003002, 'exportiimporto #2    ',  3, 'pants            ',  2, 'Men         ',  92, 'ablen st       ', 'medium      ', '19033289615sandy4581', 'smoke      ', 'N/A    ', 'Unknown', 94, 'opadtpri   ' ),
  ( 32, 'AAAAAAAAACAAAAAA', date'1997-10-27', date'2000-10-26', 'Then popular symptoms should c',  1.23,   .70,  4001001, 'amalgedu pack #1     ',  1, 'womens           ',  4, 'Shoes       ', 421, 'oughtableese   ', 'small       ', '61893737puff33118551', 'wheat      ', 'Gross  ', 'Unknown', 15, 'apadpri    ' ),
  ( 33, 'AAAAAAAAACAAAAAA', date'2000-10-27', date'2010-10-26', 'Then popular symptoms should c',  9.95,  3.38,  4001001, 'importoscholar #2    ',  1, 'country          ',  5, 'Music       ', 920, 'barablen st    ', 'N/A         ', '61893737puff33118551', 'salmon     ', 'Bundle ', 'Unknown', 10, 'ppadri     ' ),
  ( 34, 'AAAAAAAACCAAAAAA', date'1997-10-27', date'1999-10-27', 'Wid                           ', 41.58, 37.42,  9006011, 'corpmaxi #11         ',  6, 'parenting        ',  9, 'Books       ',   9, 'n st           ', 'N/A         ', '0193726173266sky9627', 'olive      ', 'Case   ', 'Unknown', 32, 'epadri     ' ),
  ( 35, 'AAAAAAAACCAAAAAA', date'1999-10-28', date'2001-10-26', 'Wid                           ', 60.04, 36.62, 10015002, 'scholaramalgamalg #2 ', 15, 'portable         ', 10, 'Electronics ', 184, 'eseeingought   ', 'N/A         ', '0193726173266sky9627', 'papaya     ', 'Box    ', 'Unknown', 60, 'apadpri    ' ),
  ( 36, 'AAAAAAAACCAAAAAA', date'2001-10-27', date'2010-10-26', 'Awful workers should see pleas', 16.77, 36.62, 10015002, 'edu packscholar #1   ',  4, 'classical        ',  5, 'Music       ',  58, 'einganti       ', 'N/A         ', '0193726173266sky9627', 'snow       ', 'Carton ', 'Unknown', 35, 'cpadypri   ' ),
  ( 37, 'AAAAAAAAFCAAAAAA', date'1997-10-27', date'2010-10-26', 'Later satisfactory jobs cannot',  3.54,  1.91,  4001002, 'amalgedu pack #2     ',  1, 'womens           ',  4, 'Shoes       ', 649, 'n stesecally   ', 'medium      ', '98pink93018836304898', 'yellow     ', 'Each   ', 'Unknown', 56, 'apadnpri   ' ),
  ( 38, 'AAAAAAAAGCAAAAAA', date'1997-10-27', date'2000-10-26', 'Both modern chapters should un', 95.72, 53.60,  3001001, 'amalgexporti #1      ',  1, 'newborn          ',  3, 'Children    ',  24, 'eseable        ', 'medium      ', '46154370thistle93626', 'magenta    ', 'Cup    ', 'Unknown', 98, 'epadpri    ' ),
  ( 39, 'AAAAAAAAGCAAAAAA', date'2000-10-27', date'2010-10-26', 'Both modern chapters should un',  1.41,   .97,  3001001, 'edu packexporti #2   ',  1, 'school-uniforms  ',  3, 'Children    ',  24, 'eseable        ', 'small       ', '46154370thistle93626', 'magenta    ', 'Carton ', 'Unknown',  7, 'npadpri    ' ),
  ( 40, 'AAAAAAAAICAAAAAA', date'1997-10-27', date'1999-10-27', 'National, administrative respo',  2.96,  1.03,  6008001, 'namelesscorp #1      ',  8, 'mens watch       ',  6, 'Jewelry     ', 251, 'oughtantiable  ', 'N/A         ', '2149purple3991411624', 'tan        ', 'Oz     ', 'Unknown',  2, 'bpadse     ' ),
  ( 41, 'AAAAAAAAICAAAAAA', date'1999-10-28', date'2001-10-26', 'National, administrative respo',  8.41,  1.03,  5004002, 'edu packscholar #2   ',  8, 'classical        ',  5, 'Music       ', 588, 'oughtantiable  ', 'N/A         ', '189742blanched671358', 'goldenrod  ', 'Pound  ', 'Unknown', 60, 'opadtese   ' ),
  ( 42, 'AAAAAAAAICAAAAAA', date'2001-10-27', date'2010-10-26', 'Too sudden galleries mention  ',  3.36,  1.03,  7003003, 'exportibrand #3      ',  8, 'kids             ',  7, 'Home        ', 588, 'eingeinganti   ', 'N/A         ', '189742blanched671358', 'turquoise  ', 'Bundle ', 'Unknown',  9, 'apadese    ' ),
  ( 43, 'AAAAAAAALCAAAAAA', date'1997-10-27', date'2010-10-26', 'Sadly required questions could',   .77,   .44,  1002002, 'importoamalg #2      ',  2, 'fragrances       ',  1, 'Women       ', 240, 'bareseable     ', 'extra large ', '11506045puff43255191', 'seashell   ', 'Bundle ', 'Unknown',  9, 'ppadse     ' ),
  ( 44, 'AAAAAAAAMCAAAAAA', date'1997-10-27', date'2000-10-26', 'Social, armed years meet later', 35.55, 31.99,  6015005, 'scholarbrand #5      ', 15, 'custom           ',  6, 'Jewelry     ', 399, 'n stn stpri    ', 'N/A         ', '0422320purple6808660', 'pink       ', 'Gross  ', 'Unknown', 46, 'eseese     ' ),
  ( 45, 'AAAAAAAAMCAAAAAA', date'2000-10-27', date'2010-10-26', 'Schools could explain elegant ',  7.26,  2.54,  1003002, 'exportiamalg #2      ',  3, 'maternity        ',  1, 'Women       ', 399, 'n stn stpri    ', 'medium      ', 'purple11863387081370', 'white      ', 'Gram   ', 'Unknown', 17, 'antiese    ' ),
  ( 46, 'AAAAAAAAOCAAAAAA', date'1997-10-27', date'1999-10-27', 'Beds return s                 ',  8.16,  3.91,  6002007, 'importocorp #7       ',  2, 'diamonds         ',  6, 'Jewelry     ', 160, 'barcallyought  ', 'N/A         ', '795moccasin608449482', 'white      ', 'Lb     ', 'Unknown',  7, 'callyese   ' ),
  ( 47, 'AAAAAAAAOCAAAAAA', date'1999-10-28', date'2001-10-26', 'Soon personal groups rejoin.  ',  8.43,  3.91,  6002007, 'edu packimporto #2   ',  2, 'sports-apparel   ',  2, 'Men         ', 725, 'antiableation  ', 'petite      ', '795moccaswheat449482', 'sienna     ', 'Box    ', 'Unknown', 29, 'ationese   ' ),
  ( 48, 'AAAAAAAAOCAAAAAA', date'2001-10-27', date'2010-10-26', 'Long services steer in a child', 92.63,  3.91,  1001001, 'amalgamalg #1        ',  1, 'dresses          ',  1, 'Women       ', 725, 'antiableation  ', 'large       ', '795moccaswheat449482', 'tan        ', 'Case   ', 'Unknown',  7, 'eingese    ' ),
  ( 49, 'AAAAAAAABDAAAAAA', date'1997-10-27', date'2010-10-26', 'Interests would not require to',  9.35,  2.99,  5004002, 'edu packscholar #2   ',  4, 'classical        ',  5, 'Music       ', 198, 'eingn stought  ', 'N/A         ', '568877snow7477506764', 'plum       ', 'Ounce  ', 'Unknown', 34, 'n stese    ' ),
  ( 50, 'AAAAAAAACDAAAAAA', date'1997-10-27', date'2000-10-26', 'Voices build for a connections',  8.42,  3.70,  9006003, 'corpmaxi #3          ',  6, 'parenting        ',  9, 'Books       ',  45, 'antiese        ', 'N/A         ', '964300816lavender122', 'peru       ', 'Ton    ', 'Unknown', 91, 'baranti    ' ),
  ( 97, 'AAAAAAAABAAAAAAA', date'1997-10-27', date'2010-10-26', 'Years open after a employers. ',  1.92,  1.22,  8005002, 'scholarnameless #2   ',  5, 'fitness          ',  8, 'Sports      ', 439, 'n stpriese     ', 'N/A         ', '87172175465083red525', 'violet     ', 'Oz     ', 'Unknown', 26, 'ought      ' ),
  ( 99, 'AAAAAAAACAAAAAAA', date'1997-10-27', date'2010-10-26', 'Bad, separate matters recognis',  5.58,  2.28,  6002003, 'importocorp #3       ',  2, 'diamonds         ',  6, 'Jewelry     ', 105, 'antibarought   ', 'N/A         ', '33magenta66717330227', 'sienna     ', 'Cup    ', 'Unknown',  4, 'able       ' ),
  (100, 'AAAAAAAACAAAAAAA', date'2000-10-27', date'2010-10-26', 'Wings could live now; public, ',  6.57,  2.82,  6014004, 'edu packbrand #4     ',  2, 'estate           ',  6, 'Jewelry     ', 105, 'antibarought   ', 'N/A         ', '8850744776navy753652', 'rosy       ', 'Dram   ', 'Unknown', 19, 'pri        ' ),
  (122, 'AAAAAAAAEAAAAAAA', date'1997-10-27', date'1999-10-27', 'European years could not bring',   .51,   .23,  7012005, 'importonameless #5   ', 12, 'paint            ',  7, 'Home        ', 146, 'callyeseought  ', 'N/A         ', '58953675575honeydew0', 'peru       ', 'Case   ', 'Unknown', 52, 'ese        ' ),
  (135, 'AAAAAAAAEAAAAAAA', date'1999-10-28', date'2001-10-26', 'European years could not bring', 69.42,   .23,  7012005, 'amalgamalg #2        ',  1, 'dresses          ',  1, 'Women       ',   2, 'able           ', 'large       ', '4595peru691582387846', 'turquoise  ', 'Cup    ', 'Unknown', 55, 'anti       ' ),
  (136, 'AAAAAAAAEAAAAAAA', date'2001-10-27', date'2010-10-26', 'European years could not bring', 95.89, 68.08,  9008007, 'namelessmaxi #7      ',  1, 'romance          ',  9, 'Books       ', 114, 'eseoughtought  ', 'N/A         ', '1white18427753054173', 'puff       ', 'Tbl    ', 'Unknown', 27, 'cally      ' ),
  (168, 'AAAAAAAAHAAAAAAA', date'1997-10-27', date'2010-10-26', 'Wives ensure supposed forces. ',   .89,   .63,  4001002, 'amalgedu pack #2     ',  1, 'womens           ',  4, 'Shoes       ',  59, 'n stanti       ', 'extra large ', '22052946slate4626263', 'lawn       ', 'Dram   ', 'Unknown', 32, 'ation      ' ),
  (199, 'AAAAAAAAIAAAAAAA', date'1997-10-27', date'2000-10-26', 'J                             ',  1.95,   .99,  8003009, 'exportinameless #9   ',  3, 'basketball       ',  8, 'Sports      ', 297, 'ationn stable  ', 'N/A         ', '4422sandy39551140183', 'turquoise  ', 'Carton ', 'Unknown', 26, 'eing       ' ),
  (200, 'AAAAAAAAIAAAAAAA', date'2000-10-27', date'2010-10-26', 'J                             ',  4.21,  3.19,  8006010, 'corpnameless #10     ',  6, 'football         ',  8, 'Sports      ', 297, 'ationn stable  ', 'N/A         ', '4422sandy39551140183', 'tan        ', 'Ton    ', 'Unknown', 45, 'n st       ' ),
  (215, 'AAAAAAAAKAAAAAAA', date'1997-10-27', date'1999-10-27', 'Never agricultural women can m', 88.15, 61.70,  5003001, 'exportischolar #1    ',  3, 'pop              ',  5, 'Music       ',   2, 'able           ', 'N/A         ', '69turquoise605332481', 'blush      ', 'Oz     ', 'Unknown', 15, 'bpadught   ' ),
  (459, 'AAAAAAAAKAAAAAAA', date'1999-10-28', date'2001-10-26', 'Never agricultural women can m',   .53, 61.70,  7004002, 'edu packbrand #2     ',  3, 'curtains/drapes  ',  7, 'Home        ', 289, 'n steingable   ', 'N/A         ', '39466074081955357960', 'firebrick  ', 'Box    ', 'Unknown', 45, 'opadtought ' );


insert into promotion (P_PROMO_SK, P_PROMO_ID, P_START_DATE_SK, P_END_DATE_SK, P_ITEM_SK, P_COST, P_RESPONSE_TARGET, P_PROMO_NAME, P_CHANNEL_DMAIL, P_CHANNEL_EMAIL, P_CHANNEL_CATALOG, P_CHANNEL_TV, P_CHANNEL_RADIO, P_CHANNEL_PRESS, P_CHANNEL_EVENT, P_CHANNEL_DEMO, P_CHANNEL_DETAILS, P_PURPOSE, P_DISCOUNT_ACTIVE) values
  ( 1, 'AAAAAAAABAAAAAAA', 120, 127,  24, 4318.00, 37, 'ought  ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Safe parents shall ', 'Unknown1', 'Y' ),
  ( 2, 'AAAAAAAACAAAAAAA', 136, 110,  23, 1522.00, 33, 'able   ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Female, sorry teach', 'Unknown1', 'Y' ),
  ( 3, 'AAAAAAAADAAAAAAA', 109, 149,  10,  132.00, 21, 'pri    ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Useless, full allie', 'Unknown1', 'Y' ),
  ( 4, 'AAAAAAAAEAAAAAAA', 123, 140,  23, 3113.00, 19, 'ese    ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Doors bury in a aud', 'Unknown1', 'Y' ),
  ( 5, 'AAAAAAAAFAAAAAAA', 125, 114,  19, 1883.00, 48, 'anti   ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Just large fingers ', 'Unknown1', 'Y' ),
  ( 6, 'AAAAAAAAGAAAAAAA', 119, 116,  20, 4628.00, 11, 'cally  ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Particular cards wo', 'Unknown2', 'Y' ),
  ( 7, 'AAAAAAAAHAAAAAAA', 129, 132,  29, 9136.00, 22, 'ation  ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Men can worry prope', 'Unknown2', 'Y' ),
  ( 8, 'AAAAAAAAIAAAAAAA', 119, 132,  11, 3482.00, 44, 'eing   ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Yesterday curious p', 'Unknown2', 'Y' ),
  ( 9, 'AAAAAAAAJAAAAAAA', 133, 144,  32, 4324.00, 40, 'n st   ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Confident, combined', 'Unknown2', 'Y' ),
  (10, 'AAAAAAAAKAAAAAAA', 142, 102,  31, 7910.00, 39, 'bar    ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Able, ethical momen', 'Unknown2', 'Y' ),
  (11, 'AAAAAAAALAAAAAAA', 135, 148,  26, 4036.00, 38, 'ought  ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Traditionally other', 'Unknown3', 'Y' ),
  (12, 'AAAAAAAAMAAAAAAA', 138, 141,  19, 4991.00, 46, 'ooops  ', 'N', 'N', 'N', 'Y', 'Y', 'Y', 'N', 'Y', 'Acute organisations', 'Unknown3', 'Y' ),
  (13, 'AAAAAAAANAAAAAAA', 115, 104,  35, 8614.00, 10, 'pri    ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Things could play h', 'Unknown3', 'Y' ),
  (14, 'AAAAAAAAOAAAAAAA', 137, 149,  20, 3213.00, 47, 'ese    ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Hitherto ordinary t', 'Unknown3', 'Y' ),
  (15, 'AAAAAAAAPAAAAAAA', 130, 105,  31,   11.00, 44, 'anti   ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Safe, free parts st', 'Unknown3', 'Y' ),
  (16, 'AAAAAAAAABAAAAAA', 101, 132,  50, 2718.00, 44, 'cally  ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Weeks believe too p', 'Unknown1', 'Y' ),
  (17, 'AAAAAAAABBAAAAAA', 128, 130,  43, 3837.00, 41, 'ation  ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Useless, single dis', 'Unknown2', 'N' ),
  (18, 'AAAAAAAACBAAAAAA', 115, 134,  46, 3519.00, 24, 'eing   ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Groups may know con', 'Unknown3', 'N' ),
  (19, 'AAAAAAAADBAAAAAA', 123, 139,  29, 1624.00, 42, 'n st   ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Federal, respective', 'Unknown1', 'N' ),
  (20, 'AAAAAAAAEBAAAAAA', 106, 137,  21, 2817.00, 25, 'bar    ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Final, hard sports ', 'Unknown2', 'N' ),
  (21, 'AAAAAAAACAAAAAAA', 101, 130,  97, 1522.00, 33, 'able   ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Female, sorry teach', 'Unknown1', 'Y' ),
  (22, 'AAAAAAAADAAAAAAA', 141, 114,  99,  132.00, 21, 'pri    ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Useless, full allie', 'Unknown1', 'Y' ),
  (23, 'AAAAAAAAEAAAAAAA', 126, 109, 100, 3113.00, 19, 'ese    ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Doors bury in a aud', 'Unknown1', 'Y' ),
  (24, 'AAAAAAAAFAAAAAAA', 122, 128, 122, 1883.00, 48, 'anti   ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Just large fingers ', 'Unknown1', 'Y' ),
  (25, 'AAAAAAAAGAAAAAAA', 108, 124, 135, 4628.00, 11, 'cally  ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Particular cards wo', 'Unknown2', 'Y' ),
  (26, 'AAAAAAAAHAAAAAAA', 110, 136, 136, 9136.00, 22, 'ation  ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Men can worry prope', 'Unknown2', 'Y' ),
  (27, 'AAAAAAAAIAAAAAAA', 147, 134, 168, 3482.00, 44, 'eing   ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Yesterday curious p', 'Unknown2', 'Y' ),
  (28, 'AAAAAAAAJAAAAAAA', 104, 141, 199, 4324.00, 40, 'n st   ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Confident, combined', 'Unknown2', 'Y' ),
  (29, 'AAAAAAAAKAAAAAAA', 100, 115, 200, 7910.00, 39, 'bar    ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Able, ethical momen', 'Unknown2', 'Y' ),
  (30, 'AAAAAAAALAAAAAAA', 126, 115, 215, 4036.00, 38, 'ought  ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Traditionally other', 'Unknown3', 'Y' ),
  (31, 'AAAAAAAAMAAAAAAA', 137, 131, 459, 4991.00, 46, 'ooops  ', 'N', 'N', 'N', 'Y', 'Y', 'Y', 'N', 'Y', 'Acute organisations', 'Unknown3', 'Y' ),
  (32, 'AAAAAAAACAAAAAAA', 115, 110,  97, 1522.00, 53, 'able   ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Female, sorry teach', 'Unknown1', 'Y' ),
  (33, 'AAAAAAAADAAAAAAA', 120, 124,  99,  132.00, 31, 'pri    ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Useless, full allie', 'Unknown1', 'Y' ),
  (34, 'AAAAAAAAEAAAAAAA', 131, 122, 100, 3113.00, 29, 'ese    ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Doors bury in a aud', 'Unknown1', 'Y' ),
  (35, 'AAAAAAAAFAAAAAAA', 145, 150, 122, 1883.00, 78, 'anti   ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Just large fingers ', 'Unknown1', 'Y' ),
  (36, 'AAAAAAAAGAAAAAAA', 123, 134, 135, 4628.00, 31, 'cally  ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Particular cards wo', 'Unknown2', 'Y' ),
  (37, 'AAAAAAAAHAAAAAAA', 123, 119, 136, 9136.00, 52, 'ation  ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Men can worry prope', 'Unknown2', 'Y' ),
  (38, 'AAAAAAAAIAAAAAAA', 142, 102, 168, 3482.00, 74, 'eing   ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Yesterday curious p', 'Unknown2', 'Y' ),
  (39, 'AAAAAAAAJAAAAAAA', 119, 110, 199, 4324.00, 20, 'n st   ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Confident, combined', 'Unknown2', 'Y' ),
  (40, 'AAAAAAAAKAAAAAAA', 112, 126, 200, 7910.00, 69, 'bar    ', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Able, ethical momen', 'Unknown2', 'Y' ),
  (41, 'AAAAAAAALAAAAAAA', 141, 145, 215, 4036.00, 38, 'ought  ', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Traditionally other', 'Unknown3', 'Y' ),
  (42, 'AAAAAAAAMAAAAAAA', 148, 131, 459, 4991.00, 46, 'ooops  ', 'Y', 'N', 'N', 'Y', 'Y', 'Y', 'N', 'Y', 'Acute organisations', 'Unknown3', 'Y' );

insert into web_sales (WS_SOLD_DATE_SK, WS_SOLD_TIME_SK, WS_SHIP_DATE_SK, WS_ITEM_SK, WS_BILL_CUSTOMER_SK, WS_BILL_CDEMO_SK, WS_BILL_HDEMO_SK, WS_BILL_ADDR_SK, WS_SHIP_CUSTOMER_SK, WS_SHIP_CDEMO_SK, WS_SHIP_HDEMO_SK, WS_SHIP_ADDR_SK, WS_WEB_PAGE_SK, WS_WEB_SITE_SK, WS_SHIP_MODE_SK, WS_WAREHOUSE_SK, WS_PROMO_SK, WS_ORDER_NUMBER, WS_QUANTITY, WS_WHOLESALE_COST, WS_LIST_PRICE, WS_SALES_PRICE, WS_EXT_DISCOUNT_AMT, WS_EXT_SALES_PRICE, WS_EXT_WHOLESALE_COST, WS_EXT_LIST_PRICE, WS_EXT_TAX, WS_COUPON_AMT, WS_EXT_SHIP_COST, WS_NET_PAID, WS_NET_PAID_INC_TAX, WS_NET_PAID_INC_SHIP, WS_NET_PAID_INC_SHIP_TAX, WS_NET_PROFIT) values
  ( 146, 73904, 127,  1, 30346,  584075, 2251, 44427, 32741,  904668,  555, 41084, 18, 30,  1,  1,  86, 53719, 31, 11.13,  12.91,  11.23,    52.08,   348.13,  345.03,   400.21,    6.96,     .00,  124.00,   348.13,   355.09,   472.13,   479.09,     3.10 ),
  ( 145, 78531, 110,  5,  2683,   11570, 5610, 17297, 83134, 1354709, 1511, 47998, 14, 11, 17,  5,  53, 59539, 81, 80.44, 122.26,  36.67,  6932.79,  2970.27, 6515.64,  9903.06,  207.91,     .00, 2574.18,  2970.27,  3178.18,  5544.45,  5752.36, -3545.37 ),
  ( 140, 66748, 149,  8, 42927,  497584, 4688, 30945, 71561, 1129572, 2963,  4067, 46, 16, 11,  8,  45,  9213, 56, 33.67,  43.43,  27.79,   875.84,  1556.24, 1885.52,  2432.08,   32.68,  466.87,  462.00,  1089.37,  1122.05,  1551.37,  1584.05,  -796.15 ),
  ( 142, 37792, 140,  8,  8977,  224529, 6034, 48786, 91866,   50929,  595, 29747, 16, 20, 13,  8, 234, 44812, 59, 41.76,  81.43,  30.12,  3027.29,  1777.08, 2463.84,  4804.37,   53.31,     .00, 2065.59,  1777.08,  1830.39,  3842.67,  3895.98,  -686.76 ),
  ( 107, 84201, 114, 11,  7424,  825175, 5326, 36277, 35619, 1781560, 2730, 24434, 57, 19, 11, 11, 290, 32941, 35, 75.78, 153.83,  30.76,  4307.45,  1076.60, 2652.30,  5384.05,   21.53,     .00, 1184.40,  1076.60,  1098.13,  2261.00,  2282.53, -1575.70 ),
  ( 104, 78192, 116, 13,  3868, 1459538, 5255, 11325, 21464, 1583435, 4029, 43699, 20, 25,  2, 13, 273, 26139, 20, 87.37, 214.93, 202.03,   258.00,  4040.60, 1747.40,  4298.60,     .00,     .00, 1246.40,  4040.60,  4040.60,  5287.00,  5287.00,  2293.20 ),
  ( 144, 16613, 132,  1, 14955, 1327144, 2886, 19875, 98931,  448883, 4795, 20984, 26,  8, 19,  1, 271, 34980, 40, 83.85, 194.53, 182.85,   467.20,  7314.00, 3354.00,  7781.20,  585.12,     .00,  466.80,  7314.00,  7899.12,  7780.80,  8365.92,  3960.00 ),
  ( 117, 22660, 132,  1, 75000, 1635437, 6274, 46358, 59854, 1670868, 3316, 46953, 11,  5, 13,  1, 287, 56630, 66,  8.56,  11.38,   2.95,   556.38,   194.70,  564.96,   751.08,   11.68,     .00,  247.50,   194.70,   206.38,   442.20,   453.88,  -370.26 ),
  ( 138, 43933, 144,  2,   685,  594553, 3268,  9583,   685,  594553, 3268,  9583,  1, 13, 13,  2, 265, 55085, 97, 81.21, 142.11,  24.15, 11442.12,  2342.55, 7877.37, 13784.67,   43.10, 1803.76, 1791.59,   538.79,   581.89,  2330.38,  2373.48, -7338.58 ),
  ( 132, 33457, 102,  7, 38086, 1919282,   31,  7227, 25449, 1000944, 5968, 10317, 52, 20, 18,  7,  16, 23197, 56, 54.21, 161.54, 117.92,  2442.72,  6603.52, 3035.76,  9046.24,   66.03,     .00,  180.88,  6603.52,  6669.55,  6784.40,  6850.43,  3567.76 ),
  ( 104, 52681, 148,  7, 17001, 1288915,  382, 39896, 17001, 1288915,  382, 39896, 20, 16,  4,  7, 160, 41858, 89, 20.11,  60.12,  45.09,  1337.67,  4013.01, 1789.79,  5350.68,  160.52,     .00, 1229.98,  4013.01,  4173.53,  5242.99,  5403.51,  2223.22 ),
  ( 149,  3906, 141,  9, 20568, 1361763, 3758,  9753, 10425,  218809, 4538, 45333, 39, 13, 14,  9, 141, 33528, 93, 18.89,  31.73,   8.56,  2154.81,   796.08, 1756.77,  2950.89,   71.64,     .00, 1238.76,   796.08,   867.72,  2034.84,  2106.48,  -960.69 ),
  ( 147, 61128, 104,  1, 11300,  685834, 5968, 34902, 39009,  491589, 5704, 33038,  1, 19, 12,  1,  90, 57884, 95, 66.53, 143.70, 107.77,  3413.35, 10238.15, 6320.35, 13651.50,  921.43,     .00, 1228.35, 10238.15, 11159.58, 11466.50, 12387.93,  3917.80 ),
  ( 145, 50592, 149,  7, 31287, 1382440, 1240, 40040, 62405,  109278, 6393, 29593, 21, 27, 20,  7,  69, 31516, 20, 97.88, 115.49,   3.46,  2240.60,    69.20, 1957.60,  2309.80,    6.22,     .00,  161.60,    69.20,    75.42,   230.80,   237.02, -1888.40 ),
  ( 126, 74702, 105,  7, 22342, 1081194, 4046, 16898, 79533, 1136671, 4607, 42659, 58, 20, 19,  7,   5, 32102,  2, 14.21,  20.60,   6.59,    28.02,    13.18,   28.42,    41.20,     .92,     .00,    5.34,    13.18,    14.10,    18.52,    19.44,   -15.24 ),
  ( 146, 24328, 132,  1, 95497, 1265027, 4157,  1687, 56606, 1033466, 1477, 26607, 51, 19, 11,  1,  59, 45074, 67, 36.06,  79.69,  14.34,  4378.45,   960.78, 2416.02,  5339.23,   38.43,     .00, 1975.16,   960.78,   999.21,  2935.94,  2974.37, -1455.24 ),
  ( 109, 78963, 130,  2, 47849, 1900285, 6768, 11251, 47849, 1900285, 6768, 11251, 43, 19, 15,  2,  25, 11100, 47, 85.74, 144.04,  70.57,  3453.09,  3316.79, 4029.78,  6769.88,  199.00,     .00, 2843.03,  3316.79,  3515.79,  6159.82,  6358.82,  -712.99 ),
  ( 122, 73425, 134,  7, 18254, 1274027, 2600,  9573, 55632, 1862929, 3292, 14665,  4, 28, 20,  7,  74,   175,  1, 38.31, 113.01,  91.53,    21.48,    91.53,   38.31,   113.01,    3.66,     .00,   14.69,    91.53,    95.19,   106.22,   109.88,    53.22 ),
  ( 137, 76677, 139,  3, 80533,  989644, 1602, 17960, 82093,  845863, 2234, 29392, 49,  9, 12,  3,  56, 39259,  8, 81.22,  82.84,  39.76,   344.64,   318.08,  649.76,   662.72,    9.54,     .00,  112.64,   318.08,   327.62,   430.72,   440.26,  -331.68 ),
  ( 149, 15352, 137, 15, 47442, 1504802, 1715,  9828, 98159,  453916, 5461, 48774,  1, 15,  2, 15, 103, 38075, 92, 94.23, 100.82,  73.59,  2505.16,  6770.28, 8669.16,  9275.44,   67.70,     .00,  555.68,  6770.28,  6837.98,  7325.96,  7393.66, -1898.88 ),
  ( 104, 68138, 130, 37, 74766,  539590, 5214, 25638, 21142, 1338598, 5310, 10134, 34, 28,  2, 37, 179, 47918, 32, 30.96,  32.50,    .65,  1019.20,    20.80,  990.72,  1040.00,    1.04,     .00,   10.24,    20.80,    21.84,    31.04,    32.08,  -969.92 ),
  ( 119, 73068, 114,  1, 72318,  728461,  604, 35947, 32448,  845146, 5176,  4119, 44,  8,  7,  1,  71, 34641, 59, 32.71,  64.76,  23.31,  2445.55,  1375.29, 1929.89,  3820.84,   27.50,     .00, 1108.02,  1375.29,  1402.79,  2483.31,  2510.81,  -554.60 ),
  ( 101, 77486, 109,  2, 30531, 1581646,  408, 16323, 81897,  301859, 2562, 36477, 50, 19, 14,  2, 119, 51836, 88, 47.07, 111.55,  61.35,  4417.60,  5398.80, 4142.16,  9816.40,  215.41, 2321.48, 2257.20,  3077.32,  3292.73,  5334.52,  5549.93, -1064.84 ),
  ( 109, 54154, 128,  8, 71245,  194019, 4793, 39567, 71245,  194019, 4793, 39567,  8, 19, 15,  8,  78, 27295, 80, 10.14,  28.59,  22.30,   503.20,  1784.00,  811.20,  2287.20,   17.84,     .00,  548.80,  1784.00,  1801.84,  2332.80,  2350.64,   972.80 ),
  ( 121, 12042, 124,  4, 78754, 1194110,  666, 25222, 27244,  516619, 3243, 20199, 32, 10, 12,  4, 107, 28976, 18, 11.65,  15.49,  14.40,    19.62,   259.20,  209.70,   278.82,   23.32,     .00,   16.56,   259.20,   282.52,   275.76,   299.08,    49.50 ),
  ( 127, 24548, 136,  4, 42262,  507174, 1239, 20904, 65584,  701368,  346, 14858,  2,  1, 20,  4, 171, 32519,  6, 91.56, 143.74, 117.86,   155.28,   707.16,  549.36,   862.44,     .00,     .00,  198.36,   707.16,   707.16,   905.52,   905.52,   157.80 ),
  ( 106, 62869, 134,  5,  5868, 1885886, 1104, 12369, 57481, 1278149, 4470, 26488,  3, 13, 15,  5, 158,   320, 32, 78.90, 157.80,  50.49,  3433.92,  1615.68, 2524.80,  5049.60,   64.62,     .00,  807.68,  1615.68,  1680.30,  2423.36,  2487.98,  -909.12 ),
  ( 118, 45793, 141,  1, 57356, 1164148, 3740, 20485, 57356, 1164148, 3740, 20485, 11,  7, 15,  1,  74,    83, 35, 44.79,  79.27,  52.31,   943.60,  1830.85, 1567.65,  2774.45,   54.92,     .00,  416.15,  1830.85,  1885.77,  2247.00,  2301.92,   263.20 ),
  ( 118, 67482, 115,  4, 70656, 1326468, 4327, 14539, 35018,  880867, 6069, 45481, 28, 28, 12,  4,  66,   178, 49, 66.56, 186.36, 108.08,  3835.72,  5295.92, 3261.44,  9131.64,     .00,     .00, 3195.78,  5295.92,  5295.92,  8491.70,  8491.70,  2034.48 ),
  ( 118, 85776, 115,  5, 24328, 1105215, 3349, 10236, 45211, 1916602, 4144, 13878,  1, 13,  3,  5,  48, 29348, 39, 68.78,  89.41,  70.63,   732.42,  2754.57, 2682.42,  3486.99,  110.18,     .00,  592.41,  2754.57,  2864.75,  3346.98,  3457.16,    72.15 ),
  ( 145, 21464, 131,  6, 50056, 1106461,  466, 44118, 70877, 1873515, 2980,  8632, 27, 19,  6,  6, 215, 46213, 17, 75.93, 108.57,   1.08,  1827.33,    18.36, 1290.81,  1845.69,     .36,     .00,  442.85,    18.36,    18.72,   461.21,   461.57, -1272.45 ),
  ( 118, 55579, 110, 11, 91602,  481284, 2966, 35048, 21106,  518129, 5694, 42976, 47,  3, 20, 11, 243, 23658, 40, 12.63,  34.10,  23.18,   436.80,   927.20,  505.20,  1364.00,   18.54,     .00,  477.20,   927.20,   945.74,  1404.40,  1422.94,   422.00 ),
  ( 149, 45646, 124, 11, 83129,  525839, 6502, 20765, 48309,  486168, 6401, 43064, 19, 25, 16, 11, 112, 58570, 74, 78.41, 199.16,  43.81, 11495.90,  3241.94, 5802.34, 14737.84,   42.79, 1102.25, 7368.92,  2139.69,  2182.48,  9508.61,  9551.40, -3662.65 ),
  ( 100, 34484, 122,  4, 37933,  116468, 5403,  3938, 40955, 1764511, 1590,  8925, 32, 14, 16,  4, 265,    28, 19, 27.85,  58.76,  19.97,   737.01,   379.43,  529.15,  1116.44,   22.76,     .00,  100.32,   379.43,   402.19,   479.75,   502.51,  -149.72 ),
  ( 118, 41956, 150,  8, 59094, 1601568, 4842, 10916, 62197,  589020, 3538, 46365, 32, 19, 16,  8,  42,  7434, 34, 83.66, 186.56, 147.38,  1332.12,  5010.92, 2844.44,  6343.04,  150.32,     .00, 1458.60,  5010.92,  5161.24,  6469.52,  6619.84,  2166.48 ),
  ( 119, 64921, 134,  8, 17658, 1206495, 1732,   522, 42282, 1327955, 1433,  5870, 49, 19,  8,  8,  64, 47435, 68, 87.84, 181.82,  74.54,  7295.04,  5068.72, 5973.12, 12363.76,  202.74,     .00, 2224.96,  5068.72,  5271.46,  7293.68,  7496.42,  -904.40 ),
  ( 136, 43867, 119,  7, 31543,  918649, 1308, 26014, 97921,  983982, 3238, 29544, 54,  3,  7,  7,  17, 42663, 85, 47.45, 100.11,  45.04,  4680.95,  3828.40, 4033.25,  8509.35,   20.67, 3598.69, 4084.25,   229.71,   250.38,  4313.96,  4334.63, -3803.54 ),
  ( 145, 39800, 102, 14, 31172, 1631866, 5713, 24760, 13730, 1163955, 5548, 38642, 49, 13, 16, 14, 110, 45875, 69, 39.01,  72.16,  53.39,  1295.13,  3683.91, 2691.69,  4979.04,  184.19,     .00, 2190.75,  3683.91,  3868.10,  5874.66,  6058.85,   992.22 ),
  ( 137, 51883, 110, 19, 43207, 1257387, 1967, 32947, 72248, 1824676, 7134, 36561, 13, 15,  2, 19, 260, 31110, 94, 83.18, 185.49,  46.37, 13077.28,  4358.78, 7818.92, 17436.06,  392.29,     .00, 7148.70,  4358.78,  4751.07, 11507.48, 11899.77, -3460.14 ),
  ( 122, 60378, 126,  2, 31463, 1542916, 2369, 29069, 34022,  607001, 6228, 31328, 22, 22,  9,  2, 109,   140,  7, 62.93, 186.90,  61.67,   876.61,   431.69,  440.51,  1308.30,     .00,     .00,  641.06,   431.69,   431.69,  1072.75,  1072.75,    -8.82 ),
  ( 100, 64500, 145,  2, 24282, 1794798, 5667, 18983, 14666,  572445, 2484, 39800, 32, 10, 12,  2, 227, 59707, 97, 74.80, 192.23, 148.01,  4289.34, 14356.97, 7255.60, 18646.31, 1004.98,     .00, 1864.34, 14356.97, 15361.95, 16221.31, 17226.29,  7101.37 ),
  ( 114, 56111, 131,  5, 41857, 1527917,  730,  7990, 41857, 1527917,  730,  7990, 47, 19,  9,  5,  14, 19157, 56,  1.84,   4.10,   3.03,    59.92,   169.68,  103.04,   229.60,    5.09,     .00,  109.76,   169.68,   174.77,   279.44,   284.53,    66.64 ),
  ( 102, 15490, 138,  1, 74569, 1486015,  661,  5260, 50833,  859346, 1998,  7397, 43, 27, 19,  1, 156, 15446, 98, 59.92, 141.41,  41.00,  9840.18,  4018.00, 5872.16, 13858.18,  281.26,     .00, 6373.92,  4018.00,  4299.26, 10391.92, 10673.18, -1854.16 ),
  ( 123, 68068, 102,  2,  6718,   70619, 4567, 22068, 76202, 1372888, 5065, 14966,  8, 20, 17,  2,  89, 49500, 43, 74.78, 215.36, 155.05,  2593.33,  6667.15, 3215.54,  9260.48,  466.70,     .00, 2500.02,  6667.15,  7133.85,  9167.17,  9633.87,  3451.61 ),
  ( 132, 34313, 149,  3, 63846, 1345724, 2802, 13880,  5070,  288914, 3914, 30592, 51, 25, 17,  3, 198, 27761, 72, 92.81, 180.97,  59.72,  8730.00,  4299.84, 6682.32, 13029.84,  128.99,     .00, 4169.52,  4299.84,  4428.83,  8469.36,  8598.35, -2382.48 ),
  ( 146, 62148, 116,  1, 11500,  724431, 5362, 14340, 73581,  262684, 1533, 23264,  6, 30, 20,  1, 210, 48910, 87, 77.23, 132.83,  53.13,  6933.90,  4622.31, 6719.01, 11556.21,   46.22,     .00, 1733.04,  4622.31,  4668.53,  6355.35,  6401.57, -2096.70 ),
  ( 130, 37748, 136,  5, 73266, 1223930, 6147, 13611, 14167, 1496334, 5156, 28620, 14, 23, 13,  5,  31, 52121,  6, 24.33,  32.35,  24.90,    44.70,   149.40,  145.98,   194.10,    2.98,     .00,   89.28,   149.40,   152.38,   238.68,   241.66,     3.42 ),
  ( 143, 43588, 147,  9, 16355,  897494, 2280, 16619, 16355,  897494, 2280, 16619,  7, 27, 11,  9, 298,   257, 94,  6.69,   8.36,   4.68,   345.92,   439.92,  628.86,   785.84,    8.79,     .00,  306.44,   439.92,   448.71,   746.36,   755.15,  -188.94 ),
  ( 141, 54207, 145,  3, 13185, 1141000, 1168,  6583, 83445,  602701, 3981, 25349, 59, 17, 18,  3, 299,  5337, 26,  8.31,  13.37,   4.54,   229.58,   118.04,  216.06,   347.62,    2.36,     .00,  100.62,   118.04,   120.40,   218.66,   221.02,   -98.02 ),
  ( 106, 77293, 143,  3, 60479,  647984, 5536,  3041,  7936, 1219616, 1603, 31908, 55,  7, 13,  3, 158,    93, 21, 24.66,  68.30,  60.78,   157.92,  1276.38,  517.86,  1434.30,   27.44,  727.53,  458.85,   548.85,   576.29,  1007.70,  1035.14,    30.99 );

insert into date_dim(D_DATE_SK,D_DATE_ID, D_DATE, D_MONTH_SEQ, D_WEEK_SEQ, D_QUARTER_SEQ, D_YEAR, D_DOW, D_MOY, D_DOM, D_QOY, D_FY_YEAR, D_FY_QUARTER_SEQ, D_FY_WEEK_SEQ, D_DAY_NAME, D_QUARTER_NAME, D_HOLIDAY, D_WEEKEND, D_FOLLOWING_HOLIDAY, D_FIRST_DOM, D_LAST_DOM, D_SAME_DAY_LY, D_SAME_DAY_LQ, D_CURRENT_DAY, D_CURRENT_WEEK, D_CURRENT_MONTH, D_CURRENT_QUARTER, D_CURRENT_YEAR) values
  ( 100, 'AAAAAAAAOCKNECAA', date'1900-05-10',  4,  19,  39, 1900, 4,  5, 10, 2, 1900,  39,  19, 'Thursday ', '1900Q2', 'N', 'N', 'N', 2415141, 2415261, 2414785, 2415060, 'N', 'N', 'N', 'N', 'N' ),
  ( 101, 'AAAAAAAAPKKNECAA', date'1900-09-16',  8,  38,  91, 1900, 0,  9, 16, 3, 1900,  91,  38, 'Sunday   ', '1900Q3', 'N', 'N', 'N', 2415264, 2415507, 2414914, 2415188, 'N', 'N', 'N', 'N', 'N' ),
  ( 102, 'AAAAAAAAEPKNECAA', date'1900-11-24', 10,  47, 115, 1900, 6, 11, 24, 4, 1900, 115,  47, 'Saturday ', '1900Q4', 'N', 'Y', 'N', 2415325, 2415629, 2414983, 2415256, 'N', 'N', 'N', 'N', 'Y' ),
  ( 103, 'AAAAAAAAJHKNECAA', date'1900-07-24',  6,  30,  60, 1900, 2,  7, 24, 3, 1900,  60,  30, 'Tuesday  ', '1900Q3', 'N', 'N', 'N', 2415202, 2415383, 2414860, 2415134, 'N', 'N', 'N', 'N', 'N' ),
  ( 104, 'AAAAAAAABMKNECAA', date'1900-10-04',  9,  40,  91, 1900, 4, 10,  4, 4, 1900,  91,  40, 'Thursday ', '1900Q4', 'N', 'N', 'N', 2415294, 2415567, 2414932, 2415205, 'N', 'N', 'N', 'N', 'N' ),
  ( 105, 'AAAAAAAANMKNECAA', date'1900-10-16',  9,  42,  91, 1900, 2, 10, 16, 4, 1900,  91,  42, 'Tuesday  ', '1900Q4', 'N', 'N', 'N', 2415294, 2415567, 2414944, 2415217, 'N', 'N', 'N', 'N', 'N' ),
  ( 106, 'AAAAAAAAJLJNECAA', date'1900-01-13',  0,   2,   1, 1900, 5,  1, 13, 1, 1900,   1,   2, 'Friday   ', '1900Q1', 'N', 'Y', 'N', 2415021, 2415020, 2414668, 2414941, 'N', 'N', 'N', 'N', 'Y' ),
  ( 107, 'AAAAAAAAMBKNECAA', date'1900-04-22',  3,  17,  29, 1900, 0,  4, 22, 2, 1900,  29,  17, 'Sunday   ', '1900Q2', 'N', 'N', 'N', 2415111, 2415201, 2414767, 2415042, 'N', 'N', 'N', 'N', 'N' ),
  ( 108, 'AAAAAAAADIKNECAA', date'1900-08-03',  7,  31,  63, 1900, 5,  8,  3, 3, 1900,  63,  31, 'Friday   ', '1900Q3', 'N', 'Y', 'N', 2415233, 2415445, 2414870, 2415144, 'N', 'N', 'N', 'N', 'N' ),
  ( 109, 'AAAAAAAAANKNECAA', date'1900-10-19',  9,  42,  91, 1900, 5, 10, 19, 4, 1900,  91,  42, 'Friday   ', '1900Q4', 'N', 'Y', 'N', 2415294, 2415567, 2414947, 2415220, 'N', 'N', 'N', 'N', 'N' ),
  ( 110, 'AAAAAAAABBLNECAA', date'1900-12-23', 11,  52, 121, 1900, 0, 12, 23, 4, 1900, 121,  52, 'Sunday   ', '1900Q4', 'N', 'N', 'N', 2415355, 2415689, 2415012, 2415285, 'N', 'N', 'N', 'N', 'Y' ),
  ( 111, 'AAAAAAAAEDLNECAA', date'1901-01-27', 12,  57, 121, 1901, 0,  1, 27, 1, 1901, 121,  57, 'Sunday   ', '1901Q1', 'N', 'N', 'N', 2415386, 2415385, 2415047, 2415320, 'N', 'N', 'N', 'N', 'N' ),
  ( 112, 'AAAAAAAAAGKNECAA', date'1900-06-29',  5,  26,  60, 1900, 5,  6, 29, 2, 1900,  60,  26, 'Friday   ', '1900Q2', 'N', 'Y', 'N', 2415172, 2415323, 2414835, 2415110, 'N', 'N', 'N', 'N', 'N' ),
  ( 113, 'AAAAAAAAOBLNECAA', date'1901-01-05', 12,  53, 121, 1901, 6,  1,  5, 1, 1901, 121,  53, 'Saturday ', '1901Q1', 'N', 'Y', 'N', 2415386, 2415385, 2415025, 2415298, 'N', 'N', 'N', 'N', 'N' ),
  ( 114, 'AAAAAAAABHMNECAA', date'1901-12-10', 23, 102, 241, 1901, 2, 12, 10, 4, 1901, 241, 102, 'Tuesday  ', '1901Q4', 'N', 'N', 'N', 2415720, 2416053, 2415364, 2415637, 'N', 'N', 'N', 'N', 'N' ),
  ( 115, 'AAAAAAAAFMJNECAA', date'1900-01-25',  0,   4,   1, 1900, 3,  1, 25, 1, 1900,   1,   4, 'Wednesday', '1900Q1', 'N', 'N', 'N', 2415021, 2415020, 2414680, 2414953, 'N', 'N', 'N', 'N', 'N' ),
  ( 116, 'AAAAAAAAGEKNECAA', date'1900-06-03',  5,  23,  60, 1900, 0,  6,  3, 2, 1900,  60,  23, 'Sunday   ', '1900Q2', 'N', 'N', 'N', 2415172, 2415323, 2414809, 2415084, 'N', 'N', 'N', 'N', 'Y' ),
  ( 117, 'AAAAAAAAONKNECAA', date'1900-11-02', 10,  44,  93, 1900, 5, 11,  2, 4, 1900,  93,  44, 'Friday   ', '1900Q4', 'N', 'Y', 'N', 2415325, 2415629, 2414961, 2415234, 'N', 'N', 'N', 'N', 'N' ),
  ( 118, 'AAAAAAAAIFLNECAA', date'1901-03-04', 14,  62, 149, 1901, 1,  3,  4, 1, 1901, 149,  62, 'Monday   ', '1901Q1', 'N', 'N', 'N', 2415445, 2415503, 2415083, 2415356, 'N', 'N', 'N', 'N', 'N' ),
  ( 119, 'AAAAAAAABCMNECAA', date'1901-09-21', 20,  90, 211, 1901, 6,  9, 21, 3, 1901, 211,  90, 'Saturday ', '1901Q3', 'N', 'Y', 'N', 2415629, 2415871, 2415284, 2415558, 'N', 'N', 'N', 'N', 'N' ),
  ( 120, 'AAAAAAAAMDMNECAA', date'1901-10-18', 21,  94, 211, 1901, 5, 10, 18, 4, 1901, 211,  94, 'Friday   ', '1901Q4', 'N', 'Y', 'N', 2415659, 2415931, 2415311, 2415584, 'N', 'N', 'N', 'N', 'N' ),
  ( 121, 'AAAAAAAACEKNECAA', date'1900-05-30',  4,  22,  59, 1900, 3,  5, 30, 2, 1900,  59,  22, 'Wednesday', '1900Q2', 'N', 'N', 'N', 2415141, 2415261, 2414805, 2415080, 'N', 'N', 'N', 'N', 'N' ),
  ( 122, 'AAAAAAAAKEKNECAA', date'1900-06-07',  5,  23,  60, 1900, 4,  6,  7, 2, 1900,  60,  23, 'Thursday ', '1900Q2', 'N', 'N', 'N', 2415172, 2415323, 2414813, 2415088, 'N', 'N', 'N', 'N', 'Y' ),
  ( 123, 'AAAAAAAACCLNECAA', date'1901-01-09', 12,  54, 121, 1901, 3,  1,  9, 1, 1901, 121,  54, 'Wednesday', '1901Q1', 'N', 'N', 'N', 2415386, 2415385, 2415029, 2415302, 'N', 'N', 'N', 'N', 'N' ),
  ( 124, 'AAAAAAAAJDKNECAA', date'1900-05-21',  4,  21,  50, 1900, 1,  5, 21, 2, 1900,  50,  21, 'Monday   ', '1900Q2', 'N', 'N', 'N', 2415141, 2415261, 2414796, 2415071, 'N', 'N', 'N', 'N', 'N' ),
  ( 125, 'AAAAAAAABIKNECAA', date'1900-08-01',  7,  31,  61, 1900, 3,  8,  1, 3, 1900,  61,  31, 'Wednesday', '1900Q3', 'N', 'N', 'N', 2415233, 2415445, 2414868, 2415142, 'N', 'N', 'N', 'N', 'Y' ),
  ( 126, 'AAAAAAAAGNKNECAA', date'1900-10-25',  9,  43,  91, 1900, 4, 10, 25, 4, 1900,  91,  43, 'Thursday ', '1900Q4', 'N', 'N', 'N', 2415294, 2415567, 2414953, 2415226, 'N', 'N', 'N', 'N', 'N' ),
  ( 127, 'AAAAAAAAKOJNECAA', date'1900-03-03',  2,   9,  29, 1900, 6,  3,  3, 1, 1900,  29,   9, 'Saturday ', '1900Q1', 'N', 'Y', 'N', 2415080, 2415139, 2414717, 2414990, 'N', 'N', 'N', 'N', 'N' ),
  ( 128, 'AAAAAAAAJFKNECAA', date'1900-06-22',  5,  25,  60, 1900, 5,  6, 22, 2, 1900,  60,  25, 'Friday   ', '1900Q2', 'N', 'Y', 'N', 2415172, 2415323, 2414828, 2415103, 'N', 'N', 'N', 'N', 'N' ),
  ( 129, 'AAAAAAAAPMKNECAA', date'1900-10-18',  9,  42,  91, 1900, 4, 10, 18, 4, 1900,  91,  42, 'Thursday ', '1900Q4', 'N', 'N', 'N', 2415294, 2415567, 2414946, 2415219, 'N', 'N', 'N', 'N', 'N' ),
  ( 130, 'AAAAAAAAOFKNECAA', date'1900-06-27',  5,  26,  60, 1900, 3,  6, 27, 2, 1900,  60,  26, 'Wednesday', '1900Q2', 'N', 'N', 'N', 2415172, 2415323, 2414833, 2415108, 'N', 'N', 'N', 'N', 'N' ),
  ( 131, 'AAAAAAAANALNECAA', date'1900-12-19', 11,  51, 121, 1900, 3, 12, 19, 4, 1900, 121,  51, 'Wednesday', '1900Q4', 'N', 'N', 'N', 2415355, 2415689, 2415008, 2415281, 'N', 'N', 'N', 'N', 'N' ),
  ( 132, 'AAAAAAAAGLLNECAA', date'1901-06-06', 17,  75, 180, 1901, 4,  6,  6, 2, 1901, 180,  75, 'Thursday ', '1901Q2', 'N', 'N', 'N', 2415537, 2415687, 2415177, 2415452, 'N', 'N', 'N', 'N', 'Y' ),
  ( 133, 'AAAAAAAAOOJNECAA', date'1900-03-07',  2,  10,  29, 1900, 3,  3,  7, 1, 1900,  29,  10, 'Wednesday', '1900Q1', 'N', 'N', 'N', 2415080, 2415139, 2414721, 2414994, 'N', 'N', 'N', 'N', 'N' ),
  ( 134, 'AAAAAAAAJPKNECAA', date'1900-11-29', 10,  48, 120, 1900, 4, 11, 29, 4, 1900, 120,  48, 'Thursday ', '1900Q4', 'N', 'N', 'N', 2415325, 2415629, 2414988, 2415261, 'N', 'N', 'N', 'N', 'N' ),
  ( 135, 'AAAAAAAAJELNECAA', date'1901-02-17', 13,  60, 138, 1901, 0,  2, 17, 1, 1901, 138,  60, 'Sunday   ', '1901Q1', 'N', 'N', 'N', 2415417, 2415447, 2415068, 2415341, 'N', 'N', 'N', 'N', 'Y' ),
  ( 136, 'AAAAAAAAHMJNECAA', date'1900-01-27',  0,   4,   1, 1900, 5,  1, 27, 1, 1900,   1,   4, 'Friday   ', '1900Q1', 'N', 'Y', 'N', 2415021, 2415020, 2414682, 2414955, 'N', 'N', 'N', 'N', 'N' ),
  ( 137, 'AAAAAAAAAJKNECAA', date'1900-08-16',  7,  33,  76, 1900, 4,  8, 16, 3, 1900,  76,  33, 'Thursday ', '1900Q3', 'N', 'N', 'N', 2415233, 2415445, 2414883, 2415157, 'N', 'N', 'N', 'N', 'N' ),
  ( 138, 'AAAAAAAAHOKNECAA', date'1900-11-11', 10,  46, 102, 1900, 0, 11, 11, 4, 1900, 102,  46, 'Sunday   ', '1900Q4', 'N', 'N', 'N', 2415325, 2415629, 2414970, 2415243, 'N', 'N', 'N', 'N', 'N' ),
  ( 139, 'AAAAAAAAEMJNECAA', date'1900-01-24',  0,   4,   1, 1900, 2,  1, 24, 1, 1900,   1,   4, 'Tuesday  ', '1900Q1', 'N', 'N', 'N', 2415021, 2415020, 2414679, 2414952, 'N', 'N', 'N', 'N', 'N' ),
  ( 140, 'AAAAAAAAHGLNECAA', date'1901-03-19', 14,  64, 149, 1901, 2,  3, 19, 1, 1901, 149,  64, 'Tuesday  ', '1901Q1', 'N', 'N', 'N', 2415445, 2415503, 2415098, 2415371, 'N', 'N', 'N', 'N', 'N' ),
  ( 141, 'AAAAAAAALKLNECAA', date'1901-05-26', 16,  74, 175, 1901, 0,  5, 26, 2, 1901, 175,  74, 'Sunday   ', '1901Q2', 'N', 'N', 'N', 2415506, 2415625, 2415166, 2415441, 'N', 'N', 'N', 'N', 'Y' ),
  ( 142, 'AAAAAAAAJOJNECAA', date'1900-03-02',  2,   9,  29, 1900, 5,  3,  2, 1, 1900,  29,   9, 'Friday   ', '1900Q1', 'N', 'Y', 'N', 2415080, 2415139, 2414716, 2414989, 'N', 'N', 'N', 'N', 'N' ),
  ( 143, 'AAAAAAAAGBKNECAA', date'1900-04-16',  3,  16,  29, 1900, 1,  4, 16, 2, 1900,  29,  16, 'Monday   ', '1900Q2', 'N', 'N', 'N', 2415111, 2415201, 2414761, 2415036, 'N', 'N', 'N', 'N', 'N' ),
  ( 144, 'AAAAAAAAADKNECAA', date'1900-05-12',  4,  19,  41, 1900, 6,  5, 12, 2, 1900,  41,  19, 'Saturday ', '1900Q2', 'N', 'Y', 'N', 2415141, 2415261, 2414787, 2415062, 'N', 'N', 'N', 'N', 'N' ),
  ( 145, 'AAAAAAAAIOJNECAA', date'1900-03-01',  2,   9,  29, 1900, 4,  3,  1, 1, 1900,  29,   9, 'Thursday ', '1900Q1', 'N', 'N', 'N', 2415080, 2415139, 2414715, 2414988, 'N', 'N', 'N', 'N', 'Y' ),
  ( 146, 'AAAAAAAADBLNECAA', date'1900-12-25', 11,  52, 121, 1900, 2, 12, 25, 4, 1900, 121,  52, 'Tuesday  ', '1900Q4', 'Y', 'N', 'N', 2415355, 2415689, 2415014, 2415287, 'N', 'N', 'N', 'N', 'N' ),
  ( 147, 'AAAAAAAAHBLNECAA', date'1900-12-29', 11,  52, 121, 1900, 6, 12, 29, 4, 1900, 121,  52, 'Saturday ', '1900Q4', 'N', 'Y', 'N', 2415355, 2415689, 2415018, 2415291, 'N', 'N', 'N', 'N', 'N' ),
  ( 148, 'AAAAAAAAKCKNECAA', date'1900-05-06',  4,  19,  35, 1900, 0,  5,  6, 2, 1900,  35,  19, 'Sunday   ', '1900Q2', 'N', 'N', 'N', 2415141, 2415261, 2414781, 2415056, 'N', 'N', 'N', 'N', 'Y' ),
  ( 149, 'AAAAAAAALOKNECAA', date'1900-11-15', 10,  46, 106, 1900, 4, 11, 15, 4, 1900, 106,  46, 'Thursday ', '1900Q4', 'N', 'N', 'N', 2415325, 2415629, 2414974, 2415247, 'N', 'N', 'N', 'N', 'N' );

--============================================================================
?section T014_Q_SJ2
prepare QueryStmt from
  SELECT
   dim1_1.CLDR_DT   Col7 
,  dim1_2.CLDR_DT   Col8 
FROM       CI_DTL_F  fact 
INNER JOIN DT_DAY_D  dim1_2 
  ON ( fact.APP_PLN_SNST_GMT_DT_KY  =  dim1_2.DT_DAY_KY )
INNER JOIN DT_DAY_D  dim1_1
  ON ( fact.APP_ACTL_SNST_GMT_DT_KY  =  dim1_1.DT_DAY_KY )
GROUP BY 1, 2
ORDER BY 1, 2;

?section Simple_SJ2
--============================================================================
--== Simple MV, single SJ segment, size 2
--============================================================================

CREATE MV T014_MV_SJ2
  RECOMPUTE INITIALIZE ON CREATE
  ENABLE QUERY REWRITE AS
  SELECT   dim1_1.CLDR_DT   Col7 
          ,dim1_2.CLDR_DT   Col8 
  FROM  CI_DTL_F  fact 
  INNER JOIN DT_DAY_D  dim1_1 
     ON ( fact.APP_PLN_SNST_GMT_DT_KY  =  dim1_1.DT_DAY_KY )
  INNER JOIN DT_DAY_D  dim1_2 
     ON ( fact.APP_ACTL_SNST_GMT_DT_KY  =  dim1_2.DT_DAY_KY )
  GROUP BY 1, 2;

set pattern $$MVName$$ T014_MV_SJ2;
select * from $$MVName$$ order by col7, col8;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014.T014_MV_SJ2';

-- Match with different order
set pattern $$QueryName$$ T014_Q_SJ2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> T014_sj2.in;
sh echo PUBLISH T014_MV_SJ2.xml >> T014_sj2.in;
sh echo MATCH T014_Q_SJ2.xml    >> T014_sj2.in;

sh sh -c "$QMS T014_sj2.in T014_sj2.out";

log;
sh cat T014_sj2.out >> LOG014;
log LOG014;

-- Verify with different order
obey TEST014(compare);

--============================================================================
?section T014_Q_SJ22
prepare QueryStmt from
  SELECT
   dim1_1.CLDR_DT   Col1 
,  dim1_2.CLDR_DT   Col2 
,  dim2_1.DSPLY_DN  Col3
,  dim2_2.DSPLY_DN  Col4
FROM       CI_DTL_F  fact 
INNER JOIN DT_DAY_D  dim1_2 
  ON ( fact.APP_PLN_SNST_GMT_DT_KY  =  dim1_2.DT_DAY_KY )
INNER JOIN DT_DAY_D  dim1_1 
  ON ( fact.APP_ACTL_SNST_GMT_DT_KY  =  dim1_1.DT_DAY_KY )
INNER JOIN HPSC_MSTR_L_CD_D  dim2_1 
  ON ( fact.LEG_CO_KY  =  dim2_1.HPSC_MSTR_L_KY )
INNER JOIN HPSC_MSTR_L_CD_D  dim2_2 
  ON ( fact.CI_CRTCLTY_KY  =  dim2_2.HPSC_MSTR_L_KY )
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2, 3, 4;

?section Medium_SJ22
--============================================================================
--== Two SJ segments, size 2,2
--============================================================================

CREATE MV T014_MV_SJ22
  RECOMPUTE INITIALIZE ON CREATE
  ENABLE QUERY REWRITE AS
  SELECT
   dim1_1.CLDR_DT   Col1 
,  dim1_2.CLDR_DT   Col2 
,  dim2_1.DSPLY_DN  Col3
,  dim2_2.DSPLY_DN  Col4
FROM       CI_DTL_F  fact 
INNER JOIN DT_DAY_D  dim1_1 
  ON ( fact.APP_PLN_SNST_GMT_DT_KY  =  dim1_1.DT_DAY_KY )
INNER JOIN DT_DAY_D  dim1_2 
  ON ( fact.APP_ACTL_SNST_GMT_DT_KY  =  dim1_2.DT_DAY_KY )
INNER JOIN HPSC_MSTR_L_CD_D  dim2_1 
  ON ( fact.LEG_CO_KY  =  dim2_1.HPSC_MSTR_L_KY )
INNER JOIN HPSC_MSTR_L_CD_D  dim2_2 
  ON ( fact.CI_CRTCLTY_KY  =  dim2_2.HPSC_MSTR_L_KY )
GROUP BY 1, 2, 3, 4;

set pattern $$MVName$$ T014_MV_SJ22;
select * from $$MVName$$ order by col1, col2, col3, col4;

obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014.T014_MV_SJ22';

-- Match with different order
set pattern $$QueryName$$ T014_Q_SJ22;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);
obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> T014_sj22.in;
sh echo PUBLISH T014_MV_SJ22.xml >> T014_sj22.in;
sh echo MATCH T014_Q_SJ22.xml    >> T014_sj22.in;

sh sh -c "$QMS T014_sj22.in T014_sj22.out";

log;
sh cat T014_sj22.out >> LOG014;
log LOG014;

-- Verify with different order
obey TEST014(compare);

--============================================================================
?section T014_Q_SJ23_SAME;
prepare QueryStmt from
  SELECT
   CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_LVL_2_NM   Col0 
,  CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_LVL_3_NM   Col1 
,  CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_LVL_4_NM   Col2 
,  ADRAD_1.APP_PRTFL_ID   Col3 
,  ADRAD_1.APP_CI_LGCL_NM   Col4 
,  CIDDRCD_1.CI_ALIAS_NM   Col5 
,  CIDDRCD_1.CI_NM   Col6 
,  ACPSGDRDDD_1.CLDR_DT   Col7 
,  ACASGDRDDD_1.CLDR_DT   Col8 
,  CLCRHMLCD_1.MSTR_L_COL_ID Col9
,  ADRAD_1.APP_TYPE_DN   Col10 
,  ADRAD_1.SMPLFCTN_APRCH_DN   Col11
,  CIDDRCD_1.SRC_SYS_UPD_DT   Col12 
,  CLCRHMLCD_1.DSPLY_DN     Col13
FROM       CI_DTL_F  CIDFRCDF_1 
INNER JOIN ASGN_GRP_ORG_HIER_D  CIAOAGOHDRAGOHD_1 
  ON ( CIDFRCDF_1.IT_ASSET_OWN_AG_ORG_HIER_KY  =  CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_HIER_KY )
INNER JOIN APP_D  ADRAD_1 
  ON ( CIDFRCDF_1.APP_KY  =  ADRAD_1.APP_KY )
INNER JOIN CI_D  CIDDRCD_1 
  ON ( CIDFRCDF_1.CI_D_KY  =  CIDDRCD_1.CI_D_KY )
INNER JOIN DT_DAY_D  ACPSGDRDDD_1 
  ON ( CIDFRCDF_1.APP_PLN_SNST_GMT_DT_KY  =  ACPSGDRDDD_1.DT_DAY_KY )
INNER JOIN DT_DAY_D  ACASGDRDDD_1 
  ON ( CIDFRCDF_1.APP_ACTL_SNST_GMT_DT_KY  =  ACASGDRDDD_1.DT_DAY_KY )
INNER JOIN HPSC_MSTR_L_CD_D  CLCRHMLCD_1 
  ON ( CIDFRCDF_1.LEG_CO_KY  =  CLCRHMLCD_1.HPSC_MSTR_L_KY )
INNER JOIN HPSC_MSTR_L_CD_D  CICDVDRHMLCD_1 
  ON ( CIDFRCDF_1.CI_CRTCLTY_KY  =  CICDVDRHMLCD_1.HPSC_MSTR_L_KY )
INNER JOIN HPSC_MSTR_L_CD_D  CISDVDRHMLCD_1 
  ON ( CIDFRCDF_1.CI_STAT_KY  =  CISDVDRHMLCD_1.HPSC_MSTR_L_KY )
INNER JOIN DVC_TYPE_D  DTDDRDTD_1 
  ON ( CIDFRCDF_1.DVC_TYPE_KY  =  DTDDRDTD_1.DVC_TYPE_KY )
WHERE (( DTDDRDTD_1.DVC_TYPE_DN  IN ('application') 
  AND  ADRAD_1.LGCL_DEL_FG  = 'n' 
  AND  CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_LVL_2_NM  IN ('hp.com it') 
  AND  CIDDRCD_1.LGCL_DEL_FG  = 'n'))
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14;

?section T014_Q_SJ23_DIFF;
prepare QueryStmt from
  SELECT
   CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_LVL_2_NM   Col0 
,  CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_LVL_3_NM   Col1 
,  CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_LVL_4_NM   Col2 
,  ADRAD_1.APP_PRTFL_ID   Col3 
,  ADRAD_1.APP_CI_LGCL_NM   Col4 
,  CIDDRCD_1.CI_ALIAS_NM   Col5 
,  CIDDRCD_1.CI_NM   Col6 
,  ACPSGDRDDD_1.CLDR_DT   Col7 
,  ACASGDRDDD_1.CLDR_DT   Col8 
,  CLCRHMLCD_1.MSTR_L_COL_ID Col9
,  ADRAD_1.APP_TYPE_DN   Col10 
,  ADRAD_1.SMPLFCTN_APRCH_DN   Col11
,  CIDDRCD_1.SRC_SYS_UPD_DT   Col12 
,  CLCRHMLCD_1.DSPLY_DN     Col13
FROM   CI_DTL_F  CIDFRCDF_1 
      ,ASGN_GRP_ORG_HIER_D  CIAOAGOHDRAGOHD_1 
      ,APP_D  ADRAD_1 
      ,CI_D  CIDDRCD_1 
      ,DT_DAY_D  ACASGDRDDD_1 
      ,DT_DAY_D  ACPSGDRDDD_1 
      ,HPSC_MSTR_L_CD_D  CISDVDRHMLCD_1 
      ,HPSC_MSTR_L_CD_D  CICDVDRHMLCD_1 
      ,HPSC_MSTR_L_CD_D  CLCRHMLCD_1 
      ,DVC_TYPE_D  DTDDRDTD_1 
WHERE (( DTDDRDTD_1.DVC_TYPE_DN  IN ('application') 
  AND ( CIDFRCDF_1.IT_ASSET_OWN_AG_ORG_HIER_KY  =  CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_HIER_KY )
  AND ( CIDFRCDF_1.APP_KY  =  ADRAD_1.APP_KY )
  AND ( CIDFRCDF_1.CI_D_KY  =  CIDDRCD_1.CI_D_KY )
  AND ( CIDFRCDF_1.APP_PLN_SNST_GMT_DT_KY  =  ACPSGDRDDD_1.DT_DAY_KY )
  AND ( CIDFRCDF_1.APP_ACTL_SNST_GMT_DT_KY  =  ACASGDRDDD_1.DT_DAY_KY )
  AND ( CIDFRCDF_1.LEG_CO_KY  =  CLCRHMLCD_1.HPSC_MSTR_L_KY )
  AND ( CIDFRCDF_1.CI_CRTCLTY_KY  =  CICDVDRHMLCD_1.HPSC_MSTR_L_KY )
  AND ( CIDFRCDF_1.CI_STAT_KY  =  CISDVDRHMLCD_1.HPSC_MSTR_L_KY )
  AND ( CIDFRCDF_1.DVC_TYPE_KY  =  DTDDRDTD_1.DVC_TYPE_KY )
  AND  ADRAD_1.LGCL_DEL_FG  = 'n' 
  AND  CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_LVL_2_NM  IN ('hp.com it') 
  AND  CIDDRCD_1.LGCL_DEL_FG  = 'n'))
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14;

?section Complex_SJ23
--============================================================================
--== Two SJ segments, size 2,3
--============================================================================

CREATE MV T014_MV_SJ23
  RECOMPUTE INITIALIZE ON CREATE
  ENABLE QUERY REWRITE AS
  SELECT
   CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_LVL_2_NM   Col0 
,  CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_LVL_3_NM   Col1 
,  CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_LVL_4_NM   Col2 
,  ADRAD_1.APP_PRTFL_ID   Col3 
,  ADRAD_1.APP_CI_LGCL_NM   Col4 
,  CIDDRCD_1.CI_ALIAS_NM   Col5 
,  CIDDRCD_1.CI_NM   Col6 
,  ACPSGDRDDD_1.CLDR_DT   Col7 
,  ACASGDRDDD_1.CLDR_DT   Col8 
,  CLCRHMLCD_1.MSTR_L_COL_ID Col9
,  ADRAD_1.APP_TYPE_DN   Col10 
,  ADRAD_1.SMPLFCTN_APRCH_DN   Col11
,  CIDDRCD_1.SRC_SYS_UPD_DT   Col12 
,  CLCRHMLCD_1.DSPLY_DN     Col13
FROM       CI_DTL_F  CIDFRCDF_1 
INNER JOIN ASGN_GRP_ORG_HIER_D  CIAOAGOHDRAGOHD_1 
  ON ( CIDFRCDF_1.IT_ASSET_OWN_AG_ORG_HIER_KY  =  CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_HIER_KY )
INNER JOIN APP_D  ADRAD_1 
  ON ( CIDFRCDF_1.APP_KY  =  ADRAD_1.APP_KY )
INNER JOIN CI_D  CIDDRCD_1 
  ON ( CIDFRCDF_1.CI_D_KY  =  CIDDRCD_1.CI_D_KY )
INNER JOIN DT_DAY_D  ACPSGDRDDD_1 
  ON ( CIDFRCDF_1.APP_PLN_SNST_GMT_DT_KY  =  ACPSGDRDDD_1.DT_DAY_KY )
INNER JOIN DT_DAY_D  ACASGDRDDD_1 
  ON ( CIDFRCDF_1.APP_ACTL_SNST_GMT_DT_KY  =  ACASGDRDDD_1.DT_DAY_KY )
INNER JOIN HPSC_MSTR_L_CD_D  CLCRHMLCD_1 
  ON ( CIDFRCDF_1.LEG_CO_KY  =  CLCRHMLCD_1.HPSC_MSTR_L_KY )
INNER JOIN HPSC_MSTR_L_CD_D  CICDVDRHMLCD_1 
  ON ( CIDFRCDF_1.CI_CRTCLTY_KY  =  CICDVDRHMLCD_1.HPSC_MSTR_L_KY )
INNER JOIN HPSC_MSTR_L_CD_D  CISDVDRHMLCD_1 
  ON ( CIDFRCDF_1.CI_STAT_KY  =  CISDVDRHMLCD_1.HPSC_MSTR_L_KY )
INNER JOIN DVC_TYPE_D  DTDDRDTD_1 
  ON ( CIDFRCDF_1.DVC_TYPE_KY  =  DTDDRDTD_1.DVC_TYPE_KY )
WHERE (( DTDDRDTD_1.DVC_TYPE_DN  IN ('application') 
  AND  ADRAD_1.LGCL_DEL_FG  = 'n' 
  AND  CIAOAGOHDRAGOHD_1.ASGN_GRP_ORG_LVL_2_NM  IN ('hp.com it') 
  AND  CIDDRCD_1.LGCL_DEL_FG  = 'n'))
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14;

set pattern $$MVName$$ T014_MV_SJ23;
select * from $$MVName$$ order by col1, col7, col8;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014.T014_MV_SJ23';

-- Match same order
set pattern $$QueryName$$ T014_Q_SJ23_SAME;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);
obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE                  >> T014_sj23_s.in;
sh echo PUBLISH T014_MV_SJ23.xml    >> T014_sj23_s.in;
sh echo MATCH T014_Q_SJ23_SAME.xml  >> T014_sj23_s.in;

sh sh -c "$QMS T014_sj23_s.in T014_sj23_s.out";

log;
sh cat T014_sj23_s.out >> LOG014;
log LOG014;

-- Verify same order
obey TEST014(compare);

-- Match different order
set pattern $$QueryName$$ T014_Q_SJ23_DIFF;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE                  >> T014_sj23_d.in;
sh echo PUBLISH T014_MV_SJ23.xml    >> T014_sj23_d.in;
sh echo MATCH T014_Q_SJ23_DIFF.xml  >> T014_sj23_d.in;

sh sh -c "$QMS T014_sj23_d.in T014_sj23_d.out";

log;
sh cat T014_sj23_d.out >> LOG014;
log LOG014;

-- Verify different order

obey TEST014(compare);

--============================================================================
?section T014_QSAME_SSJ2_SRES
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_purpose <> p2.p_purpose
  order by 1,2;

?section T014_QDIFF_SSJ2_SRES;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from promotion p2 join item on (item.i_item_sk = p2.p_item_sk)
                    join promotion p1 on (item.i_item_sk = p1.p_item_sk and
                                          p2.p_purpose <> p1.p_purpose)
  order by 1,2;

?section T014_Q1_SSJ2_SRES;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_purpose <> p2.p_purpose
    and i_product_name like '%pad%'
  order by 1,2;

?section T014_Q2_SSJ2_SRES;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_purpose <> p2.p_purpose
    and i_product_name >= 'A'
  order by 1,2;

?section T014_Q3_SSJ2_SRES;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_purpose <> p2.p_purpose
    and i_rec_start_date >= date'10-01-1997'
  order by 1,2;

?section T014_Q4_SSJ2_SRES;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_purpose <> p2.p_purpose
    and p1.p_cost > 1000
  order by 1,2;

?section SSJ2_SRES
--============================================================================
-- Two-way symmetric self-join with symmetric residual predicate.
--============================================================================
CREATE MV T014_MV_SSJ2_SRES
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_purpose <> p2.p_purpose;

set pattern $$MVName$$ T014_MV_SSJ2_SRES;
select * from $$MVName$$ order by i_item_sk, i_product_name;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_SSJ2_SRES';

-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_SSJ2_SRES;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_SSJ2_SRES.in T014_QSAME_SSJ2_SRES.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV, with syntactic and order variation.
set pattern $$QueryName$$ T014_QDIFF_SSJ2_SRES;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_SSJ2_SRES.in T014_QDIFF_SSJ2_SRES.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV, with syntactic and order variation.
obey TEST014(compare);

-- MATCH query with additional residual predicate.
set pattern $$QueryName$$ T014_Q1_SSJ2_SRES;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q1_SSJ2_SRES.in T014_Q1_SSJ2_SRES.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional residual predicate.
obey TEST014(compare);

----------------------
-- MATCH query with additional range predicate.
set pattern $$QueryName$$ T014_Q2_SSJ2_SRES;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q2_SSJ2_SRES.in T014_Q2_SSJ2_SRES.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional range predicate.
obey TEST014(compare);

----------------------
-- MATCH query with additional range predicate requiring back-join to non-self-join table.
set pattern $$QueryName$$ T014_Q3_SSJ2_SRES;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q3_SSJ2_SRES.in T014_Q3_SSJ2_SRES.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional range predicate requiring back-join to non-self-join table.
obey TEST014(compare);

----------------------
-- MATCH query with additional range predicate requiring back-join to self-join table.
set pattern $$QueryName$$ T014_Q4_SSJ2_SRES;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q4_SSJ2_SRES.in T014_Q4_SSJ2_SRES.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional range predicate requiring back-join to self-join table.
obey TEST014(compare);

----------------------
?section T014_QSAME_SSJ2_SRNG;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_discount_active = 'Y' 
    and p2.p_discount_active = 'Y'
  order by 1,2;

?section T014_QDIFF_SSJ2_SRNG;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from promotion p1
       join promotion p2 on (p1.p_item_sk = p2.p_item_sk)
       join item on (p2.p_item_sk = item.i_item_sk)
  where p2.p_discount_active = 'Y' 
    and p1.p_discount_active = 'Y'
  order by 1,2;

?section T014_Q1_SSJ2_SRNG;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_discount_active = 'Y' 
    and p1.p_discount_active = 'Y'
  order by 1,2;

?section T014_Q2_SSJ2_SRNG;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_discount_active = 'Y' 
    and p2.p_discount_active = 'N'
  order by 1,2;

?section T014_Q3_SSJ2_SRNG;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_discount_active = 'Y' 
    and p2.p_discount_active = 'Y'
    and p1.p_cost > p2.p_cost
  order by 1,2;

?section SSJ2_SRNG
--============================================================================
-- Two-way symmetric self-join with symmetric range predicates.
--============================================================================
CREATE MV T014_MV_SSJ2_SRNG(itemSk, prodName, promo1sk, promo2sk)
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select i_item_sk, i_product_name, p1.p_promo_sk, p2.p_promo_sk
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_discount_active = 'Y' 
    and p2.p_discount_active = 'Y';

set pattern $$MVName$$ T014_MV_SSJ2_SRNG;
select * from $$MVName$$ order by itemSk, prodName, promo1sk, promo2sk;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_SSJ2_SRNG';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_SSJ2_SRNG;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_SSJ2_SRNG.in T014_QSAME_SSJ2_SRNG.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variations.
set pattern $$QueryName$$ T014_QDIFF_SSJ2_SRNG;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_SSJ2_SRNG.in T014_QDIFF_SSJ2_SRNG.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variations.
obey TEST014(compare);

----------------------
-- MATCH query with duplication of one MV range pred on self-join table, and
-- omission of range pred on another.
set pattern $$QueryName$$ T014_Q1_SSJ2_SRNG;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q1_SSJ2_SRNG.in T014_Q1_SSJ2_SRNG.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

--------------------------
-- MATCH query with mismatch of one range predicate, other one matches.
set pattern $$QueryName$$ T014_Q2_SSJ2_SRNG;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q2_SSJ2_SRNG.in T014_Q2_SSJ2_SRNG.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

----------------------
-- MATCH query with addition of residual predicate requiring back-join to both
-- self-join instances.
set pattern $$QueryName$$ T014_Q3_SSJ2_SRNG;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q3_SSJ2_SRNG.in T014_Q3_SSJ2_SRNG.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with addition of residual predicate requiring back-join to both
-- self-join instances.
obey TEST014(compare);

------------------
?section T014_QSAME_SSJ2_CYCLE;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_end_date_sk = p2.p_end_date_sk
  order by 1,2;

?section T014_QDIFF_SSJ2_CYCLE;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from promotion p1 join
       promotion p2 on (p1.p_end_date_sk = p2.p_end_date_sk and
                        p1.p_item_sk = p2.p_item_sk) join
       item on (item.i_item_sk = p1.p_item_sk)
  order by 1,2;

?section T014_Q1_SSJ2_CYCLE;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from promotion p1 join
       promotion p2 on (p1.p_end_date_sk = p2.p_end_date_sk and
                        p1.p_item_sk = p2.p_item_sk) join
       item on (item.i_item_sk = p1.p_item_sk) join
       date_dim on (p1.p_end_date_sk = date_dim.d_date_sk)
  order by 1,2;

?section T014_Q2_SSJ2_CYCLE;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_start_date_sk = p2.p_start_date_sk
    and p1.p_end_date_sk = p2.p_end_date_sk
  order by 1,2;

?section SSJ2_CYCLE
--============================================================================
-- Two-way symmetric self-join with additional join predicate directly linking
-- the two instances.
--============================================================================
CREATE MV T014_MV_SSJ2_CYCLE
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select i_item_sk, i_product_name, p1.p_end_date_sk
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_end_date_sk = p2.p_end_date_sk;

set pattern $$MVName$$ T014_MV_SSJ2_CYCLE;
select * from $$MVName$$ order by i_item_sk, i_product_name, p_end_date_sk;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_SSJ2_CYCLE';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_SSJ2_CYCLE;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_SSJ2_CYCLE.in T014_QSAME_SSJ2_CYCLE.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order differences.
set pattern $$QueryName$$ T014_QDIFF_SSJ2_CYCLE;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_SSJ2_CYCLE.in T014_QDIFF_SSJ2_CYCLE.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order differences.
obey TEST014(compare);

----------------------
-- MATCH query with superset of join graph.
set pattern $$QueryName$$ T014_Q1_SSJ2_CYCLE;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q1_SSJ2_CYCLE.in T014_Q1_SSJ2_CYCLE.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with superset of join graph.
obey TEST014(compare);

----------------------
-- MATCH query with unmatched additional join condition on self-joined table from MV.
set pattern $$QueryName$$ T014_Q2_SSJ2_CYCLE;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q2_SSJ2_CYCLE.in T014_Q2_SSJ2_CYCLE.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

----------------------
?section T014_QSAME_SSJ2_RNGKEYS;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and item.i_item_sk in (45,97,100,122,135,136,168,199,200,215,459)
    and p1.p_item_sk > 100
    and p2.p_item_sk < 200
  order by 1,2;

?section T014_QDIFF_SSJ2_RNGKEYS;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from promotion p1 join
       item on (item.i_item_sk = p1.p_item_sk) join
       promotion p2 on (p2.p_item_sk = p1.p_item_sk)
  where i_item_sk > 44
    and (   i_item_sk = 100 
         or i_item_sk = 45 
         or i_item_sk in (459,215,168,135,122,97,45)
         or i_item_sk between 199 and 200)
    and p1.p_item_sk >= 101
    and p2.p_item_sk <= 199
  order by 1,2;

?section T014_Q1_SSJ2_RNGKEYS;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and (   i_item_sk = 100 
         or i_item_sk = 45 
         or i_item_sk in (459,215,168,135,122,97,45))
    and p1.p_item_sk between 140 and 170
    and p2.p_item_sk between 150 and 180
  order by 1,2;

?section T014_Q2_SSJ2_RNGKEYS;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and (   i_item_sk = 100 or i_item_sk = 45 
         or i_item_sk in (459,215,168,135,122,97,45))
    and p1.p_item_sk = 101
    and p2.p_item_sk between 150 and 215
  order by 1,2;

?section T014_Q3_SSJ2_RNGKEYS;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and item.i_item_sk in (45,97,100,101,122,135,136,168,199,200,215,459)
    and p1.p_item_sk > 100
    and p2.p_item_sk < 200
  order by 1,2;

?section SSJ2_RNGKEYS
--============================================================================
-- Two-way symmetric self-join with overlapping range predicates on the join keys.
-- Note that all range preds apply to the joinpred.
--============================================================================
CREATE MV T014_MV_SSJ2_RNGKEYS
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and item.i_item_sk in (45,97,100,122,135,136,168,199,200,215,459)
    and p1.p_item_sk > 100
    and p2.p_item_sk < 200;

set pattern $$MVName$$ T014_MV_SSJ2_RNGKEYS;
select * from $$MVName$$ order by i_item_sk, i_product_name;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_SSJ2_RNGKEYS';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_SSJ2_RNGKEYS;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_SSJ2_RNGKEYS.in T014_QSAME_SSJ2_RNGKEYS.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variation.
set pattern $$QueryName$$ T014_QDIFF_SSJ2_RNGKEYS;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_SSJ2_RNGKEYS.in T014_QDIFF_SSJ2_RNGKEYS.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variation.
obey TEST014(compare);

----------------------
-- MATCH query with range pred on joinpred column subsumed by that of MV.
-- Query range pred evaluates to a single value (168).
set pattern $$QueryName$$ T014_Q1_SSJ2_RNGKEYS;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q1_SSJ2_RNGKEYS.in T014_Q1_SSJ2_RNGKEYS.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with range pred on joinpred column subsumed by that of MV.
-- Query range pred evaluates to a single value (168).
obey TEST014(compare);

----------------------
-- MATCH query with range preds on joinpred column that evaluates an empty range.
-- Query should match because it will return no rows.
set pattern $$QueryName$$ T014_Q2_SSJ2_RNGKEYS;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q2_SSJ2_RNGKEYS.in T014_Q2_SSJ2_RNGKEYS.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with range preds on joinpred column that evaluates an empty range.
-- Query should match because it will return no rows.
obey TEST014(compare);

----------------------
-- MATCH query with range pred on joinpred column NOT subsumed by that of MV.
-- Query range pred adds the value 101 to the range, which is not included in the MV.
set pattern $$QueryName$$ T014_Q3_SSJ2_RNGKEYS;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q3_SSJ2_RNGKEYS.in T014_Q3_SSJ2_RNGKEYS.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

----------------------
?section T014_QSAME_SSJ2_RESKEYS;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_item_sk + p2.p_item_sk > 300
  order by 1,2;

?section T014_QDIFF_SSJ2_RESKEYS;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from promotion p1 join
       promotion p2 on (p1.p_item_sk = p2.p_item_sk) join
       item on (p2.p_item_sk = item.i_item_sk)
  where p1.p_item_sk + p2.p_item_sk > 300
  order by 1,2;

?section SSJ2_RESKEYS
--============================================================================
-- Two-way symmetric self-join with residual predicate on the join keys.
--============================================================================
CREATE MV T014_MV_SSJ2_RESKEYS
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_item_sk + p2.p_item_sk > 300;

set pattern $$MVName$$ T014_MV_SSJ2_RESKEYS;
select * from $$MVName$$ order by i_item_sk, i_product_name;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_SSJ2_RESKEYS';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_SSJ2_RESKEYS;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_SSJ2_RESKEYS.in T014_QSAME_SSJ2_RESKEYS.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variation.
set pattern $$QueryName$$ T014_QDIFF_SSJ2_RESKEYS;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_SSJ2_RESKEYS.in T014_QDIFF_SSJ2_RESKEYS.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variation.
obey TEST014(compare);

----------------------
?section T014_QSAME_SSJ2_ARES;
prepare QueryStmt from
  select i_item_sk, i_product_name, p1.p_cost, p2.p_cost
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_response_target < p2.p_response_target
  order by 1,2,3;

?section T014_QDIFF_SSJ2_ARES;
prepare QueryStmt from
  select i_item_sk, i_product_name, p1.p_cost, p2.p_cost
  from promotion p1 join
       item on (item.i_item_sk = p1.p_item_sk) join
       promotion p2 on (p1.p_item_sk = p2.p_item_sk)
    and p1.p_response_target < p2.p_response_target
  order by 1,2,3;

?section T014_Q1_SSJ2_ARES;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_response_target < p2.p_response_target
    and p1.p_cost between 5000 and 10000
    and p2.p_cost > 7500
  order by 1,2;

?section T014_Q2_SSJ2_ARES;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_response_target < p2.p_response_target
    and p1.p_cost > p2.p_cost + 1000
  order by 1,2;

?section SSJ2_ARES
--============================================================================
-- Two-way symmetric self-join with asymmetric residual predicate.
--============================================================================
CREATE MV T014_MV_SSJ2_ARES(item_sk, prod_name, p1_cost, p2_cost)
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select i_item_sk, i_product_name, p1.p_cost, p2.p_cost
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_response_target < p2.p_response_target;

set pattern $$MVName$$ T014_MV_SSJ2_ARES;
select * from $$MVName$$ order by item_sk, prod_name, p1_cost, p2_cost;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_SSJ2_ARES';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_SSJ2_ARES;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_SSJ2_ARES.in T014_QSAME_SSJ2_ARES.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variation.
set pattern $$QueryName$$ T014_QDIFF_SSJ2_ARES;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_SSJ2_ARES.in T014_QDIFF_SSJ2_ARES.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variation.
obey TEST014(compare);

----------------------
-- MATCH query with additional range pred on a column of each self-join instance.
set pattern $$QueryName$$ T014_Q1_SSJ2_ARES;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q1_SSJ2_ARES.in T014_Q1_SSJ2_ARES.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional range pred on a column of each self-join instance.
obey TEST014(compare);

----------------------
-- MATCH query with additional residual pred using a joinpred column of each self-join instance.
set pattern $$QueryName$$ T014_Q2_SSJ2_ARES;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q2_SSJ2_ARES.in T014_Q2_SSJ2_ARES.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional residual pred using a joinpred column of each self-join instance.
obey TEST014(compare);

----------------------
?section T014_QSAME_SSJ2_ARNG;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_response_target < p2.p_response_target
    and p1.p_channel_dmail = 'Y' 
    and p2.p_channel_dmail = 'N'
  order by 1,2;

?section T014_QDIFF_SSJ2_ARNG;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from promotion p1 join
       promotion p2 on (p2.p_item_sk = p1.p_item_sk) join
       item on (p2.p_item_sk = item.i_item_sk)
  where p1.p_response_target < p2.p_response_target
    and p1.p_channel_dmail >= 'Y'
    and p1.p_channel_dmail <= 'Y'
    and p2.p_channel_dmail >= 'N'
    and p2.p_channel_dmail <= 'N'
  order by 1,2;

?section T014_Q1_SSJ2_ARNG;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p2.p_response_target > p1.p_response_target
    and p1.p_channel_dmail = 'Y' 
    and p2.p_channel_dmail = 'N'
  order by 1,2;

?section T014_Q2_SSJ2_ARNG;
prepare QueryStmt from
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_response_target < p2.p_response_target
    and p1.p_channel_dmail = 'N' 
    and p2.p_channel_dmail = 'Y'
  order by 1,2;

?section SSJ2_ARNG
--============================================================================
-- Two-way symmetric self-join with asymmetric range predicates.
--============================================================================
CREATE MV T014_MV_SSJ2_ARNG
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select i_item_sk, i_product_name
  from item, promotion p1, promotion p2
  where item.i_item_sk = p1.p_item_sk 
    and item.i_item_sk = p2.p_item_sk
    and p1.p_response_target < p2.p_response_target
    and p1.p_channel_dmail = 'Y' 
    and p2.p_channel_dmail = 'N';

set pattern $$MVName$$ T014_MV_SSJ2_ARNG;
select * from $$MVName$$ order by i_item_sk, i_product_name;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_SSJ2_ARNG';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_SSJ2_ARNG;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_SSJ2_ARNG.in T014_QSAME_SSJ2_ARNG.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variation.
set pattern $$QueryName$$ T014_QDIFF_SSJ2_ARNG;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_SSJ2_ARNG.in T014_QDIFF_SSJ2_ARNG.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variation.
obey TEST014(compare);

----------------------
-- MATCH query with equivalent residual pred that fails to match due to text difference. a joinpred column of each self-join instance.
set pattern $$QueryName$$ T014_Q1_SSJ2_ARNG;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

set pattern $$QueryName$$ T014_Q1_SSJ2_ARNG;
obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q1_SSJ2_ARNG.in T014_Q1_SSJ2_ARNG.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

----------------------
-- MATCH query that fails range match because ranges swapped between 2 instances of self-join table.
set pattern $$QueryName$$ T014_Q2_SSJ2_ARNG;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

set pattern $$QueryName$$ T014_Q2_SSJ2_ARNG;
obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q2_SSJ2_ARNG.in T014_Q2_SSJ2_ARNG.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

----------------------
?section T014_QSAME_ASJ2_RNG1;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ShipDate.d_date - SoldDate.d_date < interval '3' day
  order by 1,2,3,4;

?section T014_QDIFF_ASJ2_RNG1;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from date_dim SoldDate join
       web_sales on (web_sales.ws_sold_date_sk = SoldDate.d_date_sk) join
       date_dim ShipDate on (web_sales.ws_ship_date_sk = ShipDate.d_date_sk)
  where ShipDate.d_date - SoldDate.d_date < interval '3' day
  order by 1,2,3,4;

?section T014_Q1_ASJ2_RNG1;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ShipDate.d_date - SoldDate.d_date < interval '72' hour
  order by 1,2,3,4;

?section T014_Q2_ASJ2_RNG1;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ShipDate.d_date - SoldDate.d_date < interval '3' day 
    and interval'2'day < SoldDate.d_date - ShipDate.d_date
  order by 1,2,3,4;

?section T014_Q3_ASJ2_RNG1;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ShipDate.d_date - SoldDate.d_date < interval '3' day 
    and interval'2'day < SoldDate.d_date - ShipDate.d_date
    and ws_order_number > 999
  order by 1,2,3,4;

?section ASJ2_RNG1
--============================================================================
-- Two-way asymmetric self-join with 2-node range predicate.
--============================================================================
CREATE MV T014_MV_ASJ2_RNG1(ws_item_sk, ws_order_number, sold_date, ship_date)
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ShipDate.d_date - SoldDate.d_date < interval '3' day;

set pattern $$MVName$$ T014_MV_ASJ2_RNG1;
select * from $$MVName$$ order by ws_item_sk, ws_order_number, sold_date, ship_date;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_ASJ2_RNG1';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_ASJ2_RNG1;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_ASJ2_RNG1.in T014_QSAME_ASJ2_RNG1.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variation.
set pattern $$QueryName$$ T014_QDIFF_ASJ2_RNG1;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_ASJ2_RNG1.in T014_QDIFF_ASJ2_RNG1.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variation.
obey TEST014(compare);

----------------------
-- MATCH query that fails residual match because equivalent value expressed differently.
set pattern $$QueryName$$ T014_Q1_ASJ2_RNG1;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q1_ASJ2_RNG1.in T014_Q1_ASJ2_RNG1.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

----------------------
-- MATCH query with additional residual predicate involving same columns
-- (1 from each self-join instance).
set pattern $$QueryName$$ T014_Q2_ASJ2_RNG1;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q2_ASJ2_RNG1.in T014_Q2_ASJ2_RNG1.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional residual predicate involving same columns
-- (1 from each self-join instance).
obey TEST014(compare);

----------------------
-- MATCH query with additional residual predicate involving same columns
-- (1 from each self-join instance), and additional range pred on non-self-join table.
set pattern $$QueryName$$ T014_Q3_ASJ2_RNG1;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q3_ASJ2_RNG1.in T014_Q3_ASJ2_RNG1.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional residual predicate involving same columns
-- (1 from each self-join instance), and additional range pred on non-self-join table.
obey TEST014(compare);

----------------------
?section T014_QSAME_ASJ2_RNG2;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price > 100
  order by 1,2,3,4;

?section T014_QDIFF_ASJ2_RNG2;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales join
       date_dim ShipDate on (ShipDate.d_date_sk = web_sales.ws_ship_date_sk) join
       date_dim SoldDate on (SoldDate.d_date_sk = web_sales.ws_sold_date_sk)
  where ws_sales_price >= 100.01
  order by 1,2,3,4;

?section T014_Q1_ASJ2_RNG2;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price >= 100
  order by 1,2,3,4;

?section T014_Q2_ASJ2_RNG2;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price >= 150
  order by 1,2,3,4;

?section ASJ2_RNG2
--============================================================================
-- Two-way asymmetric self-join with range predicate on non-self-join table.
--============================================================================
CREATE MV T014_MV_ASJ2_RNG2(ws_item_sk, ws_order_number, sold_date, ship_date)
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price > 100;

set pattern $$MVName$$ T014_MV_ASJ2_RNG2;
select * from $$MVName$$ order by ws_item_sk, ws_order_number, sold_date, ship_date;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_ASJ2_RNG2';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_ASJ2_RNG2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_ASJ2_RNG2.in T014_QSAME_ASJ2_RNG2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variation.
set pattern $$QueryName$$ T014_QDIFF_ASJ2_RNG2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_ASJ2_RNG2.in T014_QDIFF_ASJ2_RNG2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variation.
obey TEST014(compare);

----------------------
-- MATCH query with endpoint of range made inclusive so that range match fails.
set pattern $$QueryName$$ T014_Q1_ASJ2_RNG2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q1_ASJ2_RNG2.in T014_Q1_ASJ2_RNG2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

----------------------
-- MATCH query with NotProvided range match requiring back-join to non-self-join table.
set pattern $$QueryName$$ T014_Q2_ASJ2_RNG2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q2_ASJ2_RNG2.in T014_Q2_ASJ2_RNG2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with NotProvided range match requiring back-join to non-self-join table.
obey TEST014(compare);

----------------------
?section T014_QSAME_ASJ2_RNG3;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and SoldDate.d_dow >= 5
  order by 1,2,3,4;

?section T014_QDIFF_ASJ2_RNG3;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from date_dim ShipDate join
       web_sales on (web_sales.ws_ship_date_sk = ShipDate.d_date_sk) join
       date_dim SoldDate on (web_sales.ws_sold_date_sk = SoldDate.d_date_sk)
  where SoldDate.d_dow > 4
  order by 1,2,3,4;

?section T014_Q1_ASJ2_RNG3;
prepare QueryStmt from
  select SoldDate.d_date, ShipDate.d_date, max(ws_order_number)
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and SoldDate.d_dow >= 5
  group by 1,2
  order by 1,2;

?section T014_Q2_ASJ2_RNG3;
prepare QueryStmt from
  select SoldDate.d_date, ShipDate.d_date, avg(ws_sales_price)
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and SoldDate.d_dow >= 5
    group by 1,2
  order by 1,2;

?section ASJ2_RNG3
--============================================================================
-- Two-way asymmetric self-join with range predicate on self-join table.
--============================================================================
CREATE MV T014_MV_ASJ2_RNG3(ws_item_sk, ws_order_number, sold_date, ship_date)
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and SoldDate.d_dow >= 5;

set pattern $$MVName$$ T014_MV_ASJ2_RNG3;
select * from $$MVName$$ order by ws_item_sk, ws_order_number, sold_date, ship_date;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_ASJ2_RNG3';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_ASJ2_RNG3;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_ASJ2_RNG3.in T014_QSAME_ASJ2_RNG3.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variation.
set pattern $$QueryName$$ T014_QDIFF_ASJ2_RNG3;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_ASJ2_RNG3.in T014_QDIFF_ASJ2_RNG3.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variation.
obey TEST014(compare);

----------------------
-- MATCH query with Group By and aggregate on provided output column from
-- non-self-join table.
set pattern $$QueryName$$ T014_Q1_ASJ2_RNG3;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q1_ASJ2_RNG3.in T014_Q1_ASJ2_RNG3.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with Group By and aggregate on provided output column from
-- non-self-join table.
obey TEST014(compare);

----------------------
-- MATCH query with Group By and aggregate on column from non-self-join table
-- accessed via a back-join.
set pattern $$QueryName$$ T014_Q2_ASJ2_RNG3;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q2_ASJ2_RNG3.in T014_Q2_ASJ2_RNG3.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with Group By and aggregate on column from non-self-join table
-- accessed via a back-join.
obey TEST014(compare);

----------------------
?section T014_QSAME_ASJ2_RNG4;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price > 100
    and SoldDate.d_dow >= 5
  order by 1,2,3,4;

?section T014_QDIFF_ASJ2_RNG4;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales join
       date_dim SoldDate on (web_sales.ws_sold_date_sk = SoldDate.d_date_sk) join
       date_dim ShipDate on (web_sales.ws_ship_date_sk = ShipDate.d_date_sk)
  where ws_sales_price between 100.01 and 10000000
    and not SoldDate.d_dow < 5
  order by 1,2,3,4;

?section T014_Q1_ASJ2_RNG4;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price > 100
    and SoldDate.d_dow >= 5
    and ws_sales_price is not null
    and SoldDate.d_dow is not null
  order by 1,2,3,4;

?section T014_Q2_ASJ2_RNG4;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price > 150
    and SoldDate.d_dow >= 5
    and ws_sales_price is not null
  order by 1,2,3,4;

?section T014_Q3_ASJ2_RNG4;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price > 100
    and SoldDate.d_dow >= 5
    and ws_sales_price is null
  order by 1,2,3,4;

?section ASJ2_RNG4
--============================================================================
-- Two-way asymmetric self-join with range predicates on self-join and non-self-join tables.
--============================================================================
CREATE MV T014_MV_ASJ2_RNG4(ws_item_sk, ws_order_number, sold_date, ship_date)
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price > 100
    and SoldDate.d_dow >= 5;

set pattern $$MVName$$ T014_MV_ASJ2_RNG4;
select * from $$MVName$$ order by ws_item_sk, ws_order_number, sold_date, ship_date;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_ASJ2_RNG4';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_ASJ2_RNG4;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_ASJ2_RNG4.in T014_QSAME_ASJ2_RNG4.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variation.
set pattern $$QueryName$$ T014_QDIFF_ASJ2_RNG4;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_ASJ2_RNG4.in T014_QDIFF_ASJ2_RNG4.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variation.
obey TEST014(compare);

----------------------
-- MATCH query with additional redundant IS NOT NULL predicates on range pred columns.
set pattern $$QueryName$$ T014_Q1_ASJ2_RNG4;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

set pattern $$QueryName$$ T014_Q1_ASJ2_RNG4;
obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q1_ASJ2_RNG4.in T014_Q1_ASJ2_RNG4.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional redundant IS NOT NULL predicates on range pred columns.
obey TEST014(compare);

----------------------
-- MATCH query with additional redundant IS NOT NULL predicates on NotProvided
-- range pred column.
set pattern $$QueryName$$ T014_Q2_ASJ2_RNG4;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q2_ASJ2_RNG4.in T014_Q2_ASJ2_RNG4.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional redundant IS NOT NULL predicates on NotProvided
-- range pred column.
obey TEST014(compare);

----------------------
-- MATCH query with additional contradictory IS NULL predicate on column of
-- self-join table with range pred.
set pattern $$QueryName$$ T014_Q3_ASJ2_RNG4;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q3_ASJ2_RNG4.in T014_Q3_ASJ2_RNG4.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional contradictory IS NULL predicate on column of
-- self-join table with range pred.
obey TEST014(compare);

----------------------
?section T014_QSAME_ASJ2_RES1;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date_sk, ShipDate.d_date_sk
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price < .9 * ws_list_price
  order by 1,2,3,4;

?section T014_QDIFF_ASJ2_RES1;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date_sk, ShipDate.d_date_sk
  from date_dim ShipDate, date_dim SoldDate, web_sales
  where ShipDate.d_date_sk = web_sales.ws_ship_date_sk  
    and SoldDate.d_date_sk = web_sales.ws_sold_date_sk
    and ws_sales_price < .9 * ws_list_price
  order by 1,2,3,4;

?section T014_Q1_ASJ2_RES1;
prepare QueryStmt from
  select avg(SoldDate.d_month_seq), sum(ws_sales_price)
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price < .9 * ws_list_price
  order by 1,2;

?section T014_Q2_ASJ2_RES1;
prepare QueryStmt from
  select ws_warehouse_sk, SoldDate.d_day_name, ShipDate.d_day_name
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price < .9 * ws_list_price
  order by 1,2,3;

?section T014_Q3_ASJ2_RES1;
prepare QueryStmt from
  select ShipDate.d_date - SoldDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price < .9 * ws_list_price
  order by 1;

?section T014_Q4_ASJ2_RES1;
prepare QueryStmt from
  select max(ShipDate.d_dom)
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price < .9 * ws_list_price
  group by SoldDate.d_quarter_name
  order by 1;

?section ASJ2_RES1
--============================================================================
-- Two-way asymmetric self-join with residual predicate on non-self-join table.
--============================================================================
CREATE MV T014_MV_ASJ2_RES1(ws_item_sk, ws_order_number, sold_date_sk, ship_date_sk)
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select ws_item_sk, ws_order_number, SoldDate.d_date_sk, ShipDate.d_date_sk
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price < .9 * ws_list_price;
    
set pattern $$MVName$$ T014_MV_ASJ2_RES1;
select * from $$MVName$$ order by ws_item_sk, ws_order_number, sold_date_sk, ship_date_sk;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_ASJ2_RES1';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_ASJ2_RES1;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_ASJ2_RES1.in T014_QSAME_ASJ2_RES1.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variation.
set pattern $$QueryName$$ T014_QDIFF_ASJ2_RES1;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_ASJ2_RES1.in T014_QDIFF_ASJ2_RES1.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variation.
obey TEST014(compare);

----------------------
-- MATCH query with aggregate functions on columns from both self-join and
-- non-self-join tables, both accessed via back-join.
set pattern $$QueryName$$ T014_Q1_ASJ2_RES1;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q1_ASJ2_RES1.in T014_Q1_ASJ2_RES1.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with aggregate functions on columns from both self-join and
-- non-self-join tables, both accessed via back-join.
obey TEST014(compare);

----------------------
-- MATCH query with output columns accessed via back-join from both self-join
-- non-self-join tables.
set pattern $$QueryName$$ T014_Q2_ASJ2_RES1;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q2_ASJ2_RES1.in T014_Q2_ASJ2_RES1.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with output columns accessed via back-join from both self-join
-- non-self-join tables.
obey TEST014(compare);

----------------------
-- MATCH query with output expression using columns accessed via back-join from
-- both of the self-join tables.
set pattern $$QueryName$$ T014_Q3_ASJ2_RES1;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q3_ASJ2_RES1.in T014_Q3_ASJ2_RES1.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with output expression using columns accessed via back-join from
-- both of the self-join tables.
obey TEST014(compare);

----------------------
-- MATCH query with aggregate function on column of one self-join table instance,
-- grouping by column of another instance of same table, both columns accessed
-- via back-join.
set pattern $$QueryName$$ T014_Q4_ASJ2_RES1;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q4_ASJ2_RES1.in T014_Q4_ASJ2_RES1.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with aggregate function on column of one self-join table instance,
-- grouping by column of another instance of same table, both columns accessed
-- via back-join.
obey TEST014(compare);

----------------------
?section T014_QSAME_ASJ2_RES2;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ShipDate.d_quarter_seq > SoldDate.d_quarter_seq
  order by 1,2,3,4;

?section T014_QDIFF_ASJ2_RES2;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from date_dim ShipDate join
       web_sales on (web_sales.ws_ship_date_sk = ShipDate.d_date_sk) join
       date_dim SoldDate on (web_sales.ws_sold_date_sk = SoldDate.d_date_sk)
  where ShipDate.d_quarter_seq > SoldDate.d_quarter_seq
  order by 1,2,3,4;

?section T014_Q1_ASJ2_RES2;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ShipDate.d_quarter_seq > SoldDate.d_quarter_seq
    and ShipDate.d_date - SoldDate.d_date < interval'96'day
  order by 1,2,3,4;

?section T014_Q2_ASJ2_RES2;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk and 
    web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ShipDate.d_quarter_seq > SoldDate.d_quarter_seq
    and ShipDate.d_date_sk > 100
    and SoldDate.d_date_sk < 120
  order by 1,2,3,4;

?section T014_Q3_ASJ2_RES2;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ShipDate.d_quarter_seq > SoldDate.d_quarter_seq
    and SoldDate.d_date_sk < 130
    and ws_sold_date_sk > 100
  order by 1,2,3,4;

?section T014_Q4_ASJ2_RES2;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ShipDate.d_quarter_seq > SoldDate.d_quarter_seq
    and SoldDate.d_date_sk < 130
    and ShipDate.d_date_sk > 100
  order by 1,2,3,4;

?section T014_Q5_ASJ2_RES2;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and SoldDate.d_date_sk = ShipDate.d_date_sk
    and ShipDate.d_quarter_seq > SoldDate.d_quarter_seq
    and SoldDate.d_date_sk < 130
    and ShipDate.d_date_sk > 100
  order by 1,2,3,4;

?section ASJ2_RES2
--============================================================================
-- Two-way asymmetric self-join with residual predicate on self-join tables.
--============================================================================
CREATE MV T014_MV_ASJ2_RES2(ws_item_sk, ws_order_number, sold_date, ship_date)
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ShipDate.d_quarter_seq > SoldDate.d_quarter_seq;
    
set pattern $$MVName$$ T014_MV_ASJ2_RES2;
select * from $$MVName$$ order by ws_item_sk, ws_order_number, sold_date, ship_date;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_ASJ2_RES2';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_ASJ2_RES2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_ASJ2_RES2.in T014_QSAME_ASJ2_RES2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variation.
set pattern $$QueryName$$ T014_QDIFF_ASJ2_RES2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_ASJ2_RES2.in T014_QDIFF_ASJ2_RES2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variation.
obey TEST014(compare);

----------------------
-- MATCH query with additional residual predicate on columns of self-join instances.
set pattern $$QueryName$$ T014_Q1_ASJ2_RES2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q1_ASJ2_RES2.in T014_Q1_ASJ2_RES2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional residual predicate on columns of self-join instances.
obey TEST014(compare);

----------------------
-- MATCH query with additional range predicates on key columns of self-join instances.
set pattern $$QueryName$$ T014_Q2_ASJ2_RES2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q2_ASJ2_RES2.in T014_Q2_ASJ2_RES2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional range predicates on key columns of self-join instances.
obey TEST014(compare);

----------------------
-- MATCH query with additional unsatisfiable pair of range predicates on two
-- joinpred columns (1 from self-join table, other from non-self-join table).
set pattern $$QueryName$$ T014_Q3_ASJ2_RES2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q3_ASJ2_RES2.in T014_Q3_ASJ2_RES2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional unsatisfiable pair of range predicates on two
-- joinpred columns (1 from self-join table, other from non-self-join table).
obey TEST014(compare);

----------------------
-- MATCH query with additional unsatisfiable pair of range predicates on two
-- joinpred columns (both from self-join table instances).
set pattern $$QueryName$$ T014_Q4_ASJ2_RES2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q4_ASJ2_RES2.in T014_Q4_ASJ2_RES2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional unsatisfiable pair of range predicates on two
-- joinpred columns (both from self-join table instances).
obey TEST014(compare);

----------------------
-- MATCH query with additional unsatisfiable pair of range predicates on two
-- joinpred columns (both from self-join table instances). Same as
-- T014_Q4_ASJ2_RES2, but with join conditions arranged in a linear rather than
-- radial (star-shaped) pattern.
set pattern $$QueryName$$ T014_Q5_ASJ2_RES2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q5_ASJ2_RES2.in T014_Q5_ASJ2_RES2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional unsatisfiable pair of range predicates on two
-- joinpred columns (both from self-join table instances). Same as
-- T014_Q4_ASJ2_RES2, but with join conditions arranged in a linear rather than
-- radial (star-shaped) pattern.
obey TEST014(compare);

----------------------
?section T014_QSAME_ASJ2_RES3;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price < .9 * ws_list_price
    and ShipDate.d_quarter_seq > SoldDate.d_quarter_seq
  order by 1,2,3,4;

?section T014_QDIFF_ASJ2_RES3;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from date_dim ShipDate, date_dim SoldDate, web_sales
  where web_sales.ws_ship_date_sk = ShipDate.d_date_sk 
    and web_sales.ws_sold_date_sk = SoldDate.d_date_sk
    and ShipDate.d_quarter_seq > SoldDate.d_quarter_seq
    and ws_sales_price < .9 * ws_list_price
  order by 1,2,3,4;

?section T014_Q1_ASJ2_RES3;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price < .9 * ws_list_price
    and ShipDate.d_quarter_seq > SoldDate.d_quarter_seq
    and ws_order_number = cast(ShipDate.d_date - SoldDate.d_date as int)
  order by 1,2,3,4;

?section ASJ2_RES3
--============================================================================
-- Two-way asymmetric self-join with residual predicates on all tables.
--============================================================================
CREATE MV T014_MV_ASJ2_RES3(ws_item_sk, ws_order_number, sold_date, ship_date)
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_sales_price < .9 * ws_list_price
    and ShipDate.d_quarter_seq > SoldDate.d_quarter_seq;
    
set pattern $$MVName$$ T014_MV_ASJ2_RES3;
select * from $$MVName$$ order by ws_item_sk, ws_order_number, sold_date, ship_date;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_ASJ2_RES3';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_ASJ2_RES3;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_ASJ2_RES3.in T014_QSAME_ASJ2_RES3.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variation.
set pattern $$QueryName$$ T014_QDIFF_ASJ2_RES3;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_ASJ2_RES3.in T014_QDIFF_ASJ2_RES3.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variation.
obey TEST014(compare);

----------------------
-- MATCH query with additional residual pred spanning all three tables.
set pattern $$QueryName$$ T014_Q1_ASJ2_RES3;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q1_ASJ2_RES3.in T014_Q1_ASJ2_RES3.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with additional residual pred spanning all three tables.
obey TEST014(compare);

----------------------
?section T014_QSAME_ASJ2_RNG_RES;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_quantity between 2 and 99
    and ws_sales_price < .9 * ws_list_price
    and ShipDate.d_quarter_seq > SoldDate.d_quarter_seq
    and SoldDate.d_weekend = 'N'
    and ShipDate.d_current_year = 'Y'
  order by 1,2,3,4;

?section T014_QDIFF_ASJ2_RNG_RES;
prepare QueryStmt from
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales join
       date_dim ShipDate on (ShipDate.d_date_sk = web_sales.ws_ship_date_sk) join
       date_dim SoldDate on (SoldDate.d_date_sk = web_sales.ws_sold_date_sk)
  where (ws_quantity between 2 and 90 or ws_quantity between 90.01 and 99)
    and ShipDate.d_quarter_seq > SoldDate.d_quarter_seq
    and SoldDate.d_weekend = 'N' and SoldDate.d_weekend <> 'Y'
    and ws_sales_price < .9 * ws_list_price
    and ShipDate.d_current_year = 'Y'
  order by 1,2,3,4;

?section ASJ2_RNG_RES
--============================================================================
-- Two-way asymmetric self-join with several range and residual predicates involving all tables.
--============================================================================
CREATE MV T014_MV_ASJ2_RNG_RES(ws_item_sk, ws_order_number, sold_date, ship_date)
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select ws_item_sk, ws_order_number, SoldDate.d_date, ShipDate.d_date
  from web_sales, date_dim SoldDate, date_dim ShipDate
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and ws_quantity between 2 and 99
    and ws_sales_price < .9 * ws_list_price
    and ShipDate.d_quarter_seq > SoldDate.d_quarter_seq
    and SoldDate.d_weekend = 'N'
    and ShipDate.d_current_year = 'Y';
    
set pattern $$MVName$$ T014_MV_ASJ2_RNG_RES;
select * from $$MVName$$ order by ws_item_sk, ws_order_number, sold_date, ship_date;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_ASJ2_RNG_RES';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_ASJ2_RNG_RES;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_ASJ2_RNG_RES.in T014_QSAME_ASJ2_RNG_RES.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variation.
set pattern $$QueryName$$ T014_QDIFF_ASJ2_RNG_RES;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_ASJ2_RNG_RES.in T014_QDIFF_ASJ2_RNG_RES.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variation.
obey TEST014(compare);

----------------------
?section T014_QSAME_ASJ2_2_2;
prepare QueryStmt from
  select BillAddr.ca_state, cd_marital_status, cd_dep_count,
         sum(ws_net_paid), sum(ws_net_profit)
  from web_sales,
       date_dim SoldDate, 
       date_dim ShipDate,
       customer_demographics, 
       household_demographics BillHouseDemo, 
       household_demographics ShipHouseDemo, 
       income_band,
       customer_address BillAddr, 
       customer_address ShipAddr
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk 
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and web_sales.ws_bill_cdemo_sk = customer_demographics.cd_demo_sk
    and web_sales.ws_bill_addr_sk = BillAddr.ca_address_sk
    and web_sales.ws_ship_addr_sk = ShipAddr.ca_address_sk
    and web_sales.ws_bill_hdemo_sk = BillHouseDemo.hd_demo_sk
    and web_sales.ws_ship_hdemo_sk = ShipHouseDemo.hd_demo_sk
    and BillHouseDemo.hd_income_band_sk = income_band.ib_income_band_sk
    and BillHouseDemo.hd_dep_count = ShipHouseDemo.hd_dep_count
    and BillAddr.ca_city = ShipAddr.ca_city
    and BillAddr.ca_state = ShipAddr.ca_state
    and ShipDate.d_date <= SoldDate.d_date + interval'2'day
    and 45000 between ib_lower_bound and ib_upper_bound
  group by BillAddr.ca_state, cd_marital_status, cd_dep_count
  order by 1,2,3,4,5;

?section T014_QDIFF_ASJ2_2_2;
prepare QueryStmt from
  select BillAddr.ca_state, cd_marital_status, cd_dep_count,
         sum(ws_net_paid), sum(ws_net_profit)
  from customer_address BillAddr join
       customer_address ShipAddr on (BillAddr.ca_city = ShipAddr.ca_city and
                                     BillAddr.ca_state = ShipAddr.ca_state) join
       web_sales on (web_sales.ws_bill_addr_sk = BillAddr.ca_address_sk and
                     web_sales.ws_ship_addr_sk = ShipAddr.ca_address_sk) join
       household_demographics BillHouseDemo on (web_sales.ws_bill_hdemo_sk = BillHouseDemo.hd_demo_sk) join
       income_band on (BillHouseDemo.hd_income_band_sk = income_band.ib_income_band_sk) join
       customer_demographics on (web_sales.ws_bill_cdemo_sk = customer_demographics.cd_demo_sk) join
       household_demographics ShipHouseDemo on (web_sales.ws_ship_hdemo_sk = ShipHouseDemo.hd_demo_sk and
                                                BillHouseDemo.hd_dep_count = ShipHouseDemo.hd_dep_count) join
       date_dim ShipDate on (web_sales.ws_ship_date_sk = ShipDate.d_date_sk) join
       date_dim SoldDate on (web_sales.ws_sold_date_sk = SoldDate.d_date_sk)
    and ShipDate.d_date <= SoldDate.d_date + interval'2'day
    and 45000 between ib_lower_bound and ib_upper_bound
  group by BillAddr.ca_state, cd_marital_status, cd_dep_count
  order by 1,2,3,4,5;

?section T014_Q1_ASJ2_2_2;
prepare QueryStmt from
  select BillAddr.ca_state, sum(ws_net_paid), sum(ws_net_profit)
  from web_sales,
       date_dim SoldDate, 
       date_dim ShipDate,
       customer_demographics, 
       household_demographics BillHouseDemo, 
       household_demographics ShipHouseDemo, 
       income_band,
       customer_address BillAddr, 
       customer_address ShipAddr
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and web_sales.ws_bill_cdemo_sk = customer_demographics.cd_demo_sk
    and web_sales.ws_bill_addr_sk = BillAddr.ca_address_sk
    and web_sales.ws_ship_addr_sk = ShipAddr.ca_address_sk
    and web_sales.ws_bill_hdemo_sk = BillHouseDemo.hd_demo_sk
    and web_sales.ws_ship_hdemo_sk = ShipHouseDemo.hd_demo_sk
    and BillHouseDemo.hd_income_band_sk = income_band.ib_income_band_sk
    and BillHouseDemo.hd_dep_count = ShipHouseDemo.hd_dep_count
    and BillAddr.ca_city = ShipAddr.ca_city
    and BillAddr.ca_state = ShipAddr.ca_state
    and ShipDate.d_date <= SoldDate.d_date + interval'2'day
    and 45000 between ib_lower_bound and ib_upper_bound
  group by BillAddr.ca_state
  order by 1,2,3;

?section T014_Q2_ASJ2_2_2;
prepare QueryStmt from
  select BillAddr.ca_state, cd_marital_status,
         sum(ws_net_paid), sum(ws_net_profit)
  from web_sales,
       date_dim SoldDate, 
       date_dim ShipDate,
       customer_demographics, 
       household_demographics BillHouseDemo, 
       household_demographics ShipHouseDemo, 
       income_band,
       customer_address BillAddr, 
       customer_address ShipAddr
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and web_sales.ws_bill_cdemo_sk = customer_demographics.cd_demo_sk
    and web_sales.ws_bill_addr_sk = BillAddr.ca_address_sk
    and web_sales.ws_ship_addr_sk = ShipAddr.ca_address_sk
    and web_sales.ws_bill_hdemo_sk = BillHouseDemo.hd_demo_sk
    and web_sales.ws_ship_hdemo_sk = ShipHouseDemo.hd_demo_sk
    and BillHouseDemo.hd_income_band_sk = income_band.ib_income_band_sk
    and BillHouseDemo.hd_dep_count = ShipHouseDemo.hd_dep_count
    and BillAddr.ca_city = ShipAddr.ca_city
    and BillAddr.ca_state = ShipAddr.ca_state
    and ShipDate.d_date <= SoldDate.d_date + interval'2'day
    and 45000 between ib_lower_bound and ib_upper_bound
  group by BillAddr.ca_state, cd_marital_status
  order by 1,2,3,4;

?section ASJ2_2_2
--============================================================================
-- Three two-way asymmetric self-joins within a complex multi-way join.
--============================================================================
CREATE MV T014_MV_ASJ2_2_2(ca_state, cd_marital_status, cd_dep_count,
                           sum_ws_net_paid, sum_ws_net_profit)
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select BillAddr.ca_state, cd_marital_status, cd_dep_count,
         sum(ws_net_paid), sum(ws_net_profit)
  from web_sales,
       date_dim SoldDate, 
       date_dim ShipDate,
       customer_demographics, 
       household_demographics BillHouseDemo, 
       household_demographics ShipHouseDemo, 
       income_band,
       customer_address BillAddr, 
       customer_address ShipAddr
  where web_sales.ws_sold_date_sk = SoldDate.d_date_sk
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and web_sales.ws_bill_cdemo_sk = customer_demographics.cd_demo_sk
    and web_sales.ws_bill_addr_sk = BillAddr.ca_address_sk
    and web_sales.ws_ship_addr_sk = ShipAddr.ca_address_sk
    and web_sales.ws_bill_hdemo_sk = BillHouseDemo.hd_demo_sk
    and web_sales.ws_ship_hdemo_sk = ShipHouseDemo.hd_demo_sk
    and BillHouseDemo.hd_income_band_sk = income_band.ib_income_band_sk
    and BillHouseDemo.hd_dep_count = ShipHouseDemo.hd_dep_count
    and BillAddr.ca_city = ShipAddr.ca_city
    and BillAddr.ca_state = ShipAddr.ca_state
    and ShipDate.d_date <= SoldDate.d_date + interval'2'day
    and 45000 between ib_lower_bound and ib_upper_bound
  group by BillAddr.ca_state, cd_marital_status, cd_dep_count;
  
set pattern $$MVName$$ T014_MV_ASJ2_2_2;
select * from $$MVName$$ order by ca_state, cd_marital_status, cd_dep_count;
obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_ASJ2_2_2';

----------------------
-- MATCH query identical to MV.
set pattern $$QueryName$$ T014_QSAME_ASJ2_2_2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_ASJ2_2_2.in T014_QSAME_ASJ2_2_2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variation.
set pattern $$QueryName$$ T014_QDIFF_ASJ2_2_2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_ASJ2_2_2.in T014_QDIFF_ASJ2_2_2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variation.
obey TEST014(compare);

----------------------
-- MATCH query with only the first of MV's grouping columns.
set pattern $$QueryName$$ T014_Q1_ASJ2_2_2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q1_ASJ2_2_2.in T014_Q1_ASJ2_2_2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with only the first of MV's grouping columns.
obey TEST014(compare);

----------------------
-- MATCH query with all but last of MV's grouping columns.
set pattern $$QueryName$$ T014_Q2_ASJ2_2_2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_Q2_ASJ2_2_2.in T014_Q2_ASJ2_2_2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query with all but last of MV's grouping columns.
obey TEST014(compare);

---------------------
?section T014_QSAME_ASJ8_2;
prepare QueryStmt from
  select i_item_sk, i_product_name, wp_web_page_id, p_promo_name, BillCust.c_last_name
  from web_sales, item, promotion, web_page,
       customer BillCust, customer ShipCust,
       date_dim SoldDate, date_dim ShipDate,
       date_dim PromoStartDate, date_dim PromoEndDate,
       date_dim FirstCustSaleDate, date_dim FirstCustShipDate,
       date_dim PageCreateDate, date_dim PageAccessDate
  where web_sales.ws_item_sk = item.i_item_sk
    and web_sales.ws_promo_sk = promotion.p_promo_sk
    and web_sales.ws_web_page_sk = web_page.wp_web_page_sk
    and web_sales.ws_bill_customer_sk = BillCust.c_customer_sk
    and web_sales.ws_ship_customer_sk = ShipCust.c_customer_sk
    and web_sales.ws_sold_date_sk = SoldDate.d_date_sk
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and promotion.p_start_date_sk = PromoStartDate.d_date_sk
    and promotion.p_end_date_sk = PromoEndDate.d_date_sk
    and BillCust.c_first_sales_date_sk = FirstCustSaleDate.d_date_sk
    and ShipCust.c_first_shipto_date_sk = FirstCustShipDate.d_date_sk
    and web_page.wp_creation_date_sk = PageCreateDate.d_date_sk
    and web_page.wp_access_date_sk = PageAccessDate.d_date_sk
    and i_manufact in ('IBM', 'HP', 'Dell')
    and ws_sales_price >= 1000
    and BillCust.c_preferred_cust_flag = 'Y'
    and BillCust.c_email_address like '%@gmail.com'
    and PromoEndDate.d_date > current_date
    and FirstCustSaleDate.d_year < 2010
    and PageCreateDate.d_year = 2011
  order by 1,2,3,4,5;

?section T014_QDIFF_ASJ8_2;
prepare QueryStmt from
  select i_item_sk, i_product_name, wp_web_page_id, p_promo_name, BillCust.c_last_name
  from date_dim SoldDate,
       date_dim PromoEndDate,
       date_dim PageAccessDate,
       web_sales,
       date_dim FirstCustSaleDate,
       promotion,
       customer ShipCust,
       web_page,
       item, 
       date_dim PageCreateDate, 
       date_dim PromoStartDate, 
       customer BillCust, 
       date_dim ShipDate,
       date_dim FirstCustShipDate
  where BillCust.c_first_sales_date_sk = FirstCustSaleDate.d_date_sk
    and web_page.wp_creation_date_sk = PageCreateDate.d_date_sk
    and web_sales.ws_promo_sk = promotion.p_promo_sk
    and web_sales.ws_item_sk = item.i_item_sk
    and web_sales.ws_bill_customer_sk = BillCust.c_customer_sk
    and web_page.wp_access_date_sk = PageAccessDate.d_date_sk
    and web_sales.ws_ship_customer_sk = ShipCust.c_customer_sk
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and web_sales.ws_web_page_sk = web_page.wp_web_page_sk
    and promotion.p_start_date_sk = PromoStartDate.d_date_sk
    and promotion.p_end_date_sk = PromoEndDate.d_date_sk
    and web_sales.ws_sold_date_sk = SoldDate.d_date_sk
    and ShipCust.c_first_shipto_date_sk = FirstCustShipDate.d_date_sk
    and (i_manufact = 'Dell' or i_manufact = 'IBM' or i_manufact = 'HP')
    and ws_sales_price > 999.99
    and BillCust.c_preferred_cust_flag = 'Y'
    and BillCust.c_email_address like '%@gmail.com'
    and PromoEndDate.d_date > current_date
    and FirstCustSaleDate.d_year <= 2009
    and PageCreateDate.d_year > 2010 and PageCreateDate.d_year < 2012
  order by 1,2,3,4,5;

?section ASJ8_2
--============================================================================
-- 8-way and 2-way self-joins with range and residual preds.
--============================================================================
CREATE MV T014_MV_ASJ8_2
  RECOMPUTE INITIALIZE ON CREATE ENABLE QUERY REWRITE AS
  select i_item_sk, i_product_name, wp_web_page_id, p_promo_name, BillCust.c_last_name
  from web_sales, item, promotion, web_page,
       customer BillCust, customer ShipCust,
       date_dim SoldDate, date_dim ShipDate,
       date_dim PromoStartDate, date_dim PromoEndDate,
       date_dim FirstCustSaleDate, date_dim FirstCustShipDate,
       date_dim PageCreateDate, date_dim PageAccessDate
  where web_sales.ws_item_sk = item.i_item_sk
    and web_sales.ws_promo_sk = promotion.p_promo_sk
    and web_sales.ws_web_page_sk = web_page.wp_web_page_sk
    and web_sales.ws_bill_customer_sk = BillCust.c_customer_sk
    and web_sales.ws_ship_customer_sk = ShipCust.c_customer_sk
    and web_sales.ws_sold_date_sk = SoldDate.d_date_sk
    and web_sales.ws_ship_date_sk = ShipDate.d_date_sk
    and promotion.p_start_date_sk = PromoStartDate.d_date_sk
    and promotion.p_end_date_sk = PromoEndDate.d_date_sk
    and BillCust.c_first_sales_date_sk = FirstCustSaleDate.d_date_sk
    and ShipCust.c_first_shipto_date_sk = FirstCustShipDate.d_date_sk
    and web_page.wp_creation_date_sk = PageCreateDate.d_date_sk
    and web_page.wp_access_date_sk = PageAccessDate.d_date_sk
    and i_manufact in ('IBM', 'HP', 'Dell')
    and ws_sales_price >= 1000
    and BillCust.c_preferred_cust_flag = 'Y'
    and BillCust.c_email_address like '%@gmail.com'
    and PromoEndDate.d_date > current_date
    and FirstCustSaleDate.d_year < 2010
    and PageCreateDate.d_year = 2011;
    
-- Skip dumping the MV descriptor because its too damn long (over 38K)
-- for 6 rows of the TEXT table (and 7 rows is too long for the 
-- CropDescriptor script).
--set pattern $$MVName$$ T014_MV_ASJ8_2;
--obey TEST014(dump_MV);

control query default MVQR_REWRITE_CANDIDATES 'CAT.S014_TPCDS.T014_MV_ASJ8_2';

----------------------
-- MATCH query identical to MV. Should work.
set pattern $$QueryName$$ T014_QSAME_ASJ8_2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

set pattern $$QueryName$$ T014_QSAME_ASJ8_2;
obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
--sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QSAME_ASJ8_2.in T014_QSAME_ASJ8_2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV.
obey TEST014(compare);

----------------------
-- MATCH query identical to MV except for syntactic and order variation.
-- This self-join is too big (over 80,000 permutations for the top graph)
-- so full self-join analysis is skipped, and this query is not matched.
set pattern $$QueryName$$ T014_QDIFF_ASJ8_2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST014($$QueryName$$);

obey TEST014(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> $$QueryName$$.in;
--sh echo PUBLISH $$MVName$$.xml  >> $$QueryName$$.in;
sh echo MATCH $$QueryName$$.xml >> $$QueryName$$.in;

-- Can't use $$QueryName$$ here -- patterns not expanded within quotes.
sh sh -c "$QMS T014_QDIFF_ASJ8_2.in T014_QDIFF_ASJ8_2.out";

log;
sh cat $$QueryName$$.out >> LOG014;
log LOG014;

-- VERIFY query identical to MV except for syntactic and order variation.
-- This query does not get rewritten because its too complex.
-- Skip comparing data.

obey TEST014($$QueryName$$);

execute checkPlan;


?section dump_MV
--===========================================
--== Create the MV descriptor XML
--===========================================

log $$MVName$$.tmp clear;
set param ?mvName $$MVName$$;
set param ?mvSchemaName $$MVSchemaName$$;
execute getMVDescFromTEXT;
log;

#ifMX
sh $$rundir$$/mvqr/FILTERCROP $$MVName$$.TMP > $$MVName$$.tmp.f;
sh sh ./CropDescriptor.ksh -i $$MVName$$.tmp.f -o $$MVName$$.xml;
#ifMX
#ifNT
sh sh ./CropDescriptor -i $$MVName$$.tmp -o $$MVName$$.xml;
#ifNT
sh sleep 1;
sh cat $$MVName$$.xml >> LOG014;
log LOG014;

?section dump_Query
--===========================================
--== Create the query descriptor XML
--===========================================

log $$QueryName$$.tmp clear;
-- Once for the XML file,
execute QueryStmt;
log LOG014;
sh sh ./CropDescriptor.ksh -q -i $$QueryName$$.TMP -o $$QueryName$$.xml;
-- and once for the test log file
execute QueryStmt;

?section ignore
--===========================================
--===========================================
--===========================================
?section compare

obey TEST014($$QueryName$$);

execute checkPlan;

execute QueryStmt;

log T014_C1.txt clear;
execute QueryStmt;
log;
log LOG014;

-- Verify the data is correct
control query default MVQR_REWRITE_LEVEL '0';

obey TEST014($$QueryName$$);

execute checkPlan;

execute QueryStmt;

control query default MVQR_REWRITE_LEVEL '3';

log T014_C2.txt clear;
execute QueryStmt;
log;
log LOG014;

-- Diff query results
log;
sh diff T014_C1.txt T014_C2.txt >> LOG014;
log LOG014;
-- Diff is done.
