--
--   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.
--
-- This test lang/LOB.sql still includes tests for 
-- DB2 UDB incompatible datatype NCLOB.
-- Still waiting for DB2 UDB compatible functionality for NCLOB to be implemented
-- Also note that in DB2 UDB, to create BLOB and CLOB strings greater than 1 gigabyte,
-- the NOT LOGGED option must be specified (SQLSTATE 42993).
-- test that BLOB/CLOB are not reserved words
create table blob(a int);
insert into blob values(3);
select blob.a from blob;

create table clob(a int);
insert into clob values(3);
select clob.a from clob;

create table nclob(a int);
insert into nclob values(3);
select nclob.a from nclob;

create table a(blob int, clob int, nclob int);
insert into a values(1,2,3);
insert into a(blob, clob, nclob) values(1,2,3);
select a.blob, a.clob, a.nclob from a;
select a.blob, a.clob, a.nclob from a where a.blob = 1;
select a.blob, a.clob, a.nclob from a where a.clob = 2;
select a.blob, a.clob, a.nclob from a where a.nclob = 3;
select a.blob, a.clob, a.nclob from a where a.blob = 1 and a.clob = 2 and a.nclob = 3;

create table b(blob blob(3K), clob clob(2M));
insert into b values(cast(X'0031' as blob(3K)),cast('2' as clob(2M)));
insert into b(blob, clob, nclob) values(cast(X'0031' as blob(3K)),cast('2' as clob(2M)));
select b.blob, b.clob, b.nclob from b;

-- equal tests are not allowed
select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(5));
select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7));
select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7));
select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7));
select 1 from b where cast(X'e0' as blob(5))=cast(X'e000' as blob(7));

select 1 from b where X'80' = cast(X'80' as blob(1));
select 1 from b where cast(X'80' as blob(1)) = X'80';
select 1 from b where cast(X'80' as blob(1)) = cast(X'80' as blob(1));

select 1 from b where '1' = cast('1' as clob(1));
select 1 from b where cast('1' as clob(1)) = '1';
select 1 from b where cast('1' as clob(1)) = cast('1' as clob(1));

select 1 from b where '1' = cast('1' as nclob(1));
select 1 from b where cast('1' as nclob(1)) = '1';
select 1 from b where cast('1' as nclob(1)) = cast('1' as nclob(1));

-- NCLOB is comparable with CLOB

select 1 from b where cast('1' as nclob(10)) = cast('1' as clob(10));
select 1 from b where cast('1' as clob(10)) = cast('1' as nclob(10));

-- comparsion using tables
select * from b as b1, b as b2 where b1.blob=b2.blob;
select * from b as b1, b as b2 where b1.blob!=b2.blob;

select * from b as b1, b as b2 where b1.blob=X'20';
select * from b as b1, b as b2 where X'20'=b1.blob;
select * from b as b1, b as b2 where X'20'!=b1.blob;

select * from b as b1, b as b2 where b1.blob=X'7575';
select * from b as b1, b as b2 where X'7575'=b1.blob;

select b.blob, b.clob, b.nclob from b where b.blob = '1' and b.clob = '2' and b.nclob = '3';
select b.blob from b where b.blob = '1';
-- however it works for types which cloudscape autocasts to char
select b.clob from b where b.clob = '2';
select b.nclob from b where b.nclob = '3';

-- test insert of NULL
insert into b values(null, null, null);
select * from b;

-- cleanup
drop table blob;
drop table clob;
drop table nclob;
drop table a;
drop table b;

-- test insert limitations
create table b(b blob(5));
create table c(c clob(5));
create table n(n nclob(5));

insert into b values(cast(X'01020304' as blob(10)));
insert into b values(cast(X'0102030405' as blob(10)));
insert into b values(cast(X'010203040506' as blob(10)));

-- truncate before insert, no errors
insert into b values(cast(X'01020304' as blob(5)));
insert into b values(cast(X'0102030405' as blob(5)));
insert into b values(cast(X'010203040506' as blob(5)));

