-- Test: TEST025 (Executor)
-- @@@ 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 @@@
--
-- Functionality: Additional MDAM tests
-- Expected files: EXPECTED025
-- Table created: t025mdam, t025mtmp, t025m2
-- Limitations:
-- To do: - Enable tests 6, 9, and 10 at the end
-- Revision history:
--     (1/28/02) - Copied from fullstack/TEST025
--     (2/08/02) - Renamed tables with *025* to avoid
--                 name conflict with other tests.

--------------------------------------------------------------
-- File:        TEST025
-- Component:   SQL Regression Test Suite
-- Description: MDAM tests. copied from SQL/MP tests SQLKSM11
--              and SQLKSM17.
--
-- Revision History:
--  6/23/97  Created.
--  9/19/97  Adapted to cqs force.
--------------------------------------------------------------
control query default POS 'OFF';
control query default ATTEMPT_ESP_PARALLELISM 'OFF';

?section ddl
drop table t025mdam;
drop table t025mtmp;
drop table t025m2;

?section setup
log LOG025 clear;

create table t025mdam ( a smallint no default not null ,
                               b smallint no default not null ,
                               c smallint no default not null,
                               d smallint no default not null,
                               primary key (a,b) );

create table t025mtmp 
                           ( a smallint no default not null ,
                               b smallint no default not null ,
                               c smallint no default not null,
                               d smallint no default not null,
                               primary key (a,b) );



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

insert into t025mtmp select * from t025mdam;
insert into t025mtmp select a,1,c,d from t025mdam;
insert into t025mtmp select a,2,c,d from t025mdam;
insert into t025mtmp select a,3,c,d from t025mdam;

insert into t025mdam select * from t025mtmp where b > 0;

insert into t025mdam select a-20,b,c,d from t025mtmp;
insert into t025mdam select a-10,b,c,d from t025mtmp;
insert into t025mdam select a+10,b,c,d from t025mtmp;
insert into t025mdam select a+20,b,c,d from t025mtmp;
insert into t025mdam select a+30,b,c,d from t025mtmp;
insert into t025mdam select a+40,b,c,d from t025mtmp;
insert into t025mdam select a+50,b,c,d from t025mtmp;
insert into t025mdam select a+60,b,c,d from t025mtmp;
insert into t025mdam select a+70,b,c,d from t025mtmp;
insert into t025mdam select a+80,b,c,d from t025mtmp;
insert into t025mdam select a+90,b,c,d from t025mtmp;
insert into t025mdam select a+100,b,c,d from t025mtmp;
insert into t025mdam select a+110,b,c,d from t025mtmp;

update statistics for table t025mdam on every column;

-- ******************************************************************
--   Create and populate table - one partition for now
-- ******************************************************************

create table t025m2 ( q int not null , 
                             r int not null , 
                             s int, 
                             primary key (q,r) );

insert into t025m2 values (1,1,1),
                                 (1,10,10),
                                 (5,5,5),
                                 (5,50,50);


--==================================================================
--  tests where disjuncts do not overlap
--==================================================================

#ifdef SEABASE_REGRESS
control query shape scan('t025mdam', MDAM_COLUMNS ALL);
#else
control query shape exchange(scan('t025mdam', MDAM_COLUMNS ALL));
#endif

?section q1
--------------------------------------------------------------------
--   2 disjuncts, one interval in each, 1st precedes 2nd
-- expect rows (1,0),(2,0),(3,0),(5,1),(6,1),(7,1),(8,1)  (7 rows)
--------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a > 0 and a < 4)
         or
        (b = 1 and a >= 5 and a <= 8);

?section q2
--------------------------------------------------------------------
--   2 disjuncts, one interval in each, 1st follows 2nd
-- expect rows (1,1),(2,1),(3,1),(5,0),(6,0),(7,0),(8,0)  (7 rows)
--------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 8)
         or
        (b = 1 and a > 0 and a < 4);

