-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
----------------------------------------------------
-- TEST004
-- Unit Test to test range predicate matching.
-- Author: Yuval Sherman, Barry Fritchman
-- Owner: MV Query Rewrite team
----------------------------------------------------

obey TEST004(clean_up);
obey TEST004(clean_up_files);
log LOG004 clear;
obey TEST004(set_up);
obey TEST004(create_mvs);
obey TEST004(match_JQ1);
obey TEST004(match_JQ2);
obey TEST004(match_JQ3);
obey TEST004(match_JQ4);
obey TEST004(match_JQ5);
obey TEST004(match_JQ6);
obey TEST004(match_JQ7);
obey TEST004(match_I);
obey TEST004(match_SI);
obey TEST004(match_LI);
obey TEST004(match_N42);
obey TEST004(match_N61U);
obey TEST004(match_R);
obey TEST004(match_D);
obey TEST004(match_T);
obey TEST004(match_T2);
obey TEST004(match_T6);
obey TEST004(match_TS);
obey TEST004(match_TS3);
obey TEST004(match_YR);
obey TEST004(match_MON);
obey TEST004(match_SEC);
obey TEST004(match_MNT);
obey TEST004(match_MS);
obey TEST004(match_HR);
obey TEST004(match_HM);
obey TEST004(match_HS);
obey TEST004(match_HS_1);
obey TEST004(match_DY);
obey TEST004(match_DH);
obey TEST004(match_DM);
obey TEST004(match_DS);
obey TEST004(float_coverage);
obey TEST004(string_coverage);
obey TEST004(toItemExpr_coverage);
obey TEST004(test_type_constraints);
obey TEST004(test_equality_sets);
obey TEST004(test_rangespec_transformation);
obey TEST004(test_mapvid_pushdown);
obey TEST004(test_end_key);
obey TEST004(test_interval_lp);
-- comp_int_22 test uses too much memory for NT
#ifLINUX
obey TEST004(comp_int_22);
#ifLINUX
obey TEST004(check_nulls);

obey TEST004(clean_up);
exit;

?section clean_up
----------------------------------------------------
------------------ clean up section ----------------
----------------------------------------------------
drop schema range cascade;

?section clean_up_files
----------------------------------------------------
------------------ clean up files section ----------
----------------------------------------------------
sh rm range*;
sh rm RANGE*;

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

control query default MVQR_REWRITE_LEVEL '3';
control query default MVQR_REWRITE_ENABLED_OPTION 'ON';
control query default QUERY_CACHE '0';
control query default MVQR_PUBLISH_TO 'PRIVATE';
control query default REF_CONSTRAINT_NO_ACTION_LIKE_RESTRICT '1';
control query default MULTI_JOIN_THRESHOLD '2';

-- Turn this on so query descriptor will still be generated for single-table
-- queries with no Group By.
control query default MVQR_ALL_JBBS_IN_QD 'ON';

-- This is needed temporarily to work around a problem with automatic loggable
-- columns that causes a timestamp mismatch error when the schema is dropped.
control query default MV_AUTOMATIC_LOGGABLE_COLUMN_MAINTENANCE 'OFF';

obey GetMvDesc;

prepare checkPlan from
  select operator, tname
  from table(explain(NULL, 'QUERYSTMT'))
  where operator like '%_SCAN%'
  order by tname;

create schema range;
set schema range;
set pattern $$MVSchemaName$$ RANGE;


create table fact (
	fday   int,
	fmonth int,
	fyear  int,
	fitem  int,
	fquant int,
	fprice numeric (8,2),
	dimkey int);

create table dim1 (dkey int not null not droppable primary key, dimdata int);
create table dim2 (dkey int not null not droppable primary key, dimdata int);

--alter table fact add constraint fact_fk1 foreign key (dimkey) references dim1(dkey);
--alter table fact add constraint fact_fk2 foreign key (dimkey) references dim2(dkey);

insert into fact values
 ( 4, 1,2010,4,4,83, 8),
 (23, 1,2010,2,4,67, 3),
 (11, 3,2010,1,4,39, 6),
 ( 8, 4,2010,4,2,56, 5),
 (16, 4,2010,3,2,67, 9),
 ( 4, 5,2010,2,4,40, 2),
 ( 6, 7,2010,3,1,63, 6),
 ( 8, 7,2010,5,4,90, 3),
 (19, 7,2010,3,2,50, 8),
 ( 1, 8,2010,3,3, 4, 3),
 ( 2, 9,2010,3,4, 8, 1),
 ( 4, 9,2010,2,5,62, 8),
 (15, 9,2010,5,3,45, 4),
 (31,10,2010,2,1,39, 8),
 (12,11,2010,4,5,27, 9),
 ( 1,12,2010,3,5,21, 8),
 (10,12,2010,3,5,31, 1),
 (22, 1,2011,5,4,12, 2),
 ( 7, 6,2011,1,4,73, 5),
 (10, 6,2011,5,2,61, 1),
 (16, 6,2011,1,4,76, 4),
 (31, 6,2011,3,2,66, 7),
 (22, 7,2011,4,1,15, 3),
 (25, 7,2011,5,5,75, 7),
 ( 5, 8,2011,1,4,87, 3),
 (10,10,2011,4,1,66, 6),
 (24,11,2011,4,3,88, 8),
 (13,12,2011,1,1,59, 7),
 (15,12,2011,3,3,95, 5),
 (25,12,2011,2,3,69, 4),
 ( 9, 1,2012,2,2,71, 9),
 ( 1, 2,2012,2,1,49, 6),
 ( 9, 2,2012,4,3,65, 6),
 ( 9, 2,2012,3,3,67, 3),
 (17, 2,2012,2,3,98, 3),
 ( 1, 3,2012,2,4,55, 7),
 (10, 3,2012,5,4,11, 8),
 (28, 3,2012,1,5, 7, 5),
 (21, 4,2012,1,3,32, 8),
 (23, 4,2012,4,1,25, 4),
 (27, 4,2012,4,2,61, 5),
 (28, 5,2012,5,1,44, 8),
 ( 2, 6,2012,4,3,31, 2),
 (21, 6,2012,4,3,70, 5),
 (29, 6,2012,5,1,51, 5),
 ( 9, 7,2012,1,5,14, 4),
 (29, 7,2012,2,3,53, 5),
 (21, 8,2012,3,1,58,10),
 (18,12,2012,2,3,20,10),
 (20,12,2012,5,1, 4, 6); 

insert into dim1 values
 (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10);

insert into dim2 values
 (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10);

create table numtypes
(
  i    int not null,
  si   smallint,
  li   largeint check(li<10000000000),
  n42  numeric(4,2),
  n61u numeric(6,1) unsigned,
  r    real,
  d    double precision,
  f5   float(5)
);


create table dttypes
(
  i  int,
  d  date,
  t  time,
  t2 time(2),
  t6 time(6),
  ts timestamp,
  ts0 timestamp(0),
  ts3 timestamp(3),
  yr interval year not null check(yr >= interval '0' year),
  ym interval year(3) to month,
  mon interval month(4),
  sec interval second(2, 4),
  mnt interval minute(1),
  ms interval minute to second,
  hr interval hour(8),
  hm interval hour to minute,
  hs interval hour to second,
  dy interval day(3),
  dh interval day to hour,
  dm interval day to minute,
  ds interval day(7) to second(6)
);

create table t_rangespec
  (int_1       smallint default 0 not null
  ,int_2       smallint default 0 not null
  ,int_3       smallint default 0 not null
  ,primary key (int_1, int_2, int_3)
  ) no partition;
  
insert into t_rangespec values 
  (1, 1,  3), (1, 1,  2), (1, 1,  1), (1, 1,  0),
  (1, 1, -1), (1, 1, -2), (1, 1, -3), (1, 1, -4);

?section create_mvs
--===========================================
-- MJVs
--===========================================

-- No range preds.
create mv range_mjv1
	refresh on request
	initialized on create
	as  select fprice, fquant, 
		         dimkey, dimdata
	    from fact, dim1
	    where dimkey=dkey;

set pattern $$MVName$$ RANGE_MJV1;
obey TEST004(dump_MV);

-- No range preds, only dimdata*2 provided.
-- Not back-joinable.
create mv range_mjv11
	recompute
	initialized on create
	as  select fprice, fquant, 
		   dimdata*2 data2
	    from fact, dim1
	    where dimkey=dkey;

set pattern $$MVName$$ RANGE_MJV11;
obey TEST004(dump_MV);

-- single value range pred
create mv range_mjv2
	refresh on request
	initialized on create
	as  select fprice, fquant, 
		         dimkey, dimdata
	    from fact, dim1
	    where dimkey=dkey
	      and dimdata = 10;

set pattern $$MVName$$ RANGE_MJV2;
obey TEST004(dump_MV);

-- Between range pred
create mv range_mjv3
	refresh on request
	initialized on create
	as  select fprice, fquant, 
		         dimkey, dimdata
	    from fact, dim1
	    where dimkey=dkey
	      and dimdata between 5 and 15;

set pattern $$MVName$$ RANGE_MJV3;
obey TEST004(dump_MV);

-- Between range pred
create mv range_mjv4
	refresh on request
	initialized on create
	as  select fprice, fquant, 
		         dimkey, dimdata
	    from fact, dim1
	    where dimkey=dkey
	      and dimdata*2 between 10 and 30;

set pattern $$MVName$$ RANGE_MJV4;
obey TEST004(dump_MV);

--==============================================
-- Single-table MVs to test ranges + constraints
--==============================================

create mv mvrngcon_i(x)
    refresh on request
    initialized on create
    as select i
       from numtypes
       where i > 100;

set pattern $$MVName$$ MVRNGCON_I;
obey TEST004(dump_MV);

create mv mvrngcon_si(x,y)
    refresh on request
    initialized on create
    as select i, si
       from numtypes
       where si between 50 and 33000;

set pattern $$MVName$$ MVRNGCON_SI;
obey TEST004(dump_MV);

create mv mvrngcon_li(x,y)
    refresh on request
    initialized on create
    as select i, li
       from numtypes
       where li < 10000000000;

set pattern $$MVName$$ MVRNGCON_LI;
obey TEST004(dump_MV);

create mv mvrngcon_n42(x,y)
    refresh on request
    initialized on create
    as select i, n42
       from numtypes
       where n42 < 100;

set pattern $$MVName$$ MVRNGCON_N42;
obey TEST004(dump_MV);
       
create mv mvrngcon_n61u(x,y)
    refresh on request
    initialized on create
    as select i, n61u
       from numtypes
       where n61u between 0 and 100;

set pattern $$MVName$$ MVRNGCON_N61U;
obey TEST004(dump_MV);
  
create mv mvrngcon_r(x,y)
    refresh on request
    initialized on create
    as select i, r
       from numtypes
       where r < 3.40282347e+45;

set pattern $$MVName$$ MVRNGCON_R;
obey TEST004(dump_MV);


--==========================================================
-- Additional MVs to test ranges on date/time/interval types
--==========================================================

create mv mvrngcon_d(x,y)
    refresh on request
    initialized on create
    as select i, d
       from dttypes
       where d > date'2000-01-30';

set pattern $$MVName$$ MVRNGCON_D;
obey TEST004(dump_MV);
	    
create mv mvrngcon_t(x,y)
    refresh on request
    initialized on create
    as select i, t
       from dttypes
       where t between time'10:00:00' and time'11:25:30';

set pattern $$MVName$$ MVRNGCON_T;
obey TEST004(dump_MV);
	    
create mv mvrngcon_t2(x,y)
    refresh on request
    initialized on create
    as select i, t2
       from dttypes
       where t2 between time'10:00:00.20' and time'11:25:30.45';

set pattern $$MVName$$ MVRNGCON_T2;
obey TEST004(dump_MV);
	    
create mv mvrngcon_t6(x,y)
    refresh on request
    initialized on create
    as select i, t6
       from dttypes
       where t6 < time'01:15:30.123456';

set pattern $$MVName$$ MVRNGCON_T6;
obey TEST004(dump_MV);
	    
create mv mvrngcon_ts(x,y)
    refresh on request
    initialized on create
    as select i, ts
       from dttypes
       where ts < timestamp'2005-10-12 14:45:01.123456';

set pattern $$MVName$$ MVRNGCON_TS;
obey TEST004(dump_MV);

create mv mvrngcon_ts3(x,y)
    refresh on request
    initialized on create
    as select i, ts3
       from dttypes
       where ts3 < timestamp'2005-10-12 14:45:01.125'   ;

set pattern $$MVName$$ MVRNGCON_TS3;
obey TEST004(dump_MV);

create mv mvrngcon_yr(x,y)
    refresh on request
    initialized on create
    as select i, yr
       from dttypes
       where yr < interval '20' year;

set pattern $$MVName$$ MVRNGCON_YR;
obey TEST004(dump_MV);