-- clob/nclob
--   ok in spite of not being cast
insert into c values('1234');
insert into c values('12345');
insert into c values('123456');

insert into n values('1234');
insert into n values('12345');
insert into n values('123456');

--   ok
insert into c values(cast('1234' as clob(5)));
insert into c values(cast('12345' as clob(5)));
insert into c values(cast('123456' as clob(5)));

insert into n values(cast('1234' as nclob(5)));
insert into n values(cast('12345' as nclob(5)));
insert into n values(cast('123456' as nclob(5)));

select * from b;
select * from c;
select * from n;

-- concatenate
values cast('12' as clob(2)) || cast('34' as clob(2));
values cast('12' as nclob(2)) || cast('34' as nclob(2));
select 1 from b where cast('12' as clob(2)) || cast('34' as clob(2)) = '1234';
select 1 from b where cast('12' as nclob(2)) || cast('34' as nclob(2)) = '1234';
select 1 from b where cast('12' as clob(2)) || cast('34' as clob(2)) = cast('1234' as clob(4));
select 1 from b where cast('12' as nclob(2)) || cast('34' as nclob(2)) = cast('1234' as clob(4));

-- like
select * from b where b like '0102%';
select * from c where c like '12%';
select * from n where n like '12%';

select * from b where b like cast('0102%' as blob(10));
select * from c where c like cast('12%' as clob(10));
select * from n where n like cast('12%' as nclob(10));

-- cleanup
drop table b;
drop table c;
drop table n;

-- test syntax of using long type names
create table a(a binary large object(3K));
create table b(a character large object(3K));
create table c(a national character large object(3K));
create table d(a char large object(204K));

-- create index (not allowed)
create index ia on a(a);
create index ib on b(a);
create index ic on c(a);
create index id on d(a);

-- cleanup
drop table a;
drop table c;
drop table d;

-- ORDER tests on LOB types (not allowed)
select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(5));
select 1 from b where cast(X'e0' as blob(5))!=cast(X'e0' as blob(5));
select 1 from b where cast(X'e0' as blob(5))<cast(X'e0' as blob(5));
select 1 from b where cast(X'e0' as blob(5))>cast(X'e0' as blob(7));
select 1 from b where cast(X'e0' as blob(5))<=cast(X'e0' as blob(7));
select 1 from b where cast(X'e0' as blob(5))>=cast(X'e0' as blob(7));

select 1 from b where cast('fish' as clob(5))=cast('fish' as clob(5));
select 1 from b where cast('fish' as clob(5))!=cast('fish' as clob(5));
select 1 from b where cast('fish' as clob(5))<cast('fish' as clob(5));
select 1 from b where cast('fish' as clob(5))>cast('fish' as clob(7));
select 1 from b where cast('fish' as clob(5))<=cast('fish' as clob(7));
select 1 from b where cast('fish' as clob(5))>=cast('fish' as clob(7));

select 1 from b where cast('fish' as nclob(5))=cast('fish' as nclob(5));
select 1 from b where cast('fish' as nclob(5))!=cast('fish' as nclob(5));
select 1 from b where cast('fish' as nclob(5))<cast('fish' as nclob(5));
select 1 from b where cast('fish' as nclob(5))>cast('fish' as nclob(7));
select 1 from b where cast('fish' as nclob(5))<=cast('fish' as nclob(7));
select 1 from b where cast('fish' as nclob(5))>=cast('fish' as nclob(7));

-- test operands on autocast
-- beetle 5282
-- <,> <=, >= operands are not supported in db2 but supported in cloudscape
-- compare w. integer/char types are also not ok

-- CLOB testing
CREATE TABLE testoperatorclob (colone clob(1K));
INSERT INTO testoperatorclob VALUES (CAST('50' AS CLOB(1K)));
INSERT INTO testoperatorclob VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K)));
select * from testoperatorclob;

-- these select statements should raise an error but are successful in cloudscape
select * from testoperatorclob where colone > 10;
select * from testoperatorclob where colone > 5;
select * from testoperatorclob where colone < 70;
select * from testoperatorclob where colone = 50;
select * from testoperatorclob where colone != 10;
select * from testoperatorclob where colone <= 70;
select * from testoperatorclob where colone >= 10;
select * from testoperatorclob where colone <> 10;