?section q3
--------------------------------------------------------------------
--   2 disjuncts, two intervals in each, 1st precedes 2nd
-- expect rows (0,0),(4,0),(9,1),(12,1)  (4 rows)
--------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a in (0,4))
         or
        (b = 1 and a in (9,12));

?section q4
--------------------------------------------------------------------
--   2 disjuncts, two intervals in each, 1st follows 2nd
-- expect rows (0,1),(4,1),(9,0),(12,0)  (4 rows)
--------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a in (9,12))
         or
        (b = 1 and a in (0,4));

?section q5
------------------------------------------------------------------------
--   2 disjuncts, two intervals in each, lists interleave thus: 0 1 0 1
-- expect rows (0,0),(4,1),(9,0),(12,1)  (4 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a in (0,9))
         or
        (b = 1 and a in (4,12));

?section q6
------------------------------------------------------------------------
--   2 disjuncts, two intervals in each, lists interleave thus: 1 0 1 0
-- expect rows (0,1),(4,0),(9,1),(12,0)  (4 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a in (4,12))
         or
        (b = 1 and a in (0,9));

?section q7
------------------------------------------------------------------------
--   2 disjuncts, two intervals in each, lists interleave thus: 0 1 1 0
-- expect rows (0,0),(4,1),(9,1),(12,0)  (4 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a in (0,12))
         or
        (b = 1 and a in (4,9));

?section q8
------------------------------------------------------------------------
--   2 disjuncts, two intervals in each, lists interleave thus: 1 0 0 1
-- expect rows (0,1),(4,0),(9,0),(12,1)  (4 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a in (4,9))
         or
        (b = 1 and a in (0,12));

?section q9
------------------------------------------------------------------------
--   2 disjuncts, long lists just for fun, lists interleave thus:
--   1 1 0 0 1 0 1 0 1 1 1 0
-- expect rows (0,1),(2,1),(4,0),(6,0),(8,1),(10,0),
--             (12,1),(14,0),(16,1),(18,1),(20,1),(22,0)  (12 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a in (4,6,10,14,22))
         or
        (b = 1 and a in (0,2,8,12,16,18,20));

?section q10
------------------------------------------------------------------------
--   for more fun, 3 disjuncts, interleaved thus:
--   1 1 0 0 1 2 0 1 0 1 2 1 1 0
-- expect rows (0,1),(2,1),(4,0),(6,0),(8,1),(9,2),(10,0),
--             (12,1),(14,0),(16,1),(17,2),(18,1),(20,1),(22,0)  (14 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a in (4,6,10,14,22))
         or
        (b = 1 and a in (0,2,8,12,16,18,20))
         or
        (b = 2 and a in (9,17));

?section sido
--==================================================================
--  tests on single interval disjuncts that overlap

--  The dimensions we have are:
--  1.  front interval:
--    a.  exists and comes from interval1
--    b.  exists and comes from interval2
--    c.  does not exist
--  2.  back interval:
--    a.  exists and comes from interval1
--    b.  exists and comes from interval2
--    c.  does not exist
--
--  A structured test requires 5 cases (say, one each of
--  dimension 1 with dimension 2 held constant, then one
--  each in dimension 2 with dimension 1 held constant,
--  note that these have one case in common).  But since the
--  space is small we do all 9 possibilities.

--  In this script, we are *not* giving reference list union
--  a rigorous test - the disjuncts always have a single
--  disjunct reference, and they always differ.
--==================================================================

?section sido1a2a
------------------------------------------------------------------------
-- case 1a, 2a
-- expect rows (4,0),(5,0),(5,1),(6,0),(6,1),(7,0),(7,1),(8,0),(8,1),(9,0)
--  (10 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 9)
         or
        (b = 1 and a >= 5 and a <= 8);

?section sido1a2b
------------------------------------------------------------------------
-- case 1a, 2b
-- expect rows (4,0),(5,0),(5,1),(6,0),(6,1),(7,0),(7,1),(8,0),(8,1),(9,1)
--  (10 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 8)
         or
        (b = 1 and a >= 5 and a <= 9);

