>>obey TEST003(set_up);
>>----------------------------------------------------
>>-------------------- set up section ----------------
>>----------------------------------------------------
>>
>>control query default MVQR_REWRITE_LEVEL '4';

--- 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 REF_CONSTRAINT_NO_ACTION_LIKE_RESTRICT '1';

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

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

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

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

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

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

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

--- SQL operation complete.
>>
>>create table fact2 (
+>	qitem  int,
+>	qquant int,
+>	qprice numeric (8,2),
+>	qdimkey int) no partition;

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

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

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

--- SQL operation complete.
>>
>>alter table fact  add constraint fact1_fk foreign key (dimkey) references dim1(dkey);

--- SQL operation complete.
>>alter table fact2 add constraint fact2_fk foreign key (qdimkey) references dim1(dkey);

--- SQL operation complete.
>>alter table dim1  add constraint dim1_fk foreign key (fk1) references dim2(dkey);

--- SQL operation complete.
>>alter table dim2  add constraint dim2_fk foreign key (fk1) references dim3(dkey);

--- SQL operation complete.
>>
>>obey TEST003(create_mvs);
>>--===========================================
>>-- MJVs
>>--===========================================
>>
>>-- Single hub table
>>create mv outputs_mjv1
+>  refresh on request
+>  initialized on create
+>  as  select fprice, fquant, 
+>             fday oday, fmonth omonth, fyear oyear,
+>             dimkey
+>      from fact;

--- SQL operation complete.
>>
>>set pattern $$MVName$$ OUTPUTS_MJV1;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.OUTPUTS_MJV1
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B10000'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873907812239' isKeyCovered='1' numCols='8'>
          CAT.OUTPUTS.FACT
        </Table>
      </JBBCList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O125' name='SYS_SYSKEY1'>
        <Column id='C106' tableId='T1' colIndex='0' isNullable='0'>
          CAT.OUTPUTS.FACT.SYSKEY
        </Column>
      </Output>
      <Output id='O128' name='ODAY'>
        <Column id='C107' tableId='T1' colIndex='1'>
          CAT.OUTPUTS.FACT.FDAY
        </Column>
      </Output>
      <Output id='O131' name='OMONTH'>
        <Column id='C108' tableId='T1' colIndex='2'>
          CAT.OUTPUTS.FACT.FMONTH
        </Column>
      </Output>
      <Output id='O134' name='OYEAR'>
        <Column id='C109' tableId='T1' colIndex='3'>
          CAT.OUTPUTS.FACT.FYEAR
        </Column>
      </Output>
      <Output id='O140' name='FQUANT'>
        <Column id='C111' tableId='T1' colIndex='5'>
          CAT.OUTPUTS.FACT.FQUANT
        </Column>
      </Output>
      <Output id='O143' name='FPRICE'>
        <Column id='C112' tableId='T1' colIndex='6'>
          CAT.OUTPUTS.FACT.FPRICE
        </Column>
      </Output>
      <Output id='O146' name='DIMKEY'>
        <Column id='C113' tableId='T1' colIndex='7'>
          CAT.OUTPUTS.FACT.DIMKEY
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>-- Hub table + extra-hub table.
>>create mv outputs_mjv2
+>  refresh on request
+>  initialized on create
+>  as  select fprice, fquant, 
+>             dimkey, dimdata, 
+>	     fday oday, fmonth omonth, fyear oyear
+>      from fact, dim1
+>      where dimkey=dkey;

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

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ OUTPUTS_MJV2;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.OUTPUTS_MJV2
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212223873910671376' isKeyCovered='1' numCols='8'>
          CAT.OUTPUTS.FACT
        </Table>
      </JBBCList>
    </Hub>
    <ExtraHub>
      <TableList>
        <Table id='T1' TS='212223873909312114' isKeyCovered='1' numCols='3'>
          CAT.OUTPUTS.DIM1
        </Table>
      </TableList>
      <JoinPredList>
        <JoinPred id='J184'>
          <Column id='C152' tableId='T1' colIndex='0' isNullable='0'>
            CAT.OUTPUTS.DIM1.DKEY
          </Column>
          <Column ref='C141'>
          </Column>
        </JoinPred>
      </JoinPredList>
    </ExtraHub>
    <OutputList>
      <Output id='O163' name='SYS_SYSKEY1'>
        <Column id='C134' tableId='T2' colIndex='0' isNullable='0'>
          CAT.OUTPUTS.FACT.SYSKEY
        </Column>
      </Output>
      <Output id='O166' name='ODAY'>
        <Column id='C135' tableId='T2' colIndex='1'>
          CAT.OUTPUTS.FACT.FDAY
        </Column>
      </Output>
      <Output id='O169' name='OMONTH'>
        <Column id='C136' tableId='T2' colIndex='2'>
          CAT.OUTPUTS.FACT.FMONTH
        </Column>
      </Output>
      <Output id='O172' name='OYEAR'>
        <Column id='C137' tableId='T2' colIndex='3'>
          CAT.OUTPUTS.FACT.FYEAR
        </Column>
      </Output>
      <Output id='O178' name='FQUANT'>
        <Column id='C139' tableId='T2' colIndex='5'>
          CAT.OUTPUTS.FACT.FQUANT
        </Column>
      </Output>
      <Output id='O181' name='FPRICE'>
        <Column id='C140' tableId='T2' colIndex='6'>
          CAT.OUTPUTS.FACT.FPRICE
        </Column>
      </Output>
      <Output id='O184' name='DIMKEY'>
        <Column id='C141' tableId='T2' colIndex='7'>
          CAT.OUTPUTS.FACT.DIMKEY
        </Column>
      </Output>
      <Output id='O190' name='DIMDATA'>
        <Column id='C153' tableId='T1' colIndex='1'>
          CAT.OUTPUTS.DIM1.DIMDATA
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>-- 3 cascaded dimension tables
>>create mv outputs_mjv3
+>  refresh on request
+>  initialized on create
+>  as  select fprice, fquant, 
+>             dimkey, dim3.dimdata
+>      from fact, dim1, dim2, dim3
+>      where dimkey=dim1.dkey
+>        and dim1.fk1=dim2.dkey
+>        and dim2.fk1=dim3.dkey;

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

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

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

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ OUTPUTS_MJV3;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.OUTPUTS_MJV3
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T4' TS='212223873910671376' isKeyCovered='1' numCols='8'>
          CAT.OUTPUTS.FACT
        </Table>
      </JBBCList>
    </Hub>
    <ExtraHub>
      <TableList>
        <Table id='T3' TS='212223873922654754' isKeyCovered='1' numCols='3'>
          CAT.OUTPUTS.DIM1
        </Table>
        <Table id='T2' TS='212223873910046428' isKeyCovered='1' numCols='3'>
          CAT.OUTPUTS.DIM2
        </Table>
        <Table id='T1' TS='212223873910046428' isKeyCovered='1' numCols='3'>
          CAT.OUTPUTS.DIM3
        </Table>
      </TableList>
      <JoinPredList>
        <JoinPred id='J257'>
          <Column id='C207' tableId='T3' colIndex='0' isNullable='0'>
            CAT.OUTPUTS.DIM1.DKEY
          </Column>
          <Column ref='C196'>
          </Column>
        </JoinPred>
        <JoinPred id='J266'>
          <Column ref='C209'>
          </Column>
          <Column id='C216' tableId='T2' colIndex='0' isNullable='0'>
            CAT.OUTPUTS.DIM2.DKEY
          </Column>
        </JoinPred>
        <JoinPred id='J275'>
          <Column ref='C218'>
          </Column>
          <Column id='C225' tableId='T1' colIndex='0' isNullable='0'>
            CAT.OUTPUTS.DIM3.DKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </ExtraHub>
    <OutputList>
      <Output id='O236' name='SYS_SYSKEY1'>
        <Column id='C189' tableId='T4' colIndex='0' isNullable='0'>
          CAT.OUTPUTS.FACT.SYSKEY
        </Column>
      </Output>
      <Output id='O251' name='FQUANT'>
        <Column id='C194' tableId='T4' colIndex='5'>
          CAT.OUTPUTS.FACT.FQUANT
        </Column>
      </Output>
      <Output id='O254' name='FPRICE'>
        <Column id='C195' tableId='T4' colIndex='6'>
          CAT.OUTPUTS.FACT.FPRICE
        </Column>
      </Output>
      <Output id='O257' name='DIMKEY'>
        <Column id='C196' tableId='T4' colIndex='7'>
          CAT.OUTPUTS.FACT.DIMKEY
        </Column>
      </Output>
      <Output id='O266' name='SYS_DKEY2'>
        <Column id='C209' tableId='T3' colIndex='2'>
          CAT.OUTPUTS.DIM1.FK1
        </Column>
      </Output>
      <Output id='O275' name='SYS_DKEY3'>
        <Column id='C218' tableId='T2' colIndex='2'>
          CAT.OUTPUTS.DIM2.FK1
        </Column>
      </Output>
      <Output id='O281' name='DIMDATA'>
        <Column id='C226' tableId='T1' colIndex='1'>
          CAT.OUTPUTS.DIM3.DIMDATA
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>-- Two hub + one extra-hub
>>create mv outputs_mjv4
+>  refresh on request
+>  initialized on create
+>  as  select fprice, fquant, 
+>             qprice,
+>	     qdimkey, dimdata
+>      from fact f1, fact2 f2, dim1
+>      where dimkey=dkey
+>        and fitem=qitem
+>        and qdimkey=dkey;

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

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

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ OUTPUTS_MJV4;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.OUTPUTS_MJV4
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T3' TS='212223873910671376' isKeyCovered='1' numCols='8'>
          CAT.OUTPUTS.FACT
        </Table>
        <Table id='T2' TS='212223873908890274' isKeyCovered='1' numCols='5'>
          CAT.OUTPUTS.FACT2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J230'>
          <Column id='C179' tableId='T3' colIndex='4'>
            CAT.OUTPUTS.FACT.FITEM
          </Column>
          <Column id='C194' tableId='T2' colIndex='1'>
            CAT.OUTPUTS.FACT2.QITEM
          </Column>
        </JoinPred>
        <JoinPred id='J254'>
          <Column ref='C182'>
          </Column>
          <Column id='C197' tableId='T2' colIndex='4'>
            CAT.OUTPUTS.FACT2.QDIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
      <TableList>
        <Table id='T1' TS='212223873928216791' isKeyCovered='1' numCols='3'>
          CAT.OUTPUTS.DIM1
        </Table>
      </TableList>
      <JoinPredList>
        <JoinPred id='J254'>
          <Column id='C205' tableId='T1' colIndex='0' isNullable='0'>
            CAT.OUTPUTS.DIM1.DKEY
          </Column>
          <JoinPred ref='J254'>
          </JoinPred>
        </JoinPred>
      </JoinPredList>
    </ExtraHub>
    <OutputList>
      <Output id='O218' name='SYS_SYSKEY1'>
        <Column id='C175' tableId='T3' colIndex='0' isNullable='0'>
          CAT.OUTPUTS.FACT.SYSKEY
        </Column>
      </Output>
      <Output id='O233' name='FQUANT'>
        <Column id='C180' tableId='T3' colIndex='5'>
          CAT.OUTPUTS.FACT.FQUANT
        </Column>
      </Output>
      <Output id='O236' name='FPRICE'>
        <Column id='C181' tableId='T3' colIndex='6'>
          CAT.OUTPUTS.FACT.FPRICE
        </Column>
      </Output>
      <Output id='O242' name='SYS_SYSKEY2'>
        <Column id='C193' tableId='T2' colIndex='0' isNullable='0'>
          CAT.OUTPUTS.FACT2.SYSKEY
        </Column>
      </Output>
      <Output id='O251' name='QPRICE'>
        <Column id='C196' tableId='T2' colIndex='3'>
          CAT.OUTPUTS.FACT2.QPRICE
        </Column>
      </Output>
      <Output id='O254' name='QDIMKEY'>
        <Column id='C182' tableId='T3' colIndex='7'>
          CAT.OUTPUTS.FACT.DIMKEY
        </Column>
      </Output>
      <Output id='O260' name='DIMDATA'>
        <Column id='C206' tableId='T1' colIndex='1'>
          CAT.OUTPUTS.DIM1.DIMDATA
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>--===========================================
>>-- MAVs
>>--===========================================
>>
>>create mv outputs_mav1
+>  refresh on request
+>  initialized on create
+>  as  select sum(fprice*fquant) total_price, 
+>             sum(dimdata) sumdata, 
+>	     fday oday, fmonth omonth, fyear oyear	           
+>      from fact, dim1
+>      where dimkey=dkey
+>      group by fday, fmonth, fyear;

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

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ OUTPUTS_MAV1;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.OUTPUTS_MAV1
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873928216791' numCols='3'>
          CAT.OUTPUTS.DIM1
        </Table>
        <Table id='T2' TS='212223873938356572' numCols='8'>
          CAT.OUTPUTS.FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J175'>
          <Column id='C135' tableId='T1' colIndex='0' isNullable='0'>
            CAT.OUTPUTS.DIM1.DKEY
          </Column>
          <Column id='C124' tableId='T2' colIndex='7'>
            CAT.OUTPUTS.FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O146' name='TOTAL_PRICE'>
        <Expr id='X146'>
          <Function id='S146' op='sum' aggregateFunc='3'>
            <BinaryOper id='S145' op='*'>
              <Column id='C123' tableId='T2' colIndex='6'>
                CAT.OUTPUTS.FACT.FPRICE
              </Column>
              <Column id='C122' tableId='T2' colIndex='5'>
                CAT.OUTPUTS.FACT.FQUANT
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O147' name='SUMDATA'>
        <Expr id='X147'>
          <Function id='S147' op='sum' aggregateFunc='3'>
            <Column id='C136' tableId='T1' colIndex='1'>
              CAT.OUTPUTS.DIM1.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O149' name='SYS_COUNTSTAR1'>
        <Expr id='X149'>
          <Function id='S149' op='count' aggregateFunc='1'>
            <NumericVal id='S148' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O151' name='SYS_COUNT2'>
        <Expr id='X151'>
          <Function id='S151' op='count_nonull' aggregateFunc='2'>
            <BinaryOper id='S150' op='*'>
              <Column ref='C123'>
              </Column>
              <Column ref='C122'>
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O152' name='SYS_COUNT3'>
        <Expr id='X152'>
          <Function id='S152' op='count_nonull' aggregateFunc='2'>
            <Column ref='C136'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O157' name='ODAY'>
        <Column ref='C118'>
        </Column>
      </Output>
      <Output id='O160' name='OMONTH'>
        <Column ref='C119'>
        </Column>
      </Output>
      <Output id='O163' name='OYEAR'>
        <Column ref='C120'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C118' tableId='T2' colIndex='1'>
          CAT.OUTPUTS.FACT.FDAY
        </Column>
        <Column id='C119' tableId='T2' colIndex='2'>
          CAT.OUTPUTS.FACT.FMONTH
        </Column>
        <Column id='C120' tableId='T2' colIndex='3'>
          CAT.OUTPUTS.FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>create mv outputs_mav2
+>  refresh on request
+>  initialized on create
+>  as  select sum(fprice*fquant) total_price, 
+>             sum(dimdata) sumdata, 
+>	     fmonth omonth, fyear oyear
+>      from fact, dim1
+>      where dimkey=dkey
+>      group by fmonth, fyear;

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

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ OUTPUTS_MAV2;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.OUTPUTS_MAV2
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873928216791' numCols='3'>
          CAT.OUTPUTS.DIM1
        </Table>
        <Table id='T2' TS='212223873938356572' numCols='8'>
          CAT.OUTPUTS.FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J172'>
          <Column id='C132' tableId='T1' colIndex='0' isNullable='0'>
            CAT.OUTPUTS.DIM1.DKEY
          </Column>
          <Column id='C121' tableId='T2' colIndex='7'>
            CAT.OUTPUTS.FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O143' name='TOTAL_PRICE'>
        <Expr id='X143'>
          <Function id='S143' op='sum' aggregateFunc='3'>
            <BinaryOper id='S142' op='*'>
              <Column id='C120' tableId='T2' colIndex='6'>
                CAT.OUTPUTS.FACT.FPRICE
              </Column>
              <Column id='C119' tableId='T2' colIndex='5'>
                CAT.OUTPUTS.FACT.FQUANT
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O144' name='SUMDATA'>
        <Expr id='X144'>
          <Function id='S144' op='sum' aggregateFunc='3'>
            <Column id='C133' tableId='T1' colIndex='1'>
              CAT.OUTPUTS.DIM1.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O146' name='SYS_COUNTSTAR1'>
        <Expr id='X146'>
          <Function id='S146' op='count' aggregateFunc='1'>
            <NumericVal id='S145' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O148' name='SYS_COUNT2'>
        <Expr id='X148'>
          <Function id='S148' op='count_nonull' aggregateFunc='2'>
            <BinaryOper id='S147' op='*'>
              <Column ref='C120'>
              </Column>
              <Column ref='C119'>
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O149' name='SYS_COUNT3'>
        <Expr id='X149'>
          <Function id='S149' op='count_nonull' aggregateFunc='2'>
            <Column ref='C133'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O157' name='OMONTH'>
        <Column ref='C116'>
        </Column>
      </Output>
      <Output id='O160' name='OYEAR'>
        <Column ref='C117'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C116' tableId='T2' colIndex='2'>
          CAT.OUTPUTS.FACT.FMONTH
        </Column>
        <Column id='C117' tableId='T2' colIndex='3'>
          CAT.OUTPUTS.FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>create mv outputs_mav3