select * from testoperatorclob where colone > '10';
select * from testoperatorclob where colone > '5';
select * from testoperatorclob where colone < '70';
select * from testoperatorclob where colone = '50';
select * from testoperatorclob where colone != '10';
select * from testoperatorclob where colone <= '70';
select * from testoperatorclob where colone >= '10';
select * from testoperatorclob where colone <> '10';

drop table testoperatorclob;

-- BLOB testing
CREATE TABLE testoperatorblob (colone blob(1K));
INSERT INTO testoperatorblob VALUES (CAST('50' AS BLOB(1K)));
INSERT INTO testoperatorblob VALUES (CAST(cast('50' as varchar(80)) AS BLOB(1K)));
select * from testoperatorblob;

-- these select statements should raise an error but are successful in cloudscape
select * from testoperatorblob where colone > 10;
select * from testoperatorblob where colone > 5;
select * from testoperatorblob where colone < 999999;
select * from testoperatorblob where colone = 00350030;
select * from testoperatorblob where colone != 10;
select * from testoperatorblob where colone <= 999999;
select * from testoperatorblob where colone >= 10;
select * from testoperatorblob where colone <> 10;

select * from testoperatorblob where colone > '10';
select * from testoperatorblob where colone > '5';
select * from testoperatorblob where colone < '70';
select * from testoperatorblob where colone = '50';
select * from testoperatorblob where colone != '10';
select * from testoperatorblob where colone <= '70';
select * from testoperatorblob where colone >= '10';
select * from testoperatorblob where colone <> '10';

drop table testoperatorblob;

-- NCLOB testing
CREATE TABLE testoperatornclob (colone nclob(1K));
INSERT INTO testoperatornclob VALUES (CAST('50' AS NCLOB(1K)));
INSERT INTO testoperatornclob VALUES (CAST(cast('50' as varchar(80)) AS NCLOB(1K)));
select * from testoperatornclob;

-- these select statements should raise an error but are successful in cloudscape
select * from testoperatornclob where colone > 10;
select * from testoperatornclob where colone > 5;
select * from testoperatornclob where colone < 70;
select * from testoperatornclob where colone = 50;
select * from testoperatornclob where colone != 10;
select * from testoperatornclob where colone <= 70;
select * from testoperatornclob where colone >= 10;
select * from testoperatornclob where colone <> 10;

select * from testoperatornclob where colone > '10';
select * from testoperatornclob where colone > '5';
select * from testoperatornclob where colone < '70';
select * from testoperatornclob where colone = '50';
select * from testoperatornclob where colone != '10';
select * from testoperatornclob where colone <= '70';
select * from testoperatornclob where colone >= '10';
select * from testoperatornclob where colone <> '10';

drop table testoperatornclob;

----- test method invocations on LOB objects (should disallow)
-- setup
drop table b;
create table b(b blob(77));
insert into b values(cast('33' as blob(77)));

create table c(c clob(77));
insert into c values(cast('33' as clob(77)));

-- LOB as main object for method invocation not allowed
values (cast('1' as blob(1M)))->toString();
values (cast('1' as clob(1M)))->toString();
values (cast('1' as nclob(1M)))->toString();

drop table b;
drop table c;

------ TEST length functions on LOBs
---- BLOB
values length(cast('foo' as blob(10)));
values {fn length(cast('foo' as blob(10)))};

---- CHAR
values length(cast('foo' as char(10)));
values {fn length(cast('foo' as char(10)))};

---- CLOB
values length(cast('foo' as clob(10)));
values {fn length(cast('foo' as clob(10)))};

---- NCLOB
values length(cast('foo' as nclob(10)));
values {fn length(cast('foo' as nclob(10)))};

-- Longvarchar negative tests

create table testPredicate1 (c1 long varchar);
create table testPredicate2 (c1 long varchar);
insert into testPredicate1 (c1) values 'a';
insert into testPredicate2 (c1) values 'a';

