comparisons.sql

derby database source code.good for you.

--
-- this test shows the current supported comparison operators
--

-- first, do comparisons on the int type

-- create a table with couple of int columns
create table inttab (c1 int, c2 int);

-- insert some values
insert into inttab values (0, 0);
insert into inttab values (null, 5);
insert into inttab values (1, 1);
insert into inttab values (2147483647, 2147483647);

-- select each one in turn
select c1 from inttab where c1 = 0;
select c1 from inttab where c1 = 1;
select c1 from inttab where c1 = 2147483647;

-- now look for a value that isn't in the table
select c1 from inttab where c1 = 2;

-- now test null = null semantics
select c1 from inttab where c1 = c1;

-- test is null semantics
select c1 from inttab where c1 is null;
select c1 from inttab where c1 is not null;
select c1 from inttab where not c1 is null;

-- now test <>
select c1 from inttab where c1 <> 0;
select c1 from inttab where c1 <> 1;
select c1 from inttab where c1 <> 2147483647;
select c1 from inttab where c1 <> 2;
select c1 from inttab where c1 <> c1;
select c1 from inttab where c1 <> c2;

-- now test !=
select c1 from inttab where c1 != 0;
select c1 from inttab where c1 != 1;
select c1 from inttab where c1 != 2147483647;
select c1 from inttab where c1 != 2;
select c1 from inttab where c1 != c1;
select c1 from inttab where c1 != c2;

-- now test <
select c1 from inttab where c1 < 0;
select c1 from inttab where c1 < 1;
select c1 from inttab where c1 < 2;
select c1 from inttab where c1 < 2147483647;
select c1 from inttab where c1 < c1;
select c1 from inttab where c1 < c2;

-- now test >
select c1 from inttab where c1 > 0;
select c1 from inttab where c1 > 1;
select c1 from inttab where c1 > 2;
select c1 from inttab where c1 > 2147483647;
select c1 from inttab where c1 > c1;
select c1 from inttab where c1 > c2;

-- now test <=
select c1 from inttab where c1 <= 0;
select c1 from inttab where c1 <= 1;
select c1 from inttab where c1 <= 2;
select c1 from inttab where c1 <= 2147483647;
select c1 from inttab where c1 <= c1;
select c1 from inttab where c1 <= c2;

-- now test >=
select c1 from inttab where c1 >= 0;
select c1 from inttab where c1 >= 1;
select c1 from inttab where c1 >= 2;
select c1 from inttab where c1 >= 2147483647;
select c1 from inttab where c1 >= c1;
select c1 from inttab where c1 >= c2;

-- now test not
select c1 from inttab where not (c1 = 0);
select c1 from inttab where not (c1 <> 0);
select c1 from inttab where not (c1 != 0);
select c1 from inttab where not (c1 < 0);
select c1 from inttab where not (c1 <= 0);
select c1 from inttab where not (c1 > 0);
select c1 from inttab where not (c1 >= 0);

-- create a table with a couple of smallint columns.  All smallint vs. smallint
-- comparisons must be done between columns, because there are no smallint
-- constants in the language

create table smallinttab (c1 smallint, c2 smallint);

-- insert some values
insert into smallinttab values (0, 0);
insert into smallinttab values (null, null);
insert into smallinttab values (1, 1);
insert into smallinttab values (32767, 32767);
insert into smallinttab values (0, 9);
insert into smallinttab values (null, 8);
insert into smallinttab values (1, 7);
insert into smallinttab values (32767, 6);

-- select the ones where the columns are equal
select c1, c2 from smallinttab where c1 = c2;

-- test smallint = int semantics
select c1 from smallinttab where c1 = 0;
select c1 from smallinttab where c1 = 1;
select c1 from smallinttab where c1 = 32767;

-- test that the smallint gets promoted to int, and not vice versa.  65537
-- when converted to short becomes 1
select c1 from smallinttab where c1 = 65537;

-- test int = smallint semantics
select c1 from smallinttab where 0 = c1;
select c1 from smallinttab where 1 = c1;
select c1 from smallinttab where 32767 = c1;

