>>#ifLINUX
>>--obey TEST006(check_install_script);
>>#ifLINUX
>>obey TEST006(set_up);
>>----------------------------------------------------
>>-------------------- set up section ----------------
>>----------------------------------------------------
>>
>>--control query default query_cache '0';
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'LOG';

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

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

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

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

--- SQL operation complete.
>>
>>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 optimizer_rules;

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

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

--- SQL command prepared.
>>
>>obey TEST006(create_tables);
>>--===========================================
>>---------- create tables section ------------
>>--===========================================
>>
>>create table FACT (
+>        fday   int,
+>        fmonth int,
+>        fyear  int,
+>        fitem  int,
+>        fdep   int,
+>        fstore int,
+>        fstate int,
+>        fprice numeric (8,2),
+>        dimkey int) no partition;

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

--- SQL operation complete.
>>
>>create table fact3 (
+>	fday   int,
+>	fmonth int,
+>	fyear  int,
+>	fitem  int,
+>	fquant int,
+>	fprice numeric (8,2),
+>	dimkey int) no partition;

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

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

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

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

--- SQL operation complete.
>>
>>insert into fact values
+>  (25,  3, 2012, 3, 1, 1, 7, 81, 14),
+>  (14,  1, 2011, 1, 2, 8, 8, 57, 14),
+>  ( 6,  2, 2012, 9, 3, 3, 6, 86, 18),
+>  (30,  1, 2012, 9, 7, 5, 4, 36,  7),
+>  (12, 12, 2011, 6, 1, 6, 2, 76,  4),
+>  ( 3,  1, 2010, 9, 4, 3, 4, 42,  3),
+>  (19, 11, 2010, 3, 3, 1, 7, 46,  7),
+>  (30,  5, 2010, 1, 7, 1, 7, 83, 10),
+>  (14, 12, 2010, 2, 8, 6, 9, 22,  9),
+>  (14,  7, 2012, 4, 4, 4, 6, 96, 18),
+>  (25,  2, 2010, 3, 3, 9, 6, 48,  7),
+>  (19,  3, 2012, 8, 5, 1, 7, 58, 10),
+>  (22,  3, 2011, 9, 7, 1, 2, 30, 18),
+>  (10, 12, 2012, 9, 6, 1, 9, 78, 16),
+>  (20, 12, 2010, 3, 7, 1, 6, 82,  2),
+>  (12,  9, 2010, 5, 3, 7, 3, 66,  1),
+>  (26,  6, 2012, 9, 9, 1, 6, 75, 19),
+>  (13,  5, 2012, 4, 5, 6, 6, 50,  8),
+>  (28,  3, 2012, 2, 4, 9, 5, 94, 19),
+>  ( 9,  7, 2012, 4, 7, 5, 6, 33,  5),
+>  ( 3,  4, 2011, 2, 9, 3, 1, 50, 19),
+>  (10, 10, 2010, 6, 5, 7, 9, 21, 18),
+>  (30,  4, 2012, 2, 7, 5, 9, 59,  5),
+>  (19,  6, 2011, 1, 5, 1, 8, 28,  2),
+>  (13,  3, 2010, 9, 5, 3, 9, 36, 17),
+>  (20,  6, 2011, 3, 2, 2, 8, 56,  3),
+>  (10,  7, 2010, 1, 3, 4, 1, 73, 11),
+>  (12,  9, 2011, 5, 2, 9, 2, 54,  8),
+>  (29,  9, 2012, 3, 6, 2, 5, 79, 11),
+>  (27, 12, 2010, 4, 2, 2, 9, 70,  1),
+>  (16,  9, 2010, 9, 3, 6, 9, 90,  3),
+>  ( 4,  4, 2012, 3, 6, 6, 3, 50,  4),
+>  (19,  2, 2012, 3, 3, 9, 8, 34,  1),
+>  ( 8,  6, 2011, 7, 3, 5, 1, 71, 10),
+>  ( 9,  6, 2011, 2, 7, 6, 7, 40, 15),
+>  (31, 11, 2011, 2, 6, 1, 9, 78, 10),
+>  (28,  1, 2012, 3, 1, 5, 8, 39,  6),
+>  (10,  6, 2011, 6, 4, 2, 5, 77,  2),
+>  ( 1,  3, 2011, 9, 3, 4, 7, 92,  8),
+>  ( 6,  9, 2011, 2, 5, 8, 2, 30, 16),
+>  (23,  5, 2011, 3, 5, 7, 7, 15, 11),
+>  (28,  7, 2010, 6, 8, 8, 4, 44, 16),
+>  ( 4, 12, 2010, 3, 2, 7, 6, 84, 19),
+>  (11,  2, 2011, 1, 2, 1, 6, 69,  8),
+>  (13, 12, 2011, 7, 7, 6, 2, 35, 19),
+>  (14, 11, 2011, 6, 5, 5, 3, 69,  3),
+>  (25, 11, 2011, 2, 1, 4, 2, 31, 11),
+>  (18,  8, 2010, 2, 4, 1, 8, 23, 16),
+>  (23,  9, 2010, 4, 4, 8, 9, 88,  8),
+>  ( 9,  9, 2011, 5, 3, 3, 7, 17, 11);