-- UNION
select * from testPredicate1 union select * from testPredicate2;

-- IN predicate
select c1 from testPredicate1 where c1 IN (select c1 from testPredicate2);


-- NOT IN predicate
select c1 from testPredicate1 where c1 NOT IN (select c1 from testPredicate2);

-- ORDER BY clause
select * from testPredicate1 order by c1;


-- GROUP BY clause
select substr(c1,1,2) from testPredicate1 group by c1;

-- JOIN
select * from testPredicate1 t1, testPredicate2 t2 where t1.c1=t2.c1;
select * from testPredicate1 LEFT OUTER JOIN testPredicate2 on testPredicate1.c1=testPredicate2.c1;

-- PRIMARY KEY
create table testConst1(c1 long varchar not null primary key);

-- UNIQUE KEY constraints
CREATE TABLE testconst2 (col1 long varchar not null, CONSTRAINT uk UNIQUE (col1));

-- FOREIGN KEY constraints
create table testConst3 (c1 char(10) not null, primary key (c1));
create table testConst4 (c1 long varchar not null, constraint fk foreign key (c1) references testConst3 (c1));
drop table testConst3;

-- MAX aggregate function
select max(c1) from testPredicate1;

-- MIN aggregate function
select min(c1) from testPredicate1;

drop table testpredicate1;
drop table testpredicate2;

-- CLOB/BLOB limits and sizes

-- FAIL - bigger than 2G or 2Gb with no modifier
create table DB2LIM.FB1(FB1C BLOB(3G));
create table DB2LIM.FB2(FB2C BLOB(2049M));
create table DB2LIM.FB3(FB3C BLOB(2097153K));
create table DB2LIM.FB4(FB4C BLOB(2147483648));

-- OK 2G and end up as 2GB - 1 (with modifier)
create table DB2LIM.GB1(GB1C BLOB(2G));
create table DB2LIM.GB2(GB2C BLOB(2048M));
create table DB2LIM.GB3(GB3C BLOB(2097152K));
create table DB2LIM.GB4(GB4C BLOB(2147483647));

-- next lower value
create table DB2LIM.GB5(GB5C BLOB(1G));
create table DB2LIM.GB6(GB6C BLOB(2047M));
create table DB2LIM.GB7(GB7C BLOB(2097151K));
create table DB2LIM.GB8(GB8C BLOB(2147483646));
drop table DB2LIM.GB5;
drop table DB2LIM.GB6;
drop table DB2LIM.GB7;
drop table DB2LIM.GB8;

-- no length (default to 1Mb)
create table DB2LIM.GB9(GB9C BLOB);
create table DB2LIM.GB10(GB10C BINARY LARGE OBJECT);
drop table DB2LIM.GB9;
drop table DB2LIM.GB10;

-- FAIL - bigger than 2G or 2Gb with no modifier
create table DB2LIM.FC1(FC1C CLOB(3G));
create table DB2LIM.FC2(FC2C CLOB(2049M));
create table DB2LIM.FC3(FC3C CLOB(2097153K));
create table DB2LIM.FC4(FC4C CLOB(2147483648));

-- OK 2G and end up as 2GC - 1 (with modifier)
create table DB2LIM.GC1(GC1C CLOB(2G));
create table DB2LIM.GC2(GC2C CLOB(2048M));
create table DB2LIM.GC3(GC3C CLOB(2097152K));
create table DB2LIM.GC4(GC4C CLOB(2147483647));

-- next lower value
create table DB2LIM.GC5(GC5C CLOB(1G));
create table DB2LIM.GC6(GC6C CLOB(2047M));
create table DB2LIM.GC7(GC7C CLOB(2097151K));
create table DB2LIM.GC8(GC8C CLOB(2147483646));
drop table DB2LIM.GC5;
drop table DB2LIM.GC6;
drop table DB2LIM.GC7;
drop table DB2LIM.GC8;

