| >>cqd PRIMARY_KEY_CONSTRAINT_DROPPABLE_OPTION 'OFF'; |
| |
| --- SQL operation complete. |
| >>obey TEST024(runtest); |
| >>-- This section comprises the entire test, but is executed twice (from within this |
| >>-- file), first with non-droppable primary keys, and then with droppable primary keys. |
| >>obey TEST024(DDL); |
| >>log; |
| >> |
| >>create table t024mdam ( |
| +> a smallint no default not null , |
| +> b smallint no default not null , |
| +> c smallint no default not null , |
| +> d smallint no default not null, |
| +> e smallint no default not null, |
| +> f smallint no default not null, |
| +> g smallint no default not null, |
| +> h smallint no default not null, |
| +> i smallint no default not null, |
| +> j smallint no default not null, |
| +> primary key (a,b,c) ); |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>obey TEST024(INSERTROWS); |
| >>insert into t024mdam values |
| +> (-32768,-32768,-32768,-32768,-32768,-32768,-32768,-32768,-32768,-32768), |
| +> (0,0,0,0,0,0,0,0,0,0), |
| +> (0,2,2,2,2,2,2,2,2,2), |
| +> (2,0,2,2,2,2,2,2,2,2), |
| +> (2,2,2,2,2,2,2,2,2,2), |
| +> (2,2,3,2,2,2,2,2,2,2), |
| +> (2,2,4,2,2,2,2,2,2,2), |
| +> (2,3,2,2,2,2,2,2,2,2), |
| +> (2,3,3,2,2,2,2,2,2,2), |
| +> (2,3,4,2,2,2,2,2,2,2), |
| +> (3,2,2,2,2,2,2,2,2,2), |
| +> (3,2,3,2,2,2,2,2,2,2), |
| +> (3,2,4,2,2,2,2,2,2,2), |
| +> (3,3,2,2,2,2,2,2,2,2), |
| +> (3,3,3,2,2,2,2,2,2,2), |
| +> (3,3,4,2,2,2,2,2,2,2), |
| +> (32767,32767,32767,32767,32767,32767,32767,32767,32767,32767); |
| |
| --- 17 row(s) inserted. |
| >> |
| >> |
| >> |
| >>-- Run DML tests initially with rangespec_transformation set to MINIMUM. At the time |
| >>-- this change was made it was the default, but that will change at some point. |
| >>cqd RANGESPEC_TRANSFORMATION 'MINIMUM'; |
| |
| --- SQL operation complete. |
| >>obey TEST024(DML); |
| >> |
| >>#ifdef SEABASE_REGRESS |
| >>control query shape scan('t024mdam', MDAM_COLUMNS ALL); |
| |
| --- SQL operation complete. |
| >>#else |
| >>control query shape exchange(scan('t024mdam', MDAM_COLUMNS ALL)); |
| >>#endif |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Simplest case -- |
| >>-- Single disjunct -- |
| >>-- Equal predicates on all 3 key columns -- |
| >>-- -- |
| >>-- Expected result: 1 row (2,2,2,...) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Single disjunct -- |
| >>-- Equal predicates on first 2 columns -- |
| >>-- Open range pred. on 3rd - [value, hi] -- |
| >>-- -- |
| >>-- Expected result: 3 rows (2,2,2,...) -- |
| >>-- (2,2,3,...) -- |
| >>-- (2,2,4,...) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c >= 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 3 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Single disjunct -- |
| >>-- Equal predicates on first 2 columns -- |
| >>-- 2 ORed predicates on 3rd (cluster w/2 cells) -- |
| >>-- -- |
| >>-- Expected result: 2 rows (2,2,2,....) -- |
| >>-- (2,2,4,....) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and (c = 4 or c = 2); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 2 disjuncts - -- |
| >>-- One has equal pred. on all 3 key columns -- |
| >>-- (no rows satisfy this disjunct) -- |
| >>-- Other has equal pred. on first 2 columns -- |
| >>-- and OR'ed equal pred. on 3rd key column -- |
| >>-- -- |
| >>-- Expected result: 1 row (2,0,2,....) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 0 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 2 and c = 0); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 0 2 2 2 2 2 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 2 disjuncts - -- |
| >>-- One has equal pred. on first 2 columns -- |
| >>-- and OR'ed equal pred. on 3rd key column -- |
| >>-- Other has equal pred. on first 2 columns -- |
| >>-- and no predicate on 3rd key column -- |
| >>-- -- |
| >>-- Expected result: 2 rows (0,2,2,....) -- |
| >>-- (2,0,2,....) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 0 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 2); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 2 2 2 2 2 2 2 2 2 |
| 2 0 2 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 2 disjuncts - -- |
| >>-- One has equal pred. on first 2 columns -- |
| >>-- and OR'ed equal pred. on 3rd key column -- |
| >>-- Other has equal pred. on all 3 columns -- |
| >>-- -- |
| >>-- Expected result: 3 rows (0,0,0,....) -- |
| >>-- (2,2,2,....) |
| >>-- (2,2,4,....) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 2 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 0 and c = 0); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 3 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 3 disjuncts - -- |
| >>-- Predicates on all 3 key columns in all -- |
| >>-- 3 disjuncts -- |
| >>-- < and > operators introduced in this script-- |
| >>-- Single and OR'ed equal predicates also -- |
| >>-- used in this script -- |
| >>-- 2 disjuncts produced 0 rows -- |
| >>-- -- |
| >>-- Expected result: 5 rows (2,0,2,....) -- |
| >>-- (2,2,2,....) |
| >>-- (2,2,4,....) |
| >>-- (2,3,2,....) |
| >>-- (2,3,4,....) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b > -1 and b < 5 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 2 and c = 0) |
| +> or (a = 5 and b > 5 and b < 4 and c = 5); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 0 2 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| 2 3 2 2 2 2 2 2 2 2 |
| 2 3 4 2 2 2 2 2 2 2 |
| |
| --- 5 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 3 disjuncts - -- |
| >>-- Predicates on all 3 key columns in all -- |
| >>-- 3 disjuncts -- |
| >>-- <= and >= operators introduced in this -- |
| >>-- script. -- |
| >>-- Single and OR'ed equal predicates, and -- |
| >>-- < and > operators also used. -- |
| >>-- 1 disjunct produces 0 rows -- |
| >>-- -- |
| >>-- Expected result: 8 rows (0,0,0,....) -- |
| >>-- (2,0,2,....) -- |
| >>-- (2,2,2,....) -- |
| >>-- (2,2,3,....) -- |
| >>-- (2,2,4,....) -- |
| >>-- (2,3,2,....) -- |
| >>-- (2,3,3,....) -- |
| >>-- (2,3,4,....) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b > -1 and b < 5 and c >= 2 and c <= 4) |
| +> or (a = 0 and b = 0 and c = 0) |
| +> or (a = 2 and b = 2 and c = -6); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| 2 0 2 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| 2 3 2 2 2 2 2 2 2 2 |
| 2 3 3 2 2 2 2 2 2 2 |
| 2 3 4 2 2 2 2 2 2 2 |
| |
| --- 8 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Just one interval and just one disjunct |
| >>-- Result should be 2 rows: (2,2,2,...) |
| >>-- (3,2,2,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a between 2 and 4 and b = 2 and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 3 2 2 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Two intervals in the same disjunct, last column |
| >>-- Result should be 2 rows: (2,2,2,...) and (2,2,4,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c in (2,4); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Two intervals in the same disjunct, interior column |
| >>-- Result should be 2 rows: (2,2,2,...) and (2,3,2,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b in (2, 3) and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 3 2 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Just one interval, just one disjunct, using open endpoint |
| >>-- on beginning endpoint, last column. |
| >>-- Result should be 2 rows: (2,2,3,..) and (2,2,4,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c > 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Just one interval, just one disjunct, using open endpoint |
| >>-- on beginning endpoint, interior column |
| >>-- Result should be 1 row: (2,3,2,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b > 2 and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 3 2 2 2 2 2 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Just one interval, just one disjunct, using open endpoint |
| >>-- on ending endpoint, last column |
| >>-- Result should be 2 rows: (2,2,2,...) and (2,2,3,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c < 4; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Just one interval, just one disjunct, using open endpoint |
| >>-- on ending endpoint, interior column |
| >>-- Result should be 2 rows: (2,0,2,...) and (2,2,2,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b >= 0 and b < 3 and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 0 2 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Just one interval, just one disjunct, no point intervals |
| >>-- Result should be 8 rows: (2,2,2,...), (2,2,3,...), (2,3,2,...), |
| >>-- (2,3,3,...), (3,2,2,...), (3,2,3,...), |
| >>-- (3,3,2,...), (3,3,3,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a >= 1 and a <= 3 and |
| +> b >= 1 and b <= 3 and |
| +> c >= 1 and c <= 3; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 3 2 2 2 2 2 2 2 2 |
| 2 3 3 2 2 2 2 2 2 2 |
| 3 2 2 2 2 2 2 2 2 2 |
| 3 2 3 2 2 2 2 2 2 2 |
| 3 3 2 2 2 2 2 2 2 2 |
| 3 3 3 2 2 2 2 2 2 2 |
| |
| --- 8 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Test hitting MAX VALUE |
| >>-- Result should be 1 row: (32767, 32767, 32767,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 32767 and b > 32760 and c > 32760; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 32767 32767 32767 32767 32767 32767 32767 32767 32767 32767 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Test starting at MIN VALUE |
| >>-- Result should be 1 row: (-32768, -32768, -32768,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = -32768 and b < -32760 and c < 0; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| -32768 -32768 -32768 -32768 -32768 -32768 -32768 -32768 -32768 -32768 |
| |
| --- 1 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- No predicate for first disjunct on last column |
| >>-- Result should be 4 rows: (0,0,0,...), (2,2,2,...), |
| >>-- (2,2,3,...), (2,2,4,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 2) |
| +> or (a = 0 and b = 0 and c < 3); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 4 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Empty predicate for first disjunct on last column |
| >>-- Result should be 1 row: (0,0,0,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 2 and c > 12 and c < 1) |
| +> or (a = 0 and b = 0 and c < 3); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Empty predicate for first disjunct on 2nd column |
| >>-- Result should be 1 row: (0,0,0,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 2 and b < 0) |
| +> or (a = 0 and b = 0 and c < 3); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Empty predicate for first disjunct on 1st column |
| >>-- Result should be 1 row: (0,0,0,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a > 2 and a <= 2 and b = 2 and c = 2) |
| +> or (a = 0 and b = 0 and c < 3); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- All empty predicates! |
| >>-- Result should be no rows. |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 5 and a = 3 and b = 2) |
| +> or (a = 2 and b = 7 and |
| +> (c = 4 or c = 27 or c > 92 or c < 35) and c > 20 and c = 3) |
| +> or (a > 26 and a < -1); |
| |
| --- 0 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Lots of overlapping disjuncts |
| >>-- Result should be 4 rows: (0,0,0,...), (0,2,2,...), |
| >>-- (2,0,2,...), (2,2,2,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a in (0, 2, 5) and a < 4 and b = 0 and c < 5) |
| +> or (a = 0 and (b = 0 or b >= 2) and b < 4) |
| +> or (a = 2 and b = 2 and c < 3); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| 0 2 2 2 2 2 2 2 2 2 |
| 2 0 2 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 2 2 |
| |
| --- 4 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Duplicate disjuncts |
| >>-- Result should be 1 row: (2,2,2,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 2 and c = 2) |
| +> or (a = 2 and b = 2 and c = 2); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Nearly duplicate disjuncts |
| >>-- Result should be 3 rows: (2,2,2,...), (2,2,3,...), (2,2,4,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 2 and c = 3) |
| +> or (a = 2 and b = 2 and c > 0); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 3 row(s) selected. |
| >> |
| >>control query shape anything; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >> |
| >>--========================================================================== |
| >>-- Repeat with full rangespec transformation enabled. Results should be same. |
| >>cqd RANGESPEC_TRANSFORMATION 'ON'; |
| |
| --- SQL operation complete. |
| >>obey TEST024(DML); |
| >> |
| >>#ifdef SEABASE_REGRESS |
| >>control query shape scan('t024mdam', MDAM_COLUMNS ALL); |
| |
| --- SQL operation complete. |
| >>#else |
| >>control query shape exchange(scan('t024mdam', MDAM_COLUMNS ALL)); |
| >>#endif |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Simplest case -- |
| >>-- Single disjunct -- |
| >>-- Equal predicates on all 3 key columns -- |
| >>-- -- |
| >>-- Expected result: 1 row (2,2,2,...) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Single disjunct -- |
| >>-- Equal predicates on first 2 columns -- |
| >>-- Open range pred. on 3rd - [value, hi] -- |
| >>-- -- |
| >>-- Expected result: 3 rows (2,2,2,...) -- |
| >>-- (2,2,3,...) -- |
| >>-- (2,2,4,...) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c >= 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 3 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Single disjunct -- |
| >>-- Equal predicates on first 2 columns -- |
| >>-- 2 ORed predicates on 3rd (cluster w/2 cells) -- |
| >>-- -- |
| >>-- Expected result: 2 rows (2,2,2,....) -- |
| >>-- (2,2,4,....) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and (c = 4 or c = 2); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 2 disjuncts - -- |
| >>-- One has equal pred. on all 3 key columns -- |
| >>-- (no rows satisfy this disjunct) -- |
| >>-- Other has equal pred. on first 2 columns -- |
| >>-- and OR'ed equal pred. on 3rd key column -- |
| >>-- -- |
| >>-- Expected result: 1 row (2,0,2,....) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 0 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 2 and c = 0); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 0 2 2 2 2 2 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 2 disjuncts - -- |
| >>-- One has equal pred. on first 2 columns -- |
| >>-- and OR'ed equal pred. on 3rd key column -- |
| >>-- Other has equal pred. on first 2 columns -- |
| >>-- and no predicate on 3rd key column -- |
| >>-- -- |
| >>-- Expected result: 2 rows (0,2,2,....) -- |
| >>-- (2,0,2,....) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 0 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 2); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 2 2 2 2 2 2 2 2 2 |
| 2 0 2 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 2 disjuncts - -- |
| >>-- One has equal pred. on first 2 columns -- |
| >>-- and OR'ed equal pred. on 3rd key column -- |
| >>-- Other has equal pred. on all 3 columns -- |
| >>-- -- |
| >>-- Expected result: 3 rows (0,0,0,....) -- |
| >>-- (2,2,2,....) |
| >>-- (2,2,4,....) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 2 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 0 and c = 0); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 3 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 3 disjuncts - -- |
| >>-- Predicates on all 3 key columns in all -- |
| >>-- 3 disjuncts -- |
| >>-- < and > operators introduced in this script-- |
| >>-- Single and OR'ed equal predicates also -- |
| >>-- used in this script -- |
| >>-- 2 disjuncts produced 0 rows -- |
| >>-- -- |
| >>-- Expected result: 5 rows (2,0,2,....) -- |
| >>-- (2,2,2,....) |
| >>-- (2,2,4,....) |
| >>-- (2,3,2,....) |
| >>-- (2,3,4,....) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b > -1 and b < 5 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 2 and c = 0) |
| +> or (a = 5 and b > 5 and b < 4 and c = 5); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 0 2 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| 2 3 2 2 2 2 2 2 2 2 |
| 2 3 4 2 2 2 2 2 2 2 |
| |
| --- 5 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 3 disjuncts - -- |
| >>-- Predicates on all 3 key columns in all -- |
| >>-- 3 disjuncts -- |
| >>-- <= and >= operators introduced in this -- |
| >>-- script. -- |
| >>-- Single and OR'ed equal predicates, and -- |
| >>-- < and > operators also used. -- |
| >>-- 1 disjunct produces 0 rows -- |
| >>-- -- |
| >>-- Expected result: 8 rows (0,0,0,....) -- |
| >>-- (2,0,2,....) -- |
| >>-- (2,2,2,....) -- |
| >>-- (2,2,3,....) -- |
| >>-- (2,2,4,....) -- |
| >>-- (2,3,2,....) -- |
| >>-- (2,3,3,....) -- |
| >>-- (2,3,4,....) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b > -1 and b < 5 and c >= 2 and c <= 4) |
| +> or (a = 0 and b = 0 and c = 0) |
| +> or (a = 2 and b = 2 and c = -6); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| 2 0 2 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| 2 3 2 2 2 2 2 2 2 2 |
| 2 3 3 2 2 2 2 2 2 2 |
| 2 3 4 2 2 2 2 2 2 2 |
| |
| --- 8 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Just one interval and just one disjunct |
| >>-- Result should be 2 rows: (2,2,2,...) |
| >>-- (3,2,2,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a between 2 and 4 and b = 2 and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 3 2 2 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Two intervals in the same disjunct, last column |
| >>-- Result should be 2 rows: (2,2,2,...) and (2,2,4,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c in (2,4); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Two intervals in the same disjunct, interior column |
| >>-- Result should be 2 rows: (2,2,2,...) and (2,3,2,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b in (2, 3) and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 3 2 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Just one interval, just one disjunct, using open endpoint |
| >>-- on beginning endpoint, last column. |
| >>-- Result should be 2 rows: (2,2,3,..) and (2,2,4,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c > 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Just one interval, just one disjunct, using open endpoint |
| >>-- on beginning endpoint, interior column |
| >>-- Result should be 1 row: (2,3,2,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b > 2 and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 3 2 2 2 2 2 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Just one interval, just one disjunct, using open endpoint |
| >>-- on ending endpoint, last column |
| >>-- Result should be 2 rows: (2,2,2,...) and (2,2,3,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c < 4; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Just one interval, just one disjunct, using open endpoint |
| >>-- on ending endpoint, interior column |
| >>-- Result should be 2 rows: (2,0,2,...) and (2,2,2,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b >= 0 and b < 3 and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 0 2 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Just one interval, just one disjunct, no point intervals |
| >>-- Result should be 8 rows: (2,2,2,...), (2,2,3,...), (2,3,2,...), |
| >>-- (2,3,3,...), (3,2,2,...), (3,2,3,...), |
| >>-- (3,3,2,...), (3,3,3,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a >= 1 and a <= 3 and |
| +> b >= 1 and b <= 3 and |
| +> c >= 1 and c <= 3; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 3 2 2 2 2 2 2 2 2 |
| 2 3 3 2 2 2 2 2 2 2 |
| 3 2 2 2 2 2 2 2 2 2 |
| 3 2 3 2 2 2 2 2 2 2 |
| 3 3 2 2 2 2 2 2 2 2 |
| 3 3 3 2 2 2 2 2 2 2 |
| |
| --- 8 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Test hitting MAX VALUE |
| >>-- Result should be 1 row: (32767, 32767, 32767,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 32767 and b > 32760 and c > 32760; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 32767 32767 32767 32767 32767 32767 32767 32767 32767 32767 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Test starting at MIN VALUE |
| >>-- Result should be 1 row: (-32768, -32768, -32768,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = -32768 and b < -32760 and c < 0; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| -32768 -32768 -32768 -32768 -32768 -32768 -32768 -32768 -32768 -32768 |
| |
| --- 1 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- No predicate for first disjunct on last column |
| >>-- Result should be 4 rows: (0,0,0,...), (2,2,2,...), |
| >>-- (2,2,3,...), (2,2,4,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 2) |
| +> or (a = 0 and b = 0 and c < 3); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 4 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Empty predicate for first disjunct on last column |
| >>-- Result should be 1 row: (0,0,0,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 2 and c > 12 and c < 1) |
| +> or (a = 0 and b = 0 and c < 3); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Empty predicate for first disjunct on 2nd column |
| >>-- Result should be 1 row: (0,0,0,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 2 and b < 0) |
| +> or (a = 0 and b = 0 and c < 3); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Empty predicate for first disjunct on 1st column |
| >>-- Result should be 1 row: (0,0,0,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a > 2 and a <= 2 and b = 2 and c = 2) |
| +> or (a = 0 and b = 0 and c < 3); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- All empty predicates! |
| >>-- Result should be no rows. |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 5 and a = 3 and b = 2) |
| +> or (a = 2 and b = 7 and |
| +> (c = 4 or c = 27 or c > 92 or c < 35) and c > 20 and c = 3) |
| +> or (a > 26 and a < -1); |
| |
| --- 0 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Lots of overlapping disjuncts |
| >>-- Result should be 4 rows: (0,0,0,...), (0,2,2,...), |
| >>-- (2,0,2,...), (2,2,2,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a in (0, 2, 5) and a < 4 and b = 0 and c < 5) |
| +> or (a = 0 and (b = 0 or b >= 2) and b < 4) |
| +> or (a = 2 and b = 2 and c < 3); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| 0 2 2 2 2 2 2 2 2 2 |
| 2 0 2 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 2 2 |
| |
| --- 4 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Duplicate disjuncts |
| >>-- Result should be 1 row: (2,2,2,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 2 and c = 2) |
| +> or (a = 2 and b = 2 and c = 2); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Nearly duplicate disjuncts |
| >>-- Result should be 3 rows: (2,2,2,...), (2,2,3,...), (2,2,4,...) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 2 and c = 3) |
| +> or (a = 2 and b = 2 and c > 0); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 3 row(s) selected. |
| >> |
| >>control query shape anything; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >> |
| >>--========================================================================== |
| >> |
| >>obey TEST024(INSERT2); |
| >> |
| >>insert into t024mdam values |
| +> (2,4,2,2,2,2,2,2,2,2), |
| +> (2,4,3,2,2,2,2,2,2,2), |
| +> (2,4,4,2,2,2,2,2,2,2), |
| +> (2,5,2,2,2,2,2,2,2,2), |
| +> (2,5,3,2,2,2,2,2,2,2), |
| +> (2,5,4,2,2,2,2,2,2,2), |
| +> (2,6,2,2,2,2,2,2,2,2), |
| +> (2,6,3,2,2,2,2,2,2,2), |
| +> (2,6,4,2,2,2,2,2,2,2), |
| +> (3,4,2,2,2,2,2,2,2,2), |
| +> (3,4,3,2,2,2,2,2,2,2), |
| +> (3,4,4,2,2,2,2,2,2,2), |
| +> (3,5,2,2,2,2,2,2,2,2), |
| +> (3,5,3,2,2,2,2,2,2,2); |
| |
| --- 14 row(s) inserted. |
| >>insert into t024mdam values |
| +> (3,5,4,2,2,2,2,2,2,2), |
| +> (3,6,2,2,2,2,2,2,2,2), |
| +> (3,6,3,2,2,2,2,2,2,2), |
| +> (3,6,4,2,2,2,2,2,2,2), |
| +> (5,2,2,2,2,2,2,2,2,2), |
| +> (5,2,3,2,2,2,2,2,2,2), |
| +> (5,2,4,2,2,2,2,2,2,2), |
| +> (5,3,2,2,2,2,2,2,2,2), |
| +> (5,3,3,2,2,2,2,2,2,2), |
| +> (5,3,4,2,2,2,2,2,2,2), |
| +> (6,2,2,2,2,2,2,2,2,2), |
| +> (6,2,3,2,2,2,2,2,2,2), |
| +> (6,2,4,2,2,2,2,2,2,2); |
| |
| --- 13 row(s) inserted. |
| >>insert into t024mdam values |
| +> (6,3,2,2,2,2,2,2,2,2), |
| +> (6,3,3,2,2,2,2,2,2,2), |
| +> (6,3,4,2,2,2,2,2,2,2), |
| +> (7,2,2,2,2,2,2,2,2,2), |
| +> (7,2,3,2,2,2,2,2,2,2), |
| +> (7,2,4,2,2,2,2,2,2,2), |
| +> (7,3,2,2,2,2,2,2,2,2), |
| +> (7,3,3,2,2,2,2,2,2,2), |
| +> (7,3,4,2,2,2,2,2,2,2), |
| +> (10,2,2,2,2,2,2,2,2,2), |
| +> (10,2,3,2,2,2,2,2,2,2), |
| +> (10,2,4,2,2,2,2,2,2,2), |
| +> (10,3,2,2,2,2,2,2,2,2), |
| +> (10,3,3,2,2,2,2,2,2,2), |
| +> (10,3,4,2,2,2,2,2,2,2), |
| +> (11,2,2,2,2,2,2,2,2,2), |
| +> (11,2,3,2,2,2,2,2,2,2), |
| +> (11,2,4,2,2,2,2,2,2,2); |
| |
| --- 18 row(s) inserted. |
| >>insert into t024mdam values |
| +> (11,3,2,2,2,2,2,2,2,2), |
| +> (11,3,3,2,2,2,2,2,2,2), |
| +> (11,3,4,2,2,2,2,2,2,2), |
| +> (12,2,2,2,2,2,2,2,2,2), |
| +> (12,2,3,2,2,2,2,2,2,2), |
| +> (12,2,4,2,2,2,2,2,2,2), |
| +> (12,3,2,2,2,2,2,2,2,2), |
| +> (12,3,3,2,2,2,2,2,2,2), |
| +> (12,3,4,2,2,2,2,2,2,2), |
| +> (13,2,2,2,2,2,2,2,2,2), |
| +> (13,2,3,2,2,2,2,2,2,2), |
| +> (13,2,4,2,2,2,2,2,2,2), |
| +> (13,3,2,2,2,2,2,2,2,2), |
| +> (13,3,3,2,2,2,2,2,2,2), |
| +> (13,3,4,2,2,2,2,2,2,2); |
| |
| --- 15 row(s) inserted. |
| >>insert into t024mdam values |
| +> (14,2,2,2,2,2,2,2,2,2), |
| +> (14,2,3,2,2,2,2,2,2,2), |
| +> (14,2,4,2,2,2,2,2,2,2), |
| +> (14,3,2,2,2,2,2,2,2,2), |
| +> (14,3,3,2,2,2,2,2,2,2), |
| +> (14,3,4,2,2,2,2,2,2,2), |
| +> (15,2,2,2,2,2,2,2,2,2), |
| +> (15,2,3,2,2,2,2,2,2,2), |
| +> (15,2,4,2,2,2,2,2,2,2), |
| +> (15,3,2,2,2,2,2,2,2,2), |
| +> (15,3,3,2,2,2,2,2,2,2), |
| +> (15,3,4,2,2,2,2,2,2,2), |
| +> (16,2,2,2,2,2,2,2,2,2), |
| +> (16,2,3,2,2,2,2,2,2,2), |
| +> (16,2,4,2,2,2,2,2,2,2), |
| +> (16,3,2,2,2,2,2,2,2,2); |
| |
| --- 16 row(s) inserted. |
| >>insert into t024mdam values |
| +> (16,3,3,2,2,2,2,2,2,2), |
| +> (16,3,4,2,2,2,2,2,2,2), |
| +> (17,2,2,2,2,2,2,2,2,2), |
| +> (17,2,3,2,2,2,2,2,2,2), |
| +> (17,2,4,2,2,2,2,2,2,2), |
| +> (17,3,2,2,2,2,2,2,2,2), |
| +> (17,3,3,2,2,2,2,2,2,2), |
| +> (17,3,4,2,2,2,2,2,2,2), |
| +> (18,2,2,2,2,2,2,2,2,2), |
| +> (18,2,3,2,2,2,2,2,2,2), |
| +> (18,2,4,2,2,2,2,2,2,2), |
| +> (18,3,2,2,2,2,2,2,2,2), |
| +> (18,3,3,2,2,2,2,2,2,2), |
| +> (18,3,4,2,2,2,2,2,2,2); |
| |
| --- 14 row(s) inserted. |
| >> |
| >> |
| >> |
| >>-- Run additional DML tests with rangespec_transformation set to MINIMUM and ON. |
| >>cqd RANGESPEC_TRANSFORMATION 'MINIMUM'; |
| |
| --- SQL operation complete. |
| >>obey TEST024(DML2); |
| >> |
| >>#ifdef SEABASE_REGRESS |
| >>control query shape scan('t024mdam', MDAM_COLUMNS ALL); |
| |
| --- SQL operation complete. |
| >>#else |
| >>control query shape exchange(scan('t024mdam', MDAM_COLUMNS ALL)); |
| >>#endif |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Simplest case -- |
| >>-- Single disjunct -- |
| >>-- Equal predicates on all 3 key columns -- |
| >>-- -- |
| >>-- Expected result: 1 row (2,2,2,...) -- |
| >>-- statistics should be same for sparse and -- |
| >>-- adaptive dense. -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Single disjunct -- |
| >>-- Equal predicates on first 2 columns -- |
| >>-- Open range pred. on 3rd - [value, hi] -- |
| >>-- -- |
| >>-- Expected result: 3 rows (2,2,2,...) -- |
| >>-- (2,2,3,...) -- |
| >>-- (2,2,4,...) -- |
| >>-- statistics should be same for sparse and -- |
| >>-- adaptive dense. -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c >= 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 3 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Single disjunct -- |
| >>-- range on first column, equality on last. -- |
| >>-- -- |
| >>-- Expected result: 6 rows (2,0,2,....) -- |
| >>-- (2,2,2,....) -- |
| >>-- (2,3,2,....) -- |
| >>-- (2,4,2,....) -- |
| >>-- (2,5,2,....) -- |
| >>-- (2,6,2,....) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 0 2 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 3 2 2 2 2 2 2 2 2 |
| 2 4 2 2 2 2 2 2 2 2 |
| 2 5 2 2 2 2 2 2 2 2 |
| 2 6 2 2 2 2 2 2 2 2 |
| |
| --- 6 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Single disjunct -- |
| >>-- Range on first and second columns. -- |
| >>-- -- |
| >>-- Expected result: 103 -- |
| >>------------------------------------------------------------------ |
| >>#ifdef SEABASE_REGRESS |
| >>control query shape groupby(scan('t024mdam', MDAM_COLUMNS ALL)); |
| |
| --- SQL operation complete. |
| >>#else |
| >>control query shape exchange(groupby(scan('t024mdam', MDAM_COLUMNS ALL))); |
| >>#endif |
| >> |
| >> |
| >>select count(*) from t024mdam |
| +>where a >= 0 and b >= 2 and c in (2,3,4,5,6); |
| |
| (EXPR) |
| -------------------- |
| |
| 103 |
| |
| --- 1 row(s) selected. |
| >> |
| >>control query shape anything; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>--========================================================================== |
| >> |
| >> |
| >>cqd RANGESPEC_TRANSFORMATION 'ON'; |
| |
| --- SQL operation complete. |
| >>obey TEST024(DML2); |
| >> |
| >>#ifdef SEABASE_REGRESS |
| >>control query shape scan('t024mdam', MDAM_COLUMNS ALL); |
| |
| --- SQL operation complete. |
| >>#else |
| >>control query shape exchange(scan('t024mdam', MDAM_COLUMNS ALL)); |
| >>#endif |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Simplest case -- |
| >>-- Single disjunct -- |
| >>-- Equal predicates on all 3 key columns -- |
| >>-- -- |
| >>-- Expected result: 1 row (2,2,2,...) -- |
| >>-- statistics should be same for sparse and -- |
| >>-- adaptive dense. -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Single disjunct -- |
| >>-- Equal predicates on first 2 columns -- |
| >>-- Open range pred. on 3rd - [value, hi] -- |
| >>-- -- |
| >>-- Expected result: 3 rows (2,2,2,...) -- |
| >>-- (2,2,3,...) -- |
| >>-- (2,2,4,...) -- |
| >>-- statistics should be same for sparse and -- |
| >>-- adaptive dense. -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c >= 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 3 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Single disjunct -- |
| >>-- range on first column, equality on last. -- |
| >>-- -- |
| >>-- Expected result: 6 rows (2,0,2,....) -- |
| >>-- (2,2,2,....) -- |
| >>-- (2,3,2,....) -- |
| >>-- (2,4,2,....) -- |
| >>-- (2,5,2,....) -- |
| >>-- (2,6,2,....) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 0 2 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 3 2 2 2 2 2 2 2 2 |
| 2 4 2 2 2 2 2 2 2 2 |
| 2 5 2 2 2 2 2 2 2 2 |
| 2 6 2 2 2 2 2 2 2 2 |
| |
| --- 6 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Single disjunct -- |
| >>-- Range on first and second columns. -- |
| >>-- -- |
| >>-- Expected result: 103 -- |
| >>------------------------------------------------------------------ |
| >>#ifdef SEABASE_REGRESS |
| >>control query shape groupby(scan('t024mdam', MDAM_COLUMNS ALL)); |
| |
| --- SQL operation complete. |
| >>#else |
| >>control query shape exchange(groupby(scan('t024mdam', MDAM_COLUMNS ALL))); |
| >>#endif |
| >> |
| >> |
| >>select count(*) from t024mdam |
| +>where a >= 0 and b >= 2 and c in (2,3,4,5,6); |
| |
| (EXPR) |
| -------------------- |
| |
| 103 |
| |
| --- 1 row(s) selected. |
| >> |
| >>control query shape anything; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>--========================================================================== |
| >> |
| >> |
| >>cqd RANGESPEC_TRANSFORMATION RESET; |
| |
| --- SQL operation complete. |
| >> |
| >>-- Run histogram tests with rangespec_transformation set to MINIMUM and ON. |
| >>cqd RANGESPEC_TRANSFORMATION 'MINIMUM'; |
| |
| --- SQL operation complete. |
| >>obey TEST024(HISTOGRAMS); |
| >>log; |
| >> |
| >>create table t024mdam ( |
| +> a smallint no default not null , |
| +> b smallint no default not null , |
| +> c smallint no default not null , |
| +> d smallint no default not null, |
| +> e smallint no default not null, |
| +> f smallint no default not null, |
| +> g smallint no default not null, |
| +> h smallint no default not null, |
| +> i smallint no default not null, |
| +> j smallint no default not null, |
| +> primary key (a,b,c) ); |
| |
| --- SQL operation complete. |
| >> |
| >>insert into t024mdam values |
| +> (-32768,-32768,-32768,-32768,-32768,-32768,-32768,-32768,-32768,-32768), |
| +> (0,0,0,0,0,0,0,0,0,0), |
| +> (0,2,2,2,2,2,2,2,2,2), |
| +> (2,0,2,2,2,2,2,2,2,2), |
| +> (2,2,2,2,2,2,2,2,2,2), |
| +> (2,2,3,2,2,2,2,2,2,2), |
| +> (2,2,4,2,2,2,2,2,2,2), |
| +> (2,3,2,2,2,2,2,2,2,2), |
| +> (2,3,3,2,2,2,2,2,2,2), |
| +> (2,3,4,2,2,2,2,2,2,2), |
| +> (3,2,2,2,2,2,2,2,2,2), |
| +> (3,2,3,2,2,2,2,2,2,2), |
| +> (3,2,4,2,2,2,2,2,2,2), |
| +> (3,3,2,2,2,2,2,2,2,2), |
| +> (3,3,3,2,2,2,2,2,2,2), |
| +> (3,3,4,2,2,2,2,2,2,2), |
| +> (32767,32767,32767,32767,32767,32767,32767,32767,32767,32767); |
| |
| --- 17 row(s) inserted. |
| >> |
| >>update statistics for table t024mdam on every column generate 6 intervals; |
| |
| *** ERROR[1002] Catalog SEABASE does not exist or has not been registered on node \NSK. |
| |
| *** ERROR[4082] Object SEABASE.SCH.T024MDAM does not exist or is inaccessible. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>#ifdef SEABASE_REGRESS |
| >>control query shape scan('t024mdam', MDAM_COLUMNS ALL); |
| |
| --- SQL operation complete. |
| >>#else |
| >>control query shape exchange(scan('t024mdam', MDAM_COLUMNS ALL)); |
| >>#endif |
| >> |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Simplest case -- |
| >>-- Single disjunct -- |
| >>-- Equal predicates on all 3 key columns -- |
| >>-- -- |
| >>-- Expected result: 1 row (2,2,2,...) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Single disjunct -- |
| >>-- Equal predicates on first 2 columns -- |
| >>-- Open range pred. on 3rd - [value, hi] -- |
| >>-- -- |
| >>-- Expected result: 3 rows (2,2,2,...) -- |
| >>-- (2,2,3,...) -- |
| >>-- (2,2,4,...) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c >= 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 3 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Single disjunct -- |
| >>-- Equal predicates on first 2 columns -- |
| >>-- 2 ORed predicates on 3rd (cluster w/2 cells) -- |
| >>-- -- |
| >>-- Expected result: 2 rows (2,2,2,....) -- |
| >>-- (2,2,4,....) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and (c = 4 or c = 2); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 2 disjuncts - -- |
| >>-- One has equal pred. on all 3 key columns -- |
| >>-- (no rows satisfy this disjunct) -- |
| >>-- Other has equal pred. on first 2 columns -- |
| >>-- and OR'ed equal pred. on 3rd key column -- |
| >>-- -- |
| >>-- Expected result: 1 row (2,0,2,....) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 0 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 2 and c = 0); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 0 2 2 2 2 2 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 2 disjuncts - -- |
| >>-- One has equal pred. on first 2 columns -- |
| >>-- and OR'ed equal pred. on 3rd key column -- |
| >>-- Other has equal pred. on first 2 columns -- |
| >>-- and no predicate on 3rd key column -- |
| >>-- -- |
| >>-- Expected result: 2 rows (0,2,2,....) -- |
| >>-- (2,0,2,....) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 0 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 2); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 2 2 2 2 2 2 2 2 2 |
| 2 0 2 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 2 disjuncts - -- |
| >>-- One has equal pred. on first 2 columns -- |
| >>-- and OR'ed equal pred. on 3rd key column -- |
| >>-- Other has equal pred. on all 3 columns -- |
| >>-- -- |
| >>-- Expected result: 3 rows (0,0,0,....) -- |
| >>-- (2,2,2,....) |
| >>-- (2,2,4,....) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 2 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 0 and c = 0); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 3 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 3 disjuncts - -- |
| >>-- Predicates on all 3 key columns in all -- |
| >>-- 3 disjuncts -- |
| >>-- < and > operators introduced in this script-- |
| >>-- Single and OR'ed equal predicates also -- |
| >>-- used in this script -- |
| >>-- 2 disjuncts produced 0 rows -- |
| >>-- -- |
| >>-- Expected result: 5 rows (2,0,2,....) -- |
| >>-- (2,2,2,....) |
| >>-- (2,2,4,....) |
| >>-- (2,3,2,....) |
| >>-- (2,3,4,....) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b > -1 and b < 5 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 2 and c = 0) |
| +> or (a = 5 and b > 5 and b < 4 and c = 5); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 0 2 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| 2 3 2 2 2 2 2 2 2 2 |
| 2 3 4 2 2 2 2 2 2 2 |
| |
| --- 5 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 3 disjuncts - -- |
| >>-- Predicates on all 3 key columns in all -- |
| >>-- 3 disjuncts -- |
| >>-- <= and >= operators introduced in this -- |
| >>-- script. -- |
| >>-- Single and OR'ed equal predicates, and -- |
| >>-- < and > operators also used. -- |
| >>-- 1 disjunct produces 0 rows -- |
| >>-- -- |
| >>-- Expected result: 8 rows (0,0,0,....) -- |
| >>-- (2,0,2,....) -- |
| >>-- (2,2,2,....) -- |
| >>-- (2,2,3,....) -- |
| >>-- (2,2,4,....) -- |
| >>-- (2,3,2,....) -- |
| >>-- (2,3,3,....) -- |
| >>-- (2,3,4,....) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b > -1 and b < 5 and c >= 2 and c <= 4) |
| +> or (a = 0 and b = 0 and c = 0) |
| +> or (a = 2 and b = 2 and c = -6); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| 2 0 2 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| 2 3 2 2 2 2 2 2 2 2 |
| 2 3 3 2 2 2 2 2 2 2 |
| 2 3 4 2 2 2 2 2 2 2 |
| |
| --- 8 row(s) selected. |
| >> |
| >>control query shape anything; |
| |
| --- SQL operation complete. |
| >> |
| >>drop table t024mdam; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>cqd RANGESPEC_TRANSFORMATION 'ON'; |
| |
| --- SQL operation complete. |
| >>obey TEST024(HISTOGRAMS); |
| >>log; |
| >> |
| >>create table t024mdam ( |
| +> a smallint no default not null , |
| +> b smallint no default not null , |
| +> c smallint no default not null , |
| +> d smallint no default not null, |
| +> e smallint no default not null, |
| +> f smallint no default not null, |
| +> g smallint no default not null, |
| +> h smallint no default not null, |
| +> i smallint no default not null, |
| +> j smallint no default not null, |
| +> primary key (a,b,c) ); |
| |
| --- SQL operation complete. |
| >> |
| >>insert into t024mdam values |
| +> (-32768,-32768,-32768,-32768,-32768,-32768,-32768,-32768,-32768,-32768), |
| +> (0,0,0,0,0,0,0,0,0,0), |
| +> (0,2,2,2,2,2,2,2,2,2), |
| +> (2,0,2,2,2,2,2,2,2,2), |
| +> (2,2,2,2,2,2,2,2,2,2), |
| +> (2,2,3,2,2,2,2,2,2,2), |
| +> (2,2,4,2,2,2,2,2,2,2), |
| +> (2,3,2,2,2,2,2,2,2,2), |
| +> (2,3,3,2,2,2,2,2,2,2), |
| +> (2,3,4,2,2,2,2,2,2,2), |
| +> (3,2,2,2,2,2,2,2,2,2), |
| +> (3,2,3,2,2,2,2,2,2,2), |
| +> (3,2,4,2,2,2,2,2,2,2), |
| +> (3,3,2,2,2,2,2,2,2,2), |
| +> (3,3,3,2,2,2,2,2,2,2), |
| +> (3,3,4,2,2,2,2,2,2,2), |
| +> (32767,32767,32767,32767,32767,32767,32767,32767,32767,32767); |
| |
| --- 17 row(s) inserted. |
| >> |
| >>update statistics for table t024mdam on every column generate 6 intervals; |
| |
| *** ERROR[1002] Catalog SEABASE does not exist or has not been registered on node \NSK. |
| |
| *** ERROR[4082] Object SEABASE.SCH.T024MDAM does not exist or is inaccessible. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>#ifdef SEABASE_REGRESS |
| >>control query shape scan('t024mdam', MDAM_COLUMNS ALL); |
| |
| --- SQL operation complete. |
| >>#else |
| >>control query shape exchange(scan('t024mdam', MDAM_COLUMNS ALL)); |
| >>#endif |
| >> |
| >> |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Simplest case -- |
| >>-- Single disjunct -- |
| >>-- Equal predicates on all 3 key columns -- |
| >>-- -- |
| >>-- Expected result: 1 row (2,2,2,...) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c = 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Single disjunct -- |
| >>-- Equal predicates on first 2 columns -- |
| >>-- Open range pred. on 3rd - [value, hi] -- |
| >>-- -- |
| >>-- Expected result: 3 rows (2,2,2,...) -- |
| >>-- (2,2,3,...) -- |
| >>-- (2,2,4,...) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and c >= 2; |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 3 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: Single disjunct -- |
| >>-- Equal predicates on first 2 columns -- |
| >>-- 2 ORed predicates on 3rd (cluster w/2 cells) -- |
| >>-- -- |
| >>-- Expected result: 2 rows (2,2,2,....) -- |
| >>-- (2,2,4,....) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where a = 2 and b = 2 and (c = 4 or c = 2); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 2 disjuncts - -- |
| >>-- One has equal pred. on all 3 key columns -- |
| >>-- (no rows satisfy this disjunct) -- |
| >>-- Other has equal pred. on first 2 columns -- |
| >>-- and OR'ed equal pred. on 3rd key column -- |
| >>-- -- |
| >>-- Expected result: 1 row (2,0,2,....) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 0 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 2 and c = 0); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 0 2 2 2 2 2 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 2 disjuncts - -- |
| >>-- One has equal pred. on first 2 columns -- |
| >>-- and OR'ed equal pred. on 3rd key column -- |
| >>-- Other has equal pred. on first 2 columns -- |
| >>-- and no predicate on 3rd key column -- |
| >>-- -- |
| >>-- Expected result: 2 rows (0,2,2,....) -- |
| >>-- (2,0,2,....) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 0 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 2); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 2 2 2 2 2 2 2 2 2 |
| 2 0 2 2 2 2 2 2 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 2 disjuncts - -- |
| >>-- One has equal pred. on first 2 columns -- |
| >>-- and OR'ed equal pred. on 3rd key column -- |
| >>-- Other has equal pred. on all 3 columns -- |
| >>-- -- |
| >>-- Expected result: 3 rows (0,0,0,....) -- |
| >>-- (2,2,2,....) |
| >>-- (2,2,4,....) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b = 2 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 0 and c = 0); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| |
| --- 3 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 3 disjuncts - -- |
| >>-- Predicates on all 3 key columns in all -- |
| >>-- 3 disjuncts -- |
| >>-- < and > operators introduced in this script-- |
| >>-- Single and OR'ed equal predicates also -- |
| >>-- used in this script -- |
| >>-- 2 disjuncts produced 0 rows -- |
| >>-- -- |
| >>-- Expected result: 5 rows (2,0,2,....) -- |
| >>-- (2,2,2,....) |
| >>-- (2,2,4,....) |
| >>-- (2,3,2,....) |
| >>-- (2,3,4,....) |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b > -1 and b < 5 and (c = 4 or c = 2)) |
| +> or (a = 0 and b = 2 and c = 0) |
| +> or (a = 5 and b > 5 and b < 4 and c = 5); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 2 0 2 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| 2 3 2 2 2 2 2 2 2 2 |
| 2 3 4 2 2 2 2 2 2 2 |
| |
| --- 5 row(s) selected. |
| >> |
| >>------------------------------------------------------------------ |
| >>-- Description: 3 disjuncts - -- |
| >>-- Predicates on all 3 key columns in all -- |
| >>-- 3 disjuncts -- |
| >>-- <= and >= operators introduced in this -- |
| >>-- script. -- |
| >>-- Single and OR'ed equal predicates, and -- |
| >>-- < and > operators also used. -- |
| >>-- 1 disjunct produces 0 rows -- |
| >>-- -- |
| >>-- Expected result: 8 rows (0,0,0,....) -- |
| >>-- (2,0,2,....) -- |
| >>-- (2,2,2,....) -- |
| >>-- (2,2,3,....) -- |
| >>-- (2,2,4,....) -- |
| >>-- (2,3,2,....) -- |
| >>-- (2,3,3,....) -- |
| >>-- (2,3,4,....) -- |
| >>------------------------------------------------------------------ |
| >> |
| >>select * from t024mdam |
| +>where (a = 2 and b > -1 and b < 5 and c >= 2 and c <= 4) |
| +> or (a = 0 and b = 0 and c = 0) |
| +> or (a = 2 and b = 2 and c = -6); |
| |
| A B C D E F G H I J |
| ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ |
| |
| 0 0 0 0 0 0 0 0 0 0 |
| 2 0 2 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 2 2 |
| 2 2 3 2 2 2 2 2 2 2 |
| 2 2 4 2 2 2 2 2 2 2 |
| 2 3 2 2 2 2 2 2 2 2 |
| 2 3 3 2 2 2 2 2 2 2 |
| 2 3 4 2 2 2 2 2 2 2 |
| |
| --- 8 row(s) selected. |
| >> |
| >>control query shape anything; |
| |
| --- SQL operation complete. |
| >> |
| >>drop table t024mdam; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>cqd RANGESPEC_TRANSFORMATION RESET; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>#ifndef SEABASE_REGRESS |
| >>-- Re-run entire test with droppable primary keys. |
| >>cqd PRIMARY_KEY_CONSTRAINT_DROPPABLE_OPTION 'ON'; |
| >>obey TEST024(runtest); |
| >>cqd PRIMARY_KEY_CONSTRAINT_DROPPABLE_OPTION RESET; |
| >> |
| >>-- Re-run entire test with ALLOW_INPUT_PRED_REPLICATION_REDUCTION enabled. |
| >>cqd ALLOW_INPUT_PRED_REPLICATION_REDUCTION '2'; |
| >>obey TEST024(runtest); |
| >>cqd ALLOW_INPUT_PRED_REPLICATION_REDUCTION RESET; |
| >> |
| >>-- Run the MDAM costing tests. |
| >>obey TEST024(DDL); |
| >>obey TEST024(cost_runTest); |
| >>#endif |
| >> |
| >>log; |