--- 50 row(s) inserted.
>>
>>insert into fact3 values
+>  (31,  8, 2012, 6, 8, 55, 10),
+>  (15,  5, 2011, 4, 6, 64,  6),
+>  (17,  6, 2012, 9, 9, 76, 11),
+>  (12,  6, 2012, 7, 7, 14,  1),
+>  ( 1,  4, 2012, 2, 1, 85, 19),
+>  ( 7,  7, 2010, 8, 9, 21, 17),
+>  (27,  3, 2011, 4, 3, 94,  5),
+>  (18,  2, 2010, 6, 7, 47, 13),
+>  ( 8,  6, 2011, 9, 6, 93, 13),
+>  (16,  4, 2011, 4, 5, 33, 11),
+>  ( 5,  8, 2012, 1, 6, 20, 15),
+>  (13, 12, 2012, 3, 1, 29,  5),
+>  (30,  9, 2012, 8, 2, 78, 15),
+>  ( 3,  1, 2012, 1, 2, 96,  9),
+>  (14,  7, 2012, 8, 7, 57, 17),
+>  ( 7, 12, 2010, 8, 2, 33,  3),
+>  ( 1,  2, 2012, 5, 5, 43, 11),
+>  (29,  6, 2011, 6, 7, 96,  1),
+>  (30,  4, 2010, 4, 2, 32,  2),
+>  (16, 10, 2010, 1, 6, 21, 17),
+>  ( 7,  5, 2011, 7, 8, 15,  6),
+>  (11,  2, 2011, 9, 4, 27,  6),
+>  (22, 11, 2010, 5, 9, 61, 17),
+>  ( 2,  5, 2012, 5, 9, 64,  8),
+>  (13,  9, 2012, 6, 9, 86, 16),
+>  ( 7, 10, 2010, 1, 9, 63, 13),
+>  (13,  6, 2011, 8, 7, 66, 10),
+>  (24, 12, 2010, 2, 6, 22, 15),
+>  (20,  5, 2012, 4, 9, 87,  3),
+>  ( 2,  8, 2010, 1, 7, 91,  6),
+>  (29, 8 , 2010, 3, 9, 89, 13),
+>  (30,  6, 2012, 1, 9, 53,  3),
+>  (29,  8, 2012, 5, 1, 27, 16),
+>  (12,  3, 2010, 5, 7, 21, 13),
+>  (19,  6, 2012, 1, 8, 12, 15),
+>  (21, 11, 2011, 8, 7, 20, 18),
+>  (30,  1, 2010, 8, 5, 83,  2),
+>  ( 2, 12, 2010, 9, 7, 39, 18),
+>  ( 4, 11, 2012, 5, 1, 74,  9),
+>  (31,  8, 2011, 9, 2, 61,  3),
+>  (20, 10, 2012, 8, 2, 20, 15),
+>  (12,  3, 2011, 6, 5, 92,  6),
+>  (20, 12, 2010, 1, 3, 86,  9),
+>  ( 5, 11, 2012, 8, 1, 20, 10),
+>  (14,  5, 2011, 5, 8, 41,  7),
+>  ( 8,  7, 2012, 2, 7, 12,  2),
+>  (27,  6, 2012, 9, 5, 65,  7),
+>  (12,  2, 2011, 9, 2, 35, 18),
+>  (13,  2, 2011, 1, 5, 40, 16),
+>  ( 9,  8, 2011, 3, 5, 97, 13);

--- 50 row(s) inserted.
>>
>>insert into dim1(dkey, dimdata, fk1) values
+>  (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5), (6, 6, 6), (7, 7, 7), (8, 8, 8), (9, 9, 9), (10, 10, 10),
+>  (11, 11, 11), (12, 12, 12), (13, 13, 13), (14, 14, 14), (15, 15, 15), (16, 16, 16), (17, 17, 17), (18, 18, 18), (19, 19, 19);

--- 19 row(s) inserted.
>>insert into dim1(dkey, fk1) values (20, 20);

--- 1 row(s) inserted.
>>
>>insert into dim2(dkey, dimdata, fk1) 
+>  select dkey, dimdata, fk1 from dim1;

--- 20 row(s) inserted.
>>
>>insert into dim3(dkey, dimdata, fk1)
+>  select dkey, dimdata, fk1 from dim1;