-- no length (default to 1Mb)
create table DB2LIM.GC9(GC9C CLOB);
create table DB2LIM.GC10(GC10C CHARACTER LARGE OBJECT);
create table DB2LIM.GC11(GC11C CHAR LARGE OBJECT);
drop table DB2LIM.GC9;
drop table DB2LIM.GC10;
drop table DB2LIM.GC11;

SELECT CAST (TABLENAME AS CHAR(10)) AS T, CAST (COLUMNNAME AS CHAR(10)) AS C, CAST (COLUMNDATATYPE AS CHAR(30)) AS Y
	FROM SYS.SYSTABLES T, SYS.SYSSCHEMAS S, SYS.SYSCOLUMNS C
	WHERE S.SCHEMAID = T.SCHEMAID AND CAST(S.SCHEMANAME AS VARCHAR(128))= 'DB2LIM'
	AND C.REFERENCEID = T.TABLEID
	ORDER BY 1;

--- CHAR/VARCHAR and LOBs. (beetle 5741)
--- test that we can insert CHAR/VARCHAR directly

CREATE TABLE b (colone blob(1K));

VALUES '50';
INSERT INTO b VALUES '50';

VALUES cast('50' as varchar(80));
INSERT INTO b VALUES cast('50' as varchar(80));

VALUES (CAST('50' AS BLOB(1K)));
INSERT INTO b VALUES (CAST('50' AS BLOB(1K)));

VALUES (CAST(cast('50' as varchar(80)) AS BLOB(1K)));
INSERT INTO b VALUES (CAST(cast('50' as varchar(80)) AS BLOB(1K)));

VALUES cast('50' as long varchar);
INSERT INTO b VALUES cast('50' as long varchar);

-- test w LOBs
VALUES (CAST('50' AS BLOB(1K)));
INSERT INTO b VALUES (CAST('50' AS BLOB(1K)));

VALUES (CAST('50' AS CLOB(1K)));
INSERT INTO b VALUES (CAST('50' AS CLOB(1K)));

VALUES (CAST('50' AS NCLOB(1K)));
INSERT INTO b VALUES (CAST('50' AS NCLOB(1K)));

DROP TABLE b;



CREATE TABLE c (colone clob(1K));

VALUES '50';
INSERT INTO c VALUES '50';

VALUES cast('50' as varchar(80));
INSERT INTO c VALUES cast('50' as varchar(80));

VALUES (CAST('50' AS CLOB(1K)));
INSERT INTO c VALUES (CAST('50' AS CLOB(1K)));

VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K)));
INSERT INTO c VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K)));

VALUES cast('50' as long varchar);
INSERT INTO c VALUES cast('50' as long varchar);

-- test w LOBs
VALUES (CAST('50' AS BLOB(1K)));
INSERT INTO c VALUES (CAST('50' AS BLOB(1K)));

VALUES (CAST('50' AS CLOB(1K)));
INSERT INTO c VALUES (CAST('50' AS CLOB(1K)));

VALUES (CAST('50' AS NCLOB(1K)));
INSERT INTO c VALUES (CAST('50' AS NCLOB(1K)));

DROP TABLE c;



CREATE TABLE n (colone clob(1K));

VALUES '50';
INSERT INTO n VALUES '50';

VALUES cast('50' as varchar(80));
INSERT INTO n VALUES cast('50' as varchar(80));

VALUES (CAST('50' AS CLOB(1K)));
INSERT INTO n VALUES (CAST('50' AS CLOB(1K)));

VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K)));
INSERT INTO n VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K)));

VALUES cast('50' as long varchar);
INSERT INTO n VALUES cast('50' as long varchar);

-- test w LOBs
VALUES (CAST('50' AS BLOB(1K)));
INSERT INTO n VALUES (CAST('50' AS BLOB(1K)));

VALUES (CAST('50' AS CLOB(1K)));
INSERT INTO n VALUES (CAST('50' AS CLOB(1K)));

VALUES (CAST('50' AS NCLOB(1K)));
INSERT INTO n VALUES (CAST('50' AS NCLOB(1K)));

DROP TABLE n;