?section sido1a2c
------------------------------------------------------------------------
-- case 1a, 2c
-- expect rows (4,0),(5,0),(5,1),(6,0),(6,1),(7,0),(7,1),(8,0),(8,1)
--  (9 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 8)
         or
        (b = 1 and a >= 5 and a <= 8);

?section sido1b2a
------------------------------------------------------------------------
-- case 1b, 2a
-- expect rows (4,1),(5,0),(5,1),(6,0),(6,1),(7,0),(7,1),(8,0),(8,1),(9,0)
--  (10 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 9)
         or
        (b = 1 and a >= 4 and a <= 8);

?section sido1b2b
------------------------------------------------------------------------
-- case 1b, 2b
-- expect rows (4,1),(5,0),(5,1),(6,0),(6,1),(7,0),(7,1),(8,0),(8,1),(9,1)
--  (10 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 8)
         or
        (b = 1 and a >= 4 and a <= 9);

?section sido1b2c
------------------------------------------------------------------------
-- case 1b, 2c
-- expect rows (4,1),(5,0),(5,1),(6,0),(6,1),(7,0),(7,1),(8,0),(8,1)
--  (9 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 8)
         or
        (b = 1 and a >= 4 and a <= 8);

?section sido1c2a
------------------------------------------------------------------------
-- case 1c, 2a
-- expect rows (5,0),(5,1),(6,0),(6,1),(7,0),(7,1),(8,0),(8,1),(9,0)
--  (9 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 9)
         or
        (b = 1 and a >= 5 and a <= 8);

?section sido1c2b
------------------------------------------------------------------------
-- case 1c, 2b
-- expect rows (5,0),(5,1),(6,0),(6,1),(7,0),(7,1),(8,0),(8,1),(9,1)
--  (9 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 8)
         or
        (b = 1 and a >= 5 and a <= 9);

?section sido1c2c
------------------------------------------------------------------------
-- case 1c, 2c
-- expect rows (5,0),(5,1),(6,0),(6,1),(7,0),(7,1),(8,0),(8,1)
--  (8 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 8)
         or
        (b = 1 and a >= 5 and a <= 8);

?section mido
--==================================================================
--  tests on multiple interval disjuncts that overlap
--
--  The dimensions we have are:
--  1.  front interval:
--    a.  exists and comes from interval1
--    b.  exists and comes from interval2
--    c.  does not exist
--  2.  back interval:
--    a.  exists and comes from interval1
--    b.  exists and comes from interval2
--    c.  does not exist
--  3.  prev interval:
--    a.  NULLP
--    b.  not NULLP
--  4.  next interval:
--    a.  NULLP
--    b.  not NULLP
--
--  The previous section varied dimensions 1 and 2, while
--  holding dimensions 3 and 4 constant (at 3a and 4a).
--  In this section we take a subset of the dimension 1
--  and 2 combinations, with combinations of (3a,4b),
--  (3b,4a) and (3b,4b).  Note that (3a,4b) and (3b,4a)
--  is enough to span the vector space, but we add some
--  (3b,4b) cases just for fun.
--==================================================================

?section mido1a2a3a4b
------------------------------------------------------------------------
-- case 1a, 2a, 3a, 4b
-- expect rows (4,0),(5,0),(5,1),(6,0),(6,1),(7,0),(7,1),(8,0),
--             (8,1),(9,0),(11,0) through (14,0)
--  (14 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 9)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a >= 5 and a <= 8);

?section mido1a2b3a4b
------------------------------------------------------------------------
-- case 1a, 2b, 3a, 4b
-- expect rows (4,0),(5,0),(5,1),(6,0),(6,1),(7,0),(7,1),(8,0),
--             (8,1),(9,1),(11,0) through (14,0)
--  (14 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 8)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a >= 5 and a <= 9);

?section mido1a2c3a4b
------------------------------------------------------------------------
-- case 1a, 2c, 3a, 4b
-- expect rows (4,0),(5,0),(5,1),(6,0),(6,1),(7,0),(7,1),(8,0),
--             (8,1),(11,0) through (14,0)
--  (13 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 8)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a >= 5 and a <= 8);

