>>obey TEST007(set_up);
>>----------------------------------------------------
>>-------------------- set up section ----------------
>>----------------------------------------------------
>>
>>control query default query_cache '0';

--- SQL operation complete.
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>control query default MVQR_REWRITE_ENABLED_OPTION 'ON';

--- SQL operation complete.
>>control query default MVQR_PUBLISH_TO 'PRIVATE';

--- SQL operation complete.
>>control query default MULTI_JOIN_THRESHOLD '2';

--- SQL operation complete.
>>cqd HIDE_INDEXES 'ALL';

--- SQL operation complete.
>>--control query default HIST_USE_SAMPLE_FOR_CARDINALITY_ESTIMATION 'OFF';
>>--control query default HIST_ON_DEMAND_STATS_SIZE '0';
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.MVQRTEST007.SUMBYDAY, :CAT.MVQRTEST007.SUMBYMONTH, :CAT.MVQRTEST007.SUMBYYEAR';

--- SQL operation complete.
>>
>>prepare checkPlan from
+>select operator, tname
+>from table(explain(NULL, 'QUERYSTMT'))
+>where operator like '%_SCAN%'
+>order by tname;

--- SQL command prepared.
>>
>>obey GetMvDesc;
>>--set pattern $$VERS_CURR_SCHEMA_VERSION$$ 2400;
>>set param ?mvDescSubId  -2;
>>set param ?mvName 'SUMBY_DAY';
>>set param ?mvSchemaName 'SUMBY';
>>
>>-- query the TEXT SMD table for the descriptor of an MV
>>-- Note: Cast to UTF8, but we don't want to exceed the 20000 byte line
>>--       length limit of the NT version of awk (file CropDescriptor.ksh).
>>prepare getMVDescFromTEXT from
+>  select cast(text0 || coalesce(text1, '') || coalesce(text2, '') || coalesce(text3, '')
+>                    || coalesce(text4, '') || coalesce(text5, '') || coalesce(text6, '')
+>                    || coalesce(text7, '') || coalesce(text8, '') as char(15000 bytes) character set utf8)  as mv_descriptor_text
+>  from
+>		 (SELECT o.object_uid as uid0, text as text0
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 0 ) as row0
+>	left outer join
+>		 (SELECT o.object_uid as uid1, text as text1
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 1 ) as row1
+>		 on uid0 = uid1
+>	left outer join
+>		 (SELECT o.object_uid as uid2, text as text2
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 2 ) as row2
+>		 on uid0 = uid2
+>	left outer join
+>		 (SELECT o.object_uid as uid3, text as text3
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 3 ) as row3
+>		 on uid0 = uid3
+>	left outer join
+>		 (SELECT o.object_uid as uid4, text as text4
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 4 ) as row4
+>		 on uid0 = uid4
+>	left outer join
+>		 (SELECT o.object_uid as uid5, text as text5
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 5 ) as row5
+>		 on uid0 = uid5
+>	left outer join
+>		 (SELECT o.object_uid as uid6, text as text6
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 6 ) as row6
+>		 on uid0 = uid6
+>	left outer join
+>		 (SELECT o.object_uid as uid7, text as text7
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 7 ) as row7
+>		 on uid0 = uid7
+>	left outer join
+>		 (SELECT o.object_uid as uid8, text as text8
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 8 ) as row8
+>		 on uid0 = uid8;

--- SQL command prepared.
>>
>>
>>create schema mvqrtest007;

--- SQL operation complete.
>>set schema mvqrtest007;

--- SQL operation complete.
>>set pattern $$MVSchemaName$$ MVQRTEST007;
>>
>>obey TEST007(create_tables);
>>
>>create table fact (
+>  fday   int,
+>  fmonth int,
+>  fyear  int,
+>  fprice numeric (8,2),
+>  dimkey int) no partition;

--- SQL operation complete.
>>
>>
>>create table dim1 (dkey int not null not droppable primary key,
+>                   dimdata int, 
+>                   fk1 int) no partition;

--- SQL operation complete.
>>create table dim2 (dkey int not null not droppable primary key,
+>                   dimdata int, 
+>                   fk1 int) no partition;

--- SQL operation complete.
>>create table dim3 (dkey int not null not droppable primary key,
+>                   dimdata int, 
+>                   fk1 int) no partition;

--- SQL operation complete.
>>
>>--insert into fact(fday,fmonth,fyear,fprice,dimkey) values
>>--  ( 6, 2,2011,79,16),
>>--  (26,11,2011,45,12),
>>--  (11, 4,2012,54,11),
>>--  (26,11,2012,25, 8),
>>--  (11, 1,2011,93, 6),
>>--  (19,10,2011,62,15),
>>--  (31, 9,2010,48, 6),
>>--  (24, 1,2010,61,12),
>>--  (11, 4,2010,84,17),
>>--  (19,10,2011,27,10),
>>--  (12,12,2011,80, 5),
>>--  (17, 2,2012,50,17),
>>--  (17, 7,2011,90, 8),
>>--  (18, 3,2011,37, 2),
>>--  ( 6, 8,2012,66, 3),
>>--  (19, 9,2012,96,15),
>>--  (30,12,2011,70,12),
>>--  ( 3,10,2010,96, 7),
>>--  ( 3, 5,2012,41, 8),
>>--  (14, 4,2011,25,12),
>>--  ( 4, 4,2012,27,16),
>>--  (24, 8,2010,14, 2),
>>--  (20, 2,2012,89, 2),
>>--  (17,11,2010,81,13),
>>--  (17, 5,2010,67, 2),
>>--  (24, 5,2011,74,18),
>>--  ( 1,12,2010,96,18),
>>--  (18, 5,2011,77, 4),
>>--  ( 1, 9,2010,69, 1),
>>--  ( 9, 4,2012,26,12),
>>--  ( 6, 2,2012,58,10),
>>--  ( 2,10,2012,67, 5),
>>--  (26,10,2011,68,18),
>>--  (20, 4,2012,49, 4),
>>--  (20,11,2012,56, 4),
>>--  (19, 4,2010,92,13),
>>--  (24, 2,2010,51,11),
>>--  (14,10,2010,10, 4),
>>--  (12, 2,2011,28,18),
>>--  (22, 6,2010,79,17),
>>--  (20, 9,2011,30,16),
>>--  (17, 8,2010,33,15),
>>--  ( 9, 9,2012,97, 2),
>>--  (29, 4,2010,55,11),
>>--  ( 9, 5,2012,22,12),
>>--  (28, 2,2010,48,10),
>>--  (19, 5,2010,27, 2),
>>--  (25, 4,2012,43,10),
>>--  (17, 5,2012,13,15),
>>--  ( 2, 3,2010,83,18);
>>--
>>--insert into dim1(dkey, dimdata, fk1) values
>>--  (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5), (6, 6, 6), (7, 7, 7), (8, 8, 8), (9, 9, 9), (10, 10, 10),
>>--  (11, 11, 11), (12, 12, 12), (13, 13, 13), (14, 14, 14), (15, 15, 15), (16, 16, 16), (17, 17, 17), (18, 18, 18), (19, 19, 19);
>>--
>>--insert into dim2(dkey, dimdata, fk1) 
>>--  select dkey, dimdata, fk1 from dim1;
>>--
>>--insert into dim3(dkey, dimdata, fk1)
>>--  select dkey, dimdata, fk1 from dim1;
>>
>>alter table dim2 add constraint dim2_fk foreign key (fk1)
+>                  references dim3(dkey);