+>  refresh on request
+>  initialized on create
+>  as  select sum(fprice*fquant) total_price, 
+>	     sum(dimdata) sumdata, 
+>	     fyear oyear
+>      from fact, dim1
+>      where dimkey=dkey
+>      group by fyear;

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

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ OUTPUTS_MAV3;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.OUTPUTS_MAV3
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873928216791' numCols='3'>
          CAT.OUTPUTS.DIM1
        </Table>
        <Table id='T2' TS='212223873938356572' numCols='8'>
          CAT.OUTPUTS.FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J169'>
          <Column id='C129' tableId='T1' colIndex='0' isNullable='0'>
            CAT.OUTPUTS.DIM1.DKEY
          </Column>
          <Column id='C118' tableId='T2' colIndex='7'>
            CAT.OUTPUTS.FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O140' name='TOTAL_PRICE'>
        <Expr id='X140'>
          <Function id='S140' op='sum' aggregateFunc='3'>
            <BinaryOper id='S139' op='*'>
              <Column id='C117' tableId='T2' colIndex='6'>
                CAT.OUTPUTS.FACT.FPRICE
              </Column>
              <Column id='C116' tableId='T2' colIndex='5'>
                CAT.OUTPUTS.FACT.FQUANT
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O141' name='SUMDATA'>
        <Expr id='X141'>
          <Function id='S141' op='sum' aggregateFunc='3'>
            <Column id='C130' tableId='T1' colIndex='1'>
              CAT.OUTPUTS.DIM1.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O143' name='SYS_COUNTSTAR1'>
        <Expr id='X143'>
          <Function id='S143' op='count' aggregateFunc='1'>
            <NumericVal id='S142' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O145' name='SYS_COUNT2'>
        <Expr id='X145'>
          <Function id='S145' op='count_nonull' aggregateFunc='2'>
            <BinaryOper id='S144' op='*'>
              <Column ref='C117'>
              </Column>
              <Column ref='C116'>
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O146' name='SYS_COUNT3'>
        <Expr id='X146'>
          <Function id='S146' op='count_nonull' aggregateFunc='2'>
            <Column ref='C130'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O157' name='OYEAR'>
        <Column ref='C114'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C114' tableId='T2' colIndex='3'>
          CAT.OUTPUTS.FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>create mv outputs_mav4
+>  refresh on request
+>  initialized on create
+>  as  select count(*)                 countstar,
+>             count(dimdata)           count_d,
+>	   --count(distinct dimdata)  count_dis_d,
+>  	     sum(dimdata)             sum_d, 
+>	   --sum(distinct dimdata)    sum_dis_d, 
+>	     min(dimdata)             min_d,
+>	     max(dimdata)             max_d,
+>	     stddev(dimdata)          stddev_d,
+>	     stddev(dimdata, fyear)   stddev_dy,
+>	     stddev(dimdata, fmonth)  stddev_dm,
+>	     variance(dimdata)        variance_d,
+>	     sum(dimdata*dimdata)     sum_dd,
+>	     fyear                    oyear,
+>	     fmonth                   omonth
+>      from fact, dim1
+>      where dimkey=dkey
+>      group by fyear, fmonth;

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

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ OUTPUTS_MAV4;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.OUTPUTS_MAV4
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873928216791' numCols='3'>
          CAT.OUTPUTS.DIM1
        </Table>
        <Table id='T2' TS='212223873938356572' numCols='8'>
          CAT.OUTPUTS.FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J328'>
          <Column id='C225' tableId='T1' colIndex='0' isNullable='0'>
            CAT.OUTPUTS.DIM1.DKEY
          </Column>
          <Column id='C214' tableId='T2' colIndex='7'>
            CAT.OUTPUTS.FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O236' name='COUNTSTAR'>
        <Expr id='X236'>
          <Function id='S236' op='count' aggregateFunc='1'>
            <NumericVal id='S235' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O237' name='COUNT_D'>
        <Expr id='X237'>
          <Function id='S237' op='count_nonull' aggregateFunc='2'>
            <Column id='C226' tableId='T1' colIndex='1'>
              CAT.OUTPUTS.DIM1.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O238' name='SUM_D'>
        <Expr id='X238'>
          <Function id='S238' op='sum' aggregateFunc='3'>
            <Column ref='C226'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O239' name='MIN_D'>
        <Expr id='X239'>
          <Function id='S239' op='min' aggregateFunc='4'>
            <Column ref='C226'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O240' name='MAX_D'>
        <Expr id='X240'>
          <Function id='S240' op='max' aggregateFunc='5'>
            <Column ref='C226'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O248' name='STDDEV_D'>
        <Expr id='X248'>
          <Function id='S248' op='Scalar Stddev'>
            <Function id='S244' op='sum' aggregateFunc='3'>
              <BinaryOper id='S243' op='*'>
                <Function id='S242' op='cast'>
                  <Column ref='C226'>
                  </Column>
                </Function>
                <Function id='S242' op='cast'>
                  <Column ref='C226'>
                  </Column>
                </Function>
              </BinaryOper>
            </Function>
            <Function id='S245' op='sum' aggregateFunc='3'>
              <Function id='S242' op='cast'>
                <Column ref='C226'>
                </Column>
              </Function>
            </Function>
            <Function id='S247' op='cast'>
              <Function id='S246' op='count_nonull' aggregateFunc='2'>
                <Function id='S242' op='cast'>
                  <Column ref='C226'>
                  </Column>
                </Function>
              </Function>
            </Function>
          </Function>
        </Expr>
      </Output>
      <Output id='O257' name='STDDEV_DY'>
        <Expr id='X257'>
          <Function id='S257' op='Scalar Stddev'>
            <Function id='S254' op='sum' aggregateFunc='3'>
              <BinaryOper id='S253' op='*'>
                <Function id='S250' op='cast'>
                  <Column ref='C226'>
                  </Column>
                </Function>
                <BinaryOper id='S252' op='*'>
                  <Function id='S250' op='cast'>
                    <Column ref='C226'>
                    </Column>
                  </Function>
                  <Function id='S251' op='cast'>
                    <Column ref='C210'>
                    </Column>
                  </Function>
                </BinaryOper>
              </BinaryOper>
            </Function>
            <Function id='S255' op='sum' aggregateFunc='3'>
              <BinaryOper id='S252' op='*'>
                <Function id='S250' op='cast'>
                  <Column ref='C226'>
                  </Column>
                </Function>
                <Function id='S251' op='cast'>
                  <Column ref='C210'>
                  </Column>
                </Function>
              </BinaryOper>
            </Function>
            <Function id='S256' op='sum' aggregateFunc='3'>
              <Function id='S251' op='cast'>
                <Column ref='C210'>
                </Column>
              </Function>
            </Function>
          </Function>
        </Expr>
      </Output>
      <Output id='O266' name='STDDEV_DM'>
        <Expr id='X266'>
          <Function id='S266' op='Scalar Stddev'>
            <Function id='S263' op='sum' aggregateFunc='3'>
              <BinaryOper id='S262' op='*'>
                <Function id='S259' op='cast'>
                  <Column ref='C226'>
                  </Column>
                </Function>
                <BinaryOper id='S261' op='*'>
                  <Function id='S259' op='cast'>
                    <Column ref='C226'>
                    </Column>
                  </Function>
                  <Function id='S260' op='cast'>
                    <Column ref='C209'>
                    </Column>
                  </Function>
                </BinaryOper>
              </BinaryOper>
            </Function>
            <Function id='S264' op='sum' aggregateFunc='3'>
              <BinaryOper id='S261' op='*'>
                <Function id='S259' op='cast'>
                  <Column ref='C226'>
                  </Column>
                </Function>
                <Function id='S260' op='cast'>
                  <Column ref='C209'>
                  </Column>
                </Function>
              </BinaryOper>
            </Function>
            <Function id='S265' op='sum' aggregateFunc='3'>
              <Function id='S260' op='cast'>
                <Column ref='C209'>
                </Column>
              </Function>
            </Function>
          </Function>
        </Expr>
      </Output>
      <Output id='O274' name='VARIANCE_D'>
        <Expr id='X274'>
          <Function id='S274' op='Scalar Variance'>
            <Function id='S244' op='sum' aggregateFunc='3'>
              <BinaryOper id='S243' op='*'>
                <Function id='S242' op='cast'>
                  <Column ref='C226'>
                  </Column>
                </Function>
                <Function id='S242' op='cast'>
                  <Column ref='C226'>
                  </Column>
                </Function>
              </BinaryOper>
            </Function>
            <Function id='S245' op='sum' aggregateFunc='3'>
              <Function id='S242' op='cast'>
                <Column ref='C226'>
                </Column>
              </Function>
            </Function>
            <Function id='S273' op='cast'>
              <Function id='S246' op='count_nonull' aggregateFunc='2'>
                <Function id='S242' op='cast'>
                  <Column ref='C226'>
                  </Column>
                </Function>
              </Function>
            </Function>
          </Function>
        </Expr>
      </Output>
      <Output id='O276' name='SUM_DD'>
        <Expr id='X276'>
          <Function id='S276' op='sum' aggregateFunc='3'>
            <BinaryOper id='S275' op='*'>
              <Column ref='C226'>
              </Column>
              <Column ref='C226'>
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O279' name='SYS_SUM1'>
        <Expr id='X279'>
          <Function id='S279' op='sum' aggregateFunc='3'>
            <BinaryOper id='S278' op='*'>
              <Column ref='C226'>
              </Column>
              <BinaryOper id='S277' op='*'>
                <Column ref='C226'>
                </Column>
                <Column ref='C210'>
                </Column>
              </BinaryOper>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O281' name='SYS_SUM2'>
        <Expr id='X281'>
          <Function id='S281' op='sum' aggregateFunc='3'>
            <BinaryOper id='S280' op='*'>
              <Column ref='C226'>
              </Column>
              <Column ref='C210'>
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O282' name='SYS_SUM3'>
        <Expr id='X282'>
          <Function id='S282' op='sum' aggregateFunc='3'>
            <Column ref='C210'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O285' name='SYS_SUM4'>
        <Expr id='X285'>
          <Function id='S285' op='sum' aggregateFunc='3'>
            <BinaryOper id='S284' op='*'>
              <Column ref='C226'>
              </Column>
              <BinaryOper id='S283' op='*'>
                <Column ref='C226'>
                </Column>
                <Column ref='C209'>
                </Column>
              </BinaryOper>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O287' name='SYS_SUM5'>
        <Expr id='X287'>
          <Function id='S287' op='sum' aggregateFunc='3'>
            <BinaryOper id='S286' op='*'>
              <Column ref='C226'>
              </Column>
              <Column ref='C209'>
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O288' name='SYS_SUM6'>
        <Expr id='X288'>
          <Function id='S288' op='sum' aggregateFunc='3'>
            <Column ref='C209'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O291' name='SYS_COUNT7'>
        <Expr id='X291'>
          <Function id='S291' op='count_nonull' aggregateFunc='2'>
            <BinaryOper id='S290' op='*'>
              <Column ref='C226'>
              </Column>
              <BinaryOper id='S289' op='*'>
                <Column ref='C226'>
                </Column>
                <Column ref='C210'>
                </Column>
              </BinaryOper>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O293' name='SYS_COUNT8'>
        <Expr id='X293'>
          <Function id='S293' op='count_nonull' aggregateFunc='2'>
            <BinaryOper id='S292' op='*'>
              <Column ref='C226'>
              </Column>
              <Column ref='C210'>
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O294' name='SYS_COUNT9'>
        <Expr id='X294'>
          <Function id='S294' op='count_nonull' aggregateFunc='2'>
            <Column ref='C210'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O297' name='SYS_COUNT10'>
        <Expr id='X297'>
          <Function id='S297' op='count_nonull' aggregateFunc='2'>
            <BinaryOper id='S296' op='*'>
              <Column ref='C226'>
              </Column>
              <BinaryOper id='S295' op='*'>
                <Column ref='C226'>
                </Column>
                <Column ref='C209'>
                </Column>
              </BinaryOper>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O299' name='SYS_COUNT11'>
        <Expr id='X299'>
          <Function id='S299' op='count_nonull' aggregateFunc='2'>
            <BinaryOper id='S298' op='*'>
              <Column ref='C226'>
              </Column>
              <Column ref='C209'>
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O300' name='SYS_COUNT12'>
        <Expr id='X300'>
          <Function id='S300' op='count_nonull' aggregateFunc='2'>
            <Column ref='C209'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O313' name='OMONTH'>
        <Column ref='C209'>
        </Column>
      </Output>
      <Output id='O316' name='OYEAR'>
        <Column ref='C210'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C209' tableId='T2' colIndex='2'>
          CAT.OUTPUTS.FACT.FMONTH
        </Column>
        <Column id='C210' tableId='T2' colIndex='3'>
          CAT.OUTPUTS.FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>create mv outputs_mav5
+>  refresh on request
+>  initialized on create
+>  as  select count(*)                 countstar,
+>	     count(dimdata)           count_d,
+>	     sum(dimdata)             sum_d, 
+>	     min(dimdata)             min_d,
+>	     max(dimdata)             max_d,
+>	     stddev(dimdata)          stddev_d,
+>	     stddev(dimdata, fyear)   stddev_dy,
+>	     stddev(dimdata, fmonth)  stddev_dm,
+>	     variance(dimdata)        variance_d,
+>	     sum(dimdata*dimdata)     sum_dd,
+>             fyear                    oyear
+>      from fact, dim1
+>      where dimkey=dkey
+>      group by fyear;

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

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ OUTPUTS_MAV5;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.OUTPUTS_MAV5
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873928216791' numCols='3'>
          CAT.OUTPUTS.DIM1
        </Table>
        <Table id='T2' TS='212223873938356572' numCols='8'>
          CAT.OUTPUTS.FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J325'>
          <Column id='C222' tableId='T1' colIndex='0' isNullable='0'>
            CAT.OUTPUTS.DIM1.DKEY
          </Column>
          <Column id='C211' tableId='T2' colIndex='7'>
            CAT.OUTPUTS.FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O233' name='COUNTSTAR'>
        <Expr id='X233'>
          <Function id='S233' op='count' aggregateFunc='1'>
            <NumericVal id='S232' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O234' name='COUNT_D'>
        <Expr id='X234'>
          <Function id='S234' op='count_nonull' aggregateFunc='2'>
            <Column id='C223' tableId='T1' colIndex='1'>
              CAT.OUTPUTS.DIM1.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O235' name='SUM_D'>
        <Expr id='X235'>
          <Function id='S235' op='sum' aggregateFunc='3'>
            <Column ref='C223'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O236' name='MIN_D'>
        <Expr id='X236'>
          <Function id='S236' op='min' aggregateFunc='4'>
            <Column ref='C223'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O237' name='MAX_D'>
        <Expr id='X237'>
          <Function id='S237' op='max' aggregateFunc='5'>
            <Column ref='C223'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O245' name='STDDEV_D'>
        <Expr id='X245'>
          <Function id='S245' op='Scalar Stddev'>
            <Function id='S241' op='sum' aggregateFunc='3'>
              <BinaryOper id='S240' op='*'>
                <Function id='S239' op='cast'>
                  <Column ref='C223'>
                  </Column>
                </Function>
                <Function id='S239' op='cast'>
                  <Column ref='C223'>
                  </Column>
                </Function>
              </BinaryOper>
            </Function>
            <Function id='S242' op='sum' aggregateFunc='3'>
              <Function id='S239' op='cast'>
                <Column ref='C223'>
                </Column>
              </Function>
            </Function>
            <Function id='S244' op='cast'>
              <Function id='S243' op='count_nonull' aggregateFunc='2'>
                <Function id='S239' op='cast'>
                  <Column ref='C223'>
                  </Column>
                </Function>
              </Function>
            </Function>
          </Function>
        </Expr>
      </Output>
      <Output id='O254' name='STDDEV_DY'>
        <Expr id='X254'>
          <Function id='S254' op='Scalar Stddev'>
            <Function id='S251' op='sum' aggregateFunc='3'>
              <BinaryOper id='S250' op='*'>
                <Function id='S247' op='cast'>
                  <Column ref='C223'>
                  </Column>
                </Function>
                <BinaryOper id='S249' op='*'>
                  <Function id='S247' op='cast'>
                    <Column ref='C223'>
                    </Column>
                  </Function>
                  <Function id='S248' op='cast'>
                    <Column ref='C207'>
                    </Column>
                  </Function>
                </BinaryOper>
              </BinaryOper>
            </Function>
            <Function id='S252' op='sum' aggregateFunc='3'>
              <BinaryOper id='S249' op='*'>
                <Function id='S247' op='cast'>
                  <Column ref='C223'>
                  </Column>
                </Function>
                <Function id='S248' op='cast'>
                  <Column ref='C207'>
                  </Column>
                </Function>
              </BinaryOper>
            </Function>
            <Function id='S253' op='sum' aggregateFunc='3'>
              <Function id='S248' op='cast'>
                <Column ref='C207'>
                </Column>
              </Function>
            </Function>
          </Function>
        </Expr>
      </Output>
      <Output id='O263' name='STDDEV_DM'>
        <Expr id='X263'>
          <Function id='S263' op='Scalar Stddev'>
            <Function id='S260' op='sum' aggregateFunc='3'>
              <BinaryOper id='S259' op='*'>
                <Function id='S256' op='cast'>
                  <Column ref='C223'>
                  </Column>
                </Function>
                <BinaryOper id='S258' op='*'>
                  <Function id='S256' op='cast'>
                    <Column ref='C223'>
                    </Column>
                  </Function>
                  <Function id='S257' op='cast'>
                    <Column id='C206' tableId='T2' colIndex='2'>
                      CAT.OUTPUTS.FACT.FMONTH
                    </Column>
                  </Function>
                </BinaryOper>
              </BinaryOper>
            </Function>
            <Function id='S261' op='sum' aggregateFunc='3'>
              <BinaryOper id='S258' op='*'>
                <Function id='S256' op='cast'>
                  <Column ref='C223'>
                  </Column>
                </Function>
                <Function id='S257' op='cast'>
                  <Column ref='C206'>
                  </Column>
                </Function>
              </BinaryOper>
            </Function>
            <Function id='S262' op='sum' aggregateFunc='3'>
              <Function id='S257' op='cast'>
                <Column ref='C206'>
                </Column>
              </Function>
            </Function>
          </Function>
        </Expr>
      </Output>
      <Output id='O271' name='VARIANCE_D'>
        <Expr id='X271'>
          <Function id='S271' op='Scalar Variance'>
            <Function id='S241' op='sum' aggregateFunc='3'>
              <BinaryOper id='S240' op='*'>
                <Function id='S239' op='cast'>
                  <Column ref='C223'>
                  </Column>
                </Function>
                <Function id='S239' op='cast'>
                  <Column ref='C223'>
                  </Column>
                </Function>
              </BinaryOper>
            </Function>
            <Function id='S242' op='sum' aggregateFunc='3'>
              <Function id='S239' op='cast'>
                <Column ref='C223'>
                </Column>
              </Function>
            </Function>
            <Function id='S270' op='cast'>
              <Function id='S243' op='count_nonull' aggregateFunc='2'>
                <Function id='S239' op='cast'>
                  <Column ref='C223'>
                  </Column>
                </Function>
              </Function>
            </Function>
          </Function>
        </Expr>
      </Output>
      <Output id='O273' name='SUM_DD'>
        <Expr id='X273'>
          <Function id='S273' op='sum' aggregateFunc='3'>
            <BinaryOper id='S272' op='*'>
              <Column ref='C223'>
              </Column>
              <Column ref='C223'>
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O276' name='SYS_SUM1'>
        <Expr id='X276'>
          <Function id='S276' op='sum' aggregateFunc='3'>
            <BinaryOper id='S275' op='*'>
              <Column ref='C223'>
              </Column>
              <BinaryOper id='S274' op='*'>
                <Column ref='C223'>
                </Column>
                <Column ref='C207'>
                </Column>
              </BinaryOper>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O278' name='SYS_SUM2'>
        <Expr id='X278'>
          <Function id='S278' op='sum' aggregateFunc='3'>
            <BinaryOper id='S277' op='*'>
              <Column ref='C223'>
              </Column>
              <Column ref='C207'>
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O279' name='SYS_SUM3'>
        <Expr id='X279'>
          <Function id='S279' op='sum' aggregateFunc='3'>
            <Column ref='C207'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O282' name='SYS_SUM4'>
        <Expr id='X282'>
          <Function id='S282' op='sum' aggregateFunc='3'>
            <BinaryOper id='S281' op='*'>
              <Column ref='C223'>
              </Column>
              <BinaryOper id='S280' op='*'>
                <Column ref='C223'>
                </Column>
                <Column ref='C206'>
                </Column>
              </BinaryOper>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O284' name='SYS_SUM5'>
        <Expr id='X284'>
          <Function id='S284' op='sum' aggregateFunc='3'>
            <BinaryOper id='S283' op='*'>
              <Column ref='C223'>
              </Column>
              <Column ref='C206'>
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O285' name='SYS_SUM6'>
        <Expr id='X285'>
          <Function id='S285' op='sum' aggregateFunc='3'>
            <Column ref='C206'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O288' name='SYS_COUNT7'>
        <Expr id='X288'>
          <Function id='S288' op='count_nonull' aggregateFunc='2'>
            <BinaryOper id='S287' op='*'>
              <Column ref='C223'>
              </Column>
              <BinaryOper id='S286' op='*'>
                <Column ref='C223'>
                </Column>
                <Column ref='C207'>
                </Column>
              </BinaryOper>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O290' name='SYS_COUNT8'>
        <Expr id='X290'>
          <Function id='S290' op='count_nonull' aggregateFunc='2'>
            <BinaryOper id='S289' op='*'>
              <Column ref='C223'>
              </Column>
              <Column ref='C207'>
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O291' name='SYS_COUNT9'>
        <Expr id='X291'>
          <Function id='S291' op='count_nonull' aggregateFunc='2'>
            <Column ref='C207'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O294' name='SYS_COUNT10'>
        <Expr id='X294'>
          <Function id='S294' op='count_nonull' aggregateFunc='2'>
            <BinaryOper id='S293' op='*'>
              <Column ref='C223'>
              </Column>
              <BinaryOper id='S292' op='*'>
                <Column ref='C223'>
                </Column>
                <Column ref='C206'>
                </Column>
              </BinaryOper>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O296' name='SYS_COUNT11'>
        <Expr id='X296'>
          <Function id='S296' op='count_nonull' aggregateFunc='2'>
            <BinaryOper id='S295' op='*'>
              <Column ref='C223'>
              </Column>
              <Column ref='C206'>
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O297' name='SYS_COUNT12'>
        <Expr id='X297'>
          <Function id='S297' op='count_nonull' aggregateFunc='2'>
            <Column ref='C206'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O313' name='OYEAR'>
        <Column ref='C207'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C207' tableId='T2' colIndex='3'>
          CAT.OUTPUTS.FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>obey TEST003(match_JQ1);