?section mido1c2b3a4b
------------------------------------------------------------------------
-- case 1c, 2b, 3a, 4b
-- expect rows (5,0),(5,1),(6,0),(6,1),(7,0),(7,1),(8,0),
--             (8,1),(9,1),(11,0) through (14,0)
--  (13 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 8)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a >= 5 and a <= 9);

?section mido1c2c3a4b
------------------------------------------------------------------------
-- case 1c, 2c, 3a, 4b
-- expect rows (5,0),(5,1),(6,0),(6,1),(7,0),(7,1),(8,0),
--             (8,1),(11,0) through (14,0)
--  (12 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 8)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a >= 5 and a <= 8);

?section mido1a2a3b4a
------------------------------------------------------------------------
-- case 1a, 2a, 3b, 4a
-- expect rows (-5,0) through (0,0),(4,0),(5,0),(5,1),
--             (6,0),(6,1),(7,0),(7,1),(8,0),(8,1),(9,0)
--  (16 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 9)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 1 and a >= 5 and a <= 8);

?section mido1b2a3b4a
------------------------------------------------------------------------
-- case 1b, 2a, 3b, 4a
-- expect rows (-5,0) through (0,0),(4,1),(5,0),(5,1),
--             (6,0),(6,1),(7,0),(7,1),(8,0),(8,1),(9,0)
--  (16 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 9)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 1 and a >= 4 and a <= 8);

?section mido1c2a3b4a
------------------------------------------------------------------------
-- case 1c, 2a, 3b, 4a
-- expect rows (-5,0) through (0,0),(5,0),(5,1),
--             (6,0),(6,1),(7,0),(7,1),(8,0),(8,1),(9,0)
--  (15 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 9)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 1 and a >= 5 and a <= 8);

?section mido1b2b3b4a
------------------------------------------------------------------------
-- case 1b, 2b, 3b, 4a
-- expect rows (-5,0) through (0,0),(4,1),(5,0),(5,1),
--             (6,0),(6,1),(7,0),(7,1),(8,0),(8,1),(9,1)
--  (16 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 8)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 1 and a >= 4 and a <= 9);

?section mido1c2c3b4a
------------------------------------------------------------------------
-- case 1c, 2c, 3b, 4a
-- expect rows (-5,0) through (0,0),(5,0),(5,1),
--             (6,0),(6,1),(7,0),(7,1),(8,0),(8,1)
--  (14 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 8)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 1 and a >= 5 and a <= 8);

?section mido1b2c3b4b
------------------------------------------------------------------------
-- case 1b, 2c, 3b, 4b
-- expect rows (-5,0) through (0,0),(4,1),(5,0),(5,1),
--             (6,0),(6,1),(7,0),(7,1),(8,0),(8,1),
--             (11,0) through (14,0)
--  (19 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 8)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a >= 4 and a <= 8);

?section mido1c2a3b4b
------------------------------------------------------------------------
-- case 1c, 2a, 3b, 4b
-- expect rows (-5,0) through (0,0),(5,0),(5,1),
--             (6,0),(6,1),(7,0),(7,1),(8,0),(8,1),(9,0),
--             (11,0) through (14,0)
--  (19 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 9)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a >= 5 and a <= 8);

?section mido1a2a3b4b
------------------------------------------------------------------------
-- case 1a, 2a, 3b, 4b
-- expect rows (-5,0) through (0,0),(4,0),(5,0),(5,1),
--             (6,0),(6,1),(7,0),(7,1),(8,0),(8,1),(9,0),
--             (11,0) through (14,0)
--  (20 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 9)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a >= 5 and a <= 8);

?section mido1b2b3b4b
------------------------------------------------------------------------
-- case 1b, 2b, 3b, 4b
-- expect rows (-5,0) through (0,0),(4,1),(5,0),(5,1),
--             (6,0),(6,1),(7,0),(7,1),(8,0),(8,1),(9,1),
--             (11,0) through (14,0)
--  (20 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 8)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a >= 4 and a <= 9);