create mv mvrngcon_ym(x,y)
    refresh on request
    initialized on create
    as select i, ym
       from dttypes
       where ym between interval '2' year and interval '3-6' year to month;

set pattern $$MVName$$ MVRNGCON_YM;
obey TEST004(dump_MV);

create mv mvrngcon_mon(x,y)
    refresh on request
    initialized on create
    as select i, mon
       from dttypes
       where mon > interval'5000'month(4);

set pattern $$MVName$$ MVRNGCON_MON;
obey TEST004(dump_MV);

-- Note that the range in the MV definition extends beyond the range allowable
-- for the type.
create mv mvrngcon_sec(x,y)
    refresh on request
    initialized on create
    as select i, sec
       from dttypes
       where sec between interval'3.5'second and interval'125.45'second(3,2);

set pattern $$MVName$$ MVRNGCON_SEC;
obey TEST004(dump_MV);

create mv mvrngcon_mnt(x,y)
    refresh on request
    initialized on create
    as select i, mnt
       from dttypes
       where mnt < interval'5'minute and mnt > interval'30.5'second(2,1);

set pattern $$MVName$$ MVRNGCON_MNT;
obey TEST004(dump_MV);

create mv mvrngcon_ms(x,y)
    refresh on request
    initialized on create
    as select i, ms
       from dttypes
       where ms = interval'345'second(3) or ms between interval-'5'minute and interval'5'minute;

set pattern $$MVName$$ MVRNGCON_MS;
obey TEST004(dump_MV);

create mv mvrngcon_hr(x,y)
    refresh on request
    initialized on create
    as select i, hr
       from dttypes
       where hr in (interval'30'day, interval'60'minute, interval'7200'second(4)) or hr > interval'10000'hour(5);

set pattern $$MVName$$ MVRNGCON_HR;
obey TEST004(dump_MV);

create mv mvrngcon_hm(x,y)
    refresh on request
    initialized on create
    as select i, hm
       from dttypes
       where hm < interval'90'minute and hm >= interval'1'hour;

set pattern $$MVName$$ MVRNGCON_HM;
obey TEST004(dump_MV);

-- Predicate on hs covers entire range of possible values, so rangepred will
-- be generated without any ops in descriptor, indicating that the only requirement
-- is that the column has a non-null value.
create mv mvrngcon_hs(x,y)
    refresh on request
    initialized on create
    as select i, hs
       from dttypes
       where hs >= interval-'99:59:59.999999'hour to second and hs <= interval'99:59:59.999999'hour to second;

set pattern $$MVName$$ MVRNGCON_HS;
obey TEST004(dump_MV);

-- Predicate on hs covers all but the minimum and maximum value for the interval.
create mv mvrngcon_hs_1(x,y)
    refresh on request
    initialized on create
    as select i, hs
       from dttypes
       where hs > interval-'99:59:59.999999'hour to second and hs < interval'99:59:59.999999'hour to second;

set pattern $$MVName$$ MVRNGCON_HS_1;
obey TEST004(dump_MV);

-- Predicate on hs covers interval's max value with a bounded range (no use of >).
create mv mvrngcon_hs_2(x,y)
    refresh on request
    initialized on create
    as select i, hs
       from dttypes
       where hs between interval'0'second and interval'99:59:59.999999'hour to second;

set pattern $$MVName$$ MVRNGCON_HS_2;
obey TEST004(dump_MV);

create mv mvrngcon_dy(x,y)
    refresh on request
    initialized on create
    as select i, dy
       from dttypes
       where dy = interval'100'day(3);

set pattern $$MVName$$ MVRNGCON_DY;
obey TEST004(dump_MV);

-- The predicate in the MV definition is not satisfiable because the range covers
-- only values that are beyond the range allowable for the type. Therefore, the
-- MV will always be empty.
create mv mvrngcon_dh(x,y)
    refresh on request
    initialized on create
    as select i, dh
       from dttypes
       where dh > interval'100'day(3);

set pattern $$MVName$$ MVRNGCON_DH;
obey TEST004(dump_MV);

-- The predicate in the MV definition is not satisfiable because the range covers
-- only values that are below the range allowable for the type. Therefore, the
-- MV will always be empty.
create mv mvrngcon_dm(x,y)
    refresh on request
    initialized on create
    as select i, dm
       from dttypes
       where dm < interval-'100'day(3);

set pattern $$MVName$$ MVRNGCON_DM;
obey TEST004(dump_MV);

create mv mvrngcon_ds(x,y)
    refresh on request
    initialized on create
    as select i, ds
       from dttypes
       where ds between interval-'1234567 10:20:30.123456'day(7) to second(6) and interval'365'day(3);

set pattern $$MVName$$ MVRNGCON_DS;
obey TEST004(dump_MV);


?section RANGE_JQ1
prepare QueryStmt from
      select fprice, fquant, 
             dimkey, dimdata
      from fact, dim1
      where dimkey=dkey
        and fprice between 1 and 6
      order by dimkey, fprice, fquant;

?section match_JQ1
--===========================================
-- JQ1: range pred on different column - only MJV1 (NotProvided) should match
--===========================================
-- The ORDER BY clause was added to avoid sorting the resulting XML

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST004(RANGE_JQ1);

set pattern $$QueryName$$ RANGE_JQ1;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo # All comments should be ignored by QMS. >> range1.in;
sh echo # CHECK request does nothing at present, but should not interfere with subsequent requests >> range1.in;
sh echo # in the input file. >> range1.in;
sh echo CHECK >> range1.in;
sh echo # Likewise, an unknown requests is just noted in the log. No exception is thrown, and >> range1.in;
sh echo # processing of subsequent requests is continued. >> range1.in;
sh echo BLARG >> range1.in;
sh echo INITIALIZE             >> range1.in;
sh echo PUBLISH RANGE_MJV1.xml >> range1.in;
sh echo PUBLISH RANGE_MJV2.xml >> range1.in;
sh echo PUBLISH RANGE_MJV3.xml >> range1.in;
sh echo PUBLISH RANGE_MJV4.xml >> range1.in;
sh echo # STATS request writes to log, not output file. >> range1.in;
sh echo STATS                  >> range1.in;
sh echo MATCH RANGE_JQ1.xml    >> range1.in;
sh echo # CLEANUP has the effect of deleting the singleton instance of the Qms object for command-line QMS. >> range1.in;
sh echo # This gets rid of any MVs previously published in the input file or read from metadata by INITIALIZE. >> range1.in;
sh echo CLEANUP >> range1.in;
sh echo # The same match request will now not match anything. >> range1.in;
sh echo MATCH RANGE_JQ1.xml    >> range1.in;
sh echo # Now publish the matching MV and match again. The result descriptor should have it, but not the <Info> elements saying why the others do not match. >> range1.in;
sh echo PUBLISH RANGE_MJV1.xml >> range1.in;
sh echo MATCH RANGE_JQ1.xml    >> range1.in;

-- Test handling of empty catalog in QmsInitializer::processCatalogs().
create catalog mvqr_t004_emptycat;
sh sh -c "$QMS range1.in range1.out";
drop catalog mvqr_t004_emptycat;

log;
sh cat range1.out >> LOG004;
log LOG004;

-- Verify the MV is picked by the optimizer.
obey TEST004(compare);

-- Test use of nonexistent input file.
sh sh -c "$QMS alkjdlfksuytk range1.out";

-- Test use of illegal output file name.
sh sh -c "$QMS range1.in xyz:/range1.out";

-- This test will fail when DEFAULTS request is implemented, prompting us to
-- change the expected file. This ensures we won't forget to test it.
sh echo # DEFAULTS request not yet implemented, but should be recognized.
sh echo DEFAULTS >> testDefaults.in;
sh sh -c "$QMS testDefaults.in testDefaults.out";
log;
sh cat testDefaults.out >> LOG004;
log LOG004;

?section RANGE_JQ2
prepare QueryStmt from
      select fprice, fquant, 
             dimkey
      from fact, dim1
      where dimkey=dkey
        and dimdata = 10
      order by dimkey, fprice, fquant;

?section match_JQ2
--===========================================
-- JQ2: single value on dimdata - should match MJV1 (NotProvided), MJV2 (Provided) and MJV3 (NotProvided).
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST004(RANGE_JQ2);

set pattern $$QueryName$$ RANGE_JQ2;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE             >> range2.in;
sh echo PUBLISH RANGE_MJV1.xml >> range2.in;
sh echo PUBLISH RANGE_MJV2.xml >> range2.in;
sh echo PUBLISH RANGE_MJV3.xml >> range2.in;
sh echo PUBLISH RANGE_MJV4.xml >> range2.in;
sh echo MATCH RANGE_JQ2.xml    >> range2.in;

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

log;
sh cat range2.out >> LOG004;
cqd mvqr_rewrite_candidates 'CAT.RANGE.RANGE_MJV2';
log LOG004;

-- Verify the MV is picked by the optimizer.
obey TEST004(compare);

?section RANGE_JQ3
prepare QueryStmt from
      select fprice, fquant, 
             dimkey
      from fact, dim1
      where dimkey=dkey
        and dimdata between 5 and 15
      order by dimkey, fprice, fquant;

?section match_JQ3
--===========================================
-- JQ3: identical range on dimdata - should match MJV1 (NotProvided) and MJV3 (Provided).
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST004(RANGE_JQ3);

set pattern $$QueryName$$ RANGE_JQ3;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE             >> range3.in;
sh echo PUBLISH RANGE_MJV1.xml >> range3.in;
sh echo PUBLISH RANGE_MJV2.xml >> range3.in;
sh echo PUBLISH RANGE_MJV3.xml >> range3.in;
sh echo PUBLISH RANGE_MJV4.xml >> range3.in;
sh echo MATCH RANGE_JQ3.xml    >> range3.in;

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

log;
sh cat range3.out >> LOG004;
cqd mvqr_rewrite_candidates 'CAT.RANGE.RANGE_MJV3';
log LOG004;

-- Verify the MV is picked by the optimizer.
obey TEST004(compare);

?section RANGE_JQ4
prepare QueryStmt from
      select fprice, fquant, 
             dimkey
      from fact, dim1
      where dimkey=dkey
        and dimdata between 5 and 16
      order by dimkey, fprice, fquant;

?section match_JQ4
--===========================================
-- JQ4: bigger range - should match only MJV1 (NotProvided).
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST004(RANGE_JQ4);

set pattern $$QueryName$$ RANGE_JQ4;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE             >> range4.in;
sh echo PUBLISH RANGE_MJV1.xml >> range4.in;
sh echo PUBLISH RANGE_MJV2.xml >> range4.in;
sh echo PUBLISH RANGE_MJV3.xml >> range4.in;
sh echo PUBLISH RANGE_MJV4.xml >> range4.in;
sh echo MATCH RANGE_JQ4.xml    >> range4.in;

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

log;
sh cat range4.out >> LOG004;
log LOG004;

-- Verify the MV is picked by the optimizer.
obey TEST004(compare);

?section RANGE_JQ5
prepare QueryStmt from
      select fprice, fquant, 
             dimkey
      from fact, dim1
      where dimkey=dkey
        and (dimdata between 5 and 15 OR dimdata IS NULL)
      order by dimkey, fprice, fquant;

?section match_JQ5
--===========================================
-- JQ5: Same range but also IS NULL - Should match only MJV1 (NotProvided).
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

obey TEST004(RANGE_JQ5);

set pattern $$QueryName$$ RANGE_JQ5;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE             >> range5.in;
sh echo PUBLISH RANGE_MJV1.xml >> range5.in;
sh echo PUBLISH RANGE_MJV2.xml >> range5.in;
sh echo PUBLISH RANGE_MJV3.xml >> range5.in;
sh echo PUBLISH RANGE_MJV4.xml >> range5.in;
sh echo MATCH RANGE_JQ5.xml    >> range5.in;

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

log;
sh cat range5.out >> LOG004;
log LOG004;

-- Verify the MV is picked by the optimizer.
obey TEST004(compare);

?section RANGE_JQ6
prepare QueryStmt from
      select fprice, fquant
      from fact, dim1
      where dimkey=dkey
        and dimdata*2 between 10 and 30
      order by fprice, fquant;

?section match_JQ6
--===========================================
-- JQ6: Between on expression - 
-- Should match MJV1 and MJV11 (NotProvided)
-- and MJV4 (Provided).
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- identical range on expression - should match MJV1, MJV11 (NotProvided), MJV4 (Provided) 
-- and maybe one day also MJV3 (Provided).
obey TEST004(RANGE_JQ6);

set pattern $$QueryName$$ RANGE_JQ6;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE             >> range6.in;
sh echo PUBLISH RANGE_MJV1.xml >> range6.in;
sh echo PUBLISH RANGE_MJV11.xml >> range6.in;
sh echo PUBLISH RANGE_MJV2.xml >> range6.in;
sh echo PUBLISH RANGE_MJV3.xml >> range6.in;
sh echo PUBLISH RANGE_MJV4.xml >> range6.in;
sh echo MATCH RANGE_JQ6.xml    >> range6.in;

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

