>>obey TEST010(set_up);
>>----------------------------------------------------
>>-------------------- set up section ----------------
>>----------------------------------------------------
>>
>>control query default MVQR_REWRITE_LEVEL '3';

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

--- SQL operation complete.
>>control query default QUERY_CACHE '0';

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

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

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

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

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

--- SQL operation complete.
>>set pattern $$MVSchemaName$$ TEST010;
>>
>>create table T010_FACT (
+>	g1   int,
+>	g2   float,
+>	dimkey int
+>	) no partition;

--- SQL operation complete.
>>
>>create table T010_DIM (
+>        pkey int not null primary key, 
+>        dimdata1 int,
+>        dimdata2 char(10)
+>        ) store by primary key no partition;

--- SQL operation complete.
>>
>>insert into T010_FACT values
+>  (2, 6, 5), (7, 6, 8), (4, 7, 5), (9, 5, 5), (5, 5, 3),  -- Random data
+>  (2, 1, 9), (5, 8, 8), (4, 4, 6), (3, 2, 5), (9, 1, 6),
+>  (2, 4, 7), (7, 1, 9), (6, 3, 3), (1, 9, 8), (5, 3, 1),
+>  (8, 8, 2), (1, 9, 8), (8, 9, 4), (8, 1, 7), (4, 6, 4),
+>  (1, 4, 5), (3, 7, 6), (6, 7, 2), (6, 1, 2), (9, 5, 3),
+>  (4, 5, 4), (8, 6, 5), (8, 8, 8), (8, 6, 9), (1, 6, 4),
+>  (7, 5, 7), (5, 6, 2), (8, 3, 8), (2, 1, 4), (2, 1, 5),
+>  (6, 2, 5), (2, 7, 9), (3, 9, 7), (3, 5, 1), (1, 8, 4),
+>  (3, 3, 4), (9, 1, 4), (2, 1, 9), (8, 6, 6), (9, 8, 1),
+>  (5, 1, 9), (2, 9, 3), (6, 3, 2), (9, 4, 9), (2, 5, 8),
+>  (1, 1, 1), (1, 1, 1), (1, 1, 1),                       -- Higher count data
+>  (1, 2, 1), (1, 2, 1), (1, 2, 1), (1, 2, 1), (1, 2, 1), 
+>  (1, 3, 1), (1, 3, 1), (1, 3, 1), (1, 3, 1), (1, 3, 1), (1, 3, 1), (1, 3, 1), 
+>  (1, 4, 1), (1, 4, 1), (1, 4, 1), (1, 4, 1), (1, 4, 1), (1, 4, 1), (1, 4, 1), (1, 4, 1), (1, 4, 1);

--- 74 row(s) inserted.
>>insert into T010_FACT(g1)         values (0);

--- 1 row(s) inserted.
>>           -- Some null values
>>insert into T010_FACT(g1, g2)     values (0, 0);

--- 1 row(s) inserted.
>>insert into T010_FACT(g1, dimkey) values (0, 1);

--- 1 row(s) inserted.
>>insert into T010_FACT(g2)         values (0);

--- 1 row(s) inserted.
>>insert into T010_FACT(g2, dimkey) values (0, 1);

--- 1 row(s) inserted.
>>insert into T010_FACT(dimkey)     values (1);

--- 1 row(s) inserted.
>>
>>insert into T010_DIM(pkey, dimdata1, dimdata2) values
+>  (1, 1, 'abcdef'), (2, 2, 'abcdef'), (3, 3, 'abcdef'), 
+>  (4, 4, 'aNICEf'), (5, 5, 'aNICEf'), (6, 6, 'aNICEf'),
+>  (7, 7, 'aNIECE'), (8, 8, 'aNIECE'), (9, 9, 'aNIECE');

--- 9 row(s) inserted.
>>insert into T010_DIM(pkey) values (10);

--- 1 row(s) inserted.
>>
>>obey TEST010(create_mvs);
>>--===========================================
>>-- Create MVs
>>--===========================================
>>
>>-- MJV
>>create mv having_mjv
+>	recompute
+>	initialized on create
+>	as  select *
+>	    from T010_FACT, T010_DIM
+>	    where dimkey=pkey;