?section mido1c2c3b4b
------------------------------------------------------------------------
-- case 1c, 2c, 3b, 4b
-- expect rows (-5,0) through (0,0),(5,0),(5,1),
--             (6,0),(6,1),(7,0),(7,1),(8,0),(8,1),
--             (11,0) through (14,0)
--  (18 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 5 and a <= 8)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a >= 5 and a <= 8);

?section multint
--==================================================================
--  This section is the fourth part of a "white box" test of
--  evai^or^disjuncts.
--
--  In the first section, we tested the non-overlap code paths.  In
--  the second, we tested overlap code paths, where @prev^interval and
--  @next^interval were both NULLP.  In the third, we tested overlap
--  code paths, where @prev^interval and @next^interval varied.  But,
--  in both the second and third sections, the @interval2 chain
--  consisted of a single interval that was consumed.
--
--  This script, then, combines the dimensions varied in the first
--  section with those in the second and third.  Note that in theory,
--  first + second + third span the vector space.  However, there is a
--  data condition that has not been tested (this is one of the
--  limitations of the vector space model):  We must make sure that
--  when we consume an interval from the interval2 chain, the rest of
--  that chain remains intact.
--
--  So, this section adds additional (non-overlapping) intervals to
--  the interval2 chain.
--
--  The dimensions we have are:
--  1.  front interval:
--    a.  exists and comes from interval1
--    b.  exists and comes from interval2
--    c.  does not exist
--  2.  back interval:
--    a.  exists and comes from interval1
--    b.  exists and comes from interval2
--    c.  does not exist
--  3.  prev interval:
--    a.  NULLP
--    b.  not NULLP
--  4.  next interval:
--    a.  NULLP
--    b.  not NULLP
--  5.  interval2 chain
--    a.  one interval which is consumed
--    b.  consumed interval, with one following that does not overlap
--    c.  consumed interval, with one preceding that does not overlap
--    d.  consumed interval, with one preceding and one following that
--        do not overlap
--
--  The second and third sections held dimension 5 constant at 5a.
--  Here we take a subset of cases from those sections and vary on
--  dimension 5.
--==================================================================

?section multint1a2a3a4b5b
------------------------------------------------------------------------
-- case 1a, 2a, 3a, 4b, 5b
-- expect rows (4,0),(5,0),(6,0),(6,1),(7,0),(8,0),
--             (9,0),(11,0) through (14,0),(20,1)
--  (12 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 9)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a in (6,20));

?section multint1a2a3b4a5b
------------------------------------------------------------------------
-- case 1a, 2a, 3b, 4a, 5b
-- expect rows (-5,0) through (0,0),(4,0),(5,0),
--             (6,0),(6,1),(7,0),(8,0),(9,0),(20,1)
--  (14 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 9)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 1 and a in (6,20));

?section multint1a2a3b4b5b
------------------------------------------------------------------------
-- case 1a, 2a, 3b, 4b, 5b
-- expect rows (-5,0) through (0,0),(4,0),(5,0),
--             (6,0),(6,1),(7,0),(8,0),(9,0),
--             (11,0) through (14,0),(20,1)
--  (18 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 9)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a in (6,20));

?section multint1a2a3a4b5c
------------------------------------------------------------------------
-- case 1a, 2a, 3a, 4b, 5c
-- expect rows (-20,1),(4,0),(5,0),(6,0),(6,1),(7,0),(8,0),
--             (9,0),(11,0) through (14,0)
--  (12 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 9)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a in (6,-20));

?section multint1a2a3b4a5c
------------------------------------------------------------------------
-- case 1a, 2a, 3b, 4a, 5c
-- expect rows (-20,1),(-5,0) through (0,0),(4,0),(5,0),
--             (6,0),(6,1),(7,0),(8,0),(9,0)
--  (14 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 9)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 1 and a in (6,-20));

?section multint1a2a3b4b5c
------------------------------------------------------------------------
-- case 1a, 2a, 3b, 4b, 5c
-- expect rows (-20,1),(-5,0) through (0,0),(4,0),(5,0),
--             (6,0),(6,1),(7,0),(8,0),(9,0),
--             (11,0) through (14,0)
--  (18 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 9)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a in (6,-20));