log;
sh cat range6.out >> LOG004;
log LOG004;

-- Verify MV11 is picked by the optimizer.
cqd mvqr_rewrite_candidates 'CAT.RANGE.RANGE_MJV11';
obey TEST004(compare);


-- Verify MV4 is picked by the optimizer.
cqd mvqr_rewrite_candidates 'CAT.RANGE.RANGE_MJV4';
obey TEST004(compare);


?section RANGE_JQ7
prepare QueryStmt from
      select fprice, fquant 
      from fact, dim1
      where dimkey=dkey
        and dimdata*2 between 15 and 30
      order by fprice, fquant;

?section match_JQ7
--===========================================
-- JQ7: Between on expression - 
-- Should match MJV1 and MJV11 (NotProvided)
-- and MJV4 (Provided).
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- Subsumed range on expression - should match MJV1, MJV11 (NotProvided), MJV4 (NotProvided) 
obey TEST004(RANGE_JQ7);

set pattern $$QueryName$$ RANGE_JQ7;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE             >> range7.in;
sh echo PUBLISH RANGE_MJV1.xml >> range7.in;
sh echo PUBLISH RANGE_MJV11.xml >> range7.in;
sh echo PUBLISH RANGE_MJV2.xml >> range7.in;
sh echo PUBLISH RANGE_MJV3.xml >> range7.in;
sh echo PUBLISH RANGE_MJV4.xml >> range7.in;
sh echo MATCH RANGE_JQ7.xml    >> range7.in;

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

log;
sh cat range7.out >> LOG004;
log LOG004;

-- Verify MV11 is picked by the optimizer.
cqd mvqr_rewrite_candidates 'CAT.RANGE.RANGE_MJV11';
obey TEST004(compare);

-- Verify MV4 is picked by the optimizer.
cqd mvqr_rewrite_candidates 'CAT.RANGE.RANGE_MJV4';
obey TEST004(compare);


?section match_I
-- Queries requiring application of constraints to ranges
--===========================================
-- I: type-implied constraint and not null constraint change range from
--    [100..MAX] to [100..2147483647]+NULL, allowing match (Provided) with MVRNGCON_I.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should match mvrngcon_i (Provided) after application of not null constraint
prepare QueryStmt from
      select i
      from numtypes
      where i is null or i > 100
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_I;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> range_i.in;
sh echo PUBLISH MVRNGCON_I.xml  >> range_i.in;
sh echo MATCH RANGECONSTR_I.xml >> range_i.in;

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

log;
sh cat range_i.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ numtypes;
set pattern $$replay_pred$$ 'i is null or i > cast(''100'' as int)';
set pattern $$replay_mv$$ MVRNGCON_I;
set pattern $$replay_filebase$$ range_i;
set pattern $$QueryName$$ RANGECONSTR_I_CAST;
obey TEST004(replay);
log LOG004;

?section match_SI
--===========================================
-- SI: type-implied constraint changes range from [50..MAX] to [50..32767],
--     allowing match (Provided) with MVRNGCON_SI.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should match mvrngcon_si (Provided) after application of type constraint
prepare QueryStmt from
      select i
      from numtypes
      where si >= 50
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_SI;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_si.in;
sh echo PUBLISH MVRNGCON_SI.xml  >> range_si.in;
sh echo MATCH RANGECONSTR_SI.xml >> range_si.in;

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

log;
sh cat range_si.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ numtypes;
set pattern $$replay_pred$$ 'si >= cast(''50'' as smallint)';
set pattern $$replay_mv$$ MVRNGCON_SI;
set pattern $$replay_filebase$$ range_si;
set pattern $$QueryName$$ RANGECONSTR_SI_CAST;
obey TEST004(replay);
log LOG004;

?section match_LI
--===========================================
-- LI: check constraint changes range from [5000000..MAX] to [5000000..10000000000),
--     allowing match (NotProvided) with MVRNGCON_LI.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should match mvrngcon_li (NotProvided) after application of check constraint
prepare QueryStmt from
      select i
      from numtypes
      where li >= 5000000
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_LI;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_li.in;
sh echo PUBLISH MVRNGCON_LI.xml  >> range_li.in;
sh echo MATCH RANGECONSTR_LI.xml >> range_li.in;

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

log;
sh cat range_li.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ numtypes;
set pattern $$replay_pred$$ 'li >= cast(''5000000'' as largeint)';
set pattern $$replay_mv$$ MVRNGCON_LI;
set pattern $$replay_filebase$$ range_li;
set pattern $$QueryName$$ RANGECONSTR_LI_CAST;
obey TEST004(replay);
log LOG004;

?section match_N42
--===========================================
-- N42: type-implied constraint changes range from [1001..<MAX>] to [1001..9999],
--      allowing match (NotProvided) with MVRNGCON_N42.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should match mvrngcon_n42 (NotProvided) after application of type constraint
prepare QueryStmt from
      select i
      from numtypes
      where n42 > 10
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_N42;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE                >> range_n42.in;
sh echo PUBLISH MVRNGCON_N42.xml  >> range_n42.in;
sh echo MATCH RANGECONSTR_N42.xml >> range_n42.in;

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

log;
sh cat range_n42.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ numtypes;
set pattern $$replay_pred$$ 'n42 > cast(''10'' as numeric(4,2))';
set pattern $$replay_mv$$ MVRNGCON_N42;
set pattern $$replay_filebase$$ range_n42;
set pattern $$QueryName$$ RANGECONSTR_N42_CAST;
obey TEST004(replay);
-- Once again, casting as a decimal.
set pattern $$replay_pred$$ 'n42 > cast(''10'' as decimal(4,2))';
sh rm $$QueryName$$.xml;
obey TEST004(replay);
log LOG004;

?section match_N61U
--===========================================
-- N61U: type-implied constraint changes range from [<MIN>..499] to [0...499],
--       allowing match (NotProvided) with MVRNGCON_N61U.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should match mvrngcon_n61u (NotProvided) after application of type constraint
prepare QueryStmt from
      select i
      from numtypes
      where n61u < 50
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_N61U;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE                 >> range_n61u.in;
sh echo PUBLISH MVRNGCON_N61U.xml  >> range_n61u.in;
sh echo MATCH RANGECONSTR_N61U.xml >> range_n61u.in;

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

log;
sh cat range_n61u.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ numtypes;
set pattern $$replay_pred$$ 'n61u < cast(''50'' as numeric(6,1) unsigned)';
set pattern $$replay_mv$$ MVRNGCON_N61U;
set pattern $$replay_filebase$$ range_n61u;
set pattern $$QueryName$$ RANGECONSTR_N61U_CAST;
obey TEST004(replay);
log LOG004;

?section match_R
--===========================================
-- R: type-implied constraint changes range from (0..<MAX>) to (0..3.40282e+038],
--    allowing match (NotProvided) with MVRNGCON_R.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should match mvrngcon_r (NotProvided) after application of type constraint
prepare QueryStmt from
      select i
      from numtypes
      where r > 0
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_R;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> range_r.in;
sh echo PUBLISH MVRNGCON_R.xml  >> range_r.in;
sh echo MATCH RANGECONSTR_R.xml >> range_r.in;

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

log;
sh cat range_r.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ numtypes;
set pattern $$replay_pred$$ 'r > cast(''0'' as real)';
set pattern $$replay_mv$$ MVRNGCON_R;
set pattern $$replay_filebase$$ range_r;
set pattern $$QueryName$$ RANGECONSTR_R_CAST;
obey TEST004(replay);
log LOG004;

?section match_D
--===========================================
-- D: does not match because query range includes values that precede MV range.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should not match anything
prepare QueryStmt from
      select i
      from dttypes
      where d > date'1999-12-31'
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_D;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> range_d.in;
sh echo PUBLISH MVRNGCON_D.xml  >> range_d.in;
sh echo MATCH RANGECONSTR_D.xml >> range_d.in;

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

log;
sh cat range_d.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 'd > cast(''1999-12-31'' as date)';
set pattern $$replay_mv$$ MVRNGCON_D;
set pattern $$replay_filebase$$ range_d;
set pattern $$QueryName$$ RANGECONSTR_D_CAST;
obey TEST004(replay);
log LOG004;

--===========================================
-- D part 2: provided match, same range specified a different way in query.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should get provided match with mvrngcon_d
prepare QueryStmt from
      select i
      from dttypes
      where not d <= date'2000-01-30'
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_D_2;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> range_d_2.in;
sh echo PUBLISH MVRNGCON_D.xml  >> range_d_2.in;
sh echo MATCH RANGECONSTR_D_2.xml >> range_d_2.in;

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

log;
sh cat range_d_2.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 'not d <= cast(''2000-01-30'' as date)';
set pattern $$replay_mv$$ MVRNGCON_D;
set pattern $$replay_filebase$$ range_d_2;
set pattern $$QueryName$$ RANGECONSTR_D_2_CAST;
obey TEST004(replay);
log LOG004;

--===========================================
-- D part 3: not provided match, range ends short of max value.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should get not provided match with mvrngcon_d
prepare QueryStmt from
      select i
      from dttypes
      where d > date'2000-01-30' and d < date'2010-12-31'
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_D_3;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> range_d_3.in;
sh echo PUBLISH MVRNGCON_D.xml  >> range_d_3.in;
sh echo MATCH RANGECONSTR_D_3.xml >> range_d_3.in;

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

log;
sh cat range_d_3.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 'd > cast(''2000-01-30'' as date) and d < cast(''2010-12-31'' as date)';
set pattern $$replay_mv$$ MVRNGCON_D;
set pattern $$replay_filebase$$ range_d_3;
set pattern $$QueryName$$ RANGECONSTR_D_3_CAST;
obey TEST004(replay);
log LOG004;

?section match_T
--===========================================
-- T: no match; last element in IN list is not part of MV's range for t
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- time'11:25:31' prevents a match
prepare QueryStmt from
      select i
      from dttypes
      where t in (time'10:00:00', time'11:25:30', time'10:30:19', time'11:25:31')
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_T;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> range_t.in;
sh echo PUBLISH MVRNGCON_T.xml  >> range_t.in;
sh echo MATCH RANGECONSTR_T.xml >> range_t.in;

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

log;
sh cat range_t.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 't in (cast(''10:00:00'' as time(0)), cast(''11:25:30'' as time(0)), cast(''10:30:19'' as time(0)), cast(''11:25:31'' as time(0)))';
set pattern $$replay_mv$$ MVRNGCON_T;
set pattern $$replay_filebase$$ range_t;
set pattern $$QueryName$$ RANGECONSTR_T_CAST;
obey TEST004(replay);
log LOG004;

--===========================================
-- T part 2: provided match, same range specified a different way in query.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should match mvrngcon_t (Provided)
prepare QueryStmt from
      select i
      from dttypes
      where not t < time'10:00:00' and not t > time'11:25:30'
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_T_2;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> range_t_2.in;
sh echo PUBLISH MVRNGCON_T.xml  >> range_t_2.in;
sh echo MATCH RANGECONSTR_T_2.xml >> range_t_2.in;

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

log;
sh cat range_t_2.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 'not t < cast(''10:00:00'' as time(0)) and not t > cast(''11:25:30'' as time(0))';
set pattern $$replay_mv$$ MVRNGCON_T;
set pattern $$replay_filebase$$ range_t_2;
set pattern $$QueryName$$ RANGECONSTR_T_2_CAST;
obey TEST004(replay);
log LOG004;

--===========================================
-- T part 3: not provided match, all items in IN list contained in MV range.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should match mvrngcon_t (Not Provided)
prepare QueryStmt from
      select i
      from dttypes
      where t in (time'10:00:00', time'11:25:30', time'10:30:19')
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_T_3;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE              >> range_t_3.in;
sh echo PUBLISH MVRNGCON_T.xml  >> range_t_3.in;
sh echo MATCH RANGECONSTR_T_3.xml >> range_t_3.in;

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

log;
sh cat range_t_3.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 't in (cast(''10:00:00'' as time(0)), cast(''11:25:30'' as time(0)), cast(''10:30:19'' as time(0)))';
set pattern $$replay_mv$$ MVRNGCON_T;
set pattern $$replay_filebase$$ range_t_3;
set pattern $$QueryName$$ RANGECONSTR_T_3_CAST;
obey TEST004(replay);
log LOG004;

?section match_T2
--===========================================
-- T2: provided match, excess fractional seconds precision truncated
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should match mvrngcon_t2, .000001 added to end of range, but this results in
-- a constant which exceeds the precision of the range column. Truncation puts it
-- within the required matching range.
prepare QueryStmt from
      select i
      from dttypes
      where t2 between time'10:00:00.20' and time'11:25:30.450001'
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_T2;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_t2.in;
sh echo PUBLISH MVRNGCON_T2.xml  >> range_t2.in;
sh echo MATCH RANGECONSTR_T2.xml >> range_t2.in;

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