--- 20 row(s) inserted.
>>
>>insert into dim4(dkey, dimdata, fk1)
+>  select dkey, dimdata, fk1 from dim1;

--- 20 row(s) inserted.
>>
>>alter table fact  add constraint fact1_fk foreign key (dimkey) references dim1(dkey);

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

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

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

--- SQL operation complete.
>>
>>obey TEST006(create_mvs);
>>--===========================================
>>------------- create mvs section ------------
>>--===========================================
>>
>>create mv sumbyItem
+>        refresh on request
+>        initialized on create
+>        as  select sum(fprice) total_price,
+>                   sum(dimdata) sumdata,
+>                   fitem oitem, fdep odep
+>            from  FACT, DIM1
+>            where dimkey=dkey
+>            group by fitem, fdep;

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

--- SQL operation completed with warnings.
>>
>>create mv sumbyMonthDepState
+>        refresh on request
+>        initialized on create
+>        as  select sum(fprice) total_price,
+>                   sum(dimdata) sumdata,
+>                   fmonth omonth, fyear oyear,
+>                   fdep odep,
+>                   fstate ostate
+>            from  FACT, DIM1
+>            where dimkey=dkey
+>            group by fmonth, fyear,
+>                     fdep,
+>                     fstate;

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

--- SQL operation completed with warnings.
>>
>>-- No range preds.
>>create mv rangeMjv1
+>	refresh on request
+>	initialized on create
+>	as  select fprice, fquant, 
+>		         dimkey, dimdata
+>	    from fact3, dim4
+>	    where dimkey=dkey;

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

--- SQL operation completed with warnings.
>>
>>-- single value range pred
>>create mv rangeMjv2
+>	refresh on request
+>	initialized on create
+>	as  select fprice, fquant, 
+>		         dimkey, dimdata
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and dimdata = 10;

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

--- SQL operation completed with warnings.
>>
>>-- Between range pred
>>create mv rangeMjv3
+>	refresh on request
+>	initialized on create
+>	as  select fprice, fquant, 
+>		         dimkey, dimdata
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and dimdata between 5 and 15;

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

--- SQL operation completed with warnings.
>>
>>-- Between range pred
>>create mv rangeMjv4
+>	refresh on request
+>	initialized on create
+>	as  select fprice, fquant, 
+>		         dimkey, dimdata
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and dimdata*2 between 10 and 30;

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

--- SQL operation completed with warnings.
>>
>>obey TEST006(rewrite_queries);
>>--===========================================
>>--------------- query section ---------------
>>--===========================================
>>
>>set pattern $$QueryName$$ Query1;
>>obey TEST006(compare);
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>        select sum(fprice) total_price,
+>               sum(dimdata) sumdata,
+>               fitem oitem, 
+>               fdep odep
+>        from  FACT, DIM1
+>        where dimkey=dkey
+>        group by fitem, 
+>                 fdep
+>        order by fitem;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.SUMBYITEM                               

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

TOTAL_PRICE            SUMDATA               OITEM        ODEP       
---------------------  --------------------  -----------  -----------

                83.00                    10            1            7
                73.00                    11            1            3
                28.00                     2            1            5
               126.00                    22            1            2
                50.00                    19            2            9
                30.00                    16            2            5
                22.00                     9            2            8
                31.00                    11            2            1
               117.00                    35            2            4
                78.00                    10            2            6
                99.00                    20            2            7
               140.00                    22            3            2
               129.00                    15            3            6
               120.00                    20            3            1
               128.00                    15            3            3
                15.00                    11            3            5
                82.00                     2            3            7
                50.00                     8            4            5
               184.00                    26            4            4
                70.00                     1            4            2
                33.00                     5            4            7
                83.00                    12            5            3
                54.00                     8            5            2
                44.00                    16            6            8
                90.00                    21            6            5
                76.00                     4            6            1
                77.00                     2            6            4
                35.00                    19            7            7
                71.00                    10            7            3
                58.00                    10            8            5
                66.00                    25            9            7
                75.00                    19            9            9
                78.00                    16            9            6
               268.00                    29            9            3
                36.00                    17            9            5
                42.00                     3            9            4

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

--- SQL operation complete.
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>        select sum(fprice) total_price,
+>               sum(dimdata) sumdata,
+>               fitem oitem, 
+>               fdep odep
+>        from  FACT, DIM1
+>        where dimkey=dkey
+>        group by fitem, 
+>                 fdep
+>        order by fitem;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.DIM1                                    
FILE_SCAN                       CAT.OPTIMIZER_RULES.FACT                                    

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