--- SQL operation complete.
>>
>>set pattern $$MVName$$ HAVING_MJV;
>>obey TEST010(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>select * from $$MVName$$;

G1           G2                         DIMKEY       PKEY         DIMDATA1     DIMDATA2
-----------  -------------------------  -----------  -----------  -----------  ----------

          5   3.00000000000000000E+000            1            1            1  abcdef    
          ?                          ?            1            1            1  abcdef    
          ?   0.00000000000000000E+000            1            1            1  abcdef    
          0                          ?            1            1            1  abcdef    
          1   4.00000000000000000E+000            1            1            1  abcdef    
          1   4.00000000000000000E+000            1            1            1  abcdef    
          1   4.00000000000000000E+000            1            1            1  abcdef    
          1   4.00000000000000000E+000            1            1            1  abcdef    
          1   4.00000000000000000E+000            1            1            1  abcdef    
          1   4.00000000000000000E+000            1            1            1  abcdef    
          1   4.00000000000000000E+000            1            1            1  abcdef    
          1   4.00000000000000000E+000            1            1            1  abcdef    
          1   4.00000000000000000E+000            1            1            1  abcdef    
          1   3.00000000000000000E+000            1            1            1  abcdef    
          1   3.00000000000000000E+000            1            1            1  abcdef    
          1   3.00000000000000000E+000            1            1            1  abcdef    
          1   3.00000000000000000E+000            1            1            1  abcdef    
          1   3.00000000000000000E+000            1            1            1  abcdef    
          1   3.00000000000000000E+000            1            1            1  abcdef    
          1   3.00000000000000000E+000            1            1            1  abcdef    
          1   2.00000000000000000E+000            1            1            1  abcdef    
          1   2.00000000000000000E+000            1            1            1  abcdef    
          1   2.00000000000000000E+000            1            1            1  abcdef    
          1   2.00000000000000000E+000            1            1            1  abcdef    
          1   2.00000000000000000E+000            1            1            1  abcdef    
          1   1.00000000000000000E+000            1            1            1  abcdef    
          1   1.00000000000000000E+000            1            1            1  abcdef    
          1   1.00000000000000000E+000            1            1            1  abcdef    
          9   8.00000000000000000E+000            1            1            1  abcdef    
          3   5.00000000000000000E+000            1            1            1  abcdef    
          8   8.00000000000000000E+000            2            2            2  abcdef    
          6   3.00000000000000000E+000            2            2            2  abcdef    
          5   6.00000000000000000E+000            2            2            2  abcdef    
          6   1.00000000000000000E+000            2            2            2  abcdef    
          6   7.00000000000000000E+000            2            2            2  abcdef    
          5   5.00000000000000000E+000            3            3            3  abcdef    
          2   9.00000000000000000E+000            3            3            3  abcdef    
          9   5.00000000000000000E+000            3            3            3  abcdef    
          6   3.00000000000000000E+000            3            3            3  abcdef    
          8   9.00000000000000000E+000            4            4            4  aNICEf    
          9   1.00000000000000000E+000            4            4            4  aNICEf    
          3   3.00000000000000000E+000            4            4            4  aNICEf    
          1   8.00000000000000000E+000            4            4            4  aNICEf    
          2   1.00000000000000000E+000            4            4            4  aNICEf    
          1   6.00000000000000000E+000            4            4            4  aNICEf    
          4   5.00000000000000000E+000            4            4            4  aNICEf    
          4   6.00000000000000000E+000            4            4            4  aNICEf    
          2   6.00000000000000000E+000            5            5            5  aNICEf    
          6   2.00000000000000000E+000            5            5            5  aNICEf    
          2   1.00000000000000000E+000            5            5            5  aNICEf    
          8   6.00000000000000000E+000            5            5            5  aNICEf    
          1   4.00000000000000000E+000            5            5            5  aNICEf    
          3   2.00000000000000000E+000            5            5            5  aNICEf    
          9   5.00000000000000000E+000            5            5            5  aNICEf    
          4   7.00000000000000000E+000            5            5            5  aNICEf    
          4   4.00000000000000000E+000            6            6            6  aNICEf    
          8   6.00000000000000000E+000            6            6            6  aNICEf    
          3   7.00000000000000000E+000            6            6            6  aNICEf    
          9   1.00000000000000000E+000            6            6            6  aNICEf    
          2   4.00000000000000000E+000            7            7            7  aNIECE    
          3   9.00000000000000000E+000            7            7            7  aNIECE    
          7   5.00000000000000000E+000            7            7            7  aNIECE    
          8   1.00000000000000000E+000            7            7            7  aNIECE    
          7   6.00000000000000000E+000            8            8            8  aNIECE    
          2   5.00000000000000000E+000            8            8            8  aNIECE    
          8   3.00000000000000000E+000            8            8            8  aNIECE    
          8   8.00000000000000000E+000            8            8            8  aNIECE    
          1   9.00000000000000000E+000            8            8            8  aNIECE    
          1   9.00000000000000000E+000            8            8            8  aNIECE    
          5   8.00000000000000000E+000            8            8            8  aNIECE    
          2   1.00000000000000000E+000            9            9            9  aNIECE    
          9   4.00000000000000000E+000            9            9            9  aNIECE    
          5   1.00000000000000000E+000            9            9            9  aNIECE    
          2   1.00000000000000000E+000            9            9            9  aNIECE    
          2   7.00000000000000000E+000            9            9            9  aNIECE    
          8   6.00000000000000000E+000            9            9            9  aNIECE    
          7   1.00000000000000000E+000            9            9            9  aNIECE    

--- 77 row(s) selected.
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.TEST010.HAVING_MJV
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874795237497' isKeyCovered='1' numCols='3'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table id='T2' TS='212223874794487708' numCols='4'>
          CAT.TEST010.T010_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J107'>
          <Column id='C89' tableId='T1' colIndex='0' isNullable='0'>
            CAT.TEST010.T010_DIM.PKEY
          </Column>
          <Column ref='C84'>
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O101' name='G1'>
        <Column id='C82' tableId='T2' colIndex='1'>
          CAT.TEST010.T010_FACT.G1
        </Column>
      </Output>
      <Output id='O104' name='G2'>
        <Column id='C83' tableId='T2' colIndex='2'>
          CAT.TEST010.T010_FACT.G2
        </Column>
      </Output>
      <Output id='O107' name='DIMKEY'>
        <Column id='C84' tableId='T2' colIndex='3'>
          CAT.TEST010.T010_FACT.DIMKEY
        </Column>
      </Output>
      <Output id='O113' name='DIMDATA1'>
        <Column id='C90' tableId='T1' colIndex='1'>
          CAT.TEST010.T010_DIM.DIMDATA1
        </Column>
      </Output>
      <Output id='O116' name='DIMDATA2'>
        <Column id='C91' tableId='T1' colIndex='2'>
          CAT.TEST010.T010_DIM.DIMDATA2
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>-- MAV with no HAVING clause
>>create mv having_mav1
+>	recompute
+>	initialized on create
+>	as  select g1, g2, count(*) cnt_star, count(dimdata1) cnt, max(dimdata2) mx
+>	    from T010_FACT, T010_DIM
+>	    where dimkey=pkey
+>	    group by g1, g2;

--- SQL operation complete.
>>
>>set pattern $$MVName$$ HAVING_MAV1;
>>obey TEST010(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>select * from $$MVName$$;

G1           G2                         CNT_STAR              CNT                   MX
-----------  -------------------------  --------------------  --------------------  ----------

          5   3.00000000000000000E+000                     1                     1  abcdef    
          5   6.00000000000000000E+000                     1                     1  abcdef    
          9   1.00000000000000000E+000                     2                     2  aNICEf    
          1   8.00000000000000000E+000                     1                     1  aNICEf    
          1   6.00000000000000000E+000                     1                     1  aNICEf    
          4   5.00000000000000000E+000                     1                     1  aNICEf    
          4   6.00000000000000000E+000                     1                     1  aNICEf    
          4   7.00000000000000000E+000                     1                     1  aNICEf    
          3   7.00000000000000000E+000                     1                     1  aNICEf    
          2   4.00000000000000000E+000                     1                     1  aNIECE    
          9   4.00000000000000000E+000                     1                     1  aNIECE    
          1   4.00000000000000000E+000                    10                    10  abcdef    
          9   8.00000000000000000E+000                     1                     1  abcdef    
          8   8.00000000000000000E+000                     2                     2  abcdef    
          6   1.00000000000000000E+000                     1                     1  abcdef    
          5   5.00000000000000000E+000                     1                     1  abcdef    
          2   9.00000000000000000E+000                     1                     1  abcdef    
          2   1.00000000000000000E+000                     4                     4  aNIECE    
          8   1.00000000000000000E+000                     1                     1  aNIECE    
          7   6.00000000000000000E+000                     1                     1  aNIECE    
          2   5.00000000000000000E+000                     1                     1  aNIECE    
          8   3.00000000000000000E+000                     1                     1  aNIECE    
          1   9.00000000000000000E+000                     2                     2  aNIECE    
          2   7.00000000000000000E+000                     1                     1  aNIECE    
          1   3.00000000000000000E+000                     7                     7  abcdef    
          1   2.00000000000000000E+000                     5                     5  abcdef    
          6   7.00000000000000000E+000                     1                     1  abcdef    
          8   9.00000000000000000E+000                     1                     1  aNICEf    
          3   3.00000000000000000E+000                     1                     1  aNICEf    
          2   6.00000000000000000E+000                     1                     1  aNICEf    
          8   6.00000000000000000E+000                     3                     3  aNIECE    
          3   2.00000000000000000E+000                     1                     1  aNICEf    
          4   4.00000000000000000E+000                     1                     1  aNICEf    
          3   9.00000000000000000E+000                     1                     1  aNIECE    
          7   5.00000000000000000E+000                     1                     1  aNIECE    
          5   1.00000000000000000E+000                     1                     1  aNIECE    
          ?                          ?                     1                     1  abcdef    
          0                          ?                     1                     1  abcdef    
          3   5.00000000000000000E+000                     1                     1  abcdef    
          6   2.00000000000000000E+000                     1                     1  aNICEf    
          7   1.00000000000000000E+000                     1                     1  aNIECE    
          ?   0.00000000000000000E+000                     1                     1  abcdef    
          1   1.00000000000000000E+000                     3                     3  abcdef    
          6   3.00000000000000000E+000                     2                     2  abcdef    
          9   5.00000000000000000E+000                     2                     2  abcdef    
          5   8.00000000000000000E+000                     1                     1  aNIECE    

--- 46 row(s) selected.
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.TEST010.HAVING_MAV1
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874795237497' numCols='3'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table id='T2' TS='212223874794487708' numCols='4'>
          CAT.TEST010.T010_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J115'>
          <Column id='C93' tableId='T1' colIndex='0' isNullable='0'>
            CAT.TEST010.T010_DIM.PKEY
          </Column>
          <Column id='C88' tableId='T2' colIndex='3'>
            CAT.TEST010.T010_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O102' name='CNT_STAR'>
        <Expr id='X102'>
          <Function id='S102' op='count' aggregateFunc='1'>
            <NumericVal id='S101' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O103' name='CNT'>
        <Expr id='X103'>
          <Function id='S103' op='count_nonull' aggregateFunc='2'>
            <Column id='C94' tableId='T1' colIndex='1'>
              CAT.TEST010.T010_DIM.DIMDATA1
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O104' name='MX'>
        <Expr id='X104'>
          <Function id='S104' op='max' aggregateFunc='5'>
            <Column id='C95' tableId='T1' colIndex='2'>
              CAT.TEST010.T010_DIM.DIMDATA2
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O109' name='G1'>
        <Column ref='C86'>
        </Column>
      </Output>
      <Output id='O112' name='G2'>
        <Column ref='C87'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C86' tableId='T2' colIndex='1'>
          CAT.TEST010.T010_FACT.G1
        </Column>
        <Column id='C87' tableId='T2' colIndex='2'>
          CAT.TEST010.T010_FACT.G2
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>-- MAV with range HAVING clause
>>create mv having_mav2
+>	recompute
+>	initialized on create
+>	as  select g1, g2, count(*) cnt_star, count(dimdata1) cnt
+>	    from T010_FACT, T010_DIM
+>	    where dimkey=pkey
+>	    group by g1, g2
+>	    having count(dimdata1) between 2 and 8;

--- SQL operation complete.
>>
>>set pattern $$MVName$$ HAVING_MAV2;
>>obey TEST010(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>select * from $$MVName$$;

G1           G2                         CNT_STAR              CNT
-----------  -------------------------  --------------------  --------------------

          9   1.00000000000000000E+000                     2                     2
          2   1.00000000000000000E+000                     4                     4
          1   9.00000000000000000E+000                     2                     2
          8   8.00000000000000000E+000                     2                     2
          8   6.00000000000000000E+000                     3                     3
          1   2.00000000000000000E+000                     5                     5
          1   3.00000000000000000E+000                     7                     7
          9   5.00000000000000000E+000                     2                     2
          6   3.00000000000000000E+000                     2                     2
          1   1.00000000000000000E+000                     3                     3

--- 10 row(s) selected.
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.TEST010.HAVING_MAV2
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874795237497' numCols='3'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table id='T2' TS='212223874794487708' numCols='4'>
          CAT.TEST010.T010_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J122'>
          <Column id='C97' tableId='T1' colIndex='0' isNullable='0'>
            CAT.TEST010.T010_DIM.PKEY
          </Column>
          <Column id='C92' tableId='T2' colIndex='3'>
            CAT.TEST010.T010_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R148' sqlType='LARGEINT NO NULLS'>
          <Expr id='X105'>
            <Function id='S105' op='count_nonull' aggregateFunc='2'>
              <Column ref='C98'>
              </Column>
            </Function>
          </Expr>
          <OpBT startIsIncluded='1' endIsIncluded='1'>
            <NumericVal scale='0'>
              2
            </NumericVal>
            <NumericVal scale='0'>
              8
            </NumericVal>
          </OpBT>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O110' name='CNT_STAR'>
        <Expr id='X110'>
          <Function id='S110' op='count' aggregateFunc='1'>
            <NumericVal id='S109' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O111' name='CNT'>
        <Expr id='X111'>
          <Function id='S111' op='count_nonull' aggregateFunc='2'>
            <Column id='C98' tableId='T1' colIndex='1'>
              CAT.TEST010.T010_DIM.DIMDATA1
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O116' name='G1'>
        <Column ref='C90'>
        </Column>
      </Output>
      <Output id='O119' name='G2'>
        <Column ref='C91'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C90' tableId='T2' colIndex='1'>
          CAT.TEST010.T010_FACT.G1
        </Column>
        <Column id='C91' tableId='T2' colIndex='2'>
          CAT.TEST010.T010_FACT.G2
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>-- MAV with residual HAVING clause
>>create mv having_mav3
+>	recompute
+>	initialized on create
+>	as  select g1, g2, count(*) cnt_star, max(dimdata2) mx
+>	    from T010_FACT, T010_DIM
+>	    where dimkey=pkey
+>	    group by g1, g2
+>	    having max(dimdata2) like '%NICE%';

--- SQL operation complete.
>>
>>set pattern $$MVName$$ HAVING_MAV3;
>>obey TEST010(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>select * from $$MVName$$;

G1           G2                         CNT_STAR              MX        
-----------  -------------------------  --------------------  ----------

          4   7.00000000000000000E+000                     1  aNICEf    
          9   1.00000000000000000E+000                     2  aNICEf    
          4   6.00000000000000000E+000                     1  aNICEf    
          3   7.00000000000000000E+000                     1  aNICEf    
          4   5.00000000000000000E+000                     1  aNICEf    
          1   6.00000000000000000E+000                     1  aNICEf    
          1   8.00000000000000000E+000                     1  aNICEf    
          2   6.00000000000000000E+000                     1  aNICEf    
          4   4.00000000000000000E+000                     1  aNICEf    
          3   2.00000000000000000E+000                     1  aNICEf    
          8   9.00000000000000000E+000                     1  aNICEf    
          3   3.00000000000000000E+000                     1  aNICEf    
          6   2.00000000000000000E+000                     1  aNICEf    

--- 13 row(s) selected.
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.TEST010.HAVING_MAV3
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874795237497' numCols='3' residualBits='00000002'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table id='T2' TS='212223874794487708' numCols='4'>
          CAT.TEST010.T010_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J115'>
          <Column id='C91' tableId='T1' colIndex='0' isNullable='0'>
            CAT.TEST010.T010_DIM.PKEY
          </Column>
          <Column id='C86' tableId='T2' colIndex='3'>
            CAT.TEST010.T010_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
      <ResidualPredList>
        <Residual id='D101'>
          <Function id='S101' op='like'>
            <Function id='S99' op='max' aggregateFunc='5'>
              <Column ref='C93'>
              </Column>
            </Function>
            <StringVal id='S100'><![CDATA['%NICE%']]></StringVal>
          </Function>
        </Residual>
      </ResidualPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O103' name='CNT_STAR'>
        <Expr id='X103'>
          <Function id='S103' op='count' aggregateFunc='1'>
            <NumericVal id='S102' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O104' name='MX'>
        <Expr id='X104'>
          <Function id='S104' op='max' aggregateFunc='5'>
            <Column id='C93' tableId='T1' colIndex='2'>
              CAT.TEST010.T010_DIM.DIMDATA2
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O109' name='G1'>
        <Column ref='C84'>
        </Column>
      </Output>
      <Output id='O112' name='G2'>
        <Column ref='C85'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C84' tableId='T2' colIndex='1'>
          CAT.TEST010.T010_FACT.G1
        </Column>
        <Column id='C85' tableId='T2' colIndex='2'>
          CAT.TEST010.T010_FACT.G2
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MJV, :CAT.TEST010.HAVING_MAV1, :CAT.TEST010.HAVING_MAV2, :CAT.TEST010.HAVING_MAV3';

--- SQL operation complete.
>>
>>obey TEST010(match_no_having);
>>--===========================================
>>-- Query 1, No HAVING_predicates
>>-- Should match MJV1, MAV1
>>--===========================================
>>set pattern $$QueryName$$ HAVING_NONE;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  order by g1, g2;

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

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

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874795237497' numCols='3'>          
          CAT.TEST010.T010_DIM                                       
          <Key>                                                      
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_DIM.PKEY                              
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
        <Table id='T2' TS='212223874794487708' numCols='4'>          
          CAT.TEST010.T010_FACT                                      
          <Key>                                                      
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_FACT.SYSKEY                           
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
      <JoinPredList>                                                 
        <JoinPred id='J28'>                                          
          <Column ref='C9'>                                          
          </Column>                                                  
          <Column id='C4' tableId='T2' colIndex='3'>                 
            CAT.TEST010.T010_FACT.DIMKEY                             
          </Column>                                                  
        </JoinPred>                                                  
      </JoinPredList>                                                
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O17'>                                              
        <Expr id='X17'>                                              
          <Function id='S17' op='count_nonull' aggregateFunc='2'>    
            <Column id='C10' tableId='T1' colIndex='1'>              
              CAT.TEST010.T010_DIM.DIMDATA1                          
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O22'>                                              
        <Column ref='C2'>                                            
        </Column>                                                    
      </Output>                                                      
      <Output id='O25'>                                              
        <Column ref='C3'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G3'>                                                
      <Primary>                                                      
        <Column id='C2' tableId='T2' colIndex='1'>                   
          CAT.TEST010.T010_FACT.G1                                   
        </Column>                                                    
        <Column id='C3' tableId='T2' colIndex='2'>                   
          CAT.TEST010.T010_FACT.G2                                   
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE > having1.in;
>>sh echo MATCH HAVING_NONE.xml >> having1.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS having1.in having.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874796846419'>
            CAT.TEST010.HAVING_MJV
          </MVName>
          <OutputList>
            <Output ref='C10' result='Provided'>
              <MVColumn ref='C10'>
                DIMDATA1
              </MVColumn>
            </Output>
            <Output ref='O22' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O25' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874811311094'>
            CAT.TEST010.HAVING_MAV1
          </MVName>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O17' result='Provided'>
              <MVColumn ref='X17'>
                CNT
              </MVColumn>
            </Output>
            <Output ref='O22' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O25' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV2 was disqualified because the MV has range predicates, and the query does not.]]></Info>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV3 was disqualified because Table CAT.TEST010.T010_DIM failed on residual bitmap.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify MJV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MJV';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MJV                                      

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          0                          ?                     1
          1   1.00000000000000000E+000                     3
          1   2.00000000000000000E+000                     5
          1   3.00000000000000000E+000                     7
          1   4.00000000000000000E+000                    10
          1   6.00000000000000000E+000                     1
          1   8.00000000000000000E+000                     1
          1   9.00000000000000000E+000                     2
          2   1.00000000000000000E+000                     4
          2   4.00000000000000000E+000                     1
          2   5.00000000000000000E+000                     1
          2   6.00000000000000000E+000                     1
          2   7.00000000000000000E+000                     1
          2   9.00000000000000000E+000                     1
          3   2.00000000000000000E+000                     1
          3   3.00000000000000000E+000                     1
          3   5.00000000000000000E+000                     1
          3   7.00000000000000000E+000                     1
          3   9.00000000000000000E+000                     1
          4   4.00000000000000000E+000                     1
          4   5.00000000000000000E+000                     1
          4   6.00000000000000000E+000                     1
          4   7.00000000000000000E+000                     1
          5   1.00000000000000000E+000                     1
          5   3.00000000000000000E+000                     1
          5   5.00000000000000000E+000                     1
          5   6.00000000000000000E+000                     1
          5   8.00000000000000000E+000                     1
          6   1.00000000000000000E+000                     1
          6   2.00000000000000000E+000                     1
          6   3.00000000000000000E+000                     2
          6   7.00000000000000000E+000                     1
          7   1.00000000000000000E+000                     1
          7   5.00000000000000000E+000                     1
          7   6.00000000000000000E+000                     1
          8   1.00000000000000000E+000                     1
          8   3.00000000000000000E+000                     1
          8   6.00000000000000000E+000                     3
          8   8.00000000000000000E+000                     2
          8   9.00000000000000000E+000                     1
          9   1.00000000000000000E+000                     2
          9   4.00000000000000000E+000                     1
          9   5.00000000000000000E+000                     2
          9   8.00000000000000000E+000                     1
          ?   0.00000000000000000E+000                     1
          ?                          ?                     1

--- 46 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          0                          ?                     1
          1   1.00000000000000000E+000                     3
          1   2.00000000000000000E+000                     5
          1   3.00000000000000000E+000                     7
          1   4.00000000000000000E+000                    10
          1   6.00000000000000000E+000                     1
          1   8.00000000000000000E+000                     1
          1   9.00000000000000000E+000                     2
          2   1.00000000000000000E+000                     4
          2   4.00000000000000000E+000                     1
          2   5.00000000000000000E+000                     1
          2   6.00000000000000000E+000                     1
          2   7.00000000000000000E+000                     1
          2   9.00000000000000000E+000                     1
          3   2.00000000000000000E+000                     1
          3   3.00000000000000000E+000                     1
          3   5.00000000000000000E+000                     1
          3   7.00000000000000000E+000                     1
          3   9.00000000000000000E+000                     1
          4   4.00000000000000000E+000                     1
          4   5.00000000000000000E+000                     1
          4   6.00000000000000000E+000                     1
          4   7.00000000000000000E+000                     1
          5   1.00000000000000000E+000                     1
          5   3.00000000000000000E+000                     1
          5   5.00000000000000000E+000                     1
          5   6.00000000000000000E+000                     1
          5   8.00000000000000000E+000                     1
          6   1.00000000000000000E+000                     1
          6   2.00000000000000000E+000                     1
          6   3.00000000000000000E+000                     2
          6   7.00000000000000000E+000                     1
          7   1.00000000000000000E+000                     1
          7   5.00000000000000000E+000                     1
          7   6.00000000000000000E+000                     1
          8   1.00000000000000000E+000                     1
          8   3.00000000000000000E+000                     1
          8   6.00000000000000000E+000                     3
          8   8.00000000000000000E+000                     2
          8   9.00000000000000000E+000                     1
          9   1.00000000000000000E+000                     2
          9   4.00000000000000000E+000                     1
          9   5.00000000000000000E+000                     2
          9   8.00000000000000000E+000                     1
          ?   0.00000000000000000E+000                     1
          ?                          ?                     1

--- 46 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>-- Verify MAV1 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MAV1';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV1                                     

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          0                          ?                     1
          1   1.00000000000000000E+000                     3
          1   2.00000000000000000E+000                     5
          1   3.00000000000000000E+000                     7
          1   4.00000000000000000E+000                    10
          1   6.00000000000000000E+000                     1
          1   8.00000000000000000E+000                     1
          1   9.00000000000000000E+000                     2
          2   1.00000000000000000E+000                     4
          2   4.00000000000000000E+000                     1
          2   5.00000000000000000E+000                     1
          2   6.00000000000000000E+000                     1
          2   7.00000000000000000E+000                     1
          2   9.00000000000000000E+000                     1
          3   2.00000000000000000E+000                     1
          3   3.00000000000000000E+000                     1
          3   5.00000000000000000E+000                     1
          3   7.00000000000000000E+000                     1
          3   9.00000000000000000E+000                     1
          4   4.00000000000000000E+000                     1
          4   5.00000000000000000E+000                     1
          4   6.00000000000000000E+000                     1
          4   7.00000000000000000E+000                     1
          5   1.00000000000000000E+000                     1
          5   3.00000000000000000E+000                     1
          5   5.00000000000000000E+000                     1
          5   6.00000000000000000E+000                     1
          5   8.00000000000000000E+000                     1
          6   1.00000000000000000E+000                     1
          6   2.00000000000000000E+000                     1
          6   3.00000000000000000E+000                     2
          6   7.00000000000000000E+000                     1
          7   1.00000000000000000E+000                     1
          7   5.00000000000000000E+000                     1
          7   6.00000000000000000E+000                     1
          8   1.00000000000000000E+000                     1
          8   3.00000000000000000E+000                     1
          8   6.00000000000000000E+000                     3
          8   8.00000000000000000E+000                     2
          8   9.00000000000000000E+000                     1
          9   1.00000000000000000E+000                     2
          9   4.00000000000000000E+000                     1
          9   5.00000000000000000E+000                     2
          9   8.00000000000000000E+000                     1
          ?   0.00000000000000000E+000                     1
          ?                          ?                     1

--- 46 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          0                          ?                     1
          1   1.00000000000000000E+000                     3
          1   2.00000000000000000E+000                     5
          1   3.00000000000000000E+000                     7
          1   4.00000000000000000E+000                    10
          1   6.00000000000000000E+000                     1
          1   8.00000000000000000E+000                     1
          1   9.00000000000000000E+000                     2
          2   1.00000000000000000E+000                     4
          2   4.00000000000000000E+000                     1
          2   5.00000000000000000E+000                     1
          2   6.00000000000000000E+000                     1
          2   7.00000000000000000E+000                     1
          2   9.00000000000000000E+000                     1
          3   2.00000000000000000E+000                     1
          3   3.00000000000000000E+000                     1
          3   5.00000000000000000E+000                     1
          3   7.00000000000000000E+000                     1
          3   9.00000000000000000E+000                     1
          4   4.00000000000000000E+000                     1
          4   5.00000000000000000E+000                     1
          4   6.00000000000000000E+000                     1
          4   7.00000000000000000E+000                     1
          5   1.00000000000000000E+000                     1
          5   3.00000000000000000E+000                     1
          5   5.00000000000000000E+000                     1
          5   6.00000000000000000E+000                     1
          5   8.00000000000000000E+000                     1
          6   1.00000000000000000E+000                     1
          6   2.00000000000000000E+000                     1
          6   3.00000000000000000E+000                     2
          6   7.00000000000000000E+000                     1
          7   1.00000000000000000E+000                     1
          7   5.00000000000000000E+000                     1
          7   6.00000000000000000E+000                     1
          8   1.00000000000000000E+000                     1
          8   3.00000000000000000E+000                     1
          8   6.00000000000000000E+000                     3
          8   8.00000000000000000E+000                     2
          8   9.00000000000000000E+000                     1
          9   1.00000000000000000E+000                     2
          9   4.00000000000000000E+000                     1
          9   5.00000000000000000E+000                     2
          9   8.00000000000000000E+000                     1
          ?   0.00000000000000000E+000                     1
          ?                          ?                     1

--- 46 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>obey TEST010(match_no_having_rollup);
>>--===========================================
>>-- Query 2, No HAVING_predicates, with rollup
>>-- Should match MJV1, MAV1
>>--===========================================
>>set pattern $$QueryName$$ HAVING_NONE_ROLLUP;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  order by g1;

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

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

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874795237497' numCols='3'>          
          CAT.TEST010.T010_DIM                                       
          <Key>                                                      
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_DIM.PKEY                              
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
        <Table id='T2' TS='212223874794487708' numCols='4'>          
          CAT.TEST010.T010_FACT                                      
          <Key>                                                      
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_FACT.SYSKEY                           
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
      <JoinPredList>                                                 
        <JoinPred id='J28'>                                          
          <Column ref='C9'>                                          
          </Column>                                                  
          <Column id='C4' tableId='T2' colIndex='3'>                 
            CAT.TEST010.T010_FACT.DIMKEY                             
          </Column>                                                  
        </JoinPred>                                                  
      </JoinPredList>                                                
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O17'>                                              
        <Expr id='X17'>                                              
          <Function id='S17' op='count_nonull' aggregateFunc='2'>    
            <Column id='C10' tableId='T1' colIndex='1'>              
              CAT.TEST010.T010_DIM.DIMDATA1                          
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O22'>                                              
        <Column ref='C2'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G3'>                                                
      <Primary>                                                      
        <Column id='C2' tableId='T2' colIndex='1'>                   
          CAT.TEST010.T010_FACT.G1                                   
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE > having2.in;
>>sh echo MATCH HAVING_NONE_ROLLUP.xml >> having2.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS having2.in having.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874796846419'>
            CAT.TEST010.HAVING_MJV
          </MVName>
          <OutputList>
            <Output ref='C10' result='Provided'>
              <MVColumn ref='C10'>
                DIMDATA1
              </MVColumn>
            </Output>
            <Output ref='O22' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874811311094'>
            CAT.TEST010.HAVING_MAV1
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O22' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O17' result='NotProvided'>
              <Expr ref='X17'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S17'>
                    CNT
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV2 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV3 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify MJV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MJV';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MJV                                      

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

G1           CNT                 
-----------  --------------------

          0                     1
          1                    29
          2                     9
          3                     5
          4                     4
          5                     5
          6                     5
          7                     3
          8                     8
          9                     6
          ?                     2

--- 11 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           CNT                 
-----------  --------------------

          0                     1
          1                    29
          2                     9
          3                     5
          4                     4
          5                     5
          6                     5
          7                     3
          8                     8
          9                     6
          ?                     2

--- 11 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>-- Verify MAV1 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MAV1';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV1                                     

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

G1           CNT                 
-----------  --------------------

          0                     1
          1                    29
          2                     9
          3                     5
          4                     4
          5                     5
          6                     5
          7                     3
          8                     8
          9                     6
          ?                     2

--- 11 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           CNT                 
-----------  --------------------

          0                     1
          1                    29
          2                     9
          3                     5
          4                     4
          5                     5
          6                     5
          7                     3
          8                     8
          9                     6
          ?                     2

--- 11 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>obey TEST010(match_having_range_match);
>>--===========================================
>>-- Query 3, HAVING is a matching range predicate
>>-- Should match MJV1, MAV1, MAV2
>>--===========================================
>>set pattern $$QueryName$$ HAVING_RANGE_MATCH;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having count(dimdata1) between 2 and 8
+>     and count(*) = 2
+>  order by g1, g2;

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

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

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874795237497' numCols='3'>          
          CAT.TEST010.T010_DIM                                       
          <Key>                                                      
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_DIM.PKEY                              
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
        <Table id='T2' TS='212223874794487708' numCols='4'>          
          CAT.TEST010.T010_FACT                                      
          <Key>                                                      
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_FACT.SYSKEY                           
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
      <JoinPredList>                                                 
        <JoinPred id='J35'>                                          
          <Column ref='C9'>                                          
          </Column>                                                  
          <Column id='C4' tableId='T2' colIndex='3'>                 
            CAT.TEST010.T010_FACT.DIMKEY                             
          </Column>                                                  
        </JoinPred>                                                  
      </JoinPredList>                                                
      <RangePredList>                                                
        <Range id='R60' sqlType='LARGEINT NO NULLS'>                 
          <Expr id='X22'>                                            
            <Function id='S22' op='count' aggregateFunc='1'>         
              <NumericVal id='S21' scale='0'>                        
                1                                                    
              </NumericVal>                                          
            </Function>                                              
          </Expr>                                                    
          <OpEQ>                                                     
            <NumericVal scale='0'>                                   
              2                                                      
            </NumericVal>                                            
          </OpEQ>                                                    
        </Range>                                                     
        <Range id='R65' sqlType='LARGEINT NO NULLS'>                 
          <Expr id='X17'>                                            
            <Function id='S17' op='count_nonull' aggregateFunc='2'>  
              <Column ref='C10'>                                     
              </Column>                                              
            </Function>                                              
          </Expr>                                                    
          <OpBT startIsIncluded='1' endIsIncluded='1'>               
            <NumericVal scale='0'>                                   
              2                                                      
            </NumericVal>                                            
            <NumericVal scale='0'>                                   
              8                                                      
            </NumericVal>                                            
          </OpBT>                                                    
        </Range>                                                     
      </RangePredList>                                               
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O24'>                                              
        <Expr id='X24'>                                              
          <Function id='S24' op='count_nonull' aggregateFunc='2'>    
            <Column id='C10' tableId='T1' colIndex='1'>              
              CAT.TEST010.T010_DIM.DIMDATA1                          
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O29'>                                              
        <Column ref='C2'>                                            
        </Column>                                                    
      </Output>                                                      
      <Output id='O32'>                                              
        <Column ref='C3'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G3'>                                                
      <Primary>                                                      
        <Column id='C2' tableId='T2' colIndex='1'>                   
          CAT.TEST010.T010_FACT.G1                                   
        </Column>                                                    
        <Column id='C3' tableId='T2' colIndex='2'>                   
          CAT.TEST010.T010_FACT.G2                                   
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE > having3.in;
>>sh echo MATCH HAVING_RANGE_MATCH.xml >> having3.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS having3.in having.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874796846419'>
            CAT.TEST010.HAVING_MJV
          </MVName>
          <RangePredList>
            <Range ref='R60' result='NotProvided'>
              <Expr ref='X22'>
                <Function ref='S22' op='count' aggregateFunc='1'>
                  <NumericVal ref='S21' scale='0'>
                    1
                  </NumericVal>
                </Function>
              </Expr>
            </Range>
          </RangePredList>
          <OutputList>
            <Output ref='C10' result='Provided'>
              <MVColumn ref='C10'>
                DIMDATA1
              </MVColumn>
            </Output>
            <Output ref='O29' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O32' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874811311094'>
            CAT.TEST010.HAVING_MAV1
          </MVName>
          <RangePredList>
            <Range ref='R65' result='NotProvided'>
              <Expr ref='X17'>
                <MVColumn ref='S17'>
                  CNT
                </MVColumn>
              </Expr>
            </Range>
            <Range ref='R60' result='NotProvided'>
              <Expr ref='X22'>
                <MVColumn ref='S22'>
                  CNT_STAR
                </MVColumn>
              </Expr>
            </Range>
          </RangePredList>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='X24'>
                CNT
              </MVColumn>
            </Output>
            <Output ref='O29' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O32' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874814279008'>
            CAT.TEST010.HAVING_MAV2
          </MVName>
          <RangePredList>
            <Range ref='R65' result='Provided'>
            </Range>
            <Range ref='R60' result='NotProvided'>
              <Expr ref='X22'>
                <MVColumn ref='S22'>
                  CNT_STAR
                </MVColumn>
              </Expr>
            </Range>
          </RangePredList>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='X24'>
                CNT
              </MVColumn>
            </Output>
            <Output ref='O29' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O32' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV3 was disqualified because Table CAT.TEST010.T010_DIM failed on residual bitmap.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify MJV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MJV';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having count(dimdata1) between 2 and 8
+>     and count(*) = 2
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MJV                                      

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          1   9.00000000000000000E+000                     2
          6   3.00000000000000000E+000                     2
          8   8.00000000000000000E+000                     2
          9   1.00000000000000000E+000                     2
          9   5.00000000000000000E+000                     2

--- 5 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having count(dimdata1) between 2 and 8
+>     and count(*) = 2
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          1   9.00000000000000000E+000                     2
          6   3.00000000000000000E+000                     2
          8   8.00000000000000000E+000                     2
          9   1.00000000000000000E+000                     2
          9   5.00000000000000000E+000                     2

--- 5 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>-- Verify MAV1 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MAV1';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having count(dimdata1) between 2 and 8
+>     and count(*) = 2
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV1                                     

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          1   9.00000000000000000E+000                     2
          6   3.00000000000000000E+000                     2
          8   8.00000000000000000E+000                     2
          9   1.00000000000000000E+000                     2
          9   5.00000000000000000E+000                     2

--- 5 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having count(dimdata1) between 2 and 8
+>     and count(*) = 2
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          1   9.00000000000000000E+000                     2
          6   3.00000000000000000E+000                     2
          8   8.00000000000000000E+000                     2
          9   1.00000000000000000E+000                     2
          9   5.00000000000000000E+000                     2

--- 5 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>-- Verify MAV2 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MAV2';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having count(dimdata1) between 2 and 8
+>     and count(*) = 2
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV2                                     

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          1   9.00000000000000000E+000                     2
          6   3.00000000000000000E+000                     2
          8   8.00000000000000000E+000                     2
          9   1.00000000000000000E+000                     2
          9   5.00000000000000000E+000                     2

--- 5 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having count(dimdata1) between 2 and 8
+>     and count(*) = 2
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          1   9.00000000000000000E+000                     2
          6   3.00000000000000000E+000                     2
          8   8.00000000000000000E+000                     2
          9   1.00000000000000000E+000                     2
          9   5.00000000000000000E+000                     2

--- 5 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>obey TEST010(match_having_range_match_rollup);
>>--===========================================
>>-- Query 4, HAVING is a matching range predicate
>>-- Should match MJV1, MAV1
>>--===========================================
>>set pattern $$QueryName$$ HAVING_RANGE_MATCH_ROLLUP;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 2 and 8
+>     and count(*) = 2
+>  order by g1;

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

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

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874795237497' numCols='3'>          
          CAT.TEST010.T010_DIM                                       
          <Key>                                                      
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_DIM.PKEY                              
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
        <Table id='T2' TS='212223874794487708' numCols='4'>          
          CAT.TEST010.T010_FACT                                      
          <Key>                                                      
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_FACT.SYSKEY                           
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
      <JoinPredList>                                                 
        <JoinPred id='J35'>                                          
          <Column ref='C9'>                                          
          </Column>                                                  
          <Column id='C4' tableId='T2' colIndex='3'>                 
            CAT.TEST010.T010_FACT.DIMKEY                             
          </Column>                                                  
        </JoinPred>                                                  
      </JoinPredList>                                                
      <RangePredList>                                                
        <Range id='R60' sqlType='LARGEINT NO NULLS'>                 
          <Expr id='X22'>                                            
            <Function id='S22' op='count' aggregateFunc='1'>         
              <NumericVal id='S21' scale='0'>                        
                1                                                    
              </NumericVal>                                          
            </Function>                                              
          </Expr>                                                    
          <OpEQ>                                                     
            <NumericVal scale='0'>                                   
              2                                                      
            </NumericVal>                                            
          </OpEQ>                                                    
        </Range>                                                     
        <Range id='R65' sqlType='LARGEINT NO NULLS'>                 
          <Expr id='X17'>                                            
            <Function id='S17' op='count_nonull' aggregateFunc='2'>  
              <Column ref='C10'>                                     
              </Column>                                              
            </Function>                                              
          </Expr>                                                    
          <OpBT startIsIncluded='1' endIsIncluded='1'>               
            <NumericVal scale='0'>                                   
              2                                                      
            </NumericVal>                                            
            <NumericVal scale='0'>                                   
              8                                                      
            </NumericVal>                                            
          </OpBT>                                                    
        </Range>                                                     
      </RangePredList>                                               
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O24'>                                              
        <Expr id='X24'>                                              
          <Function id='S24' op='count_nonull' aggregateFunc='2'>    
            <Column id='C10' tableId='T1' colIndex='1'>              
              CAT.TEST010.T010_DIM.DIMDATA1                          
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O29'>                                              
        <Column ref='C2'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G3'>                                                
      <Primary>                                                      
        <Column id='C2' tableId='T2' colIndex='1'>                   
          CAT.TEST010.T010_FACT.G1                                   
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE > having4.in;
>>sh echo MATCH HAVING_RANGE_MATCH_ROLLUP.xml >> having4.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS having4.in having.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874796846419'>
            CAT.TEST010.HAVING_MJV
          </MVName>
          <RangePredList>
            <Range ref='R60' result='NotProvided'>
              <Expr ref='X22'>
                <Function ref='S22' op='count' aggregateFunc='1'>
                  <NumericVal ref='S21' scale='0'>
                    1
                  </NumericVal>
                </Function>
              </Expr>
            </Range>
          </RangePredList>
          <OutputList>
            <Output ref='C10' result='Provided'>
              <MVColumn ref='C10'>
                DIMDATA1
              </MVColumn>
            </Output>
            <Output ref='O29' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874811311094'>
            CAT.TEST010.HAVING_MAV1
          </MVName>
          <RangePredList>
            <Range ref='R65' result='NotProvided'>
              <Expr ref='X17'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S17'>
                    CNT
                  </MVColumn>
                </Function>
              </Expr>
            </Range>
            <Range ref='R60' result='NotProvided'>
              <Expr ref='X22'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn>
                    CNT_STAR
                  </MVColumn>
                </Function>
              </Expr>
            </Range>
          </RangePredList>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O29' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O24' result='NotProvided'>
              <Expr ref='X24'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S24'>
                    CNT
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV2 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV3 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify MJV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MJV';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 2 and 8
+>     and count(*) = 2
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MJV                                      

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

G1           CNT                 
-----------  --------------------

          ?                     2

--- 1 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 2 and 8
+>     and count(*) = 2
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           CNT                 
-----------  --------------------

          ?                     2

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

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>-- Verify MAV1 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MAV1';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 2 and 8
+>     and count(*) = 2
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV1                                     

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

G1           CNT                 
-----------  --------------------

          ?                     2

--- 1 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 2 and 8
+>     and count(*) = 2
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           CNT                 
-----------  --------------------

          ?                     2

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

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>obey TEST010(match_having_range_match_rollup_on_grouping);
>>--===========================================
>>-- Query 4-1, HAVING is a matching range predicate
>>-- Should match MJV, MAV1
>>--===========================================
>>set pattern $$QueryName$$ HAVING_RANGE_MATCH_ROLLUP_GRP;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having sum(g2) between 10 and 20
+>  order by g1;

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

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

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874795237497' numCols='3'>          
          CAT.TEST010.T010_DIM                                       
          <Key>                                                      
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_DIM.PKEY                              
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
        <Table id='T2' TS='212223874794487708' numCols='4'>          
          CAT.TEST010.T010_FACT                                      
          <Key>                                                      
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_FACT.SYSKEY                           
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
      <JoinPredList>                                                 
        <JoinPred id='J32'>                                          
          <Column ref='C9'>                                          
          </Column>                                                  
          <Column id='C4' tableId='T2' colIndex='3'>                 
            CAT.TEST010.T010_FACT.DIMKEY                             
          </Column>                                                  
        </JoinPred>                                                  
      </JoinPredList>                                                
      <RangePredList>                                                
        <Range id='R58' sqlType='DOUBLE PRECISION ALLOWS NULLS'>     
          <Expr id='X17'>                                            
            <Function id='S17' op='sum' aggregateFunc='3'>           
              <Column id='C3' tableId='T2' colIndex='2'>             
                CAT.TEST010.T010_FACT.G2                             
              </Column>                                              
            </Function>                                              
          </Expr>                                                    
          <OpBT startIsIncluded='1' endIsIncluded='1'>               
            <FloatVal>                                               
              10                                                     
            </FloatVal>                                              
            <FloatVal>                                               
              20                                                     
            </FloatVal>                                              
          </OpBT>                                                    
        </Range>                                                     
      </RangePredList>                                               
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O21'>                                              
        <Expr id='X21'>                                              
          <Function id='S21' op='count_nonull' aggregateFunc='2'>    
            <Column id='C10' tableId='T1' colIndex='1'>              
              CAT.TEST010.T010_DIM.DIMDATA1                          
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O26'>                                              
        <Column ref='C2'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G3'>                                                
      <Primary>                                                      
        <Column id='C2' tableId='T2' colIndex='1'>                   
          CAT.TEST010.T010_FACT.G1                                   
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE > having41.in;
>>sh echo MATCH HAVING_RANGE_MATCH_ROLLUP_GRP.xml >> having41.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS having41.in having.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874796846419'>
            CAT.TEST010.HAVING_MJV
          </MVName>
          <OutputList>
            <Output ref='C3' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
            <Output ref='C10' result='Provided'>
              <MVColumn ref='C10'>
                DIMDATA1
              </MVColumn>
            </Output>
            <Output ref='O26' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874811311094'>
            CAT.TEST010.HAVING_MAV1
          </MVName>
          <RangePredList>
            <Range ref='R58' result='NotProvided'>
              <Expr ref='X17'>
                <Function op='sum on grouping' aggregateFunc='9'>
                  <MVColumn ref='C3'>
                    G2
                  </MVColumn>
                  <MVColumn>
                    CNT_STAR
                  </MVColumn>
                </Function>
              </Expr>
            </Range>
          </RangePredList>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O26' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O21' result='NotProvided'>
              <Expr ref='X21'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S21'>
                    CNT
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV2 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV3 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify MJV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MJV';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having sum(g2) between 10 and 20
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MJV                                      

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

G1           CNT                 
-----------  --------------------

          6                     5
          7                     3

--- 2 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having sum(g2) between 10 and 20
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           CNT                 
-----------  --------------------

          6                     5
          7                     3

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

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>-- Verify MAV1 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MAV1';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having sum(g2) between 10 and 20
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV1                                     

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

G1           CNT                 
-----------  --------------------

          6                     5
          7                     3

--- 2 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having sum(g2) between 10 and 20
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           CNT                 
-----------  --------------------

          6                     5
          7                     3

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

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>obey TEST010(match_having_range_sub);
>>--===========================================
>>-- Query 5, HAVING is a subsumed range predicate
>>-- Should match MJV1, MAV1, MAV2
>>--===========================================
>>set pattern $$QueryName$$ HAVING_RANGE_SUB;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+> select g1, g2, count(dimdata1) cnt
+> from T010_FACT, T010_DIM
+> where dimkey=pkey
+> group by g1, g2
+> having count(dimdata1) between 3 and 8
+> order by g1, g2;

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

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

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874795237497' numCols='3'>          
          CAT.TEST010.T010_DIM                                       
          <Key>                                                      
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_DIM.PKEY                              
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
        <Table id='T2' TS='212223874794487708' numCols='4'>          
          CAT.TEST010.T010_FACT                                      
          <Key>                                                      
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_FACT.SYSKEY                           
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
      <JoinPredList>                                                 
        <JoinPred id='J32'>                                          
          <Column ref='C9'>                                          
          </Column>                                                  
          <Column id='C4' tableId='T2' colIndex='3'>                 
            CAT.TEST010.T010_FACT.DIMKEY                             
          </Column>                                                  
        </JoinPred>                                                  
      </JoinPredList>                                                
      <RangePredList>                                                
        <Range id='R58' sqlType='LARGEINT NO NULLS'>                 
          <Expr id='X17'>                                            
            <Function id='S17' op='count_nonull' aggregateFunc='2'>  
              <Column ref='C10'>                                     
              </Column>                                              
            </Function>                                              
          </Expr>                                                    
          <OpBT startIsIncluded='1' endIsIncluded='1'>               
            <NumericVal scale='0'>                                   
              3                                                      
            </NumericVal>                                            
            <NumericVal scale='0'>                                   
              8                                                      
            </NumericVal>                                            
          </OpBT>                                                    
        </Range>                                                     
      </RangePredList>                                               
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O21'>                                              
        <Expr id='X21'>                                              
          <Function id='S21' op='count_nonull' aggregateFunc='2'>    
            <Column id='C10' tableId='T1' colIndex='1'>              
              CAT.TEST010.T010_DIM.DIMDATA1                          
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O26'>                                              
        <Column ref='C2'>                                            
        </Column>                                                    
      </Output>                                                      
      <Output id='O29'>                                              
        <Column ref='C3'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G3'>                                                
      <Primary>                                                      
        <Column id='C2' tableId='T2' colIndex='1'>                   
          CAT.TEST010.T010_FACT.G1                                   
        </Column>                                                    
        <Column id='C3' tableId='T2' colIndex='2'>                   
          CAT.TEST010.T010_FACT.G2                                   
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE > having5.in;
>>sh echo MATCH HAVING_RANGE_SUB.xml >> having5.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS having5.in having.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874796846419'>
            CAT.TEST010.HAVING_MJV
          </MVName>
          <OutputList>
            <Output ref='C10' result='Provided'>
              <MVColumn ref='C10'>
                DIMDATA1
              </MVColumn>
            </Output>
            <Output ref='O26' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O29' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874811311094'>
            CAT.TEST010.HAVING_MAV1
          </MVName>
          <RangePredList>
            <Range ref='R58' result='NotProvided'>
              <Expr ref='X17'>
                <MVColumn ref='S17'>
                  CNT
                </MVColumn>
              </Expr>
            </Range>
          </RangePredList>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O21' result='Provided'>
              <MVColumn ref='X21'>
                CNT
              </MVColumn>
            </Output>
            <Output ref='O26' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O29' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874814279008'>
            CAT.TEST010.HAVING_MAV2
          </MVName>
          <RangePredList>
            <Range ref='R58' result='NotProvided'>
              <MVColumn ref='X17'>
                CNT
              </MVColumn>
            </Range>
          </RangePredList>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O21' result='Provided'>
              <MVColumn ref='X21'>
                CNT
              </MVColumn>
            </Output>
            <Output ref='O26' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O29' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV3 was disqualified because Table CAT.TEST010.T010_DIM failed on residual bitmap.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify MJV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MJV';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+> select g1, g2, count(dimdata1) cnt
+> from T010_FACT, T010_DIM
+> where dimkey=pkey
+> group by g1, g2
+> having count(dimdata1) between 3 and 8
+> order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MJV                                      

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          1   1.00000000000000000E+000                     3
          1   2.00000000000000000E+000                     5
          1   3.00000000000000000E+000                     7
          2   1.00000000000000000E+000                     4
          8   6.00000000000000000E+000                     3

--- 5 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+> select g1, g2, count(dimdata1) cnt
+> from T010_FACT, T010_DIM
+> where dimkey=pkey
+> group by g1, g2
+> having count(dimdata1) between 3 and 8
+> order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          1   1.00000000000000000E+000                     3
          1   2.00000000000000000E+000                     5
          1   3.00000000000000000E+000                     7
          2   1.00000000000000000E+000                     4
          8   6.00000000000000000E+000                     3

--- 5 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>-- Verify MAV1 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MAV1';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+> select g1, g2, count(dimdata1) cnt
+> from T010_FACT, T010_DIM
+> where dimkey=pkey
+> group by g1, g2
+> having count(dimdata1) between 3 and 8
+> order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV1                                     

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          1   1.00000000000000000E+000                     3
          1   2.00000000000000000E+000                     5
          1   3.00000000000000000E+000                     7
          2   1.00000000000000000E+000                     4
          8   6.00000000000000000E+000                     3

--- 5 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+> select g1, g2, count(dimdata1) cnt
+> from T010_FACT, T010_DIM
+> where dimkey=pkey
+> group by g1, g2
+> having count(dimdata1) between 3 and 8
+> order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          1   1.00000000000000000E+000                     3
          1   2.00000000000000000E+000                     5
          1   3.00000000000000000E+000                     7
          2   1.00000000000000000E+000                     4
          8   6.00000000000000000E+000                     3

--- 5 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>-- Verify MAV2 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MAV2';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+> select g1, g2, count(dimdata1) cnt
+> from T010_FACT, T010_DIM
+> where dimkey=pkey
+> group by g1, g2
+> having count(dimdata1) between 3 and 8
+> order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV2                                     

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          1   1.00000000000000000E+000                     3
          1   2.00000000000000000E+000                     5
          1   3.00000000000000000E+000                     7
          2   1.00000000000000000E+000                     4
          8   6.00000000000000000E+000                     3

--- 5 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+> select g1, g2, count(dimdata1) cnt
+> from T010_FACT, T010_DIM
+> where dimkey=pkey
+> group by g1, g2
+> having count(dimdata1) between 3 and 8
+> order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          1   1.00000000000000000E+000                     3
          1   2.00000000000000000E+000                     5
          1   3.00000000000000000E+000                     7
          2   1.00000000000000000E+000                     4
          8   6.00000000000000000E+000                     3

--- 5 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>obey TEST010(match_having_range_sub_rollup);
>>--===========================================
>>-- Query 6, HAVING is a subsumed range predicate
>>-- Should match MJV1, MAV1
>>--===========================================
>>set pattern $$QueryName$$ HAVING_RANGE_SUB_ROLLUP;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 3 and 8
+>  order by g1;

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

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

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874795237497' numCols='3'>          
          CAT.TEST010.T010_DIM                                       
          <Key>                                                      
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_DIM.PKEY                              
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
        <Table id='T2' TS='212223874794487708' numCols='4'>          
          CAT.TEST010.T010_FACT                                      
          <Key>                                                      
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_FACT.SYSKEY                           
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
      <JoinPredList>                                                 
        <JoinPred id='J32'>                                          
          <Column ref='C9'>                                          
          </Column>                                                  
          <Column id='C4' tableId='T2' colIndex='3'>                 
            CAT.TEST010.T010_FACT.DIMKEY                             
          </Column>                                                  
        </JoinPred>                                                  
      </JoinPredList>                                                
      <RangePredList>                                                
        <Range id='R58' sqlType='LARGEINT NO NULLS'>                 
          <Expr id='X17'>                                            
            <Function id='S17' op='count_nonull' aggregateFunc='2'>  
              <Column ref='C10'>                                     
              </Column>                                              
            </Function>                                              
          </Expr>                                                    
          <OpBT startIsIncluded='1' endIsIncluded='1'>               
            <NumericVal scale='0'>                                   
              3                                                      
            </NumericVal>                                            
            <NumericVal scale='0'>                                   
              8                                                      
            </NumericVal>                                            
          </OpBT>                                                    
        </Range>                                                     
      </RangePredList>                                               
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O21'>                                              
        <Expr id='X21'>                                              
          <Function id='S21' op='count_nonull' aggregateFunc='2'>    
            <Column id='C10' tableId='T1' colIndex='1'>              
              CAT.TEST010.T010_DIM.DIMDATA1                          
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O26'>                                              
        <Column ref='C2'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G3'>                                                
      <Primary>                                                      
        <Column id='C2' tableId='T2' colIndex='1'>                   
          CAT.TEST010.T010_FACT.G1                                   
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE > having6.in;
>>sh echo MATCH HAVING_RANGE_SUB_ROLLUP.xml >> having6.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS having6.in having.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874796846419'>
            CAT.TEST010.HAVING_MJV
          </MVName>
          <OutputList>
            <Output ref='C10' result='Provided'>
              <MVColumn ref='C10'>
                DIMDATA1
              </MVColumn>
            </Output>
            <Output ref='O26' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874811311094'>
            CAT.TEST010.HAVING_MAV1
          </MVName>
          <RangePredList>
            <Range ref='R58' result='NotProvided'>
              <Expr ref='X17'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S17'>
                    CNT
                  </MVColumn>
                </Function>
              </Expr>
            </Range>
          </RangePredList>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O26' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O21' result='NotProvided'>
              <Expr ref='X21'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S21'>
                    CNT
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV2 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV3 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify MJV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MJV';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 3 and 8
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MJV                                      

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

G1           CNT                 
-----------  --------------------

          3                     5
          4                     4
          5                     5
          6                     5
          7                     3
          8                     8
          9                     6

--- 7 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 3 and 8
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           CNT                 
-----------  --------------------

          3                     5
          4                     4
          5                     5
          6                     5
          7                     3
          8                     8
          9                     6

--- 7 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>-- Verify MAV1 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MAV1';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 3 and 8
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV1                                     

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

G1           CNT                 
-----------  --------------------

          3                     5
          4                     4
          5                     5
          6                     5
          7                     3
          8                     8
          9                     6

--- 7 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 3 and 8
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           CNT                 
-----------  --------------------

          3                     5
          4                     4
          5                     5
          6                     5
          7                     3
          8                     8
          9                     6

--- 7 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>obey TEST010(match_having_range_no_sub);
>>--===========================================
>>-- Query 7, HAVING is a none subsumed range predicate
>>-- Should match MJV1, MAV1
>>--===========================================
>>set pattern $$QueryName$$ HAVING_RANGE_NOSUB;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having count(dimdata1) between 1 and 8
+>  order by g1, g2;

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

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

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874795237497' numCols='3'>          
          CAT.TEST010.T010_DIM                                       
          <Key>                                                      
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_DIM.PKEY                              
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
        <Table id='T2' TS='212223874794487708' numCols='4'>          
          CAT.TEST010.T010_FACT                                      
          <Key>                                                      
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_FACT.SYSKEY                           
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
      <JoinPredList>                                                 
        <JoinPred id='J32'>                                          
          <Column ref='C9'>                                          
          </Column>                                                  
          <Column id='C4' tableId='T2' colIndex='3'>                 
            CAT.TEST010.T010_FACT.DIMKEY                             
          </Column>                                                  
        </JoinPred>                                                  
      </JoinPredList>                                                
      <RangePredList>                                                
        <Range id='R58' sqlType='LARGEINT NO NULLS'>                 
          <Expr id='X17'>                                            
            <Function id='S17' op='count_nonull' aggregateFunc='2'>  
              <Column ref='C10'>                                     
              </Column>                                              
            </Function>                                              
          </Expr>                                                    
          <OpBT startIsIncluded='1' endIsIncluded='1'>               
            <NumericVal scale='0'>                                   
              1                                                      
            </NumericVal>                                            
            <NumericVal scale='0'>                                   
              8                                                      
            </NumericVal>                                            
          </OpBT>                                                    
        </Range>                                                     
      </RangePredList>                                               
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O21'>                                              
        <Expr id='X21'>                                              
          <Function id='S21' op='count_nonull' aggregateFunc='2'>    
            <Column id='C10' tableId='T1' colIndex='1'>              
              CAT.TEST010.T010_DIM.DIMDATA1                          
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O26'>                                              
        <Column ref='C2'>                                            
        </Column>                                                    
      </Output>                                                      
      <Output id='O29'>                                              
        <Column ref='C3'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G3'>                                                
      <Primary>                                                      
        <Column id='C2' tableId='T2' colIndex='1'>                   
          CAT.TEST010.T010_FACT.G1                                   
        </Column>                                                    
        <Column id='C3' tableId='T2' colIndex='2'>                   
          CAT.TEST010.T010_FACT.G2                                   
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE > having7.in;
>>sh echo MATCH HAVING_RANGE_NOSUB.xml >> having7.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS having7.in having.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874796846419'>
            CAT.TEST010.HAVING_MJV
          </MVName>
          <OutputList>
            <Output ref='C10' result='Provided'>
              <MVColumn ref='C10'>
                DIMDATA1
              </MVColumn>
            </Output>
            <Output ref='O26' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O29' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874811311094'>
            CAT.TEST010.HAVING_MAV1
          </MVName>
          <RangePredList>
            <Range ref='R58' result='NotProvided'>
              <Expr ref='X17'>
                <MVColumn ref='S17'>
                  CNT
                </MVColumn>
              </Expr>
            </Range>
          </RangePredList>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O21' result='Provided'>
              <MVColumn ref='X21'>
                CNT
              </MVColumn>
            </Output>
            <Output ref='O26' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O29' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV2 was disqualified because The query range predicate R58 is not subsumed by the corresponding MV predicate.]]></Info>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV3 was disqualified because Table CAT.TEST010.T010_DIM failed on residual bitmap.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify MJV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MJV';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having count(dimdata1) between 1 and 8
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MJV                                      

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          0                          ?                     1
          1   1.00000000000000000E+000                     3
          1   2.00000000000000000E+000                     5
          1   3.00000000000000000E+000                     7
          1   6.00000000000000000E+000                     1
          1   8.00000000000000000E+000                     1
          1   9.00000000000000000E+000                     2
          2   1.00000000000000000E+000                     4
          2   4.00000000000000000E+000                     1
          2   5.00000000000000000E+000                     1
          2   6.00000000000000000E+000                     1
          2   7.00000000000000000E+000                     1
          2   9.00000000000000000E+000                     1
          3   2.00000000000000000E+000                     1
          3   3.00000000000000000E+000                     1
          3   5.00000000000000000E+000                     1
          3   7.00000000000000000E+000                     1
          3   9.00000000000000000E+000                     1
          4   4.00000000000000000E+000                     1
          4   5.00000000000000000E+000                     1
          4   6.00000000000000000E+000                     1
          4   7.00000000000000000E+000                     1
          5   1.00000000000000000E+000                     1
          5   3.00000000000000000E+000                     1
          5   5.00000000000000000E+000                     1
          5   6.00000000000000000E+000                     1
          5   8.00000000000000000E+000                     1
          6   1.00000000000000000E+000                     1
          6   2.00000000000000000E+000                     1
          6   3.00000000000000000E+000                     2
          6   7.00000000000000000E+000                     1
          7   1.00000000000000000E+000                     1
          7   5.00000000000000000E+000                     1
          7   6.00000000000000000E+000                     1
          8   1.00000000000000000E+000                     1
          8   3.00000000000000000E+000                     1
          8   6.00000000000000000E+000                     3
          8   8.00000000000000000E+000                     2
          8   9.00000000000000000E+000                     1
          9   1.00000000000000000E+000                     2
          9   4.00000000000000000E+000                     1
          9   5.00000000000000000E+000                     2
          9   8.00000000000000000E+000                     1
          ?   0.00000000000000000E+000                     1
          ?                          ?                     1

--- 45 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having count(dimdata1) between 1 and 8
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          0                          ?                     1
          1   1.00000000000000000E+000                     3
          1   2.00000000000000000E+000                     5
          1   3.00000000000000000E+000                     7
          1   6.00000000000000000E+000                     1
          1   8.00000000000000000E+000                     1
          1   9.00000000000000000E+000                     2
          2   1.00000000000000000E+000                     4
          2   4.00000000000000000E+000                     1
          2   5.00000000000000000E+000                     1
          2   6.00000000000000000E+000                     1
          2   7.00000000000000000E+000                     1
          2   9.00000000000000000E+000                     1
          3   2.00000000000000000E+000                     1
          3   3.00000000000000000E+000                     1
          3   5.00000000000000000E+000                     1
          3   7.00000000000000000E+000                     1
          3   9.00000000000000000E+000                     1
          4   4.00000000000000000E+000                     1
          4   5.00000000000000000E+000                     1
          4   6.00000000000000000E+000                     1
          4   7.00000000000000000E+000                     1
          5   1.00000000000000000E+000                     1
          5   3.00000000000000000E+000                     1
          5   5.00000000000000000E+000                     1
          5   6.00000000000000000E+000                     1
          5   8.00000000000000000E+000                     1
          6   1.00000000000000000E+000                     1
          6   2.00000000000000000E+000                     1
          6   3.00000000000000000E+000                     2
          6   7.00000000000000000E+000                     1
          7   1.00000000000000000E+000                     1
          7   5.00000000000000000E+000                     1
          7   6.00000000000000000E+000                     1
          8   1.00000000000000000E+000                     1
          8   3.00000000000000000E+000                     1
          8   6.00000000000000000E+000                     3
          8   8.00000000000000000E+000                     2
          8   9.00000000000000000E+000                     1
          9   1.00000000000000000E+000                     2
          9   4.00000000000000000E+000                     1
          9   5.00000000000000000E+000                     2
          9   8.00000000000000000E+000                     1
          ?   0.00000000000000000E+000                     1
          ?                          ?                     1

--- 45 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>-- Verify MAV1 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MAV1';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having count(dimdata1) between 1 and 8
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV1                                     

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          0                          ?                     1
          1   1.00000000000000000E+000                     3
          1   2.00000000000000000E+000                     5
          1   3.00000000000000000E+000                     7
          1   6.00000000000000000E+000                     1
          1   8.00000000000000000E+000                     1
          1   9.00000000000000000E+000                     2
          2   1.00000000000000000E+000                     4
          2   4.00000000000000000E+000                     1
          2   5.00000000000000000E+000                     1
          2   6.00000000000000000E+000                     1
          2   7.00000000000000000E+000                     1
          2   9.00000000000000000E+000                     1
          3   2.00000000000000000E+000                     1
          3   3.00000000000000000E+000                     1
          3   5.00000000000000000E+000                     1
          3   7.00000000000000000E+000                     1
          3   9.00000000000000000E+000                     1
          4   4.00000000000000000E+000                     1
          4   5.00000000000000000E+000                     1
          4   6.00000000000000000E+000                     1
          4   7.00000000000000000E+000                     1
          5   1.00000000000000000E+000                     1
          5   3.00000000000000000E+000                     1
          5   5.00000000000000000E+000                     1
          5   6.00000000000000000E+000                     1
          5   8.00000000000000000E+000                     1
          6   1.00000000000000000E+000                     1
          6   2.00000000000000000E+000                     1
          6   3.00000000000000000E+000                     2
          6   7.00000000000000000E+000                     1
          7   1.00000000000000000E+000                     1
          7   5.00000000000000000E+000                     1
          7   6.00000000000000000E+000                     1
          8   1.00000000000000000E+000                     1
          8   3.00000000000000000E+000                     1
          8   6.00000000000000000E+000                     3
          8   8.00000000000000000E+000                     2
          8   9.00000000000000000E+000                     1
          9   1.00000000000000000E+000                     2
          9   4.00000000000000000E+000                     1
          9   5.00000000000000000E+000                     2
          9   8.00000000000000000E+000                     1
          ?   0.00000000000000000E+000                     1
          ?                          ?                     1

--- 45 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having count(dimdata1) between 1 and 8
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           G2                         CNT                 
-----------  -------------------------  --------------------

          0                          ?                     1
          1   1.00000000000000000E+000                     3
          1   2.00000000000000000E+000                     5
          1   3.00000000000000000E+000                     7
          1   6.00000000000000000E+000                     1
          1   8.00000000000000000E+000                     1
          1   9.00000000000000000E+000                     2
          2   1.00000000000000000E+000                     4
          2   4.00000000000000000E+000                     1
          2   5.00000000000000000E+000                     1
          2   6.00000000000000000E+000                     1
          2   7.00000000000000000E+000                     1
          2   9.00000000000000000E+000                     1
          3   2.00000000000000000E+000                     1
          3   3.00000000000000000E+000                     1
          3   5.00000000000000000E+000                     1
          3   7.00000000000000000E+000                     1
          3   9.00000000000000000E+000                     1
          4   4.00000000000000000E+000                     1
          4   5.00000000000000000E+000                     1
          4   6.00000000000000000E+000                     1
          4   7.00000000000000000E+000                     1
          5   1.00000000000000000E+000                     1
          5   3.00000000000000000E+000                     1
          5   5.00000000000000000E+000                     1
          5   6.00000000000000000E+000                     1
          5   8.00000000000000000E+000                     1
          6   1.00000000000000000E+000                     1
          6   2.00000000000000000E+000                     1
          6   3.00000000000000000E+000                     2
          6   7.00000000000000000E+000                     1
          7   1.00000000000000000E+000                     1
          7   5.00000000000000000E+000                     1
          7   6.00000000000000000E+000                     1
          8   1.00000000000000000E+000                     1
          8   3.00000000000000000E+000                     1
          8   6.00000000000000000E+000                     3
          8   8.00000000000000000E+000                     2
          8   9.00000000000000000E+000                     1
          9   1.00000000000000000E+000                     2
          9   4.00000000000000000E+000                     1
          9   5.00000000000000000E+000                     2
          9   8.00000000000000000E+000                     1
          ?   0.00000000000000000E+000                     1
          ?                          ?                     1

--- 45 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>obey TEST010(match_having_range_no_sub_rollup);
>>--===========================================
>>-- Query 8, HAVING is a subsumed range predicate
>>-- Should match MJV1, MAV1
>>--===========================================
>>set pattern $$QueryName$$ HAVING_RANGE_NOSUB_ROLLUP;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 1 and 8
+>  order by g1;

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

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

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874795237497' numCols='3'>          
          CAT.TEST010.T010_DIM                                       
          <Key>                                                      
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_DIM.PKEY                              
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
        <Table id='T2' TS='212223874794487708' numCols='4'>          
          CAT.TEST010.T010_FACT                                      
          <Key>                                                      
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>
              CAT.TEST010.T010_FACT.SYSKEY                           
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
      <JoinPredList>                                                 
        <JoinPred id='J32'>                                          
          <Column ref='C9'>                                          
          </Column>                                                  
          <Column id='C4' tableId='T2' colIndex='3'>                 
            CAT.TEST010.T010_FACT.DIMKEY                             
          </Column>                                                  
        </JoinPred>                                                  
      </JoinPredList>                                                
      <RangePredList>                                                
        <Range id='R58' sqlType='LARGEINT NO NULLS'>                 
          <Expr id='X17'>                                            
            <Function id='S17' op='count_nonull' aggregateFunc='2'>  
              <Column ref='C10'>                                     
              </Column>                                              
            </Function>                                              
          </Expr>                                                    
          <OpBT startIsIncluded='1' endIsIncluded='1'>               
            <NumericVal scale='0'>                                   
              1                                                      
            </NumericVal>                                            
            <NumericVal scale='0'>                                   
              8                                                      
            </NumericVal>                                            
          </OpBT>                                                    
        </Range>                                                     
      </RangePredList>                                               
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O21'>                                              
        <Expr id='X21'>                                              
          <Function id='S21' op='count_nonull' aggregateFunc='2'>    
            <Column id='C10' tableId='T1' colIndex='1'>              
              CAT.TEST010.T010_DIM.DIMDATA1                          
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O26'>                                              
        <Column ref='C2'>                                            
        </Column>                                                    
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G3'>                                                
      <Primary>                                                      
        <Column id='C2' tableId='T2' colIndex='1'>                   
          CAT.TEST010.T010_FACT.G1                                   
        </Column>                                                    
      </Primary>                                                     
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE > having8.in;
>>sh echo MATCH HAVING_RANGE_NOSUB_ROLLUP.xml >> having8.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS having8.in having.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874796846419'>
            CAT.TEST010.HAVING_MJV
          </MVName>
          <OutputList>
            <Output ref='C10' result='Provided'>
              <MVColumn ref='C10'>
                DIMDATA1
              </MVColumn>
            </Output>
            <Output ref='O26' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874811311094'>
            CAT.TEST010.HAVING_MAV1
          </MVName>
          <RangePredList>
            <Range ref='R58' result='NotProvided'>
              <Expr ref='X17'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S17'>
                    CNT
                  </MVColumn>
                </Function>
              </Expr>
            </Range>
          </RangePredList>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O26' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O21' result='NotProvided'>
              <Expr ref='X21'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S21'>
                    CNT
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV2 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV3 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify MJV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MJV';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 1 and 8
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MJV                                      

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

G1           CNT                 
-----------  --------------------

          0                     1
          3                     5
          4                     4
          5                     5
          6                     5
          7                     3
          8                     8
          9                     6
          ?                     2

--- 9 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 1 and 8
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           CNT                 
-----------  --------------------

          0                     1
          3                     5
          4                     4
          5                     5
          6                     5
          7                     3
          8                     8
          9                     6
          ?                     2

--- 9 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>-- Verify MAV1 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MAV1';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 1 and 8
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV1                                     

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

G1           CNT                 
-----------  --------------------

          0                     1
          3                     5
          4                     4
          5                     5
          6                     5
          7                     3
          8                     8
          9                     6
          ?                     2

--- 9 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, count(dimdata1) cnt
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having count(dimdata1) between 1 and 8
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           CNT                 
-----------  --------------------

          0                     1
          3                     5
          4                     4
          5                     5
          6                     5
          7                     3
          8                     8
          9                     6
          ?                     2

--- 9 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>obey TEST010(match_having_residual_match);
>>--===========================================
>>-- Query 9, HAVING is a matching residual predicate
>>-- Should match MJV1, MAV1, MAV3
>>--===========================================
>>set pattern $$QueryName$$ HAVING_RES_MATCH;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, max(dimdata2) mx
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having max(dimdata2) like '%NICE%'
+>     and count(*) = g1
+>  order by g1, g2;

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

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

<Query>                                                                            
  <Version>                                                                        
    1.0                                                                            
  </Version>                                                                       
  <Misc rewriteLevel='3'>                                                          
  </Misc>                                                                          
  <JBB id='B0'>                                                                    
    <Hub>                                                                          
      <JBBCList>                                                                   
        <Table id='T1' TS='212223874795237497' numCols='3' residualBits='00000002'>
          CAT.TEST010.T010_DIM                                                     
          <Key>                                                                    
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>              
              CAT.TEST010.T010_DIM.PKEY                                            
            </Column>                                                              
          </Key>                                                                   
        </Table>                                                                   
        <Table id='T2' TS='212223874794487708' numCols='4'>                        
          CAT.TEST010.T010_FACT                                                    
          <Key>                                                                    
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>              
              CAT.TEST010.T010_FACT.SYSKEY                                         
            </Column>                                                              
          </Key>                                                                   
        </Table>                                                                   
      </JBBCList>                                                                  
      <JoinPredList>                                                               
        <JoinPred id='J34'>                                                        
          <Column ref='C9'>                                                        
          </Column>                                                                
          <Column id='C4' tableId='T2' colIndex='3'>                               
            CAT.TEST010.T010_FACT.DIMKEY                                           
          </Column>                                                                
        </JoinPred>                                                                
      </JoinPredList>                                                              
      <ResidualPredList>                                                           
        <Residual id='D56'>                                                        
          <BinaryOper id='S56' op='='>                                             
            <Function id='S21' op='count' aggregateFunc='1'>                       
              <NumericVal id='S20' scale='0'>                                      
                1                                                                  
              </NumericVal>                                                        
            </Function>                                                            
            <Column ref='C2'>                                                      
            </Column>                                                              
          </BinaryOper>                                                            
        </Residual>                                                                
        <Residual id='D19'>                                                        
          <Function id='S19' op='like'>                                            
            <Function id='S17' op='max' aggregateFunc='5'>                         
              <Column ref='C11'>                                                   
              </Column>                                                            
            </Function>                                                            
            <StringVal id='S18'><![CDATA['%NICE%']]></StringVal>                   
          </Function>                                                              
        </Residual>                                                                
      </ResidualPredList>                                                          
    </Hub>                                                                         
    <ExtraHub>                                                                     
    </ExtraHub>                                                                    
    <OutputList>                                                                   
      <Output id='O23'>                                                            
        <Expr id='X23'>                                                            
          <Function id='S23' op='max' aggregateFunc='5'>                           
            <Column id='C11' tableId='T1' colIndex='2'>                            
              CAT.TEST010.T010_DIM.DIMDATA2                                        
            </Column>                                                              
          </Function>                                                              
        </Expr>                                                                    
      </Output>                                                                    
      <Output id='O28'>                                                            
        <Column ref='C2'>                                                          
        </Column>                                                                  
      </Output>                                                                    
      <Output id='O31'>                                                            
        <Column ref='C3'>                                                          
        </Column>                                                                  
      </Output>                                                                    
    </OutputList>                                                                  
    <GroupBy id='G3'>                                                              
      <Primary>                                                                    
        <Column id='C2' tableId='T2' colIndex='1'>                                 
          CAT.TEST010.T010_FACT.G1                                                 
        </Column>                                                                  
        <Column id='C3' tableId='T2' colIndex='2'>                                 
          CAT.TEST010.T010_FACT.G2                                                 
        </Column>                                                                  
      </Primary>                                                                   
    </GroupBy>                                                                     
  </JBB>                                                                           
</Query>                                                                           

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE > having9.in;
>>sh echo MATCH HAVING_RES_MATCH.xml >> having9.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS having9.in having.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874796846419'>
            CAT.TEST010.HAVING_MJV
          </MVName>
          <OutputList>
            <Output ref='C11' result='Provided'>
              <MVColumn ref='C11'>
                DIMDATA2
              </MVColumn>
            </Output>
            <Output ref='O28' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O31' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874811311094'>
            CAT.TEST010.HAVING_MAV1
          </MVName>
          <ResidualPredList>
            <Residual ref='D19' result='NotProvided'>
              <Function ref='S19' op='like'>
                <MVColumn ref='S17'>
                  MX
                </MVColumn>
                <StringVal ref='S18'><![CDATA['%NICE%']]></StringVal>
              </Function>
            </Residual>
            <Residual ref='D56' result='NotProvided'>
              <BinaryOper ref='S56' op='='>
                <MVColumn ref='S21'>
                  CNT_STAR
                </MVColumn>
                <MVColumn ref='C2'>
                  G1
                </MVColumn>
              </BinaryOper>
            </Residual>
          </ResidualPredList>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O23' result='Provided'>
              <MVColumn ref='X23'>
                MX
              </MVColumn>
            </Output>
            <Output ref='O28' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O31' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874817574955'>
            CAT.TEST010.HAVING_MAV3
          </MVName>
          <ResidualPredList>
            <Residual ref='D19' result='Provided'>
            </Residual>
            <Residual ref='D56' result='NotProvided'>
              <BinaryOper ref='S56' op='='>
                <MVColumn ref='S21'>
                  CNT_STAR
                </MVColumn>
                <MVColumn ref='C2'>
                  G1
                </MVColumn>
              </BinaryOper>
            </Residual>
          </ResidualPredList>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O23' result='Provided'>
              <MVColumn ref='X23'>
                MX
              </MVColumn>
            </Output>
            <Output ref='O28' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O31' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV2 was disqualified because the MV has range predicates, and the query does not.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, max(dimdata2) mx
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having max(dimdata2) like '%NICE%'
+>     and count(*) = g1
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV1                                     

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

G1           G2                         MX        
-----------  -------------------------  ----------

          1   6.00000000000000000E+000  aNICEf    
          1   8.00000000000000000E+000  aNICEf    

--- 2 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, max(dimdata2) mx
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having max(dimdata2) like '%NICE%'
+>     and count(*) = g1
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           G2                         MX        
-----------  -------------------------  ----------

          1   6.00000000000000000E+000  aNICEf    
          1   8.00000000000000000E+000  aNICEf    

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

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>obey TEST010(match_having_residual_match_rollup);
>>--===========================================
>>-- Query 10, HAVING is a matching residual predicate
>>-- Should match MJV1, MAV1
>>--===========================================
>>set pattern $$QueryName$$ HAVING_RES_MATCH_ROLLUP;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, max(dimdata2) mx
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having max(dimdata2) like '%NICE%'
+>     and count(*) = g1
+>  order by g1;

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

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

<Query>                                                                            
  <Version>                                                                        
    1.0                                                                            
  </Version>                                                                       
  <Misc rewriteLevel='3'>                                                          
  </Misc>                                                                          
  <JBB id='B0'>                                                                    
    <Hub>                                                                          
      <JBBCList>                                                                   
        <Table id='T1' TS='212223874795237497' numCols='3' residualBits='00000002'>
          CAT.TEST010.T010_DIM                                                     
          <Key>                                                                    
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>              
              CAT.TEST010.T010_DIM.PKEY                                            
            </Column>                                                              
          </Key>                                                                   
        </Table>                                                                   
        <Table id='T2' TS='212223874794487708' numCols='4'>                        
          CAT.TEST010.T010_FACT                                                    
          <Key>                                                                    
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>              
              CAT.TEST010.T010_FACT.SYSKEY                                         
            </Column>                                                              
          </Key>                                                                   
        </Table>                                                                   
      </JBBCList>                                                                  
      <JoinPredList>                                                               
        <JoinPred id='J34'>                                                        
          <Column ref='C9'>                                                        
          </Column>                                                                
          <Column id='C4' tableId='T2' colIndex='3'>                               
            CAT.TEST010.T010_FACT.DIMKEY                                           
          </Column>                                                                
        </JoinPred>                                                                
      </JoinPredList>                                                              
      <ResidualPredList>                                                           
        <Residual id='D56'>                                                        
          <BinaryOper id='S56' op='='>                                             
            <Function id='S21' op='count' aggregateFunc='1'>                       
              <NumericVal id='S20' scale='0'>                                      
                1                                                                  
              </NumericVal>                                                        
            </Function>                                                            
            <Column ref='C2'>                                                      
            </Column>                                                              
          </BinaryOper>                                                            
        </Residual>                                                                
        <Residual id='D19'>                                                        
          <Function id='S19' op='like'>                                            
            <Function id='S17' op='max' aggregateFunc='5'>                         
              <Column ref='C11'>                                                   
              </Column>                                                            
            </Function>                                                            
            <StringVal id='S18'><![CDATA['%NICE%']]></StringVal>                   
          </Function>                                                              
        </Residual>                                                                
      </ResidualPredList>                                                          
    </Hub>                                                                         
    <ExtraHub>                                                                     
    </ExtraHub>                                                                    
    <OutputList>                                                                   
      <Output id='O23'>                                                            
        <Expr id='X23'>                                                            
          <Function id='S23' op='max' aggregateFunc='5'>                           
            <Column id='C11' tableId='T1' colIndex='2'>                            
              CAT.TEST010.T010_DIM.DIMDATA2                                        
            </Column>                                                              
          </Function>                                                              
        </Expr>                                                                    
      </Output>                                                                    
      <Output id='O28'>                                                            
        <Column ref='C2'>                                                          
        </Column>                                                                  
      </Output>                                                                    
    </OutputList>                                                                  
    <GroupBy id='G3'>                                                              
      <Primary>                                                                    
        <Column id='C2' tableId='T2' colIndex='1'>                                 
          CAT.TEST010.T010_FACT.G1                                                 
        </Column>                                                                  
      </Primary>                                                                   
    </GroupBy>                                                                     
  </JBB>                                                                           
</Query>                                                                           

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE > having10.in;
>>sh echo MATCH HAVING_RES_MATCH_ROLLUP.xml >> having10.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS having10.in having.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874796846419'>
            CAT.TEST010.HAVING_MJV
          </MVName>
          <OutputList>
            <Output ref='C11' result='Provided'>
              <MVColumn ref='C11'>
                DIMDATA2
              </MVColumn>
            </Output>
            <Output ref='O28' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874811311094'>
            CAT.TEST010.HAVING_MAV1
          </MVName>
          <ResidualPredList>
            <Residual ref='D19' result='NotProvided'>
              <Function ref='S19' op='like'>
                <Function op='max' aggregateFunc='5'>
                  <MVColumn ref='S17'>
                    MX
                  </MVColumn>
                </Function>
                <StringVal ref='S18'><![CDATA['%NICE%']]></StringVal>
              </Function>
            </Residual>
            <Residual ref='D56' result='NotProvided'>
              <BinaryOper ref='S56' op='='>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn>
                    CNT_STAR
                  </MVColumn>
                </Function>
                <MVColumn ref='C2'>
                  G1
                </MVColumn>
              </BinaryOper>
            </Residual>
          </ResidualPredList>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O28' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O23' result='NotProvided'>
              <Expr ref='X23'>
                <Function op='max' aggregateFunc='5'>
                  <MVColumn ref='S23'>
                    MX
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV2 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV3 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, max(dimdata2) mx
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having max(dimdata2) like '%NICE%'
+>     and count(*) = g1
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV1                                     

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

G1           MX        
-----------  ----------

          4  aNICEf    

--- 1 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, max(dimdata2) mx
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having max(dimdata2) like '%NICE%'
+>     and count(*) = g1
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           MX        
-----------  ----------

          4  aNICEf    

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

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>obey TEST010(match_having_residual_match_rollup_on_grouping);
>>--===========================================
>>-- Query 10.1, HAVING is a residual predicate on an MV grouping column
>>-- Should match MJV1, MAV1
>>--===========================================
>>set pattern $$QueryName$$ HAVING_RES_MATCH_ROLLUP_GRP;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, max(dimdata2) mx, avg(g2) avg_g2
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having avg_g2+5 between 5 and 20
+>  order by g1;

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

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

<Query>                                                                            
  <Version>                                                                        
    1.0                                                                            
  </Version>                                                                       
  <Misc rewriteLevel='3'>                                                          
  </Misc>                                                                          
  <JBB id='B0'>                                                                    
    <Hub>                                                                          
      <JBBCList>                                                                   
        <Table id='T1' TS='212223874795237497' numCols='3'>                        
          CAT.TEST010.T010_DIM                                                     
          <Key>                                                                    
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>              
              CAT.TEST010.T010_DIM.PKEY                                            
            </Column>                                                              
          </Key>                                                                   
        </Table>                                                                   
        <Table id='T2' TS='212223874794487708' numCols='4' residualBits='00000002'>
          CAT.TEST010.T010_FACT                                                    
          <Key>                                                                    
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>              
              CAT.TEST010.T010_FACT.SYSKEY                                         
            </Column>                                                              
          </Key>                                                                   
        </Table>                                                                   
      </JBBCList>                                                                  
      <JoinPredList>                                                               
        <JoinPred id='J35'>                                                        
          <Column ref='C9'>                                                        
          </Column>                                                                
          <Column id='C4' tableId='T2' colIndex='3'>                               
            CAT.TEST010.T010_FACT.DIMKEY                                           
          </Column>                                                                
        </JoinPred>                                                                
      </JoinPredList>                                                              
      <ResidualPredList>                                                           
        <Residual id='D49'>                                                        
          <BinaryOper id='S49' op='>='>                                            
            <BinaryOper id='S22' op='+'>                                           
              <BinaryOper id='S20' op='/'>                                         
                <Function id='S18' op='sum' aggregateFunc='3'>                     
                  <Column ref='C3'>                                                
                  </Column>                                                        
                </Function>                                                        
                <Function id='S19' op='count_nonull' aggregateFunc='2'>            
                  <Column ref='C3'>                                                
                  </Column>                                                        
                </Function>                                                        
              </BinaryOper>                                                        
              <NumericVal id='S21' scale='0'>                                      
                5                                                                  
              </NumericVal>                                                        
            </BinaryOper>                                                          
            <NumericVal id='S21' scale='0'>                                        
              5                                                                    
            </NumericVal>                                                          
          </BinaryOper>                                                            
        </Residual>                                                                
        <Residual id='D50'>                                                        
          <BinaryOper id='S50' op='&lt;='>                                         
            <BinaryOper id='S22' op='+'>                                           
              <BinaryOper id='S20' op='/'>                                         
                <Function id='S18' op='sum' aggregateFunc='3'>                     
                  <Column ref='C3'>                                                
                  </Column>                                                        
                </Function>                                                        
                <Function id='S19' op='count_nonull' aggregateFunc='2'>            
                  <Column ref='C3'>                                                
                  </Column>                                                        
                </Function>                                                        
              </BinaryOper>                                                        
              <NumericVal id='S21' scale='0'>                                      
                5                                                                  
              </NumericVal>                                                        
            </BinaryOper>                                                          
            <NumericVal id='S23' scale='0'>                                        
              20                                                                   
            </NumericVal>                                                          
          </BinaryOper>                                                            
        </Residual>                                                                
      </ResidualPredList>                                                          
    </Hub>                                                                         
    <ExtraHub>                                                                     
    </ExtraHub>                                                                    
    <OutputList>                                                                   
      <Output id='O17'>                                                            
        <Expr id='X17'>                                                            
          <Function id='S17' op='max' aggregateFunc='5'>                           
            <Column id='C11' tableId='T1' colIndex='2'>                            
              CAT.TEST010.T010_DIM.DIMDATA2                                        
            </Column>                                                              
          </Function>                                                              
        </Expr>                                                                    
      </Output>                                                                    
      <Output id='O20'>                                                            
        <Expr id='X20'>                                                            
          <BinaryOper id='S20' op='/'>                                             
            <Function id='S18' op='sum' aggregateFunc='3'>                         
              <Column id='C3' tableId='T2' colIndex='2'>                           
                CAT.TEST010.T010_FACT.G2                                           
              </Column>                                                            
            </Function>                                                            
            <Function id='S19' op='count_nonull' aggregateFunc='2'>                
              <Column ref='C3'>                                                    
              </Column>                                                            
            </Function>                                                            
          </BinaryOper>                                                            
        </Expr>                                                                    
      </Output>                                                                    
      <Output id='O29'>                                                            
        <Column ref='C2'>                                                          
        </Column>                                                                  
      </Output>                                                                    
    </OutputList>                                                                  
    <GroupBy id='G3'>                                                              
      <Primary>                                                                    
        <Column id='C2' tableId='T2' colIndex='1'>                                 
          CAT.TEST010.T010_FACT.G1                                                 
        </Column>                                                                  
      </Primary>                                                                   
    </GroupBy>                                                                     
  </JBB>                                                                           
</Query>                                                                           

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE > having101.in;
>>sh echo MATCH HAVING_RES_MATCH_ROLLUP_GRP.xml >> having101.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS having101.in having.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874796846419'>
            CAT.TEST010.HAVING_MJV
          </MVName>
          <OutputList>
            <Output ref='C3' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
            <Output ref='C11' result='Provided'>
              <MVColumn ref='C11'>
                DIMDATA2
              </MVColumn>
            </Output>
            <Output ref='O29' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874811311094'>
            CAT.TEST010.HAVING_MAV1
          </MVName>
          <ResidualPredList>
            <Residual ref='D50' result='NotProvided'>
              <BinaryOper ref='S50' op='&lt;='>
                <BinaryOper ref='S22' op='+'>
                  <BinaryOper ref='S20' op='/'>
                    <Function op='sum on grouping' aggregateFunc='9'>
                      <MVColumn ref='C3'>
                        G2
                      </MVColumn>
                      <MVColumn>
                        CNT_STAR
                      </MVColumn>
                    </Function>
                    <Function op='count on grouping' aggregateFunc='8'>
                      <MVColumn ref='C3'>
                        G2
                      </MVColumn>
                      <MVColumn>
                        CNT_STAR
                      </MVColumn>
                    </Function>
                  </BinaryOper>
                  <NumericVal ref='S21' scale='0'>
                    5
                  </NumericVal>
                </BinaryOper>
                <NumericVal ref='S23' scale='0'>
                  20
                </NumericVal>
              </BinaryOper>
            </Residual>
            <Residual ref='D49' result='NotProvided'>
              <BinaryOper ref='S49' op='>='>
                <BinaryOper ref='S22' op='+'>
                  <BinaryOper ref='S20' op='/'>
                    <Function op='sum on grouping' aggregateFunc='9'>
                      <MVColumn ref='C3'>
                        G2
                      </MVColumn>
                      <MVColumn>
                        CNT_STAR
                      </MVColumn>
                    </Function>
                    <Function op='count on grouping' aggregateFunc='8'>
                      <MVColumn ref='C3'>
                        G2
                      </MVColumn>
                      <MVColumn>
                        CNT_STAR
                      </MVColumn>
                    </Function>
                  </BinaryOper>
                  <NumericVal ref='S21' scale='0'>
                    5
                  </NumericVal>
                </BinaryOper>
                <NumericVal ref='S21' scale='0'>
                  5
                </NumericVal>
              </BinaryOper>
            </Residual>
          </ResidualPredList>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O29' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O20' result='NotProvided'>
              <Expr ref='X20'>
                <BinaryOper ref='S20' op='/'>
                  <Function op='sum on grouping' aggregateFunc='9'>
                    <MVColumn ref='C3'>
                      G2
                    </MVColumn>
                    <MVColumn>
                      CNT_STAR
                    </MVColumn>
                  </Function>
                  <Function op='count on grouping' aggregateFunc='8'>
                    <MVColumn ref='C3'>
                      G2
                    </MVColumn>
                    <MVColumn>
                      CNT_STAR
                    </MVColumn>
                  </Function>
                </BinaryOper>
              </Expr>
            </Output>
            <Output ref='O17' result='NotProvided'>
              <Expr ref='X17'>
                <Function op='max' aggregateFunc='5'>
                  <MVColumn ref='S17'>
                    MX
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV2 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV3 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify MAV1 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MAV1';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, max(dimdata2) mx, avg(g2) avg_g2
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having avg_g2+5 between 5 and 20
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV1                                     

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

G1           MX          AVG_G2                   
-----------  ----------  -------------------------

          1  abcdef       3.65517241379310336E+000
          2  abcdef       3.88888888888888896E+000
          3  abcdef       5.20000000000000000E+000
          4  aNICEf       5.50000000000000000E+000
          5  abcdef       4.59999999999999936E+000
          6  abcdef       3.20000000000000000E+000
          7  aNIECE       4.00000000000000000E+000
          8  abcdef       5.87500000000000000E+000
          9  abcdef       4.00000000000000000E+000
          ?  abcdef       0.00000000000000000E+000

--- 10 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, max(dimdata2) mx, avg(g2) avg_g2
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having avg_g2+5 between 5 and 20
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           MX          AVG_G2                   
-----------  ----------  -------------------------

          1  abcdef       3.65517241379310336E+000
          2  abcdef       3.88888888888888896E+000
          3  abcdef       5.20000000000000000E+000
          4  aNICEf       5.50000000000000000E+000
          5  abcdef       4.59999999999999936E+000
          6  abcdef       3.20000000000000000E+000
          7  aNIECE       4.00000000000000000E+000
          8  abcdef       5.87500000000000000E+000
          9  abcdef       4.00000000000000000E+000
          ?  abcdef       0.00000000000000000E+000

--- 10 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>-- Verify MJV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MJV';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, max(dimdata2) mx, avg(g2) avg_g2
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having avg_g2+5 between 5 and 20
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MJV                                      

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

G1           MX          AVG_G2                   
-----------  ----------  -------------------------

          1  abcdef       3.65517241379310336E+000
          2  abcdef       3.88888888888888896E+000
          3  abcdef       5.20000000000000000E+000
          4  aNICEf       5.50000000000000000E+000
          5  abcdef       4.59999999999999936E+000
          6  abcdef       3.20000000000000000E+000
          7  aNIECE       4.00000000000000000E+000
          8  abcdef       5.87500000000000000E+000
          9  abcdef       4.00000000000000000E+000
          ?  abcdef       0.00000000000000000E+000

--- 10 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, max(dimdata2) mx, avg(g2) avg_g2
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having avg_g2+5 between 5 and 20
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           MX          AVG_G2                   
-----------  ----------  -------------------------

          1  abcdef       3.65517241379310336E+000
          2  abcdef       3.88888888888888896E+000
          3  abcdef       5.20000000000000000E+000
          4  aNICEf       5.50000000000000000E+000
          5  abcdef       4.59999999999999936E+000
          6  abcdef       3.20000000000000000E+000
          7  aNIECE       4.00000000000000000E+000
          8  abcdef       5.87500000000000000E+000
          9  abcdef       4.00000000000000000E+000
          ?  abcdef       0.00000000000000000E+000

--- 10 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>
>>obey TEST010(match_having_residual_no_match);
>>--===========================================
>>-- Query 11, HAVING is a non-matching residual predicate
>>-- Should match MJV1, MAV1
>>--===========================================
>>set pattern $$QueryName$$ HAVING_RES_NOMATCH;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, max(dimdata2) mx
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having max(dimdata2) like '%NIECE%'
+>  order by g1, g2;

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

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

<Query>                                                                            
  <Version>                                                                        
    1.0                                                                            
  </Version>                                                                       
  <Misc rewriteLevel='3'>                                                          
  </Misc>                                                                          
  <JBB id='B0'>                                                                    
    <Hub>                                                                          
      <JBBCList>                                                                   
        <Table id='T1' TS='212223874795237497' numCols='3' residualBits='00000002'>
          CAT.TEST010.T010_DIM                                                     
          <Key>                                                                    
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>              
              CAT.TEST010.T010_DIM.PKEY                                            
            </Column>                                                              
          </Key>                                                                   
        </Table>                                                                   
        <Table id='T2' TS='212223874794487708' numCols='4'>                        
          CAT.TEST010.T010_FACT                                                    
          <Key>                                                                    
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>              
              CAT.TEST010.T010_FACT.SYSKEY                                         
            </Column>                                                              
          </Key>                                                                   
        </Table>                                                                   
      </JBBCList>                                                                  
      <JoinPredList>                                                               
        <JoinPred id='J31'>                                                        
          <Column ref='C9'>                                                        
          </Column>                                                                
          <Column id='C4' tableId='T2' colIndex='3'>                               
            CAT.TEST010.T010_FACT.DIMKEY                                           
          </Column>                                                                
        </JoinPred>                                                                
      </JoinPredList>                                                              
      <ResidualPredList>                                                           
        <Residual id='D19'>                                                        
          <Function id='S19' op='like'>                                            
            <Function id='S17' op='max' aggregateFunc='5'>                         
              <Column ref='C11'>                                                   
              </Column>                                                            
            </Function>                                                            
            <StringVal id='S18'><![CDATA['%NIECE%']]></StringVal>                  
          </Function>                                                              
        </Residual>                                                                
      </ResidualPredList>                                                          
    </Hub>                                                                         
    <ExtraHub>                                                                     
    </ExtraHub>                                                                    
    <OutputList>                                                                   
      <Output id='O20'>                                                            
        <Expr id='X20'>                                                            
          <Function id='S20' op='max' aggregateFunc='5'>                           
            <Column id='C11' tableId='T1' colIndex='2'>                            
              CAT.TEST010.T010_DIM.DIMDATA2                                        
            </Column>                                                              
          </Function>                                                              
        </Expr>                                                                    
      </Output>                                                                    
      <Output id='O25'>                                                            
        <Column ref='C2'>                                                          
        </Column>                                                                  
      </Output>                                                                    
      <Output id='O28'>                                                            
        <Column ref='C3'>                                                          
        </Column>                                                                  
      </Output>                                                                    
    </OutputList>                                                                  
    <GroupBy id='G3'>                                                              
      <Primary>                                                                    
        <Column id='C2' tableId='T2' colIndex='1'>                                 
          CAT.TEST010.T010_FACT.G1                                                 
        </Column>                                                                  
        <Column id='C3' tableId='T2' colIndex='2'>                                 
          CAT.TEST010.T010_FACT.G2                                                 
        </Column>                                                                  
      </Primary>                                                                   
    </GroupBy>                                                                     
  </JBB>                                                                           
</Query>                                                                           

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE > having11.in;
>>sh echo MATCH HAVING_RES_NOMATCH.xml >> having11.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS having11.in having.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874796846419'>
            CAT.TEST010.HAVING_MJV
          </MVName>
          <OutputList>
            <Output ref='C11' result='Provided'>
              <MVColumn ref='C11'>
                DIMDATA2
              </MVColumn>
            </Output>
            <Output ref='O25' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O28' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874811311094'>
            CAT.TEST010.HAVING_MAV1
          </MVName>
          <ResidualPredList>
            <Residual ref='D19' result='NotProvided'>
              <Function ref='S19' op='like'>
                <MVColumn ref='S17'>
                  MX
                </MVColumn>
                <StringVal ref='S18'><![CDATA['%NIECE%']]></StringVal>
              </Function>
            </Residual>
          </ResidualPredList>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O20' result='Provided'>
              <MVColumn ref='X20'>
                MX
              </MVColumn>
            </Output>
            <Output ref='O25' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O28' result='Provided'>
              <MVColumn ref='C3'>
                G2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV2 was disqualified because the MV has range predicates, and the query does not.]]></Info>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV3 was disqualified because MV residual predicate D101 was not matched by the query.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify MAV1 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MAV1';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, max(dimdata2) mx
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having max(dimdata2) like '%NIECE%'
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV1                                     

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

G1           G2                         MX        
-----------  -------------------------  ----------

          1   9.00000000000000000E+000  aNIECE    
          2   1.00000000000000000E+000  aNIECE    
          2   4.00000000000000000E+000  aNIECE    
          2   5.00000000000000000E+000  aNIECE    
          2   7.00000000000000000E+000  aNIECE    
          3   9.00000000000000000E+000  aNIECE    
          5   1.00000000000000000E+000  aNIECE    
          5   8.00000000000000000E+000  aNIECE    
          7   1.00000000000000000E+000  aNIECE    
          7   5.00000000000000000E+000  aNIECE    
          7   6.00000000000000000E+000  aNIECE    
          8   1.00000000000000000E+000  aNIECE    
          8   3.00000000000000000E+000  aNIECE    
          8   6.00000000000000000E+000  aNIECE    
          9   4.00000000000000000E+000  aNIECE    

--- 15 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, g2, max(dimdata2) mx
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1, g2
+>  having max(dimdata2) like '%NIECE%'
+>  order by g1, g2;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           G2                         MX        
-----------  -------------------------  ----------

          1   9.00000000000000000E+000  aNIECE    
          2   1.00000000000000000E+000  aNIECE    
          2   4.00000000000000000E+000  aNIECE    
          2   5.00000000000000000E+000  aNIECE    
          2   7.00000000000000000E+000  aNIECE    
          3   9.00000000000000000E+000  aNIECE    
          5   1.00000000000000000E+000  aNIECE    
          5   8.00000000000000000E+000  aNIECE    
          7   1.00000000000000000E+000  aNIECE    
          7   5.00000000000000000E+000  aNIECE    
          7   6.00000000000000000E+000  aNIECE    
          8   1.00000000000000000E+000  aNIECE    
          8   3.00000000000000000E+000  aNIECE    
          8   6.00000000000000000E+000  aNIECE    
          9   4.00000000000000000E+000  aNIECE    

--- 15 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>obey TEST010(match_having_residual_no_match_rollup);
>>--===========================================
>>-- Query 12, HAVING is a non-matching residual predicate
>>-- Should match MJV1, MAV1
>>--===========================================
>>set pattern $$QueryName$$ HAVING_RES_NOMATCH_ROLLUP;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, max(dimdata2) mx
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having mx like '%NIECE%'
+>  order by g1;

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

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

<Query>                                                                            
  <Version>                                                                        
    1.0                                                                            
  </Version>                                                                       
  <Misc rewriteLevel='3'>                                                          
  </Misc>                                                                          
  <JBB id='B0'>                                                                    
    <Hub>                                                                          
      <JBBCList>                                                                   
        <Table id='T1' TS='212223874795237497' numCols='3' residualBits='00000002'>
          CAT.TEST010.T010_DIM                                                     
          <Key>                                                                    
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>              
              CAT.TEST010.T010_DIM.PKEY                                            
            </Column>                                                              
          </Key>                                                                   
        </Table>                                                                   
        <Table id='T2' TS='212223874794487708' numCols='4'>                        
          CAT.TEST010.T010_FACT                                                    
          <Key>                                                                    
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>              
              CAT.TEST010.T010_FACT.SYSKEY                                         
            </Column>                                                              
          </Key>                                                                   
        </Table>                                                                   
      </JBBCList>                                                                  
      <JoinPredList>                                                               
        <JoinPred id='J30'>                                                        
          <Column ref='C9'>                                                        
          </Column>                                                                
          <Column id='C4' tableId='T2' colIndex='3'>                               
            CAT.TEST010.T010_FACT.DIMKEY                                           
          </Column>                                                                
        </JoinPred>                                                                
      </JoinPredList>                                                              
      <ResidualPredList>                                                           
        <Residual id='D19'>                                                        
          <Function id='S19' op='like'>                                            
            <Function id='S17' op='max' aggregateFunc='5'>                         
              <Column ref='C11'>                                                   
              </Column>                                                            
            </Function>                                                            
            <StringVal id='S18'><![CDATA['%NIECE%']]></StringVal>                  
          </Function>                                                              
        </Residual>                                                                
      </ResidualPredList>                                                          
    </Hub>                                                                         
    <ExtraHub>                                                                     
    </ExtraHub>                                                                    
    <OutputList>                                                                   
      <Output id='O17'>                                                            
        <Expr id='X17'>                                                            
          <Function id='S17' op='max' aggregateFunc='5'>                           
            <Column id='C11' tableId='T1' colIndex='2'>                            
              CAT.TEST010.T010_DIM.DIMDATA2                                        
            </Column>                                                              
          </Function>                                                              
        </Expr>                                                                    
      </Output>                                                                    
      <Output id='O24'>                                                            
        <Column ref='C2'>                                                          
        </Column>                                                                  
      </Output>                                                                    
    </OutputList>                                                                  
    <GroupBy id='G3'>                                                              
      <Primary>                                                                    
        <Column id='C2' tableId='T2' colIndex='1'>                                 
          CAT.TEST010.T010_FACT.G1                                                 
        </Column>                                                                  
      </Primary>                                                                   
    </GroupBy>                                                                     
  </JBB>                                                                           
</Query>                                                                           

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

--- SQL operation complete.
>>
>>sh echo INITIALIZE > having12.in;
>>sh echo MATCH HAVING_RES_NOMATCH_ROLLUP.xml >> having12.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS having12.in having.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874796846419'>
            CAT.TEST010.HAVING_MJV
          </MVName>
          <OutputList>
            <Output ref='C11' result='Provided'>
              <MVColumn ref='C11'>
                DIMDATA2
              </MVColumn>
            </Output>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST010.T010_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST010.T010_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874811311094'>
            CAT.TEST010.HAVING_MAV1
          </MVName>
          <ResidualPredList>
            <Residual ref='D19' result='NotProvided'>
              <Function ref='S19' op='like'>
                <Function op='max' aggregateFunc='5'>
                  <MVColumn ref='S17'>
                    MX
                  </MVColumn>
                </Function>
                <StringVal ref='S18'><![CDATA['%NIECE%']]></StringVal>
              </Function>
            </Residual>
          </ResidualPredList>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O24' result='Provided'>
              <MVColumn ref='C2'>
                G1
              </MVColumn>
            </Output>
            <Output ref='O17' result='NotProvided'>
              <Expr ref='X17'>
                <Function op='max' aggregateFunc='5'>
                  <MVColumn ref='S17'>
                    MX
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV2 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
    <Info><![CDATA[MV CAT.TEST010.HAVING_MAV3 was disqualified because it has HAVING predicates, and the query needs Rollup.]]></Info>
  </JbbResult>
</Result>
>>
>>-- Verify MAV1 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST010.HAVING_MAV1';

--- SQL operation complete.
>>obey TEST010(compare);
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, max(dimdata2) mx
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having mx like '%NIECE%'
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.HAVING_MAV1                                     

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

G1           MX        
-----------  ----------

          7  aNIECE    

--- 1 row(s) selected.
>>
>>log T010_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST010($$QueryName$$);
>>prepare QueryStmt from
+>  select g1, max(dimdata2) mx
+>  from T010_FACT, T010_DIM
+>  where dimkey=pkey
+>  group by g1
+>  having mx like '%NIECE%'
+>  order by g1;

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

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

FILE_SCAN                       CAT.TEST010.T010_DIM                                        
FILE_SCAN                       CAT.TEST010.T010_FACT                                       

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

G1           MX        
-----------  ----------

          7  aNIECE    

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

--- SQL operation complete.
>>
>>log T010_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>obey TEST010(clean_up);
>>----------------------------------------------------
>>------------------ clean up section ----------------
>>----------------------------------------------------
>>drop schema test010 cascade;

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

End of MXCI Session