?section multint1a2a3a4b5d
------------------------------------------------------------------------
-- case 1a, 2a, 3a, 4b, 5d
-- expect rows (-20,1),(4,0),(5,0),(6,0),(6,1),(7,0),(8,0),
--             (9,0),(11,0) through (14,0),(20,1)
--  (13 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 9)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a in (6,-20,20));

?section multint1a2a3b4a5d
------------------------------------------------------------------------
-- case 1a, 2a, 3b, 4a, 5d
-- expect rows (-20,1),(-5,0) through (0,0),(4,0),(5,0),
--             (6,0),(6,1),(7,0),(8,0),(9,0),(20,1)
--  (15 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 9)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 1 and a in (6,-20,20));

?section multint1a2a3b4b5d
------------------------------------------------------------------------
-- case 1a, 2a, 3b, 4b, 5d
-- expect rows (-20,1),(-5,0) through (0,0),(4,0),(5,0),
--             (6,0),(6,1),(7,0),(8,0),(9,0),
--             (11,0) through (14,0),(20,1)
--  (19 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 4 and a <= 9)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a in (6,-20,20));

?section multint1c2c3b4b5d
------------------------------------------------------------------------
-- case 1c, 2c, 3b, 4b, 5d
-- expect rows (-20,1),(-5,0) through (0,0),(6,0),(6,1),
--             (11,0) through (14,0),(20,1)
--  (14 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a = 6)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a in (6,20,-20));

?section multint1c2a3b4b5d
------------------------------------------------------------------------
-- case 1c, 2a, 3b, 4b, 5d
-- expect rows (-20,1),(-5,0) through (0,0),(6,0),(6,1),
--             (7,0),(8,0),(11,0) through (14,0),(20,1)
--  (16 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 6 and a <= 8)
         or
        (b = 0 and a > -6 and a <= 0)
         or
        (b = 0 and a > 10 and a < 15)
         or
        (b = 1 and a in (6,20,-20));

?section multov
--======================================================================
--  This section is the fifth and last part of a "white box" test of
--  evai^or^disjuncts.
--
--  In this section, we do test cases involving multiple overlaps, i.e.
--  where some interval in a new disjunct overlaps several intervals
--  in a prior disjunct or vice versa.
--======================================================================

?section multov1
------------------------------------------------------------------------
-- test this picture:
--       xxxxxxxxxxxx
--       x          x
-- expect rows (6,0),(6,1),(7,0),(8,0),(9,0),(10,0),(10,1)
--  (7 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 6 and a <= 10)
         or
        (b = 1 and a in (6,10));

?section multov2
------------------------------------------------------------------------
-- test this picture:
--       xxxx    xxxx
--       xxxxxxxxxxxx
-- expect rows (6,0),(6,1),(7,0),(7,1),(8,0),(8,1),(9,1),
--             (10,0),(10,1),(11,0),(11,1),(12,0),(12,1)
--  (13 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 6 and a <= 8)
         or
        (b = 0 and a >= 10 and a <= 12)
         or
        (b = 1 and a >= 6 and a <= 12);

?section multov3
------------------------------------------------------------------------
-- test this picture:
--       (xxx    xxx)
--       [xxxxxxxxxx]
-- expect rows (6,1),(7,0),(7,1),(8,0),(8,1),(9,1),
--             (10,0),(10,1),(11,0),(11,1),(12,1)
--  (11 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a > 6 and a <= 8)
         or
        (b = 0 and a >= 10 and a < 12)
         or
        (b = 1 and a >= 6 and a <= 12);

?section multov4
------------------------------------------------------------------------
-- test this picture:
--       [xxx    xxx]
--       (xxxxxxxxxx)
-- expect rows (6,0),(7,0),(7,1),(8,0),(8,1),(9,1),
--             (10,0),(10,1),(11,0),(11,1),(12,0)
--  (11 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 6 and a <= 8)
         or
        (b = 0 and a >= 10 and a <= 12)
         or
        (b = 1 and a > 6 and a < 12);