-- test that the smallint gets promoted to int, and not vice versa.  65537
-- when converted to short becomes 1
select c1 from smallinttab where 65537 = c1;

-- Now test <>
select c1, c2 from smallinttab where c1 <> c2;
select c1, c2 from smallinttab where c1 != c2;

-- test smallint <> int semantics
select c1 from smallinttab where c1 <> 0;
select c1 from smallinttab where c1 <> 1;
select c1 from smallinttab where c1 <> 32767;

select c1 from smallinttab where c1 != 0;
select c1 from smallinttab where c1 != 1;
select c1 from smallinttab where c1 != 32767;

-- test that the smallint gets promoted to int, and not vice versa.  65537
-- when converted to short becomes 1
select c1 from smallinttab where c1 <> 65537;
select c1 from smallinttab where c1 != 65537;

-- test int = smallint semantics
select c1 from smallinttab where 0 <> c1;
select c1 from smallinttab where 1 <> c1;
select c1 from smallinttab where 32767 <> c1;

select c1 from smallinttab where 0 != c1;
select c1 from smallinttab where 1 != c1;
select c1 from smallinttab where 32767 != c1;

-- test that the smallint gets promoted to int, and not vice versa.  65537
-- when converted to short becomes 1
select c1 from smallinttab where 65537 <> c1;
select c1 from smallinttab where 65537 != c1;

-- Now test <
select c1, c2 from smallinttab where c1 < c2;

-- test smallint < int semantics
select c1 from smallinttab where c1 < 0;
select c1 from smallinttab where c1 < 1;
select c1 from smallinttab where c1 < 32767;

-- test that the smallint gets promoted to int, and not vice versa.  65537
-- when converted to short becomes 1
select c1 from smallinttab where c1 < 65537;

-- test int < smallint semantics
select c1 from smallinttab where 0 < c1;
select c1 from smallinttab where 1 < c1;
select c1 from smallinttab where 32767 < c1;

-- test that the smallint gets promoted to int, and not vice versa.  65537
-- when converted to short becomes 1
select c1 from smallinttab where 65537 < c1;

-- Now test >
select c1, c2 from smallinttab where c1 > c2;

-- test smallint > int semantics
select c1 from smallinttab where c1 > 0;
select c1 from smallinttab where c1 > 1;
select c1 from smallinttab where c1 > 32767;

-- test that the smallint gets promoted to int, and not vice versa.  65537
-- when converted to short becomes 1
select c1 from smallinttab where c1 > 65537;

-- test int > smallint semantics
select c1 from smallinttab where 0 > c1;
select c1 from smallinttab where 1 > c1;
select c1 from smallinttab where 32767 > c1;

-- test that the smallint gets promoted to int, and not vice versa.  65537
-- when converted to short becomes 1
select c1 from smallinttab where 65537 > c1;

-- Now test <=
select c1, c2 from smallinttab where c1 <= c2;

-- test smallint <= int semantics
select c1 from smallinttab where c1 <= 0;
select c1 from smallinttab where c1 <= 1;
select c1 from smallinttab where c1 <= 32767;

-- test that the smallint gets promoted to int, and not vice versa.  65537
-- when converted to short becomes 1
select c1 from smallinttab where c1 <= 65537;

-- test int <= smallint semantics
select c1 from smallinttab where 0 <= c1;
select c1 from smallinttab where 1 <= c1;
select c1 from smallinttab where 32767 <= c1;

-- test that the smallint gets promoted to int, and not vice versa.  65537
-- when converted to short becomes 1
select c1 from smallinttab where 65537 <= c1;

-- Now test >=
select c1, c2 from smallinttab where c1 >= c2;

-- test smallint >= int semantics
select c1 from smallinttab where c1 >= 0;
select c1 from smallinttab where c1 >= 1;
select c1 from smallinttab where c1 >= 32767;

-- test that the smallint gets promoted to int, and not vice versa.  65537
-- when converted to short becomes 1
select c1 from smallinttab where c1 >= 65537;