TOTAL_PRICE            SUMDATA               OITEM        ODEP       
---------------------  --------------------  -----------  -----------

                83.00                    10            1            7
                73.00                    11            1            3
                28.00                     2            1            5
               126.00                    22            1            2
                50.00                    19            2            9
                30.00                    16            2            5
                22.00                     9            2            8
                31.00                    11            2            1
               117.00                    35            2            4
                78.00                    10            2            6
                99.00                    20            2            7
               140.00                    22            3            2
               129.00                    15            3            6
               120.00                    20            3            1
               128.00                    15            3            3
                15.00                    11            3            5
                82.00                     2            3            7
                50.00                     8            4            5
               184.00                    26            4            4
                70.00                     1            4            2
                33.00                     5            4            7
                83.00                    12            5            3
                54.00                     8            5            2
                44.00                    16            6            8
                90.00                    21            6            5
                76.00                     4            6            1
                77.00                     2            6            4
                35.00                    19            7            7
                71.00                    10            7            3
                58.00                    10            8            5
                66.00                    25            9            7
                75.00                    19            9            9
                78.00                    16            9            6
               268.00                    29            9            3
                36.00                    17            9            5
                42.00                     3            9            4

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

--- SQL operation complete.
>>
>>log T006_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>
>>--===========================================
>>set pattern $$QueryName$$ Query2;
>>obey TEST006(compare);
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>        select sum(fprice) total_price,
+>               sum(dimdata) sumdata,
+>               fmonth omonth, 
+>               fyear oyear,
+>               fdep odep,
+>               fstate ostate
+>          from  FACT, DIM1
+>                where dimkey=dkey
+>                group by fmonth, 
+>                         fyear,
+>                         fdep,
+>                         fstate
+>                order by fmonth;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.SUMBYMONTHDEPSTATE                      

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

TOTAL_PRICE            SUMDATA               OMONTH       OYEAR        ODEP         OSTATE
---------------------  --------------------  -----------  -----------  -----------  -----------

                57.00                    14            1         2011            2            8
                36.00                     7            1         2012            7            4
                39.00                     6            1         2012            1            8
                42.00                     3            1         2010            4            4
                34.00                     1            2         2012            3            8
                69.00                     8            2         2011            2            6
                48.00                     7            2         2010            3            6
                86.00                    18            2         2012            3            6
                81.00                    14            3         2012            1            7
                36.00                    17            3         2010            5            9
                30.00                    18            3         2011            7            2
                58.00                    10            3         2012            5            7
                92.00                     8            3         2011            3            7
                94.00                    19            3         2012            4            5
                50.00                    19            4         2011            9            1
                59.00                     5            4         2012            7            9
                50.00                     4            4         2012            6            3
                15.00                    11            5         2011            5            7
                50.00                     8            5         2012            5            6
                83.00                    10            5         2010            7            7
                75.00                    19            6         2012            9            6
                77.00                     2            6         2011            4            5
                71.00                    10            6         2011            3            1
                56.00                     3            6         2011            2            8
                40.00                    15            6         2011            7            7
                28.00                     2            6         2011            5            8
                96.00                    18            7         2012            4            6
                73.00                    11            7         2010            3            1
                33.00                     5            7         2012            7            6
                44.00                    16            7         2010            8            4
                23.00                    16            8         2010            4            8
                90.00                     3            9         2010            3            9
                54.00                     8            9         2011            2            2
                88.00                     8            9         2010            4            9
                66.00                     1            9         2010            3            3
                79.00                    11            9         2012            6            5
                30.00                    16            9         2011            5            2
                17.00                    11            9         2011            3            7
                21.00                    18           10         2010            5            9
                46.00                     7           11         2010            3            7
                31.00                    11           11         2011            1            2
                69.00                     3           11         2011            5            3
                78.00                    10           11         2011            6            9
                22.00                     9           12         2010            8            9
                76.00                     4           12         2011            1            2
                35.00                    19           12         2011            7            2
                78.00                    16           12         2012            6            9
                70.00                     1           12         2010            2            9
                82.00                     2           12         2010            7            6
                84.00                    19           12         2010            2            6

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

--- SQL operation complete.
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>        select sum(fprice) total_price,
+>               sum(dimdata) sumdata,
+>               fmonth omonth, 
+>               fyear oyear,
+>               fdep odep,
+>               fstate ostate
+>          from  FACT, DIM1
+>                where dimkey=dkey
+>                group by fmonth, 
+>                         fyear,
+>                         fdep,
+>                         fstate
+>                order by fmonth;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.DIM1                                    
FILE_SCAN                       CAT.OPTIMIZER_RULES.FACT                                    

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