?section multov5
------------------------------------------------------------------------
-- test this picture:
--       xxxxxxx      xxx   xxx
--            xxxxxxxxxxxxxxxxxxxx
-- expect rows (3,0),(4,0),(4,1),(5,0),(5,1),(6,1),(7,1),(8,1),
--             (9,1),(10,1),(11,0),(11,1),(12,0),(12,1),(13,1),
--             (14,1),(15,1),(16,0),(16,1),(17,0),(17,1),(18,0),
--             (18,1),(19,0),(19,1),(20,1),(21,1)
--  (27 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 3 and a <= 5)
         or
        (b = 0 and a > 10 and a < 13)
         or
        (b = 0 and a >= 16 and a <= 19)
         or
        (b = 1 and a >= 4 and a <= 21);

?section multov6
------------------------------------------------------------------------
-- test this picture:
--       xxxxxxxxx  x xxx xxxx xxx
--    x   x  x  x   x      x       x
-- expect rows (-5,1),(3,0),(4,0),(4,1),(5,0),(6,0),(6,1),(7,0),(8,0),(8,1),
--             (9,0),(13,0),(13,1),(16,0),(17,0),(18,0),(21,0),
--             (22,0),(22,1),(23,0),(24,0),(27,0),(28,0),(29,0),
--             (31,1)
--  (25 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a >= 3 and a <= 9)
         or
        (b = 0 and a = 13)
         or
        (b = 0 and a >= 16 and a <= 18)
         or
        (b = 0 and a >= 21 and a <= 24)
         or
        (b = 0 and a >= 27 and a <= 29)
         or
        (b = 1 and a in (-5,4,6,8,13,22,31));

?section multov7
------------------------------------------------------------------------
-- test this picture:
--      x  xxxx  x  xxxxxxx
--    xxxxxxxxxxxxxxxxx
-- expect rows (1,1),(2,1),(3,0),(3,1),(4,1),(5,1),(6,0),(6,1),
--             (7,0),(7,1),(8,1),(9,0),(9,1),(10,1),(11,1),(12,1),(13,1),
--             (14,1),(15,1),(16,0),(16,1),(17,0),(17,1),(18,0),(19,0)
--  (25 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a in (3,9))
         or
        (b = 0 and a > 5 and a < 8)
         or
        (b = 0 and a >= 16 and a <= 19)
         or
        (b = 1 and a > 0 and a < 18);

?section multov8
------------------------------------------------------------------------
-- test this picture:
--      x  xxxx  x  xxxxxxx
--    xxxxxxxxxxxxxx
-- expect rows (1,1),(2,1),(3,0),(3,1),(4,1),(5,1),(6,0),(6,1),
--             (7,0),(7,1),(8,1),(9,0),(9,1),(10,1),(11,1),(12,1),(13,1),
--             (14,1),(15,1),(16,0),(17,0),(18,0),(19,0)
--  (23 rows)
------------------------------------------------------------------------

select * from t025mdam
  where (b = 0 and a in (3,9))
         or
        (b = 0 and a > 5 and a < 8)
         or
        (b = 0 and a > 15 and a <= 19)
         or
        (b = 1 and a > 0 and a < 16);

control query shape anything;


-- ******************************************************************
--  Test dimensions:
--     1.
--        d.  MDAM
--     2.
--        a.  non-partitioned tables, non-empty
--        b.  non-partitioned tables, innermost table empty,
--                outermost non-empty
--        c.  non-partitioned tables, outermost table empty
--        d.  partitioned tables, all innermost partitions non-empty
--                all outermost partitions non-empty
--        e.  partitioned tables, 1st innermost partition empty,
--                2nd non-empty, all outermost partitions non-empty
--        f.  partitioned tables, 1st innermost partition non-empty,
--                2nd empty, all outermost partitions non-empty
--        g.  partitioned tables, both innermost partitions empty
--                all outermost partitions non-empty
--        h.  partitioned tables, 1st outermost partition empty,
--                2nd non-empty, all innermost partitions non-empty
--        i.  partitioned tables, 1st outermost partition non-empty,
--                2nd empty, all innermost partitions non-empty
--        j.  partitioned tables, both outermost partitions empty
--                all innermost partitions non-empty
--        (note:  c, h, i, j done only for join plans)
--
-- ******************************************************************