--- SQL operation complete.
>>
>>obey TEST007(create_mvs);
>>
>>create mv sumbyDay
+>        refresh on request
+>        initialized on create
+>        as  select sum(fprice) day_price, fday, fmonth, fyear
+>            from fact
+>            group by fday, fmonth, fyear;

*** WARNING[12112] A secondary index CAT.MVQRTEST007.SUMBYDAY_736878472_2113 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>
>>set pattern $$MVName$$ SUMBYDAY;
>>obey TEST007(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.MVQRTEST007.SUMBYDAY
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874551627433' numCols='6'>
          CAT.MVQRTEST007.FACT
        </Table>
      </JBBCList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O83' name='DAY_PRICE'>
        <Expr id='X83'>
          <Function id='S83' op='sum' aggregateFunc='3'>
            <Column id='C75' tableId='T1' colIndex='4'>
              CAT.MVQRTEST007.FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O85' name='SYS_COUNTSTAR1'>
        <Expr id='X85'>
          <Function id='S85' op='count' aggregateFunc='1'>
            <NumericVal id='S84' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O86' name='SYS_COUNT2'>
        <Expr id='X86'>
          <Function id='S86' op='count_nonull' aggregateFunc='2'>
            <Column ref='C75'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O91' name='FDAY'>
        <Column ref='C72'>
        </Column>
      </Output>
      <Output id='O94' name='FMONTH'>
        <Column ref='C73'>
        </Column>
      </Output>
      <Output id='O97' name='FYEAR'>
        <Column ref='C74'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G2'>
      <Primary>
        <Column id='C72' tableId='T1' colIndex='1'>
          CAT.MVQRTEST007.FACT.FDAY
        </Column>
        <Column id='C73' tableId='T1' colIndex='2'>
          CAT.MVQRTEST007.FACT.FMONTH
        </Column>
        <Column id='C74' tableId='T1' colIndex='3'>
          CAT.MVQRTEST007.FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumbyMonth
+>        refresh on request
+>        initialized on create
+>        as  select sum(day_price) month_price, fmonth, fyear
+>            from sumbyDay
+>            group by fmonth, fyear;

*** WARNING[12112] A secondary index CAT.MVQRTEST007.SUMBYMONTH_466256592_2113 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBYMONTH;
>>obey TEST007(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.MVQRTEST007.SUMBYMONTH
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874553877231' numCols='6'>
          CAT.MVQRTEST007.FACT
        </Table>
      </JBBCList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O95' name='MONTH_PRICE'>
        <Expr id='X95'>
          <Function id='S95' op='sum' aggregateFunc='3'>
            <Column id='C82' tableId='T1' colIndex='4'>
              CAT.MVQRTEST007.FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O105' name='FMONTH'>
        <Column ref='C80'>
        </Column>
      </Output>
      <Output id='O108' name='FYEAR'>
        <Column ref='C81'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G2'>
      <Primary>
        <Column id='C80' tableId='T1' colIndex='2'>
          CAT.MVQRTEST007.FACT.FMONTH
        </Column>
        <Column id='C81' tableId='T1' colIndex='3'>
          CAT.MVQRTEST007.FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumbyYear
+>        refresh on request
+>        initialized on create
+>        as  select sum(month_price) year_price, fyear
+>            from sumbyMonth
+>            group by fyear;

*** WARNING[12112] A secondary index CAT.MVQRTEST007.SUMBYYEAR_565982413_2113 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBYYEAR;
>>obey TEST007(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.MVQRTEST007.SUMBYYEAR
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874553877231' numCols='6'>
          CAT.MVQRTEST007.FACT
        </Table>
      </JBBCList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O89' name='YEAR_PRICE'>
        <Expr id='X89'>
          <Function id='S89' op='sum' aggregateFunc='3'>
            <Column id='C73' tableId='T1' colIndex='4'>
              CAT.MVQRTEST007.FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O102' name='FYEAR'>
        <Column ref='C72'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G2'>
      <Primary>
        <Column id='C72' tableId='T1' colIndex='3'>
          CAT.MVQRTEST007.FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv minbyDay
+>        refresh on request
+>        initialized on create
+>        as  select min(fprice) min_day_price, fday, fmonth, fyear
+>            from fact
+>            group by fday, fmonth, fyear;

*** WARNING[12112] A secondary index CAT.MVQRTEST007.MINBYDAY_128689913_2113 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>
>>set pattern $$MVName$$ MINBYDAY;
>>obey TEST007(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.MVQRTEST007.MINBYDAY
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874553877231' numCols='6'>
          CAT.MVQRTEST007.FACT
        </Table>
      </JBBCList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O83' name='MIN_DAY_PRICE'>
        <Expr id='X83'>
          <Function id='S83' op='min' aggregateFunc='4'>
            <Column id='C75' tableId='T1' colIndex='4'>
              CAT.MVQRTEST007.FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O85' name='SYS_COUNTSTAR1'>
        <Expr id='X85'>
          <Function id='S85' op='count' aggregateFunc='1'>
            <NumericVal id='S84' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O86' name='SYS_COUNT2'>
        <Expr id='X86'>
          <Function id='S86' op='count_nonull' aggregateFunc='2'>
            <Column ref='C75'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O91' name='FDAY'>
        <Column ref='C72'>
        </Column>
      </Output>
      <Output id='O94' name='FMONTH'>
        <Column ref='C73'>
        </Column>
      </Output>
      <Output id='O97' name='FYEAR'>
        <Column ref='C74'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G2'>
      <Primary>
        <Column id='C72' tableId='T1' colIndex='1'>
          CAT.MVQRTEST007.FACT.FDAY
        </Column>
        <Column id='C73' tableId='T1' colIndex='2'>
          CAT.MVQRTEST007.FACT.FMONTH
        </Column>
        <Column id='C74' tableId='T1' colIndex='3'>
          CAT.MVQRTEST007.FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv minbyMonth
+>        refresh on request
+>        initialized on create
+>        as  select min(min_day_price) min_month_price, fmonth, fyear
+>            from minbyDay
+>            group by fmonth, fyear;

*** WARNING[12112] A secondary index CAT.MVQRTEST007.MINBYMONTH_117351923_2113 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ MINBYMONTH;
>>obey TEST007(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.MVQRTEST007.MINBYMONTH
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874553877231' numCols='6'>
          CAT.MVQRTEST007.FACT
        </Table>
      </JBBCList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O95' name='MIN_MONTH_PRICE'>
        <Expr id='X95'>
          <Function id='S95' op='min' aggregateFunc='4'>
            <Column id='C82' tableId='T1' colIndex='4'>
              CAT.MVQRTEST007.FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O105' name='FMONTH'>
        <Column ref='C80'>
        </Column>
      </Output>
      <Output id='O108' name='FYEAR'>
        <Column ref='C81'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G2'>
      <Primary>
        <Column id='C80' tableId='T1' colIndex='2'>
          CAT.MVQRTEST007.FACT.FMONTH
        </Column>
        <Column id='C81' tableId='T1' colIndex='3'>
          CAT.MVQRTEST007.FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv minbyYear
+>        refresh on request
+>        initialized on create
+>        as  select min(min_month_price) min_year_price, fyear
+>            from minbyMonth
+>            group by fyear;

*** WARNING[12112] A secondary index CAT.MVQRTEST007.MINBYYEAR_185222733_2113 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ MINBYYEAR;
>>obey TEST007(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.MVQRTEST007.MINBYYEAR
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874553877231' numCols='6'>
          CAT.MVQRTEST007.FACT
        </Table>
      </JBBCList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O89' name='MIN_YEAR_PRICE'>
        <Expr id='X89'>
          <Function id='S89' op='min' aggregateFunc='4'>
            <Column id='C73' tableId='T1' colIndex='4'>
              CAT.MVQRTEST007.FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O102' name='FYEAR'>
        <Column ref='C72'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G2'>
      <Primary>
        <Column id='C72' tableId='T1' colIndex='3'>
          CAT.MVQRTEST007.FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv countbyDay
+>        refresh on request
+>        initialized on create
+>        as  select count(*) count_by_day, fday, fmonth, fyear
+>            from fact
+>            group by fday, fmonth, fyear;

*** WARNING[12112] A secondary index CAT.MVQRTEST007.COUNTBYDAY_529856343_2113 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>
>>set pattern $$MVName$$ COUNTBYDAY;
>>obey TEST007(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.MVQRTEST007.COUNTBYDAY
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874553877231' numCols='6'>
          CAT.MVQRTEST007.FACT
        </Table>
      </JBBCList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O79' name='COUNT_BY_DAY'>
        <Expr id='X79'>
          <Function id='S79' op='count' aggregateFunc='1'>
            <NumericVal id='S78' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O84' name='FDAY'>
        <Column ref='C67'>
        </Column>
      </Output>
      <Output id='O87' name='FMONTH'>
        <Column ref='C68'>
        </Column>
      </Output>
      <Output id='O90' name='FYEAR'>
        <Column ref='C69'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G2'>
      <Primary>
        <Column id='C67' tableId='T1' colIndex='1'>
          CAT.MVQRTEST007.FACT.FDAY
        </Column>
        <Column id='C68' tableId='T1' colIndex='2'>
          CAT.MVQRTEST007.FACT.FMONTH
        </Column>
        <Column id='C69' tableId='T1' colIndex='3'>
          CAT.MVQRTEST007.FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv countbyMonth
+>        refresh on request
+>        initialized on create
+>        as  select sum(count_by_day) count_by_month, fmonth, fyear
+>            from countbyDay
+>            group by fmonth, fyear;

*** WARNING[12112] A secondary index CAT.MVQRTEST007.COUNTBYMONTH_634326153_2113 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ COUNTBYMONTH;
>>obey TEST007(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.MVQRTEST007.COUNTBYMONTH
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874553877231' numCols='6'>
          CAT.MVQRTEST007.FACT
        </Table>
      </JBBCList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O83' name='COUNT_BY_MONTH'>
        <Expr id='X83'>
          <Function id='S83' op='count' aggregateFunc='1'>
            <NumericVal id='S80' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O93' name='FMONTH'>
        <Column ref='C70'>
        </Column>
      </Output>
      <Output id='O96' name='FYEAR'>
        <Column ref='C71'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G2'>
      <Primary>
        <Column id='C70' tableId='T1' colIndex='2'>
          CAT.MVQRTEST007.FACT.FMONTH
        </Column>
        <Column id='C71' tableId='T1' colIndex='3'>
          CAT.MVQRTEST007.FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>create mv countbyYear
+>        refresh on request
+>        initialized on create
+>        as  select sum(count_by_month) count_by_year, fyear
+>            from countbyMonth
+>            group by fyear;

*** WARNING[12112] A secondary index CAT.MVQRTEST007.COUNTBYYEAR_355365853_2113 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ COUNTBYYEAR;
>>obey TEST007(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.MVQRTEST007.COUNTBYYEAR
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874553877231' numCols='6'>
          CAT.MVQRTEST007.FACT
        </Table>
      </JBBCList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O83' name='COUNT_BY_YEAR'>
        <Expr id='X83'>
          <Function id='S83' op='count' aggregateFunc='1'>
            <NumericVal id='S78' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O96' name='FYEAR'>
        <Column ref='C69'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G2'>
      <Primary>
        <Column id='C69' tableId='T1' colIndex='3'>
          CAT.MVQRTEST007.FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv  MV1
+>    refresh on request 
+>    initialize on create 
+>    enable query rewrite
+>    as select fprice, dimkey, dim1.fk1, dim1.dkey
+>       from fact, dim1
+>       where dimkey=dim1.dkey;

*** WARNING[12112] A secondary index CAT.MVQRTEST007.MV1_165632173_2113 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ MV1;
>>obey TEST007(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.MVQRTEST007.MV1
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874552174259' isKeyCovered='1' numCols='3'>
          CAT.MVQRTEST007.DIM1
        </Table>
        <Table id='T2' TS='212223874553877231' isKeyCovered='1' numCols='6'>
          CAT.MVQRTEST007.FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J147'>
          <Column id='C123' tableId='T1' colIndex='0' isNullable='0'>
            CAT.MVQRTEST007.DIM1.DKEY
          </Column>
          <Column ref='C116'>
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O132' name='SYS_SYSKEY1'>
        <Column id='C111' tableId='T2' colIndex='0' isNullable='0'>
          CAT.MVQRTEST007.FACT.SYSKEY
        </Column>
      </Output>
      <Output id='O144' name='FPRICE'>
        <Column id='C115' tableId='T2' colIndex='4'>
          CAT.MVQRTEST007.FACT.FPRICE
        </Column>
      </Output>
      <Output id='O147' name='DIMKEY'>
        <Column id='C116' tableId='T2' colIndex='5'>
          CAT.MVQRTEST007.FACT.DIMKEY
        </Column>
      </Output>
      <Output id='O156' name='FK1'>
        <Column id='C125' tableId='T1' colIndex='2'>
          CAT.MVQRTEST007.DIM1.FK1
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv  MV2
+>    refresh on request 
+>    initialize on create 
+>    enable query rewrite
+>    as select dim2.dkey
+>       from dim1, dim2
+>       where dim1.fk1 = dim2.dkey;

*** WARNING[12112] A secondary index CAT.MVQRTEST007.MV2_518135383_2113 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ MV2;
>>obey TEST007(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.MVQRTEST007.MV2
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212223874595496885' isKeyCovered='1' numCols='3'>
          CAT.MVQRTEST007.DIM1
        </Table>
        <Table id='T1' TS='212223874552924192' isKeyCovered='1' numCols='3'>
          CAT.MVQRTEST007.DIM2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J115'>
          <Column ref='C93'>
          </Column>
          <Column id='C98' tableId='T1' colIndex='0' isNullable='0'>
            CAT.MVQRTEST007.DIM2.DKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O109' name='SYS_DKEY1'>
        <Column id='C91' tableId='T2' colIndex='0' isNullable='0'>
          CAT.MVQRTEST007.DIM1.DKEY
        </Column>
      </Output>
      <Output id='O115' name='DKEY'>
        <Column id='C93' tableId='T2' colIndex='2'>
          CAT.MVQRTEST007.DIM1.FK1
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>obey TEST007(MATCH1);
>>--===========================================
>>--== Query 1
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST007(query1);
>>prepare QueryStmt from
+>  select sum(fprice), fyear
+>  from fact 
+>  group by fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>set pattern $$QueryName$$ ROLLUP1;
>>obey TEST007(dump_Query);
>>--===========================================
>>--== Create the query descriptor XML
>>--===========================================
>>
>>log $$QueryName$$.tmp clear;
>>sh sh ./CropDescriptor.ksh -q -i $$QueryName$$.TMP -o $$QueryName$$.xml;
>>-- and once for the test log file
>>execute QueryStmt;

Query Descriptor                                                     
---------------------------------------------------------------------

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874595496885' numCols='6'>          
          CAT.MVQRTEST007.FACT                                       
          <Key>                                                      
            <Column id='C1' tableId='T1' colIndex='0' isNullable='0'>
              CAT.MVQRTEST007.FACT.SYSKEY                            
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O13'>                                              
        <Expr id='X13'>                                              
          <Function id='S13' op='sum' aggregateFunc='3'>             
            <Column id='C5' tableId='T1' colIndex='4'>               
              CAT.MVQRTEST007.FACT.FPRICE                            
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O24'>                                              
        <Column ref='C4'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G2'>                                                
      <Primary>                                                      
        <Column id='C4' tableId='T1' colIndex='3'>                   
          CAT.MVQRTEST007.FACT.FYEAR                                 
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

--- 45 row(s) selected.
>>
>>--===========================================
>>--===========================================
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

--- SQL operation complete.
>>
>>sh echo INITIALIZE >> rollup1.in;
>>sh echo PUBLISH SUMBYDAY.xml >> rollup1.in;
>>sh echo PUBLISH SUMBYMONTH.xml  >> rollup1.in;
>>sh echo PUBLISH SUMBYYEAR.xml  >> rollup1.in;
>>sh echo MATCH ROLLUP1.xml >> rollup1.in;
>>
>>sh sh -c "$QMS rollup1.in mvqrtest007.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset ref='G2' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.MVQRTEST007.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.SUMBYDAY
          </MVName>
          <GroupBy ref='G2' result='NotProvided'>
            <Primary>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
            <Output ref='O13' result='NotProvided'>
              <Expr ref='X13'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S13'>
                    DAY_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.SUMBYMONTH
          </MVName>
          <GroupBy ref='G2' result='NotProvided'>
            <Primary>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
            <Output ref='O13' result='NotProvided'>
              <Expr ref='X13'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S13'>
                    MONTH_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.SUMBYYEAR
          </MVName>
          <GroupBy ref='G2' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O13' result='Provided'>
              <MVColumn ref='X13'>
                YEAR_PRICE
              </MVColumn>
            </Output>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.MVQRTEST007.COUNTBYDAY was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.COUNTBYMONTH was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.COUNTBYYEAR was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.MINBYDAY was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.MINBYMONTH was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.MINBYYEAR was disqualified because aggregate function S13 cannot be matched.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify that the MV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.MVQRTEST007.SUMBYDAY, :CAT.MVQRTEST007.SUMBYMONTH';

--- SQL operation complete.
>>
>>obey TEST007(query1);
>>prepare QueryStmt from
+>  select sum(fprice), fyear
+>  from fact 
+>  group by fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.MVQRTEST007.SUMBYMONTH                                  

--- 1 row(s) selected.
>>
>>-- Verify that the MV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.MVQRTEST007.SUMBYDAY, :CAT.MVQRTEST007.SUMBYMONTH, :CAT.MVQRTEST007.SUMBYYEAR';

--- SQL operation complete.
>>
>>obey TEST007(query1);
>>prepare QueryStmt from
+>  select sum(fprice), fyear
+>  from fact 
+>  group by fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.MVQRTEST007.SUMBYYEAR                                   

--- 1 row(s) selected.
>>
>>--===========================================
>>--== Query 2
>>--===========================================
>>obey TEST007(MATCH2);
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST007(query2);
>>prepare QueryStmt from
+>  select sum(fprice), fyear
+>  from fact
+>  group by fmonth, fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>set pattern $$QueryName$$ ROLLUP2;
>>obey TEST007(dump_Query);
>>--===========================================
>>--== Create the query descriptor XML
>>--===========================================
>>
>>log $$QueryName$$.tmp clear;
>>sh sh ./CropDescriptor.ksh -q -i $$QueryName$$.TMP -o $$QueryName$$.xml;
>>-- and once for the test log file
>>execute QueryStmt;

Query Descriptor                                                     
---------------------------------------------------------------------

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874595496885' numCols='6'>          
          CAT.MVQRTEST007.FACT                                       
          <Key>                                                      
            <Column id='C1' tableId='T1' colIndex='0' isNullable='0'>
              CAT.MVQRTEST007.FACT.SYSKEY                            
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O13'>                                              
        <Expr id='X13'>                                              
          <Function id='S13' op='sum' aggregateFunc='3'>             
            <Column id='C5' tableId='T1' colIndex='4'>               
              CAT.MVQRTEST007.FACT.FPRICE                            
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O24'>                                              
        <Column ref='C4'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G2'>                                                
      <Primary>                                                      
        <Column id='C3' tableId='T1' colIndex='2'>                   
          CAT.MVQRTEST007.FACT.FMONTH                                
        </Column>                                                    
        <Column id='C4' tableId='T1' colIndex='3'>                   
          CAT.MVQRTEST007.FACT.FYEAR                                 
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

--- 48 row(s) selected.
>>
>>--===========================================
>>--===========================================
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

--- SQL operation complete.
>>
>>sh echo INITIALIZE >> rollup2.in;
>>sh echo PUBLISH SUMBYDAY.xml >> rollup2.in;
>>sh echo PUBLISH SUMBYMONTH.xml  >> rollup2.in;
>>sh echo PUBLISH SUMBYYEAR.xml  >> rollup2.in;
>>sh echo MATCH ROLLUP2.xml >> rollup2.in;
>>
>>sh sh -c "$QMS rollup2.in mvqrtest007.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset ref='G2' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.MVQRTEST007.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.SUMBYDAY
          </MVName>
          <GroupBy ref='G2' result='NotProvided'>
            <Primary>
              <MVColumn ref='C3'>
                FMONTH
              </MVColumn>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
            <Output ref='O13' result='NotProvided'>
              <Expr ref='X13'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S13'>
                    DAY_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.SUMBYMONTH
          </MVName>
          <GroupBy ref='G2' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O13' result='Provided'>
              <MVColumn ref='X13'>
                MONTH_PRICE
              </MVColumn>
            </Output>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.MVQRTEST007.COUNTBYDAY was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.COUNTBYMONTH was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.MINBYDAY was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.MINBYMONTH was disqualified because aggregate function S13 cannot be matched.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify that the MV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.MVQRTEST007.SUMBYDAY';

--- SQL operation complete.
>>
>>obey TEST007(query2);
>>prepare QueryStmt from
+>  select sum(fprice), fyear
+>  from fact
+>  group by fmonth, fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.MVQRTEST007.SUMBYDAY                                    

--- 1 row(s) selected.
>>
>>-- Verify that the MV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.MVQRTEST007.SUMBYDAY, :CAT.MVQRTEST007.SUMBYMONTH, :CAT.MVQRTEST007.SUMBYYEAR';

--- SQL operation complete.
>>
>>obey TEST007(query2);
>>prepare QueryStmt from
+>  select sum(fprice), fyear
+>  from fact
+>  group by fmonth, fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.MVQRTEST007.SUMBYMONTH                                  

--- 1 row(s) selected.
>>
>>
>>obey TEST007(MATCH3);
>>--===========================================
>>--== Query 3
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST007(query3);
>>prepare QueryStmt from
+>  select min(fprice), fyear
+>  from fact
+>  group by fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>set pattern $$QueryName$$ ROLLUP3;
>>obey TEST007(dump_Query);
>>--===========================================
>>--== Create the query descriptor XML
>>--===========================================
>>
>>log $$QueryName$$.tmp clear;
>>sh sh ./CropDescriptor.ksh -q -i $$QueryName$$.TMP -o $$QueryName$$.xml;
>>-- and once for the test log file
>>execute QueryStmt;

Query Descriptor                                                     
---------------------------------------------------------------------

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874595496885' numCols='6'>          
          CAT.MVQRTEST007.FACT                                       
          <Key>                                                      
            <Column id='C1' tableId='T1' colIndex='0' isNullable='0'>
              CAT.MVQRTEST007.FACT.SYSKEY                            
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O13'>                                              
        <Expr id='X13'>                                              
          <Function id='S13' op='min' aggregateFunc='4'>             
            <Column id='C5' tableId='T1' colIndex='4'>               
              CAT.MVQRTEST007.FACT.FPRICE                            
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O24'>                                              
        <Column ref='C4'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G2'>                                                
      <Primary>                                                      
        <Column id='C4' tableId='T1' colIndex='3'>                   
          CAT.MVQRTEST007.FACT.FYEAR                                 
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

--- 45 row(s) selected.
>>
>>--===========================================
>>--===========================================
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

--- SQL operation complete.
>>
>>sh echo INITIALIZE >> rollup3.in;
>>sh echo PUBLISH MINBYDAY.xml >> rollup3.in;
>>sh echo PUBLISH MINBYMONTH.xml >> rollup3.in;
>>sh echo PUBLISH MINBYYEAR.xml >> rollup3.in;
>>sh echo MATCH ROLLUP3.xml >> rollup3.in;
>>
>>sh sh -c "$QMS rollup3.in mvqrtest007.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset ref='G2' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.MVQRTEST007.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.MINBYDAY
          </MVName>
          <GroupBy ref='G2' result='NotProvided'>
            <Primary>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
            <Output ref='O13' result='NotProvided'>
              <Expr ref='X13'>
                <Function op='min' aggregateFunc='4'>
                  <MVColumn ref='S13'>
                    MIN_DAY_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.MINBYMONTH
          </MVName>
          <GroupBy ref='G2' result='NotProvided'>
            <Primary>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
            <Output ref='O13' result='NotProvided'>
              <Expr ref='X13'>
                <Function op='min' aggregateFunc='4'>
                  <MVColumn ref='S13'>
                    MIN_MONTH_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.MINBYYEAR
          </MVName>
          <GroupBy ref='G2' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O13' result='Provided'>
              <MVColumn ref='X13'>
                MIN_YEAR_PRICE
              </MVColumn>
            </Output>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.MVQRTEST007.COUNTBYDAY was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.COUNTBYMONTH was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.COUNTBYYEAR was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.SUMBYDAY was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.SUMBYMONTH was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.SUMBYYEAR was disqualified because aggregate function S13 cannot be matched.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify that the MV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.MVQRTEST007.MINBYDAY, CAT.MVQRTEST007.MINBYMONTH';

--- SQL operation complete.
>>
>>obey TEST007(query3);
>>prepare QueryStmt from
+>  select min(fprice), fyear
+>  from fact
+>  group by fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.MVQRTEST007.MINBYMONTH                                  

--- 1 row(s) selected.
>>
>>-- Verify that the MV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.MVQRTEST007.MINBYDAY, :CAT.MVQRTEST007.MINBYMONTH, :CAT.MVQRTEST007.MINBYYEAR';

--- SQL operation complete.
>>
>>obey TEST007(query3);
>>prepare QueryStmt from
+>  select min(fprice), fyear
+>  from fact
+>  group by fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.MVQRTEST007.MINBYYEAR                                   

--- 1 row(s) selected.
>>
>>
>>--===========================================
>>--== Query 4
>>--===========================================
>>obey TEST007(MATCH4);
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST007(query4);
>>prepare QueryStmt from
+>  select min(fprice), fyear
+>  from fact
+>  group by fmonth, fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>set pattern $$QueryName$$ ROLLUP4;
>>obey TEST007(dump_Query);
>>--===========================================
>>--== Create the query descriptor XML
>>--===========================================
>>
>>log $$QueryName$$.tmp clear;
>>sh sh ./CropDescriptor.ksh -q -i $$QueryName$$.TMP -o $$QueryName$$.xml;
>>-- and once for the test log file
>>execute QueryStmt;

Query Descriptor                                                     
---------------------------------------------------------------------

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874595496885' numCols='6'>          
          CAT.MVQRTEST007.FACT                                       
          <Key>                                                      
            <Column id='C1' tableId='T1' colIndex='0' isNullable='0'>
              CAT.MVQRTEST007.FACT.SYSKEY                            
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O13'>                                              
        <Expr id='X13'>                                              
          <Function id='S13' op='min' aggregateFunc='4'>             
            <Column id='C5' tableId='T1' colIndex='4'>               
              CAT.MVQRTEST007.FACT.FPRICE                            
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O24'>                                              
        <Column ref='C4'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G2'>                                                
      <Primary>                                                      
        <Column id='C3' tableId='T1' colIndex='2'>                   
          CAT.MVQRTEST007.FACT.FMONTH                                
        </Column>                                                    
        <Column id='C4' tableId='T1' colIndex='3'>                   
          CAT.MVQRTEST007.FACT.FYEAR                                 
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

--- 48 row(s) selected.
>>
>>--===========================================
>>--===========================================
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

--- SQL operation complete.
>>
>>sh echo INITIALIZE >> rollup4.in;
>>sh echo PUBLISH MINBYDAY.xml >> rollup4.in;
>>sh echo PUBLISH MINBYMONTH.xml  >> rollup4.in;
>>sh echo PUBLISH MINBYYEAR.xml  >> rollup4.in;
>>sh echo MATCH ROLLUP4.xml >> rollup4.in;
>>
>>sh sh -c "$QMS rollup4.in mvqrtest007.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset ref='G2' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.MVQRTEST007.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.MINBYDAY
          </MVName>
          <GroupBy ref='G2' result='NotProvided'>
            <Primary>
              <MVColumn ref='C3'>
                FMONTH
              </MVColumn>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
            <Output ref='O13' result='NotProvided'>
              <Expr ref='X13'>
                <Function op='min' aggregateFunc='4'>
                  <MVColumn ref='S13'>
                    MIN_DAY_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.MINBYMONTH
          </MVName>
          <GroupBy ref='G2' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O13' result='Provided'>
              <MVColumn ref='X13'>
                MIN_MONTH_PRICE
              </MVColumn>
            </Output>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.MVQRTEST007.COUNTBYDAY was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.COUNTBYMONTH was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.SUMBYDAY was disqualified because aggregate function S13 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.MVQRTEST007.SUMBYMONTH was disqualified because aggregate function S13 cannot be matched.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify that the MV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.MVQRTEST007.MINBYDAY';

--- SQL operation complete.
>>
>>obey TEST007(query4);
>>prepare QueryStmt from
+>  select min(fprice), fyear
+>  from fact
+>  group by fmonth, fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.MVQRTEST007.MINBYDAY                                    

--- 1 row(s) selected.
>>
>>-- Verify that the MV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.MVQRTEST007.MINBYDAY, :CAT.MVQRTEST007.MINBYMONTH, :CAT.MVQRTEST007.MINBYYEAR';

--- SQL operation complete.
>>
>>obey TEST007(query4);
>>prepare QueryStmt from
+>  select min(fprice), fyear
+>  from fact
+>  group by fmonth, fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.MVQRTEST007.MINBYMONTH                                  

--- 1 row(s) selected.
>>
>>obey TEST007(MATCH5);
>>--===========================================
>>--== Query 5
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST007(query5);
>>prepare QueryStmt from
+>  select count(*), fyear
+>  from fact
+>  group by fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>set pattern $$QueryName$$ ROLLUP5;
>>obey TEST007(dump_Query);
>>--===========================================
>>--== Create the query descriptor XML
>>--===========================================
>>
>>log $$QueryName$$.tmp clear;
>>sh sh ./CropDescriptor.ksh -q -i $$QueryName$$.TMP -o $$QueryName$$.xml;
>>-- and once for the test log file
>>execute QueryStmt;

Query Descriptor                                                     
---------------------------------------------------------------------

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874595496885' numCols='6'>          
          CAT.MVQRTEST007.FACT                                       
          <Key>                                                      
            <Column id='C1' tableId='T1' colIndex='0' isNullable='0'>
              CAT.MVQRTEST007.FACT.SYSKEY                            
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O14'>                                              
        <Expr id='X14'>                                              
          <Function id='S14' op='count' aggregateFunc='1'>           
            <NumericVal id='S13' scale='0'>                          
              1                                                      
            </NumericVal>                                            
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O25'>                                              
        <Column ref='C4'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G2'>                                                
      <Primary>                                                      
        <Column id='C4' tableId='T1' colIndex='3'>                   
          CAT.MVQRTEST007.FACT.FYEAR                                 
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

--- 45 row(s) selected.
>>
>>--===========================================
>>--===========================================
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

--- SQL operation complete.
>>
>>-- Don't do an INITIALIZE here; it causes unwanted MVs to be considered.
>>sh echo PUBLISH COUNTBYDAY.xml >> rollup5.in;
>>sh echo PUBLISH COUNTBYMONTH.xml >> rollup5.in;
>>sh echo PUBLISH COUNTBYYEAR.xml >> rollup5.in;
>>sh echo MATCH ROLLUP5.xml >> rollup5.in;
>>
>>sh sh -c "$QMS rollup5.in mvqrtest007.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset ref='G2' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.MVQRTEST007.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.COUNTBYDAY
          </MVName>
          <GroupBy ref='G2' result='NotProvided'>
            <Primary>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O25' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
            <Output ref='O14' result='NotProvided'>
              <Expr ref='X14'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn>
                    COUNT_BY_DAY
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.COUNTBYMONTH
          </MVName>
          <GroupBy ref='G2' result='NotProvided'>
            <Primary>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O25' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
            <Output ref='O14' result='NotProvided'>
              <Expr ref='X14'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn>
                    COUNT_BY_MONTH
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.COUNTBYYEAR
          </MVName>
          <GroupBy ref='G2' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O14' result='Provided'>
              <MVColumn ref='X14'>
                COUNT_BY_YEAR
              </MVColumn>
            </Output>
            <Output ref='O25' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
  </JbbResult>
</Result>
>>
>>-- Verify that the MV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.MVQRTEST007.COUNTBYMONTH';

--- SQL operation complete.
>>
>>obey TEST007(query5);
>>prepare QueryStmt from
+>  select count(*), fyear
+>  from fact
+>  group by fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.MVQRTEST007.COUNTBYMONTH                                

--- 1 row(s) selected.
>>
>>-- Verify that the MV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.MVQRTEST007.COUNTBYDAY, :CAT.MVQRTEST007.COUNTBYMONTH, :CAT.MVQRTEST007.COUNTBYYEAR';

--- SQL operation complete.
>>
>>obey TEST007(query5);
>>prepare QueryStmt from
+>  select count(*), fyear
+>  from fact
+>  group by fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.MVQRTEST007.COUNTBYYEAR                                 

--- 1 row(s) selected.
>>
>>obey TEST007(MATCH6);
>>--===========================================
>>--== Query 6
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST007(query6);
>>prepare QueryStmt from
+>  select count(*), fyear
+>  from fact
+>  group by fmonth, fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>set pattern $$QueryName$$ ROLLUP6;
>>obey TEST007(dump_Query);
>>--===========================================
>>--== Create the query descriptor XML
>>--===========================================
>>
>>log $$QueryName$$.tmp clear;
>>sh sh ./CropDescriptor.ksh -q -i $$QueryName$$.TMP -o $$QueryName$$.xml;
>>-- and once for the test log file
>>execute QueryStmt;

Query Descriptor                                                     
---------------------------------------------------------------------

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874595496885' numCols='6'>          
          CAT.MVQRTEST007.FACT                                       
          <Key>                                                      
            <Column id='C1' tableId='T1' colIndex='0' isNullable='0'>
              CAT.MVQRTEST007.FACT.SYSKEY                            
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O14'>                                              
        <Expr id='X14'>                                              
          <Function id='S14' op='count' aggregateFunc='1'>           
            <NumericVal id='S13' scale='0'>                          
              1                                                      
            </NumericVal>                                            
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O25'>                                              
        <Column ref='C4'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G2'>                                                
      <Primary>                                                      
        <Column id='C3' tableId='T1' colIndex='2'>                   
          CAT.MVQRTEST007.FACT.FMONTH                                
        </Column>                                                    
        <Column id='C4' tableId='T1' colIndex='3'>                   
          CAT.MVQRTEST007.FACT.FYEAR                                 
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

--- 48 row(s) selected.
>>
>>--===========================================
>>--===========================================
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

--- SQL operation complete.
>>
>>-- Don't do an INITIALIZE here; it causes unwanted MVs to be considered.
>>sh echo PUBLISH COUNTBYDAY.xml >> rollup6.in;
>>sh echo PUBLISH COUNTBYMONTH.xml  >> rollup6.in;
>>sh echo PUBLISH COUNTBYYEAR.xml  >> rollup6.in;
>>sh echo MATCH ROLLUP6.xml >> rollup6.in;
>>
>>sh sh -c "$QMS rollup6.in mvqrtest007.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset ref='G2' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.MVQRTEST007.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.COUNTBYDAY
          </MVName>
          <GroupBy ref='G2' result='NotProvided'>
            <Primary>
              <MVColumn ref='C3'>
                FMONTH
              </MVColumn>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O25' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
            <Output ref='O14' result='NotProvided'>
              <Expr ref='X14'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn>
                    COUNT_BY_DAY
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.COUNTBYMONTH
          </MVName>
          <GroupBy ref='G2' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O14' result='Provided'>
              <MVColumn ref='X14'>
                COUNT_BY_MONTH
              </MVColumn>
            </Output>
            <Output ref='O25' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
  </JbbResult>
</Result>
>>
>>-- Verify that the MV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.MVQRTEST007.COUNTBYDAY';

--- SQL operation complete.
>>
>>obey TEST007(query6);
>>prepare QueryStmt from
+>  select count(*), fyear
+>  from fact
+>  group by fmonth, fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.MVQRTEST007.COUNTBYDAY                                  

--- 1 row(s) selected.
>>
>>-- Verify that the MV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.MVQRTEST007.COUNTBYDAY, :CAT.MVQRTEST007.COUNTBYMONTH, :CAT.MVQRTEST007.COUNTBYYEAR';

--- SQL operation complete.
>>
>>obey TEST007(query6);
>>prepare QueryStmt from
+>  select count(*), fyear
+>  from fact
+>  group by fmonth, fyear
+>  order by fyear;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.MVQRTEST007.COUNTBYMONTH                                

--- 1 row(s) selected.
>>
>>obey TEST007(MATCH9);
>>--===========================================
>>--== Query 9
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST007(query9);
>>prepare QueryStmt from
+>  select fprice, dimkey
+>  from fact, dim1, dim2, dim3
+>  where dimkey=dim1.dkey
+>    and dim1.fk1=dim2.dkey
+>    and dim2.fk1=dim3.dkey
+>  order by dimkey;

--- SQL command prepared.
>>
>>
>>set pattern $$QueryName$$ ROLLUP9;
>>obey TEST007(dump_Query);
>>--===========================================
>>--== Create the query descriptor XML
>>--===========================================
>>
>>log $$QueryName$$.tmp clear;
>>sh sh ./CropDescriptor.ksh -q -i $$QueryName$$.TMP -o $$QueryName$$.xml;
>>-- and once for the test log file
>>execute QueryStmt;

Query Descriptor                                                      
----------------------------------------------------------------------

<Query>                                                               
  <Version>                                                           
    1.0                                                               
  </Version>                                                          
  <Misc rewriteLevel='3'>                                             
  </Misc>                                                             
  <JBB id='B0'>                                                       
    <Hub>                                                             
      <JBBCList>                                                      
        <Table id='T2' TS='212223874595496885' numCols='3'>           
          CAT.MVQRTEST007.DIM1                                        
          <Key>                                                       
            <Column id='C13' tableId='T2' colIndex='0' isNullable='0'>
              CAT.MVQRTEST007.DIM1.DKEY                               
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
        <Table id='T1' TS='212223874602338708' numCols='3'>           
          CAT.MVQRTEST007.DIM2                                        
          <Key>                                                       
            <Column id='C20' tableId='T1' colIndex='0' isNullable='0'>
              CAT.MVQRTEST007.DIM2.DKEY                               
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
        <Table id='T3' TS='212223874595496885' numCols='6'>           
          CAT.MVQRTEST007.FACT                                        
          <Key>                                                       
            <Column id='C1' tableId='T3' colIndex='0' isNullable='0'> 
              CAT.MVQRTEST007.FACT.SYSKEY                             
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </JBBCList>                                                     
      <JoinPredList>                                                  
        <JoinPred id='J55'>                                           
          <Column ref='C13'>                                          
          </Column>                                                   
          <Column ref='C6'>                                           
          </Column>                                                   
        </JoinPred>                                                   
        <JoinPred id='J64'>                                           
          <Column id='C15' tableId='T2' colIndex='2'>                 
            CAT.MVQRTEST007.DIM1.FK1                                  
          </Column>                                                   
          <Column ref='C20'>                                          
          </Column>                                                   
        </JoinPred>                                                   
      </JoinPredList>                                                 
    </Hub>                                                            
    <ExtraHub>                                                        
    </ExtraHub>                                                       
    <OutputList>                                                      
      <Output id='O52'>                                               
        <Column id='C5' tableId='T3' colIndex='4'>                    
          CAT.MVQRTEST007.FACT.FPRICE                                 
        </Column>                                                     
      </Output>                                                       
      <Output id='O55'>                                               
        <Column id='C6' tableId='T3' colIndex='5'>                    
          CAT.MVQRTEST007.FACT.DIMKEY                                 
        </Column>                                                     
      </Output>                                                       
    </OutputList>                                                     
  </JBB>                                                              
</Query>                                                              

--- 66 row(s) selected.
>>
>>--===========================================
>>--===========================================
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

--- SQL operation complete.
>>
>>sh echo INITIALIZE >> rollup9.in;
>>sh echo PUBLISH MV1.xml  >> rollup9.in;
>>sh echo PUBLISH MV2.xml  >> rollup9.in;
>>sh echo MATCH ROLLUP9.xml >> rollup9.in;
>>
>>sh sh -c "$QMS rollup9.in mvqrtest007.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T2'>
          CAT.MVQRTEST007.DIM1
        </Table>
        <Table ref='T1'>
          CAT.MVQRTEST007.DIM2
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.MV2
          </MVName>
          <OutputList>
            <Output ref='C13' result='Provided'>
              <MVColumn ref='C13'>
                SYS_DKEY1
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T2'>
          CAT.MVQRTEST007.DIM1
        </Table>
        <Table ref='T3'>
          CAT.MVQRTEST007.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.MV1
          </MVName>
          <OutputList>
            <Output ref='O52' result='Provided'>
              <MVColumn ref='C5'>
                FPRICE
              </MVColumn>
            </Output>
            <Output ref='O55' result='Provided'>
              <MVColumn ref='C6'>
                DIMKEY
              </MVColumn>
            </Output>
            <Output ref='C15' result='Provided'>
              <MVColumn ref='C15'>
                FK1
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
  </JbbResult>
</Result>
>>
>>-- Verify that the MV is picked by the optimizer.
>>obey TEST007(query9);
>>prepare QueryStmt from
+>  select fprice, dimkey
+>  from fact, dim1, dim2, dim3
+>  where dimkey=dim1.dkey
+>    and dim1.fk1=dim2.dkey
+>    and dim2.fk1=dim3.dkey
+>  order by dimkey;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.MVQRTEST007.DIM2                                        
FILE_SCAN                       CAT.MVQRTEST007.MV1                                         

--- 2 row(s) selected.
>>
>>obey TEST007(MATCH10);
>>--===========================================
>>--== Query 10
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST007(query10);
>>prepare QueryStmt from
+>  select dim2.dkey
+>  from dim1, dim2, dim3
+>  where dim1.fk1=dim2.dkey
+>    and dim2.fk1=dim3.dkey
+>  order by dim2.dkey;

--- SQL command prepared.
>>
>>
>>set pattern $$QueryName$$ ROLLUP10;
>>obey TEST007(dump_Query);
>>--===========================================
>>--== Create the query descriptor XML
>>--===========================================
>>
>>log $$QueryName$$.tmp clear;
>>sh sh ./CropDescriptor.ksh -q -i $$QueryName$$.TMP -o $$QueryName$$.xml;
>>-- and once for the test log file
>>execute QueryStmt;

Query Descriptor                                                     
---------------------------------------------------------------------

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T2' TS='212223874595496885' numCols='3'>          
          CAT.MVQRTEST007.DIM1                                       
          <Key>                                                      
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>
              CAT.MVQRTEST007.DIM1.DKEY                              
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
        <Table id='T1' TS='212223874602338708' numCols='3'>          
          CAT.MVQRTEST007.DIM2                                       
          <Key>                                                      
            <Column id='C8' tableId='T1' colIndex='0' isNullable='0'>
              CAT.MVQRTEST007.DIM2.DKEY                              
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
      <JoinPredList>                                                 
        <JoinPred id='J33'>                                          
          <Column ref='C3'>                                          
          </Column>                                                  
          <Column ref='C8'>                                          
          </Column>                                                  
        </JoinPred>                                                  
      </JoinPredList>                                                
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O33'>                                              
        <Column id='C3' tableId='T2' colIndex='2'>                   
          CAT.MVQRTEST007.DIM1.FK1                                   
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
  </JBB>                                                             
</Query>                                                             

--- 46 row(s) selected.
>>
>>--===========================================
>>--===========================================
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

--- SQL operation complete.
>>
>>sh echo INITIALIZE >> rollup10.in;
>>sh echo PUBLISH MV1.xml  >> rollup10.in;
>>sh echo PUBLISH MV2.xml  >> rollup10.in;
>>sh echo MATCH ROLLUP10.xml >> rollup10.in;
>>
>>sh sh -c "$QMS rollup10.in mvqrtest007.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T2'>
          CAT.MVQRTEST007.DIM1
        </Table>
        <Table ref='T1'>
          CAT.MVQRTEST007.DIM2
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.MVQRTEST007.MV2
          </MVName>
          <OutputList>
            <Output ref='O33' result='Provided'>
              <MVColumn ref='C3'>
                DKEY
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
  </JbbResult>
</Result>
>>
>>-- Verify that the MV is picked by the optimizer.
>>obey TEST007(query10);
>>prepare QueryStmt from
+>  select dim2.dkey
+>  from dim1, dim2, dim3
+>  where dim1.fk1=dim2.dkey
+>    and dim2.fk1=dim3.dkey
+>  order by dim2.dkey;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.MVQRTEST007.MV2                                         

--- 1 row(s) selected.
>>
>>obey TEST007(clean_up);
>>----------------------------------------------------
>>------------------ clean up section ----------------
>>----------------------------------------------------
>>drop schema mvqrtest007 cascade;

--- SQL operation complete.
>>
>>exit;

End of MXCI Session