log;
sh cat range_t2.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 't2 between cast(''10:00:00.20'' as time(2)) and cast(''11:25:30.450001'' as time(2))';
set pattern $$replay_mv$$ MVRNGCON_T2;
set pattern $$replay_filebase$$ range_t2;
set pattern $$QueryName$$ RANGECONSTR_T2_CAST;
obey TEST004(replay);
log LOG004;

--===========================================
-- T2 part 2: provided match, timestamp constants have different fractional
--            precision, but are equivalent.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- match (Provided) with mvrngcon_t2
prepare QueryStmt from
      select i
      from dttypes
      where t2 <= time'11:25:30.450000' and t2 >= time'10:00:00.200000'
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_T2_2;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_t2_2.in;
sh echo PUBLISH MVRNGCON_T2.xml  >> range_t2_2.in;
sh echo MATCH RANGECONSTR_T2_2.xml >> range_t2_2.in;

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

log;
sh cat range_t2_2.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 't2 <= cast(''11:25:30.450000'' as time(2)) and t2 >= cast(''10:00:00.200000'' as time(2))';
set pattern $$replay_mv$$ MVRNGCON_T2;
set pattern $$replay_filebase$$ range_t2_2;
set pattern $$QueryName$$ RANGECONSTR_T2_2_CAST;
obey TEST004(replay);
log LOG004;

?section match_T6
--===========================================
-- T6: No match, query range has same endpoint but is closed, while MV range is open.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should not match; MV does not include value time'01:15:30.123456'
prepare QueryStmt from
      select i
      from dttypes
      where t6 <= time'01:15:30.123456'
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_T6;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_t6.in;
sh echo PUBLISH MVRNGCON_T6.xml  >> range_t6.in;
sh echo MATCH RANGECONSTR_T6.xml >> range_t6.in;

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

log;
sh cat range_t6.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 't6 <= cast(''01:15:30.123456'' as time(6))';
set pattern $$replay_mv$$ MVRNGCON_T6;
set pattern $$replay_filebase$$ range_t6;
set pattern $$QueryName$$ RANGECONSTR_T6_CAST;
obey TEST004(replay);
log LOG004;

--===========================================
-- T6 part 2: Not provided match with several overlapping subranges.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- Should match (Not Provided) mvrngcon_t6.
prepare QueryStmt from
      select i
      from dttypes
      where t6 between time'01:00:00.123456' and time'01:15:30.123456' and t6 <> time'01:15:30.123456'
         or t6 in (time'00:00:00', time'01:15:30.123455', time'00:15:00.999')
         or t6 < time'00:30:00'
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_T6_2;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_t6_2.in;
sh echo PUBLISH MVRNGCON_T6.xml  >> range_t6_2.in;
sh echo MATCH RANGECONSTR_T6_2.xml >> range_t6_2.in;

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

log;
sh cat range_t6_2.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 't6 between cast(''01:00:00.123456'' as time(6)) and time''01:15:30.123456'' and t6 <> cast(''01:15:30.123456'' as time(6)) or t6 in (time''00:00:00'', cast(''01:15:30.123455'' as time(6)), time''00:15:00.999'') or t6 < cast(''00:30:00'' as time(6))';
set pattern $$replay_mv$$ MVRNGCON_T6;
set pattern $$replay_filebase$$ range_t6_2;
set pattern $$QueryName$$ RANGECONSTR_T6_2_CAST;
obey TEST004(replay);
log LOG004;

?section match_TS
--===========================================
-- TS: No match, query range has same range but adds null.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should not match; query range is same as mv, but allows null as well.
prepare QueryStmt from
      select i
      from dttypes
      where ts < timestamp'2005-10-12 14:45:01.123456' or ts is null
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_TS;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_ts.in;
sh echo PUBLISH MVRNGCON_TS.xml  >> range_ts.in;
sh echo MATCH RANGECONSTR_TS.xml >> range_ts.in;

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

log;
sh cat range_ts.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 'ts < cast(''2005-10-12 14:45:01.123456'' as timestamp(6)) or ts is null';
set pattern $$replay_mv$$ MVRNGCON_TS;
set pattern $$replay_filebase$$ range_ts;
set pattern $$QueryName$$ RANGECONSTR_TS_CAST;
obey TEST004(replay);
log LOG004;

--===========================================
-- TS part 2: Match (Provided) mvrngcon_ts, query has contiguous subranges
--            that are equivalent to mv's range.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should match (Provided) mvrngcon_ts; query range is equivalent to that of mv.
prepare QueryStmt from
      select i
      from dttypes
      where ts between timestamp'0001-01-01 00:00:00' and timestamp'1950-12-31 23:59:59.999999'
         or ts > timestamp'1951-01-01 00:00:00' and ts < timestamp'2005-10-12 14:45:01.123456'
         or ts = timestamp'1951-01-01 00:00:00'
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_TS_2;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_ts_2.in;
sh echo PUBLISH MVRNGCON_TS.xml  >> range_ts_2.in;
sh echo MATCH RANGECONSTR_TS_2.xml >> range_ts_2.in;

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

log;
sh cat range_ts_2.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 'ts between timestamp''0001-01-01 00:00:00'' and cast(''1950-12-31 23:59:59.999999'' as timestamp(6)) or ts > timestamp''1951-01-01 00:00:00'' and ts < cast(''2005-10-12 14:45:01.123456'' as timestamp(6)) or ts = timestamp''1951-01-01 00:00:00''';
set pattern $$replay_mv$$ MVRNGCON_TS;
set pattern $$replay_filebase$$ range_ts_2;
set pattern $$QueryName$$ RANGECONSTR_TS_2_CAST;
obey TEST004(replay);
log LOG004;

?section match_TS3
--===========================================
-- TS3: No match, query includes one additional value outside range of mv.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should not match; query range is same as mv, but allows null as well.
prepare QueryStmt from
      select i
      from dttypes
      where ts3 < timestamp'2005-10-12 14:45:01.125' or ts3 = timestamp'2005-10-12 14:45:01.125'
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_TS3;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE                >> range_ts3.in;
sh echo PUBLISH MVRNGCON_TS3.xml  >> range_ts3.in;
sh echo MATCH RANGECONSTR_TS3.xml >> range_ts3.in;

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

log;
sh cat range_ts3.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 'ts3 < cast(''2005-10-12 14:45:01.125'' as timestamp(3)) or ts3 = cast(''2005-10-12 14:45:01.125'' as timestamp(3))';
set pattern $$replay_mv$$ MVRNGCON_TS3;
set pattern $$replay_filebase$$ range_ts3;
set pattern $$QueryName$$ RANGECONSTR_TS3_CAST;
obey TEST004(replay);
log LOG004;

--===========================================
-- TS3 part 2: Not Provided match, query has overlapping subranges all subsumed
--             by mv range, plus gratuitous NOT NULL.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should match (Not Provided) mvrngcon_ts3
prepare QueryStmt from
      select i
      from dttypes
      where ts3 is not null and
            (ts3 between timestamp'1800-10-10 22:10:10' and timestamp'1900-10-10 22:10:10' or
             ts3 between timestamp'1850-10-10 22:10:10' and timestamp'1950-10-10 22:10:10' or
             ts3 < timestamp'1850-01-01 00:00:00')
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_TS3_2;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE                >> range_ts3_2.in;
sh echo PUBLISH MVRNGCON_TS3.xml  >> range_ts3_2.in;
sh echo MATCH RANGECONSTR_TS3_2.xml >> range_ts3_2.in;

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

log;
sh cat range_ts3_2.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 'ts3 is not null and (ts3 between timestamp''1800-10-10 22:10:10'' and cast(''1900-10-10 22:10:10'' as timestamp(3)) or ts3 between cast(''1850-10-10 22:10:10'' as timestamp(3)) and timestamp''1950-10-10 22:10:10'' or ts3 < timestamp''1850-01-01 00:00:00'')';
set pattern $$replay_mv$$ MVRNGCON_TS3;
set pattern $$replay_filebase$$ range_ts3_2;
set pattern $$QueryName$$ RANGECONSTR_TS3_2_CAST;
obey TEST004(replay);
log LOG004;

?section match_YR
--===========================================
-- YR: NotProvided match, query duplicates mv range + null, but null is prohibited
--     by NOT NULL constraint. Would be Provided except that check constraints are
--     not used for ranges in MV descriptors.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should match mvrngcon_yr, "yr is null" ignored because of NOT NULL constraint.
-- Match is NotProvided instead of Provided, because check constraints are not
-- used in ranges in MV descriptors.
prepare QueryStmt from
      select i
      from dttypes
      where yr < interval '20' year or yr is null
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_YR;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_yr.in;
sh echo PUBLISH MVRNGCON_YR.xml  >> range_yr.in;
sh echo MATCH RANGECONSTR_YR.xml >> range_yr.in;

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

log;
sh cat range_yr.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 'yr < cast(''20'' as interval year(2)) or yr is null';
set pattern $$replay_mv$$ MVRNGCON_YR;
set pattern $$replay_filebase$$ range_yr;
set pattern $$QueryName$$ RANGECONSTR_YR_CAST;
obey TEST004(replay);
log LOG004;

--===========================================
-- YR part 2: NotProvided match, query excludes negative interval values not
--            explicitly excluded by mv range, but check constraint disallows them.
--            However, our policy is to not use check constraints in the MV descriptor.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should match (NotProvided) mvrngcon_yr
-- Match is NotProvided instead of Provided, because check constraints are not
-- used in MV descriptors.
prepare QueryStmt from
      select i
      from dttypes
      where yr >= interval'0'year and yr < interval '20' year
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_YR_2;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_yr_2.in;
sh echo PUBLISH MVRNGCON_YR.xml  >> range_yr_2.in;
sh echo MATCH RANGECONSTR_YR_2.xml >> range_yr_2.in;

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

log;
sh cat range_yr_2.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 'yr >= cast(''0'' as interval year(2)) and yr < cast(''20'' as interval year(2))';
set pattern $$replay_mv$$ MVRNGCON_YR;
set pattern $$replay_filebase$$ range_yr_2;
set pattern $$QueryName$$ RANGECONSTR_YR_2_CAST;
obey TEST004(replay);
log LOG004;

--===========================================
-- YR part 3: Not Provided match, query excludes negative interval values not
--            explicitly excluded by mv range, but check constraint disallows them.
--            Start point not inclusive as in mv range, so Not Provided instead of
--            Provided. Would be NotProvided anyway because check constraints not
--            used when determining ranges for MV descriptors.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should match (Not Provided) mvrngcon_yr
prepare QueryStmt from
      select i
      from dttypes
      where yr > interval'0'year and yr < interval '20' year
	    order by i;

set pattern $$QueryName$$ RANGECONSTR_YR_3;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_yr_3.in;
sh echo PUBLISH MVRNGCON_YR.xml  >> range_yr_3.in;
sh echo MATCH RANGECONSTR_YR_3.xml >> range_yr_3.in;

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

log;
sh cat range_yr_3.out >> LOG004;
-- Now replay using a cast to the same constant value.
set pattern $$replay_tbl$$ dttypes;
set pattern $$replay_pred$$ 'yr > cast(''0'' as interval year(2)) and yr < cast(''20'' as interval year(2))';
set pattern $$replay_mv$$ MVRNGCON_YR;
set pattern $$replay_filebase$$ range_yr_3;
set pattern $$QueryName$$ RANGECONSTR_YR_3_CAST;
obey TEST004(replay);
log LOG004;

?section match_YM
--===========================================
-- YM: NotProvided match on mvrngcon_ym.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should get notprovided match on mvrngcon_ym.
prepare QueryStmt from
      select i
      from dttypes
      where ym between interval '24' month and interval '36' month
      order by i;

set pattern $$QueryName$$ RANGECONSTR_YM;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_ym.in;
sh echo PUBLISH MVRNGCON_YM.xml  >> range_ym.in;
sh echo MATCH RANGECONSTR_YM.xml >> range_ym.in;

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

log;
sh cat range_ym.out >> LOG004;
log LOG004;

?section match_MON
--===========================================
-- MON: NotProvided match on mvrngcon_mon.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should get notprovided match on mvrngcon_mon. Note use of year interval as opposed
-- to month interval used in the predicate of the mv.
prepare QueryStmt from
      select i
      from dttypes
      where mon > interval '500' year(3)
      order by i;

set pattern $$QueryName$$ RANGECONSTR_MON;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE                >> range_mon.in;
sh echo PUBLISH MVRNGCON_MON.xml  >> range_mon.in;
sh echo MATCH RANGECONSTR_MON.xml >> range_mon.in;

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

log;
sh cat range_mon.out >> LOG004;
log LOG004;