TOTAL_PRICE            SUMDATA               OMONTH       OYEAR        ODEP         OSTATE
---------------------  --------------------  -----------  -----------  -----------  -----------

                57.00                    14            1         2011            2            8
                36.00                     7            1         2012            7            4
                39.00                     6            1         2012            1            8
                42.00                     3            1         2010            4            4
                34.00                     1            2         2012            3            8
                69.00                     8            2         2011            2            6
                48.00                     7            2         2010            3            6
                86.00                    18            2         2012            3            6
                81.00                    14            3         2012            1            7
                36.00                    17            3         2010            5            9
                30.00                    18            3         2011            7            2
                58.00                    10            3         2012            5            7
                92.00                     8            3         2011            3            7
                94.00                    19            3         2012            4            5
                50.00                    19            4         2011            9            1
                59.00                     5            4         2012            7            9
                50.00                     4            4         2012            6            3
                15.00                    11            5         2011            5            7
                50.00                     8            5         2012            5            6
                83.00                    10            5         2010            7            7
                75.00                    19            6         2012            9            6
                77.00                     2            6         2011            4            5
                71.00                    10            6         2011            3            1
                56.00                     3            6         2011            2            8
                40.00                    15            6         2011            7            7
                28.00                     2            6         2011            5            8
                96.00                    18            7         2012            4            6
                73.00                    11            7         2010            3            1
                33.00                     5            7         2012            7            6
                44.00                    16            7         2010            8            4
                23.00                    16            8         2010            4            8
                90.00                     3            9         2010            3            9
                54.00                     8            9         2011            2            2
                88.00                     8            9         2010            4            9
                66.00                     1            9         2010            3            3
                79.00                    11            9         2012            6            5
                30.00                    16            9         2011            5            2
                17.00                    11            9         2011            3            7
                21.00                    18           10         2010            5            9
                46.00                     7           11         2010            3            7
                31.00                    11           11         2011            1            2
                69.00                     3           11         2011            5            3
                78.00                    10           11         2011            6            9
                22.00                     9           12         2010            8            9
                76.00                     4           12         2011            1            2
                35.00                    19           12         2011            7            2
                78.00                    16           12         2012            6            9
                70.00                     1           12         2010            2            9
                82.00                     2           12         2010            7            6
                84.00                    19           12         2010            2            6

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

--- SQL operation complete.
>>
>>log T006_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>
>>--===========================================
>>-- back join
>>-- mv matching a subset of the tables
>>-- range pred on different column - only MJV1 (NotProvided) should match
>>set pattern $$QueryName$$ Query3;
>>obey TEST006(compare);
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey, dimdata
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and fprice between 1 and 60
+>	    order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.RANGEMJV1                               

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

FPRICE        FQUANT       DIMKEY       DIMDATA    
------------  -----------  -----------  -----------

       14.00            7            1            1
       32.00            2            2            2
       12.00            7            2            2
       53.00            9            3            3
       33.00            2            3            3
       29.00            1            5            5
       15.00            8            6            6
       27.00            4            6            6
       41.00            8            7            7
       20.00            1           10           10
       55.00            8           10           10
       43.00            5           11           11
       33.00            5           11           11
       21.00            7           13           13
       47.00            7           13           13
       20.00            2           15           15
       22.00            6           15           15
       20.00            6           15           15
       12.00            8           15           15
       40.00            5           16           16
       27.00            1           16           16
       57.00            7           17           17
       21.00            9           17           17
       21.00            6           17           17
       35.00            2           18           18
       20.00            7           18           18
       39.00            7           18           18

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

--- SQL operation complete.
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey, dimdata
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and fprice between 1 and 60
+>	    order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.DIM4                                    
FILE_SCAN                       CAT.OPTIMIZER_RULES.FACT3                                   

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

FPRICE        FQUANT       DIMKEY       DIMDATA    
------------  -----------  -----------  -----------

       14.00            7            1            1
       32.00            2            2            2
       12.00            7            2            2
       53.00            9            3            3
       33.00            2            3            3
       29.00            1            5            5
       15.00            8            6            6
       27.00            4            6            6
       41.00            8            7            7
       20.00            1           10           10
       55.00            8           10           10
       43.00            5           11           11
       33.00            5           11           11
       21.00            7           13           13
       47.00            7           13           13
       20.00            2           15           15
       22.00            6           15           15
       20.00            6           15           15
       12.00            8           15           15
       40.00            5           16           16
       27.00            1           16           16
       57.00            7           17           17
       21.00            9           17           17
       21.00            6           17           17
       35.00            2           18           18
       20.00            7           18           18
       39.00            7           18           18

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

--- SQL operation complete.
>>
>>log T006_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>
>>--===========================================
>>-- single value on dimdata - should match MJV1 (NotProvided), MJV2 (Provided) and MJV3 (NotProvided).
>>--
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.OPTIMIZER_RULES.RANGEMJV1';

--- SQL operation complete.
>>set pattern $$QueryName$$ Query4;
>>obey TEST006(compare);
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and dimdata = 10
+>	    order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.RANGEMJV1                               

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       55.00            8           10
       66.00            7           10
       20.00            1           10

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