>>--===========================================
>>--== Simple join query
>>--===========================================
>>-- The ORDER BY clause was added to avoid sorting the resulting XML
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>prepare QueryStmt from
+>  select fprice*fquant total_price, 
+>         dimkey, dimdata, 
+>         fday oday, fmonth omonth, fyear oyear
+>  from fact, dim1
+>  where dimkey=dkey
+>  order by dimkey;

--- SQL command prepared.
>>
>>set pattern $$QueryName$$ OUTPUTS_JQ1;
>>obey TEST003(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='4'>                                             
  </Misc>                                                             
  <JBB id='B0'>                                                       
    <Hub>                                                             
      <JBBCList>                                                      
        <Table id='T2' TS='212223873938356572' numCols='8'>           
          CAT.OUTPUTS.FACT                                            
          <Key>                                                       
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'> 
              CAT.OUTPUTS.FACT.SYSKEY                                 
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </JBBCList>                                                     
    </Hub>                                                            
    <ExtraHub>                                                        
      <TableList>                                                     
        <Table id='T1' TS='212223873928216791' numCols='3'>           
          CAT.OUTPUTS.DIM1                                            
          <Key>                                                       
            <Column id='C19' tableId='T1' colIndex='0' isNullable='0'>
              CAT.OUTPUTS.DIM1.DKEY                                   
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </TableList>                                                    
      <JoinPredList>                                                  
        <JoinPred id='J52'>                                           
          <Column ref='C19'>                                          
          </Column>                                                   
          <Column ref='C8'>                                           
          </Column>                                                   
        </JoinPred>                                                   
      </JoinPredList>                                                 
    </ExtraHub>                                                       
    <OutputList>                                                      
      <Output id='O29'>                                               
        <Expr id='X29'>                                               
          <BinaryOper id='S29' op='*'>                                
            <Column id='C7' tableId='T2' colIndex='6'>                
              CAT.OUTPUTS.FACT.FPRICE                                 
            </Column>                                                 
            <Column id='C6' tableId='T2' colIndex='5'>                
              CAT.OUTPUTS.FACT.FQUANT                                 
            </Column>                                                 
          </BinaryOper>                                               
        </Expr>                                                       
      </Output>                                                       
      <Output id='O34'>                                               
        <Column id='C2' tableId='T2' colIndex='1'>                    
          CAT.OUTPUTS.FACT.FDAY                                       
        </Column>                                                     
      </Output>                                                       
      <Output id='O37'>                                               
        <Column id='C3' tableId='T2' colIndex='2'>                    
          CAT.OUTPUTS.FACT.FMONTH                                     
        </Column>                                                     
      </Output>                                                       
      <Output id='O40'>                                               
        <Column id='C4' tableId='T2' colIndex='3'>                    
          CAT.OUTPUTS.FACT.FYEAR                                      
        </Column>                                                     
      </Output>                                                       
      <Output id='O52'>                                               
        <Column id='C8' tableId='T2' colIndex='7'>                    
          CAT.OUTPUTS.FACT.DIMKEY                                     
        </Column>                                                     
      </Output>                                                       
      <Output id='O58'>                                               
        <Column id='C20' tableId='T1' colIndex='1'>                   
          CAT.OUTPUTS.DIM1.DIMDATA                                    
        </Column>                                                     
      </Output>                                                       
    </OutputList>                                                     
  </JBB>                                                              
</Query>                                                              

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE               >> outputs_j1.in;
>>sh echo PUBLISH OUTPUTS_MJV1.xml >> outputs_j1.in;
>>sh echo PUBLISH OUTPUTS_MJV2.xml >> outputs_j1.in;
>>sh echo PUBLISH OUTPUTS_MAV1.xml >> outputs_j1.in;
>>sh echo PUBLISH OUTPUTS_MAV2.xml >> outputs_j1.in;
>>sh echo PUBLISH OUTPUTS_MAV3.xml >> outputs_j1.in;
>>sh echo MATCH OUTPUTS_JQ1.xml    >> outputs_j1.in;
>>
>>sh sh -c "$QMS outputs_j1.in outputs.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
        <Table ref='T1'>
          CAT.OUTPUTS.DIM1
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV2
          </MVName>
          <OutputList>
            <Output ref='C7' result='Provided'>
              <MVColumn ref='C7'>
                FPRICE
              </MVColumn>
            </Output>
            <Output ref='C6' result='Provided'>
              <MVColumn ref='C6'>
                FQUANT
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <BinaryOper ref='S29' op='*'>
                  <MVColumn ref='C7'>
                    FPRICE
                  </MVColumn>
                  <MVColumn ref='C6'>
                    FQUANT
                  </MVColumn>
                </BinaryOper>
              </Expr>
            </Output>
            <Output ref='O34' result='Provided'>
              <MVColumn ref='C2'>
                ODAY
              </MVColumn>
            </Output>
            <Output ref='O37' result='Provided'>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
            </Output>
            <Output ref='O40' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O52' result='Provided'>
              <MVColumn ref='C8'>
                DIMKEY
              </MVColumn>
            </Output>
            <Output ref='O58' result='Provided'>
              <MVColumn ref='C20'>
                DIMDATA
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV1
          </MVName>
          <OutputList>
            <Output ref='C7' result='Provided'>
              <MVColumn ref='C7'>
                FPRICE
              </MVColumn>
            </Output>
            <Output ref='C6' result='Provided'>
              <MVColumn ref='C6'>
                FQUANT
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <BinaryOper ref='S29' op='*'>
                  <MVColumn ref='C7'>
                    FPRICE
                  </MVColumn>
                  <MVColumn ref='C6'>
                    FQUANT
                  </MVColumn>
                </BinaryOper>
              </Expr>
            </Output>
            <Output ref='O34' result='Provided'>
              <MVColumn ref='C2'>
                ODAY
              </MVColumn>
            </Output>
            <Output ref='O37' result='Provided'>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
            </Output>
            <Output ref='O40' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O52' result='Provided'>
              <MVColumn ref='C8'>
                DIMKEY
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV3 was disqualified because Extra hub table CAT.OUTPUTS.DIM1 is needed but cannot be matched.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.OUTPUTS.OUTPUTS_MJV2';

--- SQL operation complete.
>>prepare QueryStmt from
+>  select fprice*fquant total_price, 
+>         dimkey, dimdata, 
+>         fday oday, fmonth omonth, fyear oyear
+>  from fact, dim1
+>  where dimkey=dkey
+>  order by dimkey;

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

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

FILE_SCAN                       CAT.OUTPUTS.OUTPUTS_MJV2                                    

--- 1 row(s) selected.
>>
>>control query default MVQR_REWRITE_CANDIDATES '';

--- SQL operation complete.
>>
>>obey TEST003(match_JQ2);
>>--===========================================
>>--== 3 cascaded dimension tables
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>-- First challenge - make this query match MJV3.
>>-- To make this query more interesting, add dim2.dimdata to the select list.
>>prepare QueryStmt from
+>  select fprice, fquant, 
+>         dimkey, dim1.dimdata, dim3.dimdata
+>  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$$ OUTPUTS_JQ2;
>>obey TEST003(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='4'>                                             
  </Misc>                                                             
  <JBB id='B0'>                                                       
    <Hub>                                                             
      <JBBCList>                                                      
        <Table id='T4' TS='212223873938356572' numCols='8'>           
          CAT.OUTPUTS.FACT                                            
          <Key>                                                       
            <Column id='C1' tableId='T4' colIndex='0' isNullable='0'> 
              CAT.OUTPUTS.FACT.SYSKEY                                 
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </JBBCList>                                                     
    </Hub>                                                            
    <ExtraHub>                                                        
      <TableList>                                                     
        <Table id='T3' TS='212223873928216791' numCols='3'>           
          CAT.OUTPUTS.DIM1                                            
          <Key>                                                       
            <Column id='C19' tableId='T3' colIndex='0' isNullable='0'>
              CAT.OUTPUTS.DIM1.DKEY                                   
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
        <Table id='T2' TS='212223873928216791' numCols='3'>           
          CAT.OUTPUTS.DIM2                                            
          <Key>                                                       
            <Column id='C28' tableId='T2' colIndex='0' isNullable='0'>
              CAT.OUTPUTS.DIM2.DKEY                                   
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
        <Table id='T1' TS='212223873928216791' numCols='3'>           
          CAT.OUTPUTS.DIM3                                            
          <Key>                                                       
            <Column id='C37' tableId='T1' colIndex='0' isNullable='0'>
              CAT.OUTPUTS.DIM3.DKEY                                   
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </TableList>                                                    
      <JoinPredList>                                                  
        <JoinPred id='J71'>                                           
          <Column ref='C19'>                                          
          </Column>                                                   
          <Column ref='C8'>                                           
          </Column>                                                   
        </JoinPred>                                                   
        <JoinPred id='J80'>                                           
          <Column id='C21' tableId='T3' colIndex='2'>                 
            CAT.OUTPUTS.DIM1.FK1                                      
          </Column>                                                   
          <Column ref='C28'>                                          
          </Column>                                                   
        </JoinPred>                                                   
        <JoinPred id='J89'>                                           
          <Column id='C30' tableId='T2' colIndex='2'>                 
            CAT.OUTPUTS.DIM2.FK1                                      
          </Column>                                                   
          <Column ref='C37'>                                          
          </Column>                                                   
        </JoinPred>                                                   
      </JoinPredList>                                                 
    </ExtraHub>                                                       
    <OutputList>                                                      
      <Output id='O65'>                                               
        <Column id='C6' tableId='T4' colIndex='5'>                    
          CAT.OUTPUTS.FACT.FQUANT                                     
        </Column>                                                     
      </Output>                                                       
      <Output id='O68'>                                               
        <Column id='C7' tableId='T4' colIndex='6'>                    
          CAT.OUTPUTS.FACT.FPRICE                                     
        </Column>                                                     
      </Output>                                                       
      <Output id='O71'>                                               
        <Column id='C8' tableId='T4' colIndex='7'>                    
          CAT.OUTPUTS.FACT.DIMKEY                                     
        </Column>                                                     
      </Output>                                                       
      <Output id='O77'>                                               
        <Column id='C20' tableId='T3' colIndex='1'>                   
          CAT.OUTPUTS.DIM1.DIMDATA                                    
        </Column>                                                     
      </Output>                                                       
      <Output id='O95'>                                               
        <Column id='C38' tableId='T1' colIndex='1'>                   
          CAT.OUTPUTS.DIM3.DIMDATA                                    
        </Column>                                                     
      </Output>                                                       
    </OutputList>                                                     
  </JBB>                                                              
</Query>                                                              

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE               >> outputs_j2.in;
>>sh echo PUBLISH OUTPUTS_MJV1.xml >> outputs_j2.in;
>>sh echo PUBLISH OUTPUTS_MJV2.xml >> outputs_j2.in;
>>sh echo PUBLISH OUTPUTS_MJV3.xml >> outputs_j2.in;
>>sh echo PUBLISH OUTPUTS_MAV1.xml >> outputs_j2.in;
>>sh echo PUBLISH OUTPUTS_MAV2.xml >> outputs_j2.in;
>>sh echo PUBLISH OUTPUTS_MAV3.xml >> outputs_j2.in;
>>sh echo MATCH OUTPUTS_JQ2.xml    >> outputs_j2.in;
>>
>>sh sh -c "$QMS outputs_j2.in outputs.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T4'>
          CAT.OUTPUTS.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV1
          </MVName>
          <OutputList>
            <Output ref='O65' result='Provided'>
              <MVColumn ref='C6'>
                FQUANT
              </MVColumn>
            </Output>
            <Output ref='O68' result='Provided'>
              <MVColumn ref='C7'>
                FPRICE
              </MVColumn>
            </Output>
            <Output ref='O71' result='Provided'>
              <MVColumn ref='C8'>
                DIMKEY
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV2 was disqualified because column CAT.OUTPUTS.DIM1.FK1 cannot be provided.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV3 was disqualified because Extra hub table T1 is needed but cannot be matched.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>prepare QueryStmt from
+>  select fprice, fquant, 
+>         dimkey, dim1.dimdata, dim3.dimdata
+>  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.OUTPUTS.DIM1                                            
FILE_SCAN                       CAT.OUTPUTS.DIM2                                            
FILE_SCAN                       CAT.OUTPUTS.DIM3                                            
FILE_SCAN                       CAT.OUTPUTS.FACT                                            

--- 4 row(s) selected.
>>
>>obey TEST003(match_JQ3);
>>--===========================================
>>--== Dimension table with wrong join pred
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>alter table fact add constraint fact_fk2 foreign key (fitem) references dim1(dkey);

--- SQL operation complete.
>>prepare QueryStmt from
+>  select fprice, fquant, 
+>         dimkey, dim1.dimdata
+>  from fact, dim1
+>  where fitem=dim1.dkey
+>  order by dimkey;

--- SQL command prepared.
>>
>>set pattern $$QueryName$$ OUTPUTS_JQ3;
>>obey TEST003(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='4'>                                             
  </Misc>                                                             
  <JBB id='B0'>                                                       
    <Hub>                                                             
      <JBBCList>                                                      
        <Table id='T2' TS='212223873978946945' numCols='8'>           
          CAT.OUTPUTS.FACT                                            
          <Key>                                                       
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'> 
              CAT.OUTPUTS.FACT.SYSKEY                                 
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </JBBCList>                                                     
    </Hub>                                                            
    <ExtraHub>                                                        
      <TableList>                                                     
        <Table id='T1' TS='212223873978946945' numCols='3'>           
          CAT.OUTPUTS.DIM1                                            
          <Key>                                                       
            <Column id='C21' tableId='T1' colIndex='0' isNullable='0'>
              CAT.OUTPUTS.DIM1.DKEY                                   
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </TableList>                                                    
      <JoinPredList>                                                  
        <JoinPred id='J44'>                                           
          <Column ref='C21'>                                          
          </Column>                                                   
          <Column id='C5' tableId='T2' colIndex='4'>                  
            CAT.OUTPUTS.FACT.FITEM                                    
          </Column>                                                   
        </JoinPred>                                                   
      </JoinPredList>                                                 
    </ExtraHub>                                                       
    <OutputList>                                                      
      <Output id='O47'>                                               
        <Column id='C6' tableId='T2' colIndex='5'>                    
          CAT.OUTPUTS.FACT.FQUANT                                     
        </Column>                                                     
      </Output>                                                       
      <Output id='O50'>                                               
        <Column id='C7' tableId='T2' colIndex='6'>                    
          CAT.OUTPUTS.FACT.FPRICE                                     
        </Column>                                                     
      </Output>                                                       
      <Output id='O53'>                                               
        <Column id='C8' tableId='T2' colIndex='7'>                    
          CAT.OUTPUTS.FACT.DIMKEY                                     
        </Column>                                                     
      </Output>                                                       
      <Output id='O59'>                                               
        <Column id='C22' tableId='T1' colIndex='1'>                   
          CAT.OUTPUTS.DIM1.DIMDATA                                    
        </Column>                                                     
      </Output>                                                       
    </OutputList>                                                     
  </JBB>                                                              
</Query>                                                              

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE               >> outputs_j3.in;
>>sh echo PUBLISH OUTPUTS_MJV1.xml >> outputs_j3.in;
>>sh echo PUBLISH OUTPUTS_MJV2.xml >> outputs_j3.in;
>>sh echo PUBLISH OUTPUTS_MJV3.xml >> outputs_j3.in;
>>--sh echo PUBLISH OUTPUTS_MJV4.xml >> outputs_j3.in;
>>sh echo PUBLISH OUTPUTS_MAV1.xml >> outputs_j3.in;
>>sh echo PUBLISH OUTPUTS_MAV2.xml >> outputs_j3.in;
>>sh echo PUBLISH OUTPUTS_MAV3.xml >> outputs_j3.in;
>>sh echo MATCH OUTPUTS_JQ3.xml    >> outputs_j3.in;
>>
>>-- Should match MJV1 but not MJV3.
>>sh sh -c "$QMS outputs_j3.in outputs.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV1 was disqualified because column CAT.OUTPUTS.FACT.FITEM cannot be provided.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV2 was disqualified because Extra hub table CAT.OUTPUTS.DIM1 is needed but cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV3 was disqualified because Extra hub table CAT.OUTPUTS.DIM1 is needed but cannot be matched.]]></Info>
  </JbbResult>
</Result>
>>
>>obey TEST003(match_JQ4);
>>--===========================================
>>--== Additional join pred to extra-hub table in query.
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>prepare QueryStmt from
+>  select fprice, fquant, 
+>	 qprice,
+>         qdimkey, dimdata
+>  from fact f1, fact2 f2, dim1
+>  where dimkey=dkey
+>    and fitem=qitem
+>    and qdimkey=dkey
+>  order by dimkey;

--- SQL command prepared.
>>
>>set pattern $$QueryName$$ OUTPUTS_JQ4;
>>obey TEST003(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='4'>                                             
  </Misc>                                                             
  <JBB id='B0'>                                                       
    <Hub>                                                             
      <JBBCList>                                                      
        <Table id='T3' TS='212223873978946945' numCols='8'>           
          CAT.OUTPUTS.FACT                                            
          <Key>                                                       
            <Column id='C1' tableId='T3' colIndex='0' isNullable='0'> 
              CAT.OUTPUTS.FACT.SYSKEY                                 
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
        <Table id='T2' TS='212223873938356572' numCols='5'>           
          CAT.OUTPUTS.FACT2                                           
          <Key>                                                       
            <Column id='C21' tableId='T2' colIndex='0' isNullable='0'>
              CAT.OUTPUTS.FACT2.SYSKEY                                
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </JBBCList>                                                     
      <JoinPredList>                                                  
        <JoinPred id='J60'>                                           
          <Column id='C5' tableId='T3' colIndex='4'>                  
            CAT.OUTPUTS.FACT.FITEM                                    
          </Column>                                                   
          <Column id='C22' tableId='T2' colIndex='1'>                 
            CAT.OUTPUTS.FACT2.QITEM                                   
          </Column>                                                   
        </JoinPred>                                                   
        <JoinPred id='J84'>                                           
          <Column ref='C8'>                                           
          </Column>                                                   
          <Column id='C25' tableId='T2' colIndex='4'>                 
            CAT.OUTPUTS.FACT2.QDIMKEY                                 
          </Column>                                                   
        </JoinPred>                                                   
      </JoinPredList>                                                 
    </Hub>                                                            
    <ExtraHub>                                                        
      <TableList>                                                     
        <Table id='T1' TS='212223873978946945' numCols='3'>           
          CAT.OUTPUTS.DIM1                                            
          <Key>                                                       
            <Column id='C33' tableId='T1' colIndex='0' isNullable='0'>
              CAT.OUTPUTS.DIM1.DKEY                                   
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </TableList>                                                    
      <JoinPredList>                                                  
        <JoinPred id='J84'>                                           
          <Column ref='C33'>                                          
          </Column>                                                   
          <JoinPred ref='J84'>                                        
          </JoinPred>                                                 
        </JoinPred>                                                   
      </JoinPredList>                                                 
    </ExtraHub>                                                       
    <OutputList>                                                      
      <Output id='O63'>                                               
        <Column id='C6' tableId='T3' colIndex='5'>                    
          CAT.OUTPUTS.FACT.FQUANT                                     
        </Column>                                                     
      </Output>                                                       
      <Output id='O66'>                                               
        <Column id='C7' tableId='T3' colIndex='6'>                    
          CAT.OUTPUTS.FACT.FPRICE                                     
        </Column>                                                     
      </Output>                                                       
      <Output id='O81'>                                               
        <Column id='C24' tableId='T2' colIndex='3'>                   
          CAT.OUTPUTS.FACT2.QPRICE                                    
        </Column>                                                     
      </Output>                                                       
      <Output id='O84'>                                               
        <Column id='C8' tableId='T3' colIndex='7'>                    
          CAT.OUTPUTS.FACT.DIMKEY                                     
        </Column>                                                     
      </Output>                                                       
      <Output id='O90'>                                               
        <Column id='C34' tableId='T1' colIndex='1'>                   
          CAT.OUTPUTS.DIM1.DIMDATA                                    
        </Column>                                                     
      </Output>                                                       
    </OutputList>                                                     
  </JBB>                                                              
</Query>                                                              

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE               >> outputs_j4.in;
>>sh echo PUBLISH OUTPUTS_MJV1.xml >> outputs_j4.in;
>>sh echo PUBLISH OUTPUTS_MJV2.xml >> outputs_j4.in;
>>sh echo PUBLISH OUTPUTS_MJV3.xml >> outputs_j4.in;
>>sh echo PUBLISH OUTPUTS_MJV4.xml >> outputs_j4.in;
>>sh echo PUBLISH OUTPUTS_MAV1.xml >> outputs_j4.in;
>>sh echo PUBLISH OUTPUTS_MAV2.xml >> outputs_j4.in;
>>sh echo PUBLISH OUTPUTS_MAV3.xml >> outputs_j4.in;
>>sh echo MATCH OUTPUTS_JQ4.xml    >> outputs_j4.in;
>>
>>-- Should match MJV4, with additional join pred.
>>sh sh -c "$QMS outputs_j4.in outputs.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T3'>
          CAT.OUTPUTS.FACT
        </Table>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT2
        </Table>
        <Table ref='T1'>
          CAT.OUTPUTS.DIM1
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV4
          </MVName>
          <OutputList>
            <Output ref='O63' result='Provided'>
              <MVColumn ref='C6'>
                FQUANT
              </MVColumn>
            </Output>
            <Output ref='O66' result='Provided'>
              <MVColumn ref='C7'>
                FPRICE
              </MVColumn>
            </Output>
            <Output ref='O81' result='Provided'>
              <MVColumn ref='C24'>
                QPRICE
              </MVColumn>
            </Output>
            <Output ref='O84' result='Provided'>
              <MVColumn ref='C8'>
                QDIMKEY
              </MVColumn>
            </Output>
            <Output ref='O90' result='Provided'>
              <MVColumn ref='C34'>
                DIMDATA
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV1 was disqualified because column CAT.OUTPUTS.FACT.FITEM cannot be provided.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV2 was disqualified because column CAT.OUTPUTS.FACT.FITEM cannot be provided.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV3 was disqualified because column CAT.OUTPUTS.FACT.FITEM cannot be provided.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>prepare QueryStmt from
+>  select fprice, fquant, 
+>	 qprice,
+>         qdimkey, dimdata
+>  from fact f1, fact2 f2, dim1
+>  where dimkey=dkey
+>    and fitem=qitem
+>    and qdimkey=dkey
+>  order by dimkey;

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

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

FILE_SCAN                       CAT.OUTPUTS.OUTPUTS_MJV4                                    

--- 1 row(s) selected.
>>
>>obey TEST003(match_JQ5);
>>--===========================================
>>--== Query uses CASE expression
>>--===========================================
>>-- The ORDER BY clause was added to avoid sorting the resulting XML
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>prepare QueryStmt from
+>  select (month_name || ', ' || year_name) date_name,
+>          dimkey, dimdata
+>  from (select (case fmonth when 1 then 'Jan'
+>	                    when 2 then 'Feb'
+>	                    when 3 then 'Mar'
+>	                    else        'else'
+>	        end) month_name,
+>	       (case fyear when 2000 then '2000'
+>	                   when 2001 then '2001'
+>	                   when 2002 then '2002'
+>	                   else           'else'
+>	        end) year_name, 
+>         dimkey, dimdata, 
+>         fday oday, fmonth omonth, fyear oyear
+>  from fact, dim1
+>  where dimkey=dkey) x  
+>  order by dimkey;

--- SQL command prepared.
>>
>>set pattern $$QueryName$$ OUTPUTS_JQ5;
>>obey TEST003(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='4'>                                               
  </Misc>                                                               
  <JBB id='B0'>                                                         
    <Hub>                                                               
      <JBBCList>                                                        
        <Table id='T2' TS='212223873978946945' numCols='8'>             
          CAT.OUTPUTS.FACT                                              
          <Key>                                                         
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>   
              CAT.OUTPUTS.FACT.SYSKEY                                   
            </Column>                                                   
          </Key>                                                        
        </Table>                                                        
      </JBBCList>                                                       
    </Hub>                                                              
    <ExtraHub>                                                          
      <TableList>                                                       
        <Table id='T1' TS='212223873978946945' numCols='3'>             
          CAT.OUTPUTS.DIM1                                              
          <Key>                                                         
            <Column id='C21' tableId='T1' colIndex='0' isNullable='0'>  
              CAT.OUTPUTS.DIM1.DKEY                                     
            </Column>                                                   
          </Key>                                                        
        </Table>                                                        
      </TableList>                                                      
      <JoinPredList>                                                    
        <JoinPred id='J83'>                                             
          <Column ref='C21'>                                            
          </Column>                                                     
          <Column ref='C8'>                                             
          </Column>                                                     
        </JoinPred>                                                     
      </JoinPredList>                                                   
    </ExtraHub>                                                         
    <OutputList>                                                        
      <Output id='O60'>                                                 
        <Expr id='X60'>                                                 
          <Function id='S60' op='||'>                                   
            <Function id='S59' op='||'>                                 
              <Function id='S44' op='case'>                             
                <Function id='S43' op='if_then_else'>                   
                  <BinaryOper id='S32' op='='>                          
                    <Column id='C3' tableId='T2' colIndex='2'>          
                      CAT.OUTPUTS.FACT.FMONTH                           
                    </Column>                                           
                    <NumericVal id='S31' scale='0'>                     
                      1                                                 
                    </NumericVal>                                       
                  </BinaryOper>                                         
                  <StringVal id='S33'><![CDATA['Jan']]></StringVal>     
                  <Function id='S42' op='if_then_else'>                 
                    <BinaryOper id='S35' op='='>                        
                      <Column ref='C3'>                                 
                      </Column>                                         
                      <NumericVal id='S34' scale='0'>                   
                        2                                               
                      </NumericVal>                                     
                    </BinaryOper>                                       
                    <StringVal id='S36'><![CDATA['Feb']]></StringVal>   
                    <Function id='S41' op='if_then_else'>               
                      <BinaryOper id='S38' op='='>                      
                        <Column ref='C3'>                               
                        </Column>                                       
                        <NumericVal id='S37' scale='0'>                 
                          3                                             
                        </NumericVal>                                   
                      </BinaryOper>                                     
                      <StringVal id='S39'><![CDATA['Mar']]></StringVal> 
                      <StringVal id='S40'><![CDATA['else']]></StringVal>
                    </Function>                                         
                  </Function>                                           
                </Function>                                             
              </Function>                                               
              <StringVal id='S58'><![CDATA[', ']]></StringVal>          
            </Function>                                                 
            <Function id='S57' op='case'>                               
              <Function id='S56' op='if_then_else'>                     
                <BinaryOper id='S46' op='='>                            
                  <Column id='C4' tableId='T2' colIndex='3'>            
                    CAT.OUTPUTS.FACT.FYEAR                              
                  </Column>                                             
                  <NumericVal id='S45' scale='0'>                       
                    2000                                                
                  </NumericVal>                                         
                </BinaryOper>                                           
                <StringVal id='S47'><![CDATA['2000']]></StringVal>      
                <Function id='S55' op='if_then_else'>                   
                  <BinaryOper id='S49' op='='>                          
                    <Column ref='C4'>                                   
                    </Column>                                           
                    <NumericVal id='S48' scale='0'>                     
                      2001                                              
                    </NumericVal>                                       
                  </BinaryOper>                                         
                  <StringVal id='S50'><![CDATA['2001']]></StringVal>    
                  <Function id='S54' op='if_then_else'>                 
                    <BinaryOper id='S52' op='='>                        
                      <Column ref='C4'>                                 
                      </Column>                                         
                      <NumericVal id='S51' scale='0'>                   
                        2002                                            
                      </NumericVal>                                     
                    </BinaryOper>                                       
                    <StringVal id='S53'><![CDATA['2002']]></StringVal>  
                    <StringVal id='S40'><![CDATA['else']]></StringVal>  
                  </Function>                                           
                </Function>                                             
              </Function>                                               
            </Function>                                                 
          </Function>                                                   
        </Expr>                                                         
      </Output>                                                         
      <Output id='O83'>                                                 
        <Column id='C8' tableId='T2' colIndex='7'>                      
          CAT.OUTPUTS.FACT.DIMKEY                                       
        </Column>                                                       
      </Output>                                                         
      <Output id='O89'>                                                 
        <Column id='C22' tableId='T1' colIndex='1'>                     
          CAT.OUTPUTS.DIM1.DIMDATA                                      
        </Column>                                                       
      </Output>                                                         
    </OutputList>                                                       
  </JBB>                                                                
</Query>                                                                

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE               >> outputs_j5.in;
>>sh echo PUBLISH OUTPUTS_MJV1.xml >> outputs_j5.in;
>>sh echo PUBLISH OUTPUTS_MJV2.xml >> outputs_j5.in;
>>sh echo PUBLISH OUTPUTS_MAV1.xml >> outputs_j5.in;
>>sh echo PUBLISH OUTPUTS_MAV2.xml >> outputs_j5.in;
>>sh echo PUBLISH OUTPUTS_MAV3.xml >> outputs_j5.in;
>>sh echo MATCH OUTPUTS_JQ1.xml    >> outputs_j5.in;
>>
>>sh sh -c "$QMS outputs_j5.in outputs.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
        <Table ref='T1'>
          CAT.OUTPUTS.DIM1
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV2
          </MVName>
          <OutputList>
            <Output ref='C7' result='Provided'>
              <MVColumn ref='C7'>
                FPRICE
              </MVColumn>
            </Output>
            <Output ref='C6' result='Provided'>
              <MVColumn ref='C6'>
                FQUANT
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <BinaryOper ref='S29' op='*'>
                  <MVColumn ref='C7'>
                    FPRICE
                  </MVColumn>
                  <MVColumn ref='C6'>
                    FQUANT
                  </MVColumn>
                </BinaryOper>
              </Expr>
            </Output>
            <Output ref='O34' result='Provided'>
              <MVColumn ref='C2'>
                ODAY
              </MVColumn>
            </Output>
            <Output ref='O37' result='Provided'>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
            </Output>
            <Output ref='O40' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O52' result='Provided'>
              <MVColumn ref='C8'>
                DIMKEY
              </MVColumn>
            </Output>
            <Output ref='O58' result='Provided'>
              <MVColumn ref='C20'>
                DIMDATA
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV1
          </MVName>
          <OutputList>
            <Output ref='C7' result='Provided'>
              <MVColumn ref='C7'>
                FPRICE
              </MVColumn>
            </Output>
            <Output ref='C6' result='Provided'>
              <MVColumn ref='C6'>
                FQUANT
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <BinaryOper ref='S29' op='*'>
                  <MVColumn ref='C7'>
                    FPRICE
                  </MVColumn>
                  <MVColumn ref='C6'>
                    FQUANT
                  </MVColumn>
                </BinaryOper>
              </Expr>
            </Output>
            <Output ref='O34' result='Provided'>
              <MVColumn ref='C2'>
                ODAY
              </MVColumn>
            </Output>
            <Output ref='O37' result='Provided'>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
            </Output>
            <Output ref='O40' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O52' result='Provided'>
              <MVColumn ref='C8'>
                DIMKEY
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV3 was disqualified because Extra hub table CAT.OUTPUTS.DIM1 is needed but cannot be matched.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.OUTPUTS.OUTPUTS_MJV1';

--- SQL operation complete.
>>prepare QueryStmt from
+>  select (month_name || ', ' || year_name) date_name,
+>          dimkey, dimdata
+>  from (select (case fmonth when 1 then 'Jan'
+>	                    when 2 then 'Feb'
+>	                    when 3 then 'Mar'
+>	                    else        'else'
+>	        end) month_name,
+>	       (case fyear when 2000 then '2000'
+>	                   when 2001 then '2001'
+>	                   when 2002 then '2002'
+>	                   else           'else'
+>	        end) year_name, 
+>         dimkey, dimdata, 
+>         fday oday, fmonth omonth, fyear oyear
+>  from fact, dim1
+>  where dimkey=dkey) x  
+>  order by dimkey;

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

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

FILE_SCAN                       CAT.OUTPUTS.DIM1                                            
FILE_SCAN                       CAT.OUTPUTS.OUTPUTS_MJV1                                    

--- 2 row(s) selected.
>>
>>control query default MVQR_REWRITE_CANDIDATES '';

--- SQL operation complete.
>>
>>obey TEST003(match_JQ6);
>>--===========================================
>>--== Date/Time functions.
>>--===========================================
>>create table OUTPUTS_T61 (
+>  a int not null
+>, c1 date
+>, c2 time
+>, c3 timestamp
+>, primary key(a));

--- SQL operation complete.
>>
>>create table OUTPUTS_T62 like OUTPUTS_T61;

--- SQL operation complete.
>>
>>insert into OUTPUTS_T61 values
+>  (1, date'2010-11-21',time'11:22:33', timestamp'2010-11-21 11:22:33');

--- 1 row(s) inserted.
>>insert into OUTPUTS_T62 values
+>  (1, date'2010-11-21',time'11:22:33', timestamp'2010-11-21 11:22:33');

--- 1 row(s) inserted.
>>
>>-- This MJV uses hour() and month() functions.
>>create mv OUTPUTS_MJV61
+>  --REFRESH on request
+>  RECOMPUTE
+>  INITIALIZE on create
+>  ENABLE QUERY REWRITE 
+>  as  select t1.c1
+>            ,hour(t2.c2)  c2_hour
+>            ,month(t1.c3) c3_month
+>      from OUTPUTS_T61 t1, OUTPUTS_T62 t2
+>      where t1.c1=t2.c1;

--- SQL operation complete.
>>
>>set pattern $$MVName$$ OUTPUTS_MJV61;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.OUTPUTS_MJV61
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212223873986633805' numCols='4'>
          CAT.OUTPUTS.OUTPUTS_T61
        </Table>
        <Table id='T1' TS='212223873986805666' numCols='4'>
          CAT.OUTPUTS.OUTPUTS_T62
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J96'>
          <Column ref='C72'>
          </Column>
          <Column id='C81' tableId='T1' colIndex='1'>
            CAT.OUTPUTS.OUTPUTS_T62.C1
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O90' name='C2_HOUR'>
        <Expr id='X90'>
          <Function id='S90' op='extract_odbc'>
            <Column id='C82' tableId='T1' colIndex='2'>
              CAT.OUTPUTS.OUTPUTS_T62.C2
            </Column>
            <Parameter name='extractField' value='4'/>
          </Function>
        </Expr>
      </Output>
      <Output id='O91' name='C3_MONTH'>
        <Expr id='X91'>
          <Function id='S91' op='extract_odbc'>
            <Column id='C74' tableId='T2' colIndex='3'>
              CAT.OUTPUTS.OUTPUTS_T61.C3
            </Column>
            <Parameter name='extractField' value='2'/>
          </Function>
        </Expr>
      </Output>
      <Output id='O96' name='C1'>
        <Column id='C72' tableId='T2' colIndex='1'>
          CAT.OUTPUTS.OUTPUTS_T61.C1
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>-- This MJV uses minute() and week() functions.
>>create mv OUTPUTS_MJV62
+>  --REFRESH on request  
+>  RECOMPUTE
+>  INITIALIZE on create
+>  ENABLE QUERY REWRITE 
+>  as  select t1.c1
+>            ,minute(t2.c2)     c2_minute
+>            ,dayofmonth(t1.c3) c3_dayofmonth
+>      from OUTPUTS_T61 t1, OUTPUTS_T62 t2
+>      where t1.c1=t2.c1;

--- SQL operation complete.
>>
>>set pattern $$MVName$$ OUTPUTS_MJV62;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.OUTPUTS_MJV62
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212223873986633805' numCols='4'>
          CAT.OUTPUTS.OUTPUTS_T61
        </Table>
        <Table id='T1' TS='212223873986805666' numCols='4'>
          CAT.OUTPUTS.OUTPUTS_T62
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J96'>
          <Column ref='C72'>
          </Column>
          <Column id='C81' tableId='T1' colIndex='1'>
            CAT.OUTPUTS.OUTPUTS_T62.C1
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O90' name='C2_MINUTE'>
        <Expr id='X90'>
          <Function id='S90' op='extract_odbc'>
            <Column id='C82' tableId='T1' colIndex='2'>
              CAT.OUTPUTS.OUTPUTS_T62.C2
            </Column>
            <Parameter name='extractField' value='5'/>
          </Function>
        </Expr>
      </Output>
      <Output id='O91' name='C3_DAYOFMONTH'>
        <Expr id='X91'>
          <Function id='S91' op='extract_odbc'>
            <Column id='C74' tableId='T2' colIndex='3'>
              CAT.OUTPUTS.OUTPUTS_T61.C3
            </Column>
            <Parameter name='extractField' value='3'/>
          </Function>
        </Expr>
      </Output>
      <Output id='O96' name='C1'>
        <Column id='C72' tableId='T2' colIndex='1'>
          CAT.OUTPUTS.OUTPUTS_T61.C1
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>-- The query needs hour() and month()
>>prepare QueryStmt from
+>  select hour(t2.c2)  c2_hour
+>        ,month(t1.c3) c3_month
+>  from OUTPUTS_T61 t1, OUTPUTS_T62 t2
+>  where t1.c1=t2.c1
+>  order by t1.c1;

--- SQL command prepared.
>>
>>set pattern $$QueryName$$ OUTPUTS_JQ6;
>>obey TEST003(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='4'>                                             
  </Misc>                                                             
  <JBB id='B0'>                                                       
    <Hub>                                                             
      <JBBCList>                                                      
        <Table id='T2' TS='212223873986633805' numCols='4'>           
          CAT.OUTPUTS.OUTPUTS_T61                                     
          <Key>                                                       
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'> 
              CAT.OUTPUTS.OUTPUTS_T61.A                               
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
        <Table id='T1' TS='212223873986805666' numCols='4'>           
          CAT.OUTPUTS.OUTPUTS_T62                                     
          <Key>                                                       
            <Column id='C10' tableId='T1' colIndex='0' isNullable='0'>
              CAT.OUTPUTS.OUTPUTS_T62.A                               
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </JBBCList>                                                     
      <JoinPredList>                                                  
        <JoinPred id='J26'>                                           
          <Column ref='C2'>                                           
          </Column>                                                   
          <Column id='C11' tableId='T1' colIndex='1'>                 
            CAT.OUTPUTS.OUTPUTS_T62.C1                                
          </Column>                                                   
        </JoinPred>                                                   
      </JoinPredList>                                                 
    </Hub>                                                            
    <ExtraHub>                                                        
    </ExtraHub>                                                       
    <OutputList>                                                      
      <Output id='O20'>                                               
        <Expr id='X20'>                                               
          <Function id='S20' op='extract_odbc'>                       
            <Column id='C12' tableId='T1' colIndex='2'>               
              CAT.OUTPUTS.OUTPUTS_T62.C2                              
            </Column>                                                 
            <Parameter name='extractField' value='4'/>                
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O21'>                                               
        <Expr id='X21'>                                               
          <Function id='S21' op='extract_odbc'>                       
            <Column id='C4' tableId='T2' colIndex='3'>                
              CAT.OUTPUTS.OUTPUTS_T61.C3                              
            </Column>                                                 
            <Parameter name='extractField' value='2'/>                
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O26'>                                               
        <Column id='C2' tableId='T2' colIndex='1'>                    
          CAT.OUTPUTS.OUTPUTS_T61.C1                                  
        </Column>                                                     
      </Output>                                                       
    </OutputList>                                                     
  </JBB>                                                              
</Query>                                                              

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE                >> outputs_j6.in;
>>sh echo PUBLISH OUTPUTS_MJV61.xml >> outputs_j6.in;
>>sh echo PUBLISH OUTPUTS_MJV62.xml >> outputs_j6.in;
>>sh echo MATCH OUTPUTS_JQ6.xml     >> outputs_j6.in;
>>
>>sh sh -c "$QMS outputs_j6.in outputs.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T2'>
          CAT.OUTPUTS.OUTPUTS_T61
        </Table>
        <Table ref='T1'>
          CAT.OUTPUTS.OUTPUTS_T62
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV61
          </MVName>
          <OutputList>
            <Output ref='O20' result='Provided'>
              <MVColumn ref='X20'>
                C2_HOUR
              </MVColumn>
            </Output>
            <Output ref='O21' result='Provided'>
              <MVColumn ref='X21'>
                C3_MONTH
              </MVColumn>
            </Output>
            <Output ref='O26' result='Provided'>
              <MVColumn ref='C2'>
                C1
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV62 was disqualified because Output expression O21 cannot be Provided.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>cqd mvqr_rewrite_candidates 'CAT.OUTPUTS.OUTPUTS_MJV61';

--- SQL operation complete.
>>
>>prepare QueryStmt from
+>  select hour(t2.c2)  c2_hour
+>        ,month(t1.c3) c3_month
+>  from OUTPUTS_T61 t1, OUTPUTS_T62 t2
+>  where t1.c1=t2.c1
+>  order by t1.c1;

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

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

FILE_SCAN                       CAT.OUTPUTS.OUTPUTS_MJV61                                   

--- 1 row(s) selected.
>>-- Check the results
>>execute QueryStmt;

C2_HOUR  C3_MONTH
-------  --------

     11        11

--- 1 row(s) selected.
>>
>>-- And compare to a plan that is not rewritten
>>cqd mvqr_rewrite_level '0';

--- SQL operation complete.
>>prepare QueryStmt from
+>  select hour(t2.c2)  c2_hour
+>        ,month(t1.c3) c3_month
+>  from OUTPUTS_T61 t1, OUTPUTS_T62 t2
+>  where t1.c1=t2.c1
+>  order by t1.c1;

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

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

FILE_SCAN                       T1 (CAT.OUTPUTS.OUTPUTS_T61)                                
FILE_SCAN                       T2 (CAT.OUTPUTS.OUTPUTS_T62)                                

--- 2 row(s) selected.
>>-- Check the results
>>execute QueryStmt;

C2_HOUR  C3_MONTH
-------  --------

     11        11

--- 1 row(s) selected.
>>
>>cqd MVQR_REWRITE_LEVEL '4';

--- SQL operation complete.
>>
>>obey TEST003(match_AQ1);
>>--===========================================
>>--== Simple aggregate query
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>prepare QueryStmt from
+>  select sum(fprice*fquant) total_price, 
+>         sum(dimdata) sumdata, 
+>         fyear oyear	           
+>  from fact, dim1
+>  where dimkey=dkey
+>  group by fyear
+>  order by oyear;

--- SQL command prepared.
>>
>>set pattern $$QueryName$$ OUTPUTS_AQ1;
>>obey TEST003(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='4'>                                             
  </Misc>                                                             
  <JBB id='B0'>                                                       
    <Hub>                                                             
      <JBBCList>                                                      
        <Table id='T1' TS='212223873978946945' numCols='3'>           
          CAT.OUTPUTS.DIM1                                            
          <Key>                                                       
            <Column id='C21' tableId='T1' colIndex='0' isNullable='0'>
              CAT.OUTPUTS.DIM1.DKEY                                   
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
        <Table id='T2' TS='212223873978946945' numCols='8'>           
          CAT.OUTPUTS.FACT                                            
          <Key>                                                       
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'> 
              CAT.OUTPUTS.FACT.SYSKEY                                 
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </JBBCList>                                                     
      <JoinPredList>                                                  
        <JoinPred id='J56'>                                           
          <Column ref='C21'>                                          
          </Column>                                                   
          <Column id='C8' tableId='T2' colIndex='7'>                  
            CAT.OUTPUTS.FACT.DIMKEY                                   
          </Column>                                                   
        </JoinPred>                                                   
      </JoinPredList>                                                 
    </Hub>                                                            
    <ExtraHub>                                                        
    </ExtraHub>                                                       
    <OutputList>                                                      
      <Output id='O32'>                                               
        <Expr id='X32'>                                               
          <Function id='S32' op='sum' aggregateFunc='3'>              
            <BinaryOper id='S31' op='*'>                              
              <Column id='C7' tableId='T2' colIndex='6'>              
                CAT.OUTPUTS.FACT.FPRICE                               
              </Column>                                               
              <Column id='C6' tableId='T2' colIndex='5'>              
                CAT.OUTPUTS.FACT.FQUANT                               
              </Column>                                               
            </BinaryOper>                                             
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O33'>                                               
        <Expr id='X33'>                                               
          <Function id='S33' op='sum' aggregateFunc='3'>              
            <Column id='C22' tableId='T1' colIndex='1'>               
              CAT.OUTPUTS.DIM1.DIMDATA                                
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O44'>                                               
        <Column ref='C4'>                                             
        </Column>                                                     
      </Output>                                                       
    </OutputList>                                                     
    <GroupBy id='G3'>                                                 
      <Primary>                                                       
        <Column id='C4' tableId='T2' colIndex='3'>                    
          CAT.OUTPUTS.FACT.FYEAR                                      
        </Column>                                                     
      </Primary>                                                      
    </GroupBy>                                                        
  </JBB>                                                              
</Query>                                                              

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE               >> outputs_a1.in;
>>sh echo PUBLISH OUTPUTS_MJV1.xml >> outputs_a1.in;
>>sh echo PUBLISH OUTPUTS_MJV2.xml >> outputs_a1.in;
>>sh echo PUBLISH OUTPUTS_MAV1.xml >> outputs_a1.in;
>>sh echo PUBLISH OUTPUTS_MAV2.xml >> outputs_a1.in;
>>sh echo PUBLISH OUTPUTS_MAV3.xml >> outputs_a1.in;
>>sh echo PUBLISH OUTPUTS_MAV4.xml >> outputs_a1.in;
>>sh echo PUBLISH OUTPUTS_MAV5.xml >> outputs_a1.in;
>>sh echo MATCH OUTPUTS_AQ1.xml    >> outputs_a1.in;
>>
>>sh sh -c "$QMS outputs_a1.in outputs.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.OUTPUTS.DIM1
        </Table>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV2
          </MVName>
          <OutputList>
            <Output ref='C7' result='Provided'>
              <MVColumn ref='C7'>
                FPRICE
              </MVColumn>
            </Output>
            <Output ref='C6' result='Provided'>
              <MVColumn ref='C6'>
                FQUANT
              </MVColumn>
            </Output>
            <Output ref='O44' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='C22' result='Provided'>
              <MVColumn ref='C22'>
                DIMDATA
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV1
          </MVName>
          <OutputList>
            <Output ref='C7' result='Provided'>
              <MVColumn ref='C7'>
                FPRICE
              </MVColumn>
            </Output>
            <Output ref='C6' result='Provided'>
              <MVColumn ref='C6'>
                FQUANT
              </MVColumn>
            </Output>
            <Output ref='O44' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='C8' result='Provided'>
              <MVColumn ref='C8'>
                DIMKEY
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.OUTPUTS.DIM1
        </Table>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MAV1
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O44' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O33' result='NotProvided'>
              <Expr ref='X33'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S33'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O32' result='NotProvided'>
              <Expr ref='X32'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S32'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MAV2
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O44' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O33' result='NotProvided'>
              <Expr ref='X33'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S33'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O32' result='NotProvided'>
              <Expr ref='X32'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S32'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MAV3
          </MVName>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O32' result='Provided'>
              <MVColumn ref='X32'>
                TOTAL_PRICE
              </MVColumn>
            </Output>
            <Output ref='O33' result='Provided'>
              <MVColumn ref='X33'>
                SUMDATA
              </MVColumn>
            </Output>
            <Output ref='O44' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MAV4 was disqualified because aggregate function S32 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MAV5 was disqualified because aggregate function S32 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV3 was disqualified because Extra hub table CAT.OUTPUTS.DIM1 is needed but cannot be matched.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>prepare QueryStmt from
+>  select sum(fprice*fquant) total_price, 
+>         sum(dimdata) sumdata, 
+>         fyear oyear	           
+>  from fact, dim1
+>  where dimkey=dkey
+>  group by fyear
+>  order by oyear;

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

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

FILE_SCAN                       CAT.OUTPUTS.OUTPUTS_MJV2                                    

--- 1 row(s) selected.
>>
>>obey TEST003(match_AQ2);
>>--===========================================
>>--== Check simple aggregate functions over MJV1, MJV2, MAV4
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>prepare QueryStmt from
+>  select count(*),
+>	 count(dimdata),
+>	 sum(dimdata), 
+>	 min(dimdata),
+>	 max(dimdata),
+>         fyear oyear	           
+>  from fact, dim1
+>  where dimkey=dkey
+>  group by fyear
+>  order by oyear;

--- SQL command prepared.
>>
>>set pattern $$QueryName$$ OUTPUTS_AQ2;
>>obey TEST003(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='4'>                                             
  </Misc>                                                             
  <JBB id='B0'>                                                       
    <Hub>                                                             
      <JBBCList>                                                      
        <Table id='T1' TS='212223873978946945' numCols='3'>           
          CAT.OUTPUTS.DIM1                                            
          <Key>                                                       
            <Column id='C21' tableId='T1' colIndex='0' isNullable='0'>
              CAT.OUTPUTS.DIM1.DKEY                                   
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
        <Table id='T2' TS='212223873978946945' numCols='8'>           
          CAT.OUTPUTS.FACT                                            
          <Key>                                                       
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'> 
              CAT.OUTPUTS.FACT.SYSKEY                                 
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </JBBCList>                                                     
      <JoinPredList>                                                  
        <JoinPred id='J59'>                                           
          <Column ref='C21'>                                          
          </Column>                                                   
          <Column id='C8' tableId='T2' colIndex='7'>                  
            CAT.OUTPUTS.FACT.DIMKEY                                   
          </Column>                                                   
        </JoinPred>                                                   
      </JoinPredList>                                                 
    </Hub>                                                            
    <ExtraHub>                                                        
    </ExtraHub>                                                       
    <OutputList>                                                      
      <Output id='O32'>                                               
        <Expr id='X32'>                                               
          <Function id='S32' op='count' aggregateFunc='1'>            
            <NumericVal id='S31' scale='0'>                           
              1                                                       
            </NumericVal>                                             
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O33'>                                               
        <Expr id='X33'>                                               
          <Function id='S33' op='count_nonull' aggregateFunc='2'>     
            <Column id='C22' tableId='T1' colIndex='1'>               
              CAT.OUTPUTS.DIM1.DIMDATA                                
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O34'>                                               
        <Expr id='X34'>                                               
          <Function id='S34' op='sum' aggregateFunc='3'>              
            <Column ref='C22'>                                        
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O35'>                                               
        <Expr id='X35'>                                               
          <Function id='S35' op='min' aggregateFunc='4'>              
            <Column ref='C22'>                                        
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O36'>                                               
        <Expr id='X36'>                                               
          <Function id='S36' op='max' aggregateFunc='5'>              
            <Column ref='C22'>                                        
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O47'>                                               
        <Column ref='C4'>                                             
        </Column>                                                     
      </Output>                                                       
    </OutputList>                                                     
    <GroupBy id='G3'>                                                 
      <Primary>                                                       
        <Column id='C4' tableId='T2' colIndex='3'>                    
          CAT.OUTPUTS.FACT.FYEAR                                      
        </Column>                                                     
      </Primary>                                                      
    </GroupBy>                                                        
  </JBB>                                                              
</Query>                                                              

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE               >> outputs_a2.in;
>>sh echo PUBLISH OUTPUTS_MJV1.xml >> outputs_a2.in;
>>sh echo PUBLISH OUTPUTS_MJV2.xml >> outputs_a2.in;
>>sh echo PUBLISH OUTPUTS_MAV1.xml >> outputs_a2.in;
>>sh echo PUBLISH OUTPUTS_MAV2.xml >> outputs_a2.in;
>>sh echo PUBLISH OUTPUTS_MAV3.xml >> outputs_a2.in;
>>sh echo PUBLISH OUTPUTS_MAV4.xml >> outputs_a2.in;
>>sh echo PUBLISH OUTPUTS_MAV5.xml >> outputs_a2.in;
>>sh echo MATCH OUTPUTS_AQ2.xml    >> outputs_a2.in;
>>
>>sh sh -c "$QMS outputs_a2.in outputs.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.OUTPUTS.DIM1
        </Table>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV2
          </MVName>
          <OutputList>
            <Output ref='O47' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='C22' result='Provided'>
              <MVColumn ref='C22'>
                DIMDATA
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV1
          </MVName>
          <OutputList>
            <Output ref='O47' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='C8' result='Provided'>
              <MVColumn ref='C8'>
                DIMKEY
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.OUTPUTS.DIM1
        </Table>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MAV4
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O47' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O36' result='NotProvided'>
              <Expr ref='X36'>
                <Function op='max' aggregateFunc='5'>
                  <MVColumn ref='S36'>
                    MAX_D
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O35' result='NotProvided'>
              <Expr ref='X35'>
                <Function op='min' aggregateFunc='4'>
                  <MVColumn ref='S35'>
                    MIN_D
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O34' result='NotProvided'>
              <Expr ref='X34'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S34'>
                    SUM_D
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O33' result='NotProvided'>
              <Expr ref='X33'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S33'>
                    COUNT_D
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O32' result='NotProvided'>
              <Expr ref='X32'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn>
                    COUNTSTAR
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MAV5
          </MVName>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O32' result='Provided'>
              <MVColumn ref='X32'>
                COUNTSTAR
              </MVColumn>
            </Output>
            <Output ref='O33' result='Provided'>
              <MVColumn ref='X33'>
                COUNT_D
              </MVColumn>
            </Output>
            <Output ref='O34' result='Provided'>
              <MVColumn ref='X34'>
                SUM_D
              </MVColumn>
            </Output>
            <Output ref='O35' result='Provided'>
              <MVColumn ref='X35'>
                MIN_D
              </MVColumn>
            </Output>
            <Output ref='O36' result='Provided'>
              <MVColumn ref='X36'>
                MAX_D
              </MVColumn>
            </Output>
            <Output ref='O47' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MAV1 was disqualified because aggregate function S36 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MAV2 was disqualified because aggregate function S36 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MAV3 was disqualified because aggregate function S36 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV3 was disqualified because Extra hub table CAT.OUTPUTS.DIM1 is needed but cannot be matched.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>prepare QueryStmt from
+>  select count(*),
+>	 count(dimdata),
+>	 sum(dimdata), 
+>	 min(dimdata),
+>	 max(dimdata),
+>         fyear oyear	           
+>  from fact, dim1
+>  where dimkey=dkey
+>  group by fyear
+>  order by oyear;

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

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

FILE_SCAN                       CAT.OUTPUTS.OUTPUTS_MJV2                                    

--- 1 row(s) selected.
>>
>>obey TEST003(match_AQ3);
>>--===========================================
>>--== Check all aggregate functions over MJV1, MJV2, MAV4
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>prepare QueryStmt from
+>  select count(*),
+>	 count(dimdata),
+>	 sum(dimdata), 
+>	 avg(dimdata),
+>	 min(dimdata),
+>	 max(dimdata),
+>	 stddev(dimdata),
+>	 ----stddev(dimdata, fyear),
+>	 ----stddev(dimdata, fmonth),
+>	 variance(dimdata),
+>         fyear oyear	           
+>  from fact, dim1
+>  where dimkey=dkey
+>  group by fyear
+>  order by oyear;

--- SQL command prepared.
>>
>>set pattern $$QueryName$$ OUTPUTS_AQ3;
>>obey TEST003(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='4'>                                             
  </Misc>                                                             
  <JBB id='B0'>                                                       
    <Hub>                                                             
      <JBBCList>                                                      
        <Table id='T1' TS='212223873978946945' numCols='3'>           
          CAT.OUTPUTS.DIM1                                            
          <Key>                                                       
            <Column id='C21' tableId='T1' colIndex='0' isNullable='0'>
              CAT.OUTPUTS.DIM1.DKEY                                   
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
        <Table id='T2' TS='212223873978946945' numCols='8'>           
          CAT.OUTPUTS.FACT                                            
          <Key>                                                       
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'> 
              CAT.OUTPUTS.FACT.SYSKEY                                 
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </JBBCList>                                                     
      <JoinPredList>                                                  
        <JoinPred id='J79'>                                           
          <Column ref='C21'>                                          
          </Column>                                                   
          <Column id='C8' tableId='T2' colIndex='7'>                  
            CAT.OUTPUTS.FACT.DIMKEY                                   
          </Column>                                                   
        </JoinPred>                                                   
      </JoinPredList>                                                 
    </Hub>                                                            
    <ExtraHub>                                                        
    </ExtraHub>                                                       
    <OutputList>                                                      
      <Output id='O32'>                                               
        <Expr id='X32'>                                               
          <Function id='S32' op='count' aggregateFunc='1'>            
            <NumericVal id='S31' scale='0'>                           
              1                                                       
            </NumericVal>                                             
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O33'>                                               
        <Expr id='X33'>                                               
          <Function id='S33' op='count_nonull' aggregateFunc='2'>     
            <Column id='C22' tableId='T1' colIndex='1'>               
              CAT.OUTPUTS.DIM1.DIMDATA                                
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O34'>                                               
        <Expr id='X34'>                                               
          <Function id='S34' op='sum' aggregateFunc='3'>              
            <Column ref='C22'>                                        
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O38'>                                               
        <Expr id='X38'>                                               
          <Function id='S38' op='min' aggregateFunc='4'>              
            <Column ref='C22'>                                        
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O39'>                                               
        <Expr id='X39'>                                               
          <Function id='S39' op='max' aggregateFunc='5'>              
            <Column ref='C22'>                                        
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O47'>                                               
        <Expr id='X47'>                                               
          <Function id='S47' op='Scalar Stddev'>                      
            <Function id='S43' op='sum' aggregateFunc='3'>            
              <BinaryOper id='S42' op='*'>                            
                <Function id='S41' op='cast'>                         
                  <Column ref='C22'>                                  
                  </Column>                                           
                </Function>                                           
                <Function id='S41' op='cast'>                         
                  <Column ref='C22'>                                  
                  </Column>                                           
                </Function>                                           
              </BinaryOper>                                           
            </Function>                                               
            <Function id='S44' op='sum' aggregateFunc='3'>            
              <Function id='S41' op='cast'>                           
                <Column ref='C22'>                                    
                </Column>                                             
              </Function>                                             
            </Function>                                               
            <Function id='S46' op='cast'>                             
              <Function id='S45' op='count_nonull' aggregateFunc='2'> 
                <Function id='S41' op='cast'>                         
                  <Column ref='C22'>                                  
                  </Column>                                           
                </Function>                                           
              </Function>                                             
            </Function>                                               
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O55'>                                               
        <Expr id='X55'>                                               
          <Function id='S55' op='Scalar Variance'>                    
            <Function id='S43' op='sum' aggregateFunc='3'>            
              <BinaryOper id='S42' op='*'>                            
                <Function id='S41' op='cast'>                         
                  <Column ref='C22'>                                  
                  </Column>                                           
                </Function>                                           
                <Function id='S41' op='cast'>                         
                  <Column ref='C22'>                                  
                  </Column>                                           
                </Function>                                           
              </BinaryOper>                                           
            </Function>                                               
            <Function id='S44' op='sum' aggregateFunc='3'>            
              <Function id='S41' op='cast'>                           
                <Column ref='C22'>                                    
                </Column>                                             
              </Function>                                             
            </Function>                                               
            <Function id='S54' op='cast'>                             
              <Function id='S45' op='count_nonull' aggregateFunc='2'> 
                <Function id='S41' op='cast'>                         
                  <Column ref='C22'>                                  
                  </Column>                                           
                </Function>                                           
              </Function>                                             
            </Function>                                               
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O67'>                                               
        <Column ref='C4'>                                             
        </Column>                                                     
      </Output>                                                       
    </OutputList>                                                     
    <GroupBy id='G3'>                                                 
      <Primary>                                                       
        <Column id='C4' tableId='T2' colIndex='3'>                    
          CAT.OUTPUTS.FACT.FYEAR                                      
        </Column>                                                     
      </Primary>                                                      
    </GroupBy>                                                        
  </JBB>                                                              
</Query>                                                              

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE               >> outputs_a3.in;
>>sh echo PUBLISH OUTPUTS_MJV1.xml >> outputs_a3.in;
>>sh echo PUBLISH OUTPUTS_MJV2.xml >> outputs_a3.in;
>>sh echo PUBLISH OUTPUTS_MAV1.xml >> outputs_a3.in;
>>sh echo PUBLISH OUTPUTS_MAV2.xml >> outputs_a3.in;
>>sh echo PUBLISH OUTPUTS_MAV3.xml >> outputs_a3.in;
>>sh echo PUBLISH OUTPUTS_MAV4.xml >> outputs_a3.in;
>>sh echo PUBLISH OUTPUTS_MAV5.xml >> outputs_a3.in;
>>sh echo MATCH OUTPUTS_AQ3.xml    >> outputs_a3.in;
>>
>>sh sh -c "$QMS outputs_a3.in outputs.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.OUTPUTS.DIM1
        </Table>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV2
          </MVName>
          <OutputList>
            <Output ref='O67' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='C22' result='Provided'>
              <MVColumn ref='C22'>
                DIMDATA
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV1
          </MVName>
          <OutputList>
            <Output ref='O67' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='C8' result='Provided'>
              <MVColumn ref='C8'>
                DIMKEY
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.OUTPUTS.DIM1
        </Table>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MAV4
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O67' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O55' result='NotProvided'>
              <Expr ref='X55'>
                <Function ref='S55' op='Scalar Variance'>
                  <Function op='sum' aggregateFunc='3'>
                    <MVColumn ref='S43'>
                      SUM_DD
                    </MVColumn>
                  </Function>
                  <Function op='sum' aggregateFunc='3'>
                    <MVColumn ref='S44'>
                      SUM_D
                    </MVColumn>
                  </Function>
                  <Function ref='S54' op='cast'>
                    <Function op='sum' aggregateFunc='3'>
                      <MVColumn ref='S45'>
                        COUNT_D
                      </MVColumn>
                    </Function>
                  </Function>
                </Function>
              </Expr>
            </Output>
            <Output ref='O47' result='NotProvided'>
              <Expr ref='X47'>
                <Function ref='S47' op='Scalar Stddev'>
                  <Function op='sum' aggregateFunc='3'>
                    <MVColumn ref='S43'>
                      SUM_DD
                    </MVColumn>
                  </Function>
                  <Function op='sum' aggregateFunc='3'>
                    <MVColumn ref='S44'>
                      SUM_D
                    </MVColumn>
                  </Function>
                  <Function ref='S46' op='cast'>
                    <Function op='sum' aggregateFunc='3'>
                      <MVColumn ref='S45'>
                        COUNT_D
                      </MVColumn>
                    </Function>
                  </Function>
                </Function>
              </Expr>
            </Output>
            <Output ref='O39' result='NotProvided'>
              <Expr ref='X39'>
                <Function op='max' aggregateFunc='5'>
                  <MVColumn ref='S39'>
                    MAX_D
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O38' result='NotProvided'>
              <Expr ref='X38'>
                <Function op='min' aggregateFunc='4'>
                  <MVColumn ref='S38'>
                    MIN_D
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O34' result='NotProvided'>
              <Expr ref='X34'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S34'>
                    SUM_D
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O33' result='NotProvided'>
              <Expr ref='X33'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S33'>
                    COUNT_D
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O32' result='NotProvided'>
              <Expr ref='X32'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn>
                    COUNTSTAR
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MAV5
          </MVName>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O32' result='Provided'>
              <MVColumn ref='X32'>
                COUNTSTAR
              </MVColumn>
            </Output>
            <Output ref='O33' result='Provided'>
              <MVColumn ref='X33'>
                COUNT_D
              </MVColumn>
            </Output>
            <Output ref='O34' result='Provided'>
              <MVColumn ref='X34'>
                SUM_D
              </MVColumn>
            </Output>
            <Output ref='O38' result='Provided'>
              <MVColumn ref='X38'>
                MIN_D
              </MVColumn>
            </Output>
            <Output ref='O39' result='Provided'>
              <MVColumn ref='X39'>
                MAX_D
              </MVColumn>
            </Output>
            <Output ref='O47' result='Provided'>
              <MVColumn ref='X47'>
                STDDEV_D
              </MVColumn>
            </Output>
            <Output ref='O55' result='Provided'>
              <MVColumn ref='X55'>
                VARIANCE_D
              </MVColumn>
            </Output>
            <Output ref='O67' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MAV1 was disqualified because aggregate function S43 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MAV2 was disqualified because aggregate function S43 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MAV3 was disqualified because aggregate function S43 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV3 was disqualified because Extra hub table CAT.OUTPUTS.DIM1 is needed but cannot be matched.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>prepare QueryStmt from
+>  select count(*),
+>	 count(dimdata),
+>	 sum(dimdata), 
+>	 avg(dimdata),
+>	 min(dimdata),
+>	 max(dimdata),
+>	 stddev(dimdata),
+>	 variance(dimdata),
+>         fyear oyear	           
+>  from fact, dim1
+>  where dimkey=dkey
+>  group by fyear
+>  order by oyear;

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

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

FILE_SCAN                       CAT.OUTPUTS.OUTPUTS_MJV2                                    

--- 1 row(s) selected.
>>
>>obey TEST003(match_AQ4);
>>--===========================================
>>--== Check distinct aggregate functions over MJV1, MJV2, MAV4
>>--===========================================
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>prepare QueryStmt from
+>  select count(*),
+>	 count(distinct dimdata),
+>	 sum(distinct dimdata), 
+>         fyear oyear	           
+>  from fact, dim1
+>  where dimkey=dkey
+>  group by fyear
+>  order by oyear;

--- SQL command prepared.
>>
>>set pattern $$QueryName$$ OUTPUTS_AQ4;
>>obey TEST003(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='4'>                                             
  </Misc>                                                             
  <JBB id='B0'>                                                       
    <Hub>                                                             
      <JBBCList>                                                      
        <Table id='T1' TS='212223873978946945' numCols='3'>           
          CAT.OUTPUTS.DIM1                                            
          <Key>                                                       
            <Column id='C21' tableId='T1' colIndex='0' isNullable='0'>
              CAT.OUTPUTS.DIM1.DKEY                                   
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
        <Table id='T2' TS='212223873978946945' numCols='8'>           
          CAT.OUTPUTS.FACT                                            
          <Key>                                                       
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'> 
              CAT.OUTPUTS.FACT.SYSKEY                                 
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </JBBCList>                                                     
      <JoinPredList>                                                  
        <JoinPred id='J57'>                                           
          <Column ref='C21'>                                          
          </Column>                                                   
          <Column id='C8' tableId='T2' colIndex='7'>                  
            CAT.OUTPUTS.FACT.DIMKEY                                   
          </Column>                                                   
        </JoinPred>                                                   
      </JoinPredList>                                                 
    </Hub>                                                            
    <ExtraHub>                                                        
    </ExtraHub>                                                       
    <OutputList>                                                      
      <Output id='O32'>                                               
        <Expr id='X32'>                                               
          <Function id='S32' op='count' aggregateFunc='1'>            
            <NumericVal id='S31' scale='0'>                           
              1                                                       
            </NumericVal>                                             
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O33'>                                               
        <Expr id='X33'>                                               
          <Function id='S33' op='count distinct' aggregateFunc='6'>   
            <Column id='C22' tableId='T1' colIndex='1'>               
              CAT.OUTPUTS.DIM1.DIMDATA                                
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O34'>                                               
        <Expr id='X34'>                                               
          <Function id='S34' op='sum distinct' aggregateFunc='7'>     
            <Column ref='C22'>                                        
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O45'>                                               
        <Column ref='C4'>                                             
        </Column>                                                     
      </Output>                                                       
    </OutputList>                                                     
    <GroupBy id='G3'>                                                 
      <Primary>                                                       
        <Column id='C4' tableId='T2' colIndex='3'>                    
          CAT.OUTPUTS.FACT.FYEAR                                      
        </Column>                                                     
      </Primary>                                                      
    </GroupBy>                                                        
  </JBB>                                                              
</Query>                                                              

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE               >> outputs_a4.in;
>>sh echo PUBLISH OUTPUTS_MJV1.xml >> outputs_a4.in;
>>sh echo PUBLISH OUTPUTS_MJV2.xml >> outputs_a4.in;
>>sh echo PUBLISH OUTPUTS_MAV1.xml >> outputs_a4.in;
>>sh echo PUBLISH OUTPUTS_MAV2.xml >> outputs_a4.in;
>>sh echo PUBLISH OUTPUTS_MAV3.xml >> outputs_a4.in;
>>sh echo PUBLISH OUTPUTS_MAV4.xml >> outputs_a4.in;
>>sh echo PUBLISH OUTPUTS_MAV5.xml >> outputs_a4.in;
>>sh echo MATCH OUTPUTS_AQ4.xml    >> outputs_a4.in;
>>
>>sh sh -c "$QMS outputs_a4.in outputs.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.OUTPUTS.DIM1
        </Table>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV2
          </MVName>
          <OutputList>
            <Output ref='O45' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='C22' result='Provided'>
              <MVColumn ref='C22'>
                DIMDATA
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T2'>
          CAT.OUTPUTS.FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_MJV1
          </MVName>
          <OutputList>
            <Output ref='O45' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='C8' result='Provided'>
              <MVColumn ref='C8'>
                DIMKEY
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MAV1 was disqualified because DISTINCT aggregate function S34 cannot be rolled up.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MAV2 was disqualified because DISTINCT aggregate function S34 cannot be rolled up.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MAV3 was disqualified because aggregate function S34 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MAV4 was disqualified because DISTINCT aggregate function S34 cannot be rolled up.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MAV5 was disqualified because aggregate function S34 cannot be matched.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.OUTPUTS_MJV3 was disqualified because Extra hub table CAT.OUTPUTS.DIM1 is needed but cannot be matched.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>prepare QueryStmt from
+>  select count(*),
+>	 count(distinct dimdata),
+>	 sum(distinct dimdata), 
+>         fyear oyear	           
+>  from fact, dim1
+>  where dimkey=dkey
+>  group by fyear
+>  order by oyear;

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

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

FILE_SCAN                       CAT.OUTPUTS.OUTPUTS_MJV2                                    

--- 1 row(s) selected.
>>
>>obey TEST003(match_AQ5);
>>--===========================================
>>--== Check Rollup over an MV grouping column.
>>--===========================================
>>create table Table1 (
+>  a int not null
+>, b int
+>, c int
+>, d int
+>, e int
+>, primary key(a)
+>);

--- SQL operation complete.
>>
>>insert into Table1 values
+> (1,0,1,1,1)
+>,(2,1,1,1,2)
+>,(3,1,1,1,3)
+>,(6,1,2,2,6)
+>,(7,1,2,1,7)
+>,(8,1,2,1,8);

--- 6 row(s) inserted.
>>
>>-- Insert some rows with NULL values.
>>insert into Table1(a,b,d,e) values
+> (4,0,  2,4)
+>,(5,0,  2,5)
+>,(9,1,  1,9);

--- 3 row(s) inserted.
>>
>>select t1.b, t1.c, count(*) countstar
+>  from Table1 t1  
+>  where t1.a>0 
+>  group by t1.b,t1.c ;

B            C            COUNTSTAR           
-----------  -----------  --------------------

          1            ?                     1
          0            1                     1
          0            ?                     2
          1            2                     3
          1            1                     2

--- 5 row(s) selected.
>>
>>-- An incremental MV will have a system added COUNT(*) column.
>>create mv MAVwithCountStar 
+>  REFRESH ON REQUEST 
+>  INITIALIZE ON CREATE 
+>  ENABLE QUERY REWRITE 
+>  as select t1.b,
+>            t1.c,
+>            sum(t1.e) sum_e 
+>     from Table1 t1 
+>     where t1.a>0 
+>     group by t1.b,t1.c;

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

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ MAVWITHCOUNTSTAR;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.MAVWITHCOUNTSTAR
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874007053981' numCols='5' rangeBits='00000008'>
          CAT.OUTPUTS.TABLE1
        </Table>
      </JBBCList>
      <RangePredList>
        <Range id='R105' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C70' tableId='T1' colIndex='0' isNullable='0'>
            CAT.OUTPUTS.TABLE1.A
          </Column>
          <OpGE isNormalized='1'>
            <NumericVal scale='0'>
              1
            </NumericVal>
          </OpGE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O83' name='SUM_E'>
        <Expr id='X83'>
          <Function id='S83' op='sum' aggregateFunc='3'>
            <Column id='C74' tableId='T1' colIndex='4'>
              CAT.OUTPUTS.TABLE1.E
            </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='C74'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O91' name='B'>
        <Column ref='C71'>
        </Column>
      </Output>
      <Output id='O94' name='C'>
        <Column ref='C72'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G2'>
      <Primary>
        <Column id='C71' tableId='T1' colIndex='1'>
          CAT.OUTPUTS.TABLE1.B
        </Column>
        <Column id='C72' tableId='T1' colIndex='2'>
          CAT.OUTPUTS.TABLE1.C
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>-- Lets look at the data in the MV
>>select *, SYS_COUNTSTAR1
+>  from MAVwithCountStar;

B            C            SUM_E                 SYS_COUNTSTAR1      
-----------  -----------  --------------------  --------------------

          1            ?                     9                     1
          0            1                     1                     1
          0            ?                     9                     2
          1            2                    21                     3
          1            1                     5                     2

--- 5 row(s) selected.
>>
>>-- A recompute MV will not have it.
>>create mv MAVwithoutCountStar 
+>  RECOMPUTE 
+>  INITIALIZE ON CREATE 
+>  ENABLE QUERY REWRITE 
+>  as select t1.b,
+>            t1.c,
+>            sum(t1.e) sum_e 
+>     from Table1 t1 
+>     where t1.a>0 
+>     group by t1.b,t1.c;

--- SQL operation complete.
>>
>>set pattern $$MVName$$ MAVWITHOUTCOUNTSTAR;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.MAVWITHOUTCOUNTSTAR
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874007694553' numCols='5' rangeBits='00000008'>
          CAT.OUTPUTS.TABLE1
        </Table>
      </JBBCList>
      <RangePredList>
        <Range id='R87' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C55' tableId='T1' colIndex='0' isNullable='0'>
            CAT.OUTPUTS.TABLE1.A
          </Column>
          <OpGE isNormalized='1'>
            <NumericVal scale='0'>
              1
            </NumericVal>
          </OpGE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O68' name='SUM_E'>
        <Expr id='X68'>
          <Function id='S68' op='sum' aggregateFunc='3'>
            <Column id='C59' tableId='T1' colIndex='4'>
              CAT.OUTPUTS.TABLE1.E
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O73' name='B'>
        <Column ref='C56'>
        </Column>
      </Output>
      <Output id='O76' name='C'>
        <Column ref='C57'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G2'>
      <Primary>
        <Column id='C56' tableId='T1' colIndex='1'>
          CAT.OUTPUTS.TABLE1.B
        </Column>
        <Column id='C57' tableId='T1' colIndex='2'>
          CAT.OUTPUTS.TABLE1.C
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>prepare QueryStmt from
+>  select t1.b,
+>  	 count(*)             countstar,
+>         count(t1.c)          count_c,
+>         sum(t1.c)            sum_c,
+>         count(distinct t1.c) count_d_c,
+>         sum(distinct t1.c)   sum_d_c,
+>         max(c)               max_c,
+>         --sum(t1.c + 5)        sum_cplus5,
+>         --sum(t1.c * t1.c)     sum_csquare
+>         t1.b + sum(t1.e)     sume_plus_b
+>  from Table1 t1 
+>  where t1.a>0 
+>  group by t1.b 
+>  order by b;

--- SQL command prepared.
>>
>>set pattern $$QueryName$$ OUTPUTS_AQ5;
>>obey TEST003(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='4'>                                                       
  </Misc>                                                                       
  <JBB id='B0'>                                                                 
    <Hub>                                                                       
      <JBBCList>                                                                
        <Table id='T1' TS='212223874007694553' numCols='5' rangeBits='00000008'>
          CAT.OUTPUTS.TABLE1                                                    
          <Key>                                                                 
            <Column id='C1' tableId='T1' colIndex='0' isNullable='0'>           
              CAT.OUTPUTS.TABLE1.A                                              
            </Column>                                                           
          </Key>                                                                
        </Table>                                                                
      </JBBCList>                                                               
      <RangePredList>                                                           
        <Range id='R42' sqlType='INTEGER SIGNED NO NULLS'>                      
          <Column ref='C1'>                                                     
          </Column>                                                             
          <OpGE isNormalized='1'>                                               
            <NumericVal scale='0'>                                              
              1                                                                 
            </NumericVal>                                                       
          </OpGE>                                                               
        </Range>                                                                
      </RangePredList>                                                          
    </Hub>                                                                      
    <ExtraHub>                                                                  
    </ExtraHub>                                                                 
    <OutputList>                                                                
      <Output id='O15'>                                                         
        <Expr id='X15'>                                                         
          <Function id='S15' op='count' aggregateFunc='1'>                      
            <NumericVal id='S14' scale='0'>                                     
              1                                                                 
            </NumericVal>                                                       
          </Function>                                                           
        </Expr>                                                                 
      </Output>                                                                 
      <Output id='O16'>                                                         
        <Expr id='X16'>                                                         
          <Function id='S16' op='count_nonull' aggregateFunc='2'>               
            <Column id='C3' tableId='T1' colIndex='2'>                          
              CAT.OUTPUTS.TABLE1.C                                              
            </Column>                                                           
          </Function>                                                           
        </Expr>                                                                 
      </Output>                                                                 
      <Output id='O17'>                                                         
        <Expr id='X17'>                                                         
          <Function id='S17' op='sum' aggregateFunc='3'>                        
            <Column ref='C3'>                                                   
            </Column>                                                           
          </Function>                                                           
        </Expr>                                                                 
      </Output>                                                                 
      <Output id='O18'>                                                         
        <Expr id='X18'>                                                         
          <Function id='S18' op='count distinct' aggregateFunc='6'>             
            <Column ref='C3'>                                                   
            </Column>                                                           
          </Function>                                                           
        </Expr>                                                                 
      </Output>                                                                 
      <Output id='O19'>                                                         
        <Expr id='X19'>                                                         
          <Function id='S19' op='sum distinct' aggregateFunc='7'>               
            <Column ref='C3'>                                                   
            </Column>                                                           
          </Function>                                                           
        </Expr>                                                                 
      </Output>                                                                 
      <Output id='O20'>                                                         
        <Expr id='X20'>                                                         
          <Function id='S20' op='max' aggregateFunc='5'>                        
            <Column ref='C3'>                                                   
            </Column>                                                           
          </Function>                                                           
        </Expr>                                                                 
      </Output>                                                                 
      <Output id='O23'>                                                         
        <Expr id='X23'>                                                         
          <Function id='S23' op='cast'>                                         
            <BinaryOper id='S22' op='+'>                                        
              <Column ref='C2'>                                                 
              </Column>                                                         
              <Function id='S21' op='sum' aggregateFunc='3'>                    
                <Column id='C5' tableId='T1' colIndex='4'>                      
                  CAT.OUTPUTS.TABLE1.E                                          
                </Column>                                                       
              </Function>                                                       
            </BinaryOper>                                                       
          </Function>                                                           
        </Expr>                                                                 
      </Output>                                                                 
      <Output id='O28'>                                                         
        <Column ref='C2'>                                                       
        </Column>                                                               
      </Output>                                                                 
    </OutputList>                                                               
    <GroupBy id='G2'>                                                           
      <Primary>                                                                 
        <Column id='C2' tableId='T1' colIndex='1'>                              
          CAT.OUTPUTS.TABLE1.B                                                  
        </Column>                                                               
      </Primary>                                                                
    </GroupBy>                                                                  
  </JBB>                                                                        
</Query>                                                                        

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE                      >> outputs_a5.in;
>>sh echo PUBLISH MAVWITHCOUNTSTAR.xml    >> outputs_a5.in;
>>sh echo PUBLISH MAVWITHOUTCOUNTSTAR.xml >> outputs_a5.in;
>>sh echo MATCH OUTPUTS_AQ5.xml    >> outputs_a5.in;
>>
>>sh sh -c "$QMS outputs_a5.in outputs.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset ref='G2' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.OUTPUTS.TABLE1
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.MAVWITHCOUNTSTAR
          </MVName>
          <RangePredList>
            <Range ref='R42' result='Provided'>
            </Range>
          </RangePredList>
          <GroupBy ref='G2' result='NotProvided'>
            <Primary>
              <MVColumn ref='C2'>
                B
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O28' result='Provided'>
              <MVColumn ref='C2'>
                B
              </MVColumn>
            </Output>
            <Output ref='O23' result='NotProvided'>
              <Expr ref='X23'>
                <Function ref='S23' op='cast'>
                  <BinaryOper ref='S22' op='+'>
                    <MVColumn ref='C2'>
                      B
                    </MVColumn>
                    <Function op='sum' aggregateFunc='3'>
                      <MVColumn ref='S21'>
                        SUM_E
                      </MVColumn>
                    </Function>
                  </BinaryOper>
                </Function>
              </Expr>
            </Output>
            <Output ref='O20' result='NotProvided'>
              <Expr ref='X20'>
                <Function op='max' aggregateFunc='5'>
                  <MVColumn ref='C3'>
                    C
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O19' result='NotProvided'>
              <Expr ref='X19'>
                <Function op='sum distinct' aggregateFunc='7'>
                  <MVColumn ref='C3'>
                    C
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O18' result='NotProvided'>
              <Expr ref='X18'>
                <Function op='count distinct' aggregateFunc='6'>
                  <MVColumn ref='C3'>
                    C
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O17' result='NotProvided'>
              <Expr ref='X17'>
                <Function op='sum on grouping' aggregateFunc='9'>
                  <MVColumn ref='C3'>
                    C
                  </MVColumn>
                  <MVColumn>
                    SYS_COUNTSTAR1
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O16' result='NotProvided'>
              <Expr ref='X16'>
                <Function op='count on grouping' aggregateFunc='8'>
                  <MVColumn ref='C3'>
                    C
                  </MVColumn>
                  <MVColumn>
                    SYS_COUNTSTAR1
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O15' result='NotProvided'>
              <Expr ref='X15'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn>
                    SYS_COUNTSTAR1
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.OUTPUTS.MAVWITHOUTCOUNTSTAR was disqualified because the MV does not have a COUNT(*) column.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>cqd mvqr_rewrite_candidates 'CAT.OUTPUTS.MAVWITHCOUNTSTAR';

--- SQL operation complete.
>>prepare QueryStmt from
+>  select t1.b,
+>  	 count(*)             countstar,
+>         count(t1.c)          count_c,
+>         sum(t1.c)            sum_c,
+>         count(distinct t1.c) count_d_c,
+>         sum(distinct t1.c)   sum_d_c,
+>         max(c)               max_c,
+>         --sum(t1.c + 5)        sum_cplus5,
+>         --sum(t1.c * t1.c)     sum_csquare
+>         t1.b + sum(t1.e)     sume_plus_b
+>  from Table1 t1 
+>  where t1.a>0 
+>  group by t1.b 
+>  order by b;

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

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

FILE_SCAN                       CAT.OUTPUTS.MAVWITHCOUNTSTAR                                

--- 1 row(s) selected.
>>-- Check the results
>>execute QueryStmt;

B            COUNTSTAR             COUNT_C               SUM_C                 COUNT_D_C             SUM_D_C               MAX_C        SUME_PLUS_B
-----------  --------------------  --------------------  --------------------  --------------------  --------------------  -----------  --------------------

          0                     3                     1                     1                     1                     1            1                    10
          1                     6                     5                     8                     2                     3            2                    36

--- 2 row(s) selected.
>>
>>-- And compare to a plan that is not rewritten
>>cqd mvqr_rewrite_level '0';

--- SQL operation complete.
>>prepare QueryStmt from
+>  select t1.b,
+>  	 count(*)             countstar,
+>         count(t1.c)          count_c,
+>         sum(t1.c)            sum_c,
+>         count(distinct t1.c) count_d_c,
+>         sum(distinct t1.c)   sum_d_c,
+>         max(c)               max_c,
+>         --sum(t1.c + 5)        sum_cplus5,
+>         --sum(t1.c * t1.c)     sum_csquare
+>         t1.b + sum(t1.e)     sume_plus_b
+>  from Table1 t1 
+>  where t1.a>0 
+>  group by t1.b 
+>  order by b;

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

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

FILE_SCAN                       T1 (CAT.OUTPUTS.TABLE1)                                     

--- 1 row(s) selected.
>>-- Check the results
>>execute QueryStmt;

B            COUNTSTAR             COUNT_C               SUM_C                 COUNT_D_C             SUM_D_C               MAX_C        SUME_PLUS_B
-----------  --------------------  --------------------  --------------------  --------------------  --------------------  -----------  --------------------

          0                     3                     1                     1                     1                     1            1                    10
          1                     6                     5                     8                     2                     3            2                    36

--- 2 row(s) selected.
>>
>>cqd MVQR_REWRITE_LEVEL '4';

--- SQL operation complete.
>>
>>obey TEST003(match_AQ6);
>>--===========================================
>>--== Check use of COUNT(*) instead of COUNT(not null a)
>>--===========================================
>>
>>-- This MV has COUNT(a), instead of COUNT(*) column.
>>create mv OUTPUTS_NoCountStar
+>  refresh on request initialize on create
+>  ENABLE QUERY REWRITE
+>  as select b
+>          , sum(a) s1
+>          , count(a) c1
+>          , sum(a*a) b1
+>     from Table1
+>     group by b;

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

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ OUTPUTS_NOCOUNTSTAR;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.OUTPUTS_NOCOUNTSTAR
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874007694553' numCols='5'>
          CAT.OUTPUTS.TABLE1
        </Table>
      </JBBCList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O85' name='S1'>
        <Expr id='X85'>
          <Function id='S85' op='sum' aggregateFunc='3'>
            <Column id='C74' tableId='T1' colIndex='0' isNullable='0'>
              CAT.OUTPUTS.TABLE1.A
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O86' name='C1'>
        <Expr id='X86'>
          <Function id='S86' op='count' aggregateFunc='1'>
            <NumericVal id='S87' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O89' name='B1'>
        <Expr id='X89'>
          <Function id='S89' op='sum' aggregateFunc='3'>
            <BinaryOper id='S88' op='*'>
              <Column ref='C74'>
              </Column>
              <Column ref='C74'>
              </Column>
            </BinaryOper>
          </Function>
        </Expr>
      </Output>
      <Output id='O96' name='B'>
        <Column ref='C75'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G2'>
      <Primary>
        <Column id='C75' tableId='T1' colIndex='1'>
          CAT.OUTPUTS.TABLE1.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>prepare QueryStmt from
+>  select b, stddev(a) s1
+>  from Table1
+>  group by b
+>  order by b;

--- SQL command prepared.
>>
>>set pattern $$QueryName$$ OUTPUTS_AQ6;
>>obey TEST003(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='4'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874007694553' numCols='5'>          
          CAT.OUTPUTS.TABLE1                                         
          <Key>                                                      
            <Column id='C1' tableId='T1' colIndex='0' isNullable='0'>
              CAT.OUTPUTS.TABLE1.A                                   
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O19'>                                              
        <Expr id='X19'>                                              
          <Function id='S19' op='Scalar Stddev'>                     
            <Function id='S15' op='sum' aggregateFunc='3'>           
              <BinaryOper id='S14' op='*'>                           
                <Function id='S13' op='cast'>                        
                  <Column ref='C1'>                                  
                  </Column>                                          
                </Function>                                          
                <Function id='S13' op='cast'>                        
                  <Column ref='C1'>                                  
                  </Column>                                          
                </Function>                                          
              </BinaryOper>                                          
            </Function>                                              
            <Function id='S16' op='sum' aggregateFunc='3'>           
              <Function id='S13' op='cast'>                          
                <Column ref='C1'>                                    
                </Column>                                            
              </Function>                                            
            </Function>                                              
            <Function id='S18' op='cast'>                            
              <Function id='S17' op='count_nonull' aggregateFunc='2'>
                <Function id='S13' op='cast'>                        
                  <Column ref='C1'>                                  
                  </Column>                                          
                </Function>                                          
              </Function>                                            
            </Function>                                              
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O24'>                                              
        <Column ref='C2'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G2'>                                                
      <Primary>                                                      
        <Column id='C2' tableId='T1' colIndex='1'>                   
          CAT.OUTPUTS.TABLE1.B                                       
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE                      >> outputs_a6.in;
>>sh echo PUBLISH OUTPUTS_NOCOUNTSTAR.xml >> outputs_a6.in;
>>sh echo MATCH OUTPUTS_AQ6.xml           >> outputs_a6.in;
>>
>>sh sh -c "$QMS outputs_a6.in outputs.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset ref='G2' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.OUTPUTS.TABLE1
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.OUTPUTS_NOCOUNTSTAR
          </MVName>
          <GroupBy ref='G2' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='C2'>
                B
              </MVColumn>
            </Output>
            <Output ref='O19' result='NotProvided'>
              <Expr ref='X19'>
                <Function ref='S19' op='Scalar Stddev'>
                  <MVColumn ref='S15'>
                    B1
                  </MVColumn>
                  <MVColumn ref='S16'>
                    S1
                  </MVColumn>
                  <Function ref='S18' op='cast'>
                    <MVColumn ref='S17'>
                      C1
                    </MVColumn>
                  </Function>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.OUTPUTS.MAVWITHCOUNTSTAR was disqualified because Table CAT.OUTPUTS.TABLE1 failed on range bitmap.]]></Info>
    <Info><![CDATA[MV CAT.OUTPUTS.MAVWITHOUTCOUNTSTAR was disqualified because Table CAT.OUTPUTS.TABLE1 failed on range bitmap.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>cqd mvqr_rewrite_candidates 'CAT.OUTPUTS.OUTPUTS_NOCOUNTSTAR';

--- SQL operation complete.
>>prepare QueryStmt from
+>  select b, stddev(a) s1
+>  from Table1
+>  group by b
+>  order by b;

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

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

FILE_SCAN                       CAT.OUTPUTS.OUTPUTS_NOCOUNTSTAR                             

--- 1 row(s) selected.
>>
>>obey TEST003(match_AQ7);
>>--===========================================
>>--== Check use of GROUP BY expressions
>>--===========================================
>>
>>create table aq7_t1 (
+>  pk int not null not droppable,
+>  weekday int,
+>  primary key (pk))
+>  store by primary key;

--- SQL operation complete.
>>
>>create table aq7_t2 (
+>  a int not null not droppable,
+>  b int,
+>  primary key (a))
+>  store by primary key;

--- SQL operation complete.
>>
>>create mv aq7_mv1 
+>  REFRESH BY USER INITIALIZE BY USER ENABLE QUERY REWRITE
+>  as  select (case weekday
+>              when 1 then 'Sunday'
+>              when 2 then 'Monday'
+>              when 3 then 'Tuesday'
+>              when 4 then 'Wednesday'
+>              when 5 then 'Thursday'
+>              when 6 then 'Friday'
+>              when 7 then 'Saturday'
+>              end) day_name,
+>             count(*) count_star,
+>	     sum(b) sum_b
+>      from aq7_t1, aq7_t2
+>      where weekday = a
+>      group by 1;

--- SQL operation complete.
>>
>>set pattern $$MVName$$ AQ7_MV1;
>>obey TEST003(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.OUTPUTS.AQ7_MV1
  </Table>
  <Misc isIncremental='0' isUMV='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212223874023349500' numCols='2'>
          CAT.OUTPUTS.AQ7_T1
        </Table>
        <Table id='T1' TS='212223874023536985' numCols='2'>
          CAT.OUTPUTS.AQ7_T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J142'>
          <Column ref='C94'>
          </Column>
          <Column id='C98' tableId='T1' colIndex='0' isNullable='0'>
            CAT.OUTPUTS.AQ7_T2.A
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O134' name='DAY_NAME'>
        <Expr ref='X134'>
        </Expr>
      </Output>
      <Output id='O136' name='COUNT_STAR'>
        <Expr id='X136'>
          <Function id='S136' op='count' aggregateFunc='1'>
            <NumericVal id='S135' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O137' name='SUM_B'>
        <Expr id='X137'>
          <Function id='S137' op='sum' aggregateFunc='3'>
            <Column id='C99' tableId='T1' colIndex='1'>
              CAT.OUTPUTS.AQ7_T2.B
            </Column>
          </Function>
        </Expr>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Expr id='X134'>
          <Function id='S134' op='case'>
            <Function id='S133' op='if_then_else'>
              <BinaryOper id='S106' op='='>
                <Column id='C94' tableId='T2' colIndex='1'>
                  CAT.OUTPUTS.AQ7_T1.WEEKDAY
                </Column>
                <NumericVal id='S105' scale='0'>
                  1
                </NumericVal>
              </BinaryOper>
              <StringVal id='S107'><![CDATA['Sunday']]></StringVal>
              <Function id='S132' op='if_then_else'>
                <BinaryOper id='S109' op='='>
                  <Column ref='C94'>
                  </Column>
                  <NumericVal id='S108' scale='0'>
                    2
                  </NumericVal>
                </BinaryOper>
                <StringVal id='S110'><![CDATA['Monday']]></StringVal>
                <Function id='S131' op='if_then_else'>
                  <BinaryOper id='S112' op='='>
                    <Column ref='C94'>
                    </Column>
                    <NumericVal id='S111' scale='0'>
                      3
                    </NumericVal>
                  </BinaryOper>
                  <StringVal id='S113'><![CDATA['Tuesday']]></StringVal>
                  <Function id='S130' op='if_then_else'>
                    <BinaryOper id='S115' op='='>
                      <Column ref='C94'>
                      </Column>
                      <NumericVal id='S114' scale='0'>
                        4
                      </NumericVal>
                    </BinaryOper>
                    <StringVal id='S116'><![CDATA['Wednesday']]></StringVal>
                    <Function id='S129' op='if_then_else'>
                      <BinaryOper id='S118' op='='>
                        <Column ref='C94'>
                        </Column>
                        <NumericVal id='S117' scale='0'>
                          5
                        </NumericVal>
                      </BinaryOper>
                      <StringVal id='S119'><![CDATA['Thursday']]></StringVal>
                      <Function id='S128' op='if_then_else'>
                        <BinaryOper id='S121' op='='>
                          <Column ref='C94'>
                          </Column>
                          <NumericVal id='S120' scale='0'>
                            6
                          </NumericVal>
                        </BinaryOper>
                        <StringVal id='S122'><![CDATA['Friday']]></StringVal>
                        <Function id='S127' op='if_then_else'>
                          <BinaryOper id='S124' op='='>
                            <Column ref='C94'>
                            </Column>
                            <NumericVal id='S123' scale='0'>
                              7
                            </NumericVal>
                          </BinaryOper>
                          <StringVal id='S125'><![CDATA['Saturday']]></StringVal>
                          <NullVal/>
                        </Function>
                      </Function>
                    </Function>
                  </Function>
                </Function>
              </Function>
            </Function>
          </Function>
        </Expr>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>prepare QueryStmt from
+>  select (case weekday
+>          when 1 then 'Sunday'
+>          when 2 then 'Monday'
+>          when 3 then 'Tuesday'
+>          when 4 then 'Wednesday'
+>          when 5 then 'Thursday'
+>          when 6 then 'Friday'
+>          when 7 then 'Saturday'
+>          end) day_name,
+>         count(*) count_star,
+>         sum(b) sum_b
+>  from aq7_t1, aq7_t2
+>  where weekday = a
+>  group by 1
+>  order by 1;

--- SQL command prepared.
>>
>>set pattern $$QueryName$$ OUTPUTS_AQ7_Q;
>>obey TEST003(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='4'>                                                       
  </Misc>                                                                       
  <JBB id='B0'>                                                                 
    <Hub>                                                                       
      <JBBCList>                                                                
        <Table id='T2' TS='212223874023349500' numCols='2'>                     
          CAT.OUTPUTS.AQ7_T1                                                    
          <Key>                                                                 
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>           
              CAT.OUTPUTS.AQ7_T1.PK                                             
            </Column>                                                           
          </Key>                                                                
        </Table>                                                                
        <Table id='T1' TS='212223874023536985' numCols='2'>                     
          CAT.OUTPUTS.AQ7_T2                                                    
          <Key>                                                                 
            <Column id='C6' tableId='T1' colIndex='0' isNullable='0'>           
              CAT.OUTPUTS.AQ7_T2.A                                              
            </Column>                                                           
          </Key>                                                                
        </Table>                                                                
      </JBBCList>                                                               
      <JoinPredList>                                                            
        <JoinPred id='J50'>                                                     
          <Column ref='C2'>                                                     
          </Column>                                                             
          <Column ref='C6'>                                                     
          </Column>                                                             
        </JoinPred>                                                             
      </JoinPredList>                                                           
    </Hub>                                                                      
    <ExtraHub>                                                                  
    </ExtraHub>                                                                 
    <OutputList>                                                                
      <Output id='O42'>                                                         
        <Expr ref='X42'>                                                        
        </Expr>                                                                 
      </Output>                                                                 
      <Output id='O44'>                                                         
        <Expr id='X44'>                                                         
          <Function id='S44' op='count' aggregateFunc='1'>                      
            <NumericVal id='S43' scale='0'>                                     
              1                                                                 
            </NumericVal>                                                       
          </Function>                                                           
        </Expr>                                                                 
      </Output>                                                                 
      <Output id='O45'>                                                         
        <Expr id='X45'>                                                         
          <Function id='S45' op='sum' aggregateFunc='3'>                        
            <Column id='C7' tableId='T1' colIndex='1'>                          
              CAT.OUTPUTS.AQ7_T2.B                                              
            </Column>                                                           
          </Function>                                                           
        </Expr>                                                                 
      </Output>                                                                 
    </OutputList>                                                               
    <GroupBy id='G3'>                                                           
      <Primary>                                                                 
        <Expr id='X42'>                                                         
          <Function id='S42' op='case'>                                         
            <Function id='S41' op='if_then_else'>                               
              <BinaryOper id='S14' op='='>                                      
                <Column id='C2' tableId='T2' colIndex='1'>                      
                  CAT.OUTPUTS.AQ7_T1.WEEKDAY                                    
                </Column>                                                       
                <NumericVal id='S13' scale='0'>                                 
                  1                                                             
                </NumericVal>                                                   
              </BinaryOper>                                                     
              <StringVal id='S15'><![CDATA['Sunday']]></StringVal>              
              <Function id='S40' op='if_then_else'>                             
                <BinaryOper id='S17' op='='>                                    
                  <Column ref='C2'>                                             
                  </Column>                                                     
                  <NumericVal id='S16' scale='0'>                               
                    2                                                           
                  </NumericVal>                                                 
                </BinaryOper>                                                   
                <StringVal id='S18'><![CDATA['Monday']]></StringVal>            
                <Function id='S39' op='if_then_else'>                           
                  <BinaryOper id='S20' op='='>                                  
                    <Column ref='C2'>                                           
                    </Column>                                                   
                    <NumericVal id='S19' scale='0'>                             
                      3                                                         
                    </NumericVal>                                               
                  </BinaryOper>                                                 
                  <StringVal id='S21'><![CDATA['Tuesday']]></StringVal>         
                  <Function id='S38' op='if_then_else'>                         
                    <BinaryOper id='S23' op='='>                                
                      <Column ref='C2'>                                         
                      </Column>                                                 
                      <NumericVal id='S22' scale='0'>                           
                        4                                                       
                      </NumericVal>                                             
                    </BinaryOper>                                               
                    <StringVal id='S24'><![CDATA['Wednesday']]></StringVal>     
                    <Function id='S37' op='if_then_else'>                       
                      <BinaryOper id='S26' op='='>                              
                        <Column ref='C2'>                                       
                        </Column>                                               
                        <NumericVal id='S25' scale='0'>                         
                          5                                                     
                        </NumericVal>                                           
                      </BinaryOper>                                             
                      <StringVal id='S27'><![CDATA['Thursday']]></StringVal>    
                      <Function id='S36' op='if_then_else'>                     
                        <BinaryOper id='S29' op='='>                            
                          <Column ref='C2'>                                     
                          </Column>                                             
                          <NumericVal id='S28' scale='0'>                       
                            6                                                   
                          </NumericVal>                                         
                        </BinaryOper>                                           
                        <StringVal id='S30'><![CDATA['Friday']]></StringVal>    
                        <Function id='S35' op='if_then_else'>                   
                          <BinaryOper id='S32' op='='>                          
                            <Column ref='C2'>                                   
                            </Column>                                           
                            <NumericVal id='S31' scale='0'>                     
                              7                                                 
                            </NumericVal>                                       
                          </BinaryOper>                                         
                          <StringVal id='S33'><![CDATA['Saturday']]></StringVal>
                          <NullVal/>                                            
                        </Function>                                             
                      </Function>                                               
                    </Function>                                                 
                  </Function>                                                   
                </Function>                                                     
              </Function>                                                       
            </Function>                                                         
          </Function>                                                           
        </Expr>                                                                 
      </Primary>                                                                
    </GroupBy>                                                                  
  </JBB>                                                                        
</Query>                                                                        

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE                 >> outputs_a7.in;
>>sh echo PUBLISH AQ7_MV1.xml        >> outputs_a7.in;
>>sh echo MATCH OUTPUTS_AQ7_Q.xml    >> outputs_a7.in;
>>
>>sh sh -c "$QMS outputs_a7.in outputs.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T2'>
          CAT.OUTPUTS.AQ7_T1
        </Table>
        <Table ref='T1'>
          CAT.OUTPUTS.AQ7_T2
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.OUTPUTS.AQ7_MV1
          </MVName>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O42' result='Provided'>
              <MVColumn ref='X42'>
                DAY_NAME
              </MVColumn>
            </Output>
            <Output ref='O44' result='Provided'>
              <MVColumn ref='X44'>
                COUNT_STAR
              </MVColumn>
            </Output>
            <Output ref='O45' result='Provided'>
              <MVColumn ref='X45'>
                SUM_B
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>cqd mvqr_rewrite_candidates 'CAT.TEMP.AQ7_MV1';

--- SQL operation complete.
>>prepare QueryStmt from
+>  select (case weekday
+>          when 1 then 'Sunday'
+>          when 2 then 'Monday'
+>          when 3 then 'Tuesday'
+>          when 4 then 'Wednesday'
+>          when 5 then 'Thursday'
+>          when 6 then 'Friday'
+>          when 7 then 'Saturday'
+>          end) day_name,
+>         count(*) count_star,
+>         sum(b) sum_b
+>  from aq7_t1, aq7_t2
+>  where weekday = a
+>  group by 1;

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

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

FILE_SCAN                       CAT.OUTPUTS.AQ7_MV1                                         

--- 1 row(s) selected.
>>
>>
>>obey TEST003(match_fn);
>>--===========================================
>>--== Test functions in query select lists that use output columns from the MV.
>>--===========================================
>>cqd MULTI_JOIN_THRESHOLD '2';

--- SQL operation complete.
>>create table fn_t1(i1 int not null not droppable primary key, ch1 char(20), dt1 date);

--- SQL operation complete.
>>create table fn_t2(i2 int not null not droppable primary key, ch2 char(20), dt2 date);

--- SQL operation complete.
>>create mv fn_mv1 refresh on request initialize on create as
+>    select i1, ch1, dt1 from fn_t1,fn_t2 where i1=i2;

--- SQL operation complete.
>>
>>cqd mvqr_rewrite_candidates 'CAT.OUTPUTS.FN_MV1';

--- SQL operation complete.
>>
>>prepare QueryStmt from select trim(ch1) from fn_t1,fn_t2 where i1=i2;

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

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

FILE_SCAN                       CAT.OUTPUTS.FN_MV1                                          

--- 1 row(s) selected.
>>
>>prepare QueryStmt from select dateformat(dt1,USA) from fn_t1,fn_t2 where i1=i2;

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

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

FILE_SCAN                       CAT.OUTPUTS.FN_MV1                                          

--- 1 row(s) selected.
>>
>>prepare QueryStmt from select translate(ch1 using ISO88591TOUCS2) from fn_t1,fn_t2 where i1=i2;

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

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

FILE_SCAN                       CAT.OUTPUTS.FN_MV1                                          

--- 1 row(s) selected.
>>
>>cqd MULTI_JOIN_THRESHOLD reset;

--- SQL operation complete.
>>drop mv fn_mv1;

--- SQL operation complete.
>>drop table fn_t1;

--- SQL operation complete.
>>drop table fn_t2;

--- SQL operation complete.
>>
>>
>>obey TEST003(clean_up);
>>----------------------------------------------------
>>------------------ clean up section ----------------
>>----------------------------------------------------
>>drop schema outputs cascade;

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

End of MXCI Session