?section match_SEC
--===========================================
-- SEC: NotProvided match on mvrngcon_sec.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should get notprovided match on mvrngcon_sec.
prepare QueryStmt from
      select i
      from dttypes
      where sec = interval '10.1' second(2,1)
      order by i;

set pattern $$QueryName$$ RANGECONSTR_SEC;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE                >> range_sec.in;
sh echo PUBLISH MVRNGCON_SEC.xml  >> range_sec.in;
sh echo MATCH RANGECONSTR_SEC.xml >> range_sec.in;

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

log;
sh cat range_sec.out >> LOG004;
log LOG004;

?section match_MNT
--===========================================
-- MNT: NotProvided match on mvrngcon_mnt.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should get notprovided match on mvrngcon_mnt.
prepare QueryStmt from
      select i
      from dttypes
      where mnt between interval '1' minute and interval '3' minute
      order by i;

set pattern $$QueryName$$ RANGECONSTR_MNT;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE                >> range_mnt.in;
sh echo PUBLISH MVRNGCON_MNT.xml  >> range_mnt.in;
sh echo MATCH RANGECONSTR_MNT.xml >> range_mnt.in;

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

log;
sh cat range_mnt.out >> LOG004;
log LOG004;

?section match_MS
--===========================================
-- MS: NotProvided match on mvrngcon_ms.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should get notprovided match on mvrngcon_ms.
prepare QueryStmt from
      select i
      from dttypes
      where ms = interval'345'second(3) or ms = interval-'300'second(3)
      order by i;

set pattern $$QueryName$$ RANGECONSTR_MS;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_ms.in;
sh echo PUBLISH MVRNGCON_MS.xml  >> range_ms.in;
sh echo MATCH RANGECONSTR_MS.xml >> range_ms.in;

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

log;
sh cat range_ms.out >> LOG004;
log LOG004;

?section match_HR
--===========================================
-- HR: NotProvided match on mvrngcon_hr.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should get notprovided match on mvrngcon_hr.
prepare QueryStmt from
      select i
      from dttypes
      where hr > interval'10001'hour(5)
      order by i;

set pattern $$QueryName$$ RANGECONSTR_HR;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_hr.in;
sh echo PUBLISH MVRNGCON_HR.xml  >> range_hr.in;
sh echo MATCH RANGECONSTR_HR.xml >> range_hr.in;

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

log;
sh cat range_hr.out >> LOG004;
log LOG004;

?section match_HM
--===========================================
-- HM: NotProvided match on mvrngcon_hm.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should get notprovided match on mvrngcon_hm.
prepare QueryStmt from
      select i
      from dttypes
      where hm = interval '1:10' hour to minute
      order by i;

set pattern $$QueryName$$ RANGECONSTR_HM;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_hm.in;
sh echo PUBLISH MVRNGCON_HM.xml  >> range_hm.in;
sh echo MATCH RANGECONSTR_HM.xml >> range_hm.in;

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

log;
sh cat range_hm.out >> LOG004;
log LOG004;

?section match_HS
--===========================================
-- HS: NotProvided match on mvrngcon_hs.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should get notprovided match on mvrngcon_hs.
prepare QueryStmt from
      select i
      from dttypes
      where hs between interval -'10' hour(2) and interval '1000' minute(4)
      order by i;

set pattern $$QueryName$$ RANGECONSTR_HS;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

-- Don't do an INITIALIZE here; it causes unwanted MVs to be considered.
sh echo PUBLISH MVRNGCON_HS.xml  >> range_hs.in;
sh echo MATCH RANGECONSTR_HS.xml >> range_hs.in;

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

log;
sh cat range_hs.out >> LOG004;
log LOG004;

?section match_HS_1
--===========================================
-- HS_1: Provided match on mvrngcon_hs_1.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should get provided match on mvrngcon_hs_1. The mv has a range including all
-- allowable values but the first and last, and the query predicate just excludes
-- the first and last allowable value.
prepare QueryStmt from
      select i
      from dttypes
      where hs <> interval-'99:59:59.999999'hour to second and hs <> interval'99:59:59.999999'hour to second
      order by i;

set pattern $$QueryName$$ RANGECONSTR_HS_1;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

-- Don't do an INITIALIZE here; it causes unwanted MVs to be considered.
sh echo PUBLISH MVRNGCON_HS_1.xml  >> range_hs_1.in;
sh echo MATCH RANGECONSTR_HS_1.xml >> range_hs_1.in;

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

log;
sh cat range_hs_1.out >> LOG004;
log LOG004;

?section match_DY
--===========================================
-- DY: NotProvided match on mvrngcon_dy.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should get notprovided match on mvrngcon_dy. Rewrite instructions should
-- include predicates on dh and dm as well.
prepare QueryStmt from
      select i
      from dttypes
      where dy > interval '99' day(2) and dy < interval '101' day(3)
        and dh > interval '10 12' day(2) to hour
        and dm < interval '1 2:30' day to minute
      order by i;

set pattern $$QueryName$$ RANGECONSTR_DY;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_dy.in;
sh echo PUBLISH MVRNGCON_DY.xml  >> range_dy.in;
sh echo MATCH RANGECONSTR_DY.xml >> range_dy.in;

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

log;
sh cat range_dy.out >> LOG004;
log LOG004;

?section match_DH
--===========================================
-- DH: Matches empty mv.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- Matches mvrngcon_dh, which is guaranteed to be empty due to its predicate.
prepare QueryStmt from
      select i
      from dttypes
      where dh > interval'100'day(3)
      order by i;

set pattern $$QueryName$$ RANGECONSTR_DH;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

-- Don't do an INITIALIZE here; it causes unwanted MVs to be considered.
sh echo PUBLISH MVRNGCON_DH.xml  >> range_dh.in;
sh echo MATCH RANGECONSTR_DH.xml >> range_dh.in;

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

log;
sh cat range_dh.out >> LOG004;
log LOG004;

?section match_DM
--===========================================
-- DM: Matches empty mv.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- Matches mvrngcon_dm, which is guaranteed to be empty due to its predicate.
prepare QueryStmt from
      select i
      from dttypes
      where dm < interval-'100'day(3)
      order by i;

set pattern $$QueryName$$ RANGECONSTR_DM;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

-- Don't do an INITIALIZE here; it causes unwanted MVs to be considered.
sh echo PUBLISH MVRNGCON_DM.xml  >> range_dm.in;
sh echo MATCH RANGECONSTR_DM.xml >> range_dm.in;

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

log;
sh cat range_dm.out >> LOG004;
log LOG004;

?section match_DS
--===========================================
-- DS: NotProvided match with mvrngcon_ds.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- should get notprovided match with mvrngcon_ds; has same range except for stopping 1 microsecond short.
prepare QueryStmt from
      select i
      from dttypes
      where ds between interval-'1234567 10:20:30.123456'day(7) to second(6) and (interval'365'day(3) - interval'0.000001'second(6))
      order by i;

set pattern $$QueryName$$ RANGECONSTR_DS;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE               >> range_ds.in;
sh echo PUBLISH MVRNGCON_DS.xml  >> range_ds.in;
sh echo MATCH RANGECONSTR_DS.xml >> range_ds.in;

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

log;
sh cat range_ds.out >> LOG004;
log LOG004;

?section float_coverage
--===========================================
-- Additional tests to extend code coverage for floating point types.
--===========================================

create mv mvqr_004_flt_mv1 refresh on request initialized on create as select d from numtypes where d>5e0 group by d;
create mv mvqr_004_flt_mv2 refresh on request initialized on create as select d from numtypes where d<5e0 group by d;
cqd mvqr_rewrite_candidates 'CAT.RANGE.MVQR_004_FLT_MV1 :,CAT.RANGE.MVQR_004_FLT_MV2';
prepare s from select d from numtypes where d>10e0 group by d;
select 'Success: MV used' from table(explain(null,'S')) where tname like '%MVQR_004_FLT_MV1%';
prepare s from select d from numtypes where d<0e0 group by d;
select 'Success: MV used' from table(explain(null,'S')) where tname like '%MVQR_004_FLT_MV2%';

?section string_coverage
--===========================================
-- Additional tests to extend code coverage for char types.
--===========================================

create table tch
(
  i int,
  ch char(20),
  uch char(20) character set ucs2
);
create mv mvqr_004_str_mv1 refresh on request initialized on create as select ch from tch where ch>'seattle' group by ch;
create mv mvqr_004_str_mv2 refresh on request initialized on create as select ch from tch where ch<'seattle' group by ch;
cqd mvqr_rewrite_candidates 'CAT.RANGE.MVQR_004_STR_MV1 :,CAT.RANGE.MVQR_004_STR_MV2';
prepare s from select ch from tch where ch>'tampa' group by ch;
select 'Success: MV used' from table(explain(null,'S')) where tname like '%MVQR_004_STR_MV1%';
prepare s from select ch from tch where ch<'new york' group by ch;
select 'Success: MV used' from table(explain(null,'S')) where tname like '%MVQR_004_STR_MV2%';


?section toItemExpr_coverage
--=====================================================================================
-- Test several redefinitions of QRELement::toItemExpr() that aren't hit by other tests.
--=====================================================================================
create table t1
(
  a1 int not null not droppable primary key, b1 int, c1 int
);

create table t2
(
  a2 int not null not droppable primary key, b2 int, c2 int
);

create table t3
(
  a3 int not null not droppable primary key, b3 int, c3 int
);

-- This hits QRUnaryOper::toItemExpr().
create mv mv1 refresh on request initialized on create as
  select b1,b2
  from t1,t2
  where b1=b2 and b1>10;

cqd mvqr_rewrite_candidates 'CAT.SCH.MV1';
prepare QueryStmt from
select b1+5,nvl(b2, 999)
from t1,t2
where b1=b2 and b1>10;

execute checkPlan;

-- This hits QRNullVal::toItemExpr().
drop mv mv1 cascade;
create mv mv1 refresh on request initialized on create as
  select c1,c2
  from t1,t2
  where b1=b2 and b1>10;

cqd mvqr_rewrite_candidates 'CAT.SCH.MV1';
prepare QueryStmt from
select nullif(c1,c2)
from t1,t2
where b1=b2 and b1>10;

execute checkPlan;

-- This hits QRColumn::toItemExpr(); also tests use of backjoin column in expression
drop mv mv1 cascade;
create mv mv1 refresh on request initialized on create as
  select c1
  from t1,t2,t3
  where b1=b2 and b2=b3;

cqd mvqr_rewrite_candidates 'CAT.SCH.MV1';
prepare QueryStmt from
select c1+c2
from t1,t2,t3
where b1=b2 and b2=b3;

execute checkPlan;

drop mv mv1 cascade;
drop table t1 cascade;
drop table t2 cascade;


?section test_type_constraints
--===========================================
-- Test the effect of applying type-implied constraints to query ranges having
-- various relations to the constraint range. This test does not involve matching,
-- just generation of the query descriptor.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- Query range entirely contained within type boundaries.
select i from numtypes where si between -32000 and 32000 order by i;

-- Query range coincides with type boundaries.
select i from numtypes where si between -32768 and 32767 order by i;

-- Query range spans lower type boundary.
select i from numtypes where si between -33000 and -32000 order by i;

-- Query range spans upper type boundary.
select i from numtypes where si between 32000 and 33000 order by i;

-- Query range spans both type boundaries.
select i from numtypes where si between -33000 and 33000 order by i;

-- Query range starts at lower type boundary, extends beyond upper boundary.
select i from numtypes where si between -32768 and 33000 order by i;

-- Query range starts below lower type boundary, ends at upper boundary.
select i from numtypes where si between -33000 and 32767 order by i;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';


?section test_equality_sets
--===========================================
-- Test the division of equality set members into join pred, range preds, and
-- residual preds. This test does not involve matching, just generation of the
-- query descriptor.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

-- JP + const
select fday, dimdata
from fact f, dim1 d
where f.dimkey=d.dkey and d.dkey=10
order by 1,2;

-- JP + const + duplicate node
select fday, dimdata
from fact f, dim1 d
where f.dimkey=d.dkey and d.dkey=10 and d.dimdata=10
order by 1,2;

-- 1 node + const + resid
select fday, dimdata
from fact f, dim1 d
where f.dimkey=f.fquant and f.fquant=10
order by 1,2;

-- JP + resid
select fday, dimdata
from fact f, dim1 d
where f.dimkey=d.dkey and f.dimkey=fday+fmonth
order by 1,2;

-- JP + const + resid
select fday, dimdata
from fact f, dim1 d
where 10=fday+fmonth and f.dimkey=d.dkey and d.dkey=10
order by 1,2;

-- JP + const + multinode
select fday, dimdata
from fact f, dim1 d
where f.dimkey=d.dkey and f.dimkey=10 and 10=f.fitem+d.dimdata
order by 1,2;