-- test int >= smallint semantics
select c1 from smallinttab where 0 >= c1;
select c1 from smallinttab where 1 >= c1;
select c1 from smallinttab where 32767 >= c1;

-- test is null semantics
select c1 from smallinttab where c1 is null;
select c1 from smallinttab where c1 is not null;
select c1 from smallinttab where not c1 is null;

-- test that the smallint gets promoted to int, and not vice versa.  65537
-- when converted to short becomes 1
select c1 from smallinttab where 65537 >= c1;


-- create a table with a couple of bigint columns.  

create table biginttab (c1 bigint, c2 bigint, c3 int, c4 smallint);

-- insert some values
insert into biginttab values (0, 0, 0, 0 );
insert into biginttab values (null, null, null, null);
insert into biginttab values (9223372036854775807, 
							   9223372036854775807,
							   2147483647,
							   32767);
insert into biginttab values (-9223372036854775808, 
							   -9223372036854775808,
							   -2147483648,
							   -32768);

-- select the ones where the columns are equal
select c1, c2 from biginttab where c1 = c2;

-- test bigint = int semantics
select c1 from biginttab where c1 = 0;
select c1 from biginttab where c1 = c3;

-- test int = bigint semantics
select c1 from biginttab where 0 = c1;
select c1 from biginttab where c3 = c1;

-- test bigint = smallint semantics
select c1 from biginttab where c1 = c4;

-- test smallint = bigint semantics
select c1 from biginttab where c4 = c1;

-- Now test <>
select c1, c2 from biginttab where c1 <> c2;

-- test bigint <> int semantics
select c1 from biginttab where c1 <> 0;
select c1 from biginttab where c1 <> c3;

-- test int <> bigint semantics
select c1 from biginttab where 0 <> c1;
select c1 from biginttab where c3 <> c1;

-- test bigint <> smallint semantics
select c1 from biginttab where c1 <> c4;

-- test smallint <> bigint semantics
select c1 from biginttab where c4 <> c1;


-- Now test <
select c1, c2 from biginttab where c1 < c2;

-- test bigint < int semantics
select c1 from biginttab where c1 < 0;
select c1 from biginttab where c1 < c3;

-- test int < bigint semantics
select c1 from biginttab where 0 < c1;
select c1 from biginttab where c3 < c1;

-- test bigint < smallint semantics
select c1 from biginttab where c1 < c4;

-- test smallint < bigint semantics
select c1 from biginttab where c4 < c1;

-- Now test >
select c1, c2 from biginttab where c1 > c2;

-- test bigint > int semantics
select c1 from biginttab where c1 > 0;
select c1 from biginttab where c1 > c3;

-- test int > bigint semantics
select c1 from biginttab where 0 > c1;
select c1 from biginttab where c3 > c1;

-- test bigint > smallint semantics
select c1 from biginttab where c1 > c4;

-- test smallint > bigint semantics
select c1 from biginttab where c4 > c1;

-- Now test <=
select c1, c2 from biginttab where c1 <= c2;

-- test bigint <= int semantics
select c1 from biginttab where c1 <= 0;
select c1 from biginttab where c1 <= c3;

-- test int <= bigint semantics
select c1 from biginttab where 0 <= c1;
select c1 from biginttab where c3 <= c1;

-- test bigint <= smallint semantics
select c1 from biginttab where c1 <= c4;

-- test smallint <= bigint semantics
select c1 from biginttab where c4 <= c1;

-- Now test >=
select c1, c2 from biginttab where c1 >= c2;

-- test bigint >= int semantics
select c1 from biginttab where c1 >= 0;
select c1 from biginttab where c1 >= c3;

-- test int >= bigint semantics
select c1 from biginttab where 0 >= c1;
select c1 from biginttab where c3 >= c1;

-- test bigint >= smallint semantics
select c1 from biginttab where c1 >= c4;

-- test smallint >= bigint semantics
select c1 from biginttab where c4 >= c1;

-- test is null semantics
select c1 from biginttab where c1 is null;
select c1 from biginttab where c1 is not null;
select c1 from biginttab where not c1 is null;