--- SQL operation complete.
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and dimdata = 10
+>	    order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.DIM4                                    
FILE_SCAN                       CAT.OPTIMIZER_RULES.FACT3                                   

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       55.00            8           10
       66.00            7           10
       20.00            1           10

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

--- SQL operation complete.
>>
>>log T006_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>
>>--===========================================
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.OPTIMIZER_RULES.RANGEMJV2';

--- SQL operation complete.
>>set pattern $$QueryName$$ Query5;
>>obey TEST006(compare);
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>            from fact3, dim4
+>            where dimkey=dkey
+>              and dimdata = 10
+>            order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.RANGEMJV2                               

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       55.00            8           10
       66.00            7           10
       20.00            1           10

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

--- SQL operation complete.
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>            from fact3, dim4
+>            where dimkey=dkey
+>              and dimdata = 10
+>            order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.DIM4                                    
FILE_SCAN                       CAT.OPTIMIZER_RULES.FACT3                                   

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       55.00            8           10
       66.00            7           10
       20.00            1           10

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

--- SQL operation complete.
>>
>>log T006_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>
>>--===========================================
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.OPTIMIZER_RULES.RANGEMJV3';

--- SQL operation complete.
>>set pattern $$QueryName$$ Query6;
>>obey TEST006(compare);
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>            from fact3, dim4
+>            where dimkey=dkey
+>              and dimdata = 10
+>            order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.RANGEMJV3                               

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       55.00            8           10
       66.00            7           10
       20.00            1           10

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

--- SQL operation complete.
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>            from fact3, dim4
+>            where dimkey=dkey
+>              and dimdata = 10
+>            order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.DIM4                                    
FILE_SCAN                       CAT.OPTIMIZER_RULES.FACT3                                   

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       55.00            8           10
       66.00            7           10
       20.00            1           10

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

--- SQL operation complete.
>>
>>log T006_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>
>>--===========================================
>>-- identical range on dimdata - should match MJV1 (NotProvided) and MJV3 (Provided).
>>--
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.OPTIMIZER_RULES.RANGEMJV1';

--- SQL operation complete.
>>set pattern $$QueryName$$ Query7;
>>obey TEST006(compare);
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and dimdata between 5 and 15
+>	    order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.RANGEMJV1                               

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       29.00            1            5
       94.00            3            5
       91.00            7            6
       92.00            5            6
       15.00            8            6
       64.00            6            6
       27.00            4            6
       41.00            8            7
       65.00            5            7
       64.00            9            8
       96.00            2            9
       86.00            3            9
       74.00            1            9
       66.00            7           10
       20.00            1           10
       55.00            8           10
       43.00            5           11
       76.00            9           11
       33.00            5           11
       47.00            7           13
       93.00            6           13
       63.00            9           13
       97.00            5           13
       21.00            7           13
       89.00            9           13
       20.00            2           15
       22.00            6           15
       78.00            2           15
       12.00            8           15
       20.00            6           15

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

--- SQL operation complete.
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and dimdata between 5 and 15
+>	    order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.DIM4                                    
FILE_SCAN                       CAT.OPTIMIZER_RULES.FACT3                                   

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       29.00            1            5
       94.00            3            5
       91.00            7            6
       92.00            5            6
       15.00            8            6
       64.00            6            6
       27.00            4            6
       41.00            8            7
       65.00            5            7
       64.00            9            8
       96.00            2            9
       86.00            3            9
       74.00            1            9
       66.00            7           10
       20.00            1           10
       55.00            8           10
       43.00            5           11
       76.00            9           11
       33.00            5           11
       47.00            7           13
       93.00            6           13
       63.00            9           13
       97.00            5           13
       21.00            7           13
       89.00            9           13
       20.00            2           15
       22.00            6           15
       78.00            2           15
       12.00            8           15
       20.00            6           15

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

--- SQL operation complete.
>>
>>log T006_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>
>>--===========================================
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.OPTIMIZER_RULES.RANGEMJV3';

--- SQL operation complete.
>>set pattern $$QueryName$$ Query8;
>>obey TEST006(compare);
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>            from fact3, dim4
+>            where dimkey=dkey
+>              and dimdata between 5 and 15
+>            order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.RANGEMJV3                               

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       29.00            1            5
       94.00            3            5
       91.00            7            6
       92.00            5            6
       15.00            8            6
       64.00            6            6
       27.00            4            6
       41.00            8            7
       65.00            5            7
       64.00            9            8
       96.00            2            9
       86.00            3            9
       74.00            1            9
       66.00            7           10
       20.00            1           10
       55.00            8           10
       43.00            5           11
       76.00            9           11
       33.00            5           11
       47.00            7           13
       93.00            6           13
       63.00            9           13
       97.00            5           13
       21.00            7           13
       89.00            9           13
       20.00            2           15
       22.00            6           15
       78.00            2           15
       12.00            8           15
       20.00            6           15

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