-- 3 multi-node expns
select fday, dimdata
from fact f, dim1 d
where fitem+dkey=fquant+dimdata and fmonth+dimdata=fitem+dkey
order by 1,2;

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';


?section test_rangespec_transformation
--===========================================
--== Test comparison of endpoint values for string subranges. We disable rewrite
--== for this and just compare results with rangespec_transformation on and off.
--== The main issue is strings of differing length with the shorter string matching
--== the initial portion of the longer ones. We need to make sure we use the
--== blank-padding semantics of SQL string comparions. See Solution 10-100707-1629.
--==
--== Test range that includes all values. See Solution 10-100708-1655.
--===========================================

control query default MVQR_REWRITE_LEVEL '0';

create table mvqr_test004_t1( s varchar(10) );
insert into mvqr_test004_t1 values(' 1s'),('1s'),(convertfromhex('0921')),('');
select '|' || s || '|' from mvqr_test004_t1;
control query default  rangespec_transformation 'MINIMUM';
select '|' || s || '|' from mvqr_test004_t1 where s <= ' ';
select '|' || s || '|' from mvqr_test004_t1 where s <= '';
select '|' || s || '|' from mvqr_test004_t1 where s >= ' ';
select '|' || s || '|' from mvqr_test004_t1 where s >= '';
control query default  rangespec_transformation 'OFF';
select '|' || s || '|' from mvqr_test004_t1 where s <= ' ';
select '|' || s || '|' from mvqr_test004_t1 where s <= '';
select '|' || s || '|' from mvqr_test004_t1 where s >= ' ';
select '|' || s || '|' from mvqr_test004_t1 where s >= '';
drop table mvqr_test004_t1;

create table mvqr_test004_t2(a1 int, x1 char(10));
insert into mvqr_test004_t2(a1,x1) values(1,x'616208'), (2,'abc'),(3,'ab'),(4,'ab ');
control query default rangespec_transformation 'MINIMUM';
select a1 from mvqr_test004_t2 where x1>x'616208' and x1<='ab ';
control query default rangespec_transformation 'OFF';
select a1 from mvqr_test004_t2 where x1>x'616208' and x1<='ab ';

-- Using the same table as above, test a range that covers all values (except NULL).
-- This duplicates the problem reported by Solution 10-100708-1655.
control query default rangespec_transformation 'MINIMUM';
select a1 from mvqr_test004_t2 where a1 not between 2 and 1;

-- Using same table as above test intersection with a range derived from a LIKE
-- pred with a trailing wild card. Descriptor should show a range pred for x1='A'.
control query default MVQR_REWRITE_LEVEL '3';
control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';
select x1 from mvqr_test004_t2 where x1 in ('A', 'B') and x1 like 'A%' order by x1;
control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

-- Drop table and restore setting for rangespec cqd.
drop table mvqr_test004_t2;
control query default rangespec_transformation reset;


?section test_mapvid_pushdown
--===========================================
--== Test predicate that results in a vegref that includes a constant being placed
--== in the bottom values of the mapvids node above the MV scan node. When we 
--== call pushdownCoveredExpressions, this vegref will be removed as a covered
--== expression. We have to restore the original map to prevent a fault in the
--== Generator.
--==
--== See Solution 10-100707-1647.
--===========================================
create table t1(a1 int not null not droppable primary key);
create table t2(a2 int not null not droppable primary key);
insert into t1(a1) values(1),(2),(3),(4),(5),(6),(7),(8),(9);
insert into t2(a2) values(1),(2),(3),(4),(5),(6),(7),(8),(9);
create mv RANGE_MV_MAPVID_PUSHDOWN refresh on request initialized on create as 
  select a1,a2 
  from t1,t2 
  where a1=a2 
    and a1>5;
set pattern $$MVName$$ RANGE_MV_MAPVID_PUSHDOWN;
obey TEST004(dump_MV);
cqd mvqr_rewrite_candidates 'CAT.RANGE.RANGE_MV_MAPVID_PUSHDOWN';

-- The following where clause resolves the two betweens to the constant 6.
control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';
prepare QueryStmt from 
  select a1,a2 
  from t1,t2 
  where a1=a2 
    and a1 between 0 and 6 
    and a1 between 6 and 10
  order by a1;
control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';
    
set pattern $$QueryName$$ RANGE_MAPVID_PUSHDOWN;
obey TEST004(dump_Query);
    
sh echo INITIALIZE >> range_mapvid.in;
sh echo PUBLISH RANGE_MV_MAPVID_PUSHDOWN.xml  >> range_mapvid.in;
sh echo MATCH RANGE_MAPVID_PUSHDOWN.xml >> range_mapvid.in;

sh sh -c "$QMS range_mapvid.in range_mapvid.out";
log;
sh cat range_mapvid.out >> LOG004;
log LOG004;

-- Now check the plan
prepare QueryStmt from 
  select a1,a2 
  from t1,t2 
  where a1=a2 
    and a1 between 0 and 6 
    and a1 between 6 and 10;
    
execute checkPlan;
execute QueryStmt;
drop mv RANGE_MV_MAPVID_PUSHDOWN cascade;
drop table t1 cascade;
drop table t2 cascade;


?section test_end_key
--===========================================
--== Test redundant predicates that result in an incorrect end key value being
--== without the duplicate elimination done by rangespec. Without rangespec, this
--== resulted in an executor predicate corresponding to the Between predicate,
--== a begin key having the starting value, and an end key of <MAX>. The performance
--== was very bad. The fix was to add rangespec support for date type, which
--== eliminated the executor predicate and restored the correct end key.
--==
--== See Solution 10-101122-4639.
--===========================================
create table t4639dt
(
  dt date not null not droppable primary key,
  i int
);

cqd rangespec_transformation 'MINIMUM';
prepare s from
  SELECT COUNT (*)
  FROM t4639dt
  WHERE dt between date'2009-11-01' AND date'2009-11-30'
    AND dt >= date'2007-11-01';

select case when position('begin_key: (DT = 2009-11-01) end_key: (DT = 2009-11-30)' in description) > 0 and
                 position('executor_predicate' in description) = 0
            then 'pass'
            else 'fail'
       end
from table(explain(NULL, 'S'))
where tname = 'CAT.RANGE.T4639DT';

cqd rangespec_transformation RESET;


?section test_interval_lp
--===========================================
--== Test limits of leading precision for interval types. Since we represent
--== interval values in terms of the lowest field (either months or microseconds)
--== possible for the particular type, the leading field precision may be
--== diminished even for specific interval types that don't extend to those end
--== fields. In cases where this leading precision is exceeded, a range predicate
--== involving the type results in a residual predicate in the descriptor.
--== See QC bug 4299.
--===========================================
create table tIntvlLp
(
  i        int not null not droppable primary key,
  year16   interval year(16),     -- range
  year17   interval year(17),     -- residual
  month18  interval month(18),    -- range; minimal unit allows full max leading precision
  day7     interval day(7),       -- range
  day8     interval day(8),       -- residual
  hour8    interval hour(8),      -- range
  hour9    interval hour(9),      -- residual
  minute10 interval minute(10),   -- range
  minute11 interval minute(11),   -- residual
  second12 interval second(12,0), -- range
  second13 interval second(13,0)  -- residual; need 6 digits for microseconds
);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

prepare QueryStmt from
select i from tIntvlLp
where year16 > interval'123456'year(16)
  and year17 > interval'123456'year(17)
  and month18 > interval'123456'month(18)
  and day7 > interval'1234'day(7)
  and day8 > interval'1234'day(8)
  and hour8 > interval'1234'hour(8)
  and hour9 > interval'1234'hour(9)
  and minute10 > interval'1234'minute(10)
  and minute11 > interval'1234'minute(11)
  and second12 > interval'1234'second(12,0)
  and second13 > interval'1234'second(13,0)
order by 1;

set pattern $$QueryName$$ INTERVAL_LP;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

  
?section comp_int_22
--===========================================
--== Test use of very large in-list with comp_int_22 set to 0 and rangespec enabled.
--== See bug 3248.
--===========================================

cqd comp_int_22 '0';
cqd rangespec_transformation 'ON';

create table t3248_compint22
(
  i int not null not droppable primary key,
  ch char(20)
);

insert into t3248_compint22 values
  (1, 'abc'), (2, '105'), (3, '3000'), (4, '1'), (5,'5000'), (6, '2391'), (7, '');
  
