inbetween.sql

derby database source code.good for you.

--
-- Test IN lists and BETWEEN on the builtin  types
-- assumes these builtin types exist:
--	int, smallint, char, varchar, real
--
-- other things we might test:
-- compatibility with dynamic parameters 
set isolation to rr;

CREATE FUNCTION ConsistencyChecker() RETURNS VARCHAR(128)
EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_ConsistencyChecker.runConsistencyChecker'
LANGUAGE JAVA PARAMETER STYLE JAVA;
autocommit off;

-- Create the tables

create table s (i int);

create table t (i int, s smallint, c char(10), v varchar(50), 
	d double precision, r real, e date, t time, p timestamp);

create table test (i int, d double precision);


-- Populate the tables

insert into s values (1);
insert into s values (1);
insert into s values (2);

insert into t values (null, null, null, null, null, null, null, null, null);
insert into t values (0, 100, 'hello', 'everyone is here', 200.0e0,
	300.0e0, '1992-01-01', '12:30:30', '1992-01-01 12:30:30');
insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0,
	-300.0e0, '1992-01-02', '12:30:59', '1992-01-01 12:30:45');

insert into test values (2, 4.0);
insert into test values (3, 10.0);
insert into test values (4, 12.0);
insert into test values (5, 25.0);
insert into test values (10, 100.0);
insert into test values (-6, 36);

-- BETWEEN

-- negative tests
-- type mismatches
select * from t where i between i and e;
select * from t where i between i and t;
select * from t where i between i and p;
select * from t where e between p and p;
select * from t where 1 between e and p;

-- between null and
select * from t where i between null and i;

-- cardinality violation on a subquery
select * from t where i between i and (select i from s);

-- all parameters
select * from t where ? between ? and ?;

-- positive tests

-- type comparisons
select i from t where i between s and r;
select i from t where i between r and d;
select i from t where s between i and r;
select i from t where s between r and d;
select i from t where r between s and i;
select i from t where d between s and i;
select i from t where i between 40e1 and 50e1;
select i from t where s between 40e1 and 50e1;
select i from t where c between c and v;
select i from t where 40e1 between i and s;
select i from t where 'goodbye' between c and c;
select i from t where '1992-01-01 12:30:45' between p and p;

-- between 2 and 1
select * from t where i between 2 and 1;
select * from t where not i not between 2 and 1;
select * from t where not i between 2 and 1;
select * from t where i not between 2 and 1;

-- between arbitrary expressions
select * from test where sqrt(d) between 5 and 10;
select * from test where (i+d) between 20 and 50;
select * from test where {fn abs (i)} between 5 and 8;
select * from test where (i+d) not between 20 and 50;
select * from test where sqrt(d) not between 5 and 20;

-- not (test clone() once its implemented)
select i from t where i not between i and i;
select i from t where s not between s and s;
select i from t where c not between c and c;
select i from t where v not between v and v;
select i from t where d not between d and d;
select i from t where r not between r and r;
select i from t where e not between e and e;
select i from t where t not between t and t;
select i from t where p not between p and p;

-- between complex expressions
select i from t where s between (select i from s where i = 2) and
							    (select 100 from s where i = 2);
select * from t where i between i and (select max(i) from s);

-- subquery between
select i from t where (select i from s where i = 2) between 1 and 2;

-- parameters
prepare q1 as 'select i from t where ? between 2 and 3';
prepare q2 as 'select i from t where ? between ? and 3';
prepare q3 as 'select i from t where ? between 2 and ?';
prepare q4 as 'select i from t where i between ? and ?';

execute q1 using 'values 2';
execute q2 using 'values (2, 2)';
execute q3 using 'values (2, 3)';
execute q4 using 'values (0, 1)';

remove q1;
remove q2;
remove q3;
remove q4;

update s set i = 5 where i between 2 and 3;
select * from s;

-- delete - where clause
delete from s where i between 3 and 5;
select * from s;

-- check consistency of scans, etc.
values ConsistencyChecker();

drop table s;


-- IN List

-- recreate s as ss
create table ss (i int);
insert into ss values (1);
insert into ss values (1);
insert into ss values (2);

-- negative tests

-- empty list
select i from t where i in ();
-- null in list
select i from t where i in (null);
-- cardinality violation from subquery
select i from t where i in (1, 3, 5, 6, (select i from ss));
-- type mismatches
select i from t where i in (i, i, e);
select i from t where i in (i, i, t);
select i from t where i in (i, i, p);
select i from t where e in (e, p, e);
select i from t where 1 in (p, 2, 1);

-- positive tests

-- type comparisons
select i from t where i in (s, r, i, d, 40e1);
select s from t where s in (s, r, i, d, 40e1);
select r from t where r in (s, r, i, d, 40e1);
select d from t where d in (s, r, i, d, 40e1);
select i from t where 40e1 in (s, r, i, d, 40e1);
select c from t where c in (c, v, 'goodbye');
select v from t where v in (c, v, 'goodbye');
select i from t where 'goodbye' in (c, v, 'goodbye');
select i from t where '1992-01-01 12:30:45' in (p, '1992-01-01 12:30:45');
select p from t where p in (p, '1992-01-01 12:30:45');

-- not (test clone() once its implemented)
select i from t where i not in (i, i);
select i from t where s not in (s, s);
select i from t where c not in (c, c);
select i from t where v not in (v, v);
select i from t where d not in (d, d);
select i from t where r not in (r, r);
select i from t where e not in (e, e);
select i from t where t not in (t, t);
select i from t where p not in (p, p);

-- more nots
select i from t where i not in (0, 9, 8, 2, 7);
select i from t where i not in (0, 9, 8, 2, 7);
select i from t where not i not in (0, 9, 8, 2, 7);

-- 1 element list
select s from t where s in (100);

-- left side of expression
select s from t where (s in (100));
select s from t where (s in (100));

-- complex expressions
select i from t where i in (1, 3, 5, 6, (select i from ss where i = 2) - 2);
select * from test where i in (sqrt(d),{fn abs (i)}, -6);
select * from test where sqrt(d) in (i, 4);
select * from test where (i+d) in (6, 30);
select * from test where sqrt(d) in (i);
select * from test where {fn abs (i)} in (i);
select * from test where {fn abs (i)} not in (i);
select * from test where (i+d) not in (6, 30);
select * from test where sqrt(d) not in (5, 10, 2);

-- subquery in
select i from t where (select i from ss where i = 2) in (1, 2);

-- derived table
select * from (select * from t 
where i in (1, 3, 5, 6, (select i from ss where i = 2) - 2)) a;

update ss set i = 5 where i in (2, 3, 40e1);
select * from ss;

-- delete - where clause
delete from ss where i not in (5, 3);
select * from ss;

-- in/between 
create table u (c1 integer);
insert into u values null;
insert into u values 1;
insert into u values null;
insert into u values 2;
select * from u where c1 between 2 and 3;
select * from u where c1 in (2, 3, 0, 1);

-- add some more rows before testing static in list xform