--- SQL operation complete.
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>            from fact3, dim4
+>            where dimkey=dkey
+>              and dimdata between 5 and 15
+>            order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.DIM4                                    
FILE_SCAN                       CAT.OPTIMIZER_RULES.FACT3                                   

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       29.00            1            5
       94.00            3            5
       91.00            7            6
       92.00            5            6
       15.00            8            6
       64.00            6            6
       27.00            4            6
       41.00            8            7
       65.00            5            7
       64.00            9            8
       96.00            2            9
       86.00            3            9
       74.00            1            9
       66.00            7           10
       20.00            1           10
       55.00            8           10
       43.00            5           11
       76.00            9           11
       33.00            5           11
       47.00            7           13
       93.00            6           13
       63.00            9           13
       97.00            5           13
       21.00            7           13
       89.00            9           13
       20.00            2           15
       22.00            6           15
       78.00            2           15
       12.00            8           15
       20.00            6           15

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

--- SQL operation complete.
>>
>>log T006_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>
>>--===========================================
>>-- bigger range - should match only MJV1 (NotProvided).
>>--
>>set pattern $$QueryName$$ Query9;
>>obey TEST006(compare);
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and dimdata between 5 and 16
+>	    order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.RANGEMJV1                               

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       29.00            1            5
       94.00            3            5
       91.00            7            6
       92.00            5            6
       27.00            4            6
       15.00            8            6
       64.00            6            6
       65.00            5            7
       41.00            8            7
       64.00            9            8
       74.00            1            9
       86.00            3            9
       96.00            2            9
       66.00            7           10
       55.00            8           10
       20.00            1           10
       43.00            5           11
       33.00            5           11
       76.00            9           11
       21.00            7           13
       63.00            9           13
       97.00            5           13
       89.00            9           13
       47.00            7           13
       93.00            6           13
       20.00            6           15
       12.00            8           15
       78.00            2           15
       22.00            6           15
       20.00            2           15
       86.00            9           16
       40.00            5           16
       27.00            1           16

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

--- SQL operation complete.
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and dimdata between 5 and 16
+>	    order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.DIM4                                    
FILE_SCAN                       CAT.OPTIMIZER_RULES.FACT3                                   

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       29.00            1            5
       94.00            3            5
       91.00            7            6
       92.00            5            6
       27.00            4            6
       15.00            8            6
       64.00            6            6
       65.00            5            7
       41.00            8            7
       64.00            9            8
       74.00            1            9
       86.00            3            9
       96.00            2            9
       66.00            7           10
       55.00            8           10
       20.00            1           10
       43.00            5           11
       33.00            5           11
       76.00            9           11
       21.00            7           13
       63.00            9           13
       97.00            5           13
       89.00            9           13
       47.00            7           13
       93.00            6           13
       20.00            6           15
       12.00            8           15
       78.00            2           15
       22.00            6           15
       20.00            2           15
       86.00            9           16
       40.00            5           16
       27.00            1           16

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

--- SQL operation complete.
>>
>>log T006_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>
>>--===========================================
>>-- Same range but also IS NULL - should match only MJV1 (NotProvided).
>>--
>>set pattern $$QueryName$$ Query10;
>>obey TEST006(compare);
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and (dimdata between 5 and 15 OR dimdata IS NULL)
+>	    order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.RANGEMJV1                               

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       29.00            1            5
       94.00            3            5
       91.00            7            6
       92.00            5            6
       15.00            8            6
       64.00            6            6
       27.00            4            6
       41.00            8            7
       65.00            5            7
       64.00            9            8
       96.00            2            9
       86.00            3            9
       74.00            1            9
       66.00            7           10
       20.00            1           10
       55.00            8           10
       43.00            5           11
       76.00            9           11
       33.00            5           11
       47.00            7           13
       93.00            6           13
       63.00            9           13
       97.00            5           13
       21.00            7           13
       89.00            9           13
       20.00            2           15
       22.00            6           15
       78.00            2           15
       12.00            8           15
       20.00            6           15

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

--- SQL operation complete.
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and (dimdata between 5 and 15 OR dimdata IS NULL)
+>	    order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.DIM4                                    
FILE_SCAN                       CAT.OPTIMIZER_RULES.FACT3                                   

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       29.00            1            5
       94.00            3            5
       91.00            7            6
       92.00            5            6
       15.00            8            6
       64.00            6            6
       27.00            4            6
       41.00            8            7
       65.00            5            7
       64.00            9            8
       96.00            2            9
       86.00            3            9
       74.00            1            9
       66.00            7           10
       20.00            1           10
       55.00            8           10
       43.00            5           11
       76.00            9           11
       33.00            5           11
       47.00            7           13
       93.00            6           13
       63.00            9           13
       97.00            5           13
       21.00            7           13
       89.00            9           13
       20.00            2           15
       22.00            6           15
       78.00            2           15
       12.00            8           15
       20.00            6           15

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