SELECT * FROM t3248_compint22 WHERE ch in (
'1','2','3','4','5','6','7','8','9','10',
'11','12','13','14','15','16','17','18','19','20',
'21','22','23','24','25','26','27','28','29','30',
'31','32','33','34','35','36','37','38','39','40',
'41','42','43','44','45','46','47','48','49','50',
'51','52','53','54','55','56','57','58','59','60',
'61','62','63','64','65','66','67','68','69','70',
'71','72','73','74','75','76','77','78','79','80',
'81','82','83','84','85','86','87','88','89','90',
'91','92','93','94','95','96','97','98','99','100',
'101','102','103','104','105','106','107','108','109','110',
'111','112','113','114','115','116','117','118','119','120',
'121','122','123','124','125','126','127','128','129','130',
'131','132','133','134','135','136','137','138','139','140',
'141','142','143','144','145','146','147','148','149','150',
'151','152','153','154','155','156','157','158','159','160',
'161','162','163','164','165','166','167','168','169','170',
'171','172','173','174','175','176','177','178','179','180',
'181','182','183','184','185','186','187','188','189','190',
'191','192','193','194','195','196','197','198','199','200',
'201','202','203','204','205','206','207','208','209','210',
'211','212','213','214','215','216','217','218','219','220',
'221','222','223','224','225','226','227','228','229','230',
'231','232','233','234','235','236','237','238','239','240',
'241','242','243','244','245','246','247','248','249','250',
'251','252','253','254','255','256','257','258','259','260',
'261','262','263','264','265','266','267','268','269','270',
'271','272','273','274','275','276','277','278','279','280',
'281','282','283','284','285','286','287','288','289','290',
'291','292','293','294','295','296','297','298','299','300',
'301','302','303','304','305','306','307','308','309','310',
'311','312','313','314','315','316','317','318','319','320',
'321','322','323','324','325','326','327','328','329','330',
'331','332','333','334','335','336','337','338','339','340',
'341','342','343','344','345','346','347','348','349','350',
'351','352','353','354','355','356','357','358','359','360',
'361','362','363','364','365','366','367','368','369','370',
'371','372','373','374','375','376','377','378','379','380',
'381','382','383','384','385','386','387','388','389','390',
'391','392','393','394','395','396','397','398','399','400',
'401','402','403','404','405','406','407','408','409','410',
'411','412','413','414','415','416','417','418','419','420',
'421','422','423','424','425','426','427','428','429','430',
'431','432','433','434','435','436','437','438','439','440',
'441','442','443','444','445','446','447','448','449','450',
'451','452','453','454','455','456','457','458','459','460',
'461','462','463','464','465','466','467','468','469','470',
'471','472','473','474','475','476','477','478','479','480',
'481','482','483','484','485','486','487','488','489','490',
'491','492','493','494','495','496','497','498','499','500',
'501','502','503','504','505','506','507','508','509','510',
'511','512','513','514','515','516','517','518','519','520',
'521','522','523','524','525','526','527','528','529','530',
'531','532','533','534','535','536','537','538','539','540',
'541','542','543','544','545','546','547','548','549','550',
'551','552','553','554','555','556','557','558','559','560',
'561','562','563','564','565','566','567','568','569','570',
'571','572','573','574','575','576','577','578','579','580',
'581','582','583','584','585','586','587','588','589','590',
'591','592','593','594','595','596','597','598','599','600',
'601','602','603','604','605','606','607','608','609','610',
'611','612','613','614','615','616','617','618','619','620',
'621','622','623','624','625','626','627','628','629','630',
'631','632','633','634','635','636','637','638','639','640',
'641','642','643','644','645','646','647','648','649','650',
'651','652','653','654','655','656','657','658','659','660',
'661','662','663','664','665','666','667','668','669','670',
'671','672','673','674','675','676','677','678','679','680',
'681','682','683','684','685','686','687','688','689','690',
'691','692','693','694','695','696','697','698','699','700',
'701','702','703','704','705','706','707','708','709','710',
'711','712','713','714','715','716','717','718','719','720',
'721','722','723','724','725','726','727','728','729','730',
'731','732','733','734','735','736','737','738','739','740',
'741','742','743','744','745','746','747','748','749','750',
'751','752','753','754','755','756','757','758','759','760',
'761','762','763','764','765','766','767','768','769','770',
'771','772','773','774','775','776','777','778','779','780',
'781','782','783','784','785','786','787','788','789','790',
'791','792','793','794','795','796','797','798','799','800',
'801','802','803','804','805','806','807','808','809','810',
'811','812','813','814','815','816','817','818','819','820',
'821','822','823','824','825','826','827','828','829','830',
'831','832','833','834','835','836','837','838','839','840',
'841','842','843','844','845','846','847','848','849','850',
'851','852','853','854','855','856','857','858','859','860',
'861','862','863','864','865','866','867','868','869','870',
'871','872','873','874','875','876','877','878','879','880',
'881','882','883','884','885','886','887','888','889','890',
'891','892','893','894','895','896','897','898','899','900',
'901','902','903','904','905','906','907','908','909','910',
'911','912','913','914','915','916','917','918','919','920',
'921','922','923','924','925','926','927','928','929','930',
'931','932','933','934','935','936','937','938','939','940',
'941','942','943','944','945','946','947','948','949','950',
'951','952','953','954','955','956','957','958','959','960',
'961','962','963','964','965','966','967','968','969','970',
'971','972','973','974','975','976','977','978','979','980',
'981','982','983','984','985','986','987','988','989','990',
'991','992','993','994','995','996','997','998','999','1000',
'1001','1004','1003','1004','1005','1006','1007','1008','1009','1010',
'1011','1012','1013','1014','1015','1016','1017','1018','1019','1020',
'1021','1022','1023','1024','1025','1026','1027','1028','1029','1030',
'1031','1032','1033','1034','1035','1036','1037','1038','1039','1040',
'1041','1042','1043','1044','1045','1046','1047','1048','1049','1050',
'1051','1052','1053','1054','1055','1056','1057','1058','1059','1060',
'1061','1062','1063','1064','1065','1066','1067','1068','1069','1070',
'1071','1072','1073','1074','1075','1076','1077','1078','1079','1080',
'1081','1082','1083','1084','1085','1086','1087','1088','1089','1090',
'1091','1092','1093','1094','1095','1096','1097','1098','1099','1100',
'1101','1102','1103','1104','1105','1106','1107','1108','1109','1110',
'1111','1112','1113','1114','1115','1116','1117','1118','1119','1120',
'1121','1122','1123','1124','1125','1126','1127','1128','1129','1130',
'1131','1132','1133','1134','1135','1136','1137','1138','1139','1140',
'1141','1142','1143','1144','1145','1146','1147','1148','1149','1150',
'1151','1152','1153','1154','1155','1156','1157','1158','1159','1160',
'1161','1162','1163','1164','1165','1166','1167','1168','1169','1170',
'1171','1172','1173','1174','1175','1176','1177','1178','1179','1180',
'1181','1182','1183','1184','1185','1186','1187','1188','1189','1190',
'1191','1192','1193','1194','1195','1196','1197','1198','1199','1200',
'1201','1202','1203','1204','1205','1206','1207','1208','1209','1210',
'1211','1212','1213','1214','1215','1216','1217','1218','1219','1220',
'1221','1222','1223','1224','1225','1226','1227','1228','1229','1230',
'1231','1232','1233','1234','1235','1236','1237','1238','1239','1240',
'1241','1242','1243','1244','1245','1246','1247','1248','1249','1250',
'1251','1252','1253','1254','1255','1256','1257','1258','1259','1260',
'1261','1262','1263','1264','1265','1266','1267','1268','1269','1270',
'1271','1272','1273','1274','1275','1276','1277','1278','1279','1280',
'1281','1282','1283','1284','1285','1286','1287','1288','1289','1290',
'1291','1292','1293','1294','1295','1296','1297','1298','1299','1300',
'1301','1302','1303','1304','1305','1306','1307','1308','1309','1310',
'1311','1312','1313','1314','1315','1316','1317','1318','1319','1320',
'1321','1322','1323','1324','1325','1326','1327','1328','1329','1330',
'1331','1332','1333','1334','1335','1336','1337','1338','1339','1340',
'1341','1342','1343','1344','1345','1346','1347','1348','1349','1350',
'1351','1352','1353','1354','1355','1356','1357','1358','1359','1360',
'1361','1362','1363','1364','1365','1366','1367','1368','1369','1370',
'1371','1372','1373','1374','1375','1376','1377','1378','1379','1380',
'1381','1382','1383','1384','1385','1386','1387','1388','1389','1390',
'1391','1392','1393','1394','1395','1396','1397','1398','1399','1400',
'1401','1402','1403','1404','1405','1406','1407','1408','1409','1410',
'1411','1412','1413','1414','1415','1416','1417','1418','1419','1420',
'1421','1422','1423','1424','1425','1426','1427','1428','1429','1430',
'1431','1432','1433','1434','1435','1436','1437','1438','1439','1440',
'1441','1442','1443','1444','1445','1446','1447','1448','1449','1450',
'1451','1452','1453','1454','1455','1456','1457','1458','1459','1460',
'1461','1462','1463','1464','1465','1466','1467','1468','1469','1470',
'1471','1472','1473','1474','1475','1476','1477','1478','1479','1480',
'1481','1482','1483','1484','1485','1486','1487','1488','1489','1490',
'1491','1492','1493','1494','1495','1496','1497','1498','1499','1500',
'1501','1502','1503','1504','1505','1506','1507','1508','1509','1510',
'1511','1512','1513','1514','1515','1516','1517','1518','1519','1520',
'1521','1522','1523','1524','1525','1526','1527','1528','1529','1530',
'1531','1532','1533','1534','1535','1536','1537','1538','1539','1540',
'1541','1542','1543','1544','1545','1546','1547','1548','1549','1550',
'1551','1552','1553','1554','1555','1556','1557','1558','1559','1560',
'1561','1562','1563','1564','1565','1566','1567','1568','1569','1570',
'1571','1572','1573','1574','1575','1576','1577','1578','1579','1580',
'1581','1582','1583','1584','1585','1586','1587','1588','1589','1590',
'1591','1592','1593','1594','1595','1596','1597','1598','1599','1600',
'1601','1602','1603','1604','1605','1606','1607','1608','1609','1610',
'1611','1612','1613','1614','1615','1616','1617','1618','1619','1620',
'1621','1622','1623','1624','1625','1626','1627','1628','1629','1630',
'1631','1632','1633','1634','1635','1636','1637','1638','1639','1640',
'1641','1642','1643','1644','1645','1646','1647','1648','1649','1650',
'1651','1652','1653','1654','1655','1656','1657','1658','1659','1660',
'1661','1662','1663','1664','1665','1666','1667','1668','1669','1670',
'1671','1672','1673','1674','1675','1676','1677','1678','1679','1680',
'1681','1682','1683','1684','1685','1686','1687','1688','1689','1690',
'1691','1692','1693','1694','1695','1696','1697','1698','1699','1700',
'1701','1702','1703','1704','1705','1706','1707','1708','1709','1710',
'1711','1712','1713','1714','1715','1716','1717','1718','1719','1720',
'1721','1722','1723','1724','1725','1726','1727','1728','1729','1730',
'1731','1732','1733','1734','1735','1736','1737','1738','1739','1740',
'1741','1742','1743','1744','1745','1746','1747','1748','1749','1750',
'1751','1752','1753','1754','1755','1756','1757','1758','1759','1760',
'1761','1762','1763','1764','1765','1766','1767','1768','1769','1770',
'1771','1772','1773','1774','1775','1776','1777','1778','1779','1780',
'1781','1782','1783','1784','1785','1786','1787','1788','1789','1790',
'1791','1792','1793','1794','1795','1796','1797','1798','1799','1800',
'1801','1802','1803','1804','1805','1806','1807','1808','1809','1810',
'1811','1812','1813','1814','1815','1816','1817','1818','1819','1820',
'1821','1822','1823','1824','1825','1826','1827','1828','1829','1830',
'1831','1832','1833','1834','1835','1836','1837','1838','1839','1840',
'1841','1842','1843','1844','1845','1846','1847','1848','1849','1850',
'1851','1852','1853','1854','1855','1856','1857','1858','1859','1860',
'1861','1862','1863','1864','1865','1866','1867','1868','1869','1870',
'1871','1872','1873','1874','1875','1876','1877','1878','1879','1880',
'1881','1882','1883','1884','1885','1886','1887','1888','1889','1890',
'1891','1892','1893','1894','1895','1896','1897','1898','1899','1900',
'1901','1902','1903','1904','1905','1906','1907','1908','1909','1910',
'1911','1912','1913','1914','1915','1916','1917','1918','1919','1920',
'1921','1922','1923','1924','1925','1926','1927','1928','1929','1930',
'1931','1932','1933','1934','1935','1936','1937','1938','1939','1940',
'1941','1942','1943','1944','1945','1946','1947','1948','1949','1950',
'1951','1952','1953','1954','1955','1956','1957','1958','1959','1960',
'1961','1962','1963','1964','1965','1966','1967','1968','1969','1970',
'1971','1972','1973','1974','1975','1976','1977','1978','1979','1980',
'1981','1982','1983','1984','1985','1986','1987','1988','1989','1990',
'1991','1992','1993','1994','1995','1996','1997','1998','1999','2000',
'2001','2004','2003','2004','2005','2006','2007','2008','2009','2010',
'2011','2012','2013','2014','2015','2016','2017','2018','2019','2020',
'2021','2022','2023','2024','2025','2026','2027','2028','2029','2030',
'2031','2032','2033','2034','2035','2036','2037','2038','2039','2040',
'2041','2042','2043','2044','2045','2046','2047','2048','2049','2050',
'2051','2052','2053','2054','2055','2056','2057','2058','2059','2060',
'2061','2062','2063','2064','2065','2066','2067','2068','2069','2070',
'2071','2072','2073','2074','2075','2076','2077','2078','2079','2080',
'2081','2082','2083','2084','2085','2086','2087','2088','2089','2090',
'2091','2092','2093','2094','2095','2096','2097','2098','2099','2100',
'2101','2102','2103','2104','2105','2106','2107','2108','2109','2110',
'2111','2112','2113','2114','2115','2116','2117','2118','2119','2120',
'2121','2122','2123','2124','2125','2126','2127','2128','2129','2130',
'2131','2132','2133','2134','2135','2136','2137','2138','2139','2140',
'2141','2142','2143','2144','2145','2146','2147','2148','2149','2150',
'2151','2152','2153','2154','2155','2156','2157','2158','2159','2160',
'2161','2162','2163','2164','2165','2166','2167','2168','2169','2170',
'2171','2172','2173','2174','2175','2176','2177','2178','2179','2180',
'2181','2182','2183','2184','2185','2186','2187','2188','2189','2190',
'2191','2192','2193','2194','2195','2196','2197','2198','2199','2200',
'2201','2202','2203','2204','2205','2206','2207','2208','2209','2210',
'2211','2212','2213','2214','2215','2216','2217','2218','2219','2220',
'2221','2222','2223','2224','2225','2226','2227','2228','2229','2230',
'2231','2232','2233','2234','2235','2236','2237','2238','2239','2240',
'2241','2242','2243','2244','2245','2246','2247','2248','2249','2250',
'2251','2252','2253','2254','2255','2256','2257','2258','2259','2260',
'2261','2262','2263','2264','2265','2266','2267','2268','2269','2270',
'2271','2272','2273','2274','2275','2276','2277','2278','2279','2280',
'2281','2282','2283','2284','2285','2286','2287','2288','2289','2290',
'2291','2292','2293','2294','2295','2296','2297','2298','2299','2300',
'2301','2302','2303','2304','2305','2306','2307','2308','2309','2310',
'2311','2312','2313','2314','2315','2316','2317','2318','2319','2320',
'2321','2322','2323','2324','2325','2326','2327','2328','2329','2330',
'2331','2332','2333','2334','2335','2336','2337','2338','2339','2340',
'2341','2342','2343','2344','2345','2346','2347','2348','2349','2350',
'2351','2352','2353','2354','2355','2356','2357','2358','2359','2360',
'2361','2362','2363','2364','2365','2366','2367','2368','2369','2370',
'2371','2372','2373','2374','2375','2376','2377','2378','2379','2380',
'2381','2382','2383','2384','2385','2386','2387','2388','2389','2390',
'2391','2392','2393','2394','2395','2396','2397','2398','2399','2400',
'2401','2402','2403','2404','2405','2406','2407','2408','2409','2410',
'2411','2412','2413','2414','2415','2416','2417','2418','2419','2420',
'2421','2422','2423','2424','2425','2426','2427','2428','2429','2430',
'2431','2432','2433','2434','2435','2436','2437','2438','2439','2440',
'2441','2442','2443','2444','2445','2446','2447','2448','2449','2450',
'2451','2452','2453','2454','2455','2456','2457','2458','2459','2460',
'2461','2462','2463','2464','2465','2466','2467','2468','2469','2470',
'2471','2472','2473','2474','2475','2476','2477','2478','2479','2480',
'2481','2482','2483','2484','2485','2486','2487','2488','2489','2490',
'2491','2492','2493','2494','2495','2496','2497','2498','2499','2500',
'2501','2502','2503','2504','2505','2506','2507','2508','2509','2510',
'2511','2512','2513','2514','2515','2516','2517','2518','2519','2520',
'2521','2522','2523','2524','2525','2526','2527','2528','2529','2530',
'2531','2532','2533','2534','2535','2536','2537','2538','2539','2540',
'2541','2542','2543','2544','2545','2546','2547','2548','2549','2550',
'2551','2552','2553','2554','2555','2556','2557','2558','2559','2560',
'2561','2562','2563','2564','2565','2566','2567','2568','2569','2570',
'2571','2572','2573','2574','2575','2576','2577','2578','2579','2580',
'2581','2582','2583','2584','2585','2586','2587','2588','2589','2590',
'2591','2592','2593','2594','2595','2596','2597','2598','2599','2600',
'2601','2602','2603','2604','2605','2606','2607','2608','2609','2610',
'2611','2612','2613','2614','2615','2616','2617','2618','2619','2620',
'2621','2622','2623','2624','2625','2626','2627','2628','2629','2630',
'2631','2632','2633','2634','2635','2636','2637','2638','2639','2640',
'2641','2642','2643','2644','2645','2646','2647','2648','2649','2650',
'2651','2652','2653','2654','2655','2656','2657','2658','2659','2660',
'2661','2662','2663','2664','2665','2666','2667','2668','2669','2670',
'2671','2672','2673','2674','2675','2676','2677','2678','2679','2680',
'2681','2682','2683','2684','2685','2686','2687','2688','2689','2690',
'2691','2692','2693','2694','2695','2696','2697','2698','2699','2700',
'2701','2702','2703','2704','2705','2706','2707','2708','2709','2710',
'2711','2712','2713','2714','2715','2716','2717','2718','2719','2720',
'2721','2722','2723','2724','2725','2726','2727','2728','2729','2730',
'2731','2732','2733','2734','2735','2736','2737','2738','2739','2740',
'2741','2742','2743','2744','2745','2746','2747','2748','2749','2750',
'2751','2752','2753','2754','2755','2756','2757','2758','2759','2760',
'2761','2762','2763','2764','2765','2766','2767','2768','2769','2770',
'2771','2772','2773','2774','2775','2776','2777','2778','2779','2780',
'2781','2782','2783','2784','2785','2786','2787','2788','2789','2790',
'2791','2792','2793','2794','2795','2796','2797','2798','2799','2800',
'2801','2802','2803','2804','2805','2806','2807','2808','2809','2810',
'2811','2812','2813','2814','2815','2816','2817','2818','2819','2820',
'2821','2822','2823','2824','2825','2826','2827','2828','2829','2830',
'2831','2832','2833','2834','2835','2836','2837','2838','2839','2840',
'2841','2842','2843','2844','2845','2846','2847','2848','2849','2850',
'2851','2852','2853','2854','2855','2856','2857','2858','2859','2860',
'2861','2862','2863','2864','2865','2866','2867','2868','2869','2870',
'2871','2872','2873','2874','2875','2876','2877','2878','2879','2880',
'2881','2882','2883','2884','2885','2886','2887','2888','2889','2890',
'2891','2892','2893','2894','2895','2896','2897','2898','2899','2900',
'2901','2902','2903','2904','2905','2906','2907','2908','2909','2910',
'2911','2912','2913','2914','2915','2916','2917','2918','2919','2920',
'2921','2922','2923','2924','2925','2926','2927','2928','2929','2930',
'2931','2932','2933','2934','2935','2936','2937','2938','2939','2940',
'2941','2942','2943','2944','2945','2946','2947','2948','2949','2950',
'2951','2952','2953','2954','2955','2956','2957','2958','2959','2960',
'2961','2962','2963','2964','2965','2966','2967','2968','2969','2970',
'2971','2972','2973','2974','2975','2976','2977','2978','2979','2980',
'2981','2982','2983','2984','2985','2986','2987','2988','2989','2990',
'2991','2992','2993','2994','2995','2996','2997','2998','2999','3000'
);