-- ******************************************************************
--  3.  Test dimensions 2a with 1d (MDAM)
-- ******************************************************************

-- test 1d2a
#ifdef SEABASE_REGRESS
control query shape groupby(scan('t025m2', MDAM_COLUMNS ALL));
#else
control query shape exchange(groupby(scan('t025m2', MDAM_COLUMNS ALL)));
#endif

prepare s1d2a from
  select count(*),sum(s),min(s),max(s),avg(s) from t025m2
      where r > 4 and r < 60;

-- answer should be 3, 65, 5, 50, 21
execute s1d2a;  

control query shape anything;

-- ******************************************************************
--  4.  Test dimensions 2b with 1d (MDAM)
-- ******************************************************************

-- delete rows from table

delete from t025m2;

-- test 1d2b

-- answer should be  0, ?, ?, ?, ?
execute s1d2a;  

-- ******************************************************************
--  6.  Test dimensions 2d with 1d (MDAM)
-- ******************************************************************

-- The optimizer is not returning a plan to
-- the prepare s1d2d probably because the cqs statement
-- is not adequate. I'll fix it later (09/19/97)

-- drop table t025m2;

-- add a partition to the table, repopulate it

-- create table t025m2 ( q int not null , 
--                              r int not null , 
--                             s int, 
--                             primary key (q,r) )
--                   partition (add first key (140) location /G/DATA2);

-- insert into t025m2 values (1,1,1),
--                                  (1,10,10),
--                                  (5,5,5),
--                                  (5,50,50),
--                                  (201,201,201),
--                                  (205,205,205),
--                                  (205,255,255),
--                                  (205,265,265);

-- test 1d2d
-- control query shape exchange(groupby(scan('t025m2', MDAM_COLUMNS ALL)));

--prepare s1d2d from
--  select count(*),sum(s),min(s),max(s),avg(s) from t025m2
--      where r > 4 and r < 204;

-- answer should be 4, 266, 5, 201, 66
-- execute s1d2d;  


-- ******************************************************************
--  9.  Test dimensions 2e with 1d (MDAM)
-- ******************************************************************

-- test 1d2e

-- delete data from 1st partition
--delete from t025m2 where q < 140;

--prepare s1d2e from
  --select count(*),sum(s),min(s),max(s),avg(s) from t025m2
      --where r > 4 and r < 204;

-- answer should be 1, 201, 201, 201, 201
--execute s1d2e;  

control query shape anything;

-- ******************************************************************
--  10.  Test dimensions 2f with 1d (MDAM)
-- ******************************************************************

-- repopulate 1st partition of innermost table, and make 2nd partition
-- empty

-- s/b 4 rows deleted
--delete from t025m2;
--insert into t025m2 values (1,1,1),
                                 --(1,10,10),
                                 --(5,5,5),
                                 --(5,50,50);

-- test 1d2f

-- answer should be 3, 65, 5, 50, 21
--execute s1d2e;  

?section bugfix1
------------------------------------------------------------------
-- Description:      Genesis case 10-980205-3598                --
-- Expected result:  0 row(s) selected                          --
------------------------------------------------------------------
#ifdef SEABASE_REGRESS
control query shape scan('t025mdam', MDAM_COLUMNS ALL);
#else
control query shape partition_access(scan('t025mdam', MDAM_COLUMNS ALL));
#endif
select * from t025mdam
  where b = 2 and c = 3
     or c = 1
     or c = 2
     or c = 3
     or c = 4
     or c = 5
     or c = 6
  ;
control query shape anything;

?section cleanup

control query shape anything;

drop table t025m2;
drop table t025mdam;
drop table t025mtmp;

log;