--- SQL operation complete.
>>
>>log T006_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>
>>--===========================================
>>-- identical range on expression - should match MJV1 (NotProvided), MJV4 (Provided) 
>>-- and maybe one day also MJV3 (Provided).
>>--
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.OPTIMIZER_RULES.RANGEMJV1';

--- SQL operation complete.
>>set pattern $$QueryName$$ Query11;
>>obey TEST006(compare);
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and dimdata*2 between 10 and 30
+>	    order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.RANGEMJV1                               

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       29.00            1            5
       94.00            3            5
       91.00            7            6
       92.00            5            6
       15.00            8            6
       64.00            6            6
       27.00            4            6
       41.00            8            7
       65.00            5            7
       64.00            9            8
       96.00            2            9
       86.00            3            9
       74.00            1            9
       66.00            7           10
       20.00            1           10
       55.00            8           10
       43.00            5           11
       76.00            9           11
       33.00            5           11
       47.00            7           13
       93.00            6           13
       63.00            9           13
       97.00            5           13
       21.00            7           13
       89.00            9           13
       20.00            2           15
       22.00            6           15
       78.00            2           15
       12.00            8           15
       20.00            6           15

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

--- SQL operation complete.
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>	    from fact3, dim4
+>	    where dimkey=dkey
+>	      and dimdata*2 between 10 and 30
+>	    order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.DIM4                                    
FILE_SCAN                       CAT.OPTIMIZER_RULES.FACT3                                   

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       29.00            1            5
       94.00            3            5
       91.00            7            6
       92.00            5            6
       15.00            8            6
       64.00            6            6
       27.00            4            6
       41.00            8            7
       65.00            5            7
       64.00            9            8
       96.00            2            9
       86.00            3            9
       74.00            1            9
       66.00            7           10
       20.00            1           10
       55.00            8           10
       43.00            5           11
       76.00            9           11
       33.00            5           11
       47.00            7           13
       93.00            6           13
       63.00            9           13
       97.00            5           13
       21.00            7           13
       89.00            9           13
       20.00            2           15
       22.00            6           15
       78.00            2           15
       12.00            8           15
       20.00            6           15

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

--- SQL operation complete.
>>
>>log T006_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>
>>--===========================================
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.OPTIMIZER_RULES.RANGEMJV4';

--- SQL operation complete.
>>set pattern $$QueryName$$ Query12;
>>obey TEST006(compare);
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>            from fact3, dim4
+>            where dimkey=dkey
+>              and dimdata*2 between 10 and 30
+>            order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.RANGEMJV4                               

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       29.00            1            5
       94.00            3            5
       91.00            7            6
       92.00            5            6
       15.00            8            6
       64.00            6            6
       27.00            4            6
       41.00            8            7
       65.00            5            7
       64.00            9            8
       96.00            2            9
       86.00            3            9
       74.00            1            9
       66.00            7           10
       20.00            1           10
       55.00            8           10
       43.00            5           11
       76.00            9           11
       33.00            5           11
       47.00            7           13
       93.00            6           13
       63.00            9           13
       97.00            5           13
       21.00            7           13
       89.00            9           13
       20.00            2           15
       22.00            6           15
       78.00            2           15
       12.00            8           15
       20.00            6           15

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

--- SQL operation complete.
>>
>>obey TEST006($$QueryName$$);
>>prepare QueryStmt from
+>      select fprice, fquant, dimkey
+>            from fact3, dim4
+>            where dimkey=dkey
+>              and dimdata*2 between 10 and 30
+>            order by dimkey;

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

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

FILE_SCAN                       CAT.OPTIMIZER_RULES.DIM4                                    
FILE_SCAN                       CAT.OPTIMIZER_RULES.FACT3                                   

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

FPRICE        FQUANT       DIMKEY     
------------  -----------  -----------

       29.00            1            5
       94.00            3            5
       91.00            7            6
       92.00            5            6
       15.00            8            6
       64.00            6            6
       27.00            4            6
       41.00            8            7
       65.00            5            7
       64.00            9            8
       96.00            2            9
       86.00            3            9
       74.00            1            9
       66.00            7           10
       20.00            1           10
       55.00            8           10
       43.00            5           11
       76.00            9           11
       33.00            5           11
       47.00            7           13
       93.00            6           13
       63.00            9           13
       97.00            5           13
       21.00            7           13
       89.00            9           13
       20.00            2           15
       22.00            6           15
       78.00            2           15
       12.00            8           15
       20.00            6           15

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

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

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

End of MXCI Session