cqd rangespec_transformation reset;
cqd comp_int_22 reset;


?section check_nulls
--===========================================
--== Check if IS NULL and IS NOT NULL are represented correctly in the descriptors.
--===========================================

-- Simple IS NULL predicate
create mv range_nulls_isnull
	recompute
	initialized on create
	as  select fprice, fquant, 
		   dimdata data
	    from fact, dim2
	    where dimkey=dkey
	      and dimdata IS NULL;

set pattern $$MVName$$ RANGE_NULLS_ISNULL;
obey TEST004(dump_MV);

-- IS NULL or EQUAL (same subrange)
create mv range_nulls_isnullorequal
	recompute
	initialized on create
	as  select fprice, fquant, 
		   dimdata data
	    from fact, dim2
	    where dimkey=dkey
	      and (dimdata IS NULL or dimdata=10);

set pattern $$MVName$$ RANGE_NULLS_ISNULLOREQUAL;
obey TEST004(dump_MV);

-- IS NULL or LESS (different subrange)
create mv range_nulls_isnullorless
	recompute
	initialized on create
	as  select fprice, fquant, 
		   dimdata data
	    from fact, dim2
	    where dimkey=dkey
	      and (dimdata IS NULL or dimdata<10);

set pattern $$MVName$$ RANGE_NULLS_ISNULLORLESS;
obey TEST004(dump_MV);

-- Simple IS NOT NULL
create mv range_nulls_isnotnull
	recompute
	initialized on create
	as  select fprice, fquant, 
		   dimdata data
	    from fact, dim2
	    where dimkey=dkey
	      and dimdata IS NOT NULL;

set pattern $$MVName$$ RANGE_NULLS_ISNOTNULL;
obey TEST004(dump_MV);

-- IS NOT NULL or EQUAL (equivalent to only IS NOT NULL)
create mv range_nulls_isnotnullorequal
	recompute
	initialized on create
	as  select fprice, fquant, 
		   dimdata data
	    from fact, dim2
	    where dimkey=dkey
	      and (dimdata IS NOT NULL or dimdata=10);

set pattern $$MVName$$ RANGE_NULLS_ISNOTNULLOREQUAL;
obey TEST004(dump_MV);

-- IS NOT NULL or LESS (equivalent to only IS NOT NULL)
create mv range_nulls_isnotnullorless
	recompute
	initialized on create
	as  select fprice, fquant, 
		   dimdata data
	    from fact, dim2
	    where dimkey=dkey
	      and (dimdata IS NOT NULL or dimdata<10);

set pattern $$MVName$$ RANGE_NULLS_ISNOTNULLORLESS;
obey TEST004(dump_MV);

--===========================================
-- Query with Simple IS NULL predicate.
-- Should match all 3 IS NULL MVs.
control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

prepare QueryStmt from
	select fprice, fquant, 
	       dimdata data
	from fact, dim2
	where dimkey=dkey
	  and dimdata IS NULL
	order by fprice;

set pattern $$QueryName$$ RANGE_Q_NULLS_ISNULL;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE                               >> range_nulls1.in;
sh echo PUBLISH RANGE_NULLS_ISNULL.xml           >> range_nulls1.in;
sh echo PUBLISH RANGE_NULLS_ISNULLOREQUAL.xml    >> range_nulls1.in;
sh echo PUBLISH RANGE_NULLS_ISNULLORLESS.xml     >> range_nulls1.in;
sh echo PUBLISH RANGE_NULLS_ISNOTNULL.xml        >> range_nulls1.in;
sh echo PUBLISH RANGE_NULLS_ISNOTNULLOREQUAL.xml >> range_nulls1.in;
sh echo PUBLISH RANGE_NULLS_ISNOTNULLORLESS.xml  >> range_nulls1.in;
sh echo MATCH RANGE_Q_NULLS_ISNULL.xml           >> range_nulls1.in;

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

log;
sh cat range_nulls1.out >> LOG004;
cqd mvqr_rewrite_candidates 'CAT.RANGE.RANGE_NULLS_ISNULL';
log LOG004;

-- Verify the MV is picked by the optimizer.
prepare QueryStmt from
	select fprice, fquant, 
	       dimdata data
	from fact, dim2
	where dimkey=dkey
	  and dimdata IS NULL
	order by fprice;

execute checkPlan;

--===========================================
-- Query with Simple IS NOT NULL predicate.
-- Should match all 3 IS NOT NULL MVs (which are equivalent anyway).

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

prepare QueryStmt from
	select fprice, fquant, 
	       dimdata data
	from fact, dim2
	where dimkey=dkey
	  and dimdata IS NOT NULL
	order by fprice;

set pattern $$QueryName$$ RANGE_Q_NULLS_ISNOTNULL;
obey TEST004(dump_Query);

control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

sh echo INITIALIZE                               >> range_nulls2.in;
sh echo PUBLISH RANGE_NULLS_ISNULL.xml           >> range_nulls2.in;
sh echo PUBLISH RANGE_NULLS_ISNULLOREQUAL.xml    >> range_nulls2.in;
sh echo PUBLISH RANGE_NULLS_ISNULLORLESS.xml     >> range_nulls2.in;
sh echo PUBLISH RANGE_NULLS_ISNOTNULL.xml        >> range_nulls2.in;
sh echo PUBLISH RANGE_NULLS_ISNOTNULLOREQUAL.xml >> range_nulls2.in;
sh echo PUBLISH RANGE_NULLS_ISNOTNULLORLESS.xml  >> range_nulls2.in;
sh echo MATCH RANGE_Q_NULLS_ISNOTNULL.xml        >> range_nulls2.in;

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

log;
sh cat range_nulls2.out >> LOG004;
cqd mvqr_rewrite_candidates 'CAT.RANGE.RANGE_NULLS_ISNOTNULL';
log LOG004;

-- Verify the MV is picked by the optimizer.
prepare QueryStmt from
	select fprice, fquant, 
	       dimdata data
	from fact, dim2
	where dimkey=dkey
	  and dimdata IS NOT NULL
	order by fprice;

execute checkPlan;

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

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

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

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

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

?section get_Query_XML
--===========================================
--== Get the xml for the query, without putting anything in the log.
--== This is used for the replay of matches using cast of a constant.
--===========================================

log $$QueryName$$.tmp clear;
execute QueryStmt;
sh sh ./CropDescriptor.ksh -q -i $$QueryName$$.TMP -o $$QueryName$$.xml;

?section replay
--===========================================
--== Replay a match of a query using a different predicate. An existing result
--== descriptor is compared to the one generated with the new predicate.
--== The primary use of this is to redo a range query, replacing the constant
--== in the range pred with a cast to the constant. This causes a SystemLiteral
--== with a null header field to be instantiated instead of a ConstValue. The
--== presence of the null header field affects the location and alignment of the
--== actual data value.
--==
--== Patterns used:
--==   $$replay_tbl$$ -- Table to use in query.
--==   $$replay_pred$$ -- Predicate to use in query.
--==   $$replay_mv$$ -- MV to match.
--==   $$replay_filebase$$ -- Base name of the QMS in and out files of the match
--==                          being replayed.
--==   $$QueryName$$ -- Base name of the xml file for the query descriptor.
--==
--== NOTE: A hard-coded string, "_cast", is currently suffixed to $$replay_filebase$$
--==       to form the root of the QMS input and output files for the new invocation
--==       of QMS. If a need arises in the future to do multiple replays based on the
--==       same existing result descriptor, a new pattern will need to be added for the
--==       suffix if we want to preserve all the replayed .in and .out files for analysis
--==       after test execution.
--===========================================

control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';
-- Use the standard query template for testing range predicates for the various data types.
prepare QueryStmt from
      select i
      from $$replay_tbl$$
      where $$replay_pred$$
	    order by i;
obey TEST004(get_Query_XML);
control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';
-- Use > instead of >> on first write, in case file was previously used.
sh echo INITIALIZE > $$replay_filebase$$_cast.in;
sh echo PUBLISH $$replay_mv$$.xml >> $$replay_filebase$$_cast.in;
sh echo MATCH $$QueryName$$.xml >> $$replay_filebase$$_cast.in;
-- Patterns aren't expanded inside quotes, quotes have to be used for qms invocation.
sh cp $$replay_filebase$$_cast.in qms_temp.in;
sh sh -c "$QMS qms_temp.in qms_temp.out";
sh cp qms_temp.out $$replay_filebase$$_cast.out;
sh echo Diffs with result descriptor after changing predicate used in query: >> LOG004;
sh diff $$replay_filebase$$.out $$replay_filebase$$_cast.out >> LOG004;

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

obey TEST004($$QueryName$$);

execute checkPlan;

execute QueryStmt;

log T004_C1.txt clear;
execute QueryStmt;
log;
log LOG004;

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

obey TEST004($$QueryName$$);

execute checkPlan;

execute QueryStmt;

control query default MVQR_REWRITE_LEVEL '3';

log T004_C2.txt clear;
execute QueryStmt;
log;
log LOG004;

-- Diff query results
log;
sh diff T004_C1.txt T004_C2.txt >> LOG004;
log LOG004;
-- Diff is done.

